From: Mark Stosberg <mark@summersault.com>
To: pgsql-admin@postgresql.org
Subject: Re: Re: best practice for moving millions of rows to child table when setting up partitioning?
Date: Wed, 04 May 2011 13:04:39 -0400
Message-ID: <ips0v8$8ut$1@dough.gmane.org> (raw)
In-Reply-To: <BANLkTinRFTF0ywHQua9ZgJ28sEqdKxNHXA@mail.gmail.com>
References: <ip9abr$nk8$1@dough.gmane.org>
<659395.83416.qm@web39708.mail.mud.yahoo.com>
<ips01d$351$1@dough.gmane.org>
<BANLkTinRFTF0ywHQua9ZgJ28sEqdKxNHXA@mail.gmail.com>
On 05/04/2011 12:54 PM, Scott Marlowe wrote:
> On Wed, May 4, 2011 at 10:48 AM, Mark Stosberg <mark@summersault.com> wrote:
>>
>>>> 5. Finally, I'll drop the indexes on the parent table and
>>>> truncate it.
>>
>> Luckily I noticed the problem with TRUNCATE and partitioning before my
>> work got to production.
>>
>> TRUNCATE cascades automatically and silently to child tables, which was
>> not my intent.
>>
>> This is mentioned here:
>> http://wiki.postgresql.org/wiki/Table_partitioning
>>
>> But is not mentioned in the official documentation for TRUNCATE:
>>
>> http://www.postgresql.org/docs/9.0/static/sql-truncate.html
>
> Surely it is. Quoting:
>
> "If ONLY is specified, only that table is truncated. If ONLY is not
> specified, the table and all its descendant tables (if any) are
> truncated. "
Thanks for the reference, Scott.
It is not as findable as it could be then. Besides scanning the page, I
also searched for "child", "parent" and "partition", and none of those
words are mentioned. Neither is "inherit". Pulling out "ONLY" to have
it's own "Parameter" sub-heading also help, instead of bundling that
documentation under the "name" sub-heading.
I suggest that at least one of the above search terms be added to better
relate the documentation to the partitioning documentation.
Further, since TRUNCATE permanently and instantly deletes mass amounts
of data, I would hope that it would provide "safety" by default, but
only truncating one table unless I specify otherwise.
Then again, it would be nice if an UPDATE with no WHERE clause did
little or nothing by default, instead of mangling an entire table, but
SQL doesn't seem to be designed with safe-by-default in mind.
Mark
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-admin@postgresql.org
Cc: mark@summersault.com
Subject: Re: Re: best practice for moving millions of rows to child table when setting up partitioning?
In-Reply-To: <ips0v8$8ut$1@dough.gmane.org>
* 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