Re: Minor inheritance/check bug: Inconsistent behavior

On Fri, Aug 24, 2012 at 3:52 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> From: pgsql-bugs-owner(at)postgresql(dot)org
> [mailto:pgsql-bugs-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> Sent: Friday, August 24, 2012 10:33 AM
> Chris Travers <chris(at)metatrontech(dot)com> writes:
>>> My guess is that tableoid is not known when the check constraint is
>>> checked.
>
>>None of the system columns are set at the time check constraints are
>>checked.
>
> Is there any problem if set tableOID before calling ExecConstarints()?
> I am not sure may be this is not so important problem for which we don't
> want to
> change existing code.

Just to be sure my initial concern is this:

Table inheritance would be easier if there was a way to declare a
constraint such that it prevents insert for some rows on the parent
but not for a child and/or vice versa. This can be used to partition
the primary key between a parent and child tables to avoid some key
overlap issues. My concern was that this was the "clever" solution
that someone would try, insufficiently test, and find out that their
clever solution in fact did nothing except preventing the constraint
from being dropped and later re-applied.

As for the ideal way of looking at addressing this possibility:

I am further not going to speak for the developers here but I do
wonder about system columns generally and check constraints, and
whether the same solution is just to check the check constraint and
error if a system column is checked. Some things seem obvious but
what happens if someone says "this table cannot grow beyind 5 pages
and we will do this by checking against ctid"? If we start pulling
out some system columns for special treatment I am not sure where it
ends. I am assuming that ctid cannot be safely known before the row
is formally stored on disk.

I think the cleanest fix interface-wise is to prevent check
constraints from being added to tables in this case. I don't see it
as a high priority though.

My larger priority is to flag this as a possible thing someone is
likely to try to get around the issues storing rows in both parent and
child tables.

Best Wishes,
Chris Travers