Re: [h2] Re: Duplicate records when order by date?

2024-04-16 Thread Noel Grandin 




On 4/16/2024 10:19 AM, 'Peter Borissow' via H2 Database wrote:
(2) The query response is inconsistent. I see 4 different responses coming back from the same query on a static table. I 
don't think that's right.




SQL databases implement the relational model. The relational model works with 
sets, not lists.

Sets do not have ordering, so, if you want repeatable results and ordering, then you have to impose that ordering using 
an ORDER BY clause, and that ORDER BY clause needs to impose a total order, not a partial order.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f6cc0800-8e66-48fc-842e-25a40d32f907%40gmail.com.

Re: [h2] Re: H2 does not use SYSTEM TABLE in table types

2024-04-10 Thread Noel Grandin 
On Wed, 10 Apr 2024 at 13:01, prrvchr  wrote:

> > if we _did_ improve it, we would break some downstream libraries (like
> JOOQ and Hibernate), who rely on the values we currently return from that
> method
>
> Not if you only change the input handling: support the SYSTEM TABLE and
> TABLE types in addition to TABLE BASE in the type given as input to the
> DatabaseMetaData.getTables() method.
> I think less than 10 lines of code are necessary
>

Possibly I am missing something.

I am sure we could do that, and possibly it would have no negative side
effects.
But for you, that would require waiting for a new version to become
available, which might take some months.

Why is it hard to use the workaround? ie. doing

SELECT *, DB_OBJECT_ID('TABLE', TABLE_SCHEMA, TABLE_NAME) < 0 IS_SYSTEM FROM
INFORMATION_SCHEMA.TABLES;

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUJnLbYuX0zyB6N9zW_UR-bWs7mnnTKOeFgfK2fOBV06w%40mail.gmail.com.

Re: [h2] Re: H2 does not use SYSTEM TABLE in table types

2024-04-10 Thread Noel Grandin 
On Wed, 10 Apr 2024 at 11:00, prrvchr  wrote:

> No definitely, JDBC has nothing to do with it. It is your implementation
> which by not differentiating the type of the tables, necessarily, does not
> allow any filtering on the tables. Too bad and I don't think it's going
> to change any time soon since you can't understand such a simple thing...
>
>
That kind of comment is uncalled for, and not appreciated. This is not a
commercial product, we do not owe you anything. We do not get paid for
working on this project, nor do we get paid for spending time responding to
issues.

Now, Let us examine the proposal.

Firstly, yes, it could be improved. But we have already supplied a
workaround that extracts the required information.

Secondly, if we _did_ improve it, we would break some downstream libraries
(like JOOQ and Hibernate), who rely on the values we currently return from
that method.
We would also likely break the code of various people who use H2.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnXC0TGfHTZ3SgQtBx1c3wQEJ6SPN%2BtLyx_YFthRM7EXPg%40mail.gmail.com.

Re: [h2] how much indexes are used for optimization

2024-04-07 Thread Noel Grandin 
Run the EXPLAIN command to see which indexes are used for a specific query

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUU91ByuWeNANFe-cS3_1cWovMx%3DXHmoeMEzO-W13M-EA%40mail.gmail.com.

Re: [h2] Compact on db close does not shrink db file size

2024-03-12 Thread Noel Grandin 




On 3/12/2024 10:28 AM, Ulrich wrote:


My database has around 50.000 tables storing several 100 millions of rows. Its a kind of time series database. Data is 
added continously (around 1500 rows per second) and data older than 30 days is removed once a day.




I suggest (if you are not doing this already) that you move to an architecture 
where instead of running DELETE when
removing old data, you can run DROP TABLE or TRUNCATE TABLE, which will be more 
efficient with H2.

As long as the automatic compaction did not show nice results I decided to switch off the automatic compaction, set 
MAX_COMPACT_TIME to 3 and shutdown the database each 5 minutes using SHUTDOWN. I use SHUTDOWN instead of SHUTDOWN 
COMPACT to get control over the maximum time while the db is not available.




Unfortunately SHUTDOWN just does not try very hard, if you want to reduce disk 
space you will need to use SHUTDOWN COMPACT.

H2 is unfortunately not a great match for your specific use-case, and I don't think there is really anything in the way 
of database parameters that will make a big difference.


You could try batching your inserts (i.e. inserting a bunch of rows before doing a COMMIT), that sometimes helps reduce 
the disk usage.


Regards, Noel.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/2fb9b248-f01c-4812-bb2c-e4f49de96127%40gmail.com.

Re: [h2] Syntax error with ON CONFLICT

2024-01-17 Thread Noel Grandin 




On 1/18/2024 1:04 AM, 'Drew Dimanlig' via H2 Database wrote:

I'm getting this error trying to execute a query that does ON CONFLICT DO 
UPDATE:


We don't support that part of the postgres syntax. You are better off using 
MERGE INTO, which we do support:

https://h2database.com/html/commands.html#merge_into

https://www.postgresql.org/docs/current/sql-merge.html

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/043d5749-3bb7-451e-89e8-595ea7e0e8bc%40gmail.com.

Re: [h2] H2 Shell stuck wanting for response from database running mixed mode (1.4.196)

2023-11-29 Thread Noel Grandin 
On Wed, 29 Nov 2023 at 17:46, Scott Huddleston <
talascend.develo...@gmail.com> wrote:

> I don't see any threads named "H2 TCP Server" in the application thread
> dump, so your comment about the TCP server failing to start seems spot on.
> Is that the correct name of the thread to look for?
>
>
Yes, that would be the one.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUJ%2B0Hd35a%3DLofO5RwToqbs6AaSj11rp%2BGxdcqm8aMsZA%40mail.gmail.com.

Re: [h2] H2 Shell stuck wanting for response from database running mixed mode (1.4.196)

2023-11-28 Thread Noel Grandin 




On 11/29/2023 12:42 AM, Scott Huddleston wrote:




Where there other threads running when you took that thread dump? Because I dont' see the actual server thread running, 
which it what I would have expected.


So most likely what happened is that some other program is running on the port that H2 wants to use, the actual H2 TCP 
server failed to start, the console is now connecting to that other program, and the other program is not sending back 
the data that the console client expects.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/91e8cc3e-98cf-4784-90ab-56d0bdfd6836%40gmail.com.

Re: [h2] Java junit tests fails with "object already exists" after upgrade from h2 1.4.199 to 2.2.224

2023-10-25 Thread Noel Grandin 

I think it's because you are using a username of "PUBLIC", which conflicts with 
something we have changed (not sure what).

"PUBLIC" is the name of the default schema and the default role, so it is no 
surprise that it conflicts.

Probably we should be throwing a better error message in that case.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/8f4b2072-dc0d-46f9-a800-cce5be1660e7%40gmail.com.

Re: [h2] Java junit tests fails with "object already exists" after upgrade from h2 1.4.199 to 2.2.224

2023-10-25 Thread Noel Grandin 
My best guess right now is that you are using named in-memory databases, which means that the unit tests are sharing an 
in-memory database, and you are hitting a race condition (TOCTOU) in that chunk of code.


Short of fixing the H2 code, the only thing I can suggest is using different 
databases for different unit tests.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/546a5395-d8fe-45ae-a657-0f7418887ea8%40gmail.com.

