From: Michelle Murrain <tech@murrain.net>
To: pgsql-sql@postgresql.org
Subject: Transaction Newbie
Date: Mon, 9 Sep 2002 22:37:54 -0400
Message-ID: <a05111b08b9a30834d223@[192.168.1.20]> (raw)
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."
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tech@murrain.net
Subject: Re: Transaction Newbie
In-Reply-To: <a05111b08b9a30834d223@[192.168.1.20]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox