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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
