Monday, April 10, 2017

Orafce and plpgsql_check are ready for PostgreSQL 10

Small note: All regress tests of https://github.com/orafce/orafce and https://github.com/okbob/plpgsql_check passed on PostgreSQL 10.

Saturday, April 8, 2017

How to find unindexed foreign keys

Often performance issue is missing index on foreign keys. With few queries is very simple to find foreign keys without index:

  1. Create a demo:
    create table a(id serial primary key, v text);
    create table b(id serial primary key, id_a integer references a(id), v text);
    
  2. Create auxiliary functions:
    -- returns a subset of column names specified by their nums in array
    create or replace function attnames(oid, smallint[])
    returns name[] as $$
    select array_agg(attname )
      from (select attname
              from pg_attribute 
             where attrelid = $1 and attnum = any($2)
             order by attnum) s
    $$ language sql;
    
    -- ensure safe quoting of column names for usage in SQL
    create or replace function qnames(name[])
    returns text as $$
    select string_agg(v, ',')
      from (select quote_ident(n) v
              from unnest($1) g(n)) s
    $$ language sql;
    
  3. Following query returns foreign key constraint name, table name and related columns:
    select conname, conrelid::regclass, attnames(conrelid, conkey) 
      from pg_constraint
     where contype = 'f';
    
    ┌─────────────┬──────────┬──────────┐
    │   conname   │ conrelid │ attnames │
    ╞═════════════╪══════════╪══════════╡
    │ b_id_a_fkey │ b        │ {id_a}   │
    └─────────────┴──────────┴──────────┘
    (1 row)
    
  4. We can check if any attnames from this result are covered by some index:
    select conname, conrelid::regclass, attnames(conrelid, conkey)
      from pg_constraint
     where contype = 'f'
       and not exists (select *
                         from pg_index
                        where indrelid = conrelid
                          and attnames(conrelid,conkey) = attnames(conrelid, indkey::smallint[]));
    
    -- list of foreign keys without index
    ┌─────────────┬──────────┬──────────┐
    │   conname   │ conrelid │ attnames │
    ╞═════════════╪══════════╪══════════╡
    │ b_id_a_fkey │ b        │ {id_a}   │
    └─────────────┴──────────┴──────────┘
    (1 row)
    
  5. Next step can be generating CREATE INDEX commands:
    select format('create index on %s(%s)',
                   conrelid::regclass,
                   qnames(attnames(conrelid, conkey)))
      from pg_constraint
     where contype = 'f'
       and not exists (select *
                         from pg_index
                        where indrelid = conrelid
                          and attnames(conrelid,conkey) = attnames(conrelid, indkey::smallint[]));
    
    ┌─────────────────────────┐
    │         format          │
    ╞═════════════════════════╡
    │ create index on b(id_a) │
    └─────────────────────────┘
    (1 row)
    
  6. Inside modern psql we can execute last query with \gexec command, that ensures executing result too.
Small note about placeholders symbols in format function. We should to use %I for SQL identifiers to be result correctly escaped.
select format('(1):%I, (2):%s', 'BadTableName', 'BadTableName');
┌──────────────────────────────────────┐
│                format                │
╞══════════════════════════════════════╡
│ (1):"BadTableName", (2):BadTableName │
└──────────────────────────────────────┘
(1 row)
This case is exception, because I use casting from Oid to regclass, that enforce correct escaping. Two times escaping can produce wrong result. So %I cannot be used here.
create table "BadName"(a int);

select '"BadName"'::regclass::oid;
┌───────┐
│  oid  │
╞═══════╡
│ 16419 │
└───────┘
(1 row)

select 16419::regclass;
┌───────────┐
│ regclass  │
╞═══════════╡
│ "BadName" │
└───────────┘
(1 row)

postgres=# select format('%I, %s', 16419::regclass, 16419::regclass);
┌──────────────────────────┐
│          format          │
╞══════════════════════════╡
│ """BadName""", "BadName" │
└──────────────────────────┘
(1 row)

Sunday, February 19, 2017

new command line tool pgimportdoc

More, more times I had to import some XML documents to Postgres. How do it simply? More, some XML documents are not in UTF8 encoding, so some conversion and modification is necessary.

<?xml version="1.0" encoding="windows-1250"?>
<enprimeur>
     <vino>
         <id>1</id>
         <nazev>Alter Ego de Palmer</nazev>
         <vyrobce>63</vyrobce>
         <rocnik>2012</rocnik>
         <cena0375>0</cena0375>
         <cena1500></cena1500>
         <cena3000>0</cena3000>

It is not a hard work, but it is manual work, and it is terrible work, because PostgreSQL has enough functionality, but this functionality is not accessible from psql console.

I wrote simple tool pgimportdoc, that can helps with import any text, json, binary (to bytea field) or XML document to Postgres.

Usage:
cat ~/Stažené/enprimeur.xml | ./pgimportdoc postgres -c 'insert into xmldata values($1)' -t XML
./pgimportdoc postgres -f ~/Stažené/enprimeur.xml -c 'insert into xmldata values($1)' -t XML

Supported formats are XML, BYTEA and TEXT. The TEXT format can be used for text, json, jsonb target formats.

Link: https://github.com/okbob/pgimportdoc

Friday, December 23, 2016

OpenERP configuration

I had a customer with strange issues of OpenERP - the main problem was long life of OpenERP/PostgreSQL sessions. The fix was not hard - using pgBouncer in transaction mode with short server life time (ten minutes).

Friday, August 26, 2016

Orafce package for PostgreSQL 9.3, 9.4, 9.5, 9.6 for WIN32, WIN64 is available

Please, download from link.

Thursday, August 25, 2016

plpgsql_check for PostgreSQL 9.4, 9.5, 9.6 compiled for WIN32, WIN64 is available

please download from link

Sunday, August 21, 2016

XMLTABLE implementation is done

I finished the patch for XMLTABLE in PostgreSQL. It is working well. Examples what I found on net works:

postgres=#   SELECT t.id, x.*
     FROM employees t,
          XMLTABLE ('/Employees/Employee[age>40]'
                    PASSING t.data
                    COLUMNS firstname VARCHAR(30) PATH 'firstname', 
                            lastname VARCHAR(30) PATH 'lastname',
                            age VARCHAR(30) PATH 'age') x
    WHERE t.id = 1;
┌────┬───────────┬──────────┬─────┐
│ id │ firstname │ lastname │ age │
╞════╪═══════════╪══════════╪═════╡
│  1 │ Jim       │ Moriarty │ 52  │
│  1 │ Mycroft   │ Holmes   │ 41  │
└────┴───────────┴──────────┴─────┘
(2 rows)

Time: 1.619 ms
postgres=# SELECT t.id, x.*
     FROM employees t,
          XMLTABLE ('/Employees/Employee[@emplid=2222]'
                    PASSING t.data
                    COLUMNS firstname VARCHAR(30) PATH 'firstname', 
                            lastname VARCHAR(30) PATH 'lastname') x
    WHERE t.id = 1;
┌────┬───────────┬──────────┐
│ id │ firstname │ lastname │
╞════╪═══════════╪══════════╡
│  1 │ Sherlock  │ Homes    │
└────┴───────────┴──────────┘
(1 row)

Time: 1.606 ms
postgres=#   SELECT emp.id, x.*
     FROM employees emp,
          XMLTABLE ('/Employees/Employee'
                    PASSING emp.data
                    COLUMNS firstname VARCHAR(30) PATH 'firstname',
                            type VARCHAR(30) PATH '@type') x;
┌────┬───────────┬───────┐
│ id │ firstname │ type  │
╞════╪═══════════╪═══════╡
│  1 │ John      │ admin │
│  1 │ Sherlock  │ admin │
│  1 │ Jim       │ user  │
│  1 │ Mycroft   │ user  │
└────┴───────────┴───────┘
(4 rows)

Time: 1.556 ms

Please, test it, check it.