Re: Advice request : simultaneous function/data updates on many databases

On 04/03/20, David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> On Wed, Mar 4, 2020 at 3:55 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
> > On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange <
> > rory(at)campbell-lange(dot)net> wrote:
> >
> >> Any thoughts on how to wrap pl/pgsql function dropping and recreation code
> >> within a wrapper pl/pgsql function?
> >
> >
> > Not endorsing this but dynamic SQL works just fine (though can get hard to
> > read). Use format() and EXECUTE ... USING liberally.
> >
> >
> Or, more readable depending upon your trust level:
>
> INSERT INTO dynamic_codes VALUES (1, 'DROP FUNCTION ...');
>
> CREATE FUNCTION execute_dynamic(code_id int)
> AS $$
> sql_cmd := (SELECT val FROM dynamic_codes WHERE id = code_id);
> EXECUTE sql_cmd;
> $$;
>
> SELECT execute_dynamic(1);

Thanks very much for the useful examples.

Based on your second example, we could drop and then reload a upgrade
schema with entries in dynamic_codes then use execute_dynamic(...) as
you suggest.

Any idea on how to run execute_dynamic across many databases at roughly
the same time?

I'm just wondering if Guyren Howe's idea of having many transactions
open waiting for a clock time to commit is in fact feasible due to
(presumably) having to have all the connections open to every database
from the client until the transactions complete.