When: 7-9pm Thu Mar 20, 2014
Where: Iovation
Who: Mark Wong
What: An Adventure in Data Modeling: The Entity-Attribute-Value Model

Emma, Inc. is an email marketing company that provides a Web based
application for managing email lists and marketing campaigns. This is a
tale about Emma’s experience with a database schema design refactor that
is now using the Entity-Attribute-Value (EAV) data model on the members
in email lists. We will briefly describe the previous data model and
why we had to move to a new one. This is followed by all the places
where Emma stumbled and recovered in implementing the EAV data model and
rebuilding the application around it. Finally we will touch on where
Emma might go next with respect to the data.

Mark is a Data Architect at Emma, Inc. and works with their PostgreSQL
database systems.

–

Our meeting will be held at Iovation, on the 32nd floor of the US Bancorp Tower at 111 SW 5th (5th & Oak). It’s right on the Green & Yellow Max lines. Underground bike parking is available in the parking garage; outdoors all around the block in the usual spots. No bikes in the office, sorry!

Building security will close access to the floor at 7:30.

See you there!

The Ad

[continue reading]

When using COPY, there is an option called FORCE_NOT_NULL allowing to enforce a string to be not null even if it is not quoted. Here is an example of how it works:

=# CREATE TABLE aa (a text);
CREATE TABLE
=# \COPY aa FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(a));
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ""
>> 
>> \.
=# \pset null 'null'
Null display (null) is "null".
=# SELECT * FROM aa;
 a 
---


(2 rows)

In this case, even if the values are empty, FORCE_NOT_NULL forces them to be inserted as empty strings and not NULL values. Now, let's look at the new option FORCE_NULL that is introduced in Postgres 9.4 by this commit, feature actually written by Ian, hacker living in Japan as well as the author of this blog.

commit 3b5e03dca2afea7a2c12dbc8605175d0568b5555
Author: Andrew Dunstan <andrew@dunslane.net>
Date:   Tue Mar 4 17:31:59 2014 -0500

Provide a FORCE NULL option to COPY in CSV mode.

This forces an input field containing the quoted null string to be
returned as a NULL. Without this option, only unquoted null strings
behave this way. This helps where some CSV producers insist on quoting
every field, whether or not it is needed. The option takes a list of
fields, and only applies to those columns. There is an equivalent
column-level option added to file_fdw.

Ian Barwick, with some tweaking by Andrew Dunstan, reviewed by Payal
Singh.

Contrary to FORCE_NOT_NULL, this can be used to force an empty value to be inserted as NULL even if it is quoted.

=# TRUNCATE aa;
TRUNCATE TABLE
=# \COPY aa FROM STDIN WITH (FORMAT csv, FORCE_NULL(a));
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ""
>> 
>> \.
=# SELECT * FROM aa;
  a   
------
 null
 null
(2 rows)

file_fdw comes up as well with a similar new option, and it can be used on columns.

=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
=# CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
=# CREATE FOREIGN TABLE t

[continue reading]

Version 2.0RC2 of repmgr, Replication Manager for PostgreSQL clusters, has been released. This release introduces a new experimental feature, autofailover. With autofailover repmgr is able to automatically promote a standby and let the other standbys follow the new master, without interaction of the DBA. It also adds a lot of bug fixes and several new features. Read the full announcement at http://repmgr.org/release-notes.html.

About repmgr

repmgr is a set of open source tools that helps DBAs and System administrators manage a cluster of PostgreSQL databases.

By taking advantage of the Hot Standby capability introduced in PostgreSQL 9, repmgr greatly simplifies the process of setting up and managing database with high availability and scalability requirements.

repmgr simplifies administration and daily management, enhances productivity and reduces the overall costs of a PostgreSQL cluster by:

• monitoring the replication process;
• allowing DBAs to issue high availability operations such as switch-overs and fail-overs

repmgr is production quality software and used widely across the world
with PostgreSQL. Many users rely on repmgr to maintain their
replication setups and as such we take new releases seriously and mark
the level of maturity as a guide for users. As repmgr 2.0 moves
towards production we continue to regard the autofailover feature as
currently in beta; we expect that to fully mature in the 2.1 release.

2ndQuadrant provides contract support for PostgreSQL that includes
both repmgr and the autofailover feature.

The PostgreSQL Project is yet again participating in Google Summer of Code for 2014. Google will be funding students who take part at $5,500 USD per student (see GSoC FAQ for more details). We would like to hear from students who would be willing to work on projects to add new or enhance existing features. You won't be going it alone, we'll assign experienced community developers to mentor you throughout your project.

Have a look at the TODO list on the wiki for inspiration, or the list of project ideas submitted so far by the community, although these are by no means what projects are limited to. Whether you've spotted something you think you could work on, or have a new idea to propose, submit it to the pgsql-students mailing list for discussion (see the mailing list page to subscribe) but do it soon as there's less than 3 weeks to register final proposals.

Also, if you know of any students who may be interested in participating, please send them our way.

More information can be found on the PostgreSQL GSoC page

I just attended and presented at ConFoo, where I always learn new things. This year, the best talk I attended was by our own Magnus Hagander. Called Integrated Cache Invalidation for Better Hit Rates, it addresses the difficult task of controlling web page cache refresh by providing a reliable mechanism for invalidating the cache when the database changes. It shows how to use triggers and pgq to communicate changes to a Varnish web frontend.

Caching in front of a database is often risky. However, with triggers, pgq, and Varnish, proper invalidation can be accomplished, dramatically increasing the scalability of database-backed websites.

One of the changes in the recently released DBD::Pg version 3 (in addition to the big utf8 change), is the addition of a new attribute, pg_switch_prepared. This accompanies a behavior change in the use of prepare/execute. DBD::Pg will now postpone creating a server-side PREPARE statement until the second time a query is run via the execute() method.

Technically, DBD::Pg will use PQexecParams (part of the underlying libpq system that DBD::Pg uses) the first time a statement is executed, and switch to using PQexecPrepared the second time the statement is executed (by calling PQprepare first). When it actually switches is controlled by the pg_switch_prepared attribute, which defaults to 2 (the behavior above). You can set it to 0 or 1 to always use PQexecPrepared (as the older versions did), or you can set it to -1 to always use PQexecParams and avoid creating prepared statements entirely.

The typical flow of events in a DBI script is to create a statement handle via the prepare() method, then call the execute() time with varying arguments as many times as needed.

#!perl

use strict;
use warnings;
use DBI;

my $DSN = 'DBI:Pg:dbname=postgres';
my $dbh = DBI->connect($DSN, '', '', {AutoCommit=>0,RaiseError=>1,PrintError=>0})
  or die "Connection failed!\n";
print "DBI is version $DBI::VERSION, DBD::Pg is version $DBD::Pg::VERSION\n";

## We do this so we can see the version number in the logs
my $SQL = 'SELECT ?::text';
$dbh->do($SQL, undef, "DBD::Pg version $DBD::Pg::VERSION");

my $sth = $dbh->prepare('SELECT count(*) FROM pg_class WHERE relname = ?');
$sth->execute('foobar1');
$sth->execute('foobar2');
$sth->execute('foobar3');

When the script above is run on DBD::Pg versions 2.19.1 and 3.0.0, you can see the difference:

LOG:  execute <unnamed>: SELECT $1::text
DETAIL:  parameters: $1 = 'DBD::Pg version 2.19.1'
LOG:  execute dbdpg_p30462_1: SELECT count(*) FROM pg_class WHERE relname = $1
DETAIL:  parameters: $1 = 'foobar1'
LOG:  execute dbdpg_p30462_1: SELECT count(*) FROM pg_class WHERE relname = $1
DETAI

[continue reading]

• The original PostgreSQL 9.0 Replication shirt
• The PostgreSQL 9.3 shirt 
• The PostgreSQL 9.2 poster
• Even a PostgreSQL 9.3 laptop sleeve

Just a quick reminder that Dallas/Fort Worth PostgreSQL Users Group has a Meetup next week.

What: Talk about PostgreSQL and column based data storage

When: Wednesday, March 5, 2014 7:00 PM

Where:
Improving Enterprises
16633 Dallas Parkway Suite 110 Addison, TX 75001

DFW PUG on Meetup

We had our Streaming Rep Lab followup last night¹. It was mostly successful: half of the new people got it up & running, and the people who’d been at the previous workshop got to experiment with almost everything on the agenda.

The cheatsheet is up on our git repo: https://github.com/pdxpug/cheatsheets ².

We talked a bit about monitoring and messed around with some of the available functions (e.g. pg_last_xact_replay_timestamp), started looking at pg_xlogdump (for “educational purposes”, of course), checked out a packet capture between the master & standby, and finished up with breaking the link between the master & the standby: the standby cried a bit about not being able to reach the master, but as soon as we restored the link, everything was fine.

The overarching theme of the evening is that we need to come up with a more robust plot to make Postgres fail in an interesting way.

SELECT pg_last_xact_replay_timestamp(); will give you a WAL ID, but is only valid on a standby.
The function to translate it to a file name: SELECT pg_xlogfile_name([id]); can only be run on the master. So that’s kinda weird and requires some puzzling out.

I started a wiki page of ideas for future labs, but we may be hung up on streaming rep & breaking it for a little while

Thanks to Emma for hosting & providing dinner!

–

1 – With a puppy.
2 – Yes, I know there is something wrong with the step numbering. It renders correctly in github’s md validator, but not when I upload it.

[continue reading]

I’ll be presenting “Really, Really Fast Django” at Djangocon EU. Do come! It’s on a private island!

This is (bad) Perl

[continue reading]

PostgreSQL 9.4 is going to be shipped with a feature called ordered-set aggregates. This can be used with a new clause called WITHIN GROUP. All those things have been introduced by the following commit.

commit 8d65da1f01c6a4c84fe9c59aeb6b7e3adf870145
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon Dec 23 16:11:35 2013 -0500

Support ordered-set (WITHIN GROUP) aggregates.

This patch introduces generic support for ordered-set and hypothetical-set
aggregate functions, as well as implementations of the instances defined in
SQL:2008 (percentile_cont(), percentile_disc(), rank(), dense_rank(),
percent_rank(), cume_dist()).  We also added mode() though it is not in the
spec, as well as versions of percentile_cont() and percentile_disc() that
can compute multiple percentile values in one pass over the data.

Unlike the original submission, this patch puts full control of the sorting
process in the hands of the aggregate's support functions.  To allow the
support functions to find out how they're supposed to sort, a new API
function AggGetAggref() is added to nodeAgg.c.  This allows retrieval of
the aggregate call's Aggref node, which may have other uses beyond the
immediate need.  There is also support for ordered-set aggregates to
install cleanup callback functions, so that they can be sure that
infrastructure such as tuplesort objects gets cleaned up.

In passing, make some fixes in the recently-added support for variadic
aggregates, and make some editorial adjustments in the recent FILTER
additions for aggregates.  Also, simplify use of IsBinaryCoercible() by
allowing it to succeed whenever the target type is ANY or ANYELEMENT.
It was inconsistent that it dealt with other polymorphic target types
but not these.

Atri Sharma and Andrew Gierth; reviewed by Pavel Stehule and Vik Fearing,
and rather heavily editorialized upon by Tom Lane

To put it in simple words, this feature allows doing operations on a group of rows organized with ORDER BY thanks to the clause WITHIN GROUP.

As mentionned in the commit message, th

[continue reading]

