triggers, rules, visibility, and synchronization woes - Mailing list pgsql-general

  I'm trying to figure out a way to do the following:

     When a table gets an insert, update, or delete; information about
     that operation and the row that it operated on is passed through
     custom programming to do things to a Berkeley DB database on a
     remote server.  Essentially a database synchronizer (although it
     is not synchronizing all information).  Updates to the SQL tables
     occur within transactions.

  Triggers were my first shot.  Custom programmed a C trigger that
  captured the event and tuple (it is an AFTER trigger).  While the
  trigger had visibility to the data being changed, when the message
  passed to the remote server's custom programmed daemon is received
  that server makes a connect to the SQL server, and the data is not
  visible (because the trigger fires before the commit, so the data
  is not in the database yet).

  Then I looked at rules.  I think they will have a problem for the
  same reason... the rule will fire before the transaction is commited
  and the data will not be visible on a connection from the remove
  server.

  I don't want to have the trigger or rule pass all the data to be
  sync'ed as if the commit fails and the database rolls back, that has
  to be trapped and the server on the remote end needs to be told to
  undo what it just did.

  Now, for implicit transactions that occur for insert and update, I'd
  assume that if the trigger fires (an AFTER trigger) then everything
  will be a success as the trigger is hit.  Is this a valid assumption ?
  If so, I could have the trigger pass the data to the remote end without
  worry of having to potentially undo the change it just issued to the remote.
  This of course will not work for explicit transactions as the trigger
  will hit because the INSERT succeeds, but I'd assume it is still possible
  to COMMIT and have a failure causing a rollback.

  I've looked at using LISTEN and NOTIFY, having a daemon on the remote
  end LISTENing in for a particular NOTIFY.  Then if transactions/operations
  complete OK, the front end (php web app) could then issue a NOTIFY.
  Thankfully it appears you can LISTEN and NOTIFY for any text strings, and
  is not tied to relations, tuples, data, etc.  Not quite what I'm looking
  for, but currently, I'm at a loss of options.

  One other question... in using rules....

  CREATE RULE test_rule AS ON INSERT TO test_table DO
    SELECT my_c_func(old);

  The SELECT could then call my c function which would return a varchar
  message about the function's success or failure.  While this doesn't
  solve the issue of rules and transactions and data visibility, etc,
  what data type is old in postgres terms, and in C terms for the coded
  function ?  Is it OPAQUE for postgres, and TupleTableSlot* in C ????

  It would be so nice if there could be an after trigger that triggered
  after an explicit transaction succesfully commit's rather than before
  the explicit transaction commits.

  Am I just missing something ????

  The PostgreSQL docs are great, but really don't dig in deep enough to
  answer these sort of questions.

  Hoping to get some good advice from a real PostgreSQL guru, PostgreSQL
  developer, or anyone else that sees something I missed :-)

Thanks again for any answers, comments, or suggestions,

  -- Curt

Curtis Wilbar
Virtual E-Services Network, Inc.
curtis@vesn.net



Curtis Wilbar
Virtual E-Services Network, Inc.
curtis@vesn.net