Let's assume you have some simple database with “articles" – each article can be in many “categories". And now you want to get list of all articles in given set of categories.
Standard approach:
select a.* from articles as a join articles_in_categories as aic on a.id = aic.article_id where aic.category_id in (14,62,70,53,138)
Will return duplicated article data if given article is in more than one from listed categories. How to remove redundant rows?
More than once I've seen situation when there is a table, with serial primary key, and rows contain also some kind of creation timestamp, which is usually monotonic, or close to monotonic.
Example of such case are for example comments or posts in forums – each get it's ID, but they also have creation timestamp. And it usually is so that higher ids were added later than the lower ids.
So, let's assume you have such table, and somebody asks you to make a report on data from last month. How?
Question from title sounds weird to you? It's just a ‘rm backup_filename'? Well. I really wish it was so simple in some cases.
One of the servers I'm looking into, there is interesting situation:
One database that I am monitoring uses a lot of stored procedures. Some of them are fast, some of them are not so fast. I thought – is there a sensible way to diagnose which part of stored procedure take the most time?
I mean – I could just put the logic into application, and then every query would have it's own timing in Pg logs, but this is not practical. And I also believe that using stored procedures/functions is way better than using plain SQL due to a number of reasons.
So, I'm back to question – how to check which part of function takes most of the time?
There are several approaches on replication/failover – you might have heard of Slony, Londiste, pgPool and some other tools.
WAL Replication is different from all of them in one aspect – it doesn't let you query slave database (until 9.0, in which you actually can run read only queries on slave.
Since you can't run queries on slave, what is it good for? Well. It's good, and great in 1 very important aspect – all things that happen in database are replicated. Schema changes. Sequence modifications. Everything.
There is also drawback – you can't (as of now) replicate just one database. You replicate whole cluster (I don't like this word in this context – let's say: whole installation) of PostgreSQL. All databases that reside in given DATA directory.
So, the question is – how to set it up?
Dynamic updates of fields in NEW in PL/pgSQL
Today, on #postgresql on IRC, strk asked about updating fields in NEW record, in plpgsql, but where name of the field is in variable.
After some time, he sent his question to hackers mailing list. And he got prompt reply that it's not possible.
Well, I dare to disagree.
Continue reading Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL
One common problem that a lot of people seem to have is when they encounter error message like this:
# \i test.sql psql:test.sql:1: ERROR: invalid byte sequence for encoding "UTF8": 0xb3
Why it happens? What can be done about it? Let's see.
Please read also about this change in Pg 9.1, and this change in Pg 9.2 posts, as they explain that since Pg 9.1 some of the limitations listed in this post are no longer there.
Fight!
But more seriously – people tend to use various data types, and there have been some myths about them, so let's see how it really boils down.
First of all – All those data types are internally saved using the same C data structure – varlena.
Thanks to this we can be nearly sure that there are no performance differences. Are there no performance differences in reality? Let's test.
Continue reading CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03
First of all – I base information in this post on Linux, so if you are using another operating system – it's quite likely that it will not help you.
Second – if you're using Linux, you have probably some package manager – dpkg, rpm, pacman, poldek, whatever. Right? So, it should be easy to install Pg using this package manager?
Well, yes and no. Yes – of course all (known to me) Linux distributions include PostgreSQL, but I do not use their prepackaged version.
Why, and how do I install?
On 12th of February Tom Lane committed patch by Hitoshi Harada:
Log Message: ----------- Extend the set of frame options supported for window functions. This patch allows the frame to start from CURRENT ROW (in either RANGE or ROWS mode), and it also adds support for ROWS n PRECEDING and ROWS n FOLLOWING start and end points. (RANGE value PRECEDING/FOLLOWING isn't there yet --- the grammar works, but that's all.) Hitoshi Harada, reviewed by Pavel Stehule
Continue reading Waiting for 9.0 – extended frames for window functions