Queries with errors in PostgreSQL

[chkaufmann]

The following code fails on PostgreSQL, if "myTable1" does not exist, but "myTable2" exists.

The error is "current transaction is aborted, commands ignored until end of transaction block".

var
ds : TUniQuery;

begin
try
ds.Sql.Text := 'select * from myTable1';
ds.Open;
except
on E: Exception do begin
ds.Sql.Text := 'select * from myTable2';
ds.Open;
end;
end;
end;

Do I have to fix / workaround this myself or is this something, that can be fixed in UniDac?

cu Christian

[DepSoft]

Trying your code here it works as expected i.e. It opens table2 without displaying any exception regarding the missing table1.

D2010, Pg 9, UniDAC 3.0.0.10

Regards, Paul.

[chkaufmann]

[quote="DepSoft"]Trying your code here it works as expected i.e. It opens table2 without displaying any exception regarding the missing table1.

But try with:

ds.UniDirectional := True;

then it will fail.

D2010, Pg 8.4, UniDAC 3.0.0.8

cu Christian

[DepSoft]

You're right, you should have said

Would it not be better to properly check if the table exists first if it may not always be present?

With your exception handler, there may well be other exceptions that could also be raised that might also stop you opening table2.

I use a TableExists function that queries PG_TABLES or you could always do something with the metadata component.

e.g.

Code: Select all

SELECT COUNT(*) FROM PG_TABLES WHERE UPPER(TABLENAME) = :SOME_TABLE_NAME

Regards, Paul.

[chkaufmann]

Would it not be better to properly check if the table exists first if it may not always be present?

Well yes and know. I have my own db wrapper, UniDac is just one component set and I use exceptions in cases like this in different places.

So I prefer a solution where I don't have to go through all my code.

cu Christian

[bork]

Hello

Thank you for the information. We have reproduced this problem and fixed it. This fix will be included in the next PgDAC and UniDAC builds.

[chkaufmann]

This fix will be included in the next PgDAC and UniDAC builds.

When can we expect a new build for UniDAC?

cu Christian

[bork]

Hello

We are planning to release the next version of UniDAC this week.

[chkaufmann]

It's funny to find my own post from 9 years ago when I search for a solution for a problem :-).

I have a similar case:
- Postgresql 11
- using a TUniQuery

Code: Select all

    FUniConnection.Open;
    FUniConnection.DefaultTransaction.StartTransaction;
    try
      FUniQuery.SQL.Text := 'select last_value from GEN_NOT_THERE';  // this fails
      FUniQuery.Open;
    except
      // nothing here
    end;
    FUniQuery.SQL.Text := 'select count(*) from BSENTITY';
    FUniQuery.Open;
    FUniConnection.DefaultTransaction.Commit;

Again, the second call of "Open" fails with error "current transaction is aborted, commands ignored until end of transaction block".

Could you please look into this?

Regards
Christian

[MaximG]

We've tested UniDAC according to your description of the issue, no issues have been found. Please create and send us a sample project with source code where the issue can be reproduced. Also we need the DDL script that creates the tables that you use in the sample project. For your convenience, please use the e-support form https://www.devart.com/company/contactform.html

[MaximG]

We've received your sample code and investigated it. When accessing the non-existing object "gen_not_there", you get the error: relation "gen_not_there" does not exist, which you then handle in your application code. However, handling this error doesn't solve the root cause -- the error returned by the PostgreSQL server remains unhandled. Lack of handling for this error on the server side leads to the error: "current transaction is aborted, commands ignored until end of transaction"

[chkaufmann]

But how should I handle this? In case I opened the transaction explicitely: Did the server rollback all? Should I call conn.Commit or conn.Rollback?

Christian

[MaximG]

You need to implement the handling of the PostgreSQL server error in the except.. end block. Operator invocations for the current transaction will be completely defined by the business logic of your application in this case.