datasource with a lot of schemas cause browser to hang

[edwardsharp]

I've got a PG datasource with ~200 schemas that have ~100 tables each with quite a few columns. When I use the query editor the section that displays the schemas/tables/columns causes my browser to hang. Probably because the ng-repeat loop get out of control.

I'm wondering about what would be a good way to fix this? Perhaps there's optimizations to the angular repeat that would alleviate loop problems? My first fix was to add a LIMIT 1000 to the schema query in pg.py

redash/redash/query_runner/pg.py

Line 93 in fbb81b6

WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

But since all my schemas have the same structure I just changed the WHERE to be like WHERE table_schema = 'public';

[arikfr]

There are a few optimizations that can be done when using ng-repeat, although some of them require newer version of Angular than what we use (~1.2).

But the long term solution is to have better API for schemas, that supports server side search and pagination. I described it here: https://trello.com/c/62WWXJcX/59-better-schema-browser.

[arikfr]

Better schema browser

[jonmabe]

I have this same issue, but instead of the UI temporarily hanging the browser will just crash completely (chrome and firefox). To give context, for the DB we're using with redash, the schema query below produces 15 million results.

Looking at the code that describes the schema I realized I could just add DENY permissions to my redash user. I'll share my solution since this might also be a workaround for anyone else with this issue until the redash team has a chance to improve this UI, or add the option to bypass retrieving schema at the data source config level.

I'm using an MSSQL db, but everything here should be about the same for PG.

Here's the SQL used by redash to pull MSSQL schema:

        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE table_schema NOT IN ('guest','INFORMATION_SCHEMA','sys','db_owner','db_accessadmin'
                                  ,'db_securityadmin','db_ddladmin','db_backupoperator','db_datareader'
                                  ,'db_datawriter','db_denydatareader','db_denydatawriter'
                                  );

Here's the DENY statement I used:

DENY VIEW DEFINITION on database::[foo-db] TO [redash-reader-user];

Finally, to realize this change immediately I went ahead and deleted the cached schema from redash's redis store (there may be a better way to do this):

DEL data_source:schema:3

where 3 is your data source id

[arikfr]

@jonmabe clever!

[arikfr]

@edwardsharp to be honest, it's possible that in your case you will still have problems. I'll be happy if you can follow up after upgrading to a version with the fix.