Архивы pgsql

Быстрый поиск разницы файлов

02.08.2023 Павел Грибов PHP

Ну собственно это история одной маленькой победы, которые происходят обычно у ИТишников каждый день 😉

Предыстория: при работе скрипта по заливке данных в БД из файла произошло зависание сервера. Скрипт работал в несколько потоков с одним файлом. Потому определить на каком именно месте файла произошла остановка не представлялось возможным. Удалять уже залитое в БД и стартовать скрипт заново — не вариант, скрипт и так работал двое суток, и терять их снова — ну так себе решение.

Решение №1. «В лоб». Ну думаю доработаю скрипт так, что если данные уже есть, то просто пропускаем. Т.е. перед вставкой выполняем проверку функцией вида:

function lsYet($period,$ls){
    global $db;
  $res=false;
  $sql="select posting_addresses.id from posting_addresses
    inner join ls on ls.id=posting_addresses.ls
    where 
    posting_addresses.period='$period' and ls.ls='$ls'";
    $query = $db->connection->prepare($sql);
    $query->execute();
    while ($row99 = $query->fetch()) {
      $res=true;
    };        
 return $res;
}

function lsYet($period,$ls){

global $db;

$res=false;

$sql="select posting_addresses.id from posting_addresses

inner join ls on ls.id=posting_addresses.ls

where

posting_addresses.period='$period' and ls.ls='$ls'";

$query = $db->connection->prepare($sql);

$query->execute();

while ($row99 = $query->fetch()) {

$res=true;

};

return $res;

}

И без проблем дозальем то, чего нет в БД. Да не тут то было, оказывается операция select в этом случае весьма дорогостоящая, и т.к. в БД записей порядка 600тыс, и индексы проставлены на ls и period корректно, но всёж скорость проверки крайне низкая, и т.о. скорость «дозалития» сокращается с двух суток до суток. Ну что собственно не устраивает.

Решение №2. Вдумчивое. Решил было выгрузить ключевые строки (лицевой счет) в файлы: файл ls_in_base.txt — лицевые счета которые уже в БД и ls_all.txt — файл со всеми лицевыми счетами, которые должны быть в БД, отсортировав их командой sort:

sort ls_in_base.txt > ls_in_base.txt
sort ls_all.txt > ls_all.txt

1 2	sort ls_in_base.txt > ls_in_base.txt sort ls_all.txt > ls_all.txt

Далее воспользуемся Linux командой comm, вычленив уникальные записи файла ls_all которые не содержаться в файле ls_in_base:

comm -23 ls_all.txt ls_in_base.txt > no_in_base.txt

1	comm -23 ls_all.txt ls_in_base.txt > no_in_base.txt

И далее уже в скрипте вместо проверки наличия лс в БД при помощи запроса, проверяем наличие лс в БД при помощи in_array:

$no_in_base_ls=file_get_contents("no_in_base.txt");
$no_in_base_arr=explode("\n",$no_in_base_ls);
...
if (in_array($ls, $no_in_base_arr)==true){
...
заливаем данные в БД
...
}

$no_in_base_ls=file_get_contents("no_in_base.txt");

$no_in_base_arr=explode("\n",$no_in_base_ls);

...

if (in_array($ls, $no_in_base_arr)==true){

...

заливаем данные в БД

...

}

В результате скорость увеличилась в разы, и БД дозалить удалось в течении часа

Оставить комментарий pgsql, php

PostgreeSQL: формат даты

08.06.2023 Павел Грибов WEB

Для форматирования даты в стоку нужного формата в PGSQL предусмотрена функция to_char(дата,формат). Использовать в запросе можно примерно так:

select 
comments.comment,
to_char(comments.dt, 'DD.MM.YYYY HH24:MI:SS'),
ls.ls,
users.login
from comments
inner join ls on ls.id=comments.lsid
inner join users on users.id=comments.userid
where ls.ls='1234' order by dt desc limit 10

select

comments.comment,

to_char(comments.dt, 'DD.MM.YYYY HH24:MI:SS'),

ls.ls,

users.login

from comments

inner join ls on ls.id=comments.lsid

inner join users on users.id=comments.userid

where ls.ls='1234' order by dt desc limit 10

При этом поддерживаются следующие паттерны форматирования:

Паттерн	Описание
`HH`	час дня (01-12)
`HH12`	час дня (01-12)
`HH24`	час дня (00-23)
`MI`	минуты (00–59)
`SS`	секунды (00–59)
`MS`	милисекунды (000–999)
`US`	микросекунды (000000–999999)
`FF1`	десятые доли секунды (0-9)
`FF2`	сотая доля секунды (00-99)
`FF3`	милисекунлы (000–999)
`FF4`	десятая часть миллисекунды (0000-9999)
`FF5`	сотая доля миллисекунды (00000-99999)
`FF6`	микросекунды (000000–999999)
`SSSS`, `SSSSS`	секунды после полуночи (0-86399)
`AM` , `am` , `PM` или `pm`	меридианный индикатор (без периодов)
`A.M.` , `a.m.` , `P.M.` или `p.m.`	меридианный индикатор (с периодами)
`Y,YYY`	год (4 и более цифры)с запятой
`YYYY`	год (4 и более цифры)
`YYY`	последние 3 цифры года
`YY`	последние 2 цифры года
`Y`	последняя цифра года
`IYYY`	ISO 8601 год с номером недели (4 или более цифр)
`IYY`	последние 3 цифры года по стандарту ISO 8601 год с номером недели
`IY`	последние 2 цифры года по ISO 8601 номер недели
`I`	последняя цифра года недели по ISO 8601
`BC` , `bc` , `AD` или `ad`	индикатор эры (без периодов)
`B.C.` , `b.c.` , `A.D.` или `a.d.`	индикатор эры (с периодами)
`MONTH`	полное название месяца в верхнем регистре (с пустыми кнопками до 9 символов)
`Month`	полное название месяца с заглавными буквами (с пустыми кнопками до 9 символов)
`month`	полное название месяца в нижнем регистре (с пустыми кнопками до 9 символов)
`MON`	сокращенное название месяца в верхнем регистре (3 символа на английском языке,локализованная длина варьируется)
`Mon`	сокращенное название месяца с заглавными буквами (3 символа на английском языке,локальная длина варьируется)
`mon`	сокращенное название месяца в нижнем регистре (3 символа на английском языке,локализованная длина варьируется)
`MM`	номер месяца (01-12)
`DAY`	полное название дня в верхнем регистре (с пустыми кнопками до 9 символов)
`Day`	полное название дня с заглавной буквы (пустое,до 9 символов)
`day`	полное название дня в нижнем регистре (с пустыми кнопками до 9 символов)
`DY`	сокращенное название дня в верхнем регистре (3 символа на английском языке,локализованная длина варьируется)
`Dy`	сокращенное название дня с заглавными буквами (3 символа на английском языке,локальная длина варьируется)
`dy`	сокращенное название дня в нижнем регистре (3 символа на английском языке,локализованная длина варьируется)
`DDD`	день года (001-366)
`IDDD`	день года с нумерацией недель ISO 8601 (001-371;день 1 года-понедельник первой недели ISO)
`DD`	день месяца (01-31)
`D`	день недели, с воскресенья ( `1` ) по субботу ( `7` )
`ID`	ISO 8601 день недели, с понедельника ( `1` ) по воскресенье ( `7` )
`W`	неделя месяца (1-5)(первая неделя начинается в первый день месяца)
`WW`	номер недели года (1-53)(первая неделя начинается в первый день года)
`IW`	номер недели года с нумерацией недель ISO 8601 (01-53;первый четверг года приходится на неделю 1)
`CC`	век (2 цифры)(XXI век начинается с 2001-01-01 гг.)
`J`	Юлианская дата (целое число дней с 24 ноября 4714 г. до н.э. в местную полночь; см. Раздел B.7 )
`Q`	quarter
`RM`	месяц римскими цифрами в верхнем регистре (I-XII;I=январь)
`rm`	месяц в нижнем регистре римскими цифрами (i-xii;i=январь)
`TZ`	Аббревиатура часового пояса в верхнем регистре (поддерживается только в `to_char` )
`tz`	аббревиатура часового пояса в нижнем регистре (поддерживается только в `to_char` )
`TZH`	смещение зоны в часах
`TZM`	смещение зоны в минутах
`OF`	смещение часового пояса от UTC (поддерживается только в `to_char` )

Оставить комментарий pgsql, postrgreesql, формат даты

Перенос работающего сайта WordPress с Mysql на PostgreeSQL

24.04.2023 Павел Грибов WEB

Задача: перенести рабочий, наполненный постами, сайт на движке WordPress с БД MySQL на PostgreeSQL

Инструментарий:

1) pgloader — утилита для переноса данных из баз данных MySQL в PostgreeSQL

Особенности о которые спотыкался:

для успешного переноса необходимо чтобы пользователь в терминале под которым осуществляется перенос имел максимальные права в БД и имел доступ «без пароля» для входа в консоль pgsql
при переносе создается схема с именем БД, а не заливается по умолчанию в схему public. Что в принципе для нашей ситуации даже будет плюсом.
Перед переносом, БД уже должна быть создана в PostgreeSQL

Синтаксис:

pgloader mysql://login:password@host/db_name pgsql:///db_name

1	pgloader mysql://login:password@host/db_name pgsql:///db_name

2) Плагин PG4WP который на «лету» исправляет запросы к MySQL на PostgreeSQL

Особенности:

работает только при «чистой» установке. Т.е. WordPress должен быть штатно проинсталлирован с нуля
Инсталляция корректно работает только до версии WordPress 5.9
Плагин заброшен, последнее обновление — 3 года назад

Итак, общий алгоритм переноса рабочего WordPress получается такой:

Переносим при помощи pgloader БД в отдельную схему
Заново устанавливаем WordPress 5.9 с плагином PG4WP
Удаляем из схемы public wp_posts (это если только статьи переносим) и переносим её из схемы созданной pgloader в public
Пробуем обновить штатно WordPress

Точно понадобится:

-- перенос таблицы из отдной схемы в другую
ALTER TABLE wp_base.wp_users
SET SCHEMA public

-- перенос таблицы из отдной схемы в другую

ALTER TABLE wp_base.wp_users

SET SCHEMA public

Удаление всех таблиц в схеме:

-- удаляем таблицы
DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
    EXECUTE 'DROP TABLE ' || quote_ident(r.tablename) || ' CASCADE';
  END LOOP;
END $$;
-- удаляем последовательности
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT relname FROM pg_class where relkind = 'S') LOOP
        EXECUTE 'DROP SEQUENCE IF EXISTS ' || quote_ident(r.relname) || ' CASCADE';
    END LOOP;
END $$;

-- удаляем таблицы

DO $$ DECLARE

r RECORD;

BEGIN

FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP

EXECUTE 'DROP TABLE ' || quote_ident(r.tablename) || ' CASCADE';

END LOOP;

END $$;

-- удаляем последовательности

DO $$ DECLARE

r RECORD;

BEGIN

FOR r IN (SELECT relname FROM pg_class where relkind = 'S') LOOP

EXECUTE 'DROP SEQUENCE IF EXISTS ' || quote_ident(r.relname) || ' CASCADE';

END LOOP;

END $$;

Поменять схему всех таблиц:

DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'старая_схема') LOOP
    EXECUTE 'ALTER TABLE старая_схема.' || quote_ident(r.tablename) || ' SET SCHEMA новая_схема ';
  END LOOP;
END $$;

DO $$ DECLARE

r RECORD;

BEGIN

FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'старая_схема') LOOP

EXECUTE 'ALTER TABLE старая_схема.' || quote_ident(r.tablename) || ' SET SCHEMA новая_схема ';

END LOOP;

END $$;

В принципе пока вполне рабочая схема

Оставить комментарий pgsql, postgresql, wordpress

PostgreeSQL: ошибка вызова sha256 со значением столбца в качестве параметра

13.03.2023 Павел Грибов WEB

Функция sha256 создаёт хэш. Однако есть нюанс — в качестве входящего параметра она принимает тип bytea. Соответственно если вы вызовете что-то вроде:

select sha256('aa')

1	select sha256('aa')

То результат будет получен. Однако при попытке выполнить что-то типа:

select sha256(txt) from users;

1 2	select sha256(txt) from users;

Получите ошибку вида:

ERROR:  function sha256(character varying) does not exist
LINE 1: select sha256(txt) from users;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883

ERROR: function sha256(character varying) does not exist

LINE 1: select sha256(txt) from users;

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

SQL state: 42883

Т.е. не соответствие типов. Как быть? На лету преобразуем тип текст в тип bytea:

select sha256(txt::bytea) from users;

1	select sha256(txt::bytea) from users;

Оставить комментарий pgsql, sha256, типизация

Insert или Update если не уникальная запись

14.12.2022 Павел Грибов MySQL

Маленькая напоминалка . Синтаксис pgsql чуть отличается от mysql.

Задача: если запись не уникальна в таблице, то просто обновить дату последнего обновления.

MySQL:

INSERT INTO users_sessions(user_id, ssid) VALUES(10, "1") ON DUPLICATE KEY UPDATE last_updated=now()

1	INSERT INTO users_sessions(user_id, ssid) VALUES(10, "1") ON DUPLICATE KEY UPDATE last_updated=now()

PostgreeSQL:

insert into users_sessions (user_id,ssid) values (10,'1') on conflict(ssid) do update set last_updated=now();

1	insert into users_sessions (user_id,ssid) values (10,'1') on conflict(ssid) do update set last_updated=now();

Уникальным должен быть объявлен столбец ssid

Оставить комментарий insert or update, mysql, pgsql

1 2 »

Пн	Вт	Ср	Чт	Пт	Сб	Вс
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30