Обсуждение: BUG #4232: CREATE INDEX CONCURRENTLY
The following bug has been logged online: Bug reference: 4232 Logged by: Lawrence Cohan Email address: lawrencec@1shoppingcart.com PostgreSQL version: 8.2.5 Operating system: Redhat Linux 4.1.1 Description: CREATE INDEX CONCURRENTLY Details: We must run maintenance tasks like analyze, reindex and vacuum against our PG databases however due to the fact that we are running a 24/7 system that requires database access the reindex at the database level is way too heavy and it is generating deadlocks. I created a job to CREATE INDEX CONCURRENTLY on all user tables and DROP existing INDEX so we donât impact our production and now our application is getting errors (like the one below) just because the OID for the index was changed. Is there anything we could do to workaround this issue as so far the only option that clears it is an IIS RESET. Exception Type: Npgsql.NpgsqlException Item: NULL Severity: ERROR Code: XX000 BaseMessage: could not open relation with OID 517613 Detail: Hint: Position: Where: SQL statement "SELECT id FROM coupons WHERE merchant_id = $1 AND code = $2 " PL/pgSQL function "set_coupon" line 7 at SQL statement File: heapam.c Line: 700 Routine: relation_open ErrorSql: select * from set_coupon(NULL::int4,91221::int4,'11111'::text,'11111'::text,'1'::int4,NULL ::int4,0::int4,'1'::int4,11::numeric,0::numeric,'2008-06-10'::date,NULL::dat e,TRUE::bool,FALSE::bool) Errors: System.Collections.ArrayList Message: ERROR: XX000: could not open relation with OID 517613 Data: System.Collections.ListDictionaryInternal TargetSite: Void CheckErrors() HelpLink: NULL Source: Npgsql Thanks, Lawrence Cohan.
"Lawrence Cohan" <lawrencec@1shoppingcart.com> writes: > We must run maintenance tasks like analyze, reindex and vacuum against our > PG databases however due to the fact that we are running a 24/7 system that > requires database access the reindex at the database level is way too heavy > and it is generating deadlocks. I created a job to CREATE INDEX CONCURRENTLY > on all user tables and DROP existing INDEX so we donât impact our > production and now our application is getting errors (like the one below) > just because the OID for the index was changed. Is there anything we could > do to workaround this issue as so far the only option that clears it is an > IIS RESET. Presumably the errors are coming from re-use of cached plans. The only really simple solution would be to upgrade to PG 8.3, which knows about regenerating cached plans when needed. regards, tom lane
Many thanks - we actually considered that at some point and I looked for more specific info on this particular issue and I found about the new pg_stat_clear_snapshot() function in 8.3 as well.=20 Now that we know that this issue was fixed in 8.3 already it's even more incentive for us to plan for an upgrade. Lawrence Cohan. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Tuesday, June 10, 2008 4:58 PM To: Lawrence Cohan Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #4232: CREATE INDEX CONCURRENTLY=20 "Lawrence Cohan" <lawrencec@1shoppingcart.com> writes: > We must run maintenance tasks like analyze, reindex and vacuum against our > PG databases however due to the fact that we are running a 24/7 system that > requires database access the reindex at the database level is way too heavy > and it is generating deadlocks. I created a job to CREATE INDEX CONCURRENTLY > on all user tables and DROP existing INDEX so we don't impact our > production and now our application is getting errors (like the one below) > just because the OID for the index was changed. Is there anything we could > do to workaround this issue as so far the only option that clears it is an > IIS RESET. Presumably the errors are coming from re-use of cached plans. The only really simple solution would be to upgrade to PG 8.3, which knows about regenerating cached plans when needed. regards, tom lane Attention: The information contained in this message and or attachments is intended on= ly for the person or entity to which it is addressed and may =0D contain confidential and/or privileged material. Any review, retransmissio= n, dissemination or other use of, or taking of any action in =0D reliance upon, this information by persons or entities other than the inten= ded recipient is prohibited. If you received this in error, please =0D contact the sender and delete the material from any system and destroy any = copies.