Bug 1704425
| Summary: | spacewalk-manage-snapshots fails: function to_timestamp does not exist | ||
|---|---|---|---|
| Product: | [Community] Spacewalk | Reporter: | jacek.kowalski |
| Component: | Server | Assignee: | Michael Mráka <mmraka> |
| Status: | CLOSED CURRENTRELEASE | QA Contact: | Red Hat Satellite QA List <satqe-list> |
| Severity: | medium | Docs Contact: | |
| Priority: | unspecified | ||
| Version: | 2.9 | CC: | dan.holstad, dan-holstad |
| Target Milestone: | --- | ||
| Target Release: | --- | ||
| Hardware: | x86_64 | ||
| OS: | Linux | ||
| Whiteboard: | |||
| Fixed In Version: | spacewalk-utils-2.10.10-1 | Doc Type: | If docs needed, set a value |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2020-03-19 12:16:46 UTC | Type: | Bug |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
| Bug Depends On: | |||
| Bug Blocks: | 1802137 | ||
I found that casting the timestamp does seem to allow the script to run.
diff /bin/spacewalk-manage-snapshots /bin/spacewalk-manage-snapshots_old
104,105c104,105
< 'PURGE_DATA': "delete from rhnsnapshot ss where ss.id = any (array(select id from rhnsnapshot where created < CAST(current_timestamp as timestamp) - numtodsinterval(:num_days, 'day') limit :batchsize))",
< 'PURGE_COUNT': "select count(ss.id) from rhnsnapshot ss where ss.created < CAST(current_timestamp as timestamp) - numtodsinterval(:num_days, 'day')",
---
> 'PURGE_DATA': "delete from rhnsnapshot ss where ss.id = any (array(select id from rhnsnapshot where created < to_timestamp(:current_timestamp, 'YYYY-MM-DD HH24:MI:SS:MS') - numtodsinterval(:num_days, 'day') limit :batchsize))",
> 'PURGE_COUNT': "select count(ss.id) from rhnsnapshot ss where ss.created < to_timestamp(:current_timestamp, 'YYYY-MM-DD HH24:MI:SS:MS') - numtodsinterval(:num_days, 'day')",
~]# spacewalk-manage-snapshots --delete-older-than=60
Deleting snapshots older than 60 days
616 snapshots currently
244 snapshots to be deleted, 1000 per commit
... 244 snapshots left to purge
372 snapshots remain
# spacewalk-manage-snapshots --delete-older-than=60
Deleting snapshots older than 60 days
372 snapshots currently
0 snapshots to be deleted, 1000 per commit
372 snapshots remain
Fixed in spacewalk git by
commit c1034dac3327a4333f085ec5bff30650a9ac6545
1704425 - fixed postgresql timestamp handling
Spacewalk 2.10 has been released. https://github.com/spacewalkproject/spacewalk/wiki/ReleaseNotes210 |
Description of problem: Running "spacewalk-manage-snapshots -d 90" fails with error: function to_timestamp(timestamp with time zone, unknown) does not exist Version-Release number of selected component (if applicable): spacewalk-utils-0:2.9.9-1.el7.noarch How reproducible: always Steps to Reproduce: 1. Install spacewalk 2. Run spacewalk-manage-snapshots -d 90 Actual results: Deleting snapshots older than 90 days 132395 snapshots currently Traceback (most recent call last): File "/usr/bin/spacewalk-manage-snapshots", line 273, in <module> purge(options) File "/usr/bin/spacewalk-manage-snapshots", line 235, in purge to_be_purged = purge_count(name, opt, now_ts) File "/usr/bin/spacewalk-manage-snapshots", line 181, in purge_count stmt = rhnSQL.execute(tbl_str, current_timestamp=since, num_days=opt.num_days ) File "/usr/lib/python2.7/site-packages/spacewalk/server/rhnSQL/__init__.py", line 282, in execute return db.execute(sql, *args, **kwargs) File "/usr/lib/python2.7/site-packages/spacewalk/server/rhnSQL/driver_postgresql.py", line 227, in execute cursor.execute(*args, **kwargs) File "/usr/lib/python2.7/site-packages/spacewalk/server/rhnSQL/sql_base.py", line 151, in execute return self._execute_wrapper(self._execute, *p, **kw) File "/usr/lib/python2.7/site-packages/spacewalk/server/rhnSQL/driver_postgresql.py", line 305, in _execute_wrapper raise sql_base.SQLStatementPrepareError(self.dbh, e.pgerror, self.sql) spacewalk.server.rhnSQL.sql_base.SQLStatementPrepareError: ('ERROR: function to_timestamp(timestamp with time zone, unknown) does not exist\nLINE 1: ...unt(ss.id) from rhnsnapshot ss where ss.created < to_timesta...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.\n', <connection object at 0x7f859c924910; dsn: 'password=xxxxx dbname=rhnschema user=rhnuser', closed: 0>, "select count(ss.id) from rhnsnapshot ss where ss.created < to_timestamp(%(current_timestamp)s, 'YYYY-MM-DD HH24:MI:SS:MS') - numtodsinterval(%(num_days)s, 'day')") Expected results: Deleting snapshots older than 90 days 132401 snapshots currently 122118 snapshots to be deleted, 1000 per commit ... 122118 snapshots left to purge ... 121118 snapshots left to purge ... Additional info: Spacewalk 2.9 on CentOS 7 (spacewalk-utils-0:2.9.9-1.el7.noarch), PostgreSQL 9.2.24 (postgresql-0:9.2.24-1.el7_5.x86_64)