PokerTracker


https://staging.pokertracker.com/forums/

PT3 standard queries

https://staging.pokertracker.com/forums/viewtopic.php?f=17&t=19890

Page 1 of 2

PT3 standard queries

PostPosted: Tue May 26, 2009 6:42 pm
by checkits
I'm trying to query PostGres directly.... When I did this with PT2 it was very easy to double click on a PT3 screen and see the underlying SQL query. I could then learn a lot from that to make my own queries.

Is there some way to get the standard queries used in PT3 (similar to what was available in PT2)?

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 9:19 am
by kraada
The queries are dynamically generated so there isn't a standard query for you to see. You can find the database schema on our documentation page which will show you how the database is put together. You can also find queries PT3 has actually used in the files found in C:\Program Files\PostgreSQL\8.3\data\pg_log. I think the default logging is sufficient if not you may need to up the logging level one in the postgres.conf file (Start --> Programs --> PostgreSQL --> Configuration Files --> Edit postgresql.conf).

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 9:57 am
by checkits
Thanks!! I think this is going to be exactly what I need.

I went into PT3 and did a few reports and tabbed around in several places.

I then went to

C:\Program Files\PostgreSQL\8.3\data\pg_log

and looked at the log

I couldn't find any queries for today's date. I tried closing the program -- no queries. I looked at prior days and found queries I had manually entered into PostGres, but didn't find any that PT3 generated.

So I tried to edit the log levels as shown below; however, when I tried to save the config file -- I got the following message:

"Cannot create the C:\Program Files(86)\PostgresSQL\8.3\data\postgresql.conf file. Make sure that the path and file name are correct"

Is the problem that I'm editing it in notepad?

Any advice? Sorry if this is a dumb question :)

#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error

#log_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 10:02 am
by kraada
Hold ctrl+shift when clicking on "Edit postgresql.conf" and then it'll be run as administrator and you should be able to edit the file. Setting client_min_messages = log (take out the # in front of the line) and log_min_messages = info should be enough I think.

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 10:48 am
by checkits
Hmmmm.

Ok. I tried log and info and it didn't work for me. I tried debug1 as shown below. When I ran some reports in PT3 and tabbed around, I got the following log (using the following config file).

I also selected "enable log" on PT3.

Any idea what I'm doing wrong?

Note: When I manually do a query in pgAdmin III it writes the query to the log.

Code: Select all

2009-05-27 07:36:17 PDT LOG:  database system was shut down at 2009-05-27 07:35:15 PDT
2009-05-27 07:36:17 PDT DEBUG:  checkpoint record is at 0/55B822B0
2009-05-27 07:36:17 PDT DEBUG:  redo record is at 0/55B822B0; shutdown TRUE
2009-05-27 07:36:17 PDT DEBUG:  next transaction ID: 0/40356; next OID: 25477
2009-05-27 07:36:17 PDT DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2009-05-27 07:36:17 PDT DEBUG:  transaction ID wrap limit is 2147484026, limited by database "template1"
2009-05-27 07:36:18 PDT LOG:  database system is ready to accept connections
2009-05-27 07:36:19 PDT LOG:  autovacuum launcher started
2009-05-27 07:36:21 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:36:50 PDT DEBUG:  autovacuum: processing database "PT3 DB"
2009-05-27 07:37:20 PDT DEBUG:  autovacuum: processing database "postgres"
2009-05-27 07:37:22 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:37:29 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:37:30 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:37:30 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:37:30 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:37:49 PDT DEBUG:  autovacuum: processing database "PT3 DB"
2009-05-27 07:38:19 PDT DEBUG:  autovacuum: processing database "postgres"
2009-05-27 07:38:49 PDT DEBUG:  autovacuum: processing database "PT3 DB"
2009-05-27 07:39:19 PDT DEBUG:  autovacuum: processing database "postgres"
2009-05-27 07:39:25 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:39:25 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:39:39 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:39:39 PDT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"
2009-05-27 07:39:49 PDT DEBUG:  autovacuum: processing database "PT3 DB"

