Re: Check constraints on partition parents only?

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.

Comments?

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 305.321.1144

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(at)dunslane(dot)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(dot)sievers(at)comcast(dot)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(at)commandprompt(dot)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(dot)sontakke(at)enterprisedb(dot)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(dot)sontakke(at)enterprisedb(dot)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(at)gmail(dot)com> writes:
> On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke
> <nikhil(dot)sontakke(at)enterprisedb(dot)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(at)gmail(dot)com> writes:
> > On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke
> > <nikhil(dot)sontakke(at)enterprisedb(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)nasby(dot)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(dot)sontakke(at)enterprisedb(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(dot)sontakke(at)enterprisedb(dot)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

On Thu, Jul 28, 2011 at 9:43 AM, Nikhil Sontakke
<nikhil(dot)sontakke(at)enterprisedb(dot)com> wrote:
> 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)

This approach certainly can't work, because a table can be both an
inheritance parent and an inheritance child. It could have an ONLY
constraint, and also inherit a copy of the same constraint for one or
more parents. IOW, the fact that conislocal = true does not mean that
coninhcount is irrelevant. I think what you probably want to do is
either (a) add a new column or (b) change conislocal to a char value
and make it three-valued:

n = inherited constraint, no local definition
o = defined locally as an "ONLY" constraint
i = defined locally as a non-ONLY constraint

I think I favor the latter approach as more space-efficient, but I
hear Tom muttering about backward-compatibility...

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

> This approach certainly can't work, because a table can be both an
> inheritance parent and an inheritance child. It could have an ONLY
> constraint, and also inherit a copy of the same constraint for one or
> more parents. IOW, the fact that conislocal = true does not mean that
> coninhcount is irrelevant.

Oh I see.

> I think what you probably want to do is
> either (a) add a new column or (b) change conislocal to a char value
> and make it three-valued:
>
> n = inherited constraint, no local definition
> o = defined locally as an "ONLY" constraint
> i = defined locally as a non-ONLY constraint
>
> I think I favor the latter approach as more space-efficient, but I
> hear Tom muttering about backward-compatibility...
>
>
Yeah, in your case too an initdb would be required, so might as well go down
the route of a new column. Any preferences for the name?

connoinh
conisonly
constatic or confixed

Others?

Regards,
Nikhils

On Thu, Jul 28, 2011 at 10:01 AM, Nikhil Sontakke
<nikhil(dot)sontakke(at)enterprisedb(dot)com> wrote:
> Yeah, in your case too an initdb would be required, so might as well go down
> the route of a new column. Any preferences for the name?
> connoinh
> conisonly
> constatic or confixed

I'd probably pick conisonly from those choices.

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

On Tue, Jul 26, 2011 at 7:58 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> 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.

Agreed.

> 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 ];

I think that the automatic create feature is just about impossible to
implement reliably, at least not without autonomous transactions.
There are big problems here in the case of concurrent activity.

What Itagaki Takahiro proposed a year ago was basically something
where you would say, OK, I want to partition on this column (or maybe
expression). And then you say:

If the value is less than v1, put it in a partition called p1.
If the value is less than v2, put it in a position called p2.
<repeat ad nauseum, and then, optionally:>
If the value is not less than any of the above, put it in a partition
called poverflow.

I like that design, not least but also not only because it's similar
to what one of our competitors does.

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

Robert,

> If the value is less than v1, put it in a partition called p1.
> If the value is less than v2, put it in a position called p2.
> <repeat ad nauseum, and then, optionally:>
> If the value is not less than any of the above, put it in a partition
> called poverflow.
>
> I like that design, not least but also not only because it's similar
> to what one of our competitors does.

Sure. I'm just restarting the discussion from the point of "what's the
very simplest implementation of partitioning we could create and still
be useful?"

There's value in similicity. First, by having a very simple
implementation it's more likely someone will code it. If we let
-hackers pile on the "must have X feature" to a new partitioning
implementation, it'll never get built.

Second, the key-based partitioning I described would actually be
preferred to what you describe by a lot of users I know, because it's
even simpler than what you propose, which means less contract DBA work
they have to pay for to set it up.

