docs/README.sequence
changeset 64 dab0e9deeb67
parent 63 d76966db18e5
child 65 ce1c4d5e1576
equal deleted inserted replaced
63:d76966db18e5 64:dab0e9deeb67
     1 README.sequence - Additional documentation on manual inserts (using
       
     2 poweradmin along with other interfaces to the powerdns database).
       
     3 
       
     4 
       
     5 Removal of "sequence updater"
       
     6 -----------------------------------------------------------------------
       
     7 
       
     8 Up to poweradmin version 1.2.7-patched, the code included the so
       
     9 called "sequence updater". It was written to synchronize poweradmin
       
    10 with the database after manual inserts of zones and records. This
       
    11 happens if you insert new zones or records by hand, or have other
       
    12 interfaces than poweradmin talking to the database as well.
       
    13 
       
    14 The 1.2.7-patched version of poweradmin was using the PEAR::DB module,
       
    15 using it's nextID() function. It tells the application what will be
       
    16 the next ID for insertion. However, mysql has "auto_increment" and
       
    17 pgsql has "serial". Both allow to do insert with ID's created on the
       
    18 run, without the need of seperate sequence numbers.
       
    19 
       
    20 In order to get rid of the incidental errors and the need to hit the
       
    21 "synchronize database" from time to time, the "sequence updater" has
       
    22 been removed and the code has been updated to use the "auto_increment"
       
    23 and "serial" functionality. As long as you do manual inserts the
       
    24 correct way, this will not cause any problems.
       
    25 
       
    26 
       
    27 Insert new records and zones the correct way (using other tools)
       
    28 -----------------------------------------------------------------------
       
    29 
       
    30 According to the documentation of powerdns (2.9.20), the id column of
       
    31 the tables domains and records in a mysql setup are created using the
       
    32 "auto_increment" option and in a pgsql setup using the "serial"
       
    33 option. [1] 
       
    34 
       
    35 This will allow for auto increments of the id field, as long as no one
       
    36 does a manual insert, specifying the id. So, let's say we have created
       
    37 a table, one in mysql, one in pgsql:
       
    38 
       
    39   CREATE TABLE xmpl_tbl (id INT auto_increment, text TEXT);
       
    40   CREATE TABLE xmpl_tbl (id SERIAL PRIMARY KEY, text TEXT);
       
    41 
       
    42 This insert will work in pgsql only:
       
    43 
       
    44   INSERT INTO xmpl_tbl VALUES (DEFAULT,'First insert test");
       
    45 
       
    46 These inserts will work in mysql only:
       
    47 
       
    48   INSERT INTO xmpl_tbl VALUES (NULL,'Third insert test"); 
       
    49   INSERT INTO xmpl_tbl VALUES ('','Fourth insert test"); 
       
    50 
       
    51 This will work in both mysql and pgsql (as long as id didn't exist
       
    52 already), but it will break "auto increment" feature in pgsql [2] (can
       
    53 be fixed using setval() functionality in pgsql):
       
    54 
       
    55   INSERT INTO xmpl_tbl VALUES (42,'Fifth insert test");
       
    56 
       
    57 This will work in both mysql and pgsql:
       
    58 
       
    59   INSERT INTO xmpl_tbl (text) VALUES ('Second insert test");
       
    60 
       
    61 So, in other words: if you want to insert records and zones into the
       
    62 powerdns database using other tools and interfaces than poweradmin,
       
    63 you should make sure you are using the correct syntax.
       
    64 
       
    65 
       
    66 Limitations
       
    67 -----------------------------------------------------------------------
       
    68 
       
    69 Removing the "sequence updater" removes the possibilty to use the
       
    70 poweradmin code against other databases than mysql and pgsql (or at
       
    71 least, it is untested).
       
    72 
       
    73 
       
    74 More information
       
    75 -----------------------------------------------------------------------
       
    76 
       
    77 More information can be found here:
       
    78 
       
    79  - PowerDNS manual, section backends in detail
       
    80    <http://downloads.powerdns.com/documentation/html/generic-mypgsql-backends.html>
       
    81 
       
    82  - Postgresql documentation, section Operational Questions 
       
    83    <http://www.postgresql.org/files/documentation/books/aw_pgsql/node196.html#SECTION0029916000000000000000>
       
    84 
       
    85  - pgsql-bugs mailinglist archive
       
    86    <http://archives.postgresql.org/pgsql-bugs/2004-11/msg00340.php>
       
    87    <http://archives.postgresql.org/pgsql-bugs/2004-11/msg00344.php>
       
    88