Skip site navigation (1) Skip section navigation (2)

== Postgres Weekly News - 17 agosto 2008 ==

From: rotellaro(at)gmail(dot)com
To: pgsql-it-generale <pgsql-it-generale(at)postgresql(dot)org>
Subject: == Postgres Weekly News - 17 agosto 2008 ==
Date: 2008-08-18 07:20:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-it-generale
== Offerte di lavoro legate a PostgreSQL per il mese di Agosto ==

== PostgreSQL news locali ==

Il Prato Linux User Group terrà dei talk su PostgreSQL talks a Settembre.
Il calendario in italiano si trova all'url:

Il PGCon Brasile 2008 ci sara' il 26 e 27 settembre 2008 a Unicamp, Campinas.

Il ci sarà il 4 ottobrea a Toulouse.  Il Call for Papers è aperto:
Per registrarsi:

La conferenza su PostgreSQL della costa occidentale per il 2008
ci sarà dal 10 al 12 ottobre alla Portland State University a Portland
stato dell'Oregon.
Invio talk all'indirizzo:

Sponsorizzate il PGDay Europeo!

E' iniziato il call for papers per il PGDay europeo.

Il PGDay.(IT|EU) 2008 ci sarà il 17 e 18 ottobre a Prato.

== News su PostgreSQL ==

Planet PostgreSQL:

General Bits, archivi e nuovi articoli occasionali:

PostgreSQL Weekly News è stato spedito questa settimana grazie a David Fetter.

Per segnalare news e annunci invia un email in inglese entro le ore 15,
fuso orario della costa orientale degli U.S.A, di domenica.

Per segnalazioni in inglese david(at)fetter(dot)org, per segnalazioni in
Tedesco pwn(at)pgug(dot)de, per segnalazioni in italiano pwn(at)itpug(dot)org

== Patch applicate ==
Heikki Linnakangas committed:

- Introduce the concept of relation forks. An smgr relation can now
 consist of multiple forks, and each fork can be created and grown
 separately.  The bulk of this patch is about changing the smgr API
 to include an extra ForkNumber argument in every smgr function.
 Also, smgrscheduleunlink and smgrdounlink no longer implicitly call
 smgrclose, because other forks might still exist after unlinking
 one. The callers of those functions have been modified to call
 smgrclose instead.  This patch in itself doesn't have any
 user-visible effect, but provides the infrastructure needed for
 upcoming patches. The additional forks envisioned are a rewritten
 FSM implementation that doesn't rely on a fixed-size shared memory
 block, and a visibility map to allow skipping portions of a table in
 VACUUM that have no dead tuples.

- Relation forks patch requires a catversion bump due to changes in
 the format of some WAL records, and two-phase state files, which I

- pg_buffercache needs to be taught about relation forks, as Greg
 Stark pointed out.

- Fix pull_up_simple_union_all to copy all rtable entries from child
 subquery to parent, not only those with RangeTblRefs. We need them
 in ExecCheckRTPerms.  Report by Brendan O'Shea. Back-patch to 8.2,
 where pull_up_simple_union_all was introduced.

Bruce Momjian committed:

- Add to TODO: "Add 'hostgss' pg_hba.conf option to allow GSS
 link-level encryption."

- Add URL for TODO: "Improve ability to modify views via ALTER TABLE."

- Fix version warning bug in recently applied adjustments to psql
 startup.  Gregory Stark

- Add new SQL training web site to FAQ:

- Update Russian FAQ.  Viktor Vislobokov.

- In pgsql/src/interfaces/libpq/bcc32.mak, synchronize Borland libpq
 makefile to match MSVC.  Backpatch to 8.3.X.

- Add to TODO: "Prevent query cancel packets from being replayed by an
 attacker, especially when using SSL."

- Add URL for TODO: "Improve ability to modify views via ALTER TABLE."

- Update instructions on generating TODO.html

Peter Eisentraut committed:

- Remove TODO item, "Allow XML to accept more liberal DOCTYPE
 specifications."  Everything works correctly, per today's email to

