CODEWITHSUNDEEP
sqlpostgresql
Shubham Chaudhary
Shubham Chaudhary

Reputation: 1472

How to get number of uncommitted write commands in transaction in PostgreSQL?

Here is what I want to do 

BEGIN;
INSERT INTO sample_table VALUES ('a','b')
INSERT INTO sample_table VALUES ('c','d')

How can I get the number of commands in the transaction before committing? Like the number of uncommitted commands in above example is 2

Upvotes: 0

Views: 635

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51519

with some limitation you can use cmin +1 formula. Of course you have to know all tables where you insert. Also in my demo I show how to do it with inserts only, updates and deletes will require more logic, including xmax (if possible at all), anyway, here the code to give it a try...

sample:

t=# create table ut1(i int);
CREATE TABLE
t=# create table ut2(i int);
CREATE TABLE
t=# insert into ut1 select generate_series(1,10);
INSERT 0 10
t=# insert into ut2 select generate_series(1,10);
INSERT 0 10

demo:

t=# begin;
BEGIN
t=# insert into ut1 select 1;
INSERT 0 1
t=# insert into ut2 select 1;
INSERT 0 1
t=# insert into ut2 select generate_series(1,2);
INSERT 0 2
t=#  insert into ut1 select generate_series(1,2);
INSERT 0 2
t=# select greatest(max(ut1.cmin::text::int),max(ut2.cmin::text::int)) from ut2 join ut1 on ut1.xmin = ut2.xmin and (ut1.xmin)::text::bigint = txid_current();
 greatest
----------
        3
(1 row)

t=# end;
COMMIT
t=# begin;
BEGIN
t=# insert into ut1 select generate_series(1,2);
INSERT 0 2
t=# insert into ut2 select generate_series(1,2);
INSERT 0 2
t=# select greatest(max(ut1.cmin::text::int),max(ut2.cmin::text::int)) from ut2 join ut1 on ut1.xmin = ut2.xmin and (ut1.xmin)::text::bigint = txid_current();
 greatest
----------
        1
(1 row)

t=# end;
COMMIT

Upvotes: 3

Related Questions