== PostgreSQL Weekly News - December 25 2011 ==

== PostgreSQL Weekly News - December 25 2011 ==

HTSQL 2.2, a high-level query language for relational databases, released.
http://htsql.org

psycopg2 2.4.3, a Python connector for PostgreSQL, released.
http://initd.org/psycopg/articles/2011/12/12/psycopg-243-released/

== PostgreSQL Jobs for December ==

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

== PostgreSQL Local ==

The fifth annual "Prague PostgreSQL Developers Day" conference,
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on February 9, 2012 in Prague. The Call for Papers is open.
Please send proposals including contact information, topic, and
expected length of session to info AT p2d2 DOT cz.

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:

- Teach SP-GiST to do index-only scans. Operator classes can specify
whether or not they support this; this preserves the flexibility to
use lossy representations within an index. In passing, move
constant data about a given index into the rd_amcache cache area,
instead of doing fresh lookups each time we start an index
operation. This is mainly to try to make sure that spgcanreturn()
has insignificant cost; I still don't have any proof that it matters
for actual index accesses. Also, get rid of useless copying of
FmgrInfo pointers; we can perfectly well use the relcache's versions
in-place.
http://git.postgresql.org/pg/commitdiff/92203624934095163f8b57b5b3d7bbd2645da2c8

- Rename updateNodeLink to spgUpdateNodeLink. On reflection, the
original name seems way too generic for a global symbol. A quick
check shows this is the only exported function name in SP-GiST that
doesn't begin with "spg" or contain "SpGist", so the rest of them
seem all right.
http://git.postgresql.org/pg/commitdiff/8f57b064fdaa682ddea60f5dc27c0a5d5fcbffab

- Avoid crashing when we have problems unlinking files post-commit.
smgrdounlink takes care to not throw an ERROR if it fails to unlink
something, but that caution was rendered useless by commit
3396000684b41e7e9467d1abc67152b39e697035, which put an smgrexists
call in front of it; smgrexists *does* throw error if anything looks
funny, such as getting a permissions error from trying to open the
file. If that happens post-commit, you get a PANIC, and what's
worse the same logic appears in the WAL replay code, so the database
even fails to restart. Restore the intended behavior by removing
the smgrexists call --- it isn't accomplishing anything that we
can't do better by adjusting mdunlink's ideas of whether it ought to
warn about ENOENT or not. Per report from Joseph Shraibman of
unrecoverable crash after trying to drop a table whose FSM fork had
somehow gotten chmod'd to 000 permissions. Backpatch to 8.4, where
the bogus coding was introduced.
http://git.postgresql.org/pg/commitdiff/d0024cd1881447fa7aed58db94df379e593c6630

