is there any way of simulating an autocommit in functions in postgres

Question

I know you can't control transactions in functions or procedures, but I'm wondering if there's something like that or some alternative.

The problem: I have a function that's very expensive that turns things like a customer id into a nice html report. Trouble is - it takes seconds... so I've put into the function something that basically looks at a cache to see if a pre-rendered one exists, returning it if it does - and if it doesn't - it adds to the cache afterwards - so it will only ever render things once.

Now - given most things will never change - I sort of want to do it across everything - but given the time - it will probably take about 1 year to run - which is ok actually - this system has to run for ten. Trouble is - I don't want it to lock anything on the database, so I sort of want it trickle along, doing 1 at a time and committing immediately.

I investigated pg_cron, because that seemed an option, but the version of aurora I am using doesn't support it. Any ideas how I'd do this inside the database?

Answer 1

By all means, don't code that as a function running inside the database. It is fine to do the calculations in the database, but generating a report and iterating over customers belong into client code. That way, committing each report is not a problem.

Answer 2

Add a text column to the customer table to hold the html report.

Put trigger(s) on table(s) whose content influences the html report that refreshes the report column.

This gives you instant retrieval and only (re)calculates it when needed.

---

Or if data is stable:

create table customer_report (
  customer_id int not null primary key,
  report text not null
);

insert into customer_report
select id, my_report_proc(id)
from customer;