Re: accessing postgres from c++

We are developing a huge Library Management System with some 400 tables
and which is/was running on many UNIX derivates (SINIX, HP-UX, AIX,
SunOS, Linux) and all kind of databases one can imagine (INFORMIX,
Oracle, Sybase and now PostgreSQL). The system is written in C, C++ and
Perl and the C/C++ dblayer uses ESQL/C which made it highly portable between
the above mentioned DBSes. The PostgreSQL ESQL/C has an unbeatable
logging feature which logs all ESQL/C operations with their arguments
and results, like this:

[20746] [20.06.2022 11:57:19:817]: ECPGconnect: opening database test01 on bvbzflltdb1 port 5432 with options application_name=SunRise DBCALL V7.2 (pid=20746) for user sisis
[20746] [20.06.2022 11:57:19:821]: ecpg_execute on line 822: query: select current_date; with 0 parameter(s) on connection test01
[20746] [20.06.2022 11:57:19:821]: ecpg_execute on line 822: using PQexec
[20746] [20.06.2022 11:57:19:821]: ecpg_process_output on line 822: correctly got 1 tuples with 1 fields
[20746] [20.06.2022 11:57:19:821]: ecpg_get_data on line 822: RESULT: 20.06.2022 offset: 80; array: no
[20746] [20.06.2022 11:57:19:822]: prepare_common on line 936: name sid_sisisinst; query: "SELECT ctid, * from sisisinst WHERE version  = $1"
[20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1174: query: declare sisisinst_seq cursor with hold for SELECT ctid, * from sisisinst WHERE version  = $1; with 1 parameter(s) on connection test01
[20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1174: using PQexecParams
[20746] [20.06.2022 11:57:19:823]: ecpg_free_params on line 1174: parameter 1 = V7.2
[20746] [20.06.2022 11:57:19:823]: ecpg_process_output on line 1174: OK: DECLARE CURSOR
[20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1585: query: fetch sisisinst_seq; with 0 parameter(s) on connection test01
[20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1585: using PQexec
[20746] [20.06.2022 11:57:19:823]: ecpg_process_output on line 1585: correctly got 1 tuples with 7 fields
[20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: (0,35) offset: 19; array: no
[20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: 22.09.2021 offset: 137; array: no
[20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616       offset: 137; array: no
...

The only missing thing in this logging was the PID and exact timestamp
of the operation. The latter makes performance analysing very easy.
We added this (PID and timestamp) to the sources of the ecpglib:

postgresql-14.1/src/interfaces/ecpg/ecpglib/misc.c

I could share the diff for maybe to be included in the original
sources.

        matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!