Re: [COMMITTERS] pgsql: Remove arbitrary ALTER TABLE .. ADD COLUMN restriction. - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> Saying "we're not doing all that well" with it is an understatement.
> We're not doing it at all, except apparently for this one place.

Yeah, it's mostly broken.  That doesn't mean we should introduce more
breakage in order to gain no meaningful functionality.

> Your
> claim that this is an intentional prohibition is without a shred of
> corroborating evidence.

I put that code in --- see commit bb3da43e3bd2a74c5caf8eca7f47ff4198f479ec
--- and I know damn well that it was an intentional prohibition based on
exactly this reasoning.  Please do not argue Postgres development
history with me.

> To believe this is really a problem, you'd
> have to believe that someone will someday want to make default values
> - but not constraints - apply to row types (because, of course, if no
> one ever makes default values apply to row types, then this isn't any
> more of a problem than it is today, and if they make constraints -
> even NOT NULL constraints - apply to row types, then they'll need
> logic to recurse through to tables that use the row type anyway).

I don't see how you come to the conclusion that constraints shouldn't
apply too.  But in any case, the reason for wanting this to happen is
that the SQL spec says so.  It looks like a lot of work, which is why
nobody's tackled it yet, but that doesn't mean it never will happen.

As for the logic to recurse through row types, we have half of it
already (see above commit).  What we don't have is logic to do something
about it once we've found a column that would need changing.

> I am also pretty dubious that the spec requires this behavior, even if
> we did support defaults on row types.  Please produce the evidence.

Please produce the evidence that it doesn't.  The spec is perfectly
clear on what ALTER ADD COLUMN with DEFAULT does to tables, and it also
supports default values attached to the columns of structured types,
and it says (eg in SQL:2008 11.45 <add attribute definition>) that
adding an attribute to a user-defined type results in the same state as
if the attribute had been there all along.  One would think that that
means that the attribute appears with its default value, since you
necessarily have not done anything to modify that value in any
existing instance of the composite type.  Note that essentially the same
wording is used in ALTER ADD COLUMN for tables, SQL:2008 11.11 <add
column definition> general rule 4.

> In essence, what you're arguing is that the spec requires us to treat
> every instance of a row-type as if it were part of the table that gave
> rise to that row-type.

Not at all.  The point is that defaults *are* part of structured types
according to the spec, so they should apply to instances of those
structured types.  We fail to handle that at the moment but the
expectation has always been that we'd get it done someday.

> I think you're conflating the table with its row type, and I'd like to
> see some prior writing indicating otherwise.

I will agree that a language lawyer could argue that a table rowtype
doesn't have to act like a separately-declared composite type, but
that surely doesn't meet the POLA.  I think that an ALTER TABLE on a
table ought to have the implied effects of a spec-compliant ALTER TYPE
on the associated composite type.

> cannot alter table "%s" because column "%s"."%s" uses its rowtype
> Now, at the very least, that error message sucks,

I'll agree with that, but the answer to that is to improve the error
message, not to introduce non-spec-compliant behavior that we will
someday regret.
        regards, tom lane