Re: [GENERAL] Multi row sequence?

[Michael Fuhr]

On Fri, Dec 17, 2004 at 11:10:12AM -, Filip Wuytack wrote:

> Is it possible to have a sequence (as a multirow prim key), where sequence
> (id) only increase per group of data (grp).
> 
> E.g.
> +++-+
> | grp| id | name|
> +++-+
> | fish   |  1 | lax |
> | mammal |  1 | dog |
> | mammal |  2 | cat |
> | mammal |  3 | whale   |
> | bird   |  1 | penguin |
> | bird   |  2 | ostrich |
> +++-+

PostgreSQL's sequences are simply number generators that return a
unique value.  If you want to generate keys in the manner you
describe, then you could use a trigger to calculate what the next
id should be.  You'd probably have to lock the table to ensure that
the operation works when multiple transactions are updating at the
same time.

Take a look at the "Triggers" chapter in the documentation, as well
as the "Trigger Procedures" section of the "PL/pgSQL - SQL Procedural
Language" chapter and the "Concurrency Control" chapter.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] UNION with more restrictive DISTINCT

[Vincent Hikida]

One solution is
SELECT COALESCE(t1.id,t2.id)
,  COALESCE(t1.name,t2.name)
   FROM t1 FULL JOIN t2  ON t1.id = t2.id
- Original Message - 
From: "peter pilsl" <[EMAIL PROTECTED]>
To: "PostgreSQL List" <[EMAIL PROTECTED]>
Sent: Wednesday, December 15, 2004 1:03 PM
Subject: [GENERAL] UNION with more restrictive DISTINCT


I'd like to UNION two queries but the distinct-criteria for UNION should 
not be all columns in the queries, but only one.

example. two tables:
test=# select id,name from t1;
 id | name
+--
  1 | bob
  2 | mike
(2 rows)
test=# select id,name from t2;
 id |  name
+-
  1 | bob
  2 | mike j.
(2 rows)
# select id,name from t1 union select id,name from t2;
 id |  name
+-
  1 | bob
  2 | mike
  2 | mike j.
(3 rows)
now I want a construct that returns me only one row for each id. If 
there are different names for that id's in the different tables, the 
name of t2 should be chosen.

like:
# select id,name from t1 UNION ON (id) AND SUPERMAGICPREFER (t2.name) 
select id,name from t2;
 id |  name
+-
  1 | bob
  2 | mike j.
(2 rows)

What is an appropriate approach to this? If I use my UNION-query as 
subquery for a SELECT DISTINCT ID, I loose the name, which is important.

thnx.
peter

--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] NewsForge Poll: Favorite open source database?

[Oleg Bartunov]

On Thu, 16 Dec 2004, Marcelo Cid wrote:
http://www.newsforge.com/pollBooth.pl?qid=54
I see strange line below results:
"(You've already voted.)"
I don't believe such results because I do know I didn't been here :)

§ion=mainpage&aid=4
Marcelo Cid
Analista de Sistemas
PRODAM/DIDET/DAA/VEA
5080-9227

Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] could not read block 84253 of relation "tablename": Input/output error

[ruben]

Hi:
No way to access "tablename". Can I do something to recover? Is this a 
hardware error?

PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 
20020903 (Red Hat Linux 8.0 3.2-7)

Thanks in advance.
Ruben.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] NewsForge Poll: Favorite open source database?

[Mike Mascari]

Oleg Bartunov wrote:
On Thu, 16 Dec 2004, Marcelo Cid wrote:
http://www.newsforge.com/pollBooth.pl?qid=54

I see strange line below results:
"(You've already voted.)"
I don't believe such results because I do know I didn't been here :) 
Marcel's link did the favor of voting for you. Not good
Mike Mascari
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] BTree max row size?

[Alvaro Herrera]

On Thu, Dec 16, 2004 at 06:20:57PM -0500, Ernest Kim wrote:

Hi,

