Re: Typed tables

I'm planning to work on typed tables support. The idea is that you
create a table out of a composite type (as opposed to the other way
around, which is currently done automatically).

CREATE TYPE persons_type AS (name text, bdate date);

CREATE TABLE persons OF persons_type;

Or the fancy version:

CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );

This is useful in conjunction with PL/Proxy and similar RPC-type setups.
On the frontend/proxy instances you only create the type, and the
backend instances you create the storage for the type, and the database
system would give you a little support keeping them in sync. Think
interface and implementation.

We have all the necessary bits available in the PostgreSQL system
already; they just need to be wired together a little differently for
this feature. The CREATE TABLE / OF command would use some parts of the
LIKE and/or INHERITS logic to make a copy of the composite type's
structure, and then we'd probably need a new column in pg_class to store
the relationship of the table to its type.

One thing I'm not sure of is whether to keep the implicit row type in
that case. That is, would the above command sequence still create a
"persons" type? We could keep that so as to preserve the property "a
table always has a row type of the same name", or we could skip it in
that case, so if you create a typed table in this sense, you need to use
the type that you created yourself beforehand.

Thoughts?

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> One thing I'm not sure of is whether to keep the implicit row type in
> that case. That is, would the above command sequence still create a
> "persons" type?

Are you intending that the table and the original composite type are
independent, or are still tied together --- ie, does ALTER TABLE ADD
COLUMN or similar affect the composite type?

If not, you *must* have a rowtype that is associated with the table.

regards, tom lane

On Nov 5, 2009, at 10:24 AM, Peter Eisentraut wrote:
> One thing I'm not sure of is whether to keep the implicit row type in
> that case. That is, would the above command sequence still create a
> "persons" type? We could keep that so as to preserve the property "a
> table always has a row type of the same name"

+1 for keeping it.

> Thoughts?

Any plans to allow the specification of multiple types to define the
table?

"CREATE TABLE employee OF employee_data_type, persons_data_type;"

On tor, 2009-11-05 at 12:38 -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > One thing I'm not sure of is whether to keep the implicit row type in
> > that case. That is, would the above command sequence still create a
> > "persons" type?
>
> Are you intending that the table and the original composite type are
> independent, or are still tied together --- ie, does ALTER TABLE ADD
> COLUMN or similar affect the composite type?

They need to stay tied together. But it's to be determined whether
ALTER TABLE ADD COLUMN would work on those tables or whether there would
be some kind of ALTER TYPE.

On tor, 2009-11-05 at 11:41 -0700, James Pye wrote:
> Any plans to allow the specification of multiple types to define the
> table?
>
> "CREATE TABLE employee OF employee_data_type, persons_data_type;"

Not really, but it does open up interesting possibilities, if we just
allow composite types to participate in inheritance relationships.
Think abstract base class. That's pretty much the idea. Come to think
of it, that's how the SQL standard defined inheritance. Sounds
interesting. And might actually be simpler to implement.

On Thu, Nov 5, 2009 at 12:24 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> I'm planning to work on typed tables support.  The idea is that you
> create a table out of a composite type (as opposed to the other way
> around, which is currently done automatically).
>
> CREATE TYPE persons_type AS (name text, bdate date);
>
> CREATE TABLE persons OF persons_type;
>
> Or the fancy version:
>
> CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );

I use composite types (via tables) all the time but I never use
'create type as'...because by doing so you lose the ability to alter
the type with 'alter table'.

Am I correct that I could use your idea to make this possible (albeit
quite ugly) by:

create type foo(a text, b text);
create table foo of foo;
alter table foo add column c text;
drop table foo; -- does this drop the type as well??

merlin