I'm sure what we eventually implement will be a compromise. I just want
to push the discussion away from the "must have every feature under the
sun" direction and towards something that might actually work.

Oh, and no question that automatic partitioning will be a PITA and might
not be implemented for years. But it's a serious user desire.

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

On Thu, Jul 28, 2011 at 12:53 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Robert,
>
>> If the value is less than v1, put it in a partition called p1.
>> If the value is less than v2, put it in a position called p2.
>> <repeat ad nauseum, and then, optionally:>
>> If the value is not less than any of the above, put it in a partition
>> called poverflow.

> Sure.  I'm just restarting the discussion from the point of "what's the
> very simplest implementation of partitioning we could create and still
> be useful?"

> Second, the key-based partitioning I described would actually be
> preferred to what you describe by a lot of users I know, because it's
> even simpler than what you propose, which means less contract DBA work
> they have to pay for to set it up.

But part of the desire for "simple partitioning" is to make sure the
query planner and execution knows about partitions, can do exclude
unnecessary partitions from queries. If partion knowledge doesn't
help the query plans, its not much use excpt to reduce table size,
which isn't a hard task with the current inheritance options.

But if the "partition" selection is an opaque "simple key" type
function, you haven't given the planner/executor anything better to be
able to pick partitions for queries, unless the query is an exact "key
=" type of operation.

So I'm failing to see the benefit of that "key based" partitioning,
even if that key-based function was something like date_trunc on a
timestamp...

a.

--
Aidan Van Dyk                                             Create like a god,
aidan(at)highrise(dot)ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

On Thu, Jul 28, 2011 at 10:20:57AM -0400, Robert Haas wrote:
> What Itagaki Takahiro proposed a year ago was basically something
> where you would say, OK, I want to partition on this column (or maybe
> expression). And then you say:
>
> If the value is less than v1, put it in a partition called p1.
> If the value is less than v2, put it in a position called p2.
> <repeat ad nauseum, and then, optionally:>
> If the value is not less than any of the above, put it in a partition
> called poverflow.
>
> I like that design, not least but also not only because it's similar
> to what one of our competitors does.

FWIW, this seems to me to be the most useful design, because the other
nice use for partitioning is being able to throw away old data without
leaving huge chunks of deleted row. If the column you partition on
is a timestamp, then the above scheme makes it easy to just drop the
oldest partition when the disk is nearly full.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer

Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
> On Thu, Jul 28, 2011 at 12:53 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Second, the key-based partitioning I described would actually be
>> preferred to what you describe by a lot of users I know, because it's
>> even simpler than what you propose, which means less contract DBA work
>> they have to pay for to set it up.

> But part of the desire for "simple partitioning" is to make sure the
> query planner and execution knows about partitions, can do exclude
> unnecessary partitions from queries. If partion knowledge doesn't
> help the query plans, its not much use excpt to reduce table size,
> which isn't a hard task with the current inheritance options.

> But if the "partition" selection is an opaque "simple key" type
> function, you haven't given the planner/executor anything better to be
> able to pick partitions for queries, unless the query is an exact "key
> =" type of operation.

Right. I think the *minimum* requirement for intelligent planning is
that the partitioning be based on ranges of a btree-sortable type.
Single values is a special case of that (for discrete types anyway),
but it doesn't cover enough cases to be the primary definition.

regards, tom lane

Hi,

>>Any preferences for the name?
>> connoinh
>> conisonly
>> constatic or confixed
>
> I'd probably pick conisonly from those choices.
>

The use of "\d" inside psql will show ONLY constraints without any
embellishments similar to normal constraints. E.g.

ALTER TABLE ONLY a ADD CONSTRAINT achk CHECK (FALSE);

ALTER TABLE a ADD CONSTRAINT bchk CHECK (b > 0);

psql=# \d a
Table "public.a"
Column | Type | Modifiers
--------+---------+-----------
b | integer |
Check constraints:
"achk" CHECK (false)
"bchk" CHECK (b > 0)

