Re: [HACKERS] Inherited constraints and search paths (was Re:
On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: Berend Tober [EMAIL PROTECTED] writes: Now what, oh most wise one? OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. ... Comments anyone? Best thing to do is to prevent people from creating child tables in different schemas. Or at least advise against it. Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. My partitioning efforts will eventually distinguish between inherited and non-inherited constraints, since the former are fairly useless for partition elimination. So I can't see a reason to care whether they are there or not, if the user knows better. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Inherited constraints and search paths (was Re: [GENERAL] Preserving data after updates)
Berend Tober [EMAIL PROTECTED] writes: On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. The case in question was not one of the child table being in a different partition (do you mean schema?), although that arrangement was considered and rejected for other reasons during data base design. I should clarify: the version of the pg_dump bug that still exists in HEAD is triggered by putting the child table in a different schema than the parent. 7.3 has different behavior --- offhand I think that in 7.3 the problem can occur if the child table is created while search_path is set differently than it was when the parent was created. (Of course, across multiple pg_dump and reload cycles this may boil down to the same thing. But there are more ways to burn yourself given the 7.3 implementation.) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Inherited constraints and search paths (was Re: [GENERAL]
Simon Riggs wrote: On Thu, 2005-05-19 at 23:27 -0400, Tom Lane wrote: Berend Tober [EMAIL PROTECTED] writes: Now what, oh most wise one? OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. ... Comments anyone? Best thing to do is to prevent people from creating child tables in different schemas. Or at least advise against it. Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. My partitioning efforts will eventually distinguish between inherited and non-inherited constraints, since the former are fairly useless for partition elimination. So I can't see a reason to care whether they are there or not, if the user knows better. The case in question was not one of the child table being in a different partition (do you mean schema?), although that arrangement was considered and rejected for other reasons during data base design. In this implementation, a function called for a table constraint was in a different schema. The function so called was defined in the public scheme because it is a generic function that can be used by different applications, and some tables are relevant only to specific applications and so have there own, application-specific schema -- but they still can make use of shared definitions, i.e., this particular function, which are defined in the public schema. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:
Simon Riggs [EMAIL PROTECTED] writes: Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. Uh, why? Arguably the constraints are as much part of the parent table definition as the columns themselves. If you had check (f1 0) in the definition of a table, wouldn't you be pretty surprised to select from it and find rows with f1 0? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table child drop constraint parent_f1_check; ALTER TABLE regression=# insert into child values(-1); INSERT 0 1 regression=# select * from parent; f1 -1 (1 row) I think a good argument can be made that the above behavior is a bug, and that the ALTER command should have been rejected. We've gone to great lengths to make sure you can't ALTER a child table to make it incompatible with the parent in terms of the column names and types; shouldn't this be true of check constraints as well? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was Re:
Simon Riggs [EMAIL PROTECTED] writes: If you were going to fix that by adding a column that allows me to tell the difference between inherited and non-inherited relations, that would be a very useful piece of info for partition elimination. Inherited and non-inherited constraints you mean? Probably. I hadn't thought through the details, but certainly there would need to be some better way of telling whether a constraint was inherited. If you're looking for other inheritance wierdies, you may also be interested in this one. When you create a table that inherits from a master, it copies across constraints with exactly matching names. If a constraint is then added to the master, the constraint is copied across to the child but does not have the same name. Hmm, that's weird: if you give an explicit name (add constraint foo) then it's used, but if you let the system generate the name it's not the same. I agree that was probably unintentional. Does anyone want to argue for keeping it this way? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table parent add check (f1 100); ALTER TABLE regression=# \d parent Table public.parent Column | Type | Modifiers +-+--- f1 | integer | Check constraints: parent_f1_check CHECK (f1 0) parent_f1_check1 CHECK (f1 100) regression=# \d child Table public.child Column | Type | Modifiers +-+--- f1 | integer | Check constraints: parent_f1_check CHECK (f1 0) child_f1_check CHECK (f1 100) Inherits: parent (This is, btw, another case that would break the current code for identifying inherited constraints in pg_dump. Given a positive marker for an inherited constraint, however, we wouldn't care. So I don't think we need to consider pg_dump in debating which behavior we like.) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Inherited constraints and search paths (was Re: [GENERAL] Preserving data after updates)
Berend Tober [EMAIL PROTECTED] writes: Now what, oh most wise one? OK, now I finally get the point: you are creating child tables in different schemas than their parents live in. This creates a problem because reverse-listing of the constraints varies depending on what the search path is. An example in CVS tip is: create function foo(text) returns bool as 'select true' language sql; create table t1(f1 text constraint c1 check (foo(f1))); create schema s1; create table s1.t2() inherits(public.t1); pg_dump yields this: SET search_path = public, pg_catalog; CREATE TABLE t1 ( f1 text, CONSTRAINT c1 CHECK (foo(f1)) ); ... SET search_path = s1, pg_catalog; CREATE TABLE t2 (CONSTRAINT c1 CHECK (public.foo(f1)) ) INHERITS (public.t1); It's the same constraint, but the different reverse-listing fools pg_dump into assuming that it's different. At the moment I'm not seeing any really nice way to fix this. A short-term workaround is to hack pg_dump so that it doesn't compare the constraint expressions at all, but just assumes that a child table's constraint is the same as the parent's if the constraint name matches. You can of course break this by manually dropping the child constraint and creating a different one of the same name --- but does anyone do that in practice? (Note: the code in pg_dump seems to think that there is something special about constraint names beginning with '$', but in quick tests I don't see the system generating constraint names of that kind as far back as 7.0, which is the oldest server version pg_dump now claims to support. So I think that is long-dead code, and that a comparison of constraint names is probably sufficient in practice.) It can be argued that we should actually prohibit dropping inherited constraints, which'd eliminate that problem. I seem to recall that this has come up before and we explicitly decided against making such a restriction ... but given that a dump/restore will cause the inherited constraint to come back anyway, it can hardly be claimed that we really support dropping them. Comments anyone? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
