0

I have a PostgreSQL server and I need to run some code whenever an update happens on some table. The PostgreSQL triggers (using PL/java) allows me to call a static java method. The method I need to execute does an RPC to an external server with the information about what the update was.

What I couldn't figure out is how I can create a global resource (in this case, the network conection with the external server) which persists across calls.

One way is probably to create a static variables for the global resources. When my trigger method is called the first time, initialize those variables. In subsequent calls just use it. Will this work, and is there any other way?

What I would have loved is a general trigger interface which I could implement for my trigger.

Improve this question
3
  • 2
    Doing this exactly as you described will get "interesting" when multiple connections try to call your code concurrently. Either you hamper performance by heavy serializing or you will run into concurrency errors. Commented Dec 23, 2011 at 12:11
  • Isn't that impossible, though? PostgreSQL forks backends for each connection and AFAIK each one gets its own PL/Java JVM and therefore its own copy of any static member variables. Commented Dec 24, 2011 at 4:25
  • @CraigRinger in this specific scenario (sending an RPC out) - it should be acceptable if each separate backend sends out the RPCs separately. In general - you are right, a truly global resource cannot be available in memory. Commented Dec 24, 2011 at 6:04

2 Answers 2

Reset to default
3

Even if you'd do it - it would be "global" only for given session - not across many sessions.

But - the point is moot, because, as I see it, the approach it wrong.

Instead of adding trigger which connects to external system, I would much rather:

  1. create trigger that uses NOTIFY to inform about the change
  2. add application that LISTENs for notifications and does the remote job.
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I had performance related reservations to your approach - that notify doesn't allow sending old or new row data, and that jdbc implementation uses polling for notify instead of being async.
Also...what is meant by a session?
@Aman If you're using non-SSL connections you can use non-blocking polling for notify at rather low cost, though yes it is still polling. In PostgreSQL 9.1 (maybe 9.0 too?) you can send row data in NOTIFY. I don't know how PgJDBC handles it when it receives it though or whether it understands records. Personally I'd `notify' with a changed PK and INSERT the change record into an audit table - but that's just me.
@Aman An PostgreSQL a session is a connection; the "session" is the information about the state of the connection like transaction state, GUC settings, logged in user, etc. Each postgres.exe backend has a single connection that's running a single session.
@CraigRinger I don't think SSL is a requirement. BTW, once the records are in the audit table, you will still need to poll that table to get the work done, right? PgJDBC delivers the payload as a string - which is fine for me because I will just serialize the data.
|
0

Based on answer by depesz, and some more searching on the internet - this is the best solution to my situation:

There is way for a client to issue LISTEN xyz; statement to the server. Then NOTIFY xyz; statement can be issued at the server, and the client will be notified. xyz is used to indentify what happened. The NOTIFY statement also allows a 'payload' to be sent with it (supported only on PostgreSQL 9+, the latest version as of now).

A reasonable implementation will be to associate a trigger based on the update to the table. That trigger issues a NOTIFY statement to the client, and the payload contains the updated row data.

There is one drawback with this implementation - that the JDBC driver (java interface to connect to PostgreSQL server) doesn't support truly asynchronous behaviour. The listener has to issue a dummy query to connect to the backend (update - dummy query not required: https://groups.google.com/g/pgsql.interfaces.jdbc/c/BnHNs7HgBY0?pli=1#3ff1a432f47d1b3c. Still polling though), and only then all the pending notifications will be delivered to the client. Also note that LISTEN/NOTIFY extensions to SQL are specific to PostgreSQL only, and are not part of the SQL standard.

References: NOTIFY documentation for PostgreSQL, JDBC documentation page for listen/notify

Improve this answer

1 Comment

Can you please tell how did you send updated row data as payload using NOTIFY command? Payload accepts only String literal.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.