Re: dealing with lock

Thank you Laurenz!

Regards
Thomas

Le sam. 7 avr. 2018 à 00:02, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)albe(at)cybertec(dot)at
> > <mailto: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
> > <https://www.cybertec-postgresql.com>
> >
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>