ADD/DROP constraints

Ü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 |
Check constraints:
"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