NOTIFY in multi-statement PQexec() not sent outside of transaction

From: John Muehlhausen <jgm(at)jgm(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: NOTIFY in multi-statement PQexec() not sent outside of transaction
Date: 2020-04-20 17:13:12
Message-ID: CACk8hr6At5fr7Qscy8LWT+oBs-_SYw1QvonsAU-6srYHsKuywg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

macOS 10.13.6 (17G11023)
PostgreSQL 10.5 (Debian 10.5-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

Recreate with the following script. Expected behavior with TEST_DSN set to
your database. To see the broken behavior also set TEST_BREAK=1
---

# python 3.6
# set TEST_DSN env var

import os
import sys
import select
import psycopg2 # 2.8.4
from multiprocessing import Process, Queue

def notifier(input,output):
with psycopg2.connect(os.environ['TEST_DSN']) as conn:
conn.autocommit=True
with conn.cursor() as cur:
input.get()
if 'TEST_BREAK' in os.environ:
# docs seem to indicate that an implied transaction
# will not exist if there is an explicit begin/commit?
cur.execute(("notify __test; "
"begin; select pg_advisory_lock(7777); "
"select pg_advisory_unlock(7777); commit"))
else:
# this version works but introduces a race condition
# into my test suite where I am not sure that I am "in"
# a PQexec() but might instead be between such calls
cur.execute("notify __test")
cur.execute(( "select pg_advisory_lock(7777); "
"select pg_advisory_unlock(7777)"))
print('notifier unlocked', file=sys.stderr)
output.put(())

with psycopg2.connect(os.environ['TEST_DSN']) as conn:
conn.autocommit=True
with conn.cursor() as cur:
cur.execute("listen __test; select pg_advisory_lock(7777)")

input, output = Queue(), Queue()
p = Process(target=notifier, args=(output,input))
p.start()
output.put(())

print('waiting for notification',file=sys.stderr)
select.select([conn],[],[])
conn.poll()
print('notification received',file=sys.stderr)

cur.execute("select pg_advisory_unlock(7777)")

input.get()
p.join()

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Roman Peshkurov 2020-04-20 17:40:06 Bug with memory leak on cert validation in libpq
Previous Message Fujii Masao 2020-04-20 15:42:48 Re: pg_stat_statements: rows not updated for CREATE TABLE AS SELECT statements