Re: [GENERAL] tuple statistics update

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

etc
> Regards
> Pupillo
>
>
>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com