- Fix gincostestimate to handle ScalarArrayOpExpr reasonably. The
original coding of this function overlooked the possibility that it
could be passed anything except simple OpExpr indexquals. But
ScalarArrayOpExpr is possible too, and the code would probably crash
(and surely give ridiculous answers) in such a case. Add logic to
try to estimate sanely for such cases. In passing, fix the
treatment of inner-indexscan cost estimation: it was failing to
scale up properly for multiple iterations of a nestloop. (I think
somebody might've thought that index_pages_fetched() is linear, but
of course it's not.) Report, diagnosis, and preliminary patch by
Marti Raudsepp; I refactored it a bit and fixed the cost estimation.
Back-patch into 9.1 where the bogus code was introduced.
http://git.postgresql.org/pg/commitdiff/1db5af279441b9ee215b54de424c2af92eeb1ef8

- Update per-column ACLs, not only per-table ACL, when changing table
owner. We forgot to modify column ACLs, so privileges were still
shown as having been granted by the old owner. This meant that
neither the new owner nor a superuser could revoke the
now-untraceable-to-table-owner permissions. Per bug #6350 from Marc
Balmer. This has been wrong since column ACLs were added, so
back-patch to 8.4.
http://git.postgresql.org/pg/commitdiff/c31224e257a57fc9ad1c602414d9f6f5f4ce4ae3

- Improve planner's handling of duplicated index column expressions.
It's potentially useful for an index to repeat the same indexable
column or expression in multiple index columns, if the columns have
different opclasses. (If they share opclasses too, the duplicate
column is pretty useless, but nonetheless we've allowed such cases
since 9.0.) However, the planner failed to cope with this, because
createplan.c was relying on simple equal() matching to figure out
which index column each index qual is intended for. We do have that
information available upstream in indxpath.c, though, so the fix is
to not flatten the multi-level indexquals list when putting it into
an IndexPath. Then we can rely on the sublist structure to identify
target index columns in createplan.c. There's a similar issue for
index ORDER BYs (the KNNGIST feature), so introduce a
multi-level-list representation for that too. This adds a bit more
representational overhead, but we might more or less buy that back
by not having to search for matching index columns anymore in
createplan.c; likewise btcostestimate saves some cycles. Per bug
#6351 from Christian Rudolph. Likely symptoms include the "btree
index keys must be ordered by attribute" failure shown there, as
well as "operator MMMM is not a member of opfamily NNNN". Although
this is a pre-existing problem that can be demonstrated in 9.0 and
9.1, I'm not going to back-patch it, because the API changes in the
planner seem likely to break things such as index plugins. The
corner cases where this matters seem too narrow to justify possibly
breaking things in a minor release.
http://git.postgresql.org/pg/commitdiff/e2c2c2e8b1df7dfdb01e7e6f6191a569ce3c3195

- Rethink representation of index clauses' mapping to index columns.
In commit e2c2c2e8b1df7dfdb01e7e6f6191a569ce3c3195 I made use of
nested list structures to show which clauses went with which index
columns, but on reflection that's a data structure that only an
old-line Lisp hacker could love. Worse, it adds unnecessary
complication to the many places that don't much care which clauses
go with which index columns. Revert to the previous arrangement of
flat lists of clauses, and instead add a parallel integer list of
column numbers. The places that care about the pairing can chase
both lists with forboth(), while the places that don't care just
examine one list the same as before. The only real downside to this
is that there are now two more lists that need to be passed to
amcostestimate functions in case they care about column matching
(which btcostestimate does, so not passing the info is not an
option). Rather than deal with 11-argument amcostestimate
functions, pass just the IndexPath and expect the functions to
extract fields from it. That gets us down to 7 arguments which is
better than 11, and it seems more future-proof against likely
additions to the information we keep about an index path.
http://git.postgresql.org/pg/commitdiff/472d3935a2793343e450ba7cda4adbc323a984c3

Alvaro Herrera pushed:

- Allow CHECK constraints to be declared ONLY. This makes them
enforceable only on the parent table, not on children tables. This
is useful in various situations, per discussion involving people
bitten by the restrictive behavior introduced in 8.4. Authors:
Nikhil Sontakke, Alex Hunsaker Reviewed by Robert Haas and myself
http://git.postgresql.org/pg/commitdiff/61d81bd28dbec65a6b144e0cd3d0bfe25913c3ac

- Forgot catversion bump on previous patch. Per Tom Lane.
http://git.postgresql.org/pg/commitdiff/05e992e90e49aa5bca7e2b290ab736bfec97a7c1

Peter Eisentraut pushed:

- Add support for privileges on types. This adds support for the more
or less SQL-conforming USAGE privilege on types and domains. The
intent is to be able restrict which users can create dependencies on
types, which restricts the way in which owners can alter types.
reviewed by Yeb Havinga
http://git.postgresql.org/pg/commitdiff/729205571e81b4767efc42ad7beb53663e08d1ff

- Add ALTER DOMAIN ... RENAME You could already rename domains using
ALTER TYPE, but with this new command it is more consistent with how
other commands treat domains as a subcategory of types.
http://git.postgresql.org/pg/commitdiff/f90dd28062db2128a340fbe02f55829f15ab5561

Robert Haas pushed:

- Shave a few cycles in string_agg(). Pavel Stehule
http://git.postgresql.org/pg/commitdiff/7f0e4bb82e408090c0366c63a9ff4c0f7c4b0a8e

- sepgsql: Check CREATE permissions for some object types. KaiGai
Kohei, reviewed by Dimitri Fontaine and me.
http://git.postgresql.org/pg/commitdiff/e1042a348421bc16f4d4307228a9951e38a984f1

- Take fewer snapshots. When a PORTAL_ONE_SELECT query is executed,
we can opportunistically reuse the parse/plan shot for the execution
phase. This cuts down the number of snapshots per simple query from
2 to 1 for the simple protocol, and 3 to 2 for the extended
protocol. Since we are only reusing a snapshot taken early in the
processing of the same protocol message, the change shouldn't be
user-visible, except that the remote possibility of the planning and
execution snapshots being different is eliminated. Note that this
change does not make it safe to assume that the parse/plan snapshot
will certainly be reused; that will currently only happen if
PortalStart() decides to use the PORTAL_ONE_SELECT strategy. It
might be worth trying to provide some stronger guarantees here in
the future, but for now we don't. Patch by me; review by Dimitri
Fontaine.
http://git.postgresql.org/pg/commitdiff/d573e239f03506920938bf0be56c868d9c3416da

- Improve behavior of concurrent CLUSTER. In the previous coding, a
user could queue up for an AccessExclusiveLock on a table they did
not have permission to cluster, thus potentially interfering with
access by authorized users who got stuck waiting behind the
AccessExclusiveLock. This approach avoids that. cluster() has the
same permissions-checking requirements as REINDEX TABLE, so this
commit moves the now-shared callback to tablecmds.c and renames it,
per discussion with Noah Misch.
http://git.postgresql.org/pg/commitdiff/cbe24a6dd8fb224b9585f25b882d5ffdb55a0ba5

- Don't forget to de-escape the password field in .pgpass. This has
been broken just about forever (or more specifically, commit
7f4981f4af1700456f98ac3f2b2d84959919ec81) and nobody noticed until
Richard Huxton reported it recently. Analysis and fix by Ross
Reedstrom, although I didn't use his patch. This doesn't seem
important enough to back-patch and is mildly backward incompatible,
so I'm just doing this in master.
http://git.postgresql.org/pg/commitdiff/8d15e3ec4fcb735875a8a70a09ec0c62153c3329

- Add a security_barrier option for views. When a view is marked as a
security barrier, it will not be pulled up into the containing
query, and no quals will be pushed down into it, so that no function
or operator chosen by the user can be applied to rows not exposed by
the view. Views not configured with this option cannot provide
robust row-level security, but will perform far better. Patch by
KaiGai Kohei; original problem report by Heikki Linnakangas (in
October 2009!). Review (in earlier versions) by Noah Misch and
others. Design advice by Tom Lane and myself. Further review and
cleanup by me.
http://git.postgresql.org/pg/commitdiff/0e4611c0234d89e288a53351f775c59522baed7c

- Catversion bump for commit 0e4611c0234d89e288a53351f775c59522baed7c.
It changed the format of stored rules.
http://git.postgresql.org/pg/commitdiff/99b60fc04e53c09ca630d2798acb3768a778dc47

- Typo fixes. All noted by Jaime Casanova.
http://git.postgresql.org/pg/commitdiff/0510b62d91151b9d8c1fe1aa15c9cf3ffe9bf25b

- Add bytea_agg, parallel to string_agg. Pavel Stehule
http://git.postgresql.org/pg/commitdiff/d5448c7d31b5af66a809e6580bae9bd31448bfa7

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Jeff Davis sent in another revision of the patch to fix GiST indexing
in range types.

Magnus Hagander sent in another revisions of the patch to allow users
to kill their own queries.

Peter Eisentraut sent in a patch to disable prompting by default in
the createuser utility.

Heikki Linnakangas sent in two revisions of a patch to move more work
outside WALInsertLock.

Phil Sorber sent in three more revision of a patch to improve relation
size functions such as pg_relation_size() to avoid producing an error
when called against a no-longer-visible relation.

Marti Raudsepp sent in a patch to enable min()/max() optimization for
the bool_and and bool_or aggregates.

Tomas Vondra sent in two revisions of a patch to track temp files in
pg_stat_database.

Alvaro Herrera sent in a WIP patch to separate the default search
order of columns from the on-disk representation.

Simon Riggs sent in a WIP patch to fix some contention issues in CLOG.

Marti Raudsepp sent in a patch to fix handling of erroneous float
values, at least on some platforms.

Andrew Dunstan sent in a patch to make pretty-printing of view
definions do something that resembles actual pretty-printing. The
previous way was quite ugly in common cases.

Tomas Vondra sent in two revisions of a patch to allow EXPLAIN ANALYZE
to instrument rows, but not timing.

Simon Riggs sent in a patch to enable 16-bit page checksums.

Alexander Björnhagen sent in a patch to add a GUC to control whether a
master configured with synchronous_commit = on is allowed to stop
waiting for standby WAL sync when all synchronous standby WAL senders
are disconnected.