Skip to content

Instantly share code, notes, and snippets.

@xvaara
Created October 12, 2022 09:22
Show Gist options
  • Save xvaara/81990e8291019f931387492c1869fe84 to your computer and use it in GitHub Desktop.
Save xvaara/81990e8291019f931387492c1869fe84 to your computer and use it in GitHub Desktop.
timescaledb delete from compressed hypertable with segmentby
CREATE OR REPLACE FUNCTION delete_segmentby(_tbl regclass, _col varchar, _id anyelement, OUT result integer)
LANGUAGE plpgsql AS
$$
DECLARE
_schema varchar := (SELECT nspname
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS ns
ON c.relnamespace = ns.oid
WHERE c.oid = _tbl);
_table_name varchar := (
SELECT relname
FROM pg_catalog.pg_class
WHERE oid = _tbl);
_htc varchar := (
select '_timescaledb_internal.' || table_name
from _timescaledb_catalog.hypertable
where id = (
select compressed_hypertable_id from _timescaledb_catalog.hypertable where schema_name = _schema and table_name = _table_name)
);
count int;
_hypertable_id int;
_r record;
BEGIN
raise notice 'table: %', _tbl;
raise notice 'table_name: %', _table_name;
raise notice 'schema: %', _schema;
raise notice 'compressed hypertable: %', _htc;
EXECUTE format('delete FROM %s WHERE %s = %s', _htc, quote_ident(_col), quote_literal(_id));
GET DIAGNOSTICS result = ROW_COUNT;
raise notice 'deleted on compressed %: %', _htc, result;
select id into _hypertable_id from _timescaledb_catalog.hypertable where schema_name = _schema and table_name = _table_name;
FOR _r IN select * from _timescaledb_catalog.chunk where hypertable_id = _hypertable_id and compressed_chunk_id is null LOOP
EXECUTE format('delete FROM ONLY %s WHERE %s = %s', _r.schema_name || '.' || _r.table_name, quote_ident(_col), quote_literal(_id));
GET DIAGNOSTICS count = ROW_COUNT;
raise notice 'delete on chunk: % chunk: %', count, to_jsonb(_r);
result := result + count;
end loop;
END
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment