PostgreSQL/Materialized Views/Usage

From Cristiano da Cunha Duarte

Contents 1 Installation 1.1 General installation 1.2 Fedora install cookbook 2 Available Functions 2.1 create_dblink 2.2 drop_dblink 2.3 create_snapshot 2.4 drop_snapshot 2.5 create_snapshot_log 2.6 drop_snapshot_log 2.7 refresh_snapshot 3 Samples 3.1 Complete Refresh 3.1.1 PostgreSQL <-> PostgreSQL (Local) 3.1.2 PostgreSQL <-> PostgreSQL (Remote) 3.1.3 PostgreSQL <-> Oracle 3.1.4 PostgreSQL <-> ODBC 3.1.5 PostgreSQL <-> Sybase 3.1.6 PostgreSQL <-> MySQL 3.1.7 PostgreSQL <-> Firebird 3.2 Fast Refresh 3.2.1 PostgreSQL <-> PostgreSQL (Local) 3.2.2 PostgreSQL <-> PostgreSQL (Remote) 3.2.3 PostgreSQL <-> Oracle 3.3 Prebuilt Tables

Installation

General installation

As database superuser (often postgres, but check for your system), do the following:

1. Load PL/Perlu into your database. See the createlang documents for details on how to do this;

2. Make shure that DBI is installed on your Perl system and that the DBD of the database you choose is also installed;

3. Edit the Makefile.sh file and change the KEY variable to a better "secret" value and the BASE_SCHEMA variable to where the base(internal) Pg::Snapshot tables should be placed. Also remember to setup the remaining variables like SUPERUSER.

4. On the PostgreSQL::Snapshots root, execute:

# ./Makefile.sh

5. Load the database driver:

- On PostgreSQL:

       # psql -d <database> -h <host> -U <user> -f ./drivers/pg/snapshot.sql

- On Oracle, inside SQL+:

       SQL> @./drivers/oracle/snapshot.sql

6. Load the pgsnapshots.sql file:

       # psql -d <database> -h <host> -U <user> -f pgsnapshots.sql

7. Allow the access from your workstation(or remote server) to one or more master tables on the current database:

- Inside psql, conected as POSTGRES user:

       db=# select snapshot_do('<key>', 'ALLOW', '<masterschema>', '<mastername>', '<ip>');

- or inside SQL+, conected as SYS user:

       SQL> begin
                snapshot_do('<key>', 'ALLOW', '<masterschema>', '<mastername>', '<ip>');
            end;
            /

Where:

<key> is the "secret" value placed on the KEY variable inside the Makefile.sh file.

<masterschema> is the schema name of the master table you wish to allow access to

<mastername> is the name of the master table you wish to allow access to

<ip> is the IP address of your workstation/server to whom you wish to give access

8. Use the underlying methods aka functions as needed.

Fedora install cookbook

This is a COOK BOOK on how to setup a database with Pg::Snapshots support on FEDORA CORE.

** IMPORTANT: It assumes that the server does not have postgresql installed **

The commands bellow should be issued by the user "root" and should be followed from top to bottom at this sequence:

# yum install perl-DBD-Pg
# yum install postgresql-server postgresql-plperl
# service postgresql initdb
# service postgresql start
# su - postgres
$ createlang -d template1 plperl
$ createlang -d template1 plperlu
$ createlang -d template1 plpgsql
$ cd /tmp
$ wget http://pgfoundry.org/frs/download.php/1989/pgsnapshot-0.4.0.tgz
$ tar -xvzf pgsnapshot-0.4.0.tgz
$ cd pgsnapshot-0.4.0
$ ./Makefile.sh
$ psql -d template1 -c "create database snaptest;"
$ psql -d snaptest -f drivers/pg/snapshot.sql
$ psql -d snaptest -f pgsnapshots.sql

Available Functions

create_dblink

(implementation of "CREATE DBLINK")
This function creates a link between databases. It takes the name of the DBLINK to be created and the necessary parameters do establish the remote connection.

Syntax:

create_dblink(dblinkname text, datasource text, username text, password text, attributes text)

dblinkname: name of the DBLINK to be created

datasource: Perl:DBI CONNECTION string to the remote database

username: NAME of the remote database user

password: PASSWORD of the remote database user

attributes: connection ATTRIBUTES, like AutoCommit, RaiseErrors, etc.

drop_dblink

(implementation of "DROP DBLINK")
This function removes a link between databases taking only the DBLink name as a parameter.

Syntax:

drop_dblink(dblinkname text)

dblinkname: name of the DBLINK to be removed

create_snapshot

