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