Horton

Re: [Proposal] Global temporary tables

Pavel Stehule <pavel.stehule@gmail.com>

View thread
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>, Robert Haas <robertmhaas@gmail.com>, 曾文旌(义从) <wenjing.zwj@alibaba-inc.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>, 蔡松露(子嘉) <zijia@taobao.com>, "Cai, Le" <le.cai@alibaba-inc.com>, 张广舟(明虚) <guangzhou.zgz@alibaba-inc.com>, 赵殿奎 <diankui.zdk@alibaba-inc.com>, 萧少聪(铁庵) <shaocong.xsc@alibaba-inc.com>
Date: 2019-11-02T07:23:05Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. PageAddItemExtended(): Add LP_UNUSED assertion.

  2. Remove temporary files after backend crash

  3. Fix comment in indexing.c

  4. Fix failure to ignore leftover temp tables after a server crash.

so 2. 11. 2019 v 8:18 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:

> On Sat, Nov 2, 2019 at 6:31 AM Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
> >
> > pá 1. 11. 2019 v 17:09 odesílatel Konstantin Knizhnik <
> k.knizhnik@postgrespro.ru> napsal:
> >>
> >> On 01.11.2019 18:26, Robert Haas wrote:
> >> > On Fri, Nov 1, 2019 at 11:15 AM Konstantin Knizhnik
> >> > <k.knizhnik@postgrespro.ru> wrote:
> >> >> It seems to me that I have found quite elegant solution for
> per-backend statistic for GTT: I just inserting it in backend's catalog
> cache, but not in pg_statistic table itself.
> >> >> To do it I have to add InsertSysCache/InsertCatCache functions which
> insert pinned entry in the correspondent cache.
> >> >> I wonder if there are some pitfalls of such approach?
> >> > That sounds pretty hackish. You'd have to be very careful, for
> >> > example, that if the tables were dropped or re-analyzed, all of the
> >> > old entries got removed --
> >>
> >> I have checked it:
> >> - when table is reanalyzed, then cache entries are replaced.
> >> - when table is dropped, then cache entries are removed.
> >>
> >> > and then it would still fail if any code
> >> > tried to access the statistics directly from the table, rather than
> >> > via the caches. My assumption is that the statistics ought to be
> >> > stored in some backend-private data structure designed for that
> >> > purpose, and that the code that needs the data should be taught to
> >> > look for it there when the table is a GTT.
> >>
> >> Yes, if you do "select * from pg_statistic" then you will not see
> >> statistic for GTT in this case.
> >> But I do not think that it is so critical. I do not believe that anybody
> >> is trying to manually interpret values in this table.
> >> And optimizer is retrieving statistic through sys-cache mechanism and so
> >> is able to build correct plan in this case.
> >
> >
> > Years ago, when I though about it, I wrote patch with similar design.
> It's working, but surely it's ugly.
> >
> > I have another idea. Can be pg_statistics view instead a table?
> >
> > Some like
> >
> > SELECT * FROM pg_catalog.pg_statistics_rel
> > UNION ALL
> > SELECT * FROM pg_catalog.pg_statistics_gtt();
> >
> > Internally - when stat cache is filled, then there can be used
> pg_statistics_rel and pg_statistics_gtt() directly. What I remember, there
> was not possibility to work with queries, only with just relations.
>
> It'd be a loss if you lose the ability to see the statistics, as there
> are valid use cases where you need to see the stats, eg. understanding
> why you don't get the plan you wanted.  There's also at least one
> extension [1] that allows you to backup and use restored statistics,
> so there are definitely people interested in it.
>
> [1]: https://github.com/ossc-db/pg_dbms_stats


I don't think - the extensions can use UNION and the content will be same
as caches used by planner.