Skip to content

Commit e9d4ef2

Browse files
Masahiko Sakamotodvarrazzo
Masahiko Sakamoto
authored and
dvarrazzo
committed
Supports CREATE EXTENSION.
I've changed lib/Makefile to support CREATE EXTENSION. In order to work with previeous PostgreSQL version (i.e. < 9.1), this implementation installs both pg_reorg--1.0.sql and pg_reorg.sql. This patch lacks regression testcases for CREATE EXTENSION, which I will commit soon.
1 parent d603c49 commit e9d4ef2

File tree

4 files changed

+262
-4
lines changed

4 files changed

+262
-4
lines changed

‎lib/Makefile

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,17 @@
11
#
22
# pg_reorg: lib/Makefile
33
#
4-
# Portions Copyright (c) 2008-2011, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
4+
# Portions Copyright (c) 2008-2012, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
55
# Portions Copyright (c) 2011, Itagaki Takahiro
66
#
7-
SRCS = reorg.c pgut/pgut-be.c pgut/pgut-spi.c
8-
OBJS = $(SRCS:.c=.o)
97
MODULE_big = pg_reorg
8+
OBJS = reorg.o pgut/pgut-be.o pgut/pgut-spi.o
9+
10+
EXTENSION = pg_reorg
11+
12+
#supports both EXTENSION (for >=9.1) and without_EXTENSION (for <PG 9.1)
1013
DATA_built = pg_reorg.sql
11-
DATA = uninstall_pg_reorg.sql
14+
DATA = pg_reorg--1.0.sql pg_reorg--unpackaged--1.0.sql uninstall_pg_reorg.sql
1215

1316
ifndef USE_PGXS
1417
top_builddir = ../../..

‎lib/pg_reorg--1.0.sql

Lines changed: 224 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,224 @@
1+
/* pg_reorg/pg_reorg--1.0.sql */
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use "CREATE EXTENSION pg_reorg" to load this file. \quit
5+
6+
CREATE FUNCTION reorg.version() RETURNS text AS
7+
'MODULE_PATHNAME', 'reorg_version'
8+
LANGUAGE C IMMUTABLE STRICT;
9+
10+
CREATE AGGREGATE reorg.array_accum (
11+
sfunc = array_append,
12+
basetype = anyelement,
13+
stype = anyarray,
14+
initcond = '{}'
15+
);
16+
17+
CREATE FUNCTION reorg.oid2text(oid) RETURNS text AS
18+
$$
19+
SELECT textin(regclassout($1));
20+
$$
21+
LANGUAGE sql STABLE STRICT;
22+
23+
CREATE FUNCTION reorg.get_index_columns(oid, text) RETURNS text AS
24+
$$
25+
SELECT array_to_string(reorg.array_accum(quote_ident(attname)), $2)
26+
FROM pg_attribute,
27+
(SELECT indrelid,
28+
indkey,
29+
generate_series(0, indnatts-1) AS i
30+
FROM pg_index
31+
WHERE indexrelid = $1
32+
) AS keys
33+
WHERE attrelid = indrelid
34+
AND attnum = indkey[i];
35+
$$
36+
LANGUAGE sql STABLE STRICT;
37+
38+
CREATE FUNCTION reorg.get_index_keys(oid, oid) RETURNS text AS
39+
'MODULE_PATHNAME', 'reorg_get_index_keys'
40+
LANGUAGE C STABLE STRICT;
41+
42+
CREATE FUNCTION reorg.get_create_index_type(oid, name) RETURNS text AS
43+
$$
44+
SELECT 'CREATE TYPE ' || $2 || ' AS (' ||
45+
array_to_string(reorg.array_accum(quote_ident(attname) || ' ' ||
46+
pg_catalog.format_type(atttypid, atttypmod)), ', ') || ')'
47+
FROM pg_attribute,
48+
(SELECT indrelid,
49+
indkey,
50+
generate_series(0, indnatts-1) AS i
51+
FROM pg_index
52+
WHERE indexrelid = $1
53+
) AS keys
54+
WHERE attrelid = indrelid
55+
AND attnum = indkey[i];
56+
$$
57+
LANGUAGE sql STABLE STRICT;
58+
59+
CREATE FUNCTION reorg.get_create_trigger(relid oid, pkid oid)
60+
RETURNS text AS
61+
$$
62+
SELECT 'CREATE TRIGGER z_reorg_trigger' ||
63+
' BEFORE INSERT OR DELETE OR UPDATE ON ' || reorg.oid2text($1) ||
64+
' FOR EACH ROW EXECUTE PROCEDURE reorg.reorg_trigger(' ||
65+
'''INSERT INTO reorg.log_' || $1 || '(pk, row) VALUES(' ||
66+
' CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.' ||
67+
reorg.get_index_columns($2, ', $1.') || ')::reorg.pk_' ||
68+
$1 || ') END, $2)'')';
69+
$$
70+
LANGUAGE sql STABLE STRICT;
71+
72+
CREATE FUNCTION reorg.get_assign(oid, text) RETURNS text AS
73+
$$
74+
SELECT '(' || array_to_string(reorg.array_accum(attname), ', ') ||
75+
') = (' || $2 || '.' ||
76+
array_to_string(reorg.array_accum(quote_ident(attname)), ', ' || $2 || '.') || ')'
77+
FROM (SELECT attname FROM pg_attribute
78+
WHERE attrelid = $1 AND attnum > 0 AND NOT attisdropped
79+
ORDER BY attnum) tmp;
80+
$$
81+
LANGUAGE sql STABLE STRICT;
82+
83+
CREATE FUNCTION reorg.get_compare_pkey(oid, text)
84+
RETURNS text AS
85+
$$
86+
SELECT '(' || array_to_string(reorg.array_accum(quote_ident(attname)), ', ') ||
87+
') = (' || $2 || '.' ||
88+
array_to_string(reorg.array_accum(quote_ident(attname)), ', ' || $2 || '.') || ')'
89+
FROM pg_attribute,
90+
(SELECT indrelid,
91+
indkey,
92+
generate_series(0, indnatts-1) AS i
93+
FROM pg_index
94+
WHERE indexrelid = $1
95+
) AS keys
96+
WHERE attrelid = indrelid
97+
AND attnum = indkey[i];
98+
$$
99+
LANGUAGE sql STABLE STRICT;
100+
101+
-- Get a column list for SELECT all columns including dropped ones.
102+
-- We use NULLs of integer types for dropped columns (types are not important).
103+
CREATE FUNCTION reorg.get_columns_for_create_as(oid)
104+
RETURNS text AS
105+
$$
106+
SELECT array_to_string(reorg.array_accum(c), ',') FROM (SELECT
107+
CASE WHEN attisdropped
108+
THEN 'NULL::integer AS ' || quote_ident(attname)
109+
ELSE quote_ident(attname)
110+
END AS c
111+
FROM pg_attribute
112+
WHERE attrelid = $1 AND attnum > 0 ORDER BY attnum
113+
) AS COL
114+
$$
115+
LANGUAGE sql STABLE STRICT;
116+
117+
-- Get a SQL text to DROP dropped columns for the table,
118+
-- or NULL if it has no dropped columns.
119+
CREATE FUNCTION reorg.get_drop_columns(oid, text)
120+
RETURNS text AS
121+
$$
122+
SELECT
123+
'ALTER TABLE ' || $2 || ' ' || array_to_string(dropped_columns, ', ')
124+
FROM (
125+
SELECT
126+
reorg.array_accum('DROP COLUMN ' || quote_ident(attname)) AS dropped_columns
127+
FROM (
128+
SELECT * FROM pg_attribute
129+
WHERE attrelid = $1 AND attnum > 0 AND attisdropped
130+
ORDER BY attnum
131+
) T
132+
) T
133+
WHERE
134+
array_upper(dropped_columns, 1) > 0
135+
$$
136+
LANGUAGE sql STABLE STRICT;
137+
138+
-- includes not only PRIMARY KEYS but also UNIQUE NOT NULL keys
139+
CREATE VIEW reorg.primary_keys AS
140+
SELECT indrelid, (reorg.array_accum(indexrelid))[1] AS indexrelid
141+
FROM (SELECT indrelid, indexrelid FROM pg_index
142+
WHERE indisunique
143+
AND 0 <> ALL(indkey)
144+
AND NOT EXISTS(
145+
SELECT 1 FROM pg_attribute
146+
WHERE attrelid = indrelid
147+
AND attnum = ANY(indkey)
148+
AND NOT attnotnull)
149+
ORDER BY indrelid, indisprimary DESC, indnatts, indkey) tmp
150+
GROUP BY indrelid;
151+
152+
CREATE VIEW reorg.tables AS
153+
SELECT R.oid::regclass AS relname,
154+
R.oid AS relid,
155+
R.reltoastrelid AS reltoastrelid,
156+
CASE WHEN R.reltoastrelid = 0 THEN 0 ELSE (SELECT reltoastidxid FROM pg_class WHERE oid = R.reltoastrelid) END AS reltoastidxid,
157+
PK.indexrelid AS pkid,
158+
CK.indexrelid AS ckid,
159+
reorg.get_create_index_type(PK.indexrelid, 'reorg.pk_' || R.oid) AS create_pktype,
160+
'CREATE TABLE reorg.log_' || R.oid || ' (id bigserial PRIMARY KEY, pk reorg.pk_' || R.oid || ', row ' || reorg.oid2text(R.oid) || ')' AS create_log,
161+
reorg.get_create_trigger(R.oid, PK.indexrelid) AS create_trigger,
162+
'CREATE TABLE reorg.table_' || R.oid || ' WITH (' || array_to_string(array_append(R.reloptions, 'oids=' || CASE WHEN R.relhasoids THEN 'true' ELSE 'false' END), ',') || ') TABLESPACE ' || coalesce(quote_ident(S.spcname), 'pg_default') || ' AS SELECT ' || reorg.get_columns_for_create_as(R.oid) || ' FROM ONLY ' || reorg.oid2text(R.oid) AS create_table,
163+
reorg.get_drop_columns(R.oid, 'reorg.table_' || R.oid) AS drop_columns,
164+
'DELETE FROM reorg.log_' || R.oid AS delete_log,
165+
'LOCK TABLE ' || reorg.oid2text(R.oid) || ' IN ACCESS EXCLUSIVE MODE' AS lock_table,
166+
reorg.get_index_keys(CK.indexrelid, R.oid) AS ckey,
167+
'SELECT * FROM reorg.log_' || R.oid || ' ORDER BY id LIMIT $1' AS sql_peek,
168+
'INSERT INTO reorg.table_' || R.oid || ' VALUES ($1.*)' AS sql_insert,
169+
'DELETE FROM reorg.table_' || R.oid || ' WHERE ' || reorg.get_compare_pkey(PK.indexrelid, '$1') AS sql_delete,
170+
'UPDATE reorg.table_' || R.oid || ' SET ' || reorg.get_assign(R.oid, '$2') || ' WHERE ' || reorg.get_compare_pkey(PK.indexrelid, '$1') AS sql_update,
171+
'DELETE FROM reorg.log_' || R.oid || ' WHERE id <= $1' AS sql_pop
172+
FROM pg_class R
173+
LEFT JOIN pg_class T ON R.reltoastrelid = T.oid
174+
LEFT JOIN reorg.primary_keys PK
175+
ON R.oid = PK.indrelid
176+
LEFT JOIN (SELECT CKI.* FROM pg_index CKI, pg_class CKT
177+
WHERE CKI.indisvalid
178+
AND CKI.indexrelid = CKT.oid
179+
AND CKI.indisclustered
180+
AND CKT.relam = 403) CK
181+
ON R.oid = CK.indrelid
182+
LEFT JOIN pg_namespace N ON N.oid = R.relnamespace
183+
LEFT JOIN pg_tablespace S ON S.oid = R.reltablespace
184+
WHERE R.relkind = 'r'
185+
AND N.nspname NOT IN ('pg_catalog', 'information_schema')
186+
AND N.nspname NOT LIKE E'pg\\_temp\\_%';
187+
188+
CREATE FUNCTION reorg.reorg_indexdef(oid, oid) RETURNS text AS
189+
'MODULE_PATHNAME', 'reorg_indexdef'
190+
LANGUAGE C STABLE STRICT;
191+
192+
CREATE FUNCTION reorg.reorg_trigger() RETURNS trigger AS
193+
'MODULE_PATHNAME', 'reorg_trigger'
194+
LANGUAGE C VOLATILE STRICT SECURITY DEFINER;
195+
196+
CREATE FUNCTION reorg.conflicted_triggers(oid) RETURNS SETOF name AS
197+
$$
198+
SELECT tgname FROM pg_trigger
199+
WHERE tgrelid = $1 AND tgname >= 'z_reorg_trigger'
200+
$$
201+
LANGUAGE sql STABLE STRICT;
202+
203+
CREATE FUNCTION reorg.disable_autovacuum(regclass) RETURNS void AS
204+
'MODULE_PATHNAME', 'reorg_disable_autovacuum'
205+
LANGUAGE C VOLATILE STRICT;
206+
207+
CREATE FUNCTION reorg.reorg_apply(
208+
sql_peek cstring,
209+
sql_insert cstring,
210+
sql_delete cstring,
211+
sql_update cstring,
212+
sql_pop cstring,
213+
count integer)
214+
RETURNS integer AS
215+
'MODULE_PATHNAME', 'reorg_apply'
216+
LANGUAGE C VOLATILE;
217+
218+
CREATE FUNCTION reorg.reorg_swap(oid) RETURNS void AS
219+
'MODULE_PATHNAME', 'reorg_swap'
220+
LANGUAGE C VOLATILE STRICT;
221+
222+
CREATE FUNCTION reorg.reorg_drop(oid) RETURNS void AS
223+
'MODULE_PATHNAME', 'reorg_drop'
224+
LANGUAGE C VOLATILE STRICT;

‎lib/pg_reorg--unpackaged--1.0.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
/* pg_reorg/pg_reorg--unpackaged--1.0.sql */
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use "CREATE EXTENSION pg_reorg" to load this file. \quit
5+
6+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.version();
7+
ALTER EXTENSION pg_reorg ADD AGGREGATE reorg.array_accum (array_append, anyelement, anyarray,'{}');
8+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.oid2text(oid);
9+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.get_index_columns(oid, text);
10+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.get_index_keys(oid, oid);
11+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.get_create_index_type(oid, name);
12+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.get_create_trigger(relid oid, pkid oid);
13+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.get_assign(oid, text);
14+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.get_compare_pkey(oid, text);
15+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.get_columns_for_create_as(oid);
16+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.get_drop_columns(oid, text);
17+
ALTER EXTENSION pg_reorg ADD VIEW reorg.primary_keys;
18+
ALTER EXTENSION pg_reorg ADD VIEW reorg.tables;
19+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.reorg_indexdef(oid, oid);
20+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.reorg_trigger();
21+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.conflicted_triggers(oid);
22+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.disable_autovacuum(regclass);
23+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.reorg_apply((cstring,cstring,cstring,cstring,cstring,integer);
24+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.reorg_swap(oid);
25+
ALTER EXTENSION pg_reorg ADD FUNCTION reorg.reorg_drop(oid);

‎lib/pg_reorg.control

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
# pg_reorg extension
2+
comment = 're-organizes a PostgreSQL database'
3+
default_version = '1.0'
4+
module_pathname = '$libdir/pg_reorg'
5+
relocatable = false
6+
schema = reorg

0 commit comments

Comments
 (0)