Date: Fri, 8 Mar 2024 05:08:09 +0100
From: Erik Wienhold <ewie@ewie.name>
To: Hannu Krosing <hannuk@google.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: CREATE TABLE creates a composite type corresponding to the table
 row, which is and is not there
Message-ID: <iafzaljqlx6bjbbhllavwgkg45lfj2mssm4xbazxezhpiitofe@k4g4fzcr6g2n>
References: 
 <CAMT0RQRysCb_Amy5CTENSc5GfsvXL1a4qX3mv_hx31_v74P==g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="3rqrwxze3hgjbv2c"
Content-Disposition: inline
In-Reply-To: 
 <CAMT0RQRysCb_Amy5CTENSc5GfsvXL1a4qX3mv_hx31_v74P==g@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/iafzaljqlx6bjbbhllavwgkg45lfj2mssm4xbazxezhpiitofe%40k4g4fzcr6g2n>
Precedence: bulk


--3rqrwxze3hgjbv2c
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On 2024-03-08 01:12 +0100, Hannu Krosing wrote:
> I could not find any explanation of the following behaviour in docs -
> 
> 
> Our documentation for CREATE TABLE says:
> 
> CREATE TABLE also automatically creates a data type that represents
> the composite type corresponding to one row of the table. Therefore,
> tables cannot have the same name as any existing data type in the same
> schema.
>
> But these composite tables are only sometimes there

There's a distinction between stand-alone composite types created with CREATE
TYPE and those created implicitly via CREATE TABLE.  The former is also
called "free-standing" in the docs for pg_type.typrelid[1].

> hannuk=# CREATE TABLE pair(a int, b int);
> CREATE TABLE
> 
> hannuk=# INSERT INTO pair VALUES(1,2);
> INSERT 0 1
> 
> hannuk=# select pg_typeof(p) from pair as p;
>  pg_typeof
> -----------
>  pair
> 
> hannuk=# select pg_typeof(pg_typeof(p)) from pair as p;
>  pg_typeof
> -----------
>  regtype
> 
> # first case where I can not use the table-defined type
> 
> hannuk=# create table anoter_pair of pair;
> ERROR:  type pair is not a composite type

That error message is simply misleading.  What gets checked here is that
type "pair" was created with CREATE TYPE.  The attached patch fixes the
error message and also documents that requirement.

check_of_type() already addresses this limitation:

	/*
	 * 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 TYPE AS.  We could support any row type, but doing so
	 * would require handling a number of extra corner cases in the DDL commands.
	 * (Also, allowing domain-over-composite would open up a can of worms about
	 * whether and how the domain's constraints should apply to derived tables.)
	 */

Not sure what those corner cases are, but table inheritance is one of
them:  I played around with typeOk in check_of_type() to also accept the
composite types implicitly created by CREATE TABLE:

	typeOk = (typeRelation->rd_rel->relkind == RELKIND_COMPOSITE_TYPE ||
	          typeRelation->rd_rel->relkind == RELKIND_RELATION);

With that creating typed tables of parent and child works as expected:

	CREATE TABLE parent (a int);
	CREATE TABLE child (b int) INHERITS (parent);
	CREATE TABLE of_parent OF parent;
	CREATE TABLE of_child OF child;
	\d parent
	               Table "public.parent"
	 Column |  Type   | Collation | Nullable | Default 
	--------+---------+-----------+----------+---------
	 a      | integer |           |          | 
	Number of child tables: 1 (Use \d+ to list them.)
	
	\d of_parent
	             Table "public.of_parent"
	 Column |  Type   | Collation | Nullable | Default 
	--------+---------+-----------+----------+---------
	 a      | integer |           |          | 
	Typed table of type: parent
	
	\d child
	               Table "public.child"
	 Column |  Type   | Collation | Nullable | Default 
	--------+---------+-----------+----------+---------
	 a      | integer |           |          | 
	 b      | integer |           |          | 
	Inherits: parent
	
	\d of_child
	              Table "public.of_child"
	 Column |  Type   | Collation | Nullable | Default 
	--------+---------+-----------+----------+---------
	 a      | integer |           |          | 
	 b      | integer |           |          | 
	Typed table of type: child

But adding columns to parent does not change the typed tables:

	ALTER TABLE parent ADD c int;
	\d parent
	               Table "public.parent"
	 Column |  Type   | Collation | Nullable | Default 
	--------+---------+-----------+----------+---------
	 a      | integer |           |          | 
	 c      | integer |           |          | 
	Number of child tables: 1 (Use \d+ to list them.)
	
	\d of_parent
	             Table "public.of_parent"
	 Column |  Type   | Collation | Nullable | Default 
	--------+---------+-----------+----------+---------
	 a      | integer |           |          | 
	Typed table of type: parent
	
	\d child
	               Table "public.child"
	 Column |  Type   | Collation | Nullable | Default 
	--------+---------+-----------+----------+---------
	 a      | integer |           |          | 
	 b      | integer |           |          | 
	 c      | integer |           |          | 
	Inherits: parent
	
	\d of_child
	              Table "public.of_child"
	 Column |  Type   | Collation | Nullable | Default 
	--------+---------+-----------+----------+---------
	 a      | integer |           |          | 
	 b      | integer |           |          | 
	Typed table of type: child

