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.

Aman
  • 639
  • 1
  • 9
  • 25
  • 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. – A.H. Dec 23 '11 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. – Craig Ringer Dec 24 '11 at 04: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. – Aman Dec 24 '11 at 06:04

2 Answers2

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.
  • 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. – Aman Dec 23 '11 at 14:45
  • Also...what is meant by a session? – Aman Dec 23 '11 at 14:48
  • @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. – Craig Ringer Dec 24 '11 at 04:27
  • @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. – Craig Ringer Dec 24 '11 at 04:28
  • Thanks @CraigRinger and depesz. I will try the approaches you mentioned. I still wish the trigger framework was more powerful - this my current requirement, but later I may need to do something else on an update. Only allowing a static function call is too limiting. – Aman Dec 24 '11 at 05:42
  • 1
    @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. – Aman Dec 24 '11 at 10:23
  • @CraigRinger Can you please tell me how to send row data as payload in NOTIFY? NOTIFY only accepts string literal as payload. – Anvesh Raavi Jul 08 '15 at 19:53
  • @Raavi Send the primary key as the payload. Fetch the row data from a table by looking it up by primary key. – Craig Ringer Jul 08 '15 at 23:53
  • @CraigRinger can you please give me an example on how to send primary key as payload? Payload doesn't accept neither stored procedure variables nor select statements – Anvesh Raavi Jul 09 '15 at 00:05
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: http://goo.gl/VbFQg. 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

Aman
  • 639
  • 1
  • 9
  • 25
  • Can you please tell how did you send updated row data as payload using NOTIFY command? Payload accepts only String literal. – Anvesh Raavi Jul 08 '15 at 19:25