Skip to content

Commit 960930b

Browse files
ItGackyItGacky
committed
Fixed database corruption when target tables have dropped columns, and
there are views or functions depending on columns after dropped ones. The issue was reported by depesz, and original patch by Denish Patel. Improved documentation how to build binaries from source. COPYRIGHT updated.
1 parent 830ef42 commit 960930b

24 files changed

+198
-177
lines changed

‎COPYRIGHT

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
1-
Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
1+
Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
2+
Portions Copyright (c) 2011, Itagaki Takahiro
23
All rights reserved.
34

45
Redistribution and use in source and binary forms, with or without
@@ -9,10 +10,9 @@ modification, are permitted provided that the following conditions are met:
910
* Redistributions in binary form must reproduce the above copyright
1011
notice, this list of conditions and the following disclaimer in the
1112
documentation and/or other materials provided with the distribution.
12-
* Neither the name of the NIPPON TELEGRAPH AND TELEPHONE CORPORATION
13-
(NTT) nor the names of its contributors may be used to endorse or
14-
promote products derived from this software without specific prior
15-
written permission.
13+
* Neither the name of the authors nor the names of its contributors may
14+
be used to endorse or promote products derived from this software
15+
without specific prior written permission.
1616

1717
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
1818
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,8 @@
11
#
22
# pg_reorg: Makefile
33
#
4-
# Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
4+
# Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
5+
# Portions Copyright (c) 2011, Itagaki Takahiro
56
#
67
ifndef USE_PGXS
78
top_builddir = ../..

‎bin/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,8 @@
11
#
22
# pg_reorg: bin/Makefile
33
#
4-
# Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
4+
# Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
5+
# Portions Copyright (c) 2011, Itagaki Takahiro
56
#
67
SRCS = pg_reorg.c pgut/pgut.c pgut/pgut-fe.c
78
OBJS = $(SRCS:.c=.o)

‎bin/expected/reorg.out

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -66,6 +66,8 @@ ALTER TABLE tbl_with_dropped_column DROP COLUMN d1;
6666
ALTER TABLE tbl_with_dropped_column DROP COLUMN d2;
6767
ALTER TABLE tbl_with_dropped_column DROP COLUMN d3;
6868
ALTER TABLE tbl_with_dropped_column ADD COLUMN c3 text;
69+
CREATE VIEW view_for_dropped_column AS
70+
SELECT * FROM tbl_with_dropped_column;
6971
INSERT INTO tbl_with_dropped_toast VALUES(1, 10, 'abc');
7072
INSERT INTO tbl_with_dropped_toast VALUES(2, 20, sqrt(2::numeric(1000,999))::text || sqrt(3::numeric(1000,999))::text);
7173
ALTER TABLE tbl_with_dropped_toast DROP COLUMN t;
@@ -79,6 +81,13 @@ SELECT * FROM tbl_with_dropped_column;
7981
c1 | 1 | c2 |
8082
(2 rows)
8183

84+
SELECT * FROM view_for_dropped_column;
85+
c1 | id | c2 | c3
86+
----+----+----+----
87+
c1 | 2 | c2 |
88+
c1 | 1 | c2 |
89+
(2 rows)
90+
8291
SELECT * FROM tbl_with_dropped_toast;
8392
i | j
8493
---+----
@@ -188,20 +197,54 @@ SELECT * FROM tbl_gistkey ORDER BY 1;
188197
2 | <(4,5),6>
189198
(2 rows)
190199

200+
SET enable_seqscan = on;
201+
SET enable_indexscan = off;
202+
SELECT * FROM tbl_with_dropped_column;
203+
c1 | id | c2 | c3
204+
----+----+----+----
205+
c1 | 1 | c2 |
206+
c1 | 2 | c2 |
207+
(2 rows)
208+
209+
SELECT * FROM view_for_dropped_column;
210+
c1 | id | c2 | c3
211+
----+----+----+----
212+
c1 | 1 | c2 |
213+
c1 | 2 | c2 |
214+
(2 rows)
215+
216+
SELECT * FROM tbl_with_dropped_toast;
217+
i | j
218+
---+----
219+
1 | 10
220+
2 | 20
221+
(2 rows)
222+
223+
SET enable_seqscan = off;
224+
SET enable_indexscan = on;
191225
SELECT * FROM tbl_with_dropped_column;
192226
c1 | id | c2 | c3
193227
----+----+----+----
194228
c1 | 1 | c2 |
195229
c1 | 2 | c2 |
196230
(2 rows)
197231

