Hi,
I'm using ibatis. But in this particular case the sql statement come from a plain ascii file and it's run by the Ibatis ScriptRunner class. Beside the fact this class come from ibatis framework it's just plain sql connection (I'm I wrong???). Just to be sure, here is the code from the class. I must say that i run script that contains create table, alter table, insert statements with the same runner.
If I wrong please tell me .. I like to be wrong when the result is eliminating a misunderstanding from my part :-)
Thanks for your help!
/David
public void runScript(Connection conn, Reader reader)
throws IOException, SQLException {
StringBuffer command = null;
try {
LineNumberReader lineReader = new LineNumberReader(reader);
String line = null;
while ((line = lineReader.readLine()) != null) {
if (command == null) {
command = new StringBuffer();
}
String trimmedLine = line.trim();
if (trimmedLine.startsWith("--")) {
println(trimmedLine);
if (log.isDebugEnabled()) {
log.debug(trimmedLine);
}
} else if (trimmedLine.length() < 1 || trimmedLine.startsWith("//")) {
//Do nothing
} else if (trimmedLine.endsWith(";")) {
command.append(line.substring(0, line.lastIndexOf(";")));
command.append(" ");
Statement statement = conn.createStatement();
println(command);
if (log.isDebugEnabled()) {
log.debug(command);
} boolean hasResults = false;
if (stopOnError) {
hasResults = statement.execute(command.toString());
} else {
try {
statement.execute(command.toString());
} catch (SQLException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
}
} if (autoCommit && !conn.getAutoCommit()) {
conn.commit();
} ResultSet rs = statement.getResultSet();
if (hasResults && rs != null) {
ResultSetMetaData md = rs.getMetaData();
int cols = md.getColumnCount();
for (int i = 0; i < cols; i++) {
String name = md.getColumnName(i);
print(name + "\t");
}
println("");
while (rs.next()) {
for (int i = 0; i < cols; i++) {
String value = rs.getString(i);
print(value + "\t");
}
println("");
}
} command = null;
try {
statement.close();
} catch (Exception e) {
// Ignore to workaround a bug in Jakarta DBCP
// e.printStackTrace();
}
Thread.yield();
} else {
command.append(line);
command.append(" ");
}
}
if (!autoCommit) {
conn.commit();
}
} catch (SQLException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
log.error("Error executing: " + command, e);
throw e;
} catch (IOException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
log.error("Error executing: " + command, e);
throw e;
} finally {
conn.rollback();
flush();
}
}
Dave Cramer wrote:
David Gagnon wrote:
Hi All,
I rerun the example with the debug info turned on in postgresl. As you can see all dependent tables (that as foreign key on table IC) are emptied before the DELETE FROM IC statement is issued. For what I understand the performance problem seem to came from those selects that point back to IC ( LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x). There are 6 of them. I don't know where they are comming from. But if I want to delete the content of the table (~10k) it may be long to those 6 selects for each deleted rows. Why are those selects are there ? Are those select really run on each row deleted?
You are using hibernate. Hibernate is generating them to lock the tables.
I'm running version 7.4.5 on cygwin. I ran the same delete from pgAdminIII and I got 945562ms for all the deletes within the same transaction .. (so I was wrong saying it took less time in PgAdminIII... sorry about this).
Do you have any idea why those 6 selects are there?
Hibernate
Maybe I can drop indexes before deleting the content of the table. I didn't planned to because tables are quite small and it's more complicated in my environment. And tell me if I'm wrong but if I drop indexed do I have to reload all my stored procedure (to reset the planner related info)??? Remember having read that somewhere.. (was it in the Postgresql General Bit newletter ...anyway)
Thanks for your help I really appréciate it :-)
/David
LOG: duration: 144.000 ms
LOG: statement: DELETE FROM YN
LOG: duration: 30.000 ms
LOG: statement: DELETE FROM YO
LOG: statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = $1 AND "yonum" = $2 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."yn" x WHERE "ynyotype" = $1 AND "ynyonum" = $2 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" = $1 AND "yonum" = $2 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."yr" x WHERE "yryotype" = $1 AND "yryonum" = $2 FOR UPDATE OF x
LOG: duration: 83.000 ms
LOG: connection received: host=127.0.0.1 port=2196
LOG: connection authorized: user=admin database=webCatalog
LOG: statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end;
LOG: duration: 2.000 ms
LOG: statement: set client_encoding = 'UNICODE'
LOG: duration: 0.000 ms
LOG: statement: DELETE FROM IY
LOG: duration: 71.000 ms
LOG: statement: DELETE FROM IA
LOG: duration: 17.000 ms
LOG: statement: DELETE FROM IQ
LOG: duration: 384.000 ms
LOG: statement: DELETE FROM IC
LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."iq" x WHERE "iqicnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."ia" x WHERE "iaicnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumo" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumr" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."il" x WHERE "ilicnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x
LOG: statement: SELECT 1 FROM ONLY "public"."bd" x WHERE "bdicnum" = $1 FOR UPDATE OF x
LOG: duration: 656807.000 msMichael Fuhr wrote:
----------------------- DELETE FROM BM; DELETE FROM BD; DELETE FROM BO; DELETE FROM IL; DELETE FROM YR; DELETE FROM YN; DELETE FROM YO; DELETE FROM IY; DELETE FROM IA; DELETE FROM IQ; DELETE FROM IC;
Michael Fuhr wrote:
On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote:
Il get this strange problem when deleting rows from a Java program. Sometime (For what I noticed it's not all the time) the server take almost forever to delete rows from table.
Do other tables have foreign key references to the table you're deleting from? If so, are there indexes on the foreign key columns?
Do you have triggers or rules on the table?
Have you queried pg_locks during the long-lasting deletes to see if the deleting transaction is waiting for a lock on something?
I rememeber having tried to delete the content of my table (IC) from
PgAdminIII and I took couples of seconds!!! Not minutes.
How many records did you delete in this case? If there are foreign key references, how many records were in the referencing tables? How repeatable is the disparity in delete time? A single test case might have been done under different conditions, so it might not mean much. No offense intended, but "I remember" doesn't carry as much weight as a documented example.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
