Monday, July 11, 2011

ERROR: must be owner of language plpgsql

Update: I created a gem to make PostgreSQL a bit easier to use with Rails.

In my opinion, this error message constitutes definitive proof that PostgreSQL hates America, murders babies, supports terrorism, and worships Satan. I have paid leather-clad dominatrices in London thousands of British pounds to whip me and humiliate me, and not one of them ever came close to the anguish inflicted by this horseshit error message and the documentation which "supports" it.

(If you are a lawyer for PostgreSQL, please be advised that this is only my opinion. If you are a police officer on a vice squad, particularly in the United Kingdom, please be advised that I may be exaggerating. If you are a developer for PostgreSQL, please be advised that the Pope Himself has assured me you will burn in Hell for all eternity for writing this error message, and that Satan Himself has personally apologized to me that Hell Itself does not hold torments sufficient to punish you for what you've done.)



Fortunately, the solution is easy:

↪ psql template1
template1=# alter role my_user_name with superuser;


You may wonder what the hell template1 is. Fear not! It represents systemwide settings, so naming it after view-formatting variables could not have been more logical. Absolutely could not. No way on earth could any more logical name have been found, anywhere in all the lexicons of every human language.

To be fair, "template" can carry broader meanings, but even then, naming a template "template" is like naming a variable "variable." Anybody who does that to their users does not have their users' joy at the forefront of their list of priorities.

Anyway. Yes. A perfect fix. And by perfect I mean extremely imperfect. Switching your user to superuser means you don't have to spend three hours on it just to use your dev box, but if you deployed this way, you'd get hacked in seconds. In seriousness, PostgreSQL rocks, but it's not without its limitations. Based on the excellent PeepCode on Postgres, I hacked together (copy/pasted, really) a very, very simple implementation of full-text search.

  def self.search_description(query)
conditions = <<-EOS
to_tsvector('english', description) @@ plainto_tsquery('english', ?)
EOS
where(conditions, query)
end


The repetition of "english" frustrates me as a DRY-crazed Rails dev, but what's more worrisome here is how Postgres handles stemming. Stemming uses basic natural language processing to recognize (for example) that the terms "stem," "stemming," and "stemmed" are all related. It's very useful for "fuzzy" searching, but useless for exact matches, so sometimes you want to turn it off.

Here's what the code looks like with stemming on:

to_tsvector("english"...
to_tsvector("default"...


Of course, if you use "default," PostgreSQL uses an English dictionary, because English is the default for "default." And "english." If somebody who worked for me wrote an API like that, I wouldn't just fire them, I'd probably throw them out of a window and set them on fire. (Hopefully in the opposite order, but not necessarily.) But it gets even worse. Here's what it looks like with stemming off:

to_tsvector("simple"...

So the opposite of "simple" is "default." Here I have a fundamental philosophical disagreement with the Postgres devs, because I believe defaults should be simple, yet I have to applaud their honesty, because, to be fair, in the context of Postgres, it is indeed pretty logical to define "simple" as the opposite of "default." Points for consistency at least.