Database Technologies

1. True serializable isolation level:  Before version 9.1, PostgreSQL did not support a full serializable isolation level. A request for serializable transaction isolation actually provided snapshot isolation. This has well known anomalies which can allow inconsistent views of the data during concurrent transactions; although these anomalies only occur when certain patterns of read-write dependencies exist within a set of concurrent transactions. Where these patterns exist, the anomalies can be prevented by introducing conflicts through explicitly programmed locks or otherwise unnecessary writes to the database. Snapshot isolation is popular because performance is better than serializable isolation and the integrity guarantees which it does provide allow anomalies to be avoided or managed with reasonable effort in many environments.  Following is a link on more details: http://wiki.postgresql.org/wiki/SSI 2. INSERT/UPDATE/DELETE in WITH CLAUSE.  Now in 9.1, User w...

About updateable views user ask many times. Is it supported in PostgreSQL? Can we write Complex updateable views? Answer for above is yes . Till 9.0, we have to use RULE for implementing updateable view. Again, RULE Implementation used to be a bit work, since user has to write multiple RULES to implement this feature. Following code can be use to see this. CREATE TABLE person_detail(pid NUMERIC PRIMARY KEY, pname TEXT); CREATE TABLE person_job(pid NUMERIC PRIMARY KEY references person_detail(pid), job TEXT); INSERT INTO person_detail VALUES(1,'Angela'); INSERT INTO person_detail VALUES(2,'Tom'); INSERT INTO person_detail VALUES(3,'Heikki'); INSERT INTO person_job VALUES(1,'Documenter'); INSERT INTO person_job VALUES(2,'Developer'); INSERT INTO person_job VALUES(3,'Commiter'); CREATE VIEW person_detail_job_vw AS SELECT p.pid, p.pname, j.job FROM person_detail p LEFT JOIN person_job j ON (j.pid=p.pid); SELECT * FROM person_detail...

1. Transaction-level advisory locks: PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly.  Till 9.0, postgreSQL had only Session level locks. Now, in PostgreSQL 9.1, we have transaction level advisory lock. Some examples are given below: BEGIN; -- grabbing txn locks multiple times SELECT pg_advisory_xact_lock(1), pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2); SELECT locktype, classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory' ORDER BY classid, objid, objsubid; COMMIT; Advantage of Advisory lock are given below: 1. Advisory locks are faster 2. It avoid ...

There is always discussion of having UPSERT/MERGE in postgresql. Since, oracle and other RDBMS has this feature, therefore people ask about this feature in PostgeSQL. In previous version of PostgreSQL, we used to implement it using functions. Now in PostgreSQL 9.1, user can implement this feature using Writable CTE. PostgreSQL 9.1, now has Writable CTE using WTH. WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE. Lets see how we can use Writable CTE for UPSERT. Following are SQL Code which can be use in Oracle and PostgreSQL for creating sample data: For oracle: create table myTable (pid ...

1. SQL function format(text, …):  This function is similar to the C function sprintf; However only the following conversion specifications are recognized: %s interpolates the corresponding argument as a string %I escapes its argument as an SQL identifier %L escapes its argument as an SQL literal %% outputs a literal %. A conversion can reference an explicit parameter position by preceding the conversion specifier with n$, where n is the argument position Some Examples are given below: postgres=# select format('%1$s %3$s', 1, 2, 3); format -------- 1 3 (1 row) postgres=# select format('Hello %s', 'World'); format ------------- Hello World (1 row) postgres=# select format('Hello %s %1$s %s', 'World', 'Hello again'); format ------------------------------- Hello World World Hello again (1 row) 2. New string functions concat(), concat_ws(), left(), right(), and reverse() (i). concat() function:...

1. pg_describe_object:  In PostgreSQL 9.1, pg_describe_object function has been added. Using this function, user can get human readable string for understanding the pg_depend content as given below: postgres=# select classid, objid, objsubid, pg_describe_object(classid, objid, objsubid) from pg_depend where classid <> 0 and pg_describe_object(classi classid | objid | objsubid | pg_describe_object ---------+-------+----------+------------------------------------------------ 1247 | 16426 | 0 | type test_money 1247 | 16425 | 0 | type test_money[] 1259 | 16424 | 0 | table test_money 1259 | 16429 | 0 | index test_money_idx 2606 | 16431 | 0 | constraint uniq_constraint on table test_money 2. quote_all_identifiers (boolean) parameter:  With this parameter, User can force quoating of all identifiers in EXMPLAIN and in system catalog functions like pg_get_viewdef(). Example is given below...

PostgreSQL 9.1 has come with new options and Improvement for Client Application. So, I thought to blog about those improvements.   Deprecated createlang/droplang: In PostgreSQL 9.1, createlang/dronlang is now deprecated commands. Since in PostgreSQL 9.1, for creating additional language, user has to use CREATE EXTENSION SYNTAX , there these binaries now issue command given below: CREATE EXTENSION plperl; CREATE EXTENSION plpyhthon; Added Features in psql: In PostgreSQL 9.1, following are the important improvement done in psql command:   1. Know about connection. Now psql has meta command \conninfo which gives detal about current connections. So, if you are connected to PostgreSQL database, then you can use command "\conninfo" to get detail about your connections, some example is given below: postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432". postgres=# \conni...

Since, PostgreSQL 9.1 has already been released and there are lot of new features added in it, so, I thought to blog about each features. Today, I am discussing about Synchronous and Asynchronous Replication supported in PostgreSQL 9.1. For Asynchronous Replication, user can use following method: 1. Change Following Parameters in postgresql.conf file of Primary Database: archive_command = cp -i %p /Users/postgres/archive/%f archive_mode = on max_wal_senders = 3 # Maximum 'wal_senders', processes responsible for managing a connection with a standby server wal_keep_segments = # How many WAL segments (=files) should be kept on the primary, whatever may happen (you won't have to copy them manually on the standby if the standby gets too far behind) wal_level = hot_standby 2. Make Following changes in pg_hba.conf host replication postgres [Ipv4 address of Standby Server]/32 trust host replication postgres [Ipv4 address of Master Server]/32 tr...

Today, I thought to share my Training slides on FTS (Full Text Search) in PostgreSQL 9.0. I have used this presentation on giving training. Enjoy :). Full Text Search Training