-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
I'm trying to figure out why I keep getting an error when trying to COPY
data into a table. The basic process is to get a list of rows via primary
keys, delete from the 'target' table, grab data from the 'source' table
using the same pks, and stuff them in to the target. However, the COPY is
failing, as a row already seems to exist. Here's the logs:
2009-10-15T20:04:29-06:00 mybox postgres[2717]: [26-1] LOG: statement: begin
2009-10-15T20:04:29-06:00 mybox postgres[2717]: [27-1] LOG: duration: 0.195 ms
2009-10-15T20:04:29-06:00 mybox postgres[2717]: [28-1] LOG: statement: SET
TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE
2009-10-15T20:04:29-06:00 mybox postgres[2717]: [29-1] LOG: duration: 0.128 ms
2009-10-15T20:04:29-06:00 mybox postgres[2717]: [30-1] LOG: statement: DELETE
FROM abc.sales WHERE id IN ('RVC0777','SES0443','SES0752','SES0761','SES0805')
2009-10-15T20:04:31-06:00 mybox postgres[2717]: [31-1] LOG: duration: 2155.206
ms
2009-10-15T20:04:31-06:00 mybox postgres[2717]: [32-1] LOG: statement: COPY
abc.sales FROM STDIN
2009-10-15T20:04:31-06:00 mybox postgres[2717]: [33-1] ERROR: duplicate key
value violates unique constraint "sales_id"
2009-10-15T20:04:31-06:00 mybox postgres[2717]: [33-2] CONTEXT: COPY sales,
line 1: "SES0761#011t#011250#011f#011t"
2009-10-15T20:04:31-06:00 mybox postgres[2717]: [33-3] STATEMENT: COPY
abc.sales FROM STDIN
Table sales looks like this:
id | character varying(64)
...
Indexes:
"sales_id" UNIQUE, btree (id)
The source call is:
COPY (SELECT * FROM abc.sales WHERE id IN
('RVC0777','SES0443','SES0752','SES0761','SES0805')) TO STDOUT;
The table on the other side is identical (has the same unique index and
columns, in the same order)
The DELETE returns 0 rows (and thus is the problem) according to the script.
This is through a Perl script using DBD::Pg (and therefore libpq). The DELETE
is run
via $dbh->do, and uses PQexec.
The rows *are* there when I manually check via psql.
A SELECT inside the script just before the DELETE also shows
no matching rows (should be five).
When run from psql, the DELETE works as expected.
I've verified that only one row with SES0761 is being returned from the source.
I created a second unique index on the source and target table with no errors.
Version is 8.4.1
- --
Greg Sabino Mullane [email protected]
End Point Corporation
PGP Key: 0x14964AC8 200910152337
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkrX68wACgkQvJuQZxSWSsgBGQCg7B4CJX5lZ2WMpNBgWv1Jcyvf
ficAoOXZn1SCkMueqlPDLqh7ErF+p6GA
=dnFa
-----END PGP SIGNATURE-----
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
