Postgres and Open Source Experts

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' …

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: install PostgreSQL instance …

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, when …

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 …

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, …

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 …

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 basis. …

The deny_updates project on PgFoundry contains PL/Perl and PL/PerlU functions …