Typed tables

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