public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v6 3/3] async postgres_fdw 2+ messages / 2 participants [nested] [flat]
* [PATCH v6 3/3] async postgres_fdw @ 2017-10-19 08:24 Kyotaro Horiguchi <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Kyotaro Horiguchi @ 2017-10-19 08:24 UTC (permalink / raw) --- contrib/postgres_fdw/connection.c | 28 + .../postgres_fdw/expected/postgres_fdw.out | 272 ++++---- contrib/postgres_fdw/postgres_fdw.c | 601 +++++++++++++++--- contrib/postgres_fdw/postgres_fdw.h | 2 + contrib/postgres_fdw/sql/postgres_fdw.sql | 20 +- 5 files changed, 710 insertions(+), 213 deletions(-) diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c index 08daf26fdf..be5948f613 100644 --- a/contrib/postgres_fdw/connection.c +++ b/contrib/postgres_fdw/connection.c @@ -59,6 +59,7 @@ typedef struct ConnCacheEntry bool invalidated; /* true if reconnect is pending */ uint32 server_hashvalue; /* hash value of foreign server OID */ uint32 mapping_hashvalue; /* hash value of user mapping OID */ + void *storage; /* connection specific storage */ } ConnCacheEntry; /* @@ -203,6 +204,7 @@ GetConnection(UserMapping *user, bool will_prep_stmt) elog(DEBUG3, "new postgres_fdw connection %p for server \"%s\" (user mapping oid %u, userid %u)", entry->conn, server->servername, user->umid, user->userid); + entry->storage = NULL; } /* @@ -216,6 +218,32 @@ GetConnection(UserMapping *user, bool will_prep_stmt) return entry->conn; } +/* + * Returns the connection specific storage for this user. Allocate with + * initsize if not exists. + */ +void * +GetConnectionSpecificStorage(UserMapping *user, size_t initsize) +{ + bool found; + ConnCacheEntry *entry; + ConnCacheKey key; + + /* Find storage using the same key with GetConnection */ + key = user->umid; + entry = hash_search(ConnectionHash, &key, HASH_ENTER, &found); + Assert(found); + + /* Create one if not yet. */ + if (entry->storage == NULL) + { + entry->storage = MemoryContextAlloc(CacheMemoryContext, initsize); + memset(entry->storage, 0, initsize); + } + + return entry->storage; +} + /* * Connect to remote server using specified server and user mapping properties. */ diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 90db550b92..9374fa3a6c 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6973,7 +6973,7 @@ INSERT INTO a(aa) VALUES('aaaaa'); INSERT INTO b(aa) VALUES('bbb'); INSERT INTO b(aa) VALUES('bbbb'); INSERT INTO b(aa) VALUES('bbbbb'); -SELECT tableoid::regclass, * FROM a; +SELECT tableoid::regclass, * FROM a ORDER BY 1, 2; tableoid | aa ----------+------- a | aaa @@ -7001,7 +7001,7 @@ SELECT tableoid::regclass, * FROM ONLY a; (3 rows) UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%'; -SELECT tableoid::regclass, * FROM a; +SELECT tableoid::regclass, * FROM a ORDER BY 1, 2; tableoid | aa ----------+-------- a | aaa @@ -7029,7 +7029,7 @@ SELECT tableoid::regclass, * FROM ONLY a; (3 rows) UPDATE b SET aa = 'new'; -SELECT tableoid::regclass, * FROM a; +SELECT tableoid::regclass, * FROM a ORDER BY 1, 2; tableoid | aa ----------+-------- a | aaa @@ -7057,7 +7057,7 @@ SELECT tableoid::regclass, * FROM ONLY a; (3 rows) UPDATE a SET aa = 'newtoo'; -SELECT tableoid::regclass, * FROM a; +SELECT tableoid::regclass, * FROM a ORDER BY 1, 2; tableoid | aa ----------+-------- a | newtoo @@ -7127,35 +7127,41 @@ insert into bar2 values(3,33,33); insert into bar2 values(4,44,44); insert into bar2 values(7,77,77); explain (verbose, costs off) -select * from bar where f1 in (select f1 from foo) for update; - QUERY PLAN ----------------------------------------------------------------------------------------------- +select * from bar where f1 in (select f1 from foo) order by 1 for update; + QUERY PLAN +----------------------------------------------------------------------------------------------------------------- LockRows Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid - -> Hash Join + -> Merge Join Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid Inner Unique: true - Hash Cond: (bar.f1 = foo.f1) - -> Append - -> Seq Scan on public.bar bar_1 + Merge Cond: (bar.f1 = foo.f1) + -> Merge Append + Sort Key: bar.f1 + -> Sort Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid + Sort Key: bar_1.f1 + -> Seq Scan on public.bar bar_1 + Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid -> Foreign Scan on public.bar2 bar_2 Output: bar_2.f1, bar_2.f2, bar_2.ctid, bar_2.*, bar_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE - -> Hash + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 ORDER BY f1 ASC NULLS LAST FOR UPDATE + -> Sort Output: foo.ctid, foo.f1, foo.*, foo.tableoid + Sort Key: foo.f1 -> HashAggregate Output: foo.ctid, foo.f1, foo.*, foo.tableoid Group Key: foo.f1 -> Append - -> Seq Scan on public.foo foo_1 - Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid - -> Foreign Scan on public.foo2 foo_2 + Async subplans: 1 + -> Async Foreign Scan on public.foo2 foo_2 Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -(23 rows) + -> Seq Scan on public.foo foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid +(29 rows) -select * from bar where f1 in (select f1 from foo) for update; +select * from bar where f1 in (select f1 from foo) order by 1 for update; f1 | f2 ----+---- 1 | 11 @@ -7165,35 +7171,41 @@ select * from bar where f1 in (select f1 from foo) for update; (4 rows) explain (verbose, costs off) -select * from bar where f1 in (select f1 from foo) for share; - QUERY PLAN ----------------------------------------------------------------------------------------------- +select * from bar where f1 in (select f1 from foo) order by 1 for share; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------- LockRows Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid - -> Hash Join + -> Merge Join Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid Inner Unique: true - Hash Cond: (bar.f1 = foo.f1) - -> Append - -> Seq Scan on public.bar bar_1 + Merge Cond: (bar.f1 = foo.f1) + -> Merge Append + Sort Key: bar.f1 + -> Sort Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid + Sort Key: bar_1.f1 + -> Seq Scan on public.bar bar_1 + Output: bar_1.f1, bar_1.f2, bar_1.ctid, bar_1.*, bar_1.tableoid -> Foreign Scan on public.bar2 bar_2 Output: bar_2.f1, bar_2.f2, bar_2.ctid, bar_2.*, bar_2.tableoid - Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE - -> Hash + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 ORDER BY f1 ASC NULLS LAST FOR SHARE + -> Sort Output: foo.ctid, foo.f1, foo.*, foo.tableoid + Sort Key: foo.f1 -> HashAggregate Output: foo.ctid, foo.f1, foo.*, foo.tableoid Group Key: foo.f1 -> Append - -> Seq Scan on public.foo foo_1 - Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid - -> Foreign Scan on public.foo2 foo_2 + Async subplans: 1 + -> Async Foreign Scan on public.foo2 foo_2 Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -(23 rows) + -> Seq Scan on public.foo foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid +(29 rows) -select * from bar where f1 in (select f1 from foo) for share; +select * from bar where f1 in (select f1 from foo) order by 1 for share; f1 | f2 ----+---- 1 | 11 @@ -7223,11 +7235,12 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo); Output: foo.ctid, foo.f1, foo.*, foo.tableoid Group Key: foo.f1 -> Append - -> Seq Scan on public.foo foo_1 - Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid - -> Foreign Scan on public.foo2 foo_2 + Async subplans: 1 + -> Async Foreign Scan on public.foo2 foo_2 Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 + -> Seq Scan on public.foo foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid -> Hash Join Output: bar_1.f1, (bar_1.f2 + 100), bar_1.f3, bar_1.ctid, foo.ctid, foo.*, foo.tableoid Inner Unique: true @@ -7241,12 +7254,13 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo); Output: foo.ctid, foo.f1, foo.*, foo.tableoid Group Key: foo.f1 -> Append - -> Seq Scan on public.foo foo_1 - Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid - -> Foreign Scan on public.foo2 foo_2 + Async subplans: 1 + -> Async Foreign Scan on public.foo2 foo_2 Output: foo_2.ctid, foo_2.f1, foo_2.*, foo_2.tableoid Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 -(39 rows) + -> Seq Scan on public.foo foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.*, foo_1.tableoid +(41 rows) update bar set f2 = f2 + 100 where f1 in (select f1 from foo); select tableoid::regclass, * from bar order by 1,2; @@ -7276,16 +7290,17 @@ where bar.f1 = ss.f1; Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1)) Hash Cond: (foo.f1 = bar.f1) -> Append + Async subplans: 2 + -> Async Foreign Scan on public.foo2 foo_1 + Output: ROW(foo_1.f1), foo_1.f1 + Remote SQL: SELECT f1 FROM public.loct1 + -> Async Foreign Scan on public.foo2 foo_3 + Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) + Remote SQL: SELECT f1 FROM public.loct1 -> Seq Scan on public.foo Output: ROW(foo.f1), foo.f1 - -> Foreign Scan on public.foo2 foo_1 - Output: ROW(foo_1.f1), foo_1.f1 - Remote SQL: SELECT f1 FROM public.loct1 -> Seq Scan on public.foo foo_2 Output: ROW((foo_2.f1 + 3)), (foo_2.f1 + 3) - -> Foreign Scan on public.foo2 foo_3 - Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) - Remote SQL: SELECT f1 FROM public.loct1 -> Hash Output: bar.f1, bar.f2, bar.ctid -> Seq Scan on public.bar @@ -7303,17 +7318,18 @@ where bar.f1 = ss.f1; Output: (ROW(foo.f1)), foo.f1 Sort Key: foo.f1 -> Append + Async subplans: 2 + -> Async Foreign Scan on public.foo2 foo_1 + Output: ROW(foo_1.f1), foo_1.f1 + Remote SQL: SELECT f1 FROM public.loct1 + -> Async Foreign Scan on public.foo2 foo_3 + Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) + Remote SQL: SELECT f1 FROM public.loct1 -> Seq Scan on public.foo Output: ROW(foo.f1), foo.f1 - -> Foreign Scan on public.foo2 foo_1 - Output: ROW(foo_1.f1), foo_1.f1 - Remote SQL: SELECT f1 FROM public.loct1 -> Seq Scan on public.foo foo_2 Output: ROW((foo_2.f1 + 3)), (foo_2.f1 + 3) - -> Foreign Scan on public.foo2 foo_3 - Output: ROW((foo_3.f1 + 3)), (foo_3.f1 + 3) - Remote SQL: SELECT f1 FROM public.loct1 -(45 rows) +(47 rows) update bar set f2 = f2 + 100 from @@ -7463,27 +7479,33 @@ delete from foo where f1 < 5 returning *; (5 rows) explain (verbose, costs off) -update bar set f2 = f2 + 100 returning *; - QUERY PLAN ------------------------------------------------------------------------------- - Update on public.bar - Output: bar.f1, bar.f2 - Update on public.bar - Foreign Update on public.bar2 bar_1 - -> Seq Scan on public.bar - Output: bar.f1, (bar.f2 + 100), bar.ctid - -> Foreign Update on public.bar2 bar_1 - Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2 -(8 rows) +with u as (update bar set f2 = f2 + 100 returning *) select * from u order by 1; + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Output: u.f1, u.f2 + Sort Key: u.f1 + CTE u + -> Update on public.bar + Output: bar.f1, bar.f2 + Update on public.bar + Foreign Update on public.bar2 bar_1 + -> Seq Scan on public.bar + Output: bar.f1, (bar.f2 + 100), bar.ctid + -> Foreign Update on public.bar2 bar_1 + Remote SQL: UPDATE public.loct2 SET f2 = (f2 + 100) RETURNING f1, f2 + -> CTE Scan on u + Output: u.f1, u.f2 +(14 rows) -update bar set f2 = f2 + 100 returning *; +with u as (update bar set f2 = f2 + 100 returning *) select * from u order by 1; f1 | f2 ----+----- 1 | 311 2 | 322 - 6 | 266 3 | 333 4 | 344 + 6 | 266 7 | 277 (6 rows) @@ -8558,11 +8580,12 @@ SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER J Sort Sort Key: t1.a, t3.c -> Append - -> Foreign Scan + Async subplans: 2 + -> Async Foreign Scan Relations: ((ftprt1_p1 t1_1) INNER JOIN (ftprt2_p1 t2_1)) INNER JOIN (ftprt1_p1 t3_1) - -> Foreign Scan + -> Async Foreign Scan Relations: ((ftprt1_p2 t1_2) INNER JOIN (ftprt2_p2 t2_2)) INNER JOIN (ftprt1_p2 t3_2) -(7 rows) +(8 rows) SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3; a | b | c @@ -8597,20 +8620,22 @@ SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) -- with whole-row reference; partitionwise join does not apply EXPLAIN (COSTS OFF) SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Sort Sort Key: ((t1.*)::fprt1), ((t2.*)::fprt2) -> Hash Full Join Hash Cond: (t1.a = t2.b) -> Append - -> Foreign Scan on ftprt1_p1 t1_1 - -> Foreign Scan on ftprt1_p2 t1_2 + Async subplans: 2 + -> Async Foreign Scan on ftprt1_p1 t1_1 + -> Async Foreign Scan on ftprt1_p2 t1_2 -> Hash -> Append - -> Foreign Scan on ftprt2_p1 t2_1 - -> Foreign Scan on ftprt2_p2 t2_2 -(11 rows) + Async subplans: 2 + -> Async Foreign Scan on ftprt2_p1 t2_1 + -> Async Foreign Scan on ftprt2_p2 t2_2 +(13 rows) SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2; wr | wr @@ -8639,11 +8664,12 @@ SELECT t1.a,t1.b FROM fprt1 t1, LATERAL (SELECT t2.a, t2.b FROM fprt2 t2 WHERE t Sort Sort Key: t1.a, t1.b -> Append - -> Foreign Scan + Async subplans: 2 + -> Async Foreign Scan Relations: (ftprt1_p1 t1_1) INNER JOIN (ftprt2_p1 t2_1) - -> Foreign Scan + -> Async Foreign Scan Relations: (ftprt1_p2 t1_2) INNER JOIN (ftprt2_p2 t2_2) -(7 rows) +(8 rows) SELECT t1.a,t1.b FROM fprt1 t1, LATERAL (SELECT t2.a, t2.b FROM fprt2 t2 WHERE t1.a = t2.b AND t1.b = t2.a) q WHERE t1.a%25 = 0 ORDER BY 1,2; a | b @@ -8696,21 +8722,23 @@ SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE -- test FOR UPDATE; partitionwise join does not apply EXPLAIN (COSTS OFF) SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; - QUERY PLAN --------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------- LockRows -> Sort Sort Key: t1.a -> Hash Join Hash Cond: (t2.b = t1.a) -> Append - -> Foreign Scan on ftprt2_p1 t2_1 - -> Foreign Scan on ftprt2_p2 t2_2 + Async subplans: 2 + -> Async Foreign Scan on ftprt2_p1 t2_1 + -> Async Foreign Scan on ftprt2_p2 t2_2 -> Hash -> Append - -> Foreign Scan on ftprt1_p1 t1_1 - -> Foreign Scan on ftprt1_p2 t1_2 -(12 rows) + Async subplans: 2 + -> Async Foreign Scan on ftprt1_p1 t1_1 + -> Async Foreign Scan on ftprt1_p2 t1_2 +(14 rows) SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1; a | b @@ -8745,18 +8773,19 @@ ANALYZE fpagg_tab_p3; SET enable_partitionwise_aggregate TO false; EXPLAIN (COSTS OFF) SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------- Sort Sort Key: pagg_tab.a -> HashAggregate Group Key: pagg_tab.a Filter: (avg(pagg_tab.b) < '22'::numeric) -> Append - -> Foreign Scan on fpagg_tab_p1 pagg_tab_1 - -> Foreign Scan on fpagg_tab_p2 pagg_tab_2 - -> Foreign Scan on fpagg_tab_p3 pagg_tab_3 -(9 rows) + Async subplans: 3 + -> Async Foreign Scan on fpagg_tab_p1 pagg_tab_1 + -> Async Foreign Scan on fpagg_tab_p2 pagg_tab_2 + -> Async Foreign Scan on fpagg_tab_p3 pagg_tab_3 +(10 rows) -- Plan with partitionwise aggregates is enabled SET enable_partitionwise_aggregate TO true; @@ -8767,13 +8796,14 @@ SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 O Sort Sort Key: pagg_tab.a -> Append - -> Foreign Scan + Async subplans: 3 + -> Async Foreign Scan Relations: Aggregate on (fpagg_tab_p1 pagg_tab) - -> Foreign Scan + -> Async Foreign Scan Relations: Aggregate on (fpagg_tab_p2 pagg_tab_1) - -> Foreign Scan + -> Async Foreign Scan Relations: Aggregate on (fpagg_tab_p3 pagg_tab_2) -(9 rows) +(10 rows) SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1; a | sum | min | count @@ -8795,29 +8825,22 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; Sort Output: t1.a, (count(((t1.*)::pagg_tab))) Sort Key: t1.a - -> Append - -> HashAggregate - Output: t1.a, count(((t1.*)::pagg_tab)) - Group Key: t1.a - Filter: (avg(t1.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p1 t1 - Output: t1.a, t1.*, t1.b - Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 - -> HashAggregate - Output: t1_1.a, count(((t1_1.*)::pagg_tab)) - Group Key: t1_1.a - Filter: (avg(t1_1.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p2 t1_1 + -> HashAggregate + Output: t1.a, count(((t1.*)::pagg_tab)) + Group Key: t1.a + Filter: (avg(t1.b) < '22'::numeric) + -> Append + Async subplans: 3 + -> Async Foreign Scan on public.fpagg_tab_p1 t1_1 Output: t1_1.a, t1_1.*, t1_1.b - Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 - -> HashAggregate - Output: t1_2.a, count(((t1_2.*)::pagg_tab)) - Group Key: t1_2.a - Filter: (avg(t1_2.b) < '22'::numeric) - -> Foreign Scan on public.fpagg_tab_p3 t1_2 + Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 + -> Async Foreign Scan on public.fpagg_tab_p2 t1_2 Output: t1_2.a, t1_2.*, t1_2.b + Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 + -> Async Foreign Scan on public.fpagg_tab_p3 t1_3 + Output: t1_3.a, t1_3.*, t1_3.b Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3 -(25 rows) +(18 rows) SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1; a | count @@ -8837,20 +8860,15 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ----------------------------------------------------------------- Sort Sort Key: pagg_tab.b - -> Finalize HashAggregate + -> HashAggregate Group Key: pagg_tab.b Filter: (sum(pagg_tab.a) < 700) -> Append - -> Partial HashAggregate - Group Key: pagg_tab.b - -> Foreign Scan on fpagg_tab_p1 pagg_tab - -> Partial HashAggregate - Group Key: pagg_tab_1.b - -> Foreign Scan on fpagg_tab_p2 pagg_tab_1 - -> Partial HashAggregate - Group Key: pagg_tab_2.b - -> Foreign Scan on fpagg_tab_p3 pagg_tab_2 -(15 rows) + Async subplans: 3 + -> Async Foreign Scan on fpagg_tab_p1 pagg_tab_1 + -> Async Foreign Scan on fpagg_tab_p2 pagg_tab_2 + -> Async Foreign Scan on fpagg_tab_p3 pagg_tab_3 +(10 rows) -- =================================================================== -- access rights and superuser diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 9fc53cad68..4bfc2d39ea 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -21,6 +21,8 @@ #include "commands/defrem.h" #include "commands/explain.h" #include "commands/vacuum.h" +#include "executor/execAsync.h" +#include "executor/nodeForeignscan.h" #include "foreign/fdwapi.h" #include "funcapi.h" #include "miscadmin.h" @@ -35,6 +37,7 @@ #include "optimizer/restrictinfo.h" #include "optimizer/tlist.h" #include "parser/parsetree.h" +#include "pgstat.h" #include "postgres_fdw.h" #include "utils/builtins.h" #include "utils/float.h" @@ -56,6 +59,9 @@ PG_MODULE_MAGIC; /* If no remote estimates, assume a sort costs 20% extra */ #define DEFAULT_FDW_SORT_MULTIPLIER 1.2 +/* Retrieve PgFdwScanState struct from ForeignScanState */ +#define GetPgFdwScanState(n) ((PgFdwScanState *)(n)->fdw_state) + /* * Indexes of FDW-private information stored in fdw_private lists. * @@ -122,11 +128,29 @@ enum FdwDirectModifyPrivateIndex FdwDirectModifyPrivateSetProcessed }; +/* + * Connection common state - shared among all PgFdwState instances using the + * same connection. + */ +typedef struct PgFdwConnCommonState +{ + ForeignScanState *leader; /* leader node of this connection */ + bool busy; /* true if this connection is busy */ +} PgFdwConnCommonState; + +/* Execution state base type */ +typedef struct PgFdwState +{ + PGconn *conn; /* connection for the scan */ + PgFdwConnCommonState *commonstate; /* connection common state */ +} PgFdwState; + /* * Execution state of a foreign scan using postgres_fdw. */ typedef struct PgFdwScanState { + PgFdwState s; /* common structure */ Relation rel; /* relcache entry for the foreign table. NULL * for a foreign join scan. */ TupleDesc tupdesc; /* tuple descriptor of scan */ @@ -137,7 +161,6 @@ typedef struct PgFdwScanState List *retrieved_attrs; /* list of retrieved attribute numbers */ /* for remote query execution */ - PGconn *conn; /* connection for the scan */ unsigned int cursor_number; /* quasi-unique ID for my cursor */ bool cursor_exists; /* have we created the cursor? */ int numParams; /* number of parameters passed to query */ @@ -153,6 +176,12 @@ typedef struct PgFdwScanState /* batch-level state, for optimizing rewinds and avoiding useless fetch */ int fetch_ct_2; /* Min(# of fetches done, 2) */ bool eof_reached; /* true if last fetch reached EOF */ + bool async; /* true if run asynchronously */ + bool queued; /* true if this node is in waiter queue */ + ForeignScanState *waiter; /* Next node to run a query among nodes + * sharing the same connection */ + ForeignScanState *last_waiter; /* last element in waiter queue. + * valid only on the leader node */ /* working memory contexts */ MemoryContext batch_cxt; /* context holding current batch of tuples */ @@ -166,11 +195,11 @@ typedef struct PgFdwScanState */ typedef struct PgFdwModifyState { + PgFdwState s; /* common structure */ Relation rel; /* relcache entry for the foreign table */ AttInMetadata *attinmeta; /* attribute datatype conversion metadata */ /* for remote query execution */ - PGconn *conn; /* connection for the scan */ char *p_name; /* name of prepared statement, if created */ /* extracted fdw_private data */ @@ -197,6 +226,7 @@ typedef struct PgFdwModifyState */ typedef struct PgFdwDirectModifyState { + PgFdwState s; /* common structure */ Relation rel; /* relcache entry for the foreign table */ AttInMetadata *attinmeta; /* attribute datatype conversion metadata */ @@ -326,6 +356,7 @@ static void postgresBeginForeignScan(ForeignScanState *node, int eflags); static TupleTableSlot *postgresIterateForeignScan(ForeignScanState *node); static void postgresReScanForeignScan(ForeignScanState *node); static void postgresEndForeignScan(ForeignScanState *node); +static void postgresShutdownForeignScan(ForeignScanState *node); static void postgresAddForeignUpdateTargets(Query *parsetree, RangeTblEntry *target_rte, Relation target_relation); @@ -391,6 +422,10 @@ static void postgresGetForeignUpperPaths(PlannerInfo *root, RelOptInfo *input_rel, RelOptInfo *output_rel, void *extra); +static bool postgresIsForeignPathAsyncCapable(ForeignPath *path); +static bool postgresForeignAsyncConfigureWait(ForeignScanState *node, + WaitEventSet *wes, + void *caller_data, bool reinit); /* * Helper functions @@ -419,7 +454,9 @@ static bool ec_member_matches_foreign(PlannerInfo *root, RelOptInfo *rel, EquivalenceClass *ec, EquivalenceMember *em, void *arg); static void create_cursor(ForeignScanState *node); -static void fetch_more_data(ForeignScanState *node); +static void request_more_data(ForeignScanState *node); +static void fetch_received_data(ForeignScanState *node); +static void vacate_connection(PgFdwState *fdwconn, bool clear_queue); static void close_cursor(PGconn *conn, unsigned int cursor_number); static PgFdwModifyState *create_foreign_modify(EState *estate, RangeTblEntry *rte, @@ -522,6 +559,7 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) routine->IterateForeignScan = postgresIterateForeignScan; routine->ReScanForeignScan = postgresReScanForeignScan; routine->EndForeignScan = postgresEndForeignScan; + routine->ShutdownForeignScan = postgresShutdownForeignScan; /* Functions for updating foreign tables */ routine->AddForeignUpdateTargets = postgresAddForeignUpdateTargets; @@ -558,6 +596,10 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) /* Support functions for upper relation push-down */ routine->GetForeignUpperPaths = postgresGetForeignUpperPaths; + /* Support functions for async execution */ + routine->IsForeignPathAsyncCapable = postgresIsForeignPathAsyncCapable; + routine->ForeignAsyncConfigureWait = postgresForeignAsyncConfigureWait; + PG_RETURN_POINTER(routine); } @@ -1434,12 +1476,22 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) * Get connection to the foreign server. Connection manager will * establish new connection if necessary. */ - fsstate->conn = GetConnection(user, false); + fsstate->s.conn = GetConnection(user, false); + fsstate->s.commonstate = (PgFdwConnCommonState *) + GetConnectionSpecificStorage(user, sizeof(PgFdwConnCommonState)); + fsstate->s.commonstate->leader = NULL; + fsstate->s.commonstate->busy = false; + fsstate->waiter = NULL; + fsstate->last_waiter = node; /* Assign a unique ID for my cursor */ - fsstate->cursor_number = GetCursorNumber(fsstate->conn); + fsstate->cursor_number = GetCursorNumber(fsstate->s.conn); fsstate->cursor_exists = false; + /* Initialize async execution status */ + fsstate->async = false; + fsstate->queued = false; + /* Get private info created by planner functions. */ fsstate->query = strVal(list_nth(fsplan->fdw_private, FdwScanPrivateSelectSql)); @@ -1487,40 +1539,241 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) &fsstate->param_values); } +/* + * Async queue manipulation functions + */ + +/* + * add_async_waiter: + * + * Enqueue node if it isn't in the queue. Immediately send request it if the + * underlying connection is not busy. + */ +static inline void +add_async_waiter(ForeignScanState *node) +{ + PgFdwScanState *fsstate = GetPgFdwScanState(node); + ForeignScanState *leader = fsstate->s.commonstate->leader; + + /* + * Do nothing if the node is already in the queue or already eof'ed. + * Note: leader node is not marked as queued. + */ + if (leader == node || fsstate->queued || fsstate->eof_reached) + return; + + if (leader == NULL) + { + /* no leader means not busy, send request immediately */ + request_more_data(node); + } + else + { + /* the connection is busy, queue the node */ + PgFdwScanState *leader_state = GetPgFdwScanState(leader); + PgFdwScanState *last_waiter_state + = GetPgFdwScanState(leader_state->last_waiter); + + last_waiter_state->waiter = node; + leader_state->last_waiter = node; + fsstate->queued = true; + } +} + +/* + * move_to_next_waiter: + * + * Make the first waiter be the next leader + * Returns the new leader or NULL if there's no waiter. + */ +static inline ForeignScanState * +move_to_next_waiter(ForeignScanState *node) +{ + PgFdwScanState *leader_state = GetPgFdwScanState(node); + ForeignScanState *next_leader = leader_state->waiter; + + Assert(leader_state->s.commonstate->leader = node); + + if (next_leader) + { + /* the first waiter becomes the next leader */ + PgFdwScanState *next_leader_state = GetPgFdwScanState(next_leader); + next_leader_state->last_waiter = leader_state->last_waiter; + next_leader_state->queued = false; + } + + leader_state->waiter = NULL; + leader_state->s.commonstate->leader = next_leader; + + return next_leader; +} + +/* + * Remove the node from waiter queue. + * + * Remaining results are cleared if the node is a busy leader. + * This intended to be used during node shutdown. + */ +static inline void +remove_async_node(ForeignScanState *node) +{ + PgFdwScanState *fsstate = GetPgFdwScanState(node); + ForeignScanState *leader = fsstate->s.commonstate->leader; + PgFdwScanState *leader_state; + ForeignScanState *prev; + PgFdwScanState *prev_state; + ForeignScanState *cur; + + /* no need to remove me */ + if (!leader || !fsstate->queued) + return; + + leader_state = GetPgFdwScanState(leader); + + if (leader == node) + { + if (leader_state->s.commonstate->busy) + { + /* + * this node is waiting for result, absorb the result first so + * that the following commands can be sent on the connection. + */ + PgFdwScanState *leader_state = GetPgFdwScanState(leader); + PGconn *conn = leader_state->s.conn; + + while(PQisBusy(conn)) + PQclear(PQgetResult(conn)); + + leader_state->s.commonstate->busy = false; + } + + move_to_next_waiter(node); + + return; + } + + /* + * Just remove the node from the queue + * + * Nodes don't have a link to the previous node but anyway this function is + * called on the shutdown path, so we don't bother seeking for faster way + * to do this. + */ + prev = leader; + prev_state = leader_state; + cur = GetPgFdwScanState(prev)->waiter; + while (cur) + { + PgFdwScanState *curstate = GetPgFdwScanState(cur); + + if (cur == node) + { + prev_state->waiter = curstate->waiter; + + /* relink to the previous node if the last node was removed */ + if (leader_state->last_waiter == cur) + leader_state->last_waiter = prev; + + fsstate->queued = false; + + return; + } + prev = cur; + prev_state = curstate; + cur = curstate->waiter; + } +} + /* * postgresIterateForeignScan - * Retrieve next row from the result set, or clear tuple slot to indicate - * EOF. + * Retrieve next row from the result set. + * + * For synchronous nodes, returns clear tuple slot means EOF. + * + * For asynchronous nodes, if clear tuple slot is returned, the caller + * needs to check async state to tell if all tuples received + * (AS_AVAILABLE) or waiting for the next data to come (AS_WAITING). */ static TupleTableSlot * postgresIterateForeignScan(ForeignScanState *node) { - PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; + PgFdwScanState *fsstate = GetPgFdwScanState(node); TupleTableSlot *slot = node->ss.ss_ScanTupleSlot; - /* - * If this is the first call after Begin or ReScan, we need to create the - * cursor on the remote side. - */ - if (!fsstate->cursor_exists) - create_cursor(node); - - /* - * Get some more tuples, if we've run out. - */ + if (fsstate->next_tuple >= fsstate->num_tuples && !fsstate->eof_reached) + { + /* we've run out, get some more tuples */ + if (!node->fs_async) + { + /* + * finish the running query before sending the next command for + * this node + */ + if (!fsstate->s.commonstate->busy) + vacate_connection((PgFdwState *)fsstate, false); + + request_more_data(node); + + /* Fetch the result immediately. */ + fetch_received_data(node); + } + else if (!fsstate->s.commonstate->busy) + { + /* If the connection is not busy, just send the request. */ + request_more_data(node); + } + else + { + /* The connection is busy, queue the request */ + bool available = true; + ForeignScanState *leader = fsstate->s.commonstate->leader; + PgFdwScanState *leader_state = GetPgFdwScanState(leader); + + /* queue the requested node */ + add_async_waiter(node); + + /* + * The request for the next node cannot be sent before the leader + * responds. Finish the current leader if possible. + */ + if (PQisBusy(leader_state->s.conn)) + { + int rc = WaitLatchOrSocket(NULL, + WL_SOCKET_READABLE | WL_TIMEOUT | + WL_EXIT_ON_PM_DEATH, + PQsocket(leader_state->s.conn), 0, + WAIT_EVENT_ASYNC_WAIT); + if (!(rc & WL_SOCKET_READABLE)) + available = false; + } + + /* fetch the leader's data and enqueue it for the next request */ + if (available) + { + fetch_received_data(leader); + add_async_waiter(leader); + } + } + } + if (fsstate->next_tuple >= fsstate->num_tuples) { - /* No point in another fetch if we already detected EOF, though. */ - if (!fsstate->eof_reached) - fetch_more_data(node); - /* If we didn't get any tuples, must be end of data. */ - if (fsstate->next_tuple >= fsstate->num_tuples) - return ExecClearTuple(slot); + /* + * We haven't received a result for the given node this time, return + * with no tuple to give way to another node. + */ + if (fsstate->eof_reached) + node->ss.ps.asyncstate = AS_AVAILABLE; + else + node->ss.ps.asyncstate = AS_WAITING; + + return ExecClearTuple(slot); } /* * Return the next tuple. */ + node->ss.ps.asyncstate = AS_AVAILABLE; ExecStoreHeapTuple(fsstate->tuples[fsstate->next_tuple++], slot, false); @@ -1535,7 +1788,7 @@ postgresIterateForeignScan(ForeignScanState *node) static void postgresReScanForeignScan(ForeignScanState *node) { - PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; + PgFdwScanState *fsstate = GetPgFdwScanState(node); char sql[64]; PGresult *res; @@ -1543,6 +1796,8 @@ postgresReScanForeignScan(ForeignScanState *node) if (!fsstate->cursor_exists) return; + vacate_connection((PgFdwState *)fsstate, true); + /* * If any internal parameters affecting this node have changed, we'd * better destroy and recreate the cursor. Otherwise, rewinding it should @@ -1571,9 +1826,9 @@ postgresReScanForeignScan(ForeignScanState *node) * We don't use a PG_TRY block here, so be careful not to throw error * without releasing the PGresult. */ - res = pgfdw_exec_query(fsstate->conn, sql); + res = pgfdw_exec_query(fsstate->s.conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) - pgfdw_report_error(ERROR, res, fsstate->conn, true, sql); + pgfdw_report_error(ERROR, res, fsstate->s.conn, true, sql); PQclear(res); /* Now force a fresh FETCH. */ @@ -1591,7 +1846,7 @@ postgresReScanForeignScan(ForeignScanState *node) static void postgresEndForeignScan(ForeignScanState *node) { - PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; + PgFdwScanState *fsstate = GetPgFdwScanState(node); /* if fsstate is NULL, we are in EXPLAIN; nothing to do */ if (fsstate == NULL) @@ -1599,15 +1854,31 @@ postgresEndForeignScan(ForeignScanState *node) /* Close the cursor if open, to prevent accumulation of cursors */ if (fsstate->cursor_exists) - close_cursor(fsstate->conn, fsstate->cursor_number); + close_cursor(fsstate->s.conn, fsstate->cursor_number); /* Release remote connection */ - ReleaseConnection(fsstate->conn); - fsstate->conn = NULL; + ReleaseConnection(fsstate->s.conn); + fsstate->s.conn = NULL; /* MemoryContexts will be deleted automatically. */ } +/* + * postgresShutdownForeignScan + * Remove asynchrony stuff and cleanup garbage on the connection. + */ +static void +postgresShutdownForeignScan(ForeignScanState *node) +{ + ForeignScan *plan = (ForeignScan *) node->ss.ps.plan; + + if (plan->operation != CMD_SELECT) + return; + + /* remove the node from waiting queue */ + remove_async_node(node); +} + /* * postgresAddForeignUpdateTargets * Add resjunk column(s) needed for update/delete on a foreign table @@ -2372,7 +2643,9 @@ postgresBeginDirectModify(ForeignScanState *node, int eflags) * Get connection to the foreign server. Connection manager will * establish new connection if necessary. */ - dmstate->conn = GetConnection(user, false); + dmstate->s.conn = GetConnection(user, false); + dmstate->s.commonstate = (PgFdwConnCommonState *) + GetConnectionSpecificStorage(user, sizeof(PgFdwConnCommonState)); /* Update the foreign-join-related fields. */ if (fsplan->scan.scanrelid == 0) @@ -2457,7 +2730,11 @@ postgresIterateDirectModify(ForeignScanState *node) * If this is the first call after Begin, execute the statement. */ if (dmstate->num_tuples == -1) + { + /* finish running query to send my command */ + vacate_connection((PgFdwState *)dmstate, true); execute_dml_stmt(node); + } /* * If the local query doesn't specify RETURNING, just clear tuple slot. @@ -2504,8 +2781,8 @@ postgresEndDirectModify(ForeignScanState *node) PQclear(dmstate->result); /* Release remote connection */ - ReleaseConnection(dmstate->conn); - dmstate->conn = NULL; + ReleaseConnection(dmstate->s.conn); + dmstate->s.conn = NULL; /* MemoryContext will be deleted automatically. */ } @@ -2703,6 +2980,7 @@ estimate_path_cost_size(PlannerInfo *root, List *local_param_join_conds; StringInfoData sql; PGconn *conn; + PgFdwConnCommonState *commonstate; Selectivity local_sel; QualCost local_cost; List *fdw_scan_tlist = NIL; @@ -2747,6 +3025,18 @@ estimate_path_cost_size(PlannerInfo *root, /* Get the remote estimate */ conn = GetConnection(fpinfo->user, false); + commonstate = GetConnectionSpecificStorage(fpinfo->user, + sizeof(PgFdwConnCommonState)); + if (commonstate) + { + PgFdwState tmpstate; + tmpstate.conn = conn; + tmpstate.commonstate = commonstate; + + /* finish running query to send my command */ + vacate_connection(&tmpstate, true); + } + get_remote_estimate(sql.data, conn, &rows, &width, &startup_cost, &total_cost); ReleaseConnection(conn); @@ -3317,11 +3607,11 @@ ec_member_matches_foreign(PlannerInfo *root, RelOptInfo *rel, static void create_cursor(ForeignScanState *node) { - PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; + PgFdwScanState *fsstate = GetPgFdwScanState(node); ExprContext *econtext = node->ss.ps.ps_ExprContext; int numParams = fsstate->numParams; const char **values = fsstate->param_values; - PGconn *conn = fsstate->conn; + PGconn *conn = fsstate->s.conn; StringInfoData buf; PGresult *res; @@ -3384,50 +3674,119 @@ create_cursor(ForeignScanState *node) } /* - * Fetch some more rows from the node's cursor. + * Sends the next request of the node. If the given node is different from the + * current connection leader, pushes it back to waiter queue and let the given + * node be the leader. */ static void -fetch_more_data(ForeignScanState *node) +request_more_data(ForeignScanState *node) { - PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state; + PgFdwScanState *fsstate = GetPgFdwScanState(node); + ForeignScanState *leader = fsstate->s.commonstate->leader; + PGconn *conn = fsstate->s.conn; + char sql[64]; + + /* must be non-busy */ + Assert(!fsstate->s.commonstate->busy); + /* must be not-eof'ed */ + Assert(!fsstate->eof_reached); + + /* + * If this is the first call after Begin or ReScan, we need to create the + * cursor on the remote side. + */ + if (!fsstate->cursor_exists) + create_cursor(node); + + snprintf(sql, sizeof(sql), "FETCH %d FROM c%u", + fsstate->fetch_size, fsstate->cursor_number); + + if (!PQsendQuery(conn, sql)) + pgfdw_report_error(ERROR, NULL, conn, false, sql); + + fsstate->s.commonstate->busy = true; + + /* The node is the current leader, just return. */ + if (leader == node) + return; + + /* Let the node be the leader */ + if (leader != NULL) + { + remove_async_node(node); + fsstate->last_waiter = GetPgFdwScanState(leader)->last_waiter; + fsstate->waiter = leader; + } + else + { + fsstate->last_waiter = node; + fsstate->waiter = NULL; + } + + fsstate->s.commonstate->leader = node; +} + +/* + * Fetches received data and automatically send requests of the next waiter. + */ +static void +fetch_received_data(ForeignScanState *node) +{ + PgFdwScanState *fsstate = GetPgFdwScanState(node); PGresult *volatile res = NULL; MemoryContext oldcontext; + ForeignScanState *waiter; + + /* I should be the current connection leader */ + Assert(fsstate->s.commonstate->leader == node); /* * We'll store the tuples in the batch_cxt. First, flush the previous - * batch. + * batch if no tuple is remaining */ - fsstate->tuples = NULL; - MemoryContextReset(fsstate->batch_cxt); + if (fsstate->next_tuple >= fsstate->num_tuples) + { + fsstate->tuples = NULL; + fsstate->num_tuples = 0; + MemoryContextReset(fsstate->batch_cxt); + } + else if (fsstate->next_tuple > 0) + { + /* There's some remains. Move them to the beginning of the store */ + int n = 0; + + while(fsstate->next_tuple < fsstate->num_tuples) + fsstate->tuples[n++] = fsstate->tuples[fsstate->next_tuple++]; + fsstate->num_tuples = n; + } + oldcontext = MemoryContextSwitchTo(fsstate->batch_cxt); /* PGresult must be released before leaving this function. */ PG_TRY(); { - PGconn *conn = fsstate->conn; - char sql[64]; - int numrows; + PGconn *conn = fsstate->s.conn; + int addrows; + size_t newsize; int i; - snprintf(sql, sizeof(sql), "FETCH %d FROM c%u", - fsstate->fetch_size, fsstate->cursor_number); - - res = pgfdw_exec_query(conn, sql); - /* On error, report the original query, not the FETCH. */ + res = pgfdw_get_result(conn, fsstate->query); if (PQresultStatus(res) != PGRES_TUPLES_OK) pgfdw_report_error(ERROR, res, conn, false, fsstate->query); /* Convert the data into HeapTuples */ - numrows = PQntuples(res); - fsstate->tuples = (HeapTuple *) palloc0(numrows * sizeof(HeapTuple)); - fsstate->num_tuples = numrows; - fsstate->next_tuple = 0; + addrows = PQntuples(res); + newsize = (fsstate->num_tuples + addrows) * sizeof(HeapTuple); + if (fsstate->tuples) + fsstate->tuples = (HeapTuple *) repalloc(fsstate->tuples, newsize); + else + fsstate->tuples = (HeapTuple *) palloc(newsize); - for (i = 0; i < numrows; i++) + for (i = 0; i < addrows; i++) { Assert(IsA(node->ss.ps.plan, ForeignScan)); - fsstate->tuples[i] = + fsstate->tuples[fsstate->num_tuples + i] = make_tuple_from_result_row(res, i, fsstate->rel, fsstate->attinmeta, @@ -3437,22 +3796,73 @@ fetch_more_data(ForeignScanState *node) } /* Update fetch_ct_2 */ - if (fsstate->fetch_ct_2 < 2) + if (fsstate->fetch_ct_2 < 2 && fsstate->next_tuple == 0) fsstate->fetch_ct_2++; + fsstate->next_tuple = 0; + fsstate->num_tuples += addrows; + /* Must be EOF if we didn't get as many tuples as we asked for. */ - fsstate->eof_reached = (numrows < fsstate->fetch_size); + fsstate->eof_reached = (addrows < fsstate->fetch_size); } PG_FINALLY(); { + fsstate->s.commonstate->busy = false; + if (res) PQclear(res); } PG_END_TRY(); + /* let the first waiter be the next leader of this connection */ + waiter = move_to_next_waiter(node); + + /* send the next request if any */ + if (waiter) + request_more_data(waiter); + MemoryContextSwitchTo(oldcontext); } +/* + * Vacate the underlying connection so that this node can send the next query. + */ +static void +vacate_connection(PgFdwState *fdwstate, bool clear_queue) +{ + PgFdwConnCommonState *commonstate = fdwstate->commonstate; + ForeignScanState *leader; + + Assert(commonstate != NULL); + + /* just return if the connection is already available */ + if (commonstate->leader == NULL || !commonstate->busy) + return; + + /* + * let the current connection leader read all of the result for the running + * query + */ + leader = commonstate->leader; + fetch_received_data(leader); + + /* let the first waiter be the next leader of this connection */ + move_to_next_waiter(leader); + + if (!clear_queue) + return; + + /* Clear the waiting list */ + while (leader) + { + PgFdwScanState *fsstate = GetPgFdwScanState(leader); + + fsstate->last_waiter = NULL; + leader = fsstate->waiter; + fsstate->waiter = NULL; + } +} + /* * Force assorted GUC parameters to settings that ensure that we'll output * data values in a form that is unambiguous to the remote server. @@ -3566,7 +3976,9 @@ create_foreign_modify(EState *estate, user = GetUserMapping(userid, table->serverid); /* Open connection; report that we'll create a prepared statement. */ - fmstate->conn = GetConnection(user, true); + fmstate->s.conn = GetConnection(user, true); + fmstate->s.commonstate = (PgFdwConnCommonState *) + GetConnectionSpecificStorage(user, sizeof(PgFdwConnCommonState)); fmstate->p_name = NULL; /* prepared statement not made yet */ /* Set up remote query information. */ @@ -3653,6 +4065,9 @@ execute_foreign_modify(EState *estate, operation == CMD_UPDATE || operation == CMD_DELETE); + /* finish running query to send my command */ + vacate_connection((PgFdwState *)fmstate, true); + /* Set up the prepared statement on the remote server, if we didn't yet */ if (!fmstate->p_name) prepare_foreign_modify(fmstate); @@ -3680,14 +4095,14 @@ execute_foreign_modify(EState *estate, /* * Execute the prepared statement. */ - if (!PQsendQueryPrepared(fmstate->conn, + if (!PQsendQueryPrepared(fmstate->s.conn, fmstate->p_name, fmstate->p_nums, p_values, NULL, NULL, 0)) - pgfdw_report_error(ERROR, NULL, fmstate->conn, false, fmstate->query); + pgfdw_report_error(ERROR, NULL, fmstate->s.conn, false, fmstate->query); /* * Get the result, and check for success. @@ -3695,10 +4110,10 @@ execute_foreign_modify(EState *estate, * We don't use a PG_TRY block here, so be careful not to throw error * without releasing the PGresult. */ - res = pgfdw_get_result(fmstate->conn, fmstate->query); + res = pgfdw_get_result(fmstate->s.conn, fmstate->query); if (PQresultStatus(res) != (fmstate->has_returning ? PGRES_TUPLES_OK : PGRES_COMMAND_OK)) - pgfdw_report_error(ERROR, res, fmstate->conn, true, fmstate->query); + pgfdw_report_error(ERROR, res, fmstate->s.conn, true, fmstate->query); /* Check number of rows affected, and fetch RETURNING tuple if any */ if (fmstate->has_returning) @@ -3734,7 +4149,7 @@ prepare_foreign_modify(PgFdwModifyState *fmstate) /* Construct name we'll use for the prepared statement. */ snprintf(prep_name, sizeof(prep_name), "pgsql_fdw_prep_%u", - GetPrepStmtNumber(fmstate->conn)); + GetPrepStmtNumber(fmstate->s.conn)); p_name = pstrdup(prep_name); /* @@ -3744,12 +4159,12 @@ prepare_foreign_modify(PgFdwModifyState *fmstate) * the prepared statements we use in this module are simple enough that * the remote server will make the right choices. */ - if (!PQsendPrepare(fmstate->conn, + if (!PQsendPrepare(fmstate->s.conn, p_name, fmstate->query, 0, NULL)) - pgfdw_report_error(ERROR, NULL, fmstate->conn, false, fmstate->query); + pgfdw_report_error(ERROR, NULL, fmstate->s.conn, false, fmstate->query); /* * Get the result, and check for success. @@ -3757,9 +4172,9 @@ prepare_foreign_modify(PgFdwModifyState *fmstate) * We don't use a PG_TRY block here, so be careful not to throw error * without releasing the PGresult. */ - res = pgfdw_get_result(fmstate->conn, fmstate->query); + res = pgfdw_get_result(fmstate->s.conn, fmstate->query); if (PQresultStatus(res) != PGRES_COMMAND_OK) - pgfdw_report_error(ERROR, res, fmstate->conn, true, fmstate->query); + pgfdw_report_error(ERROR, res, fmstate->s.conn, true, fmstate->query); PQclear(res); /* This action shows that the prepare has been done. */ @@ -3888,16 +4303,16 @@ finish_foreign_modify(PgFdwModifyState *fmstate) * We don't use a PG_TRY block here, so be careful not to throw error * without releasing the PGresult. */ - res = pgfdw_exec_query(fmstate->conn, sql); + res = pgfdw_exec_query(fmstate->s.conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) - pgfdw_report_error(ERROR, res, fmstate->conn, true, sql); + pgfdw_report_error(ERROR, res, fmstate->s.conn, true, sql); PQclear(res); fmstate->p_name = NULL; } /* Release remote connection */ - ReleaseConnection(fmstate->conn); - fmstate->conn = NULL; + ReleaseConnection(fmstate->s.conn); + fmstate->s.conn = NULL; } /* @@ -4056,9 +4471,9 @@ execute_dml_stmt(ForeignScanState *node) * the desired result. This allows us to avoid assuming that the remote * server has the same OIDs we do for the parameters' types. */ - if (!PQsendQueryParams(dmstate->conn, dmstate->query, numParams, + if (!PQsendQueryParams(dmstate->s.conn, dmstate->query, numParams, NULL, values, NULL, NULL, 0)) - pgfdw_report_error(ERROR, NULL, dmstate->conn, false, dmstate->query); + pgfdw_report_error(ERROR, NULL, dmstate->s.conn, false, dmstate->query); /* * Get the result, and check for success. @@ -4066,10 +4481,10 @@ execute_dml_stmt(ForeignScanState *node) * We don't use a PG_TRY block here, so be careful not to throw error * without releasing the PGresult. */ - dmstate->result = pgfdw_get_result(dmstate->conn, dmstate->query); + dmstate->result = pgfdw_get_result(dmstate->s.conn, dmstate->query); if (PQresultStatus(dmstate->result) != (dmstate->has_returning ? PGRES_TUPLES_OK : PGRES_COMMAND_OK)) - pgfdw_report_error(ERROR, dmstate->result, dmstate->conn, true, + pgfdw_report_error(ERROR, dmstate->result, dmstate->s.conn, true, dmstate->query); /* Get the number of rows affected. */ @@ -5560,6 +5975,40 @@ postgresGetForeignJoinPaths(PlannerInfo *root, /* XXX Consider parameterized paths for the join relation */ } +static bool +postgresIsForeignPathAsyncCapable(ForeignPath *path) +{ + return true; +} + + +/* + * Configure waiting event. + * + * Add wait event so that the ForeignScan node is going to wait for. + */ +static bool +postgresForeignAsyncConfigureWait(ForeignScanState *node, WaitEventSet *wes, + void *caller_data, bool reinit) +{ + PgFdwScanState *fsstate = GetPgFdwScanState(node); + + + /* Reinit is not supported for now. */ + Assert(reinit); + + if (fsstate->s.commonstate->leader == node) + { + AddWaitEventToSet(wes, + WL_SOCKET_READABLE, PQsocket(fsstate->s.conn), + NULL, caller_data); + return true; + } + + return false; +} + + /* * Assess whether the aggregation, grouping and having operations can be pushed * down to the foreign server. As a side effect, save information we obtain in diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index eef410db39..96af75a33e 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -85,6 +85,7 @@ typedef struct PgFdwRelationInfo UserMapping *user; /* only set in use_remote_estimate mode */ int fetch_size; /* fetch size for this remote table */ + bool allow_prefetch; /* true to allow overlapped fetching */ /* * Name of the relation, for use while EXPLAINing ForeignScan. It is used @@ -130,6 +131,7 @@ extern void reset_transmission_modes(int nestlevel); /* in connection.c */ extern PGconn *GetConnection(UserMapping *user, bool will_prep_stmt); +void *GetConnectionSpecificStorage(UserMapping *user, size_t initsize); extern void ReleaseConnection(PGconn *conn); extern unsigned int GetCursorNumber(PGconn *conn); extern unsigned int GetPrepStmtNumber(PGconn *conn); diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 83971665e3..359208a12a 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1780,25 +1780,25 @@ INSERT INTO b(aa) VALUES('bbb'); INSERT INTO b(aa) VALUES('bbbb'); INSERT INTO b(aa) VALUES('bbbbb'); -SELECT tableoid::regclass, * FROM a; +SELECT tableoid::regclass, * FROM a ORDER BY 1, 2; SELECT tableoid::regclass, * FROM b; SELECT tableoid::regclass, * FROM ONLY a; UPDATE a SET aa = 'zzzzzz' WHERE aa LIKE 'aaaa%'; -SELECT tableoid::regclass, * FROM a; +SELECT tableoid::regclass, * FROM a ORDER BY 1, 2; SELECT tableoid::regclass, * FROM b; SELECT tableoid::regclass, * FROM ONLY a; UPDATE b SET aa = 'new'; -SELECT tableoid::regclass, * FROM a; +SELECT tableoid::regclass, * FROM a ORDER BY 1, 2; SELECT tableoid::regclass, * FROM b; SELECT tableoid::regclass, * FROM ONLY a; UPDATE a SET aa = 'newtoo'; -SELECT tableoid::regclass, * FROM a; +SELECT tableoid::regclass, * FROM a ORDER BY 1, 2; SELECT tableoid::regclass, * FROM b; SELECT tableoid::regclass, * FROM ONLY a; @@ -1840,12 +1840,12 @@ insert into bar2 values(4,44,44); insert into bar2 values(7,77,77); explain (verbose, costs off) -select * from bar where f1 in (select f1 from foo) for update; -select * from bar where f1 in (select f1 from foo) for update; +select * from bar where f1 in (select f1 from foo) order by 1 for update; +select * from bar where f1 in (select f1 from foo) order by 1 for update; explain (verbose, costs off) -select * from bar where f1 in (select f1 from foo) for share; -select * from bar where f1 in (select f1 from foo) for share; +select * from bar where f1 in (select f1 from foo) order by 1 for share; +select * from bar where f1 in (select f1 from foo) order by 1 for share; -- Check UPDATE with inherited target and an inherited source table explain (verbose, costs off) @@ -1904,8 +1904,8 @@ explain (verbose, costs off) delete from foo where f1 < 5 returning *; delete from foo where f1 < 5 returning *; explain (verbose, costs off) -update bar set f2 = f2 + 100 returning *; -update bar set f2 = f2 + 100 returning *; +with u as (update bar set f2 = f2 + 100 returning *) select * from u order by 1; +with u as (update bar set f2 = f2 + 100 returning *) select * from u order by 1; -- Test that UPDATE/DELETE with inherited target works with row-level triggers CREATE TRIGGER trig_row_before -- 2.18.4 ----Next_Part(Thu_Aug_20_16_36_08_2020_519)---- ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Extended Statistics set/restore/clear functions. @ 2026-01-14 17:57 Corey Huinker <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Corey Huinker @ 2026-01-14 17:57 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: Tender Wang <[email protected]>; PostgreSQL Hackers <[email protected]> On Thu, Dec 25, 2025 at 5:48 PM Michael Paquier <[email protected]> wrote: > On Thu, Dec 25, 2025 at 07:00:02PM +0800, Tender Wang wrote: > > Sorry, I forgot to cc the pgsql-hackers mail in my last email. > > Missed this one from v22-0001. Thanks. > -- > Michael > Some of the header cleanup work required adding utils/typcache.h to extended_stats.c. Rebased. Attachments: [text/x-patch] v23-0001-Add-extended-statistics-support-functions.patch (103.6K, ../../CADkLM=eKxxhXeYsHH7fbLf1waTdAyKeH+A=n039c8Ht+O7JKbA@mail.gmail.com/3-v23-0001-Add-extended-statistics-support-functions.patch) download | inline diff: From 1f56980a715a4d8b444ff4a3f07cdaf600743423 Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Thu, 25 Dec 2025 15:20:56 +0900 Subject: [PATCH v23 1/2] Add extended statistics support functions. Add pg_restore_extended_stats() and pg_clear_extended_stats(). These functions closely mirror their relation and attribute counterparts, but for extended statistics (i.e. CREATE STATISTICS) objects. --- src/include/catalog/pg_proc.dat | 18 + .../statistics/extended_stats_internal.h | 12 + src/backend/statistics/dependencies.c | 63 + src/backend/statistics/extended_stats.c | 1206 +++++++++++++++++ src/backend/statistics/mcv.c | 144 ++ src/backend/statistics/mvdistinct.c | 62 + src/test/regress/expected/stats_import.out | 578 ++++++++ src/test/regress/sql/stats_import.sql | 364 +++++ doc/src/sgml/func/func-admin.sgml | 98 ++ 9 files changed, 2545 insertions(+) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 2ac69bf2df5..d07094e1566 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12618,6 +12618,24 @@ proname => 'gist_translate_cmptype_common', prorettype => 'int2', proargtypes => 'int4', prosrc => 'gist_translate_cmptype_common' }, +# Extended Statistics Import +{ oid => '9947', + descr => 'restore statistics on extended statistics object', + proname => 'pg_restore_extended_stats', provolatile => 'v', proisstrict => 'f', + provariadic => 'any', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'any', + proargnames => '{kwargs}', + proargmodes => '{v}', + prosrc => 'pg_restore_extended_stats' }, +{ oid => '9948', + descr => 'clear statistics on extended statistics object', + proname => 'pg_clear_extended_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'void', + proargtypes => 'text text bool', + proargnames => '{statistics_schemaname,statistics_name,inherited}', + prosrc => 'pg_clear_extended_stats' }, + # AIO related functions { oid => '6399', descr => 'information about in-progress asynchronous IOs', proname => 'pg_get_aios', prorows => '100', proretset => 't', diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h index 229f1a755a6..3eb7f6b6176 100644 --- a/src/include/statistics/extended_stats_internal.h +++ b/src/include/statistics/extended_stats_internal.h @@ -127,4 +127,16 @@ extern Selectivity mcv_clause_selectivity_or(PlannerInfo *root, Selectivity *overlap_basesel, Selectivity *totalsel); +extern Datum import_mcvlist(HeapTuple tup, int elevel, int numattrs, + Oid *atttypids, int32 *atttypmods, Oid *atttypcolls, + int nitems, Datum *mcv_elems, bool *mcv_nulls, + bool *mcv_elem_nulls, float8 *freqs, float8 *base_freqs); +extern bool pg_ndistinct_validate_items(MVNDistinct *ndistinct, int2vector *stxkeys, + int numexprs, int elevel); +extern void free_pg_ndistinct(MVNDistinct *ndistinct); +extern bool pg_dependencies_validate_deps(const MVDependencies *dependencies, + const int2vector *stxkeys, int numexprs, + int elevel); +extern void free_pg_dependencies(MVDependencies *dependencies); + #endif /* EXTENDED_STATS_INTERNAL_H */ diff --git a/src/backend/statistics/dependencies.c b/src/backend/statistics/dependencies.c index f634f20dd66..f27e3ab4b18 100644 --- a/src/backend/statistics/dependencies.c +++ b/src/backend/statistics/dependencies.c @@ -1064,6 +1064,57 @@ clauselist_apply_dependencies(PlannerInfo *root, List *clauses, return s1; } +/* + * Validate an MVDependencies against the extended statistics object definition. + * + * Every MVDependencies must be checked to ensure that the attnums in the + * attributes list correspond to attnums/expressions defined by the + * extended statistics object. + * + * Positive attnums are attributes which must be found in the stxkeys, + * while negative attnums correspond to an expr number, so the attnum + * can't be below (0 - numexprs). + */ +bool +pg_dependencies_validate_deps(const MVDependencies *dependencies, + const int2vector *stxkeys, + int numexprs, int elevel) +{ + int attnum_expr_lowbound = 0 - numexprs; + + for (int i = 0; i < dependencies->ndeps; i++) + { + const MVDependency *dep = dependencies->deps[i]; + + for (int j = 0; j < dep->nattributes; j++) + { + AttrNumber attnum = dep->attributes[j]; + bool ok = false; + + if (attnum > 0) + { + for (int k = 0; k < stxkeys->dim1; k++) + if (attnum == stxkeys->values[k]) + { + ok = true; + break; + } + } + else if ((attnum < 0) && (attnum >= attnum_expr_lowbound)) + ok = true; + + if (!ok) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("pg_dependencies: invalid attnum for this statistics object: %d", attnum))); + return false; + } + } + } + return true; +} + /* * dependency_is_compatible_expression * Determines if the expression is compatible with functional dependencies @@ -1247,6 +1298,18 @@ dependency_is_compatible_expression(Node *clause, Index relid, List *statlist, N return false; } +/* + * Free allocations of an MVNDistinct + */ +void +free_pg_dependencies(MVDependencies *dependencies) +{ + for (int i = 0; i < dependencies->ndeps; i++) + pfree(dependencies->deps[i]); + + pfree(dependencies); +} + /* * dependencies_clauselist_selectivity * Return the estimated selectivity of (a subset of) the given clauses diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c index b9a8f257042..f0db82840be 100644 --- a/src/backend/statistics/extended_stats.c +++ b/src/backend/statistics/extended_stats.c @@ -18,21 +18,28 @@ #include "access/detoast.h" #include "access/genam.h" +#include "access/heapam.h" +#include "access/htup.h" #include "access/htup_details.h" #include "access/table.h" #include "catalog/indexing.h" +#include "catalog/pg_collation.h" #include "catalog/pg_statistic_ext.h" #include "catalog/pg_statistic_ext_data.h" +#include "catalog/pg_type_d.h" +#include "catalog/namespace.h" #include "commands/defrem.h" #include "commands/progress.h" #include "executor/executor.h" #include "miscadmin.h" +#include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "optimizer/optimizer.h" #include "parser/parsetree.h" #include "pgstat.h" #include "postmaster/autovacuum.h" #include "statistics/extended_stats_internal.h" +#include "statistics/stat_utils.h" #include "statistics/statistics.h" #include "utils/acl.h" #include "utils/array.h" @@ -45,6 +52,7 @@ #include "utils/rel.h" #include "utils/selfuncs.h" #include "utils/syscache.h" +#include "utils/typcache.h" /* * To avoid consuming too much memory during analysis and/or too much space @@ -72,6 +80,84 @@ typedef struct StatExtEntry List *exprs; /* expressions */ } StatExtEntry; +/* + * An index of the args for extended_statistics_update(). + */ +enum extended_stats_argnum +{ + STATSCHEMA_ARG = 0, + STATNAME_ARG, + INHERITED_ARG, + NDISTINCT_ARG, + DEPENDENCIES_ARG, + MOST_COMMON_VALS_ARG, + MOST_COMMON_VAL_NULLS_ARG, + MOST_COMMON_FREQS_ARG, + MOST_COMMON_BASE_FREQS_ARG, + EXPRESSIONS_ARG, + NUM_EXTENDED_STATS_ARGS +}; + +/* + * The argument names and typoids of the arguments for + * extended_statistics_update(). + */ +static struct StatsArgInfo extarginfo[] = +{ + [STATSCHEMA_ARG] = {"statistics_schemaname", TEXTOID}, + [STATNAME_ARG] = {"statistics_name", TEXTOID}, + [INHERITED_ARG] = {"inherited", BOOLOID}, + [NDISTINCT_ARG] = {"n_distinct", PG_NDISTINCTOID}, + [DEPENDENCIES_ARG] = {"dependencies", PG_DEPENDENCIESOID}, + [MOST_COMMON_VALS_ARG] = {"most_common_vals", TEXTARRAYOID}, + [MOST_COMMON_VAL_NULLS_ARG] = {"most_common_val_nulls", BOOLARRAYOID}, + [MOST_COMMON_FREQS_ARG] = {"most_common_freqs", FLOAT8ARRAYOID}, + [MOST_COMMON_BASE_FREQS_ARG] = {"most_common_base_freqs", FLOAT8ARRAYOID}, + [EXPRESSIONS_ARG] = {"exprs", TEXTARRAYOID}, + [NUM_EXTENDED_STATS_ARGS] = {0} +}; + +/* + * An index of the elements of the stxdexprs datum, which repeat for each + * expression in the extended statistics object. + * + * NOTE: the RANGE_LENGTH & RANGE_BOUNDS stats are not yet reflected in any + * version of pg_stat_ext_exprs. + */ +enum extended_stats_exprs_element +{ + NULL_FRAC_ELEM = 0, + AVG_WIDTH_ELEM, + N_DISTINCT_ELEM, + MOST_COMMON_VALS_ELEM, + MOST_COMMON_FREQS_ELEM, + HISTOGRAM_BOUNDS_ELEM, + CORRELATION_ELEM, + MOST_COMMON_ELEMS_ELEM, + MOST_COMMON_ELEM_FREQS_ELEM, + ELEM_COUNT_HISTOGRAM_ELEM, + NUM_ATTRIBUTE_STATS_ELEMS +}; + +/* + * The argument names and typoids of the repeating arguments for stxdexprs. + */ +static struct StatsArgInfo extexprarginfo[] = +{ + [NULL_FRAC_ELEM] = {"null_frac", FLOAT4OID}, + [AVG_WIDTH_ELEM] = {"avg_width", INT4OID}, + [N_DISTINCT_ELEM] = {"n_distinct", FLOAT4OID}, + [MOST_COMMON_VALS_ELEM] = {"most_common_vals", TEXTOID}, + [MOST_COMMON_FREQS_ELEM] = {"most_common_freqs", FLOAT4ARRAYOID}, + [HISTOGRAM_BOUNDS_ELEM] = {"histogram_bounds", TEXTOID}, + [CORRELATION_ELEM] = {"correlation", FLOAT4OID}, + [MOST_COMMON_ELEMS_ELEM] = {"most_common_elems", TEXTOID}, + [MOST_COMMON_ELEM_FREQS_ELEM] = {"most_common_elem_freqs", FLOAT4ARRAYOID}, + [ELEM_COUNT_HISTOGRAM_ELEM] = {"elem_count_histogram", FLOAT4ARRAYOID}, + [NUM_ATTRIBUTE_STATS_ELEMS] = {0} +}; + +static bool extended_statistics_update(FunctionCallInfo fcinfo); static List *fetch_statentries_for_relation(Relation pg_statext, Oid relid); static VacAttrStats **lookup_var_attr_stats(Bitmapset *attrs, List *exprs, @@ -99,6 +185,30 @@ static StatsBuildData *make_build_data(Relation rel, StatExtEntry *stat, int numrows, HeapTuple *rows, VacAttrStats **stats, int stattarget); +static HeapTuple get_pg_statistic_ext(Relation pg_stext, Oid nspoid, + const char *stxname); +static bool delete_pg_statistic_ext_data(Oid stxoid, bool inherited); + +typedef struct +{ + bool ndistinct; + bool dependencies; + bool mcv; + bool expressions; +} stakindFlags; + +static void expand_stxkind(HeapTuple tup, stakindFlags * enabled); +static void upsert_pg_statistic_ext_data(const Datum *values, + const bool *nulls, + const bool *replaces); +static bool check_mcvlist_array(ArrayType *arr, int argindex, + int required_ndims, int mcv_length); +static Datum import_expressions(Relation pgsd, int numexprs, + Oid *atttypids, int32 *atttypmods, + Oid *atttypcolls, ArrayType *exprs_arr); +static bool text_to_float4(Datum input, Datum *output); +static bool text_to_int4(Datum input, Datum *output); + /* * Compute requested extended stats, using the rows sampled for the plain @@ -2611,3 +2721,1099 @@ make_build_data(Relation rel, StatExtEntry *stat, int numrows, HeapTuple *rows, return result; } + +/* + * Fetch a pg_statistic_ext row by name+nspoid. + */ +static HeapTuple +get_pg_statistic_ext(Relation pg_stext, Oid nspoid, const char *stxname) +{ + ScanKeyData key[2]; + SysScanDesc scan; + HeapTuple tup; + Oid stxoid = InvalidOid; + + ScanKeyInit(&key[0], + Anum_pg_statistic_ext_stxname, + BTEqualStrategyNumber, + F_NAMEEQ, + CStringGetDatum(stxname)); + ScanKeyInit(&key[1], + Anum_pg_statistic_ext_stxnamespace, + BTEqualStrategyNumber, + F_OIDEQ, + ObjectIdGetDatum(nspoid)); + + /* + * Try to find matching pg_statistic_ext row. + */ + scan = systable_beginscan(pg_stext, + StatisticExtNameIndexId, + true, + NULL, + 2, + key); + + /* Unique index, so we get 0 or 1 tuples. */ + tup = systable_getnext(scan); + + if (HeapTupleIsValid(tup)) + stxoid = ((Form_pg_statistic_ext) GETSTRUCT(tup))->oid; + + systable_endscan(scan); + + if (!OidIsValid(stxoid)) + return NULL; + + return SearchSysCacheCopy1(STATEXTOID, ObjectIdGetDatum(stxoid)); +} + +/* + * Decode the stxkind column so that we know which stats types to expect. + */ +static void +expand_stxkind(HeapTuple tup, stakindFlags * enabled) +{ + Datum datum; + ArrayType *arr; + char *kinds; + + datum = SysCacheGetAttrNotNull(STATEXTOID, + tup, + Anum_pg_statistic_ext_stxkind); + arr = DatumGetArrayTypeP(datum); + if (ARR_NDIM(arr) != 1 || ARR_HASNULL(arr) || ARR_ELEMTYPE(arr) != CHAROID) + elog(ERROR, "stxkind is not a 1-D char array"); + + kinds = (char *) ARR_DATA_PTR(arr); + + for (int i = 0; i < ARR_DIMS(arr)[0]; i++) + if (kinds[i] == STATS_EXT_NDISTINCT) + enabled->ndistinct = true; + else if (kinds[i] == STATS_EXT_DEPENDENCIES) + enabled->dependencies = true; + else if (kinds[i] == STATS_EXT_MCV) + enabled->mcv = true; + else if (kinds[i] == STATS_EXT_EXPRESSIONS) + enabled->expressions = true; +} + +/* + * Perform the actual storage of a pg_statistic_ext_data tuple. + */ +static void +upsert_pg_statistic_ext_data(const Datum *values, const bool *nulls, + const bool *replaces) +{ + Relation pg_stextdata; + HeapTuple stxdtup; + HeapTuple newtup; + + pg_stextdata = table_open(StatisticExtDataRelationId, RowExclusiveLock); + + stxdtup = SearchSysCache2(STATEXTDATASTXOID, + values[Anum_pg_statistic_ext_data_stxoid - 1], + values[Anum_pg_statistic_ext_data_stxdinherit - 1]); + + if (HeapTupleIsValid(stxdtup)) + { + newtup = heap_modify_tuple(stxdtup, + RelationGetDescr(pg_stextdata), + values, + nulls, + replaces); + CatalogTupleUpdate(pg_stextdata, &newtup->t_self, newtup); + ReleaseSysCache(stxdtup); + } + else + { + newtup = heap_form_tuple(RelationGetDescr(pg_stextdata), values, nulls); + CatalogTupleInsert(pg_stextdata, newtup); + } + + heap_freetuple(newtup); + + CommandCounterIncrement(); + + table_close(pg_stextdata, RowExclusiveLock); +} + +/* + * Insert or Update Extended Statistics + * + * Major errors, such as the table not existing, the statistics object not + * existing, or a permissions failure are always reported at ERROR. Other + * errors, such as a conversion failure on one statistic kind, are reported + * as WARNINGs, and other statistic kinds may still be updated. + */ +static bool +extended_statistics_update(FunctionCallInfo fcinfo) +{ + Oid nspoid; + char *nspname; + char *stxname; + bool inherited; + Relation pg_stext; + HeapTuple tup = NULL; + + stakindFlags enabled; + stakindFlags has; + + Form_pg_statistic_ext stxform; + + Datum values[Natts_pg_statistic_ext_data]; + bool nulls[Natts_pg_statistic_ext_data]; + bool replaces[Natts_pg_statistic_ext_data]; + + bool success = true; + + Datum exprdatum; + bool isnull; + List *exprs = NIL; + int numattnums = 0; + int numexprs = 0; + int numattrs = 0; + + /* arrays of type info, if we need them */ + Oid *atttypids = NULL; + int32 *atttypmods = NULL; + Oid *atttypcolls = NULL; + Relation rel; + Oid locked_table = InvalidOid; + + memset(nulls, false, sizeof(nulls)); + memset(values, 0, sizeof(values)); + memset(replaces, 0, sizeof(replaces)); + memset(&enabled, 0, sizeof(enabled)); + + has.mcv = (!PG_ARGISNULL(MOST_COMMON_VALS_ARG) && + !PG_ARGISNULL(MOST_COMMON_VAL_NULLS_ARG) && + !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) && + !PG_ARGISNULL(MOST_COMMON_BASE_FREQS_ARG)); + has.ndistinct = !PG_ARGISNULL(NDISTINCT_ARG); + has.dependencies = !PG_ARGISNULL(DEPENDENCIES_ARG); + has.expressions = !PG_ARGISNULL(EXPRESSIONS_ARG); + + if (RecoveryInProgress()) + { + ereport(WARNING, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("recovery is in progress"), + errhint("Statistics cannot be modified during recovery.")); + PG_RETURN_BOOL(false); + } + + stats_check_required_arg(fcinfo, extarginfo, STATSCHEMA_ARG); + nspname = TextDatumGetCString(PG_GETARG_DATUM(STATSCHEMA_ARG)); + stats_check_required_arg(fcinfo, extarginfo, STATNAME_ARG); + stxname = TextDatumGetCString(PG_GETARG_DATUM(STATNAME_ARG)); + stats_check_required_arg(fcinfo, extarginfo, INHERITED_ARG); + inherited = PG_GETARG_NAME(INHERITED_ARG); + + nspoid = get_namespace_oid(nspname, true); + if (nspoid == InvalidOid) + { + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("Namespace \"%s\" not found.", stxname)); + PG_RETURN_BOOL(false); + } + + pg_stext = table_open(StatisticExtRelationId, RowExclusiveLock); + tup = get_pg_statistic_ext(pg_stext, nspoid, stxname); + + if (!HeapTupleIsValid(tup)) + { + table_close(pg_stext, RowExclusiveLock); + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("Extended Statistics Object \"%s\".\"%s\" not found.", + get_namespace_name(nspoid), stxname)); + PG_RETURN_BOOL(false); + } + + stxform = (Form_pg_statistic_ext) GETSTRUCT(tup); + expand_stxkind(tup, &enabled); + numattnums = stxform->stxkeys.dim1; + + /* decode expression (if any) */ + exprdatum = SysCacheGetAttr(STATEXTOID, + tup, + Anum_pg_statistic_ext_stxexprs, + &isnull); + + if (!isnull) + { + char *s; + + s = TextDatumGetCString(exprdatum); + exprs = (List *) stringToNode(s); + pfree(s); + + /* + * Run the expressions through eval_const_expressions. This is not + * just an optimization, but is necessary, because the planner will be + * comparing them to similarly-processed qual clauses, and may fail to + * detect valid matches without this. We must not use + * canonicalize_qual, however, since these aren't qual expressions. + */ + exprs = (List *) eval_const_expressions(NULL, (Node *) exprs); + + /* May as well fix opfuncids too */ + fix_opfuncids((Node *) exprs); + } + numexprs = list_length(exprs); + numattrs = numattnums + numexprs; + + /* Roundabout way of getting a RangeVar on the underlying table */ + rel = relation_open(stxform->stxrelid, AccessShareLock); + + /* no need to fetch reloid, we already have it */ + RangeVarGetRelidExtended(makeRangeVar(nspname, + RelationGetRelationName(rel), -1), + ShareUpdateExclusiveLock, 0, + RangeVarCallbackForStats, &locked_table); + + relation_close(rel, AccessShareLock); + + if (has.mcv) + { + if (!enabled.mcv) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("MCV parameters \"%s\", \"%s\", \"%s\", and \"%s\" were all " + "specified for extended statistics object that does not expect MCV ", + extarginfo[MOST_COMMON_VALS_ARG].argname, + extarginfo[MOST_COMMON_VAL_NULLS_ARG].argname, + extarginfo[MOST_COMMON_FREQS_ARG].argname, + extarginfo[MOST_COMMON_BASE_FREQS_ARG].argname)); + has.mcv = false; + success = false; + } + } + else + { + /* The MCV args must all be NULL. */ + if (!PG_ARGISNULL(MOST_COMMON_VALS_ARG) || + !PG_ARGISNULL(MOST_COMMON_VAL_NULLS_ARG) || + !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) || + !PG_ARGISNULL(MOST_COMMON_BASE_FREQS_ARG)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("MCV parameters \"%s\", \"%s\", \"%s\", and \"%s\" must be all specified if any are specified", + extarginfo[MOST_COMMON_VALS_ARG].argname, + extarginfo[MOST_COMMON_VAL_NULLS_ARG].argname, + extarginfo[MOST_COMMON_FREQS_ARG].argname, + extarginfo[MOST_COMMON_BASE_FREQS_ARG].argname)); + success = false; + } + } + + if (has.ndistinct && !enabled.ndistinct) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameters \"%s\" was specified for extended statistics object " + "that does not expect \"%s\"", + extarginfo[NDISTINCT_ARG].argname, + extarginfo[NDISTINCT_ARG].argname)); + has.ndistinct = false; + success = false; + } + + if (has.dependencies && !enabled.dependencies) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameters \"%s\" was specified for extended statistics object " + "that does not expect \"%s\"", + extarginfo[DEPENDENCIES_ARG].argname, + extarginfo[DEPENDENCIES_ARG].argname)); + has.dependencies = false; + success = false; + } + + if (has.expressions && !enabled.expressions) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameters \"%s\" was specified for extended statistics object " + "that does not expect \"%s\"", + extarginfo[DEPENDENCIES_ARG].argname, + extarginfo[DEPENDENCIES_ARG].argname)); + has.expressions = false; + success = false; + } + + /* + * Either of these statsistic types requires that we supply + * semi-filled-out VacAttrStatP array. + * + * + * It is not possible to use the existing lookup_var_attr_stats() and + * examine_attribute() because these functions will skip attributes for + * which attstattarget is 0, and we may have stats to import for those + * attributes. + */ + if (has.mcv || has.expressions) + { + atttypids = palloc0_array(Oid, numattrs); + atttypmods = palloc0_array(int32, numattrs); + atttypcolls = palloc0_array(Oid, numattrs); + + for (int i = 0; i < numattnums; i++) + { + AttrNumber attnum = stxform->stxkeys.values[i]; + + Oid lt_opr; + Oid eq_opr; + char typetype; + + /* + * fetch attribute entries the same as are done for attribute + * stats + */ + statatt_get_type(stxform->stxrelid, + attnum, + &atttypids[i], + &atttypmods[i], + &typetype, + &atttypcolls[i], + <_opr, + &eq_opr); + } + + for (int i = numattnums; i < numattrs; i++) + { + Node *expr = list_nth(exprs, i - numattnums); + + atttypids[i] = exprType(expr); + atttypmods[i] = exprTypmod(expr); + atttypcolls[i] = exprCollation(expr); + + /* + * Duplicate logic from get_attr_stat_type + */ + + /* + * If it's a multirange, step down to the range type, as is done + * by multirange_typanalyze(). + */ + if (type_is_multirange(atttypids[i])) + atttypids[i] = get_multirange_range(atttypids[i]); + + /* + * Special case: collation for tsvector is DEFAULT_COLLATION_OID. + * See compute_tsvector_stats(). + */ + if (atttypids[i] == TSVECTOROID) + atttypcolls[i] = DEFAULT_COLLATION_OID; + + } + } + + /* Primary Key: cannot be NULL or replaced. */ + values[Anum_pg_statistic_ext_data_stxoid - 1] = ObjectIdGetDatum(stxform->oid); + values[Anum_pg_statistic_ext_data_stxdinherit - 1] = BoolGetDatum(inherited); + + if (has.ndistinct) + { + Datum ndistinct_datum = PG_GETARG_DATUM(NDISTINCT_ARG); + bytea *data = DatumGetByteaPP(ndistinct_datum); + MVNDistinct *ndistinct = statext_ndistinct_deserialize(data); + + if (pg_ndistinct_validate_items(ndistinct, &stxform->stxkeys, numexprs, WARNING)) + { + values[Anum_pg_statistic_ext_data_stxdndistinct - 1] = ndistinct_datum; + replaces[Anum_pg_statistic_ext_data_stxdndistinct - 1] = true; + } + else + { + nulls[Anum_pg_statistic_ext_data_stxdndistinct - 1] = true; + success = false; + } + + free_pg_ndistinct(ndistinct); + } + else + nulls[Anum_pg_statistic_ext_data_stxdndistinct - 1] = true; + + if (has.dependencies) + { + Datum dependencies_datum = PG_GETARG_DATUM(DEPENDENCIES_ARG); + bytea *data = DatumGetByteaPP(dependencies_datum); + MVDependencies *dependencies = statext_dependencies_deserialize(data); + + if (pg_dependencies_validate_deps(dependencies, &stxform->stxkeys, numexprs, WARNING)) + { + values[Anum_pg_statistic_ext_data_stxddependencies - 1] = dependencies_datum; + replaces[Anum_pg_statistic_ext_data_stxddependencies - 1] = true; + } + else + { + nulls[Anum_pg_statistic_ext_data_stxddependencies - 1] = true; + success = false; + } + + free_pg_dependencies(dependencies); + } + else + nulls[Anum_pg_statistic_ext_data_stxddependencies - 1] = true; + + if (has.mcv) + { + Datum datum; + ArrayType *mcv_arr = PG_GETARG_ARRAYTYPE_P(MOST_COMMON_VALS_ARG); + ArrayType *nulls_arr = PG_GETARG_ARRAYTYPE_P(MOST_COMMON_VAL_NULLS_ARG); + ArrayType *freqs_arr = PG_GETARG_ARRAYTYPE_P(MOST_COMMON_FREQS_ARG); + ArrayType *base_freqs_arr = PG_GETARG_ARRAYTYPE_P(MOST_COMMON_BASE_FREQS_ARG); + int nitems; + Datum *mcv_elems; + bool *mcv_nulls; + int check_nummcv; + + /* + * The mcv_arr is an array of arrays of text, and we use it as the + * reference array for checking the lengths of the other 3 arrays. + */ + if (ARR_NDIM(mcv_arr) != 2) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameters \"%s\" must be a text array of 2 dimensions.", + extarginfo[MOST_COMMON_VALS_ARG].argname)); + return (Datum) 0; + } + + nitems = ARR_DIMS(mcv_arr)[0]; + + /* Fixed length arrays that cannot contain NULLs. */ + if (!check_mcvlist_array(nulls_arr, MOST_COMMON_VAL_NULLS_ARG, + 2, nitems) || + !check_mcvlist_array(freqs_arr, MOST_COMMON_FREQS_ARG, + 1, nitems) || + !check_mcvlist_array(base_freqs_arr, MOST_COMMON_BASE_FREQS_ARG, + 1, nitems)) + return (Datum) 0; + + + deconstruct_array_builtin(mcv_arr, TEXTOID, &mcv_elems, + &mcv_nulls, &check_nummcv); + + Assert(check_nummcv == (nitems * numattrs)); + + datum = import_mcvlist(tup, WARNING, numattrs, + atttypids, atttypmods, atttypcolls, + nitems, mcv_elems, mcv_nulls, + (bool *) ARR_DATA_PTR(nulls_arr), + (float8 *) ARR_DATA_PTR(freqs_arr), + (float8 *) ARR_DATA_PTR(base_freqs_arr)); + + values[Anum_pg_statistic_ext_data_stxdmcv - 1] = datum; + replaces[Anum_pg_statistic_ext_data_stxdmcv - 1] = true; + } + else + nulls[Anum_pg_statistic_ext_data_stxdmcv - 1] = true; + + if (has.expressions) + { + Datum datum; + Relation pgsd; + + pgsd = table_open(StatisticRelationId, RowExclusiveLock); + + /* + * Generate the expressions array. + * + * The attytypids, attytypmods, and atttypcols arrays have all the regular + * attributes listed first, so we can pass those arrays with a start point + * after the last regular attribute, and there should be numexprs elements + * remaining. + */ + datum = import_expressions(pgsd, numexprs, + &atttypids[numattnums], &atttypmods[numattnums], + &atttypcolls[numattnums], + PG_GETARG_ARRAYTYPE_P(EXPRESSIONS_ARG)); + + table_close(pgsd, RowExclusiveLock); + + values[Anum_pg_statistic_ext_data_stxdexpr - 1] = datum; + replaces[Anum_pg_statistic_ext_data_stxdexpr - 1] = true; + } + else + nulls[Anum_pg_statistic_ext_data_stxdexpr - 1] = true; + + upsert_pg_statistic_ext_data(values, nulls, replaces); + + heap_freetuple(tup); + table_close(pg_stext, RowExclusiveLock); + + if (atttypids != NULL) + pfree(atttypids); + if (atttypmods != NULL) + pfree(atttypmods); + if (atttypcolls != NULL) + pfree(atttypcolls); + return success; +} + +/* + * Consistency checks to ensure that other mcvlist arrays are in alignment + * with the mcv array. + */ +static bool +check_mcvlist_array(ArrayType *arr, int argindex, int required_ndims, + int mcv_length) +{ + if (ARR_NDIM(arr) != required_ndims) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameter \"%s\" must be an array of %d dimensions.", + extarginfo[argindex].argname, required_ndims)); + return false; + } + + if (array_contains_nulls(arr)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Array \"%s\" cannot contain NULLs.", + extarginfo[argindex].argname)); + return false; + } + + if (ARR_DIMS(arr)[0] != mcv_length) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameters \"%s\" must have the same number of elements as \"%s\"", + extarginfo[argindex].argname, + extarginfo[MOST_COMMON_VALS_ARG].argname)); + return false; + } + + return true; +} + +/* + * Warn of type mismatch. Common pattern. + */ +static Datum +warn_type_mismatch(Datum d, const char *argname) +{ + char *s = TextDatumGetCString(d); + + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Expression %s element \"%s\" does not match expected input type.", + argname, s)); + return (Datum) 0; +} + +/* + * Create the stxdexprs datum using the user input in an array of array of + * text, referenced against the datatypes for the expressions. + * + * This datum is needed to fill out a complete pg_statistic_ext_data tuple. + * + * The input arrays should each have numexprs elements in them and they should + * be the in the order that the expressions appear in the statistics object. + */ +static Datum +import_expressions(Relation pgsd, int numexprs, + Oid *atttypids, int32 *atttypmods, + Oid *atttypcolls, ArrayType *exprs_arr) +{ + Datum *exprs_elems; + bool *exprs_nulls; + int check_numexprs; + int offset = 0; + + FmgrInfo array_in_fn; + + Oid pgstypoid = get_rel_type_id(StatisticRelationId); + + ArrayBuildState *astate = NULL; + + /* + * Verify that the exprs_array is something that matches the expectations + * set by stxdexprs generally and the specific statistics object definition. + */ + if (ARR_NDIM(exprs_arr) != 2) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameter \"%s\" must be a text array of 2 dimensions.", + extarginfo[EXPRESSIONS_ARG].argname)); + return (Datum) 0; + } + + if (ARR_DIMS(exprs_arr)[0] != numexprs) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameter \"%s\" must have an outer dimension of %d elements.", + extarginfo[EXPRESSIONS_ARG].argname, numexprs)); + return (Datum) 0; + } + if (ARR_DIMS(exprs_arr)[1] != NUM_ATTRIBUTE_STATS_ELEMS) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameter \"%s\" must have an inner dimension of %d elements.", + extarginfo[EXPRESSIONS_ARG].argname, + NUM_ATTRIBUTE_STATS_ELEMS)); + return (Datum) 0; + } + + fmgr_info(F_ARRAY_IN, &array_in_fn); + + deconstruct_array_builtin(exprs_arr, TEXTOID, &exprs_elems, + &exprs_nulls, &check_numexprs); + + /* + * Iterate over each expected expression. + * + * The values/nulls/replaces arrays are deconstructed into a 1-D arrays, so + * we have to advance an offset by NUM_ATTRIBUTE_STATS_ELEMS to get to the + * next row of the 2-D array. + */ + for (int i = 0; i < numexprs; i++) + { + Oid typid = atttypids[i]; + int32 typmod = atttypmods[i]; + Oid stacoll = atttypcolls[i]; + TypeCacheEntry *typcache; + + Oid elemtypid = InvalidOid; + Oid elem_eq_opr = InvalidOid; + + bool ok; + + Datum values[Natts_pg_statistic]; + bool nulls[Natts_pg_statistic]; + bool replaces[Natts_pg_statistic]; + + HeapTuple pgstup; + Datum pgstdat; + + /* Advance the indexes to the next offset. */ + const int null_frac_idx = offset + NULL_FRAC_ELEM; + const int avg_width_idx = offset + AVG_WIDTH_ELEM; + const int n_distinct_idx = offset + N_DISTINCT_ELEM; + const int most_common_vals_idx = offset + MOST_COMMON_VALS_ELEM; + const int most_common_freqs_idx = offset + MOST_COMMON_FREQS_ELEM; + const int histogram_bounds_idx = offset + HISTOGRAM_BOUNDS_ELEM; + const int correlation_idx = offset + CORRELATION_ELEM; + const int most_common_elems_idx = offset + MOST_COMMON_ELEMS_ELEM; + const int most_common_elems_freqs_idx = offset + MOST_COMMON_ELEM_FREQS_ELEM; + const int elem_count_histogram_idx = offset + ELEM_COUNT_HISTOGRAM_ELEM; + + /* This finds the right operators even if atttypid is a domain */ + typcache = lookup_type_cache(typid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR); + + statatt_init_empty_tuple(InvalidOid, InvalidAttrNumber, false, + values, nulls, replaces); + + /* + * Check each of the fixed attributes to see if they have values set. If not + * set, then just let them stay with the default values set in + * statatt_init_empty_tuple(). + */ + if (!exprs_nulls[null_frac_idx]) + { + ok = text_to_float4(exprs_elems[null_frac_idx], + &values[Anum_pg_statistic_stanullfrac - 1]); + + if (!ok) + return warn_type_mismatch(exprs_elems[null_frac_idx], + extexprarginfo[NULL_FRAC_ELEM].argname); + } + + if (!exprs_nulls[avg_width_idx]) + { + ok = text_to_int4(exprs_elems[avg_width_idx], + &values[Anum_pg_statistic_stawidth - 1]); + + if (!ok) + return warn_type_mismatch(exprs_elems[avg_width_idx], + extexprarginfo[AVG_WIDTH_ELEM].argname); + } + + if (!exprs_nulls[n_distinct_idx]) + { + ok = text_to_float4(exprs_elems[n_distinct_idx], + &values[Anum_pg_statistic_stadistinct - 1]); + + if (!ok) + return warn_type_mismatch(exprs_elems[n_distinct_idx], + extexprarginfo[N_DISTINCT_ELEM].argname); + } + + /* + * The STAKIND statistics are the same as the ones found in attribute + * stats. However, these are all derived from text columns, whereas + * the ones derived for attribute stats are a mix of datatypes. This + * limits the opportunities for code sharing between the two. + * + * Some statistic kinds have both a stanumbers and a stavalues components. + * In those cases, both values must either be NOT NULL or both NULL, and + * if they aren't then we need to reject that stakind completely. Currently + * we go a step further and reject the expression array completely. + * + * Once it is established that the pairs are in NULL/NOT-NULL alignment, + * we can test either expr_nulls[] value to see if the stakind has value(s) + * that we can set or not. + */ + + /* STATISTIC_KIND_MCV */ + if (exprs_nulls[most_common_vals_idx] != + exprs_nulls[most_common_freqs_idx]) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Expression %s and %s must both be NOT NULL or both NULL.", + extexprarginfo[MOST_COMMON_VALS_ELEM].argname, + extexprarginfo[MOST_COMMON_FREQS_ELEM].argname)); + return (Datum) 0; + } + + if (!exprs_nulls[most_common_vals_idx]) + { + Datum stavalues; + Datum stanumbers; + + stavalues = statatt_build_stavalues(extexprarginfo[MOST_COMMON_VALS_ELEM].argname, + &array_in_fn, exprs_elems[most_common_vals_idx], + typid, typmod, &ok); + + if (!ok) + return (Datum) 0; + + stanumbers = statatt_build_stavalues(extexprarginfo[MOST_COMMON_FREQS_ELEM].argname, + &array_in_fn, exprs_elems[most_common_freqs_idx], + FLOAT4OID, -1, &ok); + + if (!ok) + return (Datum) 0; + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_MCV, + typcache->eq_opr, stacoll, + stanumbers, false, stavalues, false); + } + + /* STATISTIC_KIND_HISTOGRAM */ + if (!exprs_nulls[histogram_bounds_idx]) + { + Datum stavalues; + + stavalues = statatt_build_stavalues(extexprarginfo[HISTOGRAM_BOUNDS_ELEM].argname, + &array_in_fn, exprs_elems[histogram_bounds_idx], + typid, typmod, &ok); + + if (!ok) + return (Datum) 0; + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_HISTOGRAM, + typcache->lt_opr, stacoll, + 0, true, stavalues, false); + } + + /* STATISTIC_KIND_CORRELATION */ + if (!exprs_nulls[correlation_idx]) + { + Datum corr[] = {(Datum) 0}; + ArrayType *arry; + Datum stanumbers; + + ok = text_to_float4(exprs_elems[correlation_idx], &corr[0]); + + if (!ok) + { + char *s = TextDatumGetCString(exprs_elems[correlation_idx]); + + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Expression %s element \"%s\" does not match expected input type.", + extexprarginfo[CORRELATION_ELEM].argname, s)); + return (Datum) 0; + } + + arry = construct_array_builtin(corr, 1, FLOAT4OID); + + stanumbers = PointerGetDatum(arry); + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_CORRELATION, + typcache->lt_opr, stacoll, + stanumbers, false, 0, true); + } + + /* STATISTIC_KIND_MCELEM */ + if (exprs_nulls[most_common_elems_idx] != + exprs_nulls[most_common_elems_freqs_idx]) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Expression %s and %s must both be NOT NULL or both NULL.", + extexprarginfo[MOST_COMMON_ELEMS_ELEM].argname, + extexprarginfo[MOST_COMMON_ELEM_FREQS_ELEM].argname)); + return (Datum) 0; + } + + /* + * We only need to fetch element type and eq operator if we have a + * stat of type MCELEM or DECHIST, otherwise the values are unnecessary + * and not meaningful. + */ + if (!exprs_nulls[most_common_elems_idx] || + !exprs_nulls[elem_count_histogram_idx]) + { + if (!statatt_get_elem_type(typid, typcache->typtype, + &elemtypid, &elem_eq_opr)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unable to determine element type of expression")); + return (Datum) 0; + } + } + + if (!exprs_nulls[most_common_elems_idx]) + { + Datum stavalues; + Datum stanumbers; + + stavalues = statatt_build_stavalues(extexprarginfo[MOST_COMMON_ELEMS_ELEM].argname, + &array_in_fn, + exprs_elems[most_common_elems_idx], + elemtypid, typmod, &ok); + + if (!ok) + return (Datum) 0; + + stanumbers = statatt_build_stavalues(extexprarginfo[MOST_COMMON_ELEM_FREQS_ELEM].argname, + &array_in_fn, + exprs_elems[most_common_elems_freqs_idx], + FLOAT4OID, -1, &ok); + + if (!ok) + return (Datum) 0; + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_MCELEM, + elem_eq_opr, stacoll, + stanumbers, false, stavalues, false); + } + + if (!exprs_nulls[elem_count_histogram_idx]) + { + Datum stanumbers; + + stanumbers = statatt_build_stavalues(extexprarginfo[ELEM_COUNT_HISTOGRAM_ELEM].argname, + &array_in_fn, + exprs_elems[elem_count_histogram_idx], + FLOAT4OID, -1, &ok); + + if (!ok) + return (Datum) 0; + + statatt_set_slot(values, nulls, replaces, STATISTIC_KIND_DECHIST, + elem_eq_opr, stacoll, + stanumbers, false, 0, true); + } + + /* + * Currently there are no extended stats exports of the statistic + * kinds STATISTIC_KIND_BOUNDS_HISTOGRAM or + * STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM so these cannot be imported. + * These may be added in the future. + */ + + pgstup = heap_form_tuple(RelationGetDescr(pgsd), values, nulls); + pgstdat = heap_copy_tuple_as_datum(pgstup, RelationGetDescr(pgsd)); + astate = accumArrayResult(astate, pgstdat, false, pgstypoid, + CurrentMemoryContext); + + offset += NUM_ATTRIBUTE_STATS_ELEMS; + } + + pfree(exprs_elems); + pfree(exprs_nulls); + + return makeArrayResult(astate, CurrentMemoryContext); +} + +/* + * Safe conversion of text to float4. + * + * There is no need for the specific error message. + */ +static bool +text_to_float4(Datum input, Datum *output) +{ + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + char *s; + bool ok; + + s = TextDatumGetCString(input); + ok = DirectInputFunctionCallSafe(float4in, s, InvalidOid, -1, + (Node *) &escontext, output); + + pfree(s); + return ok; +} + + +/* + * Safe conversion of text to int4. + * + * There is no need for the specific error message. + */ +static bool +text_to_int4(Datum input, Datum *output) +{ + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + char *s; + bool ok; + + s = TextDatumGetCString(input); + ok = DirectInputFunctionCallSafe(int4in, s, InvalidOid, -1, + (Node *) &escontext, output); + + pfree(s); + return ok; +} + +/* + * Remove an existing pg_statistic_ext_data row for a given pg_statistic_ext + * row + inherited pair. + */ +static bool +delete_pg_statistic_ext_data(Oid stxoid, bool inherited) +{ + Relation sed = table_open(StatisticExtDataRelationId, RowExclusiveLock); + HeapTuple oldtup; + bool result = false; + + /* Is there already a pg_statistic tuple for this attribute? */ + oldtup = SearchSysCache2(STATEXTDATASTXOID, + ObjectIdGetDatum(stxoid), + BoolGetDatum(inherited)); + + if (HeapTupleIsValid(oldtup)) + { + CatalogTupleDelete(sed, &oldtup->t_self); + ReleaseSysCache(oldtup); + result = true; + } + + table_close(sed, RowExclusiveLock); + + CommandCounterIncrement(); + + return result; +} + +/* + * Restore (insert or replace) statistics for the given statistics object. + */ +Datum +pg_restore_extended_stats(PG_FUNCTION_ARGS) +{ + LOCAL_FCINFO(positional_fcinfo, NUM_EXTENDED_STATS_ARGS); + bool result = true; + + InitFunctionCallInfoData(*positional_fcinfo, NULL, NUM_EXTENDED_STATS_ARGS, + InvalidOid, NULL, NULL); + + if (!stats_fill_fcinfo_from_arg_pairs(fcinfo, positional_fcinfo, extarginfo)) + result = false; + + if (!extended_statistics_update(positional_fcinfo)) + result = false; + + PG_RETURN_BOOL(result); +} + +/* + * Delete statistics for the given statistics object. + */ +Datum +pg_clear_extended_stats(PG_FUNCTION_ARGS) +{ + char *nspname; + Oid nspoid; + char *stxname; + bool inherited; + Relation pg_stext; + HeapTuple tup; + Relation rel; + Oid locked_table = InvalidOid; + + Form_pg_statistic_ext stxform; + + stats_check_required_arg(fcinfo, extarginfo, STATSCHEMA_ARG); + nspname = TextDatumGetCString(PG_GETARG_DATUM(STATSCHEMA_ARG)); + stats_check_required_arg(fcinfo, extarginfo, STATNAME_ARG); + stxname = TextDatumGetCString(PG_GETARG_DATUM(STATNAME_ARG)); + stats_check_required_arg(fcinfo, extarginfo, INHERITED_ARG); + inherited = PG_GETARG_NAME(INHERITED_ARG); + + if (RecoveryInProgress()) + { + ereport(WARNING, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("recovery is in progress"), + errhint("Statistics cannot be modified during recovery.")); + PG_RETURN_VOID(); + } + + nspoid = get_namespace_oid(nspname, true); + if (nspoid == InvalidOid) + { + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("Namespace \"%s\" not found.", stxname)); + PG_RETURN_VOID(); + } + + pg_stext = table_open(StatisticExtRelationId, RowExclusiveLock); + tup = get_pg_statistic_ext(pg_stext, nspoid, stxname); + + if (!HeapTupleIsValid(tup)) + { + table_close(pg_stext, RowExclusiveLock); + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("Extended Statistics Object \"%s\".\"%s\" not found.", + nspname, stxname)); + PG_RETURN_VOID(); + } + + stxform = (Form_pg_statistic_ext) GETSTRUCT(tup); + + /* Roundabout way of getting a RangeVar on the underlying table */ + rel = relation_open(stxform->stxrelid, AccessShareLock); + + /* no need to fetch reloid, we already have it */ + RangeVarGetRelidExtended(makeRangeVar(nspname, + RelationGetRelationName(rel), -1), + ShareUpdateExclusiveLock, 0, + RangeVarCallbackForStats, &locked_table); + + relation_close(rel, AccessShareLock); + + delete_pg_statistic_ext_data(stxform->oid, inherited); + heap_freetuple(tup); + table_close(pg_stext, RowExclusiveLock); + + PG_RETURN_VOID(); +} diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c index 9749871b18e..dd6868a47ff 100644 --- a/src/backend/statistics/mcv.c +++ b/src/backend/statistics/mcv.c @@ -2173,3 +2173,147 @@ mcv_clause_selectivity_or(PlannerInfo *root, StatisticExtInfo *stat, return s; } + +/* + * The MCV is an array of records, but this is expected as 4 separate arrays. + * It is not possible to have a generic input function for pg_mcv_list + * because the most_common_values is a composite type with element types + * defined by the specific statistics object. + */ +Datum +import_mcvlist(HeapTuple tup, int elevel, int numattrs, Oid *atttypids, + int32 *atttypmods, Oid *atttypcolls, int nitems, + Datum *mcv_elems, bool *mcv_nulls, + bool *mcv_elem_nulls, float8 *freqs, float8 *base_freqs) +{ + MCVList *mcvlist; + bytea *bytes; + + HeapTuple *vatuples; + VacAttrStats **vastats; + + /* + * Allocate the MCV list structure, set the global parameters. + */ + mcvlist = (MCVList *) palloc0(offsetof(MCVList, items) + + (sizeof(MCVItem) * nitems)); + + mcvlist->magic = STATS_MCV_MAGIC; + mcvlist->type = STATS_MCV_TYPE_BASIC; + mcvlist->ndimensions = numattrs; + mcvlist->nitems = nitems; + + /* Set the values for the 1-D arrays and allocate space for the 2-D arrays */ + for (int i = 0; i < nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + item->frequency = freqs[i]; + item->base_frequency = base_freqs[i]; + item->values = (Datum *) palloc0_array(Datum, numattrs); + item->isnull = (bool *) palloc0_array(bool, numattrs); + } + + /* Walk through each dimension */ + for (int j = 0; j < numattrs; j++) + { + FmgrInfo finfo; + Oid ioparam; + Oid infunc; + int index = j; + + getTypeInputInfo(atttypids[j], &infunc, &ioparam); + fmgr_info(infunc, &finfo); + + /* store info about data type OIDs */ + mcvlist->types[j] = atttypids[j]; + + for (int i = 0; i < nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + /* These should be in agreement, but just to be safe check both */ + if (mcv_elem_nulls[index] || mcv_nulls[index]) + { + item->values[j] = (Datum) 0; + item->isnull[j] = true; + } + else + { + char *s = TextDatumGetCString(mcv_elems[index]); + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + if (!InputFunctionCallSafe(&finfo, s, ioparam, atttypmods[j], + (Node *) &escontext, &item->values[j])) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("MCV elemement \"%s\" does not match expected input type.", s))); + return (Datum) 0; + } + + pfree(s); + } + + index += numattrs; + } + } + + /* + * The function statext_mcv_serialize() requires an array of pointers to + * VacAttrStats records, but only a few fields within those records have + * to be filled out. + */ + vastats = (VacAttrStats **) palloc0_array(VacAttrStats *, numattrs); + vatuples = (HeapTuple *) palloc0_array(HeapTuple, numattrs); + + for (int i = 0; i < numattrs; i++) + { + Oid typid = atttypids[i]; + HeapTuple typtuple; + + typtuple = SearchSysCacheCopy1(TYPEOID, ObjectIdGetDatum(typid)); + + if (!HeapTupleIsValid(typtuple)) + elog(ERROR, "cache lookup failed for type %u", typid); + + vatuples[i] = typtuple; + + vastats[i] = palloc0_object(VacAttrStats); + + vastats[i]->attrtype = (Form_pg_type) GETSTRUCT(typtuple); + vastats[i]->attrtypid = typid; + vastats[i]->attrcollid = atttypcolls[i]; + } + + bytes = statext_mcv_serialize(mcvlist, vastats); + + for (int i = 0; i < numattrs; i++) + { + pfree(vatuples[i]); + pfree(vastats[i]); + } + pfree((void *) vatuples); + pfree((void *) vastats); + + if (bytes == NULL) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Unable to import mcv list"))); + return (Datum) 0; + } + + for (int i = 0; i < nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + pfree(item->values); + pfree(item->isnull); + } + pfree(mcvlist); + pfree(mcv_elems); + pfree(mcv_nulls); + + return PointerGetDatum(bytes); +} diff --git a/src/backend/statistics/mvdistinct.c b/src/backend/statistics/mvdistinct.c index c87e2ecbfa0..f99f35fcb54 100644 --- a/src/backend/statistics/mvdistinct.c +++ b/src/backend/statistics/mvdistinct.c @@ -599,6 +599,68 @@ generate_combinations_recurse(CombinationGenerator *state, } } +/* + * Free allocations of an MVNDistinct + */ +void +free_pg_ndistinct(MVNDistinct *ndistinct) +{ + for (int i = 0; i < ndistinct->nitems; i++) + pfree(ndistinct->items[i].attributes); + + pfree(ndistinct); +} + +/* + * Validate an MVNDistinct against the extended statistics object definition. + * + * Every MVNDistinctItem must be checked to ensure that the attnums in the + * attributes list correspond to attnums/expressions defined by the + * extended statistics object. + * + * Positive attnums are attributes which must be found in the stxkeys, + * while negative attnums correspond to an expr number, so the attnum + * can't be below (0 - numexprs). + */ +bool +pg_ndistinct_validate_items(MVNDistinct *ndistinct, int2vector *stxkeys, int numexprs, int elevel) +{ + int attnum_expr_lowbound = 0 - numexprs; + + for (int i = 0; i < ndistinct->nitems; i++) + { + MVNDistinctItem item = ndistinct->items[i]; + + for (int j = 0; j < item.nattributes; j++) + { + AttrNumber attnum = item.attributes[j]; + bool ok = false; + + if (attnum > 0) + { + for (int k = 0; k < stxkeys->dim1; k++) + if (attnum == stxkeys->values[k]) + { + ok = true; + break; + } + } + else if ((attnum < 0) && (attnum >= attnum_expr_lowbound)) + ok = true; + + if (!ok) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("pg_ndistinct: invalid attnum for this statistics object: %d", attnum))); + return false; + } + } + } + return true; +} + + /* * generate_combinations * generate all k-combinations of N elements diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 98ce7dc2841..4d7dcfca093 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -1084,11 +1084,15 @@ SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import.complex_type, UNION ALL SELECT 4, 'four', NULL, int4range(0,100), NULL; CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1)); +CREATE STATISTICS stats_import.test_stat ON name, comp, lower(arange), array_length(tags,1) +FROM stats_import.test; -- Generate statistics on table with data ANALYZE stats_import.test; CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ) WITH (autovacuum_enabled = false); CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1)); +CREATE STATISTICS stats_import.test_stat_clone ON name, comp, lower(arange), array_length(tags,1) +FROM stats_import.test_clone; -- -- Copy stats from test to test_clone, and is_odd to is_odd_clone -- @@ -1342,6 +1346,580 @@ AND attname = 'i'; (1 row) DROP TABLE stats_temp; +-- set n_distinct using at attnum (1) that is not in the statistics object +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [3,-1,-2], "ndistinct" : 4}, + {"attributes" : [1,2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct + ); +WARNING: pg_ndistinct: invalid attnum for this statistics object: 1 + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- set n_distinct using at attnum that is 0 +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [0,2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct + ); +ERROR: malformed pg_ndistinct: "[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [0,2,3,-1,-2], "ndistinct" : 4}]" +LINE 6: 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" ... + ^ +DETAIL: Invalid "attributes" element has been found: 0. +-- set n_distinct using at attnum that is outside the expression bounds(below -2) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [3,-1,-4], "ndistinct" : 4}, + {"attributes" : [3,-2,-4], "ndistinct" : 4}, + {"attributes" : [-1,-2,-4], "ndistinct" : 4}, + {"attributes" : [3,-1,-2,-4], "ndistinct" : 4}]'::pg_ndistinct + ); +WARNING: pg_ndistinct: invalid attnum for this statistics object: -4 + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- ok +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [2,3], "ndistinct" : 4}, + {"attributes" : [2,-1], "ndistinct" : 4}, + {"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct + ); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT + replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + e.dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx +-[ RECORD 1 ]----------+------------------------------------------------ +n_distinct | [{"attributes": [2, 3], "ndistinct": 4}, + + | {"attributes": [2, -1], "ndistinct": 4}, + + | {"attributes": [3, -1], "ndistinct": 4}, + + | {"attributes": [3, -2], "ndistinct": 4}, + + | {"attributes": [-1, -2], "ndistinct": 3}, + + | {"attributes": [2, 3, -1], "ndistinct": 4}, + + | {"attributes": [2, 3, -2], "ndistinct": 4}, + + | {"attributes": [2, -1, -2], "ndistinct": 4}, + + | {"attributes": [2, 3, -1, -2], "ndistinct": 4}] +dependencies | +most_common_vals | +most_common_val_nulls | +most_common_freqs | +most_common_base_freqs | + +-- set dependencies using at attnum (1) that is not in the statistics object +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": 1, "degree": 1.000000}]'::pg_dependencies + ); +WARNING: pg_dependencies: invalid attnum for this statistics object: 1 + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- set dependencies using at attnum that is 0 +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [0], "dependency": -1, "degree": 1.000000}]'::pg_dependencies + ); +ERROR: malformed pg_dependencies: "[{"attributes": [0], "dependency": -1, "degree": 1.000000}]" +LINE 6: 'dependencies', '[{"attributes": [0], "dependency": ... + ^ +DETAIL: Invalid "attributes" element has been found: 0. +-- set dependencies using at attnum that is outside the expression bounds(below -2) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": -3, "degree": 1.000000}]'::pg_dependencies + ); +WARNING: pg_dependencies: invalid attnum for this statistics object: -3 + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- ok +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": 3, "degree": 1.000000}, + {"attributes": [2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2], "dependency": -2, "degree": 1.000000}, + {"attributes": [3], "dependency": 2, "degree": 1.000000}, + {"attributes": [3], "dependency": -1, "degree": 1.000000}, + {"attributes": [3], "dependency": -2, "degree": 1.000000}, + {"attributes": [-1], "dependency": 2, "degree": 0.500000}, + {"attributes": [-1], "dependency": 3, "degree": 0.500000}, + {"attributes": [-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [-2], "dependency": 2, "degree": 0.500000}, + {"attributes": [-2], "dependency": 3, "degree": 0.500000}, + {"attributes": [-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,3], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,3], "dependency": -2, "degree": 1.000000}, + {"attributes": [2,-1], "dependency": 3, "degree": 1.000000}, + {"attributes": [2,-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [2,-2], "dependency": 3, "degree": 1.000000}, + {"attributes": [2,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [3,-1], "dependency": 2, "degree": 1.000000}, + {"attributes": [3,-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [3,-2], "dependency": 2, "degree": 1.000000}, + {"attributes": [3,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [-1,-2], "dependency": 2, "degree": 0.500000}, + {"attributes": [-1,-2], "dependency": 3, "degree": 0.500000}, + {"attributes": [2,3,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,-1,-2], "dependency": 3, "degree": 1.000000}, + {"attributes": [3,-1,-2], "dependency": 2, "degree": 1.000000}]'::pg_dependencies + ); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT + replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + replace(e.dependencies, '}, ', E'},\n') AS dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx +-[ RECORD 1 ]----------+------------------------------------------------------------------ +n_distinct | [{"attributes": [2, 3], "ndistinct": 4}, + + | {"attributes": [2, -1], "ndistinct": 4}, + + | {"attributes": [3, -1], "ndistinct": 4}, + + | {"attributes": [3, -2], "ndistinct": 4}, + + | {"attributes": [-1, -2], "ndistinct": 3}, + + | {"attributes": [2, 3, -1], "ndistinct": 4}, + + | {"attributes": [2, 3, -2], "ndistinct": 4}, + + | {"attributes": [2, -1, -2], "ndistinct": 4}, + + | {"attributes": [2, 3, -1, -2], "ndistinct": 4}] +dependencies | [{"attributes": [2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [3], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [3], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [-1], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-1], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [-1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [-2], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-2], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [-2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, 3], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, 3], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [2, -1], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2, -1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [2, -2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [3, -1], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3, -1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [3, -2], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [-1, -2], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-1, -2], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [2, 3, -2], "dependency": -1, "degree": 1.000000},+ + | {"attributes": [2, -1, -2], "dependency": 3, "degree": 1.000000},+ + | {"attributes": [3, -1, -2], "dependency": 2, "degree": 1.000000}] +most_common_vals | +most_common_val_nulls | +most_common_freqs | +most_common_base_freqs | + +-- if any one mcv param specified, all four must be specified (part 1) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[] + ); +WARNING: MCV parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" must be all specified if any are specified + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- if any one mcv param specified, all four must be specified (part 2) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[] + ); +WARNING: MCV parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" must be all specified if any are specified + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- if any one mcv param specified, all four must be specified (part 3) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[] + ); +WARNING: MCV parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" must be all specified if any are specified + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- if any one mcv param specified, all four must be specified (part 4) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[] + ); +WARNING: MCV parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" must be all specified if any are specified + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- ok +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[], + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[] + ); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT + replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + replace(e.dependencies, '}, ', E'},\n') AS dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx +-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +n_distinct | [{"attributes": [2, 3], "ndistinct": 4}, + + | {"attributes": [2, -1], "ndistinct": 4}, + + | {"attributes": [3, -1], "ndistinct": 4}, + + | {"attributes": [3, -2], "ndistinct": 4}, + + | {"attributes": [-1, -2], "ndistinct": 3}, + + | {"attributes": [2, 3, -1], "ndistinct": 4}, + + | {"attributes": [2, 3, -2], "ndistinct": 4}, + + | {"attributes": [2, -1, -2], "ndistinct": 4}, + + | {"attributes": [2, 3, -1, -2], "ndistinct": 4}] +dependencies | [{"attributes": [2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [3], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [3], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [-1], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-1], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [-1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [-2], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-2], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [-2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, 3], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, 3], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [2, -1], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2, -1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [2, -2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [3, -1], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3, -1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [3, -2], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [-1, -2], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-1, -2], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [2, 3, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, -1, -2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [3, -1, -2], "dependency": 2, "degree": 1.000000}] +most_common_vals | {{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}} +most_common_val_nulls | {{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}} +most_common_freqs | {0.25,0.25,0.25,0.25} +most_common_base_freqs | {0.00390625,0.015625,0.00390625,0.015625} + +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[] + ); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT + e.inherited, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +and e.inherited = false +\gx +-[ RECORD 1 ]----------+------- +inherited | f +null_frac | 0 +avg_width | 4 +n_distinct | -0.75 +most_common_vals | {1} +most_common_freqs | {0.5} +histogram_bounds | {-1,0} +correlation | -0.6 +most_common_elems | +most_common_elem_freqs | +elem_count_histogram | +-[ RECORD 2 ]----------+------- +inherited | f +null_frac | 0.25 +avg_width | 4 +n_distinct | -0.5 +most_common_vals | {2} +most_common_freqs | {0.5} +histogram_bounds | +correlation | 1 +most_common_elems | +most_common_elem_freqs | +elem_count_histogram | + +SELECT + pg_catalog.pg_clear_extended_stats( + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat_clone', + inherited => false); + pg_clear_extended_stats +------------------------- + +(1 row) + +SELECT COUNT(*) +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false; + count +------- + 0 +(1 row) + +SELECT COUNT(*) +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false; + count +------- + 0 +(1 row) + +-- +-- Copy stats from test_stat to test_stat_clone +-- +SELECT + e.statistics_name, + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', e.statistics_schemaname::text, + 'statistics_name', 'test_stat_clone', + 'inherited', e.inherited, + 'n_distinct', e.n_distinct, + 'dependencies', e.dependencies, + 'most_common_vals', e.most_common_vals, + 'most_common_val_nulls', e.most_common_val_nulls, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs, + 'exprs', x.exprs + ) +FROM pg_stats_ext AS e +CROSS JOIN LATERAL ( + SELECT + array_agg( + ARRAY[ee.null_frac::text, ee.avg_width::text, + ee.n_distinct::text, ee.most_common_vals::text, + ee.most_common_freqs::text, ee.histogram_bounds::text, + ee.correlation::text, ee.most_common_elems::text, + ee.most_common_elem_freqs::text, + ee.elem_count_histogram::text]) + FROM pg_stats_ext_exprs AS ee + WHERE ee.statistics_schemaname = e.statistics_schemaname + AND ee.statistics_name = e.statistics_name + AND ee.inherited = e.inherited + ) AS x(exprs) +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat'; + statistics_name | pg_restore_extended_stats +-----------------+--------------------------- + test_stat | t +(1 row) + +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, o.most_common_freqs, + o.most_common_base_freqs +FROM pg_stats_ext AS o +WHERE o.statistics_schemaname = 'stats_import' +AND o.statistics_name = 'test_stat' +EXCEPT +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, n.most_common_freqs, + n.most_common_base_freqs +FROM pg_stats_ext AS n +WHERE n.statistics_schemaname = 'stats_import' +AND n.statistics_name = 'test_stat_clone'; + inherited | n_distinct | dependencies | most_common_vals | most_common_val_nulls | most_common_freqs | most_common_base_freqs +-----------+------------+--------------+------------------+-----------------------+-------------------+------------------------ +(0 rows) + +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, n.most_common_freqs, + n.most_common_base_freqs +FROM pg_stats_ext AS n +WHERE n.statistics_schemaname = 'stats_import' +AND n.statistics_name = 'test_stat_clone' +EXCEPT +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, o.most_common_freqs, + o.most_common_base_freqs +FROM pg_stats_ext AS o +WHERE o.statistics_schemaname = 'stats_import' +AND o.statistics_name = 'test_stat'; + inherited | n_distinct | dependencies | most_common_vals | most_common_val_nulls | most_common_freqs | most_common_base_freqs +-----------+------------+--------------+------------------+-----------------------+-------------------+------------------------ +(0 rows) + +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram +FROM pg_stats_ext_exprs AS o +WHERE o.statistics_schemaname = 'stats_import' +AND o.statistics_name = 'test_stat' +EXCEPT +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram +FROM pg_stats_ext_exprs AS n +WHERE n.statistics_schemaname = 'stats_import' +AND n.statistics_name = 'test_stat_clone'; + inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram +-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- +(0 rows) + +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram +FROM pg_stats_ext_exprs AS n +WHERE n.statistics_schemaname = 'stats_import' +AND n.statistics_name = 'test_stat_clone' +EXCEPT +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram +FROM pg_stats_ext_exprs AS o +WHERE o.statistics_schemaname = 'stats_import' +AND o.statistics_name = 'test_stat'; + inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram +-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- +(0 rows) + DROP SCHEMA stats_import CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to type stats_import.complex_type diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index d140733a750..74700554c9c 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -766,6 +766,9 @@ SELECT 4, 'four', NULL, int4range(0,100), NULL; CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1)); +CREATE STATISTICS stats_import.test_stat ON name, comp, lower(arange), array_length(tags,1) +FROM stats_import.test; + -- Generate statistics on table with data ANALYZE stats_import.test; @@ -774,6 +777,9 @@ CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ) CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1)); +CREATE STATISTICS stats_import.test_stat_clone ON name, comp, lower(arange), array_length(tags,1) +FROM stats_import.test_clone; + -- -- Copy stats from test to test_clone, and is_odd to is_odd_clone -- @@ -970,4 +976,362 @@ AND tablename = 'stats_temp' AND inherited = false AND attname = 'i'; DROP TABLE stats_temp; + +-- set n_distinct using at attnum (1) that is not in the statistics object +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [3,-1,-2], "ndistinct" : 4}, + {"attributes" : [1,2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct + ); + +-- set n_distinct using at attnum that is 0 +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [0,2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct + ); + +-- set n_distinct using at attnum that is outside the expression bounds(below -2) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [3,-1,-4], "ndistinct" : 4}, + {"attributes" : [3,-2,-4], "ndistinct" : 4}, + {"attributes" : [-1,-2,-4], "ndistinct" : 4}, + {"attributes" : [3,-1,-2,-4], "ndistinct" : 4}]'::pg_ndistinct + ); + +-- ok +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [2,3], "ndistinct" : 4}, + {"attributes" : [2,-1], "ndistinct" : 4}, + {"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct + ); + +SELECT + replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + e.dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx + +-- set dependencies using at attnum (1) that is not in the statistics object +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": 1, "degree": 1.000000}]'::pg_dependencies + ); + +-- set dependencies using at attnum that is 0 +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [0], "dependency": -1, "degree": 1.000000}]'::pg_dependencies + ); + +-- set dependencies using at attnum that is outside the expression bounds(below -2) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": -3, "degree": 1.000000}]'::pg_dependencies + ); + +-- ok +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": 3, "degree": 1.000000}, + {"attributes": [2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2], "dependency": -2, "degree": 1.000000}, + {"attributes": [3], "dependency": 2, "degree": 1.000000}, + {"attributes": [3], "dependency": -1, "degree": 1.000000}, + {"attributes": [3], "dependency": -2, "degree": 1.000000}, + {"attributes": [-1], "dependency": 2, "degree": 0.500000}, + {"attributes": [-1], "dependency": 3, "degree": 0.500000}, + {"attributes": [-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [-2], "dependency": 2, "degree": 0.500000}, + {"attributes": [-2], "dependency": 3, "degree": 0.500000}, + {"attributes": [-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,3], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,3], "dependency": -2, "degree": 1.000000}, + {"attributes": [2,-1], "dependency": 3, "degree": 1.000000}, + {"attributes": [2,-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [2,-2], "dependency": 3, "degree": 1.000000}, + {"attributes": [2,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [3,-1], "dependency": 2, "degree": 1.000000}, + {"attributes": [3,-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [3,-2], "dependency": 2, "degree": 1.000000}, + {"attributes": [3,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [-1,-2], "dependency": 2, "degree": 0.500000}, + {"attributes": [-1,-2], "dependency": 3, "degree": 0.500000}, + {"attributes": [2,3,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,-1,-2], "dependency": 3, "degree": 1.000000}, + {"attributes": [3,-1,-2], "dependency": 2, "degree": 1.000000}]'::pg_dependencies + ); + +SELECT + replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + replace(e.dependencies, '}, ', E'},\n') AS dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx + +-- if any one mcv param specified, all four must be specified (part 1) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[] + ); + +-- if any one mcv param specified, all four must be specified (part 2) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[] + ); + +-- if any one mcv param specified, all four must be specified (part 3) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[] + ); + +-- if any one mcv param specified, all four must be specified (part 4) +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[] + ); + +-- ok +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[], + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[] + ); + +SELECT + replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + replace(e.dependencies, '}, ', E'},\n') AS dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx + +SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[] + ); + +SELECT + e.inherited, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +and e.inherited = false +\gx + +SELECT + pg_catalog.pg_clear_extended_stats( + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat_clone', + inherited => false); + +SELECT COUNT(*) +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false; + +SELECT COUNT(*) +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false; + +-- +-- Copy stats from test_stat to test_stat_clone +-- +SELECT + e.statistics_name, + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', e.statistics_schemaname::text, + 'statistics_name', 'test_stat_clone', + 'inherited', e.inherited, + 'n_distinct', e.n_distinct, + 'dependencies', e.dependencies, + 'most_common_vals', e.most_common_vals, + 'most_common_val_nulls', e.most_common_val_nulls, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs, + 'exprs', x.exprs + ) +FROM pg_stats_ext AS e +CROSS JOIN LATERAL ( + SELECT + array_agg( + ARRAY[ee.null_frac::text, ee.avg_width::text, + ee.n_distinct::text, ee.most_common_vals::text, + ee.most_common_freqs::text, ee.histogram_bounds::text, + ee.correlation::text, ee.most_common_elems::text, + ee.most_common_elem_freqs::text, + ee.elem_count_histogram::text]) + FROM pg_stats_ext_exprs AS ee + WHERE ee.statistics_schemaname = e.statistics_schemaname + AND ee.statistics_name = e.statistics_name + AND ee.inherited = e.inherited + ) AS x(exprs) +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat'; + +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, o.most_common_freqs, + o.most_common_base_freqs +FROM pg_stats_ext AS o +WHERE o.statistics_schemaname = 'stats_import' +AND o.statistics_name = 'test_stat' +EXCEPT +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, n.most_common_freqs, + n.most_common_base_freqs +FROM pg_stats_ext AS n +WHERE n.statistics_schemaname = 'stats_import' +AND n.statistics_name = 'test_stat_clone'; + +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, n.most_common_freqs, + n.most_common_base_freqs +FROM pg_stats_ext AS n +WHERE n.statistics_schemaname = 'stats_import' +AND n.statistics_name = 'test_stat_clone' +EXCEPT +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, o.most_common_freqs, + o.most_common_base_freqs +FROM pg_stats_ext AS o +WHERE o.statistics_schemaname = 'stats_import' +AND o.statistics_name = 'test_stat'; + +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram +FROM pg_stats_ext_exprs AS o +WHERE o.statistics_schemaname = 'stats_import' +AND o.statistics_name = 'test_stat' +EXCEPT +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram +FROM pg_stats_ext_exprs AS n +WHERE n.statistics_schemaname = 'stats_import' +AND n.statistics_name = 'test_stat_clone'; + +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram +FROM pg_stats_ext_exprs AS n +WHERE n.statistics_schemaname = 'stats_import' +AND n.statistics_name = 'test_stat_clone' +EXCEPT +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram +FROM pg_stats_ext_exprs AS o +WHERE o.statistics_schemaname = 'stats_import' +AND o.statistics_name = 'test_stat'; + DROP SCHEMA stats_import CASCADE; diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml index 2896cd9e429..7fba7a833bd 100644 --- a/doc/src/sgml/func/func-admin.sgml +++ b/doc/src/sgml/func/func-admin.sgml @@ -2165,6 +2165,104 @@ SELECT pg_restore_attribute_stats( </para> </entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_restore_extended_stats</primary> + </indexterm> + <function>pg_restore_extended_stats</function> ( + <literal>VARIADIC</literal> <parameter>kwargs</parameter> <type>"any"</type> ) + <returnvalue>boolean</returnvalue> + </para> + <para> + Creates or updates statistics for statistics objects. Ordinarily, + these statistics are collected automatically or updated as a part of + <xref linkend="sql-vacuum"/> or <xref linkend="sql-analyze"/>, so + it's not necessary to call this function. However, it is useful + after a restore to enable the optimizer to choose better plans if + <command>ANALYZE</command> has not been run yet. + </para> + <para> + The tracked statistics may change from version to version, so + arguments are passed as pairs of <replaceable>argname</replaceable> + and <replaceable>argvalue</replaceable> in the form: +<programlisting> + SELECT pg_restore_extended_stats( + '<replaceable>arg1name</replaceable>', '<replaceable>arg1value</replaceable>'::<replaceable>arg1type</replaceable>, + '<replaceable>arg2name</replaceable>', '<replaceable>arg2value</replaceable>'::<replaceable>arg2type</replaceable>, + '<replaceable>arg3name</replaceable>', '<replaceable>arg3value</replaceable>'::<replaceable>arg3type</replaceable>); +</programlisting> + </para> + <para> + For example, to set the <structfield>n_distinct</structfield>, + <structfield>dependencies</structfield>, and <structfield>exprs</structfield> + values for the statistics object <structname>myschema.mystatsobj</structname>: +<programlisting> + SELECT pg_restore_extended_stats( + 'statistics_schemaname', 'myschema'::name, + 'statistics_name', 'mytable'::name, + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4}'::pg_ndistinct, + 'dependencies', '{"2 => 1": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000}'::pg_dependencies + 'exprs', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[]); +</programlisting> + </para> + <para> + The required arguments are <literal>statistics_schemaname</literal> with a value + of type <type>name</type>, which specifies the statistics object's schema; + <literal>statistics_name</literal> with a value of type <type>name</type>, which specifies + the name of the statistics object; and <literal>inherited</literal>, which + specifies whether the statistics include values from child tables. + Other arguments are the names and values of statistics corresponding + to columns in <link linkend="view-pg-stats-ext"><structname>pg_stats_ext</structname> + </link>. To accept statistics for any expressions in the extended statistics object, the + parameter <literal>exprs</literal> with a type <type>text[]</type> is available, the array + must be two dimensional with an outer array in length equal to the number of expressions in + the object, and the inner array elements for each of the statistical columns in <link + linkend="view-pg-stats-ext-exprs"><structname>pg_stats_ext_exprs</structname></link>, some + of which are themselves arrays. + </para> + <para> + Additionally, this function accepts argument name + <literal>version</literal> of type <type>integer</type>, which + specifies the server version from which the statistics originated. + This is anticipated to be helpful in porting statistics from older + versions of <productname>PostgreSQL</productname>. + </para> + <para> + Minor errors are reported as a <literal>WARNING</literal> and + ignored, and remaining statistics will still be restored. If all + specified statistics are successfully restored, returns + <literal>true</literal>, otherwise <literal>false</literal>. + </para> + <para> + The caller must have the <literal>MAINTAIN</literal> privilege on the + table or be the owner of the database. + </para> + </entry> + </row> + <row> + <entry role="func_table_entry"> + <para role="func_signature"> + <indexterm> + <primary>pg_clear_extended_stats</primary> + </indexterm> + <function>pg_clear_extended_stats</function> ( + <parameter>statistics_schemaname</parameter> <type>name</type>, + <parameter>statistics_name</parameter> <type>name</type>, + <parameter>inherited</parameter> <type>boolean</type> ) + <returnvalue>void</returnvalue> + </para> + <para> + Clears statistics for the given statistics object, as + though the object was newly created. + </para> + <para> + The caller must have the <literal>MAINTAIN</literal> privilege on + the table or be the owner of the database. + </para> + </entry> + </row> </tbody> </tgroup> </table> base-commit: 556c92a68972818b24d31d060175ae8131d3297b -- 2.52.0 [text/x-patch] v23-0002-Include-Extended-Statistics-in-pg_dump.patch (13.6K, ../../CADkLM=eKxxhXeYsHH7fbLf1waTdAyKeH+A=n039c8Ht+O7JKbA@mail.gmail.com/4-v23-0002-Include-Extended-Statistics-in-pg_dump.patch) download | inline diff: From 1bb811049e8ef97457d80ddda5570ba23a9a1957 Mon Sep 17 00:00:00 2001 From: Corey Huinker <[email protected]> Date: Wed, 5 Nov 2025 01:13:04 -0500 Subject: [PATCH v23 2/2] Include Extended Statistics in pg_dump. Incorporate the new pg_restore_extended_stats() function into pg_dump. This detects the existence of extended statistics statistics (i.e. pg_statistic_ext_data rows). This handles many of the changes that have happened to extended statistic statistics over the various versions, including: * Format change for pg_ndistinct and pg_dependencies in current development version. Earlier versions have the format translated via the pg_dump SQL statement. * Inherited extended statistics were introduced in v15. * Expressions were introduced to extended statistics in v14. * MCV extended statistics were introduced in v13. * pg_statistic_ext_data and pg_stats_ext introduced in v12, prior to that ndstinct and depdendencies data (the only kind of stats that existed were directly on pg_statistic_ext. * Extended Statistics were introduced in v10, so there is no support for prior versions necessary. --- src/bin/pg_dump/pg_backup.h | 1 + src/bin/pg_dump/pg_backup_archiver.c | 3 +- src/bin/pg_dump/pg_dump.c | 254 +++++++++++++++++++++++++++ src/bin/pg_dump/t/002_pg_dump.pl | 28 +++ 4 files changed, 285 insertions(+), 1 deletion(-) diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index d9041dad720..2f8d9799c30 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -68,6 +68,7 @@ enum _dumpPreparedQueries PREPQUERY_DUMPCOMPOSITETYPE, PREPQUERY_DUMPDOMAIN, PREPQUERY_DUMPENUMTYPE, + PREPQUERY_DUMPEXTSTATSOBJSTATS, PREPQUERY_DUMPFUNC, PREPQUERY_DUMPOPR, PREPQUERY_DUMPRANGETYPE, diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 4a63f7392ae..1d42b0343eb 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -3007,7 +3007,8 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH) strcmp(te->desc, "SEARCHPATH") == 0) return REQ_SPECIAL; - if (strcmp(te->desc, "STATISTICS DATA") == 0) + if ((strcmp(te->desc, "STATISTICS DATA") == 0) || + (strcmp(te->desc, "EXTENDED STATISTICS DATA") == 0)) { if (!ropt->dumpStatistics) return 0; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 687dc98e46d..61d3521e105 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -325,6 +325,7 @@ static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo); static void dumpIndex(Archive *fout, const IndxInfo *indxinfo); static void dumpIndexAttach(Archive *fout, const IndexAttachInfo *attachinfo); static void dumpStatisticsExt(Archive *fout, const StatsExtInfo *statsextinfo); +static void dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo); static void dumpConstraint(Archive *fout, const ConstraintInfo *coninfo); static void dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo); static void dumpTSParser(Archive *fout, const TSParserInfo *prsinfo); @@ -8273,6 +8274,9 @@ getExtendedStatistics(Archive *fout) /* Decide whether we want to dump it */ selectDumpableStatisticsObject(&(statsextinfo[i]), fout); + + if (fout->dopt->dumpStatistics) + statsextinfo[i].dobj.components |= DUMP_COMPONENT_STATISTICS; } PQclear(res); @@ -11727,6 +11731,7 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) break; case DO_STATSEXT: dumpStatisticsExt(fout, (const StatsExtInfo *) dobj); + dumpStatisticsExtStats(fout, (const StatsExtInfo *) dobj); break; case DO_REFRESH_MATVIEW: refreshMatViewData(fout, (const TableDataInfo *) dobj); @@ -18529,6 +18534,255 @@ dumpStatisticsExt(Archive *fout, const StatsExtInfo *statsextinfo) free(qstatsextname); } +/* + * dumpStatisticsExtStats + * write out to fout the stats for an extended statistics object + */ +static void +dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) +{ + DumpOptions *dopt = fout->dopt; + PQExpBuffer query; + PGresult *res; + int nstats; + + /* Do nothing if not dumping statistics */ + if (!dopt->dumpStatistics) + return; + + if (!fout->is_prepared[PREPQUERY_DUMPEXTSTATSOBJSTATS]) + { + PQExpBuffer pq = createPQExpBuffer(); + + /* + * Set up query for constraint-specific details. + * + * 19+: query pg_stats_ext and pg_stats_ext_exprs as-is 15-18: query + * pg_stats_ext translating the ndistinct and dependencies, 14: + * inherited is always NULL 12-13: no pg_stats_ext_exprs 10-11: no + * pg_stats_ext, join pg_statistic_ext and pg_namespace + */ + + appendPQExpBufferStr(pq, + "PREPARE getExtStatsStats(pg_catalog.name, pg_catalog.name) AS\n" + "SELECT "); + + /* + * Versions 15+ have inherited stats. + * + * Create this column in all version because we need to order by it + * later. + */ + if (fout->remoteVersion >= 150000) + appendPQExpBufferStr(pq, "e.inherited, "); + else + appendPQExpBufferStr(pq, "false AS inherited, "); + + /* + * The ndistinnct and depdendencies formats changed in v19, so + * everything before that needs to be translated. + * + * The ndistinct translation converts this: + * + * {"3, 4": 11, "3, 6": 11, "4, 6": 11, "3, 4, 6": 11} + * + * to this: + * + * [ {"attributes": [3,4], "ndistinct": 11}, {"attributes": [3,6], + * "ndistinct": 11}, {"attributes": [4,6], "ndistinct": 11}, + * {"attributes": [3,4,6], "ndistinct": 11} ] + * + * and the dependencies translation converts this: + * + * {"3 => 4": 1.000000, "3 => 6": 1.000000, "4 => 6": 1.000000, "3, 4 + * => 6": 1.000000, "3, 6 => 4": 1.000000} + * + * to this: + * + * [ {"attributes": [3], "dependency": 4, "degree": 1.000000}, + * {"attributes": [3], "dependency": 6, "degree": 1.000000}, + * {"attributes": [4], "dependency": 6, "degree": 1.000000}, + * {"attributes": [3,4], "dependency": 6, "degree": 1.000000}, + * {"attributes": [3,6], "dependency": 4, "degree": 1.000000} ] + */ + if (fout->remoteVersion >= 190000) + appendPQExpBufferStr(pq, "e.n_distinct, e.dependencies, "); + else + appendPQExpBufferStr(pq, + "( " + "SELECT json_agg( " + " json_build_object( " + " 'attributes', " + " string_to_array(kv.key, ', ')::integer[], " + " 'ndistinct', " + " kv.value::bigint )) " + "FROM json_each_text(e.n_distinct::text::json) AS kv" + ") AS n_distinct, " + "( " + "SELECT json_agg( " + " json_build_object( " + " 'attributes', " + " string_to_array( " + " split_part(kv.key, ' => ', 1), " + " ', ')::integer[], " + " 'dependency', " + " split_part(kv.key, ' => ', 2)::integer, " + " 'degree', " + " kv.value::double precision )) " + "FROM json_each_text(e.dependencies::text::json) AS kv " + ") AS dependencies, "); + + /* MCV was introduced v13 */ + if (fout->remoteVersion >= 130000) + appendPQExpBufferStr(pq, + "e.most_common_vals, e.most_common_val_nulls, " + "e.most_common_freqs, e.most_common_base_freqs, "); + else + appendPQExpBufferStr(pq, + "NULL AS most_common_vals, NULL AS most_common_val_nulls, " + "NULL AS most_common_freqs, NULL AS most_common_base_freqs, "); + + /* Expressions were introduced in v14 */ + if (fout->remoteVersion >= 140000) + { + appendPQExpBufferStr(pq, + "( " + "SELECT array_agg( " + " ARRAY[ee.null_frac::text, ee.avg_width::text, " + " ee.n_distinct::text, ee.most_common_vals::text, " + " ee.most_common_freqs::text, ee.histogram_bounds::text, " + " ee.correlation::text, ee.most_common_elems::text, " + " ee.most_common_elem_freqs::text, " + " ee.elem_count_histogram::text]) " + "FROM pg_stats_ext_exprs AS ee " + "WHERE ee.statistics_schemaname = $1 " + "AND ee.statistics_name = $2 "); + + /* Inherited expressions introduced in v15 */ + if (fout->remoteVersion >= 150000) + appendPQExpBufferStr(pq, "AND ee.inherited = e.inherited"); + + appendPQExpBufferStr(pq, ") AS exprs "); + } + else + appendPQExpBufferStr(pq, "NULL AS exprs "); + + /* pg_stats_ext introduced in v12 */ + if (fout->remoteVersion >= 120000) + appendPQExpBufferStr(pq, + "FROM pg_catalog.pg_stats_ext AS e " + "WHERE e.statistics_schemaname = $1 " + "AND e.statistics_name = $2 "); + else + appendPQExpBufferStr(pq, + "FROM ( " + "SELECT s.stxndistinct AS n_distinct, " + " s.stxdependencies AS dependencies " + "FROM pg_catalog.pg_statistics_ext AS s " + "JOIN pg_catalog.pg_namespace AS n " + "ON n.oid = s.stxnamespace " + "WHERE n.nspname = $1 " + "AND e.stxname = $2 " + ") AS e "); + + /* we always have an inherited column, but it may be a constant */ + appendPQExpBufferStr(pq, "ORDER BY inherited"); + + ExecuteSqlStatement(fout, pq->data); + + fout->is_prepared[PREPQUERY_DUMPEXTSTATSOBJSTATS] = true; + + destroyPQExpBuffer(pq); + } + + query = createPQExpBuffer(); + + appendPQExpBufferStr(query, "EXECUTE getExtStatsStats("); + appendStringLiteralAH(query, statsextinfo->dobj.namespace->dobj.name, fout); + appendPQExpBufferStr(query, "::pg_catalog.name, "); + appendStringLiteralAH(query, statsextinfo->dobj.name, fout); + appendPQExpBufferStr(query, "::pg_catalog.name)"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + destroyPQExpBuffer(query); + + nstats = PQntuples(res); + + if (nstats > 0) + { + PQExpBuffer out = createPQExpBuffer(); + + int i_inherited = PQfnumber(res, "inherited"); + int i_ndistinct = PQfnumber(res, "n_distinct"); + int i_dependencies = PQfnumber(res, "dependencies"); + int i_mcv = PQfnumber(res, "most_common_vals"); + int i_mcv_nulls = PQfnumber(res, "most_common_val_nulls"); + int i_mcf = PQfnumber(res, "most_common_freqs"); + int i_mcbf = PQfnumber(res, "most_common_base_freqs"); + int i_exprs = PQfnumber(res, "exprs"); + + for (int i = 0; i < nstats; i++) + { + if (PQgetisnull(res, i, i_inherited)) + pg_fatal("inherited cannot be NULL"); + + appendPQExpBufferStr(out, + "SELECT * FROM pg_catalog.pg_restore_extended_stats(\n"); + appendPQExpBuffer(out, "\t'version', '%d'::integer,\n", + fout->remoteVersion); + appendPQExpBufferStr(out, "\t'statistics_schemaname', "); + appendStringLiteralAH(out, statsextinfo->dobj.namespace->dobj.name, fout); + appendPQExpBufferStr(out, ",\n\t'statistics_name', "); + appendStringLiteralAH(out, statsextinfo->dobj.name, fout); + appendNamedArgument(out, fout, "inherited", "boolean", + PQgetvalue(res, i, i_inherited)); + + if (!PQgetisnull(res, i, i_ndistinct)) + appendNamedArgument(out, fout, "n_distinct", "pg_ndistinct", + PQgetvalue(res, i, i_ndistinct)); + + if (!PQgetisnull(res, i, i_dependencies)) + appendNamedArgument(out, fout, "dependencies", "pg_dependencies", + PQgetvalue(res, i, i_dependencies)); + + if (!PQgetisnull(res, i, i_mcv)) + appendNamedArgument(out, fout, "most_common_vals", "text[]", + PQgetvalue(res, i, i_mcv)); + + if (!PQgetisnull(res, i, i_mcv_nulls)) + appendNamedArgument(out, fout, "most_common_val_nulls", "boolean[]", + PQgetvalue(res, i, i_mcv_nulls)); + + if (!PQgetisnull(res, i, i_mcf)) + appendNamedArgument(out, fout, "most_common_freqs", "double precision[]", + PQgetvalue(res, i, i_mcf)); + + if (!PQgetisnull(res, i, i_mcbf)) + appendNamedArgument(out, fout, "most_common_base_freqs", "double precision[]", + PQgetvalue(res, i, i_mcbf)); + + if (!PQgetisnull(res, i, i_exprs)) + appendNamedArgument(out, fout, "exprs", "text[]", + PQgetvalue(res, i, i_exprs)); + + appendPQExpBufferStr(out, "\n);\n"); + } + + ArchiveEntry(fout, nilCatalogId, createDumpId(), + ARCHIVE_OPTS(.tag = statsextinfo->dobj.name, + .namespace = statsextinfo->dobj.namespace->dobj.name, + .owner = statsextinfo->rolname, + .description = "EXTENDED STATISTICS DATA", + .section = SECTION_POST_DATA, + .createStmt = out->data, + .deps = &statsextinfo->dobj.dumpId, + .nDeps = 1)); + destroyPQExpBuffer(out); + } + PQclear(res); +} + /* * dumpConstraint * write out to fout a user-defined constraint diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 28812d28aa9..a8dcc2b5c75 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -4772,6 +4772,34 @@ my %tests = ( }, }, + # + # EXTENDED stats will end up in SECTION_POST_DATA. + # + 'extended_statistics_import' => { + create_sql => ' + CREATE TABLE dump_test.has_ext_stats + AS SELECT g.g AS x, g.g / 2 AS y FROM generate_series(1,100) AS g(g); + CREATE STATISTICS dump_test.es1 ON x, (y % 2) FROM dump_test.has_ext_stats; + ANALYZE dump_test.has_ext_stats;', + regexp => qr/^ + \QSELECT * FROM pg_catalog.pg_restore_extended_stats(\E\s+/xm, + like => { + %full_runs, + %dump_test_schema_runs, + no_data_no_schema => 1, + no_schema => 1, + section_post_data => 1, + statistics_only => 1, + schema_only_with_statistics => 1, + }, + unlike => { + exclude_dump_test_schema => 1, + no_statistics => 1, + only_dump_measurement => 1, + schema_only => 1, + }, + }, + # # While attribute stats (aka pg_statistic stats) only appear for tables # that have been analyzed, all tables will have relation stats because -- 2.52.0 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-01-14 17:57 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-10-19 08:24 [PATCH v6 3/3] async postgres_fdw Kyotaro Horiguchi <[email protected]> 2026-01-14 17:57 Re: Extended Statistics set/restore/clear functions. Corey Huinker <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox