Обсуждение: Postgres 8.3 HOT and non-persistent xids
Hi, Can someone clarify HOT updates for me (and perhaps put more detail into the docs?). Is this statement correct: the HOT technique is used on *any* table so long as no indexed column is affected. create table T (A int, B int); create index TB on T (B); insert into T (A,B) Values (1,2); So if I do an update that is identical to the existing row, nothing changes? update T set A=1, B=2 where A=1; If I change the non-indexed field, A, then HOT applies and no new tuple needed? update T set A=2, B=2 where A=1; If I change the indexed field, B, then HOT doesn't apply and a new tuple is needed? update T set A=2,B=3 where A=2; Is that correct? Actually, what actually happens when you get an update with redundant information, e.g. update T set A=2,B=4 where A=2; The value of A hasn't changed, does postgres still write the value? And for non-persistent transaction ids, the documentation says that this is for read-only transactions. What defines a read-only transaction for this purpose? Does postgres check to see if a SELECT includes e.g. a sequence change via nextval? If I mark the transaction as readonly using the PG JDBC driver, will that be sufficient? Thank you, Mike
On Dec 20, 2007 3:44 AM, Mike C <smith.not.western@gmail.com> wrote: > Hi, > > Can someone clarify HOT updates for me (and perhaps put more detail > into the docs?). Is this statement correct: the HOT technique is used > on *any* table so long as no indexed column is affected. > Its partially correct. HOT is used on system and user tables. "No index column change" is a necessary but not sufficient condition for HOT update. There must be enough free space in the same block where the old tuple exists. Though we hope that the system will stabilize in terms of availability of free space in the blocks, it might be worthy to leave free space of at least one tuple size by using appropriate fill factor at the table creation time. > create table T (A int, B int); > create index TB on T (B); > insert into T (A,B) Values (1,2); > > So if I do an update that is identical to the existing row, nothing changes? > update T set A=1, B=2 where A=1; > HOT update *is not* update-in-place. So every update, HOT or COLD, would generate a new version of the row. The power of HOT comes when the index column is not changed. This allows us to skip index inserts for the new version (thus preventing index bloats). Its also far easier to vacuum the dead HOT tuples without running VACUUM or VACUUM FULL. This gives us the ability to prevent heap bloats. > If I change the non-indexed field, A, then HOT applies and no new tuple needed? > update T set A=2, B=2 where A=1; > HOT applies, but new tuple is needed as described above. > If I change the indexed field, B, then HOT doesn't apply and a new > tuple is needed? > > update T set A=2,B=3 where A=2; Right. > > Actually, what actually happens when you get an update with redundant > information, e.g. > > update T set A=2,B=4 where A=2; > > The value of A hasn't changed, does postgres still write the value? > Yes. Every update generates a new version of the row. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
[ Pavan already answered most of this, but ... ]
"Mike C" <smith.not.western@gmail.com> writes:
> And for non-persistent transaction ids, the documentation says that
> this is for read-only transactions. What defines a read-only
> transaction for this purpose?
A transaction that has not done anything that requires it to sign an
update with its transaction ID.
> Does postgres check to see if a SELECT
> includes e.g. a sequence change via nextval?
IIRC, nextval() doesn't involve marking anything with one's XID,
because it is not a rollback-able operation. But you are thinking
at quite the wrong level if you suppose that this behavior has anything
to do with "checking a SELECT for a writing operation". The way it
really works is that a backend generates a persistent XID for its
current transaction at the instant that some bit of code first demands
the transaction's XID. Typically this happens because you insert,
update, or delete some tuple, and the XID is needed to set xmin or xmax
of the tuple. There are some other cases, but they are covered by
definition, because there is no way to get the current XID except to ask
that code for it.
> If I mark the transaction
> as readonly using the PG JDBC driver, will that be sufficient?
If this means what I think it means, it's irrelevant. Materializing a
persistent XID is driven off what the transaction *actually* does,
not off whether it's declared to be read-only or not.
regards, tom lane
On Thu, Dec 20, 2007 at 12:55:02AM -0500, Tom Lane wrote: > "Mike C" <smith.not.western@gmail.com> writes: > > And for non-persistent transaction ids, the documentation says that > > this is for read-only transactions. What defines a read-only > > transaction for this purpose? > > A transaction that has not done anything that requires it to sign an > update with its transaction ID. In this context, when I set a connection to READ ONLY do any transactions run on the connection ever bother to do any of the transactional-writes housekeeping (such as txid signing) ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Thank you both, this clears everything up for me. Cheers, Mike