Add indexes to improve performance: ERROR: relation "admissions" does not exist

[herroannekim]

I'm adding indexes/indices to improve performance. I used psql 'dbname=mimic user=mimic options=--search_path=mimiciii' -f postgres_add_indexes.sql while I was in the working directory buildmimic/postgres, I got the error:

psql:postgres_add_indexes.sql:19: NOTICE: index "admissions_idx01" does not exist, skipping DROP INDEX psql:postgres_add_indexes.sql:21: ERROR: relation "admissions" does not exist

How should I troubleshoot?

[herroannekim]

Further, when I try to skip this step and actually query mimic with psql 'dbname=mimic user=mimic options=--search_path=mimiciii' and then alter superuser and
mimic=> select count(subject_id) \n mimic-> from mimiciii.patients;
I get the following error:
ERROR: permission denied for schema mimiciii

[alistairewj]

Your first error looks like an issue regarding the schema. That is you may have built the tables on a different schema. Double check to confirm that you've built all the tables on the mimiciii schema.

The second issue implies you're using a user with insufficient privileges. You need to grant access to the schema to whatever user you are querying with, e.g. GRANT USAGE ON SCHEMA mimiciii TO <username_here>;

[herroannekim]

How do I double check if the tables are built? Also, how can I check what my local sql environment's <username> is?

[alistairewj]

By default you will have the SQL user postgres. Past that, it depends on how you built the database (the scripts make a user called mimic_user I believe). Once you are in psql, you can run a few commands:

\l - list databases
\dn - list schemas under a database
\dt - list tables under schema that's currently looked for in the search_path

You can change the search_path using the command set search_path to <schema>;

Users are done entirely independent from this and can be checked using \du

You might find a tutorial on postgres useful, e.g. https://www.tutorialspoint.com/postgresql/postgresql_schema.htm

[herroannekim]

Thank you. The postgres information was very helpful, but I'm still getting errors

                                        List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 annekim   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 mimic     |                                                            | {}

mimic=> GRANT USAGE ON SCHEMA mimiciii TO annekim;
ERROR:  permission denied for schema mimiciii
mimic=> GRANT USAGE ON SCHEMA mimiciii TO mimic;
ERROR:  permission denied for schema mimiciii

[alistairewj]

My guess is you are logged in as user mimic who doesn't have permission to grant privileges. Try logging in as annekim as follows: psql -U annekim -d mimic. See how that works!

[herroannekim]

@tompollard helped me out by granting superuser privileges to the mimic user and I believe we also changed something about the schema. Thanks!

[tompollard]

Thanks @herroannekim. @alistairewj it seems there may be something up with the build scripts and/or the tutorials, so I'll leave this issue open.

[alistairewj]

I tidied up the build scripts. I rebuilt the database successfully on my Ubuntu machine last night so hopefully this issue is resolved. Also tidied up the readme a bit.