Thread: 9.0.6 "cluster" transient failure ...
Hi folks,
We run a nightly cronjob on secondary database machine (9.0.6) to produce an abbreviated dataset for developer
laptops.The procedure is roughly as follows:
* pg_dump production db into scratch db on secondary box
* one big transaction:
** disable autovaccum on all tables in the scratch db
** do the bulk deletes / rewrites of data, including making use of temporary tables
** reenable autovaccum
** commit
* cluster scratch db to get rows in nice ordering.
* pg_dump scratch db, make available for developers to import.
Anyway, it failed last night with novel reason:
... final statements of bulk deletes / rewrites...
SELECT 786431
TRUNCATE TABLE
INSERT 0 786431
DELETE 2705377
UPDATE 198241
UPDATE 8832
UPDATE 5585
UPDATE 1
UPDATE 143977
autovacuum_on
---------------
(1 row)
COMMIT
ERROR: duplicate key value violates unique constraint "pg_toast_22608264_index"
DETAIL: Key (chunk_id, chunk_seq)=(22354927, 0) already exists.
The error is raised at the 'cluster' right after the commit.
Checking things out this morning, I was able to successfully "cluster" w/o issue.
The machine is Centos release 5.7 (Final) on x86_64, running PG 9.0.6 w/ fsync = off (being a backup box). We've not
hadany issue before, no recent changes to either the script, environment, or hardware.
That toast table no longer exists, the base oid is not mentioned in pg_class ("select * from pg_class where oid =
22354927";yields nothing). Figuring it could have been one of the temporary tables used in the transformations (at
leastone of which had data rows wide enough to get toasted)?
----
James Robinson
Socialserve.com
James Robinson <jlrobins@socialserve.com> writes: > ERROR: duplicate key value violates unique constraint "pg_toast_22608264_index" > DETAIL: Key (chunk_id, chunk_seq)=(22354927, 0) already exists. > The error is raised at the 'cluster' right after the commit. This is probably the same issue reported as bug #6393 and patched a couple weeks ago: http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5f97c5f81874695f9436fe980f7aa51b637bd54 regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
James Robinson <jlrobins@socialserve.com> writes: > ERROR: duplicate key value violates unique constraint "pg_toast_22608264_index" > DETAIL: Key (chunk_id, chunk_seq)=(22354927, 0) already exists. > The error is raised at the 'cluster' right after the commit. This is probably the same issue reported as bug #6393 and patched a couple weeks ago: http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5f97c5f81874695f9436fe980f7aa51b637bd54 regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
James Robinson <jlrobins@socialserve.com> writes: > ERROR: duplicate key value violates unique constraint "pg_toast_22608264_index" > DETAIL: Key (chunk_id, chunk_seq)=(22354927, 0) already exists. > The error is raised at the 'cluster' right after the commit. This is probably the same issue reported as bug #6393 and patched a couple weeks ago: http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5f97c5f81874695f9436fe980f7aa51b637bd54 regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
James Robinson <jlrobins@socialserve.com> writes: > ERROR: duplicate key value violates unique constraint "pg_toast_22608264_index" > DETAIL: Key (chunk_id, chunk_seq)=(22354927, 0) already exists. > The error is raised at the 'cluster' right after the commit. This is probably the same issue reported as bug #6393 and patched a couple weeks ago: http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5f97c5f81874695f9436fe980f7aa51b637bd54 regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Jan 28, 2012, at 3:45 AM, Tom Lane wrote: > James Robinson <jlrobins@socialserve.com> writes: >> ERROR: duplicate key value violates unique constraint "pg_toast_22608264_index" >> DETAIL: Key (chunk_id, chunk_seq)=(22354927, 0) already exists. > >> The error is raised at the 'cluster' right after the commit. > > This is probably the same issue reported as bug #6393 and patched a > couple weeks ago: > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e5f97c5f81874695f9436fe980f7aa51b637bd54 > > regards, tom lane Thank you very much Tom. We'll look forward to 9.0.7, and will re-report if it happens to resurface. ---- James Robinson Socialserve.com