On Thursday, 20th of November, Peter Eisentraut committed his own patch, which adds new command to PostgreSQL: TABLE.
While this command doesn't do anything that wasn't available earlier, it's worth mentioning, as it's one of patches that make PostgreSQL compatible with SQL:2008.
Basically new command “TABLE" acts like an alias to ‘SELECT * FROM':
On 19th of November Tom Lane committed patch by Takahiro Itagaki which does:
Add auto-explain contrib module for automatic logging of the plans of slow-running queries.
Finally, we got very important addons to PostgreSQL, which help with dealing with arrays.
It solves a lot of problems, which were usually solved with standard cookbook code, which was in faqs, blog posts and number of examples on irc.
Continue reading Waiting for 8.4 – array aggregate and array unpacker
On 3rd of November Andrew Dunstan committed his patch which adds new function to PostgreSQL – suppress_redundant_updates_trigger().
This function is not for using in selects, but it can help you tremendously if your database access matches certain pattern.
Continue reading Waiting for 8.4 – suppress_redundant_updates_trigger
In PostgreSQL 8.2, we got “RETURNING" clause in INSERT/UPDATE/DELETE queries.
Unfortunately it could not be used as source of rows for anything in sql.
INSERT INTO table_backup DELETE FROM TABLE WHERE ... returning *;
Well, it's still not possible, but it is a one step closer, thanks to patch written and committed by Tom Lane on 31st of October:
Allow SQL-LANGUAGE functions TO RETURN the output OF an INSERT/UPDATE/DELETE RETURNING clause, NOT just a SELECT AS formerly. A side effect OF this patch IS that WHEN a set-returning SQL FUNCTION IS used IN a FROM clause, performance IS improved because the output IS collected INTO a tuplestore WITHIN the FUNCTION, rather than USING the less efficient value-per-CALL mechanism.
On 28th of October Tom Lane committed his patch that changes some internals of functions, but it also adds interesting capability.
Continue reading Waiting for 8.4 – pl/* srf functions in selects
Let's imagine following situation:
CREATE TABLE test (id int4 PRIMARY KEY, priority int4); INSERT INTO test (id) SELECT DISTINCT (random() * 100000000)::int4 FROM generate_series(1,1000);
Table test will now contain some (up to 1000) records, with random ids.
Now, we want to update first 3 records (ordered by id) to have following values in priority:
Continue reading Tips N’ Tricks – setting field based on order
Like practically any application, applications that I deal with evolve. They change their requirements when it comes to database storage – new tables, new columns, modified columns. Or perhaps – new base data – like new values in virtually static dictionaries.
For past years we've been working with set of pl/plgsql functions which kept track of “patches", and their dependencies.
The problem with this approach is that it doesn't really scale well, and it generates problems when we use replication (Slony).
The problems mean that we have to apply the patches “by hand" on master/slave server, because downtime in the day is not acceptable, and nobody is willing to do upgrades at 3 am just to be able to add new column.
So, we deal with it. But lately I grew annoyed by this, and started to think about a better way to organize patches.
I “dream" about a system when I would write a patch itself, and the system will make it possible to install and uninstall it with automatic changing database to prior state (this is not simple with things like “update", but it is definitely possible).
System which would “understand" replication, and apply changes to all replicated servers in a way that it will be as safe as possible with no or minimal downtime.
System which would let me track dependencies, and then install them if I'll tell it to install patch, that requires some other patches that were not applied to target database.
Basically – I want something like apt-get/dpkg/rpm for database.
So, writing this seems to be perfectly possible, but is it necessary? Perhaps somebody someplace already wrote such system? Do you know any? Or should I stop whining, sit down and write it myself?
SoftNum asked on irc:
< SoftNum> does postgresql have a config option to automatically trim (both ' ' from blah) on string compares?
So, can you?
Of course there is no such option, but maybe there is a way to tell PostgreSQL to do this trim for given field? Sure there is 🙂
Continue reading Text comparisons that does automatic trim()
Let's assume you have very simple table with users:
# \d users
Table "public.users"
Column | Type | Modifiers
-----------+---------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text | not null
is_active | boolean | not null default true
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_username_key" UNIQUE, btree (username)And you'd like to count all users, and know how many of them are active …
Continue reading Tips N' Tricks – count of all and just some