[doc] improve tableoid description
[doc] improve tableoid description
Hi
Attached patch aims to improve the description of the tableoid system column [1]https://www.postgresql.org/docs/current/ddl-system-columns.html
by:
- mentioning it's useful for determining table names for partitioned tables as
well as for those in inheritance hierarchies
- mentioning the possibility of casting tableoid to regclass (which is simpler
than the currently suggested join on pg_class, which is only needed if
the schema name is absolutely required)
[1]: https://www.postgresql.org/docs/current/ddl-system-columns.html
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com
Attachments:
doc-tableoid-regclass.v1.patchtext/x-patch; charset=US-ASCII; name=doc-tableoid-regclass.v1.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index c4897d68c9..f916f0bd70 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1144,12 +1144,18 @@ CREATE TABLE circles (
<para>
The OID of the table containing this row. This column is
- particularly handy for queries that select from inheritance
+ particularly handy for queries that select from partitioned
+ tables (see <xref linkend="ddl-partitioning"/>) or inheritance
hierarchies (see <xref linkend="ddl-inherit"/>), since without it,
it's difficult to tell which individual table a row came from. The
- <structfield>tableoid</structfield> can be joined against the
+ <structfield>tableoid</structfield> can be cast to <type>regclass</type>
+ to obtain the table name. However the table's schema will only be shown
+ if the table is <emphasis>not</emphasis> in the default path, otherwise
+ <structfield>tableoid</structfield> will need to be joined against the
<structfield>oid</structfield> column of
- <structname>pg_class</structname> to obtain the table name.
+ <link linkend="catalog-pg-class"><structname>pg_class</structname></link>
+ to obtain the <structfield>relnamespace</structfield> OID (which itself
+ can be cast to <type>regnamespace</type> to obtain the schema name).
</para>
</listitem>
</varlistentry>
On Sat, Oct 17, 2020 at 6:35 PM Ian Lawrence Barwick <barwick@gmail.com> wrote:
[doc] improve tableoid description
Hi
Attached patch aims to improve the description of the tableoid system column [1]
by:- mentioning it's useful for determining table names for partitioned tables as
well as for those in inheritance hierarchies
This looks fine.
- mentioning the possibility of casting tableoid to regclass (which is simpler
than the currently suggested join on pg_class, which is only needed if
the schema name is absolutely required)
Mentioning casting to regclass is worthwhile but it's not performance
efficient if there are many tableoids. In that case, joining with
pg_class.oid is quite efficient. That line further suggests using
regnamespace which is not as efficient as joining with
pg_namespace.oid. But pg_namespace won't have as many entries as
pg_class so casting to regnamespace might be fine. Should we suggest
both the methods somehow?
--
Best Wishes,
Ashutosh Bapat
2020年10月19日(月) 20:22 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>:
On Sat, Oct 17, 2020 at 6:35 PM Ian Lawrence Barwick <barwick@gmail.com> wrote:
[doc] improve tableoid description
Hi
Attached patch aims to improve the description of the tableoid system column [1]
by:- mentioning it's useful for determining table names for partitioned tables as
well as for those in inheritance hierarchiesThis looks fine.
- mentioning the possibility of casting tableoid to regclass (which is simpler
than the currently suggested join on pg_class, which is only needed if
the schema name is absolutely required)Mentioning casting to regclass is worthwhile but it's not performance
efficient if there are many tableoids. In that case, joining with
pg_class.oid is quite efficient.
True.
That line further suggests using
regnamespace which is not as efficient as joining with
pg_namespace.oid. But pg_namespace won't have as many entries as
pg_class so casting to regnamespace might be fine. Should we suggest
both the methods somehow?
On further reflection, I think trying to explain all that is going to
end up as a
mini-tutorial which is beyond the scope of the explanation of a column, so
the existing reference to pg_class should be enough.
Revised patch attached just mentioning partitioned tables.
Regards
Ian Barwick
--
EnterpriseDB: https://www.enterprisedb.com
Attachments:
doc-tableoid-regclass.v2.patchtext/x-patch; charset=US-ASCII; name=doc-tableoid-regclass.v2.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index c4897d68c9..7b77e9ddfb 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1144,7 +1144,8 @@ CREATE TABLE circles (
<para>
The OID of the table containing this row. This column is
- particularly handy for queries that select from inheritance
+ particularly handy for queries that select from partitioned
+ tables (see <xref linkend="ddl-partitioning"/>) or inheritance
hierarchies (see <xref linkend="ddl-inherit"/>), since without it,
it's difficult to tell which individual table a row came from. The
<structfield>tableoid</structfield> can be joined against the
On Mon, Oct 19, 2020 at 5:58 PM Ian Lawrence Barwick <barwick@gmail.com> wrote:
That line further suggests using
regnamespace which is not as efficient as joining with
pg_namespace.oid. But pg_namespace won't have as many entries as
pg_class so casting to regnamespace might be fine. Should we suggest
both the methods somehow?On further reflection, I think trying to explain all that is going to
end up as a
mini-tutorial which is beyond the scope of the explanation of a column, so
the existing reference to pg_class should be enough.
Revised patch attached just mentioning partitioned tables.
From a user's point of view, it makes sense to differentiate between
partitioning and inheritance, though internally the first uses the
later.
Maybe we could just generalize the sentence as "tableoid can be used
to obtain the table name either by joining against the oid column of
pg_class or casting it to regclass as appropriate." Or just ""tableoid
can be used to obtain the table name.". Probably the users would find
out how to do that from some other part of the document.
<structfield>tableoid</structfield> can be joined against the
<structfield>oid</structfield> column of
<structname>pg_class</structname> to obtain the table name.
But even without that change, the current patch is useful. Please add
it to commitfest so it's not forgotten.
--
Best Wishes,
Ashutosh Bapat
On 2020-10-19 14:28, Ian Lawrence Barwick wrote:
On further reflection, I think trying to explain all that is going to
end up as a
mini-tutorial which is beyond the scope of the explanation of a column, so
the existing reference to pg_class should be enough.Revised patch attached just mentioning partitioned tables.
committed
2020年11月21日(土) 16:29 Peter Eisentraut <peter.eisentraut@enterprisedb.com>:
On 2020-10-19 14:28, Ian Lawrence Barwick wrote:
On further reflection, I think trying to explain all that is going to
end up as a
mini-tutorial which is beyond the scope of the explanation of a column, so
the existing reference to pg_class should be enough.Revised patch attached just mentioning partitioned tables.
committed
Thanks!
--
EnterpriseDB: https://www.enterprisedb.com