Caching…!!, its little bit hard to go in brief with single article. But will try to share my knowledge learnt from Heikki / Robert Haas / Bruce Momjianin short. In PostgreSQL, there are two layers, PG shared buffers and OS Page cache, any read/write should pass through OS cache(No bypassing till now). Postgres writes data on OS Page Cache and confirms to user as it has written to disk, later OS cache write’s to physical disk in its own pace. Continue reading →
Back again, getting very less time for blogging :) “ERROR: could not create unique indexDETAIL: Table contains duplicated values."This error is thrown out by Postgres when it encounters duplicate rows in a primary key table by failing any of these command REINDEX or CREATE UNIQUE INDEX. Why duplicate rows exists in a table ? Not sure exactly :) nor any proved explainations out… Two thing to my mind. Firstly, it might be delayed index creation or if you have shared sequences in a database, sharing on two different Primary key Tables might be the cause while restoring the data into table (pg_restore). Continue reading →
Londiste, Asynchronous Master/Slave replication tool developed by Skytools. Its very simple and user-friendly created like Slony. Core logic behind Londiste or Slony is Remote Triggering. Whereas londiste follows events queuing model which is not their in Slony - I. Overview on Skytools: Skytools is a Python-based application, it comes with a bundle of three things PgQ,Londiste & Walmgr and it also requires the Python-Postgres driver ‘psycopg2’. PGQ : Its queue mechanism built with pl/pgsql with phython framework on top of it. Continue reading →
Note: Recommended not to tamper pg_catalogs. On forum, I saw an interesting posting and also the solution, however few things of that solution made me to test it. Scenario is, “How to resize the VARCHAR column on a large table with less time and what are best approach’s”. As known standard way is to, Create a NEW column with desired size, Copy OLD data to newly created column, Drop the OLD column and finally rename the NEW with OLD column name. Continue reading →
Before discussing on deadlocks, lets see type of locks and their acquiring methodolgy in PostgreSQL. Types of Locks: Table-Level Locks and Row-Level Locks Table-Level Locks: AcessShareLock : It acquired automatically by a SELECT statement on the table or tables it retrieves from. This mode blocks ALTER TABLE, DROP TABLE, and *VACUUM (AccessExclusiveLock) *on the same table RowShareLock :It acquired automatically by a SELECT…FOR UPDATE clause. It blocks concurrent ExclusiveLock and *AccessExclusiveLock *on the same table. Continue reading →