[Postgres-xl-developers] Impossible to ALTER/DROP a table
Romain Lefrançois
romain.lefrancois at oneup.com
Tue Aug 1 11:55:16 PDT 2017
We did a rollback on the 4 prepared transactions that were on the data
nodes and we have been able to do the alter.
The owner of the transactions is the user attached to our web application
so it might be due to a crash that we experienced.
We will continue to monitor pg_prepared_xacts to see if this happens again.
Thank you a lot Pavan and Tomas.
On Tue, Aug 1, 2017 at 11:01 AM Romain Lefrançois <
romain.lefrancois at oneup.com> wrote:
> Thanks Tomas and Pavan. Here is the output of the two requests when the
> request is not running.
>
> Data node 01:
>
> postgres=# SELECT * FROM pg_prepared_xacts;
> transaction | gid |
> prepared | owner | database
>
> -------------+--------------------------------------------------+-------------------------------+-------+----------
> 2836157 | _$XC$2836157:coord01:F:2:0:272210106:-1806321976 |
> 2017-07-07 09:01:11.496329+00 | app | accounts
> (1 row)
>
> postgres=# SELECT relation::regclass, * FROM pg_locks;
> relation | locktype | database | relation | page | tuple |
> virtualxid | transactionid | classid | objid | objsubid |
> virtualtransaction | pid | mode | granted | fastpath
>
> ----------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
> pg_locks | relation | 12387 | 11676 | | |
> | | | | | 8/228379 | 17616
> | AccessShareLock | t | t
> | virtualxid | | | | | 8/228379
> | | | | | 8/228379 | 17616
> | ExclusiveLock | t | t
> 21251 | relation | 16390 | 21251 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21251 | relation | 16390 | 21251 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> | transactionid | | | | |
> | 2836157 | | | | -1/2836157 |
> | ExclusiveLock | t | f
> 16853 | relation | 16390 | 16853 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 20870 | relation | 16390 | 20870 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21253 | relation | 16390 | 21253 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21253 | relation | 16390 | 21253 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> 20902 | relation | 16390 | 20902 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 20902 | relation | 16390 | 20902 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> 17105 | relation | 16390 | 17105 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 17105 | relation | 16390 | 17105 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> 21256 | relation | 16390 | 21256 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21256 | relation | 16390 | 21256 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> 21255 | relation | 16390 | 21255 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21255 | relation | 16390 | 21255 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> 21254 | relation | 16390 | 21254 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21254 | relation | 16390 | 21254 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> 21172 | relation | 16390 | 21172 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21169 | relation | 16390 | 21169 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21252 | relation | 16390 | 21252 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21252 | relation | 16390 | 21252 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> 21171 | relation | 16390 | 21171 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21170 | relation | 16390 | 21170 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21168 | relation | 16390 | 21168 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21257 | relation | 16390 | 21257 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21257 | relation | 16390 | 21257 | | |
> | | | | | -1/2836157 |
> | RowExclusiveLock | t | f
> 21174 | relation | 16390 | 21174 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> 21173 | relation | 16390 | 21173 | | |
> | | | | | -1/2836157 |
> | AccessShareLock | t | f
> (30 rows)
>
> Data node 02:
>
> postgres=# SELECT * FROM pg_prepared_xacts;
> transaction | gid |
> prepared | owner | database
>
> -------------+--------------------------------------------------+-------------------------------+-------+----------
> 2836159 | _$XC$2836159:coord01:F:2:0:272210106:-1806321976 |
> 2017-07-07 09:01:11.570459+00 | app | domains
> 1518751 | _$XC$1518751:coord02:F:2:0:272210106:-1806321976 |
> 2017-06-27 14:46:08.973912+00 | app | accounts
> 2813738 | _$XC$2813738:coord02:F:2:0:272210106:-1806321976 |
> 2017-07-07 06:14:03.694512+00 | app | accounts
> (3 rows)
>
> postgres=# SELECT relation::regclass, * FROM pg_locks;
> relation | locktype | database | relation | page | tuple |
> virtualxid | transactionid | classid | objid | objsubid |
> virtualtransaction | pid | mode | granted | fastpath
>
> ----------+---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
> pg_locks | relation | 12387 | 11676 | | |
> | | | | | 15/77391 | 13146
> | AccessShareLock | t | t
> | virtualxid | | | | | 15/77391
> | | | | | 15/77391 | 13146
> | ExclusiveLock | t | t
> 21017 | relation | 16390 | 21017 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21047 | relation | 16390 | 21047 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 16814 | relation | 16390 | 16814 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21578 | relation | 16390 | 21578 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21243 | relation | 16390 | 21243 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 17168 | relation | 16390 | 17168 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21329 | relation | 16390 | 21329 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> | transactionid | | | | |
> | 2813738 | | | | -1/2813738 |
> | ExclusiveLock | t | f
> 21562 | relation | 16390 | 21562 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21538 | relation | 16390 | 21538 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 17918 | relation | 16390 | 17918 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21348 | relation | 16390 | 21348 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21347 | relation | 16390 | 21347 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21404 | relation | 16390 | 21404 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21447 | relation | 16390 | 21447 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21242 | relation | 16390 | 21242 | | |
> | | | | | -1/2813738 |
> | RowExclusiveLock | t | f
> 28011 | relation | 16390 | 28011 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21639 | relation | 16390 | 21639 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21329 | relation | 16390 | 21329 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21329 | relation | 16390 | 21329 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 21463 | relation | 16390 | 21463 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21241 | relation | 16390 | 21241 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21576 | relation | 16390 | 21576 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21243 | relation | 16390 | 21243 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21565 | relation | 16390 | 21565 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21641 | relation | 16390 | 21641 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21644 | relation | 16390 | 21644 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21664 | relation | 16390 | 21664 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21346 | relation | 16390 | 21346 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21011 | relation | 16390 | 21011 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20939 | relation | 16390 | 20939 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21457 | relation | 16390 | 21457 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21055 | relation | 16390 | 21055 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21567 | relation | 16390 | 21567 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21566 | relation | 16390 | 21566 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21642 | relation | 16390 | 21642 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28006 | relation | 16390 | 28006 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21575 | relation | 16390 | 21575 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21461 | relation | 16390 | 21461 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21238 | relation | 16390 | 21238 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21570 | relation | 16390 | 21570 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21247 | relation | 16390 | 21247 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21234 | relation | 16390 | 21234 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21246 | relation | 16390 | 21246 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21053 | relation | 16390 | 21053 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21079 | relation | 16390 | 21079 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21248 | relation | 16390 | 21248 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21327 | relation | 16390 | 21327 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21537 | relation | 16390 | 21537 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28008 | relation | 16390 | 28008 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 16846 | relation | 16390 | 16846 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 16846 | relation | 16390 | 16846 | | |
> | | | | | -1/2813738 |
> | RowExclusiveLock | t | f
> 21409 | relation | 16390 | 21409 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21352 | relation | 16390 | 21352 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 27847 | relation | 16389 | 27847 | | |
> | | | | | -1/2836159 |
> | RowExclusiveLock | t | f
> 21656 | relation | 16390 | 21656 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21572 | relation | 16390 | 21572 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21563 | relation | 16390 | 21563 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21237 | relation | 16390 | 21237 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21561 | relation | 16390 | 21561 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21248 | relation | 16390 | 21248 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 17953 | relation | 16390 | 17953 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21327 | relation | 16390 | 21327 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21327 | relation | 16390 | 21327 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 21247 | relation | 16390 | 21247 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 17662 | relation | 16390 | 17662 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21410 | relation | 16390 | 21410 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21246 | relation | 16390 | 21246 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21569 | relation | 16390 | 21569 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21331 | relation | 16390 | 21331 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21245 | relation | 16390 | 21245 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28000 | relation | 16390 | 28000 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20941 | relation | 16390 | 20941 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21579 | relation | 16390 | 21579 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20891 | relation | 16390 | 20891 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20983 | relation | 16390 | 20983 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 17105 | relation | 16390 | 17105 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21330 | relation | 16390 | 21330 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21330 | relation | 16390 | 21330 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 17883 | relation | 16390 | 17883 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21663 | relation | 16390 | 21663 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28007 | relation | 16390 | 28007 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28002 | relation | 16390 | 28002 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21240 | relation | 16390 | 21240 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21236 | relation | 16390 | 21236 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21230 | relation | 16390 | 21230 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21232 | relation | 16390 | 21232 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21460 | relation | 16390 | 21460 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21231 | relation | 16390 | 21231 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21349 | relation | 16390 | 21349 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21574 | relation | 16390 | 21574 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21402 | relation | 16390 | 21402 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21239 | relation | 16390 | 21239 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20977 | relation | 16390 | 20977 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 20977 | relation | 16390 | 20977 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 21249 | relation | 16390 | 21249 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 20943 | relation | 16390 | 20943 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20943 | relation | 16390 | 20943 | | |
> | | | | | -1/2813738 |
> | RowExclusiveLock | t | f
> 21654 | relation | 16390 | 21654 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21407 | relation | 16390 | 21407 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> | transactionid | | | | |
> | 2836159 | | | | -1/2836159 |
> | ExclusiveLock | t | f
> 21332 | relation | 16390 | 21332 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21332 | relation | 16390 | 21332 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 21328 | relation | 16390 | 21328 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21328 | relation | 16390 | 21328 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 21097 | relation | 16390 | 21097 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21560 | relation | 16390 | 21560 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 17105 | relation | 16390 | 17105 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 17105 | relation | 16390 | 17105 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 21655 | relation | 16390 | 21655 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21233 | relation | 16390 | 21233 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21330 | relation | 16390 | 21330 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21577 | relation | 16390 | 21577 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20923 | relation | 16390 | 20923 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> | transactionid | | | | |
> | 1518751 | | | | -1/1518751 |
> | ExclusiveLock | t | f
> 17431 | relation | 16390 | 17431 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21245 | relation | 16390 | 21245 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 17734 | relation | 16390 | 17734 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21331 | relation | 16390 | 21331 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21331 | relation | 16390 | 21331 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 17638 | relation | 16390 | 17638 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21350 | relation | 16390 | 21350 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21522 | relation | 16390 | 21522 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21643 | relation | 16390 | 21643 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21640 | relation | 16390 | 21640 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20997 | relation | 16390 | 20997 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21328 | relation | 16390 | 21328 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21332 | relation | 16390 | 21332 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21249 | relation | 16390 | 21249 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20977 | relation | 16390 | 20977 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21403 | relation | 16390 | 21403 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21448 | relation | 16390 | 21448 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20945 | relation | 16390 | 20945 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 16397 | relation | 16390 | 16397 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 17458 | relation | 16390 | 17458 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21326 | relation | 16390 | 21326 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 16699 | relation | 16390 | 16699 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21406 | relation | 16390 | 21406 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28009 | relation | 16390 | 28009 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21353 | relation | 16390 | 21353 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21573 | relation | 16390 | 21573 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28010 | relation | 16390 | 28010 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 17315 | relation | 16390 | 17315 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21523 | relation | 16390 | 21523 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21244 | relation | 16390 | 21244 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21043 | relation | 16390 | 21043 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21559 | relation | 16390 | 21559 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 17708 | relation | 16390 | 17708 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21558 | relation | 16390 | 21558 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21539 | relation | 16390 | 21539 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21218 | relation | 16390 | 21218 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21458 | relation | 16390 | 21458 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21446 | relation | 16390 | 21446 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 16853 | relation | 16390 | 16853 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21568 | relation | 16390 | 21568 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21073 | relation | 16390 | 21073 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21408 | relation | 16390 | 21408 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28003 | relation | 16390 | 28003 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 28004 | relation | 16390 | 28004 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 20945 | relation | 16390 | 20945 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21326 | relation | 16390 | 21326 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 21326 | relation | 16390 | 21326 | | |
> | | | | | -1/1518751 |
> | RowExclusiveLock | t | f
> 21235 | relation | 16390 | 21235 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21085 | relation | 16390 | 21085 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21405 | relation | 16390 | 21405 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21351 | relation | 16390 | 21351 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21459 | relation | 16390 | 21459 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 16853 | relation | 16390 | 16853 | | |
> | | | | | -1/1518751 |
> | AccessShareLock | t | f
> 28005 | relation | 16390 | 28005 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 16807 | relation | 16390 | 16807 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 27947 | relation | 16390 | 27947 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21571 | relation | 16390 | 21571 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21244 | relation | 16390 | 21244 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21411 | relation | 16390 | 21411 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21412 | relation | 16390 | 21412 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> 21564 | relation | 16390 | 21564 | | |
> | | | | | -1/2813738 |
> | AccessShareLock | t | f
> (178 rows)
>
>
> On Tue, Aug 1, 2017 at 5:29 AM Tomas Vondra <tomas.vondra at 2ndquadrant.com>
> wrote:
>
>> Hi,
>>
>> On 08/01/2017 01:31 AM, Romain Lefrançois wrote:
>> > @Tomas
>> >
>> > Indeed there is some small differences that happened short after our
>> > migration (the distribution, etc.)
>> > The "price_mode" column is missing because it's the one we are
>> > attempting to add.
>> >
>> > Regarding a potential lock: below the locks on the coordinator and the
>> > two data nodes.
>> > Two things I noticed:
>> > - On the first data node the state of the request is idle in transaction
>> > but active on the coordinator and data node 2 . Is that normal ?
>> > - On the second data node there is 187 rows which is 3 times the number
>> > of row of the first one. What could cause to have so many?
>> >
>> > We also noticed that it's not the only table that has this issue and
>> > that even doing a DROP of an index on these table is not working. The
>> > state of the request or Drop index is the same : coord = active, data-01
>> > = idle in transaction, data-02 = active.
>> >
>>
>> IMHO the number of locks itself is not a sign of anything. But these two
>> lines from the second datanode are interesting.
>>
>> locktype | database | relation | virtualxid | virtualtransaction
>> | pid | mode | granted | datname | pid | waiting |
>> state | query
>>
>> relation | 16390 | 17734 | | -1/2813738
>> | | AccessShareLock | t | | | |
>> |
>>
>> relation | 16390 | 17734 | | 37/36789
>> | 27401 | AccessExclusiveLock | f | accounts | 27401 | t |
>> active | ALTER TABLE accounts.public.outpayment_expense_line ADD COLUMN
>> price_mode character varying(64) DEFAULT 'withTaxes' NOT NULL;
>>
>> This strongly suggests there's a prepared transaction holding a lock on
>> a relation wiwth OID 17734 (which I suppose is outpayment_expense_line).
>> The fact that this is a prepared transaction is hinted by empty PID ad
>> virtual XID being -1/2813738.
>>
>> And then there are is the ALTER TABLE ... ADD COLUMN transaction,
>> attempting to do the same thing. But it gets blocked, as it has to
>> acquire an exclusive lock on the same table.
>>
>> I'm not sure why/how this happened, but it's the root cause of the
>> infinite wait - the AccessExclusiveLock lock can't be acquired until the
>> prepared transaction releases the AccessShareLock.
>>
>> Querying pg_prepared_xacts (per Pavan's recommendation) should tell you
>> more. The prepared transaction however holds many other locks, which is
>> probably why you can't ALTER other tables too.
>>
>> I'm not sure why is the prepared transaction there, and the right fix
>> depends on understanding that - you can either commit or rollback the
>> prepared transaction. Maybe you suffered a cluster failure recently, and
>> the transaction got committed only on some of the datanodes?
>>
>>
>> regards
>>
>> --
>> Tomas Vondra http://www.2ndQuadrant.com
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20170801/98ab13ef/attachment-0001.htm>
More information about the Postgres-xl-developers
mailing list