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

2020-03-08 Thread Peter J. Holzer
On 2020-03-04 14:42:01 -0800, Guyren Howe wrote: > On Mar 4, 2020, at 14:33 , Rory Campbell-Lange > wrote: > Essentially we wish to reduce the window where the frontend and backend > aren't synchronised. > > If we have (for example) 200 databases which each take 2 seconds to > up

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

2020-03-05 Thread Rory Campbell-Lange
On 04/03/20, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > We have many databases of the same type separated for data governance > reasons. They, however, share the same web front-end code. > > Presently, replacing functions and performing data updates on the > databases in series often e

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

2020-03-05 Thread Alban Hertroys
> On 4 Mar 2020, at 23:42, Guyren Howe wrote: > > On Mar 4, 2020, at 14:33 , Rory Campbell-Lange > wrote: >> >> Essentially we wish to reduce the window where the frontend and backend >> aren't synchronised. >> >> If we have (for example) 200 databases which each take 2 seconds to >> update

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

2020-03-04 Thread Rory Campbell-Lange
On 04/03/20, David G. Johnston (david.g.johns...@gmail.com) wrote: > On Wed, Mar 4, 2020 at 4:41 PM Rory Campbell-Lange > wrote: > > > 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 transac

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

2020-03-04 Thread David G. Johnston
On Wed, Mar 4, 2020 at 4:41 PM Rory Campbell-Lange wrote: > 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 > (pre

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

2020-03-04 Thread Rory Campbell-Lange
On 04/03/20, David G. Johnston (david.g.johns...@gmail.com) wrote: > On Wed, Mar 4, 2020 at 3:55 PM David G. Johnston > wrote: > > > On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange < > > r...@campbell-lange.net> wrote: > > > >> Any thoughts on how to wrap pl/pgsql function dropping and recreat

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

2020-03-04 Thread Adrian Klaver
On 3/4/20 2:42 PM, Guyren Howe wrote: On Mar 4, 2020, at 14:33 , Rory Campbell-Lange > wrote: Essentially we wish to reduce the window where the frontend and backend aren't synchronised. If we have (for example) 200 databases which each take 2 seconds to update,

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

2020-03-04 Thread David G. Johnston
On Wed, Mar 4, 2020 at 3:55 PM David G. Johnston wrote: > On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange < > r...@campbell-lange.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 dyna

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

2020-03-04 Thread David G. Johnston
On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange 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 libe

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

2020-03-04 Thread Rory Campbell-Lange
On 04/03/20, Guyren Howe (guy...@gmail.com) wrote: > On Mar 4, 2020, at 14:33 , Rory Campbell-Lange > wrote: > > > > Essentially we wish to reduce the window where the frontend and backend > > aren't synchronised. > > > > If we have (for example) 200 databases which each take 2 seconds to > > u

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

2020-03-04 Thread Guyren Howe
On Mar 4, 2020, at 14:33 , Rory Campbell-Lange wrote: > > Essentially we wish to reduce the window where the frontend and backend > aren't synchronised. > > If we have (for example) 200 databases which each take 2 seconds to > update, a client could be on the wrong frontend code for over 6 minut

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

2020-03-04 Thread Ron
On 3/4/20 4:33 PM, Rory Campbell-Lange wrote: On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 3/4/20 2:22 PM, Rory Campbell-Lange wrote: On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: We have many databases of the s

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

2020-03-04 Thread Rory Campbell-Lange
On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 3/4/20 2:22 PM, Rory Campbell-Lange wrote: > > On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: > > > On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: > > > > We have many databases of the same type separated for data gove

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

2020-03-04 Thread David G. Johnston
On Wed, Mar 4, 2020 at 3:04 PM Rory Campbell-Lange wrote: > However, for larger updates, the time when the front end code is > out-of-step with the database can cause end-user problems. > You should try very hard to structure your database migrations so that instead of going directly from unique

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

2020-03-04 Thread Adrian Klaver
On 3/4/20 2:22 PM, Rory Campbell-Lange wrote: On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: We have many databases of the same type separated for data governance reasons. They, however, share the same web front-end code. Presently,

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

2020-03-04 Thread Rory Campbell-Lange
On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote: > On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: > > We have many databases of the same type separated for data governance > > reasons. They, however, share the same web front-end code. > > > > Presently, replacing functions and performi

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

2020-03-04 Thread Adrian Klaver
On 3/4/20 2:04 PM, Rory Campbell-Lange wrote: We have many databases of the same type separated for data governance reasons. They, however, share the same web front-end code. Presently, replacing functions and performing data updates on the databases in series often executes across all databases