Code: Select all
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr'      # Valid values are combinations of
               # stderr, csvlog, syslog and eventlog,
               # depending on platform.  csvlog
               # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on      # Enable capturing of stderr and csvlog
               # into log files. Required to be on for
               # csvlogs.
               # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'pg_log'      # directory where log files are written,
               # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'   # log file name pattern,
               # can include strftime() escapes
#log_truncate_on_rotation = off      # If on, an existing log file of the
               # same name as the new log file will be
               # truncated rather than appended to.
               # But such truncation only occurs on
               # time-driven rotation, not on restarts
               # or size-driven rotation.  Default is
               # off, meaning append to existing files
               # in all cases.
#log_rotation_age = 1d         # Automatic rotation of logfiles will
               # happen after that time.  0 to disable.
#log_rotation_size = 10MB      # Automatic rotation of logfiles will
               # happen after that much log output.
               # 0 to disable.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'


# - When to Log -

client_min_messages = debug1      # values in order of decreasing detail:
               #   debug5
               #   debug4
               #   debug3
               #   debug2
               #   debug1
               #   log
               #   notice (default)
               #   warning
               #   error

log_min_messages = debug1         # values in order of decreasing detail:
               #   debug5
               #   debug4
               #   debug3
               #   debug2
               #   debug1
               #   info
               #   notice  (default)
               #   warning
               #   error
               #   log
               #   fatal
               #   panic

#log_error_verbosity = default      # terse, default, or verbose messages

#log_min_error_statement = error   # values in order of decreasing detail:
                #   debug5
               #   debug4
               #   debug3
               #   debug2
               #   debug1
                #   info
               #   notice
               #   warning
               #   error
               #   log
               #   fatal
               #   panic (effectively off)

#log_min_duration_statement = -1   # -1 is disabled, 0 logs all statements
               # and their durations, > 0 logs only
               # statements running at least this time.

#silent_mode = off         # DO NOT USE without syslog or
               # logging_collector
               # (change requires restart)

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_hostname = off
log_line_prefix = '%t '         # special values:
               #   %u = user name
               #   %d = database name
               #   %r = remote host and port
               #   %h = remote host
               #   %p = process ID
               #   %t = timestamp without milliseconds
               #   %m = timestamp with milliseconds
               #   %i = command tag
               #   %c = session ID
               #   %l = session line number
               #   %s = session start timestamp
               #   %v = virtual transaction ID
               #   %x = transaction ID (0 if none)
               #   %q = stop here in non-session
               #        processes
               #   %% = '%'
               # e.g. '<%u%%%d> '
#log_lock_waits = off         # log lock waits >= deadlock_timeout
#log_statement = 'none'         # none, ddl, mod, all
#log_temp_files = -1         # log temporary files equal or larger
               # than specified size;
               # -1 disables, 0 logs all temp files
#log_timezone = unknown         # actually, defaults to TZ environment
               # setting


#------------------------------------------------------------------------------

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 12:13 pm
by kraada
That's really odd, what do you have in your [Databases] section of the C:\Program Files\PokerTracker 3\PokerTracker.cfg file?

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 12:52 pm
by checkits
[Database]
Default.Postgres.Server=localhost
Default.Postgres.Port=5432
Default.Postgres.User=postgres
Default.Postgres.Password=xxxxxx
DB1.Name=PokerTracker 3 Database
DB1.Type=postgres
DB1.Postgres.Database=PT3 DB
DB1.Postgres.Server=localhost
DB1.Postgres.Port=5432
DB1.Postgres.User=postgres
DB1.Postgres.Password=xxxxxx
DB1.Default=Y

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 12:54 pm
by checkits
however!

it is not located where you said. It is located here...

C:\program files (x86)/PokerTracker3/Data/Config/PokerTracker.cfg

