Consecutive queries

On Sun, Apr 06, 2003 at 09:23:04PM +0100, Raymond O'Donnell wrote:

Hello all,

Is there any way to ensure that one query has completed before a
second one is executed? I have to execute an UPDATE and follow it

Sure; do one after another in the same transaction.

I'm accessing PostgresSQL via ADO on a windows machine, and I tried -

(i) enclosing the two queries in one transaction, as follows:

begin;
update ... (etc)... ;
select ...(etc)...;
commit;

If this doesn't work, then there must be something you're not telling
us, or else something _really_ strange about the ADO interface. The
UPDATE either completed or not; there'd be no way for the SELECT to
return anything different than the state of the relevant tuples.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

"Raymond O'Donnell" <rod@iol.ie> writes:

Is there any way to ensure that one query has completed before a
second one is executed? I have to execute an UPDATE and follow it
immediately with a SELECT, but I find that the SELECT is picking up
incorrect data because - I assume - the UPDATE hasn't completed yet.
When I re-run the SELECT a few seconds later I gvet the correct data.

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. If you
are (mis)using a client library that implements pooling of connections,
I can see how such a mistake might happen. But we're not going to be
able to help you if you don't show us your code.

regards, tom lane

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