Dave's PostgreSQL Stuff

 We started looking at PostgreSQL transactions last time , and now we can plunge onward.  Populating a new table is an intense process. Besides the columns we declare, there are others created for us by the server. Please read the documentation on System Columns.  First Example We start by creating a simple two-column table. demo=# create table foo (a int, b int); CREATE TABLE demo=# insert into foo (a,b) values (1,2),(3,4),(5,6); INSERT 0 3 demo=# select a,b from foo;  a | b  ---+---  1 | 2  3 | 4  5 | 6 (3 rows) The server adds the system columns that can be seen if we explicitly select them. demo=# select a, b, tableoid, xmin, cmin, xmax, cmax, ctid from foo;  a | b | tableoid | xmin | cmin | xmax | cmax | ctid   ---+---+----------+------+------+------+------+-------  1 | 2 |    16401 |  776 |    0 |    0 |    0 | (0,1)  3 | 4 |    16401 |  776 |  ...

Transactions can be a rough concept for those new to databases. Those new to PostgreSQL will find the implementation much different than those in other databases.  The default isolation level in PostgreSQL is READ COMMITTED so that you only see data that has been committed. If someone else works on the same table, you will not see those changes until they are committed. Start with an elementary table: demo=# create table ex03 (a int); CREATE TABLE demo=# insert into ex03 (a) values (1); INSERT 0 1 Behind the scenes, the system sets up some information to track your data, as we learned here and here .  demo=# select a, ctid, xmin, xmax, cmin, cmax from ex03;  a | ctid  | xmin | xmax | cmin | cmax  ---+-------+------+------+------+------  1 | (0,1) |  768 |    0 |    0 |    0 (1 row) The CTID was introduced last time , and the other columns may be new. The documentation details their definitions. For this blog, we are goin...

 Last time , we started looking at how PostgreSQL stores data by discovering where the data is stored and had a quick peek at that data. There is only one record at this point. demo=# SELECT * FROM ex01;  a | b  ---+---  1 | 1 (1 row) And the contents of that file are not precisely illuminating at first glance. For now, just trust me that our row is in there, and you will see where in just a little bit. root@ThinkPad:/var/lib/postgresql/17/main/base/16389# od -c 16390 0000000  \0  \0  \0  \0   (   z 227 001  \0  \0  \0  \0 034  \0 340 037 0000020  \0     004      \0  \0  \0  \0 340 237   @  \0  \0  \0  \0  \0 0000040  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 * 0017740 362 002  \0  \0  \0  \0  \...

PostgreSQL is an amazing database. And some of us wonder how it takes what we enter on a keyboard and stores it. The following is peeking into the 'clockworks' to see how things are done. Step 1 On a fresh install of Ubuntu with PostgreSQL 17, let us create a database. stoker@ThinkPad:~$ createdb demo Password:  stoker@ThinkPad:~$ psql demo Password for user stoker:  psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1)) Type "help" for help. Now that we are in this new database, we can ask where PostgreSQL stores the data by looking for the data directory. demo=# show data_directory;        data_directory         -----------------------------  /var/lib/postgresql/17/main (1 row) This informs us that our data will be somewhere under the /var/lib/postgresql17/main directory.  Step 2 Issuing a command to create a table starts of series of events that we will study in later posts. Right now, we want to see where the stuff goes. Here a simp...