Обсуждение: Check what has been done for a uncommitted prepared transaction

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, 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 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