PostgreSQL: PostgreSQL Stored Procedures

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SUMMARY: This article provides ten examples of stored procedures in PostgreSQL.

1. Inserting data using a procedure

2. Displaying a message on the screen

3. Using transaction control 

4. Using columns data types

5. Raising notices, warnings, and INFO messages

6. Raising exceptions

7. Traversing values in a table using a FOR loop

8. Using SECURITY INVOKER

9. Using SECURITY DEFINER

10. Setting configuration parameters

 

A stored procedure is a set of structured queries and statements such as control statements and declarations. Here are ten examples of stored procedures that can be useful in different situations.

 

1. Inserting data using a procedure

procedure_demo=# CREATE OR REPLACE PROCEDURE genre_insert_data("GenreId" integer, "Name" character varying)

procedure_demo-# LANGUAGE SQL

procedure_demo-# AS $$

procedure_demo$# INSERT INTO public."Genre" VALUES ("GenreId", "Name");

procedure_demo$# $$;

CREATE PROCEDURE

procedure_demo=# CALL genre_insert_data(26,'Pop');

CALL

procedure_demo=# select * from public."Genre" where "GenreId" = 26;

 GenreId | Name

---------+------

   26 | Pop

(1 row)

 

2. Displaying a message on the screen 

procedure_demo=# CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT)

procedure_demo-# AS $$

procedure_demo$# BEGIN

procedure_demo$# RAISE NOTICE 'Procedure Parameter: %', msg ;

procedure_demo$# END ;

procedure_demo$# $$

procedure_demo-# LANGUAGE plpgsql ;

CREATE PROCEDURE

procedure_demo=# call display_message('This is my test case');

NOTICE:  Procedure Parameter: This is my test case

      msg      

----------------------

 This is my test case

(1 row)

 

3. Using transaction control 

procedure_demo=# CREATE OR REPLACE PROCEDURE control_transaction()

procedure_demo-# LANGUAGE plpgsql

procedure_demo-# AS $$

procedure_demo$# DECLARE

procedure_demo$# BEGIN

procedure_demo$#   CREATE TABLE test1 (id int);

procedure_demo$#   INSERT INTO test1 VALUES (1);

procedure_demo$#   COMMIT;

procedure_demo$#   CREATE TABLE test2 (id int);

procedure_demo$#   INSERT INTO test2 VALUES (1);

procedure_demo$#   ROLLBACK;

procedure_demo$# END $$;

CREATE PROCEDURE

procedure_demo=#

procedure_demo=# call control_transaction();

CALL

procedure_demo=# select relname from pg_class where relname like '%test%';

 relname

---------

 test1

(1 row)

 

Here we can see that data before commit is available, but then the data that is not committed and is rolled back gets dropped from the database.

 

4. Using column data types

procedure_demo=# CREATE OR REPLACE PROCEDURE genre_id_max() LANGUAGE plpgsql AS $$

procedure_demo$# DECLARE

procedure_demo$# id "Genre"."GenreId"%type;

procedure_demo$# BEGIN

procedure_demo$# select max("GenreId") into id from public."Genre";

procedure_demo$# RAISE NOTICE 'Maximum of GenreId is : %', id ;

procedure_demo$# END;

procedure_demo$# $$ ;

CREATE PROCEDURE

procedure_demo=# call genre_id_max();

NOTICE:  Maximum of GenreId is : 26

CALL

 

5. Raising notices, warnings, and INFO messages

procedure_demo=# CREATE OR REPLACE PROCEDURE raise_warning() AS $$

procedure_demo$# DECLARE

procedure_demo$# warn INT := 10;

procedure_demo$# BEGIN

procedure_demo$# RAISE NOTICE 'value of warn : % at %: ', warn, now();

procedure_demo$# warn := warn + 10;

procedure_demo$# RAISE WARNING 'value of warn : % at %: ', warn, now();

procedure_demo$# warn := warn + 10;

procedure_demo$# RAISE INFO 'value of warn : % at %: ', warn, now();

procedure_demo$# END;

procedure_demo$# $$

procedure_demo-# LANGUAGE plpgsql;

CREATE PROCEDURE

procedure_demo=#

procedure_demo=# call raise_warning();

NOTICE:  value of warn : 10 at 2019-12-03 16:25:34.339094+05:30:

WARNING:  value of warn : 20 at 2019-12-03 16:25:34.339094+05:30:

INFO:  value of warn : 30 at 2019-12-03 16:25:34.339094+05:30:

