[PostgreSQL] Recursive CTE for hierarchy data

 

 PostgreSQL   CTE   Hierarchy

 

 

▌Problem

---

 

Assume that we have a table, Departments, which include hierarchy data like this,

 

 

and we would like to know the Agile dev team’s parent hierarchy, for example,

Agile dev team / Tiger department / IT

Here is a sample for using CTE(Common table expression) and RECURSIVE modifier to solve the problem.

 

 

▌Solution

---

 

▋Recursive CTE

WITH RECURSIVE cte_recursive AS (

        SELECT *

        FROM "Departments"

        WHERE "Id" = '001'

        UNION ALL

        SELECT t.*

        FROM "Departments" t

        INNER JOIN cte_recursive r ON t."Id" = r."Parent"

)

SELECT * FROM cte_recursive;

 

 

 

The recursive CTE results in:

 

 

 

 

▋Function (Optional)

 

We can create a function for advanced usage.

CREATE OR REPLACE FUNCTION public.find_hirearchy_departmemts(id text) RETURNS SETOF "Departments" AS 

$function$

BEGIN

    RETURN QUERY

    WITH RECURSIVE cte_recursive AS (

        SELECT *

        FROM "Departments"

        WHERE "Id" = id

        UNION ALL

        SELECT t.*

        FROM "Departments" t

        INNER JOIN cte_recursive r ON t."Id" = r."Parent"    

)

    SELECT * FROM cte_recursive;

END;

$function$ LANGUAGE plpgsql;

 

 

 

▌Reference

---

▋WITH Queries (Common Table Expressions)

▋PostgreSQL CTE

[PostgreSQL] Execute Dynamic SQL

 

 PostgreSQL   Dynamic SQL  

▌Introduction

---

 

 This is a sample of using EXECUTE to execute the dynamic SQL, which its parameters come from psql arguments.

 

 

▌Environment

---

 

▋PostgreSQL 11

 

 

▌Implement

---

 

▋The original SQL

 

Here is the original SQL we want to execute. The SQL will create a foreign data wrapper for dblink(cross database query) from database Demo to database postgres.

 

▋create_dblink_wrapper.sql

/* Create dblink wrapper to postgres */

DO $wrapper$

BEGIN

CREATE FOREIGN DATA WRAPPER pgagent_wrapper VALIDATOR postgresql_fdw_validator;

CREATE SERVER db_postgres FOREIGN DATA WRAPPER pgagent_wrapper OPTIONS (host 'localhost', dbname 'postgres' );

CREATE USER MAPPING FOR postgres SERVER db_postgres OPTIONS (user 'postgres', password 'xxxxx');

END

$wrapper$; 

 

▋ Goals

 

Since we put the user/pwd when creating the USER MAPPING and while executing the SQL on CI, it is insecure and cannot be modified depends on different environments.

 

We would like to decide the values of user/pwd by the environment variables, for example,

$ psql -h localhost -p 5432 -U postgres -d Demo -v postgres_user=$POSTGRESS_USER -v postgres_password=$POSTGRES_PASSWORD -f create_dblink_wrapper.sql

 

 

▋ This will NOT WORK!

 

Since we can get the argument values from psql command by :postgres_user. However, we cannot use the variable(s) inside the DO statement.

 

\echo USER/PWD=:postgres_user/:postgres_password -- This wiil successfully show USER/PWD=my_user/my_pwd

DO $wrapper$

CREATE USER MAPPING FOR postgres SERVER db_postgres OPTIONS (user :'postgres_user', password :'postgres_password'); --Error: syntax error on ":"

END

$wrapper$

 

▋Solution

 

We have to use the features of Dynamic SQL and use current_setting(get current value of setting) to make the SQL can read the value of arguments in DO statement.

 

▋create_dblink_wrapper.sql

 

See the SQL sample file on my Github.

/* Create dblink wrapper to postgres */

\echo POSTGRES_USER=:'postgres_user',POSTGRES_PASSWORD=:'postgres_password'

SET "pg.user" TO :'postgres_user'; --The variable must contains dot. If name it as "pg_user", it will cause error: "ERROR:  unrecognized configuration parameter "pg_user".

SET "pg.pwd" TO :'postgres_password';

SHOW "pg.user";

SHOW "pg.pwd";

DO $wrapper$

DECLARE

  usr text := current_setting('pg.user');

  pwd text := current_setting('pg.pwd');

BEGIN

CREATE FOREIGN DATA WRAPPER pgagent_wrapper VALIDATOR postgresql_fdw_validator;

CREATE SERVER db_postgres FOREIGN DATA WRAPPER pgagent_wrapper OPTIONS (host 'localhost', dbname 'postgres' );

--Execute Dynamic SQL

EXECUTE format('CREATE USER MAPPING FOR postgres SERVER db_postgres OPTIONS (user ''%I'', password ''%I'');', usr, pwd);

END

$wrapper$

 

Now we can execute the SQL as following,

$ psql -h localhost -p 5432 -U postgres -d Demo -v postgres_user=$POSTGRES_USER -v postgres_password=$POSTGRES_PASSWORD -f create_dblink_wrapper.sql

 

Output:

POSTGRES_USER='postgres',POSTGRES_PASSWORD='xxxxx'

SET

SET

 pg.user

----------

 postgres

(1 row)

  pg.pwd

-----------

 xxxxx

(1 row)

DO

 

▌Reference

---

▋ PostgreSQL Doc – psql

▋ PostgreSQL Doc - Dynamic SQL

▋ PSQL Command Line Arguments in DO script

▋ PostgreSQL: How to pass parameters from command line?

 

 

[PostgreSQL] Partition table

 PostgreSQL   Partition table 

 

 

▌Introduction

---

 

This is a simple tutorial for creating partitions on an existing table in PostgreSQL.

 

 

▌Environment

---

 

▋PostgreSQL 11

 

 

▌Steps

---

 

▋Create a demo table

 

Lets create a table first. We will use [CreateOn] as the the value for partitioning.

 

CREATE TABLE "OnlineTxs"

(

    "Id"       serial NOT NULL,

    "CardNo"   varchar(19) NOT NULL,

    "Amt"      decimal(10, 0) NOT NULL,

    "CreateOn" timestamp NOT NULL

) PARTITION BY RANGE ("CreateOn"); -- Add more columns for partitioning

 

▋ Create Partitions

 

We will create the following partition tables with different boundaries.

CREATE TABLE "OnlineTxs_20201126" PARTITION OF "OnlineTxs"

    FOR VALUES FROM ('2020-11-25') TO ('2020-11-26');

CREATE TABLE "OnlineTxs_20201127" PARTITION OF "OnlineTxs"

    FOR VALUES FROM ('2020-11-26') TO ('2020-11-27');

CREATE TABLE "OnlineTxs_20201128" PARTITION OF "OnlineTxs"

    FOR VALUES FROM ('2020-11-27') TO ('2020-11-28');

CREATE TABLE "OnlineTxs_20201129" PARTITION OF "OnlineTxs"

    FOR VALUES FROM ('2020-11-28') TO ('2020-11-29');

 CREATE TABLE "OnlineTxs_20201130" PARTITION OF "OnlineTxs"

    FOR VALUES FROM ('2020-11-29') TO ('2020-11-30');    

The records will be stored in this way by the boundaries,

 

Partition table name	Range
OnlineTxs_20201126	>= 2020-11-25 00:00:00 & < 2020-11-26 00:00:00
OnlineTxs_20201127	>= 2020-11-26 00:00:00 & < 2020-11-27 00:00:00
OnlineTxs_20201128	>= 2020-11-27 00:00:00 & < 2020-11-28 00:00:00
OnlineTxs_20201129	>= 2020-11-28 00:00:00 & < 2020-11-29 00:00:00
OnlineTxs_20201130	>= 2020-11-29 00:00:00 & < 2020-11-30 00:00:00

 

 

Verify the partitions we just made by the following SQL.

SELECT

    nmsp_parent.nspname AS parent_schema,

    parent.relname      AS parent,

    nmsp_child.nspname  AS child_schema,

    child.relname       AS child_name,

    pg_get_expr(child.relpartbound, child.oid, true) as partition_expression

FROM pg_inherits

    JOIN pg_class AS parent            ON pg_inherits.inhparent = parent.oid

    JOIN pg_class AS child             ON pg_inherits.inhrelid   = child.oid

    JOIN pg_namespace AS nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace

    JOIN pg_namespace AS nmsp_child    ON nmsp_child.oid   = child.relnamespace

WHERE parent.relname='OnlineTxs'; 

▋ (Optional) Create the Primary key and Index

ALTER TABLE public."OnlineTxs" ADD PRIMARY KEY ("Id", "CreateOn");

CREATE INDEX IX_OnlineTxs_CreateOn ON public."OnlineTxs"

(

    "CreateOn" ASC

);

 

 

▋Test the partition table with data

 

We can use the following SQL to generate some data into the table.

-- Get a random integer between low and high (low <= the random interger <= high)

