gistfile1.txt

This note explains how to build Postgres from source and setup to debug it using LLDB on a Mac. I used this technique to research this article:
http://patshaughnessy.net/2014/10/13/following-a-select-statement-through-postgres-internals

1. Shut down existing postgres if necessary - you don’t want to mess up your existing DB or work :)

$ ps aux | grep postgres
pat               456   0.0  0.0  2503812    828   ??  Ss   Sun10AM   0:11.59 postgres: stats collector process       
pat               455   0.0  0.0  2649692   2536   ??  Ss   Sun10AM   0:05.00 postgres: autovacuum launcher process       
pat               454   0.0  0.0  2640476    304   ??  Ss   Sun10AM   0:00.74 postgres: wal writer process       
pat               453   0.0  0.0  2640476    336   ??  Ss   Sun10AM   0:00.76 postgres: writer process       
pat               452   0.0  0.0  2640476    364   ??  Ss   Sun10AM   0:00.03 postgres: checkpointer process       
pat               332   0.0  0.0  2643548   1144   ??  S    Sun10AM   0:04.43 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log
pat             10687   0.0  0.0  2423360    196 s009  R+    9:17AM   0:00.00 grep postgres

$ cd /usr/local/opt/postgresql
$ launchctl unload homebrew.mxcl.postgresql.plist

Double check it’s not running:

$ psql some_db                 
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?


2. Download source code

See:
http://www.postgresql.org/ftp/source/

$ curl https://ftp.postgresql.org/pub/source/v9.4.4/postgresql-9.4.4.tar.bz2 | bzip2 -d | tar x

$ cd postgresql-9.4.4

3. Build with debugging options enabled

$  ./configure --enable-cassert --enable-debug CFLAGS="-ggdb -O0 -fno-omit-frame-pointer"

checking build system type... x86_64-apple-darwin15.0.0
checking host system type... x86_64-apple-darwin15.0.0
checking which template to use... darwin
checking whether to build with 64-bit integer date/time support... yes
checking whether NLS is wanted... no

etc…


Above that option “-O0”  is: dash, capital O and number zero

See this post for more info: 
https://wiki.postgresql.org/wiki/Developer_FAQ#Compile-time


$ make

make
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C src all
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C common all
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C ../backend submake-errcodes

etc…

cp ../../../contrib/spi/refint.so refint.so
cp ../../../contrib/spi/autoinc.so autoinc.so
cp ../../../contrib/dummy_seclabel/dummy_seclabel.so dummy_seclabel.so
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C config all
make[1]: Nothing to be done for `all'.
All of PostgreSQL successfully made. Ready to install.


$ sudo make install
Password:
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C src install
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C common install
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C ../backend submake-errcodes
make[3]: Nothing to be done for `submake-errcodes'.
/bin/sh ../../config/install-sh -c -d '/usr/local/pgsql/lib'
/usr/bin/install -c -m 644  libpgcommon.a '/usr/local/pgsql/lib/libpgcommon.a'
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C port install
/Applications/Xcode.app/Contents/Developer/usr/bin/make -C ../backend submake-errcodes
make[3]: Nothing to be done for `submake-errcodes'.

etc…

/Applications/Xcode.app/Contents/Developer/usr/bin/make -C config install
/bin/sh ../config/install-sh -c -d '/usr/local/pgsql/lib/pgxs/config'
/usr/bin/install -c -m 755 ./install-sh '/usr/local/pgsql/lib/pgxs/config/install-sh'
PostgreSQL installation complete.

4. Skip the adduser command from the postgres docs - I’ll just run postgres using my own user account to make debugging easier

Instead, I’ll set the data directory to be owned by my account:

$ sudo chown pat /usr/local/pgsql/data

5. Init the data directory

$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

6. Start the server

$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start

7. Create a test db

$ /usr/local/pgsql/bin/createdb test

8. Test some basic SQL using the test db

$ /usr/local/pgsql/bin/psql test

test=# \d
No relations found.

test=# CREATE TABLE posts(
	id serial PRIMARY KEY,
	title VARCHAR (50) NOT NULL
);

test=# \d
            List of relations
 Schema |    Name     |   Type   | Owner 
--------+-------------+----------+-------
 public | posts        | table    | pat
 public | posts_id_seq | sequence | pat
(2 rows)


test=# INSERT INTO posts(title) VALUES ('This is a test.');

test=# SELECT * from posts;
 id |      title      
----+-----------------
  1 | This is a test.
(1 row)

test=# \q



Now, let’s run LLDB!


9. First, look at what processes are running:

$ ps aux | grep postgres            
pat             22978   0.0  0.0  2474160    684   ??  SNs   9:37AM   0:00.02 postgres: stats collector process     
pat             22977   0.0  0.0  2627208   1756   ??  SNs   9:37AM   0:00.01 postgres: autovacuum launcher process     
pat             22976   0.0  0.0  2602632    644   ??  SNs   9:37AM   0:00.01 postgres: wal writer process     
pat             22975   0.0  0.0  2602632   1704   ??  SNs   9:37AM   0:00.05 postgres: writer process     
pat             22974   0.0  0.0  2610824   2508   ??  SNs   9:37AM   0:00.01 postgres: checkpointer process     
pat             22972   0.0  0.1  2599560  12916 s009  SN    9:37AM   0:00.02 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
pat             23002   0.0  0.0  2434824    760 s009  S+    9:43AM   0:00.00 grep postgres

10. Now, create a new connection from another terminal window:

$ /usr/local/pgsql/bin/psql test
psql (9.4.4)
Type "help" for help.

test=# 


11. Return to the server terminal window, and repeat the ps command:

ps aux | grep postgres
pat             38593   0.0  0.0  2598140   3112   ??  Ss   10:09AM   0:00.01 postgres: pat test [local] idle  
pat             38589   0.0  0.0  2470304    656   ??  Ss   10:08AM   0:00.00 postgres: stats collector process     
pat             38588   0.0  0.0  2614136   1576   ??  Ss   10:08AM   0:00.00 postgres: autovacuum launcher process     
pat             38587   0.0  0.0  2597752    684   ??  Ss   10:08AM   0:00.01 postgres: wal writer process     
pat             38586   0.0  0.0  2606968   1520   ??  Ss   10:08AM   0:00.04 postgres: writer process     
pat             38585   0.0  0.0  2597752    716   ??  Ss   10:08AM   0:00.00 postgres: checkpointer process     
pat             38583   0.0  0.1  2599800  13880 s009  S    10:08AM   0:00.02 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
pat             23065   0.0  0.1  2580296  15332   ??  Ss    9:46AM   0:00.36 /Applications/MacVim-snapshot-76/MacVim.app/Contents/MacOS/Vim -f -g tcop/postgres.c
pat             38595   0.0  0.0  2434824    764 s009  S+   10:09AM   0:00.00 grep postgres



Notice there’s a new process handling requests for my psql session:

pat             38593   0.0  0.0  2598140   3112   ??  Ss   10:09AM   0:00.01 postgres: pat test [local] idle  

12. Attach to the  process handling my PSQL connection using LLDB - I got 38593 from the list above!

$ lldb
(lldb) attach -p 38593
Process 38593 stopped
* thread #1: tid = 0x12ab21, 0x00007fff8dca317a libsystem_kernel.dylib`__recvfrom + 10, queue = 'com.apple.main-thread', stop reason = signal SIGSTOP
    frame #0: 0x00007fff8dca317a libsystem_kernel.dylib`__recvfrom + 10
