== PostgreSQL Weekly News - October 09 2011 ==

== PostgreSQL Weekly News - October 09 2011 ==

PostgreSQL Conference Europe 2011 will be held on October 18-21 in
Amsterdam. Registration is still open.
http://2011.pgconf.eu/registration/

== PostgreSQL Product News ==

devart dotConnect 5.50 for PostgreSQL, an ADO.NET provider, released.
http://www.devart.com/dotconnect/postgresql/

ODB 1.6.0, an ORM for C++, supports PostgreSQL.
http://www.codesynthesis.com/~boris/blog/2011/10/04/odb-1-6-0-released/

pgwatch 1.0, a browser-controlled monitoring system for PostgreSQL, released.
http://www.cybertec.at/en/pgwatch

phpPgAdmin 5.0.3, a web-based administrative tool for PostgreSQL, released.
http://phppgadmin.sourceforge.net/

== PostgreSQL Jobs for October ==

http://archives.postgresql.org/pgsql-jobs/2011-10/threads.php

== PostgreSQL Local ==

PostgreSQL Conference Europe 2011 will be held on October 18-21 in
Amsterdam.
http://2011.pgconf.eu/

PG-Day Denver 2011 will be held on Friday, October 21st, 2011 at
the Auraria Campus near downtown Denver, Colorado.
http://pgday.consistentstate.com/

pgbr will be in Sao Paulo, Brazil November 3-4, 2011.
http://pgbr.postgresql.org.br/

PGConf.DE 2011 is the German-speaking PostgreSQL Conference and will
take place on November 11th in the Rheinisches Industriemuseum in
Oberhausen, Germany. Call for Papers is open.
http://2011.pgconf.de/

The fifth edition of the Italian PostgreSQL Day (PGDay.IT 2011) will
be held on November 25, 2011 in Prato, Italy.
http://2011.pgday.it/

The Call for Papers for is open for FLOSS UK, which will be held in
Edinburgh from the 20th to the 22nd March 2012. The deadline for
proposals is the 18th November 2011 and selected speakers will be
notified by the 25th November 2011. Proposals should be submitted to
postgresql2012 AT flossuk DOT org. More information at:
http://www.flossuk.org/Events/Spring2012

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot) Spanish language
to pwn(at)arpug(dot)com(dot)ar(dot)

== Reviews ==

== Applied Patches ==

Tom Lane pushed:

- ProcedureCreate neglected to record dependencies on default
expressions. Thus, an object referenced in a default expression
could be dropped while the function remained present. This was
unaccountably missed in the original patch to add default parameters
for functions. Reported by Pavel Stehule.
http://git.postgresql.org/pg/commitdiff/76074fcaa04fb5d35e8cf7716587440e3d075d50

- Remove the custom_variable_classes parameter. This variable
provides only marginal error-prevention capability (since it can
only check the prefix of a qualified GUC name), and the consensus is
that that isn't worth the amount of hassle that maintaining the
setting creates for DBAs. So, let's just remove it. With this
commit, the system will silently accept a value for any qualified
GUC name at all, whether it has anything to do with any known
extension or not. (Unqualified names still have to match known
built-in settings, though; and you will get a WARNING at extension
load time if there's an unrecognized setting with that extension's
prefix.) There's still some discussion ongoing about whether to
tighten that up and if so how; but if we do come up with a solution,
it's not likely to look anything like custom_variable_classes.
http://git.postgresql.org/pg/commitdiff/1a00c0ef5368bb7b8ddcb3cf279df36577918ac4

- Remember the source GucContext for each GUC parameter. We used to
just remember the GucSource, but saving GucContext too provides a
little more information --- notably, whether a SET was done by a
superuser or regular user. This allows us to rip out the fairly
dodgy code that define_custom_variable used to use to try to infer
the context to re-install a pre-existing setting with. In
particular, it now works for a superuser to SET a extension's SUSET
custom variable before loading the associated extension, because GUC
can remember whether the SET was done as a superuser or not. The
plperl regression tests contain an example where this is useful.
http://git.postgresql.org/pg/commitdiff/9f5836d224e876399dfdd7d6d4343300dbc2f664

