CMD: Alexey Klyukin's Blog Command Prompt Blog Feed: Alexey Klyukin's Blog Mon, 26 Nov 2012 13:25:24 -0700 Command Prompt Mammoth 46 46 Mon, 26 Nov 2012 13:25:24 -0700 Writing a custom conflict handler for Bucardo 4 <p>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' and 'target' below), or using a delta row timestamp to determine the winner; other options include skipping conflicting rows altogether or picking one at random, but they are not very useful if you need the data to be consistent between replicas. For more complex conflict resolution rules it's necessary to write a custom conflict handler and, since the documentation is really scarce on that matter, I've decided to show how to create a simple one.</p> OpenSource PostgreSQL Mon, 30 Jul 2012 02:07:44 -0700 Fedora 17 not so easy PostgreSQL configuration <p>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. <p>My task was simple: install PostgreSQL instance for testing on a fresh Linux box, use a non-standard port. The catch: the box was running a relatively new Fedora 17. OpenSource PostgreSQL Fri, 22 Jun 2012 06:15:00 -0700 Binding PostgreSQL server to specific CPU cores in Linux <p>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. <p>Suprisingly, when compared to the old server, the extra resources didn't improve the performance. Quite the contrary, the load average and CPU utilization on a new system was much higher during load spikes, while the TPS number plummeted. After performing initial examination of their server (applying our <a href="">Audit & Tune package</a>) we've decided that 8.2 might itself become an issue. This version of PostgreSQL is outdated and no longer supported by the community. What was suggested is that 8.2 doesn't scale well for 32 cores. How can we verify that hypothesis? Since they were running a relatively modern Linux kernel (2.6.32, supplied with RHEL 6) we were able to take advantage of the interface provided by the taskset utility. PostgreSQL Thu, 26 Apr 2012 10:55:23 -0700 Migrating hierarchical queries from Oracle to PostgreSQL <p>This is the second part in a <a href="">series of blog posts</a> describing PostgreSQL analogs of common Oracle queries</p> <p>One of the most intricate Oracle specific constructions is "START WITH ... CONNECT BY". According to <a href="">Oracle's documentation</a>, 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 an example.</p> <p>Consider a table that stores opponents moves in a game of chess. Each table row contain coordinates (in <a href="">algebraic notation</a>) of a single move by whites and the move in response by blacks, as well as a column that references a preceding move, making it possible to keep multiple continuations of a specific move for the post-game analysis.</p> <pre>CREATE TABLE moves(id integer, parent integer, white varchar(10), black varchar(10));</pre> <p>The following statements describe 2 variants of a very short game, the first one leading to the early checkmate (known as a scholar's mate), and the second one to the position where black successfully avoids being checkmated.</p> <pre> INSERT INTO moves VALUES(1, 0, 'e4', 'e5'); INSERT INTO moves VALUES(2, 1, 'Qh5', 'Nc6'); INSERT INTO moves VALUES(3, 2, 'Bc4', 'g6'); INSERT INTO moves VALUES(4, 3, 'Qf3', 'Nf6'); -- checkmate is avoided INSERT INTO moves VALUES(5, 2, 'Bc4', 'Nf6'); INSERT INTO moves VALUES(6, 5, 'Qxf7#', NULL); -- blacks being checkmated </pre> <p>Let's build an Oracle query showing a sequence of moves that leads to the checkmate:</p> <pre> SELECT DISTINCT id AS final_move_id, LTRIM(SYS_CONNECT_BY_PATH(NVL(white,'')||':'||NVL(black,''),';'),';')||';' AS moves, LEVEL AS mate_in FROM moves WHERE white LIKE '%#' OR black LIKE '%#' START WITH id = 1 CONNECT BY PRIOR id = parent;</pre> <p> <table cellspacing=4> <tr> <th scope="col"> FINAL_MOVE_ID </th> <th scope="col"> MOVES </th> <th scope="col"> MATE_IN </th> </tr> <tr> <td align="right"> 6 </td> <td> e4:e5;Qh5:Nc6;Bc4:Nf6;Qxf7#:; </td> <td align="right"> 4 </td> </tr> </table> <p> <p>The query instructs Oracle to look for a checkmate:</p> <ul> <li>The search starts at the move with id = 1, as indicated in the START WITH clause, and considers all possible continuations that lead to a checkmate, denoted by the final '#' in the move's description. <li>Each move and its direct continuation, for instance, moves 2 and 5 represent the parent-child relationship, described by the PRIOR condition. <li>The search depth is stored in the LEVEL pseudo-column. </ul> <p>As a result, Oracle goes from one row to another only if the parent column of the new row contains the id of the current row, accumulating all visited rows in a result set. The SYS_CONNECT_BY_PATH clause produces a string out of the specified columns of the visited rows, connecting each (parent, child) pair by the designated character (';' in our case).</p> <p>Being Oracle SQL extension, CONNECT BY is not available in PostgreSQL. Recent versions of PostgreSQL implement <a href="">Common Table Expressions (CTE)</a>, SQL-standard way of dealing with hierarchical data. Here's one possible rewrite of the query above for PostgreSQL using recursive CTEs: OpenSource PostgreSQL SQL Fri, 09 Mar 2012 03:30:35 -0700 Pearls of Oracle to PostgreSQL conversion <p> 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 <a href=''>ora2pg</a> and <a href="">oracle foreign data wrapper</a> to accomplish this. They do, however, have a significant number of queries that needs to be converted.</p> <p> Apparently, most queries from Oracle and PostgreSQL look similar; after all, both are relational database systems, as opposed to Cassandra or MongoDB, seeking to adhere to the same standards. Unfortunately, Oracle is known for the non-standard syntax extensions that are widely used by DB developers. Fortunately, the PostgreSQL community went great lengths not only to rely on SQL standards, but to use them as a source for the new powerful features.</p> <p> How do recent versions of PostgreSQL stack up against Oracle syntax extensions, you may ask. The answer is very well; in fact, there is hardly anything in Oracle 8i syntax that cannot be emulated by PostgreSQL. Let's consider a couple of examples, starting from the one of the most peculiar syntax construction Oracle is known for, outer joins:</p> <p> Oracle versions before 9 used a non-standard syntax for left and right outer joins; support for full outer joins was missing altogether. Suppose we have a schema to track orders:</p> <pre> CREATE TABLE users(id INTEGER, email VARCHAR2(200)); CREATE TABLE products(id INTEGER, name VARCHAR2(200), price FLOAT(126)); CREATE TABLE orders(id INTEGER, userid INTEGER, productid INTEGER, quantity INTEGER); </pre> <p> A typical LEFT JOIN, returning all users that haven't made any orders, looks like this:</p> <pre> SELECT FROM users u, orders o WHERE o.userid IS NULL AND = o.userid (+); </pre> <p> Oracle's OUTER JOIN is represented by a special WHERE clause with a '+' sign added. The '+' denotes the nullable side of the join: we are looking for users that don't have any rows associated with their ids in the orders table. The PostgreSQL equivalent of this query will be a straightforward:</p> <pre> SELECT FROM users u LEFT JOIN orders.o ON ( = o.userid) WHERE o.userid IS NULL;</pre> <p> Clearly, the rule is simple: if you see a '(+)' in the Oracle's WHERE clause - it's really an outer join; if a '+' sign is on the left side of the binary operator - it's a RIGHT JOIN and vice versa. </p> <p> Let's consider an Oracle construction that is a little more complex to emulate, Oracle's rownum column. This is a special pseudo-column that numbers rows returned by an Oracle query. For instance, to get positions of all products in the product list ordered by price, one can issue the following Oracle query:</p> <pre> SELECT rownum as position, id, name, price FROM (SELECT * FROM products ORDER BY price); </pre> <p> If 'products' stores data in no particular order, the rownum value would be different from the product id.</p> <p> Unlike Oracle, PostgreSQL doesn't have a rownum column, so what would we do with the query above? Turns out, we can emulate it with the help of <a href="">window functions</a>, a feature available since PostgreSQL 8.4. These functions are capable of performing calculations over related group of rows called partitions. In our example, we need to apply the function called row_number() over the whole result set (denoted by the empty PARTITION BY clause), using ordering by price to compute the row number:</p> <pre> SELECT row_number() OVER (ORDER BY price) as position, id, name, price FROM products ORDER BY price; </pre> <p> Note that there is a subtle problem in the queries above. Different positions will be assigned to the items with equal price points. While there is no easy way to fix this in Oracle 8i, we can assign equal positions to the items with no price differences by switching the window function from row_number() to rank() in the PostgreSQL case: <pre> SELECT rank() OVER (ORDER BY price) as position, id, name, price FROM products ORDER BY price </pre> <p> There is another common use case of Oracle's rownum column, to limit the number of rows returned from a query like this (used to extract the 5 most expensive products): </p> <pre> SELECT * FROM (SELECT * from products ORDER BY price DESC) WHERE rownum <= 5; </pre> <p> We need neither window functions, nor subqueries in the PostgreSQL equivalent of this query: </p> <pre> SELECT * FROM products ORDER BY price DESC LIMIT 5; </pre> <p> Easy, right? </p> <p> You are probably wondering why am I comparing the recent versions of PostgreSQL to Oracle 8i, being almost 15 years old now? The reason is, there are a number of customers still running such an old products not ready to shell out hundreds of thousands for an upgrade to a new Oracle major version. PostgreSQL might be the best and cost-effective way to get their data to a modern relational database system and, of course, upgrades to new major versions are free (and can be performed in-place with <a href="">pg_upgrade</a>).</p> <p> There are more examples I'd like to demonstrate (including the conversion of Oracle's CONNECT BY clause), but this post is already getting too long, so I'm wrapping up for now. Stay tuned for further posts!</p> PostgreSQL SQL Wed, 16 Feb 2011 06:46:26 -0700 Changes in PL/Perl <p> 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.</p> <pre> CREATE FUNCTION check_values { my $val = shift; my $aref = shift; foreach (@$aref) { return true if $val eq $_ } return false; } </pre> <p>A practical use for this function would be a CHECK constraint ensuring that only a limited set of value can be assigned to a particular column (of course, in real life one can as well use <a href="">enums</a>).</p> <p>Even when the function itself is very simple there's no way to translate it directly into the PL/Perl code for PostgreSQL 9.0 and below. The main obstacle to such translation is the conversion of input arguments to strings in PL/Perl functions. This means that an input array of '{1,2,3,4}', passed into a PL/Perl function, will be accessible as a string literal '{1,2,3,4}' and not as an array of 4 elements.</p> <p>In the past, one way to handle that was writing Perl code to parse the array represented as a string:</p> <pre> CREATE OR REPLACE FUNCTION check_values(TEXT, TEXT[]) RETURNS BOOLEAN AS $fn$ my $val = shift; my $list = shift; $list =~ s/^{(.*)}$/\1/; foreach (split /,/, $list) { return true if $_ eq $val; } return false; $fn$ LANGUAGE plperl; SELECT check_values('a', '{a,b,c,d}'); check_values -------------- t (1 row) </pre> <p>While this code works for simple cases, it fails for more complex ones, such as when the array argument has more than one dimension, or one of the elements contains a comma:</p> <pre> SELECT check_values('a,b,c', ARRAY['a,b,c','e,f,g']); check_values -------------- f (1 row) </pre> <p>I thought that PL/Perl should be smarter on handling the input arguments, and the changes I was working on, which have been submitted <a href="">as a patch</a> for the <a href="">current commitfest</a>, teach PL/Perl to handle input arrays properly. Here's how the check function would look with this patch applied.</p> <pre> CREATE OR REPLACE FUNCTION check_values (TEXT, TEXT[]) RETURNS BOOLEAN AS $fn$ my $val = shift; my $aref = shift; foreach (@$aref) { return true if $val eq $_; } return false; $fn$ LANGUAGE plperl; SELECT check_values('a,b,c', ARRAY['a,b,c','e,f,g']); check_values -------------- t (1 row) </pre> <p>Alex Hunsaker did a great review by not only providing valuable comments and bug fixes, but also by extending the patch to generate both a reference and a string representation of array arguments, depending on whether or not they are used in a string context, so the changes won't break existing PL/Perl code. Additionally, he enabled SPI functions in PL/Perl to receive composite type and array arguments directly as Perl hash or array references, without converting them to a string first:</p> <pre> CREATE TYPE foo AS (bar int, baz int); CREATE TYPE DO $$ my $x = {bar => 9, baz => 1}; # composite type represented as a hash ref my $plan = spi_prepare('SELECT $1 as foo','foo'); my $rv = spi_exec_prepared($plan, {}, $x); elog(NOTICE, $rv->{rows}->[0]->{foo}->{bar}); $$ LANGUAGE plperl; NOTICE: 9 CONTEXT: PL/Perl anonymous code block DO </pre> <p>I look forward for this functionality to be added to 9.1 and to work on other useful features for PL/Perl and PostgreSQL.</p> OpenSource PostgreSQL Mon, 16 Aug 2010 07:55:39 -0700 An update on Replicator <p>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.<p>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. Additionally, each file contains a list of replication tables the data belongs to. Every slave has a distinct set of tables to replicate. Finally, transaction files are addressed by a special data structure called replication queue.<p> After connecting to the forwarder for the first time each slave node performs an initial sync (full dump) by requesting a complete up-to-date snapshot of replication tables. The forwarder doesn't necessarily resend such request to a master process. Instead, it checks the queue for past full dumps and reuses them if appropriate.<p>To reduce the bandwidth and disk space consumption per each slave the set of tables replicated by the slave is compared with the set of current transaction's tables, and the forwarder decides to send a transaction to the slave only when these 2 sets intersects; thus, each slave receives data only for those tables it replicates. So far, there was an important exception to this rule: full dump transactions were always sent to every slave. The original justification for this was the fact that full dump was required after addition of a new replicated table, and every slave had to be aware of this addition. <p>In 1.8 we introduced a new feature called 'per-table dumps', which allowed a slave to request a snapshot of a single table, instead of requesting a full dump. Currently, when a new table is added to the replication, only a single per-table dump is requested, and there's no need for a full dump. This made possible for a slave to 'skip' a full dump, and changes committed last week implement exactly this: if the slave is in sync (i.e. doesn't wait for a dump, or recovering from an error), the forwarder just skips sending full dump transactions to this slave, therefore avoiding most bandwidth-consuming transactions. It's a clear win!<p>Additionally there is another related positive change. When a slave restores full dump transaction it replaces the data of each replicated table with the one from the dump, leaving the table inconsistent for some period of time (which is usually short, but depends on table size and other factors). By reducing the average number of dumps per each slave we also reduced the number of these 'inconsistency gaps'. Double win!<p>The next version, 1.9, is still in development. We put it (as well as <a href="">other open-source projects</a>) on <a href="">github</a>, so you are welcome to check it out and join the <a href="">replicator mailing list</a>.<p><p>Stay tuned for further updates! OpenSource PostgreSQL Wed, 23 Jun 2010 13:36:10 -0700 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 <a href="">Beta 2 of PostgreSQL 9.0</a>. Alas, PostgreSQL doesn't backport features, in order to gain similar functionality in earlier releases you can use deny_updates.<p>The <a href="">deny_updates project on PgFoundry</a> contains PL/Perl and PL/PerlU functions that can be installed as triggers to allow or deny certain types of operations. deny_updates function can block updates to individual table columns. In the example below we'll create 2 tables to model the data for an airport timetable:<p><code>CREATE TABLE flights (flight_no TEXT PRIMARY KEY, departure TEXT, arrival TEXT);<p>CREATE TABLE timetable(flight_no TEXT REFERENCES flights ON UPDATE CASCADE, time TIMESTAMP, status TEXT);</code><p>Let's populate them with test data:<p><code>INSERT INTO flights VALUES('WU917', 'KBP', 'SIP');<p>INSERT INTO timetable VALUES('WU917', '2010-06-19 16:50', 'pending');</code><p>In practice we don't want a flight number in the timetable to be updated for an already existing departure/arrival time and status. With deny_updates we can easily add this constraint to our model:<p><code>CREATE TRIGGER deny_flightno_updates ON UPDATE TO timetable FOR EACH ROW EXECUTE PROCEDURE deny_updates('ALLOW_LIST', time, status);</code><p>Now all attempts to update the flight number will be denied:<p><code>UPDATE timetable SET flight_no='WU918' WHERE flight_no='WU917';<p>ERROR: error from Perl function "deny_updates": update of attribute 'flight_no' denied by the trigger deny_flightno_updates at line 79.</code><p>The value 'ALLOW_LIST' of the first argument indicates that the rest of the argument list contains columns allowed to be updated, and updates to non-listed columns will be denied. To get the opposite, we should leave the first argument empty. Let's use this form to construct a trigger to disallow changes of departure and arrival airports for an already added flight:<p><code>CREATE TRIGGER deny_airport_changes ON UPDATE TO flights FOR EACH ROW EXECUTE PROCEDURE deny_updates('', departure, arrival);</code><p>In practice, sometimes a trigger function has to check values of the OLD and NEW tuples to decide on allowing or blocking the triggering operation. deny_updates project provides a function called 'allow_on_condition', which does exactly that:<p><code>CREATE TRIGGER lock_cancelled_status BEFORE UPDATE ON timetable FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('%s != ''cancelled''', 'OLD.status');</code><p>The first argument of allow_on_condition is a condition, which is evaluated to decide whether the trigger operation should be allowed. The '%s' placeholders are replaced in order with arguments starting from the second, just like in printf. Note that these arguments should be quoted as strings, otherwise PostgreSQL won't recognize them as valid literals due to NEW and OLD prefixes.<p>The function above forbids changing a flight status once it's set to 'cancelled':<p><code>UPDATE timetable SET status='cancelled';<p>UPDATE 1<p>UPDATE timetable SET status='in flight';<p>ERROR: error from Perl function "allow_on_condition": expression SELECT E'cancelled' != 'cancelled' AS result is false, UPDATE is not allowed at line 71.</code><p>These functions were created by Command Prompt, Inc for <a href="">Enova Financial</a>, which kindly decided to open-source them. You can download them and/or leave your feedback at the <a href="">project's page on PgFoundry</a> OpenSource PostgreSQL SQL Fri, 23 Apr 2010 12:29:32 -0700 CodeCamp PL/Perl talk Last week I delivered a PL/Perl talk at <a href="">CodeCamp</a> 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 in production for years and were really impressed with its reliability and effectiveness, but most of them were not very familiar with it. I feel that PUGs are almost non-existent in CIS (with a nice exception of Moscow PostgreSQL user group, kudos to Nikolay), so we still have a lot of work to do on that front :) The slides are <a href="">here</a> (in Russian, but most of them are PL/Perl examples, so language doesn't matter that much as long as you understand Perl :) ). I had a couple of very interesting conversations after the talk about upcoming features in 9.0, and one of the students was actually inspired to develop a a new PL/ language. I'm looking forward for more opportunities to make PostgreSQL more popular in Ukraine and CIS ! OpenSource PostgreSQL SQL