Is this acceptable? Or we need to put in work into psql to show ONLY
somewhere in the description? If yes, ONLY CHECK sounds weird, maybe
we should use LOCAL CHECK or some such mention:

Check constraints:
"achk" LOCAL CHECK (false)
"bchk" CHECK (b > 0)

Regards,
Nikhils

On Fri, Jul 29, 2011 at 7:41 AM, Nikhil Sontakke <nikkhils(at)gmail(dot)com> wrote:
> Hi,
>
>>>Any preferences for the name?
>>> connoinh
>>> conisonly
>>> constatic or confixed
>>
>> I'd probably pick conisonly from those choices.
>>
>
> The use of "\d" inside psql will show ONLY constraints without any
> embellishments similar to normal constraints. E.g.
>
>
> ALTER TABLE ONLY a ADD CONSTRAINT achk CHECK (FALSE);
>
> ALTER TABLE a ADD CONSTRAINT bchk CHECK (b > 0);
>
> psql=# \d a
>       Table "public.a"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  b      | integer |
> Check constraints:
>    "achk" CHECK (false)
>    "bchk" CHECK (b > 0)
>
> Is this acceptable? Or we need to put in work into psql to show ONLY
> somewhere in the description? If yes, ONLY CHECK sounds weird, maybe
> we should use LOCAL CHECK or some such mention:
>
> Check constraints:
>    "achk" LOCAL CHECK (false)
>    "bchk" CHECK (b > 0)

I think you need to stick with "ONLY". Using two different words is
just going to create confusion. You could fool around with where
exactly you put it on the line, but switching to a different word
seems like not a good idea.