> I'm getting the following error message when I try to update a row in
> a table:
> 
> ERROR:  54000: index row size 2720 exceeds btree maximum, 2713
> 
> Does anyone know how to to change the btree maximum row size?  How did
> it come up with 2713 as a max row size?

You could compile Postgres with BLCKSZ set to a higher value (currently
8192).  Or you could change your indexing strategy, because if you now
want to store 2720 bytes, some other day you may want to store 11000,
and then you would overflow the ~10900 bytes that a 32768 BLCKSZ would
buy you.

What are you indexing?

The 2713 comes from (8192 - some overhead) / 3.

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] OSX 10.3.7 broke Postgresql 8.0.0b5?

[Timothy Perrigo]

On Dec 17, 2004, at 11:10 PM, Tom Lane wrote:
Fascinating.  As far as I can tell on my machine, 10.3.7 did not change
the kernel IPC limits.  So if it's not working for you guys that would
suggest that 10.3.7 added some new background usage of IPC resources,
which in combination with the PG postmaster exceeds the 
same-as-it-ever-
was kernel limit.

If ipcs worked then we'd have some chance of investigating this, but OS
X doesn't provide ipcs.  (Thank you Apple ... not)
FWIW, my installation of PG on OS X defaults to
max_connections = 50
shared_buffers = 300
because values higher than that exceed the default kernel limits.
It looks like yours has 100/1000 --- did you hand-modify that?  Or 
maybe
you hand-modified the kernel limits?  Another possible explanation is
that the 10.3.7 update overwrote any local changes you'd made to the 
IPC
limits.

regards, tom lane

I dropped the shared_buffers from 300 (the number determined by initdb) 
to 200 and I am now able to start the server.

Tim
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Multi row sequence?

[Bruno Wolff III]

On Fri, Dec 17, 2004 at 11:10:12 -,
  Filip Wuytack <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> I just started looking into PostgreSQL (coming from Mysql before), but have
> a question:
> 
> Is it possible to have a sequence (as a multirow prim key), where sequence
> (id) only increase per group of data (grp).

Why do you want to do this? It would be a lot simpler to generate unique
values over the table and that will work just fine if all you need
is uniqueness.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] OSX 10.3.7 broke Postgresql 8.0.0b5?

[Scott Ribe]

> It looks like yours has 100/1000 --- did you hand-modify that?  Or maybe
> you hand-modified the kernel limits?  Another possible explanation is
> that the 10.3.7 update overwrote any local changes you'd made to the IPC
> limits.

All the recent OS X .x updates have replaced /etc/rc. For all I know they
may always have done so, but of course prior to 10.3 we didn't have to
modify /etc/rc directly.


-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 665-7007 voice



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Multi row sequence?

[Bruno Wolff III]

On Sat, Dec 18, 2004 at 17:19:27 -,
  Filip Wuytack <[EMAIL PROTECTED]> wrote:
> I'm working on a database that will contain companies (group) and the
> relevant listing (securities-> id) and related periodic information. I want
> the end users of the data to see the relationship between the 2 (comp A,
> security 01, security 02;comp B, security 01, security 02) in the key
> (company,security) as this would for part of the keys in all the other
> related tables (depending if the info is on a company level or security
> level and e.g using the date). By using a normal increment value as unique
> key, I would loose this relation information in key, no?

No. Though from what you are saying here, it doesn't look like you need
that ID at all.
It looks like there should be a company table, a security table and
a company - security table. You haven't said enough about the peridoic
data to suggest how to handle that.
You probably want to use ID columns for the companies and securities,
because there might be companies with the same name (and perhaps securities
as well).
To model this relation you could use the tables below. (In reality you
probably need something more complicated to handle other information.)

create table company (
  id serial primary key,
  name text not null
);

create table security (
  id serial primary key,
  name text not null
);

create table compsec (
  company int references company(id),
  security int references security(id),
  primary key (company, security)
);

---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] Make a query faster...

[v . demartino2]

Dear all,

Context: FreeBSD postgresql 7.4.5, on a pentium 3 server 128MB.

I have a huge table letture02 made of 1,340,000 "freezed" records of 98
columns each (they do not change over time because the data are related
to measurements made in 2002 and 2003 there is no need to update them or,
worst, insert new records).

Because I have to query the table according to a condition related to two
columns only , contatore and data, I created a primary index on both of
them (..primary index (contatore,data)...)
But, if I query the database according to setr value of the indexed variables
an index scan is used and total runtime is a snap

explain analyze select * from letture02 where contatore=1244 and data=180;

  QUERY PLAN


--
 Index Scan using contatoredata on letture02  (cost=0.00..67.96 rows=17
width=11
60) (actual time=0.413..0.423 rows=1 loops=1)
   Index Cond: ((contatore = 1244) AND (data = 180))
 Total runtime: 0.899 ms
(3 rows)


On the contrary querying the table on the same fields but with a > or <
condition a sequential scan is used with unbearable runtime

 explain analyze select * from letture02 where contatore>1244 and data>180;

 QUERY PLAN



 Seq Scan on letture02  (cost=0.00..118103.00 rows=73000 width=1160) (actual
tim
e=6061.921..121600.729 rows=287860 loops=1)
   Filter: ((contatore > 1244) AND (data > 180))
 Total runtime: 122696.066 ms
(3 rows)


Because this second type of query should be highly demanded, is there anything
I could do to shorten the runtime?

 Vittorio




---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] OSX 10.3.7 broke Postgresql 8.0.0b5?

[Tom Lane]

Scott Ribe <[EMAIL PROTECTED]> writes:
>> It looks like yours has 100/1000 --- did you hand-modify that?  Or maybe
>> you hand-modified the kernel limits?  Another possible explanation is
>> that the 10.3.7 update overwrote any local changes you'd made to the IPC
>> limits.

> All the recent OS X .x updates have replaced /etc/rc. For all I know they
> may always have done so, but of course prior to 10.3 we didn't have to
> modify /etc/rc directly.

Bingo.  So I didn't see any change in behavior, because I hadn't edited
/etc/rc on my machine; the complainants are probably those who had
edited /etc/rc to raise the kern.sysv limits.

I'll add something to the Admin Guide pointing out that OS X updates are
likely to overwrite any manual changes to /etc/rc.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Scheduler in Postgres

[Jim C. Nasby]

As I mentioned before, I think most (if not all) of the development
effort would be outside the core database, meaning any of us would be
able to work on it (as opposed to internal stuff which requires a lot
more knowledge). There are two features dbms_job has that would probably
require some internal support:

In oracle, jobs are fired off by the database engine. If the database is
up and job support is enabled, your jobs will run. Without some kind of
support for the database to fire up connections and execute some kind of
code we would have to rely on some external means to do so. This is less
robust, isn't cross-platform, and requires more work of the end-users.

The other feature is that the connection running jobs in oracle has the
ability to re-create the connection environment used to submit the job.
This means jobs are run as the same user who submitted the job, and
certain session settings are also duplicated. In PostgreSQL, there's
currently no way to assume the identity of another user.

Even with lack of support for these two features, I still think it would
be very usefull to create a generic job system, probably as a pgFoundary
project. Enough people have asked about it that I'm sure there's plenty
of re-invented code out there. If we have a solid framework that people
are using, we'll have a much stronger case for getting the two features
I mentioned added to the back-end.

On Thu, Dec 16, 2004 at 08:58:00AM -0500, Geoffrey wrote:
> Bruno Wolff III wrote:
> 
> >cron isn't really part of the OS. Up until 8.0, any OS that Postgres
> >ran on had cron. I have seen claims that there is a version of cron that
> >runs under windows, but haven't verified that. Given this I don't see
> >how a dependence on cron is going to cause you portability problems.
> 
> I would prefer the development effort be applied to more specific 
> database engine issues and enhancements.  As Bruno has noted, you can 
> get some variation of cron on virtually every OS that runs Postgresql.
> 
> -- 
> Until later, Geoffrey
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 

-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Scheduler in Postgres

[Jim C. Nasby]

Can you open-source your code? I'm sure others would benefit from it.

