DEV Community: Ahmed Hisham

Here's How To Create Your First PostgrSQL Extension Part 4

Ahmed Hisham — Thu, 27 Jul 2023 06:45:44 +0000

Continuing on part3 we got into the stage where we have .sql file left to build the extension.

We should name the file as follows add_two_numbers--0.0.1.sql including the version.

The file will contain the following:

CREATE OR REPLACE FUNCTION
add_two_numbers(int,int) RETURNS int AS 'MODULE_PATHNAME','add_two_numbers'
LANGUAGE C STRICT;

Then we finally can install the extension:

sudo make install

You should get an output similar to:

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -fPIC -I. -I./ -I/usr/local/pgsql-13/include/server -I/usr/local/pgsql-13/include/internal  -D_GNU_SOURCE   -c -o add_two_numbers.o add_two_numbers.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -fPIC add_two_numbers.o -L/usr/local/pgsql-13/lib   -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql-13/lib',--enable-new-dtags  -shared -o add_two_numbers.so
/usr/bin/mkdir -p '/usr/local/pgsql-13/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql-13/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql-13/lib'
/usr/bin/install -c -m 644 .//add_two_numbers.control '/usr/local/pgsql-13/share/extension/'
/usr/bin/install -c -m 644 .//add_two_numbers--0.0.1.sql  '/usr/local/pgsql-13/share/extension/'
/usr/bin/install -c -m 755  add_two_numbers.so '/usr/local/pgsql-13/lib/'

This should mean that the extension is installed successfully. Now it's time to run a postgres instance and test the extension, start a postgres instance from the binaries directory, in my case it will be from usr/local/pgsql-13/bin:

./pg_ctl start -l logfile -D ./data

Then the instance:

./psql postgres

Create the extension:

postgres=# CREATE EXTENSION add_two_numbers;
CREATE EXTENSION

Run a query to test the function:

postgres=# SELECT add_two_numbers(1,3);
 add_two_numbers
-----------------
               4
(1 row)

It worked!, congratulations for building your first extension, I hope that was helpful!.

Here's How To Create Your First PostgrSQL Extension Part 3

Ahmed Hisham — Thu, 27 Jul 2023 06:22:19 +0000

After part2 we should have one file so far in our directory (Makefile), now we will get into the .control file which provides metadata about the extension, also we will be going through the .c file:

Starting by `add_two_numbers.control` file:

comment = 'Simple number add function'
default_version = '0.0.1'
relocatable = true
module_pathname = '$libdir/add_two_numbers'

comment: a brief description for extension functionality

default_version: as the name suggests, it specifies the default version of the extension

relocatable: if set to true it means that this extension can be moved to another location or server without causing issues, some extension crashes once they are moved to a different location, they require a different setup, but in the case of our extension it doesn't.

module_pathname: specifies the location to the shared library contains the extension code (the .so file in specific), which in my case will be found in /usr/local/pgsql-13/lib, and you can specify it manually up above by replacing $libdir with /usr/local/pgsql-13/lib.

Creating `add_two_numbers.c` file:

The c file contain the actual functionality of the extension, which is the addition operation between two numbers, Here's how we create a extension function in postgres according to docs:

#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(add_two_numbers);

Datum
add_two_numbers(PG_FUNCTION_ARGS)
{
int32 arg1 = PG_GETARG_INT32(0);
int32 arg2 = PG_GETARG_INT32(1);

PG_RETURN_INT32(arg1 + arg2);
}

So far if we ls in the following files should be shown:

Makefile  add_two_numbers.c  add_two_numbers.control

In the next part we will create the last file, build the extension and run it together!.

Here's How To Create Your First PostgrSQL Extension Part 2

Ahmed Hisham — Wed, 26 Jul 2023 16:39:39 +0000

We will continue what we started here, Now let's create the necessary files, and go through each of them. We stopped where we created a directory for the extension add_two_numbers. In this Blog we will start by creating the Makefile.

Makefile:

MODULES = add_two_numbers
EXTENSION = add_two_numbers
DATA = add_two_numbers--0.0.1.sql
PG_CONFIG = /usr/local/pgsql-13/bin/pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

MODULES: list of shared-library objects to be built from source files with the same stem, which mean you should specify the name of the c file we will build which we will name add_two_numbers

EXTENSION: specify the name of the extension that postgres will use to identify our extension, we will name it add_two_numbers as well.

DATA: by this variable we specify the sql file that postgres will use to run the necessary queries to create and install the extension in the database.

PG_CONFIG: By this variable we specify the path to the pg_config binary file in the postgres database where we will install the extension, this will give the make command the necessary information for proper installation. Also we will need this variable in the following variable to get the path for a very important file that extensions use in the building process, this file which is pgxs.mk, it contains generic rules to build extensions.

PGXS: as we just said above, here we need to assign a command to that retrieves the pgxs.mk file location. In $(shell $(PG_CONFIG) --pgxs) the $(PG_CONFIG) will retrieve what we assigned (PG_CONFIG) variable which is the path to the pg_config file, that's to execute it with the --pgxs flag to get the path of pgxs.mk file.

include $(PGXS): then this part is responsible to include pgxs.mk so that to apply the rules in that file to build the extension.

Very important Note: You can always refer to the other variables you can add to Makefile, as by opening pgxs.mk file you will find instructions on each variable especially for these we created, and for other variables that can be necessary for you later extensions, so take a look on it!. Get the file path by running $(PG_CONFIG) --pgxs to open it.

Here's How To Create Your First PostgrSQL Extension Part 1

Ahmed Hisham — Wed, 26 Jul 2023 12:25:05 +0000

The following blogs will target the most basics of creating a PostgreSQl extension, at the end you should become pretty familiar with what's required to create an extension for PostgreSQL. the blog series assumes you are working with a linux system like Ubuntu.

Prerequisites

Obviously you should have your version of PostgreSQL, and won't matter which version of PostgreSQL you will be using since in this tutorial we will create a very simple extension. If you don't already installed PostgreSQL, you can directly install it's package, or install it from a source code following this blog here where I described in details how to do that.

Creating an extension!:

By having PostgreSQL installed on your machine, you should have the binaries (executable files) installed somewhere in your system, for me they are in /usr/local/pgsql-13/bin.
Now create a directory where you will create the extension (you can create it anywhere) and name it add_two_numbers:

mkdir add_two_numbers

Now we need to understand what are the files exactly we need to create to accomplish the mission, there are three necessary files to create:

Makefile file: it's used to build the extension, in which you specify the instructions to build the extension.
*.control file: This file provides a metadata about the extension like the name, version and author.
*.sql file: this will contain the required sql queries to build the extension functionalities
*.c file: And this c file will contain the c functions that your extension supports.

In the next blog we will be going through each of them in details and explore the steps to build up the extension!.

When To Use Graph Database Model Over Relational Database Models

Ahmed Hisham — Thu, 13 Jul 2023 04:39:51 +0000

There are a lot of different database models out there. Nothing is right and wrong, it all depends on the cases and the requirements of the project, the blog will focus on when to use Graph databases over relational ones.

When to use a Graph model?

When Complex relations exist between data. A general and common case is when building a social media app, you will most probably use a Graph model to handle relations between users, as graph model will be much easier structure to handle complex interconnected data.
When project data model is inconsistent and requires frequent changes. Given social media app as an example, the shape of data will often be changed as demands and requirements change, so having flexible data model is necessary
When Additional attributes are only necessary for some entities (not all of them), in these cases working with relational databases will force you to create a separate table (best practices) to handle additional attributes rather than having (null) value for those attributes in rows that don't have values for them, so it's harder to handle those type of issues in relational models than in graph models which is simpler, and changing the data structure at any point in time is easier
Running reading queries is simpler with cypher than sql in a lot of cases

Graph models is not always the best suited, you should always think in terms of performance, which type of databases will bring the best performance possible?, also what type of queries I will often be running?, what kind of data you are working with?, and more importantly what expertise your team has?. Thinking in terms of all of these is important to make the right decision and choose the most suited database model to fit your needs.

I've been using postgres for a quite a long time, and I came recently across an extension that add and power postgres with graph functionalities, the extension is apache/age which is worth checking for those want to leverage both graph and relational databases functionalities in a single database.

A Look To A Query Inside Postgres Source Code

Ahmed Hisham — Tue, 11 Jul 2023 12:47:54 +0000

I would hope this blog to be a start of series where we debug PostgreSQL and see what's going on under the hood. In this blog specifically we will start by a very simple example where we stop at some point in the code using GDB debugger tool, and take a deeper look at how PostgreSQL represent the query and check a single property of it to make sure it's as expected, this will hopefully give us some confident debugging PostgreSQL.

Postgres Installation

You of don't already have PostgreSQl installed from source code you can follow this guide here and make sure you include the --enable-debug option. Almost any postgres version will be fine since what we will be debugging is almost untouched since the earliest versions of postgres. Also this blog assumes that you are already familiar with GDB, if not please follow any online tutorial to learn the basics as we won't need more than that :).

Understanding what we are going to debug

What we will debug exactly is a Query struct which is how postgres represent a query, it's defined in src/include/nodes/parsenodes.h in postgres codebase (don't be intimidated, you are not expected to know what each property or part of the code represents as postgres is very very huge codebase):

typedef struct Query
{
    NodeTag     type;

    CmdType     commandType;    /* select|insert|update|delete|merge|utility */

    /* where did I come from? */
    QuerySource querySource pg_node_attr(query_jumble_ignore);

    /*
     * query identifier (can be set by plugins); ignored for equal, as it
     * might not be set; also not stored.  This is the result of the query
     * jumble, hence ignored.
     */
    uint64      queryId pg_node_attr(equal_ignore, query_jumble_ignore, read_write_ignore, read_as(0));

    /* do I set the command result tag? */
    bool        canSetTag pg_node_attr(query_jumble_ignore);

    Node       *utilityStmt;    /* non-null if commandType == CMD_UTILITY */

    /*
     * rtable index of target relation for INSERT/UPDATE/DELETE/MERGE; 0 for
     * SELECT.  This is ignored in the query jumble as unrelated to the
     * compilation of the query ID.
     */
    int         resultRelation pg_node_attr(query_jumble_ignore);

    /* has aggregates in tlist or havingQual */
    bool        hasAggs pg_node_attr(query_jumble_ignore);
    /* has window functions in tlist */
    bool        hasWindowFuncs pg_node_attr(query_jumble_ignore);
    /* has set-returning functions in tlist */
    bool        hasTargetSRFs pg_node_attr(query_jumble_ignore);
    /* has subquery SubLink */
    bool        hasSubLinks pg_node_attr(query_jumble_ignore);
    /* distinctClause is from DISTINCT ON */
    bool        hasDistinctOn pg_node_attr(query_jumble_ignore);
    /* WITH RECURSIVE was specified */
    bool        hasRecursive pg_node_attr(query_jumble_ignore);
    /* has INSERT/UPDATE/DELETE in WITH */
    bool        hasModifyingCTE pg_node_attr(query_jumble_ignore);
    /* FOR [KEY] UPDATE/SHARE was specified */
    bool        hasForUpdate pg_node_attr(query_jumble_ignore);
    /* rewriter has applied some RLS policy */
    bool        hasRowSecurity pg_node_attr(query_jumble_ignore);
    /* is a RETURN statement */
    bool        isReturn pg_node_attr(query_jumble_ignore);

    List       *cteList;        /* WITH list (of CommonTableExpr's) */

    List       *rtable;         /* list of range table entries */

    /*
     * list of RTEPermissionInfo nodes for the rtable entries having
     * perminfoindex > 0
     */
    List       *rteperminfos pg_node_attr(query_jumble_ignore);
    FromExpr   *jointree;       /* table join tree (FROM and WHERE clauses);
                                 * also USING clause for MERGE */

    List       *mergeActionList;    /* list of actions for MERGE (only) */
    /* whether to use outer join */
    bool        mergeUseOuterJoin pg_node_attr(query_jumble_ignore);

    List       *targetList;     /* target list (of TargetEntry) */

    /* OVERRIDING clause */
    OverridingKind override pg_node_attr(query_jumble_ignore);

    OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */

    List       *returningList;  /* return-values list (of TargetEntry) */

    List       *groupClause;    /* a list of SortGroupClause's */
    bool        groupDistinct;  /* is the group by clause distinct? */

    List       *groupingSets;   /* a list of GroupingSet's if present */

    Node       *havingQual;     /* qualifications applied to groups */

    List       *windowClause;   /* a list of WindowClause's */

    List       *distinctClause; /* a list of SortGroupClause's */

    List       *sortClause;     /* a list of SortGroupClause's */

    Node       *limitOffset;    /* # of result tuples to skip (int8 expr) */
    Node       *limitCount;     /* # of result tuples to return (int8 expr) */
    LimitOption limitOption;    /* limit type */

    List       *rowMarks;       /* a list of RowMarkClause's */

    Node       *setOperations;  /* set-operation tree if this is top level of
                                 * a UNION/INTERSECT/EXCEPT query */

    /*
     * A list of pg_constraint OIDs that the query depends on to be
     * semantically valid
     */
    List       *constraintDeps pg_node_attr(query_jumble_ignore);

    /* a list of WithCheckOption's (added during rewrite) */
    List       *withCheckOptions pg_node_attr(query_jumble_ignore);

    /*
     * The following two fields identify the portion of the source text string
     * containing this query.  They are typically only populated in top-level
     * Queries, not in sub-queries.  When not set, they might both be zero, or
     * both be -1 meaning "unknown".
     */
    /* start location, or -1 if unknown */
    int         stmt_location;
    /* length in bytes; 0 means "rest of string" */
    int         stmt_len pg_node_attr(query_jumble_ignore);
} Query;

Where to start?

Make sure you have a table created in the database you are running to be able to run sql queries against. Then attach GDB to the postgres process, to know the postgres process, run your postgres instance first then enter the following command:

testdb=# SELECT pg_backend_pid();
 pg_backend_pid
----------------
          17125
(1 row)

copy the process id and then open another terminal and run:

gdb -p 17125

Now we should be ready to debug.

Debugging planner function

One of the very important stages that a query goes through is the planner stage, it's the pre-execution phase in which postgres decide the optimum way to fetch data and which algorithm to use etc. We will inspect the planner function, but where can we find the planner function?, you can simply ask vscode to find it for you by type Ctrl + Shift + h (on windows), and search for planner( like the following:

we found it!, now open the terminal where you ran gdb, and create a breakpoint at the planner function:

b src/backend/optimizer/plan/planner.c:planner

you should get an output similar to:

Breakpoint 1 at 0x560af507bcc0: file planner.c, line 269.

which verifies the breakpoint is created.

now let's open postgres instance terminal and run a very simple SELECT query (but make sure you already created the users table to run the same command I'm running, but any other table should work):

testdb=# SELECT * FROM users;

Go back to GDB terminal and press c and then enter, this will get us to where we created the breakpoint (at the planner function), you should get an output similar to the following:

Breakpoint 1, planner (parse=0x560af6cef470, query_string=0x560af6cee618 "SELECT * from users;", 
    cursorOptions=256, boundParams=0x0) at planner.c:269
warning: Source file is more recent than executable.
269      * so you'd better copy that data structure if you want to plan more than once.
(gdb)

You can notice that the argument parse is a pointer to a ** Query** which is what we want to inspect, print it out to make sure:

(gdb) print parse
$1 = (Query *) 0x560af6cef470

mmmm right!, now we want to check the commandType variable, and according to the query we ran it should be SELECT:

(gdb) print parse->commandType
$2 = CMD_SELECT

Everything is as expected. Now we should have gained a little experience with debugging postgers, and specially the Query struct, which you can go and play around with gdb to check other properties in it like the targetList variable.

I hope you found this blog helpful somehow.

Comprehensive Guide On Using (Count) Aggregation Function In Apache AGE

Ahmed Hisham — Fri, 05 May 2023 02:34:56 +0000

This blog assumes you already familiar with Apache AGE extension which is an extension for PostgreSQL that adds graph database functionalities to relational database. I chose Count function since it is a bit tricky to a lot of developers, so by the end of this blog you should feel comfortable using it. We will start out with a simple example, then moving to a hard one.

Setting up a database

First we need to create a test database test_db and create age extension for it:

postgres=# CREATE DATABASE test_db;
CREATE DATABASE
postgres=# \c test_db

test_db=# CREATE EXTENSION age;
CREATE EXTENSION
test_db=# LOAD 'age';
LOAD

data setup from docs:

SET search_path TO ag_catalog;

SELECT create_graph('graph_name');

SELECT * FROM cypher('graph_name', $$
    CREATE (a:Person {name: 'A', age: 13}),
    (b:Person {name: 'B', age: 33, eyes: "blue"}),
    (c:Person {name: 'C', age: 44, eyes: "blue"}),
    (d1:Person {name: 'D', eyes: "brown"}),
    (d2:Person {name: 'D'}),
    (a)-[:KNOWS]->(b),
    (a)-[:KNOWS]->(c),
    (a)-[:KNOWS]->(d1),
    (b)-[:KNOWS]->(d2),
    (c)-[:KNOWS]->(d2)
$$) as (a agtype);

Simple example

Now let's start with a simple example to show the basic use of Count function:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    RETURN count(*)
$$) as (total agtype);

In this query MATCH will fetch all vertices in the graph, and then by the RETURN count(*) part we inform the query to return the count of all vertices (***** means all here). You should already be familiar with that, the result we will get in our example will be:

 total
-------
 5
(1 row)

Harder example

Let's move to more serious example, don't be shocked with what you will see, we will explain everything, these are the required data setup:

SELECT * FROM cypher('graph_name', $$
CREATE (:L {a: 1, b: 2, c: 3}),
       (:L {a: 2, b: 3, c: 1}),
       (:L {a: 3, b: 1, c: 2})
$$) as (a agtype);

Now let's run the following query (we will explain it down below):

SELECT * FROM cypher('graph_name', $$
    MATCH (x:L)
    RETURN (x.a + x.b + x.c) + count(*) + count(*), x.a + x.b + x.c
$$) as (count agtype, key agtype);

you should get the following output:

 count | key
-------+-----
 12    | 6
(1 row)

Let's explain What happened here, first you need to understand the concept of grouping key, grouping key is what count function will exactly count, x.a + x.b + x.c is the grouping key in this example. Let's see how see how this works in detail

RETURN (x.a + x.b + x.c) + count(*) + count(*), x.a + x.b + x.c

as x.a + x.b + x.c is the grouping key, we need to find how many groups it makes, according to the vertices we created above
(:L {a: 1, b: 2, c: 3}),
(:L {a: 2, b: 3, c: 1}),
(:L {a: 3, b: 1, c: 2})

in all cases (1 + 2 + 3), (2 + 3 + 1) or (3 + 1 + 2) they all equal 6 so they fall into the same group, which is one group: 6, now count function will count how many 6 has showed up, it finds that 6 appeared 3 times, so count(*)=3. So if we computed left column we will get 12:
(x.a + x.b + x.c) + count(*) + count(*) =
(1 + 2 + 3) + 3 + 3 = 12

that's why we got the output:

 count | key
-------+-----
 12    | 6
(1 row)

the right part represent the groups that can be formed which will be only one group (6), and the left part yields to 12 for that group!.

congratulations!, by now you should have much deeper understanding of how count function with grouping keys.

How Does PostgreSQL Estimate The Cost Of A Query With The EXPLAIN Command

Ahmed Hisham — Thu, 04 May 2023 16:15:00 +0000

In this blog we will explore how PostgreSQL EXPLAIN command estimates the cost of a query, we will start by setting up a test database, then we will run a sequential scan command and estimate it manually and then compare it with EXPLAIN result.

Setting up a database

First we will create a test database and insert some values into it. Open a postgres instance and create a testdb database:

postgres=# CREATE DATABASE testdb;
postgres=# \c testdb

Create a table with two columns as follows:

testdb=# CREATE TABLE test_tbl (id INTEGER PRIMARY KEY, order_num INTEGER);

Now we want to insert some random rows into the table:

testdb=# INSERT INTO test_tbl SELECT generate_series(1, 10000), generate_series(1,10000);
INSERT 0 10000

To make sure everything is correct, run:

testdb=# SELECT count(*) FROM test_tbl;
count
-------
 10000
(1 row)

Estimating The Cost Of A Sequential Scan Query

A sequential scan query means that query will need to scan the entire table, going over every single tuple (row). We will run this simple sequential scan query to make our tests on it SELECT * FROM test_tbl, we will start by printing out the cost of this query using EXPLAIN command:

testdb=# EXPLAIN SELECT * FROM test_tbl;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on test_tbl  (cost=0.00..145.00 rows=10000 width=8)
(1 row)

You can notice the estimated cost has two numbers 0.00 and 145.00, yes they are two numbers, they represent the start-up and the total costs respectively.

In PostgreSQL, there are three kinds of costs: start-up, run and total. The total cost is the sum of start-up and run costs.

The start-up cost is the cost expended before exactly fetching the first tuple in the table.
The run cost is the cost to fetch all tuples.
The total cost as described is the sum of the costs of both start-up and run costs.

Now let's compute the cost ourselves according to the website here the formula is:

Total cost = (cpu run cost) + (disk run cost)
Total cost = (cpu_tuple_cost + cpu_operator_cost) * N_tuples + seq_page_cost * N_pages

Don't be shocked it is simple, let's understand each variable alone:

cpu_tuple_cost: This is the cost of processing each tuple (row) in a query result. The default value is 0.01.
cpu_operator_cost: This is the cost of processing each operator or function call in a query. The default value is 0.0025.
seq_page_cost: This is the cost of reading a page from disk sequentially. The default value is 1.0.
N_tuples: This is the number of tuples (rows) returned by the query.
N_pages: This is the number of pages read from disk during the query.

All variables are known except for N_tuples and N_pages which we can by running the following command:

testdb=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'test_tbl';
 relpages | reltuples
----------+-----------
       45 |     10000
(1 row)

As you can notice, the number of tuples is 10000 which we inserted, and the number of pages is 45 (a page is a block of data that is read from or written to disk as a unit, it is a section inside the table file). Now we have everything we need to use the equation:
Total cost = (0.01 + 0.0025) * 10000 + 1.0 * 45 = 102.5 + 45 = 147.5
The result is 147.5!, which is almost the same as what the EXPLAIN command estimated.

I hope that was helpful.

How To Debug Apache AGE using GDB

Ahmed Hisham — Wed, 03 May 2023 10:28:36 +0000

This blog post assumes you have already installed postgresql (with debugging flag enabled) and Apache AGE extension, otherwise you can follow this blog for installation process.

Loading Apache AGE

1- first we need to run a postgres instance by using psql but make sure to run the server first, you can do that by the following two commands:

pg_ctl start -l log
psql postgres

2- Load age and set the search path to ag_catalog:

LOAD age;
SET search_path = ag_catalog, "$user", public;

3- Now you should be ready to debug AGE, but first we need to know the process of where the backend process of postgres is running, so that we can attach it to the gdb process, we can know the process by the following commands:

SELECT pg_backend_pid();

this should show you an output similar to this:

postgres=# select pg_backend_pid();
 pg_backend_pid
----------------
            439
(1 row)

4- Now open another terminal and cd to where age project exits on your machine, and run gdb to attach it to that process:

yourmachine:/mnt/d/bitnine_work/age_project/age$ gdb -p 439

you should get an output similar to this:

GNU gdb (Ubuntu 9.2-0ubuntu1~20.04.1) 9.2
Copyright (C) 2020 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Type "show copying" and "show warranty" for details.
This GDB was configured as "x86_64-linux-gnu".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Find the GDB manual and other documentation resources online at:
    <http://www.gnu.org/software/gdb/documentation/>.

For help, type "help".
Type "apropos word" to search for commands related to "word".
Attaching to process 439
Reading symbols from /usr/local/pgsql-12/bin/postgres...
Reading symbols from /lib/x86_64-linux-gnu/libpthread.so.0...
Reading symbols from /usr/lib/debug/.build-id/7b/4536f41cdaa5888408e82d0836e33dcf436466.debug...
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
Reading symbols from /lib/x86_64-linux-gnu/librt.so.1...
Reading symbols from /usr/lib/debug/.build-id/ce/016c975d94bc4770ed8c62d45dea6b71405a2c.debug...
Reading symbols from /lib/x86_64-linux-gnu/libdl.so.2...
Reading symbols from /usr/lib/debug/.build-id/c0/f40155b3f8bf8c494fa800f9ab197ebe20ed6e.debug...
Reading symbols from /lib/x86_64-linux-gnu/libm.so.6...
Reading symbols from /usr/lib/debug/.build-id/fe/91b4090ea04c1559ff71dd9290062776618891.debug...
Reading symbols from /lib/x86_64-linux-gnu/libc.so.6...
Reading symbols from /usr/lib/debug/.build-id/18/78e6b475720c7c51969e69ab2d276fae6d1dee.debug...
--Type <RET> for more, q to quit, c to continue without paging--

5- Let's set a breakpoint to a function that exists in age/src/backend/parser/cypher_analyze.c file, which is analyze_cypher function:

(gdb) b src/backend/parser/cypher_analyze.c:analyze_cypher
Breakpoint 1 at 0x7f83c3da8620: file src/backend/parser/cypher_analyze.c, line 624.

6- Now create a cypher query for any graphs you created before, or create a test graph, insert some values into it, and then fetch some data, for example:

postgres=# SELECT * FROM cypher('test', $$
MATCH (v:person)
RETURN v.name
$$) as (v agtype);

after running that you will notice that nothing has returned from postgres terminal, that's because gdb interrupted the process to enable debugging:

7- In gdb terminal enter the following:

this will run the code till the breakpoint we set, then enter:

layout next

you will notice that we reached to analyze_cypher function.

We are done!, by using the following basic commands you can print out variables to check their values, or continue the code till the next breakpoint:

b for breakpoint, (b )
c for continue - continues to the next breakpoint
n for next line
s for step into
p for print, (p *) for pointers
bt for call stack
d for delete all breakpoints
list for context
q for quit.

Hope this was helpful.

Explaining How And Where PostgreSQL Stores Databases And Tables With Example.

Ahmed Hisham — Sun, 16 Apr 2023 17:55:17 +0000

By the end of this blog you will understand the basics of PostgreSQL architecture and where it store its data especially databases and tables, we will walk through a live example in which we will create a database and a table, and then navigate to where they are stored.

Postgres Installation:

