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.
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]
Sent: Tuesday, June 10, 2008 4:58 PM
To: Lawrence Cohan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4232: CREATE INDEX CONCURRENTLY
"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 only for the person or entity to which it is addressed and may
contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in
reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please
contact the sender and delete the material from any system and destroy any copies.