Max Rydahl Andersen do you have any thoughts on this? You have fought this stuff more than me.

For documentation, what happens is that we restrict the catalog and schema we search for tables, views, sequences etc to the one we expect it to be in. So this is the first change from how reading metadata used to work. Previously we would not restrict based on catalog/schema at all, often meaning we would get back incorrect objects (from other catalog/schema). We have Jira issues about that as well. So before was too loose. It seems now we are too restrictive. So we need to find the balance.

Anyway, how we determine the catalog and schema we expect an object to be in is multi-point. First, another change (addition) is that we now know by Dialect and DBMD whether catalog and/or schema is supported. If one is not supported, we do not use that in restriction. For supported namespacing, we always use a value as determined by:

1. Did the user specify a catalog/schema name explicitly?
2. Did the user define a default catalog/schema name in settings?
3. Fallback is to use current catalog/schema as determined by Connection/DBMD

Matthias Kurz Coincidentally this is why setting hibernate.default_schema = public works for you. You circumvent the fallback of "current catalog/schema". For what it is worth, another option should be to connect to the catalog/schema you want to use (here public).

That fallback current catalog/schema bit is completely new. I am not sure the problem here is a matter of being too restrictive. Clearly that part is new and for PostgreSQL at least causes problems because of this "$user" bit (rather than the driver resolving that to the actual schema name).

I think part of the problem here is that we have never really solidified a few concepts and phrases related to schema tooling, and we really need to...

First is the idea of default catalog/schema. In retrospect, I do not think I am applying that properly here for schema migration nor validation. I think the idea of default catalog/schema should be strictly limited to creating objects when the user did not supply catalog/schema explicitly for the object. As far as restricting where we look, I think that is something we should remove.

For the most part however, that takes us back to the original "too loose" situation. So given a situation where a database has multiple namespaces and a given object name might exist in multiple of those namespaces, how do we properly determine which to use?

As an initial proposal, how about:

if ( object-we-are-looking-for-explicitly-names-a-namespace ) {
    // only *ever* look in this explicit namespace
}
else {
    // look in current namespace, if known

    // look in default namespace, if defined

    // look in all namespaces, returning first hit on multiple
}

And possibly add a "searchable namespaces" setting?