AUTOCOMMIT didn't work... - Mailing list pgsql-general

Hi,

Please see the history below...

Does AUTOCOMMIT as used here functionally replace VACUUM ANALYZE?

If I turn on AUTOCOMMIT is every transaction commited as it is
executed?  If this is the case the query will take about twice as
long... I have actually tested this scenario.

Using AUTOCOMMIT as suggested didn't solve the problem.

Thanks again for your help!
Lynn Tilby
ltilby@asu.edu

******************************************************************************

#include <stdlib.h>
#include <ctype.h>

EXEC SQL INCLUDE sqlca; /* include the sql debugging stuff... */

/********* !!!!!!!!!!  POSTGRES SQL DEBUGGING STRUCTURE FIELDS ************/
#define DEBUG_SQL printf("sqlcaid = %s\n", sqlca.sqlcaid); printf("sqlabc =
%f\n", sqlca.sqlabc); printf("sqlcode = %f\n", sqlca.sqlcode);
printf("sqlca.sqlerrm.sqlerrml = %d\n", sqlca.sqlerrm.sqlerrml);
printf("sqlca.sqlerrm.sqlerrmc = %s\n", sqlca.sqlerrm.sqlerrmc); printf("sqlerrp
= %s\n", sqlca.sqlerrp); printf("sqlerrd = %f\n", sqlca.sqlerrd); printf("sqlext
= %s\n", sqlca.sqlext);

int main()
{

  EXEC SQL BEGIN DECLARE SECTION; /* declare the variables used by sql */

  EXEC SQL END DECLARE SECTION; /* declare the variables used by sql */

  EXEC SQL CONNECT TO pma;
  EXEC SQL WHENEVER sqlerror sqlprint; /* turn on verbose sql error ckg */

DEBUG_SQL
  EXEC SQL SET AUTOCOMMIT = ON;
DEBUG_SQL
  EXEC SQL VACUUM ANALYZE optn_cent_xref;
DEBUG_SQL
}

[lynn@polaris strdl_sim]$ make vrfy_prob
/usr/local/pgsql/bin/ecpg -I/usr/local/pgsql/include -o vrfy_prob.c vrfy_prob.cpg
gcc -g3 -I/usr/include/pgsql -o vrfy_prob -lm -lc -lecpg -lpq vrfy_prob.c
[lynn@polaris strdl_sim]$ make vrfy_prob
/usr/local/pgsql/bin/ecpg -I/usr/local/pgsql/include -o vrfy_prob.c vrfy_prob.cpg
gcc -g3 -I/usr/include/pgsql -o vrfy_prob -lm -lc -lecpg -lpq vrfy_prob.c
[lynn@polaris strdl_sim]$ ./vrfy_prob
sqlcaid = SQLCA   Œ
sqlabc = -1.998680
sqlcode = -1.998680
sqlca.sqlerrm.sqlerrml = 0
sqlca.sqlerrm.sqlerrmc =
sqlerrp = NOT SET
sqlerrd = -1.998680
sqlext =
sqlcaid = SQLCA   Œ
sqlabc = -1.998680
sqlcode = -1.998680
sqlca.sqlerrm.sqlerrml = 0
sqlca.sqlerrm.sqlerrmc =
sqlerrp = NOT SET
sqlerrd = -1.998680
sqlext =
sql error Postgres error: ERROR:  VACUUM cannot run inside a BEGIN/END block
 line 25.
sqlcaid = SQLCA   Œ
sqlabc = -1.998680
sqlcode = -1.998681
sqlca.sqlerrm.sqlerrml = 76
sqlca.sqlerrm.sqlerrmc = Postgres error: ERROR:  VACUUM cannot run inside a
BEGIN/END block
 line 25.
sqlerrp =  25.
sqlerrd = -1.998680
sqlext =
[lynn@polaris strdl_sim]$
*******************************************************************************
On Sat, Nov 15, 2003 at 11:31:08PM -0500, Bruce Momjian wrote:
> > I might be wrong on this but I think that ecpg using transactions by
> > default for each query.
> > Perhaps turning on autocommit?
>
> Yep, use:
>
>     EXEC SQL SET AUTOCOMMIT = ON;

Or use 'ecpg -t'.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match




Hello,

I might be wrong on this but I think that ecpg using transactions by
default for each query.
Perhaps turning on autocommit?

J


Lynn.Tilby@asu.edu wrote:

>The following program produces the output below...
>It is built with:
>
>vrfy_prob: vrfy_prob.cpg
>  /usr/local/pgsql/bin/ecpg -I/usr/local/pgsql/include -o vrfy_prob.c
vrfy_prob.cpg
>  gcc -g${DEBUG} -I/usr/include/pgsql -o vrfy_prob -lm -lc -lecpg -lpq
vrfy_prob.c
>
>I am not using a BEGIN/END block, though the error messages indicate
>that I am.
>
>How can I do a vacuum analyze from within embedded sql in a c program
>that works?
>
>Thanks for your help!
>
>Lynn Tilby
>ltilby@asu.edu
>
>******************************* PROGRAM ********************************
>#include <stdlib.h>
>#include <ctype.h>
>
>EXEC SQL INCLUDE sqlca; /* include the sql debugging stuff... */
>
>/********* !!!!!!!!!!  POSTGRES SQL DEBUGGING STRUCTURE FIELDS
************/
>#define DEBUG_SQL printf("sqlcaid = %s\n", sqlca.sqlcaid);
printf("sqlabc =
>%f\n", sqlca.sqlabc); printf("sqlcode = %f\n",
sqlca.sqlcode);
>printf("sqlca.sqlerrm.sqlerrml = %d\n", sqlca.sqlerrm.sqlerrml);
>printf("sqlca.sqlerrm.sqlerrmc = %s\n", sqlca.sqlerrm.sqlerrmc);
printf("sqlerrp
>= %s\n", sqlca.sqlerrp); printf("sqlerrd = %f\n",
sqlca.sqlerrd); printf("sqlext
>= %s\n", sqlca.sqlext);
>
>int main()
>{
>
>  EXEC SQL BEGIN DECLARE SECTION; /* declare the variables used by sql */
>
>  EXEC SQL END DECLARE SECTION; /* declare the variables used by sql */
>
>  EXEC SQL CONNECT TO pma;
>  EXEC SQL WHENEVER sqlerror sqlprint; /* turn on verbose sql error ckg */
>
>DEBUG_SQL
>  EXEC SQL VACUUM ANALYZE optn_cent_xref;
>DEBUG_SQL
>}
>
>******************************* OUTPUT ********************************
>
>[lynn@polaris strdl_sim]$ ./vrfy_prob
>sqlcaid = SQLCA   Œ
>sqlabc = -1.998680
>sqlcode = -1.998680
>sqlca.sqlerrm.sqlerrml = 0
>sqlca.sqlerrm.sqlerrmc =
>sqlerrp = NOT SET
>sqlerrd = -1.998680
>sqlext =
>sql error Postgres error: ERROR:  VACUUM cannot run inside a BEGIN/END
block
> line 23.
>sqlcaid = SQLCA   Œ
>sqlabc = -1.998680
>sqlcode = -1.998681
>sqlca.sqlerrm.sqlerrml = 76
>sqlca.sqlerrm.sqlerrmc = Postgres error: ERROR:  VACUUM cannot run inside a
>BEGIN/END block
> line 23.
>sqlerrp =  23.
>sqlerrd = -1.998680
>sqlext =
>[lynn@polaris strdl_sim]$
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
Postgresql support, programming, shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org