Re: VARIANT / ANYTYPE datatype

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Darren Duncan <darren(at)darrenduncan(dot)net>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: VARIANT / ANYTYPE datatype
Date: 2011-05-06 21:05:08
Message-ID: BANLkTi=JtxVSbaJX-UL=E0dHshbPJVs38w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, May 6, 2011 at 3:18 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> On 05/06/2011 04:08 PM, Alvaro Herrera wrote:
>>
>> Excerpts from Darren Duncan's message of mié may 04 15:33:33 -0300 2011:
>>
>>> I see VARIANT/ANYTYPE as the most general case of supporting union types,
>>> which,
>>> say, could have more specific examples of "allow any number or date here
>>> but
>>> nothing else".  If VARIANT is supported, unions in general ought to be
>>> also.
>>
>> Okay, so aside from the performance (storage reduction) gained, there's
>> this argument for having variant/union types.  It seems to me that this
>> is indeed possible to build.  Completely general VARIANT, though, is
>> rather complex.  A declared union, where you specify exactly which types
>> can be part of the union, can be catalogued, so that the system knows
>> exactly where to look when a type needs to be modified.  A general
>> VARIANT however looks complex to me to solve.
>>
>> The problem is this: if an user attempts to drop a type, and this type
>> is used in a variant somewhere, we would lose the stored data.  So the
>> drop needs to be aborted.  Similarly, if we alter a type (easy example:
>> a composite type) used in a variant, we need to cascade to modify all
>> rows using that composite.
>>
>> If the unions that use a certain type are catalogued, we at least know
>> what tables to scan to cascade.
>>
>> In a general variant, the system catalogs do not have the information of
>> what type each variant masquerades as.  We would need to examine the
>> variant's masqueraded types on each insert; if the current type is not
>> found, add it.  This seems a bit expensive.
>
> So how is a declared union going to look and operate? Something like this?
>
>    CREATE TYPE foo AS UNION (ival int, tval text, tsval timestamptz):
>    CREATE TABLE bar (myunion foo);
>    INSERT INTO bar (myunion) VALUES (ival=>1), (tval=>'some text');
>    UPDATE bar SET myunion.tsval = CURRENT_TIMESTAMP;
>
>
> Something like that could actually be quite nice for a number of purposes.

using your hypothetical example, could you cast types to the union?

select 1::int::foo;

record types would presumably work as well? you could do a lot of
*really* neat stuff that way...

merlin

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Krešimir Križanović 2011-05-06 21:27:20 Re: Compiling a PostgreSQL 7.3.2 project with Eclipse
Previous Message Tom Lane 2011-05-06 20:59:55 Re: VARIANT / ANYTYPE datatype