Re: ALTER TABLE ... REPLACE WITH

On Dec 15, 2010, at 4:39 AM, Simon Riggs wrote:

> On Wed, 2010-12-15 at 10:54 +0100, Csaba Nagy wrote:
>> On Tue, 2010-12-14 at 14:36 -0500, Robert Haas wrote:
>>>> Well, you have to do that for DROP TABLE as well, and I don't see any
>>>> way around doing it for REPLACE WITH.
>>>
>>> Sure, but in Simon's proposal you can load the data FIRST and then
>>> take a lock just long enough to do the swap. That's very different
>>> from needing to hold the lock during the whole data load.
>>
>> Except Simon's original proposal has this line in it:
>>
>> * "new_table" is TRUNCATEd.
>>
>> I guess Simon mixed up "new_table" and "old_table", and the one which
>> should get truncated is the replaced one and not the replacement,
>> otherwise it doesn't make sense to me.
>
> What I meant was...
>
> REPLACE TABLE target WITH source;
>
> * target's old rows are discarded
> * target's new rows are all of the rows from "source".
> * source is then truncated, so ends up empty
>
> Perhaps a more useful definition would be
>
> EXCHANGE TABLE target WITH source;
>
> which just swaps the heap and indexes of each table.
> You can then use TRUNCATE if you want to actually destroy data.

Are there any considerations with toast tables and the inline line pointers for toasted tuples?

Regards,

David
--
David Christensen
End Point Corporation
david(at)endpoint(dot)com