Table inheritance, unique constraints and foreign key problem

Hello,
this issue has been requested and its on the TODO-list. Since I really
need foreign key constraints on inherited tables, I have two solutions:
Adding some hackish RULES/TRIGGERS to my tables or implementing it
myself. It think the latter is better. However, I have no experience in
implementing such a feature in Postgres. I have written some triggers in
C (actually C++) using the SPI_-functions, but that's it. I have a
running 8.3beta, checked out from the repository, and I have read the
Ottawa slides. Can someone tell me in a few lines, where to start with
such a feature.
Jacob

"Jacob Rief" <jacob(dot)rief(at)gmx(dot)at> writes:

> this issue has been requested and its on the TODO-list. Since I really
> need foreign key constraints on inherited tables, I have two solutions:
> Adding some hackish RULES/TRIGGERS to my tables or implementing it
> myself. It think the latter is better. However, I have no experience in
> implementing such a feature in Postgres. I have written some triggers in
> C (actually C++) using the SPI_-functions, but that's it. I have a
> running 8.3beta, checked out from the repository, and I have read the
> Ottawa slides. Can someone tell me in a few lines, where to start with
> such a feature.

This is the tip of an iceberg. As you dig you find out it's caused by deeper
and deeper limitations until you're pretty much all of the executor.

The RI trigger code explicitly uses ONLY for the integrity checks. But if you
remove that you find it breaks because you get this message:

ERROR: SELECT FOR UPDATE/SHARE is not supported for inheritance queries

Look at src/backend/optimizer/path/allpaths.c:287 for a comment about this.

I'm a bit puzzled myself why this affects SELECT FOR UPDATE/SHARE but not
straight UPDATES and DELETES.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> I'm a bit puzzled myself why this affects SELECT FOR UPDATE/SHARE but not
> straight UPDATES and DELETES.

In straight UPDATE/DELETE we have enough structure in the query to know
how to associate each tuple returned to the executor top level with
exactly one tuple in exactly one target table (which is where to apply
the tuple lock operation). We don't have that much structure in general
SELECT --- for example, what to do with null-filled rows in a LEFT JOIN,
or cases where one row gives rise to more than one joined row, or
aggregation or UNION? Some of these cases can probably be rejected as
unsupportable, but it'll still take a lot of work.

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> I'm a bit puzzled myself why this affects SELECT FOR UPDATE/SHARE but not
>> straight UPDATES and DELETES.
>
> In straight UPDATE/DELETE we have enough structure in the query to know
> how to associate each tuple returned to the executor top level with
> exactly one tuple in exactly one target table (which is where to apply
> the tuple lock operation). We don't have that much structure in general
> SELECT --- for example, what to do with null-filled rows in a LEFT JOIN,
> or cases where one row gives rise to more than one joined row, or
> aggregation or UNION? Some of these cases can probably be rejected as
> unsupportable, but it'll still take a lot of work.

This seems like the same kind of work that would be required to support
queries like

UPDATE (SELECT a, t1.b AS src, t2.b AS dest
FROM t1 join t2 USING (a)
)
SET dest = src;

We currently support such plans using the FROM clause but handling arbitrary
queries (where they make sense) would be far more flexible. It would also let
us support updateable views in a much more flexible way than trying to reverse
engineer the view to generate rules. Instead the rules would be
straightforward substitutions just like the select rules:

UPDATE view SET ...

would just become:

UPDATE (view-definition) SET ...

And it would be up to the executor to determine whether which table the target
columns came from and whether they're updateable or the query should throw an
error.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!