CALL

procedure_demo=#

 

6. Raising exceptions

procedure_demo=# CREATE OR REPLACE PROCEDURE genre_id_exception() LANGUAGE plpgsql AS $$

procedure_demo$# DECLARE

procedure_demo$# id "Genre"."GenreId"%type;

procedure_demo$# BEGIN

procedure_demo$# select max("GenreId") into id from public."Genre";

procedure_demo$# RAISE EXCEPTION 'Maximum of GenreId is : %', id  USING HINT = 'Test For Raising exception.';

procedure_demo$# END;

procedure_demo$# $$ ;

CREATE PROCEDURE

procedure_demo=# call genre_id_exception();

ERROR:  Maximum of GenreId is : 26

HINT:  Test For Raising exception.

CONTEXT:  PL/pgSQL function genre_id_exception() line 6 at RAISE

procedure_demo=#

 

7. Traversing values in a table using a FOR loop 

procedure_demo=# CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql AS $$

procedure_demo$# DECLARE

procedure_demo$# genre_rec record;

procedure_demo$# BEGIN

procedure_demo$# for genre_rec in (select "GenreId","Name" from public."Genre" order by "GenreId")

procedure_demo$#   loop

procedure_demo$# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec."GenreId",genre_rec."Name";

procedure_demo$#   end loop;

procedure_demo$# END;

procedure_demo$# $$ ;

CREATE PROCEDURE

procedure_demo=#

procedure_demo=# call genre_traverse();

NOTICE:  Genre Id is : 1 , Name is : test

NOTICE:  Genre Id is : 2 , Name is : Jazz

NOTICE:  Genre Id is : 3 , Name is : Metal

NOTICE:  Genre Id is : 4 , Name is : Alternative & Punk

NOTICE:  Genre Id is : 5 , Name is : Rock And Roll

NOTICE:  Genre Id is : 6 , Name is : Blues

NOTICE:  Genre Id is : 7 , Name is : Latin

NOTICE:  Genre Id is : 8 , Name is : Reggae

NOTICE:  Genre Id is : 9 , Name is : Pop

NOTICE:  Genre Id is : 10 , Name is : Soundtrack

NOTICE:  Genre Id is : 11 , Name is : Bossa Nova

NOTICE:  Genre Id is : 12 , Name is : Easy Listening

NOTICE:  Genre Id is : 13 , Name is : Heavy Metal

NOTICE:  Genre Id is : 14 , Name is : R&B/Soul

NOTICE:  Genre Id is : 15 , Name is : Electronica/Dance

NOTICE:  Genre Id is : 16 , Name is : World

NOTICE:  Genre Id is : 17 , Name is : Hip Hop/Rap

NOTICE:  Genre Id is : 18 , Name is : Science Fiction

NOTICE:  Genre Id is : 19 , Name is : TV Shows

NOTICE:  Genre Id is : 20 , Name is : Sci Fi & Fantasy

NOTICE:  Genre Id is : 21 , Name is : Drama

NOTICE:  Genre Id is : 22 , Name is : Comedy

NOTICE:  Genre Id is : 23 , Name is : Alternative

NOTICE:  Genre Id is : 24 , Name is : Classical

NOTICE:  Genre Id is : 25 , Name is : Opera

NOTICE:  Genre Id is : 26 , Name is : Pop

CALL

procedure_demo=#

 

8. Using SECURITY INVOKER

SECURITY INVOKER indicates that the procedure is to be executed with the privileges of the user that calls it. That is the default. 

procedure_demo=#  CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql SECURITY INVOKER

procedure_demo-# AS $$

procedure_demo$# DECLARE

procedure_demo$# genre_rec record;

procedure_demo$# BEGIN

procedure_demo$# for genre_rec in (select "GenreId","Name" from public."Genre" order by "GenreId")

procedure_demo$#   loop

procedure_demo$# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec."GenreId",genre_rec."Name";

procedure_demo$#   end loop;

procedure_demo$# END;

procedure_demo$# $$ ;

CREATE PROCEDURE

procedure_demo=# create user test;

CREATE ROLE

procedure_demo=# \c procedure_demo test

You are now connected to database "procedure_demo" as user "test".

procedure_demo=> call genre_traverse();

ERROR:  permission denied for table Genre

CONTEXT:  SQL statement "(select "GenreId","Name" from public."Genre" order by "GenreId")"

