Re: tuple statistics update

Hi,

2017-04-19 15:49 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 04/19/2017 12:28 AM, Tom DalPozzo wrote:
>
>> 2017-04-18 21:42 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>>
>> On 04/17/2017 09:18 AM, Tom DalPozzo wrote:
>>
>> Hi, I'm using libpq to insert tuples in my table and keep looking
>> at
>> statistics through psql instead.
>> I noticed that sometimes n_tuple_ins is not updated even after 1
>> min
>> that my transaction committed.
>> My libpq connection is kept alive. If I close the connection
>> then the
>> stats get updated.
>> I know that stats are not instantaneous, but I thought that after
>> a
>> while that a transaction is committed it would be updated.
>>
>>
>> Any of this apply?:
>>
>> https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
>> <https://www.postgresql.org/docs/9.6/static/monitoring-stats.html>
>>
>> "Another important point is that when a server process is asked to
>> display any of these statistics, it first fetches the most recent
>> report emitted by the collector process and then continues to use
>> this snapshot for all statistical views and functions until the end
>> of its current transaction. So the statistics will show static
>> information as long as you continue the current transaction.
>> Similarly, information about the current queries of all sessions is
>> collected when any such information is first requested within a
>> transaction, and the same information will be displayed throughout
>> the transaction. This is a feature, not a bug, because it allows you
>> to perform several queries on the statistics and correlate the
>> results without worrying that the numbers are changing underneath
>> you. But if you want to see new results with each query, be sure to
>> do the queries outside any transaction block. Alternatively, you can
>> invoke pg_stat_clear_snapshot(), which will discard the current
>> transaction's statistics snapshot (if any). The next use of
>> statistical information will cause a new snapshot to be fetched."
>>
>> Regards
>> Pupillo
>>
>>
>> I read it, it seems to say that after N millisec that my transaction
>> ends, stat should be current. I also tried pg_stat_clear_snapshot()
>> with no success.
>>
>
> You have two sessions in play, one that is inserting rows, the other in
> psql looking at the stats. It is not clear to me which session you are
> referring to in the above. So maybe an outline of what you are doing.
> Something like:
>
> Session 1 Monitor stats table(?) using command(?)
>
> Session2 Insert rows. The INSERT query
>

I have a psql session open.
Now, through another task which uses libpq, I open a new connection and
send these commands via PQexec:
CREATE TABLE stato (ID BIGINT,DATI BYTEA);
CREATE INDEX stato_IDX ON stato (ID);
INSERT INTO stato VALUES (0,'\x6C72B55EA171DE63F229A37135CB5DE4A845FD9E');
INSERT INTO stato VALUES (1,'\x9822A5A113EE5FBBA03C6B58A139DD46D4476B8D');

As it's done, I send the following commands via psql session, waiting at
least 1 sec before each one:

ginopino=# select count(*) from stato;
count
-------
2
(1 row)

ginopino=# select relname,n_tup_ins,n_tup_upd from pg_stat_user_tables
where relname='stato';
relname | n_tup_ins | n_tup_upd
---------+-----------+-----------
stato | 0 | 0
(1 row)

Repeat just in case....
ginopino=# select count(*) from stato;
count
-------
2
(1 row)

ginopino=# select relname,n_tup_ins,n_tup_upd from pg_stat_user_tables
where relname='stato';
relname | n_tup_ins | n_tup_upd
---------+-----------+-----------
stato | 0 | 0
(1 row)

n_tup_ins still 0 while count is 2.

Now, I terminate my libpq task and then, from psql:
ginopino=# select relname,n_tup_ins,n_tup_upd from pg_stat_user_tables
where relname='stato';
relname | n_tup_ins | n_tup_upd
---------+-----------+-----------
stato | 2 | 0
(1 row)

Now n_tup_ins is 2.

Thanks
Pupillo

>
> etc
>
>> Regards
>> Pupillo
>>
>>
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>