Data migrations always have a wide range of challenges. I recently took on a request to determine the difficulty of converting an ecommerce shop's MySQL 5.0 database to PostgreSQL 9.3, with the first (presumably "easier") step being just getting the schema converted and data imported before tackling the more challenging aspect of doing a full assessment of the site's query base to re-write the large number of custom queries that leverage MySQL-specific language elements into their PostgreSQL counterparts.

During the course of this first part, which had contained a number of difficulties I had anticipated, I hit one that I definitely had not anticipated:

ERROR:  value too long for type character varying(20)

Surely, the error message is absolutely clear, but how could this possibly be? The obvious answer--that the varchar definitions were different lengths between MySQL and PostgreSQL--was sadly quite wrong (which you knew, or I wouldn't have written this).

After isolating out the row in question, the first clear distinction of the data in question was the presence of the ASCII null character in it:

'07989409006\007989409'

OK, sure, that's weird (and I have a hunch why it's there) but I'm still puzzled how the "too long" data could be in MySQL in the first place. Then, things become both weirder and more clear when I go to take a look at the record as it exists in the source database of the dump. The table in question has many columns, so naturally I look at the results with the left-justified output of \G. Without displaying the full record for a number of reasons, here's what I saw for that field:

*************************** 1. row ***************************
xxxxx: 07989409006
1 row in set (0.00 sec)

Wow! OK, so in MySQL, even though the ASCII null is clearly maintained in the data (the row came from a mysqldump directly from that same database) somehow the data are truncated to the string before the ASCII null, and so it must be determining string length from the same process that is truncating. To "prove

[continue reading]

I am happy to announce that version 1.2 of PostgreSQL Studio, an open source web interface for PostgreSQL, was released on February 23, 2014. This releases contains some new functionality mostly around improving SQL Worksheet as well as some bug fixes.

The changes in this release are:

Features

• Added Open and Save buttons to SQL Worksheet
• Moved the close button on SQL Worksheet to the upper right corner
• Improved the look and feel of pop ups windows
• Auto select the first object in a schema
• Added a limit drop down to SQL Worksheet

Bugs

• Removed extra new line characters when displaying a function
• Added a click handler to reselect an already selected row
• Added a scroll panel for the Data tab for wide tables

The new version is available from http://www.postgresqlstudio.org/download/

In analyzing a business I commonly look at reports that have two lenses, one is by doing various cohort analysis. The other is that I look for Month over Month or Week over Week or some other X over X growth in terms of a percentage. This second form of looking at data is relevant when you're in a SaaS business or essentially anythign that does recurring billing. In such a business focusing on your MRR and working on growing your MRR is how success can often be measured.

I'll jump write in, first lets assume you have some method of querying your revenue. In this case you may have some basic query similar to:

SELECT date_trunc('month', mydate) as date, 
       sum(mymoney) as revenue
FROM foo
GROUP BY date
ORDER BY date ASC;

This should give you a nice clean result:

 date                   | revenue  
------------------------+----------
 2013-10-01 00:00:00+00 | 10000    
 2013-11-01 00:00:00+00 | 11000    
 2013-12-01 00:00:00+00 | 11500    

Now this is great, but the first thing I want to do is start to see what my percentage growth month over month is. Surprise, surprise, I can do this directly in SQL. To do so I'll use a window function and then use the lag function. According to the Postgres docs

lag(value any [, offset integer [, default any ]]) same type as value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null

Essentially it orders it based on the window function and then pulls in the value from the row before. So in action it looks something like:

SELECT date_trunc('month', mydate) as date, 
       sum(mymoney) as revenue,
       lag(mymoney, 1) over w previous_month_revenue
FROM foo
WINDOW w as (order by date)
GROUP BY date
ORDER BY date ASC;

Combining to actually make it a bit more pretty (with some casting to a numeric and then formatting a bit) in terms of a percentage:

S

[continue reading]

In the next week I will be writing a series of posts about the row-security work I’ve been doing for PostgreSQL 9.4 as part of the EU’s AXLE project. I will be outlining the history, approaches tried, current status, remaining issues, and future work required.

