Check constraints on partition parents only?

On 07/25/2011 10:31 PM, Jerry Sievers wrote:

Hackers;

I just noticed that somewhere between 8.2 and 8.4, an exception is
raised trying to alter table ONLY some_partition_parent ADD CHECK
(foo).

I can understand why it makes sense to handle this as an error.

Howeverin practice on a few systems that I used to manage this would
be a problem.

1. I got into the habit of putting CHECK (false) on the parent table
if it was an always empty base table,

This is just really documentation indicating that this table can't
hold rows and of course, having the partition selector trigger
raise exception if falling through the if/else logic on a new row
insertion enforces the constraint but is less obvious.

Ok, so no real problem here. Just one example.

2. Atypical partitioning implementation where the parent table was for
initial insert/update of "live" records in an OLTP system with high
update/insert ratio. This table was partitioned retroactively in
such a way transparent to the application. The app would
eventually update a row one final time and set a status field to
some terminal status, at which time we'd fire a trigger to move the
row down into a partition. Record expiry took place periodically
by dropping a partition and creating a new one.

In that case, imagine the application user runs with
sql_inheritance to off and so, sees only the live data which
resulted in a huge performance boost. Reporting apps and in fact
all other users ran with sql_inheritance to on as usual and so, see
all the data.

Suppose the status field had several non-terminal values and one or
a few terminal values. The differing check constraints on parent
and child tables made it easy to see the intent and I presume with
constraint_exclusion set to on, let queries on behalf of regular
users that had specified a non-terminal state visit only the tiny
parent table.

Parent might have CHECK (status in (1,2,3)) and children CHECK
(status = 4).

I'll assume not many sites are architected this way but #2 here
shows a more compelling example of why it might be useful to allow
check constraints added to only a partition parent.

8.4 had this change:

*

Force child tables to inherit CHECK constraints from parents
(Alex Hunsaker, Nikhil Sontakke, Tom)

Formerly it was possible to drop such a constraint from a
child table, allowing rows that violate the constraint to be
visible when scanning the parent table. This was deemed
inconsistent, as well as contrary to SQL standard.

You're not the only one who occasionally bangs his head against it.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

On 07/25/2011 10:31 PM, Jerry Sievers wrote:

Hackers;

I just noticed that somewhere between 8.2 and 8.4, an exception is
raised trying to alter table ONLY some_partition_parent ADD CHECK
(foo).

8.4 had this change:

*

Force child tables to inherit CHECK constraints from parents
(Alex Hunsaker, Nikhil Sontakke, Tom)

Formerly it was possible to drop such a constraint from a
child table, allowing rows that violate the constraint to be
visible when scanning the parent table. This was deemed
inconsistent, as well as contrary to SQL standard.

You're not the only one who occasionally bangs his head against it.

cheers

andrew

Thanks Andrew!... Yeah, I figured it was a documented change but too
lazy tonight to browse release notes :-)

The previous behavior was to me convenient, but I agree, probably lead
to some confusion too.

That our version of partitioning can be overloaded like this though I
think adds power. A bit of which we lost adding the restrictgion.

--
Jerry Sievers
e: jerry.sievers@comcast.net
p: 305.321.1144

Excerpts from Andrew Dunstan's message of lun jul 25 22:44:32 -0400 2011:

On 07/25/2011 10:31 PM, Jerry Sievers wrote:

Hackers;

I just noticed that somewhere between 8.2 and 8.4, an exception is
raised trying to alter table ONLY some_partition_parent ADD CHECK
(foo).

8.4 had this change:

*
Force child tables to inherit CHECK constraints from parents
(Alex Hunsaker, Nikhil Sontakke, Tom)

You're not the only one who occasionally bangs his head against it.

Yeah. I think it's good that there's a barrier to blindly dropping a
constraint that may be important to have on children, but there should
be a way to override that.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

8.4 had this change:

*
Force child tables to inherit CHECK constraints from parents
(Alex Hunsaker, Nikhil Sontakke, Tom)

You're not the only one who occasionally bangs his head against it.

Sorry for the occasional head bumps :)

Yeah. I think it's good that there's a barrier to blindly dropping a
constraint that may be important to have on children, but there should
be a way to override that.

Hmmm, but then it does open up the possibility of naive users shooting
themselves in the foot. It can be easy to conjure up a
parent-only-constraint that does not gel too well with its children. And
that's precisely why this feature was added in the first place..

Regards,
Nikhils

On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke
<nikhil.sontakke@enterprisedb.com> wrote:

