PostgreSQL добавление значения в дочернюю таблицу

Question

PostgreSQL добавление значения в дочернюю таблицу

Рейтинг: 0Ответов: 1Опубликовано: 25.08.2023

Создаётся платеж, записывается в таблицы: платежи (payments) и история изменения статусов (payments_approval_history). В таблице истории изменений есть внешний ключ из ссылающийся на таблицу договоров.
Правильно ли производить запись в таблицу payments, делать commit, а после делать запись в таблицу payments_approval_history и тоже делать commit?
Как защититься от случая, когда мы записали в данные в одну таблицу, но получили ошибку при записи во вторую, и в итоге потеряли запись, ну и не поняли, что потеряли эту запись?

DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS payments_approval_history;

CREATE TABLE payments(
   payment_id INT GENERATED ALWAYS AS IDENTITY,
   payment_number VARCHAR(255) NOT NULL,
   PRIMARY KEY(payment_id)
);

CREATE TABLE payments_approval_history(
   confirm_id INT GENERATED ALWAYS AS IDENTITY,
   payment_id INT,
   status_id INT DEFAULT 1
   PRIMARY KEY(confirm_id),
   CONSTRAINT fk_payment
      FOREIGN KEY(payment_id) 
      REFERENCES payments(payment_id)
);

Записываем данные в таблицы. (упрощенный код)

import psycopg2 as pg

from psycopg2.extras import execute_values

values_p = [
    ('num-1'),
    ('num-2'),
    ('num-3')
]

with pg.connect('<connection string>') as conn:
    with conn.cursor() as cur:
        try:
            p_id = execute_values(
                cur,
                """
                INSERT INTO payments (
                    payment_number
                )
                VALUES %s on conflict do nothing 
                RETURNING payment_id;
                """,
                values_p,
                template="""(%s)""",
                page_size=len(values_p),
                fetch=True
            )
        except Exception:
            conn.rollback()
            raise
        else:
            conn.commit()


        values_p_a_h = []
        [values_p_a_h.append((x,)) for x in p_id]

        try:
            execute_values(
                cur,
                """
                INSERT INTO payments_approval_history (
                    payment_id
                )
                VALUES %s on conflict do nothing;
                """,
                values_p_a_h,
                template="""(%s::int)""",
                page_size=len(values_p_a_h) # вот тут возможны варианты. По умолчанию 500
            )
        except Exception:
            conn.rollback()
            raise
        else:
            conn.commit()

Вопрос, правильно ли так делать? если нет, то как сделать лучше?

python postgresql psycopg2

Источник: Stack Overflow на русском

Answer 1

▲ 1Принят

Попробуйте вот такую конструкцию.

create table status
(
    id      int generated by default as identity primary key,
    title   varchar not null,
    payload jsonb default '{}'
);

insert into status (title) values ('created'), ('new'), ('approved');

create table users
(
    id       int generated by default as identity primary key,
    username varchar,
    name     varchar,
    surname  varchar
);

CREATE TABLE transaction
(
    id        int GENERATED ALWAYS AS IDENTITY primary key,
    sender    int references users on delete restrict on update cascade,
    receiver  int references users on delete restrict on update cascade,
    amount    numeric not null,
    status_id int references status on delete restrict on update cascade default 1
);

create table transaction_status_history
(
    id             int generated by default as identity primary key,
    transaction_id int references transaction on delete cascade on update cascade,
    old_status_id  int references status on delete set null on update cascade,
    new_status_id  int references status on delete restrict on update cascade,
    datetime       timestamp default current_timestamp
);

create or replace function reg_transaction_status() returns trigger
    language plpgsql
as
$$
begin
    if tg_op = 'INSERT' then
        insert into transaction_status_history (transaction_id, old_status_id, new_status_id)
        values (new.id, 1, new.status_id);
    elseif tg_op = 'UPDATE' then
        insert into transaction_status_history (transaction_id, old_status_id, new_status_id)
        values (new.id, old.status_id, new.status_id);
    end if;
    return null;
end;
$$;


create trigger _000_reg_status_new
    after insert
    on transaction
    for each row
execute procedure reg_transaction_status();

create trigger _000_reg_status_upd
    after update
    on transaction
    for each row
    when ( new.status_id is distinct from old.status_id)
execute procedure reg_transaction_status();

insert into users (username, name, surname) values ('geek', 'John', 'Verter'), ('japi', 'Jago', 'Pilman');

insert into transaction (sender, receiver, amount) values (1, 2, 25000);

update transaction set status_id = 2 where id = 1;

select * from transaction_status_history;

id	transaction_id	old_status_id	new_status_id	datetime
1	1	1	1	2023-08-25 13:23:47.998185
2	1	1	2	2023-08-25 13:23:48.314745

С общей логикой последовательности статусов может напортачил слегка, но постарался наглядно изобразить создание истории статусов с помощью тригеров

PostgreSQL добавление значения в дочернюю таблицу

Ответы