pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRename Index - Deadlock
3+ messages / 3 participants
[nested] [flat]
* Rename Index - Deadlock
@ 2010-03-16 09:45 Thomas Kenner <thomas.kenner@gmail.com>
2010-03-16 16:16 ` Re: Rename Index - Deadlock Tom Lane <tgl@sss.pgh.pa.us>
2010-03-16 16:39 ` Re: Rename Index - Deadlock Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Thomas Kenner @ 2010-03-16 09:45 UTC (permalink / raw)
To: pgsql-sql
Hi,
Each day I'm recreating the index my_index of the table my_table. Therefore I
create a new index my_index_new, drop the old index my_index, and rename the
new index:
ALTER INDEX my_index_new RENAME TO my_index;
If an insert or select statement is run at the same time as the "ALTER INDEX
... RENAME TO" statement, it will end up in a deadlock.
The index contains a foreign key (don't know if this information is
important).
Is this a known issue?
Thanks,
Thomas
--
Thomas Kenner
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rename Index - Deadlock
2010-03-16 09:45 Rename Index - Deadlock Thomas Kenner <thomas.kenner@gmail.com>
@ 2010-03-16 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2010-03-16 16:16 UTC (permalink / raw)
To: Thomas Kenner <thomas.kenner@gmail.com>; +Cc: pgsql-sql
Thomas Kenner <thomas.kenner@gmail.com> writes:
> Each day I'm recreating the index my_index of the table my_table. Therefore I
> create a new index my_index_new, drop the old index my_index, and rename the
> new index:
> ALTER INDEX my_index_new RENAME TO my_index;
> If an insert or select statement is run at the same time as the "ALTER INDEX
> ... RENAME TO" statement, it will end up in a deadlock.
I doubt it's the RENAME that's the problem, more likely the DROP of the
old index. That has to get an exclusive lock on the parent table to be
sure there is nothing trying to use the index.
Why exactly do you feel you need to do this every day?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rename Index - Deadlock
2010-03-16 09:45 Rename Index - Deadlock Thomas Kenner <thomas.kenner@gmail.com>
@ 2010-03-16 16:39 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Marlowe @ 2010-03-16 16:39 UTC (permalink / raw)
To: Thomas Kenner <thomas.kenner@gmail.com>; +Cc: pgsql-sql
On Tue, Mar 16, 2010 at 3:45 AM, Thomas Kenner <thomas.kenner@gmail.com> wrote:
> Hi,
>
> Each day I'm recreating the index my_index of the table my_table. Therefore I
> create a new index my_index_new, drop the old index my_index, and rename the
> new index:
> ALTER INDEX my_index_new RENAME TO my_index;
>
> If an insert or select statement is run at the same time as the "ALTER INDEX
> ... RENAME TO" statement, it will end up in a deadlock.
A deadlock or just a lock? What version of pg? What exact error
messages do you get?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-03-16 16:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-16 09:45 Rename Index - Deadlock Thomas Kenner <thomas.kenner@gmail.com>
2010-03-16 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-16 16:39 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox