Re: Nondestructive cluster, equivalent SQL?

"Joshua b. Jore" <josh(at)greentechnologist(dot)org> writes:
> So wouldn't it work just as well and avoid the problems by executing:

> CREATE TABLE cluster_SoSIDs AS SELECT * FROM SoSIDs ORDER BY SoSID;
> TRUNCATE SoSIDs;
> INSERT INTO SoSIDs SELECT * FROM cluster_SoSIDs ORDER BY SoSID;

<<itch>> ... if we allow that, we probably shouldn't. Since TRUNCATE
can't be rolled back, it's not supposed to be allowed inside a
transaction block.

Think about what happens if you get an error or a system crash while
that INSERT is running. The INSERT rolls back; the CREATE TABLE
rolls back; the TRUNCATE does not. You just lost all your data.

If you can assume no one else is modifying the table then you could
defend against this by creating the holding-tank table in a separate
transaction before you do the TRUNCATE/INSERT. Then you'd still have
your data in event of a crash, though you'd probably need a manual
recovery procedure to move it back where you want it. But it's not
much of a general-purpose solution I'm afraid.

regards, tom lane