PostgreSQL: CTE table failure with transaction #1540
Description
Version
4.5.16 , 5.0.1
Context
While working on Hibernate Reactive, when using Common Table Expressions I noticed that
with PostgreSQL the query
sqlconnection.begin().compose( transaction -> sqlconnection
.preparedQuery( sql )
.execute( parameters )
.compose( result -> transaction.commit() )
with
sql = "with HTE_SpellBookJS (id,title,forbidden) as materialized
(select sb1_0.id,sb1_0.title,sb1_0.forbidden from (values ($1,$2,$3)) sb1_0(id,title,forbidden)),
dml_cte_BookJS (id)
as (insert into BookJS as sb1_1(id,title) select e.id,e.title from HTE_SpellBookJS e returning id),
dml_cte_SpellBookJS (id)
as (insert into SpellBookJS as sb1_0(id,forbidden) select e.id,e.forbidden from HTE_SpellBookJS e returning id)
select count(*) from dml_cte_BookJS id";
and
Tuple parameters = Tuple.wrap( new Object[]{1,"abc", true} );
throws:
io.vertx.pgclient.PgException: ERROR: current transaction is aborted, commands ignored until end of transaction block (25P02)
What it's strange is that if the begin transaction is removed
return sqlconnection
.preparedQuery( sql )
.execute( parameters )
everything works fine.
Using PostgresSQL 17.5 with the schema
create table Author (book_id integer, id integer not null, "name" varchar(255), primary key (id))
create table BookJS (id integer not null, published date, title varchar(255), primary key (id))
create table SpellBookJS (forbidden boolean not null, id integer not null, primary key (id))
alter table if exists Author add constraint FK3grg42vpuomt5xd9id6dfik8u foreign key (book_id) references BookJS
alter table if exists SpellBookJS add constraint FKefqo828cmtop6xcqolmedqwwb foreign key (id) references BookJS
Steps to reproduce
No response
Do you have a reproducer?
No response