Message-ID: <4FF6F49D.7050507@ringerc.id.au>
Date: Fri, 06 Jul 2012 22:22:21 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Stanislaw Pankevich <s.pankevich@gmail.com>
CC: Daniel Farina <daniel@heroku.com>, pgsql-performance@postgresql.org
Subject: Re: PostgreSQL db, 30 tables with number of rows < 100
	(not huge) - the fastest way to clean each non-empty table and reset
	unique identifier column of empty ones.
References: 
 <CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com>
	<4FF6CC11.2000904@ringerc.id.au>
	<CAAZKuFbpYyfRA=+DKD4yCr_c_Tag2JNO32B8NVU15gg4xX3u_Q@mail.gmail.com>
	<4FF6EA64.9010308@ringerc.id.au>
	<CAFXpGYZx_XFJubpJ9CmitLuURWad3tqeJ8dzQ+SJCDQrB1XpoQ@mail.gmail.com>
In-Reply-To: 
 <CAFXpGYZx_XFJubpJ9CmitLuURWad3tqeJ8dzQ+SJCDQrB1XpoQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 07/06/2012 09:45 PM, Stanislaw Pankevich wrote:

> Question: Is there a possibility in PostgreSQL to do DELETE on many 
> tables massively, like TRUNCATE allows. Like DELETE table1, table2, ...? 

Yes, you can do it with a writable common table expression, but you 
wanted version portability.

WITH
   discard1 AS (DELETE FROM test1),
   discard2 AS (DELETE FROM test2 AS b)
SELECT 1;

Not only will this not work in older versions (IIRC it only works with 
9.1, maybe 9.0 too but I don't see it in the documentation for SELECT 
for 9.0) but I find it hard to imagine any performance benefit over 
simply sending

   DELETE FROM test1; DELETE FROM test2;

This all smells like premature optimisation of cases that don't matter. 
What problem are you solving with this?

--
Craig Ringer