Hw2FAQ

** Q: I can't start postgres. I'm getting the message FATAL: shmat(id=469762150) failed: Not enough space. How do I fix that?

• A: Try running the "ipcclean" command. See the Postgres docs for details.
• Q: My gistnext code doesn't seem to be getting called at all! What's up?
• A: The postgres optimizer is probably not choosing an index scan; it's choosing a heap scan. You can validate this by running your query with the word "explain" in front. If it d {{{ cs186-ee=# explain select id, box_mindistance(the_box, '(0.5,0.5,0.5,0.5)') as dist from testgist where the_box >> '(0.5,0.5,0.5,0.5)'; QUERY PLAN

---

Bitmap Heap Scan on testgist (cost=56.00..155.00 rows=1000 width=36) Recheck Cond: (the_box >> '(0.5,0.5),(0.5,0.5)'::box) -> Bitmap Index Scan on testgistix (cost=0.00..55.75 rows=1000 width=0) Index Cond: (the_box >> '(0.5,0.5),(0.5,0.5)'::box) (4 rows) }}} To fix the problem, you can tell the optimizer to disable sequential scans and "bitmap scans" (which we haven't studied): {{{ cs186-ee=# set enable_seqscan to off; SET cs186-ee=# set enable_bitmapscan to off; SET cs186-ee=# explain select id, box_mindistance(the_box, '(0.5,0.5,0.5,0.5)') as dist from testgist where the_box >> '(0.5,0.5,0.5,0.5)'; QUERY PLAN

Index Scan using testgistix on testgist (cost=0.00..404.25 rows=1000 width=36) Index Cond: (the_box >> '(0.5,0.5),(0.5,0.5)'::box) (2 rows)

cs186-ee=# }}}

Alternatively, just start pg_ctl with the flags to disable those: {{{ % pg_ctl start -o "-fs -fb" }}}

---

The following are "Frequently Made Comments" I made in office hours. They don't exactly answer questions, so they don't quite qualify as an FAQ:

1. When I did my solution to the homework, I chose to rewrite gistnext's basic structure. I think the way they wrote it, as two nested infinite for loops, is inelegant. Trying to fit the priority-queue-based traversal into that looping structure seemed unnatural to me, so I just wrote my own main loops from scratch, reusing important snippets from the old gistnext implementation (e.g. the way that it accesses and manipulates the core data structures like buffers, pages, index pages, etc.) You can certainly solve the homework while preserving their loops, it's up to you.

2. One useful tidbit in the original gistget.c is the use of the function GistPageIsLeaf.

3. Another useful tidbit is the code that pulls out an it->t_tid. The t_tid is a Tuple ID (what we call a Record ID or RID in class).

4. You should read and use gistindex_keydistance -- it's the code that eventually calls your box_mindist stuff...

5. Debugging via print statements:

Some people like to debug by having their code print things out. In a server process like Postgres this is a bit tricky, since putting the usual "printf" statement in your code won't get the output to your client -- remember that psql is a different process than the postgres backend that services your SQL requests!

To get postgres to pass printed text from the backend to psql, you want to use the built-in "elog" function in Postgres. There are various levels of messages you can gave Postgres raise, but we'll stick with the NOTICE message which appears at the output by default.

To add a print statement to your code, just call "elog(NOTICE, foo)", where foo is a C string. For example, you might print out the GISTSearchStack via this C code:

{{{

char buf[1024]; GISTSearchStack curstk;

for (curstk = so->stk; curstk != NULL; curstk = curstk->next) { sprintf(buf, "Stack entry %d\n", curstk->block); elog(NOTICE, buf); } }}} Note the use of sprintf to fill the buf string with formatted text.