Re: Arrays and foreign keys

Don Baccus wrote:
> At 10:57 AM 8/10/00 +1000, Chris Bitmead wrote:
> >Stephan Szabo wrote:
> >> > This is an interesting point. Originally postgres integrity rules were
> >> > based on a very general rules system where many things were possible to
> >> > specify. I'm curious about the more recent addition of referential
> >> > integrity to postgres (I know little about it), why it is such a
> >> > specific solution and is not based on the more general postgres rules
> >> > system?
> >>
> >> Because unfortunately the SQL spec for referential integrity cannot really
> >> be implemented in the current rules system (or at least not in a way that
> >> is terribly nice).
> >
> >So it wasn't feasible to extend the current rules system to support
> >these oddities, instead of implementing the specific solution?
>
> Since Jan apparently knows more about the current rules system than anyone
> else on the planet (he's done a lot of work in that area in the past), and
> since he designed the RI system, my guess is that the simple answer to your
> question is "yes".

"Yes"

Rules are fired before the original query is executed. This
is because otherwise a DELETE (for example) already stamped
it's XID and CID into the max fields of the tuples to delete
and the command counter gets incremented. So the rules scans
would never be able to find them again. From the visibility
point of view they are deleted.

To make rules deferrable in this visibility system, someone
would need to remember the command ID of the original query,
and when later executing the deferred queries modify all the
scan-command ID's of those rangetable-entries, coming from
the original query, to have the original queries CID, while
leaving the others at the current.

Theoretically possible up to here, but as soon as there are
any functions invoked in that query which use SPI, it's over.

Finally there is that problem about "triggered data change
violation". Since only "changing the effective value" of an
FK or PK is considered to be a "data change", each individual
tuple must be checked for it. This cannot be told on the
query level.

I'm sure it cannot be done with the rule system. Thus we
created this "specific solution".

And it is true that with the "very general rules system" of
the "original Postgres 4.2" many things where possible to
specify. But most of them never worked until v6.4. I know
definitely, because I found it out the hard way - fixing it.
And still, many things don't work.

Take some look at the short description of the rule system
internals in the programmers guide. After that, you maybe
come to the same conclusions as I did. Otherwise correct me
by reimplementing SQL3 RI with rules.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #