Re: BUG: pgScript NOT thread safe!!

Mickael; Is this something that can be fixed quickly and easily? We're
building the releases first thing tomorrow, UK time and unfortunately
I can't delay as we're committed to the PostgreSQL release timing.

Ashesh; in case Mickael doesn't see this in time, can you please work
up a quick hack in pgadmin to prevent multiple scripts being run at
any one time? Probably a conveniently placed wxMutex will do the job.

Thanks for the report Andras.

On Thu, Jun 25, 2009 at 3:50 PM, Andras Fabian<Fabian@atrada.net> wrote:
> Hi pgAdmin developers,
>
> We are users of your great tool pgAdmin since we started migrating
> projects to PostgreSQl. But just two days ago we noticed a very nasty
> bug in the otherwise very great little tool pgScript.
>
> One of our developers needed to fire a lot of updates for a specific
> range of values, so he wrote a neat little pgScript code to do it.
> Essentially only a loop with some variables which are incremented and
> passed to an UPDATE statement. But some days ago he also noticed a very
> nasty behavior while running two pgScript in two different query windows
> at the same time (he needed to run it against many diferent DBs). As he
> had some PRINT outputs to monitor the advance of his script he noticed,
> that one script got some absolutely wrong values for one of his
> variables.
>
> The I wrote a simplified version of the loop - even left out the SQL
> code (!) - to look what happens. And as soon as I run them it TWO query
> windows (but started from the same pgAdmin process!) I could notice the
> weirdest behaviors one could expect. The most usual was early
> termination of the loop - without error messages - but there were also
> loops which then run much longer as their target value would have
> suggested. In one case, we even got an "unhandled exception" which
> completely nuked pgAdmin.
>
> As soon as we did not run in TWO threads in ONE process, but instead run
> the Query in two different processes (started pgAdmin twice and opened
> one Query window in each), everything was fine. We could run the script
> as often as we wanted (and the same time as the other was running) and
> nothing went wrong. The script went just trough deterministically.
>
> So, from this observations I would dare to say, that this looks like
> some very basic "thread safety" precautions were omitted in the pgScript
> code.
>
> I would also say, that this is a dangerous BUG! Think about a similar
> script like ours, which has to delete data from a specific range. Now,
> what if it deletes more, because the LOOP runs longer?`
>
> And you might also ask, why the hell we would run more than one similar
> scripts at the same time? Well, if you need to do a maintenance task
> (the same task) for many DBs or Servers, you might easily come up with
> the idea to do it from many query windows.
>
> Finally, I would - of course - share the simple script, which helps to
> reproduce the odd behavior. With it, I hope, you can quickly pinpoint
> the issue and fix it. Before that I would recommend to issue a warning
> to users, to never run more then one pgScript rom the same process.
>
> Here is the Script, start it from two query windows (from one process)
> at the same time, and you should easily see what I mean:
> -------------------------------------------------------
> DECLARE @v_package;
> DECLARE @v_purchaseidmin;
> DECLARE @v_purchaseidmax;
>
> DECLARE @v_to;
> DECLARE @v_from;
> DECLARE @v_i;
>
> SET @v_package = 10;
> SET @v_purchaseidmin = 0;
> SET @v_purchaseidmax = 120000000;
>
> SET @v_from = @v_purchaseidmin;
> SET @v_to = (@v_purchaseidmin + @v_package);
> SET @v_i = 1;
>
>
> WHILE ( @v_from <= @v_purchaseidmax ) BEGIN
>
>        PRINT  'from: ' + CAST(@v_from AS STRING);
>        PRINT 'to: ' + CAST(@v_to AS STRING);
>        PRINT 'i: ' + CAST(@v_i AS STRING);
>
>        -- some SQL code could be executed here
>
>        SET @v_from = (@v_from + @v_package);
>        SET @v_to = (@v_to + @v_package);
>        SET @v_i = @v_i+1;
> END
> -------------------------------------------------------
>
>
> Andras Fabian
>
> - - - - - - - - - - - - - -
>
> IT
> mailto:fabian@atrada.net
>
> Atrada Trading Network AG
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
>



--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com