On Thu, Dec 16, 2004 at 09:37:04AM -0800, Steve Atkins wrote:
> On Thu, Dec 16, 2004 at 10:12:46AM +0100, Marco Colombo wrote:
> > On Wed, 15 Dec 2004, Jim C. Nasby wrote:
> > 
> > >No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this
> > >is a question that comes up pretty often. I think the bulk of the work
> > >to make this happen could be done outside of the core database, so it
> > >seems a good candidate for pgfoundry.
> > 
> > What's wrong with at, cron, and some trivial shell scripting?
> > (This is a real question, I wonder what advantages dbms_job has.)
> 
> I don't know about dbms_job, but I implemented a simple cron-a-like in
> postgresql for one of my apps so that database clients can schedule
> events. The clients don't have access to a shell on the DB server,
> it's a completely separate security domain. And the clients may not
> even be powered up when an event is due to fire, so running cron there
> isn't going to work either.
> 
> Cheers,
>   Steve
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 

-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Best practice in postgres

[Jim C. Nasby]

On Sat, Dec 11, 2004 at 10:40:42PM -0600, Guy Rouillier wrote:
> Nilesh Doshi wrote:
> > Also, I thought vacuuming will be easier if oracle schema becomes
> > database in postgres. For example in our case each schema is like
> > 80-90 gb, smaller compare to vacuuming on 400gb.
> 
> I'm very new to PostgreSQL myself, so this is definitely not expert
> advice.  But the load imposed by vacuum is directly related to update
> activity.  So if your database is fairly static, you probably won't see
> much benefit to splitting out schemas into separate DBs.  Also, 8.0 has
> an auto-vacuum daemon that is supposed to lighten the load incurred by
> vacuum by checking frequently in the background.

FWIW, autovacuum has been around since 7.3. When it does a vacuum it's
no different than running the vacuum command by hand; what is different
is that it watches table activity and after a certain number of
insert/update/deletes it starts a vacuum (or analyze) automatically.
What is new in 8.0 is the ability to tell vacuum to sleep a period of
time between each tuple, so that you don't bog-down the server when
vacuum is running.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Scheduler in Postgres

[Jim C. Nasby]

On Fri, Dec 17, 2004 at 12:07:21PM -0500, Greg Stark wrote:
> 
> Csaba Nagy <[EMAIL PROTECTED]> writes:
> 
> > Now, cron isn't exactly part of the OS, is it ?
> 
> Yes, it is.

