Archive for category TOI

How to cancel SQL statements and disconnect sessions in #PostgreSQL

In PostgreSQL, you can cancel problem statements or terminate offending sessions remotely with PG_CANCEL_BACKEND and PG_TERMINATE_BACKEND. This article shows how you can do it with working examples.

edb=# select current_database,current_user;
 current_database | current_user 
------------------+--------------
 edb              | enterprisedb
(1 row)

I’m connected as superuser. The demo is done with EDB Postgres Advanced Server 10.1.5 but the shown technique should work the same with other Postgres distributions and older versions. First I create a demo user:

edb=# create role adam password 'adam' login;
CREATE ROLE
edb=# grant connect on database edb to adam;
GRANT

Now opening another session with that new user:

-bash-4.2$ psql -U adam
Password for user adam: 
psql.bin (10.1.5)
Type "help" for help.

edb=> begin
edb$> loop
edb$> null;
edb$> end loop;
edb$> end;

That session burns CPU now in an endless loop. Back to the superuser session:

edb=# select pid,usename,query
edb-# from pg_catalog.pg_stat_activity where datname='edb';
  pid  |   usename    |                         query                         
-------+--------------+-------------------------------------------------------
 14346 | adam         | begin                                                +
       |              | loop                                                 +
       |              | null;                                                +
       |              | end loop;                                            +
       |              | end;
  5517 | enterprisedb | select pid,usename,query                             +
       |              | from pg_catalog.pg_stat_activity where datname='edb';
(2 rows)

This cancels the SQL statement of one session:

edb=# select pg_cancel_backend(14346);
 pg_cancel_backend 
-------------------
 t
(1 row)

The session that was doing the endless loop gets this output:

ERROR:  canceling statement due to user request
CONTEXT:  edb-spl function inline_code_block line 3 at NULL

This disconnects a single session:

edb=# select pg_terminate_backend(14346);
 pg_terminate_backend 
----------------------
 t
(1 row)

The disconnected session gets this output after trying to do anything:

edb=> \d
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

This way you cancel all SQL statements of a certain user:

edb=# select pg_cancel_backend(pid) from pg_stat_activity where usename='adam';

This way you disconnect all sessions of a certain user:

edb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='adam';

In the unlikely event that your end users are connected with psql, it will try to reconnect them. That’s the background of the above listed output “… Attempting reset: Succeeded.” Means the session got reconnected. If you want to prevent that particular user from (re-)connecting, you need to do this additionally:

edb=# revoke connect on database edb from adam;
REVOKE ROLE

Remember to also revoke from public if you haven’t done that already, otherwise the above revoke doesn’t show any effect:

edb=# revoke connect on database edb from public;
REVOKE

Now this disconnects all sessions of one user and the above prevents new connects by that user:

edb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='adam';
 pg_terminate_backend 
----------------------
 t
(1 row)

The terminated session then gets this output:

edb=> \d
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Keep in mind that the revoke is impacting the whole user, not just one particular session. In other words no session with that user can be established subsequently until you say:

edb=# grant connect on database edb to adam;
GRANT ROLE

You can terminate all sessions connected to one particular database – except your own session – like this:

edb=# select pg_terminate_backend(pid)
edb-# from pg_stat_activity
edb-# where datname='edb' and pid<>pg_backend_pid;

Once again, this does not prevent new sessions from connecting. So either you REVOKE CONNECT on the user layer as shown above, or you do it on the database layer.

This is how normal users are prevented from connecting to the database:

edb=# alter database edb with connection limit 0;
ALTER DATABASE

Superusers can still connect. The above is the equivalent to ALTER SYSTEM ENABLE RESTRICTED SESSION in Oracle. This is what normal users get now upon trying to connect:

-bash-4.2$ psql -U adam
Password for user adam: 
psql.bin: FATAL:  too many connections for database "edb"

The default of unlimited number of sessions allowed to connect can be set back like this:

edb=# alter database edb with connection limit -1;
ALTER DATABASE

This prevents also superusers from connecting to a database, but you must not be connected to that database yourself:

edb=# alter database edb with allow_connections false;
ERROR:  cannot disallow connections for current database

So either you connect to another existing database in the cluster or you create another database temporarily:

edb=# create database dummy;
CREATE DATABASE
edb=# \c dummy
You are now connected to database "dummy" as user "enterprisedb".
dummy=# alter database edb with allow_connections false;
ALTER DATABASE

Now both normal users and superusers get this output when trying to connect:

psql.bin: FATAL:  database "edb" is not currently accepting connections

The default that connections to that database are allowed can be set back with this command:

dummy=# alter database edb with allow_connections true;
ALTER DATABASE
dummy=# \c edb
You are now connected to database "edb" as user "enterprisedb".
edb=# drop database dummy;
DROP DATABASE

What I like especially about the shown functionality is the option to remotely cancel a particular (ongoing) statement without having to terminate the session that runs the statement. I’m not aware of a supported way to do that in Oracle. It can be done if Resource Manager has been configured appropriately beforehand, but that requires quite some effort and doesn’t work just out of the box.

Leave a comment

#Accenture #Enkitec Group at #ukoug_tech17

The circus moves on to Birmingham again! You know that this is one of the must-be-there events of the Oracle community, right?

The Accenture Enkitec Group is there with 6 speakers:

Jason Arneil with two talks: Beginners Guide to Oracle IaaS Cloud and An Introduction to Sharding

Martin Bach: The Answer to the Ultimate Question of SQL, Performance Tuning & Everything

Andy Colvin: Deploying Exadata Cloud Machine – What’s it Really Like?

Frits Hoogland: Who Shut my Database Down? Options for a Security Post Mortem Investigation in the Cloud

and on Super-Sunday he delivers all about linux memory usage by the oracle database

David Kurtz: Making Materialised Views Great Again

and myself: Real-Time Materialized Views in 12c and Fast-Start Failover Enhancements in 12c

See you soon 🙂

 

Leave a comment

Why INSERT causes TX – row lock contention or TM – contention in #Oracle

Inserts may cause enq: TX – row lock contention and enq: TM – contention under special circumstances:

Bitmap Indexes

First session inserts. Second session inserts with the same values for the indexed column. Second session experiences wait event enq: TX – row lock contention until first session commits, then proceeds without error message.

Unique Indexes

First session inserts. Second session inserts with the same values for the indexed column. Second session experiences wait event enq: TX – row lock contention until first session commits, then gets ORA-00001: unique constraint violated.

Parallel Inserts

First session inserts with parallel dml enabled. Second session inserts sequentially or in parallel doesn’t matter. Second session experiences wait event enq: TM – contention. This is also true for partitioned tables unless the partitions are explicitly referred to!

My test cases for the above scenarios have been done with 12.2.0.1 and  look as follows.

Bitmap Index scenario:

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> create table t1 (n number);

Table created.

SQL> create bitmap index b on t1(n);

Index created.

SQL> insert into t1 values(1);

1 row created.

Second session inserts the same and waits. You see the wait event in v$session then.
Unique Index

SQL> create table t2 (n number);

Table created.

SQL> alter table t2 add constraint u unique(n);

Table altered.

SQL> insert into t2 values (1);

1 row created.

Second session inserts the same and waits.
Parallel insert

SQL> create table t3 (n number);

Table created.

SQL> alter table t3 parallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert into t3 select 1 from dual;

1 row created.

Second session waits for any DML.
Partitioned table parallel insert without partition clause

SQL> create table t4 (n number) partition by list (n) (partition p1 values(1),partition p2 values(2));

Table created.

SQL> alter table t4 parallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert into t4 select 1 from dual;

1 row created.

Second session waits for any DML.
Non-blocking parallel insert with explicit partition clause
Same table as above, same parallel degree and parallel DML enabled, but:

SQL> insert into t4 partition (p1) select 1 from dual;

This blocks only partition p1. Second session can do any DML against p2 explicitly.

I have the feeling that this is not widely known – could actually be a good interview question: When do inserts lead to locking waits? As always: Don’t believe it, test it! 🙂

Leave a comment

%d bloggers like this: