Recently, I was digging in the possibilities of using PostgreSQL’s set_config() and current_setting() functions when I stumbled upon a Stack Overflow question detailing pretty much the research I was doing for myself. My answer was rather elaborate, which is why I’m linking it here to find it back with more ease than through the SO interface.
Tag: PostgreSQL
Ytec, where I work since July 31 2015, is an IT company specializing in customer-specific custom software (“klantspecifieke maatwerk software“). Somewhere in late 2015, YTEC deployed a fancy new website based on React, which, to my surprise, is actually indexed somewhat decently by Google. Still, I want to be able to find my own posts on the YTEC blog when I filter by “site:blog.bigsmoke.us”, which is why I’m listing my posts there here:
- I contributed on a post about developing apps with React Native. Later, I might post some more about the pros and cons of using regular React in combination with Django.
- I wrote a post about the advantages of snapshot isolation, which was prompted by my being annoyed with all the problems caused by the Exact Globe ERP not supporting snapshot isolation for MS SQL Server.
These are some random new PostgreSQL features that I’m interested in:
-
The EXECUTE USING feature in PostgreSQL 8.4 will allow the use of more secure dynamic SQL in PL/PgSQL.
-
User defined exceptions in PostgreSQL 8.4 are a very exciting feature to me. In our unit tests we often check if an exception is properly raised. However, for exception that we raise ourselves, we’ve never been able to check which exception is raised.
Another exciting possibility is that this will allow us to come up with a very clean validation scheme that produces exceptions that we’ll be able to use in the client web GUI.
-
WITH queries in PostgreSQL 8.4 will allow some very cool things, the coolest of which is something that I’ve wished for on more than one occasion: recursion.
-
The introduction of proper support for the SQL standard XML Type makes me wish that my favorite web hosting provider would support a real database besides their standard MySQL offering.
-
Enumerated Types will make some of my table definitions slightly clearer.
-- This isn't too shabby (thanks to the CHECK constraint): CREATE TABLE persons ( the_gender CHAR(1) CHECK (gender = 'm' OR gender = 'f') ); -- But, I like this much better: CREATE TYPE gender AS ENUM ('male', 'female'); CREATE TABLE persons ( the_gender gender );
Extra kudos to the PostgreSQL development team for their accelerating pace! 😀
I just need to add a quick summary of what postgres back-end tool our Ruby on Rails application uses, and how we’ve configured it, because it’s quite confusing…
There are four postgresql backends:
- ruby-postgres. This version is no longer maintained. This is the version we used when the project began.
- postgres-pr. This is a pure ruby implementation, which is not interesting for us.
- postgres. This is the continuation of the unmaintained ruby-postgres. This version includes such fixes as that it can be compiled against libpg-8.3.
- ruby-pg. It is said that this one is now the official postgres back-end, but when I install it, the application still can’t find “postgres”.
Because the aforementioned article states that the pg extension is unstable, “postgres” seems to be what we should use. The article states that it is included in the ruby-pg package, but it doesn’t work when I install it, so I had to install “postgres”. I uninstalled ruby-pg, because it doesn’t seem necessary.
To continue, we once used a patched postgresql adapter, because we needed more accurate timestamps (the standard connection adapter rounded everything off to whole seconds), but if I recall correctly, this patch was only necessary on the connection adapter in Rails, not the back-end. We never commissioned the functionality that required this, so this existed only in the workdir of one of the devs.
As a final note; on our production server, we have a version of ruby-postgres installed in /usr/local. I can’t remember why…
A while ago, I wrote a PostgreSQL auth plugin for Rails. The plugin basically defers all authentication and authorization worries to the database layer where they are supposed to be taken care of anyway.
Using this plugin, the user is asked for his or her credentials using a HTTP Basic authentication challenge. (The code for this is adapted from Coda Hale‘s Basic HTTP authentication plugin.) It’s possible to specify a guest_username in the database.yml which will be used as a fall-back if no credentials are supplied. After successful login or if a guest user is found, the plugin will make sure that all database operations run as that user. If any operation fails due to insufficient user rights, the user will be prompted for a username/password pair again.
Detailed and up-to-date documentation for the plugin can always be found at the plugin’s homepage. Go to the plugin’s project page for getting help or for reporting issues with the plugin.
This afternoon, I had to add a unique index for some table in the Sicirec PostgreSQL database. I had already assumed that I needed to use CREATE UNIQUE INDEX to create my new unique index until I noticed, thanks to pgAdmin‘s clear GUI, that some tables clearly had unique constraints and unique indexes while one table lacked a unique constraint and only had an unique index defined.
This motivated me to take a closer look at PostgreSQL’s documentation on unique indexes:
Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there’s no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.
Grepping for CREATE UNIQUE INDEX
in our migration history quickly revealed that the index which lacked an accompanying constraint was indeed created by CREATE UNIQUE INDEX instead of by ALTER TABLE ... ADD CONSTRAINT. So, now I know that, indeed, I have to use ALTER TABLE ... ADD CONSTRAINT instead of CREATE UNIQUE INDEX to add unique constraints with accompanying indexes to existing tables.
Recent Comments