Monday, July 11, 2011

ERROR: must be owner of language plpgsql

Anguish so severe it requires puppies.



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.