Merlin Moncure wrote:
> On Thu, Nov 5, 2009 at 12:24 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> I'm planning to work on typed tables support. The idea is that you
>> create a table out of a composite type (as opposed to the other way
>> around, which is currently done automatically).
>>
>> CREATE TYPE persons_type AS (name text, bdate date);
>>
>> CREATE TABLE persons OF persons_type;
>>
>> Or the fancy version:
>>
>> CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );
>
> I use composite types (via tables) all the time but I never use
> 'create type as'...because by doing so you lose the ability to alter
> the type with 'alter table'.
>
> Am I correct that I could use your idea to make this possible (albeit
> quite ugly) by:
>
> create type foo(a text, b text);
> create table foo of foo;
> alter table foo add column c text;
> drop table foo; -- does this drop the type as well??

That seems weird. Seems we should forbid that, and have an ALTER TYPE
command instead. I guess that means that we have to somehow memorize
that the type and the table are distinct. Also, if you create a type and
a table from it, pg_dump still needs to dump the CREATE TYPE command,
not just CREATE TABLE.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

> On tor, 2009-11-05 at 11:41 -0700, James Pye wrote:
> > "CREATE TABLE employee OF employee_data_type, persons_data_type;"
>
> Not really, but it does open up interesting possibilities, if we just
> allow composite types to participate in inheritance relationships.
> Think abstract base class. That's pretty much the idea. Come to think
> of it, that's how the SQL standard defined inheritance. Sounds
> interesting. And might actually be simpler to implement.

Do you want to tightly bind the table with the underlying type?
In other words, do you think "copying column definitions" is not enough?

Like:
CREATE TABLE employee (LIKE employee_data_type, LIKE persons_data_type);
or
CREATE TABLE employee () INHERITS (employee_data_type, persons_data_type);

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Thu, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:

> This is useful in conjunction with PL/Proxy and similar RPC-type
> setups. On the frontend/proxy instances you only create the type, and
> the backend instances you create the storage for the type, and the
> database system would give you a little support keeping them in sync.
> Think interface and implementation.

Not sure I see why this is good. Why is issuing CREATE TYPE so much
easier than using CREATE TABLE? Is it worth the extra syntax and code to
support it? Can we do anything additional as a result of this? Is this
required by the standard or are we going past the standard?

--
Simon Riggs www.2ndQuadrant.com

> > This is useful in conjunction with PL/Proxy and similar RPC-type
> > setups. On the frontend/proxy instances you only create the type, and
> > the backend instances you create the storage for the type, and the
> > database system would give you a little support keeping them in sync.
> > Think interface and implementation.
>
> Not sure I see why this is good. Why is issuing CREATE TYPE so much
> easier than using CREATE TABLE? Is it worth the extra syntax and code to
> support it? Can we do anything additional as a result of this? Is this
> required by the standard or are we going past the standard?

+1. I'd like to hear from Peter why this is neccessary in the first
place.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

On Sun, 2009-11-08 at 21:17 +0000, Simon Riggs wrote:
> Not sure I see why this is good. Why is issuing CREATE TYPE so much
> easier than using CREATE TABLE? Is it worth the extra syntax and code to
> support it? Can we do anything additional as a result of this?

These are tools to improve database design in particular situations.
Nobody really *needs* this, but then again, you don't really need CREATE
TYPE for composite types in the first place. Using CREATE TABLE instead
of CREATE TYPE creates a bunch of extra things you don't need. For
example, files are created, VACUUM and ANALYZE have to keep checking the
table, backup tools think they have to back up the table, and you have
to check that no one actually inserts anything into the table.

> Is this required by the standard or are we going past the standard?

This is part of the SQL standard.

On Mon, 2009-11-09 at 12:15 +0200, Peter Eisentraut wrote:

> > Is this required by the standard or are we going past the standard?
>
> This is part of the SQL standard.

+1

--
Simon Riggs www.2ndQuadrant.com

On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
> I'm planning to work on typed tables support. The idea is that you
> create a table out of a composite type (as opposed to the other way
> around, which is currently done automatically).
>
> CREATE TYPE persons_type AS (name text, bdate date);
>
> CREATE TABLE persons OF persons_type;
>
> Or the fancy version:
>
> CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );

And here is the first patch for that. The feature is complete as far as
I had wanted it. I would like to add ALTER TYPE support, but that can
come as a separate patch.

On 1/10/10 2:34 PM, Peter Eisentraut wrote:
> On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
>> I'm planning to work on typed tables support. The idea is that you
>> create a table out of a composite type (as opposed to the other way
>> around, which is currently done automatically).

Nice. Can we come up with a better name for the feature, though?
"Composite Type Tables"? "Type-Table Inheritance"?

--Josh Berkus

On sön, 2010-01-10 at 15:27 -0800, Josh Berkus wrote:
> On 1/10/10 2:34 PM, Peter Eisentraut wrote:
> > On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
> >> I'm planning to work on typed tables support. The idea is that you
> >> create a table out of a composite type (as opposed to the other way
> >> around, which is currently done automatically).
>
> Nice. Can we come up with a better name for the feature, though?
> "Composite Type Tables"? "Type-Table Inheritance"?

"Typed tables" is the official SQL standard name for the feature, and
it's also used in DB2 documentation. So I kind of would prefer to keep
it.

On Mon, Nov 9, 2009 at 5:15 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On Sun, 2009-11-08 at 21:17 +0000, Simon Riggs wrote:
>> Not sure I see why this is good. Why is issuing CREATE TYPE so much
>> easier than using CREATE TABLE? Is it worth the extra syntax and code to
>> support it? Can we do anything additional as a result of this?
>
> These are tools to improve database design in particular situations.
> Nobody really *needs* this, but then again, you don't really need CREATE
> TYPE for composite types in the first place.  Using CREATE TABLE instead
> of CREATE TYPE creates a bunch of extra things you don't need.  For
> example, files are created, VACUUM and ANALYZE have to keep checking the
> table, backup tools think they have to back up the table, and you have
> to check that no one actually inserts anything into the table.

you also get the ability to alter the type though, which at present
outweighs the disadvantages in most cases (IMO).

I happen to be a fan of your proposal...mainly because it highlights
the highly under-appreciated composite type handling of the database.
I especially am excited about getting 'ALTER TYPE' in the future :-).
Do you think that we will ever able to apply constraints to composite
type that will be enforced on a cast?

merlin

Peter,

> "Typed tables" is the official SQL standard name for the feature, and
> it's also used in DB2 documentation. So I kind of would prefer to keep
> it.

Sorry, I missed the SQL standard part in the thread. Ignore the noise.

Oh, and BTW, +1 on accepting this, pending patch quality and all that.

--Josh Berkus

Peter Eisentraut wrote:
> On tor, 2009-11-05 at 19:24 +0200, Peter Eisentraut wrote:
>> I'm planning to work on typed tables support. The idea is that you
>> create a table out of a composite type (as opposed to the other way
>> around, which is currently done automatically).
>>
>> CREATE TYPE persons_type AS (name text, bdate date);
>>
>> CREATE TABLE persons OF persons_type;
>>
>> Or the fancy version:
>>
>> CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) );
>
> And here is the first patch for that. The feature is complete as far as
> I had wanted it. I would like to add ALTER TYPE support, but that can
> come as a separate patch.

+1

