This is my stored procedure:
CREATE OR REPLACE PROCEDURE delete_events(job_id int, config jsonb)
LANGUAGE plpgsql
AS
$$
DECLARE
l_rec record;
policy_id integer;
deactivate_compression_policy_sql text;
decompress_sql text;
delete_sql text;
activate_compression_policy_sql text;
run_compression_sql text;
BEGIN
FOR l_rec IN SELECT table_schema, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_NAME = 'log_win'
AND table_schema LIKE '%org_%'
LOOP
policy_id := format('SELECT job_id
FROM timescaledb_information.jobs
WHERE proc_name = ''policy_retention''
AND hypertable_schema = ''%I''
AND hypertable_name = ''log_win''', l_rec.table_schema);
EXECUTE policy_id;
deactivate_compression_policy_sql := format('SELECT alter_job(%I, scheduled => false)', policy_id);
EXECUTE deactivate_compression_policy_sql;
decompress_sql := format('SELECT decompress_chunk(c, true) FROM show_chunks(''%I.log_win'') c', l_rec.table_schema);
EXECUTE decompress_sql;
delete_sql := format('WITH summary AS (
SELECT time, device_id, ROW_NUMBER() OVER (PARTITION BY device_id
ORDER BY time DESC) AS rank
FROM %I.log_win
JOIN %I.device USING (device_id)
)
DELETE FROM %I.log_win
USING summary
WHERE summary.rank = 2000 AND log_win.time < summary.time AND summary.device_id = log_win.device_id', l_rec.table_schema, l_rec.table_schema, l_rec.table_schema);
EXECUTE delete_sql;
activate_compression_policy_sql := format('SELECT alter_job(%I, scheduled => true)', policy_id);
EXECUTE activate_compression_policy_sql;
run_compression_sql := format('CALL run_job(%I)', policy_id);
EXECUTE run_compression_sql;
END LOOP;
END
$$;
The issue I have is with the policy_id
. The policy id is the id for the tables compression policy. I'm trying to decompress the table so that I can trim the data before compressing it back again. When I run this I get the following error:
ERROR: invalid input syntax for type integer: "SELECT job_id
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
AND hypertable_schema = 'org_3326'
AND hypertable_name = 'log_win'"
CONTEXT: PL/pgSQL function delete_events(integer,jsonb) line 17 at assignment
SQL state: 22P02
When I run SELECT job_id FROM timescaledb_information.jobs WHERE proc_name = 'policy_retention' AND hypertable_schema = 'org_3326' AND hypertable_name = 'log_win'
, I get 1092
and it even says that the value is an integer
. I don't understand what the issue is here?
format()
does not execute an SQL statement, it just constructs a string. You cannot assign a string to an integer
variable (unless it can be cast to a number). To execute the query you have to use EXECUTE
.
So the proper sequence would be:
EXECUTE format('SELECT job_id
FROM timescaledb_information.jobs
WHERE proc_name = ''policy_retention''
AND hypertable_schema = %L
AND hypertable_name = ''log_win''', l_rec.table_schema)
INTO policy_id;
Note that you have to use the format %L
to construct a string literal. Your code was vulnerable to SQL injection.