CODEWITHSUNDEEP
postgresqltriggersplpgsql
Zach
Zach

Reputation: 538

a postgres update trigger performs everything else except the actual update

Let's use a test table :

CREATE TABLE labs.date_test
(
  pkey int NOT NULL,
  val integer,
  date timestamp without time zone,
  CONSTRAINT date_test_pkey PRIMARY KEY (pkey)
);

I have a trigger function defined as below. It is a function to insert a date into a specified column in the table. Its arguments are the primary key, the name of the date field, and the date to be inserted:

CREATE OR REPLACE FUNCTION tf_set_date()
  RETURNS trigger AS
$BODY$
DECLARE
    table_name text;
    pkey_col text := TG_ARGV[0];
    date_col text := TG_ARGV[1];
    date_val text := TG_ARGV[2];
BEGIN
    table_name := format('%I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME);
    IF TG_NARGS != 3 THEN
        RAISE 'Wrong number of args for tf_set_date()'
        USING HINT='Check triggers for table ' || table_name;
    END IF;
    EXECUTE format('UPDATE %s SET %I = %s' ||
            ' WHERE %I = ($1::text::%s).%I', 
            table_name, date_col, date_val,
            pkey_col, table_name, pkey_col )
    USING NEW;
    RAISE NOTICE '%', NEW;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

The actual trigger definition is as follows:

CREATE TRIGGER t_set_ready_date
  BEFORE UPDATE OF val
  ON labs.date_test
  FOR EACH ROW
  EXECUTE PROCEDURE tf_set_date('pkey', 'date', 'localtimestamp(0)');

Now say I do: INSERT INTO TABLEdate_test(pkey) values(1);`

Then I perform an update as follows:

UPDATE labs.date_test SET val = 1 WHERE pkey = 1;

Now the date gets inserted as expected. But the val field is still NULL. It does not have 1 as one would expect (or rather as I expected).

What am I doing wrong? The RAISE NOTICE in the trigger shows that NEW is still what I expect it to be. Aren't UPDATEs allowed in BEFORE UPDATE triggers? One comment about postgres triggers seems to indicate that original the UPDATE gets overwritten if there is an UPDATE statement in a BEFORE UPDATE trigger. Can someone help me out?

EDIT

I am trying to update the same table that invoked the trigger, and that too the same row which is to be modified by the UPDATE statement that invoked the trigger. I am running Postgresql 9.2

Upvotes: 3

Views: 1227

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324295

Given all the dynamic table names it isn't entirely clear if this trigger issues an update on the same table that invoked the trigger.

If so: That won't work. You can't UPDATE some_table in a BEFORE trigger on some_table. Or, more strictly, you can, but if you update any row that is affected by the statement that's invoking the trigger results will be unpredictable so it isn't generally a good idea.

Instead, alter the values in NEW directly. You can't do this with dynamic column names, unfortunately; you'll just have to customise the trigger or use an AFTER trigger to do the update after the rows have already been changed.

Upvotes: 2

Pavel Stehule
Pavel Stehule

Reputation: 45760

I am not sure, but your triggers can do recursion calls - it does UPDATE same table from UPDATE trigger. This is usually bad practice, and usually is not good idea to write too generic triggers. But I don't know what you are doing, maybe you need it, but you have to be sure, so you are protected against recursion.

For debugging of triggers is good push to start and to end of function body debug messages. Probably you use GET DIAGNOSTICS statement after EXECUTE statement for information about impact of dynamic SQL

DECLARE 
  _updated_rows int;
  _query text;     
BEGIN
  RAISE NOTICE 'Start trigger function xxx';
  ...
  _query := format('UPDATE ....);
  RAISE NOTICE 'dynamic sql %, %', _query, new;
  EXECUTE _query USING new;
  GET DIAGNOSICS _updated_rows = ROW_COUNT;
  RAISE NOTICE 'Updated rows %', _updated_rows;
  ...

Upvotes: 2

Related Questions