(implementation of "CREATE SNAPSHOT" or "CREATE MATERIALIZED VIEW")
This function creates a materialized view or snapshot based on a query. The query can be referencing a database link or not.

Syntax:

create_snapshot(schemaname text, snapshotname text, query text, dblink text, refresh_method text, prebuilt_table text)

schemaname: name of the schema where the snapshot will be created

snapshotname: name of the snapshot to be created

query: SQL query that will be executed at the remote database and which result will fill the snapshot

dblink: optional parameter that take the name of the DBLink to be used. If the value is NULL, the query will be executed by the local database.

refresh_method: can be "COMPLETE", "FAST" or "FORCE".

prebuilt_table: name of the prebuilt table, on the same schema of the snapshot, over which the snapshot will be created (existing data are preserved). This is an optional parameter.

IMPORTANT: the table will not be filled by this function.

drop_snapshot

(implementation of "DROP SNAPSHOT" or "DROP MATERIALIZED VIEW")
This function removes a materialized view or snapshot taking the schema name and the snapshot name as parameters.

Syntax:

drop_snapshot (schemaname text, snapshotname text)

schemaname: name of the schema where the snapshot resides

snapshotname: name of the snapshot to be removed

create_snapshot_log

(implementation of "CREATE MATERIALIZED VIEW LOG" or "CREATE SNAPSHOT LOG")
This function creates a log table bound to a master table. This log table allows the creation of fast refreshing snapshot(FAST REFRESH).

Syntax:

create_snapshot_log (schemaname text, mastername text, withwhat text)

schemaname: name of the schema where the master table resides

mastername: name of the master table

withwhat: indicate whether the snapshot log should record the primary key, the rowid, or both the primary key and rowid when rows in the master are updated. This clause also specifies whether the snapshot records filter columns, which are non-primary-key columns referenced by subquery snapshots. The syntax is:

• PRIMARY KEY: indicate that the primary key of all rows updated in the master table should be recorded in the snapshot log;
• OID: indicate that the OID of all rows updated in the master table should be recorded in the snapshot log;
• (<filter-columns>): a parenthesis-delimited comma-separated list that specifies the filter columns to be recorded in the snapshot log. For fast-refreshable primary-key snapshots defined with subqueries, all filter columns referenced by the defining subquery must be recorded in the snapshot log;
• Any combination of the above in any order.

drop_snapshot_log

(implementation of "DROP MATERIALIZED VIEW LOG" or "DROP SNAPSHOT LOG")
This function removes a log table previously bound to a master table.

Syntax:

drop_snapshot_log (schemaname text, mastername text)

schemaname: name of the schema where the master table resides

mastername: name of the master table

refresh_snapshot

(implementation of "DBMS_SNAPSHOTS.REFRESH")
This function refreshes the data on a materialized view or snapshot taking the schema and snapshot names as parameters.

Syntax:

refresh_snapshot (schemaname text, snapshotname text)

schemaname: name of the schema where the snapshot resides

snapshotname: name of the snapshot to be refreshed

Samples

Here are some examples on Pg::Snapshots usage:

Complete Refresh

PostgreSQL <-> PostgreSQL (Local)

CREATE TABLE public.test_table (
pk int,
name varchar,
address varchar,
constraint test_table_pk primary key (pk));

INSERT INTO public.test_table VALUES (1, 'albert', '101 Wall Street');
INSERT INTO public.test_table VALUES (2, 'rachel', '1021 10th Street');
INSERT INTO public.test_table VALUES (3, 'chris', '220 20th Street');
INSERT INTO public.test_table VALUES (4, 'joe', '501 Rosewell Street');
INSERT INTO public.test_table VALUES (5, 'john', '321 Lake Street');
INSERT INTO public.test_table VALUES (6, 'linda', '212 Cuba Street');
INSERT INTO public.test_table VALUES (7, 'megan', '56 1st Street');
INSERT INTO public.test_table VALUES (8, 'sarah', '88 Dodgers Street');
INSERT INTO public.test_table VALUES (9, 'anthony', '99 Daemon Av');

select create_snapshot('public', 'test_table_snapshot', 'select * from public.test_table where name ilike a%', null, 'complete', null);
select refresh_snapshot('public', 'test_table_snapshot');
select * from test_table_snapshot;

Result:

 pk |  name   |     address
----+---------+-----------------
  1 | albert  | 101 Wall Street
  9 | anthony | 99 Daemon Av

PostgreSQL <-> PostgreSQL (Remote)

Supposing you have two PostgreSQL servers:

• SERVER 1: IP 172.16.0.1
• SERVER 2: IP 172.16.0.20

On server 1:

