CODEWITHSUNDEEP
sqlpostgresqltriggers
David Chan
David Chan

Reputation: 7505

postgres update rule wont insert

i have a simple table:

CREATE TABLE aaa_has_bbb (
    aaa_id integer not null,
    bbb_id integer not null,
    rank  integer not null,

    primary key(aaa_id, bbb_id),
    uniq(aaa_id, rank)
)

I am trying to create a rule which will DELETE and INSERT because that will activate some relevant triggers.

CREATE OR REPLACE RULE pivot_key_updates AS
ON UPDATE TO aaa_has_bbb
WHERE OLD.aaa_id<>NEW.aaa_id OR OLD.bbb_id<>NEW.bbb_id
DO INSTEAD (
    --
    -- on update of keys in this pivot table, delete and insert instead
    --
    DELETE FROM aaa_has_bbb WHERE aaa_id = OLD.aaa_id 
      AND bbb_id = OLD.bbb_id;

    INSERT INTO aaa_has_bbb (aaa_id, bbb_id, rank)
    VALUES (NEW.aaa_id, NEW.bbb_id, NEW.rank);

);

This never inserts, but successfully deletes.

However, if I reverse the order like this:

CREATE OR REPLACE RULE pivot_key_updates AS
ON UPDATE TO aaa_has_bbb
WHERE OLD.aaa_id<>NEW.aaa_id OR OLD.bbb_id<>NEW.bbb_id
DO INSTEAD (
    --
    -- on update of keys in this pivot table, delete and insert instead
    --

    INSERT INTO aaa_has_bbb (aaa_id, bbb_id, rank)
    VALUES (NEW.aaa_id, NEW.bbb_id, NEW.rank+1);

    DELETE FROM aaa_has_bbb WHERE aaa_id = OLD.aaa_id 
      AND bbb_id = OLD.bbb_id;

);

Switching the order works? Why?

To make this work correctly, I have to rank+1 to avoid the key collision, but I don't really want to do this.

What am I missing?

EDIT: I realize I can make my life easier with triggers, and that's probably what I'll end up doing, but I'm very curious why my rule doesn't work as expected.

Upvotes: 0

Views: 2811

Answers (3)

wildplasser
wildplasser

Reputation: 44250

The below fragment works for the simple case, but fails for the second (batch) update, probably caused by the update not being qualified by a where-clause.

I was not able to get a correctly qualified range table entry into the resulting query plan; without a where-clause, the delete-target RTE remains unqualified in the final plan. (this could be a bug; I am not sure)

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE aaa_has_bbb
    ( aaa_id integer not null
    , bbb_id integer not null
    , zrank  integer not null
    , flipflag BOOLEAN NOT NULL DEFAULT True
    , primary key(aaa_id, bbb_id)
    , unique (aaa_id, zrank)
        );

-- I am trying to create a rule which will DELETE and INSERT because that will activate some relevant triggers.

CREATE OR REPLACE RULE pivot_key_updates AS
ON UPDATE TO aaa_has_bbb
WHERE (OLD.aaa_id <> NEW.aaa_id OR OLD.bbb_id <> NEW.bbb_id) AND OLD.flipflag = NEW.flipflag
DO INSTEAD (
    --
    -- First: copy existing records that fit the criteria
    -- The flipflag enables us to distinguish between original and cloned rows
    --
    INSERT INTO aaa_has_bbb (aaa_id, bbb_id, zrank, flipflag)
    SELECT NEW.aaa_id, NEW.bbb_id, NEW.zrank, NOT src.flipflag
    FROM aaa_has_bbb src
    WHERE src.aaa_id = OLD.aaa_id AND src.bbb_id = OLD.bbb_id
    AND src.flipflag = OLD.flipflag
        ;

    -- Next: delete existing records that fit the criteria
    DELETE FROM aaa_has_bbb del
    WHERE del.aaa_id = OLD.aaa_id AND del.bbb_id = OLD.bbb_id AND del.flipflag = OLD.flipflag
        ;
);

        -- Trigger function to reveal actual operations
CREATE FUNCTION dingdong() RETURNS TRIGGER AS
$func$
        BEGIN
        RAISE NOTICE 'Table= % operation= % Level= %'
                , TG_TABLE_NAME, TG_OP, TG_LEVEL;
        RETURN NEW;
        END
$func$
LANGUAGE plpgsql;

        -- Trigger to reveal actual operations
CREATE TRIGGER aaa_has_bbb_dingdong
AFTER INSERT OR UPDATE OR DELETE ON aaa_has_bbb
FOR EACH ROW EXECUTE PROCEDURE dingdong ();

INSERT INTO aaa_has_bbb(aaa_id, bbb_id, zrank)
VALUES (1,9, 1)
, (2,8, 1)
, (3,7, 1)
, (4,6, 1)
, (5,5, 1)
        ;

