BUG #5367: TEMP TABLES with ON COMMIT DELETE ROWS and different pg_stat features - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5367
Logged by:          Boguk Maxim
Email address:      Maxim.Boguk@gmail.com
PostgreSQL version: 8.4.2
Operating system:   Linux 2.6.18-164
Description:        TEMP TABLES with ON COMMIT DELETE ROWS and different
pg_stat features
Details:

Short description of the problem:
When transaction which used TEMP table with ON COMMIT DELETE ROWS commit or
rollback pg_stats and pg_stat_all_tables about that temporary table doesn't
reset.

It's no problem with common applications but with pgbouncer + transaction
pooling mode postgresql backends staying alive long time and incremental
errors in statistic about temporary tables leading to choose insane query
plans and bad performance in general.

Неre is simplest sample:

postgres=# CREATE TEMP TABLE test  (id integer) ON COMMIT DELETE ROWS;
CREATE TABLE
postgres=# SELECT
relid,schemaname,relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del
,n_live_tup,n_dead_tup  from pg_stat_all_tables where relname='test';
  relid   | schemaname | relname | seq_scan | seq_tup_read | n_tup_ins |
n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
----------+------------+---------+----------+--------------+-----------+----
-------+-----------+------------+------------
 11114129 | pg_temp_28 | test    |        0 |            0 |         0 |
    0 |         0 |          0 |          0
(1 row)

postgres=# begin;
BEGIN
postgres=# INSERT INTO test select 1 from generate_series(1,1000000);
INSERT 0 1000000
postgres=# commit;
COMMIT
postgres=# SELECT
relid,schemaname,relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del
,n_live_tup,n_dead_tup  from pg_stat_all_tables where relname='test';
  relid   | schemaname | relname | seq_scan | seq_tup_read | n_tup_ins |
n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
----------+------------+---------+----------+--------------+-----------+----
-------+-----------+------------+------------
 11114129 | pg_temp_28 | test    |        0 |            0 |   1000000 |
    0 |         0 |    1000000 |          0
(1 row)

So we see 1M live tuples for surely empty table.

If next transaction do the same we get next result:
postgres=# begin;
BEGIN
postgres=# INSERT INTO test select 1 from generate_series(1,1000000);
INSERT 0 1000000
postgres=# commit;
COMMIT
postgres=# SELECT
relid,schemaname,relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del
,n_live_tup,n_dead_tup  from pg_stat_all_tables where relname='test';
  relid   | schemaname | relname | seq_scan | seq_tup_read | n_tup_ins |
n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
----------+------------+---------+----------+--------------+-----------+----
-------+-----------+------------+------------
 11114129 | pg_temp_28 | test    |        1 |            0 |   2000000 |
    0 |         0 |    2000000 |          0
(1 row)

Even worse if someone call analyze test; inside transaction. Value
distribution in pg_stats will stay with that temp table in postgresql
connection forever (or until next analyze).

postgres=# begin;
BEGIN
postgres=# INSERT INTO test select 1 from generate_series(1,1000000);
INSERT 0 1000000
postgres=#
postgres=# ANALYZE test;
ANALYZE
postgres=# commit;
COMMIT
postgres=# SELECT * from pg_stats where  tablename='test';
 schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+-----------+---------+-----------+-----------+------------+----
--------------+-------------------+------------------+-------------
 pg_temp_28 | test      | id      |         0 |         4 |          1 | {1}
             | {1}               |                  |           1
(1 row)

now until next manual analyze we have wrong statistic about temporary table
(and even worse it can be random statistic... depend when analyze was
issued, sometime leading to random selection bad plans for queries with
these temp tables... good or bad plan selected depend to which backend you
got connected through pgbouncer).

I think right solution is reset pg_stat_all_tables to zeroes and empty
pg_stats for such temporary tables on commit/abort. Empty stats better then
wrong random stats.

PS: i'm understand, long living temporary tables with pgbouncer transaction
pooling bad idea itself, but still situation not too good.

PPS: sorry for not too good English