triggers, rules, visibility, and synchronization woes

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(at)vesn(dot)net

Curtis Wilbar
Virtual E-Services Network, Inc.
curtis(at)vesn(dot)net