Thread: ADD/DROP constraints
On a separate note. The one major remaining piece here is in constraints. I'm thinking what I have to check is that every constraint present on the parent table is present on the child tables. And I'm thinking I should do that by looking at the constraint's textual definition (consrc). This doesn't allow you to get by with a single stronger constraint -- you would still need the redundant looser constraint to satisfy the inheritance. But it does let you get by with constraint names that don't match the parent's. I'm not sure that's such a good thing, since pg_dump would then generate a redundant constraint when it generates the table. Maybe that would go if constraints got conislocal and coninh. Or maybe I should insist that a matching constraint name be present *and* that the source text match? That's more of a pain to code though. Is there a convenient hash module in the source for small simple hashes that don't require disk spilling? Just a string->string thing I could look up constraint definitions by name from? -- greg
Ühel kenal päeval, N, 2006-06-08 kell 16:30, kirjutas Greg Stark: > On a separate note. The one major remaining piece here is in constraints. I'm > thinking what I have to check is that every constraint present on the parent > table is present on the child tables. And I'm thinking I should do that by > looking at the constraint's textual definition (consrc). > > This doesn't allow you to get by with a single stronger constraint -- you > would still need the redundant looser constraint to satisfy the inheritance. You could find some infrastructure for "stronger constraint" recognition in constraint exclusion code, if you want to go that way. > But it does let you get by with constraint names that don't match the > parent's. > > I'm not sure that's such a good thing, since pg_dump would then generate a > redundant constraint when it generates the table. Maybe that would go if > constraints got conislocal and coninh. Currently pg_dump generates all constraints with ONLY clause anyway. But I agree that we should get rid of ONLY for ADD CONSTRAINT once we disallow dropping inherited constraints. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
On Thu, Jun 08, 2006 at 04:30:22PM -0400, Greg Stark wrote: > Or maybe I should insist that a matching constraint name be present *and* that > the source text match? That's more of a pain to code though. That could also break some partitioning schemes; I don't think it's a given that parents and children have matching constraints, and afaik a parent can have constraints that a child doesn't. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Greg Stark <gsstark@mit.edu> writes: > Or maybe I should insist that a matching constraint name be present *and* that > the source text match? That's more of a pain to code though. Yeah, that's what I'd go with. I believe that there are bits of the system (probably in pg_dump) that look *only* at the constraint name when deciding what's inherited. (This is of course bogus, but until someone does something about coninhcount it's going to be hard to have a non-bogus solution.) Allowing a name mismatch would be bad. One other point is that you should NOT rely on consrc. See the note at the bottom of http://developer.postgresql.org/docs/postgres/catalog-pg-constraint.html (Someday we should get rid of consrc altogether.) Unfortunately it won't do to compare conbin either, because that will contain column numbers that won't necessarily match. I fear you'll have to actually reverse-compile the conbin strings and see if you get a match. regards, tom lane
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> That could also break some partitioning schemes; I don't think it's a
> given that parents and children have matching constraints, and afaik a
> parent can have constraints that a child doesn't.
Not unless you drop the inherited constraint; the fact that you can is a
bug we are going to fix someday soon.
regards, tom lane
> On a separate note. The one major remaining piece here is in > constraints. I'm thinking what I have to check is that every > constraint present on the parent table is present on the > child tables. And I'm thinking I should do that by looking at > the constraint's textual definition (consrc). > > This doesn't allow you to get by with a single stronger > constraint -- you would still need the redundant looser > constraint to satisfy the inheritance. Yes, I think you would actually want eighter an identical, or a stronger constraint on the child. > But it does let you get by with constraint names that don't > match the parent's. > > I'm not sure that's such a good thing, since pg_dump would > then generate a redundant constraint when it generates the > table. Maybe that would go if constraints got conislocal and coninh. > > Or maybe I should insist that a matching constraint name be > present *and* that the source text match? That's more of a > pain to code though. I think in the meantime, I would check that eighter a source match is present OR a constraint with the same name. This would allow more flexibility and imho still enough safety checking. Until we have (or feel a need for) check logic for "stronger constraint" it would be the op's responsibility. Andreas
> > Or maybe I should insist that a matching constraint name be present > > *and* that the source text match? That's more of a pain to code though. > > That could also break some partitioning schemes; I don't > think it's a given that parents and children have matching > constraints, and afaik a parent can have constraints that a > child doesn't. Yea, but that is why we would have parent ONLY constraints, they would only apply when the tuple is actually stored in the parent relation. In the typical partitioning case it does not really matter since the parent ONLY is typically empty. Andreas
Tom Lane <tgl@sss.pgh.pa.us> writes:
> I fear you'll have to actually reverse-compile the conbin strings and see if
> you get a match.
There seems to be something I'm missing wrt the conbin string. I have a table
here with a simple check constraint:
Table "public.a" Column | Type | Modifiers --------+---------+----------- i | integer |
Checkconstraints: "x" CHECK (i = 0)
But when I loop around looking at the constraints the conbin text field has an
empty string in it:
(gdb) p *con $2 = {conname = {data = "x", '\0' <repeats 62 times>, alignmentDummy = 120}, connamespace =
2200,contype = 99 'c', condeferrable = 0 '\0', condeferred = 0 '\0', conrelid = 24661, contypid = 0, confrelid =
0, confupdtype = 32 ' ', confdeltype = 32 ' ', confmatchtype = 32 ' ', conkey = {26}, confkey = {0}, conbin =
{vl_len= 1, vl_dat = ""}, consrc = { vl_len = 21, vl_dat = "\001"}}
And when I call pg_get_expr with:
consrc = DirectFunctionCall2(pg_get_expr, PointerGetDatum(&con->conbin), ObjectIdGetDatum(con->conrelid));
I get:
ERROR: invalid memory alloc request size 4294967294
--
greg
Greg Stark <gsstark@mit.edu> writes:
> There seems to be something I'm missing wrt the conbin string.
You can't access fields that lie to the right of a variable-width field
using C struct field names. You need to use heap_getattr() or one of
its relatives to obtain a pointer to such a field. You ought to be
checking for NULL, too.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > There seems to be something I'm missing wrt the conbin string. > > You can't access fields that lie to the right of a variable-width field > using C struct field names. You need to use heap_getattr() or one of > its relatives to obtain a pointer to such a field. You ought to be > checking for NULL, too. That makes sense. Thanks -- greg