ISTM that the ultimate would be a 'create table (...._) without storage'
(or some'm) and make 'create type' an alternate syntax for SQL
conformance. For various reasons, we've internally adopted using create
table for all composites and use a c-like naming convenstion of
appending _t to such beasts.

I'll just throw a little meat into the pack wolves....constraints....?

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
> ISTM that the ultimate would be a 'create table (...._) without storage'
> (or some'm) and make 'create type' an alternate syntax for SQL
> conformance.

I don't really understand the purpose of that.

> For various reasons, we've internally adopted using create
> table for all composites and use a c-like naming convenstion of
> appending _t to such beasts.

Yes, I have a similar convention.

Peter Eisentraut wrote:
> On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
>> ISTM that the ultimate would be a 'create table (...._) without storage'
>> (or some'm) and make 'create type' an alternate syntax for SQL
>> conformance.
>
> I don't really understand the purpose of that.
>

What is the point of CREATE TYPE name AS () syntax? Why would one use create
type when there is create table? Does it provide additional functionality I am
unaware of or does it exist for comformance reasons?

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

On mån, 2010-01-11 at 19:27 -0500, Andrew Chernow wrote:
> Peter Eisentraut wrote:
> > On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
> >> ISTM that the ultimate would be a 'create table (...._) without storage'
> >> (or some'm) and make 'create type' an alternate syntax for SQL
> >> conformance.
> >
> > I don't really understand the purpose of that.
> >
>
> What is the point of CREATE TYPE name AS () syntax? Why would one use create
> type when there is create table? Does it provide additional functionality I am
> unaware of or does it exist for comformance reasons?

Well, that is a very deep question. ;-) I suppose a concise answer
would be that types are for passing data around between functions, and
tables are for storing data on disk.

2010/1/12 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> On mån, 2010-01-11 at 19:27 -0500, Andrew Chernow wrote:
>> Peter Eisentraut wrote:
>> > On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
>> >> ISTM that the ultimate would be a 'create table (...._) without storage'
>> >> (or some'm) and make 'create type' an alternate syntax for SQL
>> >> conformance.
>> >
>> > I don't really understand the purpose of that.
>> >
>>
>> What is the point of CREATE TYPE name AS () syntax?  Why would one use create
>> type when there is create table?  Does it provide additional functionality I am
>> unaware of or does it exist for comformance reasons?
>
> Well, that is a very deep question. ;-)  I suppose a concise answer
> would be that types are for passing data around between functions, and
> tables are for storing data on disk.

it should help only for "small" tables. It's looks well, but it can be
very slow and very memory expensive for bigger tables. I thing, we
need some QUERY->cursor translation mechanism. Memory based solution
(with arrays) is better than nothing, but it cannot be for all.

Pavel

>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Peter Eisentraut wrote:
> On mån, 2010-01-11 at 19:27 -0500, Andrew Chernow wrote:
>> Peter Eisentraut wrote:
>>> On mån, 2010-01-11 at 15:02 -0500, Andrew Chernow wrote:
>>>> ISTM that the ultimate would be a 'create table (...._) without storage'
>>>> (or some'm) and make 'create type' an alternate syntax for SQL
>>>> conformance.
>>> I don't really understand the purpose of that.
>>>
>> What is the point of CREATE TYPE name AS () syntax? Why would one use create
>> type when there is create table? Does it provide additional functionality I am
>> unaware of or does it exist for comformance reasons?
>
> Well, that is a very deep question. ;-) I suppose a concise answer
> would be that types are for passing data around between functions, and
> tables are for storing data on disk.
>
>

In practice, tables can be used for passing data around or storing it on disk.
So, I guess my question remains unanswered as to what the composite type offers
that a table doesn't; other than a name that better suits the task.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

On tis, 2010-01-12 at 08:05 -0500, Andrew Chernow wrote:
> In practice, tables can be used for passing data around or storing it on disk.
> So, I guess my question remains unanswered as to what the composite type offers
> that a table doesn't; other than a name that better suits the task.

The arguments of functions are types, not tables. So you need types if
you want to use functions.

Peter Eisentraut wrote:
> On tis, 2010-01-12 at 08:05 -0500, Andrew Chernow wrote:
>
>> In practice, tables can be used for passing data around or storing it on disk.
>> So, I guess my question remains unanswered as to what the composite type offers
>> that a table doesn't; other than a name that better suits the task.
>>
>
> The arguments of functions are types, not tables. So you need types if
> you want to use functions.
>
>
>

What is the point of this discussion? We're not going to remove the
facility for composite types, regardless of whether or not some people
regard them as unnecessary. And "a name that better suits the task" is
not to be sneered at anyway.

cheers

andrew

Peter Eisentraut wrote:
> On tis, 2010-01-12 at 08:05 -0500, Andrew Chernow wrote:
>> In practice, tables can be used for passing data around or storing it on disk.
>> So, I guess my question remains unanswered as to what the composite type offers
>> that a table doesn't; other than a name that better suits the task.
>
> The arguments of functions are types, not tables. So you need types if
> you want to use functions.

really....

create table mytype_t (a int, b int);

create function mytype_func(t mytype_t) returns int as
$$
select ($1).a + ($1).b;
$$ language sql;

select mytype_func((10, 10)::mytype_t);

mytype_func
-------------
20
(1 row)

A table is a record type (backend/util/adt/rowtypes.c) as is a
composite. One difference is pg_class.relkind is 'r' for relation vs.
'c' for composite.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

>
> What is the point of this discussion? We're not going to remove the
> facility for composite types, regardless of whether or not some people
> regard them as unnecessary. And "a name that better suits the task" is
> not to be sneered at anyway.
>

I never asked for anything to be removed nor do I sneer :) Honestly, I
was only trying to understand why it existed.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

On Tue, Jan 12, 2010 at 9:00 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> What is the point of this discussion? We're not going to remove the facility
> for composite types, regardless of whether or not some people regard them as
> unnecessary. And "a name that better suits the task" is not to be sneered at
> anyway.

nobody is arguing to remove the create type syntax. I suppose in
hindsight more thought might have been given to the overlap w/create
table. Also you have to admit that having both 'create type' and
'create type as' which do completely different things is pretty
awkward. in addition, we have 'create table' which gives us three
different methods of creating types, each with their own nuance and
advantages. please understand, I'm not griping: the postgresql type
system is wonderful...there's nothing else quite like it out there.

The questions I am posing are this:
*) should 'create type as' get an 'alter'? ( I think most would think so)
*) if so, how do you distinguish between the composite and non
composite version? How would this command look?
*) should we be able to define check constraints on composite types
(presumably, enforced on a cast)?
*) should 'create type as' should be walled off with 'create table'
handling most cases of type creation? (previously would have said yes,
but with typed table enhancement, probably not)

