From 3b3251cb95001be2f911e16280fd99e7dd559620 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 2 Dec 2007 22:33:20 +0000 Subject: Ensure that all and tags have IDs. This is needed to ensure that the resulting webpages have predictable URLs, instead of ever-changing numeric IDs. The new contrib docs were the biggest offender, but some old stuff had the problem too. Also, rename a couple of new contrib sgml files for consistency's sake. --- doc/src/sgml/adminpack.sgml | 8 +- doc/src/sgml/btree-gist.sgml | 13 +- doc/src/sgml/buffercache.sgml | 121 ------------------- doc/src/sgml/contrib.sgml | 10 +- doc/src/sgml/datetime.sgml | 4 +- doc/src/sgml/dblink.sgml | 69 +++++------ doc/src/sgml/filelist.sgml | 10 +- doc/src/sgml/freespacemap.sgml | 243 -------------------------------------- doc/src/sgml/install-win32.sgml | 6 +- doc/src/sgml/pgbuffercache.sgml | 120 +++++++++++++++++++ doc/src/sgml/pgfreespacemap.sgml | 242 +++++++++++++++++++++++++++++++++++++ doc/src/sgml/pgstandby.sgml | 247 ++++++++++++++++++++++++++++++++++++++ doc/src/sgml/pgtrgm.sgml | 213 +++++++++++++++++++++++++++++++++ doc/src/sgml/standby.sgml | 249 --------------------------------------- doc/src/sgml/trgm.sgml | 214 --------------------------------- 15 files changed, 875 insertions(+), 894 deletions(-) delete mode 100644 doc/src/sgml/buffercache.sgml delete mode 100644 doc/src/sgml/freespacemap.sgml create mode 100644 doc/src/sgml/pgbuffercache.sgml create mode 100644 doc/src/sgml/pgfreespacemap.sgml create mode 100644 doc/src/sgml/pgstandby.sgml create mode 100644 doc/src/sgml/pgtrgm.sgml delete mode 100644 doc/src/sgml/standby.sgml delete mode 100644 doc/src/sgml/trgm.sgml diff --git a/doc/src/sgml/adminpack.sgml b/doc/src/sgml/adminpack.sgml index 10816f5c24d..bdbe44b4f98 100644 --- a/doc/src/sgml/adminpack.sgml +++ b/doc/src/sgml/adminpack.sgml @@ -1,5 +1,10 @@ - + adminpack + + + adminpack + + adminpack is a PostgreSQL standard module that implements a number of support functions which pgAdmin and other administration and management tools @@ -29,4 +34,3 @@ - diff --git a/doc/src/sgml/btree-gist.sgml b/doc/src/sgml/btree-gist.sgml index 4e1126e33c3..c545c6ce7b4 100644 --- a/doc/src/sgml/btree-gist.sgml +++ b/doc/src/sgml/btree-gist.sgml @@ -1,14 +1,12 @@ - - - - btree-gist - btree-gist is a B-Tree implementation using GiST that supports the int2, int4, + btree_gist is a B-Tree implementation using GiST that supports the int2, int4, int8, float4, float8 timestamp with/without time zone, time with/without time zone, date, interval, oid, money, macaddr, char, varchar/text, bytea, numeric, bit, varbit and inet/cidr types. @@ -37,4 +35,3 @@ - diff --git a/doc/src/sgml/buffercache.sgml b/doc/src/sgml/buffercache.sgml deleted file mode 100644 index 1347c75ba32..00000000000 --- a/doc/src/sgml/buffercache.sgml +++ /dev/null @@ -1,121 +0,0 @@ - - pg_buffercache - - - pg_buffercache - - - - pg_buffercache module provides the means for examining - what's happening to the buffercache at any given time without having to - restart or rebuild the server with debugging code added. The intent is to - do for the buffercache what pg_locks does for locks. - - - This module consists of a C function pg_buffercache_pages() - that returns a set of records, plus a view pg_buffercache - to wrapper the function. - - - By default public access is REVOKED from both of these, just in case there - are security issues lurking. - - - - Notes - - The definition of the columns exposed in the view is: - - - Column | references | Description - ----------------+----------------------+------------------------------------ - bufferid | | Id, 1..shared_buffers. - relfilenode | pg_class.relfilenode | Refilenode of the relation. - reltablespace | pg_tablespace.oid | Tablespace oid of the relation. - reldatabase | pg_database.oid | Database for the relation. - relblocknumber | | Offset of the page in the relation. - isdirty | | Is the page dirty? - usagecount | | Page LRU count - - - There is one row for each buffer in the shared cache. Unused buffers are - shown with all fields null except bufferid. - - - Because the cache is shared by all the databases, there are pages from - relations not belonging to the current database. - - - When the pg_buffercache view is accessed, internal buffer manager locks are - taken, and a copy of the buffer cache data is made for the view to display. - This ensures that the view produces a consistent set of results, while not - blocking normal buffer activity longer than necessary. Nonetheless there - could be some impact on database performance if this view is read often. - - - - - Sample output - - regression=# \d pg_buffercache; - View "public.pg_buffercache" - Column | Type | Modifiers - ----------------+----------+----------- - bufferid | integer | - relfilenode | oid | - reltablespace | oid | - reldatabase | oid | - relblocknumber | bigint | - isdirty | boolean | - usagecount | smallint | - - View definition: - SELECT p.bufferid, p.relfilenode, p.reltablespace, p.reldatabase, - p.relblocknumber, p.isdirty, p.usagecount - FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, - reltablespace oid, reldatabase oid, relblocknumber bigint, - isdirty boolean, usagecount smallint); - - regression=# SELECT c.relname, count(*) AS buffers - FROM pg_class c INNER JOIN pg_buffercache b - ON b.relfilenode = c.relfilenode INNER JOIN pg_database d - ON (b.reldatabase = d.oid AND d.datname = current_database()) - GROUP BY c.relname - ORDER BY 2 DESC LIMIT 10; - relname | buffers - ---------------------------------+--------- - tenk2 | 345 - tenk1 | 141 - pg_proc | 46 - pg_class | 45 - pg_attribute | 43 - pg_class_relname_nsp_index | 30 - pg_proc_proname_args_nsp_index | 28 - pg_attribute_relid_attnam_index | 26 - pg_depend | 22 - pg_depend_reference_index | 20 - (10 rows) - - regression=# - - - - - Authors - - - - Mark Kirkwood markir@paradise.net.nz - - - - Design suggestions: Neil Conway neilc@samurai.com - - - Debugging advice: Tom Lane tgl@sss.pgh.pa.us - - - - - - diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index cf4495e3b89..f077e55bb85 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -1,4 +1,4 @@ - + Additional Supplied Modules @@ -95,13 +95,13 @@ psql -d dbname -f SHAREDIR/contrib/module.sql &oid2name; &pageinspect; &pgbench; - &buffercache; + &pgbuffercache; &pgcrypto; - &freespacemap; + &pgfreespacemap; &pgrowlocks; - &standby; + &pgstandby; &pgstattuple; - &trgm; + &pgtrgm; &seg; &sslinfo; &tablefunc; diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml index cbdb673b71a..364e1411cb7 100644 --- a/doc/src/sgml/datetime.sgml +++ b/doc/src/sgml/datetime.sgml @@ -1,4 +1,4 @@ - + Date/Time Support @@ -20,7 +20,7 @@ dates and times. - + Date/Time Input Interpretation diff --git a/doc/src/sgml/dblink.sgml b/doc/src/sgml/dblink.sgml index 095d600099b..cccde0bbbc9 100644 --- a/doc/src/sgml/dblink.sgml +++ b/doc/src/sgml/dblink.sgml @@ -1,14 +1,16 @@ - dblink + + + dblink + + - dblink is a contrib module which allows connections with + dblink is a module which allows connections with other databases. - - - + dblink_connect opens a persistent connection to a remote database @@ -68,8 +70,7 @@ - - + dblink_disconnect closes a persistent connection to a remote database @@ -117,8 +118,7 @@ - - + dblink_open opens a cursor on a remote database @@ -211,8 +211,7 @@ - - + dblink_fetch returns a set from an open cursor on a remote database @@ -327,8 +326,7 @@ - - + dblink_close closes a cursor on a remote database @@ -425,8 +423,7 @@ - - + dblink_exec executes an UPDATE/INSERT/DELETE on a remote database @@ -527,8 +524,7 @@ - - + dblink_current_query returns the current query string @@ -567,8 +563,7 @@ - - + dblink_get_pkey returns the position and field names of a relation's @@ -618,8 +613,7 @@ - - + dblink_build_sql_insert @@ -695,8 +689,7 @@ - - + dblink_build_sql_delete builds a delete statement using supplied values for selection @@ -770,8 +763,7 @@ - - + dblink_build_sql_update builds an update statement using a local tuple, replacing @@ -846,8 +838,7 @@ - - + dblink_get_connections returns a text array of all active named dblink connections @@ -881,8 +872,7 @@ - - + dblink_is_busy checks to see if named connection is busy with an async query @@ -922,8 +912,7 @@ - - + dblink_cancel_query cancels any active query on the named connection @@ -961,8 +950,7 @@ - - + dblink_error_message gets last error message on the named connection @@ -1000,8 +988,7 @@ - - + dblink returns a set from a remote database @@ -1146,8 +1133,7 @@ - - + dblink_send_query sends an async query to a remote database @@ -1192,14 +1178,13 @@ SELECT dblink_connect('dtest1', 'dbname=contrib_regression'); SELECT * FROM - dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3') AS t1; + dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3') AS t1; - - + dblink_get_result gets an async query result @@ -1259,7 +1244,7 @@ (1 row) contrib_regression=# SELECT * from - contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1; + contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1; t1 ---- 1 @@ -1279,7 +1264,7 @@ (0 rows) contrib_regression=# SELECT * from - dblink_send_query('dtest1', 'select * from foo where f1 < 3; select * from foo where f1 > 6') as t1; + dblink_send_query('dtest1', 'select * from foo where f1 < 3; select * from foo where f1 > 6') as t1; t1 ---- 1 diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 1a5064660bc..4c229d19841 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -1,4 +1,4 @@ - + @@ -109,13 +109,13 @@ - + - + - + - + diff --git a/doc/src/sgml/freespacemap.sgml b/doc/src/sgml/freespacemap.sgml deleted file mode 100644 index 70b27415524..00000000000 --- a/doc/src/sgml/freespacemap.sgml +++ /dev/null @@ -1,243 +0,0 @@ - - pgfreespacemap - - - pgfreespacemap - - - - This modules provides the means for examining the free space map (FSM). It - consists of two C functions: pg_freespacemap_relations() - and pg_freespacemap_pages() that return a set - of records, plus two views pg_freespacemap_relations and - pg_freespacemap_pages for more user-friendly access to - the functions. - - - The module provides the ability to examine the contents of the free space - map, without having to restart or rebuild the server with additional - debugging code. - - - By default public access is REVOKED from the functions and views, just in - case there are security issues present in the code. - - - - Notes - - The definitions for the columns exposed in the views are: - - - - pg_freespacemap_relations - - - - Column - references - Description - - - - - reltablespace - pg_tablespace.oid - Tablespace oid of the relation. - - - reldatabase - pg_database.oid - Database oid of the relation. - - - relfilenode - pg_class.relfilenode - Relfilenode of the relation. - - - avgrequest - - Moving average of free space requests (NULL for indexes) - - - interestingpages - - Count of pages last reported as containing useful free space. - - - storedpages - - Count of pages actually stored in free space map. - - - nextpage - - Page index (from 0) to start next search at. - - - -
- - - pg_freespacemap_pages - - - - Column - references - Description - - - - - reltablespace - pg_tablespace.oid - Tablespace oid of the relation. - - - reldatabase - pg_database.oid - Database oid of the relation. - - - relfilenode - pg_class.relfilenode - Relfilenode of the relation. - - - relblocknumber - - Page number in the relation. - - - bytes - - Free bytes in the page, or NULL for an index page (see below). - - - -
- - - For pg_freespacemap_relations, there is one row for each - relation in the free space map. storedpages is the - number of pages actually stored in the map, while - interestingpages is the number of pages the last VACUUM - thought had useful amounts of free space. - - - If storedpages is consistently less than interestingpages - then it'd be a good idea to increase max_fsm_pages. Also, - if the number of rows in pg_freespacemap_relations is - close to max_fsm_relations, then you should consider - increasing max_fsm_relations. - - - For pg_freespacemap_pages, there is one row for each page - in the free space map. The number of rows for a relation will match the - storedpages column in - pg_freespacemap_relations. - - - For indexes, what is tracked is entirely-unused pages, rather than free - space within pages. Therefore, the average request size and free bytes - within a page are not meaningful, and are shown as NULL. - - - Because the map is shared by all the databases, it will include relations - not belonging to the current database. - - - When either of the views are accessed, internal free space map locks are - taken, and a copy of the map data is made for them to display. - This ensures that the views produce a consistent set of results, while not - blocking normal activity longer than necessary. Nonetheless there - could be some impact on database performance if they are read often. - - - - - Sample output - pg_freespacemap_relations - -regression=# \d pg_freespacemap_relations -View "public.pg_freespacemap_relations" - Column | Type | Modifiers -------------------+---------+----------- - reltablespace | oid | - reldatabase | oid | - relfilenode | oid | - avgrequest | integer | - interestingpages | integer | - storedpages | integer | - nextpage | integer | -View definition: - SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.avgrequest, p.interestingpages, p.storedpages, p.nextpage - FROM pg_freespacemap_relations() p(reltablespace oid, reldatabase oid, relfilenode oid, avgrequest integer, interestingpages integer, storedpages integer, nextpage integer); - -regression=# SELECT c.relname, r.avgrequest, r.interestingpages, r.storedpages - FROM pg_freespacemap_relations r INNER JOIN pg_class c - ON c.relfilenode = r.relfilenode INNER JOIN pg_database d - ON r.reldatabase = d.oid AND (d.datname = current_database()) - ORDER BY r.storedpages DESC LIMIT 10; - relname | avgrequest | interestingpages | storedpages ----------------------------------+------------+------------------+------------- - onek | 256 | 109 | 109 - pg_attribute | 167 | 93 | 93 - pg_class | 191 | 49 | 49 - pg_attribute_relid_attnam_index | | 48 | 48 - onek2 | 256 | 37 | 37 - pg_depend | 95 | 26 | 26 - pg_type | 199 | 16 | 16 - pg_rewrite | 1011 | 13 | 13 - pg_class_relname_nsp_index | | 10 | 10 - pg_proc | 302 | 8 | 8 -(10 rows) - - - - - Sample output - pg_freespacemap_pages - -regression=# \d pg_freespacemap_pages - View "public.pg_freespacemap_pages" - Column | Type | Modifiers -----------------+---------+----------- - reltablespace | oid | - reldatabase | oid | - relfilenode | oid | - relblocknumber | bigint | - bytes | integer | -View definition: - SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.relblocknumber, p.bytes - FROM pg_freespacemap_pages() p(reltablespace oid, reldatabase oid, relfilenode oid, relblocknumber bigint, bytes integer); - -regression=# SELECT c.relname, p.relblocknumber, p.bytes - FROM pg_freespacemap_pages p INNER JOIN pg_class c - ON c.relfilenode = p.relfilenode INNER JOIN pg_database d - ON (p.reldatabase = d.oid AND d.datname = current_database()) - ORDER BY c.relname LIMIT 10; - relname | relblocknumber | bytes ---------------+----------------+------- - a_star | 0 | 8040 - abstime_tbl | 0 | 7908 - aggtest | 0 | 8008 - altinhoid | 0 | 8128 - altstartwith | 0 | 8128 - arrtest | 0 | 7172 - b_star | 0 | 7976 - box_tbl | 0 | 7912 - bt_f8_heap | 54 | 7728 - bt_i4_heap | 49 | 8008 -(10 rows) - - - - - Author - - Mark Kirkwood markir@paradise.net.nz - - - - diff --git a/doc/src/sgml/install-win32.sgml b/doc/src/sgml/install-win32.sgml index a87f52d5b76..a1588417472 100644 --- a/doc/src/sgml/install-win32.sgml +++ b/doc/src/sgml/install-win32.sgml @@ -1,4 +1,4 @@ - + Installation on <productname>Windows</productname> @@ -40,7 +40,7 @@ Windows 98. - + Building with <productname>Visual C++ 2005</productname> @@ -354,7 +354,7 @@ - + Building <application>libpq</application> with <productname>Visual C++</productname> or <productname>Borland C++</productname> diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml new file mode 100644 index 00000000000..e4ecc8f78ce --- /dev/null +++ b/doc/src/sgml/pgbuffercache.sgml @@ -0,0 +1,120 @@ + + pg_buffercache + + + pg_buffercache + + + + The pg_buffercache module provides a means for examining + what's happening to the buffercache at any given time without having to + restart or rebuild the server with debugging code added. The intent is to + do for the buffercache what pg_locks does for locks. + + + This module consists of a C function pg_buffercache_pages() + that returns a set of records, plus a view pg_buffercache + to wrapper the function. + + + By default public access is REVOKED from both of these, just in case there + are security issues lurking. + + + + Notes + + The definition of the columns exposed in the view is: + + + Column | references | Description + ----------------+----------------------+------------------------------------ + bufferid | | Id, 1..shared_buffers. + relfilenode | pg_class.relfilenode | Refilenode of the relation. + reltablespace | pg_tablespace.oid | Tablespace oid of the relation. + reldatabase | pg_database.oid | Database for the relation. + relblocknumber | | Offset of the page in the relation. + isdirty | | Is the page dirty? + usagecount | | Page LRU count + + + There is one row for each buffer in the shared cache. Unused buffers are + shown with all fields null except bufferid. + + + Because the cache is shared by all the databases, there are pages from + relations not belonging to the current database. + + + When the pg_buffercache view is accessed, internal buffer manager locks are + taken, and a copy of the buffer cache data is made for the view to display. + This ensures that the view produces a consistent set of results, while not + blocking normal buffer activity longer than necessary. Nonetheless there + could be some impact on database performance if this view is read often. + + + + + Sample output + + regression=# \d pg_buffercache; + View "public.pg_buffercache" + Column | Type | Modifiers + ----------------+----------+----------- + bufferid | integer | + relfilenode | oid | + reltablespace | oid | + reldatabase | oid | + relblocknumber | bigint | + isdirty | boolean | + usagecount | smallint | + + View definition: + SELECT p.bufferid, p.relfilenode, p.reltablespace, p.reldatabase, + p.relblocknumber, p.isdirty, p.usagecount + FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, + reltablespace oid, reldatabase oid, relblocknumber bigint, + isdirty boolean, usagecount smallint); + + regression=# SELECT c.relname, count(*) AS buffers + FROM pg_class c INNER JOIN pg_buffercache b + ON b.relfilenode = c.relfilenode INNER JOIN pg_database d + ON (b.reldatabase = d.oid AND d.datname = current_database()) + GROUP BY c.relname + ORDER BY 2 DESC LIMIT 10; + relname | buffers + ---------------------------------+--------- + tenk2 | 345 + tenk1 | 141 + pg_proc | 46 + pg_class | 45 + pg_attribute | 43 + pg_class_relname_nsp_index | 30 + pg_proc_proname_args_nsp_index | 28 + pg_attribute_relid_attnam_index | 26 + pg_depend | 22 + pg_depend_reference_index | 20 + (10 rows) + + regression=# + + + + + Authors + + + + Mark Kirkwood markir@paradise.net.nz + + + + Design suggestions: Neil Conway neilc@samurai.com + + + Debugging advice: Tom Lane tgl@sss.pgh.pa.us + + + + + diff --git a/doc/src/sgml/pgfreespacemap.sgml b/doc/src/sgml/pgfreespacemap.sgml new file mode 100644 index 00000000000..66748c3ef4b --- /dev/null +++ b/doc/src/sgml/pgfreespacemap.sgml @@ -0,0 +1,242 @@ + + pg_freespacemap + + + pg_freespacemap + + + + This module provides a means for examining the free space map (FSM). It + consists of two C functions: pg_freespacemap_relations() + and pg_freespacemap_pages() that return a set + of records, plus two views pg_freespacemap_relations and + pg_freespacemap_pages for more user-friendly access to + the functions. + + + The module provides the ability to examine the contents of the free space + map, without having to restart or rebuild the server with additional + debugging code. + + + By default public access is REVOKED from the functions and views, just in + case there are security issues present in the code. + + + + Notes + + The definitions for the columns exposed in the views are: + + + + pg_freespacemap_relations + + + + Column + references + Description + + + + + reltablespace + pg_tablespace.oid + Tablespace oid of the relation. + + + reldatabase + pg_database.oid + Database oid of the relation. + + + relfilenode + pg_class.relfilenode + Relfilenode of the relation. + + + avgrequest + + Moving average of free space requests (NULL for indexes) + + + interestingpages + + Count of pages last reported as containing useful free space. + + + storedpages + + Count of pages actually stored in free space map. + + + nextpage + + Page index (from 0) to start next search at. + + + +
+ + + pg_freespacemap_pages + + + + Column + references + Description + + + + + reltablespace + pg_tablespace.oid + Tablespace oid of the relation. + + + reldatabase + pg_database.oid + Database oid of the relation. + + + relfilenode + pg_class.relfilenode + Relfilenode of the relation. + + + relblocknumber + + Page number in the relation. + + + bytes + + Free bytes in the page, or NULL for an index page (see below). + + + +
+ + + For pg_freespacemap_relations, there is one row for each + relation in the free space map. storedpages is the + number of pages actually stored in the map, while + interestingpages is the number of pages the last VACUUM + thought had useful amounts of free space. + + + If storedpages is consistently less than interestingpages + then it'd be a good idea to increase max_fsm_pages. Also, + if the number of rows in pg_freespacemap_relations is + close to max_fsm_relations, then you should consider + increasing max_fsm_relations. + + + For pg_freespacemap_pages, there is one row for each page + in the free space map. The number of rows for a relation will match the + storedpages column in + pg_freespacemap_relations. + + + For indexes, what is tracked is entirely-unused pages, rather than free + space within pages. Therefore, the average request size and free bytes + within a page are not meaningful, and are shown as NULL. + + + Because the map is shared by all the databases, it will include relations + not belonging to the current database. + + + When either of the views are accessed, internal free space map locks are + taken, and a copy of the map data is made for them to display. + This ensures that the views produce a consistent set of results, while not + blocking normal activity longer than necessary. Nonetheless there + could be some impact on database performance if they are read often. + + + + + Sample output - pg_freespacemap_relations + +regression=# \d pg_freespacemap_relations +View "public.pg_freespacemap_relations" + Column | Type | Modifiers +------------------+---------+----------- + reltablespace | oid | + reldatabase | oid | + relfilenode | oid | + avgrequest | integer | + interestingpages | integer | + storedpages | integer | + nextpage | integer | +View definition: + SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.avgrequest, p.interestingpages, p.storedpages, p.nextpage + FROM pg_freespacemap_relations() p(reltablespace oid, reldatabase oid, relfilenode oid, avgrequest integer, interestingpages integer, storedpages integer, nextpage integer); + +regression=# SELECT c.relname, r.avgrequest, r.interestingpages, r.storedpages + FROM pg_freespacemap_relations r INNER JOIN pg_class c + ON c.relfilenode = r.relfilenode INNER JOIN pg_database d + ON r.reldatabase = d.oid AND (d.datname = current_database()) + ORDER BY r.storedpages DESC LIMIT 10; + relname | avgrequest | interestingpages | storedpages +---------------------------------+------------+------------------+------------- + onek | 256 | 109 | 109 + pg_attribute | 167 | 93 | 93 + pg_class | 191 | 49 | 49 + pg_attribute_relid_attnam_index | | 48 | 48 + onek2 | 256 | 37 | 37 + pg_depend | 95 | 26 | 26 + pg_type | 199 | 16 | 16 + pg_rewrite | 1011 | 13 | 13 + pg_class_relname_nsp_index | | 10 | 10 + pg_proc | 302 | 8 | 8 +(10 rows) + + + + + Sample output - pg_freespacemap_pages + +regression=# \d pg_freespacemap_pages + View "public.pg_freespacemap_pages" + Column | Type | Modifiers +----------------+---------+----------- + reltablespace | oid | + reldatabase | oid | + relfilenode | oid | + relblocknumber | bigint | + bytes | integer | +View definition: + SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.relblocknumber, p.bytes + FROM pg_freespacemap_pages() p(reltablespace oid, reldatabase oid, relfilenode oid, relblocknumber bigint, bytes integer); + +regression=# SELECT c.relname, p.relblocknumber, p.bytes + FROM pg_freespacemap_pages p INNER JOIN pg_class c + ON c.relfilenode = p.relfilenode INNER JOIN pg_database d + ON (p.reldatabase = d.oid AND d.datname = current_database()) + ORDER BY c.relname LIMIT 10; + relname | relblocknumber | bytes +--------------+----------------+------- + a_star | 0 | 8040 + abstime_tbl | 0 | 7908 + aggtest | 0 | 8008 + altinhoid | 0 | 8128 + altstartwith | 0 | 8128 + arrtest | 0 | 7172 + b_star | 0 | 7976 + box_tbl | 0 | 7912 + bt_f8_heap | 54 | 7728 + bt_i4_heap | 49 | 8008 +(10 rows) + + + + + Author + + Mark Kirkwood markir@paradise.net.nz + + + diff --git a/doc/src/sgml/pgstandby.sgml b/doc/src/sgml/pgstandby.sgml new file mode 100644 index 00000000000..3aafa09dbca --- /dev/null +++ b/doc/src/sgml/pgstandby.sgml @@ -0,0 +1,247 @@ + + pg_standby + + + pg_standby + + + + pg_standby is a production-ready program that can be used + to create a Warm Standby server. Other configuration is required as well, + all of which is described in the main server manual. + + + The program is designed to be a wait-for restore_command, + required to turn a normal archive recovery into a Warm Standby. Within the + restore_command of the recovery.conf + you could configure pg_standby in the following way: + + + restore_command = 'pg_standby archiveDir %f %p' + + + which would be sufficient to define that files will be restored from + archiveDir. + + + + pg_standby features include: + + + + + It is written in C. So it is very portable + and easy to install. + + + + + Supports copy or link from a directory (only) + + + + + Source easy to modify, with specifically designated + sections to modify for your own needs, allowing + interfaces to be written for additional Backup Archive Restore + (BAR) systems + + + + + Already tested on Linux and Windows + + + + + + Usage + + pg_standby should be used within the + restore_command of the recovery.conf + file. + + + The basic usage should be like this: + + + restore_command = 'pg_standby archiveDir %f %p' + + + with the pg_standby command usage as + + + pg_standby [OPTION]... [ARCHIVELOCATION] [NEXTWALFILE] [XLOGFILEPATH] + + + When used within the restore_command the %f and %p macros + will provide the actual file and path required for the restore/recovery. + + + + Options + + + + -c + use copy/cp command to restore WAL files from archive + + + -d + debug/logging option. + + + -k numfiles + + + Cleanup files in the archive so that we maintain no more + than this many files in the archive. + + + You should be wary against setting this number too low, + since this may mean you cannot restart the standby. This + is because the last restartpoint marked in the WAL files + may be many files in the past and can vary considerably. + This should be set to a value exceeding the number of WAL + files that can be recovered in 2*checkpoint_timeout seconds, + according to the value in the warm standby postgresql.conf. + It is wholly unrelated to the setting of checkpoint_segments + on either primary or standby. + + + If in doubt, use a large value or do not set a value at all. + + + + + -l + + + use ln command to restore WAL files from archive + WAL files will remain in archive + + + Link is more efficient, but the default is copy to + allow you to maintain the WAL archive for recovery + purposes as well as high-availability. + + + This option uses the Windows Vista command mklink + to provide a file-to-file symbolic link. -l will + not work on versions of Windows prior to Vista. + Use the -c option instead. + see + + + + + -r maxretries + + + the maximum number of times to retry the restore command if it + fails. After each failure, we wait for sleeptime * num_retries + so that the wait time increases progressively, so by default + we will wait 5 secs, 10 secs then 15 secs before reporting + the failure back to the database server. This will be + interpreted as and end of recovery and the Standby will come + up fully as a result. Default=3 + + + + + -s sleeptime + + the number of seconds to sleep between testing to see + if the file to be restored is available in the archive yet. + The default setting is not necessarily recommended, + consult the main database server manual for discussion. + Default=5 + + + + -t triggerfile + + the presence of the triggerfile will cause recovery to end + whether or not the next file is available + It is recommended that you use a structured filename to + avoid confusion as to which server is being triggered + when multiple servers exist on same system. + e.g. /tmp/pgsql.trigger.5432 + + + + -w maxwaittime + + the maximum number of seconds to wait for the next file, + after which recovery will end and the Standby will come up. + The default setting is not necessarily recommended, + consult the main database server manual for discussion. + Default=0 + + + + +
+ + + --help is not supported since + pg_standby is not intended for interactive use, except + during development and testing. + + + + + + Examples + + + + Example on Linux + +archive_command = 'cp %p ../archive/%f' + +restore_command = 'pg_standby -l -d -k 255 -r 2 -s 2 -w 0 -t /tmp/pgsql.trigger.5442 $PWD/../archive %f %p 2>> standby.log' + + + which will + + + use a ln command to restore WAL files from archive + produce logfile output in standby.log + keep the last 255 full WAL files, plus the current one + sleep for 2 seconds between checks for next WAL file is full + never timeout if file not found + stop waiting when a trigger file called /tmp.pgsql.trigger.5442 appears + + + + + + Example on Windows + + +archive_command = 'copy %p ..\\archive\\%f' + + + Note that backslashes need to be doubled in the archive_command, but + *not* in the restore_command, in 8.2, 8.1, 8.0 on Windows. + + +restore_command = 'pg_standby -c -d -s 5 -w 0 -t C:\pgsql.trigger.5442 + ..\archive %f %p 2>> standby.log' + + + which will + + + use a copy command to restore WAL files from archive + produce logfile output in standby.log + sleep for 5 seconds between checks for next WAL file is full + never timeout if file not found + stop waiting when a trigger file called C:\pgsql.trigger.5442 appears + + + + + + diff --git a/doc/src/sgml/pgtrgm.sgml b/doc/src/sgml/pgtrgm.sgml new file mode 100644 index 00000000000..b7061481a91 --- /dev/null +++ b/doc/src/sgml/pgtrgm.sgml @@ -0,0 +1,213 @@ + + pg_trgm + + + pg_trgm + + + + The pg_trgm module provides functions and index classes + for determining the similarity of text based on trigram matching. + + + + Trigram (or Trigraph) + + A trigram is a set of three consecutive characters taken + from a string. A string is considered to have two spaces + prefixed and one space suffixed when determining the set + of trigrams that comprise the string. + + + eg. The set of trigrams in the word "cat" is " c", " ca", + "at " and "cat". + + + + + Public Functions + + <literal>pg_trgm</literal> functions + + + + Function + Description + + + + + real similarity(text, text) + + + Returns a number that indicates how closely matches the two + arguments are. A zero result indicates that the two words + are completely dissimilar, and a result of one indicates that + the two words are identical. + + + + + real show_limit() + + + Returns the current similarity threshold used by the '%' + operator. This in effect sets the minimum similarity between + two words in order that they be considered similar enough to + be misspellings of each other, for example. + + + + + real set_limit(real) + + + Sets the current similarity threshold that is used by the '%' + operator, and is returned by the show_limit() function. + + + + + text[] show_trgm(text) + + + Returns an array of all the trigrams of the supplied text + parameter. + + + + + Operator: text % text (returns boolean) + + + The '%' operator returns TRUE if its two arguments have a similarity + that is greater than the similarity threshold set by set_limit(). It + will return FALSE if the similarity is less than the current + threshold. + + + + + +
+ + + + Public Index Operator Class + + The pg_trgm module comes with the + gist_trgm_ops index operator class that allows a + developer to create an index over a text column for the purpose + of very fast similarity searches. + + + To use this index, the '%' operator must be used and an appropriate + similarity threshold for the application must be set. Example: + + +CREATE TABLE test_trgm (t text); +CREATE INDEX trgm_idx ON test_trgm USING gist (t gist_trgm_ops); + + + At this point, you will have an index on the t text column that you + can use for similarity searching. Example: + + +SELECT + t, + similarity(t, 'word') AS sml +FROM + test_trgm +WHERE + t % 'word' +ORDER BY + sml DESC, t; + + + This will return all values in the text column that are sufficiently + similar to 'word', sorted from best match to worst. The index will + be used to make this a fast operation over very large data sets. + + + + + Text Search Integration + + Trigram matching is a very useful tool when used in conjunction + with a full text index. + + + The first step is to generate an auxiliary table containing all + the unique words in the documents: + + +CREATE TABLE words AS SELECT word FROM + stat('SELECT to_tsvector(''simple'', bodytext) FROM documents'); + + + where documents is a table that has a text field + bodytext that we wish to search. The use of the + simple configuration with the to_tsvector + function, instead of just using the already + existing vector is to avoid creating a list of already stemmed + words. This way, only the original, unstemmed words are added + to the word list. + + + Next, create a trigram index on the word column: + + +CREATE INDEX words_idx ON words USING gist(word gist_trgm_ops); + + + or + + +CREATE INDEX words_idx ON words USING gin(word gist_trgm_ops); + + + Now, a SELECT query similar to the example above can be + used to suggest spellings for misspelled words in user search terms. A + useful extra clause is to ensure that the similar words are also + of similar length to the misspelled word. + + + + + Since the words table has been generated as a separate, + static table, it will need to be periodically regenerated so that + it remains up to date with the document collection. + + + + + + + References + + GiST Development Site + + + + Tsearch2 Development Site + + + + + + Authors + + Oleg Bartunov oleg@sai.msu.su, Moscow, Moscow University, Russia + + + Teodor Sigaev teodor@sigaev.ru, Moscow, Delta-Soft Ltd.,Russia + + + Documentation: Christopher Kings-Lynne + + + This module is sponsored by Delta-Soft Ltd., Moscow, Russia. + + + + diff --git a/doc/src/sgml/standby.sgml b/doc/src/sgml/standby.sgml deleted file mode 100644 index 120fed4c2c0..00000000000 --- a/doc/src/sgml/standby.sgml +++ /dev/null @@ -1,249 +0,0 @@ - - - pg_standby - - - pgstandby - - - - pg_standby is a production-ready program that can be used - to create a Warm Standby server. Other configuration is required as well, - all of which is described in the main server manual. - - - The program is designed to be a wait-for restore_command, - required to turn a normal archive recovery into a Warm Standby. Within the - restore_command of the recovery.conf - you could configure pg_standby in the following way: - - - restore_command = 'pg_standby archiveDir %f %p' - - - which would be sufficient to define that files will be restored from - archiveDir. - - - - pg_standby features include: - - - - - It is written in C. So it is very portable - and easy to install. - - - - - Supports copy or link from a directory (only) - - - - - Source easy to modify, with specifically designated - sections to modify for your own needs, allowing - interfaces to be written for additional Backup Archive Restore - (BAR) systems - - - - - Already tested on Linux and Windows - - - - - - Usage - - pg_standby should be used within the - restore_command of the recovery.conf - file. - - - The basic usage should be like this: - - - restore_command = 'pg_standby archiveDir %f %p' - - - with the pg_standby command usage as - - - pg_standby [OPTION]... [ARCHIVELOCATION] [NEXTWALFILE] [XLOGFILEPATH] - - - When used within the restore_command the %f and %p macros - will provide the actual file and path required for the restore/recovery. - - - - Options - - - - -c - use copy/cp command to restore WAL files from archive - - - -d - debug/logging option. - - - -k numfiles - - - Cleanup files in the archive so that we maintain no more - than this many files in the archive. - - - You should be wary against setting this number too low, - since this may mean you cannot restart the standby. This - is because the last restartpoint marked in the WAL files - may be many files in the past and can vary considerably. - This should be set to a value exceeding the number of WAL - files that can be recovered in 2*checkpoint_timeout seconds, - according to the value in the warm standby postgresql.conf. - It is wholly unrelated to the setting of checkpoint_segments - on either primary or standby. - - - If in doubt, use a large value or do not set a value at all. - - - - - -l - - - use ln command to restore WAL files from archive - WAL files will remain in archive - - - Link is more efficient, but the default is copy to - allow you to maintain the WAL archive for recovery - purposes as well as high-availability. - - - This option uses the Windows Vista command mklink - to provide a file-to-file symbolic link. -l will - not work on versions of Windows prior to Vista. - Use the -c option instead. - see - - - - - -r maxretries - - - the maximum number of times to retry the restore command if it - fails. After each failure, we wait for sleeptime * num_retries - so that the wait time increases progressively, so by default - we will wait 5 secs, 10 secs then 15 secs before reporting - the failure back to the database server. This will be - interpreted as and end of recovery and the Standby will come - up fully as a result. Default=3 - - - - - -s sleeptime - - the number of seconds to sleep between testing to see - if the file to be restored is available in the archive yet. - The default setting is not necessarily recommended, - consult the main database server manual for discussion. - Default=5 - - - - -t triggerfile - - the presence of the triggerfile will cause recovery to end - whether or not the next file is available - It is recommended that you use a structured filename to - avoid confusion as to which server is being triggered - when multiple servers exist on same system. - e.g. /tmp/pgsql.trigger.5432 - - - - -w maxwaittime - - the maximum number of seconds to wait for the next file, - after which recovery will end and the Standby will come up. - The default setting is not necessarily recommended, - consult the main database server manual for discussion. - Default=0 - - - - -
- - - --help is not supported since - pg_standby is not intended for interactive use, except - during development and testing. - - - - - - Examples - - - - Example on Linux - -archive_command = 'cp %p ../archive/%f' - -restore_command = 'pg_standby -l -d -k 255 -r 2 -s 2 -w 0 -t /tmp/pgsql.trigger.5442 $PWD/../archive %f %p 2>> standby.log' - - - which will - - - use a ln command to restore WAL files from archive - produce logfile output in standby.log - keep the last 255 full WAL files, plus the current one - sleep for 2 seconds between checks for next WAL file is full - never timeout if file not found - stop waiting when a trigger file called /tmp.pgsql.trigger.5442 appears - - - - - - Example on Windows - - -archive_command = 'copy %p ..\\archive\\%f' - - - Note that backslashes need to be doubled in the archive_command, but - *not* in the restore_command, in 8.2, 8.1, 8.0 on Windows. - - -restore_command = 'pg_standby -c -d -s 5 -w 0 -t C:\pgsql.trigger.5442 - ..\archive %f %p 2>> standby.log' - - - which will - - - use a copy command to restore WAL files from archive - produce logfile output in standby.log - sleep for 5 seconds between checks for next WAL file is full - never timeout if file not found - stop waiting when a trigger file called C:\pgsql.trigger.5442 appears - - - - - - - diff --git a/doc/src/sgml/trgm.sgml b/doc/src/sgml/trgm.sgml deleted file mode 100644 index 63f677a6475..00000000000 --- a/doc/src/sgml/trgm.sgml +++ /dev/null @@ -1,214 +0,0 @@ - - pg_trgm - - - pgtrgm - - - - The pg_trgm module provides functions and index classes - for determining the similarity of text based on trigram matching. - - - - Trigram (or Trigraph) - - A trigram is a set of three consecutive characters taken - from a string. A string is considered to have two spaces - prefixed and one space suffixed when determining the set - of trigrams that comprise the string. - - - eg. The set of trigrams in the word "cat" is " c", " ca", - "at " and "cat". - - - - - Public Functions - - <literal>pg_trgm</literal> functions - - - - Function - Description - - - - - real similarity(text, text) - - - Returns a number that indicates how closely matches the two - arguments are. A zero result indicates that the two words - are completely dissimilar, and a result of one indicates that - the two words are identical. - - - - - real show_limit() - - - Returns the current similarity threshold used by the '%' - operator. This in effect sets the minimum similarity between - two words in order that they be considered similar enough to - be misspellings of each other, for example. - - - - - real set_limit(real) - - - Sets the current similarity threshold that is used by the '%' - operator, and is returned by the show_limit() function. - - - - - text[] show_trgm(text) - - - Returns an array of all the trigrams of the supplied text - parameter. - - - - - Operator: text % text (returns boolean) - - - The '%' operator returns TRUE if its two arguments have a similarity - that is greater than the similarity threshold set by set_limit(). It - will return FALSE if the similarity is less than the current - threshold. - - - - - -
- - - - Public Index Operator Class - - The pg_trgm module comes with the - gist_trgm_ops index operator class that allows a - developer to create an index over a text column for the purpose - of very fast similarity searches. - - - To use this index, the '%' operator must be used and an appropriate - similarity threshold for the application must be set. Example: - - -CREATE TABLE test_trgm (t text); -CREATE INDEX trgm_idx ON test_trgm USING gist (t gist_trgm_ops); - - - At this point, you will have an index on the t text column that you - can use for similarity searching. Example: - - -SELECT - t, - similarity(t, 'word') AS sml -FROM - test_trgm -WHERE - t % 'word' -ORDER BY - sml DESC, t; - - - This will return all values in the text column that are sufficiently - similar to 'word', sorted from best match to worst. The index will - be used to make this a fast operation over very large data sets. - - - - - Text Search Integration - - Trigram matching is a very useful tool when used in conjunction - with a full text index. - - - The first step is to generate an auxiliary table containing all - the unique words in the documents: - - -CREATE TABLE words AS SELECT word FROM - stat('SELECT to_tsvector(''simple'', bodytext) FROM documents'); - - - where documents is a table that has a text field - bodytext that we wish to search. The use of the - simple configuration with the to_tsvector - function, instead of just using the already - existing vector is to avoid creating a list of already stemmed - words. This way, only the original, unstemmed words are added - to the word list. - - - Next, create a trigram index on the word column: - - -CREATE INDEX words_idx ON words USING gist(word gist_trgm_ops); - - - or - - -CREATE INDEX words_idx ON words USING gin(word gist_trgm_ops); - - - Now, a SELECT query similar to the example above can be - used to suggest spellings for misspelled words in user search terms. A - useful extra clause is to ensure that the similar words are also - of similar length to the misspelled word. - - - - - Since the words table has been generated as a separate, - static table, it will need to be periodically regenerated so that - it remains up to date with the document collection. - - - - - - - References - - GiST Development Site - - - - Tsearch2 Development Site - - - - - - Authors - - Oleg Bartunov oleg@sai.msu.su, Moscow, Moscow University, Russia - - - Teodor Sigaev teodor@sigaev.ru, Moscow, Delta-Soft Ltd.,Russia - - - Documentation: Christopher Kings-Lynne - - - This module is sponsored by Delta-Soft Ltd., Moscow, Russia. - - - - - -- cgit v1.2.3