Thread: Re: [ADMIN] performance issue using DBI

Re: [ADMIN] performance issue using DBI

From
"Lee Green"
Date:
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

Re: [ADMIN] performance issue using DBI

From
Martijn van Oosterhout
Date:
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.

"ERROR:" Messages

From
Chris Gamache
Date:
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

Re: "ERROR:" Messages

From
Neil Conway
Date:
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

Re: "ERROR:" Messages

From
Darren Ferguson
Date:
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
>


Re: "ERROR:" Messages

From
Chris Gamache
Date:
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