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

Re: one or 2 transactions?

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: one or 2 transactions?
Date: 2009-12-10 19:59:48
Message-ID: 4B215334.1060005@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?

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

Responses

pgsql-novice by date

Next:From: Mladen GogalaDate: 2009-12-10 21:39:34
Subject: How to describe functions.
Previous:From: Andreas KretschmerDate: 2009-12-10 19:34:26
Subject: Re: one or 2 transactions?

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