Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

«Пост-Грэс-Кью-Эл» свободная объектно-реляционная СУБД. Обмениваемся вопросами и ответами о её работе.
Аватара пользователя
SQL*Plus
Сообщения: 241
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение SQL*Plus »

7) Создадим новую сессию и запишем новые данные, которые должны быть доступны только этой сессии:

Код: Выделить всё

CONNECT SCOTT/TIGER@MY_DB

INSERT INTO GTT_DEPT(DEPTNO, DNAME, LOC) VALUES (1012, '2-ACCOUNTING', 'NEW YORK');
INSERT INTO GTT_DEPT(DEPTNO, DNAME, LOC) VALUES (2022, '2-RESEARCH',   'DALLAS');
INSERT INTO GTT_DEPT(DEPTNO, DNAME, LOC) VALUES (3032, '2-SALES',      'CHICAGO');
INSERT INTO GTT_DEPT(DEPTNO, DNAME, LOC) VALUES (4042, '2-OPERATIONS', 'BOSTON');
INSERT INTO GTT_DEPT(DEPTNO, DNAME, LOC) VALUES (5052, '2-AEROSPACE', 'HOUSTON');
COMMIT;

SELECT * FROM GTT_DEPT;

    DEPTNO DNAME          LOC          
---------- -------------- -------------
      1012 2-ACCOUNTING   NEW YORK     
      2022 2-RESEARCH     DALLAS       
      3032 2-SALES        CHICAGO      
      4042 2-OPERATIONS   BOSTON       
      5052 2-AEROSPACE    HOUSTON 

Как видим, в результате получилось то же, что было во временной таблице

8) Посмотрим, какие данные содержатся в его базовой таблице:

Код: Выделить всё

SELECT * FROM PSEUDO_GTT_DEPT_BASE;

SQL> SELECT * FROM PSEUDO_GTT_DEPT_BASE;

    DEPTNO DNAME          LOC               AUDSID
---------- -------------- ------------- ----------
      1012 2-ACCOUNTING   NEW YORK        12962040
      2022 2-RESEARCH     DALLAS          12962040
      3032 2-SALES        CHICAGO         12962040
      4042 2-OPERATIONS   BOSTON          12962040
      5052 2-MIGRATION    MOSCOW          12962040
      1010 ACCOUNTING     NEW YORK        12962029
      2020 RESEARCH       DALLAS          12962029
      3030 SALES          CHICAGO         12962029
      4040 OPERATIONS     BOSTON          12962029
Видим автоматически заполненный столбец AUDSID с разными значениями для разных сессий.

9) Выбрать и/или удалить строки, уже неиспользуемые никакими сессиями, можно командами:

Код: Выделить всё

SELECT * FROM PSEUDO_GTT_DEPT_BASE
WHERE AUDSID NOT IN (SELECT AUDSID FROM v$session);

DELETE FROM PSEUDO_GTT_DEPT_BASE
WHERE AUDSID NOT IN (SELECT AUDSID FROM v$session);
COMMIT;
Так можно организовать уборку "мусора", который может появиться,
если пользователи не будут очищать "псевдовременную" таблицу командами:

Код: Выделить всё

DELETE FROM GTT_DEPT;
COMMIT;
Аватара пользователя
SQL*Plus
Сообщения: 241
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение SQL*Plus »

vvm13 писал(а): Вс апр 21, 2024 8:26 pm Там же есть CREATE TEMPORARY TABLE?

Да, она не GLOBAL и каждый сеанс должен будет выполнить это для себя, но так уж это плохо?
1) Обсуждаемая тема: "Как организовать аналог GLOBAL TEMPORARY TABLE..."
Это позволит облегчить миграцию приложения, использующего Oracle Database, на PostgreSQL.

2) CREATE TEMPORARY TABLE - это дополнительные команды DDL.
Хорошо ли предоставлять права на CREATE TABLE пользователям,
от имени которых идет работа прикладной системы?
roruizi
Сообщения: 4
Зарегистрирован: Ср июн 01, 2022 4:43 pm

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение roruizi »

Добавлю, что если необходим псевдо уникальный идентификатор, то можно воспользоваться аналогом Session ID (параметр %c log_line_prefix) из журнала PostgreSQL. В документации есть пример как получить этот идентификатор из представления pg_stat_activity:

Код: Выделить всё

SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
       to_hex(pid)
FROM pg_stat_activity;
vvm13
Сообщения: 11
Зарегистрирован: Ср дек 06, 2023 9:02 am

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение vvm13 »

SQL*Plus писал(а): Пн апр 22, 2024 1:48 pm
vvm13 писал(а): Вс апр 21, 2024 8:26 pm Там же есть CREATE TEMPORARY TABLE?

