Thread: one or 2 transactions?

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?

JY
--
"Wish not to seem, but to be, the best."
        -- Aeschylus

Jean-Yves F. Barbier <12ukwn@gmail.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°

Andreas Kretschmer a écrit :
> Jean-Yves F. Barbier <12ukwn@gmail.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).

You mean I don't even need DEFERRABLE INITIALLY DEFERRED?

If so, could you explain the purpose of these orders, PLS?

JY
--
Sensible and responsible women do not want to vote.
        -- Grover Cleveland, 1905

In response to Jean-Yves F. Barbier :
> Andreas Kretschmer a écrit :
> > Jean-Yves F. Barbier <12ukwn@gmail.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).
>
> You mean I don't even need DEFERRABLE INITIALLY DEFERRED?

In this case, yes.


>
> If so, could you explain the purpose of these orders, PLS?

To enable circular references like example below:

-- first try without DEFERRABLE INITIALLY DEFERRED
-- ends with error
test=# create table t1 (id int primary key, ref int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
test=*# create table t2 (id int primary key, ref int references t1 );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
CREATE TABLE
test=*# alter table t1 add constraint ref_key foreign key (ref) references t2 ;
ALTER TABLE
test=*# insert into t1 values (1, 11);
ERROR:  insert or update on table "t1" violates foreign key constraint "ref_key"
DETAIL:  Key (ref)=(11) is not present in table "t2".
test=!# rollback;
ROLLBACK

-- now with DEFERRABLE INITIALLY DEFERRED
-- without error
test=# create table t1 (id int primary key, ref int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
test=*# create table t2 (id int primary key, ref int references t1 deferrable initially deferred);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
CREATE TABLE
test=*# alter table t1 add constraint ref_key foreign key (ref) references t2 deferrable initially deferred;
ALTER TABLE
test=*# insert into t1 values (1, 11);
INSERT 0 1
test=*# insert into t2 values (11, 1);
INSERT 0 1

-- the same with DEFERRABLE INITIALLY DEFERRED
-- but with wrong data and COMMIT -> Error
test=# create table t1 (id int primary key, ref int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
test=*# create table t2 (id int primary key, ref int references t1 deferrable initially deferred);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
CREATE TABLE
test=*# alter table t1 add constraint ref_key foreign key (ref) references t2 deferrable initially deferred;
ALTER TABLE
test=*# insert into t1 values (1, 11);
INSERT 0 1
test=*# insert into t1 values (3, 33);
INSERT 0 1
test=*# commit;
ERROR:  insert or update on table "t1" violates foreign key constraint "ref_key"
DETAIL:  Key (ref)=(11) is not present in table "t2".


HTH, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

A. Kretschmer a écrit :
...
>>> That's all a single transaction, including the DDL-statements (create table).
>> You mean I don't even need DEFERRABLE INITIALLY DEFERRED?
>
> In this case, yes.
>
>
>> If so, could you explain the purpose of these orders, PLS?
>
> To enable circular references like example below:
...
> HTH, Andreas

YES: now I understand very well the process, thanks Andreas!

--
Watch out for a cold wave this week.  (Or maybe a warm WAC.)