Imply --no-order for non-clustered tables

dvarrazzo · dvarrazzo · commit 8efbd9e1c63f · 2013-04-17T01:42:22.000+01:00
--no-order can still be specified to VACUUM FULL a clustered tables (not so useful I guess...) Fixes issue #6.
diff --git a/bin/expected/repack.out b/bin/expected/repack.out
@@ -114,7 +114,7 @@ SELECT * FROM tbl_with_dropped_toast;
 --
 -- do repack
 --
-\! pg_repack --dbname=contrib_regression --no-order
+\! pg_repack --dbname=contrib_regression --table=tbl_badindex
 WARNING: skipping invalid index: CREATE UNIQUE INDEX idx_badindex_n ON tbl_badindex USING btree (n)
 \! pg_repack --dbname=contrib_regression
 \! pg_repack --dbname=contrib_regression --table=tbl_cluster
@@ -301,17 +301,17 @@ CREATE TABLE tbl_nn_uk (col1 int NOT NULL, col2 int NOT NULL, UNIQUE(col1, col2)
 CREATE TABLE tbl_pk_uk (col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1, col2), UNIQUE(col2, col1));
 CREATE TABLE tbl_nn_puk (col1 int NOT NULL, col2 int NOT NULL);
 CREATE UNIQUE INDEX tbl_nn_puk_pcol1_idx ON tbl_nn_puk(col1) WHERE col1 < 10;
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_nn
+\! pg_repack --dbname=contrib_regression --table=tbl_nn
 WARNING: relation "tbl_nn" must have a primary key or not-null unique keys
 -- => WARNING
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_uk
+\! pg_repack --dbname=contrib_regression --table=tbl_uk
 WARNING: relation "tbl_uk" must have a primary key or not-null unique keys
 -- => WARNING
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_nn_uk
+\! pg_repack --dbname=contrib_regression --table=tbl_nn_uk
 -- => OK
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_pk_uk
+\! pg_repack --dbname=contrib_regression --table=tbl_pk_uk
 -- => OK
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_nn_puk
+\! pg_repack --dbname=contrib_regression --table=tbl_nn_puk
 WARNING: relation "tbl_nn_puk" must have a primary key or not-null unique keys
 -- => WARNING
 --
