Replace source using UPDATE pg_proc

[kputnam]

The current method of replacing an existing function with an instrumented uses CREATE OR REPLACE FUNCTION. Since we don't save all the metadata like COST or other attributes, those can be removed on the instrumented version, and won't be restored with piggly untrace. This approach also requires recording parameter names, defaults, and other parts of the function signature so that we can properly redefine the function.

It seems like there's a simpler way to do this, which avoids those problems.

UPDATE pg_proc SET prosrc = '...' WHERE oid = 'snippets(int, int)'::regprocedure;

This preserves any parameter names, cost annotations, and other attributes and might also avoid other problems. Some work is needed to ensure this won't cause other issues. For example, if we only store the OID and the source, what do we do if the UPDATE statement doesn't update any rows (presumably the user replaced the proc since we last looked)?

[kputnam]

Related discussion: https://www.postgresql.org/message-id/AANLkTinEsQxbT_DYBUd9qOgh%2BXJNtqF7y3Hse3qsYX0m%40mail.gmail.com

[xzilla]

In case anyone was thinking of implementing things this way, I'll +1 the idea to use pg_get_functiondef. Note it can be difficult to work with, but something like the following should work:

SELECT pg_get_functiondef(f.oid)||';'                                                               
FROM pg_catalog.pg_proc f                                                                                                   
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public' and f.proname='film_in_stock';

Note you can adjust or omit the bits of the where clause to get different results back. Hope this helps!