Re: Table inheritance, unique constraints and foreign key problem

"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!