On Sat, 2010-08-14 at 00:29 -0700, maxxedit@xxxxxxxxx wrote: > Does this statement (UPDATE user SET email='newChangedVal', > password='existingVal') requires updating an index on user.password? > Or more generally, if an UPDATE includes an explicit but unchanged > value for an index column, does postgres need to also update the index > entries? Or does HOT apply only when indexed column is missing or not > explicit (i.e. password=password)? It does a binary comparison of the old/new values (for indexed columns), and if they are identical, it allows a HOT update. If a data type has two representations for the same value, that may mean that it does a regular update when it could do a hot update. In other words, it doesn't call a datatype-specific equality function. > Along the same line of thought as above, if password is a foreign key > column and if an UPDATE includes an explicit but unchanged value for > this fk column, does postgres need to check constraint satisfiability? No, it does not need to do the check. In this case, however, it does appear that it uses a datatype-specific equality function. So, even if you have a strange datatype where two equal values can have different representations, it will still avoid the check. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
