Partitionning: support for Truncate Table WHERE
Hello,
Working in a DSS environment, we often need to truncate table partitions regarding a WHERE condition and have to:
- query the dictionnary to identify thoses partitions,
- build SQL statements,
- truncate all partitions covered by the WHERE condition
- eventually delete the rest ...
- perform additionnal maintenance tasks
Wouldn't it be possible to make it possible directly in SQL as a TRUNCATE TABLE WHERE syntax ?
I have done something similar using a procedure in Oracle:
- identifying partitions using oracle rowids by a SELECT using the same WHERE conditions
- for each partition check if data NOT in WHERE condition
- then DELETE WHERE or TRUNCATE PARTITION depending of data distribution.
Maybe there are some other constrainst like locking, FK disabling/enabling, indexes rebuild ...
Would be pleased to ear your feedback regarding this.
Regards
PAscal
On 7 December 2016 at 07:29, legrand legrand
<legrand_legrand@hotmail.com> wrote:
Working in a DSS environment, we often need to truncate table partitions
regarding a WHERE condition and have to
[...]
Would be pleased to ear your feedback regarding this.
It sounds like something that'd be useful to do on top of declarative
partitioning, once that is merged. Perhaps you could start by reading
and testing the declarative partitioning patch. That'll give you a
better idea of the practicalities of doing what you propose on top of
it, and give you an opportunity to suggest changes to the declarative
partitioning scheme that might make conditional truncate easier later.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2016/12/07 15:26, Craig Ringer wrote:
On 7 December 2016 at 07:29, legrand legrand
<legrand_legrand@hotmail.com> wrote:Working in a DSS environment, we often need to truncate table partitions
regarding a WHERE condition and have to
[...]
Would be pleased to ear your feedback regarding this.It sounds like something that'd be useful to do on top of declarative
partitioning, once that is merged. Perhaps you could start by reading
and testing the declarative partitioning patch. That'll give you a
better idea of the practicalities of doing what you propose on top of
it, and give you an opportunity to suggest changes to the declarative
partitioning scheme that might make conditional truncate easier later.
Agreed.
If I understand the request correctly, TRUNCATE on the parent table (a
partitioned table), which currently recurses to *all* child tables
(partitions), should have a restricting WHERE clause, right? It would
become possible to implement something like that with the new declarative
partitioned tables. As Crag mentioned, you can take a look at the
discussion about declarative partitioning in the emails linked to at the
following page: https://commitfest.postgresql.org/12/611/
Thanks,
Amit
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
here is an exemple :
CREATE OR REPLACE FUNCTION truncate_table_where(v_table VARCHAR, v_where_condition VARCHAR)
RETURNS void AS $$
DECLARE
v_stmt varchar;
v_tableoid oid;
v_part varchar;
v_found_other integer;
BEGIN
LOOP
v_stmt := 'SELECT tableoid FROM '|| v_table||' WHERE '||v_where_condition||' limit 1 ';
EXECUTE v_stmt INTO v_tableoid;
IF (v_tableoid is null) THEN
EXIT;
END IF;
Select pg_namespace.nspname||'.'||pg_class.relname into v_part from pg_catalog.pg_class
INNER JOIN pg_namespace
ON pg_class.relnamespace = pg_namespace.oid where pg_class.oid = v_tableoid;
RAISE NOTICE 'Partition found: %', v_part;
-- check if other data in part
v_stmt := 'SELECT 1 FROM '|| v_part||' WHERE NOT ('||v_where_condition||') limit 1 ';
EXECUTE v_stmt INTO v_found_other;
IF (v_found_other =1) THEN
v_stmt := 'DELETE FROM '|| v_part||' WHERE '||v_where_condition;
RAISE NOTICE 'Executing: %', v_stmt;
EXECUTE v_stmt;
ELSE
v_stmt := 'TRUNCATE '|| v_part;
RAISE NOTICE 'Executing: %', v_stmt;
EXECUTE v_stmt;
END IF;
END LOOP;
END
$$ LANGUAGE plpgsql;
;
________________________________
De : Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Envoyé : mercredi 7 décembre 2016 06:58:03
À : Craig Ringer; legrand legrand
Cc : pgsql-hackers@postgresql.org
Objet : Re: [HACKERS] Partitionning: support for Truncate Table WHERE
On 2016/12/07 15:26, Craig Ringer wrote:
On 7 December 2016 at 07:29, legrand legrand
<legrand_legrand@hotmail.com> wrote:Working in a DSS environment, we often need to truncate table partitions
regarding a WHERE condition and have to
[...]
Would be pleased to ear your feedback regarding this.It sounds like something that'd be useful to do on top of declarative
partitioning, once that is merged. Perhaps you could start by reading
and testing the declarative partitioning patch. That'll give you a
better idea of the practicalities of doing what you propose on top of
it, and give you an opportunity to suggest changes to the declarative
partitioning scheme that might make conditional truncate easier later.
Agreed.
If I understand the request correctly, TRUNCATE on the parent table (a
partitioned table), which currently recurses to *all* child tables
(partitions), should have a restricting WHERE clause, right? It would
become possible to implement something like that with the new declarative
partitioned tables. As Crag mentioned, you can take a look at the
discussion about declarative partitioning in the emails linked to at the
following page: https://commitfest.postgresql.org/12/611/
Thanks,
Amit