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
>From f279ee1eccd07da14d0ff49f267f6fceffbd0778 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <e...@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
