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

[Jason Pyeron]

Thanks, I was able to find a work around and more clearly identify my 
issue. https://github.com/h2database/h2database/issues/3058 . Splitting the 
view into 3 layers made the parser happy. Layer 1 - create select * from 
x.y views in the default schema. Layer 2 the CTE, layer 3 create view x.c 
as select * from V_TMPCTE

I will add h2 to our CI pipeline to track when issue goes away.

On Thursday, March 11, 2021 at 5:05:38 AM UTC-5 Noel Grandin wrote:

> 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/b2257d0f-16a1-46f9-913c-697ac19e0a92n%40googlegroups.com.

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

[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] RFE: Enable Common Table Expression (CTE with...) in INSERT, UPDATE, DELETE, CREATE TABLE AS, CREATE VIEW AS

[Jason Pyeron]

Would you mind sharing your fix branch information?

Thanks,

Jason

On Saturday, November 18, 2017 at 10:12:21 AM UTC-5 stu...@gmail.com wrote:

> 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-da...@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 view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f77f38b3-603f-41b4-81dd-f78ca3f2666bn%40googlegroups.com.

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

[Jason Pyeron]

Encountering an error when creating a view of a CTE. Create view select 1 
works, the CTE itself works, merging yields a Table T not found error.

Did this ever make it in to 1.4.200? If not are there any WIP branches I 
should look at?

-Jason

On Saturday, November 18, 2017 at 2:20:48 PM UTC-5 Noel Grandin wrote:

> On 18 November 2017 at 17:12, Stuart McMillan  wrote:
>
>> 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).
>>
>
> there a couple of tricks we use in other places that might help.
>
> (1) we tie the lifetime of stuff to the lifetime of the top-level 
> statement, not to the lifetime of the parent object. When dealing with 
> circular stuff, this helps
>
> (2) when deleting these things, we use a loop that looks like
>   while (true)
>   progress = false
>   for each live object
>   progress |=  try to delete object
>   if (!progress) break
>  
>
>>
>> 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?
>>
>>
> It's actually (mostly) simple. We just store the SQL required to re-create 
> each object, and then we re-parse and re-execute each chunk of SQL at 
> startup. There is a couple of stages to make this work, and some flags to 
> say things like "this is db init, do not try doing validation/compile/etc 
> yet"
> And this all runs sequentially on the system session.
>
>

-- 
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/ba257192-0ce3-4a90-a3fb-97b131e0b1b7n%40googlegroups.com.

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

[Noel Grandin]

On 18 November 2017 at 17:12, Stuart McMillan  wrote:

> 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).
>

there a couple of tricks we use in other places that might help.

(1) we tie the lifetime of stuff to the lifetime of the top-level
statement, not to the lifetime of the parent object. When dealing with
circular stuff, this helps

(2) when deleting these things, we use a loop that looks like
  while (true)
  progress = false
  for each live object
  progress |=  try to delete object
  if (!progress) break


>
> 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?
>
>
It's actually (mostly) simple. We just store the SQL required to re-create
each object, and then we re-parse and re-execute each chunk of SQL at
startup. There is a couple of stages to make this work, and some flags to
say things like "this is db init, do not try doing validation/compile/etc
yet"
And this all runs sequentially on the system session.

-- 
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]

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

[Noel Grandin]

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.

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

[t603]

I think, that CTE support for INSERT, UPDATE, DELETE would be great for the 
next 1.4.197 release. If it is ready now, could it be possible to merge it 
into next release? In other words it would be fine to have as much as 
finished and not wait for CTE in CREATE VIEW. 

Dne čtvrtek 16. listopadu 2017 14:39:17 UTC+1 Noel Grandin napsal(a):
>
>
>
> On 2017/11/16 3:29 PM, Stuart McMillan wrote: 
> > 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. 
> > 
>
> Got any ideas for fixing that? 
>
> Maybe we need to thread something through the code that indicates whether 
> we should be creating this on the system 
> session or on the current session? 
> Along with some asserts to keep it honest. 
>
> Or something more radical? 
>

-- 
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

[Noel Grandin]

On 2017/11/16 3:29 PM, Stuart McMillan wrote:
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.




Got any ideas for fixing that?

Maybe we need to thread something through the code that indicates whether we should be creating this on the system 
session or on the current session?

Along with some asserts to keep it honest.

Or something more radical?

--
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] RFE: Enable Common Table Expression (CTE with...) in INSERT, UPDATE, DELETE, CREATE TABLE AS, CREATE VIEW AS

[Noel Grandin]

@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.

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

[t603]

Hello, 

is there any plan to enable Common Table Expression (CTE with...) in 
INSERT, UPDATE, DELETE, CREATE TABLE AS, CREATE VIEW AS... SQL commands? It 
would be great to simplify these commands with CTE enabled compared to 
current very long joins. 

Regards, Stepan 

-- 
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.