On Tue, Nov 30, 2010 at 10:36 PM, Andy Colson <andy@squeakycode.net> wrote:
> Based on that, I have a pgbench_accounts table (unlogged) that after a
> restart has data in it.
>
>
> andy=# select aid, bid, abalance from pgbench_accounts where abalance =
> 3305;
> aid | bid | abalance
> ---------+-----+----------
> 3790226 | 38 | 3305
> 274130 | 3 | 3305
> 2169892 | 22 | 3305
> 705321 | 8 | 3305
> 4463145 | 45 | 3305
>
> I dropped the index, and added a new one, then restart PG. Now it seems the
> index is empty/unusable.
>
> andy=# select aid, bid, abalance from pgbench_accounts where aid = 3790226;
> aid | bid | abalance
> -----+-----+----------
> (0 rows)
>
>
> andy=# select pg_indexes_size('pgbench_accounts');
> pg_indexes_size
> -----------------
> 16384
>
> Lets recreate it:
>
> andy=# drop index bob;
> DROP INDEX
> Time: 13.829 ms
> andy=# create index bob on pgbench_accounts(aid, bid);
> CREATE INDEX
> Time: 17215.859 ms
> andy=# select aid, bid, abalance from pgbench_accounts where aid = 3790226;
> aid | bid | abalance
> ---------+-----+----------
> 3790226 | 38 | 3305
> (1 row)
>
> Time: 0.712 ms
>
> andy=# select pg_indexes_size('pgbench_accounts');
> pg_indexes_size
> -----------------
> 179716096
This appears as though you've somehow gotten a normal table connected
to an unlogged index. That certainly sounds like a bug, but there's
not enough details here to figure out what series of steps I should
perform to recreate the problem.
> AND last, I tried to update my git repo and see if the patches still work.
> They do not.
Updated patches attached.
> Oh, also, I wanted to add:
>
> There is \h help: +1
> but I can find no way of determining the "tempness"/"unloggedness" of a
> table via \d*
It's clearly displayed in the \d output.
Unlogged Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
Indexes:
"test_pkey" PRIMARY KEY, btree (a)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company