Skip to content

Postgres

Parallel jobs in Postgres

Parallel jobs

Solution to implement parallel jobs in Postgres

Problem

Common approach for running time consuming or maintenance tasks in databases is using job processes. Two classic extensions that allow to launch scheduled jobs in Postgres are pgAgent and pg_cron. Problem is that if you have database hosted in a cloud they might be unavailable. This happens for Postgres RDS in AWS cloud. One of the unofficial recommendation from AWS team on support forum was to launch EC2 instance and trigger via crontab on that server psql which will execute some script or call an API on database side.

Such approach requires from you to keep (and pay for) separate EC2 instance. It has to be continiously up and running. Another advise was even more complex setup with Lambda functions.

Persistent temp tables in Postgres

Persistent temp tables

Solution to implement persistent temp tables in Postgres

Problem

One of the problems that has to be resolved during Oracle 2 Postgres code migration is handling of temporary tables. Biggest difference is that in Oracle temporary tables are persistent schema level objects. You define the table structure once and it exists until you will explicitly drop it. The only difference comparing to ordinary table is that data in temporary table are stored either during current transaction only or until end of current session.

In contrast in Postgres temp tables are pure temporary objects and got automatically dropped after current session end. They also support data lifetime duration on the transaction or session level. But no temp table is available upon start of a new session.

In PL/SQL code you might have any SELECT or DML statement issued against temp table. They will work because it will be present as a schema object.

In migrated plpgsql code such temp tables references will fail with relation "table_name" does not exist error. We have to somehow create the table before executing any statement against it.

Quick Start with Postgres

PostgreSQL quick start

Compiled guide for quick start with Postgres

Installation

The best way to start working with Postgres is to launch Docker container from official Postgres image. Such approach allows you to launch up databases for any Postgres version with the possibility to run them in parallel. Great benefit of such approach is that everything is done with a single command.

Oracle 2 Postgres cheatsheet

Oracle 2 Postgres cheatsheet

Comparative "How To" between Oracle and Postgres

Following text describes Oracle as it was before its pluggable era which started from 12th major version. While majority (if not all) of these examples will work as described there might be new features that will make some cases from not possible to already exists in Oracle. One of such examples is added OFFSET clause in SELECT which was absent in latest 11.2 version.

Postgres version described is mainly latest 11th range version which is 11.7.

In general, provided examples are not version specific and should work on both latest versions of Oracle and Postgres.