Re: unlogged tables

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