| Index: trunk/phase3/docs/databases/postgres.txt |
| — | — | @@ -3,18 +3,18 @@ |
| 4 | 4 | |
| 5 | 5 | == Overview == |
| 6 | 6 | |
| 7 | | -Support for PostgreSQL has been available since version 1.7 |
| 8 | | -of MediaWiki, and is fairly well maintained. The main code |
| 9 | | -is very well integrated, while extensions are very hit and miss. |
| | 7 | +Support for PostgreSQL has been available since version 1.7 |
| | 8 | +of MediaWiki, and is fairly well maintained. The main code |
| | 9 | +is very well integrated, while extensions are very hit and miss. |
| 10 | 10 | Still, it is probably the most supported database after MySQL. |
| 11 | | -Much of the work in making MediaWiki database-agnostic came |
| | 11 | +Much of the work in making MediaWiki database-agnostic came |
| 12 | 12 | about through the work of creating Postgres support. |
| 13 | 13 | |
| 14 | 14 | |
| 15 | 15 | == Required versions == |
| 16 | 16 | |
| 17 | | -The current minimum version of PostgreSQL for MediaWiki is 8.1. |
| 18 | | -It is expected that this will be raised to 8.3 at some point, |
| | 17 | +The current minimum version of PostgreSQL for MediaWiki is 8.1. |
| | 18 | +It is expected that this will be raised to 8.3 at some point, |
| 19 | 19 | as 8.1 and 8.2 are nearing end of life. |
| 20 | 20 | |
| 21 | 21 | |
| — | — | @@ -23,79 +23,78 @@ |
| 24 | 24 | |
| 25 | 25 | Postgres has its own schema file at maintenance/postgres/tables.sql. |
| 26 | 26 | |
| 27 | | -The goal is to keep this file as close as possible to the canonical |
| 28 | | -schema at maintenance/tables.sql, but without copying over |
| | 27 | +The goal is to keep this file as close as possible to the canonical |
| | 28 | +schema at maintenance/tables.sql, but without copying over |
| 29 | 29 | all the usage comments. General notes on the conversion: |
| 30 | 30 | |
| 31 | | -* The use of a true TIMESTAMP rather than the text string that |
| 32 | | -MySQL uses is highly encouraged. There are still places in the |
| 33 | | -code (especially extensions) which make assumptions about the |
| 34 | | -textual nature of timestamp fields, but these can almost always |
| | 31 | +* The use of a true TIMESTAMP rather than the text string that |
| | 32 | +MySQL uses is highly encouraged. There are still places in the |
| | 33 | +code (especially extensions) which make assumptions about the |
| | 34 | +textual nature of timestamp fields, but these can almost always |
| 35 | 35 | be programmed around. |
| 36 | 36 | |
| 37 | | -* Although Postgres has a true BOOLEAN type, boolean columns |
| 38 | | -are always mapped to SMALLINT, as the code does not always treat |
| 39 | | -the column as a boolean (which is limited to accepting true, |
| | 37 | +* Although Postgres has a true BOOLEAN type, boolean columns |
| | 38 | +are always mapped to SMALLINT, as the code does not always treat |
| | 39 | +the column as a boolean (which is limited to accepting true, |
| 40 | 40 | false, 0, 1, t, or f) |
| 41 | 41 | |
| 42 | | -* The default data type for all VARCHAR, CHAR, and VARBINARY |
| 43 | | -columns should simply be TEXT. The only exception is |
| 44 | | -when VARBINARY is used to store true binary data, such as |
| | 42 | +* The default data type for all VARCHAR, CHAR, and VARBINARY |
| | 43 | +columns should simply be TEXT. The only exception is |
| | 44 | +when VARBINARY is used to store true binary data, such as |
| 45 | 45 | the math_inputhash column, in which case BYTEA should be used. |
| 46 | 46 | |
| 47 | | -* All integer variants should generally be mapped to INTEGER. |
| 48 | | -There is small-to-no advantage in using SMALLINT versus |
| 49 | | -INTEGER in Postgres, and the possibility of running out of |
| 50 | | -room outweighs such concerns. The columns that are BIGINT |
| 51 | | -in other schemas should be INTEGER as well, as none of them |
| 52 | | -so far are even remotely likely to reach the 32 billion |
| | 47 | +* All integer variants should generally be mapped to INTEGER. |
| | 48 | +There is small-to-no advantage in using SMALLINT versus |
| | 49 | +INTEGER in Postgres, and the possibility of running out of |
| | 50 | +room outweighs such concerns. The columns that are BIGINT |
| | 51 | +in other schemas should be INTEGER as well, as none of them |
| | 52 | +so far are even remotely likely to reach the 32 billion |
| 53 | 53 | limit of an INTEGER. |
| 54 | 54 | |
| 55 | | -* Blobs (blob, tinyblog, mediumblob) should be mapped to TEXT |
| 56 | | -whenever possible, and to BYTEA if they are known to contain |
| | 55 | +* Blobs (blob, tinyblog, mediumblob) should be mapped to TEXT |
| | 56 | +whenever possible, and to BYTEA if they are known to contain |
| 57 | 57 | binary data. |
| 58 | 58 | |
| 59 | | -* All length modifiers on data types should be removed. If |
| 60 | | -they are on an INTEGER, it's probably an error, and if on |
| | 59 | +* All length modifiers on data types should be removed. If |
| | 60 | +they are on an INTEGER, it's probably an error, and if on |
| 61 | 61 | any text-based field, simply using TEXT is preferred. |
| 62 | 62 | |
| 63 | | -* Sequences should be explicitly named rather than using |
| | 63 | +* Sequences should be explicitly named rather than using |
| 64 | 64 | SERIAL, as the code can depend on having a specific name. |
| 65 | 65 | |
| 66 | | -* Foreign keys should be used when possible. This makes things |
| 67 | | -both easier and harder in the code, but most of the major |
| 68 | | -problems have now been overcome. Always add an explicit ON DELETE |
| 69 | | -clause, and consider carefully what choice to use (all things |
| | 66 | +* Foreign keys should be used when possible. This makes things |
| | 67 | +both easier and harder in the code, but most of the major |
| | 68 | +problems have now been overcome. Always add an explicit ON DELETE |
| | 69 | +clause, and consider carefully what choice to use (all things |
| 70 | 70 | considered, prefer CASCADE). |
| 71 | 71 | |
| 72 | | -* The use of CIDR should be done very carefully, because the code |
| 73 | | -will sometimes want to store things such as an empty string or |
| | 72 | +* The use of CIDR should be done very carefully, because the code |
| | 73 | +will sometimes want to store things such as an empty string or |
| 74 | 74 | other non-IP value in the column. When in doubt, use TEXT. |
| 75 | 75 | |
| 76 | | -* Indexes should be created using the original MySQL tables.sql |
| 77 | | -as a guide, but keeping in mind the ability of Postgres to use |
| 78 | | -partial indexes, functional indexes, and bitmaps. The index names |
| 79 | | -should be logical but are not too important, as they are never |
| 80 | | -referenced directly by the code (unlike sequence names). Most of |
| 81 | | -the indexes in the file as of this writing are there due to production |
| | 76 | +* Indexes should be created using the original MySQL tables.sql |
| | 77 | +as a guide, but keeping in mind the ability of Postgres to use |
| | 78 | +partial indexes, functional indexes, and bitmaps. The index names |
| | 79 | +should be logical but are not too important, as they are never |
| | 80 | +referenced directly by the code (unlike sequence names). Most of |
| | 81 | +the indexes in the file as of this writing are there due to production |
| 82 | 82 | testing of expensive queries on a busy wiki. |
| 83 | 83 | |
| 84 | 84 | |
| 85 | 85 | == Keeping in sync with tables.sql == |
| 86 | 86 | |
| 87 | | -The script maintenance/postgres/compare_schemas.pl should be |
| 88 | | -periodically run. It will parse both "tables.sql" files and |
| 89 | | -produce any differences found. Such differences should be fixed |
| 90 | | -or exceptions specifically carved out by editing the script |
| 91 | | -itself. This script has also been very useful in finding problems |
| 92 | | -in maintenance/tables.sql itself, as it is very strict in the |
| | 87 | +The script maintenance/postgres/compare_schemas.pl should be |
| | 88 | +periodically run. It will parse both "tables.sql" files and |
| | 89 | +produce any differences found. Such differences should be fixed |
| | 90 | +or exceptions specifically carved out by editing the script |
| | 91 | +itself. This script has also been very useful in finding problems |
| | 92 | +in maintenance/tables.sql itself, as it is very strict in the |
| 93 | 93 | format it expects things to be in. :) |
| 94 | 94 | |
| 95 | 95 | |
| 96 | 96 | == Getting help == |
| 97 | 97 | |
| 98 | | -In addition to the normal venues (MediaWiki mailing lists |
| 99 | | -and IRC channels), the #postgresql channel on irc.freenode.net |
| 100 | | -is a friendly and expert resource if you should encounter a |
| | 98 | +In addition to the normal venues (MediaWiki mailing lists |
| | 99 | +and IRC channels), the #postgresql channel on irc.freenode.net |
| | 100 | +is a friendly and expert resource if you should encounter a |
| 101 | 101 | problem with your Postgres-enabled MediaWiki. |
| 102 | | - |
| Property changes on: trunk/phase3/docs/databases/postgres.txt |
| ___________________________________________________________________ |
| Added: svn:eol-style |
| 103 | 102 | + native |