Using curr_val Wisely

Started by Jeff Waughalmost 26 years ago3 messagesgeneral
Jump to latest
#1Jeff Waugh
jdub@aphid.net

Hi all,

I've got one daft question, and one good one...

Good One:

I'm using curr_val('blah_id_seq') in a transaction like so: (in pseudo SQL)

BEGIN;
INSERT INTO blah;
INSERT INTO foo (currval('blah_id_seq'));
COMMIT;

So the second insert requires the primary key of the first table as a
reference.

In the documentation, it says that even when there's multiple users, the
back end essentially guarantees that curr_val will work in this case. How
does it distinguish? Within the transaction, connection or something else?

Just wondering so I don't stuff anything up too badly! :)

Daft One:

This would be asked all the time, I'm sure. If I want to return the primary
key, or oid of the inserted record. The best way I can figure to do that now
is by selecting curr_val('blah_id_seq') at the end of this transaction
(which is why I asked the above question, because I'd like to be sure it
returns the id I think it should!)

For some reason, I can't get the postgres interface in PHP to return the
oid... But I'm more interested in doing this as a transaction anyway. :)

- Jeff

-- jdub@aphid.net --------------------------------- http://linux.conf.au/ --

linux.conf.au - coming to Sydney in January 2001

Installing Linux Around Australia - http://linux.org.au/installfest/

#2Andrew Snow
als@fl.net.au
In reply to: Jeff Waugh (#1)
RE: Using curr_val Wisely

I'm using curr_val('blah_id_seq') in a transaction like so: (in
pseudo SQL)

curr_val() returns the current value of the sequence, in the current
transaction.
So an insertion in transaction A won't affect the value of it in existing
transaction B.

Regards,

Andrew

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Andrew Snow (#2)
Re: Using curr_val Wisely

"Andrew Snow" <als@fl.net.au> writes:

I'm using curr_val('blah_id_seq') in a transaction like so: (in
pseudo SQL)

curr_val() returns the current value of the sequence, in the current
transaction.

More precisely, currval() returns the same value returned by the most
recent nextval() on that sequence in the current backend --- transaction
boundaries don't matter AFAIR.

If you try currval() without ever having executed nextval() in the
current session, you will see this is so.

(I believe a setval() also causes currval() to become set, but that's
a special case that seldom matters.)

So you need not worry about other backends when using currval(). If you
have rules or triggers that can cause the sequence to get advanced, then
you could get confusing results if you forget these rules/triggers are
being fired.

regards, tom lane