Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of Greg
>Sabino Mullane
>Sent: woensdag 28 maart 2007 2:50
>To: pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] Guarenteeing complex referencial
>integrity through custom triggers
>
[snip]
>
>Much too elaborate - I'm sorry, but I don't think anyone here
>is willing to wade through nearly 900 lines of code. Can you
>break it down to a simpler test case? Or try and break the
>schema I provided in my previous message perhaps?
Was the only readily available example I had available at the moment in
very short notice.
-- Generic type of printer
CREATE TABLE printer (
idSERIAL 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,
PRIMARY KEY (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 (
idSERIAL NOT NULL PRIMARY KEY,
ptype INTEGER NOT NULL,
CONSTRAINT ptype FOREIGN KEY (ptype) REFERENCES printer(id),
location TEXTNOT 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()
);
--
-- !!!
-- NOTICE
--
-- This constraint is invalid, printer_id should reference printers, not
printer...
-- IF this constraint where valid, you could never change a cartridge on
printer #3...
--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');
-- dropped your last insert, was a constraint violation...
>> You cannot enforce the above constraint in the database. The keyword
>> is serializable isolation level.
>>
>> Create new printer + cartidge_defs and such.
>> Now in T1 delete a cartridge_def
>> Now in T2 insert a cartridge replacement.
>
>Works as expected in my schema when I tried it: T2 blocked at
>the insert, waiting on T1. Once T1 committed, T2 threw an
>error, as the insert was no longer valid. Using serializable
>or not, same result.
As noted above, you constraint does not enforce the constraint I
mentioned (though with loose grammer). It cannot use the primitives that
are in the postgresql database, but rather need triggers to have them
enforced.
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
)
If we replace a catridge, the cartridge_type should be defined.
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 (normally) and reduces the chance of deadlocks.
This means two constraints must be defined (assume you have plpgsql
available).
CREATE FUNCTION "TR_cartridgeRI_cartridge_change_insupd"() RETURNS
trigger AS
$BODY$BEGIN
-- This must be a BEFORE trigger: we lock the parent first...
PERFORM 1
FROM printers p INNER JOIN cartridge_types c ON p.ptype =
c.ptype
WHERE p.id = NEW.printer_id
AND c.color = NEW.color
FOR SHARE;
IF NOT FOUND THEN
RAISE EXCEPTION 'Cartridge_type not defined';
END IF;
RETURN NEW;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER "TR_cartridgeRI"
BEFORE INSERT OR UPDATE ON cartridge_change FOR EACH ROW
EXECUTE PROCEDURE "TR_RI_cartridgeRI_cartridge_change_insupd"();
CREATE FUNCTION "TR_cartridgeRI_cartridg
Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Included is a more eloborate example, which has some webby thing at > http://www.familiedobbelsteen.nl/printer-test/ (should work). Much too elaborate - I'm sorry, but I don't think anyone here is willing to wade through nearly 900 lines of code. Can you break it down to a simpler test case? Or try and break the schema I provided in my previous message perhaps? > You cannot enforce the above constraint in the database. The keyword is > serializable isolation level. > > Create new printer + cartidge_defs and such. > Now in T1 delete a cartridge_def > Now in T2 insert a cartridge replacement. Works as expected in my schema when I tried it: T2 blocked at the insert, waiting on T1. Once T1 committed, T2 threw an error, as the insert was no longer valid. Using serializable or not, same result. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200703272044 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFGCbqSvJuQZxSWSsgRA6SXAJ0Wehl9d5PGEsnyRk+FP8fNy21GzwCg06l4 DVog8s9FEXKQ5R6U2DDe/Pw= =n4Vl -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Guarenteeing complex referencial integrity through custom 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 ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers
[Resent: mailing list only] Tom, you mail server won't accept: The e-mail system was unable to deliver the message, but did not report a specific reason. Check the address and try again. If it still fails, contact your system administrator. < orange.nl #5.0.0 X-SMTP-Server; host sss.pgh.pa.us[66.207.139.130] said: 5505.0.0 Go away, spammer (in reply to MAIL FROM command)> [//] >-Original Message- >From: Tom Lane [mailto:[EMAIL PROTECTED] >Sent: maandag 26 maart 2007 19:52 >To: Joris Dobbelsteen >Cc: pgsql-hackers@postgresql.org >Subject: Re: [HACKERS] Guarenteeing complex referencial >integrity through custom triggers > >"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes: >> My intention is to expose the functionality to the outside world for >> general use. This provides means to ensure custom complex >constraints >> can be enforced properly. I hope to push it into 8.3 if possible. > >You are at least a month too late for 8.3, even if you had a >solid design now, which you clearly don't. Than its not possible, next try later on. I was messing up different dates it seemed. >Nor am I convinced >that we really want/need to support what you are talking about >at the SQL level. To me, the crosscheck stuff in the RI >support is an extremely dirty hack that might or might not be >100% correct. Exposing it to the SQL level, and thereby >committing to support it forever, seems the height of folly. Debatable... Yet I see several options: 1) Extend the approach taken for the current RI triggers (i.e. 'cross-check hack'). 2) Build some general framework for constraint enforcement. 3) Invent something new. [Few more that aren't really proposable] At this point: 1) At least Tom's not in favor and there is little commerical motivation to do it right. 2) This is extremely huge project and needs to build on a primitive, with the current only a 'dirty hack' available. Probably it extends the CHECK syntax currently supported, and this is extremely involved. 3) Falling short of the innovative/sparkling idea. The case is that at this point consistency within a single modified snapshot of the database, does not imply all possible views (snapshots) are consistent too. So we need to ensure both are consistent. Yet there is no single _supported_ way to make that work. Its falling short on its commercial competitors (and my view of an 'enterprise dbms' unfortunally). I'm fully open to other suggestions... - Joris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers
>-Original Message- >From: Tom Lane [mailto:[EMAIL PROTECTED] >Sent: maandag 26 maart 2007 19:52 >To: Joris Dobbelsteen >Cc: pgsql-hackers@postgresql.org >Subject: Re: [HACKERS] Guarenteeing complex referencial >integrity through custom triggers > >"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes: >> My intention is to expose the functionality to the outside world for >> general use. This provides means to ensure custom complex >constraints >> can be enforced properly. I hope to push it into 8.3 if possible. > >You are at least a month too late for 8.3, even if you had a >solid design now, which you clearly don't. Than its not possible, next try later on. I was messing up different dates it seemed. >Nor am I convinced >that we really want/need to support what you are talking about >at the SQL level. To me, the crosscheck stuff in the RI >support is an extremely dirty hack that might or might not be >100% correct. Exposing it to the SQL level, and thereby >committing to support it forever, seems the height of folly. Debatable... Yet I see several options: 1) Extend the approach taken for the current RI triggers (i.e. 'cross-check hack'). 2) Build some general framework for constraint enforcement. 3) Invent something new. [Few more that aren't really proposable] At this point: 1) At least Tom's not in favor and there is little commerical motivation to do it right. 2) This is extremely huge project and needs to build on a primitive, with the current only a 'dirty hack' available. Probably it extends the CHECK syntax currently supported, and this is extremely involved. 3) Falling short of the innovative/sparkling idea. The case is that at this point consistency within a single modified snapshot of the database, does not imply all possible views (snapshots) are consistent too. So we need to ensure both are consistent. Yet there is no single _supported_ way to make that work. Its falling short on its commercial competitors (and my view of an 'enterprise dbms' unfortunally). I'm fully open to other suggestions... - Joris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> As a real-world example where the constraint cannot be enforced in
> postgresql.
>
> "For every tuple t in cartridge_change, there must exists a tuple t' in
> printers with t.id = t'.id, and a tuple t'' in cartridge_types with
> t.color = t''.color and t'.printertype = t''.printertype"
While there may be unenforceable constraints, unless I am misreading your
example, I don't see a problem with enforcing this one. To restate
your problem, a company has a finite number of printers, and tracks when
a printer cartridge is changed. Each printer is of a certain type, and
each type has one or more types of cartridges that can go with it. Thus:
- -- Generic type of printer
CREATE TABLE printer (
idSERIAL 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 (
idSERIAL NOT NULL PRIMARY KEY,
ptype INTEGER NOT NULL,
CONSTRAINT ptype FOREIGN KEY (ptype) REFERENCES printer(id),
location TEXTNOT 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');
INSERT INTO cartridge_change (printer_id, color) VALUES (1,'blue');
- -- Session 1:
- -- BEGIN;
- -- INSERT INTO cartridge_change (printer_id, color) VALUES (2,'color');
- -- Session 2:
- -- BEGIN;
- -- DELETE FROM cartridge_types WHERE ptype=2 AND color = 'color';
- --
- -- Session 1:
- -- COMMIT;
- -- Session 2:
- -- ERROR
- -- Session 1:
- -- BEGIN;
- -- DELETE FROM cartridge_types WHERE ptype=2 AND color = 'color';
- -- Session 2:
- -- BEGIN;
- -- INSERT INTO cartridge_change (printer_id, color) VALUES (2,'color');
- --
- -- Session 1:
- -- COMMIT;
- -- Session 2:
- -- ERROR
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200703261429
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFGCBJ/vJuQZxSWSsgRA16BAJ4hkfcY4ui+yLUGWNerHZf0FvRbPACg++X5
e4tmrrJ1BFcxjM3PCXyKP6Y=
=CDAM
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Guarenteeing complex referencial integrity through custom triggers
"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes: > My intention is to expose the functionality to the outside world for > general use. This provides means to ensure custom complex constraints > can be enforced properly. I hope to push it into 8.3 if possible. You are at least a month too late for 8.3, even if you had a solid design now, which you clearly don't. Nor am I convinced that we really want/need to support what you are talking about at the SQL level. To me, the crosscheck stuff in the RI support is an extremely dirty hack that might or might not be 100% correct. Exposing it to the SQL level, and thereby committing to support it forever, seems the height of folly. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
