Setting the run time for a postgresql query in stata

[Enock Tusingwire]

Hi, I need help,
am trying to run a postgresql query in stata but it times out, i wanted to extend the run time but i couldnt, below is what i tried but it failed
local sql = "set statement_timeout ='1h';"
any assistance is highly appreciated

[wbuchanan]

It is probably something that needs to be adjusted in the database configuration. If you’re running a query that takes longer than an hour to complete it will probably bother your DBA quite a bit since it could involve locking multiple tables. I’d suggest testing your query in an SQL environment to optimize the performance of the query a bit. pgadmin3 had a pretty decent query plan display that might help you.

[Enock Tusingwire]

Thanks wbuchanan, i already optimized this in Navicat but its an issue of the volume of data that am pulling (over 1.5 million records). In Navicat, when i execute "set statement_timeout ='1h'", & then run the query, its able to run for about 45 minutes & pull the data. i was wondering whether there is an equivalent way of doing this in stata like incorporating it in the query or running it as an independent query before i start pulling data, thnx

[wbuchanan]

There could possibly be some syntax that exists within PostgreSQL to do that, but I would be surprised if it existed. In either case, 1.5M records shouldn’t take that long if the database is tuned and tables are indexed and joined appropriately. While it isn’t the same thing as PostgreSQL, I regularly ran queries in an Oracle 11g environment that retrieved > 20M records in a couple of minutes (when the server was getting slammed with other queries simultaneously).

That said, ODBC is generally a bit on the slower side. Maybe it would be easier to use psql to write the result set to disk as a CSV file and then use import delimited?

[Dimitriy V. Masterov]

I have not used this with PostgreSQL, but in my experience this is typically a setting in your .odbc.ini file (or whatever its equivalent is called on your system). You would need to add "set statement_timeout ='1h'" (or whatever) to that file under the right DSN that you want to query. You can usually find an example file in the installation directory for the ODBC drivers or in the manuals.

For example, here's an actual .odbc.ini that will work on either Mac or Linux/Unix for a PostgreSQL database named 'Bug' taken from the FAQ. When placed in the user’s home directory, this file provides access to the ODBC database specified:

[ODBC Data Sources] Bug = PostgreSQL [Bug] Debug = 1 CommLog = 1 ReadOnly = no Driver = /usr/local/lib/psqlodbc.so Servername = 64.xxx.xxx.xx FetchBufferSize = 99 Username = kevin Password = passwordforkevin Port = 5432 Database = bugdb statement_timeout ='1h' [Default] Driver = /home/kst/tmp/libiodbc-3.0.5/odbcsdk/lib/oplodbc.so.1

The formatting is getting mangled here for some reason, but take a look at the FAQ example there.

[wbuchanan]

Dimitriy V. Masterov I think the formatting issue might be related to using quotation styling instead of the code block markup:

\[CODE\]
stuff here
\[/CODE\]

If you ignore the escape characters that macro should treat the text as unformatted (verbatim in LaTeX).

[Dimitriy V. Masterov]

wbuchanan I still get something unreadable with the octothorp delimiters:

 
 [ODBC Data Sources]  Bug = PostgreSQL            [Bug]  Debug = 1  CommLog = 1  ReadOnly = no  Driver = /usr/local/lib/psqlodbc.so  Servername = 64.xxx.xxx.xx  FetchBufferSize = 99  Username = kevin   Password = passwordforkevin  Port = 5432  Database = bugdb    [Default]  Driver = /home/kst/tmp/libiodbc-3.0.5/odbcsdk/lib/oplodbc.so.1

[wbuchanan]

Hua Peng (StataCorp) Interesting. Perhaps it has something to do with the default string encoding on your machine somehow? I’ve not run into that issue before myself and am not sure who at Stata deals with the messageboard but it definitely defeats the purpose a bit. Maybe Hua Peng would know who could look into this? Also, when trying to @ mention people from comments on a mobile device using Chrome, the platform inserts the reference as the first bit of text in the post.