Re: strange java query behaviour

Marti Raudsepp <marti@juffo.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