select * from pg_stat_activity order by backend_start;

select backend_start, client_addr from pg_stat_activity order by backend_start;

select * from pg_stat_activity order by backend_start;

Debugging postgresql functions

in psql, i do the
set client_min_messages TO debug;

and then in the function you can do
raise debug 'foo message: %', _a value;
where % are replaced by the _a_value, variable, etc?

the raise debug is only shown if client_min_messages is debug
so you can leave them in there after, and turn on debug mode later
then
when you create
the function
to test i sometimes do
begin transaction;
select function();
and then when it fails, or after i poke at results,
rollback;
well, handy for some kinds of testing

Raw postgresql function

i would start off with
create function do_vore (
pixpair_id integer,
picture_id integer
) returns integer as $$
DECLARE
_id integer;
BEGIN
_id := 0;

-- insert into pixpair_ips
-- increment count in pixpair
-- update picture set totals there too
return _id;
END;
$$ language plpgsql;?1

Reboot server detecting open connections

#!/bin/bash
cd /home/mark/work/pop
svn up
echo 'updated to latest code. trying restart'

#find number of active connections that are established
number_of_conn=`netstat -apn 2>&1 | grep :80 | grep -i established | wc -l`
#echo $number_of_conn

trial=0
while [ $number_of_conn -ne 0 ]
do
trial=$((trial +1 ))
echo "$number_of_conn connections: trying again"
number_of_conn=`netstat -apn 2>&1 | grep :80 | grep -i established | wc -l`
done

/home/mark/bin/startstop
echo 'there were 0 connections. restarted'
echo "number of trials: $trial"

Remove dup pixpair

remove_dup_pixpair
select pic_id, sum(pic_votes) as votes from

(
select pic1_id as pic_id,
pic1_votes as pic_votes
from
pixpair p1

union

select pic2_id as pic_id,
pic2_votes as pic_votes
from
pixpair p2

) a
group by pic_id
12:45:29 am

sum of all pictures.total_votes should equal pixpair.total_votes
12:45:33 am
Travis
and then, i sum this.
select sum(votes) from
(

select pic_id, sum(pic_votes) as votes from

(
select pic1_id as pic_id,
pic1_votes as pic_votes
from
pixpair p1

union

select pic2_id as pic_id,
pic2_votes as pic_votes
from
pixpair p2

) a
group by pic_id

)b
sum
-------
11622
and that gives a differnt number still.

Transactions and deletes in pgsql

begin transaction;
delete from pixpair_ips where pixpair_id in (
select id from pixpair where pic1_id = 518 or pic2_id=518);
delete from pixpair where pic1_id = 518 or pic2_id=518;
DELETE 41
select update_pixpair(518);
update_pixpair
----------------
43
commit transaction;
COMMIT

Password securing postgres

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0 password
host all all all password
#all and 0.0.0.0 mean the same - 0.0.0.0 is an ip address meaning any
# IPv6 local connections:
host all all ::1/128 password

Duplicates in pictures table

select
p1.id as pix1_id,
p2.id as pix2_id
from
pixpair p1,
pixpair p2
where
p1.pic1_id = p2.pic2_id and p1.pic2_id = p2.pic1_id;
should show dupliacates..

select * from pixpair
where (pic1_id = _new_pic_id and pic2_id = allpics.id)
or (pic2_id = _new_pic_id and pic1.id = allpics.id) ) );

Dont forget to check if variable is valid before slicing object

#if $cutie.comment
$cutie.comment[0:100]
#end if

Dont forget to put if exists
else you will get unscriptable object error

Static variables in python

ints are not modifiable, so += actually rebinds the variable.  Try:

def_f():
   global global_var_c_hits
   global_var_c_hits +=1

Cron jobs need quiet operation

Cron jobs need quiet operation; if a command generates output, you’ll get an email from cron with the command output. So if you want to fetch a file silently with wget or curl, use a command like this:

curl –silent –output output_filename http://go.com/urltofetch.html

wget –quiet –output-document output_filename http://google.com/urltofetch.html

There are shorter versions of these options, but using the verbose options will make code or cron jobs easier to understand if you come back to them. Be aware that urls with “&” in them can confuse wget at least, so depending on your shell (bash, csh, tcsh), you may need to put single or double quotes around the url.

Threaded data collection with Python

Threaded data collection with Python, including examples Posted in General

October 19 2006

On today's Internet 2.0 there are all sorts of data feeds available for consumption. From APIs to RSS feeds, it seems like nearly every site has a machine-readable output. There are many reasons why you'd want to collect this information, which I won't go in to, so in this post I'm going to walk you through an application which consumes RSS feeds. I'll be using the Python scripting language, and I'll show you an evolution of the ways to go about the task:

Application introduction

Our application is going to work like this:

• A database contains the list of RSS feeds. This is long - 1000+ records
• Our application reads this list of feeds and processes them
• The items from the feeds are stored in the database

Database manipulation and RSS feed parsing are outside the scope of this tutorial, so we'll start off by defining some empty functions that handle all this:

PLAIN TEXT

PYTHON:

1. def get_feed_list():
2. """ Returns a list of tuples: (id, feed_url) """
3. pass
4. def get_feed_contents(feed_url):
5. """ Gets feed over HTTP, returns RSS XML """
6. pass
7. def parse_feed(feed_rss):
8. """ Parses the feed and returns a list of items """
9. pass
10. def store_feed_items(id, items):
11. """ Takes a feed_id and a list of items and stored them in the DB """
12. pass

