Обсуждение: ALTER TABLE INHERIT vs collations
Right at the moment, ALTER INHERIT doesn't verify that collations match
in a proposed inheritance child. So you can do this:
regression=# create table foo (f1 text collate "C");
CREATE TABLE
regression=# create table bar (f1 text collate "POSIX");
CREATE TABLE
regression=# alter table bar inherit foo;
ALTER TABLE
but then the planner whines about it:
regression=# select * from foo;
ERROR: attribute "f1" of relation "bar" does not match parent's collation
Does anyone think it's not a bug that ALTER TABLE lets this through?
If so, what do you think the querying semantics ought to be?
regards, tom lane
On 16 April 2011 23:23, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Right at the moment, ALTER INHERIT doesn't verify that collations match > in a proposed inheritance child. So you can do this: > > regression=# create table foo (f1 text collate "C"); > CREATE TABLE > regression=# create table bar (f1 text collate "POSIX"); > CREATE TABLE > regression=# alter table bar inherit foo; > ALTER TABLE > > but then the planner whines about it: > > regression=# select * from foo; > ERROR: attribute "f1" of relation "bar" does not match parent's collation > > Does anyone think it's not a bug that ALTER TABLE lets this through? > If so, what do you think the querying semantics ought to be? An argument to not treat it as a bug might be to suggest that the child table's column could inherit the parent table's column collation when the query targets the parent, but revert to its own otherwise. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thom Brown <thom@linux.com> writes:
> On 16 April 2011 23:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Does anyone think it's not a bug that ALTER TABLE lets this through?
>> If so, what do you think the querying semantics ought to be?
> An argument to not treat it as a bug might be to suggest that the
> child table's column could inherit the parent table's column collation
> when the query targets the parent, but revert to its own otherwise.
That seems to me to be about on par with arguing that inheritance
shouldn't demand column type matching, but should coerce child columns
on-the-fly to their parent's type. Even if you don't think that's
horrid from a theoretical standpoint, there's a good practical reason
not to allow it: if it acts that way, then indexes on the child column
will silently not be usable for many kinds of query against the parent.
People will be tearing their hair out looking for the cause of their
performance problems ... and, no doubt, filing bugs against the planner
... when throwing an error would have helped them catch the mismatch.
I think there needs to be a pretty darn compelling use-case for such
mismatches before we should allow them. And I don't see one.
regards, tom lane
On 17 April 2011 00:40, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Thom Brown <thom@linux.com> writes: >> On 16 April 2011 23:23, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Does anyone think it's not a bug that ALTER TABLE lets this through? >>> If so, what do you think the querying semantics ought to be? > >> An argument to not treat it as a bug might be to suggest that the >> child table's column could inherit the parent table's column collation >> when the query targets the parent, but revert to its own otherwise. > > That seems to me to be about on par with arguing that inheritance > shouldn't demand column type matching, but should coerce child columns > on-the-fly to their parent's type. Even if you don't think that's > horrid from a theoretical standpoint, there's a good practical reason > not to allow it: if it acts that way, then indexes on the child column > will silently not be usable for many kinds of query against the parent. > People will be tearing their hair out looking for the cause of their > performance problems ... and, no doubt, filing bugs against the planner > ... when throwing an error would have helped them catch the mismatch. > > I think there needs to be a pretty darn compelling use-case for such > mismatches before we should allow them. And I don't see one. Conceded. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sat, Apr 16, 2011 at 6:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Right at the moment, ALTER INHERIT doesn't verify that collations match > in a proposed inheritance child. So you can do this: > > regression=# create table foo (f1 text collate "C"); > CREATE TABLE > regression=# create table bar (f1 text collate "POSIX"); > CREATE TABLE > regression=# alter table bar inherit foo; > ALTER TABLE > > but then the planner whines about it: > > regression=# select * from foo; > ERROR: attribute "f1" of relation "bar" does not match parent's collation > > Does anyone think it's not a bug that ALTER TABLE lets this through? > If so, what do you think the querying semantics ought to be? We seem to generally be treating collations as type information, so that seems to argue that we ought to force it to match here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
One use case for this might be with constraint exclusion where there would be one partition per collation (language) with queries against the parent table always being for exactly one language. Not sure what the collation should be in the parent table then.
On Sat, 2011-04-16 at 18:23 -0400, Tom Lane wrote: > Right at the moment, ALTER INHERIT doesn't verify that collations > match in a proposed inheritance child. It should be prevented, I think.
On Sat, 2011-04-16 at 21:52 -0400, Jean-Pierre Pelletier wrote: > One use case for this might be with constraint exclusion where there > would be one > partition per collation (language) with queries against the parent table always > being for exactly one language. If you really wanted that, you can always use a view to make it explicit.
Peter Eisentraut <peter_e@gmx.net> writes:
> On Sat, 2011-04-16 at 18:23 -0400, Tom Lane wrote:
>> Right at the moment, ALTER INHERIT doesn't verify that collations
>> match in a proposed inheritance child.
> It should be prevented, I think.
BTW, on looking through the source to find the cause, I see that the
reason it's not prevented is that MergeAttributesIntoExisting() failed
to check for collation match. But the other three places in tablecmds.c
that check for child column type matches do check collation too. So it
seems clear that this was just an oversight in one case.
regards, tom lane