Thread: CREATE or REPLACE function pg_catalog.*

Hi,

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.

... John

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

On Wed, Nov 10, 2004 at 12:11:27PM -0500, Tom Lane wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

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