pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedBug #904: Deallocating of prepared statement in ECPG at COMMIT
6+ messages / 4 participants
[nested] [flat]
* Bug #904: Deallocating of prepared statement in ECPG at COMMIT
@ 2003-02-27 11:35 pgsql-bugs@postgresql.org
0 siblings, 2 replies; 6+ messages in thread
From: pgsql-bugs@postgresql.org @ 2003-02-27 11:35 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
Jiri Langr (jiri.langr@konero.cz) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Deallocating of prepared statement in ECPG at COMMIT
Long Description
When I prepare a statement in ECPG it lives only to first explicit transaction block. At the COMMIT it seems to be deallocated.
It is not good behavior because the main advantage of prepared statements is once prepare and many times execute!
Sample Code
ESQL/C code
********************************************
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define DBNAME "langr"
/* Testing of any SQL error, printing error message and exit */
void test(int l_exit) {
if(sqlca.sqlcode < 0) {
printf("Error: %s\n", sqlca.sqlerrm.sqlerrmc);
if(l_exit) {
EXEC SQL DISCONNECT;
exit(-1);
}
}
}
/* Demonstration of error concerning the DEALLOCATEing of PREPARED statement
at the COMMIT in PostgreSQL ESQL/C */
int main() {
EXEC SQL BEGIN DECLARE SECTION;
char dbname[64] = DBNAME;
char sql_string[1000];
int l_id;
char l_name[10 + 1];
EXEC SQL END DECLARE SECTION;
ECPGdebug(1, stderr);
/* Creating DB and connecting to them */
/* strcpy(dbname, DBNAME); */
EXEC SQL CONNECT TO :dbname;
test(1);
EXEC SQL SET AUTOCOMMIT TO ON;
test(1);
/* Creating table */
EXEC SQL DROP TABLE test;
test(0); /* no exiting because it has not to exist yet */
EXEC SQL CREATE TABLE test (
id INTEGER NOT NULL,
name CHAR(10)
);
test(1);
/* Preparing INSERT statement */
strcpy(sql_string, "INSERT INTO test VALUES(?, ?)");
EXEC SQL PREPARE prep_ins FROM :sql_string;
test(1);
/* Inserting several rows in one transaction */
EXEC SQL BEGIN;
test(1);
for(l_id = 0; l_id < 3; l_id++) {
switch(l_id) {
case 0: strcpy(l_name, "First"); break;
case 1: strcpy(l_name, "Second"); break;
case 2: strcpy(l_name, "Third"); break;
}
EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
test(1);
}
EXEC SQL COMMIT;
test(1);
/* It did work well, but the statement was DEALLOCATED automatically - WHY?? */
/* Inserting next line in separate transaction */
l_id = 3;
strcpy(l_name, "Fourth");
EXEC SQL BEGIN;
test(1);
EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
EXEC SQL COMMIT;
test(1);
EXEC SQL BEGIN;
test(1);
EXEC SQL DECLARE cur_test CURSOR FOR SELECT * FROM test;
test(1);
EXEC SQL OPEN cur_test;
test(1);
while(1) {
EXEC SQL FETCH cur_test INTO :l_id, :l_name;
test(1);
if(sqlca.sqlcode == ECPG_NOT_FOUND) break;
printf("%d: %s\n", l_id, l_name);
}
EXEC SQL CLOSE cur_test;
test(1);
EXEC SQL COMMIT;
test(1);
EXEC SQL FREE prep_ins;
test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
EXEC SQL DROP TABLE test;
test(1);
EXEC SQL DISCONNECT;
test(1);
return(0);
}
SQL code doing the same funcionality and work well!!
*****************************************************
/* Demonstration of the same functionality in SQL, where it DOES work well */
/* Creating table */
CREATE TABLE test (
id INTEGER NOT NULL,
name CHAR(10)
);
/* Preparing INSERT statement */
PREPARE prep_ins(INTEGER, CHAR) AS INSERT INTO TEST VALUES($1, $2);
/* Inserting several rows in one transaction */
BEGIN;
EXECUTE prep_ins (0, 'First');
EXECUTE prep_ins (1, 'Second');
EXECUTE prep_ins (2, 'Third');
COMMIT;
/* In SQL in the difference of ESQL/C the DEALLOCATION was not don, it is well *
/
/* Inserting next line in separate transaction */
BEGIN;
EXECUTE prep_ins (3, 'Fourth');
COMMIT;
/* Reading data from the table */
SELECT * FROM test;
/* Deallocating of the statement */
DEALLOCATE prep_ins;
DROP TABLE test;
No file was uploaded with this report
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug #904: Deallocating of prepared statement in ECPG at
@ 2003-03-17 21:41 Bruce Momjian <pgman@candle.pha.pa.us>
parent: pgsql-bugs@postgresql.org
1 sibling, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2003-03-17 21:41 UTC (permalink / raw)
To: jiri.langr@konero.cz; pgsql-bugs@postgresql.org; +Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Can someone comment on this bug report?
---------------------------------------------------------------------------
pgsql-bugs@postgresql.org wrote:
> Jiri Langr (jiri.langr@konero.cz) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> Deallocating of prepared statement in ECPG at COMMIT
>
> Long Description
> When I prepare a statement in ECPG it lives only to first explicit transaction block. At the COMMIT it seems to be deallocated.
>
> It is not good behavior because the main advantage of prepared statements is once prepare and many times execute!
>
> Sample Code
> ESQL/C code
> ********************************************
> #include <stdio.h>
> #include <stdlib.h>
> #include <string.h>
>
> #define DBNAME "langr"
>
> /* Testing of any SQL error, printing error message and exit */
> void test(int l_exit) {
> if(sqlca.sqlcode < 0) {
> printf("Error: %s\n", sqlca.sqlerrm.sqlerrmc);
> if(l_exit) {
> EXEC SQL DISCONNECT;
> exit(-1);
> }
> }
> }
>
> /* Demonstration of error concerning the DEALLOCATEing of PREPARED statement
> at the COMMIT in PostgreSQL ESQL/C */
> int main() {
>
> EXEC SQL BEGIN DECLARE SECTION;
> char dbname[64] = DBNAME;
> char sql_string[1000];
> int l_id;
> char l_name[10 + 1];
> EXEC SQL END DECLARE SECTION;
>
> ECPGdebug(1, stderr);
>
> /* Creating DB and connecting to them */
> /* strcpy(dbname, DBNAME); */
> EXEC SQL CONNECT TO :dbname;
> test(1);
> EXEC SQL SET AUTOCOMMIT TO ON;
> test(1);
>
> /* Creating table */
> EXEC SQL DROP TABLE test;
> test(0); /* no exiting because it has not to exist yet */
> EXEC SQL CREATE TABLE test (
> id INTEGER NOT NULL,
> name CHAR(10)
> );
> test(1);
>
> /* Preparing INSERT statement */
> strcpy(sql_string, "INSERT INTO test VALUES(?, ?)");
> EXEC SQL PREPARE prep_ins FROM :sql_string;
> test(1);
>
> /* Inserting several rows in one transaction */
> EXEC SQL BEGIN;
> test(1);
> for(l_id = 0; l_id < 3; l_id++) {
> switch(l_id) {
> case 0: strcpy(l_name, "First"); break;
> case 1: strcpy(l_name, "Second"); break;
> case 2: strcpy(l_name, "Third"); break;
> }
> EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
> test(1);
> }
> EXEC SQL COMMIT;
> test(1);
>
> /* It did work well, but the statement was DEALLOCATED automatically - WHY?? */
>
> /* Inserting next line in separate transaction */
> l_id = 3;
> strcpy(l_name, "Fourth");
> EXEC SQL BEGIN;
> test(1);
> EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
> test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
> EXEC SQL COMMIT;
> test(1);
>
> EXEC SQL BEGIN;
> test(1);
> EXEC SQL DECLARE cur_test CURSOR FOR SELECT * FROM test;
> test(1);
> EXEC SQL OPEN cur_test;
> test(1);
> while(1) {
> EXEC SQL FETCH cur_test INTO :l_id, :l_name;
> test(1);
> if(sqlca.sqlcode == ECPG_NOT_FOUND) break;
> printf("%d: %s\n", l_id, l_name);
> }
> EXEC SQL CLOSE cur_test;
> test(1);
> EXEC SQL COMMIT;
> test(1);
>
> EXEC SQL FREE prep_ins;
> test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
> EXEC SQL DROP TABLE test;
> test(1);
> EXEC SQL DISCONNECT;
> test(1);
> return(0);
> }
>
> SQL code doing the same funcionality and work well!!
> *****************************************************
>
> /* Demonstration of the same functionality in SQL, where it DOES work well */
>
> /* Creating table */
> CREATE TABLE test (
> id INTEGER NOT NULL,
> name CHAR(10)
> );
>
> /* Preparing INSERT statement */
> PREPARE prep_ins(INTEGER, CHAR) AS INSERT INTO TEST VALUES($1, $2);
>
> /* Inserting several rows in one transaction */
> BEGIN;
> EXECUTE prep_ins (0, 'First');
> EXECUTE prep_ins (1, 'Second');
> EXECUTE prep_ins (2, 'Third');
> COMMIT;
>
> /* In SQL in the difference of ESQL/C the DEALLOCATION was not don, it is well *
> /
> /* Inserting next line in separate transaction */
> BEGIN;
> EXECUTE prep_ins (3, 'Fourth');
> COMMIT;
>
> /* Reading data from the table */
> SELECT * FROM test;
>
> /* Deallocating of the statement */
> DEALLOCATE prep_ins;
> DROP TABLE test;
>
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at
@ 2003-03-19 21:31 Bruce Momjian <pgman@candle.pha.pa.us>
parent: pgsql-bugs@postgresql.org
1 sibling, 2 replies; 6+ messages in thread
From: Bruce Momjian @ 2003-03-19 21:31 UTC (permalink / raw)
To: jiri.langr@konero.cz; +Cc: pgsql-interfaces
Can someone comment on this?
---------------------------------------------------------------------------
pgsql-bugs@postgresql.org wrote:
> Jiri Langr (jiri.langr@konero.cz) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> Deallocating of prepared statement in ECPG at COMMIT
>
> Long Description
> When I prepare a statement in ECPG it lives only to first explicit transaction block. At the COMMIT it seems to be deallocated.
>
> It is not good behavior because the main advantage of prepared statements is once prepare and many times execute!
>
> Sample Code
> ESQL/C code
> ********************************************
> #include <stdio.h>
> #include <stdlib.h>
> #include <string.h>
>
> #define DBNAME "langr"
>
> /* Testing of any SQL error, printing error message and exit */
> void test(int l_exit) {
> if(sqlca.sqlcode < 0) {
> printf("Error: %s\n", sqlca.sqlerrm.sqlerrmc);
> if(l_exit) {
> EXEC SQL DISCONNECT;
> exit(-1);
> }
> }
> }
>
> /* Demonstration of error concerning the DEALLOCATEing of PREPARED statement
> at the COMMIT in PostgreSQL ESQL/C */
> int main() {
>
> EXEC SQL BEGIN DECLARE SECTION;
> char dbname[64] = DBNAME;
> char sql_string[1000];
> int l_id;
> char l_name[10 + 1];
> EXEC SQL END DECLARE SECTION;
>
> ECPGdebug(1, stderr);
>
> /* Creating DB and connecting to them */
> /* strcpy(dbname, DBNAME); */
> EXEC SQL CONNECT TO :dbname;
> test(1);
> EXEC SQL SET AUTOCOMMIT TO ON;
> test(1);
>
> /* Creating table */
> EXEC SQL DROP TABLE test;
> test(0); /* no exiting because it has not to exist yet */
> EXEC SQL CREATE TABLE test (
> id INTEGER NOT NULL,
> name CHAR(10)
> );
> test(1);
>
> /* Preparing INSERT statement */
> strcpy(sql_string, "INSERT INTO test VALUES(?, ?)");
> EXEC SQL PREPARE prep_ins FROM :sql_string;
> test(1);
>
> /* Inserting several rows in one transaction */
> EXEC SQL BEGIN;
> test(1);
> for(l_id = 0; l_id < 3; l_id++) {
> switch(l_id) {
> case 0: strcpy(l_name, "First"); break;
> case 1: strcpy(l_name, "Second"); break;
> case 2: strcpy(l_name, "Third"); break;
> }
> EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
> test(1);
> }
> EXEC SQL COMMIT;
> test(1);
>
> /* It did work well, but the statement was DEALLOCATED automatically - WHY?? */
>
> /* Inserting next line in separate transaction */
> l_id = 3;
> strcpy(l_name, "Fourth");
> EXEC SQL BEGIN;
> test(1);
> EXEC SQL EXECUTE prep_ins USING :l_id, :l_name;
> test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
> EXEC SQL COMMIT;
> test(1);
>
> EXEC SQL BEGIN;
> test(1);
> EXEC SQL DECLARE cur_test CURSOR FOR SELECT * FROM test;
> test(1);
> EXEC SQL OPEN cur_test;
> test(1);
> while(1) {
> EXEC SQL FETCH cur_test INTO :l_id, :l_name;
> test(1);
> if(sqlca.sqlcode == ECPG_NOT_FOUND) break;
> printf("%d: %s\n", l_id, l_name);
> }
> EXEC SQL CLOSE cur_test;
> test(1);
> EXEC SQL COMMIT;
> test(1);
>
> EXEC SQL FREE prep_ins;
> test(0); /* no exiting becase it doesn't work!! the statement doesn't exist */
> EXEC SQL DROP TABLE test;
> test(1);
> EXEC SQL DISCONNECT;
> test(1);
> return(0);
> }
>
> SQL code doing the same funcionality and work well!!
> *****************************************************
>
> /* Demonstration of the same functionality in SQL, where it DOES work well */
>
> /* Creating table */
> CREATE TABLE test (
> id INTEGER NOT NULL,
> name CHAR(10)
> );
>
> /* Preparing INSERT statement */
> PREPARE prep_ins(INTEGER, CHAR) AS INSERT INTO TEST VALUES($1, $2);
>
> /* Inserting several rows in one transaction */
> BEGIN;
> EXECUTE prep_ins (0, 'First');
> EXECUTE prep_ins (1, 'Second');
> EXECUTE prep_ins (2, 'Third');
> COMMIT;
>
> /* In SQL in the difference of ESQL/C the DEALLOCATION was not don, it is well *
> /
> /* Inserting next line in separate transaction */
> BEGIN;
> EXECUTE prep_ins (3, 'Fourth');
> COMMIT;
>
> /* Reading data from the table */
> SELECT * FROM test;
>
> /* Deallocating of the statement */
> DEALLOCATE prep_ins;
> DROP TABLE test;
>
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at
@ 2003-03-19 23:10 Philip Yarra <philip@utiba.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Philip Yarra @ 2003-03-19 23:10 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; jiri.langr@konero.cz; +Cc: pgsql-interfaces
On Thu, 20 Mar 2003 08:31, Bruce Momjian wrote:
> Can someone comment on this?
Ah, this might explain why FREE kept failing on us (see sample code below).
Or have I missed something here?
Regards, Philip Yarra.
int main(int argc, char *argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
char *target = "bob@snode5";
char *user = "philip";
char *message = "It came from embedded C";
char *pquery = "INSERT INTO test (message) VALUES(?)";
EXEC SQL END DECLARE SECTION;
if(argv[1] != NULL)
message = argv[1];
EXEC SQL WHENEVER sqlerror sqlprint;
EXEC SQL CONNECT TO :target USER :user;
/*EXEC SQL SET AUTOCOMMIT TO ON; */
EXEC SQL PREPARE stmt_id FROM :pquery;
EXEC SQL EXECUTE stmt_id USING :message;
EXEC SQL COMMIT;
EXEC SQL FREE stmt_id;
/* executing this line causes message
"sql error Invalid statement name stmt_id in line 25."
to be printed to console
*/
EXEC SQL DISCONNECT ALL;
}
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at
@ 2003-03-21 15:04 Michael Meskes <meskes@postgresql.org>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Michael Meskes @ 2003-03-21 15:04 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-interfaces
> > When I prepare a statement in ECPG it lives only to first explicit transaction block. At the COMMIT it seems to be deallocated.
Yes, it indeed is. I knew I found some docs stating it has to be. But
since I cannot find them anymore I wonder what kind of docs it was.
Very simple to fix. Just remove the call to ECPGdeallocate_all from
ECPGtrans() in file ecpg/ecpglib/misc.c resp. ecpg/lib/misc.c.
Michael
--
Michael Meskes
Email: Michael@Fam-Meskes.De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [BUGS] Bug #904: Deallocating of prepared statement
@ 2003-03-21 15:32 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Michael Meskes <meskes@postgresql.org>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2003-03-21 15:32 UTC (permalink / raw)
To: Michael Meskes <meskes@postgresql.org>; +Cc: pgsql-interfaces
This fix will appear in 7.4. Thanks for the report.
---------------------------------------------------------------------------
Michael Meskes wrote:
> > > When I prepare a statement in ECPG it lives only to first explicit transaction block. At the COMMIT it seems to be deallocated.
>
> Yes, it indeed is. I knew I found some docs stating it has to be. But
> since I cannot find them anymore I wonder what kind of docs it was.
>
> Very simple to fix. Just remove the call to ECPGdeallocate_all from
> ECPGtrans() in file ecpg/ecpglib/misc.c resp. ecpg/lib/misc.c.
>
> Michael
>
> --
> Michael Meskes
> Email: Michael@Fam-Meskes.De
> ICQ: 179140304
> Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-21 15:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-27 11:35 Bug #904: Deallocating of prepared statement in ECPG at COMMIT pgsql-bugs@postgresql.org
2003-03-17 21:41 ` Re: Bug #904: Deallocating of prepared statement in ECPG at Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-19 21:31 ` Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-19 23:10 ` Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at Philip Yarra <philip@utiba.com>
2003-03-21 15:04 ` Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at Michael Meskes <meskes@postgresql.org>
2003-03-21 15:32 ` Re: [BUGS] Bug #904: Deallocating of prepared statement Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox