CODEWITHSUNDEEP
phppostgresqltriggersplpgsqlpostgresql-8.4
Alex
Alex

Reputation: 546

PostgreSQL trigger function taking too long leading to php timeout

Problem

I have a plpgsql function prepare_download_file that is passed in several user-defined parameters from a php script, inserts these parameters in to a log table adc_query_log and then uses them to build an sql query that selects a bunch of data, copies down the results to csv and finally returns a unique id which is needed in the php script. The id is a crucial part since the php script uses it to select the 3 output files, pack them in to a .zip archive, and send an email to the user with a download link.

Depending on the parameters, the query may execute pretty quickly, other times I've found that it takes longer than 45 seconds and times out as far as the php script is concerned so it doesn't get the unique id it needs.

Proposed solution

Split the prepare_download_file function into two parts so that the first part only inserts the parameters in to the log table and returns the unique id, which should happen instantaneously (first column id is type serial):

INSERT INTO adc_query_log
  VALUES (DEFAULT,query_type,severity,year_range,local_authorities)
RETURNING id INTO session_id;

The second part make_download_file() is executed via a trigger function and returns NULL:

CREATE TRIGGER make_download_file AFTER INSERT ON adc_query_log
  FOR EACH ROW EXECUTE PROCEDURE make_download_file();

which reads the inserted values from the table to build the sql query and then does all the heavy lifting - supposedly after the unique id has been returned to the php script.

Question

Does the RETURNING id INTO session_id bit wait until the trigger function has completed before marking this transaction as complete and then returning the id? If not, is there a way I can force it not to wait?

Server is running PostgreSQL 8.4

Upvotes: 1

Views: 1427

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247270

The statement won't return until the trigger function has finished.

I would put the jobs that take a long time into a “queue” table and have a separate process that works off the jobs in the queue asynchronously. That way you don't have to worry about timeouts.

For communication between the processes, you can either pull the status regularly, or you can use PostgreSQL's LISTEN and NOTIFY.

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

I 90% sure you will have to wait for trigger to end before get the id, so the timeout would happen again.

So in this case you should get the id ( as you say is almost instant ), and then make a second call to the store procedure using the id

Upvotes: 0

Related Questions