Re: one or 2 transactions? - Mailing list pgsql-novice

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