Postgres error 25P02

[eversun]

Hi all !

Trying to wotk with Zeos 7.3 (SVN, r6270) - important problem with Postgres driver (Postgres 11.0).

If an exception is raised in stored procedure, instead of a correct message the following error appears:
SQL Error: ERROR 25P02
current transaction is aborted, commands ignored until end of transaction block
source file: postgres.c
line: 1359
funtion: exec_parse_message

[marsupilami]

Hello,

I had a similar error some years ago. This happens if you ignore a previous error message from PostgreSQL or if it gets hidden by some part of the code that raises another exception. As soon as a transaction in PostgreSQL has an error it seems to enter kinda faild state that only can be cleared by rolling back your transaction. The following codes should demonstrate the problem:

Code: Select all

  ZConnection.StartTransaction
  try
    ZConnection.ExecuteDirect('insert into testtable (primarykeyfield, datafield) values (1, 5000)');
    // this should create an error since 1 is already used in the primary key field.
    // it puts the transaction into the failed state.
    ZConnection.ExecuteDirect('insert into testtable (primarykeyfield, datafield) values (1, 5000)');
  except
    // even though 2 is not used, this will not work because the transaction is in a failed state
    // and needs to be rolled back.
    ZConnection.ExecuteDirect('insert into testtable (primarykeyfield, datafield) values (2, 5000)');
  end;
  ZConnection.Commit;

This example shows how a different exception can hide the previously generated exception so you don't know that anything went wrong in PostgreSQL:

Code: Select all

var
  SomeComponent: TComponent;
begin
  SomeComponent := nil;
  ZConnection.StartTransaction;
  try
    ZConnection.ExecuteDirect('insert into testtable (primarykeyfield, datafield) values (1, 5000)');
    // this should create an error since 1 is already used in the primary key field.
    // it puts the transaction into the failed state.
    ZConnection.ExecuteDirect('insert into testtable (primarykeyfield, datafield) values (1, 5000)');
    // commit is never executed because we generate an exception in the previous line.
    ZConnection.Commit;
  except
    // this code triggers another exception that will effectively hide the previous exception from PostgreSQL
    // by generating an access violation.
    SomeComponent.DoSomething;
    ZConnection.Rollback;
    raise;
  end;

Your best bet is to run the program and watch out for exceptions that get replaced by other exceptions or somehow don't get handeled correctly.

Best regards,

Jan