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 | 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) |
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list