I have completed the 9.0 release notes and you can view them online. There will, of course, be many adjustments to the release notes before 9.0 final.

The following steps produced a nasty failure for me today, as I was preparing to patch something in release 8.1:

git clone git://git.postgresql.org/git/postgresql.git pgsql
cd pgsql/
git checkout -b REL8_1_STABLE -t origin/REL8_1_STABLE
./configure --prefix=/home/andrew/foo/inst.5702 --with-pgport=5702 --enable-depend \
      --enable-debug --enable-cassert --with-perl -with-openssl
make

The failure looks like this:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -I../../../../src/include -D_GNU_SOURCE   -c -o indexvalid.o indexvalid.c -MMD
In file included from ../../../../src/include/executor/execdesc.h:19,
                 from ../../../../src/include/executor/executor.h:17,
                 from ../../../../src/include/executor/execdebug.h:17,
                 from indexvalid.c:19:
../../../../src/include/nodes/execnodes.h:23:29: error: nodes/tidbitmap.h: No such file or directory
In file included from ../../../../src/include/executor/execdesc.h:19,
                 from ../../../../src/include/executor/executor.h:17,
                 from ../../../../src/include/executor/execdebug.h:17,
                 from indexvalid.c:19:
../../../../src/include/nodes/execnodes.h:934: error: expected specifier-qualifier-list before ‘TIDBitmap’
../../../../src/include/nodes/execnodes.h:959: error: expected specifier-qualifier-list before ‘TIDBitmap’
make[4]: *** [indexvalid.o] Error 1

Not a good result. It looks like we still have some way to go before we can be satisfied that the git repository is working as we expect. That also makes it more imperative for me to get the buildfarm work with git and the back branches as soon as possible.

I'm very happy to announce that the Postgres project has been selected to participate in this years Google Summer of Code program. Over the next couple weeks we'll be looking to solidify our mentor base; if you work on Postgres and would be willing to mentor a student, please send me a note so we can get you signed up. If you are a student and you're interested in working on Postgres, now is the time to get your proposal together. Student applications will open up on March 29th, so we'd like to have our mentors in place for review, and hopefully had students discussing with the Postgres devs their proposals as much as needed. If anyone has any questions, feel free to email me, or track me down on irc.

Handy links for Postgres GSoC:

• Our ideas page for GSoC
• 
  
• Our loose attempt at organization
• 
  
• Our Postgres page on the GSoC site
• 
  
• Users Guide to GSoC

Thanks everyone, I'm looking forward to another interesting year with GSoC, and hoping you'll join in.

PostgreSQL's query planner and optimizer figures out how to execute your queries based on a set of table, column and index statistics, mostly kept in the pg_statistic table. Nathan Boley did an excellent write-up on how this works for our main docs. However, one thing which has been a chronic issue for PostgreSQL is setting the sample size for those statistics.

I recently took the time to write down my thoughts on why successfully managing code deployments in an online architecture is so radically different from release management in a traditional software engineering endeavor.

...

Perhaps most challenging is the pace at which competition moves. In the online world, I can have an idea this morning, an implementation this afternoon and every client of my service that shows up tomorrow will see it. In fact, things can and do happen much faster than that. You might think that rapid concept-to-availability push is reckless. You might be right. But, your competition is doing it.

...

Read more.

One database that I am monitoring uses a lot of stored procedures. Some of them are fast, some of them are not so fast. I thought – is there a sensible way to diagnose which part of stored procedure take the most time? I mean – I could just put the logic into application, and then [...]

Some time ago, I thought I needed to be able to programmatically calculate properly aligned networks containing the netmask in dotted-quad (as opposed to CIDR) format (i.e. 192.168.1.0/255.255.255.0). That need is [thankfully] all but a distant memory, but as I...

Someone asked me yesterday how to turn an hstore into XML. It's actually pretty darn easy. Here is how we are doing it in one production system I'm working on:

foo=# select xmlelement(name options, null, 
              xmlagg(xmlelement(name option, xmlattributes(key as optname), value))) 
         from each('a=>1, b=>foo'::hstore);
                                    xmlelement                                     
-----------------------------------------------------------------------------------
 <options<><option optname="a">1</option><option optname="b">foo</option></options>
(1 row)

In this system, the XML data source for a report writer app is generated from a database query using query_to_xml_and_xml_schema(), combined with the report options the user specified as an hstore and transformed as above, and the whole thing is handed off to an XSL stylesheet to produce the report writer input.

This time we're having a database where sequences were used, but not systematically as a default value of a given column. It's mainly an historic bad idea, but you know the usual excuse with bad ideas and bad code: the first 6 months it's experimental, after that it's historic.

Still, here's a query for 8.4 that will allow you to list those sequences you have that are not used as a default value in any of your tables:

WITH seqs AS (
  SELECT n.nspname, relname as seqname
    FROM pg_class c
         JOIN pg_namespace n on n.oid = c.relnamespace
   WHERE relkind = 'S'
),
     attached_seqs AS (
  SELECT n.nspname,
         c.relname as tablename,
         (regexp_matches(pg_get_expr(d.adbin, d.adrelid), '''([^'']+)'''))[1] as seqname
    FROM pg_class c
         JOIN pg_namespace n on n.oid = c.relnamespace
         JOIN pg_attribute a on a.attrelid = c.oid
         JOIN pg_attrdef d on d.adrelid = a.attrelid
                            and d.adnum = a.attnum
                            and a.atthasdef
  WHERE relkind = 'r' and a.attnum > 0
        and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval'
)

 SELECT nspname, seqname, tablename
   FROM seqs s
        LEFT JOIN attached_seqs a USING(nspname, seqname)
  WHERE a.tablename IS NULL;

I hope you don't need the query...

(public service announcement further down!)

I'm starting to get ready for the most mis-named conference so far this year. I mean, seriously. It's called East 2010, and yet it's located approximately 6000 km (that's almost 4000 miles for you Americans) to the west of the prime meridian. That's not even close. Sure, it's to the east of where the West conference is usually held, but really, this reminds me of POSIX timezones...

This will be a somewhat different conference than previous PostgreSQL conferences. It's the first big commercial conference. This has enabled it to change venue from "local university or college where rooms are cheap or free" to a conference hotel in downtown Philadelphia. Whether this is actually good for the talks is yet to be seen, but it's likely going to make some things around the conference easier and more integrated. There will also be a exhibition area - something we have tried for pgday.eu without much interest, but it will hopefully be more successful in this surrounding.

The contents of the conference are also somewhat different, since there is now a clear focus also on "decision makers", something that many PostgreSQL conferences have been lacking in. We may all want it to be true that the decision makers are the people who are actually going to use the product, but we know that's not true. This gives a conference schedule that contains a broader range of talks than we're used to - this can only be good.

Myself, I will be giving an updated version of my security talk, focusing on authentication and SSL. The updates are mainly around the new and changed authentication methods in 9.0, and some minor updates on the SSL part. If you've seen it before it may be an interesting refresher, but you might be better off going to see Greg Smith's benchmarking talk if you haven't...

Now for the second part, which is the public service announcement...

If you're like me, you find the official schedule for East very hard to read and basically useless to get an overview. It's also horrible to use

[continue reading]

Just over a year ago, I chose to use Movable Type for my blogging software because of its support for PostgreSQL.  As of this afternoon, after performing some preliminary investigation into whether or not I should upgrade, I discovered that,...

The upcoming PG East Conference is in my home town, Philadelphia. The conference promises to be a new generation of Postgres conferences, and I want to highlight some of the changes that attendees can expect.

First, as I mentioned before, the hotel venue for this conference is much nicer than typical Postgres conferences. Also, it is located in an area of Philadelphia that is packed with great restaurants and shops.

Continue Reading »

I have started preparing the release notes for Postgres 9.0. I went into great detail last year about how the release notes are created, so I will not bore you with the mind-numbing details this time. I should be ready with the first draft in a week or two.

Like the last years the PostgreSQL User Group Germany was present with a booth at Chemnitz Linux Days 2010, one of the big open source and community events in germany.

We were able to answer the obvious questions (see my CeBIT report) but since this is actually a community event and not so much business orientated, we were confronted with more  technical questions.

In addition to the booth i gave a workshop ("Optimize PostgreSQL") and a talk ("What's new in PostgreSQL 8.5 9.0"), slides here.

I would like to thank Andreas Kretschmer + daughter, Peter Eisentraut and my wife for the help during this two day event.

See you next year.

I'm tired of having to remember to set USE_PGXS=1 when building third-party PostgreSQL extensions like pgTAP. Aren’t you? I therefore submit a modest proposal for avoiding this unfortunate idiom.

Read More »

Hi all,

I'm back from Chemnitz, the german PG User Group was there with a booth, a workshop (performance tuning) and a talk about the upcoming 9.0-features.

The workshop and the talk was given by ads, so i think, he will write a blog-posting too. But i can say there was a LOT of attendees to listen his talk.

From my point of view i can say it was a success, many people comes to us and ask us 'how can we migrate from database XYZ to PostgreSQL?'. Great!

And yes, we had also a LOT of happy pg-users and we had heard much positive stories about PG.

But one disadvantage with PG comes up, particular in a hosting environment: every user can see all databases. I had heard that more than once, maybe we should think about a switch in postgresql.conf to disable this.


Regards.

During the MySQL conference Call for Papers there was some talk of getting one or two Postgres sessions into the mix, as a lot of MySQL users seem to have questions about Postgres these days. Alas, looking through the MySQLcon schedule I don't see any on there. I've also looked through the BOF's and nothing about Postgres to be found there either. So, maybe no one is interested in Postgres after all.

However I held a Postgres BOF at MySQLcon last year and we got a handful of people, and since I am going to be at MySQLcon again this year, I might as well host one again. I think it's too late to schedule one formally, but I can put some info on the schedule sheets once I'm at the conference; if you are interested in learning some more about Postgres, please keep an eye out.

I'm told today is Pi day, in celebration of our friendly transcendental constant and because today is the 14th March, which is written as 3.14 in some locales....

So I thought I'd celebrate with a look at Postgres' mathematical musculature.

postgres=# select pi();
pi
------------------
3.14159265358979

Cool! That matches on all the digits, so we're rocking. So what datatype is this?

postgres=# select pg_typeof(pi());
pg_typeof
------------------
double precision

and as the manual says, we support at least 15 digits for this datatype. Even better. So let's flex those long dormant trigonometry muscles:

postgres=# select sin(90);
sin
-------------------
0.893996663600558

Oh no! Surely sin() of 90 degrees is 1.0? Perhaps the default is radians and I just forgot. Of course, doh! The manual doesn't actually say, which seems like a flaw. Here comes a doc patch. So lets supply radians instead.

postgres=# select sin(radians(90));
sin
-----
1

Phew! That works. Now lets try some advanced stuff. There are 2*pi() radians in a circle, so pi() radians is half way round. So the sin() of that should be 0, and the cos() should be 1.

postgres=# select sin(pi()), cos(pi());
sin | cos
----------------------+-----
1.22464679914735e-16 | -1

Hmmm. That is somewhat strange, but I guess that pi is transcendental so any representation with a fixed number of digits will always be slightly wrong by exactly that number of digits. That makes sense, but I guess I was expecting sin(pi()) to return 0.

Just to put this in perspective, in comparison to the diameter of the earth that is a precision of about one millionth of a millimetre. So that is very accurate for most applications.

Let's quickly check "e", another well known transcendental. We don't provide a function for e as we do for pi, but its easy to calculate.

postgres=# select exp(1);
exp
------------------
2.71828182845905

That looks good. So lets play some games with that also.

postgres=# select ln(exp(1));
ln
----
1

Thank goodness for that. All good. I notice in the do

[continue reading]

The PostgreSQL User Group Germany were given the opportunity to have a booth at CeBIT 2010 and present the project. CeBIT is a very big IT-related exhibition and takes place in Hannover, Germany.

Continue reading "PostgreSQL @ CeBIT 2010"

I hereby declare Saturday, April 3, PostgreSQL Test-Fest day. Get our your computers and get ready to test Version 9.

Bug Fix Releases 8.4.3, 8.3.10, 8.2.16, 8.1.20, 8.0.24 and 7.4.28 will be out soon. Get ready to upgrade!

