After a great initial release of PostgreSQL Studio, I received a lot of feedback from users. Most of it was very positive, but like any initial software release, there were some bugs. Some of the bigger issues fixed was using PostgreSQL Studio with Internet Explorer as well as being able to deploy PostgreSQL Studio in Glassfish. You can download version 1.1 at: http://www.postgresqlstudio.org/download/

To be better equipped to answer support questions, particularly for customers using ActivePerl in production, we have changed the licensing terms of Community Edition: http://www.activestate.com/activeperl/license-agreement. To use ActivePerl in production, you must now have a supported Business or Enterprise Edition license; we continue to provide the latest versions free for development and testing use.

There’s a very unpleasant replication issue in version 9.0.14, 9.1.10, 9.2.5, 9.3.0 and 9.3.1 of PostgreSQL. Be sure to read the linked wiki entry carefully; it can result in silent data corruption on secondary servers in replication sets.

This tutorial introduces how to use DataFiller to fill a PostgreSQL database, say for testing functionalities and performances.

Directives in comments

The starting point of the script to generate test data is the SQL schema of the database taken from a file. It includes important information that will be used to generate data: attribute types, uniqueness, not-null-ness, foreign keys… The idea is to augment the schema with directives in comments so as to provide additional hints about data generation.

A datafiller directive is a special SQL comment recognized by the script, with a df marker at the beginning. A directive must appear after the object about which it is applied, either directly after the object declaration, in which case the object is implicit, or much later, in which case the object must be explicitely referenced:

-- this directive sets the default overall size
  -- df: size=10
-- this directive defines a macro named "fn"
  -- df fn: words=/path/to/file-containing-words
-- this directive applies to table `Foo`
CREATE TABLE Foo( -- df: mult=10.0
  -- this directive applies to attribute `fid`
  fid SERIAL -- df: offset=1000
  -- use defined macro: stuff taken out from the list of words
, stuff TEXT NOT NULL -- df: use=fn
);
-- ... much later
-- this directive applies to attribute `fid` in table `Foo`
-- df T=Foo A=fid: mangle

A simple example

Let us start with a simple example involving a library where readers borrow books. Our schema is defined in file library.sql as follows:

