Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vgIPy-00AYy0-0M for pgsql-hackers@arkaria.postgresql.org; Thu, 15 Jan 2026 08:09:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vgIPx-00FWuC-0X for pgsql-hackers@arkaria.postgresql.org; Thu, 15 Jan 2026 08:09:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vgIPw-00FWu4-19 for pgsql-hackers@lists.postgresql.org; Thu, 15 Jan 2026 08:09:21 +0000 Received: from fhigh-b4-smtp.messagingengine.com ([202.12.124.155]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vgIPt-000a70-0C for pgsql-hackers@lists.postgresql.org; Thu, 15 Jan 2026 08:09:20 +0000 Received: from phl-compute-06.internal (phl-compute-06.internal [10.202.2.46]) by mailfhigh.stl.internal (Postfix) with ESMTP id 7BD517A00A3; Thu, 15 Jan 2026 03:09:14 -0500 (EST) Received: from phl-frontend-04 ([10.202.2.163]) by phl-compute-06.internal (MEProxy); Thu, 15 Jan 2026 03:09:14 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1768464554; x=1768550954; bh=RfiRtAtvzy SmZ17cI/Gm80ioP9NjIHHDA/PF40mirHc=; b=ZA+qrsNN0YHg1s1/ZkBx80eB1x lObmaKGCTaYb9t2P4jpxvdTqhyCtMSBMjl1pGT4dtCQrdOTWK0MoWF6NITHNDpxU rn3XX7N5dIcpcrDWHW6C019/DVDOzAsQ6/0B4aylr8n1X/CHpowNbqrquEt44Ssh D65Gq4n40dSAKAolUM3X+OCGYbn4SS1/JuQ0fVPB5aw/b9MUPknkirlKK4PdUefY bKOQu5mSeYB/t/vKUeGtaDw59lINYDxpGdTywDl10kr8qrXty58MCE1fJBr2dudT cyKnUtlBWW/z/3VqRzKVFpCl9nzKeuB45RQ9CFw2U/K0bN/+7eVal1N58e4g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t= 1768464554; x=1768550954; bh=RfiRtAtvzySmZ17cI/Gm80ioP9NjIHHDA/P F40mirHc=; b=JULby4IAyjCnq9fWDYB68f/9bAxqbKWyz2xC7jo6Y7ssuzfENVU beslXUWQ47OMSDeGUnupMXKko4M1VeKIslM+KefajswzmqyVcmyysW3QjZRA9u9G r4tidCj5h9P3pf0CNMHeq2T8ii//hc83JQccTOgmHoLHzIT4PhkpXQ8BOyZF0KDz TzzrJcUhOud/rb+kylrM95C7YKmHW1ypRezDR60r4huPO0ciGxGIm/tG60mvTejf dz6TO+b/fDnI8+NxKEnMQNWYg08AARPn3K71+flfP3TlL/SNxeVJ3ExmMXkF77Pd 73CQE7C8UO1GUSYz0MSNT2diP1pWqjgPSgg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgdduvdehheegucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfurfetoffkrfgpnffqhgenuceu rghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmnegfrh hlucfvnfffucdljedtmdenucfjughrpeffhffvvefukfhfgggtuggjsehgtderredttddv necuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesphgrqh huihgvrhdrgiihiieqnecuggftrfgrthhtvghrnhepteelieefudffhffhtdetleeggeeg fffhkeeuveetiefgudduvedutefggeeivdejnecuvehluhhsthgvrhfuihiivgeptdenuc frrghrrghmpehmrghilhhfrhhomhepmhhitghhrggvlhesphgrqhhuihgvrhdrgiihiidp nhgspghrtghpthhtohepfedpmhhouggvpehsmhhtphhouhhtpdhrtghpthhtoheptghorh gvhidrhhhuihhnkhgvrhesghhmrghilhdrtghomhdprhgtphhtthhopehtnhgurhifrghn ghesghhmrghilhdrtghomhdprhgtphhtthhopehpghhsqhhlqdhhrggtkhgvrhhssehlih hsthhsrdhpohhsthhgrhgvshhqlhdrohhrgh X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 15 Jan 2026 03:09:12 -0500 (EST) Date: Thu, 15 Jan 2026 17:09:08 +0900 From: Michael Paquier To: Corey Huinker Cc: Tender Wang , PostgreSQL Hackers Subject: Re: Extended Statistics set/restore/clear functions. Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="HatbJtLvka/z/tJN" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --HatbJtLvka/z/tJN Content-Type: multipart/mixed; boundary="VkrMVjgTRteebSDW" Content-Disposition: inline --VkrMVjgTRteebSDW Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Wed, Jan 14, 2026 at 12:57:45PM -0500, Corey Huinker wrote: > Some of the header cleanup work required adding utils/typcache.h to > extended_stats.c. Thanks for sending a rebased set. It is a large patch, but most of the changes are super mechanical, making it sort of "simpler" to think about the whole. Anyway, I have begun a review of the backend changes, and found one independent piece that was useful, leading to 32e27bd32082 that I have just applied after some renames and a couple of fixes. Then I have spent a good portion of today looking at the backend changes, with first a focus to extract the "clear" function as it is=20 useful on its own, also because its relation lookup logic is the same as the restore function. And then, this block of code has been giving me a long pause, because it was fishy, and clearly wrong: + /* no need to fetch reloid, we already have it */ + RangeVarGetRelidExtended(makeRangeVar(nspname, + RelationGetRelationName(rel), -1), + ShareUpdateExclusiveLock, 0, + RangeVarCallbackForStats, &locked_table); A shocking thing here is that we build a RangeVar for the relation of the extended stats object based on the *extstat namespace*, not the *relation namespace*, and that we do so *after* opening the extstat catalog. Anyway, I think that we need to redesign that, and for consistency's sake do a RangeVar lookup *before* even trying to touch=20 the stats data or open its catalogs. That would be beneficial on consistency ground, and one piece where I think that patch is designed wrongly is that we should give in input of the new clear and restore functions the *schema* and *relation* names of the table we expect to find, so as we can enforce first a clean RangeVar lookup, then manipulate the stats data. This relates to 688dc6299a5b, as well, except that we would be stuck with an incorrect design after release if we are not careful because the function schema would be stuck in time. This has to be right from the start. All these changes have given me the attached patch for the clear() function. Another piece is that we did not really check that a role has the MAINTAIN rights of the relation where we want to manipulate the extended stats. A final thing is the location of the new SQL function code, let's put that into a new file, named extended_stats_funcs.c in the patch. There is nothing in the new restore and clear functions that require extended_stats.c. Finally, for the clear() part, the attached version addresses everything I have found during my review. We will need to make the restore() part follow the same design model with the Rangevar lookups of the parent relation, or we'll have trouble waiting ahead. -- Michael --VkrMVjgTRteebSDW Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v24-0001-Add-pg_clear_extended_stats.patch" Content-Transfer-Encoding: quoted-printable =46rom 24a7deed008e578e7c7f055cedcefd13d449c130 Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Thu, 15 Jan 2026 16:59:18 +0900 Subject: [PATCH v24] Add pg_clear_extended_stats() This function is able to clear the data associated to an extended statistics object, making things so as the object looks as newly-created. The caller of this function needs the following arguments for the stats cleared: - The name of the relation. - The schema name of the relation. - The name of the extended stats object. - The schema name of the extended stats object. The first two parameters are especially important to ensure a consistent lookup at the relation we expect to find for the stats object, relying on a RangeVar lookup where ACL are checks without locking a relation, critical to prevent denial-of-service attacks when using this function. The third and fourth parameters are important as a stats object may be on a different schema than its relation. This has been extracted from a larger patch by the same author, for a piece which is again useful on its own. I have rewritten large portions of it. XXX: Bump catalog version. Author: Corey Huinker Co-authored-by: Michael Paquier Discussion: https://postgr.es/m/CADkLM=3Ddpz3KFnqP-dgJ-zvRvtjsa8UZv8wDAQdqh= o=3DqN3kX0Zg@mail.gmail.com --- src/include/catalog/pg_proc.dat | 7 + src/backend/statistics/Makefile | 1 + src/backend/statistics/extended_stats_funcs.c | 232 ++++++++++++++++++ src/backend/statistics/meson.build | 1 + src/test/regress/expected/stats_import.out | 146 +++++++++++ src/test/regress/sql/stats_import.sql | 102 ++++++++ doc/src/sgml/func/func-admin.sgml | 29 +++ 7 files changed, 518 insertions(+) create mode 100644 src/backend/statistics/extended_stats_funcs.c diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.= dat index 2ac69bf2df55..894b6a1b6d6b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12618,6 +12618,13 @@ proname =3D> 'gist_translate_cmptype_common', prorettype =3D> 'int2', proargtypes =3D> 'int4', prosrc =3D> 'gist_translate_cmptype_common' }, =20 +# Extended Statistics functions +{ oid =3D> '9948', descr =3D> 'clear statistics on extended statistics obj= ect', + proname =3D> 'pg_clear_extended_stats', proisstrict =3D> 'f', provolatil= e =3D> 'v', + proparallel =3D> 'u', prorettype =3D> 'void', proargtypes =3D> 'text tex= t text text bool', + proargnames =3D> '{schemaname,relname,statistics_schemaname,statistics_n= ame,inherited}', + prosrc =3D> 'pg_clear_extended_stats' }, + # AIO related functions { oid =3D> '6399', descr =3D> 'information about in-progress asynchronous = IOs', proname =3D> 'pg_get_aios', prorows =3D> '100', proretset =3D> 't', diff --git a/src/backend/statistics/Makefile b/src/backend/statistics/Makef= ile index 4672bd90f225..7ff5938b0273 100644 --- a/src/backend/statistics/Makefile +++ b/src/backend/statistics/Makefile @@ -16,6 +16,7 @@ OBJS =3D \ attribute_stats.o \ dependencies.o \ extended_stats.o \ + extended_stats_funcs.o \ mcv.o \ mvdistinct.o \ relation_stats.o \ diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/st= atistics/extended_stats_funcs.c new file mode 100644 index 000000000000..9edc6c1a97eb --- /dev/null +++ b/src/backend/statistics/extended_stats_funcs.c @@ -0,0 +1,232 @@ +/*------------------------------------------------------------------------- + * + * extended_stats_funcs.c + * Functions for manipulating extended statistics. + * + * This file includes the set of facilities required to support the direct + * manipulations of extended statistics objects. + * + * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/backend/statistics/extended_stats_funcs.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/heapam.h" +#include "catalog/indexing.h" +#include "catalog/namespace.h" +#include "catalog/pg_database.h" +#include "catalog/pg_statistic_ext.h" +#include "catalog/pg_statistic_ext_data.h" +#include "miscadmin.h" +#include "nodes/makefuncs.h" +#include "statistics/stat_utils.h" +#include "utils/acl.h" +#include "utils/builtins.h" +#include "utils/fmgroids.h" +#include "utils/lsyscache.h" +#include "utils/syscache.h" + + +/* + * Index of the arguments for the SQL functions. + */ +enum extended_stats_argnum +{ + RELSCHEMA_ARG =3D 0, + RELNAME_ARG, + STATSCHEMA_ARG, + STATNAME_ARG, + INHERITED_ARG, + NUM_EXTENDED_STATS_ARGS, +}; + +/* + * The argument names and type OIDs of the arguments for the SQL + * functions. + */ +static struct StatsArgInfo extarginfo[] =3D +{ + [RELSCHEMA_ARG] =3D {"schemaname", TEXTOID}, + [RELNAME_ARG] =3D {"relname", TEXTOID}, + [STATSCHEMA_ARG] =3D {"statistics_schemaname", TEXTOID}, + [STATNAME_ARG] =3D {"statistics_name", TEXTOID}, + [INHERITED_ARG] =3D {"inherited", BOOLOID}, + [NUM_EXTENDED_STATS_ARGS] =3D {0}, +}; + +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); + +/* + * Fetch a pg_statistic_ext row by name and namespace OID. + */ +static HeapTuple +get_pg_statistic_ext(Relation pg_stext, Oid nspoid, const char *stxname) +{ + ScanKeyData key[2]; + SysScanDesc scan; + HeapTuple tup; + Oid stxoid =3D 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 =3D systable_beginscan(pg_stext, + StatisticExtNameIndexId, + true, + NULL, + 2, + key); + + /* Lookup is based on a unique index, so we get either 0 or 1 tuple. */ + tup =3D systable_getnext(scan); + + if (HeapTupleIsValid(tup)) + stxoid =3D ((Form_pg_statistic_ext) GETSTRUCT(tup))->oid; + + systable_endscan(scan); + + if (!OidIsValid(stxoid)) + return NULL; + + return SearchSysCacheCopy1(STATEXTOID, ObjectIdGetDatum(stxoid)); +} + +/* + * Remove an existing pg_statistic_ext_data row for a given pg_statistic_e= xt + * row and "inherited" pair. + */ +static bool +delete_pg_statistic_ext_data(Oid stxoid, bool inherited) +{ + Relation sed =3D table_open(StatisticExtDataRelationId, RowExclusiveLock); + HeapTuple oldtup; + bool result =3D false; + + /* Is there already a pg_statistic tuple for this attribute? */ + oldtup =3D SearchSysCache2(STATEXTDATASTXOID, + ObjectIdGetDatum(stxoid), + BoolGetDatum(inherited)); + + if (HeapTupleIsValid(oldtup)) + { + CatalogTupleDelete(sed, &oldtup->t_self); + ReleaseSysCache(oldtup); + result =3D true; + } + + table_close(sed, RowExclusiveLock); + + CommandCounterIncrement(); + + return result; +} + +/* + * Delete statistics for the given statistics object. + */ +Datum +pg_clear_extended_stats(PG_FUNCTION_ARGS) +{ + char *relnspname; + char *relname; + char *nspname; + Oid nspoid; + Oid relid; + char *stxname; + bool inherited; + Relation pg_stext; + HeapTuple tup; + Form_pg_statistic_ext stxform; + Oid locked_table =3D InvalidOid; + + /* relation arguments */ + stats_check_required_arg(fcinfo, extarginfo, RELSCHEMA_ARG); + relnspname =3D TextDatumGetCString(PG_GETARG_DATUM(RELSCHEMA_ARG)); + stats_check_required_arg(fcinfo, extarginfo, RELNAME_ARG); + relname =3D TextDatumGetCString(PG_GETARG_DATUM(RELNAME_ARG)); + + /* extended statistics arguments */ + stats_check_required_arg(fcinfo, extarginfo, STATSCHEMA_ARG); + nspname =3D TextDatumGetCString(PG_GETARG_DATUM(STATSCHEMA_ARG)); + stats_check_required_arg(fcinfo, extarginfo, STATNAME_ARG); + stxname =3D TextDatumGetCString(PG_GETARG_DATUM(STATNAME_ARG)); + stats_check_required_arg(fcinfo, extarginfo, INHERITED_ARG); + inherited =3D 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(); + } + + /* + * First open the relation where we expect to find the statistics. This + * is similar to relation and attribute statistics, so as ACL checks are + * done before any locks are taken, even before any attempts related to + * the extended stats object. + */ + relid =3D RangeVarGetRelidExtended(makeRangeVar(relnspname, relname, -1), + ShareUpdateExclusiveLock, 0, + RangeVarCallbackForStats, &locked_table); + + /* Now check if the namespace of the stats object exists. */ + nspoid =3D get_namespace_oid(nspname, true); + if (nspoid =3D=3D InvalidOid) + { + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find schema \"%s\"", nspname)); + PG_RETURN_VOID(); + } + + pg_stext =3D table_open(StatisticExtRelationId, RowExclusiveLock); + tup =3D get_pg_statistic_ext(pg_stext, nspoid, stxname); + + if (!HeapTupleIsValid(tup)) + { + table_close(pg_stext, RowExclusiveLock); + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find extended statistics object \"%s\".\"%s\"", + nspname, stxname)); + PG_RETURN_VOID(); + } + + stxform =3D (Form_pg_statistic_ext) GETSTRUCT(tup); + + /* + * This should be consistent, based on the lock taken on the table + * when we started. + */ + if (stxform->stxrelid !=3D relid) + elog(ERROR, "cache lookup failed for extended stats %u: found relation %= u but expected %u", + stxform->oid, stxform->stxrelid, relid); + + 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/meson.build b/src/backend/statistics/me= son.build index 5c89f869812c..9a7bf55e3014 100644 --- a/src/backend/statistics/meson.build +++ b/src/backend/statistics/meson.build @@ -4,6 +4,7 @@ backend_sources +=3D files( 'attribute_stats.c', 'dependencies.c', 'extended_stats.c', + 'extended_stats_funcs.c', 'mcv.c', 'mvdistinct.c', 'relation_stats.c', diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/= expected/stats_import.out index 98ce7dc28410..f859a3a6af08 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -1084,11 +1084,17 @@ 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 =3D 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 =3D false); CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 =3D 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 +1348,146 @@ AND attname =3D 'i'; (1 row) =20 DROP TABLE stats_temp; +-- Tests for pg_clear_extended_stats(). +-- Invalid argument values. +SELECT pg_clear_extended_stats(schemaname =3D> NULL, + relname =3D> 'rel_foo', + statistics_schemaname =3D> 'schema_foo', + statistics_name =3D> 'stat_bar', + inherited =3D> false); +ERROR: argument "schemaname" must not be null +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_foo', + relname =3D> NULL, + statistics_schemaname =3D> 'schema_foo', + statistics_name =3D> 'stat_bar', + inherited =3D> false); +ERROR: argument "relname" must not be null +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_foo', + relname =3D> 'rel_foo', + statistics_schemaname =3D> NULL, + statistics_name =3D> 'stat_bar', + inherited =3D> false); +ERROR: argument "statistics_schemaname" must not be null +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_foo', + relname =3D> 'rel_foo', + statistics_schemaname =3D> 'schema_foo', + statistics_name =3D> NULL, + inherited =3D> false); +ERROR: argument "statistics_name" must not be null +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_foo', + relname =3D> 'rel_foo', + statistics_schemaname =3D> 'schema_foo', + statistics_name =3D> 'stat_bar', + inherited =3D> NULL); +ERROR: argument "inherited" must not be null +-- Missing objects +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_not_exist', + relname =3D> 'test', + statistics_schemaname =3D> 'schema_not_exist', + statistics_name =3D> 'test_stat', + inherited =3D> false); +ERROR: schema "schema_not_exist" does not exist +SELECT pg_clear_extended_stats(schemaname =3D> 'stats_import', + relname =3D> 'table_not_exist', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'test_stat', + inherited =3D> false); +ERROR: relation "stats_import.table_not_exist" does not exist +SELECT pg_clear_extended_stats(schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'schema_not_exist', + statistics_name =3D> 'test_stat', + inherited =3D> false); +WARNING: could not find schema "schema_not_exist" + pg_clear_extended_stats=20 +------------------------- +=20 +(1 row) + +SELECT pg_clear_extended_stats(schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'ext_stats_not_exist', + inherited =3D> false); +WARNING: could not find extended statistics object "stats_import"."ext_st= ats_not_exist" + pg_clear_extended_stats=20 +------------------------- +=20 +(1 row) + +-- Check that records are removed after a valid clear call. +SELECT COUNT(*) FROM pg_stats_ext AS e + WHERE e.statistics_schemaname =3D 'stats_import' AND + e.statistics_name =3D 'test_stat' AND e.inherited =3D false; + count=20 +------- + 1 +(1 row) + +SELECT COUNT(*) FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname =3D 'stats_import' AND + e.statistics_name =3D 'test_stat' AND e.inherited =3D false; + count=20 +------- + 2 +(1 row) + +SELECT pg_catalog.pg_clear_extended_stats( + schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'test_stat', + inherited =3D> false); + pg_clear_extended_stats=20 +------------------------- +=20 +(1 row) + +SELECT COUNT(*) FROM pg_stats_ext AS e + WHERE e.statistics_schemaname =3D 'stats_import' AND + e.statistics_name =3D 'test_stat' AND e.inherited =3D false; + count=20 +------- + 0 +(1 row) + +SELECT COUNT(*) FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname =3D 'stats_import' AND + e.statistics_name =3D 'test_stat' AND e.inherited =3D false; + count=20 +------- + 0 +(1 row) + +-- Check that MAINTAIN is required when clearing statistics. +CREATE ROLE regress_test_extstat_clear; +GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'test_stat', + inherited =3D> false); +ERROR: permission denied for table test +RESET ROLE; +GRANT MAINTAIN ON stats_import.test TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'test_stat', + inherited =3D> false); + pg_clear_extended_stats=20 +------------------------- +=20 +(1 row) + +RESET ROLE; +REVOKE MAINTAIN ON stats_import.test FROM regress_test_extstat_clear; +REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_clear; +DROP ROLE regress_test_extstat_clear; 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/s= tats_import.sql index d140733a7502..e866cdea1b72 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -766,6 +766,10 @@ SELECT 4, 'four', NULL, int4range(0,100), NULL; =20 CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 =3D 1)); =20 +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; =20 @@ -774,6 +778,10 @@ CREATE TABLE stats_import.test_clone ( LIKE stats_impo= rt.test ) =20 CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 =3D 1)); =20 +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 +978,98 @@ AND tablename =3D 'stats_temp' AND inherited =3D false AND attname =3D 'i'; DROP TABLE stats_temp; + +-- Tests for pg_clear_extended_stats(). +-- Invalid argument values. +SELECT pg_clear_extended_stats(schemaname =3D> NULL, + relname =3D> 'rel_foo', + statistics_schemaname =3D> 'schema_foo', + statistics_name =3D> 'stat_bar', + inherited =3D> false); +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_foo', + relname =3D> NULL, + statistics_schemaname =3D> 'schema_foo', + statistics_name =3D> 'stat_bar', + inherited =3D> false); +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_foo', + relname =3D> 'rel_foo', + statistics_schemaname =3D> NULL, + statistics_name =3D> 'stat_bar', + inherited =3D> false); +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_foo', + relname =3D> 'rel_foo', + statistics_schemaname =3D> 'schema_foo', + statistics_name =3D> NULL, + inherited =3D> false); +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_foo', + relname =3D> 'rel_foo', + statistics_schemaname =3D> 'schema_foo', + statistics_name =3D> 'stat_bar', + inherited =3D> NULL); +-- Missing objects +SELECT pg_clear_extended_stats(schemaname =3D> 'schema_not_exist', + relname =3D> 'test', + statistics_schemaname =3D> 'schema_not_exist', + statistics_name =3D> 'test_stat', + inherited =3D> false); +SELECT pg_clear_extended_stats(schemaname =3D> 'stats_import', + relname =3D> 'table_not_exist', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'test_stat', + inherited =3D> false); +SELECT pg_clear_extended_stats(schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'schema_not_exist', + statistics_name =3D> 'test_stat', + inherited =3D> false); +SELECT pg_clear_extended_stats(schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'ext_stats_not_exist', + inherited =3D> false); + +-- Check that records are removed after a valid clear call. +SELECT COUNT(*) FROM pg_stats_ext AS e + WHERE e.statistics_schemaname =3D 'stats_import' AND + e.statistics_name =3D 'test_stat' AND e.inherited =3D false; +SELECT COUNT(*) FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname =3D 'stats_import' AND + e.statistics_name =3D 'test_stat' AND e.inherited =3D false; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'test_stat', + inherited =3D> false); +SELECT COUNT(*) FROM pg_stats_ext AS e + WHERE e.statistics_schemaname =3D 'stats_import' AND + e.statistics_name =3D 'test_stat' AND e.inherited =3D false; +SELECT COUNT(*) FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname =3D 'stats_import' AND + e.statistics_name =3D 'test_stat' AND e.inherited =3D false; + +-- Check that MAINTAIN is required when clearing statistics. +CREATE ROLE regress_test_extstat_clear; +GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'test_stat', + inherited =3D> false); +RESET ROLE; +GRANT MAINTAIN ON stats_import.test TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname =3D> 'stats_import', + relname =3D> 'test', + statistics_schemaname =3D> 'stats_import', + statistics_name =3D> 'test_stat', + inherited =3D> false); +RESET ROLE; +REVOKE MAINTAIN ON stats_import.test FROM regress_test_extstat_clear; +REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_clear; +DROP ROLE regress_test_extstat_clear; + DROP SCHEMA stats_import CASCADE; diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-adm= in.sgml index 2896cd9e4290..e7ea16f73b31 100644 --- a/doc/src/sgml/func/func-admin.sgml +++ b/doc/src/sgml/func/func-admin.sgml @@ -2165,6 +2165,35 @@ SELECT pg_restore_attribute_stats( + + + + + pg_clear_extended_stats + + pg_clear_extended_stats ( + schemaname name, + relname name, + statistics_schemaname name, + statistics_name name, + inherited boolean ) + void + + + Clears data of an extended statistics object, as though the object + was newly-created. The required arguments are + schemaname and relname to + specify the schema and table name of the relation whose statistics + are cleared, as well as statistics_schemaname + and statistics_name to specify the schema and + extended statistics name of the extended statistics object to cle= ar. + + + The caller must have the MAINTAIN privilege on + the table or be the owner of the database. + + + --=20 2.51.0 --VkrMVjgTRteebSDW-- --HatbJtLvka/z/tJN Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmlooKQACgkQnvQgOdby QH0YDQ/9HsuRycKSjA9BAw+87gawRm5xpIzUdITPPCcCFTlIV6Ijm2t1PBJO++a/ raic2o99TdAnlLpmo4yJzIkWM++ayDKG8H8u0lZ/avJAF86h6+tKsdLy+cB362XH 2FUau//bXLzpC0nFyOG4wTR3DCgyCDTW99exaltFTGXi2LKEGbk7p1/UqXUb54hA oJbs41s0VfMlVhVxD41xkoO69rJqhO0TB5vxXdw2omQ8XYSiFyrxjhzdBnkUmT5H 2FvBmTaJyKX01REjjRVxZSqhB66dfKPfKvvAGEuQapRx87jjRJMoGN3ojjxa4rQY Yuh8L2IeN381t5R1QxaNIxo1IumMGui9R4G4tWeTj8Z6mxqH1EVCQsSZcvs4y0J2 vpaznX2DdkwAhuP6b4eNCnXuSa4BCyxZFtOguAFA8E3DR9+RGrowDspMDDE7ebwr UXgVbxF//5VjT1fHYP4S+FMEaHuE+vEjQiE5lvtvf+hQnxEw5uG/oSYxTQIAZm4r sTL6HoxJz/A9C9lIOzcld4eYhiGeaMiklxaSRDpHk6T+EOyL9AHtviydhtfbxgzU oD6JOEMa2gj/aEuV/D+pLjV28ByPfd33VEVvcm7P5sv9DvTxbVQ3MH+K4d07Z1Z9 /Yi9iP0vWCDYX1DnTuJ2dZEqFKqfKoEXELp2yRvyMjbVtqE4RyY= =qvX+ -----END PGP SIGNATURE----- --HatbJtLvka/z/tJN--