Skip to content

Commit 78b0a0e

Browse files
ItGackyItGacky
committed
Fix two bugs.
#1010789 : pg_reorg 1.1.0 and "unexpected toast relations" #1010790 : reorg.get_index_keys() does not handle composite indexes
1 parent f3873ff commit 78b0a0e

File tree

9 files changed

+126
-20
lines changed

9 files changed

+126
-20
lines changed

‎bin/expected/reorg.out

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,13 @@ ALTER INDEX tbl_with_dropped_column_pkey SET (fillfactor = 75);
3939
ALTER TABLE tbl_with_dropped_column CLUSTER ON tbl_with_dropped_column_pkey;
4040
CREATE INDEX idx_c1c2 ON tbl_with_dropped_column (c1, c2) WITH (fillfactor = 75);
4141
CREATE INDEX idx_c2c1 ON tbl_with_dropped_column (c2, c1);
42+
CREATE TABLE tbl_with_dropped_toast (
43+
i integer,
44+
j integer,
45+
t text,
46+
PRIMARY KEY (i, j)
47+
);
48+
ALTER TABLE tbl_with_dropped_toast CLUSTER ON tbl_with_dropped_toast_pkey;
4249
--
4350
-- insert data
4451
--
@@ -59,6 +66,9 @@ ALTER TABLE tbl_with_dropped_column DROP COLUMN d1;
5966
ALTER TABLE tbl_with_dropped_column DROP COLUMN d2;
6067
ALTER TABLE tbl_with_dropped_column DROP COLUMN d3;
6168
ALTER TABLE tbl_with_dropped_column ADD COLUMN c3 text;
69+
INSERT INTO tbl_with_dropped_toast VALUES(1, 10, 'abc');
70+
INSERT INTO tbl_with_dropped_toast VALUES(2, 20, sqrt(2::numeric(1000,999))::text || sqrt(3::numeric(1000,999))::text);
71+
ALTER TABLE tbl_with_dropped_toast DROP COLUMN t;
6272
--
6373
-- before
6474
--
@@ -69,6 +79,13 @@ SELECT * FROM tbl_with_dropped_column;
6979
c1 | 1 | c2 |
7080
(2 rows)
7181

82+
SELECT * FROM tbl_with_dropped_toast;
83+
i | j
84+
---+----
85+
1 | 10
86+
2 | 20
87+
(2 rows)
88+
7289
--
7390
-- do reorg
7491
--
@@ -131,6 +148,15 @@ Indexes:
131148
"idx_c1c2" btree (c1, c2) WITH (fillfactor=75)
132149
"idx_c2c1" btree (c2, c1)
133150

151+
\d tbl_with_dropped_toast
152+
Table "public.tbl_with_dropped_toast"
153+
Column | Type | Modifiers
154+
--------+---------+-----------
155+
i | integer | not null
156+
j | integer | not null
157+
Indexes:
158+
"tbl_with_dropped_toast_pkey" PRIMARY KEY, btree (i, j) CLUSTER
159+
134160
SELECT col1, to_char(col2, 'YYYY-MM-DD HH24:MI:SS'), ","")" FROM tbl_cluster;
135161
col1 | to_char | ,")
136162
------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
@@ -169,6 +195,33 @@ SELECT * FROM tbl_with_dropped_column;
169195
c1 | 2 | c2 |
170196
(2 rows)
171197

198+
SELECT * FROM tbl_with_dropped_toast;
199+
i | j
200+
---+----
201+
1 | 10
202+
2 | 20
203+
(2 rows)
204+
205+
--
206+
-- check broken links or orphan toast relations
207+
--
208+
SELECT oid, relname
209+
FROM pg_class
210+
WHERE relkind = 't'
211+
AND oid NOT IN (SELECT reltoastrelid FROM pg_class WHERE relkind = 'r');
212+
oid | relname
213+
-----+---------
214+
(0 rows)
215+
216+
SELECT oid, relname
217+
FROM pg_class
218+
WHERE relkind = 'r'
219+
AND reltoastrelid <> 0
220+
AND reltoastrelid NOT IN (SELECT oid FROM pg_class WHERE relkind = 't');
221+
oid | relname
222+
-----+---------
223+
(0 rows)
224+
172225
--
173226
-- clean up
174227
--