-- This works
-- EXPLAIN ANALYZE
UPDATE aaa_has_bbb up1
SET     zrank = 99
 , aaa_id = up1.bbb_id
 ,       bbb_id = up1.aaa_id
WHERE up1.aaa_id = 2;

SELECT * FROM aaa_has_bbb;


-- This does not work
-- EXPLAIN ANALYZE
UPDATE aaa_has_bbb up2
SET     zrank = 100+up2.zrank
 , aaa_id = 100+ up2.aaa_id
WHERE 1=1;

SELECT * FROM aaa_has_bbb;

OUTPUT:

DROP SCHEMA
CREATE SCHEMA
SET
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "aaa_has_bbb_pkey" for table "aaa_has_bbb"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "aaa_has_bbb_aaa_id_zrank_key" for table "aaa_has_bbb"
CREATE TABLE
CREATE RULE
CREATE FUNCTION
CREATE TRIGGER
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
INSERT 0 5
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
UPDATE 0
 aaa_id | bbb_id | zrank | flipflag 
--------+--------+-------+----------
      1 |      9 |     1 | t
      3 |      7 |     1 | t
      4 |      6 |     1 | t
      5 |      5 |     1 | t
      8 |      2 |    99 | f
(5 rows)

NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= INSERT Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
NOTICE:  Table= aaa_has_bbb operation= DELETE Level= ROW
UPDATE 0
 aaa_id | bbb_id | zrank | flipflag 
--------+--------+-------+----------
(0 rows)

Plan for the final update := {insert+delete}:

                                                    QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Insert on aaa_has_bbb  (cost=0.00..61.19 rows=1 width=13) (actual time=0.082..0.082 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..61.19 rows=1 width=13) (actual time=0.011..0.032 rows=5 loops=1)
         Join Filter: (src.flipflag = up2.flipflag)
         ->  Seq Scan on aaa_has_bbb up2  (cost=0.00..47.80 rows=9 width=13) (actual time=0.005..0.010 rows=5 loops=1)
               Filter: ((flipflag = flipflag) AND ((aaa_id <> (100 + aaa_id)) OR (bbb_id <> bbb_id)))
         ->  Index Scan using aaa_has_bbb_pkey on aaa_has_bbb src  (cost=0.00..1.47 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=5)
               Index Cond: ((aaa_id = up2.aaa_id) AND (bbb_id = up2.bbb_id))
 Trigger aaa_has_bbb_dingdong: time=0.293 calls=5
 Total runtime: 0.425 ms

 Delete on aaa_has_bbb del  (cost=0.00..61.19 rows=1 width=12) (actual time=0.075..0.075 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..61.19 rows=1 width=12) (actual time=0.009..0.047 rows=10 loops=1)
         Join Filter: (del.flipflag = up2.flipflag)
         ->  Seq Scan on aaa_has_bbb up2  (cost=0.00..47.80 rows=9 width=15) (actual time=0.004..0.011 rows=10 loops=1)
               Filter: ((flipflag = flipflag) AND ((aaa_id <> (100 + aaa_id)) OR (bbb_id <> bbb_id)))
         ->  Index Scan using aaa_has_bbb_pkey on aaa_has_bbb del  (cost=0.00..1.47 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=10)
               Index Cond: ((aaa_id = up2.aaa_id) AND (bbb_id = up2.bbb_id))
 Trigger aaa_has_bbb_dingdong: time=0.494 calls=10

 Total runtime: 0.625 ms

 Update on aaa_has_bbb up2  (cost=0.00..57.21 rows=1881 width=19) (actual time=0.003..0.003 rows=0 loops=1)
   ->  Seq Scan on aaa_has_bbb up2  (cost=0.00..57.21 rows=1881 width=19) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: ((((aaa_id <> (100 + aaa_id)) OR (bbb_id <> bbb_id)) AND (flipflag = flipflag)) IS NOT TRUE)
 Total runtime: 0.023 ms
(24 rows)

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657922

I tested and reproduced your problem.
This quote from the manual on CREATE RULE should shed some light on the mystery:

Within condition and command, the special table names NEW and OLD can be used to refer to values in the referenced table. NEW is valid in ON INSERT and ON UPDATE rules to refer to the new row being inserted or updated. OLD is valid in ON UPDATE and ON DELETE rules to refer to the existing row being updated or deleted.

Bold emphasis mine.
When you DELETE the row first, the following INSERT cannot find the referenced row any more and does nothing.

I would consider using triggers instead. You might be able to adjust your existing triggers, so you do not need any additional triggers or rules at all.

Upvotes: 1

lathspell
lathspell

Reputation: 3310

Have a look at http://www.postgresql.org/docs/9.2/static/rules-update.html especially at the example that starts with "So we end up with two final query trees".

It seems to me that rules differ from triggers in that parts of the original WHERE statement gets appended to the statements that you wrote. The examples in rules-triggers.html seem to say the same.

You can use "triggers" instead if you want to do things with single rows: http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html

Upvotes: 0

Related Questions