- Add sourcefile/sourceline data to EXEC_BACKEND GUC transmission
files. This oversight meant that on Windows, the pg_settings view
would not display source file or line number information for values
coming from postgresql.conf, unless the backend had received a
SIGHUP since starting. In passing, also make the error detection in
read_nondefault_variables a tad more thorough, and fix it to not
lose precision on float GUCs (these changes are already in HEAD as
of my previous commit).
http://git.postgresql.org/pg/commitdiff/4bcb82a7d590afa16507f9089bd68ef4bcebebb1

- Fix uninitialized-variable bug.
http://git.postgresql.org/pg/commitdiff/fa56a0c3e01c175695e932e6cdc2c6915df5adc6

- Improve define_custom_variable's handling of pre-existing settings.
Arrange for any problems with pre-existing settings to be reported
as WARNING not ERROR, so that we don't undesirably abort the loading
of the incoming add-on module. The bad setting is just discarded,
as though it had never been applied at all. (This requires a change
in the API of set_config_option. After some thought I decided the
most potentially useful addition was to allow callers to just pass
in a desired elevel.) Arrange to restore the complete stacked state
of the variable, rather than cheesily reinstalling only the active
value. This ensures that custom GUCs will behave unsurprisingly
even when the module loading operation occurs within nested
subtransactions that have changed the active value. Since a module
load could occur as a result of, eg, a PL function call, this is not
an unlikely scenario.
http://git.postgresql.org/pg/commitdiff/41e461d36fb1ef78494429f28ea4b72c759f419d

- Improve and simplify CREATE EXTENSION's management of GUC variables.
CREATE EXTENSION needs to transiently set search_path, as well as
client_min_messages and log_min_messages. We were doing this by the
expedient of saving the current string value of each variable, doing
a SET LOCAL, and then doing another SET LOCAL with the previous
value at the end of the command. This is a bit expensive though,
and it also fails badly if there is anything funny about the
existing search_path value, as seen in a recent report from Roger
Niederland. Fortunately, there's a much better way, which is to
piggyback on the GUC infrastructure previously developed for
functions with SET options. We just open a new GUC nesting level,
do our assignments with GUC_ACTION_SAVE, and then close the nesting
level when done. This automatically restores the prior settings
without a re-parsing pass, so (in principle anyway) there can't be
an error. And guc.c still takes care of cleanup in event of an
error abort. The CREATE EXTENSION code for this was modeled on some
much older code in ri_triggers.c, which I also changed to use the
better method, even though there wasn't really much risk of failure
there. Also improve the comments in guc.c to reflect this
additional usage.
http://git.postgresql.org/pg/commitdiff/ba6f629326be365a3124dc80aa5d303e2b0bf46b

- Support index-only scans using the visibility map to avoid heap
fetches. When a btree index contains all columns required by the
query, and the visibility map shows that all tuples on a target heap
page are visible-to-all, we don't need to fetch that heap page.
This patch depends on the previous patches that made the visibility
map reliable. There's a fair amount left to do here, notably trying
to figure out a less chintzy way of estimating the cost of an
index-only scan, but the core functionality seems ready to commit.
Robert Haas and Ibrar Ahmed, with some previous work by Heikki
Linnakangas.
http://git.postgresql.org/pg/commitdiff/a2822fb9337a21f98ac4ce850bb4145acf47ca27

- Fix brain fade in cost estimation for index-only scans.
visibility_fraction should not be applied to regular indexscans.
Noted by Cédric Villemain.
http://git.postgresql.org/pg/commitdiff/b324384f6bd5d661efeddb83d7f607781e96947d

- Note that index-only scans can affect idx_tup_fetch. An index-only
scan that avoids heap fetches will increment idx_tup_read but not
idx_tup_fetch.
http://git.postgresql.org/pg/commitdiff/c78d8cd1464bc6b69fdc72f9ce51407c89554ece

- Prevent index-only scans in stats regression test. This bollixes
the test because it's expecting to see the idx_tup_fetch counter
increase, which won't happen if heap fetches were avoided by use of
an index-only scan. Per buildfarm results. While at it, let's just
make sure that enable_seqscan and enable_indexscan are ON for this
test ...
http://git.postgresql.org/pg/commitdiff/45401c1c25fe1ef14bf68089de86bcb5cce9f453

