Transactions repeatedly query all types, causing significant delays.

[Imran-imtiaz48]

The delays we're experiencing in certain transactions are due to the 'fetch types' functionality, which runs the following query:

select b.oid, b.typarray 
from pg_catalog.pg_type a 
left join pg_catalog.pg_type b on b.oid = a.typelem 
where a.typcategory = 'A' 
group by b.oid, b.typarray 
order by b.oid;

Each transaction re-executes this query, and when we tried disabling it, we encountered the following error: PostgresError: malformed array literal: "". The delay caused by this query is over 200ms on our side.

Is there any way we can mitigate this delay without triggering errors?

[phosmium]

Yea, I've made an issue about this some time ago. It shouldn't run on each transaction, but on each unused connection it picks from the pool. The solution I made for this, was to "prepare" all the connections in the pool. As in, using each one to perform a quick query so that all of them fetch the types.

It's a workaround, but works and performs in less than half a second, if you use them all at once.
You can also make your own fork of postgres.js and cache the types in https://github.com/porsager/postgres/blob/master/src/connection.js#L742

[stephenh]

@phosmium fwiw would be great if you could post a PR that caches the types (assuming you've implemented your suggestion of caching); I would selfishly like to copy/paste it. :-D