Yeah.  I think it's good that there's a barrier to blindly dropping a
constraint that may be important to have on children, but there should
be a way to override that.

Hmmm, but then it does open up the possibility of naive users shooting
themselves in the foot. It can be easy to conjure up a
parent-only-constraint that does not gel too well with its children. And
that's precisely why this feature was added in the first place..

Yeah, but I think we need to take that chance. At the very least, we
need to support the equivalent of a non-inherited CHECK (false) on
parent tables.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 07/26/2011 09:08 AM, Robert Haas wrote:

On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke
<nikhil.sontakke@enterprisedb.com> wrote:

Yeah. I think it's good that there's a barrier to blindly dropping a
constraint that may be important to have on children, but there should
be a way to override that.

Hmmm, but then it does open up the possibility of naive users shooting
themselves in the foot. It can be easy to conjure up a
parent-only-constraint that does not gel too well with its children. And
that's precisely why this feature was added in the first place..

Yeah, but I think we need to take that chance. At the very least, we
need to support the equivalent of a non-inherited CHECK (false) on
parent tables.

Indeed. I usually enforce that with a trigger that raises an exception,
but of course that doesn't help at all with constraint exclusion, and I
saw a result just a few weeks ago (I forget the exact details) where it
appeared that the plan chosen was significantly worse because the parent
table wasn't excluded, so there's a non-trivial downside from having
this restriction.

cheers

andrew

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke
<nikhil.sontakke@enterprisedb.com> wrote:

Hmmm, but then it does open up the possibility of naive users shooting
themselves in the foot. It can be easy to conjure up a
parent-only-constraint that does not gel too well with its children. And
that's precisely why this feature was added in the first place..

Yeah, but I think we need to take that chance. At the very least, we
need to support the equivalent of a non-inherited CHECK (false) on
parent tables.

No, the right solution is to invent an actual concept of partitioned
tables, not to keep adding ever-weirder frammishes to inheritance so
that it can continue to provide an awkward, poorly-performing emulation
of them.

regards, tom lane

On Tue, Jul 26, 2011 at 10:51:58AM -0400, Tom Lane wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke
<nikhil.sontakke@enterprisedb.com> wrote:

Hmmm, but then it does open up the possibility of naive users shooting
themselves in the foot. It can be easy to conjure up a
parent-only-constraint that does not gel too well with its children. And
that's precisely why this feature was added in the first place..

Yeah, but I think we need to take that chance. At the very least, we
need to support the equivalent of a non-inherited CHECK (false) on
parent tables.

No, the right solution is to invent an actual concept of partitioned
tables, not to keep adding ever-weirder frammishes to inheritance so
that it can continue to provide an awkward, poorly-performing emulation
of them.

Other SQL engines have partitions of types list, range and hash, and
some can sub-partition. I'm thinking it might be easiest to do the
first before adding layers of partition structure, although we should
probably bear in mind that such layers will eventually exist.

Does the wiki on this need updating?

http://wiki.postgresql.org/wiki/Table_partitioning

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Jul 25, 2011, at 9:59 PM, Jerry Sievers wrote:

That our version of partitioning can be overloaded like this though I
think adds power. A bit of which we lost adding the restrictgion.

That's why I'd be opposed to any partitioning scheme that removed the ability to have different fields in different children. We've found that ability to be very useful. Likewise, I think we need to have intelligent plans involving a parent table that's either completely empty or mostly empty.

As for dealing with inheritance and putting stuff on some children but not others, take a look at http://pgfoundry.org/projects/enova-tools/. There's a presentation there that discusses how we solved these issues and it includes the tools we created to do it. Note that we're close to releasing a cleaner version of that stuff, so if you decide to use it please ping me off-list if we haven't gotten the new stuff posted.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

Jim,

That's why I'd be opposed to any partitioning scheme that removed the ability to have different fields in different children. We've found that ability to be very useful. Likewise, I think we need to have intelligent plans involving a parent table that's either completely empty or mostly empty.

Well, I don't think that anyone is proposing making constraint exclusion
go away. However, we also need a new version of partitioning which
happens "below" the table level. I don't agree that the new
partitioning needs -- at least at the start -- the level of flexibility
which CE gives the user. In order to get simplicity, we have to
sacrifice flexibility.

In fact, I'd suggest extreme simplicity for the first version of this,
with just key partitioning. That is:

CREATE TABLE <table_name> (
... cols ... )
PARTITION ON <key_expression>
[ AUTOMATIC CREATE ];

