Check what has been done for a uncommitted prepared transaction

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 insert
statement. 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

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)

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