232+
SELECT * FROM view_for_dropped_column;
233+
c1 | id | c2 | c3
234+
----+----+----+----
235+
c1 | 1 | c2 |
236+
c1 | 2 | c2 |
237+
(2 rows)
238+
198239
SELECT * FROM tbl_with_dropped_toast;
199240
i | j
200241
---+----
201242
1 | 10
202243
2 | 20
203244
(2 rows)
204245

246+
RESET enable_seqscan;
247+
RESET enable_indexscan;
205248
--
206249
-- check broken links or orphan toast relations
207250
--

‎bin/pg_reorg.c

Lines changed: 14 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,15 @@
11
/*
22
* pg_reorg.c: bin/pg_reorg.c
33
*
4-
* Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
4+
* Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
5+
* Portions Copyright (c) 2011, Itagaki Takahiro
56
*/
67

78
/**
89
* @brief Client Modules
910
*/
1011

11-
const char *PROGRAM_VERSION = "1.1.5";
12+
const char *PROGRAM_VERSION = "1.1.6";
1213
const char *PROGRAM_URL = "http://reorg.projects.postgresql.org/";
1314
const char *PROGRAM_EMAIL = "reorg-general@lists.pgfoundry.org";
1415

@@ -19,6 +20,10 @@ const char *PROGRAM_EMAIL = "reorg-general@lists.pgfoundry.org";
1920
#include <unistd.h>
2021
#include <time.h>
2122

23+
/*
24+
* APPLY_COUNT: Number of applied logs per transaction. Larger values
25+
* could be faster, but will be long transactions in the REDO phase.
26+
*/
2227
#define APPLY_COUNT 1000
2328

2429
#define SQL_XID_SNAPSHOT_80300 \
@@ -60,6 +65,7 @@ typedef struct reorg_table
6065
const char *create_log; /* CREATE TABLE log */
6166
const char *create_trigger; /* CREATE TRIGGER z_reorg_trigger */
6267
const char *create_table; /* CREATE TABLE table AS SELECT */
68+
const char *drop_columns; /* ALTER TABLE DROP COLUMNs */
6369
const char *delete_log; /* DELETE FROM log */
6470
const char *lock_table; /* LOCK TABLE table */
6571
const char *sql_peek; /* SQL used in flush */
@@ -300,6 +306,7 @@ reorg_one_database(const char *orderby, const char *table)
300306
table.create_trigger = getstr(res, i, c++);
301307

302308
create_table = getstr(res, i, c++);
309+
table.drop_columns = getstr(res, i, c++);
303310
table.delete_log = getstr(res, i, c++);
304311
table.lock_table = getstr(res, i, c++);
305312
ckey = getstr(res, i, c++);
@@ -393,6 +400,7 @@ reorg_one_table(const reorg_table *table, const char *orderby)
393400
elog(DEBUG2, "create_log : %s", table->create_log);
394401
elog(DEBUG2, "create_trigger : %s", table->create_trigger);
395402
elog(DEBUG2, "create_table : %s", table->create_table);
403+
elog(DEBUG2, "drop_columns : %s", table->drop_columns ? table->drop_columns : "(skipped)");
396404
elog(DEBUG2, "delete_log : %s", table->delete_log);
397405
elog(DEBUG2, "lock_table : %s", table->lock_table);
398406
elog(DEBUG2, "sql_peek : %s", table->sql_peek);
@@ -450,6 +458,8 @@ reorg_one_table(const reorg_table *table, const char *orderby)
450458
command(table->delete_log, 0, NULL);
451459
command(table->create_table, 0, NULL);
452460
printfStringInfo(&sql, "SELECT reorg.disable_autovacuum('reorg.table_%u')", table->target_oid);
461+
if (table->drop_columns)
462+
command(table->drop_columns, 0, NULL);
453463
command(sql.data, 0, NULL);
454464
command("COMMIT", 0, NULL);
455465

