Re: Statistics Import and Export

On Tue, Sep 17, 2024 at 5:03 PM Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
>>
>> 1. make sure these three functions: 'pg_set_relation_stats',
>> 'pg_restore_relation_stats','pg_clear_relation_stats' proisstrict to true.
>> because in
>> pg_class catalog, these three attributes (relpages, reltuples, relallvisible) is
>> marked as not null. updating it to null will violate these constraints.
>> tom also mention this at [
>
> Things have changed a bit since then, and the purpose of the functions has changed, so the considerations are now different. The function signature could change in the future as new pg_class stats are added, and it might not still be strict.
>

if you add more arguments to relation_statistics_update,
but the first 3 arguments (relpages, reltuples, relallvisible) still not null.
and, we are unlikely to add 3 or more (nullable=null) arguments?

we have code like:
if (!PG_ARGISNULL(RELPAGES_ARG))
{
values[ncols] = Int32GetDatum(relpages);
ncols++;
}
if (!PG_ARGISNULL(RELTUPLES_ARG))
{
replaces[ncols] = Anum_pg_class_reltuples;
values[ncols] = Float4GetDatum(reltuples);
}
if (!PG_ARGISNULL(RELALLVISIBLE_ARG))
{
values[ncols] = Int32GetDatum(relallvisible);
ncols++;
}
newtup = heap_modify_tuple_by_cols(ctup, tupdesc, ncols, replaces, nulls);

you just directly declared "bool nulls[3] = {false, false, false};"
if any of (RELPAGES_ARG, RELTUPLES_ARG, RELALLVISIBLE_ARG)
is null, should you set that null[position] to true?
otherwise, i am confused with the variable nulls.

Looking at other usage of heap_modify_tuple_by_cols, "ncols" cannot be
dynamic, it should be a fixed value?
The current implementation works, because the (bool[3] nulls) is
always false, never changed.
if nulls becomes {false, false, true} then "ncols" must be 3, cannot be 2.

>>
>> 8. lock_check_privileges function issue.
>> ------------------------------------------------
>> --asume there is a superuser jian
>> create role alice NOSUPERUSER LOGIN;
>> create role bob NOSUPERUSER LOGIN;
>> create role carol NOSUPERUSER LOGIN;
>> alter database test owner to alice
>> GRANT CONNECT, CREATE on database test to bob;
>> \c test bob
>> create schema one;
>> create table one.t(a int);
>> create table one.t1(a int);
>> set session AUTHORIZATION; --switch to superuser.
>> alter table one.t1 owner to carol;
>> \c test alice
>> --now current database owner alice cannot do ANYTHING WITH table one.t1,
>> like ANALYZE, SELECT, INSERT, MAINTAIN etc.
>
>
> Interesting.
>

database owners do not necessarily have schema USAGE privilege.
-------------<<<>>>------------------
create role alice NOSUPERUSER LOGIN;
create role bob NOSUPERUSER LOGIN;
create database test;
alter database test owner to alice;
GRANT CONNECT, CREATE on database test to bob;
\c test bob
create schema one;
create table one.t(a int);
\c test alice

analyze one.t;

with cte as (
select oid as the_t
from pg_class
where relname = any('{t}') and relnamespace = 'one'::regnamespace)
SELECT
pg_catalog.pg_set_relation_stats(
relation => the_t,
relpages => 17::integer,
reltuples => 400.0::real,
relallvisible => 4::integer)
from cte;

In the above case, alice cannot do "analyze one.t;",
but can do pg_set_relation_stats, which seems not ok?
-------------<<<>>>------------------

src/include/statistics/stats_utils.h
comment
* Portions Copyright (c) 1994, Regents of the University of California
*
* src/include/statistics/statistics.h

should be "src/include/statistics/stats_utils.h"

comment src/backend/statistics/stats_utils.c
* IDENTIFICATION
* src/backend/statistics/stats_privs.c
should be
* IDENTIFICATION
* src/backend/statistics/stats_utils.c