PL/pgSQL function genre_traverse() line 5 at FOR over SELECT rows

procedure_demo=>

 

9. Using SECURITY DEFINER

SECURITY DEFINER specifies that the procedure is to be executed with the privileges of the user that owns it. A SECURITY DEFINER procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

In this example we have created a stored procedure with the user “postgres” and called it by using a “test” user who does not have access to the table.

procedure_demo=# CREATE OR REPLACE PROCEDURE genre_traverse() LANGUAGE plpgsql SECURITY DEFINER

procedure_demo-# AS $$

procedure_demo$# DECLARE

procedure_demo$# genre_rec record;

procedure_demo$# BEGIN

procedure_demo$# for genre_rec in (select "GenreId","Name" from public."Genre" order by "GenreId")

procedure_demo$#   loop

procedure_demo$# RAISE NOTICE 'Genre Id is : % , Name is : %', genre_rec."GenreId",genre_rec."Name";

procedure_demo$#   end loop;

procedure_demo$# END;

procedure_demo$# $$ ;

CREATE PROCEDURE

procedure_demo=#

procedure_demo=#

procedure_demo=# \c procedure_demo test

You are now connected to database "procedure_demo" as user "test".

procedure_demo=> call genre_traverse();

NOTICE:  Genre Id is : 1 , Name is : test

NOTICE:  Genre Id is : 2 , Name is : Jazz

NOTICE:  Genre Id is : 3 , Name is : Metal

NOTICE:  Genre Id is : 4 , Name is : Alternative & Punk

NOTICE:  Genre Id is : 5 , Name is : Rock And Roll

NOTICE:  Genre Id is : 6 , Name is : Blues

NOTICE:  Genre Id is : 7 , Name is : Latin

NOTICE:  Genre Id is : 8 , Name is : Reggae

NOTICE:  Genre Id is : 9 , Name is : Pop

NOTICE:  Genre Id is : 10 , Name is : Soundtrack

NOTICE:  Genre Id is : 11 , Name is : Bossa Nova

NOTICE:  Genre Id is : 12 , Name is : Easy Listening

NOTICE:  Genre Id is : 13 , Name is : Heavy Metal

NOTICE:  Genre Id is : 14 , Name is : R&B/Soul

NOTICE:  Genre Id is : 15 , Name is : Electronica/Dance

NOTICE:  Genre Id is : 16 , Name is : World

NOTICE:  Genre Id is : 17 , Name is : Hip Hop/Rap

NOTICE:  Genre Id is : 18 , Name is : Science Fiction

NOTICE:  Genre Id is : 19 , Name is : TV Shows

NOTICE:  Genre Id is : 20 , Name is : Sci Fi & Fantasy

NOTICE:  Genre Id is : 21 , Name is : Drama

NOTICE:  Genre Id is : 22 , Name is : Comedy

NOTICE:  Genre Id is : 23 , Name is : Alternative

NOTICE:  Genre Id is : 24 , Name is : Classical

NOTICE:  Genre Id is : 25 , Name is : Opera

NOTICE:  Genre Id is : 26 , Name is : Pop

CALL

procedure_demo=>

 

10. Setting configuration parameters

The effects of a SET LOCAL command for a variable are restricted to the procedure inside which the command is executed; the configuration parameter's prior value is restored after exiting the procedure. However, a simple SET command (without LOCAL) overrides the SET clause, much as it would do for a previous SET LOCAL command. The effects of the configuration will persist after procedure exit, unless the current transaction is rolled back.

procedure_demo=# CREATE OR REPLACE PROCEDURE datestyle_change() LANGUAGE plpgsql SET datestyle TO postgres, dmy

procedure_demo-# AS $$

procedure_demo$# BEGIN

procedure_demo$# RAISE NOTICE 'Current Date is : % ', now();

procedure_demo$# END;

procedure_demo$# $$ ;

CREATE PROCEDURE

procedure_demo=# call datestyle_change();

NOTICE:  Current Date is : Tue 03 Dec 18:09:06.824398 2019 IST

CALL

procedure_demo=# select now();

            now            

----------------------------------

 2019-12-03 18:09:12.930919+05:30

(1 row)



procedure_demo=#

 

Here, we changed the current datestyle (ISO, MDY) to “postgres, dmy” using SET, and the configuration is still effective outside the procedure.

SQL Server : SQL Server Different Types of Cursors

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

A Cursor allow us to retrieve data from a result set in singleton fashion means row by row. Cursor are required when we need to update records in a database table one row at a time. I have already explained the SQL Server Basics of Cursors.

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.

You should avoid the use of the cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Types of Cursors

1. Static Cursors

   A static cursor populates the result set at the time of cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward direction. A static cursor is slower and use more memory in comparison to other cursor. Hence you should use it only if scrolling is required and other types of cursors are not suitable.

   No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened). By default static cursors are scrollable. SQL Server static cursors are always read-only.

2. Dynamic Cursors

   A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations. By default dynamic cursors are scrollable.

3. Forward Only Cursors

   A forward only cursor is the fastest cursor among the all cursors but it doesn't support backward scrolling. You can update, delete data using Forward Only cursor. It is sensitive to any changes to the original data source.

   There are three more types of Forward Only Cursors.Forward_Only KEYSET, FORWARD_ONLY STATIC and FAST_FORWARD.

   A FORWARD_ONLY STATIC Cursor is populated at the time of creation and cached the data to the cursor lifetime. It is not sensitive to any changes to the data source.

   A FAST_FORWARD Cursor is the fastest cursor and it is not sensitive to any changes to the data source.

4. Keyset Driven Cursors

   A keyset driven cursor is controlled by a set of unique identifiers as the keys in the keyset. The keyset depends on all the rows that qualified the SELECT statement at the time of the cursor was opened. A keyset driven cursor is sensitive to any changes to the data source and supports update, delete operations. By default keyset driven cursors are scrollable.

SQL SERVER – Examples of Cursors

 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee 

Static Cursor - Example

 SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF 

Dynamic Cursor - Example

 --Dynamic Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empupdate CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Mohan'
 Update Employee SET Salary=15000 WHERE CURRENT OF Dynamic_cur_empupdate
 FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empupdate
DEALLOCATE Dynamic_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

 -- Dynamic Cursor for DELETE
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Dynamic_cur_empdelete CURSOR
DYNAMIC 
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Dynamic_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Deepak'
 DELETE Employee WHERE CURRENT OF Dynamic_cur_empdelete
 FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id,@name
 END
END
CLOSE Dynamic_cur_empdelete
DEALLOCATE Dynamic_cur_empdelete
SET NOCOUNT OFF
Go
Select * from Employee 

Forward Only Cursor - Example

 --Forward Only Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empupdate CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 Update Employee SET Salary=24000 WHERE CURRENT OF Forward_cur_empupdate
 FETCH NEXT FROM Forward_cur_empupdate INTO @Id,@name
 END
END
CLOSE Forward_cur_empupdate
DEALLOCATE Forward_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

 -- Forward Only Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Forward_cur_empdelete CURSOR
FORWARD_ONLY
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Forward_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Sonu'
 DELETE Employee WHERE CURRENT OF Forward_cur_empdelete
 FETCH NEXT FROM Forward_cur_empdelete INTO @Id,@name
 END
END
CLOSE Forward_cur_empdelete
DEALLOCATE Forward_cur_empdelete
SET NOCOUNT OFF
 Go
Select * from Employee 

Keyset Driven Cursor - Example

 -- Keyset driven Cursor for Update
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Keyset_cur_empupdate CURSOR
KEYSET
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empupdate
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Pavan'
 Update Employee SET Salary=27000 WHERE CURRENT OF Keyset_cur_empupdate
 FETCH NEXT FROM Keyset_cur_empupdate INTO @Id,@name
 END
END
CLOSE Keyset_cur_empupdate
DEALLOCATE Keyset_cur_empupdate
SET NOCOUNT OFF
 Go
Select * from Employee 

 -- Keyse Driven Cursor for Delete
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
 DECLARE Keyset_cur_empdelete CURSOR
KEYSET
FOR 
SELECT EmpID,EmpName from Employee ORDER BY EmpName
OPEN Keyset_cur_empdelete
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
 WHILE @@Fetch_status = 0
 BEGIN
 IF @name='Amit'
 DELETE Employee WHERE CURRENT OF Keyset_cur_empdelete
 FETCH NEXT FROM Keyset_cur_empdelete INTO @Id,@name
 END
END
CLOSE Keyset_cur_empdelete
DEALLOCATE Keyset_cur_empdelete
SET NOCOUNT OFF
 Go Select * from Employee 

 

PostgreSQL: Postgres Locking Revealed

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Postgres Locking Revealed

Locking is a very important part of PostgreSQL (as well as most of other modern RDBMS). It should be familiar to every developer of DB applications (especially to those who are working on highly concurrent code). Locking is mostly addressed and researched when some problem arises. In most cases, these problems relate to deadlock or data inconsistency due to misunderstanding how locking works in Postgres. Despite its importance, locking mechanism in Postgres is poorly documented and doesn’t even work as expected in some cases (as documentation suggests). I will hereby try to present everything we should know in order to work with Postgres, having in mind locking mechanisms as well as faster resolution of locking issues.

What Does Documentation Say?

Generally in Postgres we have 3 mechanisms of locking: table-level, row-level and advisory locks. Table and row level locks can be explicit or implicit. Advisory locks are mainly explicit. Explicit locks are acquired on explicit user requests (with special queries) and implicit are acquired by standard SQL commands.

In addition to table and row locks, page-level share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers normally need not be concerned with page-level locks.

Locking mechanisms have changed in time, so I will cover locking in 9.x versions of Postgres. Versions 9.1 and 9.2 are mainly the same, and versions 9.3 and 9.4 have some differences mainly connected to row-level locking.

Table-level Locks

Most of the table-level locks are acquired by built-in SQL commands, but they can also be acquired explicitly with LOCK command. Available table-level locks are:

• ACCESS SHARE – The SELECT command acquires this lock on table(s) referenced in query. General rule is that all queries that only read table acquire this lock.
• ROW SHARE – The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire this lock on target table (as well as ACCESS SHARE lock on all referenced tables in query).
• ROW EXCLUSIVE – The UPDATE, INSERT and DELETE commands acquire this lock on target table (as well as ACCESS SHARE lock on all referenced tables in query). General rule is that all queries that modify table acquire this lock.
• SHARE UPDATE EXCLUSIVE – The VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE commands acquire this lock.
• SHARE – The CREATE INDEX command acquires lock on table referenced in query.
• SHARE ROW EXCLUSIVE – Not acquired implicitly by any command.
• EXCLUSIVE – This lock mode allows only reads to process in parallel with transaction that acquired this lock. It is not acquired implicitly by any command.
• ACCESS EXCLUSIVE – The ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM FULL commands acquire lock on table referenced in query. This mode is default mode of LOCK command.

It is important to know that all these locks are table-level locks, even if they have ROW in their name.

The most important info for every mode is the list of modes which are in conflict with each other. Two transactions can’t hold locks on conflicting modes on the same table at the same time. Transaction is never in conflict with itself. Non-conflicting locks can be held concurrently by many transactions. It is also important to know that there are self-conflicting modes. When some lock mode is acquired it is held until end of transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to. Here is the table which shows which modes are in conflict with each other:

Row-level Locks

In Postgres 9.1 and 9.2 there are two row-level lock modes, but on Postgres 9.3 and 9.4 there are four modes.

Postgres doesn’t remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.

ROW-LEVEL LOCKS IN POSTGRES 9.1 AND 9.2

In these versions there are only two kinds of row-level locks: exclusive or share lock. An exclusive row level lock is automatically acquired when row is updated or deleted. Row-level locks don’t block data querying, they block just writes to the same row. Exclusive row-level lock can be acquired explicitly without the actual changing of the row with SELECT FOR UPDATE command.

Share row-level lock can be acquired with SELECT FOR SHARE command. A shared lock does not prevent other transactions from acquiring the same shared lock. However, no transaction is allowed to update, delete, or exclusively lock a row on which any other transaction holds a shared lock.

ROW-LEVEL LOCKS IN POSTGRES 9.3 AND 9.4

In Postgres 9.3 and 9.4 there are four types of row-level locks:

• FOR UPDATE – This mode causes the row fetched with SELECT to be locked for update.This prevents them from being locked, modified or deleted by other transactions. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked. This mode is also acquired by DELETE on a row, and also UPDATE of some columns (currently the set of columns considered are those that have unique index on them that can be used as foreign key – but this may change in future)
• FOR NO KEY UPDATE – This mode is similar as FOR UPDATE but it is weaker – it will not block SELECT FOR KEY SHARE lock mode. It is acquired by UPDATE command that doesn’t acquire FOR UPDATE lock.
• FOR SHARE – This mode is similar as FOR NO KEY UPDATE except it acquires share lock (not exclusive). A shared lock blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE on these rows, but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE.
• FOR KEY SHARE – Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but doesn’t prevent any other UPDATE, nor SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.