- Improve index-only scans to avoid repeated access to the index page.
We copy all the matched tuples off the page during _bt_readpage,
instead of expensively re-locking the page during each subsequent
tuple fetch. This costs a bit more local storage, but not more than
2*BLCKSZ worth, and the reduction in LWLock traffic is certainly
worth that. What's more, this lets us get rid of the API wart in
the original patch that said an index AM could randomly decline to
supply an index tuple despite having asserted pg_am.amcanreturn.
That will be important for future improvements in the
index-only-scan feature, since the executor will now be able to rely
on having the index data available.
http://git.postgresql.org/pg/commitdiff/cbfa92c23c3924d53889320cdbe26f23ee23e40c

Alvaro Herrera pushed:

- Use callbacks in SlruScanDirectory for the actual action.
Previously, the code assumed that the only possible action to take
was to delete files behind a certain cutoff point. The async notify
code was already a crock: it used a different "pagePrecedes"
function for truncation than for regular operation. By allowing it
to pass a callback to SlruScanDirectory it can do cleanly exactly
what it needs to do. The clog.c code also had its own use for
SlruScanDirectory, which is made a bit simpler with this.
http://git.postgresql.org/pg/commitdiff/09e196e4539a70c51e828abcfe48dee3efd312d8

Bruce Momjian pushed:

- Explain COALESCE example in the docs.
http://git.postgresql.org/pg/commitdiff/9d23d7cdafb3540aa2de9310c7f1afee87dfc2f2

- Change wording for COALESCE docs to use "otherwise". Per Tom Lane
http://git.postgresql.org/pg/commitdiff/3919ad864d7040361fd4b44719acaa1ec0f87bbd

- Add postmaster -C option to query configuration parameters, and have
pg_ctl use that to query the data directory for config-only
installs. This fixes awkward or impossible pg_ctl operation for
config-only installs.
http://git.postgresql.org/pg/commitdiff/aaa6e1def292cdacb6b27088898793b1b879fedf

- Simplify new pg_ctl newline termination code.
http://git.postgresql.org/pg/commitdiff/416e82ce9f0c8c4ec6d377368d670b8ca74097a6

- Move pg_upgrade doc mention of the port numbers to the same place.
http://git.postgresql.org/pg/commitdiff/981e5acd24cc63fa4f27dac3b35e5d0bf3921efc

- In pg_upgrade, improve popen() failure detection by checking for
fgets() failures.
http://git.postgresql.org/pg/commitdiff/a3996754cc0281293cc5bfb90bf594fef41aff5b

- In pg_upgrade, use the new postmaster -C option to get the real data
directory, for config-only directory installs. Only works for PG
9.2+ servers.
http://git.postgresql.org/pg/commitdiff/caa1054df8408b165e5f66ff25c87b6dd0a0a1e7

Heikki Linnakangas pushed:

- Replace the "New Linear" GiST split algorithm for boxes and points
with a new double-sorting algorithm. The new algorithm produces
better quality trees, making searches faster. Alexander Korotkov
http://git.postgresql.org/pg/commitdiff/7f3bd86843e5aad84585a57d3f6b80db3c609916

- Don't let transform_null_equals=on affect CASE foo WHEN NULL ...
constructs. transform_null_equals is only supposed to affect "foo =
NULL" expressions given directly by the user, not the internal "foo
= NULL" expression generated from CASE-WHEN. This fixes bug #6242,
reported by Sergey. Backpatch to all supported branches.
http://git.postgresql.org/pg/commitdiff/1ef60dab7049ffac52dee60b5788b6c7bc1f9d67

- Clean up a couple of box gist helper functions. The original idea
of this patch was to make box picksplit run faster, by eliminating
unnecessary palloc() overhead, but that was obsoleted by the new
double-sorting split algorithm that doesn't call these functions so
heavily anymore. Nevertheless, the code looks better this way.
Original patch by me, reviewed and tidied up after the
double-sorting patch by Kevin Grittner.
http://git.postgresql.org/pg/commitdiff/d50e1251946a6e59092f0a84fc903532eb599a4f

Robert Haas pushed:

- Make pgstatindex respond to cancel interrupts. A similar problem
for pgstattuple() was fixed in April of 2010 by commit
33065ef8bc52253ae855bc959576e52d8a28ba06, but pgstatindex() seems to
have been overlooked. Back-patch all the way, as with that commit,
though not to 7.4 through 8.1, since those are now EOL.
http://git.postgresql.org/pg/commitdiff/1cb018dd09268cab7f4ac1e8ab59b2277e7709f1

