On Thu, 4 Jun 2020 at 17:59, Tim Dawborn <tim.dawborn@gmail.com> wrote:
> tmp=> \timing on
> Timing is on.
> tmp=> BEGIN;
> BEGIN
> Time: 1.333 ms
> tmp=> ALTER TABLE foo ADD COLUMN d integer NULL;
> ALTER TABLE
> Time: 1.581 ms
> tmp=> CREATE UNIQUE INDEX myindex ON foo (a, b, c) where d = 2;
> CREATE INDEX
> Time: 37758.880 ms (00:37.759)
> tmp=> COMMIT;
> COMMIT
> Time: 3.922 ms
>
> Given that d = 2 could not ever be true as the nullable, non-default-valued column was just added inside the same
transaction,I was hoping that the index creation would be instantaneous, as it realised there's no rows that this
conditioncould be true for.
While it could be technically possible to do something like check the
xmin of the pg_attribute record for all columns mentioned in the
index's predicate all are set to the current transaction ID and the
index predicate refutes an expression containing those columns with IS
NULL clauses or whatever the DEFAULT expression value is, we've just
no way to know if any rows were inserted or updated between the ALTER
TABLE and the CREATE INDEX. Certainly, no other transaction could have
done anything since we added the column due to us holding the
AccessExclusiveLock. We just don't really have a way to know if our
own transaction did anything in between. So to do this, we'd need to
invent something to track that, and that something wouldn't be free to
maintain and we'd likely need to maintain it all the time since we'd
be unable to predict what future commands might need to use that
information.
It would likely be easier to go about it by having some sort of ALTER
TABLE ADD INDEX command, then just chain the alter tables together. We
have various other optimisations when multiple subcommands are used in
a single ALTER TABLE. However, I'm not suggesting we go and allow
indexes to be created in ALTER TABLE. I"m just suggesting that it
would likely be a better alternative than inventing something to track
when a table last had
David
