Duplicate key value violates unique constraint

[Barre]

Hi,

I launched pg_squeeze against one of my table using:

SELECT squeeze.squeeze_table('public', 'data');

Squeezing failed with message:

ERROR: duplicate key value violates unique constraint "ind_2"

Schema:

CREATE SEQUENCE IF NOT EXISTS data_id_seq;

CREATE TABLE "public"."data" (
    "id" int8 NOT NULL DEFAULT nextval('data_id_seq'::regclass),
    "parent_id" int8 NOT NULL,
    "data" bytea NOT NULL CHECK (length(raw_data) > 0),
    "year" int4 NOT NULL,
    CONSTRAINT "data_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "public"."parent"("id"),
    PRIMARY KEY ("id","year")
);

[Barre]

Running the same command, in exactly the same conditions resulted in a success just now.

[ahouska]

What is the version of pg_squeeze and PostgreSQL server?

[Barre]

What is the version of pg_squeeze and PostgreSQL server?

I am running pg_squeeze 1.7 with postgreSQL 16.

[ahouska]

Can you please share the server log around the moment the squeezing failed?

[Barre]

For sure!

2024-12-12 14:13:25.961 CET [68326] LOG:  logical decoding found initial starting point at 2A776/65116C70
2024-12-12 14:13:25.961 CET [68326] DETAIL:  Waiting for transactions (approximately 16) older than 3067061195 to end.
2024-12-12 14:13:26.845 CET [68326] LOG:  logical decoding found initial consistent point at 2A776/911C88D8
2024-12-12 14:13:26.845 CET [68326] DETAIL:  Waiting for transactions (approximately 16) older than 3067090206 to end.
2024-12-12 14:13:27.030 CET [68326] LOG:  logical decoding found consistent point at 2A776/952C6020
2024-12-12 14:13:27.030 CET [68326] DETAIL:  There are no old transactions anymore.
2024-12-12 14:13:51.846 CET [51476] postgres@postgres ERROR:  duplicate key value violates unique constraint "data_parent_id_year_key"
2024-12-12 14:13:51.846 CET [51476] postgres@postgres DETAIL:  Key (parent_id, year)=(5879151265, 2021) already exists.
2024-12-12 14:14:01.878 CET [51552] postgres@postgres ERROR:  duplicate key value violates unique constraint "data_parent_id_year_key"
2024-12-12 14:14:01.878 CET [51552] postgres@postgres DETAIL:  Key (parent_id, year)=(5943389557, 2021) already exists.
2024-12-12 14:14:23.523 CET [52197] postgres@postgres ERROR:  duplicate key value violates unique constraint "ind_2"

[ahouska]

One thing I don't understand is that that the error message is about index ind_2. Since pg_squeeze starts from zero when generating index names, ind_2 means that there are at least 3 indexes. However the DDL you pasted should only create one index (for the primary key). Is the DDL incomplete or did you (or your application) try to create a new index while the squeezing was in progress?

And in general, do you know if any DDL/DML statements were running during the squeezing? (pg_squeeze should hndle such situations, tut I wonder if that was something "very special".)

[Barre]

Here's the complete description:

                                                        Table "public.data_y2021"
     Column     |  Type   | Collation | Nullable |                   Default                   | Storage  | Compression | Stats target | Description 
----------------+---------+-----------+----------+---------------------------------------------+----------+-------------+--------------+-------------
 id             | bigint  |           | not null | nextval('data_id_seq'::regclass) | plain    |          |             |              |
 parent_id      | bigint  |           | not null |                                             | plain    |             |              | 
 raw_data       | bytea   |           | not null |                                             | extended |             |              | 
 year           | integer |           | not null |                                             | plain    |             |              | 
Partition of: data FOR VALUES FROM (2021) TO (2022)
Partition constraint: ((year IS NOT NULL) AND (year >= 2021) AND (year < 2022))
Indexes:
    "data_y2021_pkey" PRIMARY KEY, btree (id, year)
    "data_y2021_parent_id_idx" btree (parent_id)
    "data_y2021_parent_id_year_key" UNIQUE CONSTRAINT, btree (parent_id, year)
    "data_y2021_year_idx" btree (year)
Check constraints:
    "data_raw_data_check" CHECK (length(raw_data) > 0)
Foreign-key constraints:
    TABLE "data" CONSTRAINT "data_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES parent(id)
Access method: heap
Options: toast_tuple_target=700

And in general, do you know if any DDL/DML statements were running during the squeezing? (pg_squeeze should hndle such situations, tut I wonder if that was something "very special".)

Nothing apart from UPDATE, DELETE and SELECT was hitting the db at the time of squeezing.

EDIT: After double checking, no updates were occuring, but inserts were going on.

[ahouska]

Right now I have no idea how this error could occur. Perhaps I need to add a partitioned table to my tests, in case the problem is related to partitioning. Please let me know if you think you are able to reproduce the problem.

[Barre]

I am not sure if this will help but this issue occurred during significant table activity, with approximately 30-40k rows being inserted/deleted per second while the squeeze operation was performed.

This is the first occurrence of this issue, despite using pg_squeeze daily on this table for several weeks under similar workload conditions.

Looks ugly to debug, eh.

[ahouska]

The extension should handle the concurrent changes regardless the rate. I'll consider adjusting the extension so it provides more information if thing like this happens. (In fact it's not clear whether the error i caused by pg_squeeze or by the PostgreSQL logical decoding subsystem.)

[ahouska]

One more note. The log you shared by mistake (you eventually removed it from the issue description but I still have the notification in email) contained lines like

[2024-12-08 14:11:47.796 CET [2938841] WARNING:  cutoff for removing and freezing tuples is far in the past
2024-12-08 14:11:47.796 CET [2938841] HINT:  Close open transactions soon to avoid wraparound problems.](url)

If the pg_squeeze error occurs again, please check if such warnings are also around. I wonder if the issue is somehow related. (It should not unless there's a bug either in pg_squeeze or in PostgreSQL core, however bugs just happen to exist.)

Of course I recommend you to resolve the freezing problem, otherwise you can end up being unable to connect to the database until VACUUM FREEZE is performed (possibly hours.

[Barre]

These were older, unrelated logs caused by long-held transactions due to a bug in the software that was accessing the database. In this particular case, I don't think this is related to the current issue. When performing the squeeze, I didn't observe these logs (nor any similar logs for hours before and after), as the original issue had been fixed a few days prior to the squeeze operation.