[ 常見問題 ] Finding and killing long running queries on PostgreSQL

Source From Here 
Introduction 
From time to time we need to investigate if there is any query running indefinitely on our PostgreSQL database. These long running queries may interfere on the overall database performance and probably they are stuck on some background process. 

How-To 
In order to find them you can use the following query: 

view plaincopy to clipboardprint?

1. SELECT  
2.   pid,  
3.   now() - pg_stat_activity.query_start AS duration,  
4.   query,  
5.   state  
6. FROM pg_stat_activity  
7. WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';  

The first returned column is the process id, the second is duration, following the query and state of this activity. If state is idle you don’t need to worry about it, but active queries may be the reason behind low performances on your database. 
Notes. 

I’ve added the pg_cancel_backend as first option to stop the query because it’s safer than pg_terminate_backend.

In order to cancel these long running queries you should execute: 

view plaincopy to clipboardprint?

1. SELECT pg_cancel_backend(__pid__);  

The pid parameter is the value returned in the pg_stat_activity select. It may take a few seconds to stop the query entirely using the pg_cancel_backend command. If the you find the process is stuck you can kill it by running: 

view plaincopy to clipboardprint?

1. SELECT pg_terminate_backend(__pid__);  

Be careful with that! As pointed by Erwin Andreasen in the comments bellow, pg_terminate_backend is the kill -9 in PostgreSQL. It will terminate the entire process which can lead to a full database restart in order to recover consistency. 

Supplement 
* PostgreSQL Doc - System Administration Functions

[ 常見問題 ] How do you create a read-only user in PostgreSQL?

Source From Here 
Question 
I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be: 

view plaincopy to clipboardprint?

1. GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';  

What is the equivalent command or series of commands in PostgreSQL? 

How-To 

Grant usage/select to a single table 
If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so: 

view plaincopy to clipboardprint?

1. GRANT CONNECT ON DATABASE mydb TO xxx;  
2. -- This assumes you're actually connected to mydb..  
3. GRANT USAGE ON SCHEMA public TO xxx;  
4. GRANT SELECT ON mytable TO xxx;  

Multiple tables/views (PostgreSQL 9.0+) 
In the latest versions of PostgreSQL, you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one: 

view plaincopy to clipboardprint?

1. GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;  

This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future: 

view plaincopy to clipboardprint?

1. ALTER DEFAULT PRIVILEGES IN SCHEMA public  
2.    GRANT SELECT ON TABLES TO xxx;  

Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow. 

Multiple tables/views (PostgreSQL versions before 9.0) 
To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required GRANT SELECT to each table/view: 

view plaincopy to clipboardprint?

1. SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'  
2. FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace  
3. WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');  

This should output the relevant GRANT commands to GRANT SELECT on all tables, views, and sequences in public, for copy-n-paste love. Naturally, this will only be applied to tables that have already been created. 


Supplement 
* oinopion/read-access.sql 

view plaincopy to clipboardprint?

1. -- Create a group  
2. CREATE ROLE readaccess;  
3.   
4. -- Grant access to existing tables  
5. GRANT USAGE ON SCHEMA public TO readaccess;  
6. GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;  
7.   
8. -- Grant access to future tables  
9. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;  
10.   
11. -- Create a final user with password  
12. CREATE USER tomek WITH PASSWORD 'secret';  
13. GRANT readaccess TO tomek;  

* PostgreSQL Doc - Chapter 18. Database Roles and Privileges 

To determine the set of existing roles, examine the pg_roles system catalog, for example 

view plaincopy to clipboardprint?

1. SELECT rolname FROM pg_roles;  

* PostgreSQL - ALTER USER 

Change a user's password: 

view plaincopy to clipboardprint?

1. ALTER USER davide WITH PASSWORD 'hu8jmn3';  

[ Python 文章收集 ] SQLAlchemy Core - SQL Expressions (2)

Source From Here 
SQL Expressions 
SQL expressions are constructed using corresponding methods relative to target table object. For example, the INSERT statement is created by executing insert() method as follows: 

>>> ins = students.insert()
>>> ins

>>> str(ins)
'INSERT INTO students (id, name, lastname) VALUES (%(id)s, %(name)s, %(lastname)s)'

It is possible to insert value in a specific field by values() method to insert object. The code for the same is given below: 

>>> ins = students.insert().values(name='lee', lastname='john')
>>> str(ins)
'INSERT INTO students (id, name, lastname) VALUES (%(id)s, %(name)s, %(lastname)s)'

