Press Release Draft - 2016-02-09 Cumulative Update
Hi!
Below is the draft of the press release for the update this Thursday:
https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.md;h=0cccb8986c08527f65f13d704a78c36bb8de7fef;hb=afc01091dea8a1597e8e21430edc3908c581ce0c <https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.md;h=0cccb8986c08527f65f13d704a78c36bb8de7fef;hb=afc01091dea8a1597e8e21430edc3908c581ce0c>
As there are a lot of updates I did my best to consolidate some of the bullet points and as usual, people are directed to the release notes. Please let me know if there are any inaccuracies so I can fix them ASAP.
Thanks!
Jonathan
As there are a lot of updates I did my best to consolidate some of the
bullet points and as usual, people are directed to the release notes.
Please let me know if there are any inaccuracies so I can fix them ASAP.
Just some minor points:
* Several fixes for PostgreSQL operating in hot standby mode
It sounded unnatural to me. Maybe it is better without "PostgreSQL".
* Several vacuum and autovacuum fxies
Typo
* Several Unicode fixes
It sounded alarming to me. I see just one related item on the release
notes. Maybe we can clarify the problem.
* Sync our copy of the timezone library with IANA release tzcode2016j
This is repeated on the following sentence.
BEGIN;
DROP INDEX bad_index_name;
CREATE INDEX CONCURRENTLY bad_index_name ON table_name (column_name); /* replace names with your original index definition */
COMMIT;
Maybe you meant CREATE INDEX without CONCURRENTLY?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi,
Am Dienstag, den 07.02.2017, 10:37 -0500 schrieb Jonathan S. Katz:
Below is the draft of the press release for the update this Thursday:
About the CREATE INDEX CONCURRENTLY issue, I wonder whether Peter's
amcheck extension[1]https://github.com/petergeoghegan/amcheck -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.banck@credativ.de would catch that (for B-Tree indexes at least), and
if that is the case, whether we could mention that to our users as
guidance for how to check for index corruption?
Michael
[1]: https://github.com/petergeoghegan/amcheck -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.banck@credativ.de
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de
credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Michael Banck wrote:
Hi,
Am Dienstag, den 07.02.2017, 10:37 -0500 schrieb Jonathan S. Katz:
Below is the draft of the press release for the update this Thursday:
About the CREATE INDEX CONCURRENTLY issue, I wonder whether Peter's
amcheck extension[1] would catch that (for B-Tree indexes at least), and
if that is the case, whether we could mention that to our users as
guidance for how to check for index corruption?
"it does not verify that the target index is consistent with its heap
relation"
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jonathan S. Katz wrote:
Below is the draft of the press release for the update this Thursday:
https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.md;h=0cccb8986c08527f65f13d704a78c36bb8de7fef;hb=afc01091dea8a1597e8e21430edc3908c581ce0c <https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.md;h=0cccb8986c08527f65f13d704a78c36bb8de7fef;hb=afc01091dea8a1597e8e21430edc3908c581ce0c>
As there are a lot of updates I did my best to consolidate some of the bullet points and as usual, people are directed to the release notes. Please let me know if there are any inaccuracies so I can fix them ASAP.
Please do post the proposed text on list for ease of review. I wasn't
looking at the text, so I wouldn't have noticed this if Emre hadn't
replied:
76 If you believe you have been affected by the aforementioned CREATE INDEX CONCURRENTLY bug, you will have to rebuild the index. An example of rebuilding an index:
77
78 BEGIN;
79 DROP INDEX bad_index_name;
80 CREATE INDEX CONCURRENTLY bad_index_name ON table_name (column_name); /* replace names with your original index definition */
81 COMMIT;
This is not a good recipe, because using CREATE INDEX CONCURRENTLY in
the same transaction that grabs an exclusive lock on the table for the
DROP INDEX is pointless -- the access exclusive lock is held until the
end of the transaction, and CIC does not work inside a transaction
anyway so it'd raise an ERROR and rollback the DROP INDEX. So the user
would probably drop the BEGIN/COMMIT sequence in order for this to work
in the first place. (The other option is to use CREATE INDEX not
concurrent, but that would lock the table for a very long time).
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Feb 7, 2017, at 11:25 AM, Emre Hasegeli <emre@hasegeli.com> wrote:
As there are a lot of updates I did my best to consolidate some of the
bullet points and as usual, people are directed to the release notes.
Please let me know if there are any inaccuracies so I can fix them ASAP.Just some minor points:
* Several fixes for PostgreSQL operating in hot standby mode
It sounded unnatural to me. Maybe it is better without "PostgreSQL".
* Several vacuum and autovacuum fxies
Typo
* Several Unicode fixes
It sounded alarming to me. I see just one related item on the release
notes. Maybe we can clarify the problem.* Sync our copy of the timezone library with IANA release tzcode2016j
This is repeated on the following sentence.
BEGIN;
DROP INDEX bad_index_name;
CREATE INDEX CONCURRENTLY bad_index_name ON table_name (column_name); /* replace names with your original index definition */
COMMIT;Maybe you meant CREATE INDEX without CONCURRENTLY?
Thanks for the corrections / suggestions. I have applied them and will push a new version shortly!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Feb 7, 2017, at 12:44 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Jonathan S. Katz wrote:
Below is the draft of the press release for the update this Thursday:
https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.md;h=0cccb8986c08527f65f13d704a78c36bb8de7fef;hb=afc01091dea8a1597e8e21430edc3908c581ce0c <https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.md;h=0cccb8986c08527f65f13d704a78c36bb8de7fef;hb=afc01091dea8a1597e8e21430edc3908c581ce0c>
As there are a lot of updates I did my best to consolidate some of the bullet points and as usual, people are directed to the release notes. Please let me know if there are any inaccuracies so I can fix them ASAP.
Please do post the proposed text on list for ease of review. I wasn't
looking at the text, so I wouldn't have noticed this if Emre hadn't
replied:76 If you believe you have been affected by the aforementioned CREATE INDEX CONCURRENTLY bug, you will have to rebuild the index. An example of rebuilding an index:
77
78 BEGIN;
79 DROP INDEX bad_index_name;
80 CREATE INDEX CONCURRENTLY bad_index_name ON table_name (column_name); /* replace names with your original index definition */
81 COMMIT;This is not a good recipe, because using CREATE INDEX CONCURRENTLY in
the same transaction that grabs an exclusive lock on the table for the
DROP INDEX is pointless -- the access exclusive lock is held until the
end of the transaction, and CIC does not work inside a transaction
anyway so it'd raise an ERROR and rollback the DROP INDEX. So the user
would probably drop the BEGIN/COMMIT sequence in order for this to work
in the first place. (The other option is to use CREATE INDEX not
concurrent, but that would lock the table for a very long time).
Thanks for the clarification. I have updated the recipe along with Emre’s comments here:
https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.txt;h=f90d4716f240dbea4cca647b099f79865545b633;hb=d85498c284275bcab4752b91476834de780648b8 <https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.txt;h=f90d4716f240dbea4cca647b099f79865545b633;hb=d85498c284275bcab4752b91476834de780648b8>
Thanks!
Jonathan
Jonathan S. Katz wrote:
Thanks for the clarification. I have updated the recipe along with Emre’s comments here:
[updated text not included in the email]
I still don't think the recipe is a very good one because it leaves you
with a window where the affected columns are not indexed at all.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi,
Am Dienstag, den 07.02.2017, 15:58 -0500 schrieb Jonathan S. Katz:
It says "[...]then rows that were updated by transactions running at the
same time as the CREATE INDEX CONCURRENTLY command could have been index
incorrectly."
That sounds off to me, shouldn't it be "indexed incorrectly" or
something?
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de
credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Feb 7, 2017, at 4:07 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Jonathan S. Katz wrote:
Thanks for the clarification. I have updated the recipe along with Emre’s comments here:
[updated text not included in the email]
I still don't think the recipe is a very good one because it leaves you
with a window where the affected columns are not indexed at all.
Okay, so I propose two options:
1. Does anyone have a recipe they recommend that might be better? OR
2. We just leave out the recipe altogether (which is what I am leaning towards at the moment).
Thanks!
Jonathan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Feb 7, 2017, at 4:39 PM, Michael Banck <michael.banck@credativ.de> wrote:
Hi,
Am Dienstag, den 07.02.2017, 15:58 -0500 schrieb Jonathan S. Katz:
It says "[...]then rows that were updated by transactions running at the
same time as the CREATE INDEX CONCURRENTLY command could have been index
incorrectly."That sounds off to me, shouldn't it be "indexed incorrectly" or
something?
Yes, passive voice :( I’ve made the modification on my local copy and will push it up after the resolution on the CREATE INDEX recipe.
Jonathan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jonathan S. Katz wrote:
On Feb 7, 2017, at 4:07 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Jonathan S. Katz wrote:
Thanks for the clarification. I have updated the recipe along with Emre’s comments here:
[updated text not included in the email]
I still don't think the recipe is a very good one because it leaves you
with a window where the affected columns are not indexed at all.Okay, so I propose two options:
1. Does anyone have a recipe they recommend that might be better? OR
Do the CREATE INDEX CONCURRENTLY first, then DROP INDEX CONCURRENTLY the
old index, then rename the new index to the old name.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Feb 7, 2017, at 6:40 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Jonathan S. Katz wrote:
On Feb 7, 2017, at 4:07 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Jonathan S. Katz wrote:
Thanks for the clarification. I have updated the recipe along with Emre’s comments here:
[updated text not included in the email]
I still don't think the recipe is a very good one because it leaves you
with a window where the affected columns are not indexed at all.Okay, so I propose two options:
1. Does anyone have a recipe they recommend that might be better? OR
Do the CREATE INDEX CONCURRENTLY first, then DROP INDEX CONCURRENTLY the
old index, then rename the new index to the old name.
Cool. Updated:
https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.txt;h=56551b56eb1f70e734c5762abf5ddf6bd6965fdb;hb=c1b7f9c0cfd0ff106409d14a36c3122a8ee460d0 <https://git.postgresql.org/gitweb/?p=press.git;a=blob;f=update_releases/current/20170209updaterelease.txt;h=56551b56eb1f70e734c5762abf5ddf6bd6965fdb;hb=c1b7f9c0cfd0ff106409d14a36c3122a8ee460d0>
I added a note to alert people to disk space usage utilizing this method.
Thanks for the help!
Jonathan.
Am 07.02.2017 um 18:44 schrieb Alvaro Herrera <alvherre@2ndquadrant.com>:
80 CREATE INDEX CONCURRENTLY bad_index_name ON table_name (column_name); /* replace names with your original index definition */
I was thinking if we could replace that "replace names with your original index definition" with something more fancy using pg_get_indexdef in that recipe. I ended up with quite a "REINDEX CONCURRENTLY" monster:
\set index_name 'my_bad_index'
\set table_schema 'public'
SELECT :'index_name'||'_'||left(md5(random()::text), 5) AS index_name_tmp \gset
SELECT replace(replace(pg_get_indexdef((quote_ident(:'table_schema')||'.'||quote_ident(:'index_name'))::regclass), 'INDEX '||quote_ident(:'index_name'), 'INDEX '||quote_ident(:'index_name_tmp')), 'CREATE INDEX', 'CREATE INDEX CONCURRENTLY') \gexec
DROP INDEX CONCURRENTLY :"table_schema".:"index_name";
ALTER INDEX :"table_schema".:"index_name_tmp" RENAME TO :"index_name";
Probably not useable as a recipe in such an announcement but it was fun to build and to see what is actually possible with some psql magic :)
Tobias
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Tobias Bussmann wrote:
Am 07.02.2017 um 18:44 schrieb Alvaro Herrera <alvherre@2ndquadrant.com>:
80 CREATE INDEX CONCURRENTLY bad_index_name ON table_name (column_name); /* replace names with your original index definition */
I was thinking if we could replace that "replace names with your original index definition" with something more fancy using pg_get_indexdef in that recipe. I ended up with quite a "REINDEX CONCURRENTLY" monster:
\set index_name 'my_bad_index'
\set table_schema 'public'
SELECT :'index_name'||'_'||left(md5(random()::text), 5) AS index_name_tmp \gset
SELECT replace(replace(pg_get_indexdef((quote_ident(:'table_schema')||'.'||quote_ident(:'index_name'))::regclass), 'INDEX '||quote_ident(:'index_name'), 'INDEX '||quote_ident(:'index_name_tmp')), 'CREATE INDEX', 'CREATE INDEX CONCURRENTLY') \gexec
DROP INDEX CONCURRENTLY :"table_schema".:"index_name";
ALTER INDEX :"table_schema".:"index_name_tmp" RENAME TO :"index_name";Probably not useable as a recipe in such an announcement but it was fun to build and to see what is actually possible with some psql magic :)
Note that this is likely to fail if the original index name is close to
the 63 chars limit. Perhaps it's enough to add substring() when
computing index_name_tmp. (You could just not use :'index_name' there
and rely on the random md5 only, actually). Watch out for UNIQUE too.
FWIW for previous problems we've documented them in wiki pages along
with suggested solutions, and added a link to that wiki page in the
announce. Perhaps one thing to do is create a wiki page for this one
too (not volunteering myself). Probably too late to add the link to the
press release now, since it's already out as "final".
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Am 08.02.2017 um 20:17 schrieb Alvaro Herrera <alvherre@2ndquadrant.com>:
Note that this is likely to fail if the original index name is close to
the 63 chars limit. Perhaps it's enough to add substring() when
computing index_name_tmp. (You could just not use :'index_name' there
and rely on the random md5 only, actually). Watch out for UNIQUE too.
thank you for your valuable input! Here is a version that should take both into account - the query also could be simplified a bit:
\set index_name 'my_bad_index'
\set table_schema 'public'
SELECT 'tmp_'||md5(random()::text) AS index_name_tmp \gset
SELECT replace(pg_get_indexdef((quote_ident(:'table_schema')||'.'||quote_ident(:'index_name'))::regclass), ' '||quote_ident(:'index_name')||' ON', ' CONCURRENTLY '||:'index_name_tmp'||' ON') \gexec
DROP INDEX CONCURRENTLY :"table_schema".:"index_name";
ALTER INDEX :"table_schema".:"index_name_tmp" RENAME TO :"index_name";
FWIW for previous problems we've documented them in wiki pages along
with suggested solutions, and added a link to that wiki page in the
announce. Perhaps one thing to do is create a wiki page for this one
too (not volunteering myself).
I'm not even remotely into the details of the CIC issue, so I'm not the right one to create a page on that topic. But I could put this snippet as a "REINDEX CONCURRENTLY" workaround into the Administrative Snippets category of the wiki, if there are no further objections about the way it works. I always have a bit of mixed feelings with these kind of string manipulations on dynamic SQL.
Best,
Tobias
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Tobias Bussmann wrote:
But I could put this
snippet as a "REINDEX CONCURRENTLY" workaround into the Administrative
Snippets category of the wiki, if there are no further objections
about the way it works.
Sounds like a good idea. There are further complications:
* you can't DROP indexes belonging to constraints, so this recipe
doesn't work for them. One useful trick is to create the index first,
then ADD CONSTRAINT USING INDEX.
* For unique constraints referenced by FKs, the above doesn't work
either. One thing you can do is create a second index and swap the
relfilenode underneath. This is a nasty, dirty, dangerous, unsupported
trick, but it can save people's neck at times.
I always have a bit of mixed feelings with these kind of string
manipulations on dynamic SQL.
It may look a bit nasty, but locking tables for long periods (or being
without an important index for a period) is much worse in production
scenarios.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2/8/17 2:51 PM, Alvaro Herrera wrote:
I always have a bit of mixed feelings with these kind of string
manipulations on dynamic SQL.It may look a bit nasty, but locking tables for long periods (or being
without an important index for a period) is much worse in production
scenarios.
I think posting a recipe in the wiki is a great idea (especially if it
handles corner cases like constraints). I'm not so keen on trying to
code it entirely in psql though. I think it'd be a lot cleaner to have a
plpgsql function that uses format() to construct the appropriate
commands to run and then spit that out as text. Users can either cut and
paste that, or they can \gset it to a variable that they then execute,
or they can capture the output to a file which they execute.
The big advantage to this is by default you see what commands would be
run, but you can still fully automate if you want to without much extra
effort.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2/7/17 9:37 AM, Jonathan S. Katz wrote:
Below is the draft of the press release for the update this Thursday:
Thanks for the work on this!
11 There existed a race condition if CREATE INDEX CONCURRENTLY was
called on a column that had not been indexed before, then rows that were
updated by transactions running at the same time as the CREATE INDEX
CONCURRENTLY command could have been indexed incorrectly.
I think that'd read better as
11 There existed a race condition /where/ if CREATE INDEX
CONCURRENTLY was called on a column that had not been indexed before,
then rows that were updated by transactions running at the same time as
the CREATE INDEX CONCURRENTLY command /may not/ have been indexed
incorrectly.
Also, maybe we should mention that there's no way to test for this, and
make a stronger suggestion to redo any affected indexes?
20 These release contains several fixes to improve the stability of
visible data and WAL logging that we wish to highlight here.
I think this sentence can just go. If we want to keep it, IMHO this is a
better alternative: "This release contains several improvements to the
stability of data visibility and WAL logging."
22 Prior to this release, data could be prematurely pruned by a
vacuum operation when a special snapshot used for catalog scans was
presently available.
... vacuum operation even though a special catalog scan snapshot was in use.
BTW, I don't know what came out of the discussion of git references in
release notes, but I'd find it useful to be able to at least get a
complete list. Not hard for me to do that since I know git and our
naming scheme, but maybe we should include directions for doing so?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Feb 8, 2017 at 5:31 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
11 There existed a race condition /where/ if CREATE INDEX CONCURRENTLY was
called on a column that had not been indexed before, then rows that were
updated by transactions running at the same time as the CREATE INDEX
CONCURRENTLY command /may not/ have been indexed incorrectly.
I think this is moot at this point, but "may not have been indexed
incorrectly" seems to have two negatives where there should be only
one.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers