Thread: deferring/disabling unique index
Hi,
I need to know if there is a way to defer or disable a unique index on a table during an update. One way would be to set indisunique to false, perform update and then set to true. But, this seems to be an ugly solution.
I've posted a similar message 6 months ago and at that time deferring unique constraints was on a todo list. I wonder if this has been added to 7.4.1 release. If not, what is the best way to disable an index on a table?
Thanks.
Oleg
Oleg Lebedev wrote: > Hi, > > I need to know if there is a way to defer or disable a unique index on a > table during an update. One way would be to set indisunique to false, > perform update and then set to true. But, this seems to be an ugly > solution. > > I've posted a similar message 6 months ago and at that time deferring > unique constraints was on a todo list. I wonder if this has been added > to 7.4.1 release. If not, what is the best way to disable an index on a > table? It is still not done, and no one is working on it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
So, does it mean that the only way to disable the index is to drop and recreate it? What about setting indisunique to false temporarily? -----Original Message----- From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] Sent: Friday, January 09, 2004 10:19 AM To: Oleg Lebedev Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] deferring/disabling unique index Oleg Lebedev wrote: > Hi, > > I need to know if there is a way to defer or disable a unique index on > a table during an update. One way would be to set indisunique to > false, perform update and then set to true. But, this seems to be an > ugly solution. > > I've posted a similar message 6 months ago and at that time deferring > unique constraints was on a todo list. I wonder if this has been added > to 7.4.1 release. If not, what is the best way to disable an index on > a table? It is still not done, and no one is working on it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ************************************* This e-mail may contain privileged or confidential material intended for the named recipient only. If you are not the named recipient, delete this message and all attachments. Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited. We reserve the right to monitor e-mail sent through our network. *************************************
Oleg Lebedev wrote: > So, does it mean that the only way to disable the index is to drop and > recreate it? What about setting indisunique to false temporarily? Not sure. I seem to remember a way someone got around this, but can't remember the details. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>So, does it mean that the only way to disable the index is to drop and >recreate it? What about setting indisunique to false temporarily? > > > I am just curious... why would you want to defer a unique constraint? Sincerely, Joshua Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Joshua D. Drake wrote:
>
> >So, does it mean that the only way to disable the index is to drop and
> >recreate it? What about setting indisunique to false temporarily?
> >
> >
> >
> I am just curious... why would you want to defer a unique constraint?
I remember now --- if you do:
UPDATE tab SET col = col + 1;
you hit a unique constraint when you shouldn't need to. I think the
workaround was:
UPDATE tab SET col = -col + -1;
then:
UPDATE tab SET col = -col;
This assumes all the values are positive, of course.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Basically, swapping values of columns involved in a unique index causes the problem. Example: I wrote a synchronization script that syncs data between multiple databases. It retrieves primary key information from the system tables, joins remote tables and updates corresponding values. Suppose I have a table: Employee (FirstName, LastName, id) PrimaryKey: id UniqueIndex: FirstName, LastName Suppose on each database instance this table contains two records: Jane Doe 1 Jane Smith 2 Now, suppose we swap the last names between the two emplyees on one instance, so we end up with: Jane Smith 1 Jane Doe 2 Now, I want to propagate this data to another database instance and run this query: UPDATE Employee1 SET LastName=e2.LastName FROM Employee2 e2 WHERE Employee1.id = e2.id; In the above query Employee1 is the Employee table from the first DB instance and Employee2 - from the second DB instance. The query will throw an error saying that it UniqueIndex is violated when assigning last name Doe to employee with id 1. Thanks. Oleg -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Friday, January 09, 2004 10:37 AM To: Oleg Lebedev Cc: Bruce Momjian; pgsql-general@postgresql.org Subject: Re: [GENERAL] deferring/disabling unique index >So, does it mean that the only way to disable the index is to drop and >recreate it? What about setting indisunique to false temporarily? > > > I am just curious... why would you want to defer a unique constraint? Sincerely, Joshua Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL ************************************* This e-mail may contain privileged or confidential material intended for the named recipient only. If you are not the named recipient, delete this message and all attachments. Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited. We reserve the right to monitor e-mail sent through our network. *************************************
I see that it works for this simple case.
Check my previous email for a more complex example.
Thanks.
Oleg
-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Friday, January 09, 2004 10:45 AM
To: Joshua D. Drake
Cc: Oleg Lebedev; pgsql-general@postgresql.org
Subject: Re: [GENERAL] deferring/disabling unique index
Joshua D. Drake wrote:
>
> >So, does it mean that the only way to disable the index is to drop
> >and recreate it? What about setting indisunique to false temporarily?
> >
> >
> >
> I am just curious... why would you want to defer a unique constraint?
I remember now --- if you do:
UPDATE tab SET col = col + 1;
you hit a unique constraint when you shouldn't need to. I think the
workaround was:
UPDATE tab SET col = -col + -1;
then:
UPDATE tab SET col = -col;
This assumes all the values are positive, of course.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania
19073
*************************************
This e-mail may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.
*************************************