The writings of Merlin Moncure, professional database developer, about work, life, family, and everything else.

Tuesday, September 04, 2007

PostgreSQL 8.3 Features: Plan Invalidation

As previously stated, PostgreSQL 8.3 is shaping up to be a great release of the software. One of the biggest beneficiaries of the new feature set are pl/pgsql developers. 8.3 stands to be the best thing to happen to pl/pgsql since 8.0 brought dollar quoting to the table...before which some might argue serious development with the language bordered on the impractical. The major new features are vastly improved cursor handling (including UPDATE/DELETE), improved handling for set returning functions, arrays of composite types (for efficient passing of data to/from functions), and, especially, automatic invalidation of cached plans. While plan invalidation solves many tangential issues their greatest impact will certainly be in server side development of stored procedures.

When a pl/pgsql function executes for the first time in a session, the server 'compiles' it by parsing the static (not passed through EXECUTE) sql and generating plans for all the queries. This is an essential mechanism for fast repeated execution of server side functions because it allows many tedious, cpu intensive portions of query execution to be optimized. One part of this optimization involves looking up various database objects involved in the query and storing identifiers that are internal to the database. While this is useful and good, it has an unfortunate side effect in that if the structures the database were referencing in the plan were no longer valid, the function plan itself is no longer valid and may raise errors if executed. There are various workarounds to address this that are mostly obsolete (plan invalidation doesn't catch references to functions yet).

Here is the canonical example (and, arguably, the justification for the effort) of demonstrating how plan invalidation works:

create or replace function test_func() returns void as
create temp table tt as select 'hello world!'::text;
perform * from tt;
drop table tt;
$$ language plpgsql;

select test_func();
select test_func();

ERROR: relation with OID 87831 does not exist

The first invocation of test_func() generates a plan for the function. The second time around, the plan is pointing to a stale reference to 'tt' and the function fails. In PostgreSQL 8.3, this function succeeds without error. The database determines when tt is dropped that there are plans referencing it and throws them out. Historically, this was a huge impediment to pl/pgsql development because there was no particularly safe way to create and drop temporary tables from within a function. It is natural to want to create a table for temporary storage, do all kinds of things to it, and release it when done -- but sadly was not generally possible without various workarounds. For this reason, historically it was often better to use cursors inside pl/pgsql functions for holding working data which pushes code into a more procedural style. Now there is room for more set oriented style data processing which should be an attractive alternative to any dba.


Peter Manchev said...


great post, actually I was thinking about pl/pgsql and comparing it with pl/sql function result cache, featured in Oracle 11g - see

Is it possible to get pl/pgsql function result cache functionality into PostgreSQL real soon?


Merlin Moncure said...

plan invalidation is working now in cvs. I am betting for postgresql to go into beta some time into oct. which suggests release in jan-feb timeframe. These are guesstimates at best...everything depends on how HOT is handled which is still up in the air at this point.

Jeff said...

Can you expand on the "function references" and other cases where plan invalidation doesn't catch everything?

I'm glad you elevated the importance of this feature. It got in 8.3 early, and is one of the features that I'm looking forward to -- more for peace of mind than anything else.

This is also a big deal for slony.

andmazoti said...

It works for me only when I create a phisical table in my database. I'm using version 8.4 and will test it in version 9.1

andmazoti said...

it works for me only when I create a phisical table or remove the bytea column from the temp table.

Im using version 8.4 but will try with 9.1 too.