Обсуждение: Addition to TOAST documentation in 8.4 comprehensive manual
Hello, I'd like to suggest a small addition to the TOAST overview in the 8.4 comprehensive manual, Chapter 53.2 TOAST. This is my first doc patch for postgres, so if this is not appropriate, please advise. I spent almost an hour trying to figure out how to go from the TOAST table id to the owning table name... finally got help on #postgresql. Thought I'd document it for other sys admins new to PostgreSQL. Or is this something that would be better to go into the PostgreSQL Wiki? Sincerely, Aleksey To find the parent table given a TOAST table, which has a name like pg_toast_12513885, cast the OID of the toast table to "regclass". For example: Given "monkeys" table which spills over to pg_toast_12513885: select 12513885::regclass; regclass ---------- monkeys (1 row) "regclass" stands for registered "class". Per RhodiumToad on #postgresql, for reasons too complicated to explain, "class" is used to mean "relation". Reference: "casting a table's OID to regclass is handy for symbolic display of a numeric OID" http://www.postgresql.org/docs/8.4/static/datatype-oid.html
I'd like to add the following to my proposed documentation tidbit, please: The more offical/strict way, that does not rely on the implementation artifact that TOAST numeric ID is embedded in the TOAST symbolic name, is: select oid::regclass from pg_class where reltoastrelid='pg_toast_12513885'::regclass; Thanks for a world class database! Sincerely, Aleksey
On Wed, Dec 22, 2010 at 9:05 PM, Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote: > I'd like to add the following to my proposed documentation tidbit, please: > > The more offical/strict way, that does not rely on the > implementation artifact that TOAST numeric ID is embedded in the TOAST > symbolic name, is: > > select oid::regclass from pg_class where > reltoastrelid='pg_toast_12513885'::regclass; I think this might be a good thing to mention somewhere, but can you make a more specific proposal about where you think it should be put, maybe in the form of a patch? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sat, Jan 8, 2011 at 3:16 AM, Robert Haas <robertmhaas@gmail.com> wrote: > On Wed, Dec 22, 2010 at 9:05 PM, Aleksey Tsalolikhin > <atsaloli.tech@gmail.com> wrote: >> I'd like to add the following to my proposed documentation tidbit, please: >> > > I think this might be a good thing to mention somewhere, but can you > make a more specific proposal about where you think it should be put, > maybe in the form of a patch? Thank you very much, Robert. I propose it be appended to the 8.4 comprehensive manual, Chapter 53.2 TOAST. (And the 9 manual, if it's true for 9. I haven't touched 9 yet but I'm assuming this hasn't changed?) Here is the patch for 9.0.2 DocBook SGML: *** storage.sgml.orig 2011-01-08 10:38:23.000000000 -0500 --- storage.sgml 2011-01-08 10:48:53.000000000 -0500 *************** *** 404,409 **** --- 404,421 ---- comparison table, in which all the HTML pages were cut down to 7 kB to fit. </para> + <para> + To find the parent table given a <acronym>TOAST<acronym> table which has a + name like pg_toast_12513885, run: + </para> + + <para> + <programlisting> + select oid::regclass from pg_class where + reltoastrelid='pg_toast_12513885'::regclass; + </programlisting> + </para> + </sect1> <sect1 id="storage-fsm"> And here is the patch for the 8.4.6 documentation: *** storage.sgml.orig 2011-01-08 10:56:04.000000000 -0500 --- storage.sgml 2011-01-08 10:56:06.000000000 -0500 *************** *** 374,379 **** --- 374,393 ---- comparison table, in which all the HTML pages were cut down to 7 kB to fit. </para> + + <para> + To find the parent table given a <acronym>TOAST<acronym> table which has a + name like pg_toast_12513885, run: + </para> + + <para> + <programlisting> + select oid::regclass from pg_class where + reltoastrelid='pg_toast_12513885'::regclass; + </programlisting> + </para> + + </sect1> <sect1 id="storage-fsm"> Yours truly, Aleksey
Hi, how about my more specific proposal, in the form of a patch, please? (it is below in my quoted email of 8 Jan 2011.) It's my first time offering a patch for PostgreSQL documentation, please let me know if I've done it right or if I should change something. Yours truly, Aleksey On Sat, Jan 8, 2011 at 7:58 AM, Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote: > On Sat, Jan 8, 2011 at 3:16 AM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Wed, Dec 22, 2010 at 9:05 PM, Aleksey Tsalolikhin >> <atsaloli.tech@gmail.com> wrote: >>> I'd like to add the following to my proposed documentation tidbit, please: >>> > >> >> I think this might be a good thing to mention somewhere, but can you >> make a more specific proposal about where you think it should be put, >> maybe in the form of a patch? > > Thank you very much, Robert. I propose it be appended to the 8.4 > comprehensive manual, Chapter 53.2 TOAST. (And the 9 manual, if it's > true for 9. I haven't touched 9 yet but I'm assuming this hasn't > changed?) > > Here is the patch for 9.0.2 DocBook SGML: > > *** storage.sgml.orig 2011-01-08 10:38:23.000000000 -0500 > --- storage.sgml 2011-01-08 10:48:53.000000000 -0500 > *************** > *** 404,409 **** > --- 404,421 ---- > comparison table, in which all the HTML pages were cut down to 7 kB to fit. > </para> > > + <para> > + To find the parent table given a <acronym>TOAST<acronym> table which has a > + name like pg_toast_12513885, run: > + </para> > + > + <para> > + <programlisting> > + select oid::regclass from pg_class where > + reltoastrelid='pg_toast_12513885'::regclass; > + </programlisting> > + </para> > + > </sect1> > > <sect1 id="storage-fsm"> > > > And here is the patch for the 8.4.6 documentation: > > *** storage.sgml.orig 2011-01-08 10:56:04.000000000 -0500 > --- storage.sgml 2011-01-08 10:56:06.000000000 -0500 > *************** > *** 374,379 **** > --- 374,393 ---- > comparison table, in which all the HTML pages were cut down to 7 kB to fit. > </para> > > + > + <para> > + To find the parent table given a <acronym>TOAST<acronym> table which has a > + name like pg_toast_12513885, run: > + </para> > + > + <para> > + <programlisting> > + select oid::regclass from pg_class where > + reltoastrelid='pg_toast_12513885'::regclass; > + </programlisting> > + </para> > + > + > </sect1> > > <sect1 id="storage-fsm"> > > > Yours truly, > Aleksey >
On Wed, Jan 19, 2011 at 4:01 PM, Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote: > Hi, how about my more specific proposal, in the form of a patch, please? (it is > below in my quoted email of 8 Jan 2011.) > > It's my first time offering a patch for PostgreSQL documentation, please let > me know if I've done it right or if I should change something. The patch you've proposed doesn't apply to the master branch for me, but I guess what I'm more concerned about is that I don't think the next fits very well in the spot you've proposed to put it. The docs already contain this paragraph: <para> If any of the columns of a table are <acronym>TOAST</>-able, the table will have an associated <acronym>TOAST</> table, whose OID is stored in the table's <structname>pg_class</>.<structfield>reltoastrelid</> entry. Out-of-line <acronym>TOAST</>ed values are kept in the <acronym>TOAST</> table, as described in more detail below. </para> So if we need this, it seems like it ought to go right next to that, rather than farther down. But in fact, that paragraph pretty much already contains the information you're proposing to add, just phrased slightly differently. So I'm not sure any change is warranted. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Dear Robert, I understand, thank you. I didn't know how to get the parent table name, that is to say, didn't know what SQL query to run, not being familiar with postgres's internal catalog. I might just drop this as a little how-to into the Wiki for other PostgreSQL newbies like myself. I do agree this data is essentially already in the manual. Thank you for getting back to me! Best, Aleksey