We're going to have all these in a module called "functions", which can just be a file called functions.py in the same directory ( < python3.0)

Implementation 1: Single-threaded

This is the way most people would do it at first. So simple, I'll just post the sample code:

PLAIN TEXT

PYTHON:

1. import functions
2. for id, feed_url in get_feed_list():
3. rss = functions.get_feed_contents(feed_url)
4. items = functions.parse_feed(rss)
5. functions.store_feed_items(id, items)

Pretty simple huh? But there are fundamental problems. Feeds are usually slow, meaning that your program will spend a lot of time waiting for feeds to come in before you can parse them. You program will also be spending time parsing feeds when it could be getting them from the internet as well. Consequently this program will be as slow as molasses. It's like eating a bowl of peas one at a time - you'd rather just shovel them in wouldn't you? Enter: threading.

Implementation 2

So we reckon: "If we use threads, this will make things faster?" Answer: Yes. However, there are quite a few ways of doing this. We'll start off with this:

PLAIN TEXT

PYTHON:

1. import threading, functions, time
2. def thread(id, feed_url):
3. rss = functions.get_feed_contents(feed_url)
4. items = functions.parse_feed(rss)
5. functions.store_feed_items(id, items)
7. for id, feed_url in get_feed_list():
8. t = threading.Thread(target=thread, kwargs={"id":id, "feed_url":feed_url})
9. t.start()
10. while threading.activeCount()> 1: time.sleep(1)

Problem: This is just going to create as many threads as there are feed items immediately and then wait for them to finish. This has the following issues:

• If you try to request 1000+ pages at a time, many of them will time out. If a percentage of these are on the same server, you'll DoS it.
• With 1000 threads your app will likely either run out of memory or get so bogged down in context switching it'll take forever
• Try this on any consumer-grade router and it'll probably crash

So what do we do? Well, let's set a limit on the number of concurrent threads:

PLAIN TEXT

PYTHON:

1. import threading, functions, time
2. THREAD_LIMIT = 20
3. def thread(id, feed_url):
4. rss = functions.get_feed_contents(feed_url)
5. items = functions.parse_feed(rss)
6. functions.store_feed_items(id, items)
8. for id, feed_url in get_feed_list():
9. while threading.activeCount()> THREAD_LIMIT:
10. time.sleep(1)
11. t = threading.Thread(target=thread, kwargs={"id":id, "feed_url":feed_url})
12. t.start()
13. while threading.activeCount()> 1: time.sleep(1)

Spot the difference? We have another while loop right in the for loop. This is going to make our main thread code hang there while there are other threads still running.

There's another problem though, and that's with the model. In this mode, we're continually creating new threads that live for a short time, then exit. This isn't efficient. It would be much better to create a pool of threads which we can then re-use. Let's kick this up a notch.

Implementaton 3: Thread pool, and a Queue

So in this version we're going to do a few new things:

1. Use a Queue object and populate it with the list of urls.
2. Spawns a number of threads that will read items off this Queue.
3. The threads will process the data and store it.

I'll start off with the sample code then walk you through it:

PLAIN TEXT

PYTHON:

1. import threading, functions, time, Queue
2. THREAD_LIMIT = 50
3. jobs = Queue.Queue(0) # arg1 means "No item limit"
5. def thread():
6. while True: # forever
7. try:
8. id, feed_url = jobs.get(False) # arg1 means "Don't wait for items to appear"
9. except Queue.Empty:
10. # Nothing left to do, time to die
11. return
12. rss = functions.get_feed_contents(feed_url)
13. items = functions.parse_feed(rss)
14. functions.store_feed_items(id, items)
16. for info in get_feed_list():
17. Queue.put(info)
19. for n in xrange(THREAD_LIMIT):
20. t = threading.Thread(target=thread)
21. t.start()
22. while threading.activeCount()> 1: time.sleep(1) # Wait to finish

Lines to note:

• 1: We create the job list. We use a Queue class from the standard library, as it's threadsafe. In fact, this is exactly what it was designed for.
• 7-11: Read from the queue. The False parameter means that once the queue is empty, we're not interested any more. This raises the Queue.Empty exception, and we terminate the thread.
• 16: Put lots of info into the queue

Our threads run in a loop, performing work in lines 12-14, until there is no more work, then they exit. This model will work just fine for the majority of people, however, there are (still) problems. They are:

1. Opening 50 threads that write to a database will usually mean 50 database connections, or lots of locking. Either way, this is bad. Your data-getting threads don't want to be sat around waiting on a DB lock when they could be getting more information. Worse, you don't want to exceed your connection limit to the databse.
2. In this model, you have one master thread doing nothing and 50 threads doing I/O and work. This isn't a great idea in this application. Ideally you should only use threading (in Python) when you need to either:
   1. Wait for I/O
   2. Truly perform more than one concurrent task

Problem #2 isn't so serious, but it would be better to have more control over the heavy lifting. However problem #1 definitely needs addressing. The solution is to shift all processing inline to the master thread, which takes care of all the processing.

Implementation 4: 1 worker, many runners

Here's the code:

PLAIN TEXT

PYTHON:

1. import threading, functions, time, Queue
2. THREAD_LIMIT = 50
3. jobs = Queue.Queue(0) # arg1 means "No item limit"
4. rss_to_process = Queue.Queue(THREAD_LIMIT) # We set a limit on this, I'll
5. # explain later
6. def thread():
7. while True: # forever
8. try:
9. id, feed_url = jobs.get(False) # arg1 means "Don't wait for items
10. # to appear"
11. except Queue.Empty:
12. # Nothing left to do, time to die
13. return
14. rss = functions.get_feed_contents(feed_url)
15. rss_to_process.put((id, rss), True) # This will block if our processing
16. # queue is too large
18. for info in get_feed_list(): # Load them up
19. jobs.put(info)
21. for n in xrange(THREAD_LIMIT): # Unleash the hounds
22. t = threading.Thread(target=thread)
23. t.start()
24. while threading.activeCount()> 1 or not rss_to_process.empty():
25. # That condition means we want to do this loop if there are threads
26. # running OR there's stuff to process
27. try:
28. id, rss = rss_to_process.get(False, 1) # Wait for up to a second for a
29. # result
30. except Queue.Empty:
31. continue
32. items = functions.parse_feed(rss)
33. functions.store_feed_items(id, items)

Notes:

• 4: Here we have a new Queue to return the RSS XML to the master thread. We set a limit on the size of the queue in case the threads return data faster than we can process it, meaning the queue will fill up with XML, burning memory. This sets a rudimentary rate limit on the threads, as they will block when the queue is full.
• 15: This is the line in the thread that blocks when the processing queue is full.
• 25-34: This is where all the heavy lifting takes place, all inline as well.

...and there you have it, a fully fledged multithreaded data collector. Not bad for a few hours' work. It's not finished though, as there are plenty of things you'd want to add to it. For example:

• More exception handling, especially KeyboardInterrupt and other signals. I would advise importing the signal module, then wrapping our final loop in a big try/except block, catching KeyboardInterrupt. This would need to empty the job and processing queues, which will cause your threads to exit gracefully, and then your program will exit too.
• Better support for multi-core machines. The Python global interpreter lock prevents more than one thread from running at a time, however you can still spread your threads over multiple cores (I believe). What you might want to consider is throwing a few os.fork() calls in, forcing your program to multiprocess, which could then take full advantage of multiple cores. You can do this by either dividing up your work queue at the start, or by moving the heavy lifting out of the main thread and into seperate processes. Your main thread could then communicate with these "worker" processes via shared memory or sockets (my preference) then pass back the results. Make sure your functions.parse_feed can produce picklable objects.
• You could use GUI programming or ncurses to provide a progress bar, by inspecting the value of jobs.qsize()

How to ensure_ascii=False when using JSON dumps()

simplejson is very well written, but there's a catch. If you're doing UTF-8, make sure you ensure_ascii=False whenever calling dumps(). I don't know what it is with it that it will give you funky \uSOMETHING sequences for every non-ascii byte, and although that may display
right in HTML/JS, you should want to keep raw UTF-8 for better interoperability (specially with other JSON parsers).

Fix autonumbering sequences when backing a single table in postgres

yea, the sequences are never really captured on a database dump of just one table, need to do an entire backup.
in general, when i want to move a 1 table, i use the \d table_name;
then i write / copy the structure,
then create it with another psql to other db.

in general, when i want to move a 1 table, i use the \d table_name;
then i write / copy the structure,
then create it with another psql to other db.

So to fix id problems when backing a table :
select max (id) from table;

alter sequence my_sequence restart maxid+1;