(Also, don't forget you need to hack pg_dump, too.)

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

> psql=# \d a
> Table "public.a"
> Column | Type | Modifiers
> --------+---------+-----------
> b | integer |
> Check constraints:
> "achk" CHECK (false)
> "bchk" CHECK (b > 0)
>
> Is this acceptable? Or we need to put in work into psql to show ONLY
> somewhere in the description? If yes, ONLY CHECK sounds weird, maybe
> we should use LOCAL CHECK or some such mention:
>
> Check constraints:
> "achk" LOCAL CHECK (false)
> "bchk" CHECK (b > 0)

I think you need to stick with "ONLY". Using two different words is
just going to create confusion. You could fool around with where
exactly you put it on the line, but switching to a different word
seems like not a good idea.

Ok, maybe something like:

"achk" (ONLY) CHECK (false)

>>(Also, don't forget you need to hack pg_dump, too.)

Yeah, I have already hacked it a bit. This constraint now needs to be
spit out later as an ALTER command with ONLY attached to it
appropriately. Earlier all CHECK constraints were generally emitted as
part of the table definition itself.

Regards,
Nikhils

On Fri, Jul 29, 2011 at 8:30 AM, Nikhil Sontakke <nikkhils(at)gmail(dot)com> wrote:
> Yeah, I have already hacked it a bit. This constraint now needs to be
> spit out later as an ALTER command with ONLY attached to it
> appropriately. Earlier all CHECK constraints were generally emitted as
> part of the table definition itself.

Hrm. That doesn't seem so good. Maybe we've got the design wrong
here. It doesn't seem like we want to lose the ability to define
arbitrary constraints at table-creation time.

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

Nikhil Sontakke <nikkhils(at)gmail(dot)com> writes:
>> (Also, don't forget you need to hack pg_dump, too.)

> Yeah, I have already hacked it a bit. This constraint now needs to be
> spit out later as an ALTER command with ONLY attached to it
> appropriately. Earlier all CHECK constraints were generally emitted as
> part of the table definition itself.

IIRC, there's already support for splitting out a constraint that way,
in order to deal with circular dependencies. You just need to treat
this as an additional reason for splitting.

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jul 29, 2011 at 8:30 AM, Nikhil Sontakke <nikkhils(at)gmail(dot)com> wrote:
>> Yeah, I have already hacked it a bit. This constraint now needs to be
>> spit out later as an ALTER command with ONLY attached to it
>> appropriately. Earlier all CHECK constraints were generally emitted as
>> part of the table definition itself.

> Hrm. That doesn't seem so good. Maybe we've got the design wrong
> here. It doesn't seem like we want to lose the ability to define
> arbitrary constraints at table-creation time.

Well, you can't define arbitrary indexes within the CREATE TABLE syntax,
either. This does not bother me a lot.

We could imagine doing something like CHECK ONLY (foo), but that seems
quite non-orthogonal with (a) everything else in CREATE TABLE, and
(b) ALTER TABLE ONLY.

regards, tom lane

> > Yeah, I have already hacked it a bit. This constraint now needs to be
> > spit out later as an ALTER command with ONLY attached to it
> > appropriately. Earlier all CHECK constraints were generally emitted as
> > part of the table definition itself.
>
> Hrm. That doesn't seem so good. Maybe we've got the design wrong
> here. It doesn't seem like we want to lose the ability to define
> arbitrary constraints at table-creation time.
>
>
Well the handling is different now for "ONLY" constraints only. The normal
constraints can still be attached at table-creation time.

Regards,
Nikhils

> > Yeah, I have already hacked it a bit. This constraint now needs to be
> > spit out later as an ALTER command with ONLY attached to it
> > appropriately. Earlier all CHECK constraints were generally emitted as
> > part of the table definition itself.
>
> IIRC, there's already support for splitting out a constraint that way,
> in order to deal with circular dependencies. You just need to treat
> this as an additional reason for splitting.
>
>
Yeah, I have indeed followed the existing separate printing logic for "ONLY"
constraints. Had to make the table dependent on this constraint to print the
constraint *after* the table definition.

Regards,
Nikhils

> We could imagine doing something like CHECK ONLY (foo), but that seems
> quite non-orthogonal with (a) everything else in CREATE TABLE, and
> (b) ALTER TABLE ONLY.
>
>
Yeah, I thought about CHECK ONLY support as part of table definition, but as
you say - it appears to be too non-standard right now and we can always go
back to this later if the need be felt.

Regards,
Nikhils

Hi all,

PFA, patch which implements non-inheritable "ONLY" constraints. This
has been achieved by introducing a new column "conisonly" in
pg_constraint catalog. Specification of 'ONLY' in the ALTER TABLE ADD
CONSTRAINT CHECK command is used to set this new column to true.
Constraints which have this column set to true cannot be inherited by
present and future children ever.

The psql and pg_dump binaries have been modified to account for such
persistent non-inheritable check constraints. This patch also has
documentation changes along with relevant changes to the test cases.
The regression runs pass fine with this patch applied.

Comments and further feedback, if any, appreciated.

Regards,
Nikhils

Excerpts from Nikhil Sontakke's message of vie jul 29 14:12:37 -0400 2011:
> Hi all,
>
> PFA, patch which implements non-inheritable "ONLY" constraints. This
> has been achieved by introducing a new column "conisonly" in
> pg_constraint catalog. Specification of 'ONLY' in the ALTER TABLE ADD
> CONSTRAINT CHECK command is used to set this new column to true.
> Constraints which have this column set to true cannot be inherited by
> present and future children ever.
>
> The psql and pg_dump binaries have been modified to account for such
> persistent non-inheritable check constraints. This patch also has
> documentation changes along with relevant changes to the test cases.
> The regression runs pass fine with this patch applied.
>
> Comments and further feedback, if any, appreciated.

Did you look at how this conflicts with my patch to add not null
rows to pg_constraint?

https://commitfest.postgresql.org/action/patch_view?id=601

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

>> Comments and further feedback, if any, appreciated.
>
> Did you look at how this conflicts with my patch to add not null
> rows to pg_constraint?
>
> https://commitfest.postgresql.org/action/patch_view?id=601
>

I was certainly not aware of this patch in the commitfest. Your patch
has a larger footprint with more functional changes in it. IMHO, it
will be easiest to queue this non-inheritable constraints patch behind
your patch in the commitfest. There will be certain bitrot, which I
can fix once your patch gets committed.

Regards,
Nikhils