@@ -325,7 +325,7 @@ SELECT repack.get_order_by('issue3_1_idx'::regclass::oid, 'issue3_1'::regclass::
  col1, col2 DESC
 (1 row)
 
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_1
+\! pg_repack --dbname=contrib_regression --table=issue3_1
 CREATE TABLE issue3_2 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_2_idx ON issue3_2 (col1 DESC, col2 text_pattern_ops);
 SELECT repack.get_order_by('issue3_2_idx'::regclass::oid, 'issue3_2'::regclass::oid);
@@ -334,7 +334,7 @@ SELECT repack.get_order_by('issue3_2_idx'::regclass::oid, 'issue3_2'::regclass::
  col1 DESC, col2 USING ~<~
 (1 row)
 
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_2
+\! pg_repack --dbname=contrib_regression --table=issue3_2
 CREATE TABLE issue3_3 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_3_idx ON issue3_3 (col1 DESC, col2 DESC);
 SELECT repack.get_order_by('issue3_3_idx'::regclass::oid, 'issue3_3'::regclass::oid);
@@ -343,7 +343,7 @@ SELECT repack.get_order_by('issue3_3_idx'::regclass::oid, 'issue3_3'::regclass::
  col1 DESC, col2 DESC
 (1 row)
 
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_3
+\! pg_repack --dbname=contrib_regression --table=issue3_3
 CREATE TABLE issue3_4 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_4_idx ON issue3_4 (col1 NULLS FIRST, col2 text_pattern_ops DESC NULLS LAST);
 SELECT repack.get_order_by('issue3_4_idx'::regclass::oid, 'issue3_4'::regclass::oid);
@@ -352,7 +352,7 @@ SELECT repack.get_order_by('issue3_4_idx'::regclass::oid, 'issue3_4'::regclass::
  col1 NULLS FIRST, col2 DESC USING ~<~ NULLS LAST
 (1 row)
 
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_4
+\! pg_repack --dbname=contrib_regression --table=issue3_4
 CREATE TABLE issue3_5 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_5_idx ON issue3_5 (col1 DESC NULLS FIRST, col2 COLLATE "POSIX" DESC);
 SELECT repack.get_order_by('issue3_5_idx'::regclass::oid, 'issue3_5'::regclass::oid);
@@ -361,4 +361,4 @@ SELECT repack.get_order_by('issue3_5_idx'::regclass::oid, 'issue3_5'::regclass::
  col1 DESC, col2 COLLATE "POSIX" DESC
 (1 row)
 
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_5
+\! pg_repack --dbname=contrib_regression --table=issue3_5
diff --git a/bin/pg_repack.c b/bin/pg_repack.c
@@ -521,27 +521,28 @@ repack_one_database(const char *orderby, char *errbuf, size_t errsize)
 		resetStringInfo(&sql);
 		if (!orderby)
 		{
-			/* CLUSTER mode */
-			if (ckey == NULL)
+			if (ckey != NULL)
 			{
-				ereport(WARNING,
-					(errcode(E_PG_COMMAND),
-					 errmsg("relation \"%s\" has no cluster key", table.target_name)));
-				continue;
+				/* CLUSTER mode */
+				appendStringInfo(&sql, "%s ORDER BY %s", create_table, ckey);
+				table.create_table = sql.data;
+			}
+			else
+			{
+				/* VACUUM FULL mode (non-clustered tables) */
+				table.create_table = create_table;
 			}
-			appendStringInfo(&sql, "%s ORDER BY %s", create_table, ckey);
-            table.create_table = sql.data;
 		}
 		else if (!orderby[0])
 		{
-			/* VACUUM FULL mode */
-            table.create_table = create_table;
+			/* VACUUM FULL mode (for clustered tables too) */
+			table.create_table = create_table;
 		}
 		else
 		{
 			/* User specified ORDER BY */
 			appendStringInfo(&sql, "%s ORDER BY %s", create_table, orderby);
-            table.create_table = sql.data;
+			table.create_table = sql.data;
 		}
 
 		table.sql_peek = getstr(res, i, c++);
diff --git a/bin/sql/repack.sql b/bin/sql/repack.sql
@@ -120,7 +120,7 @@ SELECT * FROM tbl_with_dropped_toast;
 -- do repack
 --
 
-\! pg_repack --dbname=contrib_regression --no-order
+\! pg_repack --dbname=contrib_regression --table=tbl_badindex
 \! pg_repack --dbname=contrib_regression
 \! pg_repack --dbname=contrib_regression --table=tbl_cluster
 
@@ -177,15 +177,15 @@ CREATE TABLE tbl_nn_uk (col1 int NOT NULL, col2 int NOT NULL, UNIQUE(col1, col2)
 CREATE TABLE tbl_pk_uk (col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1, col2), UNIQUE(col2, col1));
 CREATE TABLE tbl_nn_puk (col1 int NOT NULL, col2 int NOT NULL);
 CREATE UNIQUE INDEX tbl_nn_puk_pcol1_idx ON tbl_nn_puk(col1) WHERE col1 < 10;
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_nn
+\! pg_repack --dbname=contrib_regression --table=tbl_nn
 -- => WARNING
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_uk
+\! pg_repack --dbname=contrib_regression --table=tbl_uk
 -- => WARNING
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_nn_uk
+\! pg_repack --dbname=contrib_regression --table=tbl_nn_uk
 -- => OK
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_pk_uk
+\! pg_repack --dbname=contrib_regression --table=tbl_pk_uk
 -- => OK
-\! pg_repack --dbname=contrib_regression --no-order --table=tbl_nn_puk
+\! pg_repack --dbname=contrib_regression --table=tbl_nn_puk
 -- => WARNING
 
 --
@@ -194,24 +194,24 @@ CREATE UNIQUE INDEX tbl_nn_puk_pcol1_idx ON tbl_nn_puk(col1) WHERE col1 < 10;
 CREATE TABLE issue3_1 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_1_idx ON issue3_1 (col1, col2 DESC);
 SELECT repack.get_order_by('issue3_1_idx'::regclass::oid, 'issue3_1'::regclass::oid);
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_1
+\! pg_repack --dbname=contrib_regression --table=issue3_1
 
 CREATE TABLE issue3_2 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_2_idx ON issue3_2 (col1 DESC, col2 text_pattern_ops);
 SELECT repack.get_order_by('issue3_2_idx'::regclass::oid, 'issue3_2'::regclass::oid);
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_2
+\! pg_repack --dbname=contrib_regression --table=issue3_2
 
 CREATE TABLE issue3_3 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_3_idx ON issue3_3 (col1 DESC, col2 DESC);
 SELECT repack.get_order_by('issue3_3_idx'::regclass::oid, 'issue3_3'::regclass::oid);
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_3
+\! pg_repack --dbname=contrib_regression --table=issue3_3
 
 CREATE TABLE issue3_4 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_4_idx ON issue3_4 (col1 NULLS FIRST, col2 text_pattern_ops DESC NULLS LAST);
 SELECT repack.get_order_by('issue3_4_idx'::regclass::oid, 'issue3_4'::regclass::oid);
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_4
+\! pg_repack --dbname=contrib_regression --table=issue3_4
 
 CREATE TABLE issue3_5 (col1 int NOT NULL, col2 text NOT NULL);
 CREATE UNIQUE INDEX issue3_5_idx ON issue3_5 (col1 DESC NULLS FIRST, col2 COLLATE "POSIX" DESC);
 SELECT repack.get_order_by('issue3_5_idx'::regclass::oid, 'issue3_5'::regclass::oid);
-\! pg_repack --dbname=contrib_regression --no-order --table=issue3_5
+\! pg_repack --dbname=contrib_regression --table=issue3_5
diff --git a/doc/pg_repack.rst b/doc/pg_repack.rst
@@ -253,13 +253,13 @@ Environment
 Examples
 --------
 
-Execute the following command to perform an online CLUSTER of all tables in
-database ``test``::
+Perform an online CLUSTER of all the clustered tables in the database
+``test``, and perform an online VACUUM FULL of all the non-clustered tables::
 
     $ pg_repack test
 
-Execute the following command to perform an online VACUUM FULL of table
-``foo`` in database ``test``::
+Perform an online VACUUM FULL on the table ``foo`` in the database ``test``
+(an eventual cluster index is ignored)::
 
     $ pg_repack --no-order --table foo -d test
 
@@ -319,7 +319,7 @@ ERROR: relation "table" has no cluster key
     Define a CLUSTER KEY on the table, via ALTER TABLE CLUSTER ON, or use
     one of the --no-order or --order-by modes.
 
-pg_repack: query failed: ERROR: column "col" does not exist
+ERROR: query failed: ERROR: column "col" does not exist
     The target table doesn't have columns specified by ``--order-by`` option.
 
     Specify existing columns.
@@ -404,6 +404,8 @@ Releases
 * pg_repack 1.2
 
   * Added --jobs option for parallel operation.
+  * Don't require --no-order to perform a VACUUM FULL on non-clustered tables
+    (pg_repack issue #6).
   * More helpful error messages.
   * Bugfix: correctly handle key indexes with options such as DESC, NULL
     FIRST/LAST, COLLATE (pg_repack issue #3).

Original file line number	Diff line number	Diff line change
`@@ -521,27 +521,28 @@ repack_one_database(const char orderby, char errbuf, size_t errsize)`
`521`	`521`	`resetStringInfo(&sql);`
`522`	`522`	`if (!orderby)`
`523`	`523`	`{`
`524`		`- /* CLUSTER mode */`
`525`		`- if (ckey == NULL)`
	`524`	`+ if (ckey != NULL)`
`526`	`525`	`{`
`527`		`- ereport(WARNING,`
`528`		`- (errcode(E_PG_COMMAND),`
`529`		`- errmsg("relation \"%s\" has no cluster key", table.target_name)));`
`530`		`- continue;`
	`526`	`+ /* CLUSTER mode */`
	`527`	`+ appendStringInfo(&sql, "%s ORDER BY %s", create_table, ckey);`
	`528`	`+ table.create_table = sql.data;`
	`529`	`+ }`
	`530`	`+ else`
	`531`	`+ {`
	`532`	`+ /* VACUUM FULL mode (non-clustered tables) */`
	`533`	`+ table.create_table = create_table;`
`531`	`534`	`}`
`532`		`- appendStringInfo(&sql, "%s ORDER BY %s", create_table, ckey);`
`533`		`- table.create_table = sql.data;`
`534`	`535`	`}`
`535`	`536`	`else if (!orderby[0])`
`536`	`537`	`{`
`537`		`- /* VACUUM FULL mode */`
`538`		`- table.create_table = create_table;`
	`538`	`+ /* VACUUM FULL mode (for clustered tables too) */`
	`539`	`+ table.create_table = create_table;`
`539`	`540`	`}`
`540`	`541`	`else`
`541`	`542`	`{`
`542`	`543`	`/* User specified ORDER BY */`
`543`	`544`	`appendStringInfo(&sql, "%s ORDER BY %s", create_table, orderby);`
`544`		`- table.create_table = sql.data;`
	`545`	`+ table.create_table = sql.data;`
`545`	`546`	`}`
`546`	`547`
`547`	`548`	`table.sql_peek = getstr(res, i, c++);`