`cannot complete operation on a table with identity column` error during `create_distributed_table()` on 12.1

[lkral-navmatix]

Hello, following code triggers an error SQL Error [0A000]: ERROR: cannot complete operation on a table with identity column:

START TRANSACTION;

CREATE TABLE public.table1 (
    id int8 GENERATED ALWAYS AS IDENTITY NOT NULL,
    table2_id int8 NOT NULL,
    column0 int2 NOT NULL,
    column1 timestamptz NOT NULL,
    column2 int2 NOT NULL,
    column3 int2,
    column4 int8,
    column5 int8,
    column6 int8,
    CONSTRAINT table1_pk PRIMARY KEY (id,table2_id),
    CONSTRAINT table1_table2_fk FOREIGN KEY (table2_id) REFERENCES public.table2(id)
);
SELECT create_distributed_table('public.table1', 'table2_id');

COMMIT;

We are currently running into this issue on two clusters, both in configuration 1 coordinator + 2 workers (separate worker groups, no standbys). Both are running Citus version 12.1 (verified that all nodes are reporting the same version), one is PG15, the other one is PG16.

Note that this SQL executes without any errors (and seems to achieve the desired state) when testing it locally on a cluster with 1 coordinator and 1 worker (tested both Citus 12.0 and 12.1).

Based on release notes I was under the assumption that this SQL is currently supported in 12.1. Is this correct? If so, let me know what other info could be useful for investigation.

[lkral-navmatix]

We have recreated one of the clusters with only one worker to test if the issue persists and we keep encountering it even in 1 coordinator + 1 worker setup.

Using bigserial seems to work as a workaround.

[JelteF]

Could you share the definition of public.table2? So that we can reproduce more easily.

[lkral-navmatix]

Unfortunately table2 is fairly complex and references multiple other tables. I will see if I can trigger this with some simple table.

We have also encountered this same exact issue in similar table later on and looking at things in common:

• table2 is reference table
• table1 PK has the exact same structure (first column is GENERATED ALWAYS AS IDENTITY, second one is foreign key)
• table1 is sharded using the foreign key that was used as second column in PK

[lkral-navmatix]

Okay, so we can trigger on our clusters with just this:

START TRANSACTION;

CREATE TABLE public.table2 (
    id int8 GENERATED ALWAYS AS IDENTITY NOT NULL,
    CONSTRAINT table2_pk PRIMARY KEY (id)
);
SELECT create_reference_table('public.table2');

CREATE TABLE public.table1 (
    id int8 GENERATED ALWAYS AS IDENTITY NOT NULL,
    table2_id int8 NOT NULL,
    column0 int2 NOT NULL,
    column1 timestamptz NOT NULL,
    column2 int2 NOT NULL,
    column3 int2,
    column4 int8,
    column5 int8,
    column6 int8,
    CONSTRAINT table1_pk PRIMARY KEY (id,table2_id),
    CONSTRAINT table1_table2_fk FOREIGN KEY (table2_id) REFERENCES public.table2(id)
);
SELECT create_distributed_table('public.table1', 'table2_id');

COMMIT;

[JelteF]

@gokhangulbiz Could you have quick look at this to understand what is going on here? It seems related to the GENERATED AS IDENTITY work that you did.