pgAdmin III Reindex ERROR

[shuman]

Hello. For a few days now, each time I REINDEX my holdemmanager Database in pgAdmin III, I get the following error when it stops runnning:


NOTICE: table "rakeback" was reindexedERROR: concurrent delete in progress

ERROR: concurrent delete in progress

The "Done" button doesn't appear now, and when I click "OK" the same ERROR keeps occurring at the end of each running.

I usually REINDEX each time after I FULL VACUUM, and this ERROR never occurred until only a few days ago. VACCUUM is working fine.

What should I do to fix this problem?

Thank you.

HEM Professional 1.10.06a

[_Loki_]

Hi Shuman

1] Create a new database - it is very easy to do:
FAQ - Hold'em Manager Poker Tracking Software :: Create / Delete Database (http://faq.holdemmanager.com/questions/75/Create+%7B47%7D+Delete+Database)

2] Make sure the new DB is set as the default DB (this happens anyway when you create a DB) & import all your hands from the HMArchive
FAQ - Hold'em Manager Poker Tracking Software :: How to Import & Export Hands (http://faq.holdemmanager.com/questions/98/How+to+Import+%26+Export+Hands)

3] Set your hero name.

[ 4] extra step that I take because it seems like a good idea: Close HM. Reboot PC. Open HM]

5] Check that the new DB works correctly & the figures are accurate

6] Delete old DB

NOTE: IMO although you are on old version of HM - Stay on 1.10.06a because the current one is nothing but trouble

###
Questions:
1] I think you have a corrupt DB which might have been caused by a number of things such as while importing you had a system crash or a sudden loss of power or you opened a file that was being used. I remember you had a crash a few weeks ago - It's worth thinking about why this is happening to prevent it in the future
2] How often are you doing full vacuum/reindex ? How are you deciding when to do one ? I don't think it's caused your problem, but the way you write about it - it's like you do it very often :)

[shuman]

Hi _Loki_,

1] Yes, a few weeks ago my DB got corrupted because my computer froze and I had to remove the battery pack. I'm not sure what caused this current issue though. I now have a new, faster router to use with my ethernet cable, so hopefully this helps.

2] I usually do a full vacuum/reindex after EVERY session (usually about 3-5000 hands). Is this too often? If so, how often should I do these 2 things?

Also, is it even necessary for me to do a reindex each time I do a full vacuum? My vacuum still works fine. It's just the reindex that causes an error every time now.

If the reindex is not necessary maybe I wouldn't need to create a new database.

Thank you.

[netsrak]

2) There is no need to do this after every session. Once a month is enough

A reindex after the vacuum frees a little bit more space and does some more optimizations so its usually good to do it after the vacuum.

The error message you posted doesn't look like a corrupt database - its more like a access problem. You should reboot your computer and try the reindex as first thing after the reboot. Does it give the same error message?

[shuman]

Hi netsrak,

After I just started up my computer and read your post I tried the reindex and got the same error message below. The last 2 entries in the Messages tab read:


NOTICE: table "rakeback" was reindexedERROR: concurrent delete in progress

ERROR: concurrent delete in progress

So, do you think I still need to create a new database to fix this reindex error?

Thanks.

[netsrak]

Thats strange. There is something wrong with your database.

As long as you have no other problems you can continue working with this database.

But i strongly recommend making regular backups: FAQ - Hold'em Manager Poker Tracking Software :: Backup & Restore (http://faq.holdemmanager.com/questions/418/Backup+%26+Restore)

[shuman]

netsrak,

I successfully made a database Backup and saved it to my Desktop following the video in the link you provided above. Do you think I could now fix my Reindex ERROR by uninstalling/reinstalling HEM and PostgreSQL and then using the same link above to Restore my saved database?

Or, should I just create a new database in my current programs?

Thank you.

[netsrak]

There is no need to reinstall HM. Its just a postgres issue.

Create a new database via pgadminIII and restore the backup to this database.
If the new database is up and running and you worked with it for some days you can delete the old database via pgadminIII

[shuman]

After all this discussion and my learning about creating a new database, I was wondering if you recommend that I create a new database after I accumulate a certain number of saved total hands (ie: 1 million)?

My main reason for doing this would be so that my computer runs more efficiently. Do you know if there's a certain number of saved hands at which the large size of my database might start to effect the speed of my computer?

Thank you.

[_Loki_]

Just an opinion...

So it was a corrupt DB probably... IMO the secret to a stable DB (or DB's) is being systematic & realistic. I have a monthly maintenance routine where I clear out hands older than 3 months & vacuum what's left
Also why restrict yourself to one DB ?

###
If you're buying datamined hands are they any use after 3 months ?
If these hypothetical 1,000,000 hands are hands played by you - are the hands older than say 3 months any use ?

###
If I were you... I would ask the questions - "what is HM (& the DB) for & how do I make it do what I want ? ". So as an example I have this in my case where I've got 3 active DB's that I use in play & one DB 'historical' interest...

###
HISTORICAL:

DB all = every hand I've ever played online
[to see how I'm running profit/loss & how hard I'm working in terms of hands played]

###
ACTIVE PLAYING - switch DB's depending on what I'm playing:

DB 6max = cash 6max (3months or newer hands)
[Important core game]

DB sng = stt sng (3months or newer hands)
[Less important at the moment - only learning sng - don't want to infect 6max hands with these hands]

DB mtt = mtt (Low buyin less than $3)
[Even less important - don't want to infect as above - but some of those tourneys where PS adds $1,000 & only 1.5k-3k entries....]

###
The point is (in my case) my active playing DB's can't grow by much more than 65k hands a month (I don't buy hands), but I'm dumping a months worth of hands from 4 months ago every month so the active DB's reach a certain size & then stabilise - no lag problems

[shuman]

That makes sense what you say and do. I guess I've never taken the time to analyze it like that. I haven't even checked how many total players' hands I have saved in my 1 and only DB until today--including just my 730,000 cash game hands from $1/2 NLHE fuul ring mostly on Full Tilt but also some on Stars, I have over 5.66 million total hands saved on my one DB.

I also play MTT sometimes, but only ones that have BIG final table payouts.

So, I guess I could have 2 DB right now by your analysis--1 historical like I guess I already have now, 1 three-month or less just for $1/2 NLHE full ring, and also 1 just for MTT.

I could also do 4 or more DB if i start playing on Stars more.

Thanks for your input.

[_Loki_]

Let me know how it goes if you implement some of my ideas

Also.. When I 'get rid' of a months worth of hands each month I don't use purge. I create a new DB & import 3 months of raw HH's & delete the old DB & then vacuum/ defrag the HDD. This is my quirk I guess, but it works & the 'purge' option is currently 'buggy'

Obviously to do this every month requires being organised & it doesn't suit most people

[shuman]

netsrak,

Just wanted to let you know that by my making a Backup of my messed up original database and Restoring it into a new database has fixed my re-indexing error problem.

Thanks as always.

[netsrak]

Thanks