.. -*- coding: utf-8; -*-

==========
 Postgres
==========
.. contents::
   :local:

Installing on Debian
====================

Install and create new user and database::

  $ sudo apt-get install postgresql postgresql-client
  $ sudo su - postgres
  % psql
  postgres=# CREATE USER myuser WITH PASSWORD 'mypass';
  postgres=# CREATE DATABASE mydb OWNER myuser;
  postgres=# \q

Connect as user ``mypguser`` to new database::

  $ su - mypguser
  $ psql mypgdatabase

In order to create local host superuser::

  $ sudo su - postgres
  $ createuser --superuser USER
  $ exit
  $ sudo -u USER psql

..

https://wiki.debian.org/PostgreSql
  Debian wiki instructions.

Check version
=============

By SQL query::

  SELECT version();
  SHOW server_version;
  SHOW server_version_num;

From executable::

  $ postgres -V

Check locations
===============
::

  SHOW hba_file;
  SHOW config_file;

Check settings
==============
::

   SELECT name, setting FROM pg_settings;
   SELECT name, current_setting(name), source FROM pg_settings;

Check extensions
================
::

   SELECT * FROM pg_available_extensions;

Set application name
====================

``application_name`` is displayed in ``pg_stat_activity.application_name``.

It can help identify clients of DB. To alter it in current session::

  SET application_name = 'some name';

To show current value::

  SHOW application_name;

Limitations
===========

https://wiki.postgresql.org/wiki/TOAST
  TOAST is a mechanism PostgreSQL uses to keep physical data rows from exceeding the size of a data
  block (typically 8KB).

List privileges
===============

List databases with privileges::

  \l

List schemas with privileges::

  \dn+

List users with privileges::

  \du

List privileges to objects (tables, views, sequences)::

  \dp

  SELECT * FROM information_schema.role_table_grants
  WHERE table_name = 'table_name';

List default privileges::

  \ddp

  SELECT * FROM pg_default_acl;

Grant role options
==================

Show role options and group membership::

  \du

Grant role options::

  ALTER USER me WITH SUPERUSER;
  ALTER USER me WITH CREATEDB CREATEROLE LOGIN;

Revoke role options::

  ALTER USER me WITH NOSUPERUSER;

https://www.postgresql.org/docs/current/sql-createrole.html
  ``CREATE ROLE``.

Granting selects::

  GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user;
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rw_user;
  GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO rw_user;

Making read-only role::

  GRANT CONNECT ON DATABASE mydb TO ro;
  GRANT USAGE ON SCHEMA myschema TO ro;
  GRANT SELECT ON TABLE myschema.myt1, myschema.myt2 TO ro;

Granting default privileges for new objects::

  ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_user;
  ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON SEQUENCES TO rw_user;
  ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT EXECUTE ON FUNCTIONS TO rw_user;

Revoke privileges and drop role
===============================

List roles::

  SELECT * FROM pg_roles;

  SELECT oid, rolname FROM pg_roles
  WHERE pg_has_role('xuser', oid, 'member');

Revoke privileges::

  REVOKE ALL PRIVILEGES ON DATABASE mydb FROM xuser;
  REVOKE ALL PRIVILEGES ON SCHEMA myschem FROM xuser;
  REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschem FROM xuser;
  REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschem FROM xuser;

  do $$
  DECLARE r record;
  begin
      for r in select * from pg_views where schemaname = 'myschem'
      loop
        execute 'revoke all on ' || quote_ident(r.schemaname) ||'.'|| quote_ident(r.viewname) || ' from "xuser"';
      end loop;
  end $$;

To forbid any operations (read/write) without dealing with individual privileges (any pending user
transactions will fail!)::

  REVOKE USAGE ON SCHEMA myschem FROM xuser;

To forbid connection remove privilege from ``public`` role as it inherited by all users and then from user::

  REVOKE CONNECT ON DATABASE mydb FROM public;
  REVOKE CONNECT ON DATABASE mydb FROM xuser;

Role can't be dropped until it owns objects, need to reassign owner::

  REASSIGN OWNED BY xuser TO myuser;

Some objects may left behind role (like default privileges), they can be dropped via::

  DROP OWNED BY xuser;

Remove role::

  DROP ROLE xuser;

Change password
===============
::

   ALTER USER me WITH PASSWORD 'PassWord';

Monitor connections
===================

Current user::

  SELECT current_user;
  SELECT session_user;

Active sessions and queries::

  select * from pg_stat_activity;

  select * from pg_stat_activity
  order by usename;


https://www.postgresql.org/docs/current/monitoring-stats.html
  The Statistics Collector.

Profile queries
===============

Stats on query execution time::

  SELECT * FROM pg_stat_statements;

  SELECT * FROM pg_stat_statements
  ORDER BY calls DESC;

  SELECT * FROM pg_stat_statements
  WHERE query LIKE '%some%'
  ORDER BY calls DESC;

https://www.postgresql.org/docs/current/pgstatstatements.html
  ``pg_stat_statements``.
https://www.postgresql.org/docs/current/indexes-examine.html
  Examining Index Usage.

Reveal execution details::

  EXPLAIN ANALYSE SELECT 1;
  EXPLAIN (TIMING false, COSTS false, ANALYSE) SELECT 1;
  EXPLAIN (TIMING false, COSTS false, BUFFERS false, VERBOSE false, ANALYSE) SELECT 1;

``EXPLAIN ANALYSE`` actually executes a query, if it is destructive enclose the query into a
transaction::

  BEGIN;
  EXPLAIN ANALYZE ...;
  ROLLBACK;

https://www.postgresql.org/docs/11/sql-explain.html
  Syntax of ``EXPLAIN``.
https://www.postgresql.org/docs/11/using-explain.html
  Interpreting results of ``EXPLAIN``.

List databases, schemas and tables
==================================

Default database is ``postgres``.

To list databases and database locales::

  $ psql -U pgadmin -l

or::

  => SELECT datname FROM pg_database WHERE datistemplate = false;
  => \l

To switch databases::

  => \connect NAME

Schemas::

  \dn *
  select schema_name from information_schema.schemata;
  select nspname from pg_catalog.pg_namespace;

To list all tables in the current database::

  \dt
  SELECT table_schema, table_name FROM information_schema.tables ORDER BY table_schema, table_name;
  SELECT * FROM pg_class;

or in all schemas::

  => \dt *.*

List views::

  \dv

List users::

  \du
  \du+

List schemas::

  \dn

List indexes::

  \di
  select * from pg_indexes;
  select * from pg_indexes where schemaname = '...';
  select * from pg_indexes where schemaname = '...' and tablename = '...';
  select * from pg_indexes where schemaname = '...' and indexname = '...';

  select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
  from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
  where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
  order by
    t.relname,
    i.relname;

  select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
  from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
  where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'test%'
  group by
    t.relname,
    i.relname
  order by
    t.relname,
    i.relname;

List index usage::

  SELECT * FROM pg_stat_all_indexes;
  SELECT * FROM pg_stat_all_tables;
  SELECT * FROM pg_stat_user_indexes;
  SELECT * FROM pg_stat_user_tables;

List constraints::

  SELECT * FROM pg_constraint;
  SELECT * FROM pg_constraint WHERE conrelid = (SELECT oid FROM pg_class WHERE relname LIKE 'tblName');
  select * from pg_constraint where conname like '%conName%';

Show constraints and columns to given table::

  select
    c.constraint_name,
    x.table_schema as schema_name, x.table_name, x.column_name,
    y.table_schema as foreign_schema_name, y.table_name as foreign_table_name, y.column_name as foreign_column_name
  from information_schema.referential_constraints c
  join information_schema.key_column_usage x
      on x.constraint_name = c.constraint_name
  join information_schema.key_column_usage y
      on y.ordinal_position = x.position_in_unique_constraint
      and y.constraint_name = c.unique_constraint_name
  where y.table_name = 'sa_pnr'
  order by c.constraint_name, x.ordinal_position

https://www.postgresql.org/docs/current/catalog-pg-constraint.html
  pg_constraint.
https://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys
  List table foreign keys.

List functions::

  \df

List triggers::

  \dy

List misc info::

  select current_database();
  select current_schema();
  select current_user;
  select session_user;
  select current_date;
  select version();

https://www.postgresql.org/docs/current/functions-info.html
  System Information Functions.

Set default schema.
===================
::

  set search_path to NAME;
  set schema 'NAME';

Database, table, index size
===========================

Database size::

  SELECT pg_database_size('geekdb');  -- in bytes
  SELECT pg_size_pretty(pg_database_size('dbname'));

  SELECT datname, pg_database_size(datname) FROM pg_database;

List of databases sizes::

  \l+

  SELECT pg_database_size('mydb');

List tables sizes (and constraints)::

  \d+

Table total size (with indexes)::

  SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));

Table size with TOAST (without indexes)::

  SELECT pg_table_size('schemaname.tablename');

Sole table size (without indexes and other)::

  SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));

  SELECT pg_relation_size(t.schemaname ||'.'|| t.tablename), t.* FROM pg_tables t;

Largest table in the PostgreSQL database::

  SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
  SELECT relname, pg_size_pretty(pg_relation_size(p.oid)) FROM pg_class ORDER BY relpages DESC;

Largest tables with TOAST::

  SELECT p.relname, t.relname, pg_size_pretty(pg_relation_size(p.oid)), p.relpages
  FROM pg_class p
  LEFT JOIN pg_class t ON t.reltoastrelid = p.oid
  ORDER BY p.relpages DESC;

Largest TOAST::

  select tbl.relname, toast.relname, toast.relpages
  from pg_class tbl, pg_class toast
  where tbl.reltoastrelid = toast.oid
  order by toast.relpages desc;

