Re: dealing with lock

[Thomas Poty]

Thank you Thomas

Regards
Thomas

Le sam. 7 avr. 2018 à 08:01, Thomas Kellerer  a écrit :

> Adrian Klaver schrieb am 07.04.2018 um 00:02:
> >> 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.
> Note that this will change with Postgres 11
>
>
> https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/
>
>
>
>

Re: dealing with lock

[Thomas Poty]

Thank you Laurenz!

Regards
Thomas

Le sam. 7 avr. 2018 à 00:02, Adrian Klaver  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  > >:
> >
> > 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
> > 
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: dealing with lock

[Thomas Kellerer]

Adrian Klaver schrieb am 07.04.2018 um 00:02:

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. 

Note that this will change with Postgres 11

https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/

Re: dealing with lock

[Adrian Klaver]

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


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






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: dealing with lock

[Thomas Poty]

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 :

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

Re: dealing with lock

[Laurenz Albe]

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

dealing with lock

[Thomas Poty]

Hello All,

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 ?

Thank you all for  yours answers.