Misc in PostgresSQL

Sequence

A serial-type column in a table, e.g.

create table os (
  id                            serial,
  ...

it is actually defined as

postgres=> \d os
                         Table "public.os"
 Column | Type | Modifiers
---------+-------------+-------------------------------------------------
 id      | integer     | not null default nextval('os_id_seq'::regclass)
 ...

That is, a sequence named <table>_<column>_seq is created implicitly.

Sequence is guaranteed to advance¬†every time nextval() is called, even the update is canceled by rollback. So “holes” might be left. It won’t skip existing values either (for example, suppose id is a unique key, and a value is inserted before the sequence reaches that value, a unique violation will occur when the sequence advanced to the value). The best practice is never set serial directly. In some special cases, such as restore/copy data, always call setval() after the data are copied.

Here are sequence functions

Function Return Type Description
currval(regclass) bigint Return value most recently obtained with nextval for specified sequence
lastval() bigint Return value most recently obtained with nextval for any sequence
nextval(regclass) bigint Advance sequence and return new value
setval(regclass, bigint) bigint Set sequence’s current value
setval(regclass, bigint, boolean) bigint Set sequence’s current value and is_called flag

See details at PostgreSQL Document 9.16. Sequence Manipulation Functions

 

Password File

In a trusted environment, accessing database with password can be eliminated with saved password file. On Posix systems, it is $HOME/.pgpass, on Windows, it is %APPDATA%\postgresql\pgpass.conf.

The file contains lines of following format:

hostname:port:database:username:password

except password, the first 4 fields can be *, which matches anything.

The permission of .pgpass should be stricter than 0600, otherwise the file will be ignored. However the permission of pgpass.conf is not checked on Windows.

Reference: PostgreSQL Manual: 31.15. The Password File

Errors and Messages

RAISE statement is used to report message and raise error in SQL/PLSQL

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

level

  • DEBUG
  • LOG
  • NOTICE
  • WARNING
  • EXCEPTION (default, which aborts the current transaction))

‘format’

Must be a string literal (not an expression), followed by optional arguments to be inserted into the message, where % is replaced by the string representation of arguments.

USING option

  • MESSAGE
  • DETAIL
  • HINT
  • ERRCODE
  • COLUMN, CONSTRAINT, DATATYPE, TABLE, SCHEMA

Common Error Codes

  • 02000 – no_data_found
  • 22000 – data_exception
  • 23000 – integrity_constraint_violation
  • 40000 – transaction_rollback
  • 42000 – syntax_error_or_access_rule_violation

Note:

  • although the name of 02000 is “no_data” in all versions of manual, the actual is no_data_found

Please check the full error code list

Detect No Data in PL/SQL

Example 1 (not reliable)

declare
    pid int;
begin
    select id into pid
    from table_foo
    where name='foo';
    -- not reliable, e.g. the value of id is null
    if pid is null then
        raise no_data_found
        using detail=format('name %s is not found', 'foo');
    end if;
end;

Example 2

begin
    update build
    set state='start'
    where name='foo';
    if not found then
        raise no_data_found
        using detail=format('name %s is not found', 'foo');
    end if;
end;

Trapping Errors in PL/SQL

[ <> ]
[ DECLARE
      DECLARATIONS ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
   [WHEN condition [ OR condition ...] THEN
        handler_statements
    ...]
END;

Here is an example that traps error for SELECT … INTO STRICT var. INTO STRICT means there is EXACTLY one row retrieved

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'employee % not found', myname;
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'employee % not unique', myname;
END;

Here are some examples of condition

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

Setup PostgreSQL on Linux

Install from source

The current latest release version is 9.5.4.

# get and install postgres from source
$ cd $YOUR_WORKING_PATH
$ curl https://ftp.postgresql.org/pub/source/v9.5.4/postgresql-9.5.4.tar.gz > postgresql-9.5.4.tar.gz
$ tar xzf postgresql-9.5.4.tar.gz
$ cd postgresql-9.5.4
$ ./configure # default --prefix=/usr/local/pgsql
$ make

$ sudo make install
$ sudo adduser postgres
$ sudo passwd postgres
$ sudo mkdir /usr/local/pgsql/data
$ sudo chown postgres /usr/local/pgsql/data

$ su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data # initialize database cluster

Configure Access from network

By default, postgres allows connection only from localhost. To allow clients connect over network, host entries need to be added in /usr/local/pgsql/data/pg_hba.conf, such as

host all all 192.168.0.0/16 md5

allows all users from 192.168.x.x to access all database, using md5 to hash their password. See more details in the manual 18.3 Connection and Authentication

Auto start when system boots

Although 17.3. Starting the Database Server in the manual gives the solution for old-style /etc/rc.d, modern linux distributions use systemd, here is the solution

add postgresql.service

[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

# ... but allow it still to be effective for child processes
# (note that these settings are ignored by Postgres releases before 9.5)
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

# Maximum number of seconds pg_ctl will wait for postgres to start.  Note that
# PGSTARTTIMEOUT should be less than TimeoutSec value.
Environment=PGSTARTTIMEOUT=270
Environment=PGDATA=/usr/local/pgsql/data

ExecStart=/usr/local/pgsql/bin/pg_ctl start -o "-h '*'" -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT}
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down.
# Ideally, the timeout for starting PostgreSQL server should be handled more
# nicely by pg_ctl in ExecStart, so keep its timeout smaller than this value.
TimeoutSec=300

[Install]
WantedBy=multi-user.target

as /usr/lib/systemd/system/postgresql.service, then run following commands

sudo systemctl daemon-reload
sudo systemctl enable postgresql
sudo systemctl start postgresql

After that you can connect the postgresql cluster as

/usr/local/pgsql/bin/psql -U postgres -h hostname
postgres=# create role myaccount login password='my passowrd';
postgres=# create database mydb;
postgres=# alter database mydb owner to myaccount;

 

Reference