pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedA generated column cannot be part of a partition key
8+ messages / 5 participants
[nested] [flat]
* A generated column cannot be part of a partition key
@ 2024-07-22 22:37 Diego Stammerjohann <diegostammer@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Diego Stammerjohann @ 2024-07-22 22:37 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A generated column cannot be part of a partition key
@ 2024-07-23 11:55 Francisco Olarte <folarte@peoplecall.com>
parent: Diego Stammerjohann <diegostammer@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Francisco Olarte @ 2024-07-23 11:55 UTC (permalink / raw)
To: Diego Stammerjohann <diegostammer@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A generated column cannot be part of a partition key
@ 2024-07-23 12:01 Diego Stammerjohann <diegostammer@gmail.com>
parent: Francisco Olarte <folarte@peoplecall.com>
0 siblings, 2 replies; 8+ messages in thread
From: Diego Stammerjohann @ 2024-07-23 12:01 UTC (permalink / raw)
To: Francisco Olarte <folarte@peoplecall.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A generated column cannot be part of a partition key
@ 2024-07-23 13:31 Erik Wienhold <ewie@ewie.name>
parent: Diego Stammerjohann <diegostammer@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Erik Wienhold @ 2024-07-23 13:31 UTC (permalink / raw)
To: Diego Stammerjohann <diegostammer@gmail.com>; +Cc: Francisco Olarte <folarte@peoplecall.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A generated column cannot be part of a partition key
@ 2024-07-23 13:57 Francisco Olarte <folarte@peoplecall.com>
parent: Diego Stammerjohann <diegostammer@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Francisco Olarte @ 2024-07-23 13:57 UTC (permalink / raw)
To: Diego Stammerjohann <diegostammer@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A generated column cannot be part of a partition key
@ 2024-07-23 14:01 Diego Stammerjohann <diegostammer@gmail.com>
parent: Francisco Olarte <folarte@peoplecall.com>
0 siblings, 1 reply; 8+ messages in thread
From: Diego Stammerjohann @ 2024-07-23 14:01 UTC (permalink / raw)
To: Francisco Olarte <folarte@peoplecall.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A generated column cannot be part of a partition key
@ 2024-07-23 14:18 David G. Johnston <david.g.johnston@gmail.com>
parent: Diego Stammerjohann <diegostammer@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-07-23 14:18 UTC (permalink / raw)
To: Diego Stammerjohann <diegostammer@gmail.com>; +Cc: Francisco Olarte <folarte@peoplecall.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: A generated column cannot be part of a partition key
@ 2024-07-23 14:51 Peter Eisentraut <peter@eisentraut.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2024-07-23 14:51 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Diego Stammerjohann <diegostammer@gmail.com>; +Cc: Francisco Olarte <folarte@peoplecall.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-07-23 14:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-22 22:37 A generated column cannot be part of a partition key Diego Stammerjohann <diegostammer@gmail.com>
2024-07-23 11:55 ` Francisco Olarte <folarte@peoplecall.com>
2024-07-23 12:01 ` Diego Stammerjohann <diegostammer@gmail.com>
2024-07-23 13:31 ` Erik Wienhold <ewie@ewie.name>
2024-07-23 13:57 ` Francisco Olarte <folarte@peoplecall.com>
2024-07-23 14:01 ` Diego Stammerjohann <diegostammer@gmail.com>
2024-07-23 14:18 ` David G. Johnston <david.g.johnston@gmail.com>
2024-07-23 14:51 ` Peter Eisentraut <peter@eisentraut.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox