Sunday 30 September 2012

Mounting Windows Share Folder on Linux

Method 1:

Install the samba client
   #yum install samba-client

Install the CIFS-Utils
   #yum install cifs-utils

Create  a directory that the share will mount to
   #mkdir /mnt/[shared_folder_name]

Open the NFS share file
   #vi /etc/fstab 

Add this command line in /etc/fstab file
   [windows_IP]:/[shared_folder_name] /mnt/[shared_folder_name]   cifs   
   username=[windows_username],password=[windows_password] 0 0 

Remark:
cifs = filesystem type, a windows share on Linux is recognized as cifs

To remount all filesystem on the linux server
   #mount -a


Method 2: (For VMWare Mounting Windows Host Folder]

Open thw VMWare Machine Settings window. In the Options tab, make sure you enabled the Shared Folders.

Run the vmware mount client to check the name of the shared host folder.
   # /usr/bin/vmware-hgfsclient

Mount the host folder
   # mount -t vmhgfs .host:/[windows_shared_folder] /mnt/hgfs

[Permanent]
Add this command line in /etc/fstab file
   .host:/[shared_folder_name] /mnt/[shared_folder_name]   vmhgfs   default 0 0
   

Friday 28 September 2012

Linux Shell Script

Introduction

1. You can use any editor text to write shell script.
    # vi script.sh

2. Write your shell script & save it.
    press ESC, then  :wq!.
    press ENTER.

3. Set execute permission to the script.
    # chmod +x [your_script_name]
    or
    # chmod 755 [your_script_name]

4. Then, you can execute your script.
    # ./[your_script_name]
    or
    # bash [your_script_name]
    # sh  [your_script_name]

 *****************************************************************************

How to Write a Shell Script? 

Important

There are 2 types of variable:
  • System Variables (SV)                    - in capital letters
    • Syntax :
      • $variable_name
  • User Defined Variables (UDV)      - in lower letters
    • Syntax :
      • variable_name=value
    • UDV variable name must begin with Alphanumeric character or underscore character (_), followed by >= 1 Alphanumeric character.
    • No space on either side of the equal sign.
    • Variables are case-sensitive.
    • Can define NULL variable.
      • variable_name=
      • variable name=""
 Quotes

"Double Quotes"Double Quotes" - Anything enclose in double quotes removed meaning of that characters (except \ and $).
'Single quotes'Single quotes' - Enclosed in single quotes remains unchanged.
`Back quote
`Back quote` - To execute command

Syntax that holds number of arguments specified on command line.
  • $#  

Syntax that refer to all arguments passed to script.

  • $* or $@
Mathematical Operator in  Shell Script MeaningNormal Arithmetical/ Mathematical StatementsBut in Shell
For test statement with if commandFor [ expr ] statement with if command
-eqis equal to5 == 6if test 5 -eq 6if [ 5 -eq 6 ]
-neis not equal to5 != 6if test 5 -ne 6if [ 5 -ne 6 ]
-ltis less than5 < 6if test 5 -lt 6if [ 5 -lt 6 ]
-leis less than or equal to5 <= 6if test 5 -le 6if [ 5 -le 6 ]
-gtis greater than5 > 6if test 5 -gt 6if [ 5 -gt 6 ]
-geis greater than or equal to5 >= 6if test 5 -ge 6if [ 5 -ge 6 ]

OperatorMeaning
string1 = string2string1 is equal to string2
string1 != string2string1 is NOT equal to string2
string1string1 is NOT NULL or not defined 
-n string1string1 is NOT NULL and does exist
-z string1string1 is NULL and does exist

TestMeaning
-s file   Non empty file
-f file   Is File exist or normal file and not a directory 
-d dir    Is Directory exist and not a file
-w file  Is writeable file
-r file   Is read-only file
-x file   Is file is executable


Operator           Meaning
! expressionLogical NOT
expression1  -a  expression2Logical AND
expression1  -o  expression2Logical OR

Standard FileFile Descriptors numberUseExample
stdin0as Standard input Keyboard
stdout1as Standard output Screen
stderr2as Standard error Screen

2>&1 : is used to redirect both standard output and error to /dev/null
*****************************************************************************

Related Commands


To print or acees UDV.
  • $variable_name
To print contains of variable "abc" type
  • echo $abc
To display text or value of variable
  • echo [options] [string, variables...]
Options
  • -n Do not output the trailing new line.
  • -e Enable interpretation of the following backslash escaped characters in the strings:
  • \a alert (bell)
  • \b backspace
  • \c suppress trailing new line
  • \n new line
  • \r carriage return
  • \t horizontal tab
  • \\ backslash
To perform arithmetic operations.
  • expr op1 math-operator op2
math-operator
  • +
  • -
  • /
  • %
  • \*
To print output of an arithmetic operations.(Both are back quotes)
  • echo `expr op1 math-operator op2`
To determine the exit status of command or shell script.
  • $?
To get input (data from user) from keyboard and store (data) to variable.
  • read variable1, variable2, ..., variableN
 To run 2 commands with 1 command line.
  • command1;command2
To sort contents in a file & redirect the output to new file.
sort [file_name] [new_file_name]

To translate all lower case character to upper case letters.
tr "a-z" "A-Z" [file_name] [new_file_name]

[Remark: All  commands line you can use for Shell Script.]

**************************************************************

if condition
then
 command1 if condition is true or if exit status
 of condition is 0 (zero)
 ...
 ...
fi
 
************************************************************** 

if condition
then
 if condition
 then
  .....
  ..
  do this
 else
  ....
  ..
  do this
 fi
else
 ...
 .....
 do this
fi

************************************************************** 
if condition
then
        condition is zero (true - 0)
        execute all commands up to elif statement
elif condition1 
then
        condition1 is zero (true - 0)
        execute all commands up to elif statement  
elif condition2
then
        condition2 is zero (true - 0)
        execute all commands up to elif statement          
else
        None of the above condtion,condtion1,condtion2 are true (i.e. 
        all of the above nonzero or false)
        execute all commands up to fi
fi

************************************************************** 

for { variable name } in { list }
do
        execute one for each item in the list until the list is
        not finished (And repeat all statement between do and done)
done
 
**************************************************************  

for (( expr1; expr2; expr3 ))
do
        .....
 .....
        repeat all statements between do and 
        done until expr2 is TRUE
Done

 
************************************************************** 
while [ condition ]
do
       command1
       command2
       command3
       ..
       ....
done
 
 
************************************************************** 
case  $variable-name  in
      pattern1)   command
                  ...
                  ..
                  command;;
      pattern2)   command
                  ...
                  ..
                  command;;
      patternN)   command
                  ...
                  ..
                  command;;
      *)          command
                  ...
                  ..
                  command;;
esac
 
 
************************************************************** 
function-name ( )
{
    command1
    command2
    .....
    ...
    commandN
    return
} 
 
************************************************************** 
 
**************************************************************
Example 1:

#!/bin/bash

# ask user the enter a value

echo "Enter an order number: [Enter]"
read orderNumber

#postgresql command line to get the information for the entered order number
command=`psql -d [database_name] -U [user_name] -At << EOF
                   select * from [table_name] where [column_name]='$orderNumber';
                   EOF`
#print of the output
echo $commands
#if string $command is NOT NULL and does exit
if [ -n $command ]
then
#command1 if condition is true or if exit status of condition is 0 (zero)...
.....
#end of if condition
fi
#end the shell script
exit 0


**************************************************************
Example 2:


(a) aScript.sh
#!/bin/bash
#if there is no environment path for $APP_HOME
if [-z $APP_HOME]; then       #if two command line in a line, it need to be seperated by semicolon
     echo "ERROR MESSAGE: ....";
     exit 1;
fi

#remove an existed file
if [-e "[file_name]"]
then
   rm [file_name]
fi

#To remove the trailing slash
APP_HOME=${APP_HOME%/}  ;

(b) mainProgram.sh
#!/bin/bash

# run a script in background

./aScript.sh &

#set date format
NOW=`date + %d-%m-%y`
...
.....

 

Thursday 27 September 2012

How to create a table in PostgreSQL

CREATE TABLE Syntax:

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type
,
column_name3 data_type
,
....
)

Data Types:

Text types:

Data type Description
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. Note: The values are sorted in the order you enter them.
You enter the possible values in this format: ENUM('X','Y','Z')
SET Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:

Data type Description
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Constraints:

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT

NOT NULL

CREATE TABLE table_name
(
column_name1 data_type NOT NULL,
column_name2 data_type NULL
,
column_name3 data_type
,
....
)

UNIQUE

CREATE TABLE table_name
(
Method 1: 
column_name1 data_type UNIQUE,
Method 2: 
column_name2 data_type CONSTRAINT unique_constraint_name UNIQUE,
column_name3 data_type
,
....

Method 3:
UNIQUE ([column_name]s...)
 Method 4:
CONSTRAINT [unique_constraint_name] UNIQUE ([column_name]s...)
)


Add a UNIQUE constraint to a PostgreSQL table.


Method 1:
>> CREATE UNIQUE INDEX [constraint_name] 
      ON [table_name] ([column_name1], [column_name2], ..., [column_nameN]);

Method 2:
>> ALTER TABLE [table_name] 
      AND CONSTRAINT [constraint_name]
      UNIQUE ([column_name1], [column_name2], ..., [column_nameN]);

To drop a UNIQUE constraint.
>> ALTER TABLE [table_name]
      DROP CONSTRAINT [constraint_name];

PRIMARY KEY

To create a PRIMARY KEY on the "column_name1".

 
CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type
,
column_name3 data_type
,
....
)


or, 
 
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type
,
column_name3 data_type
,
....

PRIMARY KEY(column_name1)
)

To create a PRIMARY KEY using the "column_name1" and "column_name2" .

 
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type
,
column_name3 data_type
,
....

PRIMARY KEY(column_name1, column_name2)
)


To allow naming of a PRIMARY KEY constraint.


CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type
,
column_name3 data_type
,
....

CONSTRAINT [pk_contraint_name] PRIMARY KEY([column_name]s...)
)



Add a PRIMARY KEY constraint to a PostgreSQL table.

Method 1:
>> ALTER TABLE [table_name] 
     ADD PRIMARY KEY ([column_name]s...);

Method 2:
>> ALTER TABLE [table_name]
     ADD CONSTRAINT [pk_contraint_name] PRIMARY KEY ([column_name]s...);

To drop a PRIMARY KEY constraint.

>> ALTER table [table_name]
      DROP CONSTRAINT [pk_constraint_name];

FOREIGN KEY

To create a FOREIGN KEY on the "column_name1".

 
CREATE TABLE table_nameA
(
column_nameA1 data_type PRIMARY KEY,
column_nameA2 data_type
,
column_nameA3 data_type
,
....



CREATE TABLE table_nameB
(
column_nameB1 data_type PRIMARY KEY,
column_nameB2 data_type
REFERENCES table_nameA (column_nameA1),
column_nameB3 data_type
,
....

)

or,


CREATE TABLE table_nameB
(
column_nameB1 data_type PRIMARY KEY,
column_nameB2 data_type
,
column_nameB3 data_type
,
....

FOREIGN KEY(column_nameB2) REFERENCES table_nameA (column_nameA1)
)
  

To create a FOREIGN KEY using more than one "column_name"

 
CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type
,
column_name3 data_type
,
....

FOREIGN KEY(column_name2, column_name3) 
REFERENCES [other_table](column_C1, column_C2)
)

To allow naming of a FOREIGN KEY constraint.


CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type
,
column_name3 data_type
,
....

CONSTRAINT [fk_contraint_name] FOREIGN KEY ([column_name]s...)
)



Add a FOREIGN KEY constraint to a PostgreSQL table.

Method 1:
>> ALTER TABLE [table_name] 
     ADD FOREIGN KEY ([column_name]s...);

Method 2:
>> ALTER TABLE [table_name]
     ADD CONSTRAINT [fk_contraint_name] FOREIGN KEY ([column_name]s...);

To drop a FOREIGN KEY constraint.

>> ALTER table [table_name]
      DROP CONSTRAINT [fk_constraint_name];

CHECK

CREATE TABLE table_name
(
column_name1 data_type,
Method 1:

column_name2 data_type CHECK ( column_name2 <operator> [value]),
Method 2:
column_name3 data_type CONSTRAINT new_column_name CHECK
( column_name2 <operator> [value]),
Method 3:
CHECK ( column_nameM <operator> [value]),
Method 4:
CONSTRAINT check_constraint_name CHECK ( column_nameN <operator> [value]),
....
)

DEFAULT


CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type
,
column_name3 data_type DEFAULT value
,
....

)


Wednesday 26 September 2012

How to retrieve data from postgreSQL table

To select all data from a table.
>> SELECT * FROM [table_name];

To select some contents of the columns in a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name];

To select some contents of the columns in a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name];

To list only distinct (different) values in a column from a table.
>> SELECT DISTINCT [column_name] FROM [table_name];

To extract only those data that fulfil specified criterion(s).
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_K] <operator> [value_1] 
      AND|OR [column_name_L] <operator> [value_2];
List of operators:
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
 IS                Same to

List of values:
NULL
NOT NULL

To search for a specified pattern in a column.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_K] LIKE <pattern>;

WildcardDescription
%A substitute for zero or more characters
_A substitute for exactly one character
[characters_list]Any single character in characters_list
[^characters_list]or
[!characters_list]
Any single character not in characters_list
 
 Example:
 's%'                - String starts with character 's'
'%love%'        - String contents substring 'love'
's_n'                - String which has 3 characters only which starts with 's' and end with 'n'
'[a-z][A-Z]%' - String that starts with character a-z or A-Z.
'[^0-9]%'        - String that not starts with character 0-9.

To list rows in a table where the specified column in a multiple values.
>>  SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_k] IN (value_1, value_2, ...., value_N);
 example:
>> SELECT * FROM "children" WHERE "fatherName" IN (SELECT "name" FROM "parents");

To sort the result-set by a specified column.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      ORDER BY [column_name_K] ASC|DESC;

To specific the number of records to return.
>>  SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      LIMIT [number_of_records];

To select data from one table and inserts into a different table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     INTO [new_table_name] IN [external_database]
     FROM [old_table_name];

To combine result-set of two or more SELECT statement.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name_1]
     UNION 
     SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name_2];

To give an alias name to a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name] AS [alias_name];

To give an alias name to a column.
>> SELECT [column_name_1], 
     [column_name_2] AS [alias_name_1], ..., 
     [column_name_N] AS [alias_name_2]
     FROM [table_name];

 *************************************************************
 Different PostgreSQL JOIN

JOIN               : Return rows when there is at least one match in both tables (INNER JOIN)
LEFT JOIN   : Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN  : Return rows when there is a match in one of the tables

To join 2 tables.
>> SELECT [table_x].[column_name_1],..., [table_y].[column_name_K]
     FROM [table_x]
     JOIN|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN [table_y]
     ON [table_x].[column_name_A]=[table_y].[column_name_B];



 select  1 from ..., sql语句中的1代表什么意思?查出来是个什么结果?

        select 1 from table;与select anycol(目的表集合中的任意一行) from table;与select * from table 从作用上来说是没有差别的,都是查看是否有记录,一般是作条件查询用的。select 1 from 中的1是一常量(可以为任意数值),查到的所有行的值都是它,但从效率上来说,1>anycol>*,因为不用查字典表

 


测试场景:(转自网络文献)
table表是一个数据表,假设表的行数为10行。
1:select  1 from table       增加临时列,每行的列值是写在select后的数,这条sql语句中是1
2:select count(1)  from table   管count(a)的a值如何变化,得出的值总是table表的行数
3:select sum(1) from table   计算临时列的和

 在SQL SERVER中用 1 测试了一下,发现结果如下:
1:测试结果,得出一个行数和table表行数一样的临时列(暂且这么叫,我也不知道该叫什么),每行的列值是1;
2:得出一个数,该数是table表的行数;
3:得出一个数,该数是table表的行数;
然后我又用“2”测试,结果如下:
1:得出一个行数和table表行数一样的临时列,每行的列值是2;
2:得出一个数,该数是table表的行数;
3:得出一个数,该数是table表的行数×2的数
然后我又用更大的数测试:
1:得出一个行数和table表行数一样的临时列,每行的列值是我写在select后的数;
2:还是得出一个数,该数是table表的行数;
3:得出一个数,该数是table表的行数×写在select后的数
    综上所述:第一种的写法是增加临时列,每行的列值是写在select后的数;第二种是不管count(a)的a值如何变化,得出的值总是table表的行数;第三种是计算临时列的和











XML Schema Tutorial

An XML Schema describes the structure of an XML document.
XML Schema is more powerful than DTDs.
The XML Schema Language is also referred to as XML schema Definition (XSD).

An XML Schema:
  • defines elements that can appear in a document
  • defines attributes that can appear in a document
  • defines which elements are child elements
  • defines the order of child elements
  • defines the number of child elements
  • defines whether an element is empty or can include text
  • defines data types for elements and attributes
  • defines default and fixed values for elements and attributes.

Some benefits of that XML Schemas are written in XML:
  • You don't have to learn a new language
  • You can use your XML editor to edit your Schema files
  • You can use your XML parser to parse your Schema files
  • You can manipulate your Schema with the XML DOM
  • You can transform your Schema with XSLT

 Software: Stylus Studio XML Development Environment

pom.xml

In your pom.xml file, add :
in "repositories" section :

<repository>
     <id>smartgwt</id>
     <url>http://www.smartclient.com/maven2</url>
</repository>

in "dependencies" section :

<dependency>
     <groupId>com.smartgwt</groupId>
     <artifactId>smartgwt</artifactId>
     <version>3.0</version>
</dependency>

PostgreSQL : Part 2


Login to the PostgresSQL command-line interface

    psql -U [username] [database_name]

***********************************************************************
To list all databases
> \l

To list all tables
> \dt

To list all indexs
> \di

To list all sequences
> \ds

To list all views
> \dv

To list all privileges
> \dp

To list all large objects
> \dl

To list all aggregates
> \da

To list all functions
> \df

To list all operators
> \do

To list all types
> \dT

To show all tables, views and sequences.
> \dS+

To show all schema.
> \dn

To show all tables for all schema.
> \dt *.

To show all tables for a schema.
> \dt [schema_name].

To show a table definition under a schema.
> \dt [schema_name].[table_name]

To exit from psql command prompt
> \q

To get the list of the available tables of a database in PostgreSQL.

> SELECT tablename FROM pg_tables
     WHERE tablename NOT LIKE ‘pg\\_%’
     AND tablename NOT LIKE ‘sql\\_%’;


To get the list of columns for a particular table in that database.
> SELECT attname FROM pg_attribute, pg_type
      WHERE typname = ‘table_name‘
      AND attrelid = typrelid
      AND attname NOT IN (‘cmin’, ‘cmax’, ‘ctid’, ‘oid’, ‘tableoid’, ‘xmin’, ‘xmax’);


To view table/row locks in PostgreSQL
> SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted 
      FROM pg_locks l, pg_stat_all_tables t WHERE l.relation=t.relid order by relation asc;

To show all enum label for an enum table.
> SELECT e.enumlabel FROM pg_enum e JOIN pg_type t ON e.enumtypid = t.oid WHERE t.typname = '[enum_table_name]';

How to find tables without primary keys.
> SELECT table_catalog, table_schema, table_name
     FROM information_schema.tables
     WHERE (table_catalog, table_schema, table_name) NOT IN
     (SELECT table_catalog, table_schema, table_name
     FROM information_schema.table_constraints
     WHERE constraint_type = 'PRIMARY KEY')
     AND table_schema NOT IN ('information_schema', 'pg_catalog');


How to find postgreSQL transaction in idle.

> SELECT procpid, datname, usename, client_addr,  current_query 
     FROM pg_stat_activity WHERE current_query!='<IDLE>';
> SELECT pg_cancel_backend (procpid);




To find how long locks have been active by executing the following query:
select
pg_stat_activity.datname,
pg_class.relname,
pg_locks.transactionid,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,
substr(pg_stat_activity.current_query,1,40) as current_query,
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from pg_stat_activity,pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid and usename<>'postgres' order by query_start;


How to check postgreSQL errors.
Important: All postgresql errors are logged to /var/lib/pgsql/9.1/data/pg_log/ .