‎bin/pg_reorg.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* @brief Client Modules
99
*/
1010

11-
const char *PROGRAM_VERSION = "1.1.0";
11+
const char *PROGRAM_VERSION = "1.1.1";
1212
const char *PROGRAM_URL = "http://reorg.projects.postgresql.org/";
1313
const char *PROGRAM_EMAIL = "reorg-general@lists.pgfoundry.org";
1414

‎bin/pgut/pgut.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -273,6 +273,7 @@ parse_int64(const char *value, int64 *result)
273273
#elif defined(HAVE_LONG_INT_64)
274274
val = strtol(value, &endptr, 0);
275275
#elif defined(HAVE_LONG_LONG_INT_64)
276+
val = strtoll(value, &endptr, 0);
276277
#else
277278
val = strtol(value, &endptr, 0);
278279
#endif

‎bin/sql/reorg.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,14 @@ ALTER TABLE tbl_with_dropped_column CLUSTER ON tbl_with_dropped_column_pkey;
4747
CREATE INDEX idx_c1c2 ON tbl_with_dropped_column (c1, c2) WITH (fillfactor = 75);
4848
CREATE INDEX idx_c2c1 ON tbl_with_dropped_column (c2, c1);
4949

50+
CREATE TABLE tbl_with_dropped_toast (
51+
i integer,
52+
j integer,
53+
t text,
54+
PRIMARY KEY (i, j)
55+
);
56+
ALTER TABLE tbl_with_dropped_toast CLUSTER ON tbl_with_dropped_toast_pkey;
57+
5058
--
5159
-- insert data
5260
--
@@ -72,11 +80,16 @@ ALTER TABLE tbl_with_dropped_column DROP COLUMN d1;
7280
ALTER TABLE tbl_with_dropped_column DROP COLUMN d2;
7381
ALTER TABLE tbl_with_dropped_column DROP COLUMN d3;
7482
ALTER TABLE tbl_with_dropped_column ADD COLUMN c3 text;
83+
84+
INSERT INTO tbl_with_dropped_toast VALUES(1, 10, 'abc');
85+
INSERT INTO tbl_with_dropped_toast VALUES(2, 20, sqrt(2::numeric(1000,999))::text || sqrt(3::numeric(1000,999))::text);
86+
ALTER TABLE tbl_with_dropped_toast DROP COLUMN t;
7587
--
7688
-- before
7789
--
7890

7991
SELECT * FROM tbl_with_dropped_column;
92+
SELECT * FROM tbl_with_dropped_toast;
8093

8194
--
8295
-- do reorg
@@ -95,12 +108,28 @@ SELECT * FROM tbl_with_dropped_column;
95108
\d tbl_only_ckey
96109
\d tbl_only_pkey
97110
\d tbl_with_dropped_column
111+
\d tbl_with_dropped_toast
98112

99113
SELECT col1, to_char(col2, 'YYYY-MM-DD HH24:MI:SS'), ","")" FROM tbl_cluster;
100114
SELECT * FROM tbl_only_ckey ORDER BY 1;
101115
SELECT * FROM tbl_only_pkey ORDER BY 1;
102116
SELECT * FROM tbl_gistkey ORDER BY 1;
103117
SELECT * FROM tbl_with_dropped_column;
118+
SELECT * FROM tbl_with_dropped_toast;
119+
120+
--
121+
-- check broken links or orphan toast relations
122+
--
123+
SELECT oid, relname
124+
FROM pg_class
125+
WHERE relkind = 't'
126+
AND oid NOT IN (SELECT reltoastrelid FROM pg_class WHERE relkind = 'r');
127+
128+
SELECT oid, relname
129+
FROM pg_class
130+
WHERE relkind = 'r'
131+
AND reltoastrelid <> 0
132+
AND reltoastrelid NOT IN (SELECT oid FROM pg_class WHERE relkind = 't');
104133

