Tag: postgresql

explain.depesz.com.

Long time ago I wrote small program to filter EXPLAIN ANALYZE output, and add summary of time.

A bit later (I guess, I don't recall exact time line, it could have been earlier) Michael Glaesemann started explain-analyze.info – cool tool for checking what might be wrong with given plan.

I'm not really happy with the emphasis Michael put on bad rowcount estimates, so I decided to write my own tool. Enter explain.depesz.com.

Basic idea is: paste your explain analyze plan, and see the output. You can click on column headers to let it know which parameter is the most important for you – exclusive node time, inclusive node time, or rowcount mis-estimate.

It is definitely not perfect. I know of at least 1 bug now, and will fix it in not-distant future.

But, as for now – you can test it, play it, or simply use it. If you'd like to change/fix something – sources are freely available. Just be warned – it's Perl ;-P

Getting list of most common domains

Today, on #postgresql on IRC, guy (can't contact him now to get his permission to name him), said:

I have a table called problematic_hostnames. It contains a list of banned hostnames in column “hostname" (varchar). I would like to display the top 10 troll ISPs based on this. Does PG have a way of spotting a “pattern"? Some ISPs are example.net while others are foo.bar.example.net, so you can't just regexp the last X.Y (since that would cause “.co.uk" to be one of the top troll ISPs).

Continue reading Getting list of most common domains

Recovering from a lost PostgreSQL password.

Let's say you're in situation when you have to connect to PostgreSQL, but you have no idea on what password might be set. But some definitely is, as you get this error message:

=> psql
Password:
psql: FATAL:  password authentication failed for user "depesz"

Continue reading Recovering from a lost PostgreSQL password.

Waiting for 8.4 – TABLE

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':

Continue reading Waiting for 8.4 – TABLE

Waiting for 8.4 – array aggregate and array unpacker

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

Waiting for 8.4 – suppress_redundant_updates_trigger

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

Waiting for 8.4 – sql-wrappable RETURNING

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.

Continue reading Waiting for 8.4 – sql-wrappable RETURNING

Tips N’ Tricks – setting field based on order

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:

  1. 10000
  2. 5000
  3. 1000

Continue reading Tips N’ Tricks – setting field based on order