I finally can reproduce the issue with a small ESQL/C written program
for that purpose. I could attach here the source, but even seeing its
printouts, all is perhaps clear:
the pgm does an INSERT, after this the row is there and can be seen
with SELECT; than I CLOSE a non existing cursor, which rolls back
the INSERTed data:
./embedded
tstint: 11073
INSERT done
SELECT done
SELECT: tstint: 11073 tstchar25: [hello]
CLOSE "foo_bar" done
SQL error: cursor "foo_bar" does not exist on line 57
SQL error: current transaction is aborted, commands ignored until end of transaction block on line 61
SELECT done
SELECT: tstint: 0 tstchar25: []
COMMIT done
SELECT done
SELECT: tstint: 0 tstchar25: []
ROLLBACK done
SELECT done
SELECT: tstint: 0 tstchar25: []
i.e. not the ROLLBACK removes the data, but the CLOSE of non existing
CURSOR.
We have in our huge application server and its DB-layer places where we close
in advance a CURSOR to be sure that its CREATE will not cause any
problem because it is existing. Until yesterday we thought that the
raised -400 error, like
[1471] [12.05.2020 15:48:50:477]: ecpg_check_PQresult on line 939: bad response - ERROR: cursor "adm_partab_seq" does
notexist
[1471] [12.05.2020 15:48:50:477]: raising sqlstate 34000 (sqlcode -400): cursor "adm_partab_seq" does not exist on line
939
could be overcome with the COMMIT without loosing the inserted data.
Main question: How can we ask the PostgreSQL server if a CURSOR 'foo_bar'
(still) does exist or not?
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
