`cannot complete operation on a table with identity column` error during `create_distributed_table()` on 12.1 about citus HOT 5 OPEN

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;

from citus.

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.

from citus.

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

from citus.

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

from citus.

@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.

from citus.