CREATE OR REPLACE FUNCTION fn_random_int(low INT, high INT) 

   RETURNS INT AS

$$

BEGIN

   RETURN floor(random()* (high-low + 1) + low);

END;

$$ language 'plpgsql' STRICT;

-- Create mock data

do $loop$

declare r INT;

begin

for r in 1..1000 loop

    INSERT INTO public."OnlineTxs"("CardNo","Amt","CreateOn")

    SELECT

    '123456****789' AS "CardNo",

    fn_random_int(100,9999) AS "Amt",

    '2020-11-29'::timestamp + fn_random_int(0,4) * interval '-1' day as "CreateOn";

    -- NOW() + fn_random_int(0,4) * interval '-1' day as "CreateOn";

end loop;

end;

$loop$;

 

Verify the records by the following SQL,

-- First do VACUUM ANALYZE

VACUUM ANALYZE

-- Check count from "pg_stat_user_tabls"

SELECT schemaname, relname, n_live_tup as Cnt

FROM pg_stat_user_tables

WHERE relname LIKE '%OnlineTxs%'

ORDER BY relname;

-- Or check count from "pg_inherits"

SELECT

    nmsp_parent.nspname AS parent_schema,

    parent.relname      AS parent,

    nmsp_child.nspname  AS child_schema,

    child.relname       AS child_name,

    pg_get_expr(child.relpartbound, child.oid, true) as partition_expression,

    SUM(child.reltuples)    AS cnt

FROM pg_inherits

     JOIN pg_class AS parent            ON pg_inherits.inhparent = parent.oid

     JOIN pg_class AS child             ON pg_inherits.inhrelid   = child.oid

     JOIN pg_namespace AS nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace

     JOIN pg_namespace AS nmsp_child    ON nmsp_child.oid   = child.relnamespace

WHERE parent.relname='OnlineTxs'

GROUP BY parent_schema, parent, child_schema, child_name, partition_expression

 

 

 

 

▋Move on more actions

 

▋Truncate a Partition

Truncating by Partition is much faster than doing deletion. Here is how to truncate a Partition table.

TRUNCATE "OnlineTxs_20201126"; 

 

▋Remove a Partition and its data

DROP TABLE "OnlineTxs_20201126";

▋Detach a Partition as a table

ALTER TABLE public."OnlineTxs" DETACH PARTITION "OnlineTxs_20201127";

 

▋Attach a detachd partition table

ALTER TABLE "OnlineTxs" ATTACH PARTITION "OnlineTxs_20201126"

FOR VALUES FROM ('2020-11-25') TO ('2020-11-26');

 

▌Reference

---

 

▋ Documentation: 10: 5.10. Table Partitioning - PostgreSQL

 

[PostgreSQL] pgAgent - Scheduling agent

 PostgreSQL   pgAgent 

▌Introduction

---

pgAgent is a job scheduling agent for PostgreSQL, it can has multiple steps in a job, including SQL script,  batch or shell scripts.

This article will show how to install pgAgent inside a PostgreSQL Docker container.

▌Environment

---

▋Docker Engine Community 19.03.5

▋PostgreSQL Docker Official image 10/11

▌Implement

---

▋Install

First we have to install pgAgent in the container,

$ docker exec -it demo-postgres bash

$ apt-get update && apt-get install pgagent

Then install the pgAgent extension.

▋Install pgAgent extension

CREATE EXTENSION pgagent IF NOT EXISTS;

CREATE LANGUAGE plpgsql IF NOT EXISTS;

The 【pgAgent Jobs】 shows after installing the extension,

And the new schema named “pgagent” and tables were created.

▋(Optional) Grant permission

It is recommended to create a new role for using pgAgent.

For example,

GRANT USAGE ON SCHEMA pgagent TO pgagent_user;

GRANT ALL ON SCHEMA pgagent TO pgagent_user;

GRANT ALL ON all tables IN SCHEMA pgagent to pgagent_user;

GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent_user;

GRANT ALL ON ALL FUNCTIONS IN SCHEMA pgagent TO pgagent_user;

GRANT CONNECT ON DATABASE postgres TO pgagent_user;

▋Start pgAgent

Back to the container, use the following command to start the agent.

Note that using "127.0.0.1" instead of "localhost" to start pgagent.

$ cd /usr/bin

$ pgagent hostaddr=127.0.0.1 dbname=postgres user=<user_name> -s pgagent_log.log

We can check if the connection between pgAgent and database was established by,

SELECT * FROM pgagent.pga_jobagent;

 

▋Scheduling a job

Here is an example for scheduling a cleaning-table job.

1.Create a new pgAgent Job

