Sequences : getting back the nextval() result on an insert
Hi
I am new to this list, but I've carefully searched the archives before posting this question.
I am using JDBC to access postgreSQL 6.3.
If one inserts records using a sequence to generate an auto-increment primary key, then you can get back the result by using "select currval('seq')". There is always a time between the insert and the select to retrieve the newly created primary key. What I do in my application is insert a record and display it in a java swing JTable (including the primary key).
How can I be certain that the primary key I retrieve with select currval('seq') is the one just inserted? Because in heavily loaded multi-user systems someone else can insert a record in the same table between my insert and my select.
Regards
Wim Ceulemans
Nice Software Solutions
wim.ceulemans@pophost.eunet.be
On Thu, 11 Jun 1998, Wim Ceulemans wrote:
Hi
I am new to this list, but I've carefully searched the archives before posting this question.
I am using JDBC to access postgreSQL 6.3.
If one inserts records using a sequence to generate an auto-increment primary key, then you can get back the result by using "select currval('seq')". There is always a time between the insert and the select to retrieve the newly created primary key. What I do in my application is insert a record and display it in a java swing JTable (including the primary key).
How can I be certain that the primary key I retrieve with select currval('seq') is the one just inserted? Because in heavily loaded multi-user systems someone else can insert a record in the same table between my insert and my select.
Actually, as each user spawns a new copy of the backend u can be sure that
currval() gives correct answer as it uses it's backend information. I.e
nextval() and curval() called sequently from the same process/application
will refer to their backend.
If u look at the create secuence schema dump (pg_dump -s XXX) u will see
the CACHE parameter which AFAIK referes to the pool of sequence numbers
alocated per backend.
Hope this helps...
Marin
-= Why do we need gates in a world without fences? =-
Or you could use transactions.
-DEJ
Show quoted text
-----Original Message-----
From: Marin D [SMTP:marin@cybernet.bg]
Sent: Thursday, June 11, 1998 5:42 AM
To: wstotts@worldnet.att.net
Cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Sequences : getting back the nextval()
result on an insertOn Thu, 11 Jun 1998, Wim Ceulemans wrote:
Hi
I am new to this list, but I've carefully searched the archives
before posting this question.
I am using JDBC to access postgreSQL 6.3.
If one inserts records using a sequence to generate an
auto-increment primary key, then you can get back the result by using
"select currval('seq')". There is always a time between the insert and
the select to retrieve the newly created primary key. Wh
at I do in my application is insert a record and display it in a java
swing JTable (including the primary key).How can I be certain that the primary key I retrieve with select
currval('seq') is the one just inserted? Because in heavily loaded
multi-user systems someone else can insert a record in the same table
between my insert and my select.Actually, as each user spawns a new copy of the backend u can be sure
that
currval() gives correct answer as it uses it's backend information.
I.e
nextval() and curval() called sequently from the same
process/application
will refer to their backend.If u look at the create secuence schema dump (pg_dump -s XXX) u will
see
the CACHE parameter which AFAIK referes to the pool of sequence
numbers
alocated per backend.Hope this helps...
Marin
-= Why do we need gates in a world without fences? =-
Import Notes
Resolved by subject fallback