Thread: Does converting an indexed varchar to text rewrite its index? Docssay so, tests say no.

I think the docs say that if you convert a varchar to text, it'll rewrite the index, but my test doesn't seem to indicate that. Is the test or the documentation wrong?

If the docs, I'll be happy to make a fix my first contribution to postgresql. :)

Here are the docs:

(https://www.postgresql.org/docs/10/sql-altertable.html)

> [...] changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt.

And the test:

postgres=# CREATE TABLE t1 (id serial PRIMARY KEY, name character varying(30));
CREATE TABLE
Time: 25.927 ms
postgres=# INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
INSERT 0 1000000
Time: 2080.416 ms (00:02.080)
postgres=# CREATE INDEX ON t1 (name);
CREATE INDEX
Time: 463.373 ms <-- Index takes ~500ms to build
postgres=# ALTER TABLE t1 ALTER COLUMN name TYPE text;
ALTER TABLE
Time: 19.698 ms <-- Alter takes 20ms to run (no rebuild, right?)

Thanks!

Mike

On 1/23/20 8:55 AM, Mike Lissner wrote:
> I think the docs say that if you convert a varchar to text, it'll 
> rewrite the index, but my test doesn't seem to indicate that. Is the 
> test or the documentation wrong?
> 
> If the docs, I'll be happy to make a fix my first contribution to 
> postgresql. :)
> 
> Here are the docs:
> 
> (https://www.postgresql.org/docs/10/sql-altertable.html)
> 
>  > [...] changing the type of an existing column will require the entire 
> table and its indexes to be rewritten. As an exception when changing the 
> type of an existing column, if the USING clause does not change the 
> column contents and the old type is either binary coercible to the new 
> type or an unconstrained domain over the new type, a table rewrite is 
> not needed; but *any indexes on the affected columns must still be rebuilt.*
> 
> And the test:
> 
> postgres=# CREATE TABLE t1 (id serial PRIMARY KEY, name character 
> varying(30));
> CREATE TABLE
> Time: 25.927 ms
> postgres=# INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
> INSERT 0 1000000
> Time: 2080.416 ms (00:02.080)
> postgres=# CREATE INDEX ON t1 (name);
> CREATE INDEX
> Time: 463.373 ms *<-- Index takes ~500ms to build*
> postgres=# ALTER TABLE t1 ALTER COLUMN name TYPE text;
> ALTER TABLE
> Time: 19.698 ms *<-- Alter takes 20ms to run (no rebuild, right?)*


I going to say it is the exception to the exception, in that in Postgres 
varchar and text are essentially the same type.

FYI there is a reindex going on:

test=> set client_min_messages = debug1;
test=>  CREATE TABLE t1 (id serial PRIMARY KEY, name character varying(30));
LOG:  statement: CREATE TABLE t1 (id serial PRIMARY KEY, name character 
varying(30));
DEBUG:  CREATE TABLE will create implicit sequence "t1_id_seq" for 
serial column "t1.id"
DEBUG:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" 
for table "t1"
DEBUG:  building index "t1_pkey" on table "t1" serially
CREATE TABLE
test=> INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
LOG:  statement: INSERT INTO t1 (id) SELECT generate_series(1,1000000) i;
INSERT 0 1000000
test=>  CREATE INDEX ON t1 (name);
LOG:  statement: CREATE INDEX ON t1 (name);
DEBUG:  building index "t1_name_idx" on table "t1" with request for 1 
parallel worker
CREATE INDEX
test=> ALTER TABLE t1 ALTER COLUMN name TYPE text;
LOG:  statement: ALTER TABLE t1 ALTER COLUMN name TYPE text;
DEBUG:  building index "pg_toast_37609_index" on table "pg_toast_37609" 
serially
ALTER TABLE

> 
> Thanks!
> 
> Mike
> **


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 1/23/20 11:17 AM, Mike Lissner wrote:
> Thanks Adrian. Is there a reason that the index rebuild is nearly 
> instant during the ALTER command as opposed to when you build it from 
> scratch?

Well it did not rebuilt the index("t1_name_idx") you created on name.

> 
> Does it have to do with why this is called a "toast" index?

Certain data types(those that have varlena) can have portions of their 
data stored in an auxiliary table in a compressed(or not) form. For all 
the details see:

https://www.postgresql.org/docs/12/storage-toast.html

The index is the one on this auxiliary table.

> 
> DEBUG:  building index "pg_toast_37609_index" on table "pg_toast_37609"
> 
> Thanks for the feedback. I really appreciate it and it's super 
> interesting to learn about.
> 
> Mike
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 1/23/20 1:28 PM, Mike Lissner wrote:
> You wrote:
> 
>   > Well it did not rebuilt the index("t1_name_idx") you created on name.
> 
> OK, so then the docs *are* wrong? They say that:
> 
>  > any indexes on the affected columns must still be rebuilt.
> 
> But that doesn't happen? Sorry to be persistent. I'm just a bit confused 
> here.

My guess is that it is because in Postgres varchar is just text with an 
optional length restriction. I say optional because you can do:

CREATE TABLE t2 (id serial PRIMARY KEY, name varchar);

So as I understand it when you are go from varchar to text you are not 
really changing type, just the type oid. I tried searching the source 
for confirmation of this to no avail. A definitive answer is going to 
need come from someone with more knowledge of the internals.




-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 1/23/20 1:28 PM, Mike Lissner wrote:
>> OK, so then the docs *are* wrong? They say that:
>>> any indexes on the affected columns must still be rebuilt.
>> But that doesn't happen? Sorry to be persistent. I'm just a bit confused 
>> here.

> My guess is that it is because in Postgres varchar is just text with an 
> optional length restriction.

More directly, it's because varchar piggybacks on text's operators and
index opclass.  If we've decided that we don't need to rewrite the table,
and if indexcmds.c's CheckIndexCompatible() says that the old and new
index definitions are 100% compatible, then we skip rebuilding the index
contents as well.  But you need the same opclasses to be compatible.

Commit 367bc426a, which added that behavior, seems to have been a lot
lazier about updating the user-facing docs than it should've been.
I can agree with the position that all the weird little cases in
CheckIndexCompatible() are a bit much to be documenting, but not
changing the ALTER TABLE reference page at all seems inadequate.

            regards, tom lane

On Thu, 2020-01-23 at 08:55 -0800, Mike Lissner wrote:
> I think the docs say that if you convert a varchar to text, it'll rewrite the index,
> but my test doesn't seem to indicate that. Is the test or the documentation wrong?
> 
> If the docs, I'll be happy to make a fix my first contribution to postgresql. :)
> 
> Here are the docs:
> 
> (https://www.postgresql.org/docs/10/sql-altertable.html)

We'd be happy about a documentation patch.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com