Tutorial: Custom actions

Note: Check the basic tutorial first if you haven't already.

Running custom SQL commands

If you want to trigger some custom actions for example: REFRESH MATERIALIZED VIEWS, invalidate cache data etc by executing SQL on receiving NOTIFY from postgres you can do it pretty easily using handler functions.

For instance let's create a MATERIALIZED VIEW in the test database we've created in the basic tutorial.

DROP MATERIALIZED VIEW IF EXISTS vw_users_m;

CREATE MATERIALIZED VIEW vw_users_m AS SELECT * FROM users;

Now, try inserting few rows on the users table:

INSERT INTO users(name, email) VALUES('New User 1', 'newuser1@gmail.com');
INSERT INTO users(name, email) VALUES('New User 2', 'newuser2@gmail.com');

If you now run SELECT * FROM vw_users_m you'll notice that it doens't show these two newly added rows. This is because it's a MATERIALIZED VIEW, and in order for it to show the latest data it has to be refreshed once any data has changed in the master table.

As we have already setup LISTEN/NOTIFY thing with pglistend for this database in the previous tutorial we can do it pretty easily here.

We just have to fire the REFRESH command from the listener script callbacks we've registered for the channel we're listening to.

Just open your listener script and update the following lines of code:

module.exports = function(h) {
    // Handler for the channel 'update'
    return {
        'update': function(payload) {
            console.log('Invoked with payload', payload);

            h.log('Trying to refresh the materialize view');

            // Execute the query
            h.query('REFRESH MATERIALIZED VIEW vw_users_m').then(() => {
                 h.log('Finished refreshing the materialized view'));
            }
        }
    };
};

Since we've altered these callbacks we need to reload our daemon to reload all the configurations. So, run this:

$ sudo systemctl restart pglistend

Now let's test this thing.

Open up the logs in a terminal. Notice the -f flag, we're following the logs.

$ journalctl -f -u pglistend

And from a new terminal session try adding more data to the users table.

INSERT INTO users(name, email) VALUES('New User 3', 'newuser3@gmail.com');

Now go back to the terminal where we're checking logs, it should show the following new log entries:

To ensure our view is refreshed you may now run SELECT * FROM vw_users_m once again it should show you the latest data.

Also, you can try now UPDATE and DELETE operations, and the view should still be refreshed automatically since we have setup our script to take care of that.