Mime-Version: 1.0
Message-Id: <a05111b08b9a30834d223@[192.168.1.20]>
Date: Mon, 9 Sep 2002 22:37:54 -0400
To: pgsql-sql@postgresql.org
From: Michelle Murrain <tech@murrain.net>
Subject: Transaction Newbie
Content-Type: text/plain; charset="us-ascii" ; format="flowed"

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."