Skip site navigation (1) Skip section navigation (2)

Adding new columns - bug

From: "Michael Andreasen" <michael(at)dunlops(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Adding new columns - bug
Date: 2008-02-11 12:36:08
Message-ID: c752d6bd0802110436p52b665c2pa2b27897fe8b2203@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
I have a found a bug when adding a new column to an existing table. This
happens in 6.1 and 6.2, not able to test 6.3 yet.

This is the process I am doing;

alter table product add column search_priority int default 0;
update product set  search_priority = 0;
search_priority,count(*) from product group by 1;
 search_priority | count
-----------------+-------
               0 | 26462
(1 row)

This is as expected so far.

However "product" is a very busy table and is updated every few seconds. If
I wait for a few minutes then perform the same select again I get this;

search_priority,count(*) from product group by 1;
 search_priority | count
-----------------+-------
                 |   147
               0 | 26315

Other processes are updating the product table, they NOT updating the
"search_priority" and have no knowledge of it. It seems that when they
update any column on the row the newly added field is reset to null.

If I drop the the table and reload from a dump all works well, so this
behavior only happens on columns added via an alter table.

There is nothing sepcial about the product table other than it has a couple
of trigger rules to post to other tables on updates of some fields (audit
log), nothing to cause this behavior. I have only noticed this happening of
this table, so I am guessing it's related to it having rules, since it's one
of the few that does.

Is this a known bug at all?

PS, been using postgreSQL for about 6 years and this is the ONLY bug i've
come across, so though i'd better report it ;)

Responses

pgsql-bugs by date

Next:From: Michael AndreasenDate: 2008-02-11 12:38:31
Subject: Re: Adding new columns - bug
Previous:From: Magnus HaganderDate: 2008-02-11 08:35:03
Subject: Re: BUG #3948: date/time functions returning wrong value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group