Disk page checksums to detect filesystem failures in PostgreSQL 9.3Beta 1

New feature introduced in PostgreSQL 9.3Beta 1 i.e. "Disk page checksums". Thanks to authors Simon Riggs, Jeff Davis & Greg Smith.

In earlier releases, if there's any data corruption block on disk it was silently ignored until any pointer arrives on it or some wrong results shown by the queries. Now, data corruption detected beforehand by triggering WARNING message instead of silently using or waiting for hit on the corrupted block.

Disk page checksums feature implementation is unique,  its not plug-able like EXTENSIONs its selectable feature. That's if you need your database should be under monitory umbrella of data corruption then it should be enabled at the time of cluster initialization not on existing or running cluster. Below's the example how it works.

Initialize the cluster with checksums:

initdb [OPTION]... [DATADIR]
   ........
   -k, --data-checksums      use data page checksums
   
initdb -D data_directory -k 

Now, any data corruption found will be notified as below:

postgres=# select * from corruption_test;
WARNING:  page verification failed, calculated checksum 63023 but expected 48009
ERROR:  invalid page in block 0 of relation base/12896/16409

In earlier version,just an error message.

postgres=# select * from corruption_test where id=1;
ERROR:  invalid page header in block 0 of relation base/12870/18192

That's cool right....

So, how do you know whether disk page checksums enabled on the cluster or not ?
As of now, there's no pg_catalog to store such information or any files created in the $PGDATA directory, only pg_control file will hold that information. Using pg_controldata utility you can know about it.

$ export PGDATA=/usr/local/pg93beta/data
$ pg_controldata 
....
....
....
Data page checksum version:           1

Some points on Disk page checksums:
1. Temp tables are excluded from checksums checks.
2. There's performance overhead if checksums enabled as per the PG documentation.
3. Once enabled checksums on a cluster cannot be rolled back.

Thanks
Raghav

What if, import file (txt/csv) having "BOM-ed UTF-8" encoding?

So what is "UTF-8 BOM" mean ? its byte order mark for UTF-8, some bytes (0xEF,0xBB,0xBF) are added at the start of the file to indicate that the file having unicode characters in it. BOM Characters "9".

As per Unicode documentation, the presence of BOM in file are useless, because it causes problems with non-BOM-aware software's to identify or parse the leading characters having at the start. Same has been quoted at the bottom of the Wikipedia page:

Related errors in PostgreSQL:
ERROR: invalid input syntax for integer: "9" (in psql-client)
SQL state: 22P02 (in PgAdmin-III)

Test case & fix on Windows:
Sample file "state_data.txt" created in NOTEPAD with unicode characters in it:

9,Karnataka,कर्नाटक
10,Kerala,केरळा

Table to import data:

create table states(state_code int, state_name char(30), state_in_hindi text);

Error:

postgres=# copy test from 'c:/Pgfile/state_data.txt' with delimiter ',' CSV;
ERROR:  invalid input syntax for integer: "9"
CONTEXT:  COPY test, line 1, column state_code: "9"

To fix, I have used a tool "bomremover.exe" to remove leading characters from a file as its on windows, if its on linux, then there are many tips & tricks available on net to wipe BOM from a utf-8 format file.

Tool Download link and usage:
http://www.mannaz.at/codebase/utf-byte-order-mark-bom-remover/

Eg:-
C:\Pgfile>bomremover.exe . *
Added '.\state_data.txt' to processing list.
Press enter to process all files in the list. (1 files in total)

Processing file '.\state_data.txt'...
Finished. Press Enter to Exit

After running bomremover.exe on file, re-run COPY command which will succeed to import data.

 state_code | state_name | State_name_in_hindi
------------+------------+---------------------
          9 | Karnataka  | αñòαñ░αÑ<8d>αñ¿αñ╛αñƒαñò
         10 | Kerala     | केरळा
(2 rows)

Some of the editors, avoids default saving text with UTF8-BOM:
- Windows - Notepad++ (In Notepade default BOM enabled)
- Linux - VI
- Mac - TextEdit

--Raghav

Configuring .pgpass (Password File) on Windows/Linux/Solaris

PostgreSQL users access to the database goes through libpq library. It also has a feature of allowing automation for users without prompting password by keeping their details in .pgpass file. Lets see...

Points to be considered when setting .pgpass file.
1. It should be created in postgres user home directory.
2. It should disallow any access-level to world or group.
3. Location can be controlled with PGPASSFILE environment variable.

Syntax to follow in .pgpass file:
hostname:port:database:username:password
Eg:
localhost:5432:demo:dbuser:demo@123

On Windows Platform:
1. Open the command prompt as Postgres user. So, any files/directories created will be owned by "postgres" user, no need to give explicit permissions.

runas /user:postgres "cmd"
Note: It prompts for OS postgres user password. It can also be set 
      from command prompt opened as administrator. Right-click on 
      "Command Prompt" option and choose "Runas Administrator".
       Command to set pasword: "net user user_name new_password".

2. Switch to user home directory and create "postgresql" directory and "pgpass.conf" file as below

cd %appdata%
mkdir postgresql
cd postgresql
notepad pgpass.conf  //You can also check the file ownership from command line with "calcs" command.
Eg:
C:\Users\postgres\AppData\Roaming\postgresql>cacls pgpass.conf
C:\Users\postgres\AppData\Roaming\postgresql\pgpass.conf NT AUTHORITY\SYSTEM:(ID)F
                                                         BUILTIN\Administrators:(ID)F
                                                         WIN-G3BNU2NQAI1\postgres:(ID)F

