Adding new columns - bug
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 ;)
This happens in 6.1 and 6.2, not able to test 6.3 yet..... Sorry typo...
should be 8.x ofcourse.
Show quoted text
On 11/02/2008, Michael Andreasen <michael@dunlops.com> wrote:
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 | 26315Other 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 ;)
"Michael Andreasen" <michael@dunlops.com> writes:
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.
Perhaps you should post the triggers or rules you have on this table.
SQL generally expands things like "select *" when you define things so it may
well have stored a definition which is out of date and causes this problem.
8.3 may actually fix it for you because if they're plpgsql functions then they
will replan any cached query plans.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!