How to receive automatic notifications about changes in tables?

Question

What I am using: PostgreSQL and Python. I am using Python to access PostgreSQL

What I need: Receive a automatic notification, on Python, if anyone records something on a specific table on database.

I think that it is possible using a routine that go to that table, over some interval, and check changes. But it requires a loop and I would like something like an a assynchronous way.

Is it possible?

Answer 1

donmage is quite right - LISTEN and NOTIFY are what you want. You'll still need a polling loop, but it's very lightweight, and won't cause detectable server load.

If you want psycopg2 to trigger callbacks at any time in your program, you can do this by spawning a thread and having that thread execute the polling loop. Check to see whether psycopg2 enforces thread-safe connection access; if it doesn't, you'll need to do your own locking so that your polling loop only runs when the connection is idle, and no other queries interrupt a polling cycle. Or you can just use a second connection for your event polling.

Either way, when the background thread that's polling for notify events receives one, it can invoke a Python callback function supplied by your main program, which might modify data structures / variables shared by the rest of the program. Beware, if you do this, that it can quickly become a nightmare to maintain.

If you take that approach, I strongly suggest using the multithreading / multiprocessing modules. They will make your life massively easier, providing simple ways to exchange data between threads, and limiting modifications made by the listening thread to simple and well-controlled locations.

If using threads instead of processes, it is important to understand that in cPython (i.e. "normal Python") you can't have a true callback interrupt, because only one thread may be executing in cPython at once. Read about the "global interpreter lock" (GIL) to understand more about this. Because of this limitation (and the easier, safer nature of shared-nothing by default concurrency) I often prefer multiprocessing to multithreading.

Answer 2

The commands you are looking for are probably LISTEN/NOTIFY.

If you are using psycopg, check out the section Asynchronous Notifications.

[This does require polling though.]