Re: Partitionning: support for Truncate Table WHERE
| От | legrand legrand |
|---|---|
| Тема | Re: Partitionning: support for Truncate Table WHERE |
| Дата | |
| Msg-id | AM4PR03MB171327323DCD2069A532756190850@AM4PR03MB1713.eurprd03.prod.outlook.com обсуждение исходный текст |
| Ответ на | Re: Partitionning: support for Truncate Table WHERE (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| Список | 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;
;
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
В списке pgsql-hackers по дате отправления: