Re: Add column with default value in big table - splitting of updatescan help?

On 1/31/20 5:43 AM, Durumdara wrote:
> Hello!
> 
> a.)
> PG version is mainly 9.6, but some of the servers are 10.x or 11.x.
> 
> b.)
> We have semi-automatic tool which get the a new modifications on 
> databases, and execute them at once by database.
> So one SQL script by one database, under one transaction - whole or 
> nothing. If something failed, we know where to we start again by hand. 
> It is impossible to execute only the first half, and we don't know which 
> one executed or not
> 
> The main problem that sometimes we have to modify some tables which have 
> too much records in some customer databases.
> 
> ---
> 
> As I think the best way to solve this:
> 
> 1.)
> Future goal: upgrade to PG 11.
> 
> 2.)
> We need to write a preprocessor code. If it finds "alter table" with 
> "add column" and "default", and it is "critical database", the whole 
> operation halts on that point, and warn us to "do it yourself"... :-)
> 
> ---
> 
> After the previous same problem on tk table I tried to write a client 
> app, which update records by 10000 with commit - but it was very slow.
> 
>     update tk set field1 = 'MUCH' where id in (
>        select id from tk where field1 is NULL limit 10000
>     )
> 
> I think this caused that in the goal field haven't got index (because 
> many times the fields with default values are not searchable, just row 
> level fields), and the client/server communication is slower than I like.
> 
> Formerly I thought I could speeding up this with stored proc - but as I 
> read the stored procs can't use inner transactions - so I must make 
> client programme to use begin/commit... (PGAdmin?).


Up to version 11 Postgres only had stored functions. With 11+ there are 
stored procedures and inner transactions:

https://www.postgresql.org/docs/11/plpgsql-transactions.html

In stored functions the best you can do is:

https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

> 
> Thanks for the prior infos!
> 
> Best regards
>     dd
> 
> 
> hubert depesz lubaczewski <depesz@depesz.com <mailto:depesz@depesz.com>> 
> ezt írta (időpont: 2020. jan. 30., Cs, 17:20):
> 
>     On Thu, Jan 30, 2020 at 04:51:02PM +0100, Durumdara wrote:
>      > Is there any advance when I split updates? I'm in one transaction.
> 
>     In this case - no. The benefit of split updates is when you're not in
>     single transaction.
> 
>     Why would you want to have it all done in single transaction?
> 
>     Best regards,
> 
>     depesz
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com