Search Postgresql Archives

Re: tuple statistics update

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

2017-04-19 15:49 GMT+02:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
On 04/19/2017 12:28 AM, Tom DalPozzo wrote:
2017-04-18 21:42 GMT+02:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@aklaver.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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com>




--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux