Solutions Linux is the most important FOSS event in France. The PostgreSQL french-speaking community will have a booth there during the 3 days.

PG Day France is a major French-speaking PostgreSQL community event. This year the conference will be held on Thursday, June 7th in Lyon. A hundred visitors are expected for this day dedicated to PostgreSQL and its associated projects.

You can find more information at: pgday.fr

PGDay NYC 2012 will be held on April 2, 2012 at the Lighthouse International in New York City. PGDay NYC is an intensive one-day PostgreSQL symposium with technical sessions focusing on the core topics you need to succeed with PostgreSQL. It will cover topics for users, developers and contributors to PostgreSQL.

For more information, please see http://pgday.nycpug.org

In-depth course on Replication and Recovery

In-depth course on PostgreSQL administration.

Fifth year of "Prague PostgreSQL Developers Day" conference, organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be held on February 9, 2012 in Prague.

The PostgreSQL project will have a Devroom at FOSDEM 2012, which takes place on February 4-5 in Brussels, Belgium.

On 25th of January, Simon Riggs committed patch: Allow pg_basebackup FROM standby node WITH safety checking. Base backup follows recommended PROCEDURE, plus goes TO great lengths TO ensure that partial page writes are avoided.   Jun Ishizuka AND Fujii Masao, WITH minor modifications In PostgreSQL 9.1 we got pg_basebackup – it is a tool to [...]

As part of a larger project I needed to generate real time radius records from the CDR accounting files of several cluster pairs of Broadsoft application servers. So I wrote a perl script to do just that. It maps the CDR fields to radius attribs and encodes the accounting packet using the Net::Radius::Packet CPAN module.  In my case I’m using the Radiator radius server  from OSC Software on the other end with lots of custom ‘hook code’ to clean up and store the call data coming off our network into a Postgresql database.  This is my first time doing any development with radius.. but I’ve been running this script on several servers for a few weeks now and it appears to be quite stable.

• Get it on the Project Page

Having shown memory allocation (and deallocation) in my previous blog post, I would like to show work_mem in action.

First, I ran the following database session using a ~6k RPM drive:

Continue Reading »

In the z/OS environment, tuning a DB2 subsystem may require the DBA and systems programmers to wear multiple hats and approach overall performance tuning from several different angles. What approaches are best in what situations?Mas:

http://www.databasejournal.com/features/db2/db2-zos-systems-tuning-tactics.html

No primeiro artigo desta série, falei sobre a criação de estatística (automática, explícita e implícita). Agora, vou discorrer sobre o processo de atualização (manual e automática). Apresento detalhes de quando a atualização automática ocorre, mencionando, por exemplo, o custo da recompilação, dentre outras coisas.Mas:

http://imasters.com.br/artigo/23463/sql-server/desvendando-estatisticas-do-sql-server-parte-02

On 25th of January, Alvaro Herrera committed patch: ADD pg_trigger_depth() FUNCTION   This reports the depth level OF triggers currently IN execution, OR zero IF NOT called FROM inside a TRIGGER.   No catversion bump IN this patch, but you have TO initdb IF you want access TO the NEW FUNCTION.   Author: Kevin Grittner [...]

Why aren't more people/companies using PostgreSQL? Why aren't they moving from proprietary databases to PostgreSQL? 
Possible answers: I haven't heard of PostgreSQL. I don't think PostgreSQL will meet my needs. I don't know how to get away from my database vendor.
I'd like to hear some of the reasons for not considering PostgreSQL when creating or migrating a database.
 

In my previous blog entry, I analyzed how various tools (ps and smem) report memory usage. In summary:

