Re: Add protransform for numeric, varbit, and temporal types

On Tue, Feb 07, 2012 at 12:43:11PM -0500, Robert Haas wrote:
> I've committed the numeric and varbit patches and will look at the
> temporal one next.

Thanks. The comment you added to numeric_transform() has a few typos,
"constained" -> "constrained" and "nodes" -> "notes".

> I did notice one odd thing, though: sometimes we
> seem to get a rebuild on the toast index for no obvious reason:
>
> rhaas=# set client_min_messages=debug1;
> SET
> rhaas=# create table foo (a serial primary key, b varbit);
> NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for
> serial column "foo.a"
> DEBUG: building index "pg_toast_16430_index" on table "pg_toast_16430"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "foo_pkey" for table "foo"
> DEBUG: building index "foo_pkey" on table "foo"
> CREATE TABLE
> rhaas=# alter table foo alter column b set data type varbit(4);
> DEBUG: rewriting table "foo"
> DEBUG: building index "foo_pkey" on table "foo"
> ALTER TABLE

When we rebuild the table at this point, it has a small maximum tuple size.
Therefore, we omit the toast table entirely.

> rhaas=# alter table foo alter column b set data type varbit;
> DEBUG: building index "pg_toast_16430_index" on table "pg_toast_16430"
> ALTER TABLE

This command makes the tuple size unbounded again, so we create a toast table.

> Strangely, it doesn't happen if I add another column to the table:
>
> rhaas=# set client_min_messages=debug1;
> SET
> rhaas=# create table foo (a serial primary key, b varbit, c varbit);

With the extra unconstrained varbit column, the tuple size is continuously
unbounded and the table has a toast table at all stages. So, the difference
in behavior is correct, albeit unintuitive.

> NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for
> serial column "foo.a"
> DEBUG: building index "pg_toast_16481_index" on table "pg_toast_16481"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "foo_pkey" for table "foo"
> DEBUG: building index "foo_pkey" on table "foo"
> CREATE TABLE
> rhaas=# alter table foo alter column b set data type varbit(4);
> DEBUG: building index "pg_toast_16490_index" on table "pg_toast_16490"
> DEBUG: rewriting table "foo"
> DEBUG: building index "foo_pkey" on table "foo"
> ALTER TABLE
> rhaas=# alter table foo alter column b set data type varbit;
> ALTER TABLE