Depends on how you define 'OS'. Linux is nothing but a kernel afterall,
so it's up to each distro to decide if they'll include cron (and gentoo
doesn't include cron for example). I'm pretty sure it's part of the base
install of every unix I've used, though.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Scheduler in Postgres

[Jim C. Nasby]

On Fri, Dec 17, 2004 at 03:25:20PM -0800, Ben wrote:
> I have many such tasks. Depending on implementation, it has the potential
> to be a TINY amount of less work to schedule such tasks from inside
> the database, but it takes all of about a minute to schedule it through
> cron. Including the amount of time it takes to refer to the man page.

Sure, it might take no amount of time to schedule it, but that's not the
point. Now you have to have code to:

- deal with what you do if the database is down when cron fires off
- provide some kind of semaphore so you don't have multiple copies of
  one job running at once
- connect to the database as the proper user using the proper
  credentials and setup the proper environment

What this all boils down to is there are 2 different sets of needs. For
some things cron works great, and there's no need to worry about any of
this. For other things it would be much easier not to worry about cron
at all and just have the database handle your periodic tasks (which
presumably are stored procedures or something similar).

I think pgcron is an interesting idea, though not directly PostgreSQL
related (I'd argue that if you're going to make a fancier replacement
for cron you should make it back-end agnostic). I also think a lot of
people would benefit from having something similar to dbms_job (Oracle's
scheduling system) available for PostgreSQL. As I mentioned in another
email, I think it's possible to get 90-95% of dbms_job's functionality
without having to touch the backend at all, which eliminates the
argument that development time is better spent elsewhere (since the core
PostgreSQL folks wouldn't have to worry about it at all). BTW, I mention
dbms_job as an example not because it's necessarily the right way to
handle scheduling, but because it's a convenient example. Oracle's newer
job system is even better.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

pgpool registered on pgfoundry (Re: [GENERAL] gborg.postgresql.org)

[Tatsuo Ishii]

Thanks. I have succesfully registered pgpool project on
pgfoundry.org(http://pgfoundry.org/projects/pgpool/).

I have been working on the site, and have just set up a mailing list,
web page, and uploaded latest version of pgpool tar ball.
--
Tatsuo Ishii

> > It seems gborg does not accept new projects any more. Does anybody
> > know what happens with it? Also it's really slow and sometimes does
> > not respond. I need an open source development web site which can
> > safely hosts pgpool. Recently I'm getting lot of mails regarding
> > pgpool and need mails lists, archives and hopefully searching
> > capabilty of it.
> 
> You want to look at http://www.pgfoundry.org, where we are planning on 
> migrating gborg projects over to ...
> 
> Also, Josh/Sean are working on a new server that we are goin to be moving 
> both over to over the next week or so ...
> 
> 
> Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
> Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664
> 

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Scheduler in Postgres

[Steve Atkins]

On Sat, Dec 18, 2004 at 03:01:54PM -0600, Jim C. Nasby wrote:
> Can you open-source your code? I'm sure others would benefit from it.

Eh. Big chunks of it are application specific, and a fair bit of the
rest uses some commercial libraries. I'll see what I can do.
(It'd be worth it to kill the monthly "why don't we do cron in the DB?"
 thread :) )

Cheers,
  Steve

> On Thu, Dec 16, 2004 at 09:37:04AM -0800, Steve Atkins wrote:
> > On Thu, Dec 16, 2004 at 10:12:46AM +0100, Marco Colombo wrote:
> > > On Wed, 15 Dec 2004, Jim C. Nasby wrote:
> > > 
> > > >No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this
> > > >is a question that comes up pretty often. I think the bulk of the work
> > > >to make this happen could be done outside of the core database, so it
> > > >seems a good candidate for pgfoundry.
> > > 
> > > What's wrong with at, cron, and some trivial shell scripting?
> > > (This is a real question, I wonder what advantages dbms_job has.)
> > 
> > I don't know about dbms_job, but I implemented a simple cron-a-like in
> > postgresql for one of my apps so that database clients can schedule
> > events. The clients don't have access to a shell on the DB server,
> > it's a completely separate security domain. And the clients may not
> > even be powered up when an event is due to fire, so running cron there
> > isn't going to work either.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Scheduler in Postgres

[Christopher Browne]

Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Jim C. Nasby") 
would write:
> In PostgreSQL, there's currently no way to assume the identity of
> another user.

I'm confused at that...

There seem to be ways at time of connection establishment, whether via
the psql "-U" option, or the PGconnect "dbuser=foo" option, or during
a psql session via "\c - newuser".

In what way do those not represent "assumption of another user's
identity"?
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"In the long run every program becomes rococo - then rubble."
-- Alan Perlis

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Scheduler in Postgres

[Tom Lane]

Christopher Browne <[EMAIL PROTECTED]> writes:
> Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Jim C. Nasby") 
> would write:
>> In PostgreSQL, there's currently no way to assume the identity of
>> another user.

> I'm confused at that...

> There seem to be ways at time of connection establishment, whether via
> the psql "-U" option, or the PGconnect "dbuser=foo" option, or during
> a psql session via "\c - newuser".

Not to mention SET SESSION AUTHORIZATION.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Scheduler in Postgres

[Christopher Browne]

I'm confused at that...

There seem to be ways at time of connection establishment, whether via
the psql "-U" option, or the PGconnect "dbuser=foo" option, or during
a psql session via "\c - newuser"

In what way do those not represent "assumption of a user's identity"?
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"In the long run every program becomes rococo - then rubble."
-- Alan Perlis

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes

[Bruce Momjian]

Because the GET DIAGNOSTICS is after the CREATE TEMP TABLE command, I
think zero is the right value, rather than the number of rows in the
SELECT.  I can see why it was handy to do it the old way in 7.3 but it
seems it was a byproduct of GET DIAGNOSTICS not working properly.

I suppose the only clean way to do it now is to do a SELECT COUNT().

---

Rob Long wrote:
> Hello.
> 
> Seeking further clarity regarding GET DIAGNOSTICS behavior in 7.4.
> 
> As described previously GET DIAGNOSTICS in the following example does not 
> work in 7.4.5:
> 
> CREATE OR REPLACE FUNCTION "public"."rowcount_test" () RETURNS bigint AS'
> DECLARE
>base_hits bigint;
>  BEGIN
>  
>  base_hits := 0;
>  
>  CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data;
>  GET DIAGNOSTICS base_hits = ROW_COUNT;
> 
>  RETURN base_hits;
>  END;
> 'LANGUAGE 'plpgsql' VOLATILE
> 
> Base_hits returns 0 and not 1 while 7.3 returns 1.  Without base_hits := 0, 
> null would be returned.
> 
> Output:
> 
> 7.3.3
> queriesdbtest=# select * from public.rowcount_test();
>  rowcount_test 
> ---
>  1
> (1 row)
> 
> 7.4.5
> queriesdbtest=# select * from public.rowcount_test();
>  rowcount_test 
> ---
>  0
> (1 row)
> 
> What is the preferred/recommended way for obtaining rows worked with via the 
> last SQL statement?  Can this be a bug in 7.4.5 as the documentation 
> indicates that this should work as described?
> 
> Thanks in advance,
> Rob
> 
> 
> 
> Maksim Likharev <[EMAIL PROTECTED]> writes:
> 
> >> consider following code:
> >  
> >
> 
> >> CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS '
> >> DECLARE
> >>base_hits bigint;
> >> BEGIN
> >> CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data;
> >> GET DIAGNOSTICS base_hits = ROW_COUNT;
> >  
> >
> 
> >> RETURN base_hits;
> >> END;
> >> ' LANGUAGE PLPGSQL VOLATILE;
> >  
> >
> 
> >> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp
> >> table
> >> in 7.4.5 GET DIAGNOSTICS returns 0
> >  
> >
> 
> Hmm.  I'm not sure if that's a bug or an improvement.  The command did
> not return any rows to plpgsql, so in that sense row_count = 0 is
> correct, but I can see why you feel you've lost some capability.
> 
> Anyone else have an opinion about this?
> 
>   regards, tom lane
> 
> 
> 
> 
> Subject:
> Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes
> From:
> "Richard Huxton" <[EMAIL PROTECTED]>
> Date:
> Thu, 2 Dec 2004 01:34:37 -0800
> 
> To:
> "Tom Lane" <[EMAIL PROTECTED]>
> CC:
> <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
> 
> 
> Tom Lane wrote:
> 
> >> Maksim Likharev <[EMAIL PROTECTED]> writes:
> >> 
> >  
> >
>  in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into
>  a temp table in 7.4.5 GET DIAGNOSTICS returns 0
> >>
> >>
> >> 
> >> 
> >> Hmm.  I'm not sure if that's a bug or an improvement.  The command
> >> did not return any rows to plpgsql, so in that sense row_count = 0 is
> >>  correct, but I can see why you feel you've lost some capability.
> >> 
> >> Anyone else have an opinion about this?
> >  
> >
> 
> Well, from the manuals:
> "The currently available status items are ROW_COUNT, the number of rows
> processed by the last SQL command sent down to the SQL engine"
> 
> Nothing there about rows being returned.
> 
> And by analogy:
> "A PERFORM statement sets FOUND true if it produces (and discards) a
> row, false if no row is produced."
> 
> If you've FOUND rows then presumably ROW_COUNT should be non-zero. So 
> set it if rows aren't returned I'd opine.
> 
> --
>Richard Huxton
>Archonet Ltd
> 
> 
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])