WITH statements Because the WITH statement works like having a temporary table, the formatting will follow rules similar to the CREATE TABLE statement. • Between the WITH and the alias there is a tab followed by the word AS and a carriage return • The round brackets surrounding the inner query indent one tab from the alias • The inner query indents one tab from the round brackets Conclusions This coding style is at first sight complex and with some drawback as it can slow down the coding process because of more keystrokes required for the uppercase and the carriage return.

Before starting with the book’s topic I want to explain how to set up an efficient environment and some good practice which can improve the code’s readability and quality. As somebody will notice these methods are completely opposite to the general code style trends. I’ll try to give the motivation for each rule. Anyway, in general because the SQL universe is a strange place this requires strange approach. In order to write and read effectively the SQL the coder should gain the capability to get a mental map between the query’s sections and the underlying logic.

With the revolutionary PostgreSQL 8.0 were introduced a different memory manager, the two queues. This algorithm uses three list of buffer page pointers called cache directory blocks (CDB). The lists T1,T2 are actually pointing buffered pages. B1 lists pages been recently in the shared buffer. The list T1 is used as LRU for the pages loaded from disk. The list T2 is used as LRU list for pages already cached and evicted from the list T1.

I’m not dead yeat. Just hard days. However, I’ll talk at the Skiff in the sunny Brighton about PostgreSQL, the big the fast and the (NOSQL on) ACID. If you are around we should kick in the talk at 19.00 BST. This talk is part of the SANE meetings happening on a monthly basis. More informations about the Skiff there. http://www.theskiff.org/ Here the slides PostgreSQL, the big the fast and the (NOSQL on) Acid from Federico Campoli

3.1.1 PostgreSQL 7.4, the LRU list In PostgreSQL 7.4 the free space in the shared buffer was managed using a simple last recently used list. When a page is first request from disk the buffer manager puts it into the first buffer in the free LRU list. If the first buffer is used by another page the list is shifted by one unity and the eventual last page in the list is discarded.