RAISE on zbx_drop_old_partitions

[melodous]

Hi Ryan,

First of all sorry if I'm bothering you and thanks for share these scripts :)

I have a problem with them and maybe you can give me a hand with a doubt. They work really well, but It fail in my environment dropping the old tables. This is an example of the execution, adding some debugging output.

zabbix-server=> SELECT zbx_drop_old_partitions('history_log', '2017-07-15'::TIMESTAMP);
NOTICE:  Current now: 2017-07-19 20:36:04.070907+02 - childbond: 2017-07-15 00:00:00 - cutoff: 2017-07-15 00:00:00+02 - relname history_log_2017_07_14
NOTICE:  Dropped partition: partitions.history_log_2017_07_14
NOTICE:  Current now: 2017-07-19 20:36:04.070907+02 - childbond: 2017-07-16 00:00:00 - cutoff: 2017-07-15 00:00:00+02 - relname history_log_2017_07_15
NOTICE:  Current now: 2017-07-19 20:36:04.070907+02 - childbond: 2017-07-17 00:00:00 - cutoff: 2017-07-15 00:00:00+02 - relname history_log_2017_07_16
NOTICE:  Current now: 2017-07-19 20:36:04.070907+02 - childbond: 2017-07-18 00:00:00 - cutoff: 2017-07-15 00:00:00+02 - relname history_log_2017_07_17
NOTICE:  Current now: 2017-07-19 20:36:04.070907+02 - childbond: 2017-07-19 00:00:00 - cutoff: 2017-07-15 00:00:00+02 - relname history_log_2017_07_18
NOTICE:  Current now: 2017-07-19 20:36:04.070907+02 - childbond: 2017-07-20 00:00:00 - cutoff: 2017-07-15 00:00:00+02 - relname history_log_2017_07_19
ERROR:  Current timestamp is earlier than upper bound for partition partitions.history_log_2017_07_19
CONTEXT:  función PL/pgSQL zbx_drop_old_partitions(text,timestamp with time zone,text) en la línea 34 en RAISE

It work correctly but when it go through the last table and execute the code to protect the current partition, It raise an error and I think that make a rollback, so doesn't drop any table. If I drop this part of code it works, but I’m really asking my self why this code doesn’t works in my environment but it does for yours.

-- protect current partition
IF NOW() < child_bound THEN
      RAISE 'Current timestamp is earlier than upper bound for partition %.%', child.child_nspname, child.child_relname;
END IF;

Thanks in advance.

[alexmirtoff]

Change RAISE to RAISE NOTICE.

[cavaliercoder]

Can you please try the fix in #3 in non-production? I don't have access to a test environment at the moment.

[ComBin]

@cavaliercoder, hello! Why this fix is still no in master?

[cavaliercoder]

No-one has validated the fix in #3. I no longer have the means to test changes to this package and rely on feedback from users.

[ComBin]

I tested it. #3 is fix this issue. The old parts are deleted. But i did not test case when current partitions fell into the specified range.