PS PT3 said I needed to update my cache so I'm doing that now. Doubt that matters, but wanted to tell you anyway.

Also, when I manually run PostGres queries, the log says there's an error before doing the query as shown below.
Code: Select all
2009-05-27 07:43:26 PDT ERROR:  relation "holdem_hand_player_statistics" does not exist
2009-05-27 07:43:26 PDT STATEMENT:  SELECT
      (   
         Select   hole_cards
         From   lookup_hole_cards
         Where   holdem_hand_player_statistics.id_holecard = lookup_hole_cards.id_holecard
      ) as hole_cards,
         
      
      sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='N' THEN 1 ELSE 0 END) as N,
      
      sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='C' THEN 1 ELSE 0 END) as C,
      
      sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='F' THEN 1 ELSE 0 END) as F,
      sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='R' THEN 1 ELSE 0 END) as R,
      count(*)
   

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 2:46 pm
by kraada
Check your postgres.conf and make sure it's on port 5432; if you have another installation of postgres around that could be causing the problems we're seeing here (you think you're using installation A you're actually using installation B).

With the manual queries: which database were you connected to at the time you ran that query?

Could you also paste the exact query you ran so that I could try running it against my database?

Re: PT3 standard queries

PostPosted: Wed May 27, 2009 3:11 pm
by checkits
yep it's on 5432.

I don't think I have another installation. If I do I can't see the folders associated with it.

In pgAdmin III I see

Servers(1)
Postgres SLQ Sserver 8.3 (locathost:5432)
Databases(2)
+PT3 DB
+postgres

Interestingly, I cannot find the table names, etc. from PT3 like holdem_hand_player_statistics (as I would w/ PT2). Rather I see 40 tables that start w/ pg_aggregate, pg_am, pg_amop, etc. Is that ok?

Here's the query I ran. I was just trying to figure out what N means in enum_p_3bet_action. I still don't know. Nor can I make sense of this variable. Most all of my results are N.

I ran this on PT3 DB
Code: Select all
SELECT
   (   
      Select   hole_cards
      From   lookup_hole_cards
      Where   holdem_hand_player_statistics.id_holecard = lookup_hole_cards.id_holecard
   ) as hole_cards,
      
   
   sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='N' THEN 1 ELSE 0 END) as N,
   
   sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='C' THEN 1 ELSE 0 END) as C,
   
   sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='F' THEN 1 ELSE 0 END) as F,
   sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='R' THEN 1 ELSE 0 END) as R,
   count(*)

FROM holdem_hand_player_statistics
WHERE TRUE
  AND holdem_hand_player_statistics.flg_p_3bet_opp IS TRUE
GROUP BY
   id_holecard



Is it ok that my log starts with an error when I run this query as shown below?
Code: Select all
2009-05-27 12:03:55 PDT ERROR:  relation "holdem_hand_player_statistics" does not exist
2009-05-27 12:03:55 PDT STATEMENT:  SELECT
      (   
         Select   hole_cards
         From   lookup_hole_cards
         Where   holdem_hand_player_statistics.id_holecard = lookup_hole_cards.id_holecard
      ) as hole_cards,
         
      
      sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='N' THEN 1 ELSE 0 END) as N,
      
      sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='C' THEN 1 ELSE 0 END) as C,
      
      sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='F' THEN 1 ELSE 0 END) as F,
      sum(CASE WHEN holdem_hand_player_statistics.enum_p_3bet_action='R' THEN 1 ELSE 0 END) as R,
      count(*)
   
   
   FROM holdem_hand_player_statistics
   WHERE TRUE
     AND holdem_hand_player_statistics.flg_p_3bet_opp IS TRUE
   
   GROUP BY
      id_holecard
   


Note: Thank you soo much for helping me figure this out!!!! I really appreciate it!
All times are UTC - 5 hours [ DST ]
Page 1 of 2
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/