Re: Add column with default value in big table - splitting of updates can 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(at)depesz(dot)com <mailto:depesz(at)depesz(dot)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(dot)klaver(at)aklaver(dot)com