Re: unlogged tables

On 11/30/2010 10:27 PM, Robert Haas wrote:
>
> 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.
>
>> 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)
>
Jeez... Were it a snake it'd a bit me!

Ok. I blew away my database and programs, re-gitted, re-patched (they work), re-compiled (ok), and re-ran initdb.

I have these non-standard settings:
shared_buffers = 512MB
work_mem = 5MB
checkpoint_segments = 7

1st) I can recreate some warning messages from vacuum:
WARNING: relation "ulone" page 0 is uninitialized --- fixing
WARNING: relation "pg_toast_16433" page 0 is uninitialized --- fixing

you create an unlogged table, fill it, restart pg (and it clears the table), then fill it again, and vacuum complains. Here is a log:

andy=# drop table ulone;
DROP TABLE
Time: 40.532 ms
andy=# create unlogged table ulone(id serial, a integer, b integer, c text);
NOTICE: CREATE TABLE will create implicit sequence "ulone_id_seq" for serial column "ulone.id"
CREATE TABLE
Time: 151.968 ms
andy=# insert into ulone(a, b, c) select x, 1, 'bbbbbbbbbbb' from generate_series(1, 10000000) x;
INSERT 0 10000000
Time: 80401.505 ms
andy=# \q

$ vacuumdb -az
vacuumdb: vacuuming database "andy"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"

$ sudo /etc/rc.d/postgresql stop
Stopping PostgreSQL: No directory, logging in with HOME=/

$ sudo /etc/rc.d/postgresql start
Starting PostgreSQL:

$ psql
Timing is on.
psql (9.1devel)
Type "help" for help.

andy=# select count(*) from ulone;
count
-------
0
(1 row)

Time: 1.164 ms
andy=# insert into ulone(a, b, c) select x, 1, 'bbbbbbbbbbb' from generate_series(1, 10000000) x;
INSERT 0 10000000
Time: 75312.753 ms
andy=# \q

$ vacuumdb -az
vacuumdb: vacuuming database "andy"
WARNING: relation "ulone" page 0 is uninitialized --- fixing
WARNING: relation "pg_toast_16478" page 0 is uninitialized --- fixing
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"

2nd) I can get the data to stick around after restart. Though not reliably. In general:

create and fill a table, vacuum it (not sure if its important, I do it because thats what I'd done in my pgbench testing where I noticed the data stuck around), wait an hour (I usually left it for 12-24 hours, but recreated it with as little as a half hour), then restart pg. Sometimes the data is there... sometimes not.

I also filled my table with more data than memory would hold so it would spill to disk, again, because it recreates my pgbench setup.

I'm still working on finding the exact steps, but I wanted to get you #1 above.

-Andy