• ps columns TRS, DRS, and VSZ report virtual address space allocated, not actual RAM allocated.
• smem's USS reports a process's private (unshared) memory allocated.
• smem's PSS is a sum of process's private memory allocated and a proportional amount of shared memory (both System V shared memory, like Postgres's shared_buffers, and shared libraries).
• RSS shows actual RAM allocated, private and shared.

With these issues understood, let's look at a running Postgres cluster:

Continue Reading »

On 23th of January, Simon Riggs committed patch: ALTER <thing> [IF EXISTS] ... allows silent DDL IF required, e.g. ALTER FOREIGN TABLE IF EXISTS foo RENAME TO bar   Pavel Stehule This adds important capability – change object of it exists, and not raise exception if it doesn’t. Conditional DDL was always big point on [...]

Not so long ago I used Common Table Expressions for Fibonacci Numbers calculation.

Today I had a conversation with one client about SQL in general and about PosgreSQL dialect in particular. We talked about SQL’s Turing completeness also. Well my opponent is sure that SQL (Postgres dialect either) is not Turing Complete. But I know for sure that if SQL supports CTE it is Turing Complete. Well, I’m sure about it because some time ago at Oscon 2009 David Fetter said so. And my confidence in this man is boundless.

Anyway, my client proposed to implement Factorial calculation on a pure SQL. I choose Postgres dialect. He agreed.

That was his first mistake! He didn’t know that Postgres has built in “!” and “!!” operators for this purpose.

But to be more convincing, I have wrote this code:

WITH RECURSIVE fact(i, f) AS ( VALUES (2, 1) UNION ALL SELECT i + 1, i * f FROM fact ) SELECT f FROM fact LIMIT 10;
Filed under: Coding, PostgreSQL Tagged: CTE, factorial, oscon, PostgreSQL, SQL, trick

Among the long list of new features of PostgreSQL 9.1 the new SQL/MED implementation is probably of the most underrated. SQL/MED is an extension of the SQL standard defines foreign data wrappers (FDW) that allow you to reach data located outside of your database, with regular SQL request (MED stands for Management of External Data). In a nutshell, SQL/MED is cool.

As far as i know, PostgreSQL and DB2 are the only major RDBMS providing an implementation of this standard. The beauty with PostgreSQL is that you can write your own data wrappers to connect your database to any storage you want ; other RDBMS, NoSQL storages, web services, whatever system that holds data and can deliver it.

Basically to Write a Foreign Data Wrapper you need to code six callback routines in C to explain how to plan and execute queries toward your new source of data.

PostgreSQL 9.1 has been released a few month ago, but there's already a bunch of FDW available (even though most of them are still beta). At the time, I'm writing this we already have http://wiki.postgresql.org/wiki/For..., so you can connect your PostgreSQL server to ; Oracle, MySQL, SQLlite, anything providing an ODBC driver, LDAP, couchdb, redis, Amazon S3 storage, CSV files, Twitter, Google Search, HTML pages, RSS feeds, ....

For an almost complete list, check out the PostgreSQL wiki ; http://wiki.postgresql.org/wiki/For...

And of course these wrappers are packaged as extension and most of them are available on PGXN and quite easy to install : http://pgxn.org/tag/fdw/

But there's more ! Among these FDW there's one called Multicorn. This particular extension that allow you to write FDW in Python. A wrapper over another wrapper. Basically this means you don't have to know C to write your own FDW and you can use high-level libraries from the Python world.

Ok let's take a simple example. I'd like to query data contained in a web calendar like Google Calendar. Those data are available in ical files, with a specific format called iCalendar. So want i need to do is explain PostgreSQL how to reach and parse the files.

With Python and Multicorn, this is done with 20 lines of code : see icalfdw.py

And that's it.

Of course this is a basic implementation. We can do better with some optimizations and a few log handling. It's also important to keep in mind that foreign data wrappers have strong limitations... I'll talk about all that and other things in my next blog post.

In the meantime, if you're in Brussels this week-end for FOSDEM you can come to the PostgreSQL Devroom, I'll make a presentation of Multicorn along with its creator.

There are a few parts of the PostgreSQL internals that poke out usefully if you look in the right place for them.  One useful set to know about are the System Columns, which you can explicitly request but don't see by default.  For example:

psql -x -c "SELECT oid,* FROM pg_class LIMIT 1"
There is no column named oid in the pg_class table, but it's there if you ask for it.  The oid used to be relied on more heavily in PostgreSQL as a way to identify rows.  That's not true for regular tables anymore, and you really don't want to start doing that for your own tables.  OIDs are mainly useful now when joining parts of the System Catalog together.  A good example is the Disk Usage query.  If you want to find the namespace a table is in, you need to know you can ask for its OID.  It's possible to get some of this data out of more portable views like information_schema.tables.  But many of the useful things in this area are PostgreSQL specific.  Sometimes I see people starting with the information_schema views and joining against other tables using its text name fields, such as the listed table_name.  That approach has several edge cases that don't work out correctly; not handling TOAST columns is a common example.  That makes them more prone to breaking on you later, probably after your system has gone into production, than an OID based join.

There is also a tableoid system column.  As described in the documentation, its main use case is identifying which partition a row come from.  That's not a great thing to be driving application logic from, but it can be useful for monitoring or troubleshooting purposes.  For example, if you SELECT rows from the parent table in a partitioning inheritance scheme, it's normally expected that no rows will actually be stored there.  Checking the tableoid is one way to confirm that.  You might confirm that your INSERT/UPDATE trigger is moving rows to the right place using tableoid as well.  It's possible to do that for each individual partition section, but running a query against the parent will make sure you hit every row in the table.

Another internal column related to uniquely identifying rows is the ctid.  The ctid is a direct pointer to the physical block (using PostgreSQL's 8K page size) and position of a row.  ctids are a pair of numbers, and the first row will be (0,1).  While this is the fastest way to find a row more than once in the same transaction block, these numbers are not stable in the long term.  Any UPDATE and some maintenance operations will change them.  One thing you can use these for is finding duplicate data in a table.  Let's say you're trying to add a unique constraint, but one row in the table is duplicated 3 times, which blocks the unique index from being created.  When rows are identical in every column, you can't write any simple SELECT statement to uniquely identify them.  That means deleting all of them but one copy requires some annoying and fragile SQL code, combining DELETE with LIMIT and/or OFFSET--which is always scary.  If you use the ctid instead, the implementation will be PostgreSQL specific, but it will also be faster and cleaner.  See Deleting Duplicate Records in a Table for an example of how that can be done.

The other system columns all relate to transaction visibility:  xmin, cmin, xmax, cmax.  When you delete a row in PostgreSQL, it isn't eliminated from disk immediately.  It's possible that some other query that's executing at the same time will still need to see that row, and the transaction isolation in PostgreSQL worries about such things.  If you ever want to learn how that isolation works, the way the Multiversion Concurrency Control (MVCC) implementation is handled, you can watch parts of it happen.  Just open transactions in two different sessions, UPDATE/DELETE in one of them, and then look at those rows in the other.  You can still see them in the session where they weren't touched, but they'll be marked to expire in the future via their xmax being set.  To really pull that all together, you also need to know about some of the System Information Functions.  txid_current() is the most useful for this sort of learning experience, it provides a reference point for the always increasing system transaction ID.  You can find a more detailed exploration of using these functions and system columns in Bruce's MVCC Unmasked talk.  The "Routine Maintenance" chapter of my book also shows examples how how MVCC works through the perspective of the system columns.

As you perhaps know, explain.depesz.com has anonymization feature. Couple of days ago Filip contacted me and sent a patch that stopped anonymization of typecasts. I thought about the patch, and what it achieves, changed it’s internals, but kept the effect. And today, it got released. Normal plan looks like this. But you might want to [...]