Обсуждение: [GENERAL] 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
mailto:daryl.stultz@opentempo.com
Principal Software Developer
_____________________________________
OpenTempo, Inc
http://www.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
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
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?
>>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).
>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
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
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.
>> 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