Horton
Menu

Thread

  1. A generated column cannot be part of a partition key

    Diego Stammerjohann <diegostammer@gmail.com> — 2024-07-22T22:37:08Z

     Hello,

 In the documentation (
https://www.postgresql.org/docs/current/ddl-generated-columns.html),
there's the following statement:

 A generated column cannot be part of a partition key

 However, I was able to create a partitioned table using the generated
column, as follows:

 CREATE TABLE "partitioned_table"
 (
   "id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1
INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1),
   "text_value" VARCHAR(100) NOT NULL,
   CONSTRAINT "pk_partitioned_table" PRIMARY KEY ("id") WITH
(fillfactor='100')
 ) PARTITION BY RANGE ("id");

 CREATE TABLE "partition_table_01" PARTITION OF "partitioned_table" FOR
VALUES FROM (1)        TO (10000000);
 CREATE TABLE "partition_table_02" PARTITION OF "partitioned_table" FOR
VALUES FROM (10000000) TO (20000000);

Diego

  2. Re: A generated column cannot be part of a partition key

    Francisco Olarte <folarte@peoplecall.com> — 2024-07-23T11:55:59Z

    On Tue, 23 Jul 2024 at 12:01, Diego Stammerjohann
<diegostammer@gmail.com> wrote:
>  In the documentation (https://www.postgresql.org/docs/current/ddl-generated-columns.html), there's the following statement:
>  A generated column cannot be part of a partition key
>  However, I was able to create a partitioned table using the generated column, as follows:
>  CREATE TABLE "partitioned_table"
>  (
>    "id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1),
>    "text_value" VARCHAR(100) NOT NULL,
>    CONSTRAINT "pk_partitioned_table" PRIMARY KEY ("id") WITH (fillfactor='100')
>  ) PARTITION BY RANGE ("id");
>
>  CREATE TABLE "partition_table_01" PARTITION OF "partitioned_table" FOR VALUES FROM (1)        TO (10000000);
>  CREATE TABLE "partition_table_02" PARTITION OF "partitioned_table" FOR VALUES FROM (10000000) TO (20000000);

It may benefit from better wording in the docs, but they are coherent.
You did not create a generated column, but an identy column. BOTH use
the keyword generated, which is probably misleading. From the docs:

GENERATED ALWAYS AS ( generation_expr ) STORED
This clause creates the column as a generated column. The column
cannot be written to, and when read the result of the specified
expression will be returned.
...
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
This clause creates the column as an identity column. It will have an
implicit sequence attached to it and the column in new rows will
automatically have values from the sequence assigned to it. Such a
column is implicitly NOT NULL.


Francisco Olarte.

  3. Re: A generated column cannot be part of a partition key

    Diego Stammerjohann <diegostammer@gmail.com> — 2024-07-23T12:01:31Z

    OK, that sounds good to me.

So, the documentation should be update to something like this:

"An expression based generated column cannot be part of a partition key."

Sounds fair?

Diego H. S.


On Tue, Jul 23, 2024, 08:56 Francisco Olarte <folarte@peoplecall.com> wrote:

> On Tue, 23 Jul 2024 at 12:01, Diego Stammerjohann
> <diegostammer@gmail.com> wrote:
> >  In the documentation (
> https://www.postgresql.org/docs/current/ddl-generated-columns.html),
> there's the following statement:
> >  A generated column cannot be part of a partition key
> >  However, I was able to create a partitioned table using the generated
> column, as follows:
> >  CREATE TABLE "partitioned_table"
> >  (
> >    "id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1
> INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1),
> >    "text_value" VARCHAR(100) NOT NULL,
> >    CONSTRAINT "pk_partitioned_table" PRIMARY KEY ("id") WITH
> (fillfactor='100')
> >  ) PARTITION BY RANGE ("id");
> >
> >  CREATE TABLE "partition_table_01" PARTITION OF "partitioned_table" FOR
> VALUES FROM (1)        TO (10000000);
> >  CREATE TABLE "partition_table_02" PARTITION OF "partitioned_table" FOR
> VALUES FROM (10000000) TO (20000000);
>
> It may benefit from better wording in the docs, but they are coherent.
> You did not create a generated column, but an identy column. BOTH use
> the keyword generated, which is probably misleading. From the docs:
>
> GENERATED ALWAYS AS ( generation_expr ) STORED
> This clause creates the column as a generated column. The column
> cannot be written to, and when read the result of the specified
> expression will be returned.
> ...
> GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
> This clause creates the column as an identity column. It will have an
> implicit sequence attached to it and the column in new rows will
> automatically have values from the sequence assigned to it. Such a
> column is implicitly NOT NULL.
>
>
> Francisco Olarte.
>

  4. Re: A generated column cannot be part of a partition key

    Erik Wienhold <ewie@ewie.name> — 2024-07-23T13:31:13Z

    On 2024-07-23 14:01 +0200, Diego Stammerjohann wrote:
> So, the documentation should be update to something like this:
> 
> "An expression based generated column cannot be part of a partition key."
> 
> Sounds fair?

I think that change is unnecessarily confusing since that page only
talks about those generated columns.  Note that pg17 will also document
identity columns[1], right before that generated columns section.  Not
sure if generated != identity should be made explicit when the preceding
section introduces identity columns.

[1] https://www.postgresql.org/docs/17/ddl-identity-columns.html

-- 
Erik

  5. Re: A generated column cannot be part of a partition key

    Francisco Olarte <folarte@peoplecall.com> — 2024-07-23T13:57:45Z

    Diego:

On Tue, 23 Jul 2024 at 14:01, Diego Stammerjohann
<diegostammer@gmail.com> wrote:
> OK, that sounds good to me.
Due to top posting I am unable to discern what part sounds good.

> So, the documentation should be update to something like this:
> "An expression based generated column cannot be part of a partition key."
> Sounds fair?

Sounds good, but if you analyze it there is no such thing as "An
expression based generated column" defined. There are generated
columns and identity columns. Unfortunately both use the keyword
GENERATED in their definition / creation, probably due to some
standard committee shenanigans.

Francisco Olarte.

  6. Re: A generated column cannot be part of a partition key

    Diego Stammerjohann <diegostammer@gmail.com> — 2024-07-23T14:01:53Z

    So, for me, it would be good to have in some place the information that an
IDENTITY column is not a GENERATED column. That's what caused confusion to
me.

Diego H. S.

On Tue, Jul 23, 2024, 10:58 Francisco Olarte <folarte@peoplecall.com> wrote:

> Diego:
>
> On Tue, 23 Jul 2024 at 14:01, Diego Stammerjohann
> <diegostammer@gmail.com> wrote:
> > OK, that sounds good to me.
> Due to top posting I am unable to discern what part sounds good.
>
> > So, the documentation should be update to something like this:
> > "An expression based generated column cannot be part of a partition key."
> > Sounds fair?
>
> Sounds good, but if you analyze it there is no such thing as "An
> expression based generated column" defined. There are generated
> columns and identity columns. Unfortunately both use the keyword
> GENERATED in their definition / creation, probably due to some
> standard committee shenanigans.
>
> Francisco Olarte.
>

  7. Re: A generated column cannot be part of a partition key

    David G. Johnston <david.g.johnston@gmail.com> — 2024-07-23T14:18:48Z

    On Tuesday, July 23, 2024, Diego Stammerjohann <diegostammer@gmail.com>
wrote:

> So, for me, it would be good to have in some place the information that an
> IDENTITY column is not a GENERATED column. That's what caused confusion to
> me.
>

Yeah, 5.3 defines what generated columns are and does not include
identity.  But nothing in Chapter 5 discusses identity which seems like a
bug if Chapter 5 is intended be the official reference for concepts.  Maybe
we need to rename Defaults to also reference Identity?

We should point out in 5.3 that Identity is not “generated” and ensure that
we link to 5.3 when we use “generated column” elsewhere in the docs.

David J.

  8. Re: A generated column cannot be part of a partition key

    Peter Eisentraut <peter@eisentraut.org> — 2024-07-23T14:51:06Z

    On 23.07.24 16:18, David G. Johnston wrote:
> On Tuesday, July 23, 2024, Diego Stammerjohann <diegostammer@gmail.com 
> <mailto:diegostammer@gmail.com>> wrote:
> 
>     So, for me, it would be good to have in some place the information
>     that an IDENTITY column is not a GENERATED column. That's what
>     caused confusion to me.
> 
> 
> Yeah, 5.3 defines what generated columns are and does not include 
> identity.  But nothing in Chapter 5 discusses identity which seems like 
> a bug if Chapter 5 is intended be the official reference for concepts.

This has been changed in the documentation for PG 17.  There are now 
separate sections for identity columns and generated columns:

https://www.postgresql.org/docs/17/ddl-identity-columns.html
https://www.postgresql.org/docs/17/ddl-generated-columns.html