Re: one or 2 transactions?

Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com> wrote:

> Hi list,
>
> I've got tables: account & client, creating a client must automatically
> create the corresponding account that'll be a foreign key into client.
>
> AFAI read, I must DEFERRABLE INITIALLY DEFERRED the foreign key constraint
> into client.
>
> But can I do all this into only one transaction (writing account's row
> before client's), or am I obliged to have 2 distinct transactions?

One single transaction, first create the account and then the client, as
you said. For instance (i don't know your tables):

test=# create table account (id serial primary key, name text);
NOTICE: CREATE TABLE will create implicit sequence "account_id_seq" for serial column "account.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "account_pkey" for table "account"
CREATE TABLE
Zeit: 289,478 ms
test=*# create table client (id int references account, name text);
CREATE TABLE
Zeit: 41,802 ms
test=*# insert into account values (default, 'account1');
INSERT 0 1
Zeit: 1,014 ms
test=*# insert into client values (currval('account_id_seq'), 'client1');
INSERT 0 1
Zeit: 10,208 ms
test=*# commit;
COMMIT
Zeit: 0,447 ms

That's all a single transaction, including the DDL-statements (create table).

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°