JPA 3 Hibernate + PostgreSQL 9.6.3 errors in queries with limitation

Question

After upgrade to Spring Boot 3 I`ve got next problem; My entity

@Data
@Table(name = "campaign_status")
@Entity
public class CampaignStatus {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "campaign_status_id")
    private Long campaignStatusId;

    @Column(name = "name")
    private String name;

}

My jpa repository interface

@Repository
public interface CampaignStatusDao extends JpaRepository<CampaignStatus, Long> {
    CampaignStatus findTop1ByName(String status);
}

And I get the next errorin console

Hibernate: 
    select
        c1_0.campaign_status_id,
        c1_0.name 
    from
        campaign_status c1_0 
    where
        c1_0.name=? fetch first ? rows only

2023-01-26T11:43:12.854+02:00  WARN 18183 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2023-01-26T11:43:12.854+02:00 ERROR 18183 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "$2"
  Position: 99

I tried with PostgreSQL 9.6.24 and there is no ERROR with the same code. But upgrade db version in production will be very painful. So may be there is a way to figure it out. By the way the same error if I try to execute jpa method with paging

Answer 1

The solution suggested in the comments helped.

Implement a custom dialect that extends org.hibernate.dialect.PostgreSQLDialect and overrides the getLimitHandler to return the org.hibernate.dialect.pagination.LimitOffsetLimitHandler.INSTANCE.

Then configure Hibernate to use that. specify in properties

spring.jpa.database-platform=your.package.CustomPostgreSQLDialect