sql: pg_proc.pronamespace is not populated for built-in functions

[knz]

Found while working on #88061.

Describe the problem

The pronamespace column in pg_proc is NULL for all the built-in functions.

This is incorrect:

• we want the OID of pg_catalog for all the "base" built-ins
• and the OID of crdb_internal for the crdb_internal built-ins

Additionally, once the above is achieved, the crdb_internal. prefix should be stripped in the proname column.

To Reproduce

SELECT n.nspname AS "Schema"
    p.proname AS "Name"
FROM pg_proc p
  LEFT JOIN ps_namespace n ON n.oid = p.pronamespace

cc @rafiss for triage.

Jira issue: CRDB-23240
Epic: CRDB-23454

[rafiss]

#94771 should fix this nevermind

[rafiss]

@knz which version are you using? on master the "we want the OID of pg_catalog for all the base built-ins" concern seems to be resolved already.

root@localhost:26257/defaultdb> SELECT n.nspname AS "Schema"
                             ->     , p.proname AS "Name"
                             -> FROM pg_proc p
                             ->   LEFT JOIN pg_namespace n ON n.oid = p.pronamespace limit 10;
    Schema   |           Name
-------------+----------------------------
  pg_catalog | _st_contains
  pg_catalog | _st_containsproperly
  pg_catalog | _st_coveredby
  pg_catalog | _st_coveredby
  pg_catalog | _st_covers
  pg_catalog | _st_covers
  pg_catalog | _st_crosses
  pg_catalog | _st_dfullywithin
  pg_catalog | _st_dfullywithinexclusive
  pg_catalog | _st_dwithin
(10 rows)

root@localhost:26257/defaultdb> SELECT n.nspname AS "Schema"
                             ->     , p.proname AS "Name"
                             -> FROM pg_proc p
                             ->   LEFT JOIN pg_namespace n ON n.oid = p.pronamespace where p.proname = 'array_length';
    Schema   |     Name
-------------+---------------
  pg_catalog | array_length
(1 row)

We can work on the fix for crdb_internal builtins though.

[knz]

After rebasing my PR, I was not able to reproduce the issue any more. So you were right to ask about the version - a recent PR has fixed the main bug. Thanks.

I do still think we need to do something about crdb_internal still and I see you have a PR open for it. Let's move the discussion there.