Real life use case of Postgres' LISTEN/NOTIFY

A Postgres noob asked me: what's the real time (sic) use of LISTEN and NOTIFY? And here's how the conversation went:


noob:  what's the real time use of LISTEN and NOTIFY?

 

in postgresql

 

me:  Well, that is a very useful facility.

 

Say you have two applications, one inserts some data and the other reads and acts on that data..

 

In a normal setup you would have the reading application querying the DB every few seconds to see if new data has come in..

 

This incurs overhead on the dataabase.

 

In Postgres you can use LISTEN/NOTIFY to achieve the same thing without having to 'poll' the database every few seconds.

 

The reading application can register its interest in new data by saying 'LISTEN newdata'

 

And the writing application, whenever it inserts new data, can send  anotification, saying 'NOTIFY newdata'

 

At this point, Postgres will send a message to the reading application that 'newdata' has arrived, and now the reading application query the database to process newly inserted data.

 

I hope that explains it.

noob:  thank you Gurjeet, that's really good

 

Happy Weekend

Postgres can, Oracle can't (create duplicate indexes)

This is in the series of "Postgres can, Oracle can't" articles. This one is not really something Oracle can't do, but probably something they chose not to.

If you try to create an index on a set of columns that already have an index, then Oracle will throw an error. But Postgres allows you to do this (probably because of partial indexes, or probably in the spirit of Open Source).

This can be leveraged in routine REINDEX operations. If you try to reindex an index in Postgres, it takes such a lock on the underlying table that INSERT/UPDATE/DELETE operations on that table are blocked, hence causing probable application downtime.

So, combining three of Postgres' unique features, we can re-index indexes without causing application downtime; these features are:
1. DDL obeys transactions
2. Ability to create duplicate indexes
3. Ability to create indexes concurrently.

CREATE INDEX CONCURRENTLY command allows you to create an index in such a way that other sessions are allowed INSERT/UPDATE/DELETE operations.

/* Concurrent index creation canot work in a transaction */

create index concurrently temp_emp_deptno on emp(dept);
begin transaction;
alter index emp_deptno rename to dropped_emp_deptno;
alter index temp_emp_deptno rename to emp_dropped;
drop index dropped_emp_deptno;
commit transaction;

Please refer to he CREATE INDEX documentation for caveats of using CREATE INDEX CONCURRENTLY command. Also, this method does not lend itself to reindexing Primary Key indexes.

Creating an unmodifiable table in Postgres

Here's a simple and effective way of avoiding any accidental INSERT/UPDATE/DELETE operation against any table:

(developed this as part of Postgres porting of Spacewalk )

CREATE TABLE dual ( dummy char );
INSERT INTO dual values ( 'X' );
CREATE OR REPLACE RULE insert_dual AS ON INSERT TO dual DO INSTEAD NOTHING;
CREATE OR REPLACE RULE update_dual AS ON UPDATE TO dual DO INSTEAD NOTHING;
CREATE OR REPLACE RULE delete_dual AS ON DELETE TO dual DO INSTEAD NOTHING;