PostgreSQL Weekly News – August 08 2010

== PostgreSQL Weekly News – August 08 2010 ==

Bruce Momjian’s will walk through PostgreSQL 9.0 at the next NYC
PostgreSQL Meetup, August 10th at 6:00 pm. RSVP below:

== PostgreSQL Product News ==

Npgsql 2.0.10, a .NET data provider for PostgreSQL, released.

== PostgreSQL Jobs for August ==

== PostgreSQL Local ==

FrOSCon 2010 will take place in St. Augustin, Germany on August 21-22,
2010. The German PostgreSQL User Group got his own devroom and is
looking for talks. More info:

The Call for Papers for West is open until September 5, 2010. Details at:

== PostgreSQL in the News ==

Planet PostgreSQL:

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, German language
to, Italian language to Spanish language

== Reviews ==

Brendan Jurd reviewed the patch to reduce the on-disk size of NUMERIC.

KaiGai Kohei reviewed Robert Haas’s patch to refactor comment.c

== Applied Patches ==

Simon Riggs committed:

– Reduce lock levels of CREATE TRIGGER and some ALTER TABLE, CREATE
RULE actions. Avoid hard-coding lockmode used for many altering DDL
commands, allowing easier future changes of lock levels.
Implementation of initial analysis on DDL sub-commands, so that many
lock levels are now at ShareUpdateExclusiveLock or
ShareRowExclusiveLock, allowing certain DDL not to block
reads/writes. First of number of planned changes in this area;
additional docs required when full project complete.

Robert Haas committed:

– Remove ancient PL/pgsql line numbering hack. While this hack
arguably has some benefit in terms of making PL/pgsql’s line
numbering match the programmer’s expectations, it also makes
PL/pgsql inconsistent with the remaining PLs, making it difficult
for clients to reliably determine where the error actually is. On
balance, it seems better to be consistent. Pavel Stehule

– Add btree_gist support for searching on “not equals”. Jeff Davis,
with slight editorialization by me.

– In contrib/fuzzystrmatch, teach levenshtein() about multi-byte
characters. Based on a patch by, and further ideas from, Alexander

– In pgsql/src/backend/commands/tablecmds.c, fix inheritance count
tracking in ALTER TABLE .. ADD CONSTRAINT. Without this patch,
constraints inherited by children of a parent table which itself has
multiple inheritance parents can end up with the wrong coninhcount.
After dropping the constraint, the children end up with a leftover
copy of the constraint that is not dumped and cannot be dropped.
There is a similar problem with ALTER TABLE .. ADD COLUMN, but that
looks significantly more difficult to resolve, so I’m committing
this fix separately. Back-patch to 8.4, which is the first release
that has coninhcount. Report by Hank Enting.

– Regression tests for new btree_gist “not equals” support. Jeff
Davis, with minor adjustments by me.

– In pgsql/src/backend/utils/adt/numeric.c, allow numeric to use a
more compact, 2-byte header in many cases. Review by Brendan Jurd
and Tom Lane.

– In pgsql/src/backend/utils/adt/numeric.c, fix numeric_maximum_size()
calculation. The old computation can sometimes underestimate the
necessary space by 2 bytes; however we’re not back-patching this,
because this result isn’t used for anything critical. Per
discussion with Tom Lane, make the typmod test in this function
match the ones in numeric() and apply_typmod() exactly.

– In pgsql/src/include/utils/numeric.h, fix declared argument name for
numeric_maximum_size. The previous commit changed the function to
say ‘typmod’ rather than ‘typemod’, but I forgot to update the
header file.

– Standardize get_whatever_oid functions for object types with
unqualified names: 1. Add a missing_ok parameter to
get_tablespace_oid. 2. Avoid duplicating get_tablespace_od guts in
objectNamesToOids. 3. Add a missing_ok parameter to
get_database_oid. 4. Replace get_roleid and get_role_checked with
get_role_oid. 5. Add get_namespace_oid, get_language_oid,
get_am_oid. 6. Refactor existing code to use new interfaces.
Thanks to KaiGai Kohei for the review.

– Standardize get_whatever_oid functions for other object types: 1.
Rename TSParserGetPrsid to get_ts_parser_oid. 2. Rename
TSDictionaryGetDictid to get_ts_dict_oid. 3. Rename
TSTemplateGetTmplid to get_ts_template_oid. 4. Rename
TSConfigGetCfgid to get_ts_config_oid. 5. Rename
FindConversionByName to get_conversion_oid. 6. Rename
GetConstraintName to get_constraint_oid. 7. Add new functions
get_opclass_oid, get_opfamily_oid, get_rewrite_oid,
get_rewrite_oid_without_relid, get_trigger_oid, and get_cast_oid.
The name of each function matches the corresponding catalog. Thanks
to KaiGai Kohei for the review.

– In pgsql/src/backend/utils/cache/syscache.c, fix incorrect pathname
in comment.

– In pgsql/doc/src/sgml/release-9.0.sgml, rearrange “big features”
section of the release notes. Josh Berkus

Tom Lane committed:

– Fix core dump in QTNodeCompare when tsquery_cmp() is applied to two
empty tsqueries. CompareTSQ has to have a guard for the case rather
than blindly applying QTNodeCompare to random data past the end of
the datums. Also, change QTNodeCompare to be a little less
trusting: use an actual test rather than just Assert’ing that the
input is sane. Problem encountered while investigating another
issue (I saw a core dump in autoanalyze on a table containing
multiple empty tsquery values). Back-patch to all branches with
tsquery support. In HEAD, also fix some bizarre (though not
outright wrong) coding in tsq_mcontains().

– Be a little more careful with the shift computations in QT2QTN and
makeTSQuerySign. The first of these is a live bug, on some
platforms, as per bug #5590 from John Regehr. However the
consequences seem limited because of the relatively narrow scope of
use of QTNode.sign. The shift in makeTSQuerySign is actually safe
because TSQS_SIGLEN is unsigned, but it seems like a good idea to
insert an explicit cast rather than depend on that.

– In pgsql/doc/src/sgml/btree-gist.sgml, fix markup, other minor
editing for recent btree_gist doc changes.

– In pgsql/src/backend/utils/adt/nabstime.c, add some comments to
tinterval_cmp_internal pointing out its severe implementation
deficiencies. Per discussion of bug #5592, we’re not going to
change it, but these things should be documented so that if anyone
ever reimplements type tinterval, they will be more careful.

– Kibitzing on \conninfo patch: adjust the order of field output to
match the parameters of \connect, and fix oversight of not enabling
translation of the messages. Also, adjust \connect’s similar
messages to match, and deal with 8.2-era violation of basic
translatability guidelines there.

– In pgsql/doc/src/sgml/config.sgml, fix mangled grammar.

– Code review for –quote-all-identifiers patch: add missing –help
documentation for new pg_dump/pg_dumpall parameters, make a couple
of trivial stylistic adjustments to make the code follow usual
project style.

– Replace the naive HYPOT() macro with a standards-conformant
hypotenuse function. This avoids unnecessary overflows and probably
gives a more accurate result as well. Paul Matthews, reviewed by
Andrew Geery

– In pgsql/doc/src/sgml/syntax.sgml, add an example to clarify the use
of ORDER BY in multiple-argument aggregates. People seem to not get
this right without help.

– In pgsql/doc/src/sgml/libpq.sgml, fix sloppy mistakes in
documentation of PQescapeLiteral and PQescapeIdentifier. Noted by
Dmitriy Igrishin.

– In pgsql/doc/src/sgml/syntax.sgml, forgot to back-patch earlier
change to documentation for aggregate ORDER BY clauses. Tags: —-

– Remove the single-argument form of string_agg(). It added nothing
much in functionality, while creating an ambiguity in usage with
ORDER BY that at least two people have already gotten seriously
confused by. Also, add an opr_sanity test to check that we don’t in
future violate the newly minted policy of not having built-in
aggregates with the same name and different numbers of parameters.
Per discussion of a complaint from Thom Brown.

