Thread: Check what has been done for a uncommitted prepared transaction
Hi:
I want to know what happens been done for an uncommitted prepared
transaction with pg_waldump, however I can't find it.
demo=# begin;
BEGIN
demo=*# select txid_current();
txid_current
--------------
608
(1 row)
demo=*# prepare transaction 's';
PREPARE TRANSACTION
demo=# insert into mm select generate_series(1, 1000);
INSERT 0 1000
demo=#
BEGIN
demo=*# select txid_current();
txid_current
--------------
608
(1 row)
demo=*# prepare transaction 's';
PREPARE TRANSACTION
demo=# insert into mm select generate_series(1, 1000);
INSERT 0 1000
demo=#
===
pg_wal> ~/postgres/bin/pg_waldump 00000001000000000000008F | grep 'tx: 608'
rmgr: Transaction len (rec/tot): 138/ 138, tx: 608, lsn: 0/8F68C020, prev 0/8F68BFD0, desc: PREPARE gid s: 2020-05-14 15:00:33.212997 CST
rmgr: Transaction len (rec/tot): 138/ 138, tx: 608, lsn: 0/8F68C020, prev 0/8F68BFD0, desc: PREPARE gid s: 2020-05-14 15:00:33.212997 CST
I can get the log for "prepared command" only, but nothing was found for the insert
statement. what should I do?
My version is 9.4.
Thanks
On Thu, May 14, 2020 at 3:38 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
I can get the log for "prepared command" only, but nothing was found for the insertstatement. what should I do?My version is 9.4.
Sorry, my production version is 9.4 and my demo above is v12. I tried in 9.4, I still have
troubles to get the logs.
Thanks
On Thu, May 14, 2020 at 03:38:24PM +0800, Andy Fan wrote: > I want to know what happens been done for an uncommitted prepared > transaction with pg_waldump, however I can't find it. > > demo=*# prepare transaction 's'; > PREPARE TRANSACTION > demo=# insert into mm select generate_series(1, 1000); > INSERT 0 1000 > > I can get the log for "prepared command" only, but nothing was found for > the insert statement. what should I do? Because in your previous sequence you inserted the data after preparing the transaction and they are part of a completely different transaction, no? -- Michael
Attachment
On Thu, May 14, 2020 at 4:05 PM Michael Paquier <michael@paquier.xyz> wrote:
On Thu, May 14, 2020 at 03:38:24PM +0800, Andy Fan wrote:
> I want to know what happens been done for an uncommitted prepared
> transaction with pg_waldump, however I can't find it.
>
> demo=*# prepare transaction 's';
> PREPARE TRANSACTION
> demo=# insert into mm select generate_series(1, 1000);
> INSERT 0 1000
>
> I can get the log for "prepared command" only, but nothing was found for
> the insert statement. what should I do?
Because in your previous sequence you inserted the data after
preparing the transaction and they are part of a completely different
transaction, no?
Thanks, actually I don't know how to use prepared transaction and how it works.
I care about this because there is a long prepared transaction exists in our customer,
and we want to know what this transaction has done（like any data it changed).
All the things I know is the data comes from pg_prepared_xact, but it doesn't help a lot.
Best Regards
Andy Fan
On Thu, 2020-05-14 at 16:26 +0800, Andy Fan wrote: > Thanks, actually I don't know how to use prepared transaction and how it works. > I care about this because there is a long prepared transaction exists in our customer, > and we want to know what this transaction has done（like any data it changed). > All the things I know is the data comes from pg_prepared_xact, but it doesn't help a lot. Idf you have the transaction ID from "pg_prepared_xact", you could check what locks are held: SELECT * FROM pg_locks WHERE transactionid = ...; Than might give you a clue. Using prepared transactions without a transaction manager that keeps track of them and cleans up if necessary is dangerous. Yours, Laurenz Albe -- +43-670-6056265 Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: https://www.cybertec-postgresql.com
On Thu, May 14, 2020 at 9:33 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2020-05-14 at 16:26 +0800, Andy Fan wrote:
> Thanks, actually I don't know how to use prepared transaction and how it works.
> I care about this because there is a long prepared transaction exists in our customer,
> and we want to know what this transaction has done（like any data it changed).
> All the things I know is the data comes from pg_prepared_xact, but it doesn't help a lot.
Idf you have the transaction ID from "pg_prepared_xact", you could check
what locks are held:
SELECT * FROM pg_locks WHERE transactionid = ...;
Than might give you a clue.
Thanks a lot. this transaction only lock a transactionid lock, so I assume there is nothing
is done in this transaction.
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------+---------+----------
transactionid | | | | | | 1181845808 | | | | -1/1181845808 | | ExclusiveLock | t | f
(1 row)
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------+---------+----------
transactionid | | | | | | 1181845808 | | | | -1/1181845808 | | ExclusiveLock | t | f
(1 row)
Best Regards
Andy Fan
On Fri, 2020-05-15 at 08:09 +0800, Andy Fan wrote: > On Thu, May 14, 2020 at 9:33 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > On Thu, 2020-05-14 at 16:26 +0800, Andy Fan wrote: > > > Thanks, actually I don't know how to use prepared transaction and how it works. > > > I care about this because there is a long prepared transaction exists in our customer, > > > and we want to know what this transaction has done（like any data it changed). > > > All the things I know is the data comes from pg_prepared_xact, but it doesn't help a lot. > > > > Idf you have the transaction ID from "pg_prepared_xact", you could check > > what locks are held: > > > > SELECT * FROM pg_locks WHERE transactionid = ...; > > > > Than might give you a clue. > > > > Thanks a lot. this transaction only lock a transactionid lock, so I assume there is nothing > is done in this transaction. > > > locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction| pid | mode | granted | fastpath > ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------+---------+---------- > transactionid | | | | | | 1181845808 | | | | -1/1181845808 | | ExclusiveLock | t | f > (1 row) Looks like it, yes. Roll it back then. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com