Hi,
We are having a problem, for a while, that is difficult to isolate.
Our ETL includes the following steps:
1) Open JDBC connection, add data to a table, commit, close connection 2) Open JDBC connection, some ALTER TALBLE statements, commit, close connection
Occasionally, we get the following during step 2) :
java.sql.SQLException: ALTER TABLE: set READ or INSERT ONLY not possible with outstanding updates (wait until updates are flushed) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2732)
What puzzles me is that step one is completed when this happens, the commit is done and the connection is closed (we triple-checked that is actually is closed).
Notice that inserting a sleep between the to steps makes it work correctly.
As I said, this is hard to isolate and reproduce. Still, can anyone guess what is exactly happening? Can it be that a background process is still flushing updates from step 1) ? Even if the JDBC connection is closed ? Can we force a blocking flush, so that it doesn't return until it's safe? If not, how can we know when updates are flushed?
Thanks, Roberto
Hi,
just a "wild" guess: WAL (write ahead log) not flushed, yet? IMHO there is no (easy) way to check that (outside the MonetDB kernel)
Stefan
----- On Jul 31, 2018, at 5:43 PM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Hi,
We are having a problem, for a while, that is difficult to isolate.
Our ETL includes the following steps:
- Open JDBC connection, add data to a table, commit, close connection
- Open JDBC connection, some ALTER TALBLE statements, commit, close connection
Occasionally, we get the following during step 2) :
java.sql.SQLException: ALTER TABLE: set READ or INSERT ONLY not possible with outstanding updates (wait until updates are flushed) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2732)
What puzzles me is that step one is completed when this happens, the commit is done and the connection is closed (we triple-checked that is actually is closed).
Notice that inserting a sleep between the to steps makes it work correctly.
As I said, this is hard to isolate and reproduce. Still, can anyone guess what is exactly happening? Can it be that a background process is still flushing updates from step 1) ? Even if the JDBC connection is closed ? Can we force a blocking flush, so that it doesn't return until it's safe? If not, how can we know when updates are flushed?
Thanks, Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thanks Stefan. Then I guess we'll loop until it succeeds.
What is annoying is that this could in principle happen at every statement executed, so in principle all statements should be wrapped with this loop.
On Tue, 31 Jul 2018 at 18:03 Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Hi,
just a "wild" guess: WAL (write ahead log) not flushed, yet? IMHO there is no (easy) way to check that (outside the MonetDB kernel)
Stefan
----- On Jul 31, 2018, at 5:43 PM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Hi,
We are having a problem, for a while, that is difficult to isolate.
Our ETL includes the following steps:
- Open JDBC connection, add data to a table, commit, close connection
- Open JDBC connection, some ALTER TALBLE statements, commit, close
connection
Occasionally, we get the following during step 2) :
java.sql.SQLException: ALTER TABLE: set READ or INSERT ONLY not possible
with
outstanding updates (wait until updates are flushed) at
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2732)
What puzzles me is that step one is completed when this happens, the
commit is
done and the connection is closed (we triple-checked that is actually is closed).
Notice that inserting a sleep between the to steps makes it work
correctly.
As I said, this is hard to isolate and reproduce. Still, can anyone
guess what
is exactly happening? Can it be that a background process is still flushing updates from step
- ?
Even if the JDBC connection is closed ? Can we force a blocking flush, so that it doesn't return until it's safe? If not, how can we know when updates are flushed?
Thanks, Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@C https://maps.google.com/?q=tefan.Manegold@C&entry=gmail&source=gWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 <020%20592%204212> | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Roberto,
does it also happen with other DDL / ALTER statements, or only with ALTER TABLE qname SET { { READ | INSERT } ONLY | READ WRITE } as the error message suggests?
Thanks! Stefan
----- On Aug 1, 2018, at 10:25 AM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Thanks Stefan. Then I guess we'll loop until it succeeds.
What is annoying is that this could in principle happen at every statement executed, so in principle all statements should be wrapped with this loop.
On Tue, 31 Jul 2018 at 18:03 Stefan Manegold < Stefan.Manegold@cwi.nl > wrote:
Hi,
just a "wild" guess: WAL (write ahead log) not flushed, yet? IMHO there is no (easy) way to check that (outside the MonetDB kernel)
Stefan
----- On Jul 31, 2018, at 5:43 PM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Hi,
We are having a problem, for a while, that is difficult to isolate.
Our ETL includes the following steps:
- Open JDBC connection, add data to a table, commit, close connection
- Open JDBC connection, some ALTER TALBLE statements, commit, close connection
Occasionally, we get the following during step 2) :
java.sql.SQLException: ALTER TABLE: set READ or INSERT ONLY not possible with outstanding updates (wait until updates are flushed) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2732)
What puzzles me is that step one is completed when this happens, the commit is done and the connection is closed (we triple-checked that is actually is closed).
Notice that inserting a sleep between the to steps makes it work correctly.
As I said, this is hard to isolate and reproduce. Still, can anyone guess what is exactly happening? Can it be that a background process is still flushing updates from step 1) ? Even if the JDBC connection is closed ? Can we force a blocking flush, so that it doesn't return until it's safe? If not, how can we know when updates are flushed?
Thanks, Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | S tefan.Manegold@C WI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
I looked back at all internal tickets that are related. The first occurrences dates back to November 2015 using release Jan2015 (I cannot say whether previous releases behaved differently).
It seems indeed to only happen with ALTER TABLE qname SET { { READ | INSERT } ONLY | READ WRITE }
Roberto
On Wed, 1 Aug 2018 at 12:35 Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Roberto,
does it also happen with other DDL / ALTER statements, or only with ALTER TABLE qname SET { { READ | INSERT } ONLY | READ WRITE } as the error message suggests?
Thanks! Stefan
----- On Aug 1, 2018, at 10:25 AM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Thanks Stefan. Then I guess we'll loop until it succeeds.
What is annoying is that this could in principle happen at every
statement
executed, so in principle all statements should be wrapped with this
loop.
On Tue, 31 Jul 2018 at 18:03 Stefan Manegold < Stefan.Manegold@cwi.nl >
wrote:
Hi,
just a "wild" guess: WAL (write ahead log) not flushed, yet? IMHO there is no (easy) way to check that (outside the MonetDB kernel)
Stefan
----- On Jul 31, 2018, at 5:43 PM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Hi,
We are having a problem, for a while, that is difficult to isolate.
Our ETL includes the following steps:
- Open JDBC connection, add data to a table, commit, close connection
- Open JDBC connection, some ALTER TALBLE statements, commit, close
connection
Occasionally, we get the following during step 2) :
java.sql.SQLException: ALTER TABLE: set READ or INSERT ONLY not
possible with
outstanding updates (wait until updates are flushed) at
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2732)
What puzzles me is that step one is completed when this happens, the
commit is
done and the connection is closed (we triple-checked that is actually is closed).
Notice that inserting a sleep between the to steps makes it work
correctly.
As I said, this is hard to isolate and reproduce. Still, can anyone
guess what
is exactly happening? Can it be that a background process is still flushing updates from step
- ?
Even if the JDBC connection is closed ? Can we force a blocking flush, so that it doesn't return until it's
safe?
If not, how can we know when updates are flushed?
Thanks, Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | S tefan.Manegold@C WI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 <020%20592%204212> | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing lis
https://maps.google.com/?q=list+mailing+lis&entry=gmail&source=gt
users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 <020%20592%204212> | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Stefan (and others),
I don't know in detail how the WAL works, but looking more closely at the code (see below), it puzzles me that table_has_updates can return true after a COMMIT statement has returned correctly. Can it?
static char * alter_table_set_access(mvc *sql, char *sname, char *tname, int access) { ... if (t->access != access) { if (access && table_has_updates(sql->session->tr, t)) throw(SQL,"sql.alter_table_set_access",SQLSTATE(40000) "ALTER TABLE: set READ or INSERT ONLY not possible with outstanding updates (wait until updates are flushed)\n"); ... }
static int table_has_updates(sql_trans *tr, sql_table *t) { node *n; int cnt = 0;
for ( n = t->columns.set->h; !cnt && n; n = n->next) { sql_column *c = n->data; BAT *b = store_funcs.bind_col(tr, c, RD_UPD_ID); if ( b == 0) return -1; cnt |= BATcount(b) > 0; if (isTable(t) && t->access != TABLE_READONLY && (t->base.flag != TR_NEW /* alter */ ) && t->persistence == SQL_PERSIST && !t->commit_action) cnt |= store_funcs.count_col(tr, c, 0) > 0; BBPunfix(b->batCacheid); } return cnt; }
On Wed, 1 Aug 2018 at 14:33 Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
I looked back at all internal tickets that are related. The first occurrences dates back to November 2015 using release Jan2015 (I cannot say whether previous releases behaved differently).
It seems indeed to only happen with ALTER TABLE qname SET { { READ | INSERT } ONLY | READ WRITE }
Roberto
On Wed, 1 Aug 2018 at 12:35 Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Roberto,
does it also happen with other DDL / ALTER statements, or only with ALTER TABLE qname SET { { READ | INSERT } ONLY | READ WRITE } as the error message suggests?
Thanks! Stefan
----- On Aug 1, 2018, at 10:25 AM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Thanks Stefan. Then I guess we'll loop until it succeeds.
What is annoying is that this could in principle happen at every
statement
executed, so in principle all statements should be wrapped with this
loop.
On Tue, 31 Jul 2018 at 18:03 Stefan Manegold < Stefan.Manegold@cwi.nl wrote:
Hi,
just a "wild" guess: WAL (write ahead log) not flushed, yet? IMHO there is no (easy) way to check that (outside the MonetDB kernel)
Stefan
----- On Jul 31, 2018, at 5:43 PM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
Hi,
We are having a problem, for a while, that is difficult to isolate.
Our ETL includes the following steps:
- Open JDBC connection, add data to a table, commit, close connection
- Open JDBC connection, some ALTER TALBLE statements, commit, close
connection
Occasionally, we get the following during step 2) :
java.sql.SQLException: ALTER TABLE: set READ or INSERT ONLY not
possible with
outstanding updates (wait until updates are flushed) at
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2732)
What puzzles me is that step one is completed when this happens, the
commit is
done and the connection is closed (we triple-checked that is actually
is
closed).
Notice that inserting a sleep between the to steps makes it work
correctly.
As I said, this is hard to isolate and reproduce. Still, can anyone
guess what
is exactly happening? Can it be that a background process is still flushing updates from
step 1) ?
Even if the JDBC connection is closed ? Can we force a blocking flush, so that it doesn't return until it's
safe?
If not, how can we know when updates are flushed?
Thanks, Roberto
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | S tefan.Manegold@C WI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 <020%20592%204212> | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing lis
https://maps.google.com/?q=list+mailing+lis&entry=gmail&source=gt
users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 <020%20592%204212> | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Roberto Cornacchia -
Stefan Manegold