pg_index question

To answer my own question, "No it won't work." I still get a unique constraint
error.

On Friday 25 March 2005 04:10 pm, Terry Lee Tucker saith:

Hi,

If I were to set the value of pg_class.indisunique on a unique index to
False inside a transaction so I could juggle sequence numbers around on a
table with a unique two element index, and then set it back again to its
proper value, all in the same transaction, would that allow me to
temorarily override the unique index behavior? Is it safe to temporarily
change the value of that column?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--

On Fri, 2005-03-25 at 15:10, Terry Lee Tucker wrote:

Hi,

If I were to set the value of pg_class.indisunique on a unique index to False
inside a transaction so I could juggle sequence numbers around on a table
with a unique two element index, and then set it back again to its proper
value, all in the same transaction, would that allow me to temorarily
override the unique index behavior? Is it safe to temporarily change the
value of that column?

You could, but, in order to ensure there are no duplicates, you'd have
to check after turning it back on to see if there were and delete them
by some logic that made sense for your transactional methodologies.

I wouldn't recommend it really, because I'm willing to bet there are
race conditions I'm not thinking of that could bite you in the behind.

Umm, I tried it and it worked. Sure you got the right relid?

Well, I think I had the right oid, but I agree with you. I think I have a
better solution than this. I think I real try it once more though. I must
have done something wrong.

Thanks for the reply :o)

On Friday 25 March 2005 04:53 pm, Scott Marlowe saith:

Scott Marlowe <smarlowe@g2switchworks.com> writes:

Umm, I tried it and it worked. Sure you got the right relid?

I think the relation cache will probably not notice a manual update on
pg_index. So whether it "works" or not would depend on a lot of
extraneous factors like whether the relation cache entry was already
built for the target table and whether you did something else that would
force a cache rebuild.

In any case, the whole idea is so fraught with pitfalls that I couldn't
recommend it ...

regards, tom lane

Thanks Tom.

One never knows until he asks...

On Friday 25 March 2005 06:36 pm, Tom Lane saith:

Scott Marlowe <smarlowe@g2switchworks.com> writes:

Umm, I tried it and it worked. Sure you got the right relid?

I think the relation cache will probably not notice a manual update on
pg_index. So whether it "works" or not would depend on a lot of
extraneous factors like whether the relation cache entry was already
built for the target table and whether you did something else that would
force a cache rebuild.

In any case, the whole idea is so fraught with pitfalls that I couldn't
recommend it ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Quote: 42
"In selecting men for office, let principle be your guide. Regard not
the particular sect or denomination of the candidate -- look to his
character...."

--Noah Webster

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com