CREATE ROLE server1_link LOGIN PASSWORD 'abcdefg';
CREATE TABLE public.source (
	id INT,
	name TEXT,
	CONSTRAINT source_pk PRIMARY KEY (id)
);
GRANT SELECT ON public.source TO server1_link;
INSERT INTO public.source(id, name) VALUES (1,'John Smith');
INSERT INTO public.source(id, name) VALUES(2,'Rob Tomson');
INSERT INTO public.source(id, name) VALUES(3,'Jane Fonda');
INSERT INTO public.source(id, name) VALUES(4,'Albert Einstein');
INSERT INTO public.source(id, name) VALUES(5,'Arnold Swartz');
INSERT INTO public.source(id, name) VALUES(6,'Claire Lindon');
INSERT INTO public.source(id, name) VALUES(7,'Matt Druppal');

SELECT snapshot_do('123456', 'ALLOW', 'public', 'source', '172.16.0.20');

On server 2:

SELECT create_dblink('server1', 'dbi:Pg:dbname=snaptest;host=172.16.0.1', 'server1_link', 'abcdefg', '{AutoCommit => 0}');
SELECT create_snapshot('public', 'source_at_target', 'select * from public.source', 'server1', 'complete', null);
SELECT refresh_snapshot('public', 'source_at_target');

PostgreSQL <-> Oracle

connect scott/tiger@DB

CREATE TABLE test_table (
pk number(8),
name varchar2(100),
address varchar(100),
constraint test_table_pk primary key (pk));

INSERT INTO test_table VALUES (1, 'albert', '101 Wall Street');
INSERT INTO test_table VALUES (2, 'rachel', '1021 10th Street');
INSERT INTO test_table VALUES (3, 'chris', '220 20th Street');
INSERT INTO test_table VALUES (4, 'joe', '501 Rosewell Street');
INSERT INTO test_table VALUES (5, 'john', '321 Lake Street');
INSERT INTO test_table VALUES (6, 'linda', '212 Cuba Street');
INSERT INTO test_table VALUES (7, 'megan', '56 1st Street');
INSERT INTO test_table VALUES (8, 'sarah', '88 Dodgers Street');
INSERT INTO test_table VALUES (9, 'anthony', '99 Daemon Av');

select create_dblink('my_oracle_link', 'dbi:Oracle:DB', 'scott', 'tiger', );
select create_snapshot('public', 'test_table_snapshot', 'select * from scott.test_table where name like a%', 'my_oracle_link', 'complete', null);
select refresh_snapshot('public', 'test_table_snapshot');
select * from test_table_snapshot;

Result:

 pk |  name   |     address
----+---------+-----------------
  1 | albert  | 101 Wall Street
  9 | anthony | 99 Daemon Av

PostgreSQL <-> ODBC

PostgreSQL <-> Sybase

PostgreSQL <-> MySQL

PostgreSQL <-> Firebird

Fast Refresh

PostgreSQL <-> PostgreSQL (Local)

PostgreSQL <-> PostgreSQL (Remote)

Supposing you have two PostgreSQL servers:

• SERVER 1: IP 172.16.0.1
• SERVER 2: IP 172.16.0.20

On server 1:

CREATE ROLE server1_link LOGIN PASSWORD 'abcdefg';
CREATE TABLE public.source (
	id INT,
	name TEXT,
	CONSTRAINT source_pk PRIMARY KEY (id)
);
GRANT SELECT ON public.source TO server1_link;
INSERT INTO public.source(id, name) VALUES (1,'John Smith');
INSERT INTO public.source(id, name) VALUES(2,'Rob Tomson');
INSERT INTO public.source(id, name) VALUES(3,'Jane Fonda');
INSERT INTO public.source(id, name) VALUES(4,'Albert Einstein');
INSERT INTO public.source(id, name) VALUES(5,'Arnold Swartz');
INSERT INTO public.source(id, name) VALUES(6,'Claire Lindon');
INSERT INTO public.source(id, name) VALUES(7,'Matt Druppal');

SELECT snapshot_do('123456', 'ALLOW', 'public', 'source', '172.16.0.20');
SELECT create_snapshot_log('public', 'source', 'PRIMARY KEY');

On server 2:

SELECT create_dblink('server1', 'dbi:Pg:dbname=snaptest;host=172.16.0.1', 'server1_link', 'abcdefg', '{AutoCommit => 0}');
SELECT create_snapshot('public', 'source_at_target', 'select * from public.source', 'server1', 'fast', null);
SELECT refresh_snapshot('public', 'source_at_target');

PostgreSQL <-> Oracle

Prebuilt Tables