To open the series, I’d like to talk about what row security is good for and why you might want it.

The purpose of the row-security feature is to allow different users to see different subsets of the data in the same table, according to admin-provided security rules. Security policy can use arbitrary SQL expressions to control data visibility, including sub-queries against other tables.

If you’re paying attention, this will sound a lot like a view. You’re not mistaken – row security is not unlike transparently replacing references to a table with an updatable security_barrier view over its contents. It must be a security barrier to prevent data from being leaked by user defined functions used as predicates. If row-security is enforcing write access control, it there behaves like a WITH CHECK OPTION security_barrier view.

There are a few reasons why row-security is desirable, and more useful than simply defining a view over each table.

Most importantly, row-security is pluggable – in addition to looking policies up from the system catalogs, it’s also possible to use a policy hook to supply arbitrary policy from extensions. This serves as a building block for adding SELinux-controlled row-access policies or other declarative security policies, and for using row security as the basis for row-level audit logging.

Unlike replacing a relation with an updatable security_barrier view, it’s also easy to add, change, or remove row-security policy on a table without redefining anything else in the database. Replacing a table with a view requires you to play games with access permissions and the search_path, and most importantly other views that referenced the original table do not automatically now reference the view since search_path references in views are resolve

[continue reading]

Over the past few weeks, new distributions have been added on apt.postgresql.org: Ubuntu 13.10 codenamed "saucy" and the upcoming Ubuntu LTS release 14.04 codenamed "trusty".

Adding non-LTS releases for the benefit of developers using PostgreSQL on their notebooks and desktop machines has been a frequently requested item since we created the repository. I had some qualms about targeting a new Ubuntu release every 6 months, but with having automated more and more parts of the repository infrastructure, and the bootstrapping process now being painless, the distributions are now available for use. Technically, trusty started as empty, so it hasn't all packages yet, but of course all the PostgreSQL server packages are there, along with pgAdmin. Saucy started as a copy of precise (12.04) so it has all packages. Not all packages have been rebuilt for saucy, but the precise packages included (you can tell by the version number ending in .pgdg12.4+12 or .pgdg13.10+1) will work, unless apt complains about dependency problems. I have rebuilt the packages needing it I was aware about (most notably the postgresql-plperl packages) - if you spot problems, please let us know on the mailing list.

Needless to say, last week's PostgreSQL server updates are already included in the repository.

Streaming replication slots are a pending feature in PostgreSQL 9.4, as part of the logical changeset extraction.

What are they for, what do you need to know, what changes?

What are replication slots?

Streaming replication slots are a new facility introduced in PostgreSQL 9.4. They are a persistent record of the state of a replica that is kept on the master server even when the replica is offline and disconnected.

They aren’t used for physical replication by default, so you’ll only be dealing with them if you enable their use. This post explains what they do, why they’re useful for physical replication, and why they’re necessary for logical replication.

Why slots?

As part of the ongoing work Andres has been doing on log-streaming logical replication, changeset extraction, and bidirectional replication, there’s been the need to better control WAL retention.

As streaming replication currently works, the master doesn’t make any effort to retain extra WAL for a standby that’s fallen behind. If the standby gets too far behind, the master will delete WAL segments the standby still needs to replay, and the standby cannot recover. You are expected to configure continuous archiving and provide a restore_command to give the replica access to the archive, or be willing to re-create a standby that falls behind.

A less reliable alternative is to set extra_wal_segments to a “high enough” value to ensure that the replica never falls too far behind – but there’s no real way to guess what is high enough, and the higher the value is set the greater the risk the master will be able to run out of space in pg_xlog. So generally archiving is preferred.

The master could retain WAL for a replica while it’s connected, but as soon as the replica disconnects for any reason the master forgets all about it. As transient disconnects are common, this means there’s not much point in trying to retain WAL just for connected replicas.

We’ve gotten away with this so far because the option of falling back to continuous archiving has been available f

[continue reading]