Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Wed, Apr 27, 2011 at 09:30:41PM +0300, Peter Eisentraut wrote: > Here is the patch to fix that, as discussed. Looks correct. Thanks. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
Peter Eisentraut writes: > Here is the patch to fix that, as discussed. Looks sane --- I assume you tested it against the originally complained-of scenario? http://archives.postgresql.org/message-id/201103111328.p2bdsfd10...@momjian.us If so, please apply soon --- we need to wrap beta1 this evening. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
Here is the patch to fix that, as discussed.
diff --git i/src/bin/pg_dump/pg_dump.c w/src/bin/pg_dump/pg_dump.c
index c2f6180..afc7fd7 100644
--- i/src/bin/pg_dump/pg_dump.c
+++ w/src/bin/pg_dump/pg_dump.c
@@ -12004,7 +12004,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
"UNLOGGED " : "",
reltypename,
fmtId(tbinfo->dobj.name));
- if (tbinfo->reloftype)
+ /*
+ * In case of a binary upgrade, we dump the table normally and attach
+ * it to the type afterward.
+ */
+ if (tbinfo->reloftype && !binary_upgrade)
appendPQExpBuffer(q, " OF %s", tbinfo->reloftype);
actual_atts = 0;
for (j = 0; j < tbinfo->numatts; j++)
@@ -12032,7 +12036,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
bool has_notnull = (tbinfo->notnull[j]
&& (!tbinfo->inhNotNull[j] || binary_upgrade));
-if (tbinfo->reloftype && !has_default && !has_notnull)
+if (tbinfo->reloftype && !has_default && !has_notnull && !binary_upgrade)
continue;
/* Format properly if not first attr */
@@ -12060,7 +12064,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
}
/* Attribute type */
-if (tbinfo->reloftype)
+if (tbinfo->reloftype && !binary_upgrade)
{
appendPQExpBuffer(q, "WITH OPTIONS");
}
@@ -12126,7 +12130,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
if (actual_atts)
appendPQExpBuffer(q, "\n)");
- else if (!tbinfo->reloftype)
+ else if (!(tbinfo->reloftype && !binary_upgrade))
{
/*
* We must have a parenthesized attribute list, even though empty,
@@ -12192,6 +12196,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* an INHERITS clause --- the latter would possibly mess up the column
* order. That also means we have to take care about setting
* attislocal correctly, plus fix up any inherited CHECK constraints.
+ * Analogously, we set up typed tables using ALTER TABLE / OF here.
*/
if (binary_upgrade && tbinfo->relkind == RELKIND_RELATION)
{
@@ -12268,6 +12273,14 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
}
}
+ if (tbinfo->reloftype)
+ {
+appendPQExpBuffer(q, "\n-- For binary upgrade, set up typed tables this way.\n");
+appendPQExpBuffer(q, "ALTER TABLE ONLY %s OF %s;\n",
+ fmtId(tbinfo->dobj.name),
+ tbinfo->reloftype);
+ }
+
appendPQExpBuffer(q, "\n-- For binary upgrade, set heap's relfrozenxid\n");
appendPQExpBuffer(q, "UPDATE pg_catalog.pg_class\n"
"SET relfrozenxid = '%u'\n"
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Fri, Apr 08, 2011 at 03:43:39PM -0400, Robert Haas wrote: > On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch wrote: > > Incidentally, this led me to notice that you can hang a typed > > table off a table row type. ?ALTER TABLE never propagates to such typed > > tables, > > allowing them to get out of sync: > > > > create table t (x int, y int); > > create table is_a of t; > > create table is_a2 of t; > > alter table t drop y, add z int; > > \d is_a > > ? ? Table "public.is_a" > > ?Column | ?Type ? | Modifiers > > +-+--- > > ?x ? ? ?| integer | > > ?y ? ? ?| integer | > > Typed table of type: t > > > > Perhaps we should disallow the use of table row types in CREATE TABLE ... > > OF? > > Yes, I think we need to do that. Having thought about it some more, that would be unfortunate. We rarely distinguish between table row types and CREATE TYPE AS types. Actually, I'm not aware of any place we distinguish other than in ALTER TABLE/ALTER TYPE, to instruct you to use the other. But depending on how hard it is to fix, that might be a good stopgap. > >> It looks like Noah Misch might have found another problem in this area. > >> We'll have to investigate that. > > > > Your bits in dumpCompositeType() are most of what's needed to fix that, I > > think. > > Most? I think it will just fall out of the completed fix for the original reported problem. Will keep you posted. nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 12:50 PM, Peter Eisentraut wrote:
> On tor, 2011-02-10 at 06:31 +0200, Peter Eisentraut wrote:
>> > ERROR: cannot drop column from typed table
>> >
>> > which probably is because test_type2 has a dropped column.
>>
>> It should call
>>
>> ALTER TYPE test_type2 DROP ATTRIBUTE xyz CASCADE;
>>
>> instead. That will propagate to the table.
>
> Here is a patch that addresses this problem.
>
> It looks like Noah Misch might have found another problem in this area.
> We'll have to investigate that.
There's something wrong with this patch - it never arranges to
actually drop the phony column. Consider:
create type foo as (a int, b int);
alter table foo drop attribute b;
create table x (a int, b int);
alter table x drop column b;
Then pg_dump --binary-upgrade emits, in relevant part, the following for x:
CREATE TABLE x (
a integer,
"pg.dropped.2" INTEGER /* dummy */
);
-- For binary upgrade, recreate dropped column.
UPDATE pg_catalog.pg_attribute
SET attlen = 4, attalign = 'i', attbyval = false
WHERE attname = 'pg.dropped.2'
AND attrelid IN ('x'::pg_catalog.regclass);
ALTER TABLE ONLY x DROP COLUMN "pg.dropped.2";
But for t we get only:
CREATE TYPE foo AS (
a integer,
"pg.dropped.2" INTEGER /* dummy */
);
...which is no good.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch wrote: > Incidentally, this led me to notice that you can hang a typed > table off a table row type. ALTER TABLE never propagates to such typed > tables, > allowing them to get out of sync: > > create table t (x int, y int); > create table is_a of t; > create table is_a2 of t; > alter table t drop y, add z int; > \d is_a > Table "public.is_a" > Column | Type | Modifiers > +-+--- > x | integer | > y | integer | > Typed table of type: t > > Perhaps we should disallow the use of table row types in CREATE TABLE ... OF? Yes, I think we need to do that. >> It looks like Noah Misch might have found another problem in this area. >> We'll have to investigate that. > > Your bits in dumpCompositeType() are most of what's needed to fix that, I > think. Most? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 09:32:08PM -0400, Noah Misch wrote:
> ... ALTER TYPE mistakenly
> only touches the first table-of-type:
>
> create type t as (x int, y int);
> create table is_a of t;
> create table is_a2 of t;
> alter type t drop attribute y cascade, add attribute z int cascade;
> \d is_a
> Table "public.is_a"
> Column | Type | Modifiers
> +-+---
> x | integer |
> z | integer |
> Typed table of type: t
> \d is_a2
> Table "public.is_a2"
> Column | Type | Modifiers
> +-+---
> x | integer |
> y | integer |
> Typed table of type: t
>
> Might be a simple fix; looks like find_typed_table_dependencies() only grabs
> the
> first match.
This is a fairly independent one-liner, so here's that patch. I didn't
incorporate the test case, because it seems distinctly unlikely to recur.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4a97819..bd18db3 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***
*** 4014,4020 find_typed_table_dependencies(Oid typeOid, const char
*typeName, DropBehavior be
scan = heap_beginscan(classRel, SnapshotNow, 1, key);
! if (HeapTupleIsValid(tuple = heap_getnext(scan, ForwardScanDirection)))
{
if (behavior == DROP_RESTRICT)
ereport(ERROR,
--- 4014,4020
scan = heap_beginscan(classRel, SnapshotNow, 1, key);
! while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
if (behavior == DROP_RESTRICT)
ereport(ERROR,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On ons, 2011-04-06 at 11:49 -0400, Noah Misch wrote: > Peter, were you planning to complete this? I can take a swing at it, if it > would be helpful. Help is always welcome. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Tue, Apr 05, 2011 at 09:44:44AM -0400, Robert Haas wrote: > On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch wrote: > > On Wed, Mar 30, 2011 at 07:50:12PM +0300, Peter Eisentraut wrote: > >> Here is a patch that addresses this problem. > > > > This only works when exactly one typed table uses each composite type having > > dropped columns. ?With zero users, the placeholder column never gets > > dropped. > > Actually, it happens to work for >1 user, but only because ALTER TYPE > > mistakenly > > only touches the first table-of-type: > > > > create type t as (x int, y int); > > create table is_a of t; > > create table is_a2 of t; > > alter type t drop attribute y cascade, add attribute z int cascade; > > \d is_a > > ? ? Table "public.is_a" > > ?Column | ?Type ? | Modifiers > > +-+--- > > ?x ? ? ?| integer | > > ?z ? ? ?| integer | > > Typed table of type: t > > \d is_a2 > > ? ? Table "public.is_a2" > > ?Column | ?Type ? | Modifiers > > +-+--- > > ?x ? ? ?| integer | > > ?y ? ? ?| integer | > > Typed table of type: t > > > > Might be a simple fix; looks like find_typed_table_dependencies() only > > grabs the > > first match. ?Incidentally, this led me to notice that you can hang a typed > > table off a table row type. ?ALTER TABLE never propagates to such typed > > tables, > > allowing them to get out of sync: > > > > create table t (x int, y int); > > create table is_a of t; > > create table is_a2 of t; > > alter table t drop y, add z int; > > \d is_a > > ? ? Table "public.is_a" > > ?Column | ?Type ? | Modifiers > > +-+--- > > ?x ? ? ?| integer | > > ?y ? ? ?| integer | > > Typed table of type: t > > > > Perhaps we should disallow the use of table row types in CREATE TABLE ... > > OF? > > > >> It looks like Noah Misch might have found another problem in this area. > >> We'll have to investigate that. > > > > Your bits in dumpCompositeType() are most of what's needed to fix that, I > > think. > > Where are we on this? Peter, were you planning to complete this? I can take a swing at it, if it would be helpful. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch wrote: > On Wed, Mar 30, 2011 at 07:50:12PM +0300, Peter Eisentraut wrote: >> On tor, 2011-02-10 at 06:31 +0200, Peter Eisentraut wrote: >> > > ERROR: cannot drop column from typed table >> > > >> > > which probably is because test_type2 has a dropped column. >> > >> > It should call >> > >> > ALTER TYPE test_type2 DROP ATTRIBUTE xyz CASCADE; >> > >> > instead. That will propagate to the table. >> >> Here is a patch that addresses this problem. > > This only works when exactly one typed table uses each composite type having > dropped columns. With zero users, the placeholder column never gets dropped. > Actually, it happens to work for >1 user, but only because ALTER TYPE > mistakenly > only touches the first table-of-type: > > create type t as (x int, y int); > create table is_a of t; > create table is_a2 of t; > alter type t drop attribute y cascade, add attribute z int cascade; > \d is_a > Table "public.is_a" > Column | Type | Modifiers > +-+--- > x | integer | > z | integer | > Typed table of type: t > \d is_a2 > Table "public.is_a2" > Column | Type | Modifiers > +-+--- > x | integer | > y | integer | > Typed table of type: t > > Might be a simple fix; looks like find_typed_table_dependencies() only grabs > the > first match. Incidentally, this led me to notice that you can hang a typed > table off a table row type. ALTER TABLE never propagates to such typed > tables, > allowing them to get out of sync: > > create table t (x int, y int); > create table is_a of t; > create table is_a2 of t; > alter table t drop y, add z int; > \d is_a > Table "public.is_a" > Column | Type | Modifiers > +-+--- > x | integer | > y | integer | > Typed table of type: t > > Perhaps we should disallow the use of table row types in CREATE TABLE ... OF? > >> It looks like Noah Misch might have found another problem in this area. >> We'll have to investigate that. > > Your bits in dumpCompositeType() are most of what's needed to fix that, I > think. Where are we on this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 07:50:12PM +0300, Peter Eisentraut wrote: > On tor, 2011-02-10 at 06:31 +0200, Peter Eisentraut wrote: > > > ERROR: cannot drop column from typed table > > > > > > which probably is because test_type2 has a dropped column. > > > > It should call > > > > ALTER TYPE test_type2 DROP ATTRIBUTE xyz CASCADE; > > > > instead. That will propagate to the table. > > Here is a patch that addresses this problem. This only works when exactly one typed table uses each composite type having dropped columns. With zero users, the placeholder column never gets dropped. Actually, it happens to work for >1 user, but only because ALTER TYPE mistakenly only touches the first table-of-type: create type t as (x int, y int); create table is_a of t; create table is_a2 of t; alter type t drop attribute y cascade, add attribute z int cascade; \d is_a Table "public.is_a" Column | Type | Modifiers +-+--- x | integer | z | integer | Typed table of type: t \d is_a2 Table "public.is_a2" Column | Type | Modifiers +-+--- x | integer | y | integer | Typed table of type: t Might be a simple fix; looks like find_typed_table_dependencies() only grabs the first match. Incidentally, this led me to notice that you can hang a typed table off a table row type. ALTER TABLE never propagates to such typed tables, allowing them to get out of sync: create table t (x int, y int); create table is_a of t; create table is_a2 of t; alter table t drop y, add z int; \d is_a Table "public.is_a" Column | Type | Modifiers +-+--- x | integer | y | integer | Typed table of type: t Perhaps we should disallow the use of table row types in CREATE TABLE ... OF? > It looks like Noah Misch might have found another problem in this area. > We'll have to investigate that. Your bits in dumpCompositeType() are most of what's needed to fix that, I think. Thanks, nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On tor, 2011-02-10 at 06:31 +0200, Peter Eisentraut wrote:
> > ERROR: cannot drop column from typed table
> >
> > which probably is because test_type2 has a dropped column.
>
> It should call
>
> ALTER TYPE test_type2 DROP ATTRIBUTE xyz CASCADE;
>
> instead. That will propagate to the table.
Here is a patch that addresses this problem.
It looks like Noah Misch might have found another problem in this area.
We'll have to investigate that.
diff --git i/src/bin/pg_dump/pg_dump.c w/src/bin/pg_dump/pg_dump.c
index 5561295..4cea954 100644
--- i/src/bin/pg_dump/pg_dump.c
+++ w/src/bin/pg_dump/pg_dump.c
@@ -7889,6 +7889,7 @@ dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
int ntups;
int i_attname;
int i_atttypdefn;
+ int i_attisdropped;
int i_typrelid;
int i;
@@ -7900,11 +7901,11 @@ dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
appendPQExpBuffer(query, "SELECT a.attname, "
"pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, "
+ "a.attisdropped, "
"typrelid "
"FROM pg_catalog.pg_type t, pg_catalog.pg_attribute a "
"WHERE t.oid = '%u'::pg_catalog.oid "
"AND a.attrelid = t.typrelid "
- "AND NOT a.attisdropped "
"ORDER BY a.attnum ",
tyinfo->dobj.catId.oid);
@@ -7915,6 +7916,7 @@ dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
i_attname = PQfnumber(res, "attname");
i_atttypdefn = PQfnumber(res, "atttypdefn");
+ i_attisdropped = PQfnumber(res, "attisdropped");
i_typrelid = PQfnumber(res, "typrelid");
if (binary_upgrade)
@@ -7932,11 +7934,20 @@ dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
{
char *attname;
char *atttypdefn;
+ bool attisdropped;
attname = PQgetvalue(res, i, i_attname);
atttypdefn = PQgetvalue(res, i, i_atttypdefn);
+ attisdropped = (PQgetvalue(res, i, i_attisdropped)[0] == 't');
- appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
+ if (attisdropped)
+ {
+ if (binary_upgrade)
+/* see under dumpTableSchema() */
+appendPQExpBuffer(q, "\n\t%s INTEGER /* dummy */", fmtId(attname));
+ }
+ else
+ appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
if (i < ntups - 1)
appendPQExpBuffer(q, ",");
}
@@ -12105,14 +12116,26 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
tbinfo->attlen[j],
tbinfo->attalign[j]);
appendStringLiteralAH(q, tbinfo->attnames[j], fout);
- appendPQExpBuffer(q, "\n AND attrelid = ");
+ appendPQExpBuffer(q, "\n AND attrelid IN (");
appendStringLiteralAH(q, fmtId(tbinfo->dobj.name), fout);
- appendPQExpBuffer(q, "::pg_catalog.regclass;\n");
+ appendPQExpBuffer(q, "::pg_catalog.regclass");
+ if (tbinfo->reloftype)
+ appendPQExpBuffer(q, ", '%s'::pg_catalog.regclass", tbinfo->reloftype);
+ appendPQExpBuffer(q, ");\n");
- appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
- fmtId(tbinfo->dobj.name));
- appendPQExpBuffer(q, "DROP COLUMN %s;\n",
- fmtId(tbinfo->attnames[j]));
+ if (tbinfo->reloftype)
+ {
+ appendPQExpBuffer(q, "ALTER TYPE %s ",
+ tbinfo->reloftype);
+ appendPQExpBuffer(q, "DROP ATTRIBUTE %s CASCADE;\n",
+ fmtId(tbinfo->attnames[j]));
+ }
+ else {
+ appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
+ fmtId(tbinfo->dobj.name));
+ appendPQExpBuffer(q, "DROP COLUMN %s;\n",
+ fmtId(tbinfo->attnames[j]));
+ }
}
else if (!tbinfo->attislocal[j])
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Fri, Mar 11, 2011 at 8:28 AM, Bruce Momjian wrote: > Is this still an open bug? Is anyone working on fixing this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
Is this still an open bug? --- Tom Lane wrote: > I find that pg_upgrade fails in HEAD when asked to do a 9.1-to-9.1 > upgrade of the regression database. It gets to this bit of the > restore script: > > CREATE TABLE test_tbl2 OF public.test_type2; > > -- For binary upgrade, recreate dropped column. > UPDATE pg_catalog.pg_attribute > SET attlen = -1, attalign = 'i', attbyval = false > WHERE attname = 'pg.dropped.2' > AND attrelid = 'test_tbl2'::pg_catalog.regclass; > ALTER TABLE ONLY test_tbl2 DROP COLUMN "pg.dropped.2"; > > and fails with > > ERROR: cannot drop column from typed table > > which probably is because test_type2 has a dropped column. > > Somebody has failed to think through something, because if this state of > affairs was allowed to be created during the regression tests, why > should we not be able to restore it? > > (pg_upgrade's ENUM support is broken too, but at least that one is a > one-line fix.) > > regards, tom lane -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On ons, 2011-02-09 at 23:16 -0500, Bruce Momjian wrote: > I am not aware of this code changing in 9.1. Was this test in 9.0? > Does this problem happen for 9.0? No, because you can't drop anything from a typed table in 9.0. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On ons, 2011-02-09 at 18:43 -0500, Tom Lane wrote: > I find that pg_upgrade fails in HEAD when asked to do a 9.1-to-9.1 > upgrade of the regression database. It gets to this bit of the > restore script: > > CREATE TABLE test_tbl2 OF public.test_type2; > > -- For binary upgrade, recreate dropped column. > UPDATE pg_catalog.pg_attribute > SET attlen = -1, attalign = 'i', attbyval = false > WHERE attname = 'pg.dropped.2' > AND attrelid = 'test_tbl2'::pg_catalog.regclass; > ALTER TABLE ONLY test_tbl2 DROP COLUMN "pg.dropped.2"; > > and fails with > > ERROR: cannot drop column from typed table > > which probably is because test_type2 has a dropped column. It should call ALTER TYPE test_type2 DROP ATTRIBUTE xyz CASCADE; instead. That will propagate to the table. I'm not sure though, whether a composite type preserves the dropped attribute for re-dropping in this case. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
Tom Lane wrote: > I find that pg_upgrade fails in HEAD when asked to do a 9.1-to-9.1 > upgrade of the regression database. It gets to this bit of the > restore script: > > CREATE TABLE test_tbl2 OF public.test_type2; > > -- For binary upgrade, recreate dropped column. > UPDATE pg_catalog.pg_attribute > SET attlen = -1, attalign = 'i', attbyval = false > WHERE attname = 'pg.dropped.2' > AND attrelid = 'test_tbl2'::pg_catalog.regclass; > ALTER TABLE ONLY test_tbl2 DROP COLUMN "pg.dropped.2"; > > and fails with > > ERROR: cannot drop column from typed table > > which probably is because test_type2 has a dropped column. > > Somebody has failed to think through something, because if this state of > affairs was allowed to be created during the regression tests, why > should we not be able to restore it? I am not aware of this code changing in 9.1. Was this test in 9.0? Does this problem happen for 9.0? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Typed-tables patch broke pg_upgrade
I find that pg_upgrade fails in HEAD when asked to do a 9.1-to-9.1 upgrade of the regression database. It gets to this bit of the restore script: CREATE TABLE test_tbl2 OF public.test_type2; -- For binary upgrade, recreate dropped column. UPDATE pg_catalog.pg_attribute SET attlen = -1, attalign = 'i', attbyval = false WHERE attname = 'pg.dropped.2' AND attrelid = 'test_tbl2'::pg_catalog.regclass; ALTER TABLE ONLY test_tbl2 DROP COLUMN "pg.dropped.2"; and fails with ERROR: cannot drop column from typed table which probably is because test_type2 has a dropped column. Somebody has failed to think through something, because if this state of affairs was allowed to be created during the regression tests, why should we not be able to restore it? (pg_upgrade's ENUM support is broken too, but at least that one is a one-line fix.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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 signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Typed tables
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
On Tue, Jan 12, 2010 at 9:00 AM, Andrew Dunstan 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
2010/1/12 Peter Eisentraut : > 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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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 wolvesconstraints? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
On Mon, Nov 9, 2009 at 5:15 AM, Peter Eisentraut wrote: > On Sun, 2009-11-08 at 21:17 +, 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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.
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 97e95ed..37c6543 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -4750,19 +4750,29 @@ ORDER BY c.ordinal_position;
user_defined_type_catalog
sql_identifier
- Applies to a feature not available in PostgreSQL
+
+ If the table is a typed table, the name of the database that
+ contains the underlying data type (always the current
+ database), else null.
+
user_defined_type_schema
sql_identifier
- Applies to a feature not available in PostgreSQL
+
+ If the table is a typed table, the name of the schema that
+ contains the underlying data type, else null.
+
user_defined_type_name
sql_identifier
- Applies to a feature not available in PostgreSQL
+
+ If the table is a typed table, the name of the underlying data
+ type, else null.
+
@@ -4778,7 +4788,7 @@ ORDER BY c.ordinal_position;
is_typed
yes_or_no
- Applies to a feature not available in PostgreSQL
+ YES if the table is a typed table, NO if not
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 43764f1..28eb52e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -32,6 +32,16 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE tablespace ]
+CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
+OF type_name [ (
+ { column_name WITH OPTIONS [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
+| table_constraint }
+[, ... ]
+) ]
+[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
+[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
+[ TABLESPACE tablespace ]
+
where column_constraint is:
[ CONSTRAINT constraint_name ]
@@ -154,6 +164,27 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE type_name
+
+
+ Creates a typed table, which takes its
+ structure from the specified composite type (name optionally
+ schema-qualified). A typed table is tied to its type; for
+ example the table will be dropped if the type is dropped
+ (with DROP TYPE ... CASCADE).
+
+
+
+ When a typed table is created, then the data types of the
+ columns are determined by the underlying composite type and are
+ not specified by the CREATE TABLE command.
+ But the CREATE TABLE command can add defaults
+ and constraints to the table and can specify storage parameters.
+
+
+
+
+
column_name
@@ -1182,6 +1213,17 @@ CREATE TABLE cinemas (
+
+ Create a composite type and a typed table:
+
+CREATE TYPE employee_type AS (name text, salary numeric);
+
+CREATE TABLE employees OF employee_type (
+PRIMARY KEY (name),
+salary WITH OPTIONS DEFAULT 1000
+);
+
+
@@ -1331,6 +1373,19 @@ CREATE TABLE cinemas (
and USING INDEX TABLESPACE are extensions.
+
+
+ Typed Tables
+
+
+Typed tables implement a subset of the SQL standard. According to
+the standard, a typed table has columns corresponding to the
+underlying composite type as well as one other column that is
+the self-referencing column. PostgreSQL does not
+support these self-referencing columns explicitly, but the same
+effect can be had using the OID feature.
+
+
@@ -1341,6 +1396,7 @@ CREATE TABLE cinemas (
+
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index b37ee39..7ec96de 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -217,6 +217,7 @@ Boot_CreateStmt:
$5 ? GLOBALTABLESPACE_OID : 0,
$3,
$7,
+ InvalidOid,
BOOTSTRAP_SUPERUSERID,
tupdesc,
NIL,
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 81cd8c5..dfe5911 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -72,7 +72,9 @@
static void AddNewRelationTuple(Relation pg_class_desc,
Relation new_rel_desc,
- Oid new_
Re: [HACKERS] Typed tables
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
On Sun, 2009-11-08 at 21:17 +, 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
> > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
Peter Eisentraut 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
Merlin Moncure wrote: > On Thu, Nov 5, 2009 at 12:24 PM, 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) ); > > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
On Thu, Nov 5, 2009 at 12:24 PM, 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) ); 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
On tor, 2009-11-05 at 12:38 -0500, Tom Lane wrote: > Peter Eisentraut 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed tables
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
