Thread: [doc] improve tableoid description

[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
- 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

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 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.

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

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