Re: dealing with lock

Thank you Laurenz !

We will certainly have to change our release management.

Is there a way to identify the list of statements that have to rewrite the
table.

If I am right, at least these statements need to do this :
- create a unique index
- add a column with a default value

Regards,

Thomas

2018-04-06 17:11 GMT+02:00 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>:

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