CREATE TABLE Book(
  bid SERIAL PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE Reader(
  rid SERIAL PRIMARY KEY,
  firstname TEXT NOT NULL,
  lastname TEXT NOT NULL,
  born DATE NOT NULL,
  gender BOOLEAN NOT NULL,
  phone TEXT -- nullable, maybe no phone
);

CREATE TABLE Borrow(
  borrowed TIMESTAMP NOT NULL,
  rid INTEGER NOT NULL REFERENCES Reader,
  bid INTEGER NOT NULL REFERENCES Book,
  PRIMARY KEY(bid) -- a book is borrowed at most once at a time!
);

The first and only information we really need to provide is

[continue reading]

I have just released version 1.1.3 of DataFiller.

The Python script processes a PostgreSQL database schema file augmented with directives in comments, and generates random data matching this schema, taking into account constraints such as types, but also primary key, unique, foreign keys, not null…

The minimum setting is to provide a mult directive to specify the relative scaling size for tables. Different random generators can be selected for typical types (int, float, dates, strings…) and are subject to many parameters.

Here is a sample definition, say in a pgbench.sql file:

-- TPC-B example adapted from pgbench
-- define a macro named "POW"
  -- df POW: gen=power alpha=1.5
-- reset default size for scaling
  -- df: size=1

CREATE TABLE pgbench_branches( -- df: mult=1.0
  bid SERIAL PRIMARY KEY,
  bbalance INTEGER NOT NULL,   -- df: size=100000000 use=POW
  filler CHAR(88) NOT NULL
);

CREATE TABLE pgbench_tellers(  -- df: mult=10.0
  tid SERIAL PRIMARY KEY,
  bid INTEGER NOT NULL REFERENCES pgbench_branches,
  tbalance INTEGER NOT NULL,   -- df: size=100000 use=POW
  filler CHAR(84) NOT NULL
);

CREATE TABLE pgbench_accounts( -- df: mult=100000.0
  aid BIGSERIAL PRIMARY KEY,
  bid INTEGER NOT NULL REFERENCES pgbench_branches,
  abalance INTEGER NOT NULL,-- df: offset=-1000 size=100000 use=POW
  filler CHAR(84) NOT NULL
);

CREATE TABLE pgbench_history(  -- df: nogen
  tid INTEGER NOT NULL REFERENCES pgbench_tellers,
  bid INTEGER NOT NULL REFERENCES pgbench_branches,
  aid BIGINT NOT NULL REFERENCES pgbench_accounts,
  delta INTEGER NOT NULL,
  mtime TIMESTAMP NOT NULL,
  filler CHAR(22)
  -- UNIQUE (tid, bid, aid, mtime)
);

Then running datafiller.py will generate the schema definition and data filling ready for psql:

datafiller.py -f -T pgbench.sql | psql

Option -f for filter outputs the schema definition before data insertions. Option -T for transaction embeds everything in a single transaction. For more information, the documentation is embedded in the script:

datafiller.py --man

Disclaimer: We always assume that when we have an issue and think it's the operating system, 99% of the time, it turns out to be something else. We therefore caution against assuming that the problem is with your operating system, unless your use-case and the following example completely overlap.

It all started with one of our customers reporting performance issues with their CitusDB cluster. This customer designed their cluster such that their working set would fit into memory, but their query run-times showed every indication that their queries were hitting disk. This naturally reduced their query run times by 10-100x.

We started looking into this problem by first examining CitusDB's query distribution mechanism and then by checking the PostgreSQL instances on the machines. We found that neither was the culprit here, and came up with the following observations:

1. The customer's working set was one day's worth of query logs. Once they were done looking at a particular day, they started querying the next day's data.
2. Their queries involved mostly sequential I/O. They didn't use indexes a lot.
3. A day's data occupied more than 60% of the memory on each node (but way less than total available memory). They didn't have anything else using memory on their instances.

Our assumption going into this was that since each day's data easily fit into RAM, the Linux memory manager would eventually bring that day's data into the page cache. Once the customer started querying the next day's data (and only next day's data), then the new data would come into the page cache. At least, this is what a simple cache using the LRU eviction policy would do.

It turns out LRU has two shortcomings when used as a page replacement algorithm. First, an exact LRU implementation is too costly in this context. Second, the memory manager needs to account for frequency as well, so that a large file read doesn't evict the entire cache. Therefore, Linux uses a more sophisticated algorithm than LRU; and that algorithm doesn't play along well with the w

[continue reading]

Last week, Eric Hanson showed off Aquameta’s “Spacebase”. Neat stuff! I’m still trying to wrap my head around some of it.

Here are Eric’s slides and the pg_meta git repo. Watch for Aquameta’s release on pg-announce.

No meeting in December, as usual. Go to the Seventh Winter Coders’ Social instead.

We’ll be back in January. An exciting lineup for the first few months of next year is taking shape.

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

What: Talk about PostgreSQL, foreign data wrappers and a DFW PGDay next year

When: Wednesday, December 4, 2013 7:00 PM

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

DFW PUG on Meetup

This demo shows how to run an example in BigSQL that uses Hive, Hadoop, PostgreSQL, and the Hadoop Foreign Data Wrapper to leverage the power of Hadoop from within PostgreSQL. This tutorial uses BigSQL 9.3 Release 2 – Beta2, which includes Hadoop-2.2 and Hive-0.12. You can download the newest release, or the Quick start VM for VMware, at BigSQL.org.

In fewer than four minutes this tutorial will show you how to:

