Problems with user-defined types having different type OIDs across load-balanced PG server

[faibistes]

(See #2366)

I connect via haproxy to multiple load-balanced postgresql, and I get that error if I call a function that returns a user defined type. I call ReloadTypes every time I call that function, but there shoud be a better way. I don't know, maybe the connection could have a NoTypesCache property.

[roji]

@faibistes it's not possible to have something like NoTypesCache - every single query executed against PostgreSQL returns results which reference types; Npgsql must be able to know these types. What you're proposing would basically mean that every single query would have to be "prefixed" by the full type loading mechanism, which is quite heavy - this simply doesn't work.

One possible solution here is for your application to catch the exception about an unknown type, called ReloadTypes at that point, and then re-execute the original query. This isn't something Npgsql can do for you.

Otherwise, can you provide a bit more context on why so many user-defined types are being created all the time? That seems like a problematic design.

[faibistes]

Maybe I didn't get myself clear, I didn't mean that a .net function returned a type, I meant that a postgresql function returns a result of a user type.
It's just one function that returns a SETOF rutacto, where rutacto is
CREATE TYPE rutacto AS ( gidcto int4, geom geometry, altocoste bool);
But I think the type cache might get confused by different OID's when haproxy connects to a different server. It may cause issues on replicated scenarios too, I don't know.
Maybe the property I'm suggesting makes sense in load balanced scenarios

[roji]

@faibistes what may be happening, is that you have the same type (rutacto) defined in multiple PG servers, but with a different type OID: try executing the following query on both your servers:

SELECT oid FROM pg_type WHERE typname='rutacto';

If that is the case, and you're using the same connection string to talk to the two servers (via some proxy), then you indeed have a problem. Npgsql caches all type info on the host/port/database, but in your scenario, that combination hides two servers with divergent type systems. Let me know if this is indeed your case - I'm not sure what can be done but I can think about it.

[faibistes]

Yep, that's the case:

osm=# SELECT oid FROM pg_type WHERE typname='rutacto';
  oid
-------
 33412
(1 row)
osm=# SELECT oid FROM pg_type WHERE typname='rutacto';
  oid
-------
 18438
(1 row)

What I just suggested is a convenience, but far from a real solution. I can bear with the extra overhead of ReloadTypes, as this is a pretty heavy query, but other load balanced scenarios may suffer more from this.

[roji]

Note that Npgsql 6.0 introduced its own support for connecting to multiple hosts, including failover and load balancing scenarios (see docs). When using that, Npgsql is aware of each different server, and so it shouldn't be a problem for the same type to have different OIDs in different servers.

However, I do agree it's important for this to work in other scenarios. On a hunch, I tried modifying the type OID in pg_type, and it worked:

CREATE TYPE foo AS ENUM ('one', 'too');
SELECT oid FROM pg_type WHERE typname = 'foo'; -- 948788
UPDATE pg_type SET oid=948788 WHERE oid=948787;

So it's possible for you to manually align OIDs across your different databases. Once you do that, everything should just work.

[roji]

Closing this as there's nothing really to do on the Npgsql side.