Patch: plan invalidation vs stored procedures

From: Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Patch: plan invalidation vs stored procedures
Date: 2008-08-06 18:28:22
Message-ID: 4899ED46.1060002@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
plan-inval-proc.patch text/x-diff 9.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Marko Kreen 2008-08-06 18:28:28 Re: plan invalidation vs stored procedures
Previous Message Merlin Moncure 2008-08-06 18:13:58 Re: plan invalidation vs stored procedures