Ответы пользователя по тегу SQL
  • Как выбрать следующие 100 строк из большой таблицы?

    Melkij
    @Melkij
    PostgreSQL DBA
    2. Нужно ли включать order by в таких огромных таблицах?

    Не зависит от размера базы.
    Или у вас есть order by и поведение ожидаемое или у вас нет order by и СУБД имеет полное право на каждый запрос выдавать любые подходящие под фильтр данные в любом порядке.
    Запрос выдающий неправильные данные обычно никому не нужен, даже если он и быстрый.

    offset для пагинации вообще вещь неуклюжая. https://use-the-index-luke.com/no-offset
    Быстрая пагинация - это передача уникального идентификатора, последнего на просматриваемой странице. Т.е. запрос на выборку следующих 100 записей начиная после такого-то id.
    Бонусом консистентное поведение, если, например, что-то из строк предыдущей страницы удалили. Оффсет тупо потеряет строку из выборки и пользователь может не найти то что искал.

    3. Ну типа берем сначала вытаскиваем данные с одной таблицы, потом по результату смотрим id, по нему вытаскиваем данные связанные со второй таблицы, затем смотрим новый результат и так же вытаскиваем с третьей. Получается в бд не будет строится огромная временная таблица из трех больших таблиц.

    Вы изобрели то что делает джойн. База в общем случае его сделает лучше.
    Будет ли огромная временная таблица - смотрите план.
    Ответ написан
    Комментировать
  • Как перенести дамп базы данных с Ubuntu на Windows?

    Melkij
    @Melkij
    PostgreSQL DBA
    К каким бы то ни было паролям базы источника отношения не имеет.
    Вопрос исключительно к настройке вашей базы в этом экзотическом окружении windows.

    Во-первых неясно пароль какого пользователя запрашивается?

    Того, к которому вы сказали подключиться psql. Поскольку вы не указали соответствующий ключ -U и, скорей всего, не определили переменную окружения - то psql пытается использовать имя пользователя аналогичное имени пользователю открытой консоли.

    Ошибка прямым текстом говорил, что была использована парольная аутентификация, в результате пользователя с таким паролем в базе не оказалось. Разберитесь как авторизоваться в базе в вашей системе, к дампам отношения не имеет никакого.
    Ответ написан
    4 комментария
  • Как написать функцию итератор?

    Melkij
    @Melkij
    PostgreSQL DBA
    Это называется не итератором.

    insert into terms (term, freq) values (?, 1) on conflict (term) do update freq = excluded.freq + 1 returning freq;

    В функцию завернуть по желанию. 9.5+
    Ответ написан
    Комментировать
  • Как победить NULL при составлении запроса в БД?

    Melkij
    @Melkij
    PostgreSQL DBA
    Соответственно придумайте, как корректно представить NULL в values именно NULL'ом. От такого использования функции join очевидно придётся отказаться.

    А лучше не извращайтесь, и используйте pdo prepared statements, который null передавать умеет штатно.
    Ответ написан
    Комментировать
  • Как правильно отправить sql ошибку клиенту?

    Melkij
    @Melkij
    PostgreSQL DBA
    А зачем вообще отправлять ошибку запроса на клиент?
    Ошибке запроса самое место в логе и только там. Клиенту - HTTP 500 и "извините, не шмогла" написанное любым вариантом какой вам понравится.
    Если требуется другое сообщение (или действие) для конкретной ошибки - ловите этот конкретный код ошибки. На допустимость логина обычно проверяют предварительным запросом, в апдейт on conflict пока не завезли. Плюс можно хапнуть advisory lock для устранения race condition и закатать всё в одну хранимку чтобы сэкономить на планировщике и сети.
    Ответ написан
    2 комментария
  • Какие запросы к бд mysql можно считать быстрыми и какие медленными?

    Melkij
    @Melkij
    PostgreSQL DBA
    Какие запросы к бд mysql можно считать быстрыми и какие медленными?

    Быстрые это те, время обработки которых не мешает пользователю/бизнесу/всем_причастным.
    Медленные - это соответственно те, которые мешают.

    0.06 sec в консоли - это 6 сотых секунды, конечно.

    Для веба значение имеет время рендеринга страницы пользователю. Хороший результат для backend'а - это 0,2 секунды на генерацию страницы. Это включая всё время обращений к кешам, базам данных и прочую логику построения страницы.
    Ответ написан
    Комментировать
  • Postgresql запрос, сравнение полученных данных и перевод в секунды, как сделать?

    Melkij
    @Melkij
    PostgreSQL DBA
    select extract(epoch from now() - MAX(DATE_OP)) seconds_ago from tablename;
    Ответ написан
    Комментировать
  • Как сделать сортировку рекурсивного запроса в postgresql?

    Melkij
    @Melkij
    PostgreSQL DBA
    Так сравниваются массивы - поэлементно по количеству элементов в меньшем из массивов.
    Если элементы идентичны - сравниваем длины массивов. Поэтому массивы в таком виде вам подходят слабо.

    Может лучше ваш список смежности вовсе заменить на штатное материализованное представление ltree?
    Ну или во всяком случае на него можно заменить ваши массивы.
    Ответ написан
    1 комментарий
  • Как правильно составить запрос для взятия первого значения из группы?

    Melkij
    @Melkij
    PostgreSQL DBA
    Ну а когда станет скучно перебирать всю таблицу - обращайтесь :-)

    Postgresql 9.4 и выше, выбор по известному диапазону дат через index scan по timestamp полю:
    select day, data 
    from generate_series('2017-12-10', '2017-12-12', interval '1 day') as day, 
    lateral (
        select data from tablename 
        where "timestamp" between day and day + interval '1 day' 
        order by "timestamp" desc limit 1
    ) ljd;


    Все дни из таблицы по индексу:
    with recursive t as (
    (select "timestamp"::date as day, data from tablename order by "timestamp" desc limit 1)
    union all
    select bpt.* from t, lateral (
    select "timestamp"::date as day, data from tablename where "timestamp" < t.day order by "timestamp" desc limit 1
    ) as bpt
    )
    select * from t;

    Используя loose index scan
    Ответ написан
    7 комментариев
  • Как правильно создать функцию возвращающую булевый результат выполнения селекта?

    Melkij
    @Melkij
    PostgreSQL DBA
    Вероятно при упрощении примера вы выкинули собственно саму причину синтаксической ошибки.
    Потому что приведённая в вопросе хранимка синтаксически корректна, что, впрочем, не изменяет некорректности семантической.
    ERROR: cannot use RETURN QUERY in a non-SETOF function

    Вы не можете использовать return query для возврата одного значений. return query только для функций возвращающих множество. Булево выражение можно посчитать непосредственно в return, как в большинстве других языков:
    CREATE OR REPLACE FUNCTION checkPermission(text, text) RETURNS BOOLEAN AS
    $$
    BEGIN
      RETURN $1 && $2;
    END;
    $$ LANGUAGE plpgsql


    Ну и мелочь лучше записывать на language sql, что позволяет оптимизаторы делать интересные фокусы в целях повышения производительности запросов.
    CREATE OR REPLACE FUNCTION checkPermission(text, text) RETURNS BOOLEAN AS
    $$
      SELECT $1 && $2;
    $$ LANGUAGE sql


    Разумеется, это будет работать только если у вас определён оператор && для пары операндов типа text.
    Ответ написан
    Комментировать
  • Можно ли поставить разные таймзоны в типе datetime with timezone postgresql?

    Melkij
    @Melkij
    PostgreSQL DBA
    timestamp with time zone приводит и хранит данные только в UTC и переводит их в местное время при чтении согласно сессионной настройке timezone.

    https://www.postgresql.org/docs/9.6/static/datatyp...
    For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.


    timestamp without time zone вовсе игнорирует указание в литерале временной зоны.

    а других timestamp и нет. Поэтому нет, хранить таймзону (при необходимости в этом) необходимо отдельно. Зато за счёт этого timestamp занимает всего константные 8 байт.
    Ответ написан
    Комментировать
  • Вопрос по статье на Хабре про триггеры - могут ли гарантировать триггеры целостность данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    но как переменная current_amount попадет внутрь триггера?

    Автор статьи не стал вдаваться в детали какой-либо СУБД, поэтому уточняйте в документации к своей СУБД.
    Для for each row триггеров обычно можно читать из NEW.amount в after insert триггерах.
    Для for each statement - смотрите документацию к своей СУБД.

    либо ограничить инсерты\апдейты (разрешив их только вызовами изнутри sql-сервера), но мне кажется, что sql-сервер не позволяет добавлять такие ограничения на таблицы

    Смотрите опять же документацию к своей СУБД.
    Для postgresql такую модель прав сделать можно. Для этого необходим отдельный пользователь для деплоя приложения (вообще полезная штука всегда) и security definer триггер.

    могут ли гарантировать триггеры целостность данных?

    Могут, но бывают сюрпризы. Например, mysql вам не запретит из триггера на ACID табличке что-то делать с myisam. Последующий rollback транзакции по любой причине = у вас потеря целостности. Впрочем, если вы знаете о целостности данных - то вы и не станете использовать всякое myisam.
    Ответ написан
    Комментировать
  • Выбрать первую и последнюю запись группы?

    Melkij
    @Melkij
    PostgreSQL DBA
    В смысле вот так?
    select ticket_number , min(some_id), max(some_id) from tablename group by ticket_number
    Ответ написан
  • Как сделать DELETE с вложенным SELECT`ом?

    Melkij
    @Melkij
    PostgreSQL DBA
    Исторически mysql сообщает об ошибке синтаксиса начиная с того места, где эта ошибка случилась.
    Т.е. парсер mysql не умеет использовать алиас таблицы для delete запросов.

    Или, как следует из документации
    Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement. Elsewhere, alias references are permitted but not alias declarations.

    Возможно сделать вырожденный случай мультитабличного удаления с одной таблицей, тогда алиас для таблицы задать возможно:
    delete t from test as t where t.i = 100;
    Ответ написан
  • Можно ли сделать такой SQL-запрос?

    Melkij
    @Melkij
    PostgreSQL DBA
    select org from tablename group by org having count(distinct active) > 1;
    Ответ написан
    2 комментария
  • Что учить администратору баз данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    Как человек в этом году неожиданно сменивший деятельность с senior php dev на DBA - хочу задать встречный вопрос:
    а вы вообще видите вакансии на начинающего-студента-DBA? Целую одну или хотя бы даже две? Для увидевшего SQL вот только что студента и уже желающего быть DBA всего через пару месяцев? Человека, который даже не написал, какую именно СУБД ему интересно изучать до уровня DBA?

    Отдельная позиция DBA в проекте - значит ни штатные системные администраторы (со стороны администрирования) ни старшие разработчики (со знанием SQL) уже не обладают достаточными познаниями в используемой СУБД. Но эта БД важна для бизнеса и ищется специалист по ней, который будет обеспечивать хорошую работу этой базы.
    Несколько позиций DBA в кампании чтобы брать стажера - это очень большой проект. Эти могут себе позволить отвлекать своих специалистов от работы и вкладывать деньги в выращивание нового специалиста. Если вы чем-то сильно заинтересуете кампанию.

    Я не знаю как стать DBA с нуля. Похоже DBA становятся только имея за плечами заметный опыт администрирования или разработки.

    Определитесь с конкретной СУБД и прочитайте полностью её мануал. Например мануал postgresql 10 в pdf занимает свыше 3 тысяч страниц A4. На пару месяцев этого уже хватит. А это только мануал. Только по непосредственно СУБД.
    Плюс необходимо знать базовое администрирование той ОС под которой эта СУБД используется (например, я как postgresql dba даже близко не представляю что делать с windows - такая экзотика в жизни не встречается. А вот для MS SQL наверняка необязательно разбираться в linux).
    Плюс теория: реляционная логика, обеспечение транзакционного, конкурентного доступа, восстановление после сбоев
    Плюс практика - активность в профильных сообществах, форумах. Читаете, проверяете, запоминаете, вежливо переспрашиваете в комментариях если вам кажется что предыдущий отвечающий ошибся, отвечаете на вопросы.

    Интересно? Вперёд. Но в DBA за 3 месяца из нулевого студента - не верю.
    Ответ написан
    2 комментария
  • Как правильно написать двойное обновление?

    Melkij
    @Melkij
    PostgreSQL DBA
    Вы сильно заблуждаетесь, если думаете, что ваш cte выполняется последовательно.
    The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.

    https://www.postgresql.org/docs/9.6/static/queries...

    Вы выполняете на одном слепке данных одних и тех строк различающиеся действия. Не надо так. Я понятий не имею, какой эффект от этого будет.
    К тому же вы элементарно переписываетесь в один просто запрос
    UPDATE "TABLE1"
    SET
      "Value2" = (NOT EXISTS(
        SELECT NULL
        FROM "TABLE2"
        WHERE "что-то" = "кое-что"
    )
          AND NOT EXISTS(
        SELECT NULL
        FROM "TABLE3"
        WHERE "что-то" = "кое-что"
    ))
    WHERE "кое-что" = ANY ($1 :: INT [])
    Ответ написан
    Комментировать
  • Как при запросе получить в ячейке больший объем символов?

    Melkij
    @Melkij
    PostgreSQL DBA
    pg_stat_activity.query ограничен сверху конфигурационным параметром track_activity_query_size. Дефолтно 1024 байта.

    PS: current_query оно называлось до postgresql 9.2, которые уже давно EOL и сам 9.2 уже EOL. Планируйте обновление.
    Ответ написан
  • PostgreSQL 9.4.9. Текстовая колонка: какие-то значения были добавлены как string, другие как integer- может ли это привести к значения NULL?

    Melkij
    @Melkij
    PostgreSQL DBA
    Как добавляли, так и будет храниться и читаться.
    postgres=# create schema garage;
    CREATE SCHEMA
    postgres=# CREATE TABLE garage.users
    (
    car_id text COLLATE pg_catalog."default"
    )
    WITH (
    OIDS = FALSE
    )
    TABLESPACE pg_default;
    CREATE TABLE
    postgres=# insert into garage.users values ('1'), ($$'1'$$), (null);
    INSERT 0 3
    postgres=# select car_id, car_id is null from garage.users ;
     car_id | ?column? 
    --------+----------
     1      | f
     '1'    | f
            | t
    Ответ написан
    Комментировать
  • Как посмотреть логи изменений в базе данных MySQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    - изобрести машину времени
    - вернуться на дату 90 дней назад
    - включить логирование всех запросов либо настроить ведение и непрерывное архивирование бинарного лога
    - вернуться в настоящее время и анализировать настроенный в прошлом шаге лог

    Если вы не настроили вести лог изменений - то его никто хранить не будет. Даже mvcc версионник innodb будет стараться вычищать старые версии строк. Потому что зачем хранить кучу данных, если не было изложено требования их хранить?
    Ответ написан
    Комментировать