From: | Vitaly Belman <vitalyb(at)gmail(dot)com> |
---|---|
To: | George Essig <george(dot)essig(at)gmail(dot)com> |
Cc: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Backupping the table values |
Date: | 2005-03-01 15:48:44 |
Message-ID: | fa96e3c6050301074815cff2d5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I tried looking into your solution.. However, the "DEFERRABLE
INITIALLY DEFERRED" doesn't seem to act as I expect it to. I made two
sample tables:
--------------------------------------------------------------------------------
CREATE TABLE functions.temp1
(
id1 int4 NOT NULL,
id2 int4,
CONSTRAINT pk_temp1 PRIMARY KEY (id1),
CONSTRAINT temp2_id2 FOREIGN KEY (id2) REFERENCES functions.temp2
(id2) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY
DEFERRED
)
WITHOUT OIDS;
CREATE TABLE functions.temp2
(
id2 int4 NOT NULL,
CONSTRAINT temp2_pk PRIMARY KEY (id2),
CONSTRAINT temp2_id2_key UNIQUE (id2)
)
WITHOUT OIDS;
--------------------------------------------------------------------------------
Then I tried to run the following SQL:
begin;
delete from temp2;
delete from temp1;
end;
Based on what the documentation says, this transaction should've
worked, but instead all I get is:
ERROR: update or delete on "temp2" violates foreign key constraint
"temp2_id2" on "temp1"
DETAIL: Key (id2)=(1) is still referenced from table "temp1".
Have I done anything wrong?
On Sat, 26 Feb 2005 19:56:32 -0600, George Essig <george(dot)essig(at)gmail(dot)com> wrote:
> On Sat, 26 Feb 2005 16:47:38 +0200, Vitaly Belman <vitalyb(at)gmail(dot)com> wrote:
> > So basically what I have is:
> >
> > public schema, in which there are two tables, A and B.
> > backup schema, in which there are two tables, A and B.
> >
> > On table A and B in public I add a trigger "On Delete" which inserts
> > the deleted data to the matching tables in the backup scehma.
> >
> > That'd work fine except the foreign keys problem. In A I have a
> > column, "B_id" that is a foreign key to an "id" in the B table. Thus
> > it means that I have to delete from A before I delete from B.
> >
> > Inserting into the backup folders, on the other hand, should be
> > reversed, from the same reasons.
> >
> > Anyone has an idea how to solve this?
> >
>
> Maybe a DEFERRABLE INITIALLY DEFERRED foreign key would work. This
> will cause the foreign key constraint to be checked at the end of the
> transaction. See the manual at:
>
> http://www.postgresql.org/docs/8.0/static/sql-createtable.html
>
> George Essig
>
--
ICQ: 1912453
AIM: VitalyB1984
MSN: tmdagent(at)hotmail(dot)com
Yahoo!: VitalyBe
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2005-03-01 15:55:07 | Re: Field count in a RECORD variable - plpgsql |
Previous Message | Mike Preston | 2005-03-01 15:31:05 | Field count in a RECORD variable - plpgsql |