Thread: ANALYZE, pg_class.xmin && pg_class.reltuples

ANALYZE, pg_class.xmin && pg_class.reltuples

From
Luca Ferrari
Date:
Hi all,
I've a doubt about running ANALYZE and inspecting pg_class.xmin and
pg_class.reltuples.
Let's create a simple table (note, I'm the only user on this instance):

testdb=> CREATE TABLE fizz AS SELECT v FROM generate_series( 1, 100 ) v;
SELECT 100
testdb=> SELECT relpages, reltuples, xmin, age( xmin ) FROM pg_class
WHERE relname = 'fizz';
-[ RECORD 1 ]---------
relpages  | 0
reltuples | 0
xmin      | 2359180435
age       | 1


Therefore transaction 435 created the table.
Now analyze the table:

testdb=> analyze fizz;
ANALYZE

testdb=> SELECT relpages, reltuples, xmin, age( xmin ) FROM pg_class
WHERE relname = 'fizz';
-[ RECORD 1 ]---------
relpages  | 1
reltuples | 100
xmin      | 2359180435
age       | 2


So the pg_class record has changed its content, and in fact we are now
2 transaction away the table creation, but the pg_class.xmin is the
same.
I would have expected that pg_class.xmin was updated as per
user-tables.Now, clearly ANALYZE hit the pg_statistic table and the
xmin "update" is there, but this would make me thing
pg_class.reltuples is a generated column based on some aggregation of
the latter pg_statistic, that apparently is not.
Am I missing something?

Thanks,
Luca



Re: ANALYZE, pg_class.xmin && pg_class.reltuples

From
Tom Lane
Date:
Luca Ferrari <fluca1978@gmail.com> writes:
> I've a doubt about running ANALYZE and inspecting pg_class.xmin and
> pg_class.reltuples.
> ...
> So the pg_class record has changed its content, and in fact we are now
> 2 transaction away the table creation, but the pg_class.xmin is the
> same.

ANALYZE (and VACUUM) update the table's pg_class row non-transactionally.
See the comments for vac_update_relstats():


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/vacuum.c;h=5c4bc15b441b36da99b0703500a3268086b40d9e;hb=HEAD#l1266

            regards, tom lane