Thread: Nondestructive cluster, equivalent SQL?

Since the cluster command causes so many hassles with PL/pgSQL functions,
triggers, constraints, indexes etc I'm asking if this spot of SQL emulates
the expected behaviour:

CREATE TABLE SoSIDs (
    SoSID CHARACTER(10),

    CONSTRAINT SoSIDsPKey PRIMARY KEY (SoSID)
);
CREATE RULE SoSIDsUpd AS ON UPDATE TO SoSIDs
    DO INSTEAD NOTHING;

Other PL/pgSQL functions and tables use this table for triggers, foreign
keys and triggers. Running 'CLUSTER SoSIDsPkey ON SoSIDs' is equivalent to

CREATE TABLE cluster_SoSIDs AS SELECT * FROM SoSIDs ORDER BY SoSID;
DROP TABLE SoSIDs;
ALTER TABLE cluster_SoSIDs RENAME TO SoSIDs;

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;

I'm just looking to hear a yay/nay on whether this is a good idea or not
and if I've got the idea down right. Also, since I'd like to put this
into a PL/pgSQL function how do I get the 'create table ... as ...' part
to work? PL/pgSQL appears to reserve the 'create table ... as select ...'
and 'select * into ... from ...' syntax. I'm not sure how else to
generate the SQL code to do this. Ideas?

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

"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

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
>

"Joshua b. Jore" <josh@greentechnologist.org> writes:
> The sql-truncate.html documentation page doesn't say that truncate isn't
> transaction safe.

?  I see

: TRUNCATE cannot be executed inside a transaction block (BEGIN/COMMIT
: pair), because there is no way to roll it back.

This could perhaps be more verbose, but the point is covered...

            regards, tom lane