@@ -635,7 +645,7 @@ reorg_cleanup(bool fatal, void *userdata)
635645

636646
if (fatal)
637647
{
638-
fprintf(stderr, "!!!FATAL ERROR!!! Please refer to a manual.\n\n");
648+
fprintf(stderr, "!!!FATAL ERROR!!! Please refer to the manual.\n\n");
639649
}
640650
else
641651
{
@@ -671,6 +681,6 @@ pgut_help(bool details)
671681
printf(" -t, --table=TABLE reorg specific table only\n");
672682
printf(" -n, --no-order do vacuum full instead of cluster\n");
673683
printf(" -o, --order-by=columns order by columns instead of cluster keys\n");
674-
printf(" -T, --wait-timeout=secs timeout to cancel other backends on conflict.\n");
684+
printf(" -T, --wait-timeout=secs timeout to cancel other backends on conflict\n");
675685
printf(" -Z, --no-analyze don't analyze at end\n");
676686
}

‎bin/pgut/pgut-fe.c

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,8 @@
11
/*-------------------------------------------------------------------------
2-
*
32
* pgut-fe.c
43
*
5-
* Copyright (c) 2009-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
6-
*
4+
* Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
5+
* Portions Copyright (c) 2011, Itagaki Takahiro
76
*-------------------------------------------------------------------------
87
*/
98

‎bin/pgut/pgut-fe.h

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,8 @@
11
/*-------------------------------------------------------------------------
2-
*
32
* pgut-fe.h
43
*
5-
* Copyright (c) 2009-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
6-
*
4+
* Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
5+
* Portions Copyright (c) 2011, Itagaki Takahiro
76
*-------------------------------------------------------------------------
87
*/
98

‎bin/pgut/pgut.c

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,8 @@
11
/*-------------------------------------------------------------------------
2-
*
32
* pgut.c
43
*
5-
* Copyright (c) 2009-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
6-
*
4+
* Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
5+
* Portions Copyright (c) 2011, Itagaki Takahiro
76
*-------------------------------------------------------------------------
87
*/
98

‎bin/pgut/pgut.h

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,8 @@
11
/*-------------------------------------------------------------------------
2-
*
32
* pgut.h
43
*
5-
* Copyright (c) 2009-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
6-
*
4+
* Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
5+
* Portions Copyright (c) 2011, Itagaki Takahiro
76
*-------------------------------------------------------------------------
87
*/
98

‎bin/sql/reorg.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -80,6 +80,8 @@ ALTER TABLE tbl_with_dropped_column DROP COLUMN d1;
8080
ALTER TABLE tbl_with_dropped_column DROP COLUMN d2;
8181
ALTER TABLE tbl_with_dropped_column DROP COLUMN d3;
8282
ALTER TABLE tbl_with_dropped_column ADD COLUMN c3 text;
83+
CREATE VIEW view_for_dropped_column AS
84+
SELECT * FROM tbl_with_dropped_column;
8385

8486
INSERT INTO tbl_with_dropped_toast VALUES(1, 10, 'abc');
8587
INSERT INTO tbl_with_dropped_toast VALUES(2, 20, sqrt(2::numeric(1000,999))::text || sqrt(3::numeric(1000,999))::text);
@@ -89,6 +91,7 @@ ALTER TABLE tbl_with_dropped_toast DROP COLUMN t;
8991
--
9092

9193
SELECT * FROM tbl_with_dropped_column;
94+
SELECT * FROM view_for_dropped_column;
9295
SELECT * FROM tbl_with_dropped_toast;
9396

