Re: How to reset a server error '25P02 in_failed_sql_transaction'

[Guillaume Lelarge]

Le mar. 31 déc. 2019 à 06:55, Matthias Apitz  a écrit :

>
> Hello,
>
> Due to a wrong human input in the GUI of our application our
> application server, from the point of view of the PostgreSQL server it
> is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL
> server, here from our own debug logging the command and the error
> message of the server:
>
>
> posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum
> WHERE desk>='*2019' AND feldnr IN ( 2 )   ORDER BY desk ASC , feldnr ASC
> posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum  FROM
> :select_anw;
>  sqlca 
> sqlcode: -400
> sqlerrm.sqlerrml: 61
> sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918
> ...
>
> All subsequent correct (SELECT ...) statements get rejected with, for
> example:
>
>
> ...
> posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum
> WHERE desk>='31.12.1900' AND feldnr IN ( 2 )   ORDER BY desk ASC , feldnr
> ASC
> posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum  FROM
> :select_anw;
>  sqlca 
> sqlcode: -400
> sqlerrm.sqlerrml: 105
> sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until
> end of transaction block on line 918
> sqlerrd: 0 0 0 0 0 0
> sqlwarn: 0 0 0 0 0 0 0 0
> sqlstate: 25P02
> posSqlError===
> ...
>
> Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN
> TRANSACTION;'
>
> What is the correct way to abort the "transaction" as requested by the PG
> server to return to normal operations?
>
>
You need to issue a ROLLBACK. then you'll be able to open another
transaction.


-- 
Guillaume.

RE: How to reset a server error '25P02 in_failed_sql_transaction'

[Patrick FICHE]

Hi,

Which behavior are you looking for ?
If you want the following statements to succeed, I guess that you don't want to 
be in a transaction context.
In this case, you should have a look at the following link : 
https://www.postgresql.org/docs/11/ecpg-commands.html
By default, the AUTOCOMMIT is OFF but you can set it to ON and it should solve 
your issue as only explicit transactions will keep a transaction context.

Regards,

Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96 



-Original Message-
From: Matthias Apitz  
Sent: Tuesday, December 31, 2019 6:55 AM
To: pgsql-general@lists.postgresql.org
Subject: How to reset a server error '25P02 in_failed_sql_transaction'


Hello,

Due to a wrong human input in the GUI of our application our application 
server, from the point of view of the PostgreSQL server it is the PostgreSQL 
client, issues a broken ESQL/C command to the PostgreSQL server, here from our 
own debug logging the command and the error message of the server:


posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum WHERE 
desk>='*2019' AND feldnr IN ( 2 )   ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum  FROM 
:select_anw;  sqlca 
sqlcode: -400
sqlerrm.sqlerrml: 61
sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918 ...

All subsequent correct (SELECT ...) statements get rejected with, for example: 


...
posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum WHERE 
desk>='31.12.1900' AND feldnr IN ( 2 )   ORDER BY desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum  FROM 
:select_anw;  sqlca 
sqlcode: -400
sqlerrm.sqlerrml: 105
sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until end of 
transaction block on line 918
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 25P02
posSqlError===
...

Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN 
TRANSACTION;'

What is the correct way to abort the "transaction" as requested by the PG 
server to return to normal operations?

Thanks

matthias

--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 
Public GnuPG key: http://www.unixarea.de/key.pub

Pg import access

[Sonam Sharma]

How to grant table import access in postgres ?

Re: Pg import access

[Adrian Klaver]

On 12/31/19 4:41 AM, Sonam Sharma wrote:

How to grant table import access in postgres ?


Define import.

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Need auto fail over cluster solution for PostGres

[Girish Kumar]

If it's on Windows, does Windows Fail over cluster supports Postgres auto 
failover? I need a solution where secondary node becomes primary when primary 
fails and vice versa automatically without any manual intervention ( this is 
exactly how AAG works with SQL server on Windows)

Regards,
Girish kumar

Get Outlook for Android


From: Fabrízio de Royes Mello 
Sent: Monday, December 30, 2019 10:15:09 PM
To: Girish Kumar 
Cc: pgsql-general@lists.postgresql.org 
Subject: Re: Need auto fail over cluster solution for PostGres