If you are using PostgreSQL, have you ever loaded an SQL script file or dump file through psql? Well, duh.

-f vs. <

If you are just starting out, you will probably try this:

psql mydb < dump.sql

and that's quite OK.

Once you hit your first error message such as

ERROR:  syntax error at or near "VEIW"

you might figure out that for some reason

psql mydb -f dump.sql

is better, because it produces

psql:dump.sql:56: ERROR:  syntax error at or near "VEIW"

instead, allowing you to actually find the error in your file.

Now I admit that it is almost entirely me who is to blame for this bizarre difference, because at some point in the distant past, the GNU Coding Standards recommended that programs should behave the same independent of whether the standard input or output is a terminal or a file. The current version of said standard actually explicitly creates an exception saying that error messages should be changed to the noninteractive style when the standard input is not from a terminal. So this should probably be fixed.

Note that the -f form above is not portable. It depends on the GNU getopt extension that permits options after nonoption arguments. To be portable, you need to write either

psql -d mydb -f dump.sql

or

psql -f dump.sql mydb

Frankly, I hardly ever do this because I rarely use a non-GNU system, but keep it in mind when writing scripts or documentation intended to be portable.

psqlrc

The next thing you should always do when running psql scripts is using the option -X, which prevents the reading of the .psqlrc file. Because that file could contain anything, and you have no idea how it will interact with your script.

In my mind, this is a design mistake in psql. Unix shells have different startup files for interactive and noninteractive usage, so they don't have this problem.

Quiet or Loud

Anyway, if you are restoring a dump, these commands will produce output that does something like this:

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION
ALTER TABLE
ALTER TABLE
CREA

[continue reading]

I called the our hotel representative today because I was confused about why we had a deadline of 03/11 on the room discount. I was trying to push them to extend the date because we had met our room quota and I was wondering why they were trying to shut down the discount. Apparently, not only have we met our room quota, but the hotel is reaching capacity!

If you have not booked your hotel room for PostgreSQL Conference East 2010, now is definitely the time! If you do not book soon, you will be staying at another hotel (of course, you are still welcome to the conference).

• Hotel Information
• Register

Today is the deadline for the special room rate at the hotel hosting this month's PostgreSQL Conference East 2010.  If you've been procrastinating booking a spot at the conference, as of tomorrow that will start costing you.

My talk is on Database Hardware Benchmarking and is scheduled for late afternoon on the first day, Thursday March 25th.  Those who might have seen this talk before, either live at PGCon 2009 or via the video link available there, might be wondering if I'm going to drag out the same slides and talk again.  Not the case; while the general philosophy of the talk ("trust no one, run your own benchmarks") stays the same, the examples and test mix suggested have been updated to reflect another year worth of hardware advances, PostgreSQL work, and my own research during that time.  The Intel vs. AMD situation in particular has changed quite a bit, requiring a new set of memory benchmarks to really follow what's going on now.

And PostgreSQL 9.0 fixed a major problem that kept it from normally delivering accurate results on Linux, due to a kernel regression that made much worse an already far too common situation:  it's easy for a single pgbench client to become the bottleneck when running it, rather than the database itself.  The review I did for multi-threaded pgbench (which can also be multi-process pgbench on systems that don't support threads) suggested a solid >30% speedup even on systems that didn't have the bad kernel incompatibility on them.  Subsequent testing suggests it can easily take 8 pgbench processes to get full throughput out of even inexpensive modern processors under recent Linux kernels.  I'll go over exactly how that ends up playing out on such systems, and how this new feature makes it possible again to use pgbench as the primary way to measure CPU performance running the database.


Recently I've also made an updated to the git repo for pgbench-tools that adds working support for PostgreSQL 8.4 and basic 9.0 compatibility, and the next update will include support for the mu

[continue reading]

