Aaz on software - Blogging about tech stuff

Давайте представим, что происходит, когда запускается Rails-консоль и делается первый запрос к базе данных:

irb(main):001:0> Account.last
  Account Load (1.9ms)  SELECT  "accounts".* FROM "accounts" ORDER BY "accounts"."id" DESC LIMIT $1  [["LIMIT", 1]]

Rails формирует SQL-запрос, логирует и отдает на выполнение базе данных. SQL-запросы постоянно мелькают перед глазами в логах и консоли. Но этим взаимодействие Rails и базы данных не ограничивается. Давайте подсмотрим, какие еще невидимые SQL-запросы выполняются в Rails.

Установка нового соединения

Рассмотрим Rails 5.2 и PostgreSQL 12. Если зайти в Rails-консоль и ввести Account.last, то Rails выполнит кучу странных SQL-запросов:

SET client_min_messages TO 'warning'
SET standard_conforming_strings = on
SET SESSION timezone TO 'UTC'
            SELECT t.oid, t.typname
            FROM pg_type as t
            WHERE t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'bool')

              SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
              FROM pg_type as t
              LEFT JOIN pg_range as r ON oid = rngtypid
              WHERE
                t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'text', 'varchar', 'char', 'name', 'bpchar', 'bool', 'bit', 'varbit', 'timestamptz', 'date', 'money', 'bytea', 'point', 'hstore', 'json', 'jsonb', 'cidr', 'inet', 'uuid', 'xml', 'tsvector', 'macaddr', 'citext', 'ltree', 'line', 'lseg', 'box', 'path', 'polygon', 'circle', 'interval', 'time', 'timestamp', 'numeric')
                OR t.typtype IN ('r', 'e', 'd')
                OR t.typinput = 'array_in(cstring,oid,integer)'::regprocedure
                OR t.typelem != 0

SHOW TIME ZONE
SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = ANY (current_schemas(false)) AND c.relkind IN ('r','v','m','p','f')
SELECT a.attname
  FROM (
         SELECT indrelid, indkey, generate_subscripts(indkey, 1) idx
           FROM pg_index
          WHERE indrelid = '"accounts"'::regclass
            AND indisprimary
       ) i
  JOIN pg_attribute a
    ON a.attrelid = i.indrelid
   AND a.attnum = i.indkey[i.idx]
 ORDER BY i.idx

SHOW search_path
SELECT  "accounts".* FROM "accounts" ORDER BY "accounts"."id" DESC LIMIT $1
              SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
                     c.collname, col_description(a.attrelid, a.attnum) AS comment
                FROM pg_attribute a
                LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
                LEFT JOIN pg_type t ON a.atttypid = t.oid
                LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
               WHERE a.attrelid = '"accounts"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum

SHOW max_identifier_length

Давайте разберем что здесь к чему.

Rails устанавливает соединения с базой данных лениво, т.е. только при необходимости, при первом обращении к базе. Поэтому до выполнения Account.last Rails не установило ни одного соединения.

При вызове метода last Rails устанавливает и настраивает первое соединение к базе данных. Чтобы сгенерировать accessor‘ы для атрибутов моделей и выполнять type casting атрибутов к типам колонок Rails нужна структура таблиц. Эти данные лениво загружаются из базы при первом обращении к таблице и, конечно же, кэшируются.

Давайте разберем каждый из приведенных выше SQL-запросов.

#1

SET client_min_messages TO 'warning'

Rails настраивает client_min_messages - уровень клиентского логирования (warning в данном случае). Сервер базы данных логирует служебные и пользовательские сообщения в журнал согласно серверному уровню логировани - например вывод дерева запроса, дерево запроса после применения правил или плана выполнения. Решение отправлять ли эти сообщению клиенту зависит от выставленного для соединения уровня клиентского логирования, который по-умолчанию равен notice.

Ради интереса можно поиграться с отладочным выводом PostgreSQL. Для этого надо выставить самый подробный уровень логирования (DEBUG1) и включить логирование, на пример, плана запросов или внутреннего представления SQL-запросов до и после оптимизации:

SET debug_print_plan      TO true;
SET debug_print_rewritten TO true;
SET debug_print_parse     TO true;

SET client_min_messages   TO 'DEBUG1';

SELECT * FROM accounts;               -- или любой другой SQL-запрос

Отладочный вывод в PostgreSQL это большая и интересная тема и заслуживает отдельный пост.

#2

SET standard_conforming_strings = on

