Re: dealing with lock

On Fri, 2018-04-06 at 16:58 +0200, Thomas Poty wrote:
> Here is a bit of context : we are migrating from MySQL to PostgreSQL and we have about 1000 tables.
> Some tables are quite small but some others are very large. The service provided to our clients
> relies on a high avaiability with a minimum down time due to any legal deadlines.
>
> So, lets imagine :
> in Transaction 1 : I am querying Table A (select)
> in Transaction 2 : I am trying to alter Table A ( due to our product evolution)
> in Transaction 3 : I am want to query Table1 (select)
>
> in MySQL : Transaction 1 retrieve data in Table A.
> Transaction 2 : is trying to alter Table A but it is blocked by Transaction 1
> Transaction 3 : Transaction 1 retrieves data in Table A ( Retreiving data is possible until Transaction 2 commit)
>
> In PostgreSQL, it is a bit different : Transaction 1 retrieve data in Table A.
> Transaction 2 : is trying to alter Table A but it is blocked by Transaction 1
> Transaction 3 : Transaction 3 cannot retrieve data because Transaction 2 did not terminate its transaction.
>
> So, with MySQL, the application is able to keep working with the table until the alter table completed.
>
> With PostgreSQL, the application will probably be blocked (until having the lock on this table).
> If I understand, if the alter table takes a long time (several hours) to execute, clients will be blocked during several hours.
>
> How do you deal with this problem? Maybe I missed something ?

The solution is to avoid ALTER TABLE statements that have to rewrite
the table outside of maintenance windows.

If your transactions are short, as they should be, it should not be
a big deal to add or drop a column, for example.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com