105134
--
106135
-- clean up

‎doc/index-ja.html

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -46,7 +46,7 @@ <h1>pg_reorg ホームページへようこそ</h1>
4646
<h2>ドキュメント</h2>
4747

4848
<ul>
49-
<li><a href="pg_reorg-ja.html">pg_reorg 1.1.0 ドキュメント</a></li>
49+
<li><a href="pg_reorg-ja.html">pg_reorg 1.1.1 ドキュメント</a></li>
5050
<li><a href="pg_batch-ja.html">pg_batch 1.2.0 ドキュメント</a></li>
5151
</ul>
5252

‎doc/index.html

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,8 +48,8 @@ <h1>Welcome to the pg_reorg Project Home Page</h1>
4848
<h2>Documentation</h2>
4949

5050
<ul>
51-
<li><a href="pg_reorg.html">pg_reorg 1.1.0 documentation</a></li>
52-
<li><a href="pg_batch-ja.html">pg_batch 1.2.0 documentation</a> (ja)</li>
51+
<li><a href="pg_reorg.html">pg_reorg 1.1.1 documentation</a></li>
52+
<li><a href="pg_batch.html">pg_batch 1.2.0 documentation</a></li>
5353
</ul>
5454

5555
<h2>Execution time</h2>

‎doc/pg_reorg-ja.html

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
</head>
99

1010
<body>
11-
<h1 id="pg_reorg">pg_reorg 1.1.0</h1>
11+
<h1 id="pg_reorg">pg_reorg 1.1.1</h1>
1212
<div class="navigation">
1313
<a href="index-ja.html">Top</a> &gt;
1414
<a href="pg_reorg-ja.html">pg_reorg</a>

‎doc/pg_reorg.html

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
</head>
99

1010
<body>
11-
<h1 id="pg_reorg">pg_reorg 1.1.0</h1>
11+
<h1 id="pg_reorg">pg_reorg 1.1.1</h1>
1212
<div class="navigation">
1313
<a href="index.html">Top</a> &gt;
1414
<a href="pg_reorg.html">pg_reorg</a>
@@ -67,7 +67,7 @@ <h2 id="synopsis">Synopsis</h2>
6767

6868
<h2 id="description">Description</h2>
6969
<p>pg_reorg is an utility program to reorganize tables in PostgreSQL databases.
70-
Unlike <a href="http://www.postgresql.jp/document/current/html/app-clusterdb.html">clusterdb</a>, it doesn't block any selections and updates during reorganization.
70+
Unlike <a href="http://developer.postgresql.org/pgdocs/postgres/app-clusterdb.html">clusterdb</a>, it doesn't block any selections and updates during reorganization.
7171
You can choose one of the following methods to reorganize.</p>
7272
<ul>
7373
<li>Online CLUSTER (ordered by cluster index)</li>

‎lib/reorg.c

