On 15.10.2013 03:44, Huang, Suya wrote:
> Thanks Tomas!
>
> However, in the example I sent, I already did a vacuum full right
> after deleted the rows causing problem, before created the index and
> got an error even the table is vacuumed. Note, the table is I
> temporarily created using create table as select *... so no other
> people is accessing that table, except me for the testing purpose.
>
> Any ideas? And today, while I did the same thing, I can create index
> on the table right after I deleted the rows causing problem, without
> vacuum.
>
> Anything I missed here?
Not sure. The only thing I can think of is another transaction
preventing the autovacuum from removing the rows, but I can't reproduce
it. What PostgreSQL version are you using?
BTW take a look at this contrib module:
http://www.postgresql.org/docs/9.1/static/pageinspect.html
It allows looking directly on items on a data page like this:
select * from heap_page_items(get_raw_page('test', 0));
Maybe that'll help you identify the problem.
Tomas