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