Validating JSON data type column in PostgreSQL

In this post, we see how to validate the JSON/JSONB data type column in PostgreSQL 10. Most of the basic JSON/JSONB validation has been taken care in core of PostgreSQL itself, however you may have a requirement to validate the JSON format elements data types while inserting. For such requirements, you need to write a Pl/PgSQL function to validate the JSON data when accepting it in JSON/JSONB column in a CHECK constraint.

Continue reading →

Compiling plpgsql_check extension with EDB Postgres 9.6

plpgsql_check extension helps developers to validate all embeded SQL and SQL statements inside plpgsql function. Its one of the useful extensions particularly when working with plpgsql development. For more details refer to plpgsql_check documentation. By default, plpgsql_check extension not enabled in community PostgreSQL or commercial EDB Postgres. You need compile the extension with your flavor database. Community PostgreSQL compilation is easy and documented in the above reference link, however below steps help you to compile with commercial EDB Postgres database.

Continue reading →

Compiling write-able mongo_fdw extension on binary format of PostgreSQL installation.

A short blog to enable write-able mongo_fdw extension in PostgreSQL 9.4. PostgreSQL provides a powerful feature called Foreign Data Wrappers (FDW), which allows DBAs to connect to other data sources from within PostgreSQL. Foreign Data Wrapper implementation is based on SQL/MED, that’s supported from PostgreSQL 9.1 version onwards, which means we can now access remote database through PostgreSQL seamlessly. Today we have variety of FDW’s available, in this blog, we will be compiling a latest version of write-able FDW “mongo_fdw” to access MongoDB.

Continue reading →

Compiling pg_repack extension on binary format of PostgreSQL installation

This blog is about compiling pg_repack extension on binary format of PostgreSQL installation. Most of you know, pg_repack extension is one of well known PostgreSQL extensions, its especially used for reclaiming space[bloats] ONLINE without holding an EXCLUSIVE LOCK on Tables/Indexes. To enable pg_repack extension in PostgreSQL database, it should be compiled from sources. Its quite easy and simple to compile from source on any installed variants(source,rpm,binary) of PostgreSQL, however its slightly different if it is with binary format of PostgreSQL [One Click Installer] as they are pre-built binary bundle with dependency libraries.

Continue reading →

Utilising caching contrib's pg_prewarm and pg_hibernator in PostgreSQL 9.4.

Numerous DBA’s (counting me), put questions all the time to PostgreSQL hackers/developers/architects on mailing list: Q1. Does PG has the ability to cache/warm a relation ? Q2. Is it possible to return to prior state of cache where it was left before shutting down the database server because of maintenance ? In earlier releases of PostgreSQL, there in no chance of warming a relation or storing a cache states, but from PostgreSQL 9.

Continue reading →