Postgresql – Trigger on 2 tables : issue with INSERT and UPDATE

postgresqltriggerupdate

This is related to my previous question:
Trigger to cascade updates to a second table.

In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields in metadonnees (formerly table2). Merged like this now:

CREATE OR REPLACE FUNCTION activite.tbl_emprise_metadonnees()
RETURNS trigger AS
$BODY$
begin

IF (TG_OP = 'INSERT') THEN
    insert into activite.metadonnees (gid, date_publication, contributor, code_tranche)
    VALUES (new.gid, current_date, current_user, NEW.code_tranche)
    ;
    return new ;

elseif (TG_OP = 'DELETE') THEN
DELETE from activite.metadonnees
where gid = old.gid ;
return old ;

elsif (TG_OP = 'UPDATE') THEN 
UPDATE activite.metadonnees 
SET (contributor, code_tranche) = (current_user, NEW.code_tranche)
where metadonnees.gid = new.gid
;
return new ;
end if ;

end;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;


CREATE TRIGGER trg_tbl_emprise_metadonnees
AFTER INSERT OR DELETE OR UPDATE
ON activite.emprise
FOR EACH ROW
EXECUTE PROCEDURE activite.tbl_emprise_metadonnees();

Table definitions:

CREATE TABLE activite.emprise
(gid integer NOT NULL DEFAULT PRIMARY KEY nextval('activite.emprise_gid_seq'::regclass),
surface numeric(9,2),
annee integer,
ro character varying(50),
numope character varying(12),
typope character varying(25),
geom geometry(MultiPolygon,2154),
typemp character varying(30),
nomope character varying(80),
numoa character varying(20),
numprescr character varying(25),
tranche integer DEFAULT 1,
code_tranche character varying(15),
producteur character varying(15),
notice_rapport character varying(50)

CREATE TABLE activite.metadonnees
(gid integer NOT NULL PRIMARY KEY,
date_publication date,
"date_création" date,
"généalogie" character varying(250),
"résolution_spatiale" character varying(5), 
responsable character varying(10),
restrictions character varying(100),
source character varying(15),
creator character varying(50),
publisher character varying(80),
identifier character varying(50),
title character varying(80),
subject character varying,
code_tranche character varying(15),
contributor character varying,
dates character varying,
type_donnees character,
format character varying,  
language character varying, 
coverage character varying

If I use AFTER INSERT OR DELETE OR UPDATE trigger then when a record is inserted into emprise, the field contributor is overwritten by the current_user value for all records.

If the trigger is limited to AFTER INSERT OR DELETE then after insertion everything goes well: the field contributor is not replaced for all records (as desired), just the record concerned in emprise.

In both cases, the contributor and code_tranche are updated if, after insertion, code_tranche value is changed in table emprise. I will conclude that UPDATE is the cause of this dysfunction but I am unable to find why.

Best Answer

Your trigger function should work and cannot cause the described problem on its own. Moreover, this does not make any sense at all:

If the ON UPDATE trigger is active then when a record is inserted into emprise, the field contributor is overwritten by the current_user value for all records.

UPDATE triggers are not fired on INSERT. Maybe just wrong terms?

And you mentioned primary keys before, but I don't see them here. Anything else "simplified"? You need to be precise to pin down the problem ...

Only a couple of simplifications, optimizations and a minor fix:

CREATE OR REPLACE FUNCTION activite.tbl_emprise_metadonnees()
  RETURNS trigger AS
$func$
BEGIN
   CASE TG_OP
   WHEN 'INSERT' THEN
      INSERT INTO activite.metadonnees (gid, date_publication, contributor, code_tranche)
      VALUES (NEW.gid, current_date, current_user, NEW.code_tranche);

   WHEN 'DELETE' THEN
      DELETE FROM activite.metadonnees
      WHERE  gid = OLD.gid 

   WHEN 'UPDATE' THEN 
      UPDATE activite.metadonnees 
      SET   (gid, contributor, code_tranche) = (NEW.gid, current_user, NEW.code_tranche)
      WHERE  metadonnees.gid = OLD.gid
      AND   (gid, contributor, code_tranche)  -- only update if anything changes
            IS DISTINCT FROM (NEW.gid, current_user, NEW.code_tranche);
   ELSE
      -- do nothing (should not occur)
   END CASE;

   RETURN NULL;  -- for AFTER trigger
END
$func$  LANGUAGE plpgsql;

Note how I base the UPDATE on OLD.gid. It would be slightly incorrect to base it on NEW.gid and possibly miss changes to that column.

You can simplify RETURN because, quoting the manual:

The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.

And CASE is a bit shorter and faster.

The root of your problem is still burried elsewhere.

Aside from that, a clean solution would be to remove redundant storage (if at all possible). Use a FK constraint with ON UPDATE CASCADE ON DELETE CASCADE. Code examples:

And store contributor and code_tranche in table emprise once per entity. Then you need no trigger on UPDATE or DELETE, just on INSERT. Or even merge the two tables (which seem to be in 1:1 relation). Then you need neither triggers nor FK constraints. NULL storage is cheap - in case you are worried about many null columns in the one table.

Do nullable columns occupy additional space in PostgreSQL?

Related Solutions

Mysql – Create a trigger to update table data on another Server’s database

To accomplish what you are wanting to do, it is possible to use the FEDERATED storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.

This is not exactly a simple out-of-the-box solution, because it requires additional precautions and requires you to decide whether consistency or isolation tolerance is more important and allow the queries to fail when the other server isn't available (more consistency) or use a CONTINUE HANDLER to suppress errors (isolation tolerance).

But here is an extremely simplified example.

Each server would have the identical configuration.

The local user table:

CREATE TABLE user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A local table that is federated to the user table on the other server.

CREATE TABLE remote_user (
  username varchar(64) NOT NULL,
  password varbinary(48) NOT NULL, /* encrypted of course */
  PRIMARY KEY(username)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:pass@the_other_host:port/schema/user';

Selecting from remote_user on one server will retrieve the records from the other server, and insert/update/delete on that table will change data on the other server.

So, we create triggers do accomplish the purpose of updating the distance server. They are written as BEFORE triggers, with the idea being that we don't want to do something to ourselves that we can't do to the other server -- for example, if a username already exists on the other server, but not here, we want the insert on the other server to throw an error that prevents us from creating the user here... as opposed to creating a user here with what would be a conflicting username. This is, of course, one of the tradeoff decisions you'll need to make.

DELIMITER $$

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
  INSERT INTO remote_user (username,password) VALUES (NEW.username,NEW.password);
END $$

CREATE TRIGGER user_bu BEFORE UPDATE ON user FOR EACH ROW
BEGIN
  UPDATE remote_user 
     SET username = NEW.username,
         password = NEW.password
   WHERE username = OLD.username;
END $$

CREATE TRIGGER user_bd BEFORE DELETE ON user FOR EACH ROW
BEGIN
  DELETE FROM remote_user
   WHERE username = OLD.username;
END $$

DELIMITER ;

This is not a perfect solution and is not a high-availability solution, because it relies on solid connectivity between the two systems and even if you are using InnoDB and transactions, the actions you take against the target table are not part of your local transaction and cannot be rolled back.

I use the FEDERATED engine quite a bit; it comes in handy for a number of creative purposes in my environment, including one situation where I used a federated query launched by a trigger to impose foreign key constraints against a foreign data source; however, I restrict its use to back-end processes where unexpected issues such as timeouts, coding errors, or server-to-server network/outage/isolation events cannot result in the end user on one of our web sites experiencing any kind of problem. Your ability to tolerate such a situation would be a major determining factor into whether this is an appropriate solution.

An alternative would be to configure your two servers in master/master replication. For this, you would need to use different database names on each server, so that for most events that replicate, the two servers could not possibly conflict with each other. In the worst-case scenario, if you lose connectivity or encounter a replication error, the two sites would still be running independently and you could resynchronize and recover. Configuration would look something like this:

database_a database for site A
database_b database for site B
database_c database for only the shared table(s)

Then, in database_a and database_b:

CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW user AS SELECT * FROM c.user;

MySQL will treat database_a.user and database_b.user as aliases for the "real" user table, database_c.user, so you would not have to change your application other than to use its designated database (i.e, you wouldn't have to configure it to understand that the user table was actually in a different schema, because the view will function pretty much transparently with this configuration). If the schemas have foreign keys against the user table, you would declare those against the true base table database_c.user.

Configure the two servers to replicate everything, but set auto_increment_increment and auto_increment_offset appropriately so you do not have conflicting auto-increment values on the shared table(s), if your tables use auto-increment. (Note, the documentation says that these variables are for NDB tables only, but that's not accurate).

An extra advantage of this setup is that your two servers would then have a complete duplicate of the other site's data that you could potentially use to your advantage for recovery from hardware failure in one of the servers.

Postgresql – Delete duplicate records with no change in between

Core feature is the window function lag().
Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!):

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int)
  RETURNS integer AS
$func$
DECLARE
   del_ct int;
BEGIN
   -- this may or may not be necessary:
   -- lock rows to avoid race conditions with concurrent deletes
   PERFORM 1
   FROM   vendor_prices
   WHERE  vendor = _vendor
   ORDER  BY sku, effective_date, id  -- guarantee row locks in consistent order
   FOR    UPDATE;

   -- delete redundant prices
   DELETE FROM vendor_prices v
   USING (
      SELECT id
           , price = lag(price) OVER w  -- same as last row
             AND (lead(id) OVER w) IS NOT NULL AS del  -- not last row
      FROM   vendor_prices
      WHERE  vendor = _vendor
      WINDOW w AS (PARTITION BY sku ORDER BY effective_date, id)
      ) d
   WHERE v.id = d.id
   AND   d.del;

   GET DIAGNOSTICS del_ct = ROW_COUNT;  -- optional:
   RETURN del_ct;  -- return number of deleted rows
END
$func$  LANGUAGE plpgsql;

Call:

SELECT remove_vendor_price_dupes(1);

Notes

The current version of the 9.3 major release is 9.3.6. The project recommends that ...

all users run the latest available minor release for whatever major version is in use.
A multicolumn index on (vendor, sku, effective_date, id) would be perfect for this - in this particular order. But Postgres can combine indexes rather efficiently, too.
It might pay to add the otherwise irrelevant price as last item ot the index to get index-only scans out of this. You'll have to test.
Since you have concurrent deletes it may be a good idea to run a separate delete per vendor to reduce the potential for race conditions and deadlocks. Since there are only a few vendors, this seems like a reasonable partitioning. (Many tiny calls would be comparatively slow.)
I am running a separate SELECT (PERFORM in plpgsql, since we do not use the result) because the row locking clause FOR UPDATE cannot be used together with window functions. Don't let the keyword mislead you, this is not just for updates. I am locking all rows for the given vendor, since the result depends on all rows. Concurrent reads are not impaired, only concurrent writes have to wait until we are done. That's another reason why deleting rows for one vendor at a time in a separate transaction should be best.
sku is unique per product, so we can PARTITION BY it.
ORDER BY effective_date, id: your first version of the question included code for duplicate rows, so I added id to ORDER BY as additional tie breaker. This way it works for duplicates on (sku, effective_date) as well.
To preserve the last row for each set: AND (lead(id) OVER w) IS NOT NULL. Reusing the same window for lead() is cheap - independent of the added explicit WINDOW clause - that's just syntax shorthand for convenience.
I am locking rows in the same order: ORDER BY sku, effective_date, id. Make sure that concurrent DELETEs operate in the same order to avoid deadlocks. If all other transactions delete no more than a single row within the same transaction, there cannot be deadlocks and you don't need the row locking at all.
If concurrent INSERTs could lead to a different result (make different rows obsolete), you have to lock the whole table in EXCLUSIVE mode instead to avoid race conditions:
```
LOCK TABLE vendor_prices IN EXCLUSIVE MODE;
```
Do that only if it's necessary. It blocks all concurrent write access.
I am returning the number of rows deleted, but that's totally optional. You might as well return nothing and declare the function as RETURNS void.

Best Answer

Related Solutions

Mysql – Create a trigger to update table data on another Server’s database

Postgresql – Delete duplicate records with no change in between

Notes

Related Question