Re: language handlers in public schema? - Mailing list pgsql-hackers

From Tom Lane
Subject Re: language handlers in public schema?
Date
Msg-id 21335.1119578277@sss.pgh.pa.us
Whole thread Raw
In response to Re: language handlers in public schema?  (Andrew Dunstan <andrew@dunslane.net>)
Responses Re: language handlers in public schema?
List pgsql-hackers
Andrew Dunstan <andrew@dunslane.net> writes:
> This won't work - it would miss the validators. Slightly more complex 
> than I thought.

Well, there's always the brute-force solution:

regression=# explain analyze select * from pg_proc WHERE NOT proisagg AND (pronamespace != (select oid from
pg_namespacewhere nspname = 'pg_catalog') 
 
regression(# OR oid in (select lanplcallfoid from pg_language) OR oid in (select lanvalidator from pg_language));
                                         QUERY PLAN                                                  
 
--------------------------------------------------------------------------------------------------------------Seq Scan
onpg_proc  (cost=3.16..90.79 rows=1754 width=283) (actual time=17.562..22.686 rows=115 loops=1)  Filter: ((NOT
proisagg)AND ((pronamespace <> $0) OR (hashed subplan) OR (hashed subplan)))  InitPlan    ->  Seq Scan on pg_namespace
(cost=0.00..1.06rows=1 width=4) (actual time=0.063..0.083 rows=1 loops=1)          Filter: (nspname =
'pg_catalog'::name) SubPlan    ->  Seq Scan on pg_language  (cost=0.00..1.04 rows=4 width=4) (actual time=0.011..0.042
rows=4loops=1)    ->  Seq Scan on pg_language  (cost=0.00..1.04 rows=4 width=4) (actual time=0.022..0.057 rows=4
loops=1)Totalruntime: 24.760 ms
 
(9 rows)

I had thought this would be excessively slow compared to the present

regression=# explain analyze select * from pg_proc WHERE NOT proisagg AND pronamespace != (select oid from pg_namespace
wherenspname = 'pg_catalog');                                                 QUERY PLAN
                 
 
--------------------------------------------------------------------------------------------------------------Seq Scan
onpg_proc  (cost=1.06..78.22 rows=1002 width=283) (actual time=10.537..11.915 rows=112 loops=1)  Filter: ((NOT
proisagg)AND (pronamespace <> $0))  InitPlan    ->  Seq Scan on pg_namespace  (cost=0.00..1.06 rows=1 width=4) (actual
time=0.068..0.091rows=1 loops=1)          Filter: (nspname = 'pg_catalog'::name)Total runtime: 12.799 ms
 
(6 rows)

but it doesn't look intolerable at all.

Next question is whether there are any other places that would be
affected besides createlang/droplang.  I can't think of any offhand,
but ...
        regards, tom lane


pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [PATCHES] O_DIRECT for WAL writes
Next
From: "Qingqing Zhou"
Date:
Subject: Re: regression failure