History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: BRUCE-14
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Robert Klahn
Reporter: Derek Chan
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Bruce

deletes fail when there are identical rows

Created: 25/Sep/07 01:33 PM   Updated: 19/Oct/07 01:47 PM
Component/s: Daemon
Affects Version/s: 1.0
Fix Version/s: None

Time Tracking:
Not Specified

Testcase included: yes


 Description  « Hide
Replication stops with this error:

org.postgresql.util.PSQLException: ERROR: 2 rows updated, deleted, or inserted. Expected one and only one row.

Steps to reproduce:

CREATE TABLE test (x INT, y INT);

on master:

CREATE TRIGGER test_sn AFTER INSERT OR DELETE OR UPDATE ON test FOR EACH STATEMENT EXECUTE PROCEDURE bruce.logsnapshottrigger();
CREATE TRIGGER test_tx AFTER INSERT OR DELETE OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE bruce.logtransactiontrigger();

on slave:

CREATE TRIGGER test_deny BEFORE INSERT OR DELETE OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE bruce.denyaccesstrigger();

on master:

INSERT INTO test VALUES (1, 1);
INSERT INTO test VALUES (1, 1);
DELETE FROM test WHERE x=1 AND y=1;

=# select * from bruce.slavesnapshotstatus ;
clusterid | slave_xaction | master_current_xaction | master_min_xaction | master_max_xaction | master_outstanding_xactions | update_time
---------------------------------------------------------------------------------------------------------------------------------------
1 | 9950079 | 358712580 | 358712580 | 358712581 | 358712580 | 2007-09-25 11:30:30.813368
(1 row)

=> select * from bruce.transactionlog where xaction=358712580;
rowid | xaction | cmdtype | tabname | info
-------------------------------------------------------------
48869 | 358712580 | D | public.test | x:int4:MQ==:Unable to render embedded object: File () not found.
48870 | 358712580 | D | public.test | x:int4:MQ==:Unable to render embedded object: File () not found.
(2 rows)



 All   Comments   Work Log   Change History      Sort Order: Ascending order - Click to sort in descending order
Robert Klahn - 19/Oct/07 01:47 PM
At first glance, this appears unfixable. Postgresql lacks a LIMIT clause on DELETE.

we may need to require that a replicated table have at least a UNIQUE index.