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

[Stuart McMillan]

Noel,

Yes - certainly the session locking  (system or connection session) is one
issue I have been wrestling with - especially when the CTE persistent views
have to join the parent views lifecycle - I did a hacky-fix which I am not
happy about to bypass that (and might not pass a code review).

If we sidestep (ignore for the time being) my hacky attempts to bypass that
issue - the next stumbling block was making the persistent views truly
persistent - even across database reconnections. The blocker there is in
reviving the cte views from their db persisted state - which fails dismally
while using the plan SQL to re-create the cte views. I have to admit I was
at a loss at how to fix that, since I do not understand the mechanism in
which permanent DB-objects are persisted in H2. Which classes and fields
are persisted in the db across connections/db-restarts for views and tables?

I am can re-open my fix branch and show what I have (test cases which fail)
- but would need some guidance in the areas I am not understanding.

Also I can try to integrate your meta locking detection stuff - in case
this is actually part of my problem

Cheers Stuart

On Fri, Nov 17, 2017 at 8:22 AM, Noel Grandin  wrote:

> Stuart,
>
> I have just pushed some meta-locking debug infrastructure.
>
> If you add the
>
>-Dh2.check2=true
>
> command line option when running H2, it will be turned on.
>
> The TestAll code also turns it on.
>
> The extra code fails nice and early when a single thread attempts to lock
> the meta info using two different sessions.
>
> ​
>
> --
> 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 post to this group, send email to h2-database@googlegroups.com.
> Visit this group at https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

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

[Stuart McMillan]

All except create view as are currently working on master branch. Create
view as is taking much much longer than i thought, as cte are hard to
persist between connections in current architectured form.

On Nov 16, 2017 6:58 AM, "Noel Grandin"  wrote:

> @stumc was working on such things, but he seems to have taken a break ?​
>
> --
> 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 post to this group, send email to h2-database@googlegroups.com.
> Visit this group at https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] NPE in simple Common Table Expression

[Stuart McMillan]

Thanks Mike,

I'll have a look at this case and see what I can think of to fix it.
Thanks for taking the time to report it!

stumc

On Mon, Oct 9, 2017 at 5:15 AM, Mike Goodwin  wrote:

> Yes, I did try with the head (I assume master) revision at the time - 7099
> I think.
>
>
> On Fri, Oct 6, 2017 at 7:34 AM, Noel Grandin 
> wrote:
>
>> Can you try a master build, we have fixed various WITH problems recently
>> (thanks to people like @stumc)​
>>
>> --
>> 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 post to this group, send email to h2-database@googlegroups.com.
>> Visit this group at https://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> 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 post to this group, send email to h2-database@googlegroups.com.
> Visit this group at https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Re: [h2] Re: NPE in simple Common Table Expression

[Stuart McMillan]

Hi Yannick,

You are testing a use case of WITH-CTE's that I never thought about.

Under the hood, the CTE's are implemented as temporary session views which
get removed after the initial WITH expression has been used (used once, I
believe). So for this implementation's use in your scenario is almost never
going to work.  :-(

You need an implementation where WITH CTE's can be are expected to

i) persist as part of a permanent view (maybe the WITH statement CTE's can
dynamically re-create themselves when needed OR be scoped to live longer
but not so as to allow their names to collide with other user's table or
view objects.)
ii) be repeatably invoked every time the view is used (not just once)

This will take some time to think about - but I am very appreciative that I
have your test case to focus my thoughts on.
Thanks for taking the time to report it!

stumc

On Mon, Oct 9, 2017 at 5:19 AM, Yannick Tailliez  wrote:

> Hi,
>
> In my case, I do :
>
> URL : jdbc:h2:tcp://127.0.0.1:9092/database/test;MODE=
> MSSQLServer;MVCC=TRUE;DEFAULT_LOCK_TIMEOUT=5000
>
> CREATE TABLE my_tree (
>  id INTEGER,
>  parent_fk INTEGER
> );
>
> INSERT INTO my_tree ( id, parent_fk) VALUES ( 1, NULL );
> INSERT INTO my_tree ( id, parent_fk) VALUES ( 11, 1 );
> INSERT INTO my_tree ( id, parent_fk) VALUES ( 111, 11 );
> INSERT INTO my_tree ( id, parent_fk) VALUES ( 12, 1 );
> INSERT INTO my_tree ( id, parent_fk) VALUES ( 121, 12 );
>
> CREATE OR REPLACE VIEW v_my_tree AS
> WITH RECURSIVE tree_cte (sub_tree_root_id, tree_level, parent_fk,
> child_fk) AS (
> SELECT mt.ID AS sub_tree_root_id, CAST(0 AS INT) AS tree_level,
> mt.parent_fk, mt.id
>   FROM my_tree mt
>  UNION ALL
> SELECT sub_tree_root_id, mtc.tree_level + 1 AS tree_level,
> mtc.parent_fk, mt.id
>   FROM my_tree mt
> INNER JOIN tree_cte mtc ON mtc.child_fk = mt.parent_fk
> )
> SELECT sub_tree_root_id, tree_level, parent_fk, child_fk FROM tree_cte;
>
> First time, the CREATE VIEW work well.
>
> A second call return to me a NullPointerException :
>
> Error: General error: "java.lang.NullPointerException"; SQL statement:
> CREATE OR REPLACE VIEW v_my_tree [...] [5-196]
> SQLState:  HY000
> ErrorCode: 5
>
>
> And a third (and all following) call return to me :
>
> Error: Timeout trying to lock table "SYS"; SQL statement:
> CREATE OR REPLACE VIEW v_my_tree [...] [50200-196]
> SQLState:  HYT00
> ErrorCode: 50200
>
>
> --
> 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 post to this group, send email to h2-database@googlegroups.com.
> Visit this group at https://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Requesting contributor access to push, create pull request and create/edit issues for CTE fixes

[Stuart McMillan]

Hi H2ers,

I am looking at fixing some of the CTE issues listed in the roadmap page 
and gitub issues list.

Can you give me access to the project so I can push ? My github user id is 
stumc.

I wrote the code, it's mine, and I'm contributing it to H2 for distribution 
multiple-licensed under the MPL 2.0, and the EPL 1.0 
(http://h2database.com/html/license.html).

Thanks

Stuart McMillan

-- 
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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.