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