Опция standard_conforming_strings влияет на интерпретацию строковых литералов в запросах - точнее на интерпретацию backslash-последовательностей (\n, \t, …). Включенная опция означает, что интерпретация будет выключена. Начиная с PostgreSQL 9.1 опция включена по-умолчанию.

#3

SET SESSION timezone TO 'UTC'

Эта команда, что впрочем очевидно, выставляет timezone для соединения. База данных использует timezone в следующих случаях:

при передаче данных клиенту даты и время с timezone конвертируется в системный timezone
при передаче данных серверу литералы даты и времени без явного timezone неявно получает системный timezone

Если timezone не указать явно, то работают следующие правила:

если выставить на клиенте переменную окружения PGTZ, то клиентская библиотека libpq самостоятельно выставит этот timezone для соединений
если клиент не выставил timezone для соединения, то PostgreSQL проверит выставлен ли timezone в конфиге postgresql.conf
если и там timezone не настроен, тогда используется локальный timezone сервера:
- PostgreSQL смотрит в переменную окружения TZ и только затем
- получает текущий timezone у операционной системы

Ссылки:

#4

SELECT t.oid, t.typname
FROM pg_type as t
WHERE t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'bool')

Здесь Rails получает информацию о некоторых базовых типах PostgreSQL. В служебной таблице pg_type, хранятся характеристики базовых и пользовательских типов. Rails зачитывает имя типа и первичный ключ oid:

 oid | typname
-----+---------
| bool
| int8
| int2
| int4
| oid
| float4
| float8
(7 rows)

https://www.postgresql.org/docs/12/catalog-pg-type.html

#5

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
  LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'text', 'varchar', 'char', 'name', 'bpchar', 'bool', 'bit', 'varbit', 'timestamptz', 'date', 'money', 'bytea', 'point', 'hstore', 'json', 'jsonb', 'cidr', 'inet', 'uuid', 'xml', 'tsvector', 'macaddr', 'citext', 'ltree', 'line', 'lseg', 'box', 'path', 'polygon', 'circle', 'interval', 'time', 'timestamp', 'numeric')
  OR t.typtype IN ('r', 'e', 'd')
  OR t.typinput = 'array_in(cstring,oid,integer)'::regprocedure
  OR t.typelem != 0

Здесь Rails зачитывает характеристики как базовых типов (t.typname IN ('int2', 'float4', 'text', 'varchar'...)), так и пользовательских - ranges, enums и domains (t.typtype IN ('r', 'e', 'd')) и массивов (t.typelem != 0).

  oid  |        typname        | typelem | typdelim |    typinput    | rngsubtype | typtype | typbasetype
