Thread: Re: [ADMIN] performance issue using DBI
If you need to get the actual "current value" without incrementing, try:
SELECT last_value FROM <sequence_name>;
This will return the last value returned from a nextval command directly
from the sequence properties in the system tables.
-----Original Message-----
From: Dan Langille [mailto:dan@langille.org]
Sent: Thursday, June 06, 2002 12:34 PM
To: Nicolas Nolst
Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [ADMIN] [GENERAL] performance issue using DBI
On 6 Jun 2002 at 17:14, Oliver Elphick wrote:
> On Thu, 2002-06-06 at 14:55, Joshua b. Jore wrote:u
> > Don't use currval since some other process might alter the
> > sequence between the time you call nextval and currval.
>
> This is wrong. currval() will always return the last serial assigned
in
> *the*same*session*. It is not affected by other users at all.
Folks, here's a short test which might help. Note the BEGIN.
$ psql testing
testing=# create sequence test;
CREATE
testing=# select currval('test');
ERROR: test.currval is not yet defined in this session
testing=# select setval('test', 1);
setval
--------
1
(1 row)
testing=# select currval('test');
currval
---------
1
(1 row)
testing=# select currval('test');
currval
---------
1
(1 row)
testing=# select currval('test');
currval
---------
1
(1 row)
testing=# select currval('test');
currval
---------
1
(1 row)
testing=#
Then, in another window, I did this:
$ psql testing
# select nextval('test');
nextval
---------
2
(1 row)
testing=# select nextval('test');
nextval
---------
3
(1 row)
testing=# select nextval('test');
nextval
---------
4
(1 row)
testing=#
Then back to the other window:
testing=# select currval('test');
currval
---------
1
(1 row)
testing=# select nextval('test');
nextval
---------
5
(1 row)
testing=#
cheers
FWIW: I always use nextval when looking for a new ID.
--
Dan Langille
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Thu, Jun 06, 2002 at 12:45:09PM -0400, Lee Green wrote: > If you need to get the actual "current value" without incrementing, try: > > SELECT last_value FROM <sequence_name>; > > This will return the last value returned from a nextval command directly > > from the sequence properties in the system tables. Except that if you do it this way, you *will* be affected by setvals done by other users. Use currval(), that's what it's there for. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Some of my carefully throught out, and painstakingly graded SQL inserts (or updates) are failing intermittantly because I guess I didn't grade hard enough! :) I'm getting ERROR: messages on the console (and rightly so!) ... My question is, how can I find out what the text of the failed query would be. That would give me a better clue as to where my code fails. It would also take a lot less time going through the code SQL statement by SQL statement to find the guilty one. I compiled --without-syslog to eek the last bit of performance out of PostgreSQL. I have no problem recompiling with more feedback detail enabled to resolve the problem. I just need a clue on where to start looking. The whole logging part of PostgreSQL is still magical to me. CG __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com
On Tue, 11 Jun 2002 07:53:19 -0700 (PDT) "Chris Gamache" <cgg007@yahoo.com> wrote: > I'm getting ERROR: messages on the console (and rightly so!) ... My question > is, how can I find out what the text of the failed query would be. Set "debug_print_query = true" in postgresql.conf and send the postmaster a SIGHUP. > I compiled --without-syslog to eek the last bit of performance out of > PostgreSQL. I'd be surprised if that had any performance impact, particularly since logging through syslog is disabled by default. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Redirect the output to a logfile of your choice. There was a huge talk about this yesterday on the list. #!/bin/bash HOME=/usr/local/pgsql LOG=$HOME/logs/postgres.log USER=darren ulimit -SHc unlimited su $USER -c "nohup $HOME/bin/postmaster -D $HOME/db $@ >$LOG 2>&1 </dev/null&" This script above is what i use to start the postmaster and as you can see i redirect the output to the file postgres.log. There are also different logging levels in the postgres.conf file that you can turn on. HTH Darren Ferguson On Tue, 11 Jun 2002, Chris Gamache wrote: > Some of my carefully throught out, and painstakingly graded SQL inserts (or > updates) are failing intermittantly because I guess I didn't grade hard enough! > :) > > I'm getting ERROR: messages on the console (and rightly so!) ... My question > is, how can I find out what the text of the failed query would be. That would > give me a better clue as to where my code fails. It would also take a lot less > time going through the code SQL statement by SQL statement to find the guilty > one. > > I compiled --without-syslog to eek the last bit of performance out of > PostgreSQL. I have no problem recompiling with more feedback detail enabled to > resolve the problem. I just need a clue on where to start looking. The whole > logging part of PostgreSQL is still magical to me. > > CG > > __________________________________________________ > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup > http://fifaworldcup.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
The solution is not as sexy as the one I was hoping for... :) I'm not one to look a gift-horse in the mouth. Thank you for your responses, which were right on target. I imagine I can combine the debug_print_query with the logging to a postgres.log and just do a tail -f postgres.log | grep -B1 ERROR: and see where I'm going wrong! CG --- Darren Ferguson <darren@crystalballinc.com> wrote: > Redirect the output to a logfile of your choice. There was a huge talk > about this yesterday on the list. > > #!/bin/bash > HOME=/usr/local/pgsql > LOG=$HOME/logs/postgres.log > USER=darren > > ulimit -SHc unlimited > su $USER -c "nohup $HOME/bin/postmaster -D $HOME/db $@ >$LOG 2>&1 > </dev/null&" > > This script above is what i use to start the postmaster and as you can see > i redirect the output to the file postgres.log. There are also different > logging levels in the postgres.conf file that you can turn on. > __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com