– In pgsql/src/backend/parser/parse_func.c, add a very specific hint
for the case that we’re unable to locate a function matching a call
like f(x, ORDER BY y,z). It could be that what the user really
wants is f(x,z ORDER BY y). We now have pretty conclusive evidence
that many people won’t understand this problem without concrete
guidance, so give it to them. Per further discussion of the
string_agg() problem.

– In pgsql/src/backend/utils/misc/guc.c, add a comment about the
initial default value of max_stack_depth.

– In pgsql/doc/src/sgml/release-9.0.sgml, fix inaccurate description
of deferrable unique constraints, per Dean Rasheed.

– In pgsql/doc/src/sgml/release-9.0.sgml, let’s put that </link> in a
sane place …

– Recognize functional dependency on primary keys. This allows a
table’s other columns to be referenced without listing them in GROUP
BY, so long as the primary key column(s) are listed in GROUP BY.
Eventually we should also allow functional dependency on a UNIQUE
constraint when the columns are marked NOT NULL, but that has to
wait until NOT NULL constraints are represented in pg_constraint,
because we need to have pg_constraint OIDs for all the conditions
needed to ensure functional dependency. Peter Eisentraut, reviewed
by Alex Hunsaker and Tom Lane.

– Add stats functions and views to provide access to a transaction’s
own statistics counts. These numbers are being accumulated but
haven’t yet been transmitted to the collector (and won’t be, until
the transaction ends). For some purposes, though, it’s handy to be
able to look at them. Joel Jacobson, reviewed by Itagaki Takahiro

– Add an xpath_exists() function. This is equivalent to XMLEXISTS
except that it offers support for namespace mapping. Mike Fowler,
reviewed by David Fetter

Bruce Momjian committed:

– In pgsql/doc/src/sgml/ref/explain.sgml, add EXPLAIN documentation
example. Gabrielle Roth.

– In pgsql/doc/src/sgml/release-9.0.sgml, fix 9.0 release notes vacuum
mention, not -> now. Peter Fokkinga.

Peter Eisentraut committed:

– Add xmlexists function by Mike Fowler, reviewed by Peter Eisentraut.

– In pgsql/doc/src/sgml/plpython.sgml, document which Python
environment variables affect PL/Python

– In pgsql/doc/src/sgml/xfunc.sgml, fix indexterm spelling.

== Rejected Patches (for now) ==

No one was disappointed this week 🙂

== Pending Patches ==

Mike Fowler sent in another revision of the xml_is_well_formed()

Robert Haas and Yeb Havinga traded patches fixing the issue with
multiply-inherited columns.

Tom Lane sent in a patch to fix an issue with ANALYZE in the case of
expression indexes with opckeytype that is not the default one.

Pavel Stehule and Robert Haas traded patches for the \ef [line number]
and \sf (show function) feature in psql.

Zoltan Boszormenyi sent in another revision of the lock_timeout patch.

Pavel Stehule sent in two more revisions of the xslt with no limits

Josh Tolley, David Fetter and Pavel Stehule sent in new revisions of
the GROUPING SETS patch.

Florian Pflug sent in another revision of the “true serialization”

Zoltan Boszormenyi sent in a patch to fix ECPG in the case of dynamic
cursor names, which did not exist before 9.0.

Florian Pflug sent in two more revisions of the patch to show
individual statement latencies in pgbench output.

Boxuan Zhai, Heikki Linnakangas and Simon Riggs traded patches which
implement, describe and test MERGE.

Simon Riggs sent in a detailed specification for MERGE.

Dean Rasheed sent in a WIP patch to add triggers for VIEWs.

Jan Otto sent in a patch to fix and update contrib/isn.

Fujii Masao sent in another patch to help with synchronous

Heikki Linnakangas sent in a patch to fix an infelicity in EXECUTE …
USING with unknown parameters.

Robert Haas sent in a patch to refactor comment.c, partly as a preface
to adding a SECURITY LABEL command.

Tom Lane sent in another revision of the gincostestimate patch.

Kris Jurka sent in another revision of the patch to fix JDBC in
certain situations with COPY IN.

Tom Lane sent in a patch with a different approach to the
gincostestimate issue.

Peter Eisentraut sent in a patch to add the following functionalities

Mark Wong sent in a WIP patch to implement parallel quicksort.

Comments are closed.