Horton

Re: Statistics Import and Export

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Jeff Davis <pgsql@j-davis.com>, Nathan Bossart <nathandbossart@gmail.com>, Bruce Momjian <bruce@momjian.us>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Magnus Hagander <magnus@hagander.net>, Stephen Frost <sfrost@snowman.net>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Peter Smith <smithpb2250@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, alvherre@alvh.no-ip.org
Date: 2025-01-18T17:00:04Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Change pg_dump default for statistics export.

  2. pg_dump: Adjust reltuples from 0 to -1 for dumps of older versions.

  3. vacuumdb: Don't skip empty relations in --missing-stats-only mode.

  4. pg_dump: Fix query for gathering attribute stats on older versions.

  5. Prevent redeclaration of typedef TocEntry.

  6. Remove unused function parameters in pg_backup_archiver.c.

  7. pg_dump: Retrieve attribute statistics in batches.

  8. pg_dump: Reduce memory usage of dumps with statistics.

  9. Skip second WriteToc() call for custom-format dumps without data.

  10. Add relallfrozen to pg_dump statistics.

  11. Matview statistics depend on matview data.

  12. Add pg_dump --with-{schema|data|statistics} options.

  13. Stats: use schemaname/relname instead of regclass.

  14. CREATE INDEX: do update index stats if autovacuum=off.

  15. Don't convert to and from floats in pg_dump.

  16. CREATE INDEX: don't update table stats if autovacuum=off.

  17. Organize and deduplicate statistics import tests.

  18. Address stats export review comments.

  19. Address stats import review comments.

  20. Add relallfrozen to pg_class

  21. Fix pg_strtof() to not crash on NULL endptr.

  22. Use attnum to identify index columns in pg_restore_attribute_stats().

  23. pg_dump: prepare attribute stats query.

  24. Avoid unnecessary relation stats query in pg_dump.

  25. Remove redundant pg_set_*_stats() variants.

  26. Do not use in-place updates for statistics import.

  27. Fix confusion about data type of pg_class.relpages and relallvisible.

  28. Documentation fixups for dumping statistics.

  29. Trial fix for old cross-version upgrades.

  30. Transfer statistics during pg_upgrade.

  31. Lock table in ShareUpdateExclusive when importing index stats.

  32. Use in-place updates for pg_restore_relation_stats().

  33. Improve error message for replication of generated columns.

  34. pg_dump: Add dumpSchema and dumpData derivative flags.

  35. Disallow modifying statistics on system columns.

  36. Add missing CommandCounterIncrement() in stats import functions.

  37. Add functions pg_restore_relation_stats(), pg_restore_attribute_stats().

  38. Documentation fixup.

  39. Add functions pg_set_attribute_stats() and pg_clear_attribute_stats().

  40. Change pg_*_relation_stats() functions to return type to void.

  41. Disable autovacuum for tables in stats import tests.

  42. Allow pg_set_relation_stats() to set relpages to -1.

  43. Fixup for pg_set_relation_stats().

  44. Create functions pg_set_relation_stats, pg_clear_relation_stats.

  45. Add memory/disk usage for Window aggregate nodes in EXPLAIN.

  46. Improve performance of dumpSequenceData().

  47. Add INJECTION_POINT_CACHED() to run injection points directly from cache

  48. Improve performance of binary_upgrade_set_pg_class_oids().

  49. Improve assertion in mdwritev()

  50. CREATE INDEX: do not update stats during binary upgrade.

  51. Redefine pg_class.reltuples to be -1 before the first VACUUM or ANALYZE.

Attachments

On Fri, Jan 17, 2025 at 10:20 PM jian he <jian.universality@gmail.com> wrote:
>
> hi.
>
> SELECT * FROM pg_catalog.pg_restore_relation_stats(
>     'relation', 'public.tenk1_hundred'::regclass,
>     'version', '180000'::integer,
>     'relpages', '11'::integer,
>     'reltuples', '10000'::real,
>     'relallvisible', '0'::integer
> );
> dump and execute the above query generated a warning
> WARNING:  missing lock for relation "tenk1_hundred" (OID 18431,
> relkind i) @ TID (15,34)
>
This seems to be an existing issue.
For pg_restore_relation_stats, we don't have regress tests for index relation.
I am not sure the WARNING is ok.


I found out that the previous mail attached no-cfbot
(refactor_pg_dump_onlyoption.no-cfbot)
refactoring of statistics, data, schema is not fully correct.
This email attached no-cfbot,
I think it is tuitive and correct refactor of handling these three options.



typedef struct _dumpOptions, typedef struct _restoreOptions
we already have three bools (dumpSchema, dumpData,  dumpStatistics).
Why do we need three int (no_data, no_schema, no_statistics) fields
for these two structs?
since they represent the same information. (for example, no_data == 1,
means/imply dumpData is false)
(disclaimer, this part I didn't dig deeper).


doc/src/sgml/ref/pg_restore.sgml
     <varlistentry>
      <term><option>-X</option></term>
      <term><option>--statistics-only</option></term>
      <listitem>
       <para>
        Restore only the statistics, not schema (data definitions) or data.
       </para>
       <para>
        (Do not confuse this with the <option>--schema</option> option, which
        uses the word <quote>schema</quote> in a different meaning.)
       </para>
      </listitem>
     </varlistentry>
here, we don't need to mention
"(Do not confuse this with the <option>--schema</option> option, which"... part?



--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -108,6 +112,7 @@ main(int argc, char **argv)
         {"username", 1, NULL, 'U'},
         {"verbose", 0, NULL, 'v'},
         {"single-transaction", 0, NULL, '1'},
+        {"statistics-only", no_argument, NULL, 'P'},

Here it should be
+        {"statistics-only", no_argument, NULL, 'X'},
?



If we introduced REQ_STATS, then better checking all the REQ_DATA occurrences,
does REQ_STATS apply to there also?
for example in pg_backup_tar.c and  pg_backup_directory.c,
functions: WriteToc, function WriteDataChunks, RestoreArchive.



-----------------------------------------------
I tested locally, dump, restore, directory, custom format is not
working as intended, with v38.
I use the following to test it.
CONN2 is my local connect string.
BIN2 is a local bin directory.
varchar_tbl.dir is directory format dump full output, including data,
schema, statistics.
-----------------------------------------------
${CONN2} -c 'drop table varchar_tbl;'
$BIN2/pg_restore --dbname=src2 --list varchar_tbl.dir
#only schema
$BIN2/pg_restore --dbname=src2  --format=directory --no-statistics
--no-data varchar_tbl.dir
${CONN2} -c 'select attname=$$f1$$ as expect_zero_row from pg_stats
where tablename = $$varchar_tbl$$;'
${CONN2} -c 'select (reltuples < 0 and relpages = 0) as expect_true
from pg_class where relname = $$varchar_tbl$$;'
#only data
$BIN2/pg_restore --dbname=src2  --format=directory --no-statistics
--no-schema varchar_tbl.dir
${CONN2} -c 'select attname=$$f1$$ as expect_zero_row from pg_stats
where tablename = $$varchar_tbl$$;'
${CONN2} -c 'select (reltuples < 0 and relpages = 0) as expect_true
from pg_class where relname = $$varchar_tbl$$;'
#only statistics
$BIN2/pg_restore --dbname=src2 --format=directory --statistics-only
varchar_tbl.dir
${CONN2} -c 'select attname=$$f1$$ as expect_zero_row from pg_stats
where tablename = $$varchar_tbl$$;'
${CONN2} -c 'select reltuples > 0 and relpages > 0 as expect_true from
pg_class where relname = $$varchar_tbl$$;'