pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: Armand Pirvu (gmail) <armand.pirvu@gmail.com>
To: pgsql-admin@postgresql.org
Subject: counting transactions
Date: Fri, 4 Sep 2015 15:44:42 -0500
Message-ID: <60217767-53CA-49C0-9A80-C1EAB4E24D5F@gmail.com> (raw )
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
view thread (5+ messages) latest in thread
Message-ID: <60217767-53CA-49C0-9A80-C1EAB4E24D5F@gmail.com>
Permalink: ../60217767-53CA-49C0-9A80-C1EAB4E24D5F@gmail.com/
Also on: postgresql.org/message-id/60217767-53CA-49C0-9A80-C1EAB4E24D5F@gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: armand.pirvu@gmail.com
Subject: Re: counting transactions
In-Reply-To: <60217767-53CA-49C0-9A80-C1EAB4E24D5F@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox