Zapytanie nie widzi tabeli - jak działa search_path

PostgreSQL

Zapytanie nie widzi tabeli - jak działa search_path

W skrócie

To klasyczne zaskoczenie - \dt pokazuje tabelę, a SELECT twierdzi, że jej nie ma. Winowajcą jest niemal zawsze search_path, czyli lista schematów, w których PostgreSQL szuka obiektów podanych bez nazwy schematu. Schemat to logiczny pojemnik na tabele i inne obiekty wewnątrz jednej bazy. Problem dotyczy każdego, kto rozdziela dane na schematy albo pracuje na kilku rolach. Pokazujemy, jak działa rozwiązywanie nazw i jak ustawić search_path tak, by zapytania trafiały tam, gdzie trzeba.

Jak to wygląda w praktyce

Zapytanie po samej nazwie tabeli kończy się błędem, choć tabela istnieje:

sklep=> SELECT * FROM klienci;
ERROR:  relation "klienci" does not exist
LINE 1: SELECT * FROM klienci;
                      ^

Gdy jednak podasz nazwę schematu wprost, to samo zapytanie działa bez problemu:

sklep=> SELECT * FROM crm.klienci;
 id | nazwa
----+-------
  1 | Kowalski

To najczystszy dowód, że tabela istnieje, tylko leży w schemacie crm, którego nie ma na liście search_path. Serwer szukał klienci w innych schematach i jej tam nie znalazł.

Dlaczego tak się dzieje

Gdy odwołujesz się do obiektu bez nazwy schematu (na przykład klienci zamiast crm.klienci), PostgreSQL przechodzi kolejno przez schematy wypisane w parametrze search_path i bierze pierwszy, w którym znajdzie obiekt o tej nazwie. Domyślny search_path to "$user", public - najpierw schemat o nazwie równej nazwie bieżącej roli, a potem public. Jeśli Twoja tabela jest w schemacie crm, którego nie ma na tej liście, serwer po prostu jej nie zobaczy i zgłosi relation does not exist - mimo że obiekt fizycznie istnieje. To nie problem uprawnień (tamten daje permission denied), tylko rozwiązywania nazw. Ta sama nazwa tabeli może zresztą występować w kilku schematach naraz, a search_path rozstrzyga, którą z nich widzisz bez kwalifikacji.

Jak to rozwiązać krok po kroku

1. Ustal, w którym schemacie jest tabela: SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'klienci';.
2. Sprawdź bieżący search_path w swojej sesji: SHOW search_path;.
3. Doraźnie w zapytaniu odwołuj się do tabeli z nazwą schematu: SELECT * FROM crm.klienci; - to zawsze zadziała niezależnie od search_path.
4. Aby zmienić to na czas sesji: SET search_path = crm, public;. Ustawienie znika po rozłączeniu.
5. Aby zmiana była trwała dla roli: ALTER ROLE app SET search_path = crm, public; - zadziała przy kolejnych logowaniach tej roli.
6. Aby ustawić domyślny schemat dla całej bazy: ALTER DATABASE sklep SET search_path = crm, public;. Kolejność ma znaczenie - pierwszy schemat z listy jest przeszukiwany najpierw.

Jak sprawdzić, że zadziałało

Po zmianie potwierdź, że search_path ma właściwe schematy - a przede wszystkim, że serwer rozwiązuje nazwę do konkretnego obiektu:

SHOW search_path;
SELECT to_regclass('klienci') AS znaleziona_tabela;

Funkcja to_regclass zwróci pełną nazwę tabeli (na przykład crm.klienci), jeśli nazwa jest rozwiązywalna w bieżącym search_path, albo NULL, jeśli nadal nie jest widoczna. Gdy zwraca właściwą tabelę, zapytania po samej nazwie zaczną działać.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5