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