Обсуждение: 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