Hi All,
I have noticed that any SQL query executed immediately after attaching
to a particular debugging target (pldebugger) either takes longer time
for execution or it hangs on Windows. I have checked it on
PostgreSQL-9.5 and PostgreSQL-9.6 versions and have found that the
issue only exists on 9.6 version. After doing 'git bisect' i found
that the following git commit in PostgreSQL-9.6 branch is the culprit.
commit *98a64d0bd713cb89e61bef6432befc*
4b7b5da59e
Author: Andres Freund <and...@anarazel.de>
Date: Mon Mar 21 09:56:39 2016 +0100
Introduce WaitEventSet API.
Commit ac1d794 ("Make idle backends exit if the postmaster dies.")
introduced a regression on, at least, large linux systems. Constantly
adding the same postmaster_alive_fds to the OSs internal datastructures
for implementing poll/select can cause significant contention; leading
to a performance regression of nearly 3x in one example.
This can be avoided by using e.g. linux' epoll, which avoids having to
add/remove file descriptors to the wait datastructures at a high rate.
Unfortunately the current latch interface makes it hard to allocate any
persistent per-backend resources.
.................
Following are the steps to reproduce the issue:
1) Download pldebugger from below url and copy it into contrib directory.
git clone git://git.postgresql.org/git/pldebugger.git
2) Start a new backend session (psql -d postgres)
3) Create a plpgsql function say func1();
4) Get the oid of the func1 and enable debugging of this using pldbgapi
function as shown below
select plpgsql_oid_debug(16487);
5) execute function func1 : select func1();
After executing above query we will get the message as below and
terminal will not respond as it will go in listen mode.
NOTICE: PLDBGBREAK:2
6) Start another backend session.
7) Execute below query.
SELECT * FROM pldbg_attach_to_port(2)
NOTE: We need to extract the port number from step 5 NOTICE message
after 'PLDBGBREAK:' string and use as input here.
8) Execute any SQL query now and the problem starts. I have tried with
below queries.
SELECT 1;
OR
SELECT pg_backend_pid();
OR
SELECT FROM pldbg_wait_for_breakpoint(1::INTEGER);
....
Problem Analysis:
-------------------------
Allthough i am very new to Windows, i tried debugging the issue and
could find that Backend is not receiving the query executed after
"SELECT pldbg_attach_to_port(2)" and is infinitely waiting on
"WaitEventSetWaitBlock()" at WaitForMultipleObjects() to read the
input command. Below is the backtrace for the same.
postgres.exe!WaitEventSetWaitBlock(WaitEventSet * set, int
cur_timeout, WaitEvent * occurred_events, int nevents) Line 1384 +
0x2b bytes C
postgres.exe!WaitEventSetWait(WaitEventSet * set, long timeout,
WaitEvent * occurred_events, int nevents) Line 936 + 0x18 bytes C
postgres.exe!secure_read(Port * port, void * ptr, unsigned __int64
len) Line 168 C
postgres.exe!pq_recvbuf() Line 921 + 0x33 bytes C
postgres.exe!pq_getbyte() Line 963 + 0x5 bytes C
postgres.exe!SocketBackend(StringInfoData * inBuf) Line 334 + 0x5 bytes C
postgres.exe!ReadCommand(StringInfoData * inBuf) Line 507 + 0xa bytes C
postgres.exe!PostgresMain(int argc, char * * argv, const char *
dbname, const char * username) Line 4004 + 0xd bytes C
postgres.exe!BackendRun(Port * port) Line 4259 C
postgres.exe!SubPostmasterMain(int argc, char * * argv) Line 4750 C
postgres.exe!main(int argc, char * * argv) Line 216 C
postgres.exe!__tmainCRTStartup() Line 555 + 0x19 bytes C
postgres.exe!mainCRTStartup() Line 371 C
With Regards,
Ashutosh Sharma
EnterpriseDB: http://www.enterprisedb.com
