Horton
Menu

Thread

  1. Re: Error while updating a table

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2025-04-18T05:49:30Z

    Hello,
We use PowerBuilder along with PostgreSQL.
PostgreSQL 15.7 (Ubuntu 15.7-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit

Suddenly, one system starts throwing an error while updating a record from PowerBuilder.  The same update statement (window) works fine, when run from other computers.  Error occurs only when the statement is run from a particular computer.  The SQL Statement is
Update public.co_voucherseries_transaction_branchSet    vouchernumber   = <new number>Where  companycode     = '100'And    branchcode      = '001'And    accountperiodid = 1And    voucherseries   = 'SERIES'And    voucherversion  = 'version'And    activestatus    = 'Y'  ;
The error isSQLSTATE=25P02ERROR: current transaction is aborted, commands ignored until end of transaction block;Error while executing the query
Through googling, I found that last transaction was not properly rolled back.The (Postgresql) Linux server will be shut down every night and re-booted next day morning.   
Don't know when this error has occurred.  
Now I need to rectify this error?  How to do it?  Any help is really appreciated.
Happiness Always
BKR Sivaprakash

  2. Re: Error while updating a table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-04-18T06:54:30Z

    On Fri, 2025-04-18 at 05:49 +0000, sivapostgres@yahoo.com wrote:
> We use PowerBuilder along with PostgreSQL.
> 
> PostgreSQL 15.7 (Ubuntu 15.7-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
> 
> Suddenly, one system starts throwing an error while updating a record from PowerBuilder.
> The same update statement (window) works fine, when run from other computers.  Error
> occurs only when the statement is run from a particular computer.  The SQL Statement is
> 
> Update public.co_voucherseries_transaction_branch
> Set    vouchernumber   = <new number>
> Where  companycode     = '100'
> And    branchcode      = '001'
> And    accountperiodid = 1
> And    voucherseries   = 'SERIES'
> And    voucherversion  = 'version'
> And    activestatus    = 'Y'  ;
> 
> The error is
> SQLSTATE=25P02
> ERROR: current transaction is aborted, commands ignored until end of transaction block;
> Error while executing the query
> 
> Through googling, I found that last transaction was not properly rolled back.
> The (Postgresql) Linux server will be shut down every night and re-booted next day morning.   
> 
> Don't know when this error has occurred.  
> 
> Now I need to rectify this error?  How to do it?  Any help is really appreciated.

You would have to improve the code quality of the application, which does not seem
to do correct error handling or transaction management.

That error means that the *previous* statement (or one of the statements earlier in
the same database transaction) has caused an error.  In PostgreSQL, that means that
the transaction is aborted, and the following statements until the end of the
transaction will receive the error you experience.

So one of the following must be the case:

- the previous statement caused an error, but you didn't detect or handle that

- the previous transaction run on the same database connection caused an error,
  but you forgot to run ROLLBACK

Yours,
Laurenz Albe

  3. Re: Error while updating a table

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2025-04-18T07:31:52Z

     Thanks Laurenz,
Now I need to do 2 things.
1.  Correct my application to catch errors and do either Rollback or commit.   This part is running smoothly from all other computers, so far.  Nevertheless I'll check that for any missing catch.2.  Find the faulty entry and do rectify.
For the second part, how to identify that culprit query?   Seems this has happened a few days back.  At that time, we had some power issues that made network to fail.  
To proceed, we need to find the culprit query?  How? Where?  No idea what was the pid then, as all computers were shut down every night.
Any possibilities?  steps?
Happiness Always
BKR Sivaprakash

    On Friday 18 April, 2025 at 12:24:54 pm IST, Laurenz Albe <laurenz.albe@cybertec.at> wrote:  
 
 On Fri, 2025-04-18 at 05:49 +0000, sivapostgres@yahoo.com wrote:
> We use PowerBuilder along with PostgreSQL.
> 
> PostgreSQL 15.7 (Ubuntu 15.7-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
> 
> Suddenly, one system starts throwing an error while updating a record from PowerBuilder.
> The same update statement (window) works fine, when run from other computers.  Error
> occurs only when the statement is run from a particular computer.  The SQL Statement is
> 
> Update public.co_voucherseries_transaction_branch
> Set    vouchernumber   = <new number>
> Where  companycode     = '100'
> And    branchcode      = '001'
> And    accountperiodid = 1
> And    voucherseries   = 'SERIES'
> And    voucherversion  = 'version'
> And    activestatus    = 'Y'  ;
> 
> The error is
> SQLSTATE=25P02
> ERROR: current transaction is aborted, commands ignored until end of transaction block;
> Error while executing the query
> 
> Through googling, I found that last transaction was not properly rolled back.
> The (Postgresql) Linux server will be shut down every night and re-booted next day morning.   
> 
> Don't know when this error has occurred.  
> 
> Now I need to rectify this error?  How to do it?  Any help is really appreciated.

You would have to improve the code quality of the application, which does not seem
to do correct error handling or transaction management.

That error means that the *previous* statement (or one of the statements earlier in
the same database transaction) has caused an error.  In PostgreSQL, that means that
the transaction is aborted, and the following statements until the end of the
transaction will receive the error you experience.

So one of the following must be the case:

- the previous statement caused an error, but you didn't detect or handle that

- the previous transaction run on the same database connection caused an error,
  but you forgot to run ROLLBACK

Yours,
Laurenz Albe

  4. Re: Error while updating a table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-04-18T09:58:11Z

    On Fri, 2025-04-18 at 07:31 +0000, sivapostgres@yahoo.com wrote:
> For the second part, how to identify that culprit query?

If the query caused an error, and you left "log_min_messages" at the
default setting, the error and the statement that caused it should
be in the PostgreSQL log.

Yours,
Laurenz Albe

  5. Re: Error while updating a table

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2025-04-18T11:20:13Z

     If the query caused an error ....
Does it mean that other reasons might also be there?
Nevertheless, I'll check the log file to find the query...
Happiness Always
BKR Sivaprakash
    On Friday 18 April, 2025 at 03:53:06 pm IST, Laurenz Albe <laurenz.albe@cybertec.at> wrote:  
 
 On Fri, 2025-04-18 at 07:31 +0000, sivapostgres@yahoo.com wrote:
> For the second part, how to identify that culprit query?

If the query caused an error, and you left "log_min_messages" at the
default setting, the error and the statement that caused it should
be in the PostgreSQL log.

Yours,
Laurenz Albe

  6. Re: Error while updating a table

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2025-04-19T12:38:49Z

     So far, I couldn't find the query that caused the error.   Is there any way to unlock it?  
Re-start, Format and fresh install of windows, etc. ?
Happiness Always
BKR Sivaprakash
    On Friday 18 April, 2025 at 04:50:52 pm IST, sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote:  
 
  If the query caused an error ....
Does it mean that other reasons might also be there?
Nevertheless, I'll check the log file to find the query...
Happiness Always
BKR Sivaprakash
    On Friday 18 April, 2025 at 03:53:06 pm IST, Laurenz Albe <laurenz.albe@cybertec.at> wrote:  
 
 On Fri, 2025-04-18 at 07:31 +0000, sivapostgres@yahoo.com wrote:
> For the second part, how to identify that culprit query?

If the query caused an error, and you left "log_min_messages" at the
default setting, the error and the statement that caused it should
be in the PostgreSQL log.

Yours,
Laurenz Albe

  7. Re: Error while updating a table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-04-19T12:55:12Z

    On Sat, 2025-04-19 at 12:38 +0000, sivapostgres@yahoo.com wrote:
> So far, I couldn't find the query that caused the error.   Is there any way to unlock it?  
> 
> Re-start, Format and fresh install of windows, etc. ?

On Windows you have to reboot, bow three times toward sunset,
say a prayer to Bill Gates, then reboot again.

No, seriously: randomly messing around with your machine rarely ever
gets you closer to a solution.

If you get the error

  current transaction is aborted, commands ignored until end of transaction block

it is 100% clear that an earlier statement in the same transaction must
have got an error.  Write your application so that it logs *every* error
that comes from the database, then you are sure to catch that error.

Also, if "log_min_messages" is "warning" (the default) or "error", you
will find the error in the PostgreSQL log.  That's all I can say to you.

Yours,
Laurenz Albe

  8. Re: Error while updating a table

    sivapostgres@yahoo.com <sivapostgres@yahoo.com> — 2025-04-19T13:06:27Z

     Thanks Laurenz,
> it is 100% clear that an earlier statement in the same transaction musthave got an error.  Write your application so that it logs *every* error
that comes from the database, then you are sure to catch that error.

There is an option to log every sql statement that's executed from the application.  Unfortunately, that option was not set at that time.   Even I enable it now, I can't figure out that error.   By any chance, if I get that statement, what should I do?  What are the Steps (or documentation) to correct this issue?
Happiness Always
BKR Sivaprakash
    On Saturday 19 April, 2025 at 06:25:37 pm IST, Laurenz Albe <laurenz.albe@cybertec.at> wrote:  
 
 On Sat, 2025-04-19 at 12:38 +0000, sivapostgres@yahoo.com wrote:
> So far, I couldn't find the query that caused the error.   Is there any way to unlock it?  
> 
> Re-start, Format and fresh install of windows, etc. ?

On Windows you have to reboot, bow three times toward sunset,
say a prayer to Bill Gates, then reboot again.

No, seriously: randomly messing around with your machine rarely ever
gets you closer to a solution.

If you get the error

  current transaction is aborted, commands ignored until end of transaction block

it is 100% clear that an earlier statement in the same transaction must
have got an error.  Write your application so that it logs *every* error
that comes from the database, then you are sure to catch that error.

Also, if "log_min_messages" is "warning" (the default) or "error", you
will find the error in the PostgreSQL log.  That's all I can say to you.

Yours,
Laurenz Albe

  9. Re: Error while updating a table

    David G. Johnston <david.g.johnston@gmail.com> — 2025-04-19T13:14:54Z

    On Sat, Apr 19, 2025 at 6:06 AM sivapostgres@yahoo.com <
sivapostgres@yahoo.com> wrote:

> By any chance, if I get that statement, what should I do?
>

Read it.

What are the Steps (or documentation) to correct this issue?
>
>
Impossible to say until the statement is read.

David J.

  10. 回复： Error while updating a table

    Andy Huang <258790519@qq.com> — 2025-04-19T13:24:11Z

    I suggest to correct you application code first to log this error out in your application, the following picture is our code which can catch these kind of issue, and log it in the application log files.









------------------&nbsp;原始邮件&nbsp;------------------
发件人:                                                                                                                        "David G. Johnston"                                                                                    <david.g.johnston@gmail.com&gt;;
发送时间:&nbsp;2025年4月19日(星期六) 晚上9:14
收件人:&nbsp;"sivapostgres@yahoo.com"<sivapostgres@yahoo.com&gt;;
抄送:&nbsp;"Pgsql-general"<pgsql-general@postgresql.org&gt;;"Laurenz Albe"<laurenz.albe@cybertec.at&gt;;
主题:&nbsp;Re: Error while updating a table



On Sat, Apr 19, 2025 at 6:06 AM sivapostgres@yahoo.com <sivapostgres@yahoo.com&gt; wrote:

By any chance, if I get that statement, what should I do?&nbsp; 




Read it.


What are the Steps (or documentation) to correct this issue?






Impossible&nbsp;to say until the statement is read.


David J.

  11. Re: Error while updating a table

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-04-19T19:42:03Z

    On 2025-04-19 13:06:27 +0000, sivapostgres@yahoo.com wrote:
> Thanks Laurenz,
> > it is 100% clear that an earlier statement in the same transaction must
> > have got an error.  Write your application so that it logs *every* error
> > that comes from the database, then you are sure to catch that error.
> 
> There is an option to log every sql statement that's executed from the
> application.  Unfortunately, that option was not set at that time.

You don't have to log every sql statement. Just the ones which cause
errors. As Laurenz mentioned, that happens automatically with the
default settings.

> Even I enable it now, I can't figure out that error.   

Just search the logs for the string "ERROR".

> By any chance, if I get that statement, what should I do?

1) Read the error message.
2) Figure out what caused the error
3) Fix the problem

        hjp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"