On 6 Apr 2003 at 19:41, Tom Lane wrote:
> This behavior is, quite simply, not possible. Unless perhaps you are
> issuing the two queries across different connections, so that the
> second one actually starts to execute before the first one is done.
I've tried to ensure that the two queries are run by the same
backend. I'm using Delphi 6 to manipulate ADO, which talks to
Postgres via ODBC. The code of the specific procedure which seems to
be misbehaving is given below - as you'll see, the two queries are
built as a single string and so are executed together when
TheQry.Open is called.
> If you are (mis)using a client library that implements pooling of
> connections, I can see how such a mistake might happen.
The connection pooling is handled by ODBC.
--Ray.
-----[Delphi code follows]------
function TDatabaseLink.CountApplicantsAvailable(const CourseCode:
WideString): Integer;
var
Conn: TADOConnection;
TheQry: TADOQuery;
begin
Conn := TADOConnection.Create(nil);
TheQry := TADOQuery.Create(nil);
try
Conn.ConnectionString := ADOConnStr; // defined elsewhere
Conn.Open;
TheQry.Connection := Conn;
TheQry.SQL.Text := 'begin; ';
// add the UPDATE query
TheQry.SQL.Add('update applications set status=' +
MakeIntegerStr(statAvailable + statChoice2)
+ ' where applicationnumber in '
+ '(select applicationnumber from applications a inner join
courses c on (a.choice1=c.coursecode) '
+ 'where (a.choice2=' + QuotedStr(CourseCode) + ') '
+ 'and (c.isfull=' + MakeBooleanStr(true) + ') '
+ 'and (a.status=' + MakeIntegerStr(statAvailable +
statChoice1) + ')'
+ '); ');
TheQry.SQL.Add('commit; ');
// add the SELECT query
TheQry.SQL.Add('select count(applicationnumber) from applications
'
+ 'where (choice1=' + QuotedStr(CourseCode) + ' and status=' +
MakeIntegerStr(statAvailable + statChoice1) + ') '
+ 'or (choice2=' + QuotedStr(CourseCode) + ' and status=' +
MakeIntegerStr(statAvailable + statChoice2) + ') '
+ 'or (choice3=' + QuotedStr(CourseCode) + ' and status=' +
MakeIntegerStr(statAvailable + statChoice3) + ');');
// execute the query
TheQry.Open;
Result := TheQry.Fields[0].AsInteger;
finally
TheQry.Close;
TheQry.Free;
Conn.Close;
Conn.Free;
end;
end;
-------------------------------------------------------------
Raymond O'Donnell http://www.galwaycathedral.org/recitals
rod@iol.ie Galway Cathedral Recitals
-------------------------------------------------------------
