Postgres foreign data wrapper for Oracle database
This is part 2 of #fdw foreign data wrappers series of posts. In part 1 it was described what foreign data mean in Postgres as well as provided an example of connection to MySQL database. In this post we will deal with accessing data in Oracle database.
Postgres foreign data wrapper for MySQL database
This is the first part of #fdw foreign data wrappers series of posts.
One of the great features in Postgres is the ability to access data stored in outer sources as if they were stored in ordinary tables within the database. There is no requirement to the source as is. For example, it can be another RDBMS such as Oracle or MySQL. But we are not limited to relational databases only. It can be also just a csv file or even Hadoop file system objects. All you need to do to get access to these data is to install corresponding foreing data wrapper extension.
Postgres datatypes mechanics overview
Postgres allows automatic types conversion to some extent.
It supports fully automated implicit conversion, more constrained implicit conversion on assignment operation only and explicit conversion specified via CAST function or :: operator.
Solution to implement parallel jobs in Postgres
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.
Solution to implement persistent temp tables in Postgres
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.
Compiled guide for quick start with Postgres
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.
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.
As many other people all over the world I like football. Some time ago I found free public API with football results which are provided by football-data.org site and decided to write UI for it.
Chumaky team is happy to announce our site launch. To make this fact more interesting from tech side in this blog post I'll describe how it was built & deployed.
