Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit51d1642

Browse files
committed
- Corrections for tables, columns with uppercase characters
- Don't attempt to convert partial or expressional unique indexes- Don't attempt to convert unique indexes based on a non-defaultopclasses- Untested prevention of conversion of non-btree indexes uniqueindexes. Untested as postgresql doesn't allow hash, gist, or rtreebased indexes to be unique.rbt=# create unique index t on a using hash (col);ERROR: DefineIndex: access method "hash" does not support UNIQUEindexesrbt=# create unique index t on a using gist (col);ERROR: DefineIndex: access method "gist" does not support UNIQUEindexesrbt=# select version(); version------------------------------------------------------------------------ PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4Rod Taylor
1 parent8f337e8 commit51d1642

File tree

2 files changed

+95
-31
lines changed

2 files changed

+95
-31
lines changed

‎contrib/adddepend/README.adddepend‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -16,9 +16,9 @@ individually.
1616
created but there was no indication that the index was created as a
1717
UNIQUE column constraint.
1818

19-
Fortunately, 7.3 now tracks such dependencies and handles these cases.
20-
Unfortunately, PostgreSQL dumps from prior releases don't contain
21-
such dependency information.
19+
Fortunately,PostgreSQL7.3and laternow tracks such dependencies
20+
and handles these cases.Unfortunately, PostgreSQL dumps from prior
21+
releases don't containsuch dependency information.
2222

2323
This script operates on >= 7.3 databases and adds dependency information
2424
for the objects listed above. It prompts the user on whether to create
@@ -42,4 +42,4 @@ Options:
4242
the conversion for each item found. This forces YES to all
4343
questions.
4444

45-
Rod Taylor <rbt@rbt.ca>
45+
Rod Taylor <pg@rbt.ca>

‎contrib/adddepend/adddepend‎

Lines changed: 91 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,8 @@
11
#!/usr/bin/perl
2-
# $Id: adddepend,v 1.4 2003/05/14 03:25:55 tgl Exp $
2+
# $Id: adddepend,v 1.5 2003/06/25 01:11:09 momjian Exp $
33

44
# Project exists to assist PostgreSQL users with their structural upgrade
5-
# from 7.2 (or prior) to 7.3(possibly later). Must be run against a 7.3
5+
# fromPostgreSQL7.2 (or prior) to 7.3or 7.4. Must be run against a 7.3 or 7.4
66
# database system (dump, upgrade daemon, restore, run this script)
77
#
88
# - Replace old style Foreign Keys with new style
@@ -125,6 +125,20 @@ my $dbh = DBI->connect($dsn, $dbuser, $dbpass);
125125
# We want to control commits
126126
$dbh->{'AutoCommit'} = 0;
127127

128+
# PostgreSQL's version is used to determine what queries are required
129+
# to retrieve a given information set.
130+
my$sql_GetVersion =qq{
131+
SELECT cast(substr(version(), 12, 1) as integer) * 10000
132+
+ cast(substr(version(), 14, 1) as integer) * 100
133+
as version;
134+
};
135+
136+
my$sth_GetVersion =$dbh->prepare($sql_GetVersion);
137+
$sth_GetVersion->execute();
138+
my$version =$sth_GetVersion->fetchrow_hashref;
139+
my$pgversion =$version->{'version'};
140+
141+
128142
# control where things get created
129143
my$sql =qq{
130144
SET search_path = public;
@@ -210,13 +224,13 @@ sub findForeignKeys
210224
}
211225

212226
# Start off our column lists
213-
my$key_cols ="$lcolumn_name";
214-
my$ref_cols ="$fcolumn_name";
227+
my$key_cols ="\"$lcolumn_name\"";
228+
my$ref_cols ="\"$fcolumn_name\"";
215229

216230
# Perhaps there is more than a single column
217231
while ($lcolumn_name =shift(@junk)and$fcolumn_name =shift(@junk)) {
218-
$key_cols .=",$lcolumn_name";
219-
$ref_cols .=",$fcolumn_name";
232+
$key_cols .=",\"$lcolumn_name\"";
233+
$ref_cols .=",\"$fcolumn_name\"";
220234
}
221235

222236
my$trigsql =qq{
@@ -252,7 +266,7 @@ sub findForeignKeys
252266
/^RI_FKey_noaction_upd$/&&do {$updatetype ="ON UPDATE NO ACTION";last;};
253267
}
254268

255-
$triglist .="DROP TRIGGER\"$trigname\" ON$tablename;\n";
269+
$triglist .="DROP TRIGGER\"$trigname\" ON\"$tablename\";\n";
256270
}
257271

258272

@@ -264,8 +278,8 @@ sub findForeignKeys
264278

265279
my$fkey =qq{
266280
$triglist
267-
ALTER TABLE$table ADD$constraint FOREIGN KEY ($key_cols)
268-
REFERENCES$ftable($ref_cols)$matchtype$updatetype$deletetype;
281+
ALTER TABLE\"$table\" ADD$constraint FOREIGN KEY ($key_cols)
282+
REFERENCES\"$ftable\"($ref_cols)$matchtype$updatetype$deletetype;
269283
};
270284

271285
# Does the user want to upgrade this sequence?
@@ -291,33 +305,83 @@ MSG
291305
# the corresponding entry in pg_constraint)
292306
subfindUniqueConstraints
293307
{
294-
my$sql =qq{
295-
SELECT ci.relname AS index_name
296-
, ct.relname AS table_name
297-
, pg_catalog.pg_get_indexdef(indexrelid) AS constraint_definition
298-
FROM pg_class AS ci
299-
JOIN pg_index ON (ci.oid = indexrelid)
300-
JOIN pg_class AS ct ON (ct.oid = indrelid)
301-
JOIN pg_catalog.pg_namespace ON (ct.relnamespace = pg_namespace.oid)
302-
WHERE indisunique
303-
AND NOT EXISTS (SELECT TRUE
304-
FROM pg_catalog.pg_depend
305-
JOIN pg_catalog.pg_constraint ON (refobjid = pg_constraint.oid)
306-
WHERE objid = indexrelid
307-
AND objsubid = 0)
308-
AND nspname NOT IN ('pg_catalog', 'pg_toast');
308+
my$sql;
309+
if ($pgversion >= 70400 ) {
310+
$sql =qq{
311+
SELECT pg_index.*, quote_ident(ci.relname) AS index_name
312+
, quote_ident(ct.relname) AS table_name
313+
, pg_catalog.pg_get_indexdef(indexrelid) AS constraint_definition
314+
, indclass
315+
FROM pg_catalog.pg_class AS ci
316+
JOIN pg_catalog.pg_index ON (ci.oid = indexrelid)
317+
JOIN pg_catalog.pg_class AS ct ON (ct.oid = indrelid)
318+
JOIN pg_catalog.pg_namespace ON (ct.relnamespace = pg_namespace.oid)
319+
WHERE indisunique -- Unique indexes only
320+
AND indpred IS NULL -- No Partial Indexes
321+
AND indexprs IS NULL -- No expressional indexes
322+
AND NOT EXISTS (SELECT TRUE
323+
FROM pg_catalog.pg_depend
324+
JOIN pg_catalog.pg_constraint
325+
ON (refobjid = pg_constraint.oid)
326+
WHERE objid = indexrelid
327+
AND objsubid = 0)
328+
AND nspname NOT IN ('pg_catalog', 'pg_toast');
329+
};
330+
}
331+
else
332+
{
333+
$sql =qq{
334+
SELECT pg_index.*, quote_ident(ci.relname) AS index_name
335+
, quote_ident(ct.relname) AS table_name
336+
, pg_catalog.pg_get_indexdef(indexrelid) AS constraint_definition
337+
, indclass
338+
FROM pg_catalog.pg_class AS ci
339+
JOIN pg_catalog.pg_index ON (ci.oid = indexrelid)
340+
JOIN pg_catalog.pg_class AS ct ON (ct.oid = indrelid)
341+
JOIN pg_catalog.pg_namespace ON (ct.relnamespace = pg_namespace.oid)
342+
WHERE indisunique -- Unique indexes only
343+
AND indpred = '' -- No Partial Indexes
344+
AND indproc = 0 -- No expressional indexes
345+
AND NOT EXISTS (SELECT TRUE
346+
FROM pg_catalog.pg_depend
347+
JOIN pg_catalog.pg_constraint
348+
ON (refobjid = pg_constraint.oid)
349+
WHERE objid = indexrelid
350+
AND objsubid = 0)
351+
AND nspname NOT IN ('pg_catalog', 'pg_toast');
352+
};
353+
}
354+
355+
my$opclass_sql =qq{
356+
SELECT TRUE
357+
FROM pg_catalog.pg_opclass
358+
JOIN pg_catalog.pg_am ON (opcamid = pg_am.oid)
359+
WHERE amname = 'btree'
360+
AND pg_opclass.oid = ?
361+
AND pg_opclass.oid < 15000;
309362
};
310363

311364
my$sth =$dbh->prepare($sql) || triggerError($!);
365+
my$opclass_sth =$dbh->prepare($opclass_sql) || triggerError($!);
312366
$sth->execute();
313367

368+
ITERATION:
314369
while (my$row =$sth->fetchrow_hashref)
315370
{
316371
# Fetch vars
317372
my$constraint_name =$row->{'index_name'};
318373
my$table =$row->{'table_name'};
319374
my$columns =$row->{'constraint_definition'};
320375

376+
# Test the opclass is BTree and was not added after installation
377+
my@classes =split(//,$row->{'indclass'});
378+
while (my$class =pop(@classes))
379+
{
380+
$opclass_sth->execute($class);
381+
382+
next ITERATIONif ($sth->rows == 0);
383+
}
384+
321385
# Extract the columns from the index definition
322386
$columns =~s|.*\(([^\)]+)\).*|$1|g;
323387
$columns =~s|([^\s]+)[^\s]+_ops|$1|g;
@@ -358,9 +422,9 @@ MSG
358422
subfindSerials
359423
{
360424
my$sql =qq{
361-
SELECT nspname
362-
, relname
363-
, attname
425+
SELECT nspname AS nspname
426+
, relname AS relname
427+
, attname AS attname
364428
, adsrc
365429
FROM pg_catalog.pg_class as c
366430

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp