Script to analyze population tendency on HEM - Page 2 - Heads Up SNG and Spin and Gos -- Two Plus Two Poker Forums

level of logging detail and all manner of other options are found in the file postgresql.conf, changes require restart of postgres server to take effect. where it puts the log files is specified in there too I think. The postgresql.conf file is pretty well commented IIRC, more docs here: http://www.postgresql.org/docs/9.1/s...g-logging.html - the parameter we're probably most interested in is is this one, we want "all":

Quote:

log_statement (enum)

Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). [snip]

The default is none. Only superusers can change this setting.

Also, the easy way to get only HU players is to only import HU games into a fresh database, ldo. It's perhaps unwise to experiment with SQL commands including UPDATEs and INSERTSs on your main, only, all-time database lol.

Quote

06-21-2012 , 02:58 AM

#27

genher

veteran

Join Date: May 2007 Posts: 2,931

YES!!!! I have just created a 17000 players alias on HM2 !

(well actualy it's still loading and might load for a while...but it's working)

Thanks guys ! now finaly, I can really talk population tendancy! That little script should be a very valuable tool indeed!

Last edited by genher; 06-21-2012 at 03:10 AM.

Quote

06-21-2012 , 03:11 AM

#28

genher

veteran

Join Date: May 2007 Posts: 2,931

Quote:

Originally Posted by genher

YES!!!! I have just created a 17000 players alias on HM2 !

spoke too fast ...crashed the whole think. may be 17000 players is a bit too much

I might have to limit that

Quote

06-21-2012 , 05:42 AM

#29

genher

veteran

Join Date: May 2007 Posts: 2,931

it looks like I need to limit the number of players? dunno how many tho! I guess trial and error...How many players have you guys managed to do?

to do that is it just:

Quote:

INSERT INTO aliases
SELECT 11084, p1.player_id
FROM players p1
WHERE p1.player_id NOT IN
(
SELECT a1.player_id
FROM aliases a1
WHERE a1.aliasplayer_id = 11084
-- to exclude players yet inserted
)
AND p1.player_id NOT IN
(
SELECT a2.player_id
FROM players p2 JOIN aliases a2 ON p2.player_id = a2.aliasplayer_id
WHERE p2.pokersite_id = -1 and p2.playername = 'Genher'
-- to exclude hero nicknames from the insert
)
AND p1.pokersite_id <> -1
-- to exclude other aliases
LIMIT 1000
;

Last edited by genher; 06-21-2012 at 05:49 AM.

Quote

06-21-2012 , 06:53 AM

#30

Benjamin the Donk

old hand

Join Date: Mar 2012 Posts: 1,254

You guys are brilliant, I am so trying this on the weekend with PT4. I guess I should backup the DB first? Any chance I will kill it?

Spoiler:

Quote

06-21-2012 , 07:04 AM

#31

I<3Poker

grinder

Join Date: Jan 2012 Posts: 518

Quote:

Originally Posted by Benjamin the Donk

You guys are brilliant, I am so trying this on the weekend with PT4. I guess I should backup the DB first? Any chance I will kill it?

Spoiler:

Just make a backup to be on the safe side. ;-)
Also you may want to optimize database performance before running the query ("Housekeeping" in PT).

http://preview.pokertracker.com/guid...ing-postgresql

Quote

06-21-2012 , 08:23 AM

#32

chaosad

adept

Join Date: Nov 2007 Posts: 969

sorry for the stupid question, but what are the benefits?

Quote

06-21-2012 , 08:24 AM

#33

tigerjack89

grinder

Join Date: Jun 2010 Posts: 460

Quote:

Originally Posted by _dave_

have to read more but seems great, expecially if I can see all the events when I create a new player.

Quote:

Yes, I have different databases, but could be right to select all players that played heads up. However, I think that he selects only 2 max, but tends to exclude MTT heads up.

@genher
I have 11k players on my db, so I don't think this is the problem. But well, you can divide the player into 2 (or more) groups and then insert a group at time.

For example, you can add at the end of the code

Code:

[...]
AND p1.pokersite_id <> -1
AND p1.player_id < 4000

In the next query instead

Code:

AND p1.player_id >= 4000 AND p1.player_id < 8000

and so on.

@benjamin I have very little time to experiment with PT4 cause university exam so your help is very appreciated