9497
--
@@ -114,8 +117,19 @@ SELECT col1, to_char("time", 'YYYY-MM-DD HH24:MI:SS'), ","")" FROM tbl_cluster;
114117
SELECT * FROM tbl_only_ckey ORDER BY 1;
115118
SELECT * FROM tbl_only_pkey ORDER BY 1;
116119
SELECT * FROM tbl_gistkey ORDER BY 1;
120+
121+
SET enable_seqscan = on;
122+
SET enable_indexscan = off;
123+
SELECT * FROM tbl_with_dropped_column;
124+
SELECT * FROM view_for_dropped_column;
125+
SELECT * FROM tbl_with_dropped_toast;
126+
SET enable_seqscan = off;
127+
SET enable_indexscan = on;
117128
SELECT * FROM tbl_with_dropped_column;
129+
SELECT * FROM view_for_dropped_column;
118130
SELECT * FROM tbl_with_dropped_toast;
131+
RESET enable_seqscan;
132+
RESET enable_indexscan;
119133

120134
--
121135
-- check broken links or orphan toast relations

‎doc/index-ja.html

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -121,7 +121,10 @@ <h2>実行時間</h2>
121121
</div>
122122

123123
<hr />
124-
<p class="footer">Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>
124+
<p class="footer">
125+
Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION<br />
126+
Portions Copyright (c) 2011, Itagaki Takahiro
127+
</p>
125128

126129
<script type="text/javascript">
127130
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");

‎doc/index.html

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -122,7 +122,10 @@ <h2>Execution time</h2>
122122
</center>
123123

124124
<hr />
125-
<p class="footer">Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>
125+
<p class="footer">
126+
Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION<br />
127+
Portions Copyright (c) 2011, Itagaki Takahiro
128+
</p>
126129

127130
<script type="text/javascript">
128131
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");

‎doc/pg_reorg-ja.html

Lines changed: 19 additions & 7 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.3</h1>
11+
<h1 id="pg_reorg">pg_reorg 1.1.6</h1>
1212
<div class="navigation">
1313
<a href="index-ja.html">Top</a> &gt;
1414
<a href="pg_reorg-ja.html">pg_reorg</a>
@@ -291,20 +291,29 @@ <h2 id="details">詳細</h2>
291291
最後にシステムカタログを直接書き換えることで、元のテーブルと名前を交換しています。</p>
292292

293293
<h2 id="install">インストール方法</h2>
294-
<p>pgxs を使ってビルドできます。</p>
294+
<p>
295+
UNIX や Linux では、make を実行すると自動的に pgxs を使ってビルドできます。
296+
前もって PostgreSQL 開発用パッケージ (postgresql-devel 等) をインストールし、pg_config にパスを通してください。
297+
</p>
295298
<pre>$ cd pg_reorg
296-
$ make USE_PGXS=1
299+
$ make
297300
$ su
298-
$ make USE_PGXS=1 install</pre>
301+
$ make install</pre>
302+
<p>
303+
Windows では Microsoft Visual C++ 2010 でビルドできます。
304+
msvc フォルダ内にプロジェクトファイルがあります。
305+
</p>
299306

300307
<p>その後、データベースに関数を登録します。</p>
301308
<pre>$ pg_ctl start
302-
$ psql -f $PGSHARE/contrib/pg_reorg.sql your_database</pre>
309+
$ psql -f $PGSHARE/contrib/pg_reorg.sql -d your_database</pre>
310+
311+
<p>(注意: <code>CREATE EXTENSION</code> はまだサポートしていません。)</p>
303312

304313
<h2 id="requirement">動作環境</h2>
305314
<dl>
306315
<dt>PostgreSQLバージョン</dt>
307-
<dd>PostgreSQL 8.3, 8.4, 9.0</dd>
316+
<dd>PostgreSQL 8.2, 8.3, 8.4, 9.0, 9.1</dd>
308317
<dt>OS</dt>
309318
<dd>RHEL 5.2, Windows XP SP3</dd>
310319
<dt>ディスク容量</dt>
@@ -320,7 +329,10 @@ <h2 id="seealso">関連項目</h2>
320329
<a href="index-ja.html">Top</a> &gt;
321330
<a href="pg_reorg-ja.html">pg_reorg</a>
322331
<div>
323-
<p class="footer">Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>
332+
<p class="footer">
333+
Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION<br />
334+
Portions Copyright (c) 2011, Itagaki Takahiro
335+
</p>
324336

325337
<script type="text/javascript">
326338
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");

0 commit comments

Comments
 (0)