... where <key_expression> can be any immutable expression on one or
more columns of some_table. This actually covers range partitioning as
well, provided that the ranges can be expressed as the results of an
expression (e.g. EXTRACT ('month' FROM date_processed ) ).

For the optional AUTOMATIC CREATE phrase, new values for key_expression
would result in the automatic creation of new partitions when they
appear (this has some potential deadlocking issues, so it's not ideal
for a lot of applications). Otherwise, you'd create partitions manually:

CREATE PARTITION ON <table_name> KEY <key_value>;
DROP PARTITION ON <table_name> KEY <key_value>;

... where <key_value> is some valid value which could result from
<key_expression>.

Yes, this is a very narrow and simplistic partitioning spec. However,
it would cover 80% of the use cases I see in the field or on IRC, while
being 80% simpler than CE. And CE would still be there for those who
need it.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Hi,

Yeah, but I think we need to take that chance. At the very least, we

need to support the equivalent of a non-inherited CHECK (false) on
parent tables.

Indeed. I usually enforce that with a trigger that raises an exception, but
of course that doesn't help at all with constraint exclusion, and I saw a
result just a few weeks ago (I forget the exact details) where it appeared
that the plan chosen was significantly worse because the parent table wasn't
excluded, so there's a non-trivial downside from having this restriction.

The downside appears to be non-trivial indeed! I cooked up the attached
patch to try to allow ALTER...ONLY...CHECK(false) on parent tables.

If this approach looks acceptable, I can provide a complete patch later with
some documentation changes (I think we ought to tell about this special case
in the documentation) and a minor test case along with it (if the need be
felt for the test case).

Although partitioning ought to be looked at from a different angle
completely, maybe this small patch can help out a bit in the current scheme
of things, although this is indeed a unusual special casing... Thoughts?

Regards,
Nikhils

On Wed, Jul 27, 2011 at 6:39 AM, Nikhil Sontakke
<nikhil.sontakke@enterprisedb.com> wrote:

Yeah, but I think we need to take that chance.  At the very least, we
need to support the equivalent of a non-inherited CHECK (false) on
parent tables.

Indeed. I usually enforce that with a trigger that raises an exception,
but of course that doesn't help at all with constraint exclusion, and I saw
a result just a few weeks ago (I forget the exact details) where it appeared
that the plan chosen was significantly worse because the parent table wasn't
excluded, so there's a  non-trivial downside from having this restriction.

The downside appears to be non-trivial indeed! I cooked up the attached
patch to try to allow ALTER...ONLY...CHECK(false) on parent tables.

If this approach looks acceptable, I can provide a complete patch later with
some documentation changes (I think we ought to tell about this special case
in the documentation) and a minor test case along with it (if the need be
felt for the test case).
Although partitioning ought to be looked at from a different angle
completely, maybe this small patch can help out a bit in the current scheme
of things, although this is indeed a unusual special casing... Thoughts?

Well, I don't have anything strongly against the idea of an
uninherited constraint, though it sounds like Tom does. But I think
allowing it just in the case of CHECK (false) would be pretty silly.
And, I'm fairly certain that this isn't going to play nice with
coninhcount... local constraints would have to be marked as local,
else the wrong things will happen later on when you drop them.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

Well, I don't have anything strongly against the idea of an
uninherited constraint, though it sounds like Tom does. But I think
allowing it just in the case of CHECK (false) would be pretty silly.
And, I'm fairly certain that this isn't going to play nice with
coninhcount... local constraints would have to be marked as local,
else the wrong things will happen later on when you drop them.

Yeah. If we're going to allow this then we should just have a concept
of a non-inherited constraint, full stop. This might just be a matter
of removing the error thrown in ATAddCheckConstraint, but I'd be worried
about whether pg_dump will handle the case correctly, what happens when
a new child is added later, etc etc.

regards, tom lane

On Jul 27, 2011, at 1:08 PM, Tom Lane wrote:

Yeah. If we're going to allow this then we should just have a concept
of a non-inherited constraint, full stop. This might just be a matter
of removing the error thrown in ATAddCheckConstraint, but I'd be worried
about whether pg_dump will handle the case correctly, what happens when
a new child is added later, etc etc.

Is this looking at the wrong problem? The reason I've wanted to get a parent check constraint not to fire in a child is because I'm using the parent/child relationship for partioning. Will this be relevant if/when an independent partitioning feature is added that does not rely on table inheritance?

Best,

David

On 07/27/2011 04:14 PM, David E. Wheeler wrote:

On Jul 27, 2011, at 1:08 PM, Tom Lane wrote:

