Re: logical column position

Neil Conway wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > I don't think you can speak of "bloat" for pg_attribute. But you
> > can speak of a problem when you want to do the old col = col + 1 in
> > the presence of a unique index.
>
> I'm sorry, but I'm not sure what either of these comments mean -- can
> you elaborate?

Peter is pointing out a problem with our unique indexes that might
cause you a problem. Suppose you have a unique index in attlognum:

test=> create table xx( lognum int);
CREATE TABLE
test=> insert into xx values (1);
INSERT 17145 1
test=> insert into xx values (2);
INSERT 17146 1
test=> update xx set lognum = lognum + 1;
UPDATE 2
test=> create unique index yy on xx (lognum);
CREATE INDEX
test=> update xx set lognum = lognum + 1;
ERROR: duplicate key violates unique constraint "yy"

There is discussion to delay unique constraint failures until commit,
then recheck them to see if they are still valid, sort of like what we
do with deferred triggers. This would fix the problem because on
commit, those values are unique, but aren't while the rows are updated
invidually. If we don't get that working you might want to use the 1000
gap idea because it doesn't cause this problem, and we don't support
>1600 columns, so a 1000 gap shouldn't cause a problem and can be
modified later. If they hit 999 updates, just tell them to dump/reload
the table.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073