Thread: Backupping the table values
I am allowing my users to delete data from certain tables. However, to be able to do a rollback of user changes, I decided to create another "backup" schema that most data tables will copy data to, upon delete. 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? -- ICQ: 1912453 AIM: VitalyB1984 MSN: tmdagent@hotmail.com Yahoo!: VitalyBe
On Sat, 26 Feb 2005 16:47:38 +0200, Vitaly Belman <vitalyb@gmail.com> wrote: > I am allowing my users to delete data from certain tables. However, to > be able to do a rollback of user changes, I decided to create another > "backup" schema that most data tables will copy data to, upon delete. > > 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. If you want to a DELETE on table B in public to cause the dependant (child) rows on public A to be removed you can make the foreign key cascade with "ON DELETE CASCADE". This will guard against orphaned rows in B. If both A and B have the ON DELETE trigger a DELETE on A will cause the correct DELETE on B, and the insertion order in the backup schema should be correct. That is, if you really need the foreign keys on the backup schema at all. If the backup is only written to by triggers the foreign keys may be a waste. -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org
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.essig@gmail.com> wrote: > On Sat, 26 Feb 2005 16:47:38 +0200, Vitaly Belman <vitalyb@gmail.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@hotmail.com Yahoo!: VitalyBe
On Tue, 1 Mar 2005 17:48:44 +0200, Vitaly Belman <vitalyb@gmail.com> wrote: > -------------------------------------------------------------------------------- > 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; > > Remove 'ON UPDATE RESTRICT ON DELETE RESTRICT' from your create table statement. The manual says the following about RESTRICT: "Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable." George Essig