Troubleshooting Postgres/JPA/Spring with long running stored procedure

Question

I'm having an issue with a couple of long running stored procedure calls that I'm calling from DAO that don't return control back to the service that called the DAO.

Here's the pattern: Within a AWS SWF workflow that we'll run once per year, I have a service that needs to call 5 stored procedures to populate 5 summary tables in our Postgres database. All 5 procedures have the same format and are pretty simple. They each read from the same detail table and filter/group the data in different ways to summarize. For each (called synchronously), the service:

starts a new transaction

Calls a DAO method

The DAO method calls the plpgsql stored procedure using JPA's StoredProcedureQuery.

When the DAO method completes, the service updates a separate activity table to indicate that the summary activity is complete.

For 3 of the 5 stored procedures, this pattern works. The stored procedure commits its work and the summary activity is updated. However, for the 2 longer-running stored procedure calls (20+ minutes), the DAO never returns control back to the service. The stored procedure methods do commit their work to the database, since that's the plpgsql way, but because the DAO never seems to return to the service, my activity table is not updated to show that that procedure has been completed. The SWF workflow keeps running as well, because the service call hasn't completed. I've left this running overnight, and found that though a stored proc saved data to the summary table, the service/DAO call was still running 12 hours later.

I'm not sure where my issue is with this, so I'd appreciate any ideas. Our application uses Postgres, Hibernate, JPA and Spring, and we use BoneCP to set up our data source. I don't have any timeouts set in my @Transaction annotations, and the behavior I'm seeing doesn't seem to indicate a spring timeout.

A few BoneCP settings:

 idleMaxAgeInMinutes=5
  idleConnectionTestPeriodInMinutes=1

All 5 stored procedures use this format:

CREATE OR REPLACE FUNCTION summarize_abc(p_id INTEGER) RETURNS BOOLEAN AS $$
        DECLARE
        BEGIN
         insert into summary_table (a, b, c, d, e, inserted_on)
         select p_id, b, c, d, sum(e) as AMOUNT, CURRENT_TIMESTAMP
         from detail_table s
         where
         s.id = p.id
         group by a, b, c, d;

 RETURN 1;
 END;
 $$ LANGUAGE plpgsql;

And the DAO methods are all like this:

 public void summarizeA(int id) {
        ProviderAccess pa = getProviderAccess();
        EntityManager entityManager = getEntityManager();
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery(pa.getSchemaName() +
                ".summarize_abc");
        query.registerStoredProcedureParameter("p_id", Integer.class, ParameterMode.IN);
        query.setParameter("p_id", id);
        query.execute();
    }

Troubleshooting Postgres/JPA/Spring with long running stored procedure

0 Answers0