Conflicts on row-level locks:

	CURRENT LOCK MODE
	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				x
FOR SHARE			x	x
FOR NO KEY UPDATE		x	x	x
FOR UPDATE	x	x	x	x

Advisory Locks

Postgres provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly.

There are two ways to acquire an advisory lock in Postgres: at session level or at transaction level. Once acquired at session level, an advisory lock is held until explicitly released or the session ends. Unlike standard lock requests, session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later. A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released. Transaction-level lock requests, on the other hand, behave more like regular lock requests: they are automatically released at the end of the transaction, and there is no explicit unlock operation. This behavior is often more convenient than the session-level behavior for short-term usage of an advisory lock. Session-level and transaction-level lock requests for the same advisory lock identifier will block each other in the expected way. If a session already holds a given advisory lock, additional requests by it will always succeed, even if other sessions are awaiting the lock; this statement is true regardless of whether the existing lock hold and new request are at session level or transaction level. Complete list of functions for manipulating advisory locks can be found in documentation.

Here are some samples with acquiring transaction level exclusive advisory locks (pg_locks is system view, described latter in this post. It holds information about transaction holding table-level and advisory locks.):

Open first psql session, open a transaction and acquire an advisory lock:

-- Transaction 1
BEGIN;

SELECT pg_advisory_xact_lock(1);
-- Some work here

Now open a second psql session and run a new transaction on the same advisory lock:

-- Transaction 2
BEGIN;

SELECT pg_advisory_xact_lock(1);
-- This transaction is now blocked

In the third psql session we can check check what this lock looks like:

