Postgres 9.3 feature highlight: FOR KEY SHARE and FOR NO KEY UPDATE

PostgreSQL 3 Responses »
Jan 292013

Prior to PostgreSQL 9.3, there are two levels of locks allowing to control DML operations on a given set of rows for a transaction by using SELECT FOR SHARE and FOR UPDATE. Such locks taken on rows in a transaction block allow blocking INSERT/DELETE/UPDATE on those rows.
There is also a protocol between those lock levels. FOR UPDATE is equivalent to an exclusive lock on the row selected, meaning that no other backend can take a FOR UPDATE lock on the same row and waits until the other other transaction finishes. FOR SHARE means that all the other backends can take a FOR SHARE lock on those rows. No FOR UPDATE locks can be taken on rows already locked with FOR SHARE. It is also possible to use the NOWAIT option, making the server return an error if there is a wait situation.

PostgreSQL 9.3 introduces two new levels of locks: FOR KEY SHARE and FOR NO KEY UPDATE. This feature has been committed thanks to the perseverance of Alvaro Herrera after two years of effort. Really congratulations to Alvaro!
commit 0ac5ad5134f2769ccbaefec73844f8504c4d6182
Author: Alvaro Herrera
Date: Wed Jan 23 12:04:59 2013 -0300
　
Improve concurrency of foreign key locking
　
This patch introduces two additional lock modes for tuples: "SELECT FOR
KEY SHARE" and "SELECT FOR NO KEY UPDATE". These don't block each
other, in contrast with already existing "SELECT FOR SHARE" and "SELECT
FOR UPDATE". UPDATE commands that do not modify the values stored in
the columns that are part of the key of the tuple now grab a SELECT FOR
NO KEY UPDATE lock on the tuple, allowing them to proceed concurrently
with tuple locks of the FOR KEY SHARE variety.
　
Foreign key triggers now use FOR KEY SHARE instead of FOR SHARE; this
means the concurrency improvement applies to them, which is the whole
point of this patch.

The main point of this feature is to reduce lock contention for foreign key triggers, as now those ones use FOR KEY SHARE instead of FOR SHARE. Also, UPDATE commands that do not update columns related to the key of the tuple now take now a FOR NO KEY UPDATE, explaining the name of the lock. With this level of locking, UPDATE queries that do not involve columns of the tuple key can perform concurrently.

Honestly, with now 4 levels of locks, it is becoming complicated to remember which operation blocks or allows the other on the same tuple. So let’s make a couple of tests to determine what blocks what with a simple table with some data:
postgres=# CREATE TABLE aa AS SELECT 1 AS a;
SELECT 1

The test scenario is pretty simple: two client sessions trying to take a lock on the same tuple. Session 1 launches its commands first, then session 2, the goal being to see if session 2 takes the lock or waits for it.
Session 1:
BEGIN;
SELECT * FROM aa FOR $LOCK;

Then session 2 does that
SELECT * FROM aa FOR $LOCK;
$LOCK can be either FOR SHARE, FOR UPDATE, FOR NO KEY UPDATE or FOR KEY SHARE.

Here are the results:

Session 1
Locks UPDATE NO KEY UPDATE SHARE KEY SHARE
Session 2 UPDATE Waits Waits Waits Waits
NO KEY UPDATE Waits Waits Waits OK
SHARE Waits Waits OK OK
KEY SHARE Waits OK OK OK

I hope this table helps. Have fun.

Posted by Michael at 11:59 am Tagged with: , , , , , , , , , , , , , , , , ,

Postgres 9.3 feature highlight: auto-updatable views

PostgreSQL No Responses »
Jan 122013

Prior to PostgreSQL 9.3, trying to execute a DML on a view results in an error. The view is not able to execute directly a query to its parent table.
For example, you can see this kind of behavior in 9.2.
postgres=# CREATE TABLE aa (a int, b int);
CREATE TABLE
postgres=# CREATE VIEW aav AS SELECT * FROM aa;
CREATE VIEW
postgres=# INSERT INTO aav VALUES (1,2);
ERROR: cannot insert into view "aav"
HINT: You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.

