Patch: plan invalidation vs stored procedures
Martin Pihlak <martin.pihlak@gmail.com>
From: Martin Pihlak <martin.pihlak@gmail.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2008-08-06T18:28:22Z
Lists: pgsql-hackers
Attachments
- plan-inval-proc.patch (text/x-diff) patch
This is a followup for thread "plan invalidation vs stored procedures". The background is that it is impossible to change function return type without dropping and recreating. Unfortunately dropping a function ruins all of the prepared statements that reference that function (including other functions). To make matters worse the ruined plans are never invalidated and keep returning "cache lookup failed" error until replanned (requires admin intervention). Also the DBA that dropped the function probably has no clue that something is wrong - not before looking at the server logs at least. This is NOT good, especially if the database is supporting paid services. I have prepared proof of concept patch to support plan invalidation on function DROP (will add ALTER, REPLACE, etc. later). Currently the invalidation is handled by just dropping all the plans when invalidation message is received. The desired behaviour would be of course to drop only the affected plans. This needs function oid list to be present in PlannedStmt -- will look into this later. Probably a job for the planner. Changing statement result type is also currently prohibited in StorePreparedStatement. There maybe good reasons for this, but for the invalidation to be really useful, it should be enabled. Right now the attempt to change type renders the plan unusable -- "ERROR: cached plan must not change result type". Besides that, the patch could already be useful in some environments - if you are willing to trade the errors for some extra planning CPU. There are probably a lot of details that I have overlooked. I'd be really thankful for some constructive comments and criticism. Especially, what needs to be done to have this in the core. Feedback appreciated. regards, Martin