Question:
I have a PostgreSql 9.3 database which contains three tables with no links between them (such as foreign keys). Two Java 8 applications are connected to this database, one running on a client and the other on the server where the database is. The application on the server needs to know if only one of the database tables is being modified.
I was playing around with the code on this website , and I was able to receive notifications in my sample app, even sending them from the pgAdmin III sql scripts window just by running "Notify mymessage" (in my case, it worked without needing to do "Select 1") ).
The good thing about this method is that it would be enough for the client application to send notifications every time it modifies the table that the application is interested in on the server.
The downside of this method is that the application on the server does not know which record is being modified, inserted or deleted.
Is there a way to send, along with the notification, data such as the primary key of the registry involved?
Answer:
I had the same problem once, the solution I came up with was to use an INSERT and UPDATE trigger that sent something like this:
PERFORM pg_notify('nombredetabla', NEW.campoidentificador);
Then, by listening to the nombredetabla channel, it would receive notifications from the records and by looking at the payload it would know which record was updated.