Skip site navigation (1) Skip section navigation (2)

Re: unlogged tables

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 (view raw or flat)
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: relax-sync-commit-v1.patch
Description: application/octet-stream (3.1 KB)
Attachment: relpersistence-v3.patch
Description: application/octet-stream (65.6 KB)
Attachment: unlogged-tables-v3.patch
Description: application/octet-stream (52.5 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Jeff JanesDate: 2010-12-01 04:32:23
Subject: Re: profiling connection overhead
Previous:From: Greg SmithDate: 2010-12-01 04:25:47
Subject: Re: Spread checkpoint sync

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group