type SERIAL in C host-struct
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!
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
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!"
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]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; array: no ...
[26301]: ecpg_get_data on line 60: RESULT: освобождение offset: 81; 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
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 withECPGdebug(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:47:263]: ecpg_finish: connection newsisis closed
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
....
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
[18328]: [12.11.2019 18:38:47:263]: ecpg_finish: connection newsisis closed
[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