Re: VARIANT / ANYTYPE datatype

Excerpts from Tom Lane's message of mié may 04 14:36:44 -0300 2011:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:

> > The main idea is to be able to store column values in an audit table
> > like this:
> > old_value variant
> > new_value variant
> > Currently, they use text for old_value and new_value, but this is, of
> > course, not very satisfactory.
>
> Just out of curiosity, what actual functionality gain would ensue over
> just using text? It seems like doing anything useful with the audit
> table contents would still require casting the column to text, or the
> moral equivalent of that.

Storage efficiency. These people have really huge databases; small
changes in how tight things are packed makes a large difference for
them. (For example, we developed a type to store SHA-2 digests in a
more compact way than bytea mainly because of this reason. Also, at
some time they also wanted to apply compression to hstore keys and
values.)

As someone commented downthread, they also want to have things such as a
"typeof" operator. It could be used in (say) a plpgsql function to
choose different branches of code.

Things go wild when you think about using VARIANT as a function
parameter type. Given how complex are our current function resolution
rules I'm not really sold on making this work at all, so initially I'm
aiming at just raising an error in that case.

> If we did have a concept of ANYTYPE, or really we'd need ANYTYPE[],
> it could perhaps be used in pg_statistic, in place of the rather ugly
> anyarray hack that's there now. But I note that nothing above the level
> of C code can do anything very useful with the contents of pg_statistic,
> and I'm not sure that having an official type would change that.

Well, we could get rid of ugly hacks that are in various places in the
backend code to cope with this. Nor sure how useful it'd be for doing
things at the SQL level.

> > However, as a middle ground we agreed that we could allow a declared
> > variant to store any pinned type without restrictions; those can't be
> > changed or dropped so there's no safety concern.
>
> If you're going to accept that there are restrictions, I don't see that
> there is a good argument against your thought of a declared "union"
> type. At least then it's clear what the restrictions are. I am firmly
> against exposing the notion of "pinned" types in any user-visible SQL
> semantics, and even more firmly against the idea of creating fundamental
> functionality that only works for built-in types and can't be extended
> to add-on types.

The argument was "it's too cumbersome to use" (which makes sense: users
are certain to forget to add the new type to the declared union when
they add a new column to the table, possibly causing a run-time error if
the audit trigger is in place and attempts to load a new datum into the
log table.)

I understand the reluctancy to use pinned-ness in a user-visible way.
Back to the drawing board.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support