Thread: transactions, serial ids, and JDBC
I've come to the point where I really need to run a transaction. In the past it hasn't been as crucial, so I've been happy with individual queries, but I am now past that point. I am now trying to insert a row into three separate tables, and the rows refer to each other. Two of them have SERIAL ids which need to be used as foreign keys. Here's a trimmed down version of the tables: CREATE TABLE A ( id SERIAL not null, somedata int not null, primary key (id) ); CREATE TABLE B ( id SERIAL not null, moredata int not null, a_id integer not null REFERENCES A(id), primary key (id) ); CREATE TABLE C ( b_id integer not null REFERENCES B(id), yetmoredata int not null, primary key (b_id) ); The transaction needs to look something like this: BEGIN INSERT INTO A (somedata) VALUES (1); INSERT INTO B (moredata, a_id) VALUES (1, <id from last insert>); INSERT INTO C (yetmoredata, b_id) VALUES (1, <id from last insert>); END I don't know how to dependably get the id from the last insert. One possibility, I suppose, is to call nextval myself and use the value explicitly, but if there is a way to do it portably (i.e. not depending on PostgreSQL's specific implementation of a self-incrementing id field) I would prefer it. Oh, one more thing. I'm doing this from JDBC. Can I do transactions with a long text string with all of this, or do I need to send each line (including BEGIN and END) as a separate Statement? Or is there some better way? --Greg
Gregory Seidman <gss+pg@cs.brown.edu> writes: > I don't know how to dependably get the id from the last insert. One > possibility, I suppose, is to call nextval myself and use the value > explicitly, but if there is a way to do it portably (i.e. not depending on > PostgreSQL's specific implementation of a self-incrementing id field) I > would prefer it. Use currval() to get the last ID produced by a sequence. AFAIK most databases implement a concept similar to sequences, but it's not standardized -- i.e. it will be difficult or impossible to use the same technique with different database systems. > Oh, one more thing. I'm doing this from JDBC. Can I do transactions with a > long text string with all of this, or do I need to send each line > (including BEGIN and END) as a separate Statement? Either way will work. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
On second thought, is there any reason not to put the whole transaction into a function? Will it still act as a transaction? And do I have to use plpgsql or is there a way to store a variable (i.e. the ids I need) using straight SQL? --Greg Gregory Seidman sez: } I've come to the point where I really need to run a transaction. In the } past it hasn't been as crucial, so I've been happy with individual queries, } but I am now past that point. I am now trying to insert a row into three } separate tables, and the rows refer to each other. Two of them have SERIAL } ids which need to be used as foreign keys. Here's a trimmed down version of } the tables: } } CREATE TABLE A ( } id SERIAL not null, } somedata int not null, } primary key (id) } ); } CREATE TABLE B ( } id SERIAL not null, } moredata int not null, } a_id integer not null REFERENCES A(id), } primary key (id) } ); } CREATE TABLE C ( } b_id integer not null REFERENCES B(id), } yetmoredata int not null, } primary key (b_id) } ); } } The transaction needs to look something like this: } } BEGIN } } INSERT INTO A (somedata) VALUES (1); } INSERT INTO B (moredata, a_id) VALUES (1, <id from last insert>); } INSERT INTO C (yetmoredata, b_id) VALUES (1, <id from last insert>); } } END } } I don't know how to dependably get the id from the last insert. One } possibility, I suppose, is to call nextval myself and use the value } explicitly, but if there is a way to do it portably (i.e. not depending on } PostgreSQL's specific implementation of a self-incrementing id field) I } would prefer it. } } Oh, one more thing. I'm doing this from JDBC. Can I do transactions with a } long text string with all of this, or do I need to send each line } (including BEGIN and END) as a separate Statement? Or is there some better } way? } } --Greg } } } ---------------------------(end of broadcast)--------------------------- } TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Will there every be something akin to Oracle's INSERT ... RETURNING <fieldname>. In all our Oracle apps we used Triggers on our pk cols to populate with the next sequence value. The app code ( java, php, pl/sql, whatever ) would use the INSERT ... RETURNING syntax, let the Trigger and sequence handle the id and assign the returned value to a local variable, which could then be referenced as needed to UPDATE, DELETE, SELECT, whatever, the target record by pk. Paul -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Neil Conway Sent: Wednesday, August 07, 2002 15:46 To: gss+pg@cs.brown.edu Cc: PostgreSQL general mailing list Subject: Re: [GENERAL] transactions, serial ids, and JDBC Gregory Seidman <gss+pg@cs.brown.edu> writes: > I don't know how to dependably get the id from the last insert. One > possibility, I suppose, is to call nextval myself and use the value > explicitly, but if there is a way to do it portably (i.e. not depending on > PostgreSQL's specific implementation of a self-incrementing id field) I > would prefer it. Use currval() to get the last ID produced by a sequence. AFAIK most databases implement a concept similar to sequences, but it's not standardized -- i.e. it will be difficult or impossible to use the same technique with different database systems. > Oh, one more thing. I'm doing this from JDBC. Can I do transactions with a > long text string with all of this, or do I need to send each line > (including BEGIN and END) as a separate Statement? Either way will work. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Gregory Seidman wrote: > On second thought, is there any reason not to put the whole transaction > into a function? Will it still act as a transaction? And do I have to use > plpgsql or is there a way to store a variable (i.e. the ids I need) using > straight SQL? A function will always be executed within transaction context. You don't actually need any variables for this: create function abc(int,int,int) returns int as ' insert into a (somedata) values ($1); insert into b (moredata, a_id) values ($2, currval(''a_id_seq'')); insert into c (yetmoredata, b_id) values ($3, currval(''b_id_seq'')); select currval(''a_id_seq'')::int; ' language sql; If your real tables have more fields, you may hit the limit on the number of parameters allowed in a function call - search the archives to see how you can change that limit if you need to. -- Peter Gibbs EmKel Systems
On 8/8/02 1:56 AM, "Peter Gibbs" <peter@emkel.co.za> wrote: > Gregory Seidman wrote: > >> On second thought, is there any reason not to put the whole transaction >> into a function? Will it still act as a transaction? And do I have to use >> plpgsql or is there a way to store a variable (i.e. the ids I need) using >> straight SQL? > > A function will always be executed within transaction context. You don't > actually need any variables for this: > > create function abc(int,int,int) returns int as ' > insert into a (somedata) values ($1); > insert into b (moredata, a_id) values ($2, currval(''a_id_seq'')); > insert into c (yetmoredata, b_id) values ($3, currval(''b_id_seq'')); > select currval(''a_id_seq'')::int; > ' language sql; > I have a function that needs to do several things and roll it all back if any element fails. I wrote it like this: CREATE FUNCTION transfer_student(integer,,character,,character,,character,,integer) RETURNS int4 AS ' BEGIN; UPDATE iep_student SET id_county = $2, id_district = $3, id_school = $4, id_case_mgr = 0, id_list_team='' WHERE id_student = $1; UPDATE iep_student_team SET status='Inactive' WHERE id_student = $1; UPDATE iep_transfer_request SET transfer_type='Confirmed' where id_transfer_request = $5; COMMIT; SELECT id_student from iep_student where id_student = $1; ' LANGUAGE 'sql'; I believe I read elsewhere that transactional logic doesn't work or doesn't apply in a function. And the message above seems to imply that the function will behave transactionally without explicit begin/commit. So what do I need to do, or not do, in order that the function executes the way I intend, that all three UPDATES will succeed or fail? Thanks, Steve
Hello all: I thought I had understood that the AS keyword for column aliasing was optional in SQL. Yet it appears that, at least some of the time, it's necessary in Postgresql. Have I misunderstood the standard, or does postgres deliberately diverge? Thanks, steve
Steve Lane wrote: > Hello all: > > I thought I had understood that the AS keyword for column aliasing was > optional in SQL. Yet it appears that, at least some of the time, it's > necessary in Postgresql. Have I misunderstood the standard, or does postgres > deliberately diverge? PostgreSQL intentionally diverges. See (near the bottom - SQL92 heading): http://www.postgresql.org/idocs/index.php?sql-select.html Joe
Re: Transactions in functions ( was Re: transactions, serial ids, and JDBC)
From
Martijn van Oosterhout
Date:
On Fri, Aug 09, 2002 at 08:48:34PM -0500, Steve Lane wrote: > I believe I read elsewhere that transactional logic doesn't work or doesn't > apply in a function. And the message above seems to imply that the function > will behave transactionally without explicit begin/commit. I think you mean that you cannot start new transactions within a function.; This is true as postgres does not support nested transactions. However, functions are always within a transaction as they always appear as part of a statement at the top level. > So what do I need to do, or not do, in order that the function executes the > way I intend, that all three UPDATES will succeed or fail? Nothing. It'll work that way anyway. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.