-------+-----------------------+---------+----------+----------------+------------+---------+-------------
| bool                  |       0 | ,        | boolin         |            | b       |           0
| bytea                 |       0 | ,        | byteain        |            | b       |           0
| char                  |       0 | ,        | charin         |            | b       |           0
| name                  |      18 | ,        | namein         |            | b       |           0
| int8                  |       0 | ,        | int8in         |            | b       |           0
| int2                  |       0 | ,        | int2in         |            | b       |           0
| int2vector            |      21 | ,        | int2vectorin   |            | b       |           0
| int4                  |       0 | ,        | int4in         |            | b       |           0
| text                  |       0 | ,        | textin         |            | b       |           0
| oid                   |       0 | ,        | oidin          |            | b       |           0
| oidvector             |      26 | ,        | oidvectorin    |            | b       |           0
| json                  |       0 | ,        | json_in        |            | b       |           0
| xml                   |       0 | ,        | xml_in         |            | b       |           0
| point                 |     701 | ,        | point_in       |            | b       |           0
| lseg                  |     600 | ,        | lseg_in        |            | b       |           0
| path                  |       0 | ,        | path_in        |            | b       |           0
| box                   |     600 | ;        | box_in         |            | b       |           0
| polygon               |       0 | ,        | poly_in        |            | b       |           0
| line                  |     701 | ,        | line_in        |            | b       |           0
| float4                |       0 | ,        | float4in       |            | b       |           0
| float8                |       0 | ,        | float8in       |            | b       |           0
| circle                |       0 | ,        | circle_in      |            | b       |           0
| money                 |       0 | ,        | cash_in        |            | b       |           0
| macaddr               |       0 | ,        | macaddr_in     |            | b       |           0
| inet                  |       0 | ,        | inet_in        |            | b       |           0
| cidr                  |       0 | ,        | cidr_in        |            | b       |           0
| bpchar                |       0 | ,        | bpcharin       |            | b       |           0
| varchar               |       0 | ,        | varcharin      |            | b       |           0
| date                  |       0 | ,        | date_in        |            | b       |           0
| time                  |       0 | ,        | time_in        |            | b       |           0
| timestamp             |       0 | ,        | timestamp_in   |            | b       |           0
| timestamptz           |       0 | ,        | timestamptz_in |            | b       |           0
| interval              |       0 | ,        | interval_in    |            | b       |           0
| bit                   |       0 | ,        | bit_in         |            | b       |           0
| varbit                |       0 | ,        | varbit_in      |            | b       |           0
| numeric               |       0 | ,        | numeric_in     |            | b       |           0
| uuid                  |       0 | ,        | uuid_in        |            | b       |           0
| tsvector              |       0 | ,        | tsvectorin     |            | b       |           0
| jsonb                 |       0 | ,        | jsonb_in       |            | b       |           0
| int4range             |       0 | ,        | range_in       |         23 | r       |           0
| numrange              |       0 | ,        | range_in       |       1700 | r       |           0
| tsrange               |       0 | ,        | range_in       |       1114 | r       |           0
| tstzrange             |       0 | ,        | range_in       |       1184 | r       |           0
| daterange             |       0 | ,        | range_in       |       1082 | r       |           0
| int8range             |       0 | ,        | range_in       |         20 | r       |           0
| _record               |    2249 | ,        | array_in       |            | p       |           0
| _bool                 |      16 | ,        | array_in       |            | b       |           0
| _bytea                |      17 | ,        | array_in       |            | b       |           0
| _char                 |      18 | ,        | array_in       |            | b       |           0
| _name                 |      19 | ,        | array_in       |            | b       |           0
| _int8                 |      20 | ,        | array_in       |            | b       |           0
| _int2                 |      21 | ,        | array_in       |            | b       |           0
| _int2vector           |      22 | ,        | array_in       |            | b       |           0
| _int4                 |      23 | ,        | array_in       |            | b       |           0
| _regproc              |      24 | ,        | array_in       |            | b       |           0
| _text                 |      25 | ,        | array_in       |            | b       |           0
| _oid                  |      26 | ,        | array_in       |            | b       |           0
| _tid                  |      27 | ,        | array_in       |            | b       |           0
| _xid                  |      28 | ,        | array_in       |            | b       |           0
| _cid                  |      29 | ,        | array_in       |            | b       |           0
| _oidvector            |      30 | ,        | array_in       |            | b       |           0
| _json                 |     114 | ,        | array_in       |            | b       |           0
| _xml                  |     142 | ,        | array_in       |            | b       |           0
| _point                |     600 | ,        | array_in       |            | b       |           0
| _lseg                 |     601 | ,        | array_in       |            | b       |           0
| _path                 |     602 | ,        | array_in       |            | b       |           0
| _box                  |     603 | ;        | array_in       |            | b       |           0
| _polygon              |     604 | ,        | array_in       |            | b       |           0
| _line                 |     628 | ,        | array_in       |            | b       |           0
| _float4               |     700 | ,        | array_in       |            | b       |           0
| _float8               |     701 | ,        | array_in       |            | b       |           0
| _circle               |     718 | ,        | array_in       |            | b       |           0
| _money                |     790 | ,        | array_in       |            | b       |           0
| _macaddr              |     829 | ,        | array_in       |            | b       |           0
| _inet                 |     869 | ,        | array_in       |            | b       |           0
| _cidr                 |     650 | ,        | array_in       |            | b       |           0
| _macaddr8             |     774 | ,        | array_in       |            | b       |           0
| _aclitem              |    1033 | ,        | array_in       |            | b       |           0
| _bpchar               |    1042 | ,        | array_in       |            | b       |           0
| _varchar              |    1043 | ,        | array_in       |            | b       |           0
| _date                 |    1082 | ,        | array_in       |            | b       |           0
| _time                 |    1083 | ,        | array_in       |            | b       |           0
| _timestamp            |    1114 | ,        | array_in       |            | b       |           0
| _timestamptz          |    1184 | ,        | array_in       |            | b       |           0
| _interval             |    1186 | ,        | array_in       |            | b       |           0
| _timetz               |    1266 | ,        | array_in       |            | b       |           0
| _bit                  |    1560 | ,        | array_in       |            | b       |           0
| _varbit               |    1562 | ,        | array_in       |            | b       |           0
| _numeric              |    1700 | ,        | array_in       |            | b       |           0
| _refcursor            |    1790 | ,        | array_in       |            | b       |           0
| _regprocedure         |    2202 | ,        | array_in       |            | b       |           0
| _regoper              |    2203 | ,        | array_in       |            | b       |           0
| _regoperator          |    2204 | ,        | array_in       |            | b       |           0
| _regclass             |    2205 | ,        | array_in       |            | b       |           0
| _regtype              |    2206 | ,        | array_in       |            | b       |           0
| _regrole              |    4096 | ,        | array_in       |            | b       |           0
| _regnamespace         |    4089 | ,        | array_in       |            | b       |           0
| _uuid                 |    2950 | ,        | array_in       |            | b       |           0
| _pg_lsn               |    3220 | ,        | array_in       |            | b       |           0
| _tsvector             |    3614 | ,        | array_in       |            | b       |           0
| _gtsvector            |    3642 | ,        | array_in       |            | b       |           0
| _tsquery              |    3615 | ,        | array_in       |            | b       |           0
| _regconfig            |    3734 | ,        | array_in       |            | b       |           0
| _regdictionary        |    3769 | ,        | array_in       |            | b       |           0
| _jsonb                |    3802 | ,        | array_in       |            | b       |           0
| _jsonpath             |    4072 | ,        | array_in       |            | b       |           0
| _txid_snapshot        |    2970 | ,        | array_in       |            | b       |           0
| _int4range            |    3904 | ,        | array_in       |            | b       |           0
| _numrange             |    3906 | ,        | array_in       |            | b       |           0
| _tsrange              |    3908 | ,        | array_in       |            | b       |           0
| _tstzrange            |    3910 | ,        | array_in       |            | b       |           0
| _daterange            |    3912 | ,        | array_in       |            | b       |           0
| _int8range            |    3926 | ,        | array_in       |            | b       |           0
| _cstring              |    2275 | ,        | array_in       |            | b       |           0
| _schema_migrations    |   16388 | ,        | array_in       |            | b       |           0
| cardinal_number       |       0 | ,        | domain_in      |            | d       |          23
| _cardinal_number      |   13402 | ,        | array_in       |            | b       |           0
| character_data        |       0 | ,        | domain_in      |            | d       |        1043
| _character_data       |   13405 | ,        | array_in       |            | b       |           0
| sql_identifier        |       0 | ,        | domain_in      |            | d       |          19
| _sql_identifier       |   13407 | ,        | array_in       |            | b       |           0
| time_stamp            |       0 | ,        | domain_in      |            | d       |        1184
| _time_stamp           |   13412 | ,        | array_in       |            | b       |           0
| yes_or_no             |       0 | ,        | domain_in      |            | d       |        1043
| _yes_or_no            |   13414 | ,        | array_in       |            | b       |           0
| _ar_internal_metadata |   16396 | ,        | array_in       |            | b       |           0
| _accounts             |   16406 | ,        | array_in       |            | b       |           0
| _payments             |   16417 | ,        | array_in       |            | b       |           0
(128 rows)

