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
