Postgresql – good way to run a trigger for each record in a postgres table

plpgsqlpostgresqltrigger

I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the replicated tables, and store it in the processed form that I need, while stripping out the records that I want to ignore.

Right now, after setting up a new replica, I run an update and set a value back to itself (eg, UPDATE tablename SET field=field) to force the trigger to run, but some of the tables are millions of records, and growing, and it can take 30min. (and then there's the vaccuum, too).

Is there some better way to trigger it, or some way to write a function such that it'll work with either input passed in or NEW depending on calling context? I'm reluctant to keep two different functions around, as I've seen too many times where one gets updated, and not the other.

Best Answer

It can be done, using the following template:

CREATE TABLE tablename ( ... );

/* for direct invocation */
CREATE FUNCTION propagate_data(newrow tablename) RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO other_table VALUES (newrow.a, newrow.b, ...);
END:
$$;

/* trigger function wrapper */
CREATE FUNCTION propagate_data_trg() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM propagate_data(NEW);
END;
$$;

CREATE TRIGGER propagate_data AFTER INSERT ON tablename FOR EACH ROW
    EXECUTE PROCEDURE propagate_data_trg();

Related Solutions

SQL Server – Getting New and Updated Data with an Archive Column

How about making use of the OUTPUT virtual table? Set your transaction isolation level correctly (snapshot/serializable) so that you only see the rows as of the moment your process begins.

Use the following for your OLE DB Source

UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0;

That updates everything in a nice atomic operation with a side effect of generating the target output into your data flow buffers. Route that to your destination and it's done. Nice and neat

Postgresql – how to delete all rows with empty field via function/trigger combo in Postgres v 9.3

There are several things wrong with this trigger.

First: your delete statement. You can't compare NULL using =. You need to use IS NULL:

DELETE FROM test2 WHERE email IS NULL;

Second: a trigger function (quote from the manual) "must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for."

So return test2; should be return new;.

Third: you created a row level trigger, which is going to be very bad for performance. As you are not dependent on the actual values that are changed by the update, a statement level trigger will be much more efficient, because it only fires once for each UPDATE statement rather than once for each row that has been changed.

In a statement level trigger the return value is ignored, so the suggested return new; from above becomes a return null; as there is no new or old record available in a statement level trigger.

Putting it all together:

CREATE OR REPLACE FUNCTION clean_emp() 
  RETURNS trigger AS                                                                           $$
BEGIN
  DELETE FROM test2 WHERE email IS NULL;
  return NULL; -- as we are now using a statement level trigger, null is fine
END;
$$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_empty2
AFTER UPDATE OF name ON test2 
  FOR EACH STATEMENT --<< fire only once for each statement, not row
  EXECUTE PROCEDURE clean_emp();

If you also want to disallow empty values ( '' is something different than 'NULL') you would need to change the condition to where coalesce(email, '') = '')

But the trigger approach is wrong to begin with: you should declare the email column as NOT NULL and then nobody will ever be able to put NULL values into that column and therefore you don't need the trigger at all.

Best Answer

Related Solutions

SQL Server – Getting New and Updated Data with an Archive Column

Postgresql – how to delete all rows with empty field via function/trigger combo in Postgres v 9.3

Related Question