Lines changed: 36 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -75,7 +75,7 @@ static void RenameRelationInternal(Oid myrelid, const char *newrelname, Oid name
7575
Datum
7676
reorg_version(PG_FUNCTION_ARGS)
7777
{
78-
return CStringGetTextDatum("pg_reorg 1.1.0");
78+
return CStringGetTextDatum("pg_reorg 1.1.1");
7979
}
8080

8181
/**
@@ -468,6 +468,12 @@ reorg_get_index_keys(PG_FUNCTION_ARGS)
468468
int nattr;
469469

470470
parse_indexdef(&stmt, index, table);
471+
elog(DEBUG2, "indexdef.create = %s", stmt.create);
472+
elog(DEBUG2, "indexdef.index = %s", stmt.index);
473+
elog(DEBUG2, "indexdef.table = %s", stmt.table);
474+
elog(DEBUG2, "indexdef.type = %s", stmt.type);
475+
elog(DEBUG2, "indexdef.columns = %s", stmt.columns);
476+
elog(DEBUG2, "indexdef.options = %s", stmt.options);
471477

472478
/*
473479
* FIXME: this is very unreliable implementation but I don't want to
@@ -482,6 +488,8 @@ reorg_get_index_keys(PG_FUNCTION_ARGS)
482488
char *opcname;
483489

484490
token = next;
491+
while (isspace((unsigned char) *token))
492+
token++;
485493
next = skip_until(index, next, ',');
486494

487495
opcname = token + strlen(token);
@@ -555,7 +563,7 @@ reorg_get_index_keys(PG_FUNCTION_ARGS)
555563
else
556564
appendStringInfoString(&str, token);
557565
if (next)
558-
appendStringInfoChar(&str, ',');
566+
appendStringInfoString(&str, ", ");
559567
}
560568

561569
if (indexRel != NULL)
@@ -777,16 +785,6 @@ reorg_swap(PG_FUNCTION_ARGS)
777785
natts1 = getint16(tuple, desc, 8);
778786
natts2 = getint16(tuple, desc, 9);;
779787

780-
/* should be all-or-nothing */
781-
if ((reltoastrelid1 == InvalidOid || reltoastidxid1 == InvalidOid ||
782-
reltoastrelid2 == InvalidOid || reltoastidxid2 == InvalidOid) &&
783-
(reltoastrelid1 != InvalidOid || reltoastidxid1 != InvalidOid ||
784-
reltoastrelid2 != InvalidOid || reltoastidxid2 != InvalidOid))
785-
{
786-
elog(ERROR, "reorg_swap : unexpected toast relations (T1=%u, I1=%u, T2=%u, I2=%u",
787-
reltoastrelid1, reltoastidxid1, reltoastrelid2, reltoastidxid2);
788-
}
789-
790788
/* change owner of new relation to original owner */
791789
if (owner1 != owner2)
792790
{
@@ -848,7 +846,32 @@ reorg_swap(PG_FUNCTION_ARGS)
848846
}
849847

850848
/* swap names for toast tables and toast indexes */
851-
if (reltoastrelid1 != InvalidOid)
849+
if (reltoastrelid1 == InvalidOid)
850+
{
851+
if (reltoastidxid1 != InvalidOid ||
852+
reltoastrelid2 != InvalidOid ||
853+
reltoastidxid2 != InvalidOid)
854+
elog(ERROR, "reorg_swap : unexpected toast relations (T1=%u, I1=%u, T2=%u, I2=%u",
855+
reltoastrelid1, reltoastidxid1, reltoastrelid2, reltoastidxid2);
856+
/* do nothing */
857+
}
858+
else if (reltoastrelid2 == InvalidOid)
859+
{
860+
char name[NAMEDATALEN];
861+
862+
if (reltoastidxid1 == InvalidOid ||
863+
reltoastidxid2 != InvalidOid)
864+
elog(ERROR, "reorg_swap : unexpected toast relations (T1=%u, I1=%u, T2=%u, I2=%u",
865+
reltoastrelid1, reltoastidxid1, reltoastrelid2, reltoastidxid2);
866+
867+
/* rename X to Y */
868+
snprintf(name, NAMEDATALEN, "pg_toast_%u", oid2);
869+
RenameRelationInternal(reltoastrelid1, name, PG_TOAST_NAMESPACE);
870+
snprintf(name, NAMEDATALEN, "pg_toast_%u_index", oid2);
871+
RenameRelationInternal(reltoastidxid1, name, PG_TOAST_NAMESPACE);
872+
CommandCounterIncrement();
873+
}
874+
else if (reltoastrelid1 != InvalidOid)
852875
{
853876
char name[NAMEDATALEN];
854877
int pid = getpid();

0 commit comments

Comments
 (0)