PGCluster

PGCluster 

• PGCluster is a multi-master, synchronous replication system based on PostgreSQL Database Server.
• Synchronous Replication System
• No delay occurs for data duplication between the Cluster DBs.
• Multi-master Cluster DB System
• There is no Cluster DBs preference for queries. A user can use any node for any type of query

• PGCluster has two functions
• A load balancing function
• A high availability function

• It has 3 kinds of servers
• Replication Server[s]
• Cluster Servers
• Load Balancing Server[s]

PGCluster – Versions

• PGCluster 1.9 is for PostgreSQL 8.3.
• Latest minor version: 1.9.0rc4, for PostgreSQL 8.3.0
• PGCluster 1.7 is for PostgreSQL 8.2.
• Latest minor version: 1.7.0rc11, for PostgreSQL 8.2.6
• PGCluster 1.5 is for PostgreSQL 8.1.
• Latest minor version: 1.5.0rc20, for PostgreSQL 8.1.11
• PGCluster 1.3 is for PostgreSQL 8.0.
• Latest minor version: 1.3.0rc10, for PostgreSQL 8.0.15
• PGCluster 1.1 is for PostgreSQL 7.4.
• Latest minor version: 1.1.2rc9, for PostgreSQL 7.4.19

PGCluster – Architecture

Cursors in PL/pgSQL

About Cursors
Every SQL statement executed by the PostgreSQL Server has an individual cursor associated with it :

• Implicit cursors : Declared and managed by PL/pgSQL for all DML and PL/pgSQL SELECT statements
• Explicit cursors : Declared and managed by the programmer

Explicit Cursor Operations

Controlling Explicit Cursors

Declaring the Cursor

Syntax : 

             CURSOR cursor_name IS select_statement;

Example :

      DECLARE 

      CURSOR emp_cursor IS 

      SELECT empno, ename FROM emp 

      WHERE deptno =30;

Opening the Cursor :

       DECLARE

              CURSOR emp_cursor IS 

              SELECT empno, ename FROM emp

              WHERE deptno =30;

       BEGIN

              OPEN emp_cursor;

Fetching Data from the Cursor :

       DECLARE

              CURSOR emp_cursor IS 

              SELECT empno, ename FROM emp

              WHERE deptno =30;

              empno emp.empno%TYPE;

              lname emp.ename%TYPE;

       BEGIN

              OPEN emp_cursor;

              FETCH emp_cursor INTO empno, lname;

              raise Notice ‘%’,empno; 

              raise Notice ‘%’,lname;

              EXIT WHEN NOT FOUND;

              END LOOP; 

       END;

Closing the Cursor

       ...  

       LOOP

       CLOSE emp_cursor;

END;

Iterative Control in PL/pgSQL

LOOP Statements

• Loops repeat a statement or sequence of statements multiple times.
• There are three loop types:
• Basic loop
• FOR loop
• WHILE loop

Basic Loops

• Syntax :

          LOOP

          statement1;

          . . .

          EXIT [WHEN condition];

          END LOOP;

• Example :

         create or replace function f2() returns void as

         $$

         DECLARE

         ctr numeric:=0;

         BEGIN

               LOOP

                     raise notice '%',ctr;

                     ctr:=ctr+1;

                     EXIT WHEN ctr > 3;

               END LOOP;

         END;

$$ language plpgsql;

WHILE Loops

• Syntax :

          WHILE condition LOOP

          statement1;

          statement2;

          . . .

          END LOOP;

• Use the WHILE loop to repeat statements 
• while a condition is TRUE.

FOR Loops

• Use a FOR loop to shortcut the test for the numeric of iterations.
• Do not declare the counter; it is declared implicitly.
• 'lower_bound .. upper_bound' is required syntax.

          FOR counter IN [REVERSE]

          lower_bound..upper_bound LOOP

          statement1;

          statement2;

          . . .

          END LOOP;

• Example :

          create or replace function f3() returns void as

          $$

          BEGIN

          FOR ctr IN 1..3 LOOP

          raise notice '%',ctr;

          END LOOP;

          END;

          $$ language plpgsql;

Loop through Query Results

FOR record_or_row IN query LOOP

statements

END LOOP [ label ];

create or replace function f4() returns void as

$$

DECLARE

rec emp%ROWTYPE;

BEGIN

FOR rec IN select * from emp LOOP

raise notice '%',rec.ename;

END LOOP;

END;

$$ language plpgsql;

The %TYPE Attribute in PL/pgSQL

The %TYPE attribute

• Is used to declare a variable according to : 
• A database column definition
• Another declared variable
• Is prefixed with :
• The database table and column
• The name of the declared variable

Declaring Variables with the %TYPE Attribute


Syntax :
identifier table.column_name%TYPE;


Examples :
emp_name emp.ename%TYPE;
balance NUMERIC;
min_balance balance%TYPE := 1000;

Use of Variables in PL/pgSQL

Variables can be used for :

• Temporary storage of data
• Manipulation of stored values
• Reusability

Handling Variables in PL/pgSQL

Variables are :

• Declared and initialized in the declarative section
• Used and assigned new values in the executable section
• Passed as parameters to PL/pgSQL subprograms
• Used to hold the output of a PL/pgSQL subprogram

         DECLARE
         emp_hiredate DATE;
         emp_deptno NUMERIC := 10;
         location VARCHAR(13) := 'Atlanta';

Guidelines for Declaring PL/pgSQL Variables

• Avoid using column names as identifiers.
• Use the NOT NULL constraint when the variable must hold a value.

PL/pgSQL

PL/pgSQL Block Structure

• DECLARE (Optional) 
• Variables, 
• cursors,
• BEGIN (Mandatory) 
• SQL statements 
• PLPGSQL statements
• EXCEPTION (Optional) 
• Actions to perform when errors occur
• END; (Mandatory)

• All key words and identifiers can be written in mixed upper and lower case. Identifiers are implicitly converted to lowercase unless doublequoted.
• There are two types of comments in PL/pgSQL.
• -- starts a comment that extends to the end of the line.
• /* multi-line comments */

Procedural Languages

Procedural Languages Overview

• PostgreSQL allows user-defined functions to be written in a variety of procedural languages. 
• The database server has no built-in knowledge about how to interpret the function's source text. 
• Instead, the task is passed to a handler that knows the details of that particular language.
• PostgreSQL currently supports several standard procedural languages :
• PL/pgSQL
• PL/Tcl
• PL/Perl
• PL/Python
• PL/Java
• PL/Ruby
• Other languages can be defined by users

• PL/pgSQL is a loadable procedural language for the PostgreSQL database system.
• PL/pgSQL has several distinct features :
• Can be used to create functions and trigger procedures,
• Adds control structures to the SQL language,
• Can perform complex computations,
• Inherits all user-defined types, functions, and operators,
• Can be defined to be trusted by the server,
• Is easy to use.