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

Cannot list indexes of a table if schema is not in search path #2562

Closed
kYann opened this issue Aug 12, 2019 · 3 comments

Comments

@kYann
Copy link

commented Aug 12, 2019

Steps to reproduce

CREATE SCHEMA new_schema;
CREATE TABLE new_schema.new_table (dummy_column text);
CREATE INDEX new_idx
  ON new_schema.new_table (dummy_column);

Then call the GetIndexes request :

select current_database() as table_catalog,
    n.nspname as table_schema,
    t.relname as table_name,
    i.relname as index_name
from
    pg_catalog.pg_class i join
    pg_catalog.pg_index ix ON ix.indexrelid = i.oid join
    pg_catalog.pg_class t ON ix.indrelid = t.oid join
    pg_attribute a on t.oid = a.attrelid left join
    pg_catalog.pg_user u ON u.usesysid = i.relowner left join
    pg_catalog.pg_namespace n ON n.oid = i.relnamespace
where
    i.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(i.oid)
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r' AND current_database() = 'new_db' AND n.nspname = 'new_schema' AND t.relname = 'new_table'

The issue

The GetIndexes method returns 0 results. I'm expecting my indexes to be listed.

This method returns 0 results because the request use the pg_table_is_visible function which check if the current table is in the search path (https://www.postgresql.org/docs/9.5/functions-info.html#FUNCTIONS-INFO-SCHEMA-TABLE).
This function is only used in the case of listing indexes but not when listing tables or constraints. The behavior does not seems coherent with the rest of the Get* functions.

Also when trying to generate DDL with NHibernate, indexes are always regenerated.

kYann pushed a commit to kYann/npgsql that referenced this issue Aug 12, 2019

Yann ROBIN
Update NpgsqlSchema.cs
Fix npgsql#2562 by removing the constraint of listing indexes in the current search path
@YohDeadfall

This comment has been minimized.

Copy link
Member

commented Aug 21, 2019

@roji What is the correct behavior? Should we respect visibility everywhere?

@YohDeadfall YohDeadfall added the bug label Aug 21, 2019

@YohDeadfall YohDeadfall added this to the 4.0.10 milestone Aug 21, 2019

@roji

This comment has been minimized.

Copy link
Member

commented Aug 21, 2019

I've just checked and SqlClient does show all indexes in all schemas (SQL Server doesn't have search_path), I don't see any reason for us to do otherwise. This code is extremely old, we should just fix it.

@roji

This comment has been minimized.

Copy link
Member

commented Aug 21, 2019

Have approved for merge, @YohDeadfall unless you have any other thoughts we can merge and backport.

@roji roji closed this in #2563 Aug 21, 2019

roji added a commit that referenced this issue Aug 21, 2019

Update NpgsqlSchema.cs
Fix #2562 by removing the constraint of listing indexes in the current search path

roji added a commit that referenced this issue Aug 21, 2019

Update NpgsqlSchema.cs
Fix #2562 by removing the constraint of listing indexes in the current search path
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.