Support SQL transactions

[munnik]

SUMMARY

Ability to begin/commit/rollback a transaction. This can help to group certain queries together so they all take effect at once.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

community.postgresql.postgresql_query

ADDITIONAL INFORMATION

Some queries belong together and they either must all succeed or all fail. The approach below doesn't seem to work.

- name: "Start transaction ({{ organization.name }})"
  community.postgresql.postgresql_query:
    login_user: "somebody"
    login_password: "pw"
    login_host: "pghost"
    db: "something"
    query: 'BEGIN;'

- name: "Remove permissions ({{ organization.name }})"
  community.postgresql.postgresql_query:
    login_user: "somebody"
    login_password: "pw"
    login_host: "pghost"
    db: "something"
    query: 'DELETE FROM "organization_permissions" WHERE "organization" = %s;'
    positional_args:
      - "{{ organization.db_user }}"

- name: "Setup permissions ({{ organization.name }})"
  community.postgresql.postgresql_query:
    login_user: "somebody"
    login_password: "pw"
    login_host: "pghost"
    db: "something"
    query: 'INSERT INTO organization_permissions ("permission", "organization") values (%s, %s);'
    positional_args:
      - "{{ permission }}"
      - "{{ organization.db_user }}"
  loop: "{{ organization.permissions }}"
  loop_control:
    loop_var: permission

- name: "Commit transaction ({{ organization.name }})"
  community.postgresql.postgresql_query:
    login_user: "somebody"
    login_password: "pw"
    login_host: "pghost"
    db: "something"
    query: 'COMMIT'

[hunleyd]

That doesn't work, iirc, because each of those is a new connection. You'd need to put your queries in a file and then pass the file to psql using the command module, I think.

[betanummeric]

Hi @munnik

Although not really intended, it should be possible to simply write multiple statements as one "query":

- community.postgresql.postgresql_query:
    login_user: "somebody"
    login_password: "pw"
    login_host: "pghost"
    db: "something"
    query: >-
      BEGIN;
      DELETE FROM "organization_permissions" WHERE "organization" = %s;';
      COMMIT;
    positional_args:
      - "{{ organization.db_user }}"

This is not ideal because one can only access the result of the last statement, and every iteration of an ansible loop will be a dedicated transaction.

The community.postgresql.postgresql_script module works the same way, but there the statements are read from a file on the target host instead from a variable.

To have a single transaction across multiple ansible tasks as you requested is currently not supported. Maybe it could work if we extend the module(s) to receive and return a variable holding an open transaction, but I'm not sure if this is possible with ansible.