Htykuut

Postgres Version : 11

Procedure code:

CREATE OR REPLACE PROCEDURE public.prc_insert_proc(table_name character varying)

 LANGUAGE plpgsql

AS $procedure$ declare StartTime timestamptz;



EndTime timestamptz;



Delta double precision;

err_context text;

tab_partition RECORD;

v_tab_partition varchar;

begin



for tab_partition in (SELECT

    child.relname       AS child

FROM pg_inherits

    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid

    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid

    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace

    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace

WHERE parent.relname=table_name) loop

v_tab_partition = tab_partition.child;

begin

execute 'insert

    into

        test_tab (id,

    report_date,

        effective_asof_date,

        effective_until_date,

        active_flag) select id,

      to_date(report_date,''YYMMDD'') report_date,

            now(),

            ''31-Dec-2200'',

            ''Y''

        from '||

            tab_partition.child ||'  on

            conflict (id) do update set report_date=excluded.report_date;';

commit;

end;



end loop;







exception

    when others then

        GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;

        RAISE INFO 'Error Name:%',SQLERRM;

        RAISE INFO 'Error State:%', SQLSTATE;

        RAISE INFO 'Error Context:%', err_context;



end;



$procedure$;

I am calling above procedure by creating sqlalchemy engine and then calling this procedure.

def load_test(session, table, log):  # pragma: no cover

    try:

        sql = "call prc_insert_proc('" + 

            table + "');"

        session.execute(text(sql).execution_options(autocommit=False))

        session.commit()

    except ErrorBase as e:

        log.error(f"{str(e)}")

        raise

It is throwing up following error. Session was created using sessionmaker and create_engine.

engine = create_engine(config.connection_string, echo=False)

Session = sessionmaker()

Session.configure(bind=engine)

session = Session()

invalid transaction termination

I am pretty new to both sqlalchemy and PostgreSQL.Each partition of the table has about a million record and I want to commit after every insert. Other option would be get the list of partition in the python script and add partition name parameter in procedure. Loop through the procedure in python and commit after every call. However, when I read that in PostgreSQL 11 we can commit inside the procedure than I thought of using that.