AUTOCOMMIT didn't work...

[Lynn.Tilby]

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
lt****@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: me****@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
Ly********@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
lt****@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.
[phone] - [e-mail] - http://www.commandprompt.com
PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Oliver Elphick]

On Mon, 2003-11-17 at 20:39, Ly********@asu.edu wrote:

Hi,

Please see the history below...

Does AUTOCOMMIT as used here functionally replace VACUUM ANALYZE?
No; they are completely separate concepts. Autocommit treats every
statement as a separate transaction; as you have found, this will slow
things down.

VACUUM ANALYZE reads through all rows in a table or all the tables in a
database to set up some statistics to guide the planner. It needs to be
used regularly or the planner will start to produce invalid results and
queries will take a long time. That is not the same reason for taking a
long time as making every statement a separate transaction.
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.

I think someone said turn it on when he meant turn it off? It is always
on by default.

To put many statements into one transaction, enclose them all in BEGIN;
and END; They should then run a lot faster.
Oliver Elphick Ol************@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"A Song for the sabbath day. It is a good thing to
give thanks unto the LORD, and to sing praises unto
thy name, O most High." Psalms 92:1
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)