Getting record by 2 criteria, ordered by third – how to do it quickly?

Let's assume we have a simple table:

CREATE TABLE objects (
    id          serial primary key,
    category    INT4        NOT NULL DEFAULT 0,
    object_type INT4        NOT NULL DEFAULT 0,
    entered_on  TIMESTAMPTZ NOT NULL DEFAULT now()
);

(This is simplification, but it contains all necessary columns).

What should I do to be able to quickly get 50 newest objects in given category/object_type (or in many categories/many object_types, or in all categories/object_types), optionally with limiting entered_on “older than …".

Continue reading Getting record by 2 criteria, ordered by third – how to do it quickly?

Waiting for 8.4 – ordered data loading in pg_dump

Great (and admittedly long overdue) patch by Tom Lane:

Make pg_dump --data-only try to order the table dumps so that foreign keys'
referenced tables are dumped before the referencing tables.  This avoids
failures when the data is loaded with the FK constraints already active.
If no such ordering is possible because of circular or self-referential
constraints, print a NOTICE to warn the user about it.

What it exactly means?

Continue reading Waiting for 8.4 – ordered data loading in pg_dump

Hunting “idle in transactions”

If you ever encountered “idle in transaction" connections, you most likely hate them. I know, I personally hate them. They interfere with most of “cool toys" like replication, vacuum, DDL queries.

So, when I saw them on a database I was looking on, I decided to act.

Easier to say, difficult to do. How to fix the problem?

Continue reading Hunting “idle in transactions"

Waiting for 8.4 – UNION / INTERSECT / EXCEPT

During last month or so, Tom Lane commited changes in PostgreSQL, which were foundations for adding hash-based versions of popular features.

I already described first such feature – DISTINCT.

Now, there were 3 more commits which were related to this:

Continue reading Waiting for 8.4 – UNION / INTERSECT / EXCEPT