Re: CREATE TABLE creates a composite type corresponding to the table row, which is and is not there

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