- Try to fix memory barriers on x86_64. %esp is no good; must use
%rsp there.
http://git.postgresql.org/pg/commitdiff/6a6082c27c148eb452d804af306e8745f6e49b36

- Add missing space. Dickson S. Guedes
http://git.postgresql.org/pg/commitdiff/13168ae4ebb6eac6f668a4a157e7fe3ffae22d85

- Fix typo.
http://git.postgresql.org/pg/commitdiff/041dceb2590081c397daeda84ff28ffc71a51fb0

- Revert accidental change to pg_config_manual.h. This was broken in
commit 53dbc27c62d8e1b6c5253feba04a5094cb8fe046, which introduced
unlogged tables. Fortunately, as debugging tools go, this one is
pretty cheap, which is probably why it took nine months for someone
to notice, but it's not intended to be enabled by default, so
revert. Noted by Fujii Masao.
http://git.postgresql.org/pg/commitdiff/c980426c69ae76b04aa9de010027438457aeaa7f

- Fix ALTER TABLE ONLY .. DROP CONSTRAINT. When I consolidated two
copies of the HOT-chain search logic in commit
4da99ea4231e3d8bbf28b666748c1028e7b7d665, I introduced a behavior
change: the old code wouldn't necessarily traverse the entire chain,
if the most recently returned tuple were updated while the HOT chain
traversal is in progress. The new behavior seems more correct, but
unfortunately, the code here relies on a scan with SnapshotNow
failing to see its own updates. That seems pretty shaky even with
the old HOT chain traversal behavior, since there's no guarantee
that these updates will always be HOT, but it's trivial to broke a
failure with the new HOT search logic. Fix by updating just the
first matching pg_constraint tuple, rather than all of them, since
there should be only one anyway. But since nobody has reproduced
this failure on older versions, no back-patch for now. Report and
test case by Alex Hunsaker; tablecmds.c changes by me.
http://git.postgresql.org/pg/commitdiff/c0f03aae0469e758964faac0fb741685170c39a5

Magnus Hagander pushed:

- Ensure walsenders can be SIGTERMed while in non-walsender code. In
order to exit on SIGTERM when in non-walsender code, such as
do_pg_stop_backup(), we need to set the interrupt variables that are
used there, and not just the walsender local ones.
http://git.postgresql.org/pg/commitdiff/7aeff9f4a4e57dca5727828bf95f12cf63003270

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Simon Riggs sent in two more revisions of the patch to separate
checkpointing and background writing into distinct components.

Royce Ausburn sent in two revisions of a patch to enable monitoring
unremovable tuples.

Heikki Linnakangas sent in another revision of a patch to fix some
socket issues on HP-UX.

Fujii Masao sent in another revision of a patch to fix a bug in
recovery.

Fujii Masao sent in another revision of a patch to add a
pg_last_xact_insert_timestamp column.

Alex Hunsaker sent in two more revisions of a patch to do some
encoding checking for PL/Perl inputs.

Pavel Stehule sent in a patch which returns the number of rows
processed by COPY.

KaiGai Kohei sent in two more revisions of the patch to rework DROP
into a single framework.

Pavel Stehule sent in two revisions of a patch to implement CHECK
FUNCTION and CHECK TRIGGER.

Alex Hunsaker sent in two more revisions of a patch to allow
non-inheritable CHECK constraints.

Alex Hunsaker and Robert Haas traded revisions of a patch to fix ALTER
TABLE ONLY ... DROP CONSTRAINT.

Marti Raudsepp sent in another revision of a patch to log crashed
backends.

Kyotaro HORIGUCHI sent in a patch to endure that make_greater_string()
returns a string.

Simon Riggs sent in two revisions of a patch to prevent duplicate
checkpoints.

Etsuro Fujita sent in another revision of a patch to collect
statistics on foreign tables.

Yeb Havinga sent in another revision of a patch that enables cursors
with named parameters.

Julien Tachoires sent in a WIP patch which enables moving TOAST tables
to a different tablespace.

Kevin Grittner sent in another revision of the patch to optimize
box_penalty.

KaiGai Kohei sent in another revision of the patch to fix some leaks
in VIEWs.

Jun Ishiduka sent in another revision of the patch to allow creating a
backup from a hot standby.