Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

Good day, Adrian.
I get the error inside the program by catching the exception and logging
it with diagnostic info provided by the DVM (a runtime interpreter
similar in concept to a JVM) that embed the PG driver.

This is the fragment of the source code  where the error occurs ...
comments are in blue color

# composing the query string with hardcoded WHERE part. In other cases
the query is parametric and values are passed with the FOREACH (FOREACH
... USING <argument list> ...)
LET l_qry = "SELECT * FROM quote_forn "||
            " WHERE evento_id  = "||t_qtf.evento_id||
            " and bt_id = "||idhh_bt

PREPARE q_stm FROM l_qry
DECLARE c_cur CURSOR FOR q_stm
TRY
    LET c_qtf = ar_qtforn.getLength()
    LET j = c_qtf

# FOREACH is a code structure who simplifies the OPEN/FETCH/CLOSE
structure. It is translated at runtime to OPEN cursor ... FETCH rows ...
CLOSE cursor.
    FOREACH c_cur INTO r_qtf.*
        FOR v = 1 TO c_qtf
            IF ... ... THEN ... ... END IF
        END FOR
        LET j = j + 1
        LET ar_qtforn[j].id          = r_qtf.id
        ... ...
        ... ...
    END FOREACH

CATCH
    LET str_msg = "Some error message ... event: ", t_qtf.evento_id, "
has failed. ", SQLERRMESSAGE, " - SQLSTATE: ", SQLSTATE # SQLSTATE is a
predefined variable containing the native PG SQLSTATE
# Write an application log line (the one I have sent to you inside the
cross log comparison along the timeline)
    CALL GesLog(NULL, 1, str_msg)

END TRY
... ...
... ...

After that the program tries a single-row SELECT on a table just to
check if it is still able to get data from DB, and it fails with
*SQLSTATE XX000* (what you see into the log fragment one millisecond
after the server log reports error *08003*).

In some cases the query can be very complex and in other very simple, it
seems doesn't matters.
As you can see the code is very simple, but 999 times it works fine and
one time it fail returning *XX001* for minutes, and in the meantime a
lot of SQL operations including FETCH cursor works well.

I can't give you info on what the DVM does at low level, but I can send
you the distinct full session log fragment at server side, where it is
quite easy to understand how the DVM translates the program's SQL
queries end what PostgreSQL does.
May I give you any other info ?
Do you think it can be useful to include in this thread the 4Js Suppory
guys ?

Thanks again and best regards.
Enrico

*Enrico Schenone*
Software Architect

*Cleis Tech s.r.l.* - www.gruppocleis.it
Sede di Genova, Via Paolo Emilio Bensa, 2 - 16124 Genova, ITALY
Tel: +39-0104071400   Fax: +39-0104073276
Mobile: +39-320 7709352
E-mail: eschenone(at)cleistech(dot)it

<https://gruppocleis.it><https://ibm.biz/BdqAJh>

<https://ibm.biz/BdqAJh>
<https://ibm.biz/BdqAJh>
Il 19/12/24 17:34, Adrian Klaver ha scritto:
> On 12/18/24 23:52, Enrico Schenone wrote:
>> Good day, Adrian.
>> First of all I thank-you for you answer.
>
>> At the time the error occurs, dozens of other SQL sessions are active
>> & running on DB server, and no-one is reporting any error at-all (not
>> only fetch errors).
>> This happens sometimes also in system with no (or low) stress
>> situations.
>>
>> One of things I don't understand is why at client side I get the
>> XX001 error on the FETCH (normally the first fetch) while at server
>> side I heve no error related to the fetch forward ?
>
> Where are you fetching the client error messages from?
>
>> Another is why in the meantime no other client application report an
>> error, considering that there may be several parallel instances of
>> the same client application ?
>> And finally why after seconds or minutes the same process newly
>> instantiated works with no more errors ?
>
> Answers to this and the below is going to need the client code.
>
>>
>> I can suppose that the client closes the connection once got the
>> XX001 error, but I can't say why it receives this error while it is
>> not reported at server side and not block i/o error is reported.
>> Is it a false positive or what ?
>>
>> Four Js support said <We use the standard C API provided by the DB
>> vendor. In the case of PostgreSQL, we use the C API client -
>> https://www.postgresql.org/docs/current/libpq.html >
>>
>> At client side I have installed the following PostgreSQL packages ...
>>
>>      postgresql-client-16:amd64/jammy-pgdg 16.5-1.pgdg22.04+1
>> upgradeable to 16.6-1.pgdg22.04+1
>>      postgresql-client-common:all/jammy-pgdg 262.pgdg22.04+1
>> upgradeable to 267.pgdg22.04+1
>>
>> Best regards.
>> Enrico
>>
>>
>> *Enrico Schenone*
>> Software Architect
>>
>> *Cleis Tech s.r.l.* - www.gruppocleis.it
>> Sede di Genova, Via Paolo Emilio Bensa, 2 - 16124 Genova, ITALY
>> Tel: +39-0104071400   Fax: +39-0104073276
>> Mobile: +39-320 7709352
>> E-mail: eschenone(at)cleistech(dot)it
>>
>> <https://gruppocleis.it><https://ibm.biz/BdqAJh>
>>
>> <https://ibm.biz/BdqAJh>
>> <https://ibm.biz/BdqAJh>
>> Il 19/12/24 00:11, Adrian Klaver ha scritto:
>>> On 12/17/24 08:30, Enrico Schenone wrote:
>>>> Good day.
>>>> My name is Enrico Schenone, from Genoa, Italy.
>>>> I'm a software achitect working at Cleis Tech - Genoa - Italy  -
>>>> http://gruppocleis.it
>>>> Me and my team are using PostgreSQL v12 to v16 on Debian 10-12 and
>>>> Ubuntu Server 22.04 LTS with no-cluster configuration.
>>>> Our applications are developed with 4Js Genero platform (classified
>>>> as LCAP) - https://4js.com
>>>>
>>>> I whish to report an issue where I can't say if it happens at
>>>> server or client side (or both as well).
>>>
>>> This:
>>>
>>> "unexpected EOF on client connection "
>>>
>>> makes me believe this is on client side.
>>>
>>> To be clear the client is running on Ubuntu Server 22.04, correct?
>>>
>>> Have you looked at the OS system log for relevant entries at the
>>> time the error occurs?
>>>
>>> If so what are they?
>>>
>>> This only happens in production environment, is there anything in it
>>> that is materially different from where you ran the test below?
>>>
>>>
>>>> Hoping you can help me or address to someone who can do it.
>>>> Thanks in advance.
>>>> Enrico
>>>> --
>>>>
>>>> *Enrico Schenone*
>>>> Software Architect
>>>>
>>>
>>
>