Thread: Partitionning: support for Truncate Table WHERE

Partitionning: support for Truncate Table WHERE

From
legrand legrand
Date:
<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> 

Re: Partitionning: support for Truncate Table WHERE

From
Craig Ringer
Date:
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



Re: Partitionning: support for Truncate Table WHERE

From
Amit Langote
Date:
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





Re: Partitionning: support for Truncate Table WHERE

From
legrand legrand
Date:

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