To follow this blog you should have PostgreSQL already installed on your machine, you can install it from here (but pay attention to where you install PostgreSQL, the default option will be on C:\Program Files\PostgreSQL\version, don't forget to add the path of /bin folder to your environment variables to be able to access PostgreSQL from your terminal using psql.

Creating a database with a table:

We will first create a database called test_db with a table called users, we will make it as simple as possible:

postgres=# CREATE DATABASE test_db;
CREATE DATABASE

postgres=# \c test_db

test_db=# CREATE TABLE users (id SERIAL PRIMARY KEY, username TEXT);
CREATE TABLE

Then insert a row (tuple) into users table:

test_db=# INSERT INTO users (username) VALUES ('ahmed hisham');
INSERT 0 1

A database cluster:

PostgreSQL runs on a single host and manages one database cluster (a database cluster is a collection of databases), as you can see below, each database contains its tables.

In PostgreSQL all objects including (databases and tables which are refered to as objects) are managed by a respective object identifiers (OIDs), this is a unique value for each object. Each database is a subdirectory named by its oid. PostgreSQL stores its databases in the base folder PostgreSQL/YOUR_VERSION/data/base. The parent directory (data) or PGDATA has the following architecture:

Each database is a subdirectory in /base folder and it is named by its oid as described above, tables are files stored inside database subdirectory, and named by its oid (table oid) as well, so our test_db is inside /base directory, and users table is a file inside test_db subdirectory.

Our mission is to move to users table file and check if the row we inserted above to users table is really stored there. But wait, all databases subdirectories are named by their oid and we don't know the oid of test_db as if you ls inside base subdirectory you will get the following output (list of database oids):

ahmeduser@ahmed:/usr/local/pgsql-12/bin/data/base$ ls
1  12674  12675  32768  40960  49152  pgsql_tmp

So to get test_db subdirectory oid we need to know its oid, to do that run the following command:

test_db=# SELECT datname, oid FROM pg_database where datname='test_db';
 datname |  oid
---------+-------
 test_db | 49152
(1 row)

The above command go to pg_database which is a system catalog that stores information about databases and their corresponding oids. We need to do the same for users table and get its oid:

test_db=# SELECT relname, oid FROM pg_class where relname='users';
 relname |  oid
---------+-------
 users   | 57346
(1 row)

pg_class is the same as pg_database but for tables.

Now let's navigate to test_db subdirectory using its oid 49152:

ahmeduser@ahmed:/usr/local/pgsql-12/bin/data/base$ cd 49152
ahmeduser@ahmed:/usr/local/pgsql-12/bin/data/base/49152$

Then open users table file using any editor you like, I will use vim in my linux terminal:

ahmeduser@ahmed:/usr/local/pgsql-12/bin/data/base/49152$ vim 57346

Output:

You should get a similar result, you can notice that the username value I inserted was ahmed hisham which appears at the bottom of the file (note: these characters @^ represent free spaces, free spaces are necessary for PostgreSQL to manage how to store and scan data inside table files).

We are done!, you should by now have an idea of where and how PostgreSQL stores databases and tables, I hope that was useful, feel free to ask any question.

Explaining In Detail How To Install PostgreSQL From Source Code On Windows With a Full Understanding.

Ahmed Hisham — Fri, 07 Apr 2023 21:01:28 +0000

In this blog I will provide a full understanding of the steps of installing PostgreSQL from the source code and how to get it to work properly, also the installation process should make postgres ready to LOAD Apache AGE extension, and I provided resources at the end of the blog to know how to install Apache AGE.

What is Postgres?

PostgreSQL is a powerful open-source relational database (RDB) management system that uses SQL as its main query language. PostgreSQL proved to be highly reliable, stable, scalable, and secure. As it is a relational database, it provides ACID properties which stands for (Atomicity, Consistency, Isolation, and Durability).

How To Install Postgres From Source Code:

1- Requirements before Installation

To work with Apache AGE Extension you need to install Linux subsystem on your windows as Apache AGE is not supporting windows installation yet. So you will need to enable Windows Subsystem For Linux on your windows to able to install wsl, you can do that by searching for Turn Windows features on or off on the windows search bar down below, then scroll down till you find Windows Subsystem for Linux and check it. Now restart your machine and you should be ready to go.

To install wsl (this blog assumes you have Windows 10 version 2004 or higher), you will only need to open command prompt as an administrator and run the following command:

wsl --install

This command will download Ubuntu as the distro by default and for more information you can check here, also make sure to download linux terminal for the right distro from Windows Store, I'm using Ubuntu 20.04.5 LTS for this tutorial:

2- Installation:

Installing PostgreSQL from source code make it more customisable to fit your application needs and it also make it easier to switch between different versions, In this blog we will install PostgreSQL 12 as it is the latest version that Apache AGE extension supports for now (very soon it will support PostgreSQL 13, you can always check here), but the installation process of future supported versions will be the same.

1- Open your linux terminal as an administrator and run the following command (this will ensure you have the required git and PostgreSQL dependencies for installation):

sudo apt install git libreadline-dev zlib1g-dev bison flex build-essential

2- Before cloning the repository, to access the drives of your machine on terminal you can simply run:

cd /mnt/c

3- this will change the current directory to the root directory of C drive. Now create a directory where you will install PostgreSQL, we will name it (age_project) and then clone the PostgreSQL repo:

mkdir age_project
cd age_project
git clone https://git.postgresql.org/git/postgresql.git

4- Access postgresql directory and switch to PostgreSQL 12

cd postgresql
git checkout REL_12_STABLE

5- Now if you ls in the directory, you will find a configure script which we should be run to configure the source code for installation:

./configure --prefix=/usr/local/pgsql-12 --enable-debug

the --prefix option specifies the installation directory for PostgreSQL 12 as /usr/local/pgsql-12, /usr/local/ is where locally compiled applications install to by default, so when we compile the code it knows where to send the compiled files, note: we didn't create that directory yet, we just informed postgresql to store the executable files there after compilation (specifically after running make install command later), so we will need to create the directory in a next step. The --enable-debug is necessary to allow debugging for source code.

6- Run the make command:

make

7- Now the executable files are in the same directory but we still need to install them where we specified above, to /usr/local/pgsql-12 directory, to do that run the following command to create the folder first, change the owner and then install (make sure to replace [user] with your user name):

sudo mkdir /usr/local/pgsql-12
sudo chown [user] /usr/local/pgsql-12
make install

8- After installation we are almost done, the remaining step is to add some environment variables to be able to access the executable files in the terminal, we do this by the following command:

export PATH=/usr/local/pgsql-12/bin/:$PATH
export PGDATA=/usr/local/pgsql-12/bin/data

we exported /usr/local/pgsql-12/bin/ as it contains the executable files of PostgreSQL 12 such as pg_ctl which we will use to run postgres server.
we exported the data directory /usr/local/pgsql-12/bin/data because it is where postgres stores the data like databases data, each database created has a subdirectory there.

9- Finally it is time to work with postgres, we will first initiate the cluster (A database cluster is a collection of databases), we simply need to run initdb in the terminal (which is one of the files installed in /usr/local/pgsql-12/bin/:

initdb

10- start a server by the following command:

pg_ctl start -l logfile

in this case we named the log file logfile which will be created and it contains information about the server activity.

11- Now start postgres by running:

psql postgres

12- Congrats you are done!, you can exit psql by \q and stop the server by running:

pg_ctl stop -l logfile

With having PostgreSQL running you can successfully LOAD Apache AGE extension, you can clone it from github repo here, and for more information about how to deal with it you can visit https://age.apache.org/.

DEV Community: Ahmed Hisham

Here's How To Create Your First PostgrSQL Extension Part 4

Here's How To Create Your First PostgrSQL Extension Part 3

Starting by add_two_numbers.control file:

Creating add_two_numbers.c file:

Here's How To Create Your First PostgrSQL Extension Part 2

Here's How To Create Your First PostgrSQL Extension Part 1

Prerequisites

Creating an extension!:

When To Use Graph Database Model Over Relational Database Models

When to use a Graph model?

A Look To A Query Inside Postgres Source Code

Postgres Installation

Understanding what we are going to debug

Where to start?

Debugging planner function

Comprehensive Guide On Using (Count) Aggregation Function In Apache AGE

Setting up a database

Simple example

Harder example

How Does PostgreSQL Estimate The Cost Of A Query With The EXPLAIN Command

Setting up a database

Estimating The Cost Of A Sequential Scan Query

How To Debug Apache AGE using GDB

Loading Apache AGE

Explaining How And Where PostgreSQL Stores Databases And Tables With Example.

Postgres Installation:

Creating a database with a table:

A database cluster:

Explaining In Detail How To Install PostgreSQL From Source Code On Windows With a Full Understanding.

What is Postgres?

How To Install Postgres From Source Code:

1- Requirements before Installation

2- Installation:

Starting by `add_two_numbers.control` file:

Creating `add_two_numbers.c` file: