Re: 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: 550 5.0.0 Go away, spammer (in reply to MAIL FROM command)>
[//]

>-----Original Message-----
>From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>Sent: maandag 26 maart 2007 19:52
>To: Joris Dobbelsteen
>Cc: pgsql-hackers(at)postgresql(dot)org
>Subject: Re: [HACKERS] Guarenteeing complex referencial
>integrity through custom triggers
>
>"Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)nl> 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