Solving that is a matter of using triggers or rules on this view to redirect the given query (INSERT, UPDATE or DELETE) to the wanted parent relation. Here for example by using an INSTEAD rule.
postgres=# CREATE RULE aav_insert AS ON INSERT TO aav
postgres-# DO INSTEAD INSERT INTO aa VALUES (NEW.a, NEW.b);
CREATE RULE
postgres=# INSERT INTO aav VALUES (1,2);
INSERT 0 1
postgres=# select * from aa;
a | b
---+---
1 | 2
(1 row)
postgres=# DROP rule aav_insert ON aav;
DROP RULE

Or with an INSTEAD trigger (trigger on views have been introduced in 9.1).
postgres=# CREATE FUNCTION aav_insert() RETURNS TRIGGER AS $$
DECLARE
　query varchar;
BEGIN
　-- Execute action only for an INSERT
　IF TG_OP = 'INSERT' then
　　query := 'INSERT INTO aa VALUES(' || NEW.a || ', ' || NEW.b || ');';
　　EXECUTE query;
　END IF;
　RETURN NULL;
END;
$$ LANGUAGE plpgsql;
postgres=# CREATE TRIGGER aav_insert_tr INSTEAD OF INSERT ON aav
postgres=# FOR EACH ROW EXECUTE PROCEDURE aav_insert();
postgres=# INSERT INTO aav VALUES (7,99);
INSERT 0 0
postgres=# select * from aav;
a | b
---+----
1 | 2
7 | 99
(2 rows)

PostgreSQL 9.3 introduces a new functionality that allows application programmers not to care anymore about using rules or triggers when executing INSERT, UPDATE or DELETE on views. This feature has been introduced by this commit and is called auto-updatable views.
commit a99c42f291421572aef2b0a9360294c7d89b8bc7
Author: Tom Lane
Date: Sat Dec 8 18:25:48 2012 -0500
　
Support automatically-updatable views.
　
This patch makes "simple" views automatically updatable, without the need
to create either INSTEAD OF triggers or INSTEAD rules. "Simple" views
are those classified as updatable according to SQL-92 rules. The rewriter
transforms INSERT/UPDATE/DELETE commands on such views directly into an
equivalent command on the underlying table, which will generally have
noticeably better performance than is possible with either triggers or
user-written rules. A view that has INSTEAD OF triggers or INSTEAD rules
continues to operate the same as before.
　
For the moment, security_barrier views are not considered simple.
Also, we do not support WITH CHECK OPTION. These features may be
added in future.
　
Dean Rasheed, reviewed by Amit Kapila

This feature presents the advantage to facilitate the maintenance work of the rules and triggers that the application has to write prior to 9.3 in order to run an INSERT/UPDATE/DELETE query directly on a view.

There are multiple cases where views containing cannot be auto-updatable, a couple of examples being views containing clauses like GROUP BY, LIMIT, OFFSET, DISTINCT or HAVING. There are other restrictions so be sure to refer to the documentation for that.

Now let’s have a look at this feature.
postgres=# CREATE TABLE aa (a int, b int);
CREATE TABLE
postgres=# CREATE VIEW aav1 AS SELECT * FROM aa;
CREATE VIEW

Two new system functions called pg_view_is_insertable and pg_view_is_updatable have been introduced to check if a view can receive an INSERT or UPDATE directly.
postgres=# select pg_view_is_updatable('aav1'::regclass),
postgres-# pg_view_is_insertable('aav1'::regclass);
pg_view_is_updatable | pg_view_is_insertable
----------------------+-----------------------
t | t
(1 row)

So it looks to be the case for the view aav1, then let’s try it.
postgres=# INSERT INTO aav1 VALUES (1,2);
INSERT 0 1
postgres=# SELECT * FROM aa;
a | b
---+---
1 | 2
(1 row)
postgres=# UPDATE aav1 SET b = 50 WHERE a = 1;
UPDATE 1
postgres=# SELECT * FROM aa;
a | b
---+----
1 | 50
(1 row)
postgres=# DELETE FROM aav1 WHERE a = 1;
DELETE 1
postgres=# SELECT * FROM aa;
a | b
---+---
(0 rows)

INSERT, UPDATE and DELETE queries have been executed without the need of additional triggers or rules. Yeah.

One last thing, it is possible to check if a view is auto-updatable by looking at its information in information_schema.tables. Let’s add here also the example of a view that cannot be auto-updatable.
postgres=# CREATE VIEW aav2 AS SELECT count(*) FROM aa;
CREATE VIEW
postgres=# SELECT table_name, is_insertable_into
postgres-# FROM information_schema.tables
postgres-# WHERE table_name LIKE 'aav%';
table_name | is_insertable_into
------------+--------------------
aav1 | YES
aav2 | NO
(2 rows)

And I think that’s all about auto-updatable views.
Enjoy!

Posted by Michael at 3:29 pm Tagged with: , , , , , , , , , , , , , ,

Rules in Postgres-XC

PostgreSQL 4 Responses »
May 092012

One of the features that has been really improved the last couple of weeks is the stabilization of remote query planning for DML for Postgres-XC standard planner. And this has consequences on rules, because a rule is fired on Coordinators by design, and you need to provide a global way to plan queries correctly with remote nodes. Just to recall, a rule is the possibility to define an alternative action when doing an INSERT, UPDATE or DELETE on a table.
Another important point is that the query of a rules is not planned at the moment of the rule creation, but after rule is fired, however it doesn’t change the fact that a correct query planning is needed at a moment or another.

A rule can for example be used to define DML on views.
A view is roughly a projection of table data into a wanted shape, and it is by default not possible to define DML actions on it.

Let’s take an example, here are a simple table and a simple view.
postgres=# CREATE TEMP TABLE t1 (a int PRIMARY KEY, b int) DISTRIBUTE BY HASH(a);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
postgres=# INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4);
INSERT 0 4
postgres=# CREATE VIEW t1_v AS SELECT a AS a_v, b AS b_v FROM t1;
NOTICE: view "t1_v" will be a temporary view
CREATE VIEW

When trying to UPDATE the view, you will get the following error.
postgres=# UPDATE t1_v SET b_v = 2 WHERE a_v = 3;
ERROR: cannot update view "t1_v"
HINT: You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.

So let’s define a view on it and check that an UPDATE on a view is possible.
postgres=# CREATE RULE t1_upd AS ON UPDATE TO t1_v DO INSTEAD UPDATE t1 SET b = new.b_v WHERE a = old.a_v AND b = old.b_v;
CREATE RULE
postgres=# UPDATE t1_v SET b_v = 2 WHERE a_v = 3;
UPDATE 1
postgres=# select * from t1_v;
a_v | b_v
-----+-----
1 | 1
2 | 2
4 | 4
3 | 2
(4 rows)

So yes, RULES are now completely supported in Postgres-XC. And it is included in 1.0. The secret of how it works? The thing that took me 4 weeks to figure out?
Well, some extensions have been added in standard planner for DELETE, INSERT and UPDATE remote planning (ModifyTable used as TopPlan). For people willing to look at the code, all the secrets are located in functions create_remoteinsert_plan, create_remoteupdate_plan and create_remotedelete_plan of createplan.c. Those functions have been built and adapted to former scan plan of PostgreSQL to react as a wrapper for inner remote table scans within PostgreSQL standard planner. The trick is to create a DML query generated based on the scan plans generated for each tables that has to be updated, deleted or insert.

One of the limitations? The use of constraints.
Depending on the distribution strategy used, you may not be able to check the consistency of constraints globally.
But this is another story…

Posted by Michael at 9:16 am Tagged with: , , , , , , , , , , , ,

Complex DML queries and clause push-down in Postgres-XC

PostgreSQL No Responses »
Jan 132012

This week, I spent a long time working on this commit. Just by looking at the date, commit happened before leaving for week-end :)
commit 8ef0c48acadec3c9888d302888a7d279d82323e5
Author: Michael P
Date: Fri Jan 13 16:05:00 2012 +0900
　
Improve target list selection for remote DML queries
　
This commit makes remote DML planning generally available
for replicated and hash tables. There are still issues
related to node selection for round robin tables though.
The target list of UPDATE and DELETE using coordinator quals
was set to fetch only CTID when generating SELECT in their
inner plan generated by create_remotequery_plan.
　
Their target list is rewritten to include the columns in quals
so as to be able to evaluate those quals correctly on Coordinator.
In addition remote planning for UPDATE has been improved to be
able to target correct node when launching query.
　
A new regression test called xc_remote is added, it uses the
parameter enable_fast_query_shipping to force all the queries
to go through standard planner. Tests are done on replicated,
hash and round robin tables.

In all the examples of this article, those two tables are used with the following cluster configuration of Postgres-XC cluster.
db=# select node_name, node_type from pgxc_node; -- 1 Coordinator, 2 Datanodes
node_name | node_type
-----------+-----------
coord1 | C
dn1 | D
dn2 | D
(3 rows)
db=# create table aa (a int, b timestamp) distribute by hash(a);
CREATE TABLE
db=# create table bb (a int, b timestamp) distribute by replication;
CREATE TABLE

For database clusters in general, it is essential to have an efficient and consistent way to manage queries on both local and remote nodes. Efficiency is important to reduce data load on the system. Consistency is even more important to avoid dirty data in your database. So, about queries in general, let’s use an example. SELECT queries may contain expressions that can be evaluated on remote nodes. A common example for that is when the expression is a constant.
db=# explain verbose select * from aa where a = 1;
QUERY PLAN
---------------------------------------------------
Data Node Scan (cost=0.00..0.00 rows=0 width=0)
　　Output: a, b
　　Node/s: dn1
　　Remote query: SELECT a, b FROM aa WHERE (a = 1)
(4 rows)

In this case the query can be completely shipped to the remote node, returning correct results.

Expressions that cannot be pushed down are those who need to be evaluated on local nodes with all the necessary data fetched from remote nodes. For example, let’s take the replicated table bb. We want to select data on it with a time-based expression. Each node of the cluster (at least in the case of Postgres-XC) is located on a different server, each server having a different time line.So, is the following SQL shippable?
SELECT a from bb where b < now();
The answer is no. What is necessary to do is to get all the tuples (a,b) from table bb (a is necessary to send back result), and then apply the time based condition on all the results (explaining why b is necessary).
This results in the following plan.
db=# explain verbose select a from bb where b < now();
QUERY PLAN
-----------------------------------------------------------------
　Result (cost=0.00..1.01 rows=1000 width=4)
　Output: a
　-> Data Node Scan on bb (cost=0.00..1.01 rows=1000 width=4)
　　　Output: a, b
　　　Node/s: dn1
　　　Remote query: SELECT a, b FROM ONLY bb WHERE true
　　　Coordinator quals: (bb.b < now())
(7 rows)

Well, Postgres-XC has already a lot of mechanisms to manage SELECT and INSERT queries. But what was missing are the parts related to UPDATE and DELETE. So the new functionality committed this week allows to use complex expressions.
For example, in the case of update, you can run sequence and time based updates needing local node evaluation to run consistently.
db=# insert into bb values (1,now());
INSERT 0 1
db=# insert into bb values (2,now());
INSERT 0 1
db=# insert into bb values (3,now());
INSERT 0 1
db=# select * from bb;
a | b
---+---------------------------------
1 | Fri Jan 13 06:26:32.872665 2012
2 | Fri Jan 13 06:26:38.261489 2012
3 | Fri Jan 13 06:26:40.943182 2012
(3 rows)
db=# update bb set a = nextval('seq'), b = now();
UPDATE 3
db=# select * from bb;
a | b
---+---------------------------------
1 | Fri Jan 13 06:28:01.273496 2012
2 | Fri Jan 13 06:28:01.273496 2012
3 | Fri Jan 13 06:28:01.273496 2012
(3 rows)
db=# explain verbose update bb set a = nextval('seq'), b = now();
QUERY PLAN
-----------------------------------------------------------------------
　Update on public.bb (cost=0.00..11.01 rows=1000 width=6)
　Node/s: dn1, dn2
　Remote query: UPDATE public.bb SET a = $1, b = $2 WHERE ctid = $3
　-> Result (cost=0.00..11.01 rows=1000 width=6)
　　　Output: nextval('seq'::regclass), now(), ctid
　　　-> Data Node Scan on bb (cost=0.00..1.01 rows=1000 width=6)
　　　　　　Output: ctid
　　　　　　Node/s: dn1
　　　　　　Remote query: SELECT ctid FROM ONLY bb WHERE true
(9 rows)

You need here to select all the data to be updated from remote nodes, then you have to apply the time base expression (now) and the sequence value (nextval), and finally push those values to dedicated remote nodes.

This works also with WHERE clauses using non-shippable expressions.
db=# explain verbose update bb set a = nextval('seq'), b = now() WHERE b < now();
QUERY PLAN
---------------------------------------------------------------------------------
　Update on public.bb (cost=0.00..11.02 rows=1000 width=14)
　　Node/s: dn1, dn2
　　Remote query: UPDATE public.bb SET a = $1, b = $2 WHERE b = $3 AND ctid = $4
　　-> Result (cost=0.00..11.02 rows=1000 width=14)
　　　　Output: nextval('seq'::regclass), now(), b, ctid
　　　　-> Data Node Scan on bb (cost=0.00..1.01 rows=1000 width=14)
　　　　　　Output: b, ctid
　　　　　　Node/s: dn1
　　　　　　Remote query: SELECT b, ctid FROM ONLY bb WHERE true
　　　　　　Coordinator quals: (bb.b < now())
(10 rows)

Here what is added is a condition to pre-select a subset of rows. Such operation is costly though because you have to fetch all the rows of the table first in inner plan.

The same kind of crazy SQL are also possible for DELETE with mixing shippable and non-shippable expressions.
db=# explain verbose delete from bb where a = 2 and b < now();
QUERY PLAN
------------------------------------------------------------------------------
　Delete on public.bb (cost=0.00..1.02 rows=1000 width=18)
　Node/s: dn1, dn2
　Remote query: DELETE FROM public.bb WHERE a = $1 AND b = $2 AND ctid = $3
　-> Result (cost=0.00..1.02 rows=1000 width=18)
　　　Output: a, b, ctid
　　　-> Data Node Scan on bb (cost=0.00..1.02 rows=1000 width=18)
　　　　　Output: a, b, ctid
　　　　　Node/s: dn1
　　　　　Remote query: SELECT a, b, ctid FROM ONLY bb WHERE (a = 2)
　　　　　Coordinator quals: (bb.b < now())
(10 rows)

You can notice here that the constant expression "a = 2" is shipped in the most inner plan, improving query efficiency by that much.
A lot of things are now possible, and all this stuff will be included in release 0.9.7!

Posted by Michael at 11:44 pm Tagged with: , , , , , , , , , , , , , ,
©2010-2013 Michael Paquier All content is ©Copyright of Otacoo.com 2010-2013. Privacy Policy - Terms of Use