In the last PostgreSQL version (8.4) there is a small change for the problem with collation. There can be set collation per database, not per cluster, so using only one PostgreSQL instance it is possible to have many databases with different language settings.
Last time I keep on playing Ruby on Rails, quite nice tool but migrations are something a little bit… well… Houston, we’ve got a problem. Databases in Rails are treated as a simple stupid bag full of data put here and there.
Someone asked me recently how to list all tables that a given user can select from. The problem they had was that they had a special user used by a client, so the client can make its own sql queries. However due to the security policy, that user can only perform SELECT queries, and only from some tables.
They wanted to give the client a list of tables which can be selected.
FOUND is a global variable that exists in the plpgsql procedural language used in PostgreSQL database. Last time I was writing some complicated procedures for moving many records to archive schema, just for having only the fresh data the main schema so it would be faster. During that I notices a very strange error that suddenly turned out just to be a stupid mistake.
The manual says that:
A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.
So I used it… that way, but not with a simple query, I used `execute` for some custom string created by some parameters concatenation, nevermind. Here is a very simple test sql that shows the problem:
Yea, simple question. Usually I hear something like this: “Help me, my database is so slow because it is so big…. about 1,000 records’”. That usually makes me laugh. Adding some indexes and convincing programmers not to get all records with every select usually helps and suddenly the database is not so big.
Yesterday the newest PostgreSQL 8.4 has been released. Great, but I still keep on waiting for the 8.5 release that is going to have some quite nice replication built in.