From: Craig Ringer <ringerc@ringerc.id.au>
To: Stanislaw Pankevich <s.pankevich@gmail.com>
Cc: Daniel Farina <daniel@heroku.com>
Cc: 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.
Date: Fri, 06 Jul 2012 22:22:21 +0800
Message-ID: <4FF6F49D.7050507@ringerc.id.au> (raw)
In-Reply-To: <CAFXpGYZx_XFJubpJ9CmitLuURWad3tqeJ8dzQ+SJCDQrB1XpoQ@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ringerc@ringerc.id.au, s.pankevich@gmail.com, daniel@heroku.com
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.
In-Reply-To: <4FF6F49D.7050507@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox