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

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.

*************************************