Re: BUG: pgScript NOT thread safe!!

Sorry, I have - of course - forgot to support the important pgAdmin and OS vewrsion information. It is pgAdmin 1.10rc1
runon Windows XP (SP3). 

Andras Fabian

- - - - - - - - - - - - - -

IT
mailto:fabian@atrada.net

Atrada Trading Network AG


-----Ursprüngliche Nachricht-----
Von: Andras Fabian
Gesendet: Donnerstag, 25. Juni 2009 16:51
An: 'pgadmin-support@postgresql.org'
Betreff: BUG: pgScript NOT thread safe!!

Hi pgAdmin developers,

We are users of your great tool pgAdmin since we started migrating projects to PostgreSQl. But just two days ago we
noticeda 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
pgScriptcode 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
windowsat the same time (he needed to run it against many diferent DBs). As he had some PRINT outputs to monitor the
advanceof 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
runthem it TWO query windows (but started from the same pgAdmin process!) I could notice the weirdest behaviors one
couldexpect. The most usual was early termination of the loop - without error messages - but there were also loops
whichthen run much longer as their target value would have suggested. In one case, we even got an "unhandled exception"
whichcompletely 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
pgAdmintwice and opened one Query window in each), everything was fine. We could run the script as often as we wanted
(andthe 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
omittedin 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
specificrange. 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
doa maintenance task (the same task) for many DBs or Servers, you might easily come up with the idea to do it from many
querywindows. 

Finally, I would - of course - share the simple script, which helps to reproduce the odd behavior. With it, I hope, you
canquickly pinpoint the issue and fix it. Before that I would recommend to issue a warning to users, to never run more
thenone 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
Imean: 
-------------------------------------------------------
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 ) BEGINPRINT  '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