ossc-db/pg_dbms_stats

Let PostgreSQL to make execution plans using frozen statistics.

pg_dbms_stats's Issues

When will PostgreSQL15 be supported?

Hi,

When will pg_dbms_stats support PostgreSQL 15?
Please let me know if you have a plan.

pg_dbms_stats for Postgres 12

Hi,

I want to use pg_dbms_stats in Postgres 12.1, but I failed make command with following messages.

make: *** No rule to make target `doc/export_effective_stats-12.sql.sample', needed by `all'.  Stop.

Current pg_dbms_stats seems not to support Postgres 12.
Is there any plan to support Postgres 12?

Regards,

Release of pg_dbms_stats for PostgreSQL 15

Hi,

PostgreSQL 15 has already gone through its second point release for now, so I'd like to ask if there are any plans for the pg_dbms_stats version for this release?

pg_dbms_stats 1.3.9 vs PG92

Hello, pg_dbms_stats team.

In 9.2.16, I got the following error.

db1=# CREATE EXTENSION pg_dbms_stats;
ERROR:  column c.relrowsecurity does not exist

When I commented out the end of dbms_stats.stats view definition, it worked.

 /* AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)) */

Thanks,

on Windows / import ERROR by newline code difference

First, I am happy to report that pg_dbms_stats works fine on Windows (MSVC build), as far as I can tell, except as noted below.

The export scripts export_effective_stats-*.sql.samle and export_plain_stats-*.sql.sample doesn't work in Windows OS because of newline code difference. It causes the following error when importing.

db1=# SELECT dbms_stats.import_table_stats('public', 't1', 'C:\pdata\14.10\export_stats.dmp');
ERROR:  COPY file signature not recognized

I was able to avoid this error by writing TO 'server-side file path' instead of using \o and TO STDOUT in the scripts. I would suggest adding scripts like this for Windows.

Cannot access to table belong to database after drop extension pg_dbms_stats

Hi there, I need your advice on the issue below.

In case of dropping the extension pg_dbms_stats from the database, I have experienced that sometimes we cannot access the table belong to the database.

postgres=# drop extension pg_dbms_stats ;
DROP EXTENSION
postgres=# select * from public.test_table ;
ERROR:  relation "dbms_stats.relation_stats_locked" does not exist
LINE 1: ...elpages, reltuples, curpages, relallvisible  FROM dbms_stats...
                                                             ^
HINT:  Check your settings of pg_dbms_stats.
QUERY:  SELECT relpages, reltuples, curpages, relallvisible  FROM dbms_stats.relation_stats_locked WHERE relid = $1
postgres=# drop extension pg_dbms_stats ;
ERROR:  extension "pg_dbms_stats" does not exist
postgres=# select * from public.test_table ;
ERROR:  relation "dbms_stats.relation_stats_locked" does not exist
LINE 1: ...elpages, reltuples, curpages, relallvisible  FROM dbms_stats...
                                                             ^
HINT:  Check your settings of pg_dbms_stats.
QUERY:  SELECT relpages, reltuples, curpages, relallvisible  FROM dbms_stats.relation_stats_locked WHERE relid = $1

these are the steps reroduce the issue.

1. create extension pg_dbms_stats ;
2. create table public.test_table as select * from pg_tables ;
3. select * from public.test_table;
4. drop extension pg_dbms_stats;
5. select * from public.test_table;

P.S. it does not always occur when I repeat the steps but it happens

doesn't work well for PG 9.6.3+ version

It seems to me that pg_dbms_stats doesn't work well for PG 9.6.3+ version.
(after this patch https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c33c42362 ?)

Both the original and dbms_stats' column histogram statistics are ignored
when pg_dbms_stats.use_locked_stats = on;

example (with pg_dbms_stats-5c2b1d0 + PG9.6.5) :

db1=# CREATE TABLE t1 (id int primary key, v text);
CREATE TABLE
db1=# INSERT INTO t1 SELECT g, '1' || md5(g::text) FROM generate_series(1, 10000) as g;
INSERT 0 10000
db1=# VACUUM ANALYZE t1;
VACUUM

db1=# SELECT dbms_stats.clean_up_stats();
 clean_up_stats
----------------
(0 rows)

db1=# explain SELECT * FROM t1 WHERE v < '000';
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on t1  (cost=0.00..209.00 rows=5000 width=38)
   Filter: (v < '000'::text)
(2 rows)

db1=# SET pg_dbms_stats.use_locked_stats TO off;
SET

db1=# explain SELECT * FROM t1 WHERE v < '000';
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on t1  (cost=0.00..209.00 rows=1 width=38)
   Filter: (v < '000'::text)
(2 rows)

db1=# SELECT dbms_stats.lock_table_stats('t1');
 lock_table_stats
------------------
 t1
(1 row)

db1=# SET pg_dbms_stats.use_locked_stats TO on;
SET

db1=# explain SELECT * FROM t1 WHERE v < '000';
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on t1  (cost=0.00..209.00 rows=5000 width=38)
   Filter: (v < '000'::text)
(2 rows)

make installcheck: 3 of 6 tests failed.

Hi,

Thanks for making pg_dbms_stats.

There seems to be a problem with some of the tests,
due to permission problems.

$ tail -n 3 results/ut-common.out
/* Reconnection as regular user */
\c - regular_user
\connect: FATAL:  Peer authentication failed for user "regular_user"

$ tail -n 3 results/ut.out
/* reconnection needed to flush cache */
\c - regular_user
\connect: FATAL:  Peer authentication failed for user "regular_user"

$ grep -A 2 ERROR results/ut_imp_exp.out
ERROR:  permission denied for schema dbms_stats
LINE 1: SELECT 1 FROM ONLY "dbms_stats"."relation_stats_locked" x WH...
                          ^
--
ERROR:  permission denied for schema dbms_stats
LINE 1: SELECT *   FROM dbms_stats.column_stats_locked WHERE stareli...
                        ^
--
ERROR:  permission denied for schema dbms_stats
LINE 1: SELECT 1 FROM ONLY "dbms_stats"."relation_stats_locked" x WH...
                          ^

Log output from make installcheck:

$ make installcheck
/usr/lib/postgresql/13/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/13/bin'    --encoding=UTF8 --temp-config=regress.conf --dbname=contrib_regression init-common ut_fdw_init init ut-common ut ut_imp_exp
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test init-common                  ... ok          618 ms
test ut_fdw_init                  ... ok           44 ms
test init                         ... ok           49 ms
test ut-common                    ... FAILED (test process exited with exit code 2)      278 ms
test ut                           ... FAILED (test process exited with exit code 2)     4637 ms
test ut_imp_exp                   ... FAILED     1037 ms

======================
3 of 6 tests failed.
======================