[hibernate-dev] Multi-table Bulk id strategy using Common Table Expressions

[Vlad Mihalcea]

The CTE is bound to the currently executing query. It takes the ids that
need otherwise would be in a temporary table and feeds them using the
VALUES() expression.
It's interesting the reason for doing so: "reducing concurrency on system
tables every time Postgresql creates temp tables".
I'd have to do some research regarding that statement.

Does it sound like a good idea to you?

Vlad

On Wed, Mar 23, 2016 at 6:43 PM, Steve Ebersole <steve at hibernate.org> wrote:

> How does one reference a CTE across multiple deletes via JDBC?  A CTE, by
> definition, is available only during the execution of a single query.  In
> most databases you "work around" that by grouping statements into a block
> (GO, BEGIN/END etc).  This is what JDBC poorly attempt to mimic with
> PreparedStatement batching.  So practically I am just not sure how we'd do
> this from JDBC.  Are you thinking we'd build database-specific blocks?
>
>
> On Wed, Mar 23, 2016 at 8:05 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
> wrote:
>
>> Hi,
>>
>> I found a very interesting solution for the Bulk id multi-table strategy
>> for those cases when the DB management policy forbids creating Temporary
>> Tables.
>> This situation is documented in this Forum post:
>>
>> https://forum.hibernate.org/viewtopic.php?f=1&t=1043080&p=2489096#p2489096
>>
>> After doing some investigation, I found this project on GitHub:
>>
>> https://github.com/epiresdasilva/cte-multi-table-bulk-id-stategy
>>
>> After migrating it for Hibernate 5.x, I tested it and it works very nice,
>> generating queries like this:
>>
>> with HT_announecement ( id ) as (
>> values
>>     (?), (?) )
>> delete
>>     from
>>         announcement
>>     where
>>         (
>>             id
>>         ) IN (
>>             select
>>                 id
>>             from
>>                 HT_announecement
>>         )
>>
>> I think we should take this idea and write and come up with a built-in
>> strategy using CTE.
>>
>> What do you think?
>>
>> Vlad
>> _______________________________________________
>> hibernate-dev mailing list
>> hibernate-dev at lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>
>