Thread: 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@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.
Attachment
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 constraintsto 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@gmx.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@postgresql.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@dunslane.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