Re: SPI_ERROR_CONNECT within pl/pgsql, PG 8.4 - Mailing list pgsql-bugs

Hi,

> I'm convinced that 8.3 has the same bug, in the sense that it could fail
> this way if it had to revalidate a cached plan.  Probably the reason you
> didn't see it before is that 8.4 has more conditions in which it will
> invalidate cached plans.

Ok

> In particular, is it possible that this
> failure occurs when you try to call a plpgsql function that has just
> been replaced via CREATE OR REPLACE FUNCTION?

Ah, you might be on to something here, we have a few patterns that match
that....

-----

When starting a certain wizard in our app, we need to keep track of complex
dependancies; in order to do this, we call a custom function called
'tree_amounts_blockade_setup', which basically executes the following steps:

- create temp table
- create index on that table
- create before trigger on that table based on an existing pgsql function
- create after trigger on that table based on another existing pgsql function

Now, if I look at the distribution of users running into this error today, it
looks like this:

      1 a
      1 b
      1 c
      1 d
      2 e
      3 f
      3 g
      3 h
      3 i
      4 j
      4 k
     10 l
     16 m
     16 n
     22 o
    120 p
    853 q

User 'q' is precisely the single user that would indeed use this 'wizard' the
most and therefor would call 'tree_amounts_blockade_setup' the most. It has to
be said though that the winner's score is a bit skewed, I verified this and it
turns out the specific user was happily ignoring all errors and continuing the
work (or at least trying to) without reconnecting ;)

-----

One other thing that comes to mind is the fact that we have functions that
when called should include certain temp tables when they exist. We can and do
check for existence of a temp table through something like

(SELECT 1 FROM information_schema.tables WHERE table_schema ~''pg_temp'' AND
table_name = ''t_creditor_address'')

but for this to work the temp schema has to exists. For this we 'cheat', every
user will create and drop a temp table once upon connecting to the database,
the function itself doublechecks and bails out IF NOT pg_my_temp_schema() > 0


> [ looks at CVS logs ... ]  Another case that will cause plan
> invalidation in 8.4 and not 8.3 is creating or dropping a schema.
> Do you do a lot of that?

Apart from the temp schema described above, we don't do that at all.


==========

I'd also like to mention still our C-trigger..... :

- it starts of with three plan variables containing '0' and it uses
SPI_saveplan() to fill these with a cached plan

- the three underlying tables will never change structure during such a
session, so I expected these variables / plans to never change

- if these static SPIPlanPtr variables are non-zero, the cached plans will be
reused in a SPI_execute_plan() call

Now the docs of SPI_saveplan say the following:

"If one of the objects (a table, function, etc.) referenced by the prepared
plan is dropped or redefined, then future executions of SPI_execute_plan may
fail or return different results than the plan initially indicates. "


Given the fact that 8.4 invalidates plans much more often, this might become
tricky..... the 'SPI_execute_plan may fail' part can be handled, but I'm a bit
worried about the 'may return different results' part..... Is there a way to
determine (efficiently) that such a save plan has been invalidated?

Or maybe for the future: would a kind of guarded plan pointer be handy...?







--
Best,




Frank.