View Issue Details

IDProjectCategoryView StatusLast Update
0000182Pgpool-IIBugpublic2016-03-29 16:11
Reportertoni.baumannAssigned Tot-ishii 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionopen 
PlatformLinux OSCentosOS VersionCentos 6.7
Product Version 
Target VersionFixed in Version 
Summary0000182: Pgpool child hangs with full cpu load after permission error message from backend
DescriptionAfter updating pgpool from pgpool-II-94-3.4.3-2.rhel6 to 3.5.0-1.rhel6 we are experiencing high cpu load and stuck pgpool processes after running a daily sql batch job. The frontend client is a software called PGNP 1.3.0 linking a MS SQL db with a Postgresql 9.4 server. During an UPDATE statement where the user has not enough permissions PGNP receives the forwarded error message from pgpool and tries to go back to a set savepoint, waiting for further communication from pgool and running into a timeout after 60 secs. The pgpool process shows 100% cpu load and state 'idle in transaction'
Steps To Reproduce1. Execute 'UPDATE OPSDCL_box_status SET CODE = 'TEST' WHERE id = 8' on MS SQL with user without required permissions
2. Wait for timeout of query (60 sec)
Additional InformationBacktrace of hanging pgpool process:
Core was generated by `pgpool: ttmis dcl 192.168.3.90(1102) idle in transaction '.
#0 0x0000000000452749 in pool_ssl_pending (cp=0xbde750) at utils/pool_ssl.c:316
316 if (cp→ssl_active > 0 && SSL_pending(cp→ssl) > 0)
(gdb) bt
#0 0x0000000000452749 in pool_ssl_pending (cp=0xbde750) at utils/pool_ssl.c:316
0000001 0x0000000000428cbf in is_cache_empty (frontend=0xbde750, backend=0xbdd6f0) at protocol/pool_process_query.c:3014
0000002 0x000000000042f534 in pool_process_query (frontend=<value optimized out>, backend=0xbdd6f0, reset_request=0) at protocol/pool_process_query.c:270
0000003 0x0000000000427340 in do_child (fds=0xbdce20) at protocol/child.c:370
0000004 0x000000000040660d in fork_a_child (fds=0xbdce20, id=31) at main/pgpool_main.c:678
0000005 0x000000000040aa5d in PgpoolMain (discard_status=<value optimized out>, clear_memcache_oidmaps=<value optimized out>) at main/pgpool_main.c:320
0000006 0x000000000040530e in main (argc=<value optimized out>, argv=<value optimized out>) at main/main.c:310


ssl is off in pgpool.conf

With pgpool 3.4 there is no problem, the error message is returned instantly and no high cpu load is caused.

Sequence of sql statements PGNP is sending:
START TRANSACTION ISOLATION LEVEL READ COMMITTED
SAVEPOINT PGNP_NESTED_TRANS
DECLARE pgnpcsr283 CURSOR FOR SELECT * FROM “box_status”
FETCH FORWARD 100 FROM pgnpcsr283
UPDATE “public".“box_status” SET “code” = $1 WHERE “id” = $2

t+30 secs
ROLLBACK TO SAVEPOINT PGNP_NESTED_TRANS

t+60 secs
RELEASE SAVEPOINT PGNP_NESTED_TRANS

We could not replicate this on the command line with psql.
TagsNo tags attached.

Activities

toni.baumann

2016-03-24 18:54

reporter  

pgpooldebug.log (73,982 bytes)

toni.baumann

2016-03-24 19:00

reporter  

pgpool.conf (2,258 bytes)

t-ishii

2016-03-24 21:15

developer   ~0000717

Multiple query stack problems with 3.5.0 have been identified. Please try the 3.5 stable head.

http://git.postgresql.org/gitweb/?p=pgpool2.git;a=shortlog;h=refs/heads/V3_5_STABLE

toni.baumann

2016-03-25 00:34

reporter   ~0000718

I tried with V3_5_STABLE, seems like it's working. Will get back to you after the weekend and a few nightly sql jobs.

toni.baumann

2016-03-29 15:51

reporter   ~0000740

I can confirm that the described issue does not occur anymore. Will there be a corresponding release in the near future?

t-ishii

2016-03-29 16:04

developer   ~0000742

Next minor releases will be in public on April 1st.

toni.baumann

2016-03-29 16:06

reporter   ~0000743

Thank you for your support!

Issue History

Date Modified Username Field Change
2016-03-24 18:54 toni.baumann New Issue
2016-03-24 18:54 toni.baumann File Added: pgpooldebug.log
2016-03-24 19:00 toni.baumann File Added: pgpool.conf
2016-03-24 21:09 t-ishii Assigned To => t-ishii
2016-03-24 21:09 t-ishii Status new => feedback
2016-03-24 21:15 t-ishii Note Added: 0000717
2016-03-25 00:34 toni.baumann Note Added: 0000718
2016-03-25 00:34 toni.baumann Status feedback => assigned
2016-03-25 09:28 t-ishii Status assigned => feedback
2016-03-29 15:51 toni.baumann Note Added: 0000740
2016-03-29 15:51 toni.baumann Status feedback => assigned
2016-03-29 16:04 t-ishii Note Added: 0000742
2016-03-29 16:06 toni.baumann Note Added: 0000743
2016-03-29 16:11 t-ishii Status assigned => resolved