#6

SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = ANY (current_schemas(false)) AND c.relkind IN ('r','v','m','p','f')

Rails получает список таблиц, views, materialized views, partitioned tables и foreign tables (relkind IN ('r','v','m','p','f')). В примере возвращаются служебные таблицы Rails (schema_migrations и ar_internal_metadata) и две пользовательские таблицы - accounts и payments:

       relname
----------------------
 schema_migrations
 ar_internal_metadata
 accounts
 payments
(4 rows)

#7

SELECT a.attname
  FROM (
         SELECT indrelid, indkey, generate_subscripts(indkey, 1) idx
           FROM pg_index
          WHERE indrelid = '"accounts"'::regclass
            AND indisprimary
       ) i
  JOIN pg_attribute a
    ON a.attrelid = i.indrelid
   AND a.attnum = i.indkey[i.idx]
 ORDER BY i.idx

Rails получает имя первичного ключа таблицы accounts к которой обращались в изначальном запросе:

 attname
---------
 id
(1 row)

#8

SHOW search_path

PostgreSQL поддерживает понятие схема базы. База данных содержит схемы, а те в свою очередь содержат таблицы и другие именованные объекты. Полный идентификатор таблицы состоит из имени базы, схемы и имени таблицы - my_database.my_schema.my_table. Если в запросе используют имя таблицы без схема, она ищется в перечисленных в search_path схемах.

Опция search_path по-умолчанию равна "$user", public, где "$user" означает имя текущего пользователя и игнорируется если такой схемы нет. Поэтому обычно применяется схема public.

   search_path
-----------------
 "$user", public
(1 row)

https://www.postgresql.org/docs/12/ddl-schemas.html

#9

