PostgreSQL adding value to child table

Question

A payment is created, recorded in the tables: payments (payments) and history of status changes (payments_approval_history). The change history table has a foreign key that refers to the contracts table. Is it correct to write to the payments table, do a commit, and then write to the payments_approval_history table and also do a commit? How to protect ourselves from the case when we wrote to data in one table, but got an error when writing to the second, and as a result lost the record, well, did not understand that we had lost this record?

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)
);

Write data to tables. (simplified code)

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) # here are some options. Default 500
             )
         except Exception:
             conn.rollback()
             raise
         else:
             conn.commit()

The question is, is it right to do so? if not, how to do better?