Skip site navigation (1) Skip section navigation (2)

Re: one or 2 transactions?

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: one or 2 transactions?
Date: 2009-12-11 06:10:09
Message-ID: 20091211061009.GA11321@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
In response to Jean-Yves F. Barbier :
> Andreas Kretschmer a écrit :
> > 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).
> 
> 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

In response to

Responses

pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2009-12-11 06:22:17
Subject: Re: one or 2 transactions?
Previous:From: Thomas KellererDate: 2009-12-10 22:03:12
Subject: Re: How to describe functions.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group