Alvaro Herrera committed:

- Have autovacuum consider processing TOAST tables separately from
 their main tables.  This requires vacuum() to accept processing a
 toast table standalone, so there's a user-visible change in that
 it's now possible (for a superuser) to execute "VACUUM

Tom Lane committed:

- Implement SEMI and ANTI joins in the planner and executor.
 (Semijoins replace the old JOIN_IN code, but antijoins are new
 functionality.)  Teach the planner to convert appropriate EXISTS and
 NOT EXISTS subqueries into semi and anti joins respectively.  Also,
 LEFT JOINs with suitable upper-level IS NULL filters are recognized
 as being anti joins.  Unify the InClauseInfo and OuterJoinInfo
 infrastructure into "SpecialJoinInfo".  With that change, it becomes
 possible to associate a SpecialJoinInfo with every join attempt,
 which permits some cleanup of join selectivity estimation.  That
 needs to be taken much further than this patch does, but the next
 step is to change the API for oprjoin selectivity functions, which
 seems like material for a separate patch.  So for the moment the
 output size estimates for semi and especially anti joins are quite

- Performance fix for new anti-join code in nodeMergejoin.c: after
 finding a match in antijoin mode, we should advance to next outer
 tuple not next inner.  We know we don't want to return this outer
 tuple, and there is no point in advancing over matching inner tuples
 now, because we'd just have to do it again if the next outer tuple
 has the same merge key.  This makes a noticeable difference if there
 are lots of duplicate keys in both inputs.  Similarly, after finding
 a match in semijoin mode, arrange to advance to the next outer tuple
 after returning the current match; or immediately, if it fails the
 extra quals.  The rationale is the same.  (This is a performance bug
 in existing releases; perhaps worth back-patching?  The planner
 tries to avoid using mergejoin with lots of duplicates, so it may
 not be a big issue in practice.) Nestloop and hash got this right to
 start with, but I made some cosmetic adjustments there to make the
 corresponding bits of logic look more similar.

- Clean up the loose ends in selectivity estimation left by my patch
 for semi and anti joins.  To do this, pass the SpecialJoinInfo
 struct for the current join as an additional optional argument to
 operator join selectivity estimation functions.  This allows the
 estimator to tell not only what kind of join is being formed, but
 which variable is on which side of the join; a requirement long
 recognized but not dealt with till now.  This also leaves the door
 open for future improvements in the estimators, such as accounting
 for the null-insertion effects of lower outer joins.  I didn't do
 anything about that in the current patch but the information is in
 principle deducible from what's passed.  The patch also clarifies
 the definition of join selectivity for semi/anti joins: it's the
 fraction of the left input that has (at least one) match in the
 right input.  This allows getting rid of some very fuzzy thinking
 that I had committed in the original 7.4-era IN-optimization patch.
 There's probably room to estimate this better than the present patch
 does, but at least we know what to estimate.  Since I had to touch
 CREATE OPERATOR anyway to allow a variant signature for join
 estimator functions, I took the opportunity to add a couple of
 additional checks that were missing, per my recent message to
 * Check that estimator functions return float8; Require execute
 * permission at the time of CREATE OPERATOR on the
 operator's function as well as the estimator functions;
 * Require ownership of any pre-existing operator that's modified by
 the command.  I also moved the lookup of the functions out of
 OperatorCreate() and into operatorcmds.c, since that seemed more
 consistent with most of the other catalog object creation processes,

- Fix a couple of places where psql might fail to report a suitable
 error if PQexec returns NULL.  These don't seem significant enough
 to be worth back-patching, but they ought to get fixed ...

- In pgsql/src/bin/pg_dump/pg_backup_db.c, fix pg_dump/pg_restore's
 ExecuteSqlCommand() to behave suitably if PQexec returns NULL
 instead of a PGresult.  The former coding would fail, which is OK,
 but it neglected to give you the PQerrorMessage that might tell you
 why.  In the oldest branches, there was another problem: it'd
 sometimes report PQerrorMessage from the wrong connection.