Re: [h2] Java junit tests fails with "object already exists" after upgrade from h2 1.4.199 to 2.2.224

2023-10-25 Thread Noel Grandin 

On 10/24/2023 5:01 PM, Knut Skomedal wrote:
Caused by: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.RuntimeException: object already exists" 
[5-224]


Can you post the actual stacktrace of this part ?

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/788a2c36-8bec-4daa-bab9-d9d402090cea%40gmail.com.

Re: [h2] H2 Database Engine: New version released

2023-08-29 Thread Noel Grandin 




On 8/29/2023 1:33 PM, Andreas Reichel wrote:

if not, would you like to accept such a basic test performance test suite?



Sure, sounds like a useful tool.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/dc08123e-633e-baa9-860a-d2aacd8cdb3e%40gmail.com.

Re: [h2] Using (+) symbol for joins H2 database in Oracle mode

2023-08-03 Thread Noel Grandin 
Sorry, we do not support that syntax, even in oracle mode.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWb3FxD2ohkqWczPsRNHVxE50L%3DFgztir90pwyHhnaKZA%40mail.gmail.com.

Re: [h2] Problem upgrading from h2 version 2.1.214 to 2.2.220

2023-08-03 Thread Noel Grandin 




On 8/3/2023 1:10 PM, Thomas Hurley wrote:
I am Product Manager for 3 products all using H2 DB and backwards compatability for updates is really an essential 


This is an open-source volunteer project, not a commercial offering, so no, we 
are not planning anything like that.

You are more than welcome to assign some of your engineers to help improve H2 
on company time, however.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a30b6200-8e7a-4b69-817c-7d0c3fd8ad59%40gmail.com.

Re: [h2] Problem upgrading from h2 version 2.1.214 to 2.2.220

2023-08-02 Thread Noel Grandin 
You are going to need to do a dump/restore for this upgrade.
Elsewhere on this group someone posted a link to a different tool that they
wrote that helps with migration - that might be useful.

I just normally code up some batch/shell files and run the backup and
restore tools.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnXe4bs6TeTUr0txR4eC9JV4pSfqV8htN7OZbdiP8CyecQ%40mail.gmail.com.

Re: [h2] Time statistics for explian analzy

2023-07-12 Thread Noel Grandin 




On 7/11/2023 5:51 PM, CrazyDrumming wrote:
One question I have is, does the time counted by explain analyze include the time it takes to convert the queried data 
into strings? (Strings are used to display results on the console)


It does not. But that it normally a very small amount of the overall time spent 
performing a query.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/58ac9089-0473-b0d3-7411-fa2941050cdf%40gmail.com.

Re: [h2] Atomically swap tables

2023-06-12 Thread Noel Grandin 
you would need to use SET EXCLUSIVE to briefly block other operations

https://h2database.com/html/commands.html#set_exclusive

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVXHu0j5T4sfGLDQ%2BZVvYZwqjPM%2BccMGiQ5mFXEJ83QyA%40mail.gmail.com.

Re: [h2] Problem updating from 1.4.200 to 2.1.214

2023-05-23 Thread Noel Grandin 

I don't know how that code could ever have compiled.

A JdbcDataSource is a supplier of java.sql.Connection objects, it is not a 
subclass (or a superclass) of Connection.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/088d6b22-db4f-8840-4dea-1673a91e4cf6%40gmail.com.

Re: [h2] In-Memory H2 deleted rows not garbage collected.

2023-05-21 Thread Noel Grandin 
(1) That is not expected behaviour, my H2 databases grow to a point and
then stabilise (and I run my H2 databases pretty hard)

(2) in-memory is slightly different, we have a couple of places where we do
things different because we know it's a memory database

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWxhGSnbE36c5ySxua2M%2BbXT-6LzUS8HHyq1O4Z%2BQsTLw%40mail.gmail.com.

Re: [h2] In-Memory H2 deleted rows not garbage collected.

2023-05-21 Thread Noel Grandin 
I don't have any obvious answers.

You can try 2 things.

First, try building H2 from git master and seeing if the changes since the
last official release help.

If that doesn't help, we're going to need a standalone test case (no
Spring, etc) to reproduce the problem and then we might be able to fix it.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWR-m8vzvi9LE7XBf59CJRLGz_x_rm2CrauUL%3DchW3O1g%40mail.gmail.com.

Re: [h2] In-Memory H2 deleted rows not garbage collected.

2023-05-20 Thread Noel Grandin 
check that all your transactions are being closed. Holding a transaction
open means that the storage cannot be freed.



>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnXhZosk3b1LEK-b_6%2BTFFePhQ1%3DbkWRnvb5Auhx4OoEZA%40mail.gmail.com.

Re: [h2] Need advice with embedded H2 losing data due to "revert"

2023-05-04 Thread Noel Grandin 




On 5/3/2023 3:10 PM, Christian Dirks wrote:
We are running a java application using embedded H2 databases and ran into data loss situation that we can not explain. 
It appears as if the database reverted to a previous state - or that data wasn't written to file for hours.




Given the lengths of time involved, my first guess would be some kind of cloud-sync service that is getting confused and 
reverting parts of the filesystem to a prior state.


My second guess would be that HA-JDBC is doing something weird like directing writes to one file and not the other, and 
then choosing the wrong file the next time the software starts up.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/d0c225ff-769f-73da-718f-dd2055db7ba2%40gmail.com.

Re: [h2] How to differentiate system tables from others

2023-05-02 Thread Noel Grandin 
On Tue, 2 May 2023 at 15:30, prrvchr  wrote:

> To be more precise:
>  Here is the result of some tests on the DataBaseMetadata.getTables()
> method with the String[] types parameter:
>

types = new String[] { "BASE TABLE", "VIEW" };

Should do what you need

>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWWa7OnsTwqmDjkDpbYOa%2BsfPr-BvqZpZDHyg5GY%3DWcWw%40mail.gmail.com.

Re: [h2] How to differentiate system tables from others

2023-05-02 Thread Noel Grandin 



Assuming you are using the normal API, it's in the TABLE_TYPE column:

https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTables-java.lang.String-java.lang.String-java.lang.String-java.lang.String:A-

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/1dca1e74-5517-6497-2cee-aa0315373623%40gmail.com.

Re: [h2] Request for advice

2023-04-26 Thread Noel Grandin 




On 4/26/2023 11:03 AM, Silvio wrote:
We have some heavy queries that involve selecting records from a base cached table A (~100K records) that satisfy a 
quite a number of conditions expressed as


A.PK [NOT] IN (...)



You could also express those as

  A.PK NOT IN (  SubNotCondition1 UNION SubNotCondition2 ... )
  AND A.PK IN ( SubCondition1 UNION SubCondition2 ... )

which should result in less scanning of the second table.



We are thinking of using a temporary memory table C that holds primary keys of table A, evaluating the subqueries on B 
seperately inserting or removing keys into table C as needed and finally having a single subquery




That might help, and I have done similar things (on other databases).

You probably want a session-local temporary table, and you probably want to do

CREATE IF NOT EXISTS TEMP1
TRUNCATE TEMP1

for each query, rather than creating and dropping it, because CREATE/DROP is 
quite expensive in H2.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/84204ee9-6404-35a5-7766-47ddab91d855%40gmail.com.