the thing about a table is in the general can have (constraints, such as primary keys, foreign keys, that reference other tables), indexes(for finding data faster), triggers (calls a stored procedure), and rules (that rewrite operations based on data, like triggers)
but most people just have tables, maybe with a primary key default nextval('a_sequnce'), so it is a lot simpler
so in psql
\d tablename
dumps the structure of the table to the text console
---------
well like here
\d task
Table "task.task"
Column | Type | Modifiers
----------------+-------------------+---------------------------------------------------
id | bigint | not null default nextval('task_id_seq'::regclass)
entry_date | date |
completed_date | date |
project_id | bigint |
name | character varying |
description | text |
parent_id | bigint |
Indexes:
"task_pk" PRIMARY KEY, btree (id)
Foreign-key constraints:
"_parent_id" FOREIGN KEY (parent_id) REFERENCES task(id)
"_project_id" FOREIGN KEY (project_id) REFERENCES project(id)
this shows me the table "task" contains id, entry_date, completed_date,,...
and their types, an then i see the constriants, like what foreign key it references, so i know that this table depends on another table.
i then have a couple minutes in a text editor and make this into
CREATE TABLE TASK (ID BIGINT NOT NULL DEFAULT NEXTVAL('task_id_seq'),
entry_date date,
etc.
then use that to create teh table in new db.
i guess it is hte manual old-school way to d it.
then when the new table exists,
(in both new, and old databases,)
i can type
psql -h host1 -U user1 -c "copy task to stdout csv" | psql -h host2 -U user2 -c "copy task from stdin csv"
or i guess you can do a copy out to a file first, and then copy into the second db by cat the file.
like how you would install that country data, i emailed to you.
for more info on the copy command, "\h copy" from the psql prompt
lol, see, thats why i never use a "GUI", becuse the command line is always better than any GUI.
so then after i get the table created, i do the create sequence task_id_seq start nnnnn;
actually, if you ran the sql propertly, it doesnt let you create the table without having the sequence ther..
so i would then have the sequence manually created, and to start at next higher value.

DISTINT 300ms grouby 1300ms for 10 entries

recentlycommentedposts = web.query('SELECT DISTINCT ON(comments.postid) comments.postid, comments.comment, posts.title, posts.id FROM comments JOIN posts on posts.id=comments.postid order by comments.postid DESC limit 10')
recentlycommentedposts = web.query('''select postid, max(created) as created
from comments
group by postid
order by created desc
limit 10;''')

select p.id, p.title, p.created as post_date, c.comment, a.created as comment_date
from posts p,
comments c,
(select postid, max(created) as created
from comments
group by postid
order by created desc
limit 10) a
where
p.id = a.postid
and c.postid = a.postid
and c.created = a.created
order by post_date desc;

"htpasswd" and /etc/httpd/conf/httpd.conf

there is probably a
so anywhere after athat
after that change, we need to make the /www/svn_new/main/auth.conf file
the "htpasswd" commmand does this
from shell,
cd /www/svn_new/main/
htpasswd -c auth.conf travis
(-c creates if not exists)
then to add future users, for example,
htpasswd auth.conf travis
5:27:38 pm
mark
htpasswd -c /etc/httpd/repo_passwd user

Postgresql VIEWs will save your day

oh yea, thats right mysql sucks ass for the complex queries, like even
select * from users where company_id in (select id from company where company_name='foo') ;
is that a join
Travis :
yea, it could be done with a join too
the above was running the output of one select into the input of a second.
:
i m doing a double join
i think it is super slow
2:04:33 pm
Travis
a join would be
select user.*
from users u, company c
where u.company_id = c.id
and c.company_name='foo'
i found with postgresql, if i was always running this sort of query
then i create a view
===================================
create or replace view v_company_users as
select user.* , c.company_name
from users u, company c
where u.company_id = c.id;
2:05:34 pm
mark
wat os a voew
2:05:40 pm
Travis
then my app does
select * from v_company_uses where company='foo'
so, instead of doing a join query and specifying parameters, create a view, and the view barfs out the values and you query the where on the view.

Rebuilding large GIN indexes

Rebuilding large GIN indexes

takes time. A lot of time. With rebuilding, I mean when you for example have to UPDATE all your tsvector fields due to a change in tsearch2 configuration. (Yes, I had to do that because I had a slightly incorrect tsearch2 configuration for the archives search database). So don't do it. Instead use the fact that PostgreSQL has nice transactional DDL and do something like this:


BEGIN TRANSACTION;

CREATE TABLE messages_new AS SELECT id,txt,to_tsvector(txt) AS fti

FROM messages;

CREATE INDEX messages_new_fti ON messages_new USING gin(fti);

ANALYZE messags_new;

ALTER TABLE messages RENAME TO messages_old;

ALTER TABLE messages_new RENAME TO messages;

COMMIT;

DROP TABLE messages_old;

ALTER INDEX messages_new_fti RENAME TO messages_fti;


(apologies for any typos, I didn't bother to actually type these commands into the database again, and I lost my cut-and-paste of what I ran)

This way, the messages table can still serve up searches without any disruption to the searches at all. And creating the new index is a lot faster than updating the existing one if you have to touch all rows.

Getting random rows from a database table

Getting random rows from a database table

Selecting random rows from a table in your database is generally useful for two things: grabbing one or more rows to display and/or use somehow, and for selecting a random subset of your rows and performing some sort of statistical analysis on the data. While the standard way of using ORDER BY RANDOM() is occassionally useful, it is very slow, it is non-repeatable, and it does not scale well. I'll demonstrate some better methods to get random rows.

For this article, I'll be using a table named mydata which contains ten million rows of data, and a primary key named id, which is of type bigint.

First, it's important to distinguish between random and unordered. If you simply pull rows from your table without an ORDER BY clause, they may appear random, but they are not: they are simply in an undefined order. In PostgreSQL, they will be roughly in an order related to the last time they were updated or inserted. However, by "random" we really mean that any row in the table has as much chance as appearing as another row within our SELECT statement. We'll need some way to accomplish this is SQL.

---

ORDER BY RANDOM()

How do we get something "random" into our query? Every modern computer has some way of generating a random number, and PostgreSQL has a way as well: the built in RANDOM() function, which generates a double precision number from from 0.0 to 1.0:

SELECT RANDOM() FROM generate_series(1,5);

Running the above yields something like this:

random
-------------------
0.739388620946556
0.706028273329139
0.150622034911066
0.396196706686169
0.412912936415523

(Note the use of the nifty new generate_series() function to repeat a SQL command a certain number of times).

PostgreSQL also allows you to use RANDOM() in the ORDER BY clause, which is one way to get a random row from the database. Let's pull out three random values from our test table:

SELECT id FROM mydata ORDER BY RANDOM() LIMIT 3;

id
---------
3635389
9417084
8385175

This appears to work just fine, but it has a major drawback - it does not scale, and gets extremely slow as the table size increases. This is a consequence of how ORDER BY RANDOM() works - it basically assigns a random number to every row in the database, then orders the entire table by the random numbers, and then returns the rows you want. For small tables, this is not much of a problem, but this is a terrible solution as the tables grow in size. Here's a breakdown on speeds on my system for grabbing a single row by using the query SELECT id FROM mydata ORDER BY RANDOM() LIMIT 1:

Number of rows	Time to run
One thousand (1000)	3 milliseconds
Ten thousand (10,000)	40 milliseconds
One hundred thousand (100,000)	Half a second
One million (1,000,000)	7 seconds
Ten million (10,000,000)	149 seconds

Fortunately, there are much better ways to obtain random rows. There are two basic approaches to take - we can pick randomly from a range of values, or we can store a random number inside the table itself.

---

Range of Values

Let's keep using our mydata table, which has a primary key of id. If we know enough information about a column in the database, we can use that to get random rows by picking random values of that column. In this example, all we need to know is the minimum and maximum value of the id column, and we can have an external program generate a random number between the minimum and the maximum and put it into a query:

SELECT * FROM mydata WHERE id = '4012341';

We can also have the database help us choose the number, if we know there are a maximum of 10 million ids:

SELECT * FROM mydata
WHERE id =
(SELECT (RANDOM() * 10000000)::int OFFSET 0)
LIMIT 1;

Both run in under a second, as the primary key column id is indexed. (The use of OFFSET 0 is needed in the second query to force the planner to evaluate RANDOM() only one time).

There are a few problems with this approach, however. One obvious one is that the query above may fail if there are any "holes" in the range of numbers from min to max. Storing information about where the holes are is probably impractical, but we can get around it by finding the value that is closest to the random number we picked, like this:

SELECT * FROM mydata
WHERE id >= '4012341'
LIMIT 1;

While that query addresses the problem of holes, it has two additional problems: it does not guarantee that the same row is returned each time, and it sometimes runs very, very slow. Running an EXPLAIN plan shows us why the speed difference:

QUERY PLAN
----------------------------------------
Limit (cost=0.00..0.02 rows=1)
-> Seq Scan on mydata
(cost=0.00..223040.00 rows=9996117)
Filter: (id >= 4012341::bigint)

The index is not being used. As a good rule of thumb, never use a LIMIT without an ORDER BY clause. Let's add one in, which will solve both of our problems. The index will be used, and the results will be predictable:

SELECT * FROM mydata
WHERE id >= '4012341'
ORDER BY id LIMIT 1;

This strategy of using ">= (value) ORDER BY (column) LIMIT 1" is one which we will us a lot from this point forward.

Another problem is that we are not guaranteed to get the number of rows that we want. For example:

SELECT * FROM mydata
WHERE id >= '9999999'
ORDER BY id LIMIT 100;

This will only return 2 rows since our sample data has a maximum id of ten million. There are two ways around this problem: you can re-run the query with a new random number until you get the number of random rows you need, or you can adjust your random number (or your table) to make sure that you always have at least that many. For example, if your data has 100 rows and you want to pull 10 of them at random, then make sure you never ask for an id of more than 90. Alternatively, you could "pad" your table with 10 extra rows, and then safely use the numbers 1-100.

There is one final problem: picking our own random values from range will not produce truly random rows unless the data is perfectly uniformly distributed. Consider a table with two rows and values of 1 and 10. Our strategy above would cause the 10 value to appear more often than the 1 value, which is not the randomness we are looking for. In addition to the holes, if the values are not unique, then the distribution may not be uniform, and we once again lack true randomness. We need a way to combine the true randomness of ORDER BY RANDOM() with the speed of a Range of Values.

---

Random Column

The final and best solution is to create a new column in your database that stores random values. The table can then be sorted by this column, and get back random rows in a fast, repeatable, and truly random way. What we are basically doing is emulating the effect of ORDER BY RANDOM(), which as you recall creates a random value for each row in the database. Let's apply it to our test table.

First, we create a new column to hold the random values. Since RANDOM() returns the type "double precision", we create a new column of that type. We'll name it myrand:

ALTER TABLE mydata ADD myrand DOUBLE PRECISION;

Now we can populate that row with a random number from 0.0 to 1.0:

BEGIN;
UPDATE mydata SET myrand = RANDOM();
COMMIT;

This does take a non-trivial amount of time to run (372 seconds to populate all ten million rows), but it is a one-time cost. Since we'll be hitting this column to generate our random rows, we should put an index on it as well. But before we do that, we have to also ensure that our results are reproducible. In other words, the same query should return the same exact rows. Something like this is not guaranteed to get the same 10 rows each time it is run:

SELECT * FROM mydata
ORDER BY myrand LIMIT 10;

Why? Because there is no unique constraint on the myrand column, and it is possible (especially with our 10 million row example table) that two myrand columns contain the same value. As another rule of thumb, always make sure your ORDER BY clause specifies a unique set of rows. Our primary key, "id", is unique, so that makes a good backup for when our myrand column happens to have the same value. Our new query becomes:

SELECT * FROM mydata
ORDER BY myrand, id LIMIT 10;

Now we can create the index, on both of the columns in that ORDER BY. For good measure, we'll analyze the table as well:

CREATE INDEX myrand_randomhelp ON mydata(myrand,id);

ANALYZE VERBOSE mydata;

Before the index was in place, the query to grab a random row took over 180 seconds. Now that it is in place, the query runs in less than 1 second (126 milliseconds).

So that's our basic "Random Column" strategy: assign each row a random number, make sure it is linked to another unique column, and make an index across both of them. This allows us to get fast, repeatable, and truly random rows. You can also ensure that new rows get a new random value automatically added to them by doing this:

ALTER TABLE mydata ALTER myrand
SET NOT NULL DEFAULT RANDOM();

If you don't care about repeatability, and simply want to grab a random row, you can do this:

SELECT * FROM mydata
WHERE myrand >= (SELECT RANDOM() OFFSET 0)
ORDER BY myrand ASC LIMIT 1;

The ORDER BY clause is needed to ensure that our index is used. Note that Postgres has no problem using our previous index we created on both columns, because we put the myrand column first inside of that index. The above query is basically what Wikipedia uses when you click on the "Random Page" link.

Another advantage to using a Random Column is that not are the results reproducible, they are resettable. Let's say that you are using this method to pull 100 random rows at time out of a table with 1000 rows for statistical analysis. You also want to make sure that you never use the same row more than once, so you use an OFFSET:

SELECT * FROM mydata ORDER BY myrand, id
LIMIT 100 OFFSET 1;
SELECT * FROM mydata ORDER BY myrand, id
LIMIT 100 OFFSET 100;
SELECT * FROM mydata ORDER BY myrand, id
LIMIT 100 OFFSET 200;
etc...

(Note: although offset starts at 0, we ignore the first column as OFFSET 100 is easier to read then OFFSET 99). At some point, you want to run some more tests, but you don't want the same grouping as before. In other words, you want to reshuffle the deck of cards. Simple enough, just assign new values to the 'myrand' column:

UPDATE mydata SET myrand = RANDOM();

The only drawback to the whole Random Column strategy is the time and effort it takes to set it up, and the additional disk space needed to handle the extra column. Because of the extra column, INSERTS and UPDATES may run slightly slower.

Here's a summary of the three strategies to grab some random rows from a table:

Technique	Pros	Cons	When to use
ORDER BY RANDOM()	Completely random No table changes needed Easy to append to existing queries	Very slow: does not scale Non-repeatable result sets	Quick ad-hoc queries and very small tables that will not grow large
Range of Values	Very fast Uses existing columns	Not truly random Must track minimum and maximum Hard to get desired number of rows Inserts can affect results	When data is very well-defined and stable (even then, be cautious)
Random Column	Very fast Truly random Reproducible Automatically maintained Resettable Column data type matches random() for easy use	Takes up disk space Initial setup cost Inserts may be slowed by the extra column and the default value	Whenever possible

index of max value in a list PYTHON

the max function, it returns the maximum value in the
list rather than the index associated with that value.

How do I return the index?

l.index(max(l)

l.index(max(l)) will give you the index of the first occurrance of the
maximum.

m = max(l)
[ i for i,v in enumerate(l) if v==m ]

will give you a list of all indices where the max occurs. (Putting the
'max(l)' outside the list comprehension prevents it from being evaluated
for each loop element.)

autoVACUUM in postgres

stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 120

in postgresql.conf and
kill -1 the pid for postgres
using cat postmaster.pid

How to reload postgres config files without restarting db on the LIVE

you can change it on the live service and kill -1 to make it take effect
we should not kill postgres right
yea, if you edit postmaster.conf and change max_connections=512 then it should not require stopping and starting .
cat postmaster.pid
database]$ cat postmaster.pid
32623
/work/database
database]$ cat postmaster.pid
32623
so pid 32623 is your postmaster pid f
so if you kill -1 32623
after you have done the changes to postgresql.conf file
that will make postmaster re-read the config file without shutting it down
i.e. existing connections dont get dropped.
ur sure it wont kill the db like last time
and how to check if it has read the new postgresql.conf
i guess if you tail -f the log file.
the active postgresql log file (in pg_log folder) will say "reloading.."

received SIGHUP, reloading configuration files
if you had a really bad typo , then the database would either ignore the changes, or safely shutitself down.

Fixing dbus problems with gaim

rpm -ql dbus | grep lib
/lib/dbus-1.0
/lib/libdbus-1.so.3
/lib/libdbus-1.so.3.2.0
/var/lib/dbus
cd /lib/
sudo ln -s libdbus-1.so.3.2.0 libdbus-1.so.2
sudo ldconfig
gaim

vi tab retab --- space to tab -- tab to space

:set tabstop=4 " Force tabs to be displayed/expanded to 4 spaces (instead of default 8).
:set softtabstop=4 " Make Vim treat key as 4 spaces, but respect hard Tabs.
: " I don't think this one will do what you want.
:set expandtab " Turn Tab keypresses into spaces. Sounds like this is happening to you.
" You can still insert real Tabs as [Ctrl]-V [Tab].
:set noexpandtab " Leave Tab keys as real tabs (ASCII 9 character).
:1,$retab! " Convert all tabs to space or ASCII-9 (per "expandtab"),
" on lines 1_to_end-of-file.
:set shiftwidth=4 " When auto-indenting, indent by this much.
" (Use spaces/tabs per "expandtab".)
:help tabstop " Find out more about this stuff.
:help vimrc " Find out more about .vimrc/_vimrc :-)

Make python use UTF8 instead of ascii

cd /usr/lib/python2.3/site-packages/
vi sitecustomize.py

(add the following lines to the file, save it)
import sys, codecs
sys.setdefaultencoding('utf-8')

from
http://plone.org/products/cmfcontentpanels/issues/1

OSCON 2005 PostgreSQL Presentations

The OSCON 2005 PostgreSQL Presentations are up, but a bunch of them are in OpenOffice format. I went through the pain of installing OpenOffice to convert them to PDF. For completeness, I have also included the presentations that were already available as PDF.

Chris Browne:

• Replicating PostgreSQL Databases Using Slony-I
• Event Propagation in Slony-I
• Sample Replication RT/3
• Upcoming Slony-I Enhancements

Joe Conway:

• Terrabytes of Business Intelligence

Lance Obermeyer:

• Running PostgreSQL On Windows

Bruce Momjian:

• State of PostgreSQL 8.1
• Porting PostgreSQL To Windows

Aaron Thul:

• PostgreSQL Built Your Car

Robert Treat:

• Explaining Explain

How I replace out divs in inserts to keep the layout

i.Description =re.sub(r'<.div[^>]*?>', '', i.Description)#replaces div id tags

When div when span

DIV is an arbitrary *block* element. It can contain other block
elements (including other DIVs). P is block element for
paragraphs. P cannot contain other block elements; it cannot
contain other Ps; it cannot contain DIVs. DIV does not create a
new P. But DIV will close any P that is open.
[color=blue]
> I tend to use SPAN because it does not generate any line break.[/color]

SPAN is an *inline* element. It cannot contain a block element.
SPAN is closed whenever the block containing it is closed.

Faster Inserts with PostgreSQL

Faster Inserts with PostgreSQL

As I mentioned yesterday, I'm working on optimizing lots of inserts into a database, and I need solutions for different DB servers. Today I have been working with PostgreSQL. Although the PostgreSQL COPY command is recommended, I can't seam to get it to work with ColdFusion, and cfquery. I've tried lots of different ways of doing it.

Fast Insert Solution on PostgreSQL:

So far the fastest solution I have come up with is using PREPARE to create a temporary prepared statement (yes, I'm aware of cfqueryparam, and this method inserts with the highest speed). So here's how you do it:

PREPARE preparedInsert (int, varchar) AS
 INSERT INTO tableName (intColumn, charColumn)
 VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
EXECUTE preparedInsert (3,'c');
DEALLOCATE preparedInsert;

Your basically creating a function that allows you to pass variables to your insert statement. Inside the first set of parenthesis you list the types of your variables, then variables are referred to as $1, $3, etc. inside the statement.

Next you can EXECUTE the statement as many times as you need to (this can all be done inside one SQL statement, inside one cfquery tag).

Finally when I'm done, I DEALLOCATE the function, otherwise if you try to PREPARE a statement named preparedInsert again during the same connection session you will get an error.

Experimenting with functional python

http://www.bluebits.gr/weblog/programming/experimenting-with-functional-python-2006-07-26-20-11.html

Building Web Pages with Python

http://adminspotting.net/building-web-pages-with-python/

Default python encoding to unicode

In file
vim /usr/lib/python2.4/site.py
Comment out line 352 and add 353

352 #encoding = "ascii" # Default value set by _PyUnicode_Init()
353 encoding = "utf8"

to check

ipython
import sys
sys.getdefaultencoding()
Out[2]: 'utf8'

cgi programming with python

Take a look at Guido's examples here...

http://www.python.org/doc/essays/ppt/sd99east/sld057.htm

Best wishes,

How to disable system beep in windows

You can find and configure it under Device Manager|View|Show Hidden Devices|Non Plug and
Play|Beep|Action|Properties|Driver, then set the "Startup Type:" to "Disabled"

Changing your default shell

One of the neat things about *nix is the ability to work with many different shells. As with anything else in the *nix world there is bound to be heated debates over which shell is better. Whether you want to use good ol' sh or (my personal favorite) bash you can change your default shell using the commands below.

1. Do a cat on /etc/shells. The output will show you the location of the binaries for all the shells installed on your system. IT IS EXTREMELY IMPORTANT THAT YOU KNOW EXACTLY WHERE THE SHELL YOU WANT TO USE IS!:
   cat /etc/shells
   
   
2. Now use the chsh command to change your default shell. On Free and OpenBSD the command to change your default shell to bash is (this may differ if your on another *nix variant so read your man page for chsh):
   chsh -s /path/to/bash
   
   
3. If you don't know what variant you are using do this to find out:
   uname -a

The traffic within Amazon EC2 and S3 is free

The traffic within Amazon EC2 and S3 is free, so you can have setups as funky as you wish. Remeber what it takes to build Flickr or Livejournal datacenter? Now you can do similar setups from home (unbelievable) and just let Amazon take care of the networking and hardware. This is so much more ‘WebOS’ than Google’s walled garden.

I applaud Amazon.

Please note that EC2 is not limited to web hosting applications, far from it. It makes even more sense to use it for virtual render farms, to run simulations and other tasks that require a lot of computing power but are usually executed only once in a while. So if you need for ex. 100 instance-hours to complete the computation, you can make your own cluster of 20 machines of similar power (will cost about $10000 for hardware alone) and complete the task in 5 hours, you can use EC2 to create this virtual cluster, compute and then shut it down when done and pay much less — $10 per job. Or you could use EC2 to create a 200 machine virtual cluster, complete the job in half an hour and pay the same $10 for it. Think about that.

Python quick reference

http://www.geocities.com/labspython/pqrc-2.4-a4-latest.pdf

Python autocomplete and history

Title: Completer with history viewer support and more features Category:

Description: This module let "tab" key can indent and completing valid python identifiers, keywords, and filenames. This module support history view also. Note1: original python rlcompleter module only avail in unix-like environment Note2: if you seek a more simple completer you could try Jian Ding Chen's recipe at: http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/496812 Source: Text Source # History.py # # Store the file "History.py" # in site-packages directory like "/usr/lib/python2.4/site-packages," # or a directory pointed by PYTHONPATH environment varable, # or your home directory. # # Insert the line, "import History" to "~/.pystartup" file, # and set an environment variable to point to it: # "export PYTHONSTARTUP=${HOME}/.pystartup" in bash. # # References: # Guido van Rossum. Python Tutorial. Python Sfotware Foundation, 2005. 86 # Jian Ding Chen. Indentable rlcompleter. Python Cookbook Recipe 496812 # Guido van Rossum. rlcompleter.py. Python Sfotware Foundation, 2005 # # 2006.06.29 Sunjoong LEE # __author__ = 'Sunjoong LEE ' __date__ = '2006-06-29' __version__ = '1.0' from atexit import register from itertools import count as icount, ifilter, imap from os import listdir, remove from os.path import exists, expanduser, split as psplit from readline import clear_history, get_current_history_length, \ get_completer_delims, get_history_item, get_line_buffer, \ insert_text, parse_and_bind, read_history_file, redisplay, \ set_completer, set_completer_delims, set_history_length, \ set_pre_input_hook, write_history_file from pwd import getpwall from rlcompleter import Completer from tempfile import mktemp import __main__ historyPath = expanduser('~/.pyhistory') HISTORY_LENGTH = 100 class History: def __init__(self): self.recall() set_history_length(HISTORY_LENGTH) parse_and_bind('tab: complete') # delims = get_completer_delims() delims = ' \t\n`!@#$%^&*()-=+[{]}\\|;:,<>?' set_completer_delims(delims) set_completer(irlcompleter().complete) def __repr__(self): """print out current history information""" command = get_history_item(get_current_history_length()) if command == 'history': length = get_current_history_length() if length > 1: return reduce(lambda x, y: '%s\n%s' % (x, y), imap(get_history_item, xrange(1, length))) else: return '' else: return '<%s instance>' % __name__ def __call__(self): """print out current history information with line number""" length = get_current_history_length() if length > 1: kount = icount(1).next for command in imap(get_history_item, xrange(1, length)): print '%s\t%s' % (kount(), command) def save(self, filename, pos = None, end = None): """write history number from pos to end into filename file""" length = get_current_history_length() if length > 1: if not pos: pos = 1 elif pos >= length - 1: pos = length - 1 elif pos < 1: pos = length + pos - 1 if not end: end = length elif end >= length: end = length if end < 0: end = length + end else: end = end + 1 fp = open(filename, 'w') write = fp.write if pos < end: map(lambda x: write('%s\n' % x), imap(get_history_item, xrange(pos, end))) else: write('%s\n' % get_history_item(pos)) fp.close() def clear(self): """save the current history and clear it""" write_history_file(historyPath) clear_history() def recall(self, historyPath = historyPath): """clear the current history and recall it from saved""" clear_history() if exists(historyPath): read_history_file(historyPath) def execute(self, pos, end = None): """execute history number from pos to end""" length = get_current_history_length() if length > 1: if pos >= length - 1: pos = length - 1 elif pos < 1: pos = length + pos - 1 if not end: end = pos + 1 elif end >= length: end = length if end < 0: end = length + end else: end = end + 1 to_execute = map(get_history_item, xrange(pos, end)) filename = mktemp() fp = open(filename, 'w') write = fp.write map(lambda x: write('%s\n' % x), to_execute.__iter__()) fp.close() try: execfile(filename, __main__.__dict__) read_history_file(filename) remove(filename) except: remove(filename) class irlcompleter(Completer): def complete(self, text, state): if text == '': # you could replace ' ' to \t if you indent via tab return [' ', None][state] elif text.count("'") == 1: if not state: self.file_matches(text, "'") try: return self.matches[state] except IndexError: return None elif text.count('"') == 1: if not state: self.file_matches(text, '"') try: return self.matches[state] except IndexError: return None else: return Completer.complete(self, text, state) def file_matches(self, text, mark): if '~' in text: if '/' in text: text = '%s%s%s' % (mark, expanduser( text[text.find('~'):text.find('/')]), text[text.find('/'):]) else: self.user_matches(text, mark) return text1 = text[1:] delim = '/' if not text1: directory = '' elif text1 == '.': directory = '.' elif text1 == '..': directory = '..' elif text1 == '/': directory = '/' delim = '' elif text1[-1] == '/': directory = text1[:-1] delim = text1[len(directory):] else: directory, partial = psplit(text1) delim = text1[len(directory):][:-len(partial)] if directory: listing = map(lambda x: '%s%s%s%s' % (mark, directory, delim, x), listdir(directory).__iter__()) else: listing = map(lambda x: '%s%s' % (mark, x), listdir('.').__iter__()) n = len(text) self.matches = filter(lambda x: x[:n] == text, listing.__iter__()) def user_matches(self, text, mark): n = len(text) self.matches = filter(lambda x: x[:n] == text, imap(lambda x: '%s~%s' % (mark, x[0]), getpwall().__iter__())) def save_history(historyPath = historyPath): from readline import write_history_file write_history_file(historyPath) register(save_history) def hook(): from readline import set_pre_input_hook import __main__ set_pre_input_hook() delattr(__main__, 'History') delattr(__main__, '__file__') set_pre_input_hook(hook) setattr(__main__.__builtins__, 'history', History()) Discussion: To show an example, I had input as below; i[tab][tab]m[tab] pwd[return] pri[tab] pwd.[tab]__do[tab][return] fp = op[tab]('/[tab][tab]e[tab]/pas[tab]', 'r')[return] hi[tab][return] >>> i i if in int is issubclass id import input intern isinstance iter >>> import pwd >>> print pwd. pwd.__class__ pwd.__init__ pwd.__str__ pwd.__delattr__ pwd.__name__ pwd.getpwall pwd.__dict__ pwd.__new__ pwd.getpwnam pwd.__doc__ pwd.__reduce__ pwd.getpwuid pwd.__file__ pwd.__reduce_ex__ pwd.struct_passwd pwd.__getattribute__ pwd.__repr__ pwd.struct_pwent pwd.__hash__ pwd.__setattr__ >>> fp = open('/ '/bin '/home '/mnt '/sbin '/boot '/lib '/opt '/tmp '/dev '/lost+found '/proc '/usr '/etc '/media '/root '/var >>> history import pwd print pwd.__doc__ fp = open('/etc/passwd', 'r')