SELECT * FROM pg_locks;
-- Only relevant parts of output
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted |fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
    advisory   |    16393 |          |      |       |            |               |       0 |     1 |        1 | 4/36               |  1360 | ExclusiveLock       | f       | f
    advisory   |    16393 |          |      |       |            |               |       0 |     1 |        1 | 3/186              | 14340 | ExclusiveLock
    ```

-- Transaction 1
COMMIT;
-- This transaction now released lock, so Transaction 2 can continue

We can also make a call to non-blocking methods for acquiring locks. These methods will try to acquire lock, and return true (if they succeeded in that) or false (if lock can’t be acquired).

-- Transaction 1
BEGIN;

SELECT pg_advisory_xact_lock(1);
-- Some work here

-- Transaction 2
BEGIN;

SELECT pg_try_advisory_xact_lock(1) INTO vLockAcquired;
IF vLockAcquired THEN
-- Some work
ELSE
-- Lock not acquired
END IF;

-- Transaction 1
COMMIT;

and now some practice…

Monitoring Locks

Base station for monitoring locks held by all active transactions is pg_locks system view. This view contains one row per active lockable object, requested lock mode and relevant transaction. What is very important to know is that pg_locks holds info about locks that are tracked in memory, so it doesn’t show row-level locks! This view shows table-level and advisory locks. If a transaction is waiting for a row-level lock, it will usually appear in the view as waiting for the permanent transaction ID of the current holder of that row lock. This is something which makes debugging of row level locks much harder. In fact, you will not see row level locks anywhere, until someone blocks the transaction that holds it (then you will see a tuple that is locked in pg_locks table). pg_locks is not very readable view (not very human friendly) so lets make our view which will show locking information more acceptable to us:

-- View with readable locks info and filtered out locks on system tables

CREATE VIEW active_locks AS
SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype, pg_locks.database,
       pg_locks.relation, pg_locks.page, pg_locks.tuple, pg_locks.virtualtransaction,
       pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active_locks';

-- Now when we want to see locks just type
SELECT * FROM active_locks;

Now we have a playground for some experiments…

Simple Examples

Let’s create some tables for practicing:

CREATE TABLE parent (
  id serial NOT NULL PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE child (
  id serial NOT NULL PRIMARY KEY,
  parent_id int4 NOT NULL,
  name text NOT NULL,
  CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id)
);

and try some simple transactions to check how locking looks like:

BEGIN;

SELECT * FROM active_locks; -- There are no active locks yet
 clock_timestamp | relname | locktype | database | relation | page | tuple | virtualtransaction | pid | mode | granted
-----------------+---------+----------+----------+----------+------+-------+--------------------+-----+------+---------
(0 rows)

INSERT INTO parent (name) VALUES ('Parent 1');

SELECT * FROM active_locks;
      clock_timestamp       |    relname    | locktype | database | relation | page | tuple | virtualtransaction | pid  |       mode       | granted
----------------------------+---------------+----------+----------+----------+------+-------+--------------------+------+------------------+---------
 2015-04-12 13:43:02.896+02 | parent_id_seq | relation |    16393 |    16435 |      |       | 3/150              | 9000 | AccessShareLock  | t
 2015-04-12 13:43:02.896+02 | parent        | relation |    16393 |    16437 |      |       | 3/150              | 9000 | RowExclusiveLock | t
(2 rows)

COMMIT;

SELECT * FROM active_locks;
 clock_timestamp | relname | locktype | database | relation | page | tuple | virtualtransaction | pid | mode | granted
-----------------+---------+----------+----------+----------+------+-------+--------------------+-----+------+---------
(0 rows)

We can see that after inserting one row in parent table, we acquired ROW EXCLUSIVE lock on parent table. parent_id_seq is sequence for parent’s primary key. As this relations is selected (as table) we acquired ACCESS SHARE lock on it.

Let’s try to insert something in child table;

BEGIN;

INSERT INTO child (parent_id, name) VALUES (1, 'Child 1 Parent 1');

SELECT * FROM active_locks;
      clock_timestamp      |   relname    | locktype | database | relation | page | tuple | virtualtransaction | pid  |       mode       | granted
---------------------------+--------------+----------+----------+----------+------+-------+--------------------+------+------------------+---------
 2015-04-12 13:50:48.17+02 | parent_pkey  | relation |    16393 |    16444 |      |       | 3/152              | 9000 | AccessShareLock  | t
 2015-04-12 13:50:48.17+02 | parent       | relation |    16393 |    16437 |      |       | 3/152              | 9000 | RowShareLock     | t
 2015-04-12 13:50:48.17+02 | child_id_seq | relation |    16393 |    16446 |      |       | 3/152              | 9000 | AccessShareLock  | t
 2015-04-12 13:50:48.17+02 | child        | relation |    16393 |    16448 |      |       | 3/152              | 9000 | RowExclusiveLock | t
(4 rows)

COMMIT;

Now the situation is more interesting. We can see additional ROW SHARE lock on parent table. What we can’t see here is that this INSERT also acquired row-level share lock on row referenced in parent table. We can see this with executing two transactions in parallel:

-- Transaction 1
BEGIN;

INSERT INTO child (parent_id, name) VALUES (1, 'Child 2 Parent 1');

-- Transaction 2
BEGIN;

DELETE FROM parent WHERE id = 1;

Now open a third session and let’s see what our locks look like:

SELECT * FROM active_locks;
      clock_timestamp       |   relname    | locktype | database | relation | page | tuple | virtualtransaction | pid  |        mode         | granted
----------------------------+--------------+----------+----------+----------+------+-------+--------------------+------+---------------------+---------
 2015-04-12 14:18:35.005+02 | parent_pkey  | relation |    16393 |    16444 |      |       | 4/32               | 4428 | RowExclusiveLock    | t
 2015-04-12 14:18:35.005+02 | parent       | relation |    16393 |    16437 |      |       | 4/32               | 4428 | RowExclusiveLock    | t
 2015-04-12 14:18:35.005+02 | parent_pkey  | relation |    16393 |    16444 |      |       | 3/153              | 9000 | AccessShareLock     | t
 2015-04-12 14:18:35.005+02 | parent       | relation |    16393 |    16437 |      |       | 3/153              | 9000 | RowShareLock        | t
 2015-04-12 14:18:35.005+02 | child_id_seq | relation |    16393 |    16446 |      |       | 3/153              | 9000 | AccessShareLock     | t
 2015-04-12 14:18:35.005+02 | child        | relation |    16393 |    16448 |      |       | 3/153              | 9000 | RowExclusiveLock    | t
 2015-04-12 14:18:35.005+02 | parent       | tuple    |    16393 |    16437 |    0 |     1 | 4/32               | 4428 | AccessExclusiveLock | t
(7 rows)

DELETE query is blocked, waiting for transaction 1 to finish. We can see that it acquired a lock on tuple 1. But why is DELETE query blocked if we can see that all locks are granted? These two transactions are not synchronized on lock on any relation. In fact if one transaction holds a lock on some row that and second transaction asks for that lock, second transaction will then try to acquire SHARE lock on transaction that holds that lock. When first transaction finishes, second transaction will continue. This is possible because each transaction holds EXCLUSIVE lock on itself. We can see this is pg_locks view, and here is the output (just part that is important):

locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted |fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 transactionid |          |          |      |       |            |           707 |         |       |          | 3/153              |  9000 | ExclusiveLock       | t       | f
 transactionid |          |          |      |       |            |           707 |         |       |          | 4/32               |  4428 | ShareLock           | f       | f
 transactionid |          |          |      |       |            |           708 |         |       |          | 4/32               |  4428 | ExclusiveLock       | t       | f

We can see that transactions 707 (pid 9000) and 708 (pid 4428) holds EXCLUSIVE lock on their transaction ids, and that transaction 708 tries to acquire SHARE lock on transaction 707.

Now, the most interesting sample. We can play with updating child table but without actually changing anything correlated to parent table (in this case parent_id column).

BEGIN;

UPDATE child SET name = 'My new name' WHERE id = 1;

SELECT * FROM active_locks;
      clock_timestamp       |  relname   | locktype | database | relation | page | tuple | virtualtransaction | pid  |       mode       | granted
----------------------------+------------+----------+----------+----------+------+-------+--------------------+------+------------------+---------
 2015-04-14 09:05:42.713+02 | child_pkey | relation |    16393 |    16455 |      |       | 3/183              | 3660 | RowExclusiveLock | t
 2015-04-14 09:05:42.713+02 | child      | relation |    16393 |    16448 |      |       | 3/183              | 3660 | RowExclusiveLock | t
(2 rows)

UPDATE child SET name = 'My new name' WHERE id = 1;

SELECT * FROM active_locks;
      clock_timestamp       |   relname   | locktype | database | relation | page | tuple | virtualtransaction | pid  |       mode       | granted
----------------------------+-------------+----------+----------+----------+------+-------+--------------------+------+------------------+---------
 2015-04-14 09:05:45.765+02 | parent_pkey | relation |    16393 |    16444 |      |       | 3/183              | 3660 | AccessShareLock  | t
 2015-04-14 09:05:45.765+02 | parent      | relation |    16393 |    16437 |      |       | 3/183              | 3660 | RowShareLock     | t
 2015-04-14 09:05:45.765+02 | child_pkey  | relation |    16393 |    16455 |      |       | 3/183              | 3660 | RowExclusiveLock | t
 2015-04-14 09:05:45.765+02 | child       | relation |    16393 |    16448 |      |       | 3/183              | 3660 | RowExclusiveLock | t
(4 rows)

COMMIT;

This is very interesting and most important to remember. As we can see we are executing UPDATE query which doesn’t touch anything related to parent table. After first execution we can see that just child table contains table level locks here. Same thing is for row-level locks. Only child table’s row is locked FOR UPDATE. This is optimization that exists in Postgres. If locking manager can figure out from the first query that foreign key is not changed (it is not mentioned in update query or is set to same value) it will not lock parent table. But in second query it will behave as it is described in documentation (it will lock parent table in ROW SHARE locking mode and referenced row in FOR SHARE mode). This is very dangerous because it can lead to the most dangerous and hardest to find deadlocks. We can use explicit locking at the beggining of transaction to avoid this. Behavior is different on Postgres 9.1 and 9.2 on one side, and 9.3 and 9.4 on another. The difference is related to row level locking. Postgres 9.3 and 9.4 will acquire weaker FOR KEY SHARE locking mode on parent table. This locking mode is not in conflict with FOR NO KEY UPDATE mode so they can be acquired in parallel by two transactions. This is much better, so there is less chance for deadlocks in 9.3 and 9.4.

The best way to prevent deadlocks, when we are aware they can happen between two transactions, is to acquire row level locks in some order (for example ordered by primary key) and to acquire most restrictive locks first. Having in mind Postgres optimization with locking described in previous paragraph, explicit locking is sometimes the only way to avoid deadlocks.

Once a deadlock happens in Postgres, it will clear it by aborting one of the transactions involved in deadlock. Exactly which transaction will be aborted is difficult to predict and should not be relied upon.

Takeaways

It is very important to have in mind how locking in Postgres works. Deadlocks in highly concurrent environments are probably unavoidable but it is important to know how to discover, monitor and resolve them. Even doing everything “by the book” will not always resolve all possible issues about locking but will reduce them and make them easier to fix. Table-level locks are viewable through pg_locks system view, but row-level locks are not, so this makes locks debugging much harder, so I hope this will be possible in future Postgres versions.