Thread: Transaction Newbie

Hi,

I've been using Postgres for a while, almost exclusively through the 
perl DBI (although I do plenty of work on the command line).

I have realized, belatedly, that I need transactions for this thing I 
want to accomplish, but I've not done transactions before, so I need 
a bit of help. And, I'm not sure whether it's a transaction I need, 
or a lock.

I have (many) tables with automatically entering serial value as 
primary key, set by a sequence. I need to insert a row, and then get 
the value of that row I just entered. I thought first of doing two 
sql statements in a row:

if the primary key is table_id, with default value 
"nextval('table_seq') - then these two statements:

insert into table (field1,field2,field3) values (value1,value2,value3)
select currval('table_seq')

work to get me the value I need. Except, of course if someone else 
has inserted a row inbetween these two statements.

I tried a transaction test, and this is what I got:

pew=# begin work;
BEGIN
pew=# insert into categories values 
('23423423','test','testing','3','today','today','mpm','test 
category');
INSERT 83910 1
pew=# select currval('category_id');
NOTICE:  current transaction is aborted, queries ignored until end of 
transaction block
*ABORT STATE*
pew=# commit work
pew-# ;
COMMIT
pew=# select * from categories;

And the insert didn't happen.

Am I thinking about this right? Is there a better way to get the 
value of a newly inserted record?

Thanks!


PS: I'm subscribed to sql, odbc and general, and have not been 
getting general mail for quite some time. I've send emails to the 
address that's supposed to be read by humans, but gotten no response. 
If anyone is in a position to help me out - much appreciated!
-- 
.Michelle

--------------------------
Michelle Murrain, Technology Consulting
tech@murrain.net     http://www.murrain.net
413-253-2874 ph
413-222-6350 cell
413-825-0288 fax
AIM:pearlbear0 Y!:pearlbear9 ICQ:129250575

"A vocation is where the world's hunger & your great gladness meet."

> if the primary key is table_id, with default value
> "nextval('table_seq') - then these two statements:
>
> insert into table (field1,field2,field3) values (value1,value2,value3)
> select currval('table_seq')
>
> work to get me the value I need. Except, of course if someone else
> has inserted a row inbetween these two statements.

Hmmm - I'm not sure currval has that problem - have you actually tried it
with two psql windows?

> I tried a transaction test, and this is what I got:
>
> pew=# begin work;

You can just go 'begin;'

> BEGIN
> pew=# insert into categories values
> ('23423423','test','testing','3','today','today','mpm','test
> category');
> INSERT 83910 1
> pew=# select currval('category_id');
> NOTICE:  current transaction is aborted, queries ignored until end of
> transaction block
> *ABORT STATE*

As soon as you see this, it means you have made a syntax error or something
in your sql, which causes an automatic abort.

> pew=# commit work
> pew-# ;

You can't commit once the transaction is aborted, you need to ROLLBACK;

> COMMIT
> pew=# select * from categories;
>
> And the insert didn't happen.

It didn't happen because something caused the whole transaction to be
aborted.

> Am I thinking about this right? Is there a better way to get the
> value of a newly inserted record?

Chris

In article <a05111b08b9a30834d223@[192.168.1.20]>, Michelle Murrain wrote:
> Hi,
> 
> I've been using Postgres for a while, almost exclusively through the 
> perl DBI (although I do plenty of work on the command line).
> 
> I have realized, belatedly, that I need transactions for this thing I 
> want to accomplish, but I've not done transactions before, so I need 
> a bit of help. And, I'm not sure whether it's a transaction I need, 
> or a lock.
> 
> I have (many) tables with automatically entering serial value as 
> primary key, set by a sequence. I need to insert a row, and then get 
> the value of that row I just entered. I thought first of doing two 
> sql statements in a row:
> 

It sort of depends on what you mean by "get the value of that row".

If you just need the primary key value, then yes you can do:

insert into t (c1, c2, c3) values (v1, v2, v3);
select currval('sequence_name');

Or if what you really want is to use that primary key value to
insert more records in to other tables which relate to this one
(ie with a foreign key), then what you might want is:

-- pk here inserted automatically by DEFAULT nextval('sequence_name')
insert into t (c1, c2, c3) values (v1, v2, v3);

insert into t2 (fk, c4, c5) values (currval('sequence_name'), v4, v5);


As long as the two statements are in the same session (ie. in
the same connection instance) then there is no need for a transaction.

Sequences are set up to be used this way, currval() sees only values
retrieved by nextval() in *this session* it will not see anything
done in other connections to the database.