The equal operator can't be used between pg_type.oid and pg_type.typinput

[xuefengze]

The BasicTypeMapForResults class in ruby-pg will execute the SQL below, which results in an error:

ERROR rw-main handle_query{mode="simple query" session_id=0 sql= SELECT t.oid, t.typname, t.typelem, t.typdelim, ti.proname AS typinput FROM pg_type as t JOIN pg_proc as ti ON ti.oid = t.typinput
}: pgwire::pg_protocol: error when process message error=Failed to run the query: Failed to bind expression: ti.oid = t.typinput: function equal(integer, character varying) does not exist

SELECT t.oid, t.typname, t.typelem, t.typdelim, ti.proname AS typinput
	FROM pg_type as t
	JOIN pg_proc as ti ON ti.oid = t.typinput;

[lmatz]

works in pg:

martin=# SELECT t.oid, t.typname, t.typelem, t.typdelim, ti.proname, t.typinput 
FROM pg_type as t
JOIN pg_proc as ti ON ti.oid = t.typinput;
  oid   |                typname                 | typelem | typdelim |           proname            |           typinput           
--------+----------------------------------------+---------+----------+------------------------------+------------------------------
     16 | bool                                   |       0 | ,        | boolin                       | boolin
     17 | bytea                                  |       0 | ,        | byteain                      | byteain
     18 | char                                   |       0 | ,        | charin                       | charin
     19 | name                                   |      18 | ,        | namein                       | namein
     20 | int8                                   |       0 | ,        | int8in                       | int8in
     21 | int2                                   |       0 | ,        | int2in                       | int2in
     22 | int2vector                             |      21 | ,        | int2vectorin                 | int2vectorin

regproc seems to be a very special type in PG, printed as characters but able to equal with type oid

https://github.com/risingwavelabs/risingwave/blame/main/src/frontend/src/catalog/system_catalog/pg_catalog/pg_type.rs#L34
#12272

cc: @TennyZhuang @xiangjinwu, do you know how it is expected to work?

[xiangjinwu]

regproc seems to be a very special type in PG, printed as characters but able to equal with type oid

Yes. As we used quick hacks to provide pg_type.typinput as varchar while pg_proc.oid as int, we cannot continue on this path to allow equal operation between any varchar and int. We will need to support these internal data types natively - or defer the effort further if more hacks are possible.

[lmatz]

It looks like we have to provide a plugin if users want to access RW via ruby-pg

[github-actions]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.

[xiangjinwu]

Will be covered by #16830