es for most Linux systems). This
can verify that the heap is consistent with indexes.
--
Peter Geoghegan
On Mon, Mar 19, 2018 at 1:01 PM, Jeremy Finzel wrote:
> SELECT heap_page_items(get_raw_page('pg_authid', 7));
Can you post this?
SELECT * FROM page_header(get_raw_page('pg_authid', 7));
--
Peter Geoghegan
a guess, but it can't hurt to check if the contents
of the tuples themselves are actually sane by inspecting them with
"SELECT * FROM pg_authid". heap_page_items() doesn't actually care
about the shape of the tuples in the page, so this might have been
missed.
--
Peter Geoghegan
rows for a target
index (in addition to testing the structure of a target B-Tree index
itself). This is probably the best general test for corruption that is
available. There is a fair chance that this will reveal new
information.
--
Peter Geoghegan
make
time for that. If you can generalize from the example query that calls
the bt_index_check() function, but set
"heapallindexed=>i.indisprimary" and remove "n.nspname =
'pg_catalog'", as well as "LIMIT 10". This will test tables and
indexes from all schemas, which might be interesting.
--
Peter Geoghegan
many LVM + Postgres setups
will involve multiple logical volumes. This makes it possible for a
small inconsistency across logical volumes to corrupt data.
I don't know anything about your SAN snapshotting, but this is at
least something to consider.
--
Peter Geoghegan
t yet have
the necessary debuginfo repos set up. Just a guess. That is sometimes
a required extra step.
[1] https://postgr.es/m/7369.1520528...@sss.pgh.pa.us
--
Peter Geoghegan
t; elsewhere?
I would look into this suspected 9.5 regression, if that's possible:
https://postgr.es/m/CAH2-Wz=sfakvmv1x9jh19ej8am8tzn9f-yecips9hrrrqss...@mail.gmail.com
--
Peter Geoghegan
ug (in which case the checksum will be correct).
You can also run amcheck. Get the version targeting earlier Postgres
releases off Github (there are packages for most Linux systems). This
can verify that the heap is consistent with indexes.
--
Peter Geoghegan
On Mon, Apr 9, 2018 at 5:55 PM, Alexandre Arruda wrote:
> I ran amcheck in all index of a table and I only get empty returns.
Did you try doing so with the "heapallindexed" option? That's what's
really interesting here.
--
Peter Geoghegan
/freezing. The only heap_freeze_tuple() caller is code used by
CLUSTER, so it's not that hard to imagine a MultiXact freezing bug
that is peculiar to CLUSTER. Though I haven't thought about it in much
detail.
--
Peter Geoghegan
7;t think that that's what this is, since this error occurs
within heap_freeze_tuple() -- it's not the over-enforced
HEAP_XMAX_IS_LOCKED_ONLY() error within heap_prepare_freeze_tuple().
And, because this database wasn't pg_upgraded.
I should wait until tomorrow before doing any further analysis, though.
--
Peter Geoghegan
wed this error, though?
Have you noticed any data loss? Things look okay when you do your dump
+ restore, right? The problem, as far as you know, is strictly that
CLUSTER + VACUUM refuse to finish/raise these multixactid errors?
--
Peter Geoghegan
ase? You'll need to be able to
install the pageinspect contrib module.
--
Peter Geoghegan
h is that the git logo
links to https://git.postgresql.org/gitweb/, which has many
non-very-important git repos. Instead, it should point to the main
PostgreSQL repository's gitweb page, which is at
https://git.postgresql.org/gitweb/?p=postgresql.git.
--
Peter Geoghegan
survey.
This sounds pretty far from constructive to me, which automatically
detracts from what you're saying.
--
Peter Geoghegan
er
>> about this one in HeapTupleSatisfiesMVCC:
>
> I suggest raising this on -hackers. I agree that it's unfortunate.
I wonder if BootstrapTransactionId also needs to be considered here.
--
Peter Geoghegan
ovides the fastest possible access to
a crowdsourced answer, without requiring or even encouraging
participation.
--
Peter Geoghegan
sons. You can say the same thing about
any position of leadership or authority within the community, though.
That hasn't really been much of a problem in my experience, and I see
no reason for particular concern about it here.
--
Peter Geoghegan
ast until now. You can make exactly the same slippery
slope argument against that.
--
Peter Geoghegan
nity members who have a strong belief in the CoC (and I don't mean that
> kindly)?
The CoC states that the committee's members cannot come from the core team.
--
Peter Geoghegan
to that.
We have a pretty good track record through totally informal standards
for behavior. Setting a good example is absolutely essential. While
that's still the most important thing, it doesn't seem particularly
scalable on its own.
--
Peter Geoghegan
. Let's try to keep our standards here.
Whose standards are these? By my count, the majority of e-mails you've
ever sent to a PostgreSQL mailing list have been sent in the last 2
days, to this code of conduct thread.
--
Peter Geoghegan
formality, means of enforcement, etc.
Naturally, the rules across disparate groups vary widely for all kinds
of reasons. Formalizing and being more transparent about how this
works seems like the opposite of paternalism to me.
--
Peter Geoghegan
lica has incompatible collation rules, given that it uses a totally
different OS.
--
Peter Geoghegan
+ exceptions end up calling std::abort(). I've seen bugs
in modules like PL/V8 that were caused by this. The symptom was a
mysterious message in the logs about SIGABRT. Perhaps that's what
happened here?
What extensions are installed, if any?
--
Peter Geoghegan
ably unusable.
Do you find that the issue goes away if you set
max_parallel_maintenance_workers=0 on v11/master?
--
Peter Geoghegan
gt;
>
> I wonder why we don't just generally trigger invalidations to an
> indexes' "owning" relation in CacheInvalidateHeapTuple()?
I don't know, but that seems like a good question.
--
Peter Geoghegan
. ISTM
> there's some risks that it'd cause issues. Will you tackle this?
Okay.
--
Peter Geoghegan
;
> Is there a way to get this to work right ?
If you're using v10 with ICU, then you can create a custom ICU
collation for this, with "natural" sort order. Something like this
should work:
CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
See the docs -- "23.2.2.3.2. ICU collations".
--
Peter Geoghegan
nking I could use an index, the
> PK for instance, and see if it references these pages.
Ir'a probably not serious, but you may want to try amcheck's
heapallindexed check. You'll have to use the non-contrib packages for
that right now, though, but those are available from the PGDG repos.
--
Peter Geoghegan
been distracted by
project work. I accept responsibility for the open item, though.
--
Peter Geoghegan
quietly and painlessly.
> The truth is absolute and cannot be changed.
> Perception is not the truth.
> Flerp!
I cannot imagine what reaction you were expecting to this. In all
sincerity, I suggest reflecting on your words. You don't seem to have
realistic expectations about ho
(), not CacheInvalidateHeapTuple()).
Since nobody seems to be that excited about the
CacheInvalidateHeapTuple() idea, I haven't pursued it.
--
Peter Geoghegan
0001-Add-table-relcache-invalidation-to-index-builds.patch
Description: Binary data
ract
already.
> I wonder if it wouldn't be more appropriately placed closer to the
> UpdateIndexRelation(), given that that's essentially what necessitates
> the relcache flush?
That makes sense. I'll do it that way.
--
Peter Geoghegan
istering invalidations for coherency.
Fair enough. How about the attached revision?
--
Peter Geoghegan
v2-0001-Add-table-relcache-invalidation-to-index-builds.patch
Description: Binary data
lot
better than either the status quo, or a platitude about inclusivity.
--
Peter Geoghegan
something
> somewhere that some other tweet disagreed with on faceplant"?
>
> Great plan if you do for-pay postgresql support for the living.
You can make your own conclusions about my motivations, just as I'll
make my own conclusions about yours. I'm not going to engage with you
on either, though.
--
Peter Geoghegan
ill respect it.
In all sincerity, if you're compelled to walk away from participating
in mailing list discussions on a point of principle, then I wish you
well. That is your right.
--
Peter Geoghegan
beta2 run, but it sounds as though maybe it could be relevant to
> this kind of workload - Is that so?
You seem to be worried about keeping indexes as small as possible.
vacuum_cleanup_index_scale_factor won't help with that.
--
Peter Geoghegan
nt release of 9.4 -- not
9.4.6. You're missing years of bug fixes by sticking to such an old
point release, including some rather nasty ones -- 9.4.23 is the
current 9.4 point release. Actually, 9.4 is going to lose support this
year, as the oldest stable version that's currently supported by the
community.
--
Peter Geoghegan
On Mon, Jul 8, 2019 at 12:19 PM Peter Geoghegan wrote:
> Well, you're still running autovacuum very aggressively here. It'll
> easily keep up when run on a relatively small table such as this.
Also, an exactly equal number of insertions and deletions is rather
likely to result in
r a REINDEX, then why bother at all? There is no reason
to think that that will be more effective than simple vacuuming.
--
Peter Geoghegan
;fragmentation" isn't usually much of a problem when using Postgres.
--
Peter Geoghegan
me existing index tuple, causing many
page splits despite there being no real change in the logical contents
of the index. Even then, the table will itself lose much of its
original order, so the index will become "unclustered" as it becomes
fragmented.
--
Peter Geoghegan
ins the heap/table sort order
among duplicates by treating heap TID as a tiebreaker column, which
may make REINDEXing totally unnecessary for you. It's harder to model
this issue because the problem with heap TID order will only be seen
when there is at least a moderate amount of churn.
--
Peter Geoghegan
On Tue, Jul 9, 2019 at 9:04 PM Peter Geoghegan wrote:
> ISTM that the simplest explanation here is that index fragmentation
> (and even index size) is a red herring, and the real issue is that
> you're suffering from problems similar to those that are described in
> these old t
here,
so it seems worth comparing index size in detail.
--
Peter Geoghegan
//www.postgresql.org/docs/current/amcheck.html
--
Peter Geoghegan
a corrupt index) ultimately risks the availability of every
database in the cluster. Many installations receive little to no
supervision, so it may just be a matter of time there. That is
certainly a bad thing.
--
Peter Geoghegan
On Wed, Jul 17, 2019 at 9:21 AM Peter Geoghegan wrote:
> It's possible that amcheck would have given you an accurate diagnosis
> of the problem -- especially if you used bt_index_parent_check():
>
> https://www.postgresql.org/docs/current/amcheck.html
BTW, be sure to use th
ore aggressive than give up
when there is a "failed to re-find parent key" or similar condition.
Perhaps it would make more sense to make the index inactive (for some
value of "inactive") instead of just complaining. That might be the
least worst option, all things considered.
--
Peter Geoghegan
ng can break when somebody
creates an index on a non-immutable expression (including VACUUM),
provided that all the problems only affect the table with the broken
index. OTOH, taking down the entire Postgres cluster as an indirect
consequence of one person's ill-considered CREATE INDEX really sucks.
That distinction seems important to me.
--
Peter Geoghegan
On Wed, Jul 17, 2019 at 10:27 AM Peter Geoghegan wrote:
> > It's possible that amcheck would have given you an accurate diagnosis
> > of the problem -- especially if you used bt_index_parent_check():
> >
> > https://www.postgresql.org/docs/current/amcheck.htm
to
corrupt B-Tree indexes (not Geometry):
https://trac.osgeo.org/postgis/ticket/3841
Though I also see what could be a comparable bug in Geometry:
https://trac.osgeo.org/postgis/ticket/3777
These bugs are from about 3 years ago. If I'm right you should be able
to isolate the bug using amc
checked, restoring this database will take about 30GB of disk
space on top of the dump file itself.
--
Peter Geoghegan
led almost
immediately. If it happened with any regularity, somebody would have
complained by now.
As Tom said, it's hard to give a useful answer without more context --
how you define "immediate"?
--
Peter Geoghegan
earlier than pg93. So thank you for
> your hard work and dedication to this awesome piece of software.
How long did it take on 9.3?
I am the author of the parallel CREATE INDEX feature. It's good to get
feedback like this.
--
Peter Geoghegan
mit applies to a tuple *after*
TOAST compression has been applied.
--
Peter Geoghegan
sort code received many improvements over the years, really
starting in 9.5, and continuing in 9.6, 10 and 11. FWIW, I think that
that was probably the biggest factor here. Though parallel CREATE
INDEX will have helped as well.
--
Peter Geoghegan
ry here, modified to verify all B-Tree indexes (not
just those indexes in the pg_catalog schema):
https://www.postgresql.org/docs/10/amcheck.html
--
Peter Geoghegan
ck('my_index', true);
If that doesn't show any errors, then perhaps try this:
SELECT bt_index_parent_check('my_index', true);
If you're on Postgres 10, then you should leave out the second
argument, "true", since that version doesn't have the extra
heapallindexed check.
Let us know what you see.
--
Peter Geoghegan
have that for the ICU
collations, but it currently isn't possible to use ICU as the default
collation provider. You really have to go out of your way to use ICU
collations.
--
Peter Geoghegan
4:
regression=# create extension pageinspect;
CREATE EXTENSION
regression=# select version from bt_metap('pg_aggregate_fnoid_index');
version
-
4
(1 row)
--
Peter Geoghegan
ss and until
you REINDEX. This includes cases where you're running pg_upgrade
against a restored physical backup.
--
Peter Geoghegan
processes in a free of any other load system.
> It is postgres 11.7
Try increasing maintenance_work_mem from the default of 64MB. MWM
constrains the number of parallel workers used.
--
Peter Geoghegan
segments that become ready to be archived during crash recovery
were potentially recycled without being archived."
Sorry that you were affected by this bug -- it really sucks.
--
Peter Geoghegan
rites didn't restored the page to the state before the
> index-item deletion happened(that is, if full_page_writes were set to
> off.). (If it found to be the cause, I'm not sure why that didn't
> happen on 9.5.)
There is also a Heap/HOT_UPDATE log line with similar errors.
--
Peter Geoghegan
is approach to dumping out a single page image
easier, since it doesn't involve relfilenodes or filesystem files:
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#contrib.2Fpageinspect_page_dump
--
Peter Geoghegan
anted to fix this for numeric, we'd have to invent a new
numeric datatype (called numeric2, say). That probably isn't as hard
as it sounds, since it could be part of the same B-Tree operator
family as numeric. It could also be implicitly cast to numeric.
--
Peter Geoghegan
On Tue, Aug 18, 2020 at 9:44 AM Peter Geoghegan wrote:
> If we wanted to fix this for numeric, we'd have to invent a new
> numeric datatype (called numeric2, say). That probably isn't as hard
> as it sounds, since it could be part of the same B-Tree operator
> family as num
split like this then
you need three copies of the key -- the original, the new, and a
second copy of the original. That's much more complicated.
--
Peter Geoghegan
=
operator will be broken in the presence of different display scales.
It's a non-starter.
The numeric2 design that I sketched is a bit ugly, but I can see no
better way. A three-way posting list split (i.e. the other design that
you sketched) is a special case that is very hard to test, very
complicated, and of little value in the grand scheme of things.
--
Peter Geoghegan
test. And it might defeat certain future optimizations based on heap
TID being the only tiebreaker. Having two types of equality might have
to bleed into the optimizer.
It's a question of engineering trade-offs. I don't think that it's worth it.
--
Peter Geoghegan
. That will make the table larger initially, but leaving enough
space behind on the same heap pages for successor tuples makes it
possible to use HOT updates.
--
Peter Geoghegan
On Thu, Jan 21, 2021 at 12:55 PM Tommy Li wrote:
> Is there any way to configure autovacuum to log the same information as
> VACUUM VERBOSE?
No. Though there really should be.
--
Peter Geoghegan
On Mon, Apr 18, 2022 at 11:37 PM bhargav kamineni wrote:
> executing the vacuum on the entire cluster is also giving the same HINTS and
> WARNING's
You're using Aurora, not PostgreSQL. Perhaps this is actually a bug,
but there is no way for anybody here to know.
--
Peter Geoghegan
nxid) reached vacuum_freeze_table_age).
See my recent response to a similar question here:
https://postgr.es/m/CAH2-WzkFQ-okvVXizpy4dCEVq75N-Qykh=crhzao-eajflv...@mail.gmail.com
--
Peter Geoghegan
vacuum, but that's not really true (apart from VACUUM FULL, which
really is quite different). The difference between aggressive and
non-aggressive can be big in practice due to an accumulation of
unfrozen pages over multiple non-aggressive vacuums.
--
Peter Geoghegan
ion is in dire need of an
overhaul. :-(
--
Peter Geoghegan
more sense for *your* workload, where huge differences in the rate of
MultiXact consumption among tables is likely the norm. This still
isn't perfect (far from it), but it has the potential to make things
far better here.
--
Peter Geoghegan
ll be
set to false on the standby.
--
Peter Geoghegan
On Tue, Oct 11, 2022 at 10:04 PM Tom Lane wrote:
> Do we propagate visibility-map bits to standbys?
Yes.
--
Peter Geoghegan
interlock at all in the case of
nbtree index scans with MVCC snapshots -- but *not* with index-only
scans. See "Making concurrent TID recycling safe" in the nbtree
README. I only got around to documenting all of the details here quite
recently. The index-only scan thing dates back to 9.5.
--
Peter Geoghegan
mud, too. What does "inferred" mean here? I think it means "chosen as
> arbiter index", but maybe I misunderstand.)
Unique index/constraint inference is the process by which we choose an
arbiter index. See the second paragraph of the "ON CONFLICT Clause"
section of the INSERT docs.
--
Peter Geoghegan
usual to have a perfectly
random and uniformly distributed clustering of index tuples, which is
what the index built via hashing exhibits. Even a v4 UUID index could
easily have plenty of duplicates, which would probably do
significantly better on the metric you've focussed on.
--
Peter Geoghegan
. This is a highly contrived example.
FWIW I think that it could be a lot less bad, even with indexes that
you'd think would be almost as bad as the bad one from your test case.
Even things that appear to be random aren't usually nearly as random
as what you've shown.
--
Peter Geoghegan
relative ordering of each script
can be changed this way. There is also something called merged
tailorings.
The OP should see the Postgres ICU docs for hints on how to use these
facilities to make a custom collation that matches whatever their
requirements are:
https://www.postgresql.org/docs/
look into, in particular anything I
> should check before upgrading the remaining 13.8 DB to 13.9?
I recommend running amcheck on all indexes, or at least all
possibly-affected text indexes.
--
Peter Geoghegan
interface for
running amcheck routine, so maybe look into that once you upgrade.
--
Peter Geoghegan
ust about possible with an access method like GIN. Do you have
any non-btree indexes on the table? Can you show us the details of the
table, including all of its indexes? In other words, can you show "\d
applications" output from psql?
--
Peter Geoghegan
Can you run amcheck's bt_index_check() routine against some of the
indexes you've shown? There is perhaps some chance that index
corruption exists and causes VACUUM to take a very long time to delete
index pages. This is pretty much a wild guess, though.
--
Peter Geoghegan
at version). And if you were on 14+, things in
this area would be much better still.
--
Peter Geoghegan
x27;d have frozen that
one tuple and then set the page all-visible. The page would likely be
frozen again by the next aggressive VACUUM, which is usually much more
expensive.
--
Peter Geoghegan
PG15 in theory can be used to do UPSERT
> properly that is void of the aforementioned limitation.
> The downside is it is rather verbose.
The MERGE command has various race conditions that are particularly
relevant to UPSERT type use cases. See the wiki page you referenced
for a huge amoun
been built with ICU support for many years -- they
modify the source code minimally to make this work. It may well still
be impossible to use "az-x-icu" on a FreeBSD installation of 9.6,
though.
--
Peter Geoghegan
ilable.
See:
https://github.com/petergeoghegan/amcheck
--
Peter Geoghegan
manually, but what would it look like for en-x-icu?
It is safe to call pg_import_system_collations() directly, which is
all that initdb does. This is documented, so you wouldn't be relying
on a hack.
--
Peter Geoghegan
ning that you clear manually with a command, not a mechanism that
> really tracks which database objects were last rebuilt/validated with
> a given version.
Yes, that does seem like a big remaining weakness.
--
Peter Geoghegan
hin GIN, where
it's impossible for the main entry tree to have duplicates without at
least storing them in a posting list.
--
Peter Geoghegan
1 - 100 of 154 matches
Mail list logo