The SQL echoed on Python console doesn’t show the actual value (‘john’ in this case). Instead, SQLALchemy generates a bind parameter which is visible in compiled form of the statement: 

>>> ins.compile().params
{'id': None, 'name': 'lee', 'lastname': 'john'}

Executing Expression 
In order to execute the resulting SQL expressions, we have to obtain a connection object representing an actively checked out DBAPI connection resource and then feed the expression object as shown in the code below: 

view plaincopy to clipboardprint?

1. conn = engine.connect()  

The following insert() object can be used for execute() method: 

>>> ins = students.insert().values(name = 'Lee', lastname = 'John')
>>> result = conn.execute(ins)
2019-03-05 22:11:50,246 INFO sqlalchemy.engine.base.Engine INSERT INTO students (name, lastname) VALUES (%(name)s, %(lastname)s) RETURNING students.id
2019-03-05 22:11:50,246 INFO sqlalchemy.engine.base.Engine {'name': 'Lee', 'lastname': 'John'}
2019-03-05 22:11:50,251 INFO sqlalchemy.engine.base.Engine COMMIT

Following is the entire snippet that shows the execution of INSERT query using SQLAlchemy’s core technique: 

view plaincopy to clipboardprint?

1. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
2.   
3. db_string = "postgresql://postgres:john7810@localhost/testdb"  
4. engine = create_engine(db_string, echo = True)  
5. meta = MetaData(bind=engine)  
6.   
7. students = Table('students', meta, Column('id', Integer, primary_key = True),  
8.                  Column('name', String), Column('lastname', String))  
9.   
10. ins = students.insert().values(name = 'Lee', lastname = 'John')  
11. conn = engine.connect()  
12. result = conn.execute(ins)  

The result can be verified by opening the database using psql: 

view plaincopy to clipboardprint?

1. testdb=# SELECT * FROM students;  
2. id | name | lastname  
3. ----+------+----------  
4.   1 | Lee  | John  
5. (1 row)  

Selecting Rows 
The select() method of table object enables us to construct SELECT expression: 

>>> s = students.select()
>>> str(s)
'SELECT students.id, students.name, students.lastname \nFROM students'

We can use this select object as a parameter to execute() method of connection object as shown in the code below: 

>>> result = conn.execute(s)
2019-03-05 22:20:32,484 INFO sqlalchemy.engine.base.Engine SELECT students.id, students.name, students.lastname
FROM students
2019-03-05 22:20:32,484 INFO sqlalchemy.engine.base.Engine {}

The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method: 

>>> result.__class__

>>> for row in result.fetchone():
... print(row)
...
1
Lee
John

The complete code to print all rows from students table is shown below: 
- demo3.py 

view plaincopy to clipboardprint?

1. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
2.   
3. db_string = "postgresql://postgres:john7810@localhost/testdb"  
4. engine = create_engine(db_string, echo = True)  
5. meta = MetaData(bind=engine)  
6.   
7. students = Table('students', meta, Column('id', Integer, primary_key = True),  
8.                  Column('name', String), Column('lastname', String))  
9.   
10. conn = engine.connect()  
11. s = students.select()  
12. result = conn.execute(s)  
13. for row in result:  
14.     print("id={}, name={}; lastname={}".format(row[0], row[1], row[2]))  

We have inserted a few fake testing data. So our output will look like: 

id=1, name=Lee; lastname=John
id=2, name=Lin; lastname=Mary
id=3, name=Ravi; lastname=Kapoor
id=4, name=Rajiv; lastname=Khanna
id=5, name=Komal; lastname=Bhandari
id=6, name=Abdul; lastname=Sattar
id=7, name=Priya; lastname=Rajhans

The WHERE clause of SELECT query can be applied by using Select.where(). For example, if we want to display rows with id >2: 

view plaincopy to clipboardprint?

1. s = students.select().where(students.c.id>2)  
2. result = conn.execute(s)  
3.   
4. for row in result:  
5.    print (row)  

Here c attribute is an alias for column. Following output will be displayed on the shell: 

(3, 'Ravi', 'Kapoor')
(4, 'Rajiv', 'Khanna')
(5, 'Komal', 'Bhandari')
(6, 'Abdul', 'Sattar')
(7, 'Priya', 'Rajhans')

Here, we have to note that select object can also be obtained by select() function in sqlalchemy.sql module. The select() function requires the table object as argument: 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql import select  
2. students = meta.tables['students']  
3. s = select([students])  
4. result = conn.execute(s)  

Supplement 
* SQLAlchemy Document - Insert, Updates, Deletes