Error while calling stored procedure in Postgres using Spring boot JPA

Question

I have created a stored procedure in Postgres

    CREATE OR REPLACE PROCEDURE reset_disabled()  
    LANGUAGE plpgsql  
    AS $$
    DECLARE
    BEGIN  
    UPDATE regional_price_info
           SET disabled = NULL;
    END;  
    $$; 

I tried to call procedure from the repository class in two ways however both not worked

1. Use @Query annotation

   @Query(value = "CALL reset_disabled();", nativeQuery = true)
   void priceActivator();
   

   I got error

   org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet

2. I tried with @Procedure annotation

   @Procedure(value = "reset_disabled")
   void priceActivator2();
   

   I got error

   2023-02-22T07:43:00,013 [scheduling-1] [INFO ] com.zageno.pricing.activator.PriceActivator priceActivatorByValidity [28] - Triggering price activator procedure Hibernate: {call reset_disabled()} 2023-02-22T07:43:00,016 [scheduling-1] [WARN ] org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions [137] - SQL Error: 0, SQLState: 42809 2023-02-22T07:43:00,017 [scheduling-1] [ERROR] org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions [142] - ERROR: reset_disabled() is a procedure Hint: To call a procedure, use CALL. Position: 15

   In above error log (highlighted in bold) , Hibernate query using "CALL" and error is suggesting use "CALL" this seems contradictory.

Answer 1

Well I figured out solution

If SP performs a DML operation like (Insert,Update) add following annotations along with @Query;

@Transactional 
@Modifying(clearAutomatically = true)
@Query(value = "CALL reset_disabled();", nativeQuery = true)
void priceActivator();

Answer 2

you need to add @Transactional @Modifying tags above of the @Query because without them spring thinks you return resultset within your query.

@Transactional
@Modifying
@Query(value = "CALL reset_disabled();", nativeQuery = true)
void priceActivator();