|
9 | 9 |
|
10 | 10 | <para>
|
11 | 11 | The <filename>dump_stat</> module provides functions that allow you to
|
12 |
| - backup and recover the contents of the pg_statistic table. The |
13 |
| - <function>dump_statistic</> function generates <literal>INSERT</> statements which |
14 |
| - can later be applied to a compatible PostgreSQL server. The extension |
| 12 | + backup and recover the contents of the |
| 13 | + <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link> |
| 14 | + table. The <function>dump_statistic</> function generates <literal>INSERT</> |
| 15 | + statements which can later be applied to a compatible database. The extension |
15 | 16 | should be installed on the recipient server in order to successfuly restore
|
16 |
| - statistical data since these statements rely on the provided functions. |
| 17 | + statistical data since these statements heavily rely on the provided functions. |
| 18 | + </para> |
| 19 | + |
| 20 | + <para> |
| 21 | + Note that the definition of the |
| 22 | + <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link> |
| 23 | + table might change occasionally, which means that generated dump might be incompatible |
| 24 | + with future releases of PostgreSQL. |
17 | 25 | </para>
|
18 | 26 |
|
19 | 27 | <sect2>
|
|
33 | 41 | <function>dump_statistic</function> dumps the contents of the
|
34 | 42 | <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
|
35 | 43 | system catalog. It produces an <literal>INSERT</> statement
|
36 |
| - per each tuple of the <literal>pg_statistic</>, excluding |
37 |
| - the ones that contain statistical data for tables in |
38 |
| - <literal>information_schema</> and <literal>pg_catalog</> |
39 |
| - schemas. |
| 44 | + per each tuple of the |
| 45 | + <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>, |
| 46 | + excluding the ones that contain statistical data for tables in the |
| 47 | + <literal>information_schema</> and <literal>pg_catalog</> schemas. |
| 48 | + </para> |
| 49 | + |
| 50 | + <para> |
| 51 | + The <literal>INSERT</> statement takes form of |
| 52 | +<screen> |
| 53 | +WITH upsert as ( |
| 54 | + UPDATE pg_catalog.pg_statistic SET %s |
| 55 | + WHERE to_schema_qualified_relation(starelid) = relname |
| 56 | + AND to_attname(relname, staattnum) = attname |
| 57 | + AND to_atttype(relname, staattnum) = atttype |
| 58 | + AND stainherit = r.stainherit |
| 59 | + RETURNING *) |
| 60 | +ins as ( |
| 61 | + SELECT %s |
| 62 | + WHERE NOT EXISTS (SELECT * FROM upsert) |
| 63 | + AND to_attnum(relname, attname) IS NOT NULL |
| 64 | + AND to_atttype(relname, attname) = atttype) |
| 65 | +INSERT INTO pg_catalog.pg_statistic SELECT * FROM ins; |
| 66 | +</screen> |
40 | 67 | </para>
|
41 | 68 | </listitem>
|
42 | 69 | </varlistentry>
|
|
54 | 81 | <function>dump_statistic</function> dumps the contents of the
|
55 | 82 | <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
|
56 | 83 | system catalog. It produces an <literal>INSERT</> statement
|
57 |
| - per each tuple of the <literal>pg_statistic</>, excluding |
58 |
| -the ones that contain statistical data for tables in |
59 |
| -<literal>information_schema</> and <literal>pg_catalog</> |
60 |
| -schemas. |
| 84 | + per each tuple of the |
| 85 | +<link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>, |
| 86 | +excluding the ones that do not relate to the tables in |
| 87 | +<literal>schema_name</> schema. |
61 | 88 | </para>
|
62 | 89 | </listitem>
|
63 | 90 | </varlistentry>
|
|
75 | 102 | <function>dump_statistic</function> dumps the contents of the
|
76 | 103 | <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
|
77 | 104 | system catalog. It produces an <literal>INSERT</> statement
|
78 |
| - per each tuple of the <literal>pg_statistic</>, excluding |
79 |
| -the ones that contain statistical data for tables in |
80 |
| -<literal>information_schema</> and <literal>pg_catalog</> |
81 |
| -schemas. |
| 105 | + per each tuple of the |
| 106 | +<link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>, |
| 107 | +excluding the ones that do not relate to the specified |
| 108 | +<literal>"schema_name.table_name"</> table. |
82 | 109 | </para>
|
83 | 110 | </listitem>
|
84 | 111 | </varlistentry>
|
|
138 | 165 |
|
139 | 166 | <listitem>
|
140 | 167 | <para>
|
141 |
| - Returns the element type of the given array as oid. |
| 168 | + Returns the element type of the given array as<literal>oid</>. |
142 | 169 | </para>
|
143 | 170 | </listitem>
|
144 | 171 | </varlistentry>
|
|
153 | 180 |
|
154 | 181 | <listitem>
|
155 | 182 | <para>
|
156 |
| - Given a relation name and a column number, returns the column name as text. |
| 183 | + Given a relation name and a column number, returns the column name |
| 184 | + as <literal>text</>. |
157 | 185 | </para>
|
158 | 186 | </listitem>
|
159 | 187 | </varlistentry>
|
|
168 | 196 |
|
169 | 197 | <listitem>
|
170 | 198 | <para>
|
171 |
| - Given a relation name and a column name, returns the column number as int2. |
| 199 | + Given a relation name and a column name, returns the column number |
| 200 | + as <literal>int2</>. |
172 | 201 | </para>
|
173 | 202 | </listitem>
|
174 | 203 | </varlistentry>
|
|
183 | 212 |
|
184 | 213 | <listitem>
|
185 | 214 | <para>
|
186 |
| - Given a relation name and a column name, returns the column type as text. |
| 215 | + Given a relation name and a column name, returns the column type |
| 216 | + as <literal>text</>. |
187 | 217 | </para>
|
188 | 218 | </listitem>
|
189 | 219 | </varlistentry>
|
|
201 | 231 | <function>to_namespace</function> duplicates the behavior of
|
202 | 232 | the cast to <literal>regnamespace</> type, which is not
|
203 | 233 | present in the 9.4 release (and prior releases). This
|
204 |
| - function returns the oid of the givennamespace. |
| 234 | + function returns the<literal>oid</> of the givenschema. |
205 | 235 | </para>
|
206 | 236 | </listitem>
|
207 | 237 | </varlistentry>
|
|
216 | 246 |
|
217 | 247 | <listitem>
|
218 | 248 | <para>
|
219 |
| - <function>get_namespace</function> returns thenamespace |
220 |
| - of the given relation as oid. |
| 249 | + <function>get_namespace</function> returns theschema |
| 250 | + of the given relation as<literal>oid</>. |
221 | 251 | </para>
|
222 | 252 | </listitem>
|
223 | 253 | </varlistentry>
|
|