Оптимизация запросов из базы данных для мобильного приложения

  • Основным механизмом этого процесса является trigger в PostgreSql.Создается функция которая обновляет информацию о том, в каком состоянии находиться таблица. Состояние — это переменная в которой храниться время с типом данных double precision.
INSERT INTO core.sd_table_change (c_table_name, n_change)
VALUES (_c_table_name, (SELECT EXTRACT(EPOCH FROM now())))
ON CONFLICT (c_table_name) DO UPDATE
SET c_table_name = _c_table_name,
n_change = (SELECT EXTRACT(EPOCH FROM now()));
  • Далее создается триггер, который изменяет «состояние таблицы»
CREATE OR REPLACE FUNCTION core.cft_change_version() RETURNS trigger
   LANGUAGE plpgsql
   AS $$
BEGIN
   IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      select core.sf_table_change_update(t.c_table_name_ref) from  (select TG_TABLE_NAME as c_table_name_ref UNION select  c_table_name_ref from core.sd_table_change_ref where c_table_name = TG_TABLE_NAME) as t; END IF; RETURN NEW;
END
$$;
ALTER FUNCTION core.cft_change_version() OWNER TO mobnius;
COMMENT ON FUNCTION core.cft_change_version() IS 'Триггер. Обновление справочной версии';
  • Для хранения информации о состоянии, создается 2 таблицы:
  1. Состояние таблиц — имя таблицы и её числовое состояние
  2. Таблицы зависимых состояний
CREATE TABLE core.sd_table_change (
c_table_name text NOT NULL,
n_change double precision NOT NULL
);
ALTER TABLE core.sd_table_change OWNER TO mobnius;
COMMENT ON TABLE core.sd_table_change IS 'Изменение состояния таблицы';
COMMENT ON COLUMN core.sd_table_change.c_table_name IS 'Имя таблицы';
COMMENT ON COLUMN core.sd_table_change.n_change IS 'Версия изменения';

ALTER TABLE core.sd_table_change
ADD CONSTRAINT sd_table_change_pkey PRIMARY KEY (c_table_name);
CREATE TABLE core.sd_table_change_ref (
id smallint DEFAULT nextval('core.sd_table_change_ref_id_seq'::regclass) NOT NULL,
c_table_name text NOT NULL,
c_table_name_ref text NOT NULL
);
ALTER TABLE core.sd_table_change_ref OWNER TO mobnius;
COMMENT ON TABLE core.sd_table_change_ref IS 'Зависимость таблиц состояний';
COMMENT ON COLUMN core.sd_table_change_ref.c_table_name IS 'Таблица';
COMMENT ON COLUMN core.sd_table_change_ref.c_table_name_ref IS 'Зависимая таблица';

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT sd_table_change_ref_pkey PRIMARY KEY (id);

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT sd_table_change_ref_c_table_name_fkey FOREIGN KEY (c_table_name) REFERENCES core.sd_table_change(c_table_name) NOT VALID;

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT "sd_table_change_ref_cTable_name_ref_fkey" FOREIGN KEY (c_table_name_ref) REFERENCES core.sd_table_change(c_table_name) NOT VALID;

PostgreSQL: преобразование строки в число

В базе данных доступны следующие строки:

Исходные данные

Требуется в колонке c_house_number, которая является типом text, найти число и сохранить преобразованное значение в новой колонке:

Читать далее «PostgreSQL: преобразование строки в число»

Генерация подписи для андроид приложения через интерфейс Android Studio

Рис. 1

Для генерации APK с релизной подписью необходимо выбрать пункт меню Build -> Generate Signed Bundle / Apk (рисунок 1).

Читать далее «Генерация подписи для андроид приложения через интерфейс Android Studio»

Еще один способ логирования состояния сервера

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

  • [remote server] — IP или доменное имя TCP сервера для обработки информации
  • [port] — порт сервера

Каталог для хранения bash-скриптов находиться по адресу /var/lib/big-brother

Примечание: если нет такого каталога, то нужно его создать.

Читать далее «Еще один способ логирования состояния сервера»

Создание временной таблицы в PostgreSQL

    --  список ТУ заданной РЭС
create temp table t_registr_pts (
    id uuid,
    c_registr_pts text,
    f_status_consume int,
    b_technical_metering bool,
    f_subscr uuid,
    f_address uuid,
    c_ps text,
    c_line_ps text,
    c_rp text,
    c_line_rp text,
    c_tp text,
    c_line_tp text,
    c_raion text,
    c_city_name text,
    c_settlement_name text,
    c_street_name text
) on commit drop;

How to track query progress in PostgreSQL?

I found a good answer here: Tracking progress of an update statement

The trick is to first create a sequence (name it as you like):

CREATE SEQUENCE query_progress START 1;

Then append to your query’s WHERE part:

AND NEXTVAL('query_progress')!=0

Now you can query the progress:

SELECT NEXTVAL('query_progress');

Finally don’t forget to get rid of the sequence:

DROP SEQUENCE query_progress;

Note that this will most likely make your query run even slower and every time you check progress it will additionally increment the value. The above link suggested creating a temporary sequence but PostgreSQL doesn’t seem to make them visible across sessions.

How to track query progress in PostgreSQL? — Stack Overflow