Re: strange java query behaviour

Marti Raudsepp <marti(at)juffo(dot)org> writes:
> The documentation says: The view schemata contains all schemas in the
> current database that are owned by a currently enabled role.

> In other words: this view only displays schemas that are *owned* by
> your user, or roles that your current user inherits from (superuser
> sees everything of course). Sadly it doesn't list visible/accessible
> schemas.

> I think this is pretty surprising; not sure if it's just bad legacy or
> if there is some good reason for this behavior. I couldn't find any
> justification in the source code.

The justification is that the SQL standard requires the view to act that
way.

20.46 SCHEMATA view

Function

Identify the schemata in a catalog that are owned by a given user.

Definition

CREATE VIEW SCHEMATA AS
SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER,
DEFAULT_CHARACTER_SET_CATALOG, DEFAULT_CHARACTER_SET_
SCHEMA,
DEFAULT_CHARACTER_SET_NAME, SQL_PATH
FROM DEFINITION_SCHEMA.SCHEMATA

WHERE ( SCHEMA_OWNER = CURRENT_USER
OR
SCHEMA_OWNER IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) )
AND
CATALOG_NAME
= ( SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA_CATALOG_NAME );

GRANT SELECT ON TABLE SCHEMATA
TO PUBLIC WITH GRANT OPTION;

> I think we should add a TODO item for fixing this?

Waste of breath. You could try lobbying the SQL committee to change the
standard, perhaps.

regards, tom lane