Re: [HACKERS] Partitionning: support for Truncate Table WHERE
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...@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_legr...@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: [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 >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
Re: [HACKERS] Partitionning: support for Truncate Table WHERE
On 7 December 2016 at 07:29, legrand legrandwrote: > 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
[HACKERS] 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