Problems with transactions and sequences

[Sune Nielsen]

Hello All!

Im suffering an appearantly common problem with psql, although I
haven't been able to locate the proper solution.
Using phpPgAdmin I've created a relation (note: this relation is
grossly simplified but the problem remains the same) :

CREATE TABLE Users
( bid SERIAL,
name CHAR(8) NOT NULL,
PRIMARY KEY (bid)
);

Now, the problem is that I wish to extract the sequence number from a
newly inserted user, like this:

INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');

This works perfectly(!), but my project involves multiple simultanous
users so I have to use transactions like this:

BEGIN;
INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');
COMMIT;

This, however, doesn't work although I thought it should. It doesn't
return anything at all?!?! Could somebody explain me what I'm doing
wrong, or tell my how to do it instead?

Any help would be greatly appreciated!
--
Sune Nielsen su****@image.dk
Student at
Dept. of Computer Science
Copenhagen University
http://www.diku.dk

[Tom Lane]

Sune Nielsen <su****@image.dk> writes:

Now, the problem is that I wish to extract the sequence number from a
newly inserted user, like this: INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq'); This works perfectly(!), but my project involves multiple simultanous
users so I have to use transactions like this: BEGIN;
INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');
COMMIT;
No, you don't need the BEGIN/COMMIT. Read the description of currval
again.
This, however, doesn't work although I thought it should. It doesn't
return anything at all?!?!

Surely it does. You didn't say exactly what client-side code you are
using, but I suspect it's looking at the result of the COMMIT step
(ie, nothing) instead of the result of the prior SELECT.

regards, tom lane

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

http://archives.postgresql.org

[Sune Nielsen]

On Thu, 28 Aug 2003 10:51:21 -0400, in
comp.databases.postgresql.general you wrote:

Sune Nielsen <su****@image.dk> writes:

Now, the problem is that I wish to extract the sequence number from a
newly inserted user, like this:

INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');

This works perfectly(!), but my project involves multiple simultanous
users so I have to use transactions like this:

BEGIN;
INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');
COMMIT;

No, you don't need the BEGIN/COMMIT. Read the description of currval
again.

Okay, I think I get it. Because of the current connection-session I'm
safe?

This, however, doesn't work although I thought it should. It doesn't
return anything at all?!?!

Surely it does. You didn't say exactly what client-side code you are
using, but I suspect it's looking at the result of the COMMIT step
(ie, nothing) instead of the result of the prior SELECT.

Sorry about that. I was querying using SQL through phpPgAdmin's
SQL-interface although I was merely prototyping for some PHP-code I
couldn't get to work. But for the reference, how do I obtain the
results from an SELECT-statement if psql only returns the value of
COMMIT?

Thanks for you very speedy reply!
--
Sune Nielsen su****@image.dk
Student at
Dept. of Computer Science
Copenhagen University
http://www.diku.dk

[Sune Nielsen]

On Thu, 28 Aug 2003 20:24:43 +0530,
sh*****************@persistent.co.in ("Shridhar Daithankar") wrote:

On 28 Aug 2003 at 14:35, Sune Nielsen wrote:

INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');

This works perfectly(!), but my project involves multiple simultanous
users so I have to use transactions like this:

BEGIN;
INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');
COMMIT;

You need to extract the current val first and explicitly use it in insert
statement. That way you won't have to produce work-arounds later..

As I could read from the reply from Tom Lane, it seemed that I only
needed to to remove the BEGIN and COMMIT in order to make it work.
What problems do you foresee if don't perform the queries in opposite
order? I'm kind of new to this stuff :-)

Thanks for your fast reply!
--
Sune Nielsen su****@image.dk
Student at
Dept. of Computer Science
Copenhagen University
http://www.diku.dk

[Lanette Miller]

> You need to extract the current val first and explicitly use it in insert

statement. That way you won't have to produce work-arounds later.. This statement is wrong. If you were going to use this method (
getting the value first - then setting it), I believe you would need
to call nextval not currval.

Lanette

sh*****************@persistent.co.in ("Shridhar Daithankar") wrote in message news:<3F4E650B.3698.795CE8@localhost>... On 28 Aug 2003 at 14:35, Sune Nielsen wrote:

INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');

This works perfectly(!), but my project involves multiple simultanous
users so I have to use transactions like this:

BEGIN;
INSERT INTO Users (name) VALUES ('JohnDoe');
SELECT CURRVAL('users_bid_seq');
COMMIT;

Bye
Shridhar