- Improve sublink pullup code to handle ANY/EXISTS sublinks that are
 at top level of a JOIN/ON clause, not only at top level of WHERE.
 (However, we can't do this in an outer join's ON clause, unless the
 ANY/EXISTS refers only to the nullable side of the outer join, so
 that it can effectively be pushed down into the nullable side.)  Per
 request from Kevin Grittner.  In passing, fix a bug in the initial
 implementation of EXISTS pullup: it would Assert if the EXIST's
 WHERE clause used a join alias variable.  Since we haven't yet
 flattened join aliases when this transformation happens, it's
 necessary to include join relids in the computed set of RHS relids.

- In pgsql/src/backend/optimizer/plan/subselect.c, remove prohibition
 against SubLinks in the WHERE clause of an EXISTS subquery that
 we're considering pulling up.  I hadn't wanted to think through
 whether that could work during the first pass at this stuff.
 However, on closer inspection it seems to be safe enough.

- In pgsql/src/backend/optimizer/path/joinrels.c, add some defenses
 against constant-FALSE outer join conditions.  Since
 eval_const_expressions will generally throw away anything that's
 ANDed with constant FALSE, what we're left with given an example
 like select * from tenk1 a where (unique1,0) in (select unique2,1
 from tenk1 b); is a cartesian product computation, which is really
 not acceptable.  This is a regression in CVS HEAD compared to
 previous releases, which were able to notice the impossible join
 condition in this case --- though not in some related cases that are
 also improved by this patch, such as select * from tenk1 a left join
 tenk1 b on (a.unique1=b.unique2 and 0=1); Fix by skipping evaluation
 of the appropriate side of the outer join in cases where it's
 demonstrably unnecessary.

Magnus Hagander committed:

- Make the temporary directory for pgstat files configurable by the
 GUC variable stats_temp_directory, instead of requiring the admin to
 mount/symlink the pg_stat_tmp directory manually.  For now the
 config variable is PGC_POSTMASTER.  Room for further improvment that
 would allow it to be changed on-the-fly.  Original idea by Euler
 Taveira de Oliveira.

- In pgsql/src/tools/msvc/, probes.h is generated from
 probes.d, not pg_trace.d.

== Patch rigettate (per ora) ==
Nessuno è stato scontentato questa settimana :-)

== Patch in attesa ==

Zdenek Kotala sent in a patch to introduce a page layout footprint in
aid of his in-place upgrades work.

David Wheeler sent in another patch to clean up the citext contrib

Zdenek Kotala sent in a WIP patch for his new page API in aid of his
in-place upgrades work.

ITAGAKI Takahiro sent in another revision of his "Copy storage
parameters" patch.

Gregory Stark sent in a patch to fix a bug in psql where old variables
were being used for the new database connection after \c.

ITAGAKI Takahiro sent in a patch to add duration option (-T) to
pgbench instead of number of transactions (-t). -t and -T are mutually

Jan Urbanski sent in two more revisions of his oprrest patch for text

Dmitri Koterov sent in a patch to add three new functions to
contrib/intarray: int_array_append_aggregate(int[]), which quickly
merges arrays, _int_group_count_sort(int[], bool), a frequency-based
sort, and bidx(int[], int), a binary search in a sorted array.

Martin Pihlak sent in a patch that implements plan invalidation on
function DROP, REPLACE and ALTER.

Ryan Bradetich sent in a patch to implement unsigned integer types.

Magnus Hagander sent in a patch to do pg_hba.conf and postgresql.conf
parsing in the postmaster and matching in the backend.  Per

(all opinions expressed are my own)
Federico Campoli
PostgreSQL Consulting -> PGHost

pgsql-it-generale by date

Next:From: gabriele.bartoliniDate: 2008-08-18 09:06:27
Subject: Appello: abbinamento pubblicitario per il PGDay
Previous:From: rotellaroDate: 2008-08-11 11:25:07
Subject: == Postgres Weekly News - 10 agosto 2008 ==

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group