Tag: postgresql

OmniPITR – important update

Couple of important points:

  • Fixed omnipitr-backup-slave on Pg 9.0
  • Added tests system which lets me check if the omnipitr actually work after I change stuff
  • Finally added version information.

The fix is important – in some cases, making backups off slave on 9.0 will not work.

By not work I mean that backups would be still done, but Pg wouldn't start from it. If started as slave, it would work OK, but you wouldn't be able to promote it to standalone.

New version of OmniPITR fixes that, as long as you're using -cm switch (and possibly some others like -h, -P, -U or -d – details in docs.

Version information is actually not really simple to get, it requires:

perl -I/opt/omnipitr/lib -le 'use OmniPITR::Program; print $OmniPITR::Program::VERSION'

But it works. It will be changed to normal –version option in near future.

Also, as you can perhaps see, we migrated to github.

Pagination with fixed order

Some time ago I wrote about getting fast pagination. While fast, it had some problems which made it unusable for some. Specifically – you couldn't get page count, and easily jump to page number N.

I did some thinking on the subject, and I think I found a way to make it all work. Quite fast. And with not big overhead. Let me show you.

Continue reading Pagination with fixed order

Understanding postgresql.conf : log*

After loooong pause, adding next (well, second) post to the “series“. This time, I'd like to describe how logging works. And I don't mean binary logging (WAL), but the log for us, humans, to read.

Before I will go to the postgresql.conf options, let me first describe what happens when PostgreSQL wants to log something.

Continue reading Understanding postgresql.conf : log*

Find cheapest combination of rooms in hotels

Today, on Stack Overflow there was interesting question.

Generally, given table that looks like this:

room | people | price   | hotel
 1   |    1   |   200   |   A
 2   |    2   |   99    |   A
 3   |    3   |   95    |   A
 4   |    1   |   90    |   B
 5   |    6   |   300   |   B

Find cheapest combination of rooms that would accomodate given number of guests.

Continue reading Find cheapest combination of rooms in hotels

Waiting for 9.1 – Synchronous replication

On 6th of March, Simon Riggs committed patch:

Efficient transaction-controlled synchronous replication.
If a standby is broadcasting reply messages and we have named
one or more standbys in synchronous_standby_names then allow
users who set synchronous_replication to wait for commit, which
then provides strict data integrity guarantees. Design avoids
sending and receiving transaction state information so minimises
bookkeeping overheads. We synchronize with the highest priority
standby that is connected and ready to synchronize. Other standbys
can be defined to takeover in case of standby failure.
 
This version has very strict behaviour; more relaxed options
may be added at a later date.
 
Simon Riggs and Fujii Masao, with reviews by Yeb Havinga, Jaime
Casanova, Heikki Linnakangas and Robert Haas, plus the assistance
of many other design reviewers.

Continue reading Waiting for 9.1 – Synchronous replication

Waiting for 9.1 – Writable CTE

On 25th of February, Tom Lane committed patch:

Support data-modifying commands (INSERT/UPDATE/DELETE) IN WITH.   
 
This patch implements data-modifying WITH queries according TO the           
semantics that the updates ALL happen WITH the same command counter VALUE,
AND IN an unspecified ORDER.  Therefore one WITH clause can't see the 
effects of another, nor can the outer query see the effects other than
through the RETURNING values.  And attempts to do conflicting updates will
have unpredictable results.  We'll need TO document ALL that.           
 
This commit just fixes the code; documentation updates are waiting ON
author.                                                                 
 
Marko Tiikkaja AND Hitoshi Harada

Continue reading Waiting for 9.1 – Writable CTE

explain.depesz.com version 2.0

Thanks to enormous work done by Łukasz ‘metys' Lewandowski, explain.depesz.com page is no longer “desiged by depesz" (which is a code for “ugly as hell"), but is nice, and good looking.

If you like it, please do send some thank you note to Łukasz – he blogs in Polish, but he reads and understands English too.

Change is not only skin deep. The whole site has been rewritten, and uses now Mojolicious web framework instead of Catalyst.

This change should be a welcome surprise to anyone willing to setup their own copy of the site for top-secret plans from their company – mostly because number of dependencies dropped significantly.

All in all – have fun, and thanks for using the site.

Waiting for 9.1 – Foreign data wrapper

Well, saying that on particular date someone committed patch, wouldn't be really telling. In fact various bits and pieces of underlying logic have been committed for a long time, but now we finally have some functionality visible and available to end users.

This became the case thanks to these two commits, both committed on 20th of February, by Tom Lane.

First:

Implement an API to let foreign-data wrappers actually be functional.
 
This commit provides the core code and documentation needed.  A contrib
module test case will follow shortly.
 
Shigeru Hanada, Jan Urbanski, Heikki Linnakangas

and second:

Add contrib/file_fdw foreign-data wrapper for reading files via COPY.
 
This is both very useful in its own right, and an important test case
for the core FDW support.
 
This commit includes a small refactoring of copy.c to expose its option
checking code as a separately callable function.  The original patch
submission duplicated hundreds of lines of that code, which seemed pretty
unmaintainable.
 
Shigeru Hanada, reviewed by Itagaki Takahiro and Tom Lane

Continue reading Waiting for 9.1 – Foreign data wrapper