merlin

merlin

On tis, 2010-01-12 at 09:54 -0500, Merlin Moncure wrote:
> *) should 'create type as' get an 'alter'? ( I think most would think so)

Working on that right now ...

> *) if so, how do you distinguish between the composite and non
> composite version? How would this command look?

I'm only dealing with the composite types right now, and the syntax is
ALTER TYPE name ADD/DROP ATTRIBUTE name, per SQL standard.

> *) should we be able to define check constraints on composite types
> (presumably, enforced on a cast)?

That could be an interesting feature addition. It'd basically be the
composite-type version of domains.

> *) should 'create type as' should be walled off with 'create table'
> handling most cases of type creation? (previously would have said yes,
> but with typed table enhancement, probably not)

This might be a matter of taste, but also note that these interfaces are
prescribed by the SQL standard, so if you have them, they should do the
things the spec says.

On 01/12/2010 06:43 AM, Andrew Chernow wrote:
>>
>> What is the point of this discussion? We're not going to remove the
>> facility for composite types, regardless of whether or not some people
>> regard them as unnecessary. And "a name that better suits the task" is
>> not to be sneered at anyway.
>>
>
> I never asked for anything to be removed nor do I sneer :) Honestly, I
> was only trying to understand why it existed.

It exists because once upon a time when SRFs were first created, and you
were using a function returning SETOF RECORD, you would either have to
enumerate every column definition in your query, or create a "dummy"
table that had the right columns/types to match your return tuple.

That solution was generally viewed as grotty -- the former is a lot of
typing and clutter, and the latter creates a table with the only purpose
being to get the needed composite type created. Therefore we added the
ability to skip the table creation and just produce the needed composite
type.

HTH

Joe