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

unlogged tables

From: Andy Colson <andy(at)squeakycode(dot)net>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: unlogged tables
Date: 2010-12-01 03:36:54
Message-ID: 4CF5C2D6.7060102@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-hackers
I have played around a little more, and think I found a problem.

If given enough time, an unlogged table makes it to disk, and a restart wont clear the data.  If I insert a bunch of stuff, commit, and quickly restart PG, it table is cleared.  If I let it sit for a while, it stays.

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




I also did kill -9 on all the postgres* processes, while they were busy inserting records, to try to corrupt the database.  But could not seem to.  Setting fsync off also did not give me errors, but I assume because I was using unlogged tables, and they were all getting cleared anyway, I never saw them.

With fsync off and normal tables, I got bad looking things in my logs and vacuum:

LOG:  unexpected pageaddr 1/AB1D6000 in log file 1, segment 187, offset 1925120
WARNING:  relation "access" page 28184 is uninitialized --- fixing
etc...


AND last, I tried to update my git repo and see if the patches still work. They do not.

There was much discussion on the syntax:

create unlogged table vs create temp xxx table vs something else.

There was much discussion on how persistent the tables should be.  And some on backups.

At this point, though, I find myself at an end, not sure what else to do until the dust settles.

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*
The only way I found was to "pg_dump -s"


I will attempt to link this to the website, and mark it as returned to author.

-Andy

Responses

pgsql-hackers by date

Next:From: KaiGai KoheiDate: 2010-12-01 03:59:46
Subject: Re: [GENERAL] column-level update privs + lock table
Previous:From: Hitoshi HaradaDate: 2010-12-01 03:30:46
Subject: Re: SQL/MED - core functionality

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