Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Improvements In Postgresql 9.4 And Postgres Activity

          With the beta release of PostgreSQL 9.4, DBA's have been given some cool features like pg_prewarm, JSONB, ALTER SYSTEM, Replication Slots and many more. Out of numerous architectural level features presented in this version, likewise there are other few minor enhancements those I have attempted to cover in this blog.
pg_stat_activity view included two new columns (backend_xid/backend_min) to track the transaction id information. pg_stat_activity.backend_xid column covers the id of top-level transaction currently begin executed and pg_stat_activity.backend_xmin column covers the information of minimal running XID. Check out below two query outputs executed in two different situations, first one show the hierarchal information of the transaction id in backend_xmin column of sessions trying to acquire lock(table/Row) on same row, whereas other one just an independent transactions happening without disturbing the same row. This kind of a information help user to know more about the transactions when waiting queries found in the database.
postgres=# select pid,backend_xid,backend_xmin,query from pg_stat_activity where pid<>pg_backend_pid();
 pid  | backend_xid | backend_xmin |           query
-------+-------------+--------------+---------------------------
22351 |        1905 |         1904 | insert into a values (1);
  785 |        1904 |              | insert into a values (1);
12796 |             |         1904 | truncate  a;
12905 |             |         1904 | delete from a ;

postgres=# select pid,backend_xid,backend_xmin,query from pg_stat_activity where pid<>pg_backend_pid();
 pid  | backend_xid | backend_xmin |            query
-------+-------------+--------------+-----------------------------
22351 |             |              | insert into foo values (1);
  785 |        1900 |              | insert into foo values (1);
(2 rows)


New clauses in CREATE TABLESPACE/ALTER TABLESPACE as "with" and "move" options respectively. Similarly, meta command \db+ to give detailed information about the parameters set for a particular TABLESPACE using "with" option.

postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
   [ OWNER user_name ]
   LOCATION 'directory'
   [ WITH ( tablespace_option = value [, ... ] ) ]

Example:
postgres=# create tablespace t1 location '/usr/local/pgpatch/pg/ts' with (seq_page_cost=1,random_page_cost=3);
CREATE TABLESPACE

postgres=# \db+
                                                   List of tablespaces

   Name    |  Owner   |         Location         | Access privileges |               Options                | Description
------------+----------+--------------------------+-------------------+--------------------------------------+-------------
pg_default | postgres |                          |                   |                                      |
pg_global  | postgres |                          |                   |                                      |
t1         | postgres | /usr/local/pgpatch/pg/ts |                   | {seq_page_cost=1,random_page_cost=3} |
(3 rows)

                              New system functions to give information on type regclass,regproc,regprocedure,regoper,regoperator and regtype. For all the types, new functions are to_regclass(), to_regproc(), to_regprocedure(), to_regoper(), to_regoperator() and to_regtype().

Example:
select to_regclass('pg_catalog.pg_class'),to_regtype('pg_catalog.int4'),to_regprocedure('pg_catalog.abs(numeric)'),to_regproc('pg_catalog.now'),to_regoper('pg_catalog.||/');
to_regclass | to_regtype | to_regprocedure | to_regproc | to_regoper
-------------+------------+-----------------+------------+------------
pg_class    | integer    | abs(numeric)    | now        | ||/
(1 row)

New "-g" option in command line utility CREATEUSER to specify role membership.
-bash-4.1$ createuser -g rw -p 10407 r1
-bash-4.1$ psql -p 10407

psql (9.4beta1) Type "help" for help.
postgres=# \dg
                            List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication | {}
r1        |                                                | {rw}

pg_stat_all_tables view, has a new column "n_mod_since_analyze", which highlights on the number of rows has been modified since the table was last analyzed. Below outputs brief about the "n_mod_since_analyze" column changes, first time manual analyze executed and after sometime autovacuum invoked on the table, in this duration we can figure out how many rows effected with different catalog update calls.

postgres=# analyze a;
ANALYZE

postgres=# select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables where relname='a';
relname | last_autoanalyze |         last_analyze          | n_mod_since_analyze
---------+------------------+-------------------------------+---------------------
a       |                  | 2014-05-03 02:09:51.002006-07 |                   0
(1 row)

postgres=# insert into a values(generate_series(1,100));
INSERT 0 100

postgres=# select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables where relname='a';
relname | last_autoanalyze |         last_analyze          | n_mod_since_analyze
---------+------------------+-------------------------------+---------------------
a       |                  | 2014-05-03 02:09:51.002006-07 |                 100
(1 row)

postgres=# truncate a;
TRUNCATE TABLE

postgres=# select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables where relname='a';
relname | last_autoanalyze |         last_analyze          | n_mod_since_analyze
---------+------------------+-------------------------------+---------------------
a       |                  | 2014-05-03 02:09:51.002006-07 |                 100
(1 row)

postgres=# select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables where relname='a';
relname |       last_autoanalyze        |         last_analyze          | n_mod_since_analyze
---------+-------------------------------+-------------------------------+---------------------
a       | 2014-05-03 02:14:21.806912-07 | 2014-05-03 02:09:51.002006-07 |                   0
(1 row)

pg_stat_archiver, its a new view introduced to track all WALs generated and it also captures failed WAL's count. If you are from Oracle then this one is like "ARCHIVE LOG LIST".
postgres=# select * from pg_stat_archiver ;
-[ RECORD 1 ]------+------------------------------
archived_count     | 167
last_archived_wal  | 00000001000000000000009B
last_archived_time | 2014-05-02 20:42:36.230998-07
failed_count       | 75
last_failed_wal    | 000000010000000000000012
last_failed_time   | 2014-05-01 12:09:57.087644-07
stats_reset        | 2014-04-30 19:02:01.288521-07

pg_stat_statements, extension module has a new column queryid to track the internal hash code, computed from the statement's parse tree.
postgres=# select queryid,query from pg_stat_statements;
 queryid   |               query
------------+------------------------------------
1144716789 | select * from pg_stat_statements ;
(1 row)

Comments

Popular posts from this blog

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Database startup / shutdown /restart

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger