Second insert not working, but first and third are

Question

I have three EXECUTE statements running using psycopg2. The first INSERT is visible after the updates and the third INSERT is visible, but second INSERT is not happening.

OpenERP is giving my function an open cursor which I use for all three statements; OpenERP then commits after my function returns. I've tried to catch any exceptions (there are none), I've tried using debugging prints to find clues (none) -- any ideas? Below are the statements.

works:

    cr.execute("""\
        INSERT INTO ir_act_report_xml
            (
              id, header, report_type, type, model, name, report_name,
              create_uid, create_date, write_uid, write_date,
              auto, multi, attachment_use
               )
        VALUES
            (
              %s, 't', 'pdf', 'ir.actions.report.xml', '%s', '%s', '%s',
              1, now() AT TIME ZONE 'UTC', 1, now() AT TIME ZONE 'UTC',
              false, false, false
              )
            """
            % (action_id, model_name, title_name, report_xml_id)
              )

does not work, no errors given

    cr.execute("""\
        INSERT INTO ir_model_data
            (
            module, name, model, res_id, date_init, date_update,
            create_uid, create_date, write_uid, write_date,
            noupdate
            )
        VALUES
            (
              '%s', '%s', '%s', %s, now() AT TIME ZONE 'UTC', now() AT TIME ZONE 'UTC',
              1, now() AT TIME ZONE 'UTC', 1, now() AT TIME ZONE 'UTC',
              false
              )
            """
            % (module, imd_xml_id, model_name, action_id)
            )

works:

    cr.execute("""\
        INSERT INTO ir_values
            (
              id, key, key2, value, model, name, res_id,
              create_uid, create_date, write_uid, write_date
              )
        VALUES
            (
              %s, 'action', 'client_print_multi', 'ir.actions.report.xml,%s', '%s', '%s', 0,
              1, now() AT TIME ZONE 'UTC', 1, now() AT TIME ZONE 'UTC'
              )
            """
            % (iv_id, action_id, model_name, title_name),
            )

All three are processed serially with no intervening commits nor rollbacks.

Answer 1

To track down problems like that, check what actually happens on the database.

Set log_min_duration_statement to 0 before running your SQL statements and check the PostgreSQL log file afterwards. Then you know what statements were actually executed.

Possible explanations for behavior like that:

• The three INSERTs are executed on different database sessions.

• The failing statement doesn't even make it to the database, and the exception is caught in Python.

• Your transaction management is different from what you think, and one statement is rolled back.