Re: [h2] H2 PreparedStatement and Arrays

2022-12-06 Thread Noel Grandin 

You can do something like:

final Long[] messageIds = ...
final String sqlQuery = "SELECT * FROM FROM FOO WHERE FOO.MessageID IN (SELECT 
ID FROM TABLE(ID BIGINT=?))";
PreparedStatement ps = conn.prepareStatement(sSqlQuery);
ps.setObject(1, messageIds );

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ef5f40a4-1abd-8b93-2a28-0e3f84fb9d44%40gmail.com.

Re: [h2] DELETE statement causes the database to multiply in size and stuck.

2022-11-21 Thread Noel Grandin 

HI

There probably is something wrong with our execution of that query, but I'm afraid it's too complex to be debuggable 
just from the overview.


Unless you can prepare us a self-contained test-case, that won't be much we can 
do, sorry.

Regards, Noel.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/4554dda2-4d69-7cc1-c437-d6d3207da12c%40gmail.com.

Re: [h2] DELETE statement causes the database to multiply in size and stuck.

2022-11-19 Thread Noel Grandin 
What does
   EXPLAIN PLAN 
show?

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVEfV9czquzb-nXy4j8kWeytZN4AjXg7TpCoz0yA8wO-w%40mail.gmail.com.

Re: [h2] Question about JDBC prepared statements in H2

2022-11-07 Thread Noel Grandin 




On 11/7/2022 4:59 PM, Silvio wrote:


I was hoping for perhaps some speedup if the query planner could make a more optimal query plan based on known values in 
the SQL string as opposed to a more generic query plan that would have to take all possible values for one or more 
parameters into account. If that would never be the case in H2 then there is little to no advantage for us in changing 
anything.




There are very few opportunities to do this (that I am aware of), so I don't 
expect it to make any real difference.

But if you do find any potential optimisations that we are missing, please feel 
free to log a feature request :-)

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/18f9c69b-ff51-adc4-459f-b2f33b8179fb%40gmail.com.

Re: [h2] Question about JDBC prepared statements in H2

2022-11-07 Thread Noel Grandin 

I prefer PreparedStatement purely so I can avoid thinking about escaping string 
values.

But if that doesn't worry you, then there is no down side to using Statement 
for execute only once.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/21a13bbb-15d6-1b14-d089-9abcc0301472%40gmail.com.

Re: [h2] Problems with linked table

2022-10-14 Thread Noel Grandin 



On 10/14/2022 9:28 AM, Gerhard Duennebeil wrote:


Maybe it's a bit naive, but wouldn't it be possible to store the original name beside the local name and reflect that 
original name back to the linked database?




Oh, we wish, that would be so awesome.

The case-sensitivity of identifiers in SQL is a horrible, terrible, no-good 
mess.

So. when we ask the Postgresql JDBC driver what the column name is, we receive 
"rf".

Now, unfortunately, we need to know:
Is this the "one true name" of the column?
Or is it just a representation of an identifier that is case-insensitive, for which any of 'RF' 'rf' 'Rf' 'rF' is 
acceptable ?


And... we don't know, and there is insufficient information to be sure. So we have to guess, and in your case, we 
guess wrong.


The simplest fix, is for you to change your query to quote 'rf' at source i.e. 
to write

update reloadflag_disabled set 'rf'=true

The longer, nastier fix, would be that in TableLink#readMetaData we would have add extra logic to detect the ambiguous 
case, and issue some test queries to find out which of the cases we are dealing with.


If you want to spin up a patch/PR to add the logic, feel free, but I'll 
understand if you're not interested :-)


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/640887e8-028b-70dd-6289-2a58432fef0e%40gmail.com.

Re: [h2] Problems with linked table

2022-10-14 Thread Noel Grandin 
Firstly, let me congratulate you some very good debugging!

On Fri, 14 Oct 2022 at 08:05, Gerhard Duennebeil <
gerhard.duenneb...@gmail.com> wrote:

>
> Within the routine at line 199 a routine is called "n =
> convertColumnName(n);"
>
>
Can you debug into that convertColumnName and see which of the 4 if
branches it is hitting?
and what values the

storesMixedCase

storesLowerCase

storesMixedCaseQuoted

supportsMixedCaseIdentifiers

fields have ?

It is possible we may have to add another special case, to make Postgresql
happy.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVUyBa9x4DhkZXBH1a00n6Dx9qrZNHxmN97yc0bAk7wUw%40mail.gmail.com.

Re: [h2] Problems with linked table

2022-10-13 Thread Noel Grandin 
Unfortunately that smells like the postgresql database driver is returning
bad metadata,
because it should be returning column names with the correct case from the
SELECT.

But you'd have to debug way down to be sure.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUxR2JFq-RTMWoxhQKuiQOtG_-MFwXXHhQrJ-W_uLYnQw%40mail.gmail.com.

Re: [h2] Encountered a org.h2.jdbc.JdbcSQLException: IO Exception: null [90028-190] while accessing the database

2022-10-13 Thread Noel Grandin 
You can try the recovery tool, but there are no guarantees, sometimes you
get lucky, sometimes not.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnV5fEWcP0KmLcBpKMkRMqiszfyvkRVhPg6PiAPJvhNZ4w%40mail.gmail.com.

Re: [h2] data conversion issues

2022-10-13 Thread Noel Grandin 
That's not enough information to make a proper diagnosis, but at a guess I
would say that you are trying to convert that string to a number and it
won't fit into the destination type.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWU9T3HtjJERz_dCPdpGWAwADpQW8YwY_sv2O%2B%3D%2BLnb4Q%40mail.gmail.com.

Re: [h2] Adding New Data Types in H2 Open Source Code

2022-10-12 Thread Noel Grandin 
The relevant classes are called things like Value* - ValueString,
ValueDecimal, etc.

So you'd need to make a new subclass of Value and work from there.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVo8Sem8aKwirWYXmezKi1bdbYL_wQ%3DfAub%3DXYCgwLGMA%40mail.gmail.com.

Re: [h2] JDBC getIndexInfo for large table is very slow

2022-09-30 Thread Noel Grandin 
How many tables do you have, that should return in milliseconds.

You could try rinning the built in profiler (see the docs) and see what it
is doing.

You could also directly query the metadata table INFORMATION_SCHEMA.INDEXES

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUcg_-kwi5jg_azxTb1kBE7K_%3DCjjx-7YSaTQL9GdQjoA%40mail.gmail.com.

Re: [h2] Management of users, roles and privileges in LibreOffice Base

2022-09-01 Thread Noel Grandin 




On 2022/08/24 3:10 pm, prrvchr wrote:
I just finished the admin module  
allowing the management of users, roles and privileges in LibreOffice Base.


You are welcome to ask whatever questions you need to, and we will try and answer them, but nobody can make an 
open-ended commitment to helping you.


H2 has

CREATE USER
https://h2database.com/html/commands.html#create_user

ALTER USER ADMIN
https://h2database.com/html/commands.html#alter_user_admin

ALTER USER RENAME
https://h2database.com/html/commands.html#alter_user_rename

ALTER USER SET PASSWORD
https://h2database.com/html/commands.html#alter_user_set_password

