Thread: attempting to retrieve column names from information schema fails.
Hi, I'm looking on some insights on the following problem on retrieving table column names from the information schema ( postgres9.6.6 FreeBSD 11.1 ) When my client starts up up. Each thread ( 10 ) run this command. select column_name from information_schema.columns where table_name = 'pep_port_log'; I have encountered two instances of testing Where the server does not respond to that command. Otherwise I can interact with the database. I end up restoring the database to recover. At the point of failure, Using pgamdinIII , I find the information schema is visible. In general any catalog "column" relatedtable fails to respond with data. ( I end up cancelling the query, as it just appears to hang. Normal response would be a second or two ) The tables that it should be representing i.e. pep_port_log is present and viewable in it's schema. A sample of other non-column related objects return data in the information schema. Shutting down the database/postgres and restarting does not clear the issue. I do not recall seeing any locks that would help explain the issue. Nothing yet has caught my eye in the postgres log related to this circumstance. I'll add that this all works 99.99% of the time. I have only experienced this twice, while exploring load testing of thetotal system. The client side was started, running and has coredumped. The client side has a pool of connections (10) that as a matterof routine Drop and re-establish a connection on a round robin basis. One of the threads may have been running the above command atthe time the client coredumps in an unrelated matter. It is on the subsequent restart of the client that this command starts to failand the client fails to reach a runnable condition as it blocks on this query. I have no clue the point at which the information_schema. column relations got out of wack. Any suggestions as to how these column related relations might become corrupted or Debug/inspection measure that I should attempt on the next rare occurrence? Best Regards Dave Day
"Day, David" <dday@redcom.com> writes: > I'm looking on some insights on the following problem on retrieving table column names from the information schema ( postgres9.6.6 FreeBSD 11.1 ) > When my client starts up up. Each thread ( 10 ) run this command. > select column_name from information_schema.columns where table_name = 'pep_port_log'; > I have encountered two instances of testing Where the server does not respond to that command. Hm. Next time it happens, look into pg_stat_activity to see if the backend process is waiting, and if so for what, and what other processes are waiting. (I'm wondering about undetected deadlocks, for instance.) If it's running, maybe you could collect some stack traces to try to narrow down what it's doing. Actually, a stack trace would be useful if it's waiting, too. https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD regards, tom lane
Thank you Tom. I'll log that suggestion with our internal trouble report for actions to take on it's next occurrence. I should have thought of using your suggested view. It's been some time since I have had any suspicious postgresql behavior to explore and had forgotten about some of the better power tools. Admin skills get rusty. Best Dave -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, December 04, 2017 11:08 AM To: Day, David <dday@redcom.com> Cc: pgsql-general@postgresql.org Subject: Re: attempting to retrieve column names from information schema fails. "Day, David" <dday@redcom.com> writes: > I'm looking on some insights on the following problem on retrieving > table column names from the information schema ( postgres 9.6.6 FreeBSD 11.1 ) When my client starts up up. Each thread( 10 ) run this command. > select column_name from information_schema.columns where table_name = > 'pep_port_log'; I have encountered two instances of testing Where the server does not respond to that command. Hm. Next time it happens, look into pg_stat_activity to see if the backend process is waiting, and if so for what, and whatother processes are waiting. (I'm wondering about undetected deadlocks, for instance.) If it's running, maybe you couldcollect some stack traces to try to narrow down what it's doing. Actually, a stack trace would be useful if it's waiting,too. https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD regards, tom lane