Em seg., 30 de dez. de 2019 às 13:30, Girish Kumar 
mailto:girish_ku...@live.in>> escreveu:
>
> Hello,
>
> I need to setup an auto fail over cluster for Postgres similar to Always 
> Available Group(AAG) provided by Microsoft for SQL Server. Any suggestions? 
> Prefer open source solutions(On Ubuntu)
>

Some FOSS options:

https://github.com/zalando/patroni
https://github.com/sorintlab/stolon
https://github.com/ClusterLabs/PAF

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: How to reset a server error '25P02 in_failed_sql_transaction'

[Ron]

On 12/31/19 3:40 AM, Guillaume Lelarge wrote:
Le mar. 31 déc. 2019 à 06:55, Matthias Apitz > a écrit :



Hello,

Due to a wrong human input in the GUI of our application our
application server, from the point of view of the PostgreSQL server it
is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL
server, here from our own debug logging the command and the error
message of the server:


posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from
titel_datum WHERE desk>='*2019' AND feldnr IN ( 2 )  ORDER BY desk ASC
, feldnr ASC
posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum 
FROM :select_anw;
 sqlca 
sqlcode: -400
sqlerrm.sqlerrml: 61
sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918
...

All subsequent correct (SELECT ...) statements get rejected with, for
example:


...
posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from
titel_datum WHERE desk>='31.12.1900' AND feldnr IN ( 2 )   ORDER BY
desk ASC , feldnr ASC
posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum 
FROM :select_anw;
 sqlca 
sqlcode: -400
sqlerrm.sqlerrml: 105
sqlerrm.sqlerrmc: current transaction is aborted, commands ignored
until end of transaction block on line 918
sqlerrd: 0 0 0 0 0 0
sqlwarn: 0 0 0 0 0 0 0 0
sqlstate: 25P02
posSqlError===
...

Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN
TRANSACTION;'

What is the correct way to abort the "transaction" as requested by the PG
server to return to normal operations?


You need to issue a ROLLBACK. then you'll be able to open another transaction.


But how do you issue a ROLLBACK to a different pid?

--
Angular momentum makes the world go 'round.

Re: How to reset a server error '25P02 in_failed_sql_transaction'

[David G. Johnston]

On Tuesday, December 31, 2019, Ron  wrote:
>
> But how do you issue a ROLLBACK to a different pid?
>

You cannot.  At that point you need to start from scratch.

 pg_terminate_backend(*pid int*)

And let the problematic app deal with losing its database connection
however it will.

David J.

Re: How to reset a server error '25P02 in_failed_sql_transaction'

[Ron]

On 12/31/19 11:29 AM, David G. Johnston wrote:
On Tuesday, December 31, 2019, Ron > wrote:


But how do you issue a ROLLBACK to a different pid?


You cannot.  At that point you need to start from scratch.

pg_terminate_backend(/pid int/)

And let the problematic app deal with losing its database connection 
however it will.


David J.


That's what I thought (and privately told OP).


--
Angular momentum makes the world go 'round.

How to shorten a chain of logically replicated servers

[Mike Lissner]

Hi, I'm trying to figure out how to shorten a chain of logically
replicating servers. Right now we have three servers replicating like
so:

A --> B --> C

And I'd like to remove B from the chain of replication so that I only have:

A --> C

Of course, doing this without losing data is the goal. If the
replication to C breaks temporarily, that's fine, so long as all the
changes on A make it to C eventually.

I'm not sure how to proceed with this. My best theory is:

1. In a transaction, DISABLE the replication from A to B and start a
new PUBLICATION on A that C will subscribe to in step ③ below. The
hope is that this will simultaneously stop sending changes to B while
starting a log of new changes that can later be sent to C.

2. Let any changes queued on B flush to C. (How to know when they're
all flushed?)

3. Subscribe C to the new PUBLICATION created in step ①. Create the
subscription with copy_data=False. This should send all changes to C
that hadn't been sent to B, without sending the complete tables.

4. DROP all replication to/from B (this is just cleanup; the incoming
changes to B were disabled in step ①, and outgoing changes from B were
flushed in step ②).

Does this sound even close to the right approach? Logical replication
can be a bit finicky, so I'd love to have some validation of the
general approach before I go down this road.

Thanks everybody and happy new year,

Mike