CODEWITHSUNDEEP
python-3.xpsycopg2postgresql-10
Paabo
Paabo

Reputation: 41

Python psycopg2 executing select pg_notify doesn't work

This is my very first question at StackOverflow so if i am doing something wrong please be gentle.

I'm struggling with executing SELECT pg_notify from Python script. It seems that it doesn't work at all.

My NodeJS server is listening 'testnotify' channel using pg-promise. I'm putting this just for completeness because it is working.

db.connect({direct: true})
.then(sco => {
    sco.client.on('notification', data => {
        console.log('Received:', data);
    });
    return sco.none('LISTEN $1~', 'testnotify');
})
.catch(error => {
    console.log('Error:', error);
});

My Python script should rise notification after series of successful db operations.

I'm doing this like that

conn = psycopg2.connect(conn_string)
cur = conn.cursor()
cur.execute("SELECT pg_notify('%s', '%s');" % ('testnotify', 'blabla'))

or like that

query = "SELECT pg_notify('testnotify', 'blabla');"
print(query)
cur.execute(query)

I've tried in similar way with NOTIFY testnotify, 'blabla' and nothing works. Nothing happen at NodeJS side. But when i copy result of print(query) from Python console and execute it directly from PostgreSQL then it is working like a charm. I don't understand what's wrong with my code.

I'm using psycopg2 2.7.5, PostgreSQL 10, Node 10 LTS, pg-promise at Windows 10.

Side note: This is not a problem with Node because it is working when pg_notify or notify is raised using trigger at source table in postgresql or when executing notification as regular sql query at db. It is not working only when i'm trying to raise notification from python script.

After two days of juggling with this i think that this is something obvious and stupid but i can't see it. Or maybe it is just impossible...

Please help.

Upvotes: 3

Views: 1732

Answers (1)

Paabo
Paabo

Reputation: 41

Oh my... i just found solution... even two ways to solve this.

The clue was in psycopg documentation... obvious huh?

to send notification using

cur.execute("SELECT pg_notify('%s', '%s');" % ('testnotify', 'blabla'))

one have to set connection to autocommit like that

import psycopg2
import psycopg2.extensions
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

or simply if you don't want autocommit then after doing

cur.execute("SELECT pg_notify('%s', '%s');" % ('testnotify', 'blabla'))

you have to commit it

conn.commit()

aaand now Node is receiving notifications from postgresql via python

Upvotes: 1

Related Questions