• Create a Hive tables
• Load a CSV file into a Hive table
• Set up the Hadoop Foreign Data Wrapper in PostgreSQL
• Access data in Hadoop from PostgreSQL

Skills Required to Write Basic Reports

1. A good, solid understanding of SQL and PL/PGSQL in a PostgreSQL environment.  This is the single most important skill and it is where most of the reporting effort lies.
2. Basic understanding of Perl syntax.  Any basic tutorial will do.
3. A basic understanding of Moose.  A general understanding of the documentation is sufficient, along with existing examples.
4. A very basic understanding of the LedgerSMB reporting framework as discussed in this series.

Skills Required to Write More Advanced Reports

1. An in-depth understanding of our HTML elements abstraction system (this will be discussed in a future post here)
2. A general proficiency with Template Toolkit, possibly including the LaTeX filter for running portions of the template through a LaTeX filter.

Strengths

Weaknesses

[continue reading]

As the creator of the initial major release notes, I am aware of the many changes that go into every release. What I often cannot see is the pattern of feature growth that the Postgres community accomplishes on a yearly basis.

This keynote presentation from PGConf EU contained a graphic that gave me a new perspective. The presentation by EnterpriseDB's Keith Alsheimer includes valuable research and quotes, but slide 18 is the image that caught my eye. It takes Postgres features and groups them into three areas:

• Easy to use / deploy
• High-end enterprise requirements
• New workloads / platforms

With examples under each section, the diagram clearly shows Postgres development targeting three different segments. With this visual, it is easier for me to see which segment a new feature targets, and when a feature might benefit one segment while harming another.

Continue Reading »

This time I will describe two things: installing a new extension using pgxn and using Javascript for writing a PostgreSQL stored procedure.

The last time I was describing a couple of nice features of the incoming PostgreSQL 9.3, I wrote about merging JSONs in Postgres using a stored procedure written in Python. In one of the comments there was a suggestion that I should try using Javascript for that, as JSON is much more native there.

So let's try Javascript with PostgreSQL.

Installing PL/V8

PL/V8 is a PostgreSQL procedural language powered by V8 Javascript Engine. This way we can have Javascript backed, something funny which could be used to create something like NoSQL database, with Javascript procedures and storing JSON.

To have this procedural language, you need to install it as a separate extension. This can be done with system packages, if your system provides them. For Ubuntu, which I use, there are packages ready, however I use PostgreSQL compiled from source, and I keep it in my local directory, so I had to install it in a little bit different way.

I keep my PostgreSQL in ~/postgres directory. The ~/postgres/bin directory is added to environmnent $PATH variable. It is important, as the further steps will use pg_config program, which prints lots of information about the PostgreSQL installation.

The code for PL/V8 can be found in the project's PGXN page. You can of course download source, and install it. However there is much simpler way to do it. PGXN provides a tool for managing extensions stored there.

To get the client for pgxn, it's enough to write:

$ pip install pgxnclient

To install PL/V8 you also need to have the developer library for the V8 engine:

$ sudo apt-get install libv8-dev
$ pgxn install plv8

Now you can install the extension with a simple command:

$ pgxn install plv8

This should download, compile and copy all the files into a proper directory described by the pg_config program.

Use PL/V8

For each database where you want to use this extension, you need to create it:

plv8=# CREATE E

[continue reading]

