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

Re: one or 2 transactions?

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: one or 2 transactions?
Date: 2009-12-10 19:34:26
Message-ID: 20091210193426.GA12823@tux (view raw or flat)
Thread:
Lists: pgsql-novice
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°

In response to

Responses

pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2009-12-10 19:59:48
Subject: Re: one or 2 transactions?
Previous:From: Jean-Yves F. BarbierDate: 2009-12-10 19:07:38
Subject: one or 2 transactions?

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