Thread: 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. 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 | 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 ;) > > >
"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!