1

There is a trigger in postgres that gets called whenever a particular table is updated. It is used to send updates to another API.

Is there a way one can control the firing of this trigger? Sometimes when I update the table I don't want the trigger to be fired. How do I do this? Is there a silence trigger sql syntax?

If not

Can I fire triggers when a row is updated by PG user X and when PG user Y updates the table no trigger should be fired?

codeAnand
  • 990
  • 1
  • 8
  • 29
  • Confused. Didn't you just delete an identical post to this a few seconds ago? – Craig Ringer Jan 07 '14 at 11:21
  • @CraigRinger yes, StackOverflow was giving me quality warnings and hence I asked my friend to post it for me. Eventually both of us posted it at the same time. – codeAnand Jan 07 '14 at 11:22
  • It does that for a reason. Rather than ignoring such warnings, **read the advice in the warnings messages you get and follow it**. Otherwise eventually you will both get banned from the site. (You get penalized for creating and then deleting lots of questions too, btw). Using a friend's account to avoid a ban or ban-warning will just get *both* accounts banned if a moderator notices or someone points it out. – Craig Ringer Jan 07 '14 at 11:32

2 Answers2

5

In recent Postgres versions, there is a when clause that you can use to conditionally fire the trigger. You could use it like:

... when (old.* is distinct from new.*) ...

I'm not 100% this one will work (can't test atm):

... when (current_user = 'foo') ...

(If not, try placing it in an if block in your plpgsql.)

http://www.postgresql.org/docs/current/static/sql-createtrigger.html

(There also is the [before|after] update of [col_name] syntax, but I tend to find it less useful because it'll fire even if the column's value remains the same.)

Adding this extra note, seeing that @CraigRinger's answer highlights what you're up to...

Trying to set up master-master replication between Salesforce and Postgres using conditional triggers is, I think, a pipe dream. Just forget it... There's going to be a lot more to it than that: you'll need to lock data as appropriate on both ends (which won't necessarily be feasible in a reasonable way), manage the resulting deadlocks (which might not automatically get detected), and deal with conflicting data.

Your odds of successfully pulling this off with a tiny team is about about zero -- especially if your Postgres skills are at the level where investing time in reading the manual would answer your own questions. You can safely bet that someone much more competent at Salesforce or some major SQL shop (e.g. like the one Craig works for) considered the same, and either miserably failed or ruled it out.

Moreover, I'd stress that implementing efficient, synchronous, multi-master replication is not a solved problem. You read that right: not solved. Just a few years ago, doing it at all wasn't well solved enough to make it in the Postgres core. So you've no prior art that works well to base your work on and iterate upon.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • does the current_user tell me the user who updated the row, or the user who is listening to the trigger? – codeAnand Jan 07 '14 at 11:36
  • The user who is connected to the db. If you want the app user, you need an extra field to store whoever updated it last, and things become simple: `when (new.updated_by = 'foo')` (or, more likely, his id). – Denis de Bernardy Jan 07 '14 at 11:38
  • 3
    @codeAnand It identifies the username of the user who ran the command that caused the trigger to fire. That's usually the login name of the user who connected to the DB, but it can change if you're running (eg) a `SECURITY DEFINER` procedure. If your app uses a single SQL-level login for all users, `current_user` is useless to you. – Craig Ringer Jan 07 '14 at 11:38
3

This seems to be the same problem as this post a few minutes ago, approaching it from a different direction.

If so, while you can indeed do as Denis suggests, don't attempt to reinvent this wheel. Use an established tool like Slony-I or Bucardo if you are attempting two-way (multi-master) replication. You also need to understand the major limitations involved in multi-master when dealing with conflicting updates.

In general, there are a few ways to control trigger firing:

  • Let the trigger fire, then put logic in the PL/PgSQL trigger body to cause it to take no action if a certain condition is met. This is often the only option when the rules are complex.

  • As Denis points out, use a trigger WHEN clause to conditionally fire the trigger

  • Use session_replication_role to control the firing of all triggers

  • Directly enable/disable triggers.

In particular, if your application shares a single SQL-level user ID for all database access and does its own user management above the SQL level, and you want to control trigger firing on a per-user basis, the only way to do it will be with in-trigger logic. You might find this prior answer about getting user IDs within triggers useful:

Passing user id to PostgreSQL triggers

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Slony I lets me do Master-master replication with two databases; What i am trying to do is master-master replication with Postgresql and Salesforce(via REST API) – codeAnand Jan 07 '14 at 11:54
  • @codeAnd Well, you can't put triggers on a REST API can you? So how do you possibly expect it to work, when you can only fire events from one end? Are you going to poll the API calls looking for changes? – Craig Ringer Jan 07 '14 at 11:55
  • I have triggers on Salesforce . That part is solved. – codeAnand Jan 07 '14 at 11:57
  • @Denis please spend 6 minutes on this video http://www.youtube.com/watch?v=drMim_GMDUU – codeAnand Jan 07 '14 at 12:57
  • @codeAnand: only watched a few seconds of it, but I wouldn't hold my breath on how it fares when the interval between transactions is in the same ballpark as network latency. If you would, methinks go right ahead and buy it instead of trying to clone it. – Denis de Bernardy Jan 07 '14 at 13:07
  • 1
    Yeah. Sounds like it's doing async, probably not particularly consistent pushing using something like http callbacks. Technical detail is thin on the ground and the website has vanished due to the buy-out. I'd be really surprised if that tool produced timely, consistent results - but you might not need that, in which case a similar http callback based approach would be reasonable for you. It isn't "triggers on salesforce", but it might work for your needs. I'd be pretty worried about inconsistency, divergence and resync myself. – Craig Ringer Jan 07 '14 at 13:09
  • @Denis they have closed the service. – codeAnand Jan 07 '14 at 13:18
  • @CraigRinger i have figured the salesforce part of the issue out. But the mechanism goes into a loop. – codeAnand Jan 07 '14 at 13:19
  • 1
    @codeAnand: No offense, but I honestly doubt it. Closer to reality, you'll get pathological cases where conflicting rows rear their ugly heads, and where network latency related issues get in the way of sync'ing efficiently. There's more to it than simply shipping a payload. – Denis de Bernardy Jan 07 '14 at 13:26
  • Yep. I'd be really interested in seeing the details on the salesforce side of this solution, and I think we'd probably have to be able to see the full system to help you anyway. – Craig Ringer Jan 07 '14 at 13:30
  • @CraigRinger how can i show you the salesforce side of the solution? Will you be open to skype? – codeAnand Jan 08 '14 at 07:35