Re: Nondestructive cluster, equivalent SQL?

Foo, ok so I'll just stick that code outside of PostgreSQL, do the
hold-tank thing and have the code call home to mom if things go really
badly (and hope to not fault during recovery).

The sql-truncate.html documentation page doesn't say that truncate isn't
transaction safe. I notice that when I actually try the code it does
complain about being inside a begin/end block. This occurs from simple SQL
at the psql client, inside a PL/pgSQL function, and inside an EXECUTE
statement. So while the bases appear to be covered in actual code, the
docs just didn't reflect that.

Thanks much,
Joshua b. Jore ; http://www.greentechnologist.org ; 10012 11010 11022
10202 1012 2122 11020 10202 10202 11002 1020 1012 11102 11102 11102 1201
11001 11002 10211 11020 10202 10202 11002 11021 1201 11010 11020 10211

On Fri, 24 May 2002, Tom Lane wrote:

> "Joshua b. Jore" <josh@greentechnologist.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
>