Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

GenerateTypesQuery - Search Path Problem #2668

Open
lvffilho opened this issue Oct 3, 2019 · 8 comments

Comments

@lvffilho
Copy link

commented Oct 3, 2019

I have one database with a common schema (public) and other clients' schemas.
Ex: public (common)
client1 (specific)
client2 (specific)
client3 (specific)
client4 (specific)
client5 (specific)
client6 (specific)
client7 (specific)

I set the search path to the specific schema than the public one. ('cliente1, public'), but when it opens connection load composites its loading all schemas, not the search path ones, making opening connections very slow.
I took a look at the code and saw that the GenerateTypesQuery method at PostgresDatabaseInfo is not considering the search path.

Npgsql version: 4.1.1
PostgreSQL version: 11
Operating system: OS Mojave

@roji

This comment has been minimized.

Copy link
Member

commented Oct 3, 2019

It's not very clear that the search_path should be used as a filter for loading composite types... The search_path is, after all, only a search path - nothing prevents you from using tables or types defined in schemas not in the search_path.

@lvffilho

This comment has been minimized.

Copy link
Author

commented Oct 8, 2019

We have more than 200 schemas in database. Because our tenant is per schema, not database.
In my case i use only the specifc client schema and the public one. Ex. (client1, public)
Npgsql schema is reading all the 200 schemas to load all types, and getting to slow to do this in all of them.

@YohDeadfall

This comment has been minimized.

Copy link
Member

commented Oct 8, 2019

All types are cached, so the first connection could be slow.

@lvffilho

This comment has been minimized.

Copy link
Author

commented Oct 8, 2019

Before version 3.7 the driver didn't load all of them and I didn't have this problem. Should have a parameter to be Eager or Lazy to load this.
We have to wait 20 seconds just to get the connection.

@YohDeadfall

This comment has been minimized.

Copy link
Member

commented Oct 8, 2019

As the docs says, loading table composites isn't recommended in version 4. So you see why it's so. To fix this on your side you can open an connection to the database on application start to load all composites. What we can do, I think is to limit loading types to ones which have mapping.

@roji

This comment has been minimized.

Copy link
Member

commented Oct 9, 2019

Before version 3.7 the driver didn't load all of them and I didn't have this problem. Should have a parameter to be Eager or Lazy to load this.

To be clear, this exact parameter already exists (see Load Table Composites), and it defaults to false. In other words, by default we don't load any table composite types whatsoever - you have to manually opt in to get this. Can you confirm you opted into this? Are you sure you actually need table composite types?

What we don't have (and never did!) is for PostgreSQL to load table composite types while filtering only those in the search_path, which is what you seemed to be asking for in the original issue message.

@lvffilho

This comment has been minimized.

Copy link
Author

commented Oct 9, 2019

Yes, I don't use it. It's set to false.
Taking a look and debugging "PostgresDatabaseInfo" at the method "GenerateTypesQuery" I saw that this behavior it's not only for composite but all types.

This is the base SQL

SELECT ns.nspname, a.typname, a.oid, a.typbasetype,
CASE WHEN pg_proc.proname='array_recv' THEN 'a' ELSE a.typtype END AS typtype,
CASE
  WHEN pg_proc.proname='array_recv' THEN a.typelem
  ELSE 0
END AS typelem,
CASE
  WHEN pg_proc.proname IN ('array_recv','oidvectorrecv') THEN 3    /* Arrays before */
  WHEN a.typtype='r' THEN 2                                        /* Ranges before */
  WHEN a.typtype='d' THEN 1                                        /* Domains before */
  ELSE 0                                                           /* Base types first */
END AS ord
FROM pg_type AS a
JOIN pg_namespace AS ns ON (ns.oid = a.typnamespace)
JOIN pg_proc ON pg_proc.oid = a.typreceive
LEFT OUTER JOIN pg_class AS cls ON (cls.oid = a.typrelid)
LEFT OUTER JOIN pg_type AS b ON (b.oid = a.typelem)
LEFT OUTER JOIN pg_class AS elemcls ON (elemcls.oid = b.typrelid)
where
  a.typtype IN ('b', 'r', 'e', 'd') OR         /* Base, range, enum, domain */
  (a.typtype = 'c' AND cls.relkind='c') OR /* User-defined free-standing composites (not table composites) by default */
  (pg_proc.proname='array_recv' AND (
    b.typtype IN ('b', 'r', 'e', 'd') OR       /* Array of base, range, enum, domain */
    (b.typtype = 'p' AND b.typname IN ('record', 'void')) OR /* Arrays of special supported pseudo-types */
    (b.typtype = 'c' AND elemcls.relkind='c')  /* Array of user-defined free-standing composites (not table composites) */
  )) OR
  (a.typtype = 'p' AND a.typname IN ('record', 'void'))  /* Some special supported pseudo-types */
ORDER BY ord;

It looks at all schemas at the database and takes about 5 or 8 seconds to load everything every connection that it's opened (loading 35k rows), but as I said before, we have more than 250 schemas and my connection won't use anything for the others schemas, even don't have access to them. Every user at Postgres, have one specificity Schema to work.

If I filter the Postgres´s base schemas and the ones that I need from my connection path, it goes extremely fast. Like 112ms and loads just 300 rows.

Example:

SELECT ns.nspname, a.typname, a.oid, a.typbasetype,
CASE WHEN pg_proc.proname='array_recv' THEN 'a' ELSE a.typtype END AS typtype,
CASE
  WHEN pg_proc.proname='array_recv' THEN a.typelem
  ELSE 0
END AS typelem,
CASE
  WHEN pg_proc.proname IN ('array_recv','oidvectorrecv') THEN 3    /* Arrays before */
  WHEN a.typtype='r' THEN 2                                        /* Ranges before */
  WHEN a.typtype='d' THEN 1                                        /* Domains before */
  ELSE 0                                                           /* Base types first */
END AS ord
FROM pg_type AS a
JOIN pg_namespace AS ns ON (ns.oid = a.typnamespace **and ns.nspname in ('pg_catalog', 'information_schema', 'pg_toast', 'public', 'rede000001')**)
JOIN pg_proc ON pg_proc.oid = a.typreceive
LEFT OUTER JOIN pg_class AS cls ON (cls.oid = a.typrelid)
LEFT OUTER JOIN pg_type AS b ON (b.oid = a.typelem)
LEFT OUTER JOIN pg_class AS elemcls ON (elemcls.oid = b.typrelid)
where
  a.typtype IN ('b', 'r', 'e', 'd') OR         /* Base, range, enum, domain */
  (a.typtype = 'c' AND cls.relkind='c') OR /* User-defined free-standing composites (not table composites) by default */
  (pg_proc.proname='array_recv' AND (
    b.typtype IN ('b', 'r', 'e', 'd') OR       /* Array of base, range, enum, domain */
    (b.typtype = 'p' AND b.typname IN ('record', 'void')) OR /* Arrays of special supported pseudo-types */
    (b.typtype = 'c' AND elemcls.relkind='c')  /* Array of user-defined free-standing composites (not table composites) */
  )) OR
  (a.typtype = 'p' AND a.typname IN ('record', 'void'))  /* Some special supported pseudo-types */
ORDER BY ord;
@lvffilho lvffilho changed the title Load Table Composites Search Path Problem GenerateTypesQuery - Search Path Problem Oct 9, 2019
@roji

This comment has been minimized.

Copy link
Member

commented Oct 10, 2019

This is quite odd - this query only loads database types, not data or anything similar. What kind of types are you creating in your database (and in such large quantities)? To understand exactly what's going on, can you post what is being returned by Npgsql's regular type loading query? It doesn't necessarily have to be a full dump (although a gist of that would also help), I'm trying to better understand how you're using PostgreSQL and how that's triggering this type explosion.

Note that as a workaround, you can pass Server Compatibility Mode=NoTypeLoading. This will disable type loading entirely, and will only use a restricted set of known types which are hardcoded into Npgsql's source code. Unless you're using the more advanced types this may be sufficient for you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.