Обсуждение: DELETE not seeing expected rows before COPY on 8.4.1

-----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-----

On Thu, Oct 15, 2009 at 11:44 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
> The DELETE returns 0 rows (and thus is the problem) according to the script.

I've had this problem before when there is a BEFORE DELETE trigger
that is inadvertently preventing the deletion, though this seems hard
to rationalize with the rest of what you're seeing...   still, any
triggers or rules on these tables?

...Robert

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> The DELETE returns 0 rows (and thus is the problem) according to the script.

> I've had this problem before when there is a BEFORE DELETE trigger
> that is inadvertently preventing the deletion, though this seems hard
> to rationalize with the rest of what you're seeing...   still, any
> triggers or rules on these tables?

There is a trigger on both sides that does an insert to another table,
but it should not prevent the deletion. Further, this session is
running 'replica' mode. Going to do some more debugging this morning...

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200910160720
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkrYVxYACgkQvJuQZxSWSsh8pgCdGtYA1tzFxeaq+Uwdij/BEsKf
recAmweTdVFQsZc19qqu8wJNfSjvTU2+
=eLya
-----END PGP SIGNATURE-----