November 2013 is a great month for Datatrans Informatika and Systran Electronics Zone. “Smartcard for Frequent Traveler” (SFFT), a PostgreSQL based project presented by the two companies (located at Batam, Indonesia) as a team has won Gold Award in ASEAN ICT Award 2013 under Public Sector Category. (http://www.aseanictaward.com/index.php/about-aicta/aicta-2013-finalist). The ceremony was held on beautiful evening, 14th November 2013, at Gardens by The Bay, Marina Bay, Singapore.

SFFT is an ICT project to help frequent traveler entering and exiting Indonesian territory more effiecienty via 8 international seaports spreaded in Riau Islands province. Instead of wasting time in long queue for showing passport pages to officer at immigration check point, traveler can simply insert smartcard into reader at automatic verification gate.

SFFT relies on PostgreSQL database server installed at the 8 remote nodes at 3 different islands. The nodes are inter-replicated cascadely by means of Slony. Its asynchronousity helps replication works over unreliable network connection.

SFFT is an ajax-based web application. Hardwares were driven through efficient Mozilla’s Js-ctypes. The technology enables communication back and forth between javascript codes and dynamic linked library written in C. At the backend, SFFT has Java Servlets deployed on Tomcat web server. Data exchanges with PostgreSQL database server is run through JDBC. PgPool was there to reduce connection overhead. Extension codes based on gSOAP library has been developed for data exchanges between PostgreSQL and immigration SOAP/XML web service. Overall system design is as follows:

I would like to thank PostgreSQL. It was really beautiful evening at the heart ot south east asia.

It seems I blog a lot more about conferences than about technology these days, and that sometimes makes me a bit sad. Not because I blog too much about the conferences, but that I don't blog enough about tech stuff.. But regardless of that, it's time to post our feedback from this years conference.

As we've collected the same statistics as previous years, it's easy to compare with previous years. And it turns out that a fair amount of the statistics are very similar.

It appears we are doing a decent job of keeping up satisfaction, with almost exactly the same score as last year for overall impression and program.

There is a small drop in the top score, but it's well within the margin of error of the sample. But of course, it just means that we have to keep working hard to keep the score high for next year as well, which we're very happy to see!

A recurring questions is: ‘how can I copy a couple of rows from one database to another’? People try to set up some replication, or dump entire database, however the solution is pretty simple.

Example

For this blog post I will create two similar tables, I will be copying data from one to another. They are in the same database, but in fact that doesn’t matter, you can use this example to copy to another database as well. Even on another server, that’s enough to change arguments for the psql commands.

The tables are:

test=# CREATE TABLE original_table (i INTEGER, t TEXT);
CREATE TABLE
test=# CREATE TABLE copy_table (i INTEGER, t TEXT);
CREATE TABLE

Now I will insert two rows, which I will copy later to the “copy_table”.

test=# INSERT INTO original_table(i, t) VALUES
  (1, 'Lorem ipsum dolor sit amet'),
  (2, 'consectetur adipiscing elit');
INSERT 0 2

test=# SELECT * FROM original_table ;
 i |              t              
---+-----------------------------
 1 | Lorem ipsum dolor sit amet
 2 | consectetur adipiscing elit
(2 rows)

test=# SELECT * FROM copy_table;
 i | t 
---+---
(0 rows)

The Solution

Of course I can set up replication, which is too much effort for ad hoc copying two rows. Of course I could dump entire database, but imagine a database with millions of rows, and you just want to copy those two rows.

Fortunately there is “copy” command. However simple copy saves file on the same server as PostgreSQL is running, and usually you don’t have access to the file system there. There is another command, that’s internal command for psql, it is named “\COPY”. It behaves exactly like copy, but it writes files on the machine you run psql at.

Save To File

The first and simplest solution we could save those two rows into a file, and load it later on another database.

First, let’s find out how “\COPY” works:

$ psql test -c \
"\COPY (SELECT i, t FROM original_table ORDER BY i) TO STDOUT"

1 Lorem ipsum dolor sit amet
2 consectetur adipiscing elit

As you can see, the main part of this command is the select query w

[continue reading]

Overall Structure and Preamble

[continue reading]

\pset linestyle unicode-double5 style
┌───────────┬──────────┬──────────┬────────────┬────────────┬───────────────────────┐
│   Name    │  Owner   │ Encoding │  Collate   │   Ctype    │   Access privileges   │
┝━━━━━━━━━━━┿━━━━━━━━━━┿━━━━━━━━━━┿━━━━━━━━━━━━┿━━━━━━━━━━━━┿━━━━━━━━━━━━━━━━━━━━━━━┥
│ postgres  │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │                       │
│ template0 │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │ =c/postgres           │
│           │          │          │            │            │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │ =c/postgres           │
│           │          │          │            │            │ postgres=CTc/postgres │
└───────────┴──────────┴──────────┴────────────┴────────────┴───────────────────────┘

\pset linestyle unicode-double6 style
┌───────────┬──────────┬──────────┬────────────┬────────────┬───────────────────────┐
│   Name    │  Owner   │ Encoding │  Collate   │   Ctype    │   Access privileges   │
╞═══════════╪══════════╪══════════╪════════════╪════════════╪═══════════════════════╡
│ postgres  │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │                       │
│ template0 │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │ =c/postgres           │
│           │          │          │            │            │ postgres=CTc/postgres │
│ template1 │ postgres │ UTF8     │ en_US.utf8 │ en_US.utf8 │ =c/postgres           │
│           │          │          │            │            │ postgres=CTc/postgres │
└───────────┴──────────┴──────────┴────────────┴────────────┴───────────────────────┘

Originally released in 2006, pgpool-II is the Swiss Army Knife of Postgres middleware tools, allowing connection pooling, replication, load balancing, and parallel query. With the ascendency of PgBouncer and streaming replication, its role has changed, but its position as an intermediary between database clients and servers has allowed it to retain a unique and valuable toolset. For example, pgpool's ability to detect and redirect read-only queries is used in many read-balancing solutions, including EnterpriseDB's cloud database offering. It is also uniquely positioned to handle server fail-over by redirecting client requests.

Unfortunately, with all these features, pgpool's complexity has hampered its adoption. Fortunately, a new focus on reliability has greatly increased its usefulness. Six months ago, EnterpriseDB employee and now pgpool committer Muhammad Usama started working on pgpool stability by restructuring the code and importing Postgres's memory manager into pgpool. Lead pgpool developer Tatsuo Ishii is now using the Coverity software scanner to find and fix pgpool bugs. Even VMWare's Heikki Linnakangas has joined to help. Pgpool developers are even considering removing parallel query to simplify the code and help focus development efforts.

These are exciting times for pgpool. The number of fixes in November alone is massive, and a new release with these fixes is already being planned. As middleware, pgpool is in a unique position to provide powerful database features, and it is great to see it growing in that role.

Continue Reading »

This is a continuation of a series of posts about how I use Postgres everyday.

One of the most pleasant aspects of working with Postgres is coming across features that save me lots of typing. Whenever I see repetitive SQL queries, I now tend to assume there is a feature available that will help me out.

One such feature is INSERT using a SELECT, and beyond that, using the output of a SELECT statement in place of VALUES.

Take for example:

INSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'selena'), (select id from bar where type = 'name'));    
INSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'funny'), (select id from bar where type = 'name'));
INSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'chip'), (select id from bar where type = 'name'));

I think a lot of people know that this is possible. There are a few problems with it – like if the result of the WHERE clause isn’t unique in both cases, you’d get an error. In this case, id in both tables were surrogate keys, with both name and type being unique.

What some people don’t realize is that you can SELECT, and then directly insert that into a table:

INSERT into foo_bar (foo_id, bar_id) ( 
  SELECT foo.id, bar.id FROM foo CROSS JOIN bar 
    WHERE type = 'name' AND name IN ('selena', 'funny', 'chip') 
);

If the values you wanted to take from the table bar were not all the same, the query would be considerably more complex. Given that I only am interested in a single value from bar, and I want it joined with a series of explicitly selected values from foo, this version of the query saves me a lot of typing.

I may have referenced this feature a few times when breaking down functions used for reports in Socorro. It’s super convenient and saves quite a bit of typing! You can put any valid SQL query in there, including CTEs. The documentation for INSERT provides a few more examples.