Re: [pgAdmin III] #12: pgScript engine is not threadsafe

#12: pgScript engine is not threadsafe
---------------------------+------------------------------------------------
 Reporter:  Andras Fabian  |       Owner:  mdeloison
     Type:  bug            |      Status:  assigned 
 Priority:  major          |   Milestone:           
Component:  pgadmin        |     Version:           
 Keywords:                 |    Platform:           
---------------------------+------------------------------------------------
Changes (by dpage):

  * owner:  dpage => mdeloison
  * status:  new => assigned


Old description:

> 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
> -------------------------------------------------------

New description:

 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
 -------------------------------------------------------

--

Comment:

 Comment from Mickael...

 Very sorry for this bug. I did not take into account the case when one
 uses 2 or more Query tool windows. If one launches two windows, it
 will create two pgsApplication. However, it will use the same
 Flex/Bison parser. Actually I can''t do anything about Flex & Bison
 not supporting multiple threads.

 So the only immediate fix I see is the one provided by Ashesh plus
 showing a message box when a script is running saying that a script is
 already running (Magnus suggestion). I do not currently have a
 development environment up-to-date: Ashesh, could you add the message
 box please?

 I write down this bug in my TODO list and will try to manage it better
 later. Once again, sorry for this bug.

--
Ticket URL: <http://code.pgadmin.org/trac/ticket/12#comment:1>
pgAdmin III <http://code.pgadmin.org/trac/>
pgAdmin III