>-----Original Message-----
>From: Hannu Krosing [mailto:[EMAIL PROTECTED]
>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 DELETE
RESTRICT,
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) REFERENCES printers(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 quite large view "SELECT p.id, c.color
FROM printers p INNER JOIN cartridge_types c ON p.ptype = c.ptype"). In these
cases I'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 definition of 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 should continue 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-intuitive and concurrency
reducing.
- Joris
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
