How to get a table's OID

I often get questions from customers trying to work with OIDs of relations they need to add somewhere (three times just this week). For example, someone wants to add a special autovacuum configuration for a specific table. He then needs to find out the OID of the relation. People often tend to use the following query then:

SELECT oid FROM pg_class WHERE relname = 'foo' AND relkind = 'r';
oid
-------
16385
(1 row)

This returns the current OID of the table and its relation name. The OID can then be used for everything you need it to. However, there's an much easier way to get that information: just use a cast of the relation name to regclass:

SELECT 'foo'::regclass::oid;
oid
-------
16385
(1 row)

Or, to get back to the pg_autovacuum example above:

INSERT INTO pg_autovacuum
VALUES('foo'::regclass, 't', -1, 0.05, -1, -1, -1, -1, -1, -1);

If you are going to assign regclass to an OID type, you don't need the extra cast to OID, since there's an existing implicit cast to OID. There are other reg* casts available as well, for examaple:

SELECT 'now'::regproc::oid;
oid
------
1299
(1 row)

SELECT 'int4'::regtype::oid;
oid
-----
23

Debian devscripts and Subversion

I often have to work with Subversion, Tagging and other things and a collegue of me (a Debian Developer) pointed me to a tool called svnpath, which eases working with svn and URLs. I have so many repositories that i often forget their spelling or tagging convention. svnpath is contained in the famous devscripts Debian package with various other tools, so it's worth a look.

For example, if you want to know your current tags, you can use

# svn ls $(svnpath tags)

if you're current working directory is trunk/. To create a new tag, you're going to do the following:

# svn cp $(svnpath) $(svnpath tags)/release-1.1

Very nice ;)

Froscon 2008

The program schedule for Froscon 2008 is online. Again, there will be plenty of very interesting talks and i think it's going to be a very nice community event. So if you want to attend a friendly, very nice event, we should met at Froscon 2008. Regarding PostgreSQL, i'll have a talk about "PostgreSQL Troubleshooting", and there will be a PostgreSQL Workshop teached by Susanne Ebrecht.

Building PostgreSQL on IBM pSeries/SLES 10

It turns out that compiling PostgreSQL on IBM's pSeries isn't that straightforward. Compiling PostgreSQL is very easy, just do a configure; make install and your job is done. However, Red Hat and SuSE on IBM's pSeries are multiarch platforms with 64-bit kernels and 32-bit userspace, capable of running and building 64-bit applications. To build PostgreSQL you have to specify some extra compiler flags. On SLES/ppc64 we had success with the following settings, passed to make:

make CC="gcc -m64" LDREL="-r -melf64ppc"

PostgreSQL development with git

After getting an account from Peter Eisentraut i've just started to publish my current development efforts regarding PostgreSQL on git.postgresql.org. I found this a very neat idea, since developers are able to track development from other people in an easy way, without sending huge patches around. Also big patches are hard to read and if you are able to track development live, reviewing a new functionality gets a lot easier. I've never used git before, so let's see how it works.