Обсуждение: Consecutive queries

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

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;

(ii) putting a COMMIT before the SELECT, as follows:

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

Neither produces what I want. Any help will be greatly appreciated!

--Ray.

-------------------------------------------------------------
Raymond O'Donnell     http://www.galwaycathedral.org/recitals
rod@iol.ie                          Galway Cathedral Recitals
-------------------------------------------------------------

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