Some trigger and programming feature in PostgreSQL

Anonymous Block Support:
Now, user would be able to create anonymous block using plpgsql as given below:

do
$$
declare
   id1 varchar;
begin
   select id into id1 from test_sort limit 1;
   raise notice '%',id1;
end;
$$ language plpgsql;

Output:

NOTICE:  1.1
DO

Column Trigger:
PG9.0 is also supports the column level and When Triggers.

Following is an example:

create or replace function test_col_trigger() returns trigger
as
$$
  BEGIN
      RAISE NOTICE 'INSERTED VALUE in Column a %',new.id;
       RETURN null;
  END;
$$ language plpgsql;

create trigger col_trigger after update of "a" on test_def FOR EACH ROW EXECUTE procedure test_col_trigger();

Output:

postgres=# update test_def set a='Second';
NOTICE:  INSERTED VALUE in Column a 1
UPDATE 1

When Triggers in PG9.0:

PG9.0 has "When trigger" which allowed user to create trigger to execute procedure for a particular condition.

create or replace function test_col_trigger() returns trigger
as
$$
  BEGIN
      RAISE NOTICE 'INSERTED VALUE in Column a %',new.id;
       RETURN null;
  END;
$$ language plpgsql;

create trigger trigger_when AFTER update on test_Def for each row when (old.col1 = new.col2) execute procedure test_col_trigger();

Output:

postgres=# update test_def set a='Second';
NOTICE:  SAME Result inserted a 1
UPDATE 1

There are some other new features has been added in PG9.0.

Important features which DBA must know are following:

1. New VACUUM FULL

Prior to PG9.0, VACUUM FULL was slow, since it used to move the blocks to a block which is closer to beginning of the table. This process used to consume lot of CPU and does the lot of random I/O. With this, after VACUUM FULL, DBA has to reindex the indexes associated with table since, VACUUM FULL used to leave some Bloats/Fragmentation in Indexes.

Overall it was not an efficient method.

However from PG9.0, VACUUM FULL has been coded to work smartly. At the place of moving the tuples, VACUUM FULL will now create a fresh table with new indexes and will destroy the old bloated table. This method is faster than the old method.

2. New column added in pg_stat_activity

application_name is a new column which has been added in pg_stat_activity, which would be giving the information about the application from where connections has been made.

Example is given below:

postgres=# select * from pg_stat_activity where procpid=pg_backend_pid();
 datid | datname  | procpid | usesysid | usename  | application_name | client_addr | client_port |          backend_start           |            xact_start            |           query_start            | waiting |                         current_query                          
-------+----------+---------+----------+----------+------------------+-------------+-------------+----------------------------------+----------------------------------+----------------------------------+---------+----------------------------------------------------------------
 11874 | postgres |    4533 |       10 | postgres | psql             |             |          -1 | 2010-11-02 11:39:13.170051+05:30 | 2010-11-02 12:55:04.278363+05:30 | 2010-11-02 12:55:04.278363+05:30 | f       | select * from pg_stat_activity where procpid=pg_backend_pid();

3. Per database and per role configuration.

4. --analyze-only new option has been added in vacuumdb command.
This option would allow the user to run analyze only using vacuumdb command.
Following is an output of vacuumed —help shows:

-Z, --analyze-only              only update optimizer statistics

For more information other feature in PG9.0, following blog is helpful:
http://wiki.postgresql.org/wiki/Illustrated_9_0