Postgres Schemas

[Jon]

Hi,

I just installed OpenAcess ORM to create my first model, but in the Update From Database wizard many of my schemas are missing. 
In the attached screenshot, the window on the left shows a partial schema list compared to the schemas displayed in the ORM wizard.
http://grab.by/gHCc

I can also send you the results of a query against pg_namespace or other info if that would help.

Thanks
Jon

[Ralph Waldenmaier]

Hello Jon,

We are obtaining the schemas that the connected user has access to. See the following sql that is used to get the schemas.

/* owned schemas */
select
schemas.nspname
from
(
    select
    nspname,
    nspowner
    from pg_namespace
) schemas,
(
    SELECT usesysid FROM pg_user WHERE usename = current_user
) owner
where
schemas.nspowner = owner.usesysid
union
/*granted schemas*/
select
s.nspname
from
(
select
nspname,
cast(unnest(nspacl) as name) membership
from pg_namespace
) s,
(
    select
    *
    from
    (
        select
        groname,
        unnest(grolist) usesysid
        from
        pg_group
    ) groups
    join
    (
        SELECT usesysid FROM pg_user WHERE usename = current_user
    ) users on
    groups.usesysid = users.usesysid
) roles
where
strpos(s.membership, roles.groname) > 0
union
/* no nspacl defined => access to public */
select
nspname
from pg_namespace
where
nspacl is null
union
/* public defined explicitly */
select
nspname
from
(
    select
    nspname,
    cast(unnest(nspacl) as name) membership
    from pg_namespace
) schemas
where
substring(schemas.membership, 1, 1) = '='

Can you please check if you see the respective schemas when you execute this query against your database with the user that you use in Telerik OpenAccess ORM? Can you also please check if the user has the appropriate rights, to access these schemas.

I hope this information is helpful for you.
Feel free to ask if you have any other question. 

All the best,
Ralph
the Telerik team

[Jon]

Hi,

The SQL you provided looks good and is returning all 27 schemas in my database, including the schemas not displayed by the wizard. Are you sure there's no additional .NET code that might affect the number of schemas shown?
Also, are there any limitations on the number of schemas displayed by the wizard?  Otherwise, I'm not sure what's going on.

One note: you can and should ignore the 'pg_temp*' and pg_toast_temp*' schemas as these are temporary, system-created schemas that should never be included in a model. See this thread answer from Tom Lane: 
http://archives.postgresql.org/pgsql-bugs/2012-04/msg00156.php

Here's your SQL with the temp schemas excluded:
http://pastebin.com/Z0aFk7UQ

Thanks
jon

[Ralph Waldenmaier]

Hello Jon,

Thank you for providing the hint regarding the pg_temp* schemas. We will improve our filter logic accordingly.
As you guessed, we are indeed filtering the following schemas in our code  "pg_catalog", "pg_toast", "pg_temp_1", "pg_toast_temp_1" and "information_schema".
The wizards are not filtering any schema information.

Can you please confirm that you do get different results when you connect to your postgres database via an arbitrary sql editor and our wizards, using exactly the same user to connect to the database?

All the best,
Ralph
the Telerik team