libsystem_kernel.dylib`__recvfrom:
->  0x7fff8dca317a <+10>: jae    0x7fff8dca3184            ; <+20>
    0x7fff8dca317c <+12>: movq   %rax, %rdi
    0x7fff8dca317f <+15>: jmp    0x7fff8dc9e2d4            ; cerror
    0x7fff8dca3184 <+20>: retq   

Executable module set to "/usr/local/pgsql/bin/postgres".
Architecture set to: x86_64h-apple-macosx.

13. Set a breakpoint

(lldb) b exec_simple_query
Breakpoint 1: where = postgres`exec_simple_query + 48 at postgres.c:854, address = 0x0000000101450250

This is the start of where Postgres executes a single SQL statement. See my article for more context.
http://patshaughnessy.net/2014/10/13/following-a-select-statement-through-postgres-internals

14. Allow the posrtgres to continue - the attach command stops the process.

(lldb) c
Process 38593 resuming

15. Return to the client window, and type in a SQL statement

test=# select * from posts;


… notice no result will be returned - this is because the server hit the breakpoint!

16. Return the server window and see Postgres stopped at the breakpoint

Process 38593 stopped
* thread #1: tid = 0x12ab21, 0x0000000101450250 postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 48 at postgres.c:854, queue = 'com.apple.main-thread', stop reason = breakpoint 1.1
    frame #0: 0x0000000101450250 postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 48 at postgres.c:854
   851 	static void
   852 	exec_simple_query(const char *query_string)
   853 	{
-> 854 		CommandDest dest = whereToSendOutput;
   855 		MemoryContext oldcontext;
   856 		List	   *parsetree_list;
   857 		ListCell   *parsetree_item;


Get a backtrace/stackdump:

(lldb) bt

* thread #1: tid = 0x12ab21, 0x0000000101450250 postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 48 at postgres.c:854, queue = 'com.apple.main-thread', stop reason = breakpoint 1.1
  * frame #0: 0x0000000101450250 postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 48 at postgres.c:854
    frame #1: 0x000000010144f9f4 postgres`PostgresMain(argc=1, argv=0x00007fa7eb803458, dbname=0x00007fa7eb8032c0, username=0x00007fa7eb8032a0) + 2868 at postgres.c:4074
    frame #2: 0x00000001013b579f postgres`BackendRun(port=0x00007fa7eb600230) + 703 at postmaster.c:4164
    frame #3: 0x00000001013b49a4 postgres`BackendStartup(port=0x00007fa7eb600230) + 420 at postmaster.c:3829
    frame #4: 0x00000001013b3b08 postgres`ServerLoop + 616 at postmaster.c:1597
    frame #5: 0x00000001013b138f postgres`PostmasterMain(argc=3, argv=0x00007fa7eb403790) + 5455 at postmaster.c:1244
    frame #6: 0x00000001012f090b postgres`main(argc=3, argv=0x00007fa7eb403790) + 779 at main.c:228
    frame #7: 0x00007fff888e25ad libdyld.dylib`start + 1


Look at the current location’s C source code:

(lldb) l
   858 		bool		save_log_statement_stats = log_statement_stats;
   859 		bool		was_logged = false;
   860 		bool		isTopLevel;
   861 		char		msec_str[32];
   862 	
   863 	
   864 		/*


Step forward by 1 C statement:

(lldb) n
(lldb) Process 38593 stopped
* thread #1: tid = 0x12ab21, 0x000000010145025b postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 59 at postgres.c:858, queue = 'com.apple.main-thread', stop reason = step over
    frame #0: 0x000000010145025b postgres`exec_simple_query(query_string=0x00007fa7ec044e38) + 59 at postgres.c:858
   855 		MemoryContext oldcontext;
   856 		List	   *parsetree_list;
   857 		ListCell   *parsetree_item;
-> 858 		bool		save_log_statement_stats = log_statement_stats;
   859 		bool		was_logged = false;
   860 		bool		isTopLevel;
   861 		char		msec_str[32];




Have fun!!  :) :)