Postgres Pro
Downloads
Home   >   mailing lists

Thread: type SERIAL in C host-struct

type SERIAL in C host-struct

From
Matthias Apitz
Date:
Hello,

We're struggling with the following problem (here show in a simplified
case).

We have in a PG 11.4 database a table with two columns: SERIAL, VARCHAR(11).

In the ESQL/C pgm the code is:

        EXEC SQL BEGIN DECLARE SECTION;
        ...
        struct {
                int ser;
                char name [11];
        } host_struct;
        EXEC SQL END DECLARE SECTION;

an INSERT with

        strcpy(host_struct.name, "Sigrid");
        host_struct.ser = 0;

        EXEC SQL INSERT INTO lina VALUES (:host_struct);

works but, sets the SERIAL column to 0;

an INSERT with

        EXEC SQL INSERT INTO lina VALUES (DEFAULT, :host_struct.name);

works correctly and increments the SERIAL on every INSERT:

printf "select * from lina WHERE name = 'Sigrid';\n" | psql -Usisis -d newsisis
 lid |         name
-----+----------------------
  28 | Sigrid
  29 | Sigrid
   0 | Sigrid   <*********** this was with host_struct.ser = 0;
  30 | Sigrid
  31 | Sigrid

How the value for host_struct.ser must be given, as we do not want to
name all the struct members in the INSERT statement(s), the real structs
have plenty much columns, some ~30.

Thanks

    matttias
-- 
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!

Re: type SERIAL in C host-struct

From
Matthias Apitz
Date:
El día jueves, noviembre 07, 2019 a las 12:39:39p. m. +0100, Matthias Apitz escribió:

>
> Hello,
>
> We're struggling with the following problem (here show in a simplified
> case).
>
> We have in a PG 11.4 database a table with two columns: SERIAL, VARCHAR(11).
>
> In the ESQL/C pgm the code is:
>
>         EXEC SQL BEGIN DECLARE SECTION;
>         ...
>         struct {
>                 int ser;
>                 char name [11];
>         } host_struct;
>         EXEC SQL END DECLARE SECTION;
>
> an INSERT with
>
>         strcpy(host_struct.name, "Sigrid");
>         host_struct.ser = 0;
>
>         EXEC SQL INSERT INTO lina VALUES (:host_struct);
>
> works but, sets the SERIAL column to 0;
>
> an INSERT with
>
>         EXEC SQL INSERT INTO lina VALUES (DEFAULT, :host_struct.name);
>
> works correctly and increments the SERIAL on every INSERT:

At the end of the day we came up with the following solution:

        strcpy(host_struct.name, "Sigrid");
        EXEC SQL select nextval('lina_lid_seq') into :host_struct.ser;

        EXEC SQL INSERT INTO lina VALUES ( :host_struct );

which seems to work fine. Any comments about side effects?
The layout of the table 'lina' is ( serial lid, varchar name )

Thanks,

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

"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut"
"Believe little, scrutinise all, think by your own: How see through manipulations"
ISBN-10: 386489218X
Attachment

Re: type SERIAL in C host-struct

From
"Peter J. Holzer"
Date:
On 2019-11-07 20:14:47 +0100, Matthias Apitz wrote:
> At the end of the day we came up with the following solution:
>
>         strcpy(host_struct.name, "Sigrid");
>         EXEC SQL select nextval('lina_lid_seq') into :host_struct.ser;
>
>         EXEC SQL INSERT INTO lina VALUES ( :host_struct );
>
> which seems to work fine. Any comments about side effects?

You are performing two queries instead of one, so you have to wait for
one extra round trip. Not a problem if client and server are on the same
network or you insert a few host_structs per second. May be a problem if
client and server are in different datacenters and you want to do
hundreds of inserts per second.

        hp

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

Re: type SERIAL in C host-struct

From
Matthias Apitz
Date:
El día sábado, noviembre 09, 2019 a las 04:31:53p. m. +0100, Peter J. Holzer escribió:

> On 2019-11-07 20:14:47 +0100, Matthias Apitz wrote:
> > At the end of the day we came up with the following solution:
> > 
> >         strcpy(host_struct.name, "Sigrid");
> >         EXEC SQL select nextval('lina_lid_seq') into :host_struct.ser;
> > 
> >         EXEC SQL INSERT INTO lina VALUES ( :host_struct );        
> > 
> > which seems to work fine. Any comments about side effects?
> 
> You are performing two queries instead of one, so you have to wait for
> one extra round trip. Not a problem if client and server are on the same
> network or you insert a few host_structs per second. May be a problem if
> client and server are in different datacenters and you want to do
> hundreds of inserts per second.

Thanks for the feedback. Client and server run both on the same Linux
host. I found also the debug log of ESQL/C i.e. how to enable it with

ECPGdebug(int on, FILE *stream);

The resulting log is really fine for debugging our code:

[26301]: ECPGdebug: set to 1
[26301]: ECPGconnect: opening database newsisis on localhost port 5432  for user sisis
[26301]: prepare_common on line 49: name s_statename; query: "SELECT myint, mychar, mydate, myvchar, myblob FROM
mytypes;"
[26301]: ecpg_execute on line 52: query: declare c_statename cursor for SELECT myint, mychar, mydate, myvchar, myblob
FROMmytypes;; with 0 parameter(s) on connection newsisis
 
[26301]: ecpg_execute on line 52: using PQexec
[26301]: ecpg_process_output on line 52: OK: DECLARE CURSOR
[26301]: ecpg_execute on line 60: query: fetch in c_statename; with 0 parameter(s) on connection newsisis
[26301]: ecpg_execute on line 60: using PQexec
[26301]: ecpg_process_output on line 60: correctly got 1 tuples with 5 fields
[26301]: ecpg_get_data on line 60: RESULT: 1 offset: 4; array: no
[26301]: ecpg_get_data on line 60: RESULT: char offset: 8; array: no
[26301]: ecpg_get_data on line 60: RESULT: 08.05.1945 offset: 8; array: no
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no
...

What is missing are timestamps in enough precision. I will add this to
the code in postgresql-11.4/src/interfaces/ecpg/ecpglib/misc.c where the
implementation of ecpg_log() is.

Thanks

    matthias

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

"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut"
"Believe little, scrutinise all, think by your own: How see through manipulations"
ISBN-10: 386489218X

Re: type SERIAL in C host-struct

From
Matthias Apitz
Date:
El día sábado, noviembre 09, 2019 a las 07:45:31p. m. +0100, Matthias Apitz escribió:

> Thanks for the feedback. Client and server run both on the same Linux
> host. I found also the debug log of ESQL/C i.e. how to enable it with
>
> ECPGdebug(int on, FILE *stream);
>
> The resulting log is really fine for debugging our code:
>
> ...

I added time stamps to the logging as:

/tmp/ecpg.log:
...
[18328] [12.11.2019 18:38:44:637]: ecpg_execute on line 120: query: insert into mytypes values ( $1  , $2  , $3  , $4
,$5  ); with 5 parameter(s) on connection newsisis 
[18328] [12.11.2019 18:38:46:881]: ecpg_execute on line 120: using PQexecParams
[18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 1 = 99
[18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 2 =
[18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 3 = 2000-01-01
[18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 4 =
[18328] [12.11.2019 18:38:46:881]: ecpg_free_params on line 120: parameter 5 = \x6c696e6520....
....
[18328] [12.11.2019 18:38:47:084]: ecpg_process_output on line 120: OK: INSERT 0 1
[18328] [12.11.2019 18:38:47:084]: ECPGtrans on line 126: action "commit"; connection "newsisis"
[18328] [12.11.2019 18:38:47:260]: deallocate_one on line 0: name s_statename
[18328] [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed

The above shows an INSERT into a 'bytea' column of ~36 MByte (yes we
have such large XML data for billing/accounting processes in the
database). It takes ~0.2 sec to insert such a monster row.

On INSERT/UPDATE the column data (XML) comes down from the application
layer as a 'char *' pointer. I know with strlen(src) how long it is, I double
its length and add 3 (for the "\x" in front and the '\0' at the end) and malloc
the space for the host variable for INSERT/UPDATE and translate the
octets to hex representation.

When FETCH-ing such a column I do not know the resulting length of the
bytea data for doing a malloc(). I could do before any FETCH a

SELECT octet_length(myblob) FROM mytypes;

but I do not know how expensive this would be.

Any other idea to predict the needed space for the host variable on
FETCH?

    matthias


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

"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut"
"Believe little, scrutinise all, think by your own: How see through manipulations"
ISBN-10: 386489218X
Attachment