By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

Problems with transactions and sequences

P: n/a
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
Nov 11 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 11 '05 #2

P: n/a
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
Nov 11 '05 #3

P: n/a
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
Nov 11 '05 #4

P: n/a
> 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

Nov 11 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.