Diesel: Custom types with same name in different schemas don't work due to oid caching

[Boscop]

When you have a custom type (e.g. using diesel-derive-enum) with the same name in different schemas, it doesn't work with diesel, it doesn't consider the search_path when looking up types.
Instead, diesel uses the first type that matches the name, regardless of the schema it's in.

It seems like this PR introduced this behavior: https://github.com/diesel-rs/diesel/pull/2088

Btw, it looks like jdbc had the same issue:
https://www.postgresql.org/message-id/flat/20110308183723.08A5B1337B93%40mail.postgresql.org

[weiznich]

Please not that this behaviour was not introduced in that PR, the query does just not check the corresponding search path.
I think it should be not that hard to fix issue. Basically we only need to add way to have a optional schema/search path for custom types. Otherwise public will be assumed as search patch. Fortunately the next release will be 2.0 so we are able to tweak the API a bit here.

Steps to implement this change:

• Change PgMetadataLookup::lookup_type to have a additional optional parameter for the schema name.
• Adjust this query to use that optional schema name.
• Change PgMetadataCache to store also the schema name.
• Change the SqlType derive to have an optional schema parameter (basically like the type_name parameter, but optional) and pass it here to the changed lookup function.
• Add a test case to this file

[Boscop]

• Change the SqlType derive to have an optional schema parameter (basically like the type_name parameter, but optional) and pass it here to the changed lookup function.

@weiznich But wouldn't that hardcode the schema at compile-time, so that that custom type only works in that one schema?
That wouldn't solve our problem, we are using the same enum type in multiple schemas that are created at runtime based on user input (but structurally the same for all tables).
So the set of schemas that the enum type will be used in is not fixed/known at compile-time.
It needs to do the lookup based on the current search_path at runtime!

[weiznich]

If you have "the same" type in multiple schema they remain different types. You wouldn't consider the same rust type definitions in different modules to be resolved to the same type, right?

[Boscop]

@weiznich It's static definition vs dynamic instances. Like having 1 struct and multiple instances at runtime. We have 1 "schema" (in the sense of a set of schemas for a set of tables) and multiple instances at runtime (multiple pg schemas).
Being able to "mount" one of these instances to a set of static types (that represent this "schema") based on the search_path is a valid use case IMO.
At least it's our use case (multi-tenant system with one schema instance per tenant).
Would it be possible to accommodate this use case in Diesel or do you think it'd be too disruptive / inefficient?

[weiznich]

If you have the "same" types in different schemas that's only a interpretation added on top of the actual types. It's nothing we can infer (because matching by name/whatever will fail for constructed edge cases). That means it will be never something that will be supported by diesel out of the box in my opinion. That said it should be possible to support such an setup by implementing some things manually.
The probably easiest way to do that with the latest released diesel version is to just set the search schema dynamically by just calling SELECT pg_catalog.set_config('search_path', 'your_schema', false);.

[sgrif]

One thing that's important to note is that getting the current search_path requires a round trip to the database, which defeats the purpose of caching the result in the first place. I'm not sure what the right answer is here, but I think there's some missing context here. Duplicating a type per-schema seems like an anti-pattern to me. If the type is the same for all schemas, why does it need to be schema specific in the first place?

[sgrif]

@Boscop Can you clarify what you're asking for here? Is your issue that the query itself doesn't take the search path into account? Or that we cache the answer, but that may change if the search path changes?

[sgrif]

So it seems like there are three issues here:

• We don't take the search path into account on lookup
• There's no way to specify an explicit schema
• If the result were based on the search path, the answer could change between calls

I believe the first two can be solved by changing the where clause on our query to WHERE oid = $1::regtype::oid. This would also resolve schemas for us (though we should make sure this still works when given an explicit schema that isn't in the search path). We can also just narrow the schema manually if the string contains a ., but that'd be more complicated and also break any types with a . in the name (which probably won't actually break any real code)

The third problem is something I consider to be extremely niche, but it seems like we could support it by providing a second lookup function which doesn't cache the answer. But if you can't tell us the schema you want us to look in, I don't see any way we could possibly support that without doing a query every time the lookup needs to be performed.