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)
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