Thread

1. transactions, serial ids, and JDBC

   Gregory Seidman <gss+pg@cs.brown.edu> — 2002-08-07T22:41:22Z

   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
   
   
   

2. Re: transactions, serial ids, and JDBC

   Neil Conway <nconway@klamath.dyndns.org> — 2002-08-07T22:45:37Z

   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
   
   
   

3. Re: transactions, serial ids, and JDBC

   Gregory Seidman <gss+pg@cs.brown.edu> — 2002-08-07T23:02:31Z

   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
   
   

4. Re: transactions, serial ids, and JDBC

   Paul Ogden <pogden@claresco.com> — 2002-08-08T02:28:29Z

   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
   
   
   

5. Re: transactions, serial ids, and JDBC

   Peter Gibbs <peter@emkel.co.za> — 2002-08-08T06:56:50Z

   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
   
   
   
   

6. Transactions in functions ( was Re: transactions, serial ids, and JDBC)

   Steve Lane <slane@fmpro.com> — 2002-08-10T01:48:34Z

   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
   
   
   

7. AS keyword

   Steve Lane <slane@fmpro.com> — 2002-08-10T01:52:38Z

   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
   
   
   

8. Re: AS keyword

   Joe Conway <mail@joeconway.com> — 2002-08-10T02:02:07Z

   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
   
   
   
   
   

9. Re: Transactions in functions ( was Re: transactions, serial ids, and JDBC)

   Martijn van Oosterhout <kleptog@svana.org> — 2002-08-10T03:00:27Z

   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.