And yes, make a backup if you are experimenting and have no idea of SQL

Quote

06-21-2012 , 08:47 AM

#34

tigerjack89

grinder

Join Date: Jun 2010 Posts: 460

Quote:

Originally Posted by chaosad

sorry for the stupid question, but what are the benefits?

Analyze population tendencies.
For example, with HEM1 I can't view all the hands shown by villains in a specific spot without replaying all the hands. With the global alias I can see something like that for example

I know, I have a small sample on this spot, but i think i make the point.

Quote

06-21-2012 , 08:51 AM

#35

Benjamin the Donk

old hand

Join Date: Mar 2012 Posts: 1,254

Yes it's much easier to create a readless strategy or analyse villain's if you can lump them all in together and treat them like a player.

Say an unknown donks into A72 - I can alalyse the general population in dry ace high flops and see what a donk usually means and go from there.
Can also create an optimal readless calling range for open shoves, etc

Quote

06-21-2012 , 09:41 AM

#36

chaosad

adept

Join Date: Nov 2007 Posts: 969

ah ok that makes sense. thank you for the explanation.

Quote

06-21-2012 , 05:49 PM

#37

genher

veteran

Join Date: May 2007 Posts: 2,931

I have run 5000 players last night no pb, I will try more today. I do recommand backing up tho. I had to kill a database last night as HM2 couldn't connect to it any more and because it had changed to default HM2 couldn't start neither.

As for the benefits, it's very interesting to see what the general population REALLY does. from the few situations I looked at last night, peoples are much tighter than it's often thought.

Quote

06-22-2012 , 02:05 AM

#38

genher

veteran

Join Date: May 2007 Posts: 2,931

@tigerhack89: I have 2 SN (on 2 differents sites) how do I change the script to exclude both sn? i have an idea but I am not sure.
thanks again

Quote

06-22-2012 , 08:06 AM

#39

tigerjack89

grinder

Join Date: Jun 2010 Posts: 460

Quote:

Originally Posted by genher

@tigerhack89: I have 2 SN (on 2 differents sites) how do I change the script to exclude both sn? i have an idea but I am not sure.
thanks again

Well, as I suggested the first thing to do is create a new Alias for the Hero, then insert all the players except that in the Hero alias. Always post#11 to see more details

If you don't want to create an alias, you have to add two more conditions in the insert sentence

Code:

...AND p1.playername <> 'nick1' AND p1.playername <> 'nick2';

Check also if you have yet inserted your nick in the 'Global alias'.
Let me know

Last edited by tigerjack89; 06-22-2012 at 08:15 AM.

Quote

06-22-2012 , 08:13 AM

#40

tigerjack89

grinder

Join Date: Jun 2010 Posts: 460

Regarding PT4. On a brief look, seems that isn't too simple to create a global alias.
Indeed, in PT4 the alias is an attribute of the player. It's impossibile to uniquely identify an alias, because it has no attributes.
For this reason, a player can partecipate only to a single alias, not more. And I don't know what are the results on the database.

But maybe I'm wrong and someone more espert will prove that I'm wrong

Quote

06-22-2012 , 08:53 AM

#41

dontbeleivethehype

journeyman

Join Date: Mar 2009 Posts: 264

just had a look at the PT3 database and couldn't see if it was even possible...

from this link I can only presume that running this feature will just change the id_player within the database - essentially losing the original data forever.... is that totally wrong?

Quote:

Indeed, in PT4 the alias is an attribute of the player. It's impossibile to uniquely identify an alias, because it has no attributes.

I am not familiar with poker tracker 4 but could you not just set all of these attributes to `global`, I presume the column is non-unique?

or is this not going to achieve what is intended?

Quote

06-23-2012 , 01:18 AM

#42

Benjamin the Donk

old hand

Join Date: Mar 2012 Posts: 1,254

I have just had a go with PT4 (with a lot of help from _dave_). It sort of works but mostly doesn't. All players (apart from hero) get added to the alias (which I have named "All Villains") but when I open up PT4 I find that none of players are in my database any more, all I have is me and All Villains, but there are 0 hands for All Villains. Can anyone help?

I backed up my DB so it isn't an issue.

Quote

06-24-2012 , 01:02 AM

#43

Benjamin the Donk

old hand

