Typed tables

[Peter Eisentraut-2]

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?


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

[Tom Lane-2]

Peter Eisentraut <peter_e@...> 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

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

[James William Pye-6]

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;"

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

[Peter Eisentraut-2]

On tor, 2009-11-05 at 12:38 -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e@...> 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.


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

[Peter Eisentraut-2]

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.


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

[Merlin Moncure-2]

On Thu, Nov 5, 2009 at 12:24 PM, Peter Eisentraut <peter_e@...> 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

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

[Heikki Linnakangas-3]

Merlin Moncure wrote:

> On Thu, Nov 5, 2009 at 12:24 PM, Peter Eisentraut <peter_e@...> 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

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

[Takahiro Itagaki]

Peter Eisentraut <peter_e@...> 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



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

[Simon Riggs]

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


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

[Tatsuo Ishii-4]

> > 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

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

[Peter Eisentraut-2]

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.



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

[Simon Riggs]

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


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