postgresql_query has no option creates and no option removes

[sebasmannem]

SUMMARY

the shell module (and some other generic modules) have an option creates: and an option removes:.

• creates: a filename, when it already exists, this step will not be run.
• removes: a filename, when it does not exist, this step will not be run. This helps to easily make using this module (somewhat) idempotent. We could add these options to postgresql_query too:
• creates: a query, when it returns records, this step will not be run.
• removes: a query, when it doesn't return records, this step will not be run.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql_query

ADDITIONAL INFORMATION

I currently have a case where a script to create a user schema should only be run by Ansible, if it doesn't already exist. We can automate this with a first step which checks and registers, but nicer would be to have an option that runs a query like:

select nspname from pg_namespace where nspname = 'my_schema';

, and only runs the script if it returns 0 rows.

If you agree, I will fix this and the other postgresql_query issues that are currently open...

[sebasmannem]

@Andersson007 Let me know if you are ok with me making a PR for this...

[sebasmannem]

Btw, a nice feature is that this can also be used to only run on master databases with something like:

creates: "select * from pg_database where not pg_is_in_recovery();"

[Andersson007]

@sebasmannem hi, thank you for the issue! Sounds good to me (though I don't work with databases any more but it sounds interesting). I'm happy with any worthwhile improvements that have integration tests and do not break backwards compatibility and user playbooks:)

@tcraxs @andytom @kostiantyn-nemchenko @asif what do you think about the suggestion?

(if no objections today or tomorrow, feel free to start. If you gonna fix more than one issue, could you please submit them with separate PRs so that it would be easier to review?)

Thank you!

[Andersson007]

I'm not sure if option names creates / removes can't be confused for some users. Maybe run_if_true or something else that would be closer to this context?

[hunleyd]

I concur that creates/removes is going to be confusing/non-obvious. What about if_exists/if_not_exists as those are already known to people who write SQL (create table foo if not exists, drop view bar if exists)?

[kostiantyn-nemchenko]

+1 for if_exists/if_not_exists

[Andersson007]

+1 to if_exists/if_not_exists from me too. @hunleyd thanks for the suggestion!

[sebasmannem]

So, When the query in If_exists does not return rows, the main query will not be run. Similarly when the query in if_not_exists returns rows, the main query will not be run. Sounds good :)

Verstuurd vanaf mijn iPhone

Op 26 aug. 2021 om 17:16 heeft Andrew Klychkov @.***> het volgende geschreven:

 +1 to if_exists/if_not_exists from me too. @hunleyd thanks for the suggestion!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[klando]

So you need 2 tasks (get info, act if required), why adding complexity here ?

To be clear: -1 from me. I fail to see a real use-case. Examples ?

[Andersson007]

closing this to keep the tracker clean, thanks everyone!