Skip to content

Commit 232c9bb

Browse files
ItGackyItGacky
committed
Use one of not-null unique keys to identify rows when the target table doesn't
have a primary key. Some of users want to use not-null unique keys rather than primary keys because postgres doesn't support REINDEX PRIMARY KEY CONCURRENTLY. - Support 9.1dev. - Improve Makefile to use PGXS automatically.
1 parent d8d39cc commit 232c9bb

File tree

9 files changed

+95
-15
lines changed

9 files changed

+95
-15
lines changed

‎Makefile

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3,14 +3,22 @@
33
#
44
# Copyright (c) 2008-2010, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
55
#
6+
ifndef USE_PGXS
7+
top_builddir = ../..
8+
makefile_global = $(top_builddir)/src/Makefile.global
9+
ifeq "$(wildcard $(makefile_global))" ""
10+
USE_PGXS = 1 # use pgxs if not in contrib directory
11+
endif
12+
endif
13+
614
ifdef USE_PGXS
715
PG_CONFIG = pg_config
816
PGXS := $(shell $(PG_CONFIG) --pgxs)
917
include $(PGXS)
1018
else
11-
subdir = pg_statsinfo
12-
top_builddir = ../..
13-
include $(top_builddir)/src/Makefile.global
19+
subdir = pg_reorg
20+
include $(makefile_global)
21+
include $(top_srcdir)/contrib/contrib-global.mk
1422
endif
1523

1624
SUBDIRS = bin lib

‎bin/Makefile

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -15,13 +15,21 @@ PG_CPPFLAGS = -I$(libpq_srcdir)
1515
endif
1616
PG_LIBS = $(libpq)
1717

18+
ifndef USE_PGXS
19+
top_builddir = ../../..
20+
makefile_global = $(top_builddir)/src/Makefile.global
21+
ifeq "$(wildcard $(makefile_global))" ""
22+
USE_PGXS = 1 # use pgxs if not in contrib directory
23+
endif
24+
endif
25+
1826
ifdef USE_PGXS
19-
PGXS := $(shell pg_config --pgxs)
27+
PG_CONFIG = pg_config
28+
PGXS := $(shell $(PG_CONFIG) --pgxs)
2029
include $(PGXS)
2130
else
22-
subdir = contrib/pg_reorg
23-
top_builddir = ../../..
24-
include $(top_builddir)/src/Makefile.global
31+
subdir = contrib/$(MODULE_big)
32+
include $(makefile_global)
2533
include $(top_srcdir)/contrib/contrib-global.mk
2634
endif
2735

‎bin/expected/reorg.out

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -222,3 +222,20 @@ SELECT oid, relname
222222
-----+---------
223223
(0 rows)
224224

225+
--
226+
-- NOT NULL UNIQUE
227+
--
228+
CREATE TABLE tbl_nn (col1 int NOT NULL, col2 int NOT NULL);
229+
CREATE TABLE tbl_uk (col1 int NOT NULL, col2 int , UNIQUE(col1, col2));
230+
CREATE TABLE tbl_nn_uk (col1 int NOT NULL, col2 int NOT NULL, UNIQUE(col1, col2));
231+
CREATE TABLE tbl_pk_uk (col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1, col2), UNIQUE(col2, col1));
232+
\! pg_reorg --dbname=contrib_regression --no-order --table=tbl_nn
233+
ERROR: relation "tbl_nn" must have a primary key or not-null unique keys
234+
-- => ERROR
235+
\! pg_reorg --dbname=contrib_regression --no-order --table=tbl_uk
236+
ERROR: relation "tbl_uk" must have a primary key or not-null unique keys
237+
-- => ERROR
238+
\! pg_reorg --dbname=contrib_regression --no-order --table=tbl_nn_uk
239+
-- => OK
240+
\! pg_reorg --dbname=contrib_regression --no-order --table=tbl_pk_uk
241+
-- => OK

‎bin/pg_reorg.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -293,7 +293,7 @@ reorg_one_database(const char *orderby, const char *table)
293293
if (table.pkid == 0)
294294
ereport(ERROR,
295295
(errcode(E_PG_COMMAND),
296-
errmsg("relation \"%s\" has no primary key", table.target_name)));
296+
errmsg("relation \"%s\" must have a primary key or not-null unique keys", table.target_name)));
297297

298298
table.create_pktype = getstr(res, i, c++);
299299
table.create_log = getstr(res, i, c++);

‎bin/sql/reorg.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -130,3 +130,19 @@ SELECT oid, relname
130130
WHERE relkind = 'r'
131131
AND reltoastrelid <> 0
132132
AND reltoastrelid NOT IN (SELECT oid FROM pg_class WHERE relkind = 't');
133+
134+
--
135+
-- NOT NULL UNIQUE
136+
--
137+
CREATE TABLE tbl_nn (col1 int NOT NULL, col2 int NOT NULL);
138+
CREATE TABLE tbl_uk (col1 int NOT NULL, col2 int , UNIQUE(col1, col2));
139+
CREATE TABLE tbl_nn_uk (col1 int NOT NULL, col2 int NOT NULL, UNIQUE(col1, col2));
140+
CREATE TABLE tbl_pk_uk (col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1, col2), UNIQUE(col2, col1));
141+
\! pg_reorg --dbname=contrib_regression --no-order --table=tbl_nn
142+
-- => ERROR
143+
\! pg_reorg --dbname=contrib_regression --no-order --table=tbl_uk
144+
-- => ERROR
145+
\! pg_reorg --dbname=contrib_regression --no-order --table=tbl_nn_uk
146+
-- => OK
147+
\! pg_reorg --dbname=contrib_regression --no-order --table=tbl_pk_uk
148+
-- => OK

‎lib/Makefile

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -9,13 +9,21 @@ MODULE_big = pg_reorg
99
DATA_built = pg_reorg.sql
1010
DATA = uninstall_pg_reorg.sql
1111

12+
ifndef USE_PGXS
13+
top_builddir = ../../..
14+
makefile_global = $(top_builddir)/src/Makefile.global
15+
ifeq "$(wildcard $(makefile_global))" ""
16+
USE_PGXS = 1 # use pgxs if not in contrib directory
17+
endif
18+
endif
19+
1220
ifdef USE_PGXS
13-
PGXS := $(shell pg_config --pgxs)
21+
PG_CONFIG = pg_config
22+
PGXS := $(shell $(PG_CONFIG) --pgxs)
1423
include $(PGXS)
1524
else
16-
subdir = contrib/pg_reorg
17-
top_builddir = ../../..
18-
include $(top_builddir)/src/Makefile.global
25+
subdir = contrib/$(MODULE_big)
26+
include $(makefile_global)
1927
include $(top_srcdir)/contrib/contrib-global.mk
2028
endif
2129

‎lib/pg_reorg.sql.in

Lines changed: 19 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -106,6 +106,20 @@ $$
106106
$$
107107
LANGUAGE sql STABLE STRICT;
108108

109+
-- includes not only PRIMARY KEYS but also UNIQUE NOT NULL keys
110+
CREATE VIEW reorg.primary_keys AS
111+
SELECT indrelid, (reorg.array_accum(indexrelid))[1] AS indexrelid
112+
FROM (SELECT indrelid, indexrelid FROM pg_index
113+
WHERE indisunique
114+
AND 0 <> ALL(indkey)
115+
AND NOT EXISTS(
116+
SELECT 1 FROM pg_attribute
117+
WHERE attrelid = indrelid
118+
AND attnum = ANY(indkey)
119+
AND NOT attnotnull)
120+
ORDER BY indrelid, indisprimary DESC, indnatts, indkey) tmp
121+
GROUP BY indrelid;
122+
109123
CREATE VIEW reorg.tables AS
110124
SELECT R.oid::regclass AS relname,
111125
R.oid AS relid,
@@ -127,10 +141,13 @@ CREATE VIEW reorg.tables AS
127141
'DELETE FROM reorg.log_' || R.oid || ' WHERE id <= $1' AS sql_pop
128142
FROM pg_class R
129143
LEFT JOIN pg_class T ON R.reltoastrelid = T.oid
130-
LEFT JOIN (SELECT * FROM pg_index WHERE indisprimary) PK
144+
LEFT JOIN reorg.primary_keys PK
131145
ON R.oid = PK.indrelid
132146
LEFT JOIN (SELECT CKI.* FROM pg_index CKI, pg_class CKT
133-
WHERE CKI.indexrelid = CKT.oid AND CKI.indisclustered AND CKT.relam = 403) CK
147+
WHERE CKI.indisvalid
148+
AND CKI.indexrelid = CKT.oid
149+
AND CKI.indisclustered
150+
AND CKT.relam = 403) CK
134151
ON R.oid = CK.indrelid
135152
LEFT JOIN pg_namespace N ON N.oid = R.relnamespace
136153
LEFT JOIN pg_tablespace S ON S.oid = R.reltablespace

‎lib/pgut/pgut-be.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -151,6 +151,12 @@ extern void tuplestore_putvalues(Tuplestorestate *state, TupleDesc tdesc,
151151

152152
#endif
153153

154+
#if PG_VERSION_NUM < 90100
155+
156+
#define ATExecChangeOwner(relationOid, newOwnerId, recursing, lockmode) \
157+
ATExecChangeOwner((relationOid), (newOwnerId), (recursing))
158+
#endif
159+
154160
#if PG_VERSION_NUM < 80300
155161
#define RelationSetNewRelfilenode(rel, xid) \
156162
setNewRelfilenode((rel))

‎lib/reorg.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -766,7 +766,7 @@ reorg_swap(PG_FUNCTION_ARGS)
766766
/* change owner of new relation to original owner */
767767
if (owner1 != owner2)
768768
{
769-
ATExecChangeOwner(oid2, owner1, true);
769+
ATExecChangeOwner(oid2, owner1, true, AccessExclusiveLock);
770770
CommandCounterIncrement();
771771
}
772772

0 commit comments

Comments
 (0)