Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Robert Haas
On Fri, Jul 29, 2011 at 7:41 AM, Nikhil Sontakke nikkh...@gmail.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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Robert Haas
On Fri, Jul 29, 2011 at 8:30 AM, Nikhil Sontakke nikkh...@gmail.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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Tom Lane
Nikhil Sontakke nikkh...@gmail.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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Jul 29, 2011 at 8:30 AM, Nikhil Sontakke nikkh...@gmail.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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Alvaro Herrera
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-29 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-28 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-28 Thread Tom Lane
Nikhil Sontakke nikhil.sonta...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-28 Thread Robert Haas
On Thu, Jul 28, 2011 at 9:43 AM, Nikhil Sontakke nikhil.sonta...@enterprisedb.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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-28 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-28 Thread Robert Haas
On Thu, Jul 28, 2011 at 10:01 AM, Nikhil Sontakke nikhil.sonta...@enterprisedb.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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-27 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-27 Thread Robert Haas
On Wed, Jul 27, 2011 at 6:39 AM, Nikhil Sontakke nikhil.sonta...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-27 Thread Tom Lane
Robert Haas robertmh...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-27 Thread David E. Wheeler
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-27 Thread Andrew Dunstan
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-27 Thread Robert Haas
On Wed, Jul 27, 2011 at 4:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-27 Thread Alex Hunsaker
On Wed, Jul 27, 2011 at 14:08, Tom Lane t...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-26 Thread Nikhil Sontakke
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-26 Thread Robert Haas
On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke nikhil.sonta...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-26 Thread Andrew Dunstan
On 07/26/2011 09:08 AM, Robert Haas wrote: On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke nikhil.sonta...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-26 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke nikhil.sonta...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-26 Thread David Fetter
On Tue, Jul 26, 2011 at 10:51:58AM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Jul 26, 2011 at 4:12 AM, Nikhil Sontakke nikhil.sonta...@enterprisedb.com wrote: Hmmm, but then it does open up the possibility of naive users shooting themselves in the foot. It

Re: [HACKERS] Check constraints on partition parents only?

2011-07-26 Thread Jerry Sievers
Andrew Dunstan and...@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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-26 Thread Jim Nasby
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

[HACKERS] Check constraints on partition parents only?

2011-07-25 Thread Jerry Sievers
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.

Re: [HACKERS] Check constraints on partition parents only?

2011-07-25 Thread Andrew Dunstan
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

Re: [HACKERS] Check constraints on partition parents only?

2011-07-25 Thread Alvaro Herrera
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