472,371 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,371 software developers and data experts.

AUTOCOMMIT didn't work...


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.
+1-503-222-2783 - jd@commandprompt.com - 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

Nov 12 '05 #1
1 2726
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)

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Marcus | last post by:
Hello, I have 2 questions regarding InnoDB tables: 1. In the MySQL manual, it states that "MySQL begins each client connection with autocommit mode enabled by default. When autocommit is...
0
by: jy2003 | last post by:
Below is what I got by using MySQL's --log startup option. The original Java(with JDBC driver) program that creates the queries at runtime was coded by other people, and unfortunately, they are not...
0
by: Scott Mills | last post by:
Hi all, I've got MySQL 4.1.3 installed on RH 7.3... everything's fine so far except for getting the init_connect string to work. I followed the instructions on...
4
by: Christian Traber | last post by:
Hi, thanks for the new great version! Only one small problem, how can I disable autocommit in Postgres 7.4 in libpgtcl and psql? I found something about .psqlrc but how is the syntax and how...
1
by: Carmen Gloria Sepulveda Dedes | last post by:
Hola. He instalado postgres 7.4, y vi que ya no es posible hacer un "alter database ... set autocommit = off" Leyendo la documentacion de 7.4, encontre lo siguiente: Note: The...
0
by: Carmen Gloria Sepulveda Dedes | last post by:
Hello. I have installed postgres 7.4, and I see that it's not possible to do "alter database ... set autocommit = off" In the documentation, I found: Note: The autocommit-on mode is...
1
by: Matt Van Mater | last post by:
I'm looking to get a little more performance out of my database, and saw in the docs a section about disabling autocommit by using the BEGIN and COMMIT keywords. My problem is this: I enforce...
33
by: John Sidney-Woollett | last post by:
With the advent of postgres v8, would it be possible to change the default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ? Although this might break backward compatibility, it might be...
2
by: Jia Lu | last post by:
Hi all. I use cx_Oracle to connect to an Oracle9i DB. And I want to turn on AUTOCOMMIT function. I see that cur.execute("SET AUTOCOMMIT ON") cannot work. Is there any method to do that ?? ...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
0
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.