Whereas changing a composite type and its typed tables is possible with
ALTER TYPE ... ADD ATTRIBUTE ... CASCADE.

> # the type definitely is there as promised
> 
> hannuk=# create type pair as (a int, b int);
> ERROR:  type "pair" already exists
> 
> # and I can create similar type wit other name and use it to create table
> 
> hannuk=# create type pair2 as (a int, b int);
> CREATE TYPE
> 
> hannuk=# create table anoter_pair of pair2;
> CREATE TABLE
> 
> # and i can even use it in LIKE
> 
> hannuk=# CREATE TABLE pair3(like pair2);
> CREATE TABLE
> 
> # the type is present in pg_type with type 'c' for Composite
> 
> hannuk=# select typname, typtype from pg_type where typname = 'pair';
>  typname | typtype
> ---------+---------
>  pair    | c
> (1 row)
> 
> # and I can add comment to the type
> 
> hannuk=# COMMENT ON TYPE pair is 'A Shroedingers type';
> COMMENT
> 
> # but \dT does not show it (second case)
> 
> hannuk=# \dT pair
>      List of data types
>  Schema | Name | Description
> --------+------+-------------
> (0 rows)

\dT ignores the composite types implicitly created by CREATE TABLE.

[1] https://www.postgresql.org/docs/16/catalog-pg-type.html

-- 
Erik

--3rqrwxze3hgjbv2c
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename="v1-0001-Document-that-typed-tables-rely-on-CREATE-TYPE.patch"

From f279ee1eccd07da14d0ff49f267f6fceffbd0778 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Fri, 8 Mar 2024 04:21:56 +0100
Subject: [PATCH v1] Document that typed tables rely on CREATE TYPE

CREATE TABLE OF accepts only composite types that were created with
CREATE TYPE.  Clarify that also in the error message.
---
 doc/src/sgml/ref/create_table.sgml        | 2 ++
 src/backend/commands/tablecmds.c          | 8 +++++++-
 src/test/regress/expected/typed_table.out | 6 +++++-
 src/test/regress/sql/typed_table.sql      | 4 ++++
 4 files changed, 18 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 4cbaaccaf7..889496cf0a 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -254,6 +254,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       schema-qualified).  A typed table is tied to its type; for
       example the table will be dropped if the type is dropped
       (with <literal>DROP TYPE ... CASCADE</literal>).
+      Expects the composite type to have been created with
+      <xref linkend="sql-createtype"/>.
      </para>
 
      <para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 7014be8039..bef630139d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6975,8 +6975,14 @@ check_of_type(HeapTuple typetuple)
 		 * 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 created with CREATE TYPE",
+							format_type_be(typ->oid))));
 	}
-	if (!typeOk)
+	else
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 				 errmsg("type %s is not a composite type",
diff --git a/src/test/regress/expected/typed_table.out b/src/test/regress/expected/typed_table.out
index 2e47ecbcf5..bb21b69a1a 100644
--- a/src/test/regress/expected/typed_table.out
+++ b/src/test/regress/expected/typed_table.out
@@ -89,8 +89,12 @@ drop cascades to function get_all_persons()
 drop cascades to table persons2
 drop cascades to table persons3
 CREATE TABLE persons5 OF stuff; -- only CREATE TYPE AS types may be used
-ERROR:  type stuff is not a composite type
+ERROR:  type stuff is not a composite type created with CREATE TYPE
 DROP TABLE stuff;
+CREATE TYPE simple AS ENUM ('a');
+CREATE TABLE of_simple OF simple;  -- not a composite type
+ERROR:  type simple is not a composite type
+DROP TYPE simple;
 -- implicit casting
 CREATE TYPE person_type AS (id int, name text);
 CREATE TABLE persons OF person_type;
diff --git a/src/test/regress/sql/typed_table.sql b/src/test/regress/sql/typed_table.sql
index 9ef0cdfcc7..eaa11b0f94 100644
--- a/src/test/regress/sql/typed_table.sql
+++ b/src/test/regress/sql/typed_table.sql
@@ -50,6 +50,10 @@ CREATE TABLE persons5 OF stuff; -- only CREATE TYPE AS types may be used
 
 DROP TABLE stuff;
 
+CREATE TYPE simple AS ENUM ('a');
+CREATE TABLE of_simple OF simple;  -- not a composite type
+DROP TYPE simple;
+
 
 -- implicit casting
 
-- 
2.44.0


--3rqrwxze3hgjbv2c--