Обсуждение: 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

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.