Re: Transaction Newbie

> 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