DELETE not seeing expected rows before COPY on 8.4.1 - Mailing list pgsql-hackers

-----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
READWRITE
             
 
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 greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200910152337
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkrX68wACgkQvJuQZxSWSsgBGQCg7B4CJX5lZ2WMpNBgWv1Jcyvf
ficAoOXZn1SCkMueqlPDLqh7ErF+p6GA
=dnFa
-----END PGP SIGNATURE-----