Extensions in PostgreSQL 9.1 fixes another pet-peeve

One thing I've really disliked is the fact that contrib modules had installation scripts that enforced the schema to public for the installation. In my opinion, for no useful reason at all.

For example, I often install the pgcrypto contrib module. And I install this in the pgcrypto schema, that I then either add to the search_path variable or just explicitly use in my queries, with things like pcrypto.crypt('foobar','barfoo'). For versions prior to 9.1, being able to do this required me to manually edit the installed pgcrypto.sql file, to remove the SET search_path = public; command.

Extensions in 9.1 makes this so much nicer. To get pgcrypto into it's own schema, I now just need to do:

postgres=# CREATE SCHEMA pgcrypto;
CREATE SCHEMA
postgres=# CREATE EXTENSION pgcrypto SCHEMA pgcrypto;
CREATE EXTENSION

If I happen to create it in public by mistake, I can even move it after the fact!

postgres=# ALTER EXTENSION pgcrypto SET SCHEMA pgcrypto;
ALTER EXTENSION

You still need to create the schema manually - in theory we could auto-create that, but the work is still a lot easier than before. And fully supported!


Add comment

New comments can no longer be posted on this entry.

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

PGDay/MED 2023
Apr 13, 2023
St Julian's, Malta
PGDay Chicago 2023
Apr 20, 2023
Chicago, USA
PGCon 2023
May 30-Jun 2, 2023
Ottawa, Canada
PGConf.EU 2023
Dec 12-15, 2023
Prague, Czechia

Past

pgday.Paris 2023
Mar 23, 2023
Paris, France
Nordic PGDay 2023
Mar 21, 2023
Stockholm, Sweden
SCaLE 2023
Mar 9-12, 2023
Pasadena, CA, USA
FOSDEM 2023
Feb 2-5, 2023
Brussels, Belgium
PGConf.EU 2022
Oct 25-28, 2022
Berlin, Germany
More past conferences