Thread: pg_index question
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?
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? On Fri, 2005-03-25 at 15:30, Terry Lee Tucker wrote: > 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)
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: > Umm, I tried it and it worked. Sure you got the right relid? > > On Fri, 2005-03-25 at 15:30, Terry Lee Tucker wrote: > > 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)
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