Index size::

  SELECT pg_indexes_size('my_ix');

Largest index::

  select pg_relation_size(oid), * from pg_class
  where relkind = 'i'
  order by pg_relation_size(oid) desc;

  select pg_relation_size(i.oid), t.relname as tname, i.relname as iname, *
  from pg_class i
  join pg_index ix on ix.indexrelid = i.oid
  join pg_class t on t.oid = ix.indrelid
  where i.relkind = 'i'
  order by pg_relation_size(i.oid) desc;

Size of types::

  SELECT pg_column_size(5::smallint);
  SELECT pg_column_size(5::int);
  SELECT pg_column_size(5::bigint);

Size of column::

  SELECT sum(pg_column_size(col)) FROM tbl;

Total of all ``pg_column_size`` is less then ``pg_relation_size`` because table is using pages to
store rows and we might have gaps inside pages.

https://www.postgresql.org/docs/9.6/functions-admin.html
  Database Object Management Functions.
https://wiki.postgresql.org/wiki/Disk_Usage
  Finding the size of various object in your database.
https://wiki.postgresql.org/wiki/Index_Maintenance
  Index size/usage statistics.

MVCC and transaction id
=======================

As being MVCC Postgres doesn't delete or update existing rows. Row visibility is controled by
transaction id (monotonous incremented number): ``xmin`` - first transaction that can see row,
``xmax`` - last transaction that can see row::

  SELECT xmin, xmax, * FROM foo WHERE id < 5 ORDER BY id;

Vacuum
======

Stats on vacuum oprtations per table::

  select * from pg_stat_user_tables;

Table statistics
================

Full table statistics is in ``pg_statistics`` table which is accessible only by superuser.

Human readable stats are in ``pg_stats`` view.

Using psql client
=================

Using password file ``~/.pgpass``::

  # comment
  hostname:port:database:username:password
  hostname:port:*:username:password
  hostname:*:*:username:password

Connect by::

  $ psql -U $USER -h $HOST -p $PORT  $SCHEMA

Switch to long lines format::

  \x

How to view execution plan::

  EXPLAIN query;
  EXPLAIN ANALYZE query;

Collect statistics::

  ANALYZE table;

How to redirect the output of query to a file::

  \o output_file
  SELECT * FROM pg_class;

Switch database::

  \connect DBNAME
  \c DBNAME

Load external SQL file::

  \i my.sql

Execute external file::

  psql -f my.sql

Export data
===========

Export from ``psql``::

  \copy (SELECT * FROM foo) TO '/tmp/my.csv' WITH CSV

Import data
===========

Import CSV file::

  \copy tbl_name from 'my.csv' csv;
  \copy tbl_name from 'my.csv' delimiter ':' csv;

  $ psql -U $USER -h $HOST $DB -c "\\copy tbl_name from 'my.csv' csv"

JDBC driver
===========

https://jdbc.postgresql.org/about/about.html
  The current version of the driver should be compatible with PostgreSQL 8.2 and higher, and Java 6
  (JDBC 4.0), Java 7 (JDBC 4.1), Java 8 (JDBC 4.2) and Java 9.
https://jdbc.postgresql.org/download.html
  Java/JDBC/Postgres compatibility table.

plpgsql
=======

Simple function::

  CREATE OR REPLACE FUNCTION incx(x INTEGER)
  RETURNS INTEGER STABLE AS $$
    BEGIN
      RETURN x + 1;
    END;
  $$ LANGUAGE plpgsql;

Support for procedures is added only in Postgres v11.

https://www.postgresql.org/docs/current/sql-createfunction.html
  ``CREATE FUNCTION``.
https://www.postgresql.org/docs/current/plpgsql-control-structures.html
  plpgsql control structures.
https://www.postgresql.org/docs/current/errcodes-appendix.html
  PostgreSQL Error Codes.

Anonymous block::

  do $$
  begin
      null;
  end $$;

You cannot perform ``select`` without assigning resultset, special keyword ``perform`` is used
insted of ``select`` in PLPQGSL::

  do $$
  begin
      perform 1 union all select 2;
  end$$;

Temporary constant table
========================

Standard SQL syntax::

  SELECT 1 AS num, 'a' AS str
  UNION ALL SELECT 2, 'b'
  UNION ALL SELECT 3, 'c';

Postgre extension::

  SELECT num, str FROM (
    VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS tbl(num, str);

  WITH tbl(num, str) AS (VALUES (1, 'a'), (2, 'b'), (3, 'c'))
  SELECT num, str FROM tbl;

To generate table::

  SELECT val FROM generate_series(1, 10) val;

  WITH t AS (SELECT val FROM generate_series(1, 10) val)
  SELECT * FROM t;

  WITH
    seq1(num) AS (VALUES (1), (2), (3)),
    seq2(str) AS (VALUES ('a'), ('b'), ('c'))
  SELECT * FROM seq1
  CROSS JOIN seq2;