Да, она не GLOBAL и каждый сеанс должен будет выполнить это для себя, но так уж это плохо?
1) Обсуждаемая тема: "Как организовать аналог GLOBAL TEMPORARY TABLE..."
Это позволит облегчить миграцию приложения, использующего Oracle Database, на PostgreSQL.
И? Вы пытаетесь заменить ораклиные GLOBAL TEMPORARY TABLE какой-то относительно сложной машинерией? Я хотел понять, действительно ли она нужна и когда она может быть нужна, вместо того, чтобы просто выполнить CREATE TEMP TABLE в начале сеанса.
SQL*Plus писал(а): Пн апр 22, 2024 1:48 pm 2) CREATE TEMPORARY TABLE - это дополнительные команды DDL.
Хорошо ли предоставлять права на CREATE TABLE пользователям,
от имени которых идет работа прикладной системы?
Там ведь GRANT TEMP ON DATABASE? "TEMPORARY, TEMP: Allows temporary tables to be created while using the database.".
Аватара пользователя
SQL*Plus
Сообщения: 241
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение SQL*Plus »

vvm13 писал(а): Вт апр 23, 2024 9:39 am
SQL*Plus писал(а): Пн апр 22, 2024 1:48 pm
vvm13 писал(а): Вс апр 21, 2024 8:26 pm Там же есть CREATE TEMPORARY TABLE?

Да, она не GLOBAL и каждый сеанс должен будет выполнить это для себя, но так уж это плохо?
1) Обсуждаемая тема: "Как организовать аналог GLOBAL TEMPORARY TABLE..."
Это позволит облегчить миграцию приложения, использующего Oracle Database, на PostgreSQL.
И? Вы пытаетесь заменить ораклиные GLOBAL TEMPORARY TABLE какой-то относительно сложной машинерией?
Я хотел понять, действительно ли она нужна и когда она может быть нужна, вместо того,
чтобы просто выполнить CREATE TEMP TABLE в начале сеанса.
Если вы посмотрите внимательно, вы увидите, что никакой "сложной машинерии" в предложенном подходе нет.
Поведение глобальной "псевдовременной" таблицы (ПВТ) с точки зрения прикладной системы
практически такое же, как у Global Temporary Table (GTT), но при этом еще поддерживается параллельное выполнение операций.

Внешнее отличие (не видимое изнутри сессии) состоит в том, что после завершения сессии её данные могут остаться в таблице,
и их придется периодически удалять в какой-то другой сессии БД описанной выше командой.
vvm13 писал(а): Вт апр 23, 2024 9:39 am
SQL*Plus писал(а): Пн апр 22, 2024 1:48 pm 2) CREATE TEMPORARY TABLE - это дополнительные команды DDL.
Хорошо ли предоставлять права на CREATE TABLE пользователям,
от имени которых идет работа прикладной системы?
Там ведь GRANT TEMP ON DATABASE? "TEMPORARY, TEMP: Allows temporary tables to be created while using the database.".
  1. Стоит ли пользователям БД предоставлять лишние привилегии?
    GRANT TEMP ON DATABASE my_database TO postscott;
    Такая привилегия даёт пользователю postscott возможность создавать временные таблицы в любой схеме базы данных my_database.
  2. Программист должен постоянно помнить, что нужно в начале сеанса создать нужные временные таблицы.
    Нельзя использовать принцип Set it and forget it (настрой и забудь).
Аватара пользователя
SQL*Plus
Сообщения: 241
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение SQL*Plus »

Есть ли в PostrgeSQL какой-либо уникальный идентификатор сессии,
никогда не повторяющийся в течение жизни базы данных?


Или нечто аналогичное, которое можно использовать для реализации описанного выше подхода?
Аватара пользователя
SQL*Plus
Сообщения: 241
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение SQL*Plus »

roruizi писал(а): Пн апр 22, 2024 7:15 pm Добавлю, что если необходим псевдо уникальный идентификатор, то можно воспользоваться аналогом Session ID (параметр %c log_line_prefix) из журнала PostgreSQL. В документации есть пример как получить этот идентификатор из представления pg_stat_activity:

Код: Выделить всё

SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
       to_hex(pid)
FROM pg_stat_activity;
Выполнил этот запрос на своей базе:

Код: Выделить всё

SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
       to_hex(pid) AS uniq_sess_id
FROM pg_stat_activity;

uniq_sess_id
------------------
6620d407.299c
6620d407.297c
662a6b51.49ec
662a6b53.7b1c
662a6b89.2f44
6620d403.245c
6620d403.25a0
6620d407.2958
Как теперь понять, что относится к моей сессии?
vvm13
Сообщения: 11
Зарегистрирован: Ср дек 06, 2023 9:02 am

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение vvm13 »

SQL*Plus писал(а): Вт апр 23, 2024 5:22 pm Если вы посмотрите внимательно, вы увидите, что никакой "сложной машинерии" в предложенном подходе нет.
Поведение глобальной "псевдовременной" таблицы (ПВТ) с точки зрения прикладной системы
практически такое же, как у Global Temporary Table (GTT), но при этом еще поддерживается параллельное выполнение операций.

Внешнее отличие (не видимое изнутри сессии) состоит в том, что после завершения сессии её данные могут остаться в таблице,
и их придется периодически удалять в какой-то другой сессии БД описанной выше командой.
Нет "сложной машинерии", но есть "относительно сложная машинерия". Если "периодически удалять в какой-то другой сессии БД", то это не совсем похоже на GTT. Кстати, она LOGGED или нет? И то, и другое может быть проблемно. И truncate работать не будет.
list=1][*] Стоит ли пользователям БД предоставлять лишние привилегии?
GRANT TEMP ON DATABASE my_database TO postscott;
Такая привилегия даёт пользователю postscott возможность создавать временные таблицы в любой схеме базы данных my_database.
OK, и в чём может быть опасность такой привилегии? По именам таблиц юзеры между собой не пересекаются и один из них не помешает другому. Диск забить мусором можно при любом подходе.
[*]Программист должен постоянно помнить, что нужно в начале сеанса создать нужные временные таблицы.
Нельзя использовать принцип Set it and forget it (настрой и забудь).
Ну, никто же не заставляет создавать это в начале сеанса. Можно же прямо перед использованием. Для памяти будет легче.
Аватара пользователя
SQL*Plus
Сообщения: 241
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение SQL*Plus »

vvm13 писал(а): Пт апр 26, 2024 12:07 am
SQL*Plus писал(а): Вт апр 23, 2024 5:22 pm Если вы посмотрите внимательно, вы увидите, что никакой "сложной машинерии" в предложенном подходе нет.
Поведение глобальной "псевдовременной" таблицы (ПВТ) с точки зрения прикладной системы
практически такое же, как у Global Temporary Table (GTT), но при этом еще поддерживается параллельное выполнение операций.

Внешнее отличие (не видимое изнутри сессии) состоит в том, что после завершения сессии её данные могут остаться в таблице,
и их придется периодически удалять в какой-то другой сессии БД описанной выше командой.
Нет "сложной машинерии", но есть "относительно сложная машинерия".
Если "периодически удалять в какой-то другой сессии БД", то это не совсем похоже на GTT.
. . .
С точки зрения пользователя сессии Oracle в которой используется ПВТ,
она выглядит так же, как GTT, плюс позволяет использовать параллелизм.

Выполнил INSERT - добавились строки
Выполнил UPDATE - строки изменяются
Выполнил DELETE - строки удаляются
Выполнение COMMIT не удаляет строки (ON COMMIT PRESERVE ROWS),
но делает доступным параллельное выполнение/
vvm13 писал(а): Пт апр 26, 2024 12:07 am Кстати, она LOGGED или нет?
И то, и другое может быть проблемно.
Это зависит от того, как вы договоритесь с администратором вашей базы, отвечающим за её Backup-Recovery.
В Oracle:
- NOLOGGING лучше c точки зрения экономного использования ресурсов и скорости выполнения команд изменения данных
- LOGGING лучше c точки зрения спокойствия администратора при восстановлении базы данных-
не будет сообщений о том, что данные какой-то таблицы восстановить не удалось,
поскольку изменение данных не были записаны в журналы REDO.

Разработчики любят NOLOGGING (быстрее и ещё быстрее!)
Администраторы любят LOGGING (спокойствие и только спокойствие!)

использования ресурсов и скорости выполнения команд изменения данных
vvm13 писал(а): Пт апр 26, 2024 12:07 am И truncate работать не будет.
Да, TRUNCATE работать не будет.
Это сразу было оговорено.
SQL*Plus писал(а): Пт апр 19, 2024 4:40 pm Основные предпосылки и пожелания
. . .
4) Не требуется поддержка усечения временных таблиц (команда TRUNCATE TABLE для "псевдовременных" таблиц не поддерживается)
. . .
Аватара пользователя
SQL*Plus
Сообщения: 241
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: Как организовать аналог GLOBAL TEMPORARY TABLE в PostgreSQL ?!

Сообщение SQL*Plus »

vvm13 писал(а): Пт апр 26, 2024 12:07 am
SQL*Plus писал(а): Вт апр 23, 2024 5:22 pm list=1][*] Стоит ли пользователям БД предоставлять лишние привилегии?
GRANT TEMP ON DATABASE my_database TO postscott;
Такая привилегия даёт пользователю postscott возможность создавать временные таблицы в любой схеме базы данных my_database.
OK, и в чём может быть опасность такой привилегии? По именам таблиц юзеры между собой не пересекаются и один из них не помешает другому. Диск забить мусором можно при любом подходе.
Хорошо.
Будем считать, что зловредных пользователей не будет.
Снимем это замечание.
Ответить