There are several approaches on replication/failover – you might have heard of Slony, Londiste, pgPool and some other tools. WAL Replication is different from all of them in one aspect – it doesn’t let you query slave database (until 9.0, in which you actually can run read only queries on slave. Since you can’t run queries on [...]

This article available in Russian.

People that are carefully watching the novelties in the world of PostgreSQL, not by hearsay familiar with blog of Hubert ‘depesz’ Lubaczewski. A series of his posts «Waiting for X.X» – a real fount of useful information.

He doesn’t forget the upcoming release either. There are already 34 posts from the series “Waiting for 9.0″ on his blog. It would seem there is no chance to keep up with him. But looking through the release notes once again, I discovered a valuable innovation is overlooked. Namely, a new LISTEN/NOTIFY implementation.

Let’s start with dry facts.

Internal implementation change

At the moment (versions 8.x and below) mechanism uses a pg_listener system table as a storage. It contains all the listeners waiting for any notices. If necessary, the table is scanned and updated.

In the new version this will be replaced with an in-memory queue. First, it will give a huge increase in speed. And secondly, this implementation is also more compatible with Hot Standby usage. It should be noted however, that currently there is not yet any facility for HS slaves to receive notifications generated on the master, but implementation is planned for the future.

Payload

Finally, the developers added a second parameter for the NOTIFY command , the so-called «payload». Plans for the introduction of which were before the creation of the earth’s firmament.

This extra parameter represents a simple string literal up to 8000 characters. For everyday needs, I think, it’s enough. In the case of large data is recommended to keep them in the table and send notification with the record identifier.

Highlights summary

• If a NOTIFY is executed within a transaction, a notification is not delivered until the transaction is committed.
• If the listening session receives a notification signal during the transaction, notification itself will be delivered to the client only upon completion of the transaction, regardless of the result of the transaction itself (COMMIT or ROLLBACK).
• If notifications are

[continue reading]

As we continue the countdown to the largest community and user conference in PostgreSQL history I am reminded of all the great content we have had in the past. Today while I was reviewing the curriculum for the PostgreSQL Performance and Maintenance class, I came across this great talk by Bruce Momjian as a further example of the high quality information you will receive not only from the various trainings but also all the other (over 50!) sessions at PostgreSQL Conference East!

I have a client who runs a PITR warm standby for their main production database. A couple of time recently it has fallen over, due to factors beyond the control of Postgres. The first time a data drive ran out of space, and the second time (today) a tablespace was created on the master that referred to a directory that didn't exist on the standby. Fixing this was really easy. Just remove the problem (clear space, create directory) and restart the standby. I'm really quite impressed by how robust the whole thing is. It's the sort of robustness that makes Postgres a joy to work with.

DBI-Link is now on Github!

Here's how I did it:
Continue reading "Free Your DBI-Link, and the Rest Will Follow"

Why are temporal extensions in PostgreSQL important? Quite simply, managing time data is one of the most common requirements, and current general-purpose database systems don’t provide us with the basic tools to do it. Every general-purpose DBMS falls short both in terms of usability and performance when trying to manage temporal data.

What is already done?

• PERIOD data type, which can represent anchored intervals of time; that is, a chunk of time with a definite beginning and a definite end (in contrast to a SQL INTERVAL, which is not anchored to any specific beginning or end time).
  • Critical for usability because it acts as a set of time, so you can easily test for containment and other operations without using awkward constructs like BETWEEN or lots of comparisons (and keeping track of inclusivity/exclusivity of boundary points).
  • Critical for performance because you can index the values for efficient “contains” and “overlaps” queries (among others).

• Temporal Keys (called Exclusion Constraints, and will be available in the next release of PostgreSQL, 9.0), which can enforce the constraint that no two periods of time (usually for a given resource, like a person) overlap. See the documentation (look for the word “EXCLUDE”), and see my previous articles (part 1 and part 2) on the subject.
  • Critical for usability to avoid procedural, error-prone hacks to enforce the constraint with triggers or by splitting time into big chunks.
  • Critical for performance because it performs comparably to a UNIQUE index, unlike the other procedural hacks which are generally too slow to use for most real systems.

What needs to be done?

• Range Types — Aside from PERIOD, which is based on TIMESTAMPTZ, it would also be useful to have very similar types based on, for example, DATE. It doesn’t stop there, so the natural conclusion is to generalize PERIOD into “range types” which could be based on almost any subtype.
• Range Keys, Foreign Range Keys — If Range Types are known to the Postgres engine, that means that we can have syntac

[continue reading]