Postgres Architecture and Administration (3 of 3)

  1. Create System Catalogs (Data Dictonary Views) and the Standard Packages.
    1. Construct the Data Dictonary views
    2. As mentioned in the previous sections, when the command initdb is executed to create the Database system, the System Catalogs are also created under the $PGHOME directory, as well as under the special database named template1 which will form the skeleton for other databases created under the databasesystem.

    3. Prepare and Install the PLPGSQL,PLTCL,PLPERL environment
    4. To install a procedural language in Postgres the createlang command available from the shell is used, in order to use this command it is recommended that the PGLIB environment variable be set, this variable should point to the: /usr/local/pgsql/lib directory (the Postgres libraries), the variable can also be set with a flag (-L) at createlang execution. It should be noted that by default no procedural language is active on any database,NOTE: active; the difference between active and installed is that the installation refers to the installation-library level, while the activation is done at the more granular database level, this will be made clearer in the following sections.

      Install (Activate) PLPGSQL

      In order to activate the PLPGSQL (Procedural Language PG(Postgres)SQL) the command createlang will require at a mininmum two parameters, the library location (provided with the $PGLIB variable or -L flag) and the database (provided with the $DBNAME or -d flag), upon execution, the language should be typed in at the prompt:

      [postgres@servidor1 db]$ createlang -d workload
      Language to install in database workload: plpgsql
      [postgres@servidor1 db]$ 

      Install (Activate) PLTCL & PLPERL

      To activate these procedural languages the process is a little more involved than for PLPGSQL, this is due to the fact that they need to be installed in the system ; these procedural languages requiere the shared libraries and , the default installation/compile does not create these shared libraries, so they will have to be installed in the /usr/local/pgsql/lib directory. To do this there are two possibilities:

        1. Recompile and reinstall postgres with the --with-tcl flag. (This in no way affects existing databasesystems or databases)
        2. Go into the source tree of your intial compilation and under the directory /src/pl locate the perl and tcl directories, these will contain the source (.c) for obtaining the and libraries, these should then be placed in the /usr/local/pgsql/lib library.

      To successfully install the and correctly, the system you are installing Postgres on will have to have the appropriate Tcl Interpreter and Perl Interpreter , this is necessary because Postgres does not come with a procedural language environment so in order for procedural languages to be executed it will rely on the "Host's" installed packages.

      Activate Languages by Default

      In order to activate languages by default on a new database, this can be done by activating the desired languages in the template1 database located under the $PGDATA/base directory, this is made possible because every newly created database generates its base tables from this template database

    5. Administer installed languages and stored procedures.
    6. Every database in a databasesystem contains a table named: pg_language, this table contains al the active procedural languages for the database. This table can be queried directly from the shell with the createlang -d workload -l command, it would display the following:

      [postgres@servidor1 postgres]$ createlang -d workload -l
           Procedural languages
        Name   | Trusted? | Compiler 
       plpgsql | t        | PL/pgSQL
       pltcl   | t        | PL/Tcl
      (2 rows)
    7. Administer stored procedures and packages
    8. Information about the defined functions in a database can be queried with the \df (regexp) command in PSQL (Postgres's SQL*Plus), the \df+ (regexp) can also be used to display more detailed information like language (plperl,pltcl..) and description. If no (regexp) is provided all functions will be returned, if specified it returns those functions matching the regexp.

    9. List the types of database event triggers
    10. A trigger is executed implicitly whenever the triggering event happens,and the trigger doesnt accept arguments. Triggers are used to:

        1. Maintain complex integrity constraints not possible through declarative constraints enabled on the table.
        2. Auditing information in a table, by recording the changes made and who made them.
        3. Automatically signalling other programs that action needs to take place, when changes are made to a table.

      The general syntax is :

       CREATE TRIGGER trigger_name 
        [BEFORE | AFTER] [INSERT| DELETE | UPDATE] triggering_event 
        ON table_reference FOR EACH [ROW]
        EXECUTE PROCEDURE procedure (args);

      Where triggering_event specifies when the trigger fires, table_reference is the table for which the trigger is defined, and procedure (args) is the main code for the trigger.

      Compared to Oracle, Postgres cannot define the trigger event or action directly in the declaration, Postgres depends on creating a separate function to be executed on the trigger, this has the disadvantage of splitting the trigger logic in various parts, but it has the advantage of calling any triggering action (function) within Postgres support: Perl, Tcl or built-in Sql.


      The triggering event determines the type of the trigger. Triggers can be defined for INSERT, UPDATE, or DELETE operations. They can be fired before or after the operation, in the current version Postgres (7.0) they can only be fired on row operations (Oracle allows statement operations). The values for the statement, timing and level determine the type of the trigger. There are a total of 6 possible types: 3 statements,2 timing and 1 level.

      Category Values Comments
      Statement INSERT, UPDATE, DELETE Defines which kind of DML statements causes the trigger to fire
      Timing BEFORE or AFTER Defines wether the trigger fires before the statement is executed or after the statement is executed
      Level Row (Statement not currently supported) If the trigger is a row-level trigger, it fires once for each row affected by the triggering statement. If the trigger is a statement-level trigger (not supported) it fires once , either before or after the statement. A row-level trigger is identified by the FOR EACH ROW clause in the trigger definition

      You can drop the trigger with the following commands:

       DROP TRIGGER [name] on [table name] <trigger_name>

      NOTE: There is no command for enabling|disabling a trigger in Postgres like Oracle.

      To display the triggers defined in a database you have to query the object descriptors with the argument "triggers", from PSQL: \dd triggers

  2. Maintaining the Control Files
  3. To the extent the author knows, there is nothing similar to a control file in Postgres , all information regarding the physical structure (file location) of a database is located in the system catalogs.

  4. Maintaining the Redo Log Files
  5. Other than the fact that Postgres complies with the "readers wait for writers" database adage, that is, readers will consistently see only commited data , and writers must wait for any other data that is locked or under transaction, it is not clear if the transaction history made on the database is or can be piped out to a particular file and later reapplied , to conform what would in "Oracle-speak" be called a Redo-Log file, facilitating the rollback to a certain transaction or time functionality.

    This issue brings up another interesting question, since all uncommited data is kept in memory,what happens if a long query/transaction is executed and the memory is exhausted or overwritten by another transaction, what happens if a user tries to query this overwritten data, will a SNAPSHOT TO OLD appear like Oracle, is there any manner to write this out to a file before its overwritten ? Basically creating an online redo-log file ? If you know how, please email me so I can add it here.

  6. Managing Tablepace and Data Files
  7. There is no such logical structure in a Postgres database, everything is kept directly in system files, this would be similar to having Oracle data files , but without any extra logical divisions (a.k.a tablespaces)

  8. Storage Structure and Relationships

  9. Managing Rollback Segments

  10. Managing Tables

  11. Managing Indexes

  12. Maintaining Data Integrity

  13. Loading Data

  14. Reorganizing Data

  15. Managing Password Security and Resources

  16. Managing Users

  17. Managing Priviliges

  18. Managing Roles

  19. Using National Language Support