Обсуждение: counting transactions

Hi

I have a table  and an sql file which has a bunch of inserts

I need to count the transactions that happen


Since the autocommit is ON , aside other internals, I should have roughly the same number of transactions as inserts

I was looking at  "xact_commit" column from the "pg_stat_database"


select * from pg_stat_database where datname='alonedb';
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (1,'SdnVwhNC',
'cjisHsjK','iuAVZbIU', 'dGm', 'lsu', 'yZn'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (2,'qXxtnlEi',
'cPtDBHFR','CvNWKYbg', 'eDt', 'gpY', 'wtP'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (3,'XJPRnHhR',
'ZLZQXbyk','dylerhdb', 'aLp', 'yAD', 'VCP'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (4,'AnhPoyFI',
'VzMBtdAk','KortOCdo', 'ZSH', 'rME', 'yOH'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (5,'iKSJEcan',
'GtuSFsfQ','alHxFYXr', 'DZN', 'RVA', 'zCP'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (6,'GiwxKOxF',
'kESBUusk','soKzMiDP', 'FYq', 'aHp', 'PHU'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (7,'piwfYySd',
'WrmjKokB','ryndcZjb', 'mgB', 'oXg', 'caZ'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (8,'yfBzBGLu',
'NlASbtWF','NxxjtVVg', 'JuD', 'fNg', 'KUP'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (9,'wMnntvRV',
'bOrsXviK','wETGZIpM', 'Rfd', 'KiZ', 'NDV'); 
insert into person(person_id,first_name, middle_name,last_name, title, prefix, suffix) values (10,'ejYyXsnT',
'CbXKywbR','ACJKilmi', 'uuc', 'klR', 'kcQ'); 
select * from pg_stat_database where datname='alonedb';


alonedb=# select * from pg_stat_database where datname='alonedb';
 datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched |
tup_inserted| tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_r 
ead_time | blk_write_time |          stats_reset

-------+---------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+------
---------+----------------+-------------------------------
 16386 | alonedb |           1 |      101205 |            17 |      3324 |   955026 |       966533 |      249624 |
100138 |          34 |       50000 |         0 |          0 |          0 |         0 |       
       0 |              0 | 2015-08-28 16:46:45.332615-05

and after

alonedb=# select * from pg_stat_database where datname='alonedb';
 datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched |
tup_inserted| tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_r 
ead_time | blk_write_time |          stats_reset

-------+---------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+------
---------+----------------+-------------------------------
 16386 | alonedb |           1 |      101205 |            17 |      3324 |   955026 |       966533 |      249624 |
100138 |          34 |       50000 |         0 |          0 |          0 |         0 |       
       0 |              0 | 2015-08-28 16:46:45.332615-05
(1 row)


But I did another variant

1 - select * from pg_stat_database where datname='alonedb';
 datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched |
tup_inserted| tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_r 
ead_time | blk_write_time |          stats_reset

-------+---------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+------
---------+----------------+-------------------------------
 16386 | alonedb |           1 |      101296 |            27 |      3509 |   983714 |      1009484 |      264308 |
100168 |          38 |       50000 |         0 |          0 |          0 |         0 |       
       0 |              0 | 2015-08-28 16:46:45.332615-05


2 - disconnect
3 - reconnect
4 - run the inserts
5 - disconnect
6 - reconnect

7 - select * from pg_stat_database where datname='alonedb';
 datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched |
tup_inserted| tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | blk_r 
ead_time | blk_write_time |          stats_reset

-------+---------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+------
---------+----------------+-------------------------------
 16386 | alonedb |           1 |      101309 |            27 |      3512 |   985576 |      1010638 |      265262 |
100178 |          38 |       50000 |         0 |          0 |          0 |         0 |       
       0 |              0 | 2015-08-28 16:46:45.332615-05


I was expecting both situations to yield similar results


So in my mind several questions
1 - why the difference ?
2 - is there any way to really count the transactions ? In Ingres for example I can look in logdump output or in imadb

Thanks
-- Armand

Armand Pirvu (gmail) <armand.pirvu@gmail.com> wrote:

> is there any way to really count the transactions ? In Ingres for
> example I can look in logdump output or in imadb

If you only care about transactions that modify the database, you
could look at the output of the pg_controldata utility and pick off
the NextXID value.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Perfect Kevin

Many thanks.
On the same token anyway I can count the number of connects/disconnects for a specific database ?



Thanks
Armand

On Sep 8, 2015, at 7:56 AM, Kevin Grittner <kgrittn@ymail.com> wrote:

> Armand Pirvu (gmail) <armand.pirvu@gmail.com> wrote:
>
>> is there any way to really count the transactions ? In Ingres for
>> example I can look in logdump output or in imadb
>
> If you only care about transactions that modify the database, you
> could look at the output of the pg_controldata utility and pick off
> the NextXID value.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company