<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">What error message do you see on the backend (postgresql) server?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I am having a similar issue, whereby the select has_function_privilege is failing, causing the connection to the postgres server to drop (the error kills the
connection.) So, what’s in the postgres log? (You may want to turn the logging level up in postgresql.conf).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> pgpool-general-bounces@pgpool.net [mailto:pgpool-general-bounces@pgpool.net]
<b>On Behalf Of </b>Kristjan Siimson<br>
<b>Sent:</b> Friday, January 17, 2014 8:20 AM<br>
<b>To:</b> pgpool-general@pgpool.net<br>
<b>Subject:</b> [pgpool-general: 2445] Segfault in pgpool triggered by a background query<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Sorry, my previous e-mail was sort of useless, I really did have an invalid query, so my description was misleading.<br>
<br>
I did some more digging and found this:<br>
<br>
2014-01-17 13:34:04 DEBUG: pid 3668: ProcessFrontendResponse: kind from frontend Q(51)
<br>
2014-01-17 13:34:04 DEBUG: pid 3668: pool_unset_doing_extended_query_message: done
<br>
2014-01-17 13:34:04 DEBUG: pid 3668: statement2: insert into account (id, bid, balance, filler) values (1, 1, 0.0, "FRESH ACCOUNT");
<br>
2014-01-17 13:34:04 DEBUG: pid 3668: pool_set_query_in_progress: done <br>
2014-01-17 13:34:04 DEBUG: pid 3668: do_query: extended:0 query:SELECT count(*) from (SELECT has_function_privilege('kristjan', 'pgpool_regclass(cstring)', 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pgpool_regclass')) AS
s <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_read: EOF encountered with backend <br>
2014-01-17 13:34:04 ERROR: pid 3668: do_query: error while reading message kind <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_search_relcache: do_query failed <br>
2014-01-17 13:34:04 DEBUG: pid 3668: do_query: extended:0 query:SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%now()%' OR d.adsrc LIKE '%''now''::text%') AND (a.atttypid = 'timestamp'::regtype::oid OR a.atttypid = 'timestamp with time zone'::regtype::oid
OR a.atttypid = 'date'::regtype::oid OR a.atttypid = 'time'::regtype::oid OR a.atttypid = 'time with time zone'::regtype::oid) , false) FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid = d.adrelid AND
a.attnum = d.adnum) WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.relname = 'account' ORDER BY a.attnum
<br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_read: EOF encountered with backend <br>
2014-01-17 13:34:04 ERROR: pid 3668: do_query: error while reading message kind <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_search_relcache: do_query failed <br>
2014-01-17 13:34:04 DEBUG: pid 3668: wait_for_query_response: waiting for backend 1 completing the query
<br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_read: EOF encountered with backend <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_read: EOF encountered with backend <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_read: EOF encountered with backend <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_read: EOF encountered with backend <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_read: EOF encountered with backend <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_read: EOF encountered with backend <br>
2014-01-17 13:34:04 ERROR: pid 3668: read_kind_from_backend: failed to read kind from 1 th backend
<br>
2014-01-17 13:34:04 LOG: pid 3668: do_child: exits with status 1 due to error <br>
2014-01-17 13:34:04 ERROR: pid 3668: pool_flush_it: write failed to backend (1). reason: Broken pipe offset: 0 wlen: 5
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: I am 3671 accept fd 6 <br>
2014-01-17 13:34:04 DEBUG: pid 3671: read_startup_packet: application_name: psql <br>
2014-01-17 13:34:04 DEBUG: pid 3671: Protocol Major: 3 Minor: 0 database: bench_parallel user: kristjan
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: new_connection: connecting 0 backend <br>
2014-01-17 13:34:04 DEBUG: pid 3671: new_connection: skipping slot 0 because backend_status = 3
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: new_connection: connecting 1 backend <br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_ssl: SSL requested but SSL support is not available
<br>
2014-01-17 13:34:04 DEBUG: pid 3639: reap_handler called <br>
2014-01-17 13:34:04 DEBUG: pid 3639: reap_handler: call wait3 <br>
2014-01-17 13:34:04 DEBUG: pid 3639: child 3668 exits with status 256 <br>
2014-01-17 13:34:04 DEBUG: pid 3639: fork a new child pid 3675 <br>
2014-01-17 13:34:04 DEBUG: pid 3639: reap_handler: normally exited <br>
2014-01-17 13:34:04 DEBUG: pid 3675: I am 3675 <br>
2014-01-17 13:34:04 DEBUG: pid 3675: pool_initialize_private_backend_status: initialize backend status
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_read_message_length: slot: 1 length: 8 <br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_do_auth: auth kind:0 <br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_read_message_length2: master slot: 1 length: 26
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: 1 th backend: name: application_name value: psql
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_read_message_length2: master slot: 1 length: 25
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: 1 th backend: name: client_encoding value: UTF8
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_read_message_length2: master slot: 1 length: 23
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: 1 th backend: name: DateStyle value: ISO, MDY
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_read_message_length2: master slot: 1 length: 25
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: 1 th backend: name: integer_datetimes value: on
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_read_message_length2: master slot: 1 length: 27
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: 1 th backend: name: IntervalStyle value: postgres
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_read_message_length2: master slot: 1 length: 20
<br>
2014-01-17 13:34:04 DEBUG: pid 3671: 1 th backend: name: is_superuser value: on <br>
2014-01-17 13:34:04 DEBUG: pid 3671: pool_read_message_length2: master slot: 1 length: 25<br>
<br>
And with more digging I found that pgpool really does not "like" the following query regardless of whether the backend was another instance of pgpool or a Postgre server:<br>
<br>
Query:<br>
<br>
bench_parallel=# SELECT count(*) from (SELECT has_function_privilege('kristjan', 'pgpool_regclass(cstring)', 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pgpool_regclass')) AS s;
<br>
The connection to the server was lost. Attempting reset: Succeeded. <br>
<br>
Output from pgpool daemon in debug mode, note the segfault:<br>
<br>
2014-01-17 15:01:52 DEBUG: pid 3569: ProcessFrontendResponse: kind from frontend Q(51)
<br>
2014-01-17 15:01:52 DEBUG: pid 3569: pool_unset_doing_extended_query_message: done
<br>
2014-01-17 15:01:52 DEBUG: pid 3569: statement2: SELECT count(*) from (SELECT has_function_privilege('kristjan', 'pgpool_regclass(cstring)', 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pgpool_regclass')) AS s;
<br>
2014-01-17 15:01:52 DEBUG: pid 3569: pool_set_query_in_progress: done <br>
2014-01-17 15:01:52 DEBUG: pid 3569: initSelectStmt: ANALYZE now(0) <br>
2014-01-17 15:01:52 DEBUG: pid 3569: initSelectStmt: ANALYZE now(1) <br>
2014-01-17 15:01:52 DEBUG: pid 3569: inside build_range_info num= 1 current_select=1
<br>
2014-01-17 15:01:52 DEBUG: pid 3569: inside build_range_info const or func 1 <br>
2014-01-17 15:01:52 DEBUG: pid 3569: initSelectStmt: ANALYZE now(2) <br>
2014-01-17 15:01:52 DEBUG: pid 3569: inside build_range_info num= 1 current_select=2
<br>
2014-01-17 15:01:52 DEBUG: pid 3569: inside build_range_info const or func 2 <br>
2014-01-17 15:01:52 DEBUG: pid 3569: inside build_virtual_info no dist state=E p <br>
2014-01-17 15:01:52 DEBUG: pid 3556: I am 3556 accept fd 10 <br>
2014-01-17 15:01:52 DEBUG: pid 3556: read_startup_packet: application_name: psql <br>
2014-01-17 15:01:52 DEBUG: pid 3556: Protocol Major: 3 Minor: 0 database: bench_parallel user: kristjan
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: new_connection: connecting 0 backend <br>
2014-01-17 15:01:52 DEBUG: pid 3556: new_connection: connecting 1 backend <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_ssl: SSL requested but SSL support is not available
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_ssl: SSL requested but SSL support is not available
<br>
2014-01-17 15:01:52 DEBUG: pid 3516: reap_handler called <br>
2014-01-17 15:01:52 DEBUG: pid 3516: reap_handler: call wait3 <br>
2014-01-17 15:01:52 ERROR: pid 3516: Child process 3569 was terminated by segmentation fault
<br>
2014-01-17 15:01:52 DEBUG: pid 3516: child 3569 exits with status 11 by signal 11
<br>
2014-01-17 15:01:52 DEBUG: pid 3516: fork a new child pid 3570 <br>
2014-01-17 15:01:52 DEBUG: pid 3516: reap_handler: normally exited <br>
2014-01-17 15:01:52 DEBUG: pid 3570: I am 3570 <br>
2014-01-17 15:01:52 DEBUG: pid 3570: pool_initialize_private_backend_status: initialize backend status
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length: slot: 0 length: 8 <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length: slot: 1 length: 8 <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_do_auth: auth kind:0 <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 26
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 26
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: application_name value: psql
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: application_name value: psql
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 25
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 25
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: client_encoding value: UTF8
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: client_encoding value: UTF8
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 23
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 23
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: DateStyle value: ISO, MDY
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: DateStyle value: ISO, MDY
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 25
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 25
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: integer_datetimes value: on
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: integer_datetimes value: on
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 27
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 27
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: IntervalStyle value: postgres
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: IntervalStyle value: postgres
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 20
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 20
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: is_superuser value: on <br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: is_superuser value: on <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 25
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 25
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: server_encoding value: UTF8
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: server_encoding value: UTF8
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 25
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 25
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: server_version value: 9.3.2
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: server_version value: 9.3.2
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 35
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 35
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: session_authorization value: kristjan
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: session_authorization value: kristjan
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 35
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 35
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: standard_conforming_strings value: on
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: standard_conforming_strings value: on
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 0 length: 28
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length2: master slot: 1 length: 28
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 0 th backend: name: TimeZone value: Europe/Tallinn
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: 1 th backend: name: TimeZone value: Europe/Tallinn
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length: slot: 0 length: 12
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length: slot: 1 length: 12
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_do_auth: cp->info[i]:0x7f5eca6b7000 pid:10951
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_do_auth: cp->info[i]:0x7f5eca6b7088 pid:10298
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_send_auth_ok: send pid 10298 to frontend
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: select_load_balancing_node: selected backend id is 1
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: selected load balancing node: 1 <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_unset_query_in_progress: done <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_unset_command_success: done <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_unset_writing_transaction: done <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_unset_failed_transaction: done <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_unset_transaction_isolation: done <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_unset_skip_reading_from_backends: done <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_unset_ignore_till_sync: done <br>
2014-01-17 15:01:52 DEBUG: pid 3556: read_kind_from_backend: kind: Z from 0 th backend
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: read_kind_from_backend: kind: Z from 1 th backend
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: ProcessBackendResponse: kind from backend: Z
<br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length: slot: 0 length: 5 <br>
2014-01-17 15:01:52 DEBUG: pid 3556: pool_read_message_length: slot: 1 length: 5 <br>
2014-01-17 15:01:52 DEBUG: pid 3556: ReadyForQuery: transaction state: <br>
2014-01-17 15:01:52 DEBUG: pid 3556: ReadyForQuery: transaction state:I <br>
2014-01-17 15:01:52 DEBUG: pid 3556: ProcessBackendResponse: Ready For Query <br>
2014-01-17 15:01:52 DEBUG: pid 3570: pool_ssl: SSL requested but SSL support is not available
<br>
2014-01-17 15:01:52 DEBUG: pid 3570: s_do_auth: auth kind: 0 <br>
2014-01-17 15:01:52 DEBUG: pid 3570: s_do_auth: backend key data received <br>
2014-01-17 15:01:52 DEBUG: pid 3570: s_do_auth: transaction state: I <o:p></o:p></p>
</div>
<p class="MsoNormal">The nodes output the following. The error happened also before installation of pgpool_regclass plus I was able to run 'CREATE EXTENSION' pgpool_regclass through pgpool.<br>
<br>
bench_parallel=# SELECT count(*) from (SELECT has_function_privilege('kristjan', 'pgpool_regclass(cstring)', 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pgpool_regclass')) AS s;<br>
count <br>
-------<br>
1<br>
(1 row)<o:p></o:p></p>
</div>
</div>
</body>
</html>