PostgreSQL-How does a long running select query behave when its source tables are updated?
Posted on 2007-10-11
I'm wondering if a long running select query would restart processing if its source tables were modified during the run of the query. We acquired this query from a vendor and it runs on PostgreSQL 7.4.6. One example run of the query today took 7 minutes to return information on 4 invoices. I'm not currently trying to alter the query because additional code would have to be rewritten and that's not an option. However, I'm wondering if other activity in the source tables would cause the select query to restart processing. If so, I could make it so the other activity would be avoided on days when this query would be running frequently. I have not found any lock type being specified in the vendor's code, so I'm assuming it's using the default method (Access Share?). So, what happens to a select query that is running when its source tables are being updated and inserted into?