From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Andy Colson <andy(at)squeakycode(dot)net> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: unlogged tables |
Date: | 2010-12-01 04:27:35 |
Message-ID: | AANLkTimfXJAJj3wS_VZaAJOL2U8Uw5+7sM-dBZXDSOG5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Nov 30, 2010 at 10:36 PM, Andy Colson <andy(at)squeakycode(dot)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
Attachment | Content-Type | Size |
---|---|---|
unlogged-tables-v3.patch | application/octet-stream | 52.5 KB |
relpersistence-v3.patch | application/octet-stream | 65.6 KB |
relax-sync-commit-v1.patch | application/octet-stream | 3.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2010-12-01 04:32:23 | Re: profiling connection overhead |
Previous Message | Greg Smith | 2010-12-01 04:25:47 | Re: Spread checkpoint sync |