Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZXxr3-0002aD-Ss for pgsql-admin@arkaria.postgresql.org; Fri, 04 Sep 2015 20:45:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZXxr3-0001At-AV for pgsql-admin@arkaria.postgresql.org; Fri, 04 Sep 2015 20:45:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZXxr2-0001Af-KF for pgsql-admin@postgresql.org; Fri, 04 Sep 2015 20:45:00 +0000 Received: from mail-qk0-x230.google.com ([2607:f8b0:400d:c09::230]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZXxqv-0006c9-Ve for pgsql-admin@postgresql.org; Fri, 04 Sep 2015 20:44:59 +0000 Received: by qkcf65 with SMTP id f65so13602408qkc.3 for ; Fri, 04 Sep 2015 13:44:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:content-type:content-transfer-encoding:subject:date:message-id :to:mime-version; bh=jUrGAZV7X6dmVk+orZMedYubvHvc8UGM2Ts5jf1PzKM=; b=n+elcxWNI2j2ZL2Gb355PMlKAZTpk8xUI5xZweGzY4A6NpxFhYL2WAf7YDhfgGqdfr tkCsH/unkNNOPPwQaHADhDL9C57gBuro1fzrk0rxUiVY+iXiPp7Aew5vQ1mwBSXjBvao C3bOMRBzckY/ahE3R58TGA6GFxpSv7Afn6OadR5MnzMZuWL2mJCvBe+SRIVhN+Lih2CX TI2v5FNaBm1tqV/0NepTnbDmOH/9YpKP3MmeMcTKOvVEVeIN9nCtZYlma88AV0TrojAt ib5RdvVB7fdf/olbCKjmQz4n6VtMatuMPsZIeW7cgdEdfG8orScxuSwDKXgLt+AmNbOE SzBg== X-Received: by 10.55.201.141 with SMTP id m13mr8218912qkl.68.1441399492984; Fri, 04 Sep 2015 13:44:52 -0700 (PDT) Received: from [192.168.5.107] (99-125-129-226.lightspeed.mssnks.sbcglobal.net. [99.125.129.226]) by smtp.gmail.com with ESMTPSA id t69sm2027109qki.11.2015.09.04.13.44.52 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 04 Sep 2015 13:44:52 -0700 (PDT) From: "Armand Pirvu (gmail)" Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Subject: counting transactions Date: Fri, 4 Sep 2015 15:44:42 -0500 Message-Id: <60217767-53CA-49C0-9A80-C1EAB4E24D5F@gmail.com> To: pgsql-admin@postgresql.org Mime-Version: 1.0 (Apple Message framework v1085) X-Mailer: Apple Mail (2.1085) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org 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 t= he 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=3D'alonedb'; insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (1,'SdnVwhNC', 'cjisHsjK', 'iuAVZbIU', 'dGm', 'lsu', 'yZ= n'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (2,'qXxtnlEi', 'cPtDBHFR', 'CvNWKYbg', 'eDt', 'gpY', 'wt= P'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (3,'XJPRnHhR', 'ZLZQXbyk', 'dylerhdb', 'aLp', 'yAD', 'VC= P'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (4,'AnhPoyFI', 'VzMBtdAk', 'KortOCdo', 'ZSH', 'rME', 'yO= H'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (5,'iKSJEcan', 'GtuSFsfQ', 'alHxFYXr', 'DZN', 'RVA', 'zC= P'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (6,'GiwxKOxF', 'kESBUusk', 'soKzMiDP', 'FYq', 'aHp', 'PH= U'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (7,'piwfYySd', 'WrmjKokB', 'ryndcZjb', 'mgB', 'oXg', 'ca= Z'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (8,'yfBzBGLu', 'NlASbtWF', 'NxxjtVVg', 'JuD', 'fNg', 'KU= P'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (9,'wMnntvRV', 'bOrsXviK', 'wETGZIpM', 'Rfd', 'KiZ', 'ND= V'); insert into person(person_id,first_name, middle_name,last_name, title, pref= ix, suffix) values (10,'ejYyXsnT', 'CbXKywbR', 'ACJKilmi', 'uuc', 'klR', 'k= cQ'); select * from pg_stat_database where datname=3D'alonedb'; alonedb=3D# select * from pg_stat_database where datname=3D'alonedb'; datid | datname | numbackends | xact_commit | xact_rollback | blks_read | = blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_de= leted | conflicts | temp_files | temp_bytes | deadlocks | blk_r ead_time | blk_write_time | stats_reset=20=20=20=20=20=20=20=20=20= =20 -------+---------+-------------+-------------+---------------+-----------+-= ---------+--------------+-------------+--------------+-------------+-------= ------+-----------+------------+------------+-----------+------ ---------+----------------+------------------------------- 16386 | alonedb | 1 | 101205 | 17 | 3324 | = 955026 | 966533 | 249624 | 100138 | 34 | = 50000 | 0 | 0 | 0 | 0 |=20=20=20=20=20=20 0 | 0 | 2015-08-28 16:46:45.332615-05 and after alonedb=3D# select * from pg_stat_database where datname=3D'alonedb'; datid | datname | numbackends | xact_commit | xact_rollback | blks_read | = blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_de= leted | conflicts | temp_files | temp_bytes | deadlocks | blk_r ead_time | blk_write_time | stats_reset=20=20=20=20=20=20=20=20=20= =20 -------+---------+-------------+-------------+---------------+-----------+-= ---------+--------------+-------------+--------------+-------------+-------= ------+-----------+------------+------------+-----------+------ ---------+----------------+------------------------------- 16386 | alonedb | 1 | 101205 | 17 | 3324 | = 955026 | 966533 | 249624 | 100138 | 34 | = 50000 | 0 | 0 | 0 | 0 |=20=20=20=20=20=20 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=3D'alonedb'; datid | datname | numbackends | xact_commit | xact_rollback | blks_read | = blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_de= leted | conflicts | temp_files | temp_bytes | deadlocks | blk_r ead_time | blk_write_time | stats_reset=20=20=20=20=20=20=20=20=20= =20 -------+---------+-------------+-------------+---------------+-----------+-= ---------+--------------+-------------+--------------+-------------+-------= ------+-----------+------------+------------+-----------+------ ---------+----------------+------------------------------- 16386 | alonedb | 1 | 101296 | 27 | 3509 | = 983714 | 1009484 | 264308 | 100168 | 38 | = 50000 | 0 | 0 | 0 | 0 |=20=20=20=20=20=20 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=3D'alonedb'; datid | datname | numbackends | xact_commit | xact_rollback | blks_read | = blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_de= leted | conflicts | temp_files | temp_bytes | deadlocks | blk_r ead_time | blk_write_time | stats_reset=20=20=20=20=20=20=20=20=20= =20 -------+---------+-------------+-------------+---------------+-----------+-= ---------+--------------+-------------+--------------+-------------+-------= ------+-----------+------------+------------+-----------+------ ---------+----------------+------------------------------- 16386 | alonedb | 1 | 101309 | 27 | 3512 | = 985576 | 1010638 | 265262 | 100178 | 38 | = 50000 | 0 | 0 | 0 | 0 |=20=20=20=20=20=20 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 examp= le I can look in logdump output or in imadb Thanks -- Armand --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin