TPgQuery not using supplied connection

[nickbarnes]

Using PgDAC 4.1.4.

With Query.FetchAll=False, calling Query.Open uses some implicitly created connection, rather than the one assigned to Query.Connection. This effectively breaks temp table queries, since the temp tables created on the supplied connection are not visible.

In other words, the following code fails, with the error "relation "temp" does not exist":

Code: Select all

procedure TempTest;
var
  Connection: TPgConnection;
  Query: TPgQuery;
begin
  Connection := TPgConnection.Create(nil);
  Connection.Database := 'database';
  Connection.Server := 'server';
  Connection.UserName := 'user';
  Connection.Password := 'password';
  Connection.Connect;

  Query := TPgQuery.Create(nil);
  Query.Connection := Connection;
  Query.SQL.Text := 'CREATE TEMP TABLE Temp (TestRef CHAR(10))';
  Query.ExecSQL;

  Query := TPgQuery.Create(nil);
  Query.Connection := Connection;
  Query.SQL.Text := 'SELECT TestRef FROM Temp';
  Query.FetchAll := False;
  Query.Open;
end;

I seem to be able to work around this by setting Query.FetchAll:=True, or Query.Options.CursorWithHold:=True, or by explicitly starting my own transaction around the Query.Open, but I'd rather not have to do any of these.

Is there a better solution?

[AlexP]

Hello,

This is correct behaviour, and it is due to the specificity of PostgreSQL server. To use FetchAll, we have to create a new connection, in which the open DataSet will be fetched.
To work in the FetchAll = False mode with temporary tables, you should start a new transaction before creating a temporary table; otherwise, on an attempt to open the table, a new transaction will be automatically started, in which the created temp table will be not visible.

[nickbarnes]

Thanks! Not quite what I was hoping to hear, but at least I know what to expect. I wasn't aware that Postgres doesn't allow multiple concurrent transactions per connection, so even though the behaviour here seems counterintuitive, I guess there's really no better option...