Join Date: Mar 2012 Posts: 1,254

Hey guys, I have been trying to get this to work in PT4:

I used the following code to create an alias for all villains in PG SQL:

Code:

UPDATE player
SET id_player_alias = 5724
WHERE id_player != 1 AND id_player != -1 AND id_player != 0 AND id_player != 5724
;

and it creates an alias ok, but no hands are showing.

See me PT4 forum post for more info, I hope someone can help.

Also many thanks to _dave_ for teaching me a bit of SQL yesterday, I'm such a noob

edit: forgot link - https://www.pokertracker.com/forums/...209021#p209021 - last post

Quote

06-24-2012 , 01:19 PM

#44

tigerjack89

grinder

Join Date: Jun 2010 Posts: 460

Hey benjamin. Maybe i'm wrong, i don't have postgresql now, but I remember that all the alias are setted to 0 as default, so imo the best thing to do is to create an alias for all hero nicks, see the number and then run the following script

Code:

UPDATE player
SET id_player_alias = 5724
WHERE id_player_alias = 0 AND id_player_alias <> [Hero alias number] AND id_player > 0;

Also, 5724 is only a random number or there is a particular reason? Just so curious

Quote

06-24-2012 , 02:11 PM

#45

_dave_

_Pooh_Bah_

Join Date: Feb 2005 Posts: 13,146

Just checked a log, PT4 basically rewrites it's whole database to make an alias lol. Seems id_player_alias is only really used to store the name and to revert things back again.

Code:

UPDATE tourney_hand_summary SET id_winner = 1272 WHERE id_winner = 1352;
UPDATE tourney_hand_player_statistics SET id_player = 1272 WHERE id_player = 1352;
UPDATE tourney_hand_player_combinations SET id_player = 1272 WHERE id_player = 1352;
UPDATE tourney_summary SET id_winner = 1272 WHERE id_winner = 1352;
UPDATE tourney_results SET id_player = 1272 WHERE id_player_real = 1352;
UPDATE player SET id_player_alias = 1272 WHERE id_player = 1352;

which is probably enough to make the alias, then a few of these with a truckload of values:

Code:

Insert cache with SQL: INSERT INTO tourney_cache VALUES(1272,1, 1, 2, 0, false, false, false...

which I'd hope are not necessary for anything but hud use.

Last edited by _dave_; 06-29-2012 at 11:06 PM.

Quote

06-24-2012 , 07:03 PM

#46

tigerjack89

grinder

Join Date: Jun 2010 Posts: 460

so, as i thought, there is no real way to do this on PT4? wtf i was just going to give it a chance.

Quote

06-24-2012 , 07:38 PM

#47

_dave_

_Pooh_Bah_

Join Date: Feb 2005 Posts: 13,146

well, it is certainly possible to do but due to daft alias design it makes the database useless during play, so the database must be duplicated (prob easiest with pg_dump -> pg_restore) then aliased via SQL, then pop tendencies can be studied in the duplicate.

inconvenient, but not impossible

also annoying that new imports must be either done twice, once to each database, or the whole backup/restore/alias procedure repeated. and different samples overlapping will need more database duplication. HEM seems better in this regard, since a single database can support many aliases - but then it appears HEM has issues with large numbers of aliased players although 15000 may well be enough, I am not sure.

Last edited by _dave_; 06-25-2012 at 09:35 AM.

Quote

06-24-2012 , 08:23 PM

#48

kasparovski

journeyman

Join Date: Feb 2008 Posts: 359

So, i tried to do what's explained on the op and that worked well but i noted that the alias as himself inside so i deleted it (is this ok to do?). but i assume there's still there my sn and stuff so how do i delete them (can't find them on that huge list to do it manually)? Do i now do Quimp method on top of this?

Sorry but i can't understand half of what you guys are writing, can't someone who understands this better do a cliffs on the best way to do this plz?

Quote

06-24-2012 , 09:31 PM

#49

kasparovski

journeyman

Join Date: Feb 2008 Posts: 359

lol, well, i re-read op and luckily i already had an alias with all my sn so no problem with that, how do i limit the number of players?

"The following error occurred when running this report: ERROR: 54001: stack depth limit exceeded"

? i can only see the result, not the reports, pf cards or hands.

Last edited by kasparovski; 06-24-2012 at 09:41 PM.