Re: Guarenteeing complex referencial integrity throughcustom triggers - Mailing list pgsql-hackers

>-----Original Message-----
>From: Hannu Krosing [mailto:hannu@skype.net]
>Sent: dinsdag 27 maart 2007 15:45
>To: Joris Dobbelsteen
>Cc: pgsql-hackers@postgreSQL.org
>Subject: Re: [HACKERS] Guarenteeing complex referencial
>integrity throughcustom triggers
>
>Ühel kenal päeval, E, 2007-03-26 kell 16:05, kirjutas Joris
>Dobbelsteen:
>
>> Oracle has choosen to allow constraint enforcement by locking on the
>> parent tuple. In contrast postgres has chosen (historically, see RI
>> triggers) to fail on detecting conflicting newly inserted rows (the
>> cross-check).
>
>Could you give an example, where postgresql fails to detect
>conflicting newly inserted rows ?

I'm re-using Greg Sabino Mullane example, so here goes the first part

- -- Generic type of printer
CREATE TABLE printer ( id    SERIAL NOT NULL PRIMARY KEY, brand TEXT   NOT NULL, model TEXT   NOT NULL
);
INSERT INTO printer (brand,model) VALUES ('epson','1200');
INSERT INTO printer (brand,model) VALUES ('hp','laserjet99');

- -- A printer can hold one or more cartridges, distinguished by 'color'
CREATE TABLE cartridge_types ( ptype INT  NOT NULL,   CONSTRAINT cc_ptype FOREIGN KEY (ptype) REFERENCES printer(id) ON
DELETERESTRICT, color TEXT NOT NULL 
);
CREATE UNIQUE INDEX ctype ON cartridge_types(ptype,color);
INSERT INTO cartridge_types VALUES (1,'black');
INSERT INTO cartridge_types VALUES (2,'black');
INSERT INTO cartridge_types VALUES (2,'color');

- -- Specific printers in the company
CREATE TABLE printers ( id        SERIAL  NOT NULL PRIMARY KEY, ptype     INTEGER NOT NULL,   CONSTRAINT ptype FOREIGN
KEY(ptype) REFERENCES printer(id), location  TEXT    NOT NULL 
);
INSERT INTO printers(ptype,location) VALUES (1,'Room 234');
INSERT INTO printers(ptype,location) VALUES (2,'Break room #2');
INSERT INTO printers(ptype,location) VALUES (2,'NE corner of warehouse');

- -- Printers require lots of pesky hardware updates
CREATE TABLE cartridge_change ( printer_id INT  NOT NULL,   CONSTRAINT change_printer FOREIGN KEY (printer_id)
REFERENCESprinters(id), color      TEXT NOT NULL, whenchanged TIMESTAMPTZ NOT NULL DEFAULT now() ); 
ALTER TABLE cartridge_change ADD CONSTRAINT cc FOREIGN KEY (printer_id, color) REFERENCES cartridge_types
(ptype,color);

INSERT INTO cartridge_change (printer_id, color) VALUES (1,'black');

------------------------

As a slight note: I'm not going to write out all the triggers that should normally be needed on the cartidge_change,
printers,printer and cartridge_type tables to normally enforce the following constraint: 

FOR ALL t IN cartridge_change
THERE MUST EXIST (SELECT 1                 FROM printers                 INNER JOIN cartridge_types ON
cartridge_types.ptype= printers.ptype                 WHERE printers.id = cartridge_change.printer_id        AND
cartridge_types.color= cartridge_change.color                 ) 

Obviously we follow good practices:
Before inserting a row into cartridge_change, we should check the cartridge_types table and lock the row.
After deleting a row in cartridge_types, we should check the cartridge_change table for constraint violation.
We will first lock the parent and then the childs. This gives consistency and reduces the chance of deadlocks.

As you should note, this cannot be done with a referencial constraint (unless you are really willing to materialize a
quitelarge view "SELECT p.id, c.color FROM printers p INNER JOIN cartridge_types c ON p.ptype = c.ptype"). In these
casesI'm even not sure that the constraint can be enforced (need to think this over carefully). 

------------------------

For the the actions:
We will happen to do the following at the same time: Change the color toner on printer #2 (break room), and remove the
definitionof the "color" toner from the "hp laserjet99". Trigger actions are in implicit... 
The T1 and T2 prefixes will denote the executing transactions. Statements might wait for locks, in which case you
shouldcontinue with the next ones (unless its for the same transaction, in which case I did it wrong). 

T1: BEGIN ISOLATION LEVEL SERIALIZABLE;
T2: BEGIN ISOLATION LEVEL SERIALIZABLE;

-- T1 will insert into cartridge_change, but first the trigger
-- it will lock the parent (it should return exactly a single row).
T1: SELECT 1 FROM printers p INNER JOIN cartridge_change c ON p.ptype = c.ptype WHERE p.printer_id = 2 AND c.color =
'color'FOR SHARE; 
-- Now the actual function
T1: INSERT INTO cartridge_change (printer_id, color) VALUES (2,'color');

-- T2 will now delete the 'color' cartridge definition for hp laserjet99.
-- Trigger doing a check, that dependencies do not exists...
-- First delete statement will wait for the lock of T1...
T2: DELETE FROM cartridge_change WHERE ptype=2 AND color='color';

-- T1 commits in the meanwhile
T1: COMMIT

-- T2 is now out of the lock, so execute trigger
T2: SELECT 1   FROM cartridge_change c   INNER JOIN printers p ON p.id = c.printer_id   WHERE c.color = 'color'   AND
p.ptype= 2; -- Could also be delete... 

-- Please notice we didn't find any rows.
-- It works under read commited nevertheless...

T2: COMMIT;

-----------------------------

At this point my constraint is violated...

Another go would be to instead of the SELECT FOR SHARE we had executed an UPDATE, but this is really ugly, hidious,
counter-intuitiveand concurrency reducing. 

- Joris