2.【General】: Name the new job and set Job class, Host agent(Optional).

3.【Steps】: We can set multiple steps to run SQL scripts or batches here.

Notice that if we wanna run SQL script, we have to enter the database connection string, e.q.

host=localhost port=5432 dbname=postgres connect_timeout=10 user='xxxxx' password='my_pwd'

PS. We can use pgpass (The Password File) to skip storing the password in a pgAgent job. We will talk about it later in this article.

Navigate to Code tab, enter the SQL/Batch script.

4.【Schedules】: Set the job’s start/end time and how it repeats.

5.Save and get the new scheduling job.

For more details on creating a pgAgent job, see official document.

▋Logs

We can check the logs of a job from these tables:

SELECT * FROM pgagent.pga_joblog

SELECT * FROM pgagent.pga_jobsteplog

SELECT * FROM pgagent.pga_exception

▋Use Password File to protect the pwd

To not saving password in a pgAgent job, we will create .pgpass (The Password File) inside the PostgreSQL container.

$ sudo su postgres

$ echo localhost:5432:*:postgres:my_pwd >> /var/lib/postgresql/data/.pgpass

$ chmod 600 /var/lib/postgresql/data/.pgpass

$ chown postgres:postgres /var/lib/postgresql/data/.pgpass

$ export PGPASSFILE='/var/lib/postgresql/data/.pgpass'

 

Then we will be able to remove the password from the connection string of pgAgent job.

We can test if it works by connecting to database without entering password:

$ psql -h localhost -U postgres <db_name>

In Windows, you can use the Password File as following, $ cd %APPDATA% $ mkdir Postgres $ cd Postgres $ echo localhost:5432:*:postgres:my_pwd >> pgpass.conf $ SETX PGPASSFILE %APPDATA%\Postgres\pgpass.conf

▋Create/Drop a pgAgent job by SQL script

We can export the create sql script of a pgAgent job by pgAdmin as following,

Here are a pgAgent job’s create/drop sql script for reference.

/* Create pgAgent job */

DO $$

DECLARE

    jid integer;

    scid integer;

BEGIN

-- Creating a new job

INSERT INTO pgagent.pga_job(

    jobjclid, jobname, jobdesc, jobhostagent, jobenabled

) VALUES (

    1::integer, 'Routine Clean'::text, ''::text, ''::text, true

) RETURNING jobid INTO jid;

-- Steps

-- Inserting a step (jobid: NULL)

INSERT INTO pgagent.pga_jobstep (

    jstjobid, jstname, jstenabled, jstkind,

    jstconnstr, jstdbname, jstonerror,

    jstcode, jstdesc

) VALUES (

    jid, 'Clean_News'::text, true, 's'::character(1),

    'host=localhost port=5432 dbname=postgres connect_timeout=10 user=''postgres'''::text, ''::name, 'f'::character(1),

    'DELETE FROM public."News"'::text, ''::text

) ;

-- Schedules

-- Inserting a schedule

INSERT INTO pgagent.pga_schedule(

    jscjobid, jscname, jscdesc, jscenabled,

    jscstart, jscend,    jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths

) VALUES (

    jid, 'Daily'::text, ''::text, true,

    '2020-04-24 06:14:44+00'::timestamp with time zone, '2020-04-30 05:51:17+00'::timestamp with time zone,

    -- Minutes

    ARRAY[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],

    -- Hours

    ARRAY[false,false,false,false,false,false,false,false,false,false,false,true,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],

    -- Week days

    ARRAY[false,false,false,false,false,false,false]::boolean[],

    -- Month days

    ARRAY[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],

    -- Months

    ARRAY[false,false,false,false,false,false,false,false,false,false,false,false]::boolean[]

) RETURNING jscid INTO scid;

END

$$;

 

/* Delete pgAgent job */

DO $$

DECLARE

    jname VARCHAR(50) :='Routine Clean';

    jid INTEGER;

BEGIN

SELECT "jobid" INTO jid from pgagent."pga_job"

WHERE "jobname"=jname;

DELETE FROM pgagent."pga_schedule"

WHERE "jscjobid"=jid;

DELETE FROM pgagent.pga_jobstep

WHERE "jstjobid"=jid;

DELETE FROM pgagent."pga_job"

WHERE "jobid"=jid;

END

$$;

▌Reference

---

▋pgadmin.org - Creating a pgAgent Job

▋Install pgAgent on Postgres 10 (Debian Linux)

▋pgAgent service for PostgreSQL does not run

▋SETTING UP PGAGENT AND DOING SCHEDULED BACKUPS