CREATE or REPLACE function pg_catalog.*

John Hansen <john@geeknet.com.au> writes:

When doing CREATE or REPLACE FUNCTION of a builtin function, it seems to
have no effect if its in the 'C" language. SQL functions seem to work,
but as neilc pointed out, it may be due to the SQL function being
inlined.
The builtin function is still called, not the userdefined function for
'C' language functions.

You can't override a builtin C function that way because there is a
built-in map from function OID to builtin function address, and it's
consulted before trying to look in pg_proc.

This behavior is not really open to negotiation; not only on grounds of
speed, but on grounds of circularity. (The functions used in the
process of looking up entries in pg_proc itself obviously must have such
a short circuit...) You'd have to build a modified backend in which the
particular functions you want to replace are not listed in the builtin
mapping table.

regards, tom lane

Isn't there a load/unload function for the .so that would work
in this case?

--elein

elein <elein@varlena.com> writes:

Isn't there a load/unload function for the .so that would work
in this case?

Won't affect the fmgr_builtins table ... he wanted to replace a builtin,
not a previously-dynamically-loaded function.

regards, tom lane

The builtin function is still called, not the userdefined

function for

'C' language functions.

You can't override a builtin C function that way because
there is a built-in map from function OID to builtin function
address, and it's consulted before trying to look in pg_proc.

That doesn't make sense, since if I delete the entry from pg_proc and
then create the funtion, everything works fine.

Eg: delete from pg_proc whete proname = 'funcname'; create function
pg_catalog.funcname();

This behavior is not really open to negotiation; not only on
grounds of speed, but on grounds of circularity. (The
functions used in the process of looking up entries in
pg_proc itself obviously must have such a short circuit...)
You'd have to build a modified backend in which the
particular functions you want to replace are not listed in
the builtin mapping table.

regards, tom lane

Well, as someone pointed out, if it is possible to execute replace
function on a builtin, then it should work.

... John

"John Hansen" <john@geeknet.com.au> writes:

You can't override a builtin C function that way because
there is a built-in map from function OID to builtin function
address, and it's consulted before trying to look in pg_proc.

That doesn't make sense, since if I delete the entry from pg_proc and
then create the funtion, everything works fine.

Sure, because then the new entry has a new OID that doesn't match any
entry in the fmgr_builtin table.

Well, as someone pointed out, if it is possible to execute replace
function on a builtin, then it should work.

[ shrug... ] Nobody promised that you could change any arbitrary thing
without breaking your system. Superusers are allowed to do "delete from
pg_proc", too, but that doesn't mean you'll be pleased with the results.

regards, tom lane

On Thu, 2004-11-11 at 04:11, Tom Lane wrote:

You can't override a builtin C function that way because there is a
built-in map from function OID to builtin function address, and it's
consulted before trying to look in pg_proc.

This behavior is not really open to negotiation

Then shouldn't we disallow the CREATE OR REPLACE FUNCTION?

-Neil

Neil Conway <neilc@samurai.com> writes:

On Thu, 2004-11-11 at 04:11, Tom Lane wrote:

You can't override a builtin C function that way because there is a
built-in map from function OID to builtin function address, and it's
consulted before trying to look in pg_proc.

Then shouldn't we disallow the CREATE OR REPLACE FUNCTION?

We shouldn't disallow it completely; for instance you could validly
change the volatility or other attributes that way.

There might be an argument for rejecting an attempt to replace the
prolang or prosrc values of a built-in, but frankly I think it's a waste
of effort to code up such a thing ...

regards, tom lane