[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