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 <st...@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.v...@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@lists.jboss.org >> https://lists.jboss.org/mailman/listinfo/hibernate-dev >> > _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
