4

I am working with two PostgreSQL databases (one internal and one on our company Web server) where I am attempting to share data between the two instances through the use of triggers. The databases have a "shared" table (basically a replica of the same table with pretty much the same data on both servers). When our in-house representatives update the table, it successfully updates the Web server's database via dblink without a problem.

The issue, however, is when I try to introduce a trigger on the Web server to pass updated values back to the internal server. I always seem to end up triggering an infinite loop (pun intended), which then forces me to shut down both PGSQL instances so that I can DROP the problem trigger. I have tried a number of ways to prevent the loop from occurring, including making the WHERE clause as specific as I believe I can, but I can't seem to get around this issue.

After a lot of research, I found this thread on this SE site (and a few other similar threads/posts on other sites) which indicate that one possible way to prevent a loop is to use the pg_trigger_depth() function to catch when a trigger is being executed by another trigger. That sounds great, except for one fatal issue: my internal PGSQL database is running v9.1 (my Web server is running 9.2).

Based on my initial testing, I'm assuming that pg_trigger_depth() cannot return a "correct" value when the trigger is being called from a server that doesn't support the function (or possibly if it is being called across the dblink connection - I'm not certain what the limiting factor is in this environment).

So, my question, I suppose, is this: Is there any way to emulate the pg_trigger_depth() function in this type cross-database scenario to prevent an infinite loop? Failing that, does anyone have any other suggestions for a way to keep this from locking up my database servers?

Here are some (redacted) basics of the functions in question:

THIS TRIGGER FUNCTION ON THE WEB SERVER (I currently don't have an actual trigger created for this one to prevent it from creating the dreaded infinite loop)

CREATE OR REPLACE FUNCTION webschema.update_inhouse_billing()
  RETURNS trigger AS
$BODY$DECLARE
  myconnect text[];
  myint integer;

BEGIN
select * into myconnect from dblink.dblink_get_connections();
if (myconnect[1] is null )  then
 perform dblink.dblink_connect('myconn','inhousedb');
end if;

BEGIN
select * from dblink.dblink('myconn','select inhouseschema.updatebilling(' ||
     quote_nullable(NEW."UserID")          || ',' || 
     quote_nullable(NEW."Month")           || ',' ||
     quote_nullable(NEW."Count1")          || ',' || 
     quote_nullable(NEW."Count2")          || ',' ||
     quote_nullable(NEW."Count3")          || ',' || 
     quote_nullable(NEW."CalcAmount")      || ',' ||
     quote_nullable(NEW."OverrideAmount")  || ',' ||
     quote_nullable(NEW."Posted")          || ')') as t(a integer) into myint;

EXCEPTION
   WHEN others THEN
     RETURN NEW;

END;    
RETURN NEW;
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

CALLS THIS FUNCTION ON THE IN-HOUSE DATABASE

CREATE OR REPLACE FUNCTION inhouseschema.updatebilling(userid integer, month integer, count1 integer, count2 integer, count3 integer, calcamount numeric, overrideamount numeric, posted integer)
  RETURNS integer AS
$BODY$DECLARE
  mycount integer;
BEGIN
    UPDATE inhouseschema.billing b SET 
        "Count1"         = $3,
        "Count2"         = $4,
        "Count3"         = $5,
        "CalcAmount"     = $6,
        "OverrideAmount" = $7,
        "Posted"         = $8
    WHERE (b."UserID" = $1 AND b."Month" = $2 AND b."Paid" IS NULL) 
    AND b."Count1" <> $3 AND b."Count2" <> $4 AND b."Count3" <> $5 AND b."CalcAmount" <> $6;

    GET DIAGNOSTICS mycount = ROW_COUNT;
RETURN mycount;
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

THIS TRIGGER FUNCTION ON THE IN-HOUSE SERVER

CREATE OR REPLACE FUNCTION inhouseschema.update_web_billing()

  RETURNS trigger AS
$BODY$DECLARE
  myconnect text[];
  myint integer;
BEGIN

select * into myconnect from dblink.dblink_get_connections();
if (myconnect[1] is null )  then
 perform dblink.dblink_connect('myconn','webserverdb');
end if;

BEGIN
select * from dblink.dblink('myconn','select webschema.updatebilling(' ||
     quote_nullable(NEW."UserID")          || ',' || 
     quote_nullable(NEW."Month")           || ',' ||
     quote_nullable(NEW."Count1")          || ',' || 
     quote_nullable(NEW."Count2")          || ',' ||
     quote_nullable(NEW."Count3")          || ',' || 
     quote_nullable(NEW."CalcAmount")      || ',' ||
     quote_nullable(NEW."OverrideAmount")  || ',' ||
     quote_nullable(NEW."Posted")          || ',' ||
     quote_nullable(NEW."Paid")            || ')') as t(a integer) into myint;         

EXCEPTION
   WHEN others THEN
     RETURN NEW;

END;    
RETURN NEW;
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

CALLS THIS FUNCTION ON THE WEB SERVER DATABASE

CREATE OR REPLACE FUNCTION webschema.updatebilling(userid integer, month integer, count1 integer, count2 integer, count3 integer, calcamount numeric, overrideamount numeric, posted integer, paid integer)
  RETURNS integer AS
$BODY$DECLARE
  mycount integer;
BEGIN

UPDATE webschema.billing b SET 
    "Count1"         = $3,
    "Count2"         = $4,
    "Count3"         = $5,
    "CalcAmount"     = $6,
    "OverrideAmount" = $7,
    "Posted"         = $8,
    "Paid"           = $9
WHERE (b."UserID" = $1 AND b."Month" = $2);

GET DIAGNOSTICS mycount = ROW_COUNT;
RETURN mycount;
END;$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

I pretty much want the data from our in-house database to be the "final authority" as our Accounting Department have an interface for approving the information posted and flagging it as "paid", but I want the information from the Web site to come in and update the in-house database as long as it hasn't been marked as paid.

The trigger I've been trying to set up on the Web server is:

CREATE TRIGGER billing_update_inhouse AFTER UPDATE ON webschema.billing FOR EACH ROW EXECUTE PROCEDURE webschema.update_inhouse_billing();

And the trigger (that appears to be working correctly) on the in-house server is:

CREATE TRIGGER billing_update_web AFTER UPDATE ON inhouseschema.billing FOR EACH ROW EXECUTE PROCEDURE inhouseschema.update_web_billing();

Okay, so I guess that's about it. There may be a typo or two in the above code with my redactions, but this should be pretty much it. Does anyone have any suggestions or thoughts on how I might be able to resolve this issue once and for all?

  • I suppose I probably should have prefaced this with the disclaimer: "this is my first real attempt to work with triggers ever, so if there's an even better way to go about this, I'm all ears. – G_Hosa_Phat Sep 7 '15 at 22:02
  • I have one "dumb" question about the inhouseschema.updatebilling I posted above: If the values in the database are NULL, but the parameter values are not, will PostgreSQL recognize them as "<>"? I've run into cases in other databases where a NULL value has to be explicitly checked for in addition to the "not equal" condition. – G_Hosa_Phat Sep 7 '15 at 22:50
  • 2
    You can just SET myapp.row_origin = 'webapp' or SET myapp.row_origin = 'otherthing' before the trigger, and in the trigger use current_setting('myapp.row_origin') to detect that. You'll want to set a global default in postgresql.conf or via alter database ... set ... so that it's never undefined. – Craig Ringer Sep 8 '15 at 0:22
  • @CraigRinger: Thank you for the suggestion, but allow me to show my ignorance here. If I choose to go the ALTER DATABASE route, would I actually go run a query (using my examples above) that states something like ALTER DATABASE webserverdb SET myapp.row_origin = 'web' (and another where row_origin = inhouse), or am I misunderstanding? After that has been set, then, for example on the Web server's trigger function, would I put in my trigger functions, before the first BEGIN statement, something like IF current_setting('myapp.row_origin') = 'web' THEN...? – G_Hosa_Phat Sep 8 '15 at 13:30
  • Correction to above: put the IF...THEN conditional statement after the first BEGIN statement. Regardless, I'm reading up on the SET command. I'm still not sure about setting up the default setting for myapp.row_origin, but are you suggesting something like this on the update_inhouse_billing() trigger function? BEGIN IF current_settings('myapp.row_origin') = 'web' THEN select * into myconnect... then BEGIN SET myapp.row_origin = 'inhouse' select * from dblink.dblink.... Then do the opposite in update_web_billing() (IF current_settings('myapp.row_origin') = 'inhouse'...)? – G_Hosa_Phat Sep 8 '15 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.