Re: BUG #6489: Alter table with composite type/table

On Wed, Aug 29, 2012 at 6:55 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

>
> Yeah -- the question at hand is whether certain table-ish mechanisms
> apply to a table's type when it's used in non-table-storage ways. In
> particular, defaults and constraints are interesting.

> I'm arguing that they don't apply: defaults and constraints only make
> sense when explicitly set by for the table and only when a record is
> inserted into the table. In other words, they apply to the storage of
> the table, not the type.

There are two places the line could be effectively drawn: At storage of
the type, or at storage of the table. These avoid the outer join problems
for example. My vote would be for storage of the type because once you get
into type methods, it is nice to be able to assume that if it is being run
in the standard use case that some columns are not null. This also is
useful when for column name collision reasons the type may have to be
re-used in a column. Also in cases of nested tables, it would be nice to
be able to do things other than iterating through every member of an array.

On the other hand, multiple inheritance gives us solutions for all these
problems. First it allows the sort of richness in modelling with central
constraint management that we get with drawing the line at storage of the
type, and we can always throw a few columns together, add deep constraints
and inherit from these in different contexts.

You can get a complete system at either set of assumptions. The current
system however draws different assumptions at awkward moments leaving the
DBA realize quite quickly that the developers are not on the same page.

I think there's a lot of circumstantial
> support for that argument; consider the case of plpgsql declared
> record variables for example...what happens to them?

Again, the question is simply this:

Are the table constraints for storage complete in themselves (and assuming
full knowledge of all changes of internal data types) or are they
encapsulated within the types?

A nice middle ground might be domains over complex types. However, we
don't currently support that. Otherwise you end up with really awkward
constructs like:

CREATE FUNCTION is_valid (mytype) RETURNS BOOL

and then:

check ((mytypetest).is_valid)

> If you do want
> defaults and constraints to propagate, then I think we need new
> conventions to do that strictly on compatibility grounds. Maybe if
> you did want propagating behaviors you could explicitly ask for them:
>

Given the current mess in this area, I think backwards-compatibility
settings on a per-database level would be sufficient.

The fact is that anyone doing significant work in this area is already
becoming very familiar with all sorts of ideosyncratic behavior. I don't
think that the as long as the choice was offered, there would be much
resistance towards moving to a more manageable toolkit.

>
> create table foo(a int default 1);
> create table bar(f foo default row(2)::foo); --- this works fine now
>

Sure.

> create table bar(f foo default type_defaults(f)); --- what about this
> (returns default populated type)?
>

The point though is that currently you end up having to write your own
constraint/defaults routines in sql, which then get brought into the table
schema.

create table bar(f foo check (type_constraints(f))); --- or this?
>

You can do this now, see above.

>
> A second undecided point is whether adding attributes to a type should
> require default/not null checks to occur as when adding columns to
> table along with table rebuild if necessary. Here again I'm arguing
> no: previously inserted types to the table have already been defaulted
> and if you buy my argument above, it seems to fit in pretty well.

But this basically means, if you want centralized management of your check
constraints and defaults, multiple inheritance is the only real tool
available, right? Personally I think from an object-relational
perspective, multiple inheritance (despite the gotchas) is currently a
*lot* cleaner than complex types in columns. Especially if you prefix
column names with something meaningful for the class, you can essentially
inline all the types you need, and collapse your table to each type as
needed,.

For example, suppose we didn't have a CIDR type and I wanted to create one
using composit types. I need the netmask bits to be set not null on all
valid entries. The best way to do this would be:

CREATE TABLE cidr_type (
cidr_inet_address text,
cidr_netmask_bits int,
CHECK ((cidr_inet_address IS NULL AND cidr_netmask_bits IS NULL) or
(cidr_inet_address IS NOT NULL AND cidr_netmask_bits IS
NOT NULL)
);

Then I can write my functions about cidr_type and inherit it on other
tables and I get proper constraints, but I *cannot* use this as a column
type without adding functions to manage the check constraints and
explicitly checking them. Again if you had domains available you could
create a domain that would presumably be expanded in table storage.

> So,
> you wouldn't have to go around looking for type_defaults() in the
> event you added a defaulted column to a table (or, if we going in this
> direction, a type). If adding a constraint, you'd probably have to go
> looking around for type_constraints() though.
>
> merlin
>

I suppose this is yet another reason why multiple inheritance is an
absolutely killer feature in PostgreSQL is that currently you *can* model
your data in an equivalent way *and* have check constraints and not null
constraints enforced ;-)

Best Wishes,
Chris Travers