I've already seen temporary tables created for queries with 'order by' clauses (they go in /tmp or where the java.io.tmpdir property points to); is that a special case? I assume temporary tables in general (or large ones, small ones could be in RAM as you said) could be done the same way, no? To me, read-only means that the database itself should not be modified, and creating temporary files as needed is acceptable.
Also can we clarify why temporary tables hit the .h2.db file at all? Is this something to do with database recovery? If we could put all temporary tables on /tmp, this could be a fairly trivial patch, right? On May 31, 10:17 am, Noel Grandin <[email protected]> wrote: > Read-only is used for the case where you don't want to write anything to > disk, like for example where you are running > from a DVD. > > In order to support temporary tables on a read-only database, we'd have to > allocate temporary tables in RAM. > > It's certainly possible, but it'll take some work. Patches are welcome :-) > > > > > > > > Joel Nelson wrote: > > Anyone? It's not clear to me why 'distinct' seems to be a special case > > which is not supported on read-only database connections. Is this a > > wrinkle in the implementation, or am I missing something? It seems the > > ability to (implicitly, as a side-effect of a query) create temporary > > tables on a read-only database is very broadly useful, e.g. for the > > efficient implementation of a join or an 'in' clause. > > > Thanks, > > Joel > > > On May 23, 2:28 pm, Joel Nelson <[email protected]> wrote: > >> Do you think it is necessary to implement it that way? Large query > >> results are persisted to disk already for read-only databases; it > >> doesn't seem fundamentally different to do an external sort and > >> distinct that way? > > >> On May 18, 11:34 am, Noel Grandin <[email protected]> wrote: > > >>> The DISTINCT operation needs to allocate a temporary table, which it > >>> can't do if the database is read-only. > >>> On Tue, May 17, 2011 at 14:03, Valentin Tablan <[email protected]> wrote: > >>>> Here's an example of a full stack trace when this occurs: > >>>> Caused by: org.h2.jdbc.JdbcSQLException: The database is read only; SQL > >>>> statement: > >>>> SELECT DISTINCT "ContentMentions".ID, "ContentMentions".Length FROM > >>>> "ContentMentions" [90097-150] > >>>> at > >>>> org.h2.message.DbException.getJdbcSQLException(DbException.java:327) > >>>> at org.h2.message.DbException.get(DbException.java:167) > >>>> at org.h2.message.DbException.get(DbException.java:144) > >>>> at org.h2.message.DbException.get(DbException.java:133) > >>>> at > >>>> org.h2.engine.Database.checkWritingAllowed(Database.java:1616) > >>>> at org.h2.store.PageStore.logUndo(PageStore.java:1005) > >>>> at org.h2.store.PageFreeList.allocate(PageFreeList.java:96) > >>>> at org.h2.store.PageStore.allocatePage(PageStore.java:1138) > >>>> at org.h2.store.PageStore.allocatePage(PageStore.java:1123) > >>>> at org.h2.index.PageDataIndex.<init>(PageDataIndex.java:69) > >>>> at org.h2.table.RegularTable.<init>(RegularTable.java:90) > >>>> at org.h2.schema.Schema.createTable(Schema.java:551) > >>>> at org.h2.result.ResultTempTable.<init>(ResultTempTable.java:56) > >>>> at org.h2.result.LocalResult.addRow(LocalResult.java:238) > >>>> at org.h2.command.dml.Select.queryFlat(Select.java:509) > >>>> at org.h2.command.dml.Select.queryWithoutCache(Select.java:597) > >>>> at org.h2.command.dml.Query.query(Query.java:256) > >>>> at org.h2.command.dml.Query.query(Query.java:226) > >>>> at org.h2.command.dml.Query.query(Query.java:37) > >>>> at > >>>> org.h2.command.CommandContainer.query(CommandContainer.java:78) > >>>> at org.h2.command.Command.executeQuery(Command.java:178) > >>>> at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:77) > >>>> Thanks, > >>>> Valentin > >>>> On Tue, 2011-05-17 at 12:42 +0100, Valentin Tablan wrote: > >>>>> Hi all, > >>>>> While executing a SELECT query on a read-only database, I'm getting an > >>>>> exception saying the DB is read-only: > >>>>> org.h2.jdbc.JdbcSQLException: The database is read only; SQL statement: > >>>>> SELECT DISTINCT "DocumentMentions".ID, "DocumentMentions".Length FROM > >>>>> "DocumentMentions" WHERE "DocumentMentions".L2_ID IS NULL [90097-150] > >>>>> I would have thought that one can run SELECT queries on read-only DBs. > >>>>> Is that not the case? > >>>>> Other similar queries do not cause the error, so I'm a bit confused as > >>>>> to what the cause may be. > >>>>> Any ideas? > >>>>> thanks, > >>>>> Valentin > >>>> -- > >>>> You received this message because you are subscribed to the Google > >>>> Groups "H2 Database" group. > >>>> To post to this group, send email to [email protected]. > >>>> To unsubscribe from this group, send email to > >>>> [email protected]. > >>>> For more options, visit this group > >>>> athttp://groups.google.com/group/h2-database?hl=en. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
