Re: cache lookup failed from empty plpythonu function

On 2013-01-24 15:03:36 +0100, Sandro Santilli wrote:
> I've found a bug in plpythonu resulting in a "cache lookup" failure.
> Here's the SQL to reproduce (thanks Andres):
>
>  CREATE EXTENSION plpythonu;
>  CREATE OR REPLACE FUNCTION t() RETURNS trigger AS 'pass' LANGUAGE 'plpythonu';
>  CREATE TABLE a();
>  CREATE TABLE b();
>  CREATE TRIGGER check_quota AFTER INSERT ON a FOR EACH ROW EXECUTE PROCEDURE t();
>  INSERT INTO a DEFAULT VALUES;
>  DROP TABLE a;
>  CREATE TRIGGER check_quota
>   AFTER INSERT ON b FOR EACH ROW EXECUTE PROCEDURE t();
>  INSERT INTO b DEFAULT VALUES;
>  --ERROR: cache lookup failed for relation 20200
>  --Time: 0.570 ms
>  -- cleanup with DROP TABLE b; DROP FUNCTION t();
>
> Logs show:
>
>  ERROR:  XX000: cache lookup failed for relation 3694414
>  LOCATION:  PLy_procedure_argument_valid, plpython.c:1469
>
> Andres (in IRC #postgresql) said:
>
>   <andres> hm, I think I see the problem. The caching there looks pretty
>            broken to me.
>   <andres> i.e. the caching doesn't work *at all* if a function is used
>            as a trigger for more than one table and this is just fallout
>            of that.
>   <andres> Looks like a typo to me, procedure_get should use the table's
>            oid instead of fn_oid when doing lookups in PLy_trigger_cache.

ISTM the caching code for plpythonu trigger functions has been broken
for some time. The bug seem to be that PLy_procedure_get looks up the
function in a separate cache for trigger functions (PLy_trigger_cache)
but still only uses the function oid for lookup.
So the same function can be looked up for two tables and when the cached
entry originally refers to the old table that has been dropped we see
the above error.

The fix seems tob e to make PLy_trigger_cache have a key of (reloid,
fn_oid) instead of just fn_oid.

Other suggestions?

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services