Corrupt index
Hello,
We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a Multi-zone failover configuration. We do not have access to the hardware or file system. Fsync is enabled.
We have a recurring problem that we can get out of with "reindex table mytable". Basically we can't find a particular record or two using the primary key and after reindexing we can. There does seem to be a pattern related to application business processes but we are at a loss as to how this could happen.
Assuming the RDS instance is solid, what are the things we could do using nothing but SQL that could cause a corruption to the primary key index? I've researched the issue a fair amount and most content talks about hardware, file system, and fsync. As mentioned above fsync (and other properties) we can control (and fsync is enabled) but we can't get to the file system or hardware. If such a thing cannot be done with transactions, statements, and queries alone, what should we be looking at for settings?
Thanks.
--
Daryl Stultz
Principal Software Developer
_____________________________________
OpenTempo, Inc
http://www.opentempo.com<http://www.opentempo.com/>
mailto:daryl.stultz@opentempo.com<mailto:daryl.stultz@opentempo.com>
On Tue, Aug 15, 2017 at 12:24 PM, Daryl Stultz
<daryl.stultz@opentempo.com> wrote:
We have a recurring problem that we can get out of with "reindex table
mytable". Basically we can't find a particular record or two using the
primary key and after reindexing we can. There does seem to be a pattern
related to application business processes but we are at a loss as to how
this could happen.
You've given no details at all. What business pattern? What does the
index and table look like?
--
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 15 August 2017 21:24:29 GMT+02:00, Daryl Stultz <daryl.stultz@opentempo.com> wrote:
Hello,
We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a
Are you using reindex concurrently? There was a bugfix in 9.3.16 (+-1, i'm not sure).
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Aug 15, 2017 at 12:24 PM, Daryl Stultz
You've given no details at all. What business pattern? What does the
index and table look like?
Hi Peter. There are no details that are obvious to me that I should be providing. The business process is one of a thousand that the application performs. To describe it generically, if we were running a commerce website, every time we find ourselves in this situation the record is related to a cancelled order. Something like that.
The primary key of the table is of "serial" type with an index like so:
"scheduledassignments_pk" PRIMARY KEY, btree (scheduledassignmentid)
We have various triggers on the table.
Since the DB is managed by Amazon, I'm inclined to think the system is set up properly and the hardware is solid. This leaves me to consider some pattern in SQL, transactions and such that we are running that might be causing the corruption. Basically I don't have any idea what to look for.
/Daryl
We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a
Are you using reindex concurrently? There was a bugfix in 9.3.16 (+-1, i'm not sure).
Hi Andreas, not that I'm aware of. I issue "reindex table mytable" to get out of the bad situation. I'm not sure if you are saying this bug could be getting me into the situation. If you don't mean that I am manually executing reindex concurrently, how do I go about determining if the system is using that procedure? Perhaps after some transaction, the system rebuilds the index automatically using "concurrently" and that is buggy/failing?
Thanks.
/Daryl
On 15 August 2017 21:54:56 GMT+02:00, Daryl Stultz <daryl.stultz@opentempo.com> wrote:
We are using PostgreSQL 9.3.14 on a managed Amazon RDS with a
Are you using reindex concurrently? There was a bugfix in 9.3.16 (+-1,
i'm not sure).
Hi Andreas, not that I'm aware of. I issue "reindex table mytable" to
get out of the bad situation. I'm not sure if you are saying this bug
could be getting me into the situation. If you don't mean that I am
manually executing reindex concurrently, how do I go about determining
if the system is using that procedure? Perhaps after some transaction,
the system rebuilds the index automatically using "concurrently" and
that is buggy/failing?Thanks.
/Daryl
I only wanted to exclude it. Anyway, you should install the latest patches.
Regards, Andreas.
--
2ndQuadrant - The PostgreSQL Support Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 8/15/2017 1:07 PM, Andreas Kretschmer wrote:
I only wanted to exclude it. Anyway, you should install the latest patches.
he can't, with RDS, he's at the mercy of Amazon for all sysadmin duties.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 8/15/2017 1:07 PM, Andreas Kretschmer wrote:
I only wanted to exclude it. Anyway, you should install the latest patches.
he can't, with RDS, he's at the mercy of Amazon for all sysadmin duties.
For many things, yes. I do have options to upgrade PG. We could probably do a minor upgrade with no concern. I can go to 9.3.16/17. I could also go to 9.4.9/11/12.
/Daryl