Thank you Laurenz! Regards Thomas
Le sam. 7 avr. 2018 à 00:02, Adrian Klaver <adrian.kla...@aklaver.com> a écrit : > On 04/06/2018 12:09 PM, Thomas Poty wrote: > > 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. > > https://www.postgresql.org/docs/10/static/sql-altertable.html > > Notes > > "Adding a column with a DEFAULT clause or changing the type of an > existing column will require the entire table and its indexes to be > rewritten. As an exception when changing the type of an existing column, > if the USING clause does not change the column contents and the old type > is either binary coercible to the new type or an unconstrained domain > over the new type, a table rewrite is not needed; but any indexes on the > affected columns must still be rebuilt. Adding or removing a system oid > column also requires rewriting the entire table. Table and/or index > rebuilds may take a significant amount of time for a large table; and > will temporarily require as much as double the disk space." > > > For the more general case of modifying a table and the locks it takes, > search the above link for lock to see what locks are taken instead of > the default of ACCESS EXCLUSIVE. > > For what the locks mean see: > > https://www.postgresql.org/docs/10/static/explicit-locking.html > > > > > 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 > > <mailto: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 > > <https://www.cybertec-postgresql.com> > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
