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

== Postgres Weekly News - 10 agosto 2008 ==

From: rotellaro(at)gmail(dot)com
To: pgsql-it-generale <pgsql-it-generale(at)postgresql(dot)org>
Subject: == Postgres Weekly News - 10 agosto 2008 ==
Date: 2008-08-11 11:25:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-it-generale
== Postgres News prodotti ==

Rilasciato BitNami LAPPStack 1.0

Rilasciato pgbouncer 1.2.3

Rilasciato phpPgAdmin 4.2.1

Rilasciato PyReplica 1.0.3

Rilasciato Another PostgreSQL Diff Tool 1.2

Rilasciato pgSphere 1.0.1

Rilasciato PostgreSQL Toolbox 1

== 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:

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 e Devrim GUNDUZ.

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 ==
Tom Lane committed:

- Improve CREATE/DROP/RENAME DATABASE so that when failing because the
 source or target database is being accessed by other users, it tells
 you whether the "other users" are live sessions or uncommitted
 prepared transactions.  (Indeed, it tells you exactly how many of
 each, but that's mostly just because it was easy to do so.)  This
 should help forestall the gotcha of not realizing that a prepared
 transaction is what's blocking the command.  Per discussion.

- Improve SELECT DISTINCT to consider hash aggregation, as well as
 sort/uniq, as methods for implementing the DISTINCT step.  This
 eliminates the former performance gap between DISTINCT and GROUP BY,
 and also makes it possible to do SELECT DISTINCT on datatypes that
 only support hashing not sorting.  SELECT DISTINCT ON is still
 always implemented by sorting; it would take executor changes to
 support hashing that, and it's not clear it's worth the trouble.
 This is a release-note-worthy incompatibility from previous PG
 versions, since SELECT DISTINCT can no longer be counted on to
 deliver sorted output without explicitly saying ORDER BY.  (Anyone
 who can't cope with that can consider turning off enable_hashagg.)
 Several regression test queries needed to have ORDER BY added to
 preserve stable output order.  I fixed the ones that manifested
 here, but there might be some other cases that show up on other

- In pgsql/src/test/regress/pg_regress.c, fix some message style
 guideline violations in pg_regress, as well as some failures to
 expose messages for translation.

- In pgsql/src/backend/storage/buffer/bufmgr.c, in ReadOrZeroBuffer
 (and related entry points), don't bother to call PageHeaderIsValid
 when we zero the buffer instead of reading the page in. The actual
 performance improvement is probably marginal since this function
 isn't very heavily used, but a cycle saved is a cycle earned Zdenek

- Add an ORDER BY to one more SELECT DISTINCT test case, per buildfarm

- In pgsql/src/backend/optimizer/plan/planner.c, department of second
 thoughts: fix newly-added code in planner.c to make real sure that
 DISTINCT ON does what it's supposed to, ie, sort by the full ORDER
 BY list before unique-ifying.  The error seems masked in simple
 cases by the fact that query_planner won't return query pathkeys
 that only partially match the requested sort order, but I wouldn't
 want to bet that it couldn't be exposed in some way or other.

- Do not allow Unique nodes to be scanned backwards.  The code claimed
 that it would work, but in fact it didn't return the same rows when
 moving backwards as when moving forwards.  This would have no
 visible effect in a DISTINCT query (at least assuming the column
 datatypes use a strong definition of equality), but it gave entirely
 wrong answers for DISTINCT ON queries.

- Teach the system how to use hashing for UNION.  (INTERSECT/EXCEPT
 will follow, but seem like a separate patch since most of the
 remaining work is on the executor side.)  I took the opportunity to
 push selection of the grouping operators for set operations into the
 parser where it belongs.  Otherwise this is just a small exercise in
 making prepunion.c consider both alternatives.  As with the recent
 DISTINCT patch, this means we can UNION on datatypes that can hash
 but not sort, and it means that UNION without ORDER BY is no longer
 certain to produce sorted output.

- Support hashing for duplicate-elimination in INTERSECT and EXCEPT
 queries.  This completes my project of improving usage of hashing
 for duplicate elimination (aggregate functions with DISTINCT remain
 undone, but that's for some other day).  As with the previous
 patches, this means we can INTERSECT/EXCEPT on datatypes that can
 hash but not sort, and it means that INTERSECT/EXCEPT without ORDER
 BY are no longer certain to produce sorted output.

- Improve INTERSECT/EXCEPT hashing by realizing that we don't need to
 make any hashtable entries for tuples that are found only in the
 second input: they can never contribute to the output.  Furthermore,
 this implies that the planner should endeavor to put first the
 smaller (in number of groups) input relation for an INTERSECT.
 Implement that, and upgrade prepunion's estimation of the number of
 rows returned by setops so that there's some amount of sanity in the
 estimate of which one is smaller.

- In pgsql/src/backend/executor/execMain.c, install checks in executor
 startup to ensure that the tuples produced by an INSERT or UPDATE
 will match the target table's current rowtype.  In pre-8.3 releases
 inconsistency can arise with stale cached plans, as reported by
 Merlin Moncure.  (We patched the equivalent hazard on the SELECT
 side in Feb 2007; I'm not sure why we thought there was no risk on
 the insertion side.) In 8.3 and HEAD this problem should be
 impossible due to plan cache invalidation management, but it seems
 prudent to make the check anyway.  Back-patch as far as 8.0.  7.x
 versions lack ALTER COLUMN TYPE, so there seems no way to abuse a
 stale plan comparably.

- Fix corner-case bug introduced with HOT: if REINDEX TABLE pg_class
 (or a REINDEX DATABASE including same) is done before a session has
 done any other update on pg_class, the pg_class relcache entry was
 left with an incorrect setting of rd_indexattr, because the
 indexed-attributes set would be first demanded at a time when we'd
 forced a partial list of indexes into the pg_class entry, and it
 would remain cached after that.  This could result in incorrect
 decisions about HOT-update safety later in the same session.  In
 practice, since only pg_class_relname_nsp_index would be missed out,
 problem.  Per report and test case from Ondrej Jirman.

Magnus Hagander committed:

- Move pgstat.tmp into a temporary directory under $PGDATA named
 pg_stat_tmp.  This allows the use of a ramdrive (either through
 mount or symlink) for the temporary file that's written every half
 second, which should reduce I/O.  On server shutdown/startup, the
 file is written to the old location in the global directory, to
 preserve data across restarts.  Bump catversion since the $PGDATA
 directory layout changed.

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

== Patch in attesa ==

ITAGAKI Takahiro sent in a patch to user NDirectFileRead/Write
counters to get I/O counts in BufFile the module.  These counters are
visible when log_statement_stats is on.

Pavel Stehule sent in a patch to implement GROUPING SETS.

Tom Lane sent in a patch to use hashes for set operations.

Martin Pihlak sent in a patch to make dropping and re-creating
functions work more nicely with plan invalidation.

Simon Riggs sent in a patch which adds a hook for stats plugins.

Abhijit Menon-Sen sent in a patch which extend has_table_privilege()
to include sequence information.

Robert Haas sent in a patch to implement CREATE OR REPLACE VIEW.

Simon Riggs sent two revisions of a patch to fix pg_stop_backup per
suggestion by Fujii Masao.  pg_stop_backup now tests XLogArchiveCheckDone()
for both stopxlogfilename and history file and then stats the stop

Marko Kreen sent in a patch to fix a security issue in dblink.

Alvaro Herrera sent in two revisions of a patch to make autovacuum
process TOAST tables separately from main tables.

Volkan YAZICI sent in three revisions of a patch to allow people to
increase the verbosity of set-returning functions.

David Wheeler sent in some touch-ups for his citext patch.

Euler Taveira de Oliveira sent in a patch which allows symlinking
statistics files at initdb time.

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

pgsql-it-generale by date

Next:From: rotellaroDate: 2008-08-18 07:20:47
Subject: == Postgres Weekly News - 17 agosto 2008 ==
Previous:From: rotellaroDate: 2008-08-04 08:01:23
Subject: == Postgres Weekly News - 3 agosto 2008 ==

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