On Fri, Apr 15, 2011 at 11:58:30AM -0400, Noah Misch wrote:
> When we're done with the relkind-restriction patch, I'll post a new version of
> this one. It will remove the circularity check and add a relkind check.
Here it is. Changes from tt1v1-alter-of.patch to tt1v2-alter-of.patch:
* Use transformOfType()'s relkind check in ATExecAddOf()
* Remove circularity check
* Open pg_inherits with AccessShareLock
* Fix terminology in ATExecDropOf() comment
* Rebase over pgindent changes
Changes from tt2v1-binary-upgrade.patch to tt2v2-binary-upgrade.patch:
* Rebase over dumpCompositeType() changes from commit acfa1f45
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index c194862..4e02438 100644
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
***************
*** 63,68 **** ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
--- 63,70 ----
RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [,
... ] )
INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
+ OF <replaceable class="PARAMETER">type_name</replaceable>
+ NOT OF
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
***************
*** 491,496 **** ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
--- 493,522 ----
</varlistentry>
<varlistentry>
+ <term><literal>OF <replaceable
class="PARAMETER">type_name</replaceable></literal></term>
+ <listitem>
+ <para>
+ This form links the table to a composite type as though <command>CREATE
+ TABLE OF</> had formed it. The table's list of column names and types
+ must precisely match that of the composite type; the presence of
+ an <literal>oid</> system column is permitted to differ. The table must
+ not inherit from any other table. These restrictions ensure
+ that <command>CREATE TABLE OF</> would permit an equivalent table
+ definition.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>NOT OF</literal></term>
+ <listitem>
+ <para>
+ This form dissociates a typed table from its type.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>OWNER</literal></term>
<listitem>
<para>
diff --git a/src/backend/commands/tablecindex 1f709a4..f0e6f24 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 81,86 ****
--- 81,87 ----
#include "utils/snapmgr.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
+ #include "utils/typcache.h"
/*
***************
*** 357,362 **** static void ATExecEnableDisableRule(Relation rel, char
*rulename,
--- 358,366 ----
static void ATPrepAddInherit(Relation child_rel);
static void ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE
lockmode);
static void ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE
lockmode);
+ static void drop_parent_dependency(Oid relid, Oid refclassid, Oid refobjid);
+ static void ATExecAddOf(Relation rel, const TypeName *ofTypename, LOCKMODE
lockmode);
+ static void ATExecDropOf(Relation rel, LOCKMODE lockmode);
static void ATExecGenericOptions(Relation rel, List *options);
static void copy_relation_data(SMgrRelation rel, SMgrRelation dst,
***************
*** 2684,2689 **** AlterTableGetLockLevel(List *cmds)
--- 2688,2703 ----
break;
/*
+ * These subcommands affect implicit row type
conversion. They
+ * have affects similar to CREATE/DROP CAST on
queries. We
+ * don't provide for invalidating parse trees
as a result of
+ * such changes. Do avoid concurrent pg_class
updates, though.
+ */
+ case AT_AddOf:
+ case AT_DropOf:
+ cmd_lockmode = ShareUpdateExclusiveLock;
+
+ /*
* These subcommands affect general strategies
for performance
* and maintenance, though don't change the
semantic results
* from normal data reads and writes. Delaying
an ALTER TABLE
***************
*** 2942,2954 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_EnableAlwaysRule:
case AT_EnableReplicaRule:
case AT_DisableRule:
- ATSimplePermissions(rel, ATT_TABLE);
- /* These commands never recurse */
- /* No command-specific prep needed */
- pass = AT_PASS_MISC;
- break;
case AT_DropInherit: /* NO INHERIT */
ATSimplePermissions(rel, ATT_TABLE);
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
--- 2956,2966 ----
case AT_EnableAlwaysRule:
case AT_EnableReplicaRule:
case AT_DisableRule:
case AT_DropInherit: /* NO INHERIT */
+ case AT_AddOf: /* OF */
+ case AT_DropOf: /* NOT OF */
ATSimplePermissions(rel, ATT_TABLE);
+ /* These commands never recurse */
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
***************
*** 3211,3216 **** ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
--- 3223,3234 ----
case AT_DropInherit:
ATExecDropInherit(rel, (RangeVar *) cmd->def, lockmode);
break;
+ case AT_AddOf:
+ ATExecAddOf(rel, (TypeName *) cmd->def, lockmode);
+ break;
+ case AT_DropOf:
+ ATExecDropOf(rel, lockmode);
+ break;
case AT_GenericOptions:
ATExecGenericOptions(rel, (List *) cmd->def);
break;
***************
*** 4046,4051 **** find_typed_table_dependencies(Oid typeOid, const char
*typeName, DropBehavior be
--- 4064,4105 ----
/*
+ * check_of_type
+ *
+ * Check whether a type is suitable for CREATE TABLE OF/ALTER TABLE OF. If it
+ * isn't suitable, throw an error. Currently, we require that the type
+ * originated with CREATE TABLE AS. We could support any row type, but doing
so
+ * would require handling a number of extra corner cases in the DDL commands.
+ */
+ void
+ check_of_type(HeapTuple typetuple)
+ {
+ Form_pg_type typ = (Form_pg_type) GETSTRUCT(typetuple);
+ bool typeOk = false;
+
+ if (typ->typtype == TYPTYPE_COMPOSITE)
+ {
+ Relation typeRelation;
+
+ Assert(OidIsValid(typ->typrelid));
+ typeRelation = relation_open(typ->typrelid, AccessShareLock);
+ typeOk = (typeRelation->rd_rel->relkind ==
RELKIND_COMPOSITE_TYPE);
+ /*
+ * Close the parent rel, but keep our AccessShareLock on it
until xact
+ * commit. That will prevent someone else from deleting or
ALTERing
+ * the type before the typed table creation/conversion commits.
+ */
+ relation_close(typeRelation, NoLock);
+ }
+ if (!typeOk)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("type %s is not a composite type",
+
format_type_be(HeapTupleGetOid(typetuple)))));
+ }
+
+
+ /*
* ALTER TABLE ADD COLUMN
*
* Adds an additional attribute to a relation making the assumption that
***************
*** 8355,8362 **** ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE
lockmode)
ScanKeyData key[3];
HeapTuple inheritsTuple,
attributeTuple,
! constraintTuple,
! depTuple;
List *connames;
bool found = false;
--- 8409,8415 ----
ScanKeyData key[3];
HeapTuple inheritsTuple,
attributeTuple,
! constraintTuple;
List *connames;
bool found = false;
***************
*** 8522,8532 **** ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE
lockmode)
systable_endscan(scan);
heap_close(catalogRelation, RowExclusiveLock);
! /*
! * Drop the dependency
! *
! * There's no convenient way to do this, so go trawling through
pg_depend
! */
catalogRelation = heap_open(DependRelationId, RowExclusiveLock);
ScanKeyInit(&key[0],
--- 8575,8603 ----
systable_endscan(scan);
heap_close(catalogRelation, RowExclusiveLock);
! drop_parent_dependency(RelationGetRelid(rel),
! RelationRelationId,
!
RelationGetRelid(parent_rel));
!
! /* keep our lock on the parent relation until commit */
! heap_close(parent_rel, NoLock);
! }
!
! /*
! * Drop the dependency created by StoreCatalogInheritance1 (CREATE TABLE
! * INHERITS/ALTER TABLE INHERIT -- refclassid will be RelationRelationId) or
! * heap_create_with_catalog (CREATE TABLE OF/ALTER TABLE OF -- refclassid will
! * be TypeRelationId). There's no convenient way to do this, so go trawling
! * through pg_depend.
! */
! static void
! drop_parent_dependency(Oid relid, Oid refclassid, Oid refobjid)
! {
! Relation catalogRelation;
! SysScanDesc scan;
! ScanKeyData key[3];
! HeapTuple depTuple;
!
catalogRelation = heap_open(DependRelationId, RowExclusiveLock);
ScanKeyInit(&key[0],
***************
*** 8536,8542 **** ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE
lockmode)
ScanKeyInit(&key[1],
Anum_pg_depend_objid,
BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(RelationGetRelid(rel)));
ScanKeyInit(&key[2],
Anum_pg_depend_objsubid,
BTEqualStrategyNumber, F_INT4EQ,
--- 8607,8613 ----
ScanKeyInit(&key[1],
Anum_pg_depend_objid,
BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
ScanKeyInit(&key[2],
Anum_pg_depend_objsubid,
BTEqualStrategyNumber, F_INT4EQ,
***************
*** 8549,8556 **** ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE
lockmode)
{
Form_pg_depend dep = (Form_pg_depend) GETSTRUCT(depTuple);
! if (dep->refclassid == RelationRelationId &&
! dep->refobjid == RelationGetRelid(parent_rel) &&
dep->refobjsubid == 0 &&
dep->deptype == DEPENDENCY_NORMAL)
simple_heap_delete(catalogRelation, &depTuple->t_self);
--- 8620,8627 ----
{
Form_pg_depend dep = (Form_pg_depend) GETSTRUCT(depTuple);
! if (dep->refclassid == refclassid &&
! dep->refobjid == refobjid &&
dep->refobjsubid == 0 &&
dep->deptype == DEPENDENCY_NORMAL)
simple_heap_delete(catalogRelation, &depTuple->t_self);
***************
*** 8558,8566 **** ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE
lockmode)
systable_endscan(scan);
heap_close(catalogRelation, RowExclusiveLock);
! /* keep our lock on the parent relation until commit */
! heap_close(parent_rel, NoLock);
}
/*
--- 8629,8807 ----
systable_endscan(scan);
heap_close(catalogRelation, RowExclusiveLock);
+ }
! /*
! * ALTER TABLE OF
! *
! * Attach a table to a composite type, as though it had been created with
CREATE
! * TABLE OF. All attname, atttypid, atttypmod and attcollation must match.
The
! * subject table must not have inheritance parents. These restrictions ensure
! * that you cannot create a configuration impossible with CREATE TABLE OF
alone.
! */
! static void
! ATExecAddOf(Relation rel, const TypeName *ofTypename, LOCKMODE lockmode)
! {
! Oid relid = RelationGetRelid(rel);
! Type typetuple;
! Form_pg_type typ;
! Oid typeid;
! Relation inheritsRelation,
! relationRelation;
! SysScanDesc scan;
! ScanKeyData key;
! AttrNumber table_attno,
! type_attno;
! TupleDesc typeTupleDesc,
! tableTupleDesc;
! ObjectAddress tableobj,
! typeobj;
! HeapTuple classtuple;
!
! /* Validate the type. */
! typetuple = typenameType(NULL, ofTypename, NULL);
! check_of_type(typetuple);
! typ = (Form_pg_type) GETSTRUCT(typetuple);
! typeid = HeapTupleGetOid(typetuple);
!
! /* Fail if the table has any inheritance parents. */
! inheritsRelation = heap_open(InheritsRelationId, AccessShareLock);
! ScanKeyInit(&key,
! Anum_pg_inherits_inhrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
! scan = systable_beginscan(inheritsRelation, InheritsRelidSeqnoIndexId,
! true, SnapshotNow, 1,
&key);
! if (HeapTupleIsValid(systable_getnext(scan)))
! ereport(ERROR,
! (errcode(ERRCODE_WRONG_OBJECT_TYPE),
! errmsg("typed tables cannot inherit")));
! systable_endscan(scan);
! heap_close(inheritsRelation, AccessShareLock);
!
! /*
! * Check the tuple descriptors for compatibility. Unlike inheritance,
we
! * require that the order also match. However, attnotnull need not
match.
! * Also unlike inheritance, we do not require matching relhasoids.
! */
! typeTupleDesc = lookup_rowtype_tupdesc(typeid, -1);
! tableTupleDesc = RelationGetDescr(rel);
! table_attno = 1;
! for (type_attno = 1; type_attno <= typeTupleDesc->natts; type_attno++)
! {
! Form_pg_attribute type_attr,
! table_attr;
! const char *type_attname,
! *table_attname;
!
! /* Get the next non-dropped type attribute. */
! type_attr = typeTupleDesc->attrs[type_attno - 1];
! if (type_attr->attisdropped)
! continue;
! type_attname = NameStr(type_attr->attname);
!
! /* Get the next non-dropped table attribute. */
! do
! {
! if (table_attno > tableTupleDesc->natts)
! ereport(ERROR,
!
(errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("table is missing
column \"%s\"",
! type_attname)));
! table_attr = tableTupleDesc->attrs[table_attno++ - 1];
! } while (table_attr->attisdropped);
! table_attname = NameStr(table_attr->attname);
!
! /* Compare name. */
! if (strncmp(table_attname, type_attname, NAMEDATALEN) != 0)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("table has column \"%s\" where
type requires \"%s\"",
! table_attname,
type_attname)));
!
! /* Compare type. */
! if (table_attr->atttypid != type_attr->atttypid ||
! table_attr->atttypmod != type_attr->atttypmod ||
! table_attr->attcollation != type_attr->attcollation)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("table \"%s\" has different
type for column \"%s\"",
!
RelationGetRelationName(rel), type_attname)));
! }
! DecrTupleDescRefCount(typeTupleDesc);
!
! /* Any remaining columns at the end of the table had better be dropped.
*/
! for (; table_attno <= tableTupleDesc->natts; table_attno++)
! {
! Form_pg_attribute table_attr =
tableTupleDesc->attrs[table_attno - 1];
! if (!table_attr->attisdropped)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("table has extra column \"%s\"",
!
NameStr(table_attr->attname))));
! }
!
! /* If the table was already typed, drop the existing dependency. */
! if (rel->rd_rel->reloftype)
! drop_parent_dependency(relid, TypeRelationId,
rel->rd_rel->reloftype);
!
! /* Record a dependency on the new type. */
! tableobj.classId = RelationRelationId;
! tableobj.objectId = relid;
! tableobj.objectSubId = 0;
! typeobj.classId = TypeRelationId;
! typeobj.objectId = typeid;
! typeobj.objectSubId = 0;
! recordDependencyOn(&tableobj, &typeobj, DEPENDENCY_NORMAL);
!
! /* Update pg_class.reloftype */
! relationRelation = heap_open(RelationRelationId, RowExclusiveLock);
! classtuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
! if (!HeapTupleIsValid(classtuple))
! elog(ERROR, "cache lookup failed for relation %u", relid);
! ((Form_pg_class) GETSTRUCT(classtuple))->reloftype = typeid;
! simple_heap_update(relationRelation, &classtuple->t_self, classtuple);
! heap_freetuple(classtuple);
! heap_close(relationRelation, RowExclusiveLock);
!
! ReleaseSysCache(typetuple);
! }
!
! /*
! * ALTER TABLE NOT OF
! *
! * Detach a typed table from its originating type. Just clear reloftype and
! * remove the dependency.
! */
! static void
! ATExecDropOf(Relation rel, LOCKMODE lockmode)
! {
! Oid relid = RelationGetRelid(rel);
! Relation relationRelation;
! HeapTuple tuple;
!
! if (!OidIsValid(rel->rd_rel->reloftype))
! ereport(ERROR,
! (errcode(ERRCODE_WRONG_OBJECT_TYPE),
! errmsg("\"%s\" is not a typed table",
! RelationGetRelationName(rel))));
!
! /*
! * We don't bother to check ownership of the type --- ownership of the
table
! * is presumed enough rights. No lock required on the type, either.
! */
!
! drop_parent_dependency(relid, TypeRelationId, rel->rd_rel->reloftype);
!
! /* Clear pg_class.reloftype */
! relationRelation = heap_open(RelationRelationId, RowExclusiveLock);
! tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
! if (!HeapTupleIsValid(tuple))
! elog(ERROR, "cache lookup failed for relation %u", relid);
! ((Form_pg_class) GETSTRUCT(tuple))->reloftype = InvalidOid;
! simple_heap_update(relationRelation, &tuple->t_self, tuple);
! heap_freetuple(tuple);
! heap_close(relationRelation, RowExclusiveLock);
}
/*
diff --git a/src/backend/parser/gram.y index a22ab66..1e4f8f6 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 1933,1938 **** alter_table_cmd:
--- 1933,1955 ----
n->def = (Node *) $3;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> OF <type_name> */
+ | OF any_name
+ {
+ AlterTableCmd *n =
makeNode(AlterTableCmd);
+ TypeName *def =
makeTypeNameFromNameList($2);
+ def->location = @2;
+ n->subtype = AT_AddOf;
+ n->def = (Node *) def;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> NOT OF */
+ | NOT OF
+ {
+ AlterTableCmd *n =
makeNode(AlterTableCmd);
+ n->subtype = AT_DropOf;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> OWNER TO RoleId */
| OWNER TO RoleId
{
diff --git a/src/backend/parser/index 4f1bb34..0078814 100644
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
***************
*** 825,859 **** transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
TupleDesc tupdesc;
int i;
Oid ofTypeId;
- bool typeOk = false;
AssertArg(ofTypename);
tuple = typenameType(NULL, ofTypename, NULL);
typ = (Form_pg_type) GETSTRUCT(tuple);
ofTypeId = HeapTupleGetOid(tuple);
ofTypename->typeOid = ofTypeId; /* cached for later */
- if (typ->typtype == TYPTYPE_COMPOSITE)
- {
- Relation typeRelation;
-
- Assert(OidIsValid(typ->typrelid));
- typeRelation = relation_open(typ->typrelid, AccessShareLock);
- typeOk = (typeRelation->rd_rel->relkind ==
RELKIND_COMPOSITE_TYPE);
- /*
- * Close the parent rel, but keep our AccessShareLock on it
until xact
- * commit. That will prevent someone else from deleting or
ALTERing
- * the type before the typed table creation commits.
- */
- relation_close(typeRelation, NoLock);
- }
- if (!typeOk)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("type %s is not a composite type",
- format_type_be(ofTypeId))));
-
tupdesc = lookup_rowtype_tupdesc(ofTypeId, -1);
for (i = 0; i < tupdesc->natts; i++)
{
--- 825,839 ----
TupleDesc tupdesc;
int i;
Oid ofTypeId;
AssertArg(ofTypename);
tuple = typenameType(NULL, ofTypename, NULL);
+ check_of_type(tuple);
typ = (Form_pg_type) GETSTRUCT(tuple);
ofTypeId = HeapTupleGetOid(tuple);
ofTypename->typeOid = ofTypeId; /* cached for later */
tupdesc = lookup_rowtype_tupdesc(ofTypeId, -1);
for (i = 0; i < tupdesc->natts; i++)
{
diff --git a/src/include/commands/tablecmindex d438352..3f971eb 100644
*** a/src/include/commands/tablecmds.h
--- b/src/include/commands/tablecmds.h
***************
*** 56,61 **** extern void find_composite_type_dependencies(Oid typeOid,
--- 56,63 ----
Relation
origRelation,
const char
*origTypeName);
+ extern void check_of_type(HeapTuple typetuple);
+
extern AttrNumber *varattnos_map(TupleDesc olddesc, TupleDesc newdesc);
extern AttrNumber *varattnos_map_schema(TupleDesc old, List *schema);
extern void change_varattnos_of_a_node(Node *node, const AttrNumber
*newattno);
diff --git a/src/include/nodes/parsenodindex c6337cf..24b4f72 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 1219,1224 **** typedef enum AlterTableType
--- 1219,1226 ----
AT_DisableRule, /* DISABLE RULE name */
AT_AddInherit, /* INHERIT parent */
AT_DropInherit, /* NO INHERIT parent */
+ AT_AddOf, /* OF <type_name> */
+ AT_DropOf, /* NOT OF */
AT_GenericOptions, /* OPTIONS (...) */
} AlterTableType;
diff --git a/src/test/regress/expecteindex 5b1223b..8344d85 100644
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
***************
*** 1942,1944 **** Typed table of type: test_type2
--- 1942,1982 ----
CREATE TYPE test_type_empty AS ();
DROP TYPE test_type_empty;
+ --
+ -- typed tables: OF / NOT OF
+ --
+ CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
+ ALTER TYPE tt_t0 DROP ATTRIBUTE z;
+ CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
+ CREATE TABLE tt1 (x int, y bigint); --
wrong base type
+ CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
+ CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column
order
+ CREATE TABLE tt4 (x int);
-- too few columns
+ CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
+ CREATE TABLE tt6 () INHERITS (tt0); --
can't have a parent
+ CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
+ ALTER TABLE tt7 DROP q;
-- OK
+ ALTER TABLE tt0 OF tt_t0;
+ ALTER TABLE tt1 OF tt_t0;
+ ERROR: table "tt1" has different type for column "y"
+ ALTER TABLE tt2 OF tt_t0;
+ ERROR: table "tt2" has different type for column "y"
+ ALTER TABLE tt3 OF tt_t0;
+ ERROR: table has column "y" where type requires "x"
+ ALTER TABLE tt4 OF tt_t0;
+ ERROR: table is missing column "y"
+ ALTER TABLE tt5 OF tt_t0;
+ ERROR: table has extra column "z"
+ ALTER TABLE tt6 OF tt_t0;
+ ERROR: typed tables cannot inherit
+ ALTER TABLE tt7 OF tt_t0;
+ CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
+ ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed
table
+ ALTER TABLE tt7 NOT OF;
+ \d tt7
+ Table "public.tt7"
+ Column | Type | Modifiers
+ --------+--------------+-----------
+ x | integer |
+ y | numeric(8,2) |
+
diff --git a/src/test/regress/sql/alter_table.sqindex 43a9ce9..25fa7d5 100644
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
***************
*** 1369,1371 **** ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
--- 1369,1401 ----
CREATE TYPE test_type_empty AS ();
DROP TYPE test_type_empty;
+
+ --
+ -- typed tables: OF / NOT OF
+ --
+
+ CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
+ ALTER TYPE tt_t0 DROP ATTRIBUTE z;
+ CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2)); -- OK
+ CREATE TABLE tt1 (x int, y bigint); --
wrong base type
+ CREATE TABLE tt2 (x int, y numeric(9,2)); -- wrong typmod
+ CREATE TABLE tt3 (y numeric(8,2), x int); -- wrong column
order
+ CREATE TABLE tt4 (x int);
-- too few columns
+ CREATE TABLE tt5 (x int, y numeric(8,2), z int); -- too few columns
+ CREATE TABLE tt6 () INHERITS (tt0); --
can't have a parent
+ CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
+ ALTER TABLE tt7 DROP q;
-- OK
+
+ ALTER TABLE tt0 OF tt_t0;
+ ALTER TABLE tt1 OF tt_t0;
+ ALTER TABLE tt2 OF tt_t0;
+ ALTER TABLE tt3 OF tt_t0;
+ ALTER TABLE tt4 OF tt_t0;
+ ALTER TABLE tt5 OF tt_t0;
+ ALTER TABLE tt6 OF tt_t0;
+ ALTER TABLE tt7 OF tt_t0;
+
+ CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
+ ALTER TABLE tt7 OF tt_t1; -- reassign an already-typed
table
+ ALTER TABLE tt7 NOT OF;
+ \d tt7
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c2f6180..1c6205b 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
***************
*** 7937,7942 **** static void
--- 7937,7943 ----
dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
{
PQExpBuffer q = createPQExpBuffer();
+ PQExpBuffer dropped = createPQExpBuffer();
PQExpBuffer delq = createPQExpBuffer();
PQExpBuffer labelq = createPQExpBuffer();
PQExpBuffer query = createPQExpBuffer();
***************
*** 7944,7952 **** dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
--- 7945,7957 ----
int ntups;
int i_attname;
int i_atttypdefn;
+ int i_attlen;
+ int i_attalign;
+ int i_attisdropped;
int i_attcollation;
int i_typrelid;
int i;
+ int actual_atts;
/* Set proper schema search path so type references list correctly */
selectSourceSchema(tyinfo->dobj.namespace->dobj.name);
***************
*** 7962,7967 **** dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
--- 7967,7973 ----
*/
appendPQExpBuffer(query, "SELECT a.attname, "
"pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, "
+ "a.attlen, a.attalign,
a.attisdropped, "
"CASE WHEN a.attcollation <>
at.typcollation "
"THEN a.attcollation ELSE 0
END AS attcollation, "
"ct.typrelid "
***************
*** 7979,7984 **** dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
--- 7985,7991 ----
/* We assume here that remoteVersion must be at least 70300 */
appendPQExpBuffer(query, "SELECT a.attname, "
"pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, "
+ "a.attlen, a.attalign,
a.attisdropped, "
"0 AS attcollation, "
"ct.typrelid "
"FROM pg_catalog.pg_type ct,
pg_catalog.pg_attribute a "
***************
*** 7996,8001 **** dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
--- 8003,8011 ----
i_attname = PQfnumber(res, "attname");
i_atttypdefn = PQfnumber(res, "atttypdefn");
+ i_attlen = PQfnumber(res, "attlen");
+ i_attalign = PQfnumber(res, "attalign");
+ i_attisdropped = PQfnumber(res, "attisdropped");
i_attcollation = PQfnumber(res, "attcollation");
i_typrelid = PQfnumber(res, "typrelid");
***************
*** 8010,8047 **** dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
appendPQExpBuffer(q, "CREATE TYPE %s AS (",
fmtId(tyinfo->dobj.name));
for (i = 0; i < ntups; i++)
{
char *attname;
char *atttypdefn;
Oid attcollation;
attname = PQgetvalue(res, i, i_attname);
atttypdefn = PQgetvalue(res, i, i_atttypdefn);
attcollation = atooid(PQgetvalue(res, i, i_attcollation));
! appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
! /* Add collation if not default for the column type */
! if (OidIsValid(attcollation))
{
! CollInfo *coll;
! coll = findCollationByOid(attcollation);
! if (coll)
{
! /* always schema-qualify, don't try to be smart
*/
! appendPQExpBuffer(q, " COLLATE %s.",
!
fmtId(coll->dobj.namespace->dobj.name));
! appendPQExpBuffer(q, "%s",
!
fmtId(coll->dobj.name));
}
}
!
! if (i < ntups - 1)
! appendPQExpBuffer(q, ",");
}
appendPQExpBuffer(q, "\n);\n");
/*
* DROP must be fully qualified in case same name appears in pg_catalog
--- 8020,8094 ----
appendPQExpBuffer(q, "CREATE TYPE %s AS (",
fmtId(tyinfo->dobj.name));
+ actual_atts = 0;
for (i = 0; i < ntups; i++)
{
char *attname;
char *atttypdefn;
+ char *attlen;
+ char *attalign;
+ bool attisdropped;
Oid attcollation;
attname = PQgetvalue(res, i, i_attname);
atttypdefn = PQgetvalue(res, i, i_atttypdefn);
+ attlen = PQgetvalue(res, i, i_attlen);
+ attalign = PQgetvalue(res, i, i_attalign);
+ attisdropped = (PQgetvalue(res, i, i_attisdropped)[0] == 't');
attcollation = atooid(PQgetvalue(res, i, i_attcollation));
! if (attisdropped && !binary_upgrade)
! continue;
!
! /* Format properly if not first attr */
! if (actual_atts++ > 0)
! appendPQExpBuffer(q, ",");
! appendPQExpBuffer(q, "\n\t");
! if (!attisdropped)
{
! appendPQExpBuffer(q, "%s %s", fmtId(attname),
atttypdefn);
! /* Add collation if not default for the column type */
! if (OidIsValid(attcollation))
{
! CollInfo *coll;
!
! coll = findCollationByOid(attcollation);
! if (coll)
! {
! /* always schema-qualify, don't try to
be smart */
! appendPQExpBuffer(q, " COLLATE %s.",
!
fmtId(coll->dobj.namespace->dobj.name));
! appendPQExpBuffer(q, "%s",
!
fmtId(coll->dobj.name));
! }
}
}
! else /* binary_upgrade - see under dumpTableSchema() */
! {
! appendPQExpBuffer(q, "%s INTEGER /* dummy */",
fmtId(attname));
!
! /* stash separately for insertion after the CREATE TYPE
*/
! appendPQExpBuffer(dropped,
! "\n-- For binary
upgrade, recreate dropped column.\n");
! appendPQExpBuffer(dropped, "UPDATE
pg_catalog.pg_attribute\n"
! "SET attlen = %s, "
! "attalign = '%s',
attbyval = false\n"
! "WHERE attname = ",
attlen, attalign);
! appendStringLiteralAH(dropped, attname, fout);
! appendPQExpBuffer(dropped, "\n AND attrelid = ");
! appendStringLiteralAH(dropped,
fmtId(tyinfo->dobj.name), fout);
! appendPQExpBuffer(dropped, "::pg_catalog.regclass;\n");
!
! appendPQExpBuffer(dropped, "ALTER TYPE %s ",
!
fmtId(tyinfo->dobj.name));
! appendPQExpBuffer(dropped, "DROP ATTRIBUTE %s;\n",
! fmtId(attname));
! }
}
appendPQExpBuffer(q, "\n);\n");
+ appendPQExpBufferStr(q, dropped->data);
/*
* DROP must be fully qualified in case same name appears in pg_catalog
***************
*** 8077,8082 **** dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
--- 8124,8130 ----
PQclear(res);
destroyPQExpBuffer(q);
+ destroyPQExpBuffer(dropped);
destroyPQExpBuffer(delq);
destroyPQExpBuffer(labelq);
destroyPQExpBuffer(query);
***************
*** 12004,12010 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo)
"UNLOGGED " : "",
reltypename,
fmtId(tbinfo->dobj.name));
! if (tbinfo->reloftype)
appendPQExpBuffer(q, " OF %s", tbinfo->reloftype);
actual_atts = 0;
for (j = 0; j < tbinfo->numatts; j++)
--- 12052,12058 ----
"UNLOGGED " : "",
reltypename,
fmtId(tbinfo->dobj.name));
! if (tbinfo->reloftype && !binary_upgrade)
appendPQExpBuffer(q, " OF %s", tbinfo->reloftype);
actual_atts = 0;
for (j = 0; j < tbinfo->numatts; j++)
***************
*** 12032,12038 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo)
bool has_notnull =
(tbinfo->notnull[j]
&&
(!tbinfo->inhNotNull[j] || binary_upgrade));
! if (tbinfo->reloftype && !has_default &&
!has_notnull)
continue;
/* Format properly if not first attr */
--- 12080,12087 ----
bool has_notnull =
(tbinfo->notnull[j]
&&
(!tbinfo->inhNotNull[j] || binary_upgrade));
! if (tbinfo->reloftype && !binary_upgrade &&
! !has_default && !has_notnull)
continue;
/* Format properly if not first attr */
***************
*** 12060,12066 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo)
}
/* Attribute type */
! if (tbinfo->reloftype)
{
appendPQExpBuffer(q, "WITH OPTIONS");
}
--- 12109,12115 ----
}
/* Attribute type */
! if (tbinfo->reloftype && !binary_upgrade)
{
appendPQExpBuffer(q, "WITH OPTIONS");
}
***************
*** 12126,12132 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo)
if (actual_atts)
appendPQExpBuffer(q, "\n)");
! else if (!tbinfo->reloftype)
{
/*
* We must have a parenthesized attribute list, even
though empty,
--- 12175,12181 ----
if (actual_atts)
appendPQExpBuffer(q, "\n)");
! else if (!(tbinfo->reloftype && !binary_upgrade))
{
/*
* We must have a parenthesized attribute list, even
though empty,
***************
*** 12268,12273 **** dumpTableSchema(Archive *fout, TableInfo *tbinfo)
--- 12317,12329 ----
}
}
+ 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
