Thread: Update comment for README.HOT
I would like to apply the attached patch to README.HOT so clarify when single-page cleanup happens, e.g. not during INSERT. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + Index: src/backend/access/heap/README.HOT =================================================================== RCS file: /cvsroot/pgsql/src/backend/access/heap/README.HOT,v retrieving revision 1.6 diff -c -c -r1.6 README.HOT *** src/backend/access/heap/README.HOT 23 Apr 2010 23:21:44 -0000 1.6 --- src/backend/access/heap/README.HOT 17 Sep 2010 19:48:16 -0000 *************** *** 246,251 **** --- 246,257 ---- is arbitrarily capped at MaxHeapTuplesPerPage (the most tuples that could fit without HOT pruning). + Effectively, space reclamation happens during tuple retrieval when the + page is nearly full (<10%) and a buffer cleanup lock can be acquired. + This means that UPDATE, DELETE, and SELECT can trigger space + reclamation, while INSERT ... VALUES cannot because it does not retrieve + a row. + VACUUM ------
On 17 September 2010 20:52, Bruce Momjian <bruce@momjian.us> wrote: > I would like to apply the attached patch to README.HOT so clarify when > single-page cleanup happens, e.g. not during INSERT. > "... when the page is nearly full (<10%) ..." Shouldn't that be >90%? "... while INSERT ... VALUES cannot because it does not retrieve a row." Is this still true when it's used in conjunction with RETURNING? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Bruce Momjian <bruce@momjian.us> writes:
> + This means that UPDATE, DELETE, and SELECT can trigger space
> + reclamation, while INSERT ... VALUES cannot because it does not retrieve
> + a row.
I don't believe that's correct. It might have happened to work that way
for you in a particular test. It's certainly not something I'd document
as being intended long-term behavior.
regards, tom lane
Thom Brown wrote: > On 17 September 2010 20:52, Bruce Momjian <bruce@momjian.us> wrote: > > I would like to apply the attached patch to README.HOT so clarify when > > single-page cleanup happens, e.g. not during INSERT. > > > > "... when the page is nearly full (<10%) ..." > > Shouldn't that be >90%? > > "... while INSERT ... VALUES cannot because it does not retrieve a row." > > Is this still true when it's used in conjunction with RETURNING? I think returning might cause a clean --- I have not tested that. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > + This means that UPDATE, DELETE, and SELECT can trigger space > > + reclamation, while INSERT ... VALUES cannot because it does not retrieve > > + a row. > > I don't believe that's correct. It might have happened to work that way > for you in a particular test. It's certainly not something I'd document > as being intended long-term behavior. Well, I would like to document something about this because I was surprised that when INSERT did not trigger a cleanup. I realize we might change the behavior but then we would update the file too, hopefully. How is the attached version using "often"? I also clarified it is < 10% free. I found this while doing tests for a new MVCC talk I will be delivering at PG West. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + Index: src/backend/access/heap/README.HOT =================================================================== RCS file: /cvsroot/pgsql/src/backend/access/heap/README.HOT,v retrieving revision 1.6 diff -c -c -r1.6 README.HOT *** src/backend/access/heap/README.HOT 23 Apr 2010 23:21:44 -0000 1.6 --- src/backend/access/heap/README.HOT 17 Sep 2010 21:21:56 -0000 *************** *** 246,251 **** --- 246,257 ---- is arbitrarily capped at MaxHeapTuplesPerPage (the most tuples that could fit without HOT pruning). + Effectively, space reclamation happens during tuple retrieval when the + page is nearly full (<10% free) and a buffer cleanup lock can be + acquired. This means that UPDATE, DELETE, and SELECT can trigger space + reclamation, but often not during INSERT ... VALUES because it does + not retrieve a row. + VACUUM ------
Applied. --------------------------------------------------------------------------- Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <bruce@momjian.us> writes: > > > + This means that UPDATE, DELETE, and SELECT can trigger space > > > + reclamation, while INSERT ... VALUES cannot because it does not retrieve > > > + a row. > > > > I don't believe that's correct. It might have happened to work that way > > for you in a particular test. It's certainly not something I'd document > > as being intended long-term behavior. > > Well, I would like to document something about this because I was > surprised that when INSERT did not trigger a cleanup. I realize we > might change the behavior but then we would update the file too, > hopefully. > > How is the attached version using "often"? I also clarified it is < 10% > free. > > I found this while doing tests for a new MVCC talk I will be delivering > at PG West. > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > Index: src/backend/access/heap/README.HOT > =================================================================== > RCS file: /cvsroot/pgsql/src/backend/access/heap/README.HOT,v > retrieving revision 1.6 > diff -c -c -r1.6 README.HOT > *** src/backend/access/heap/README.HOT 23 Apr 2010 23:21:44 -0000 1.6 > --- src/backend/access/heap/README.HOT 17 Sep 2010 21:21:56 -0000 > *************** > *** 246,251 **** > --- 246,257 ---- > is arbitrarily capped at MaxHeapTuplesPerPage (the most tuples that > could fit without HOT pruning). > > + Effectively, space reclamation happens during tuple retrieval when the > + page is nearly full (<10% free) and a buffer cleanup lock can be > + acquired. This means that UPDATE, DELETE, and SELECT can trigger space > + reclamation, but often not during INSERT ... VALUES because it does > + not retrieve a row. > + > > VACUUM > ------ > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +