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

Commitbad9ce7

Browse files
committed
Add missing /contrib files
1 parent6de7d4f commitbad9ce7

File tree

19 files changed

+1418
-0
lines changed

19 files changed

+1418
-0
lines changed

‎contrib/array/README.array_iterator

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
Array iterator functions, by Massimo Dal Zotto <dz@cs.unitn.it>
2+
Copyright (C) 1999, Massimo Dal Zotto <dz@cs.unitn.it>
3+
4+
This software is distributed under the GNU General Public License
5+
either version 2, or (at your option) any later version.
6+
7+
8+
This loadable module defines a new class of functions which take
9+
an array and a scalar value, iterate a scalar operator over the
10+
elements of the array and the value, and compute a result as
11+
the logical OR or AND of the iteration results.
12+
For example array_int4eq returns true if some of the elements
13+
of an array of int4 is equal to the given value:
14+
15+
array_int4eq({1,2,3}, 1) --> true
16+
array_int4eq({1,2,3}, 4) --> false
17+
18+
If we have defined T array types and O scalar operators we can
19+
define T x O x 2 array functions, each of them has a name like
20+
"array_[all_]<basetype><operation>" and takes an array of type T
21+
iterating the operator O over all the elements. Note however
22+
that some of the possible combination are invalid, for example
23+
the array_int4_like because there is no like operator for int4.
24+
25+
We can then define new operators based on these functions and use
26+
them to write queries with qualification clauses based on the
27+
values of some of the elements of an array.
28+
For example to select rows having some or all element of an array
29+
attribute equal to a given value or matching a regular expression:
30+
31+
create table t(id int4[], txt text[]);
32+
33+
-- select tuples with some id element equal to 123
34+
select * from t where t.id *= 123;
35+
36+
-- select tuples with some txt element matching '[a-z]'
37+
select * from t where t.txt *~ '[a-z]';
38+
39+
-- select tuples with all txt elements matching '^[A-Z]'
40+
select * from t where t.txt[1:3] **~ '^[A-Z]';
41+
42+
The scheme is quite general, each operator which operates on a base type
43+
can be iterated over the elements of an array. It seem to work well but
44+
defining each new operators requires writing a different C function.
45+
Furthermore in each function there are two hardcoded OIDs which reference
46+
a base type and a procedure. Not very portable. Can anyone suggest a
47+
better and more portable way to do it ?
48+
49+
See also array_iterator.sql for an example on how to use this module.
Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
Date: Wed, 1 Apr 1998 15:19:32 -0600 (CST)
2+
From: Hal Snyder <hal@vailsys.com>
3+
To: vmehr@ctp.com
4+
Subject: [QUESTIONS] Re: Spatial data, R-Trees
5+
6+
> From: Vivek Mehra <vmehr@ctp.com>
7+
> Date: Wed, 1 Apr 1998 10:06:50 -0500
8+
9+
> Am just starting out with PostgreSQL and would like to learn more about
10+
> the spatial data handling ablilities of postgreSQL - in terms of using
11+
> R-tree indexes, user defined types, operators and functions.
12+
>
13+
> Would you be able to suggest where I could find some code and SQL to
14+
> look at to create these?
15+
16+
Here's the setup for adding an operator '<@>' to give distance in
17+
statute miles between two points on the earth's surface. Coordinates
18+
are in degrees. Points are taken as (longitude, latitude) and not vice
19+
versa as longitude is closer to the intuitive idea of x-axis and
20+
latitude to y-axis.
21+
22+
There's C source, Makefile for FreeBSD, and SQL for installing and
23+
testing the function.
24+
25+
Let me know if anything looks fishy!
26+
27+
A note on testing C extensions - it seems not enough to drop a function
28+
and re-create it - if I change a function, I have to stop and restart
29+
the backend for the new version to be seen. I guess it would be too
30+
messy to track which functions are added from a .so and do a dlclose
31+
when the last one is dropped.
Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,90 @@
1+
2+
findoidjoins
3+
4+
This program scans a database, and prints oid fields (also regproc fields)
5+
and the tables they join to. CAUTION: it is ver-r-r-y slow on a large
6+
database, or even a not-so-large one. We don't really recommend running
7+
it on anything but an empty database, such as template1.
8+
9+
Uses pgeasy library.
10+
11+
Run on an empty database, it returns the system join relationships (shown
12+
below for 7.0). Note that unexpected matches may indicate bogus entries
13+
in system tables --- don't accept a peculiar match without question.
14+
In particular, a field shown as joining to more than one target table is
15+
probably messed up. In 7.0, the *only* field that should join to more
16+
than one target is pg_description.objoid. (Running make_oidjoins_check
17+
is an easy way to spot fields joining to more than one table, BTW.)
18+
19+
The shell script make_oidjoins_check converts findoidjoins' output
20+
into an SQL script that checks for dangling links (entries in an
21+
OID or REGPROC column that don't match any row in the expected table).
22+
Note that fields joining to more than one table are NOT processed.
23+
24+
The result of make_oidjoins_check should be installed as the "oidjoins"
25+
regression test. The oidjoins test should be updated after any
26+
revision in the patterns of cross-links between system tables.
27+
(Ideally we'd just regenerate the script as part of the regression
28+
tests themselves, but that seems too slow...)
29+
30+
---------------------------------------------------------------------------
31+
32+
Join pg_aggregate.aggtransfn1 => pg_proc.oid
33+
Join pg_aggregate.aggtransfn2 => pg_proc.oid
34+
Join pg_aggregate.aggfinalfn => pg_proc.oid
35+
Join pg_aggregate.aggbasetype => pg_type.oid
36+
Join pg_aggregate.aggtranstype1 => pg_type.oid
37+
Join pg_aggregate.aggtranstype2 => pg_type.oid
38+
Join pg_aggregate.aggfinaltype => pg_type.oid
39+
Join pg_am.amgettuple => pg_proc.oid
40+
Join pg_am.aminsert => pg_proc.oid
41+
Join pg_am.amdelete => pg_proc.oid
42+
Join pg_am.ambeginscan => pg_proc.oid
43+
Join pg_am.amrescan => pg_proc.oid
44+
Join pg_am.amendscan => pg_proc.oid
45+
Join pg_am.ammarkpos => pg_proc.oid
46+
Join pg_am.amrestrpos => pg_proc.oid
47+
Join pg_am.ambuild => pg_proc.oid
48+
Join pg_am.amcostestimate => pg_proc.oid
49+
Join pg_amop.amopid => pg_am.oid
50+
Join pg_amop.amopclaid => pg_opclass.oid
51+
Join pg_amop.amopopr => pg_operator.oid
52+
Join pg_amproc.amid => pg_am.oid
53+
Join pg_amproc.amopclaid => pg_opclass.oid
54+
Join pg_amproc.amproc => pg_proc.oid
55+
Join pg_attribute.attrelid => pg_class.oid
56+
Join pg_attribute.atttypid => pg_type.oid
57+
Join pg_class.reltype => pg_type.oid
58+
Join pg_class.relam => pg_am.oid
59+
Join pg_description.objoid => pg_proc.oid
60+
Join pg_description.objoid => pg_type.oid
61+
Join pg_index.indexrelid => pg_class.oid
62+
Join pg_index.indrelid => pg_class.oid
63+
Join pg_opclass.opcdeftype => pg_type.oid
64+
Join pg_operator.oprleft => pg_type.oid
65+
Join pg_operator.oprright => pg_type.oid
66+
Join pg_operator.oprresult => pg_type.oid
67+
Join pg_operator.oprcom => pg_operator.oid
68+
Join pg_operator.oprnegate => pg_operator.oid
69+
Join pg_operator.oprlsortop => pg_operator.oid
70+
Join pg_operator.oprrsortop => pg_operator.oid
71+
Join pg_operator.oprcode => pg_proc.oid
72+
Join pg_operator.oprrest => pg_proc.oid
73+
Join pg_operator.oprjoin => pg_proc.oid
74+
Join pg_proc.prolang => pg_language.oid
75+
Join pg_proc.prorettype => pg_type.oid
76+
Join pg_rewrite.ev_class => pg_class.oid
77+
Join pg_statistic.starelid => pg_class.oid
78+
Join pg_statistic.staop => pg_operator.oid
79+
Join pg_trigger.tgrelid => pg_class.oid
80+
Join pg_trigger.tgfoid => pg_proc.oid
81+
Join pg_type.typrelid => pg_class.oid
82+
Join pg_type.typelem => pg_type.oid
83+
Join pg_type.typinput => pg_proc.oid
84+
Join pg_type.typoutput => pg_proc.oid
85+
Join pg_type.typreceive => pg_proc.oid
86+
Join pg_type.typsend => pg_proc.oid
87+
88+
---------------------------------------------------------------------------
89+
90+
Bruce Momjian (root@candle.pha.pa.us)

‎contrib/fulltextindex/README.fti

Lines changed: 197 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,197 @@
1+
An attempt at some sort of Full Text Indexing for PostgreSQL.
2+
3+
The included software is an attempt to add some sort of Full Text Indexing
4+
support to PostgreSQL. I mean by this that we can ask questions like:
5+
6+
Give me all rows that have 'still' and 'nash' in the 'artist' field.
7+
8+
Ofcourse we can write this as:
9+
10+
select * from cds where artist ~* 'stills' and artist ~* 'nash';
11+
12+
But this does not use any indices, and therefore, if your database
13+
gets very large, it will not have very high performance (the above query
14+
requires at least one sequential scan, it probably takes 2 due to the
15+
self-join).
16+
17+
The approach used by this add-on is to define a trigger on the table and
18+
column you want to do this queries on. On every insert in the table, it
19+
takes the value in the specified column, breaks the text in this column
20+
up into pieces, and stores all sub-strings into another table, together
21+
with a reference to the row in the original table that contained this
22+
sub-string (it uses the oid of that row).
23+
24+
By now creating an index over the 'fti-table', we can search for
25+
substrings that occur in the original table. By making a join between
26+
the fti-table and the orig-table, we can get the actual rows we want
27+
(this can also be done by using subselects, and maybe there're other
28+
ways too).
29+
30+
The trigger code also allows an array called StopWords, that prevents
31+
certain words from being indexed.
32+
33+
As an example we take the previous query, where we assume we have all
34+
sub-strings in the table 'cds-fti':
35+
36+
select c.*
37+
from cds c, cds-fti f1, cds-fti f2
38+
wheref1.string ~ '^stills' and
39+
f2.string ~ '^nash' and
40+
f1.id = c.oid and
41+
f2.id = c.oid ;
42+
43+
We can use the ~ (case-sensitive regular expression) here, because of
44+
the way sub-strings are built: from right to left, ie. house -> 'se' +
45+
'use' + 'ouse' + 'house'. If a ~ search starts with a ^ (match start of
46+
string), btree indices can be used by PostgreSQL.
47+
48+
Now, how do we create the trigger that maintains the fti-table? First: the
49+
fti-table should have the following schema:
50+
51+
create cds-fti ( string varchar(N), id oid );
52+
53+
Don't change the *names* of the columns, the varchar() can in fact also
54+
be of text-type. If you do use varchar, make sure the largest possible
55+
sub-string will fit.
56+
57+
The create the function that contains the trigger::
58+
59+
create function fti() returns opaque as
60+
'/path/to/fti.so' language 'newC';
61+
62+
And finally define the trigger on the 'cds' table:
63+
64+
create trigger cds-fti-trigger after update or insert or delete on cds
65+
for each row execute procedure fti(cds-fti, artist);
66+
67+
Here, the trigger will be defined on table 'cds', it will create
68+
sub-strings from the field 'artist', and it will place those sub-strings
69+
in the table 'cds-fti'.
70+
71+
Now populate the table 'cds'. This will also populate the table 'cds-fti'.
72+
It's fastest to populate the table *before* you create the indices.
73+
74+
Before you start using the system, you should at least have the following
75+
indices:
76+
77+
create index cds-fti-idx on cds-fti (string, id);
78+
create index cds-oid-idx on cds (oid);
79+
80+
To get the most performance out of this, you should have 'cds-fti'
81+
clustered on disk, ie. all rows with the same sub-strings should be
82+
close to each other. There are 3 ways of doing this:
83+
84+
1. After you have created the indices, execute 'cluster cds-fti-idx on cds-fti'.
85+
2. Do a 'select * into tmp-table from cds-fti order by string' *before*
86+
you create the indices, then 'drop table cds-fti' and
87+
'alter table tmp-table rename to cds-fti'
88+
3. *Before* creating indices, dump the contents of the cds-fti table using
89+
'pg_dump -a -t cds-fti dbase-name', remove the \connect
90+
from the beginning and the \. from the end, and sort it using the
91+
UNIX 'sort' program, and reload the data.
92+
93+
Method 1 is very slow, 2 a lot faster, and for very large tables, 3 is
94+
preferred.
95+
96+
97+
BENCH:
98+
~~~~~
99+
100+
Maarten Boekhold <maartenb@dutepp0.et.tudelft.nl>
101+
The following data was generated by the 'timings.sh' script included
102+
in this directory. It uses a very large table with music-related
103+
articles as a source for the fti-table. The tables used are:
104+
105+
product : contains product information : 540.429 rows
106+
artist_fti : fti table for product : 4.501.321 rows
107+
clustered : same as above, only clustered : 4.501.321 rows
108+
109+
A sequential scan of the artist_fti table (and thus also the clustered table)
110+
takes around 6:16 minutes....
111+
112+
Unfortunately I cannot probide anybody else with this test-date, since I
113+
am not allowed to redistribute the data (it's a database being sold by
114+
a couple of wholesale companies). Anyways, it's megabytes, so you probably
115+
wouldn't want it in this distribution anyways.
116+
117+
I haven't tested this with less data.
118+
119+
The test-machine is a Pentium 133, 64 MB, Linux 2.0.32 with the database
120+
on a 'QUANTUM BIGFOOT_CY4320A, 4134MB w/67kB Cache, CHS=8960/15/63'. This
121+
is a very slow disk.
122+
123+
The postmaster was running with:
124+
125+
postmaster -i -b /usr/local/pgsql/bin/postgres -S 1024 -B 256 \
126+
-o -o /usr/local/pgsql/debug-output -F -d 1
127+
128+
('trashing' means a 'select count(*) from artist_fti' to completely trash
129+
any disk-caches and buffers....)
130+
131+
TESTING ON UNCLUSTERED FTI
132+
trashing
133+
1: ^lapton and ^ric : 0.050u 0.000s 5m37.484s 0.01%
134+
2: ^lapton and ^ric : 0.050u 0.030s 5m32.447s 0.02%
135+
3: ^lapton and ^ric : 0.030u 0.020s 5m28.822s 0.01%
136+
trashing
137+
1: ^lling and ^tones : 0.020u 0.030s 0m54.313s 0.09%
138+
2: ^lling and ^tones : 0.040u 0.030s 0m5.057s 1.38%
139+
3: ^lling and ^tones : 0.010u 0.050s 0m2.072s 2.89%
140+
trashing
141+
1: ^aughan and ^evie : 0.020u 0.030s 0m26.241s 0.19%
142+
2: ^aughan and ^evie : 0.050u 0.010s 0m1.316s 4.55%
143+
3: ^aughan and ^evie : 0.030u 0.020s 0m1.029s 4.85%
144+
trashing
145+
1: ^lling : 0.040u 0.010s 0m55.104s 0.09%
146+
2: ^lling : 0.030u 0.030s 0m4.716s 1.27%
147+
3: ^lling : 0.040u 0.010s 0m2.157s 2.31%
148+
trashing
149+
1: ^stev and ^ray and ^vaugh : 0.040u 0.000s 1m5.630s 0.06%
150+
2: ^stev and ^ray and ^vaugh : 0.050u 0.020s 1m3.561s 0.11%
151+
3: ^stev and ^ray and ^vaugh : 0.050u 0.010s 1m5.923s 0.09%
152+
trashing
153+
1: ^lling (no join) : 0.050u 0.020s 0m24.139s 0.28%
154+
2: ^lling (no join) : 0.040u 0.040s 0m1.087s 7.35%
155+
3: ^lling (no join) : 0.020u 0.030s 0m0.772s 6.48%
156+
trashing
157+
1: ^vaughan (no join) : 0.040u 0.030s 0m9.075s 0.77%
158+
2: ^vaughan (no join) : 0.030u 0.010s 0m0.609s 6.56%
159+
3: ^vaughan (no join) : 0.040u 0.010s 0m0.503s 9.94%
160+
trashing
161+
1: ^rol (no join) : 0.020u 0.030s 0m49.898s 0.10%
162+
2: ^rol (no join) : 0.030u 0.020s 0m3.136s 1.59%
163+
3: ^rol (no join) : 0.030u 0.020s 0m1.231s 4.06%
164+
165+
TESTING ON CLUSTERED FTI
166+
trashing
167+
1: ^lapton and ^ric : 0.020u 0.020s 2m17.120s 0.02%
168+
2: ^lapton and ^ric : 0.030u 0.020s 2m11.767s 0.03%
169+
3: ^lapton and ^ric : 0.040u 0.010s 2m8.128s 0.03%
170+
trashing
171+
1: ^lling and ^tones : 0.020u 0.030s 0m18.179s 0.27%
172+
2: ^lling and ^tones : 0.030u 0.010s 0m1.897s 2.10%
173+
3: ^lling and ^tones : 0.040u 0.010s 0m1.619s 3.08%
174+
trashing
175+
1: ^aughan and ^evie : 0.070u 0.010s 0m11.765s 0.67%
176+
2: ^aughan and ^evie : 0.040u 0.010s 0m1.198s 4.17%
177+
3: ^aughan and ^evie : 0.030u 0.020s 0m0.872s 5.73%
178+
trashing
179+
1: ^lling : 0.040u 0.000s 0m28.623s 0.13%
180+
2: ^lling : 0.030u 0.010s 0m2.339s 1.70%
181+
3: ^lling : 0.030u 0.010s 0m1.975s 2.02%
182+
trashing
183+
1: ^stev and ^ray and ^vaugh : 0.020u 0.010s 0m17.667s 0.16%
184+
2: ^stev and ^ray and ^vaugh : 0.030u 0.010s 0m3.745s 1.06%
185+
3: ^stev and ^ray and ^vaugh : 0.030u 0.020s 0m3.439s 1.45%
186+
trashing
187+
1: ^lling (no join) : 0.020u 0.040s 0m2.218s 2.70%
188+
2: ^lling (no join) : 0.020u 0.020s 0m0.506s 7.90%
189+
3: ^lling (no join) : 0.030u 0.030s 0m0.510s 11.76%
190+
trashing
191+
1: ^vaughan (no join) : 0.040u 0.050s 0m2.048s 4.39%
192+
2: ^vaughan (no join) : 0.030u 0.020s 0m0.332s 15.04%
193+
3: ^vaughan (no join) : 0.040u 0.010s 0m0.318s 15.72%
194+
trashing
195+
1: ^rol (no join) : 0.020u 0.030s 0m2.384s 2.09%
196+
2: ^rol (no join) : 0.020u 0.030s 0m0.676s 7.39%
197+
3: ^rol (no join) : 0.020u 0.030s 0m0.697s 7.17%

‎contrib/isbn_issn/README.isbn_issn

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
2+
ISBN (books) and ISSN (serials)
3+
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4+
5+
This directory contains definitions for a couple of PostgreSQL
6+
external types, for a couple of international-standard namespaces:
7+
ISBN (books) and ISSN (serials). Rather than just using a char()
8+
member of the appropriate length, I wanted my database to include
9+
the validity-checking that both these numbering systems were designed
10+
to encompass. A little bit of research revealed the formulae
11+
for computing the check digits, and I also included some validity
12+
constraints on the number of hyphens.
13+
14+
The internal representation of these types is intended to be
15+
compatible with `char16', in the (perhaps vain) hope that
16+
this will make it possible to create indices of these types
17+
using char16_ops.
18+
19+
These are based on Tom Ivar Helbekkmo's IP address type definition,
20+
from which I have copied the entire form of the implementation.
21+
22+
Garrett A. Wollman, August 1998

0 commit comments

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