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.a...@cybertec.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 >
