Tag: postgresql

Waiting for 8.5 – DO

On 22nd of September, Tom Lane committed a patch by Petr Jelinek:

Log Message:
-----------
Implement the DO statement to support execution of PL code without having
to create a function for it.
 
Procedural languages now have an additional entry point, namely a function
to execute an inline code block.  This seemed a better design than trying
to hide the transient-ness of the code from the PL.  As of this patch, only
plpgsql has an inline handler, but probably people will soon write handlers
for the other standard PLs.
 
In passing, remove the long-dead LANCOMPILER option of CREATE LANGUAGE.
 
Petr Jelinek

Continue reading Waiting for 8.5 – DO

Calculating backlog of events to handle

Yesterday on my favorite IRC channel fooqux asked interesting question. I took some more questions, and here is problem description:

We have a system which, every 5 minutes, takes a number of tasks to be done. Tasks are uniform. Within 5 minutes we can handle at most 100 tasks. Given the history of number of tasks added every 5 minutes, calculate backlog at any given moment.

Did you understand the problem? Well – I didn't. So, let's see the data, and expected output.

Continue reading Calculating backlog of events to handle

Waiting for 8.5 – changes in hstore

Andrew Gierth (RhodiumToad on irc) wrote, and Tom Lane committed:

Assorted improvements in contrib/hstore.
 
Remove the 64K limit on the lengths of keys and values within an hstore.
(This changes the on-disk format, but the old format can still be read.)
Add support for btree/hash opclasses for hstore --- this is not so much
for actual indexing purposes as to allow use of GROUP BY, DISTINCT, etc.
Add various other new functions and operators.
 
Andrew Gierth

Continue reading Waiting for 8.5 – changes in hstore

Waiting for 8.5 – MOVE {FORWARD,BACKWARD} X

On 29th of September (I know, there is a backlog – I'll work on it, I promise), Tom Lane committed another patch from Pavel Stehule:

Allow MOVE FORWARD n, MOVE BACKWARD n, MOVE FORWARD ALL, MOVE BACKWARD ALL
in plpgsql.  Clean up a couple of corner cases in the MOVE/FETCH syntax.
 
Pavel Stehule

Continue reading Waiting for 8.5 – MOVE {FORWARD,BACKWARD} X

Speeding up dump/restore process

As some of you know, I've been working lately for OmniTI company. When doing things for them (PostgreSQL related of course :), I stumbled on very interesting problem.

One of our clients is working on PostgreSQL 8.2, and wants to upgrade to 8.3. This is generally trivial – pg_dump, pg_restore/psql, and you're done.

But, this situation was with a twist – we had only very limited time-frame to do the migration. To be exact – we had 4 hours that we can bring the website down for.

So, we had to dump database, transfer it to new server, and load. All within 4 hours. Simple? Sure. Database was ~ 200GB (after restore – around 130GB). How to do it?

Continue reading Speeding up dump/restore process

Waiting for 8.5 – Machine readable EXPLAIN

After very long discussions, on 10th of August, Tom Lane committed patch by Robert Haas:

Log Message:
-----------
Extend EXPLAIN to support output in XML or JSON format.
 
There are probably still some adjustments to be made in the details
of the output, but this gets the basic structure in place.
 
Robert Haas

Continue reading Waiting for 8.5 – Machine readable EXPLAIN

Waiting for 8.5 – Multi-threaded pgbench

On 3rd of August, Tatsuo Ishii committed patch by ITAGAKI Takahiro:

Log Message:
-----------
Multi-threaded version of pgbench contributed by ITAGAKI Takahiro,
reviewed by Greg Smith and Josh Williams.
 
Following is the proposal from ITAGAKI Takahiro:
 
Pgbench is a famous tool to measure postgres performance, but nowadays
it does not work well because it cannot use multiple CPUs. On the other
hand, postgres server can use CPUs very well, so the bottle-neck of
workload is *in pgbench*.
 
Multi-threading would be a solution. The attached patch adds -j
(number of jobs) option to pgbench. If the value N is greater than 1,
pgbench runs with N threads. Connections are equally-divided into
them (ex. -c64 -j4 => 4 threads with 16 connections each). It can
run on POSIX platforms with pthread and on Windows with win32 threads.
 
Here are results of multi-threaded pgbench runs on Fedora 11 with intel
core i7 (8 logical cores = 4 physical cores * HT). -j8 (8 threads) was
the best and the tps is 4.5 times of -j1, that is a traditional result.
 
$ pgbench -i -s10
$ pgbench -n -S -c64 -j1   =>  tps = 11600.158593
$ pgbench -n -S -c64 -j2   =>  tps = 17947.100954
$ pgbench -n -S -c64 -j4   =>  tps = 26571.124001
$ pgbench -n -S -c64 -j8   =>  tps = 52725.470403
$ pgbench -n -S -c64 -j16  =>  tps = 38976.675319
$ pgbench -n -S -c64 -j32  =>  tps = 28998.499601
$ pgbench -n -S -c64 -j64  =>  tps = 26701.877815
 
Is it acceptable to use pthread in contrib module?
If ok, I will add the patch to the next commitfest.

Continue reading Waiting for 8.5 – Multi-threaded pgbench

Getting session variables without touching postgresql.conf

This post has been updated with new code that uses temporary table – the code is at the end of post!

There was this question on Stack Overflow.

For future reference: guy asked how to do session variables – i.e. something he could define once in session, and later reuse in standard sql queries – without modifying postgresql.conf – so usage of custom_variable_classes is forbidden 🙂

While I don't actually see why somebody would want to avoid modifying its postgresql.conf (short of “it's shared hosting and I don't have superuser privileges"), I thought that it will be rather simple, and at the same time, rather interesting.

So, let's do it:

Continue reading Getting session variables without touching postgresql.conf

Waiting for 8.5 – hinting for number of distinct values

Robert Haas wrote, and Tom Lane committed (on 2nd of August) patch which adds interesting capability:

Log Message:
-----------
Add ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT
 
Robert Haas

Log message is rather terse, so what does it exactly do?

Continue reading Waiting for 8.5 – hinting for number of distinct values