Is there any reason for us to keep putting the language handler
functions in the public schema? If they were put in, say, pg_catalog (as
I see Peter's pl/sh language does for its handler) then the public
schema could be dropped by people who want to do that with no ill
effect, AFAICS.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Andrew Dunstan <andrew@dunslane.net> writes:
> Is there any reason for us to keep putting the language handler
> functions in the public schema?

I believe one of the issues there is that pg_dump doesn't dump functions
that are in pg_catalog. You could possibly fix it to make an exception
for functions that are referenced by pg_language entries, but I think
this would be a nontrivial change.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>Is there any reason for us to keep putting the language handler
>>functions in the public schema?
>>
>>
>
>I believe one of the issues there is that pg_dump doesn't dump functions
>that are in pg_catalog. You could possibly fix it to make an exception
>for functions that are referenced by pg_language entries, but I think
>this would be a nontrivial change.
>
>

Hmm. Could we not just add something this condition:

or pg_catalog.format_type(prorettype, NULL) != 'language_handler'

in pg_dump.c around line 2191?

I'd like to get this done, but I don't think I'll have time before July
1. How about putting it on the TODO list?


cheers

andrew



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Andrew Dunstan <andrew@dunslane.net> writes:
> Hmm. Could we not just add something this condition:
> or pg_catalog.format_type(prorettype, NULL) != 'language_handler'
> in pg_dump.c around line 2191?

You forgot about the validators, which are not so easily identified.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

I wrote:

>
> Hmm. Could we not just add something this condition:
>
> or pg_catalog.format_type(prorettype, NULL) != 'language_handler'
>
> in pg_dump.c around line 2191?
>
>

This won't work - it would miss the validators. Slightly more complex
than I thought.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 2005-06-24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> Is there any reason for us to keep putting the language handler
>> functions in the public schema?
>
> I believe one of the issues there is that pg_dump doesn't dump functions
> that are in pg_catalog. You could possibly fix it to make an exception
> for functions that are referenced by pg_language entries, but I think
> this would be a nontrivial change.

I think there's a case for a schema to exist by default that (a) does not
have public CREATE rights and (b) is not the default place to create
objects. There might even be a case for two of them: one on the default
search path and one not. Then stuff like languages, small contrib modules
(large ones should create their own schema), etc., can all default to
installing somewhere other than public.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

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_namespace where nspname = 'pg_catalog')
regression(# OR oid in (select lanplcallfoid from pg_language) OR oid in (select lanvalidator from pg_language));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on pg_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.06 rows=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=4 loops=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)
Total runtime: 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 where nspname = 'pg_catalog');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on pg_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.091 rows=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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tom Lane wrote:

>regression=# explain analyze select * from pg_proc WHERE NOT proisagg AND (pronamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
>regression(# OR oid in (select lanplcallfoid from pg_language) OR oid in (select lanvalidator from pg_language));
>
>
>

Yeah. I think we'd need to add "where langlcallfoid != 0" so we don't
pick up the internal/C/sql handlers. However, on closer inspection it
appears that doind all this in pg_dump would be lots more invasive than
I first thought. An alternative would be to adopt AndrewSN's suggestion
of an extra schema (or possibly two) created by initdb where we put
extra stuff. That would mean a very simple addition to initdb.c and no
changes to pg_dump, except ...


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

me either, but I wonder if we should provide an option on pg_dump to
restore function handlers found in public to whatever we decide about
the above.

thoughts?

cheers

andrew




---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Andrew Dunstan <andrew@dunslane.net> writes:
> Yeah. I think we'd need to add "where langlcallfoid != 0" so we don't
> pick up the internal/C/sql handlers. However, on closer inspection it
> appears that doind all this in pg_dump would be lots more invasive than
> I first thought.

Why --- what else is needed beyond the addition of those clauses to the
one query?

> me either, but I wonder if we should provide an option on pg_dump to
> restore function handlers found in public to whatever we decide about
> the above.

I don't see the need. If they were in public before, they can stay
there --- or the DBA can run createlang before running pg_restore to
put them where he wants.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>Yeah. I think we'd need to add "where langlcallfoid != 0" so we don't
>>pick up the internal/C/sql handlers. However, on closer inspection it
>>appears that doind all this in pg_dump would be lots more invasive than
>>I first thought.
>>
>>
>
>Why --- what else is needed beyond the addition of those clauses to the
>one query?
>
>

There are tests for both the function and the handler based on
finfo->dobj.namespace->dump that inhibit output if we're in the catalog
schema.

If we go down this path ISTM the simplest thing would be to add a field
to the FuncInfo object to allow it to be marked as a a handler/validator.

Also, I think pg_dump *never* quotes the handler name or qualifies it
with a schema name - that looks like it might be a bug, regardless of this.

>
>
>>me either, but I wonder if we should provide an option on pg_dump to
>>restore function handlers found in public to whatever we decide about
>>the above.
>>
>>
>
>I don't see the need. If they were in public before, they can stay
>there --- or the DBA can run createlang before running pg_restore to
>put them where he wants.
>
>
>
>

OK, I'm all for a simple life.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)