SELECT a.attname, format_type(a.atttypid, a.atttypmod),
       pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
       c.collname, col_description(a.attrelid, a.attnum) AS comment
  FROM pg_attribute a
  LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
  LEFT JOIN pg_type t ON a.atttypid = t.oid
  LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
 WHERE a.attrelid = '"accounts"'::regclass
   AND a.attnum > 0 AND NOT a.attisdropped
 ORDER BY a.attnum

Здесь Rails получает структуру таблицы accounts - имена полей (id, name), типы, NOT NULL ограничения, etc:

 attname |    format_type    |             pg_get_expr              | attnotnull | atttypid | atttypmod | collname | comment
---------+-------------------+--------------------------------------+------------+----------+-----------+----------+---------
 id      | bigint            | nextval('accounts_id_seq'::regclass) | t          |       20 |        -1 |          |
 name    | character varying |                                      | f          |     1043 |        -1 |          |
(2 rows)

В колонке atttypid возвращаются id типов, которые получили на шаге #5:

  oid  |        typname        | typelem | typdelim |    typinput    | rngsubtype | typtype | typbasetype
-------+-----------------------+---------+----------+----------------+------------+---------+-------------
    20 | int8                  |       0 | ,        | int8in         |            | b       |           0
  1043 | varchar               |       0 | ,        | varcharin      |            | b       |           0

В поле atttypmod хранится специфичные для типа параметры, которые указывались для колонки при создании таблицы, например, максимальная длина для varchar. Значение -1 означает, что таких данных нет.

Условие a.attnum > 0 означает, что загружаются только пользовательские колонки, которые указали при создании или изменении структуры таблицы. Для служебных колонок, например oid, значение attrnum отрицательное.

Колонки фильтруются по еще одному условию - NOT a.attisdropped. В поле attisdropped хранится признак, что колонку удалили из таблицы. Несмотря на удаление колонки данные остаются в таблице но игнорируются при выполнении SQL-запросов.

#10

SHOW max_identifier_length

max_identifier_length - это read-only значение, которое ограничивает длину имен таблиц и колонок. Опция задается только на этапе компиляции PostgreSQL и по-умолчанию равно 63 байтам.

 max_identifier_length
-----------------------
 63
(1 row)

https://www.postgresql.org/docs/10/runtime-config-preset.html#GUC-MAX-IDENTIFIER-LENGTH

SQL-запросы для таблиц

Как видно, часть запросов общие и относятся к клиентским настройкам соединения и настройкам самой базы данных. Остальные запросы касаются таблицы accounts, к которой обращались в изначальном запросе.

Если тут же сделать аналогичный запрос к другой таблице (payments),

irb(main):002:0> Payment.last
SELECT  "payments".* FROM "payments" ORDER BY "payments"."id" DESC LIMIT $1

то увидим только специфичные для нее запросы - определение первичного ключа и схемы таблицы:

SELECT a.attname
  FROM (
         SELECT indrelid, indkey, generate_subscripts(indkey, 1) idx
           FROM pg_index
          WHERE indrelid = '"payments"'::regclass
            AND indisprimary
       ) i
  JOIN pg_attribute a
    ON a.attrelid = i.indrelid
   AND a.attnum = i.indkey[i.idx]
 ORDER BY i.idx

SELECT  "payments".* FROM "payments" ORDER BY "payments"."id" DESC LIMIT $1

SELECT a.attname, format_type(a.atttypid, a.atttypmod),
       pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
       c.collname, col_description(a.attrelid, a.attnum) AS comment
  FROM pg_attribute a
  LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
  LEFT JOIN pg_type t ON a.atttypid = t.oid
  LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
 WHERE a.attrelid = '"payments"'::regclass
   AND a.attnum > 0 AND NOT a.attisdropped
 ORDER BY a.attnum

Если повторить этот же запрос еще раз, выполнится только запрос на чтение данных из таблицы payments и служебные запросы уже не делаются.

Как сделать невидимые запросы видимыми

Один из самых доступных способов увидеть служебные SQL-запросы, хотя и не самый прозрачный, это встроенный в Rails механизм событий.

В Rails события публикуются для всех основных операций: вызовы action‘ов controller‘а, рендеринг partial‘ов и шаблонов, выполнение SQL-запросов итд. В последнем случае события будут публиковаться для каждого SQL-запроса, как пользовательского так и служебного.

Чтобы увидеть служебные запросы достаточно зайти в Rails-консоль и подписаться на событие sql.active_record следующим образом:

ActiveSupport::Notifications.subscribe "sql.active_record" do |name, started, finished, unique_id, data|
  puts "#{data[:sql]}\n"
end

Далее любой невидимый SQL-запрос будет выводиться в консоль.