postgresql_query cannot handle interval type #47
Comments
markwort
changed the title
interval type
|
@markwort thanks reporting the issue! Nice to hear that people find the collection useful! CC @Dorn- @andytom @ilicmilan @kostiantyn-nemchenko @matburt @nerzhul @sebasmannem @tcraxs if anybody wants to fix this (see recently merged #46 as an example), please, put your intention here ASAP not to work in parallel (I'll also do it if I decide). Thanks! |
|
FYI: I'm looking at this |
|
there were unrelated CI issues, fixed now |
|
Nice! I verified it and now it works as expected: If I run into something like this again, now I'll now where to start looking. |
|
@markwort thanks for the feedback! |
|
I backported the fix to 2.9 and hope that it'll be merged soon |
netbsd-srcmastr
pushed a commit
to NetBSD/pkgsrc
that referenced
this issue
Feb 25, 2021
v2.9.18 ======= Release Summary --------------- | Release Date: 2021-02-18 | `Porting Guide <https://docs.ansible.com/ansible/devel/porting_guides.html>`__ Minor Changes ------------- - ansible-test - The ``pylint`` sanity test is now supported on Python 3.8. - inventory cache - do not show a warning when the cache file does not (yet) exist. Security Fixes -------------- - **security issue** - Mask default and fallback values for ``no_log`` module options (CVE-2021-20228) - _sf_account_manager - `initiator_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - _sf_account_manager - `target_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - aws_netapp_cvs_active_directory - `api_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - aws_netapp_cvs_active_directory - `secret_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - aws_netapp_cvs_filesystems - `api_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - aws_netapp_cvs_filesystems - `secret_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - aws_netapp_cvs_pool - `api_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - aws_netapp_cvs_pool - `secret_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - aws_netapp_cvs_snapshots - `api_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - aws_netapp_cvs_snapshots - `secret_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - bitbucket_pipeline_variable - hide user sensitive information which are marked as ``secured`` from logging into the console (ansible-collections/community.general#1635) (CVE-2021-20180). - ce_vrrp - `auth_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - cp_mgmt_vpn_community_meshed - `shared_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - cp_mgmt_vpn_community_star - `shared_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - docker_swarm - `signing_ca_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_backend_service - `oauth2_client_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_disk - `disk_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_disk - `source_image_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_disk - `source_snapshot_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_image - `image_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_image - `source_disk_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_instance_template - `disk_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_instance_template - `source_image_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_region_disk - `disk_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_region_disk - `source_snapshot_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_snapshot - `snapshot_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_snapshot - `source_disk_encryption_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_ssl_certificate - `private_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_compute_vpn_tunnel - `shared_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gcp_sql_instance - `client_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - gitlab_runner - `registration_token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - iap_start_workflow - `token_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - ibm_sa_host - `iscsi_chap_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - keycloak_client - `auth_client_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - keycloak_client - `registration_access_token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - keycloak_clienttemplate - `auth_client_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - keycloak_group - `auth_client_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - librato_annotation - `api_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - na_elementsw_account - `initiator_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - na_elementsw_account - `target_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - netscaler_lb_monitor - `radkey` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - nios_nsgroup - `tsig_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - nxos_aaa_server - `global_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - nxos_pim_interface - `hello_auth_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - oneandone_firewall_policy - `auth_token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - oneandone_load_balancer - `auth_token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - oneandone_monitoring_policy - `auth_token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - oneandone_private_network - `auth_token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - oneandone_public_ip - `auth_token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - ovirt - `instance_rootpw` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - pagerduty_alert - `api_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - pagerduty_alert - `integration_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - pagerduty_alert - `service_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - pulp_repo - `feed_client_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - rax_clb_ssl - `private_key` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - snmp_facts - hide user sensitive information such as ``privkey`` and ``authkey`` from logging into the console (ansible-collections/community.general#1621) (CVE-2021-20178). - spotinst_aws_elastigroup - `multai_token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - spotinst_aws_elastigroup - `token` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). - utm_proxy_auth_profile - `frontend_cookie_secret` is now masked with no_log and no longer emitted in logging/output (CVE-2021-20191). Bugfixes -------- - Fix incorrect variable scoping when using ``import with context`` in Jinja2 templates. (ansible/ansible#72615) - ansible-test - Temporarily limit ``cryptography`` to versions before 3.4 to enable tests to function. - ansible-test - The ``--remote`` option has been updated for Python 2.7 to work around breaking changes in the newly released ``get-pip.py`` bootstrapper. - ansible-test - The ``--remote`` option has been updated to use a versioned ``get-pip.py`` bootstrapper to avoid issues with future releases. - display correct error information when an error exists in the last line of the file (ansible/ansible#16456) - facts - properly report virtualization facts for Linux guests running on bhyve (ansible/ansible#73167) - mysql_user - add ``INVOKE LAMBDA`` privilege support (ansible-collections/community.general#283). - mysql_user - add ``SHOW_ROUTINE`` privilege support (ansible-collections/community.mysql#86). - mysql_user - add missed privileges to support (ansible-collections/community.general#617). - pause - do not warn when running in the background if a timeout is provided (ansible/ansible#73042) - postgresql_info - fix crash caused by wrong PgSQL version parsing (ansible-collections/community.postgresql#40). - postgresql_ping - fix crash caused by wrong PgSQL version parsing (ansible-collections/community.postgresql#40). - postgresql_query - fix datetime.timedelta type handling (ansible-collections/community.postgresql#47). - postgresql_query - fix decimal handling (ansible-collections/community.postgresql#45). - postgresql_set - return a message instead of traceback when a passed parameter has not been found (ansible-collections/community.postgresql#41). - psrp connection plugin - ``to_text(stdout)`` before json.loads in psrp.Connection.put_file in case stdout is bytes. - win_find - Get-FileStat used [int] instead of [int64] for file size calculations v2.9.17 ======= Release Summary --------------- | Release Date: 2021-01-18 | `Porting Guide <https://docs.ansible.com/ansible/devel/porting_guides.html>`__ Minor Changes ------------- - ansible-test - Added a ``--export`` option to the ``ansible-test coverage combine`` command to facilitate multi-stage aggregation of coverage in CI pipelines. - ansible-test - added a ``--venv-system-site-packages`` option for use with the ``--venv`` option - ansible-test - virtualenv helper scripts now prefer ``venv`` on Python 3 over ``virtualenv`` if the ``ANSIBLE_TEST_PREFER_VENV`` environment variable is set - bigiq_device_info module - add information on BIG-IQ 7.x support Bugfixes -------- - Fix bytestring vs string comparison in module_utils.basic.is_special_selinux_path() so that special-cased filesystems which don't support SELinux context attributes still allow files to be manipulated on them. (ansible/ansible#70244) - Freeform actions did not work with ``ansible.builtin.`` or ``ansible.legacy.`` FQCN (ansible/ansible#72958). - async - Fix Python 3 interpreter parsing from module by comparing with bytes (ansible/ansible#70690) - bigiq_device_info module - fix iteration bug in a _transform_name_attribute method - docker_image - if ``push=true`` is used with ``repository``, and the image does not need to be tagged, still push. This can happen if ``repository`` and ``name`` are equal (ansible-collections/community.docker#52, ansible-collections/community.docker#53). - docker_image - report error when loading a broken archive that contains no image (ansible-collections/community.docker#46, ansible-collections/community.docker#55). - docker_image - report error when the loaded archive does not contain the specified image (ansible-collections/community.docker#41, ansible-collections/community.docker#55). - inventory - pass the vars dictionary to combine_vars instead of an individual key's value (ansible/ansible#72975). - k8s - add support for python-kubernetes v12 and later - backport of support in community.kubernetes - paramiko connection plugin - Ensure we only reset the connection when one has been previously established (ansible/ansible#65812) - systemd - preserve the full unit name when using a templated service and ``systemd`` failed to parse dbus due to a known bug in ``systemd`` (ansible/ansible#72985) - unsafe_proxy - Ensure that data within a tuple is marked as unsafe (ansible/ansible#65722) - user - do the right thing when ``password_lock=True`` and ``password`` are used together (ansible/ansible#72992) v2.9.16 ======= Release Summary --------------- | Release Date: 2020-12-14 | `Porting Guide <https://docs.ansible.com/ansible/devel/porting_guides.html>`__ Minor Changes ------------- - ansible-doc - provide ``has_action`` field in JSON output for modules. That information is currently only available in the text view (ansible/ansible#72359). - ansible-galaxy - find any collection dependencies in the globally configured Galaxy servers and not just the server the parent collection is from. - ansible-test - Added the ``-remote rhel/7.9`` option to run tests on RHEL 7.9 - ansible-test - Fix container hostname/IP discovery for the ``acme`` test plugin. - ansible-test - centos6 end of life - container image updated to point to vault base repository (ansible/distro-test-containers#54) - iptables - reorder comment postition to be at the end (ansible/ansible#71444). - lvol - fix idempotency issue when using lvol with ``%VG`` or ``%PVS`` size options and VG is fully allocated (ansible-collections/community.general#229). Bugfixes -------- - Adjust various hard-coded action names to also include their ``ansible.builtin.`` and ``ansible.legacy.`` prefixed version (ansible/ansible#71817, ansible/ansible#71818, ansible/ansible#71824). - Collection callbacks were ignoring options and rules for stdout and adhoc cases. - Fix virt module to support list_vms with a status of paused (ansible/ansible#72059) - Fixed issue when `netstat` is either missing or doesn't have execution permissions leading to incorrect command being executed. - Try to load action plugin from the same collection as the module (ansible/ansible#66701) - account for bug in Python 2.6 that occurs during interpreter shutdown to avoid stack trace - ansible-test - Correctly detect changes in a GitHub pull request when running on Azure Pipelines. - ansible-test - Skip installing requirements if they are already installed. - ansible-test - add constraint for ``cffi`` to prevent failure on systems with older versions of ``gcc`` (https://foss.heptapod.net/pypy/cffi/-/issues/480) - ansible-test - convert target paths to unicode on Python 2 to avoid ``UnicodeDecodeError`` (ansible/ansible#68398, ansible/ansible#72623). - ansible-test - improve classification of changes to ``.gitignore``, ``COPYING``, ``LICENSE``, ``Makefile``, and all files ending with one of ``.in`, ``.md`, ``.rst``, ``.toml``, ``.txt`` in the collection root directory (ansible/ansible#72353). - ansible-test now uses GNU tar format instead of the Python default when creating payloads for remote systems - azure_rm inventory plugin - update to fetch availability zone information of VM in hostvars. (ansible-collections/azure#161) - dnf - fix filtering to avoid dependncy conflicts (ansible/ansible#72316) - ec2_group - Fixes error handling during tagging failures (ansible-collections/amazon.aws#210). - ensure 'local' connection always has the correct default user for actions to consume. - network_cli - Update paramiko play_context when network_cli's play context is updated so that ssh parameters can be updated as well. - network_cli connection plugin - Perform privilege escalation before setting terminal. - pause - Fix indefinite hang when using a pause task on a background process (ansible/ansible#32142) - remove redundant remote_user setting in play_context for local as plugin already does it, also removes fork/thread issue from use of pwd library. - set_mode_if_different - handle symlink if it is inside a directory with sticky bit set (ansible/ansible#45198) - systemd - account for templated unit files using ``@`` when searching for the unit file (ansible/ansible#72347 (comment)) - systemd - follow up fix to ansible/ansible#72338 to use ``list-unit-files`` rather than ``list-units`` in order to show all units files on the system. - systemd - work around bug with ``systemd`` 245 and 5.8 kernel that does not correctly report service state (ansible/ansible#71528) - wait_for - catch and ignore errors when getting active connections with psutil (ansible/ansible#72322) v2.9.15 ======= Release Summary --------------- | Release Date: 2020-11-02 | `Porting Guide <https://docs.ansible.com/ansible/devel/porting_guides.html>`__ Minor Changes ------------- - ansible-test - Add a ``--docker-network`` option to choose the network for running containers when using the ``--docker`` option. - ansible-test - Collections can now specify pip constraints for unit and integration test requirements using ``tests/unit/constraints.txt`` and ``tests/integration/constraints.txt`` respectively. - dnf - now shows specific package changes (installations/removals) under ``results`` in check_mode. (ansible/ansible#66132) - module_defaults - add new module s3_metrics_configuration from community.aws to aws module_defaults group (ansible/ansible#72145). - vmware_guest_custom_attributes - Fixed issue when trying to set a VM custom attribute when there are custom attributes with the same name for other object types (ansible-collections/community.vmware#412). Breaking Changes / Porting Guide -------------------------------- - ansible-galaxy login command has been removed (see ansible/ansible#71560) Bugfixes -------- - Restore the ability for changed_when/failed_when to function with group_by. - ansible-test - Always connect additional Docker containers to the network used by the current container (if any). - ansible-test - Always map ``/var/run/docker.sock`` into test containers created by the ``--docker`` option if the docker host is not ``localhost``. - ansible-test - Attempt to detect the Docker hostname instead of assuming ``localhost``. - ansible-test - Correctly detect running in a Docker container on Azure Pipelines. - ansible-test - Prefer container IP at ``.NetworkSettings.Networks.{NetworkName}.IPAddress`` over ``.NetworkSettings.IPAddress``. - ansible-test - The ``cs`` and ``openshift`` test plugins now search for containers on the current network instead of assuming the ``bridge`` network. - ansible-test - Using the ``--remote`` option on Azure Pipelines now works from a job running in a container. - ansible-test - disable ansible-doc sanity test for vars plugins in collections, which are not supported by Ansible 2.9 (ansible/ansible#72336). - async_wrapper - Fix race condition when ``~/.ansible_async`` folder tries to be created by multiple async tasks at the same time - ansible/ansible#59306 - dnf - it is now possible to specify both ``security: true`` and ``bugfix: true`` to install updates of both types. Previously, only security would get installed if both were true. (ansible/ansible#70854) - facts - fix distribution fact for SLES4SAP (ansible/ansible#71559). - kubectl - follow up fix in _build_exec_cmd API (ansible/ansible#72171). - nmcli - typecast parameters to string as required (ansible/ansible#59095). - ovirt_disk - don't move disk when already in storage_domain (oVirt/ovirt-ansible-collection#135). - postgresql_pg_hba - fix a crash when a new rule with an 'options' field replaces a rule without or vice versa (ansible-collections/community.general#1108). - postgresql_privs - fix the module mistakes a procedure for a function (ansible-collections/community.general#994) - powershell - remove getting the PowerShell version from the env var ``POWERSHELL_VERSION``. This feature never worked properly and can cause conflicts with other libraries that use this var - user - AnsibleModule.run_command returns a tuple of return code, stdout and stderr. The module main function of the user module expects user.create_user to return a tuple of return code, stdout and stderr. Fix the locations where stdout and stderr got reversed. - user - Local users with an expiry date cannot be created as the ``luseradd`` / ``lusermod`` commands do not support the ``-e`` option. Set the expiry time in this case via ``lchage`` after the user was created / modified. (ansible/ansible#71942) - zfs - fixed ``invalid character '@' in pool name"`` error when working with snapshots on a root zvol (ansible-collections/community.general#932). v2.9.14 ======= Release Summary --------------- | Release Date: 2020-10-05 | `Porting Guide <https://docs.ansible.com/ansible/devel/porting_guides.html>`__ Minor Changes ------------- - ansible-test - Added CI provider support for Azure Pipelines. - ansible-test - Added support for Ansible Core CI request signing for Shippable. - ansible-test - Allow custom ``--remote-stage`` options for development and testing. - ansible-test - Fix ``ansible-test coverage`` reporting sub-commands (``report``, ``html``, ``xml``) on Python 2.6. - ansible-test - Refactored CI related logic into a basic provider abstraction. - ansible-test - Remove the discontinued ``us-east-2`` choice from the ``--remote-aws-region`` option. - ansible-test - Request remote resources by provider name for all provider types. - ansible-test - Show a warning when the obsolete ``--remote-aws-region`` option is used. - ansible-test - Support custom remote endpoints with the ``--remote-endpoint`` option. - ansible-test - Update built-in service endpoints for the ``--remote`` option. - ansible-test - Use new endpoint for Parallels based instances with the ``--remote`` option. - vmware_guest - Support HW version 15 / vSphere 6.7U2 (ansible-collections/community.vmware#99). Security Fixes -------------- - kubectl - connection plugin now redact kubectl_token and kubectl_password in console log (ansible-collections/community.kubernetes#65) (CVE-2020-1753). Bugfixes -------- - Handle write_files option in cgroup_perf_recap callback plugin (ansible/ansible#64936). - Prevent templating unused variables for {% include %} (ansible/ansible#68699) - Provide more information in AnsibleUndefinedVariable (ansible/ansible#55152) - ansible-doc - do not crash if plugin name cannot be found (ansible/ansible#71965). - ansible-doc - properly show plugin name when ``name:`` is used instead of ``<plugin_type>:`` (ansible/ansible#71965). - ansible-test - Change classification using ``--changed`` now consistently handles common configuration files for supported CI providers. - ansible-test - The ``resource_prefix`` variable provided to tests running on Azure Pipelines is now converted to lowercase to match other CI providers. - ansible-test - for local change detection, allow to specify branch to compare to with ``--base-branch`` for all types of tests (ansible/ansible#69508). - docker_login - now correctly reports changed status on logout for Docker versions released after June 2020. - docker_login - now obeys check_mode for logout - interfaces_file - escape regular expression characters in old value (ansible-collections/community.general#777). - ovirt_disk - fix upload when direct upload fails (oVirt/ovirt-ansible-collection#120). - postgres_user - remove false positive ``no_log`` warning for ``no_password_changes`` option (ansible/ansible#68106). - psrp - Fix hang when copying an empty file to the remote target - runas - create a new token when running as ``SYSTEM`` to ensure it has the full privileges assigned to that account
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hey, first off: thanks for this great collection of modules, I find them very useful!
SUMMARY
When trying to
SELECTa field of typeinterval(a PostgreSQL type describing time intervals), postgresql_query fails withThis
datetime.timedeltatype seems to come from psycopg2...I stumbled across this when querying
pg_stat_replication, which contains a few columns with type interval.The funny thing is: as long as e.g. replay_lag is null (meaning there is essentially no time difference between the production of WAL and replaying of the same on a replica) the query works fine. But as soon as there is an actual interval present, execution fails.
I hope this is quite an easy issue to solve, I think it could work when we simply return the datetime.timedelta as a String.
In the meantime, it is possible to work around this issue by casting to epoch or another date/time type in PostgreSQL.
Please let me know if or how I can be of further help when fixing this.
ISSUE TYPE
COMPONENT NAME
postgresql_query
ANSIBLE VERSION
CONFIGURATION
OS / ENVIRONMENT
Controller:
Hosts:
python3-psycopg2/focal,now 2.8.4-2 amd64psql (12.5 (Ubuntu 12.5-0ubuntu0.20.04.1))STEPS TO REPRODUCE
Here's a playbook which should be able to reproduce the issue. In my project, there are a few members to the database cluster, that's why all tasks are only run once.
The first task (and debug) run fine, but the execution fails on the second usage of postgresql_query.
Here's the same data retrieved by psycopg2:
EXPECTED RESULTS
ACTUAL RESULTS
Here's more verbose output of the failed task:
The text was updated successfully, but these errors were encountered: