Just excellent service

POSTGRES CENTERED FULL STACK SUPPORT, CONSULTING & DEVELOPMENT

Blog

Writing a custom conflict handler for Bucardo 4

Bucardo, an asynchronous multi-master replication system (or, maybe the asynchronous multi-master replication system for PostgreSQL, cause I know of no other actively developed ones), deals with replication conflicts by providing conflict handlers in a form of standard (built-in) or custom code procedures. The built-in conflict handlers resolve a conflict by taking a row from the source or the target databases (in Bucardo 4 only 2 masters are supported, called 'source …

Fedora 17 not so easy PostgreSQL configuration

I don't usually post rants here, but this one might be actually helpful to others, so let's make an exception. It will be related to installing PostgreSQL from distro-specific packages. I usually prefer setting PostgreSQL from sources, unlike the majority of users; nevertheless, I'm familiar with how popular distros, like Debian or Fedora, manage their PostgreSQL layouts. Or so I thought until today.

My task was simple …

Binding PostgreSQL server to specific CPU cores in Linux

Recently we had a customer who was running PostgreSQL 8.2 on a 32 cores system with 64GB of memory. They were deploying this server in addition to the already running one with 24 total cores and 32GB of memory. PostgreSQL configuration has been adjusted for extra resources, the database has been partitioned roughly in half between the 2 servers and the queries running against both servers were similar.

Suprisingly …

Migrating hierarchical queries from Oracle to PostgreSQL

This is the second part in a series of blog posts describing PostgreSQL analogs of common Oracle queries

One of the most intricate Oracle specific constructions is "START WITH ... CONNECT BY". According to Oracle's documentation, the syntax is: SELECT [query] [START WITH initial_condition] CONNECT BY [nocycle] condition. This statement is commonly used to traverse hierarchical data in the parent-child order. It's easier to illustrate how it works with …

Pearls of Oracle to PostgreSQL conversion

We have been working on a large Oracle 8i conversion to PostgreSQL. Our customers were not concerned with the data conversion: there are tools like ora2pg and oracle foreign data wrapper to accomplish this. They do, however, have a significant number of queries that needs to be converted.

Apparently, most queries from Oracle and PostgreSQL look similar; after all, both are relational database systems, as opposed to Cassandra or MongoDB …

Changes in PL/Perl

I have been disappointed for a long time with the way PL/Perl handles array arguments. For example, let's consider a simple Perl function that takes a value and a list and checks whether the value is present in the list.

CREATE FUNCTION check_values
{
    my $val = shift;
    my $aref = shift;
	
    foreach (@$aref) {
        return true if $val eq $_ 
    }
    return false;
}

A practical use for this function would be a …

An update on Replicator

This is my first post on Replicator, I'm going to start by describing the terminology we use, bringing some analogies from other replication systems.

Replicator is an asynchronous master-to-multiple-slaves replication system. It works by propagating binary changes from a single read-write node (called master) to one or more read-only nodes (called slaves) through an intermediary (forwarder) process. The data changes are stored in binary transaction files on a per-transaction …

Controlling per-column updates with deny_updates

One of my favorite features of the upcoming PostgreSQL release is conditional triggers. With minimum efforts one can build per-column triggers by adding a column check into the triggering condition. This functionality is already available with Beta 2 of PostgreSQL 9.0. Alas, PostgreSQL doesn't backport features, in order to gain similar functionality in earlier releases you can use deny_updates.

The deny_updates project on PgFoundry contains PL/Perl and …

CodeCamp PL/Perl talk

Last week I delivered a PL/Perl talk at CodeCamp conference in Kyiv, Ukraine. The conference has been held at Kyiv Polytechnic Institute, which is one of the top technical schools in Ukraine, so there were both really smart students and seasoned engineers. I talked about Perl inside PostgreSQL, deciding to highlight interesting features of PL/Perl with examples, hopefully covering most of them. Some of the attendees used PostgreSQL …