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

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?).

Thanks for the prior infos!

Best regards
dd

hubert depesz lubaczewski <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
>
>