Now connection will be via pgpass.conf file.

On Solaris/Linux platform:
Procedure is same on both of the platforms.

su - postgres     //this will land in the home directory set for postgres user
vi .pgpass.conf    //enter all users entries 
chmod 0600 .pgpass.conf    // change the ownership to 0600 to avoid error like 
-bash-3.2$ psql
WARNING: password file "/opt/PostgreSQL/9.2//.pgpass" has group or world access; permissions should be u=rw (0600) or less

Note: If the environment variable PGPASSWORD is set, then the ~/.pgpass file is not read.

Regards
Raghav

psql client can be a chess board now...

Yes, pgChess extension now allows you to play a chess game with Postgres Instance(computer). Thanks to authors. Installation is very simple with pgxnclient. pgChess extension supported with another extension called pg2podg.
Documentation Link:
https://github.com/gciolli/pgChess/blob/master/doc/

pgxnclient install pgchess
pgxnclient install pg2podg

After installing, just run CREATE EXTENSION commands
 
create extension pgchess;
create extension pg2podg;

Two types of play, (1) PC vs PC and (2) PC vs Human.
To play a new game of PC vs Human run below file (download source from the above link for the files) in unaligned format of psql terminal.

postgres=# \pset format unaligned
postgres=# \i /usr/local/src/pgchess/pgchess-0.1.7/test/sql/play/PG_v_Human.sql

For your move follow below syntax when prompted "Your Move ? ".

        -- For now, we use this syntax:
        --
        --   XXpYYz
        --
        -- where XX is the starting square, YY is the ending square, p
        -- is the piece and z is an optional square where the desired
        -- promotion is specified.

Enjoy….

--Raghav

8 New Oracle compatibility features in EnterpriseDB PPAS 9.2 Beta

Some of new Oracle feature included in PostgresPlus Advanced Server 9.2 beta. Most of them are charm to the product and needed if anyone has migrated from Oracle to PostgreSQL. Many more new features in the product itself, however I would like to walk-through few of them which are related to Oracle compatibility.

1. Object Type Support: In this version, member functions are supported, but not yet MAP member functions.

create or replace type mf_test as object
(
    name varchar2(30),
     member function disp return varchar2
);
create or replace type body mf_test as
    member function disp return varchar2 is
     begin
           return 'Name : '||name;
     end;
end;
edb=# declare
edb-#    v_mf mf_test;
edb$# begin
edb$#    v_mf := mf_test('Raghavendra');
edb$#    dbms_output.put_line(v_mf.disp());
edb$# end;
Name : Raghavendra

EDB-SPL Procedure successfully completed

2. Pl/SQL sub types : Now we can define own subtypes in the declarative part of any PL/SQL block, subprogram, or package.

Syntax: SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];

declare
   subtype hiredate is date not null;
   pfdate hiredate := sysdate;
begin
   dbms_output.put_line(pfdate);
end;
06-OCT-12 19:53:44

EDB-SPL Procedure successfully completed

3. DROP TABLE CASCADE CONSTRAINTS: This option will delete all foriegn key constraints that referencing the table to be dropped, and then drops the table.

edb=# create table master(id int primary key);
edb=# create table master1(id int references master(id));
edb=# \d master1
 Table "enterprisedb.master1"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Foreign-key constraints:
    "master1_id_fkey" FOREIGN KEY (id) REFERENCES master(id)

edb=# drop table master cascade constraints;
NOTICE:  drop cascades to constraint master1_id_fkey on table master1
DROP TABLE
edb=# \d master1
 Table "enterprisedb.master1"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |

4. TYPE's in Package definition:

create or replace package t_pack as 
    type ftype is record(name varchar2(20)); 
end;
edb=# call t_pack.ftype('EDB');
 ftype
-------
 (EDB)
(1 row)

5. TABLE() function call on Nested tables: A TABLE() enables to query a collection in the FROM clause like a table.

CREATE OR REPLACE TYPE string_a IS TABLE OF VARCHAR2(765);
select * from table(string_a('abc','xyz')) ;
column_value
--------------
abc
xyz
(2 rows)

6. IN/OUT of UDT in function call: The functions udtabletype_in and udtabletype_out are now supported for nested tables.

7. Usage on reserved Keywords(LOG/CURRENT_DATE): Now LOG word can be used for naming the function. Also CURRENT_DATE, can be used for variable names.

edb=# create or replace function log(t text) return text as 
begin 
   return t;
end;

edb=# select log('EDB');
log
-----
 EDB
(1 row)

edb=# declare
edb-# current_date date := '07-OCT-2012';
edb$# begin
edb$#    dbms_output.put_line(current_date);
edb$# end;
07-OCT-12 00:00:00

EDB-SPL Procedure successfully completed

8. STRING /NVARCHAR2 datatype support: Now STRING datatype and NVARCHAR2 for multi-byte data is supported for table column. String aliased to VARCHAR2 and NVARCHAR2 maps to varchar of PPAS data-types.

edb=# create table dtype( a string, b nvarchar2);
CREATE TABLE
edb=# \d dtype
       Table "enterprisedb.dtype"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 a      | character varying |
 b      | character varying |

Download & release notes link:
http://www.enterprisedb.com/products-services-training/products/postgres-plus-advanced-server/downloads

--Raghav