SQL

• clustering
  • postgres clustering
• SQL programming
  • unwrap postgresql array into rows
• Postgresql
  • commands
  • Rule
    • 38.4.1. How Update Rules Work

clustering

postgres clustering

High Availability Clustering with PostgreSQL

http://raghavt.blogspot.com/2011/10/high-availability-clustering-with.html

PostgreSQL Fault Tolerance

http://blog.ronvalente.net/devops/postgresql_fault_tolerance

SQL programming

unwrap postgresql array into rows

use unnset

http://www.sqlfiddle.com/#!1/c774a/24

DROP TABLE IF EXISTS em_user CASCADE;
CREATE TABLE em_user
	(user_id int, auth_user integer[]);

INSERT INTO em_user
	(user_id, auth_user)
VALUES
	(1, ARRAY[1,2,3]),
	(2, ARRAY[2,1]),
	(3, ARRAY[3,2,1]),
	(4, ARRAY[4,5]),
	(5, ARRAY[5,1]);

SELECT user_id,unnest(auth_user)"auth_user" 
FROM em_user;

Postgresql

commands

• get pid of postgres

select pg_backend_pid();

top -p 
pmap -x 17731

• Some settings changes in postgresql.conf require a full restart, which will terminate active connections and abort uncommitted transactions:

sudo /etc/init.d/postgresql restart

To stop server

sudo /etc/init.d/postgresql stop

Rule

38.4.1. How Update Rules Work

For ON INSERT rules, the original query (if not suppressed by INSTEAD) is done before any actions added by rules. This allows the actions to see the inserted row(s).

For ON UPDATE and ON DELETE rules, the original query is done after the actions added by rules. This ensures that the actions can see the to-be-updated or to-be-deleted rows; otherwise, the actions might do nothing because they find no rows matching their qualifications.