Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sX56c-001Qxk-DC for pgsql-hackers@arkaria.postgresql.org; Thu, 25 Jul 2024 20:30:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sX56a-001UJK-So for pgsql-hackers@arkaria.postgresql.org; Thu, 25 Jul 2024 20:30:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sX55W-001RI4-LH for pgsql-hackers@lists.postgresql.org; Thu, 25 Jul 2024 20:29:22 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sX55T-001Qnm-UE for pgsql-hackers@lists.postgresql.org; Thu, 25 Jul 2024 20:29:21 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 46PKTBv11556598; Thu, 25 Jul 2024 16:29:12 -0400 From: Tom Lane To: Erik Wienhold cc: "David G. Johnston" , Robert Haas , Hannu Krosing , Peter Eisentraut , PostgreSQL Hackers Subject: Re: CREATE TABLE creates a composite type corresponding to the table row, which is and is not there In-reply-to: <1cd8a224-4038-43ba-bb11-a4cd8d015462@ewie.name> References: <73jtqglviqor5ozqrthjmau7nj4xi7z4obzaqflf7cvliaf4em@4obr7wqlqkye> <1cd8a224-4038-43ba-bb11-a4cd8d015462@ewie.name> Comments: In-reply-to Erik Wienhold message dated "Sat, 18 May 2024 03:56:36 +0200" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <1556584.1721939341.0@sss.pgh.pa.us> Date: Thu, 25 Jul 2024 16:29:11 -0400 Message-ID: <1556597.1721939351@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1556584.1721939341.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Erik Wienhold writes: > Thanks, I didn't know that guideline. Both fixed in v6. This still isn't following our usual message style IMO. Here's a proposed v7 that outputs -ERROR: type stuff is not a composite type +ERROR: type stuff is the row type of another table +DETAIL: A typed table must use a stand-alone composite type created with= CREATE TYPE. I did a bit of copy-editing on the docs changes too. One notable point is that I dropped the parenthetical bit about "(name optionally schema-qualified)". That struck me as quite unnecessary, and it definitely doesn't read well to have two parenthetical comments in a single four-line sentence. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name*0="v7-0001-Document-that-typed-tables-rely-on-CREATE-TYPE.patc"; name*1="h"; charset="us-ascii" Content-ID: <1556584.1721939341.2@sss.pgh.pa.us> Content-Description: v7-0001-Document-that-typed-tables-rely-on-CREATE-TYPE.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_= table.sgml index f19306e776..93b3f664f2 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -249,18 +249,18 @@ WITH ( MODULUS nume= ric_literal, REM 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). + structure from the specified stand-alone composite type (that is, + one created using ) though it sti= ll + produces a new composite type as well. The table will have a + dependency on the referenced type, meaning that cascaded alter and + drop actions on that type will propagate to the table. = - 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. + A typed table always has the same column names and data types as th= e + type it is derived from, so you cannot specify additional columns. But the CREATE TABLE command can add defaults - and constraints to the table and can specify storage parameters. + and constraints to the table, as well as specify storage parameters= . diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/table= cmds.c index 721d24783b..0b2a52463f 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -6962,8 +6962,15 @@ 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 the row type of another table", + format_type_be(typ->oid)), + errdetail("A typed table must use a stand-alone composite type crea= ted with CREATE TYPE."))); } - 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..b6fbda3f21 100644 --- a/src/test/regress/expected/typed_table.out +++ b/src/test/regress/expected/typed_table.out @@ -89,7 +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 the row type of another table +DETAIL: A typed table must use a stand-alone composite type created with= CREATE TYPE. +CREATE TYPE tt_enum_type AS ENUM ('a'); +CREATE TABLE of_tt_enum_type OF tt_enum_type; -- not a composite type at = all +ERROR: type tt_enum_type is not a composite type +DROP TYPE tt_enum_type; DROP TABLE stuff; -- implicit casting CREATE TYPE person_type AS (id int, name text); diff --git a/src/test/regress/sql/typed_table.sql b/src/test/regress/sql/t= yped_table.sql index 9ef0cdfcc7..57ce12782b 100644 --- a/src/test/regress/sql/typed_table.sql +++ b/src/test/regress/sql/typed_table.sql @@ -48,6 +48,10 @@ DROP TYPE person_type CASCADE; = CREATE TABLE persons5 OF stuff; -- only CREATE TYPE AS types may be used = +CREATE TYPE tt_enum_type AS ENUM ('a'); +CREATE TABLE of_tt_enum_type OF tt_enum_type; -- not a composite type at = all +DROP TYPE tt_enum_type; + DROP TABLE stuff; = = ------- =_aaaaaaaaaa0--