Yeah. If we're going to allow this then we should just have a concept
of a non-inherited constraint, full stop. This might just be a matter
of removing the error thrown in ATAddCheckConstraint, but I'd be worried
about whether pg_dump will handle the case correctly, what happens when
a new child is added later, etc etc.

Is this looking at the wrong problem? The reason I've wanted to get a parent check constraint not to fire in a child is because I'm using the parent/child relationship for partioning. Will this be relevant if/when an independent partitioning feature is added that does not rely on table inheritance?

Yes, I have clients using inheritance for non-partitioning purposes, and
they would love to have this.

cheers

andrew

On Wed, Jul 27, 2011 at 4:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

Well, I don't have anything strongly against the idea of an
uninherited constraint, though it sounds like Tom does.  But I think
allowing it just in the case of CHECK (false) would be pretty silly.
And, I'm fairly certain that this isn't going to play nice with
coninhcount... local constraints would have to be marked as local,
else the wrong things will happen later on when you drop them.

Yeah.  If we're going to allow this then we should just have a concept
of a non-inherited constraint, full stop.  This might just be a matter
of removing the error thrown in ATAddCheckConstraint, but I'd be worried
about whether pg_dump will handle the case correctly, what happens when
a new child is added later, etc etc.

Right. I'm fairly sure all that stuff is gonna break with the
proposed implementation. It's a solvable problem, but it's going to
take more than an afternoon to crank it out.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Jul 27, 2011 at 14:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah.  If we're going to allow this then we should just have a concept
of a non-inherited constraint, full stop.  This might just be a matter
of removing the error thrown in ATAddCheckConstraint, but I'd be worried
about whether pg_dump will handle the case correctly, what happens when
a new child is added later, etc etc.

[ For those who missed it ]
pg_dump getting things wrong was a big reason to disallow
ONLYconstraints. That is pg_dump did not treat ONLY constraints
correctly, it always tried to stick them on the parent table:
http://archives.postgresql.org/pgsql-bugs/2007-04/msg00026.php

I for example had some backups that had to be manually fixed (by
removing constraints) to get them to import. I would wager the
mentioned clients that have been doing this have broken backups as
well :-(

Now that we have coninhcnt, conislocal etc... we can probably support
ONLY. But I agree with Robert it's probably a bit more than an
afternoon to crank out :-)

Now that we have coninhcnt, conislocal etc... we can probably support
ONLY. But I agree with Robert it's probably a bit more than an
afternoon to crank out :-)

Heh, agreed :), I was just looking for some quick and early feedback. So
what we need is basically a way to indicate that a particular constraint is
non-inheritable forever (meaning - even for future children) and that should
work?

Right now, it seems that the "ONLY" usage in the SQL only translates to a
recurse or no-recurse operation. For the parent, a constraint is marked with
conislocal set to true (coninhcount is 0) and for children, coninhcount is
used to indicate inheritance of that constraint with conislocal being set to
false.

What we need is to persist information of a particular constraint to be as
specified - ONLY for this table. We could do that by adding a new column in
pg_constraint like 'connoinh' or something, but I guess we would prefer not
to get into the initdb business. Alternatively we could bring about the same
by using a combination of conislocal and coninhcnt. For ONLY constraints, we
will need to percolate this information down to the point where we define it
in the code. We can then mark ONLY constraints to have conislocal set to
TRUE and coninhcnt set to a special value (-1). So to summarize, what I am
proposing is:

Introduce new column connoinh (boolean) in pg_constraint

OR in existing infrastructure:

Normal constraints: conislocal (true) coninhcnt (0)
(inheritable like today)
Inherited constraints: conislocal (false) coninhcnt (n > 0)
ONLY constraints: conislocal (true) coninhcnt (-1) (not
inheritable)

With this arrangment, pg_dump will be able to easily identify and spit out
ONLY specifications for specific constraints and then they won't be blindly
passed on to children table under these new semantics.

Thoughts? Anything missing? Please let me know.

Regards,
Nikhils

Nikhil Sontakke <nikhil.sontakke@enterprisedb.com> writes:

What we need is to persist information of a particular constraint to be as
specified - ONLY for this table. We could do that by adding a new column in
pg_constraint like 'connoinh' or something, but I guess we would prefer not
to get into the initdb business.

Uh, why not? I trust you're not imagining this would get back-patched.

Alternatively we could bring about the same
by using a combination of conislocal and coninhcnt.

Ugh. New column, please. If you're wondering why, see the flak Robert
has been taking lately for replacing pg_class.relistemp. Random changes
in the semantics of existing columns are trouble.

regards, tom lane