Обсуждение: Partitionning: support for Truncate Table WHERE
<div dir="ltr" id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;font-family:Calibri,Arial,Helvetica,sans-serif;"><p>Hello,<p><br/><p>Working in a DSSenvironment, we often need to truncate table partitions regarding a WHERE condition and have to:<p>- query the dictionnaryto identify thoses partitions,<p>- build SQL statements,<p><span>- truncate all partitions covered by the WHEREcondition <br /></span><p><span>- eventually delete the rest ...</span><p>- perform additionnal maintenance tasks<br/><p><br /><p>Wouldn't it be possible to make it possible directly in SQL as a TRUNCATE TABLE WHERE syntax ?<br /><p>Ihave done something similar using a procedure in Oracle:<p>- identifying partitions using oracle rowids by a SELECT usingthe same WHERE conditions<p>- for each partition check if data NOT in WHERE condition<p>- then DELETE WHERE orTRUNCATE PARTITION depending of data distribution. <br /><p><br /><p><br /><p>Maybe there are some other constrainst likelocking, FK disabling/enabling, indexes rebuild ...<p><br /><p>Would be pleased to ear your feedback regarding this.<p><br/><p>Regards<p>PAscal<br /></div>
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
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
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;
;
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
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
> 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