Thread: Problems with transactions and sequences
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 sunegn@image.dk
Student at
Dept. of Computer Science
Copenhagen University
http://www.diku.dk
Sune Nielsen <sunegn@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
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..
Bye
Shridhar
--
Vulcans do not approve of violence. -- Spock, "Journey to Babel", stardate
3842.4
if one gives a value for the seirial field, the trigger for the serial
on that table doesn't generate another value?
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..
>
>Bye
> Shridhar
>
>--
>Vulcans do not approve of violence. -- Spock, "Journey to Babel", stardate
>3842.4
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
On 28 Aug 2003 at 8:22, Dennis Gearon wrote: > if one gives a value for the seirial field, the trigger for the serial > on that table doesn't generate another value? If you are going to get sequence value first and insert it later, you can just declare it int8 rather than serial..:-) Would that solve problem? Actually I have no idea if you can insert specific value in a serial field. I wouldn't be surprised if it works as intended but don't have a postgresql installation handy here. (Working in windows right now..) Bye Shridhar -- Immutability, Three Rules of: (1) If a tarpaulin can flap, it will. (2) If a small boy can get dirty, he will. (3) If a teenager can go out, he will.
On Thu, 28 Aug 2003 10:51:21 -0400, in
comp.databases.postgresql.general you wrote:
>Sune Nielsen <sunegn@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 sunegn@image.dk
Student at
Dept. of Computer Science
Copenhagen University
http://www.diku.dk
On Thu, 28 Aug 2003 20:24:43 +0530,
shridhar_daithankar@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 sunegn@image.dk
Student at
Dept. of Computer Science
Copenhagen University
http://www.diku.dk
> 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
shridhar_daithankar@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
On Thu, Aug 28, 2003 at 21:10:19 +0530, Shridhar Daithankar <shridhar_daithankar@persistent.co.in> wrote: > > Actually I have no idea if you can insert specific value in a serial field. I > wouldn't be surprised if it works as intended but don't have a postgresql > installation handy here. (Working in windows right now..) Yes you can. Serial types are implemented using a default that gets a value from a corresponding sequence. If you specify a value while inserting the default won't get used.
On Thu, Aug 28, 2003 at 08:22:47 -0700, Dennis Gearon <gearond@fireserve.net> wrote: > if one gives a value for the seirial field, the trigger for the serial > on that table doesn't generate another value? Serials are implemented using a default function, not a trigger.