Queries covering deleted relations

[terrorobe]

While rolling out pg_stat_plans on more databases I noticed problems with queries containing dropped relations:

bacula=# select * from pg_stat_plans where planid = 1305572066;
-[ RECORD 1 ]-------+---------------------------------------------------------------------------------
planid              | 1305572066
userid              | 16384
dbid                | 16386
query               | SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles FROM DelCandidates
had_our_search_path | t
from_our_database   | t
query_explainable   | t
calls               | 1
total_time          | 0.013
rows                | 2
shared_blks_hit     | 0
shared_blks_read    | 0
shared_blks_written | 0
local_blks_hit      | 1
local_blks_read     | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 
blk_write_time      | 
last_startup_cost   | 34.9
last_total_cost     | 36.9

bacula=# \d DelCandidates
Did not find any relation named "DelCandidates".
bacula=# select pg_stat_plans_explain(1305572066, 16384, 16386);
ERROR:  relation "delcandidates" does not exist
LINE 1: ...elCandidates.JobId,DelCandidates.PurgedFiles FROM DelCandida...
                                                             ^
QUERY:  EXPLAIN SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles FROM DelCandidates
bacula=# 

Is it possible to set query_explainable to false for queries where one of the needed relations doesn't exist anymore/at the moment?

[simonat2ndQuadrant]

On 27 January 2013 02:26, Michael Renner notifications@github.com wrote:

Is it possible to set query_explainable to false for queries where one of
the needed relations doesn't exist anymore/at the moment?

That would need a DDL trigger to make it work, so not at present.

Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[petergeoghegan]

@terrorobe Yeah, that's possible. Just discriminate against ERRCODE_UNDEFINED_TABLE errors when pg_stat_plans catches errors that the underlying query might throw up. All of these Actually, you'd probably want to look for all of these:

ERRCODE_UNDEFINED_COLUMN
ERRCODE_UNDEFINED_CURSOR
ERRCODE_UNDEFINED_DATABASE
ERRCODE_UNDEFINED_FUNCTION
ERRCODE_UNDEFINED_PSTATEMENT
ERRCODE_UNDEFINED_SCHEMA
ERRCODE_UNDEFINED_TABLE
ERRCODE_UNDEFINED_PARAMETER
ERRCODE_UNDEFINED_OBJECT

Mostly these errors are thrown up during parse analysis.

There are scenarios in which we may unsuccessfully detect that a query is prepared, as with the "single level, multiple entries for same client query" scenario that we recently saw with SQL functions. Maybe we could teach pg_stat_plans to be smarter about those cases, but ERRCODE_UNDEFINED_PARAMETER would be a good one for catching any that may remain.

[petergeoghegan]

I should add that it would be nice if pg_stat_plans didn't propagate these and similar "expected" errors to clients, but rather just returned NULL. That way, it would be relatively straightforward to get plans for the entire hash table cache in a single query.

[terrorobe]

Yes - handling such errors gracefully would be nice - in most cases we don't overly care if we can't explain some plans.

In our collector we fetch the explain output for each plan separately which causes a linear increase in runtime.