pá 7. 2. 2020 v 18:28 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal:
> > > On 07.02.2020 18:15, Robert Haas wrote: > > On Wed, Feb 5, 2020 at 10:48 AM Konstantin Knizhnik > > <k.knizh...@postgrespro.ru> wrote: > > My answer is - yes. > >> Just because: > >> - Such behavior is compatible with regular tables. So it will not > >> confuse users and doesn't require some complex explanations. > >> - It is compatible with Oracle. > >> - It is what DBA usually want when creating index. > >> - > >> There are several arguments against such behavior: > >> - Concurrent building of index in multiple sessions can consume a lot of > >> memory > >> - Building index can increase query execution time (which can be not > >> expected by clients) > > I think those are good arguments, especially the second one. There's > > no limit on how long building a new index might take, and it could be > > several minutes. A user who was running a query that could have > > completed in a few seconds or even milliseconds will be unhappy to > > suddenly wait a long time for a new index to be built. And that is an > > entirely realistic scenario, because the new index might be better, > > but only marginally. > Yes, I agree that this arguments are important. > But IMHO less important than incompatible behavior (Pavel doesn't agree > with word "incompatible" in this context > since semantic of temp tables is in any case different with semantic of > regular tables). > > Just want to notice that if we have huge GTT (so that creation of index > takes significant amount of time) > sequential scan of this table also will not be fast. > > But in any case, if we agree that we can control thus behavior using GUC > or index property, > then it is ok for me. > > > > > > > Also, an important point to which I've already alluded a few times is > > that creating an index can fail. Now, one way it can fail is that > > there could be some problem writing to disk, or you could run out of > > memory, or whatever. However, it can also fail because the new index > > is UNIQUE and the data this backend has in the table doesn't conform > > to the associated constraint. It will be confusing if all access to a > > table suddenly starts complaining about uniqueness violations. > > Yes, building index can fail (as any other operation with database). > What's wring with it? > If it is fatal error, then backend is terminated and content of its temp > table is disappeared. > If it is non-fatal error, then current transaction is aborted: > > > Session1: > postgres=# create global temp table gtt(x integer); > CREATE TABLE > postgres=# insert into gtt values (generate_series(1,100000)); > INSERT 0 100000 > > Session2: > postgres=# insert into gtt values (generate_series(1,100000)); > INSERT 0 100000 > postgres=# insert into gtt values (1); > INSERT 0 1 > What when session 2 has active transaction? Then to be correct, you should to wait with index creation to end of transaction. > Session1: > postgres=# create unique index on gtt(x); > CREATE INDEX > > Sessin2: > postgres=# explain select * from gtt where x=1; > ERROR: could not create unique index "gtt_x_idx" > DETAIL: Key (x)=(1) is duplicated. > This is little bit unexpected behave (probably nobody expect so any SELECT fail with error "could not create index" - I understand exactly to reason and context, but this side effect is something what I afraid. > > > I don't believe that the feature you are proposing can be correctly > > implemented in 10 lines of code. I would be pleasantly surprised if it > > can be done in 1000. > > > Right now I do not see any sources of extra complexity. > Will be pleased if you can point them to me. > > -- > Konstantin Knizhnik > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > >
