Bug #904: Deallocating of prepared statement in ECPG at COMMIT

Started by Nonamealmost 23 years ago6 messages

pgsql-bugs@postgresql.org

almost 23 years ago

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

Bruce Momjian

pgman@candle.pha.pa.us

almost 23 years ago

In reply to: Noname (#1)

Re: Bug #904: Deallocating of prepared statement in ECPG at

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

Bruce Momjian

pgman@candle.pha.pa.us

almost 23 years ago

In reply to: Noname (#1)

Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at

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

Philip Yarra

philip@utiba.com

almost 23 years ago

In reply to: Bruce Momjian (#3)

Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at

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;

}

Michael Meskes

meskes@postgresql.org

almost 23 years ago

In reply to: Bruce Momjian (#3)

Re: [BUGS] Bug #904: Deallocating of prepared statement in ECPG at

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!

Bruce Momjian

pgman@candle.pha.pa.us

almost 23 years ago

In reply to: Michael Meskes (#5)

Re: [BUGS] Bug #904: Deallocating of prepared statement

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