CREATE ROLE
https://h2database.com/html/commands.html#create_role

DROP ROLE
https://h2database.com/html/commands.html#drop_role

GRANT RIGHT
https://h2database.com/html/commands.html#grant_right

GRANT ALTER ANY SCHEMA
https://h2database.com/html/commands.html#grant_alter_any_schema

GRANT ROLE
https://h2database.com/html/commands.html#grant_role

REVOKE ROLE
https://h2database.com/html/commands.html#revoke_role

SET PASSWORD
https://h2database.com/html/commands.html#set_password

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/52c10616-38bd-bd47-cf65-e71088596bfd%40gmail.com.

Re: [h2] Question about recover tool

2022-07-18 Thread Noel Grandin 

On 2022/07/15 2:35 pm, Silvio wrote:



Can the recover tool be manipulated to open a database in some form of "safe mode"? Or is there any other way to read a 
database file with the sole purpose of dumping the content of its tables?





Not at the moment.

The recover tool passes down a flag to make certain parts of the lower level 
code more tolerant of problems.

Certainly that could be extended to cover more situations.

Patches are welcome :-)

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a98b1346-1a5d-82d9-692f-4cdc78c73822%40gmail.com.

Re: [h2] Exception from PgServerThread.initDb

2022-07-13 Thread Noel Grandin 

You are obviously doing something rather unusual because initDb is not supposed 
to be called via reflection.

So we're going to need a complete standalone test-case to be able to help.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/31da4c04-0d6e-169b-6406-3515d89eeb24%40gmail.com.

Re: [h2] H2 knowledge

2022-07-13 Thread Noel Grandin 

what documentation we have is all either at
   https://h2database.com/html/main.html

or inside the source code

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/195915be-91eb-e778-3271-9b80b1247e35%40gmail.com.

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

2022-06-23 Thread Noel Grandin 




On 2022/06/23 9:30 am, prrvchr wrote:


/I don't know what do you mean by system table and why you need to distinguish 
them from normal tables and views./


Unfortunately, we cannot change this easily because of backwards compatibility.

What you can do, is to special case H2 in your JDBC driver.

All of the H2 system tables will have METADATA in the first column of the result set, and INFORMATION_SCHEMA in the 
second column.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/6822bf4a-4571-4976-7100-a9b4ee434f8a%40gmail.com.

Re: [h2] Column not found when SELECTing from a recursive Common Table Expression (CTE)

2022-06-14 Thread Noel Grandin 
Unfortunately CTE's in H2 are currently pretty bad, and nobody has felt
sufficiently motivated to do the surgery that is necessary to accomodate
them really well.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnU9kB6KArjUouSnSjm9pX-_BOYcN6Rud8S0yfObQrA0Wg%40mail.gmail.com.

Re: [h2] Automatic COMPACT does not seem to work

2022-06-14 Thread Noel Grandin 
In general, compacting should work while the db is running, but it's fairly
conservative, so if you have running queries or open transactions, that can
keep a lot of stuff alive that should be compacted.
Also, if you are adding stuff to the db while compacting, even if the
compacter frees up a ton of space, there will likely be trailing entries in
the file that prevent the file from being truncated.

If you can temporarily pause all your queries or operations while you run
the compacter, that might help.

Possibly we should look into an option that does that (pausing)
automatically.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVzL66xyFJpt4PJ5GV4oX8oO1wBapqmAcVuO_vxEAQ9yA%40mail.gmail.com.

Re: [h2] How to create table programming in version 2 ?

2022-06-06 Thread Noel Grandin 
You're using purely internal functions, which is always going to be problematic, because then you're never sure if you 
have obeyed all the invariants which the code needs.


Why not just call "CREATE TABLE" using the normal JDBC API?


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/b5e08a8e-5b34-5103-2144-33dd192388d4%40gmail.com.

Re: [h2] Properly handling large result sets

2022-04-13 Thread Noel Grandin 
MAX_MEMORY_ROWS is the one you want

On Wed, 13 Apr 2022 at 16:12, Ron Exxon  wrote:

> I have a query that returns a very large result set, which gets overflowed
> to disk in a size that is too large. The environment has ample memory to
> handle this result set, however I haven't been able to find which
> configuration parameter controls the maximum in-memory size for a result
> set.
> I have tried CACHE_SIZE and MAX_MEMORY_ROWS and neither seemed to have an
> impact on the overflow.
> Can anybody point me to what I'm missing? Any help is greatly appreciated
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/c29b4f34-20af-44ba-9610-37e870624e9dn%40googlegroups.com
> 
> .
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWCpUsOFJ9AHLOspSeCMyM6eU%2Bn3ewNNdc-XJjgV%2BT_sA%40mail.gmail.com.

Re: [h2] Value is different for AVG(CAST AS DECIMAL)

2022-04-08 Thread Noel Grandin 
I think you will need to cast to something like DECIMAL(20,2) i.e. specify
the precision and scale

On Thu, 7 Apr 2022 at 21:49, Jane  wrote:

> Hi All,
>
> I am upgrading H2 from 1.4.197 to 2.1.210. I have a query which casts a
> value to DECIMAL, as - SELECT AVG(CAST(A AS DECIMAL). Column A is INTEGER.
>
>  In older H2, this used to produce a value like 10002.5 and in 2.1.210,
> the same query produces 10002.0. What's happening internally here? Is there
> anything I can do to make this behave like in 1.0?
>
> The same query is being used across multiple databases (oracle, Postgres
> etc). That is why.
>
> Thanks,
> J.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/66a79014-70be-47c6-9f16-7d3328a269a4n%40googlegroups.com
> 
> .
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnXkxsCy8_m9hNs%3DJv48H%2BOSFwvUFG%2Btehg4D9hTfnJ7sA%40mail.gmail.com.

Re: [h2] Error 50000-210: org.h2.jdbc.JdbcSQLNonTransientException

2022-04-08 Thread Noel Grandin 
If you can provide a standalone test case, we can probably debug the issue

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnV3%3Dpr7DPpgxJP3gjx%2BfNApSVF%2BPuARnhhxpnh7D%2Bzd7Q%40mail.gmail.com.

Re: [h2] Migration from 1.4.199 TO 2.1.210 Character Problem

2022-03-30 Thread Noel Grandin 
CHAR has a default length of 1 and is a fixed length datatype.
I think you want to be using VARCHAR there.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUSn_dVb0g9wHhM65FvBGFigvq88BtasqAD920N62rLCw%40mail.gmail.com.

Re: [h2] H2 DB Temp File is created on separate drive

2022-02-02 Thread Noel Grandin 
You'd have to modify how the java VM chooses where to put temporary files.
There are various ways to do that depending on how you want to control it,
but at minimum it would have to apply to that whole java process,

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVRchVKKyWz%2B0fdKttX5HUevKCHiYvojb0YQ294CzDUZQ%40mail.gmail.com.

Re: [h2] Statement.execute throws Syntax error (206/210)

2022-02-02 Thread Noel Grandin 
You can specify a column as either INT or IDENTITY but not both.

On Wed, 2 Feb 2022 at 20:38, Helmut Leininger  wrote:

> This was not the case in 200
>
> Syntax error in SQL statement "create table persons (mstb_per_no int
> [*]identity not null, mstb_father int, mstb_mother int, mstb_last_edit
> date, mstb_dsid int, mstb_ref_id int, mstb_reference varchar(12),
> mstb_spoulast int, mstb_scbuff varchar(10), mstb_pbirth varchar(30),
> mstb_pdeath varchar(30), mstb_relate int, mstb_relatefo int, mstb_tt
> varchar(1), mstb_sex varchar(1), mstb_living varchar(1), mstb_birthorder
> varchar(2), mstb_multibirth varchar(1), mstb_adopted varchar(1),
> mstb_ance_int varchar(1), mstb_desc_int varchar(1))"; expected "ARRAY,
> INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL,
> AUTO_INCREMENT, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT,
> CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES,
> AUTO_INCREMENT, ,, )"; SQL statement:
>
> create table persons (mstb_per_no int identity not null, mstb_father int,
> mstb_mother int, mstb_last_edit date, mstb_dsid int, mstb_ref_id int,
> mstb_reference varchar(12), mstb_spoulast int, mstb_scbuff varchar(10),
> mstb_pbirth varchar(30), mstb_pdeath varchar(30), mstb_relate int,
> mstb_relatefo int, mstb_tt varchar(1), mstb_sex varchar(1), mstb_living
> varchar(1), mstb_birthorder varchar(2), mstb_multibirth varchar(1),
> mstb_adopted varchar(1), mstb_ance_int varchar(1), mstb_desc_int
> varchar(1)) [42001-210]
>
> Sorry for possible double  posting (this is my first try in this group)
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/f64c2b8d-9e77-4d40-acc5-649a5ebdf311n%40googlegroups.com
> 
> .
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUyL7FpEEWDD%3Dn0E0xC1V34ebtXgLn%2BL6R71whw%3DZPsvg%40mail.gmail.com.

Re: [h2] H2DB breaks GraalVM native image production

2022-01-05 Thread Noel Grandin 
You're missing the the JTS geometry classes

Caused by: org.graalvm.compiler.java.BytecodeParser$BytecodeParserError:
com.oracle.graal.pointsto.constraints.UnresolvedElementException:
Discovered unresolved type during parsing: org.locationtech.jts.geom.Geometry.
To diagnose the issue you can use the --allow-incomplete-classpath option.
The missing type is then reported at run time when it is accessed the first
time.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnV73jadeeMvF4UnKCLWLnjLQeUeMLQ5ooCCjdos5fKeoQ%40mail.gmail.com.

Re: [h2] Re: hibernate migration an MODE=PostgreSQL

2021-12-02 Thread Noel Grandin 




On 2021/12/02 9:59 am, Andrea Spacca wrote:


without `MODE=PostgreSQL` and with `org.hibernate.dialect.H2Dialect` I get the 
following error:
```
ERROR: NULL not allowed for column "ID"; SQL statement:
insert into users (id, password, session_id, username) values (null, ?, ?, ?) 
[23502-202]
```



What does the definition for the users table look like? This seems like some 
kind of issue with auto-generated key columns.


with `MODE=PostgreSQL` and `org.hibernate.dialect.PostgreSQL10Dialect` I get 
this one:
```


The "MODE=" stuff is essentially unsupported at this point - none of the 
existing maintainers are interested,
it looks like Hibernate is looking for metadata that H2 doesn't know how to 
emulate.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/01eb033e-4879-7c91-ca26-22b0e36c6514%40gmail.com.

Re: [h2] Question about live backups

2021-11-11 Thread Noel Grandin 

On 2021/11/11 12:08 pm, Silvio wrote:


Is there any risk in using the BACKUP command with databases being in (heavy) use? Any increased chance of database 
corruption? Will it dramatically slow down database  access?





In theory, using the BACKUP command on a live MVStore database is possible, what it does is temporarily prevent re-using 
blocks for the duration of the backup.


So as long as your backup happens fairly quickly, or your database has a fairly 
light write-load, you should be fine.

However, I stress that I don't know that anyone has tested this particularly 
well.

Regards, Noel.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/13f9d727-4f87-7547-a1cb-06f7aa2b56b1%40gmail.com.

Re: [h2] In memory query optimizations

2021-10-25 Thread Noel Grandin 
Trying to use H2's internals directly is not a good place to start.
Rather use the JDBC API.

I wouldn't bother setting any parameters initially unless you run into a
problem, H2 is pretty fast out the box.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVxaHaXXOk4VHHi5hpcdZ1-3E3gfnvAY7Lk5Hwm7mDM%3DA%40mail.gmail.com.

Re: [h2] H2 Connection leaks in application logs

2021-08-07 Thread Noel Grandin 
somewhere you are leaking connections i.e. forgetting to return them to the
pool

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVzCcmOJCv8X%3DPpPasZr8-Ab_T2i%3DW9P9PoieRd1uD0pw%40mail.gmail.com.

Re: [h2] H2 Inmemory not able to load 7lc records and giving heapspace issues

2021-07-25 Thread Noel Grandin 
try using nioMemFS to store the data on the native heap

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUhgJAP3CLUED_jD5r62odjngxLGoyovV03EO7j4AfKYQ%40mail.gmail.com.

Re: [h2] Autonomous commit - commit single savepoint or transaction

2021-07-25 Thread Noel Grandin 
you're going to need to use two connections to achieve that

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUNkjHdv%3DmcpQ4S_zM6mZNUmxcpMg5riNZK_sR6cjTBnQ%40mail.gmail.com.

Re: [h2] CANCEL_SESSION & CREATE_INDEX

2021-06-16 Thread Noel Grandin 




On 2021/06/15 6:36 pm, Tom Kalmijn wrote:

I was looking for a way to cancel a long running 'CREATE INDEX'.

I tried "CALL CANCEL_SESSION()" with no luck.

The resultset does return True but the running create index query does not stop.


There is currently no support for interrupting a CREATE INDEX operation.

CANCEL_SESSION sets a cancel flag, but unless the operation code checks the flag, it has no effect, and in this case, 
CREATE INDEX does not check the flag


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/0aed1880-93b1-55fb-9346-7ea3d5f1884a%40gmail.com.

Re: [h2] Make backup relative to the current location

2021-05-14 Thread Noel Grandin 




On 2021/05/13 8:07 pm, 'Christoph Läubrich' via H2 Database wrote:

I'm writing an upgrade script and like to make a backup from withing that 
script.

Is it possible to get the name of the current database location to use for 
something like this:



DATABASE_PATH() is the function you want:

http://h2database.com/html/functions.html#database_path



--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/16273586-f095-5170-c4fb-099be0cf7759%40gmail.com.

Re: [h2] Re: release date : 1.4.201 version

2021-05-13 Thread Noel Grandin 
On Wed, 12 May 2021 at 19:36, Gottfried Theimer 
wrote:

> It is great to see all the effort being put into the development of H2. It
> is however not easy to see what the main work items are currently. Is the
> *Roadmap* on the H2 website still the plan? What is intended for the next
> release?
>
>
There isn't really any grand plan - H2 is largely feature complete for the
purpose of most of the contributors.

I believe katzyn is going to do some JSON and GEOMETRY work, but there is
nothing much planned otherwise.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnX5UTkxEqNWj7OxjcOM-JAYTEw0OwzeiYNc4yozb-vVrg%40mail.gmail.com.

Re: [h2] using external(cloud) key-value db as backing store

2021-04-27 Thread Noel Grandin 
On Sat, 24 Apr 2021 at 06:52, Alex Ramos  wrote:

> I've come down to a decision-point with three choices... 1) reimplement
> MVStore abstraction, 2) reimplement filesystem abstraction used by MVStore,
> or 3) implement pluggable tables, one layer up.
>
>
>
MVStore is a "transactional key-value store", not merely key-value, so just
bear that in mind.

Pluggable tables are probably the easiest.
Another option is CREATE LINKED TABLE if you have a JDBC driver for your
cloud store.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUq_KbXirjyGJmNmxeQq_-mn3UisNjLLH4_zPq%2BgQ6x%2Bw%40mail.gmail.com.

Re: [h2] RFE: Enable Common Table Expression (CTE with...) in INSERT, UPDATE, DELETE, CREATE TABLE AS, CREATE VIEW AS

2021-03-11 Thread Noel Grandin 
You can try the HEAD of the main git repo, but CTE's in H2 are a bit of a
hack and still have a lot of issues.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUtuY4hqp0JzN%3DY2xwvusjebUaTn0x1YXJT%3Dvu9EPfdMA%40mail.gmail.com.

Re: [h2] Re: PostgreSQL range type and functions

2021-02-18 Thread Noel Grandin 




On 2021/02/19 9:27 am, Evgenij Ryazanov wrote:

New data types will definitely require massive changes everywhere.



Yup, that's what I meant by deep vs shallow integration.

If the new datatypes are just aliases over existing types, it's not a problem.

If the new datatypes want proper compatibility with PostgeSQL, then we would have to see how much compat we can supply 
before we draw the line and say "this is too disruptive".


On the up side, such new datatypes would give us an initial idea of the 
requirements for UDTs.

And I dont think we need to follow the SQL-Standard design for UDTs - nobody 
else does.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/a3e32913-724c-b2c3-3bfd-b952921bb678%40gmail.com.

Re: [h2] Re: PostgreSQL range type and functions

2021-02-18 Thread Noel Grandin 



Compatibility with PostgreSQL is ok, IMO, because it's a major open-source 
engine.

And as long as someone else is doing the work, I'm not opposed to accepting 
such contributions.

Yeah, it's a little icky in the parsing code, but as long as it's only active 
in PostgreSQL compat mode, that's fine by me.

For me, it depends on how deep you want the compatibility to go - surface level compatibility is fine, but deep 
compatibility that requires adding extra paths through other more important data structures would be a stretch.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/91c496e6-05a4-ccbf-551f-69fa23650e66%40gmail.com.

Re: [h2] Re: Nested loop join implementation

2021-02-16 Thread Noel Grandin 

We don't have such other join strategies so we don't need any interfaces, so we 
just implement the join directly in code.

That code that we pointed at, is in fact the join code.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/7c50b937-1e31-16a6-260f-308adfc60fab%40gmail.com.

Re: [h2] Odd UUID to BOOLEAN conversion error

2021-01-21 Thread Noel Grandin 
That smells like the kind of bug that H2 sometimes has with determining the
type of a parameter in a PreparedStatement.

Normally it can be worked around by using CAST around the ? so that H2
knows exactly what type to use there.

Possibly we could track down the bug if you can provide a standalone
test-case.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnW0hMkRm362G4BdaxeUz6C_4Jkx7fy4VW9QaNVZ7k5xMA%40mail.gmail.com.

Re: [h2] Re: Linked table fetch size

2020-12-30 Thread Noel Grandin 
Sounds possible and reasonable to me.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWxQdpfVLYjL7ARVMveAXX_Ok3tseaB0Pk0iGopQOOT_A%40mail.gmail.com.

Re: [h2] Multiple pagestore h2 instances in a single VM and memory footprint

2020-12-24 Thread Noel Grandin 
It is kind of hard to tell what is going on here, because you're trying
different things and causing yourself different problems in the process.

If you are opening a lot of H2 databases, you may need to reduce the cache
size, since each open database will have its own cache.

On Wed, 23 Dec 2020 at 23:47, Gerlits András 
wrote:

> unless I used "nioMapped". And even then, when I open any one of these
> databases, they are always read into memory fully, even if I put "file" in
> the URL. So, not just in my software, but if I open a split database of 2
> gigs in a SQL console application like squirrel, it grows the amount of
> used memory by 2 gigabytes or throws an out of memory error.
>
>
"nioMapped" means using mmap() to map the file space into the memory space,
so it doesn't actually "grow" the memory usage, but it does use up virtual
memory space. You should ideally be monitoring the working set size, not
the vmsize.

You might be seeing an out of memory error because you are trying to mmap()
a large database into a 32-bit process, and running out of virtual memory.
Try running a 64-bit JRE.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnURBfUd0aX42Qim3CmfQ2BHse%2BBPuW8SkVNaT31%2B25Umg%40mail.gmail.com.

Re: [h2] Re: Fun with URIs and Windows: jdbc:h2:C:/ vs. jdbc:h2:/C:/

2020-11-24 Thread Noel Grandin 
On Tue, 24 Nov 2020 at 18:37, Andreas Reichel <
andr...@manticore-projects.com> wrote:

>
> So I wonder: should we not try to accept valid Uris at least for the
>  jdbc:h2:file: and  jdbc:h2:nio: variants?
> Would you consider PRs covering that?
>
>
Sorry, but noo, a JDBC connection string is not a valid URI, and does not
follow that syntax.
We also don't need to make it's existing syntax any more complicated.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUeteBVCyZ5fKDsnsBHbdJPeZum9aaSo-_aXVA04%2Bdc3A%40mail.gmail.com.

Re: [h2] Re: Time To First Row on large ResultSet

2020-11-10 Thread Noel Grandin 
You can turn LAZY_QUERY_EXECUTION on and off using
   SET LAZY_QUERY_EXECUTION ON/OFF

But, as I said earlier, it has restrictions - it locks internal data
structures for the duration of retrieveing the resultset, so it is not
great for setups that need multiple concurrent queries.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWvbPArXxLZoPcEUdMqSK0j3A7%3DjFaXthvnVUXpY%3DhgvA%40mail.gmail.com.

Re: [h2] Re: Time To First Row on large ResultSet

2020-11-09 Thread Noel Grandin 

> On Tuesday, November 10, 2020 at 12:03:30 AM UTC-5 val wrote:
> I'm guessing this is  how the h2 client/driver (not the db engine it 
self) behaves to handle a ResultSet.  It will
> fully serialize the contents of the ResultSet to disk first and then 
serve that. Perhaps this is necessary to

No, it's just the easiest way to handle multiple resultset performantly, because then the engine doesn't have to 
remember any state about the resultset.


On 2020/11/10 7:31 am, val wrote:

Adding *LAZY_QUERY_EXECUTION=1 * to the connection url is closer to what I was 
looking for.


Yeah, that is currently our best answer for extremely large result sets, note that it only works for relatively simple 
queries, and it will likely be quite bad performance-wise if you have more than connection attempting to hit the same table.


The alternative is to grab chunks of the resultset at a time, using 
OFFSET...LIMIT



I wonder where the bottleneck is. What would it take to bring this 20 minutes total iteration time down to the 6 minutes 
it takes if you prebuild the ResultSet first.




You'd have to run a profiler, either our simple one (see our performance page), or one of the other ones, to see where 
the bottleneck is.


TBH, H2 is mostly optimised for small quick queries, not really large ones.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/69be3538-b2fe-b5a6-1247-18cbb02eaa18%40gmail.com.

Re: [h2] Connecting Grafana to H2 Database

2020-10-30 Thread Noel Grandin 
Grafana supports connecting to a postgresql database, so possibly you could
make it connect to H2 using the H2 postgresql protocol support.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUYJUd6MhPOv88jj23zsX0KMqaCMJdLtKEbMAN6W8WDZQ%40mail.gmail.com.

Re: [h2] MvStore cache leak

2020-10-26 Thread Noel Grandin 

We should either

(a) throw an exception if the cache size is so big we can't handle it

(b) use BigInteger or store the cache sizes in kilobytes or megabytes to avoid 
overflow.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/00da5c81-1d63-8359-1a3b-598b0b2a4db8%40gmail.com.

Re: [h2] MvStore cache leak

2020-10-23 Thread Noel Grandin 
That looks like an integer overflow somewhere.

I would tweak the source code in the places that modify that field, to dump
stack traces, and work backwards.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUx8kDfN99kBjEJdH_aLDicMKX4wHmR5C8D6eQEwtSWUg%40mail.gmail.com.

Re: [h2] How to access the H2 parser and AST?

2020-10-07 Thread Noel Grandin 




On 2020/10/07 3:54 pm, Andreas Reichel wrote:
but please let me ask further: How exactly is the SQL syntax and grammar defined/specified for H2? Is there a BNF file 
somewhere? Or is it all hard-coded in Java only?


We use a top-down recursive descent parser, and we have separate documentation files which we pass through a build-time 
tool to generate BNF-type diagrams.


--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/264556de-722d-288c-0984-dfc2b3e69f4d%40gmail.com.

Re: [h2] How to access the H2 parser and AST?

2020-10-07 Thread Noel Grandin 




On 2020/10/07 2:37 pm, Andreas Reichel wrote:


can we access the H2 internal Parse (which should know best) and retrieve 
the Query AST? I did not find anything in
the API documentation.


No, the internal parser doesn't generate a real AST, it produces something that roughly half-way between an AST and a 
query-plan.



is there an ANTLR 4 grammar for H2? (I did not find any)


No, although the documentation does have a fairly complete set of grammar-like 
productions.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/d272ea92-f302-dfc3-1841-4fb344dc2732%40gmail.com.

Re: [h2] Is the client-server communication protocol to the server a published API?

2020-09-23 Thread Noel Grandin 
Sorry, no. We can and do change it from time to time.

But if you have that level of access in your language, why not just load
the H2 jar and use the JDBC API?

If you cant do that, I see the option as :
(*) write some kind of proxy which has a stable TCP API on one side, and
talks JDBC on the other
(*) talk to the H2 native TCP API, and just accept that you'll have to
update your code from time to time - we do maintain backwards compatibility
with earlier clients, but we will absolutely not support your client in any
official way.
(*) talk to the H2 PostgreSQL TCP API, since we implement a subset of that.

Hope this helps.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWpqs8bnu3Hrk2KAz3cu0v5623fATg-2-Fv6KhCmx64Tw%40mail.gmail.com.

Re: [h2] H2 1.4.200: Random JRE crash

2020-09-11 Thread Noel Grandin 
H2 does not use any native code at all, so if you are seeing crashes you
either have a dodgy machine, or are unlucky enough to have found a bug in
the Java VM.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnVy%3DBWPHnWetaBPdB%3Dc_3U9LZbppWsx6BZ8F8wPv_RC2w%40mail.gmail.com.

Re: [h2] H2 Procedural SQL language efforts

2020-09-07 Thread Noel Grandin 
I don't see the point. We have a perfectly good language already (Java),
and even a built-in compiler for it.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUYY01aUTVHEXVToVuqjS1kMBsDL02LTXKhmiuDoGBuKw%40mail.gmail.com.

Re: [h2] Will a H2 v1.4.200 jar file read/open a database created by H2 V1.3.x ?

2020-06-27 Thread Noel Grandin 
you best bet is to do a full backup and restore.

It is quite possible you have latent corruption that only newer versions
will complain about.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnU9KgXEq7Ex5bbxN1vPoj_QpzNzLFvVabzCcz4HF860sg%40mail.gmail.com.

Re: [h2] H2 as read-only cache, strategy for refresh

2020-06-26 Thread Noel Grandin 
last line should have been

"rename new table to proper name"

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnU-mLeAQ9%3DLT6HQpVsv2%2B_xMsqVz7h2KZfSF1Ze4ftatw%40mail.gmail.com.

Re: [h2] H2 as read-only cache, strategy for refresh

2020-06-26 Thread Noel Grandin 
create new table from CSV

drop old table

rename old table to new table

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUNLqy_qAmL3iA78mGjoDpzrcnXgxsnM%2B-OXgQYEyZFAw%40mail.gmail.com.

Re: [h2] upgrade from 1.4.197 to 1.4.200 broke db

2020-06-03 Thread Noel Grandin 
yes, you're going to need to do a dump/restore across those versions

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWX1f-YB%3DCZPu2mdUjkwYC5sOnPxYU8hgYT%3DJzfpYG8yQ%40mail.gmail.com.

Re: [h2] TRANSACTIONAL option with creating/dropping tables/indexes

2020-05-30 Thread Noel Grandin 
On Sat, 30 May 2020 at 03:22, Eric Faulhaber  wrote:

>
>- As noted, CreateIndex.update() checks the transactional flag before
>committing.  Is this variable ever set to true, such that the commit would
>be bypassed?
>
> It is set when creating constraints inside a table and when the
TRANSACTIONAL keyword is encountered.



>
>-
>- Is there a functional reason why CREATE INDEX, DROP INDEX, and DROP
>TABLE should not support the TRANSACTIONAL option for temporary tables and
>the indexes on them?  Or is it simply that no one has implemented it yet?
>
> Most likely.



>
>- If we were to implement support for TRANSACTIONAL syntax on CREATE
>INDEX, DROP INDEX, and DROP TABLE, would such support likely be welcomed
>back into the project?
>
>
We're quite happy to accept changes, as long they come with at least one
unit test and don't break any of the other unit tests.


See build instructions here:

http://h2database.com/html/build.html

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWoREi%2BSyuQ%3D2MVMGP_dQq3vSH%3DKX0Lxz-HMYK%3DQcjz7w%40mail.gmail.com.

Re: [h2] Doubt about an array function [Question]

2020-05-06 Thread Noel Grandin 
if you make the ALIAS DETERMINISTIC it should cache results to some extent

On Wed, 6 May 2020 at 10:58, bocher  wrote:

> Dear all,
>
> I have the following sql query
>
> CREATE VIEW geomColumns as select t[0] as geometry_type, t[1] srid FROM 
> (SELECT findmetadata(COLUMN_TYPE) AS t from FROM INFORMATION_SCHEMA.COLUMNS 
> WHERE TYPE_NAME = 'GEOMETRY')
>
> where findmetadata return an array of values
>
> Is there a way to cache the result of the findmetadata function and avoid
> multiple calls in the main select query ?
>
>
> Regards
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/3731e4b1-77d5-4807-9064-7729de977f14%40googlegroups.com
> 
> .
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnU%2Bd3muWkKab1tdK5vdzUzdmjz-8JMA00FxKye8U8ZBTg%40mail.gmail.com.

Re: [h2] Group BY on "large" tables from file-system causes Out of Memory Error

2020-04-21 Thread Noel Grandin 
TBH, retrieving super large result sets is not something we optimise for.

If you really need that, you can try turn on the LAZY_FETCH feature.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnW6H71LKPK1qrdbFKqr7J4qoD%2BU0TM5FaZuQuhR%2BU%3Dh_Q%40mail.gmail.com.

Re: [h2] Group BY on "large" tables from file-system causes Out of Memory Error

2020-04-21 Thread Noel Grandin 
Which version is this ?

And what happens when you remove the dangerous options? (LOG and UNDO_LOG)

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnXnwWZm9hHe3KpLEFWZDQ%3DnbCQhF7L67jfhL-5HWdJhvA%40mail.gmail.com.

Re: [h2] database benchmark performace mysql vs cache vs system vs directory file

2020-04-13 Thread Noel Grandin 
I'm sorry to say, but you need to do more reading on how caches work, that
is not at all how it functions.

A brief overview of our architecture is here:
http://h2database.com/html/architecture.html

When people say that SQLite is faster than the filesystem, they mean it is
faster for the specific use-case of storing and retrieving structured
records.
It can be faster because it knows more about the structure and
access-pattern of the data than the filesystem.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnXyeHom0xh%3DD4fUxCPQO7fCXmzH%2BOL-5T%2B7VgEt3qE58A%40mail.gmail.com.

Re: [h2] database benchmark performace mysql vs cache vs system vs directory file

2020-04-12 Thread Noel Grandin 
Not really, look up how an LRU cache works e.g.

https://en.wikipedia.org/wiki/Cache_replacement_policies

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnV3NRqE9HOWmzVFPPJAriyJg5MB%2BxgMauW%3DZnFmbyhvwQ%40mail.gmail.com.

Re: [h2] is there any clustering, replication and backup functionality?

2020-04-12 Thread Noel Grandin 
No

>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnXKGAGeAJwjBivY9Q9i_H-yM%3DdK2O28UgqKLsb7TPviFw%40mail.gmail.com.

Re: [h2] database benchmark performace mysql vs cache vs system vs directory file

2020-04-11 Thread Noel Grandin 
On Sat, 11 Apr 2020 at 21:04, Danilo Santos 
wrote:

> exists one https://database.serverbenchmark.com/ like
> https://gpu.userbenchmark.com/ or https://cpu.userbenchmark.com/
> 
>
>
Not that I am aware of. Lots of databases prohibit the publication of
unauthorised benchmarks, so it is a little tricky.



> why are there still people using mysql instead of 1 portable database like
> h2database?
>
>
There are a lot of features in different databases, and different
tradeoffs. Your job as a developer is to find the database that has the
right tradeoffs for your application.

> how does the database cache system work? is it faster than the operating
> system's directory system? or that sqlite?
>
>
We have two cache systems, a fairly normal LRU cache and a scan-resistant
LRU cache. Our cache is more "efficient" than the operating systems cache
for the specific purpose of serving rows, but that is because our cache
operates at a different level.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnUYV0QKMEH23OggzDW6%3DE9TtMDXA05rUJ1cQKWPEHqsiw%40mail.gmail.com.

Re: [h2] does h2 support prefix index?

2020-04-10 Thread Noel Grandin 
http://h2database.com/html/features.html#computed_columns

On Fri, 10 Apr 2020 at 11:49, Chen Huang  wrote:

> I have a table with a TEXT/CLOB like column that need to be indexed:
>
> CREATE TABLE test (id INT PRIMARY KEY, val VARCHAR(512))
>
> but this index takes too much spaces:
>
> CREATE INDEX test_val ON test(val)
>
> Can I create an index with only a prefix of this column? like MySQL's
> prefix index:
>
> CREATE INDEX test_val ON test(val(20))
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/b97b567d-426d-4363-8526-fa89c7b26564%40googlegroups.com
> 
> .
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWp68Xdt5R-gnzYzjp9SO1JHsdk6POAqrpCXmSt5mHG2g%40mail.gmail.com.

Re: [h2] Support for simulating Firebird's RelaxedAliasChecking

2020-04-08 Thread Noel Grandin 

Yeah, sorry about that. If you want to work on this, the relevant code path is 
probably the ColumnResolver stuff.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ac5373d0-6ff0-672c-73ab-7415bf95af0a%40gmail.com.

Re: [h2] Support for simulating Firebird's RelaxedAliasChecking

2020-04-07 Thread Noel Grandin 
We don't have such a thing. You're welcome to attempt to implement it, but
I think we'd be unlikely to accept such a patch because it would mean
carrying ugly code in an important part of H2.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnV12rZWPpNEDi21iak6jLM5AumCzMxryKCG6fgAcdgbjA%40mail.gmail.com.

Re: [h2] Escaping reserved characters in database URL

2020-04-06 Thread Noel Grandin 
No, we have no encoding for stuff inside a "jdbc:h2:" URL. If you really
really need this, you could create a custom filesystem extension
using org.h2.store.fs.FilePath and then invent your own encoding.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnV5cdf%3DjV6u%2BJzZVDLxQSsufjGw8iAbvTyow6USutc0NQ%40mail.gmail.com.

Re: [h2] Data in application is read only after crash

2020-03-31 Thread Noel Grandin 






1) I suppose it is to be done from the H2 console ?


that will work, or just executing SHUTDOWN. Killing the DB process sometimes 
lead to corruption.


2) I have attached a log I have just received


You have a corrupt DB.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/5b9461cb-d7b3-68f4-abb2-0d44478229d3%40gmail.com.

Re: [h2] What is the best way to store large text values?

2020-03-31 Thread Noel Grandin 

If you're shutting the DB down normally, it should start up pretty quickly.

H2 is unfortunately not great with TEXT/LOB data. At that size you could try 
storing it in a VARCHAR column.

You'll have to be more specific about what exactly is slow.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/1e028e43-903d-a322-2c42-27fa63d6393a%40gmail.com.

Re: [h2] Data in application is read only after crash

2020-03-30 Thread Noel Grandin 
you will be probably need to do a dump/restore

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnULc6Crgj4GttUoGc_6q%2BpbSeb-_byrsVHux%3DHREC66fw%40mail.gmail.com.

Re: [h2] Migrate H2 to MS SQL

2020-03-27 Thread Noel Grandin 
Use the SCRIPT command to generate a .sql  script, and then tweak that as
necessary to successfully import into MS-SQL.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/CAFYHVnWcbF9W-tt8UekeV376mEoskzQLZNi%3DQsL5YCVsb8C2AQ%40mail.gmail.com.

  1   2   3   4   5   6   7   8   9   10   >