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:
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
_______________________________________________ users-list mailing list 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 | 1098 XG Amsterdam (NL) |
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: > > > > 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 > > > > > > > > _______________________________________________ > > 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=g>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 >
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:
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
_______________________________________________ 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
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
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:
> >>
> >> 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
> >>
> >>
> >>
> >> _______________________________________________
> >> 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=g>t
> > 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:
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
_______________________________________________ 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=g>t 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