Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

I'm getting a strange error message when I try to insert a date using the view/edit grid in pgadmin. See below. I've tried quotes, no quotes and various formats. The column type is clearly "date."

[cid:4ff69cfe-2efa-4636-8dde-6230512706f7]

Mark Brady, Ph.D.
Deputy Chief Data Officer, TRMC
amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>
________________________________
From: Enrico Schenone <eschenone(at)cleistech(dot)it>
Sent: Friday, December 20, 2024 10:02 AM
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Cc: Massimo Catti <mcatti(at)cleistech(dot)it>; Livio Pizzolo <lpizzolo(at)cleistech(dot)it>
Subject: Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

Hi, Adrian.
Today I have collected a tcpdump at client side with communications
between application server and db server while the issue was occurring
one time per second on another program.
I send you two files.
The first one is a zipped tarball (.tgz) containing a text
representation of the tcpdump starting at point where it reports the
declaration of the failing cursor ("cu4" as you can see in the first
line of the file) and subsequent fetch. Consider that the client
application log detected the XX001 error on the first FETCH of the
cursor at 2024-12-20 12:17:35.175
The second file (zipped tarball .tgz) is too big to be sent as
attachment, so I provide a link where it can be downloaded. It is the
fraction of tcpdump recorded during the program failure (occurred
several times). It is in .pcap format so it is possible to open it with
Wireshark or tcpdump -A -r
Anyone interested can download it at
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcleislabs.cleistech.it%2Fdownloads%2Ftcpdump_out009.pcap.tgz&data=05%7C02%7C%7Cfe8da7a507744c7842d608dd210ec77b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638703069888918551%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=KrfcnJUpuwV8CqzzkPvOf6SHgewaxFB%2FjuFm8vSDkgM%3D&reserved=0<https://cleislabs.cleistech.it/downloads/tcpdump_out009.pcap.tgz>

Consider that during the dump several different cursor was declared with
the name "cu4", but the one failing is the one of the first line.
Maybe an expert (I'm not so expert) can see if the disconnection is
really made by the client and/or if the data returned by the server are
really corrupted as per XX001 SQLSTATE.

Best regards.
Enrico

Il 19/12/24 22:47, Adrian Klaver ha scritto:
>
>
> On 12/19/24 11:40 AM, Enrico Schenone wrote:
>> Hello, my answers in line along your message ...
>> Thanks a lot again.
>>
>> Enrico
>>
>
>>> On 12/19/24 10:11, Enrico Schenone wrote:
>>>> 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.
>>>
>
>> The 4Js DVM (Dynamic Virtual Machine) is that one
>> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2F4js.com%2Fonline_documentation%2Ffjs-gas-manual-html%2Findex.html%23gas-topics%2Fc_gas_what_is_dvm.html&data=05%7C02%7C%7Cfe8da7a507744c7842d608dd210ec77b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638703069888937436%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=Hy8B3dQj2ugrOZOy0sLlCZDRPlsSsfGPrpwz1Jjbxwg%3D&reserved=0<https://4js.com/online_documentation/fjs-gas-manual-html/index.html#gas-topics/c_gas_what_is_dvm.html>
>>
>>> In other words an Android client?
>>>
>> No, it is a runtime interpreter for Linux, Windows, IBM AIX, macOS
>> and other unix-like OSs. It ensures the portability of 4Js Genero
>> compiled programs (p-code) on several OS platforms.
>> 4Js Genero is a Low Code Application Platform. The programming
>> language, named "BDL - Business Development Language", is an
>> evolution of the Informix-4gl.
>> Compiled programs needs a runtime interpreter (DVM) to be executed.
>> The DVM embeds at low-level the DB drivers provided by several vendors,
>
> From previous post you mentioned:
>
> "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 "
>
> So are they building their own driver over libpq?
>
>> and at BDL high level the application program can easily connect to
>> the major DBs on the market thanks to its ODI (Open Database Interface).
>>>> 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.
>>>
>>> That might be useful.
>>>
>> Please take a look to the attached text file, that is the full
>> failing session log (filtered from the debug5 PostgreSQL server log).
>
> This is where it falls off the rails, but I can't see why?:
>
> 2024-12-16 17:27:14.406 CET [2214722] cleistech(at)hh24odds_prod -
> 192.168.16.17900000676054e0.21cb42 LOCATION: ShowTransactionStateRec,
> xact.c:5510
> 2024-12-16 17:27:14.406 CET [2214722] cleistech(at)hh24odds_prod -
> 192.168.16.17900000676054e0.21cb42 STATEMENT: fetch forward 50 from cu6
> 2024-12-16 17:27:14.406 CET [2214722] cleistech(at)hh24odds_prod -
> 192.168.16.17900000676054e0.21cb42 LOG: 00000: statement: fetch
> forward 50 from cu6
> 2024-12-16 17:27:14.406 CET [2214722] cleistech(at)hh24odds_prod -
> 192.168.16.17900000676054e0.21cb42 LOCATION: exec_simple_query,
> postgres.c:1073
> 2024-12-16 17:27:14.406 CET [2214722] cleistech(at)hh24odds_prod -
> 192.168.16.17900000676054e0.21cb42 DEBUG: 00000: CommitTransaction(1)
> name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid:
> 0/1/0
>
> 2024-12-16 17:27:14.406 CET [2214722] cleistech(at)hh24odds_prod -
> 192.168.16.17900000676054e0.21cb42 LOCATION: ShowTransactionStateRec,
> xact.c:5510
> 2024-12-16 17:27:14.406 CET [2214722] cleistech(at)hh24odds_prod -
> 192.168.16.17900000676054e0.21cb42 STATEMENT: fetch forward 50 from cu6
> 2024-12-16 17:27:14.407 CET [2214722] cleistech(at)hh24odds_prod -
> 192.168.16.17908006676054e0.21cb42 LOG: 08006: could not receive data
> from client: Connessione interrotta dal corrispondente
>
>>>> Thanks again and best regards.
>>>> Enrico
>
>