DEV Community: Abhi-Kmr2046

WAL buffer and WAL segment file

Abhi-Kmr2046 — Thu, 31 Aug 2023 16:50:03 +0000

WAL stands for Write-Ahead Logging. It is a standard method for ensuring data integrity. WAL data is a description of changes made to the actual data, and is also known as metadata.

WAL’s central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to persistent storage. This ensures that in the event of any error, we will be able to recover the database using the log. Any changes that have not been applied to the data pages can be redone from the log records. This is roll-forward recovery, also known as REDO.
WAL buffers and WAL segment files are two components of the WAL system in PostgreSQL.

WAL buffers are in-memory buffers that temporarily hold transactions until they are written to disk. They are used to speed up write operations by allowing transactions to be written to disk in batches rather than one at a time. If the WAL buffer becomes full, the contents of the buffer are dumped into a WAL segment file.

WAL segment files are on-disk files that store the contents of the WAL buffer when it becomes full. They are used to ensure that all transactions are written to disk before being acknowledged as complete. When a new transaction is started, it is written to a new WAL segment file.

How to write PostgreSQL extensions in C++

Abhi-Kmr2046 — Fri, 30 Jun 2023 16:41:14 +0000

Creating a completely new extension for PostgreSQL involves several steps.
Here we will create a basic extension with that adds SQL function to calculate factorial of a given integer. We will write the extension code in c++.

Create the Factorial Extension

Setp 1:
Create factorial_extension folder in share directory in the postgres installation folder.
Set up the folder structure. This folder contains file factorial_extension.control, factorial_extension--1.0.sql, factorial_extension.cpp and MakeFile.

factorial_extension/
    |
    +-- factorial_extension.control
    |
    +-- factorial_extension.cpp
    |
    +-- factorial_extension--1.0.sql
    |
    +-- Makefile

Setp 2:
Write code for factorial in C++

#include <postgres.h>
#include <fmgr.h>
#include <utils/numeric.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern "C" {
    PG_FUNCTION_INFO_V1(factorial);

    Datum factorial(PG_FUNCTION_ARGS) {
        int32 arg = PG_GETARG_INT32(0);

        if (arg < 0)
            ereport(ERROR,
                    (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                     errmsg("Factorial input must be a non-negative integer")));

        int64 result = 1;
        for (int i = 2; i <= arg; ++i)
            result *= i;

        PG_RETURN_INT64(result);
    }
}

Setp 3:
Write the control file. The control file for an extension, also known as the .control file, is a metadata file that provides information about the extension. It contains details such as the name, version, author, module path, dependencies, and other important attributes of the extension.

# factorial_extension.control
comment = 'Extension to calculate factorial'
default_version = '1.0'
module_pathname = '$libdir/factorial_extension'
relocatable = false

Step 4:
Write the SQL script (factorial_extension--1.0.sql):

-- factorial_extension--1.0.sql

-- Create the extension schema
CREATE SCHEMA factorial_extension;

-- Create the SQL function
CREATE OR REPLACE FUNCTION factorial_extension.factorial(integer)
RETURNS bigint AS 'factorial_extension', 'factorial'
LANGUAGE C STRICT;

-- Grant execute permission to public (change to appropriate roles if needed)
GRANT EXECUTE ON FUNCTION factorial_extension.factorial(integer) TO PUBLIC;

Step 4:
Write the MakeFile:

MODULES = factorial_extension
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Run the Extension

Step 1: Start or restart the PostgreSQL server
Step 2: Connect to the database where you want to install the extension
Step 3: To install the extension run the following command.

CREATE EXTENSION factorial_extension;

Use the Extension

SELECT factorial_extension.factorial(5);

How to create extensions in PostgreSQL?

Abhi-Kmr2046 — Fri, 30 Jun 2023 16:28:11 +0000

Create the Factorial Extension

Setp 1:
Create factorial_extension folder in share directory in the postgres installation folder.
Set up the folder structure. This folder contains file factorial_extension.control and factorial_extension--1.0.sql

factorial_extension/
    |
    +-- factorial_extension.control
    |
    +-- factorial_extension--1.0.sql

Setp 2:
Write the control file. The control file for an extension, also known as the .control file, is a metadata file that provides information about the extension. It contains details such as the name, version, author, module path, dependencies, and other important attributes of the extension.

# factorial_extension.control
comment = 'Extension to calculate factorial'
default_version = '1.0'
module_pathname = '$libdir/factorial_extension'
relocatable = false

Step 3:
Write the SQL script (factorial_extension--1.0.sql):

-- factorial_extension--1.0.sql

-- Create a new schema for the extension
CREATE SCHEMA factorial_extension;

-- Create the factorial function
CREATE OR REPLACE FUNCTION factorial_extension.factorial(n INTEGER)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT := 1;
BEGIN
    IF n < 0 THEN
        RAISE EXCEPTION 'Factorial is not defined for negative numbers';
    ELSIF n > 1 THEN
        FOR i IN 2..n LOOP
            result := result * i;
        END LOOP;
    END IF;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Grant execute permission to public (change to appropriate roles if needed)
GRANT EXECUTE ON FUNCTION factorial_extension.factorial(INTEGER) TO PUBLIC;

Run the Extension

Step 1: Start or restart the PostgreSQL server
Step 2: Connect to the database where you want to install the extension
Step 3: TO install the extension run the following command.

CREATE EXTENSION factorial_extension;

Use the Extension

SELECT factorial_extension.factorial(5);

What are Extension in PostgreSQL???

Abhi-Kmr2046 — Fri, 30 Jun 2023 15:47:02 +0000

Extensions in PostgreSQL are modular add-ons that enhance the functionality of the core database system. They provide a way to extend the capabilities of PostgreSQL by adding new features, data types, functions, operators, and procedural languages.

The functionalities can range from simple utilities to complex features like full-text search, geographic information systems (GIS), data compression, auditing, and more. By installing the relevant extensions, users can customize their PostgreSQL installation to suit their specific needs.

Extensions can introduce new data types to PostgreSQL. For example, the hstore extension adds support for a key-value store data type, which can be useful for storing dynamic properties associated with database records. Similarly, the uuid-ossp extension provides a data type for universally unique identifiers (UUIDs). These additional data types enhance the flexibility and expressiveness of the database.

PostgreSQL supports multiple procedural languages, such as PL/pgSQL, PL/Python, PL/Perl, PL/Java, and more. Extensions can introduce new procedural languages, allowing developers to write database functions, triggers, and stored procedures in their language of choice. This flexibility enables developers to leverage their existing skills and integrate custom logic seamlessly within the database.

There are many such extensions like PostGIS for spatial and geographic data, pgcrypto provides cryptographic functions, citext extension provides a case-insensitive text data type in PostgreSQL.

Apache AGE is such an extension for PostgreSQL that enables graph database functionality within the PostgreSQL. It allows users to store, query, and manipulate graph data using familiar SQL syntax. With this extension, users can model complex relationships, traverse graphs efficiently, and perform advanced graph queries using standard SQL.

Creating New Function in Apache AGE

Abhi-Kmr2046 — Sun, 28 May 2023 18:49:50 +0000

We can add new functions in Apache age. To do this we will have to modify a few files and compile the Apache AGE code.
To demonstrate this we'll create a gcd function which is not available in apache age.

`age--1.1.0.sql`

CREATE FUNCTION ag_catalog.age_gcd(agtype, agtype)
RETURNS agtype
LANGUAGE c
PARALLEL SAFE
AS 'MODULE_PATHNAME';

This declares the function we are creating. Here we have two arguments of type agetype because apache age only supports agetype as arguments.

`src/backend/utils/adt/agtype.c`

We'll add our function in the above file. Just add the given code anywhere in the file. This is syntax of adding a function to apache age.


PG_FUNCTION_INFO_V1(age_gcd);

Datum age_gcd(PG_FUNCTION_ARGS)
{
    agtype *agt_arg0 = AG_GET_ARG_AGTYPE_P(0);
    agtype *agt_arg1 = AG_GET_ARG_AGTYPE_P(1);
    agtype_value *arg0;
    agtype_value *arg1;
    agtype_value agtv_result;

    /* gcd supports agtype integer as input */
    arg0 = get_ith_agtype_value_from_container(&agt_arg0->root, 0);
    arg1 = get_ith_agtype_value_from_container(&agt_arg1->root, 0);

    /* only integers are allowed */
    if (arg0->type != AGTV_INTEGER || arg1->type != AGTV_INTEGER )
        ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                        errmsg("arguments must resolve to an integer")));

    /* check for agtype null */
    if (arg0->type == AGTV_NULL)
        PG_RETURN_POINTER(agt_arg1);
    if (arg1->type == AGTV_NULL)
        PG_RETURN_POINTER(agt_arg0);

    agtv_result.val.numeric = DatumGetNumeric(
                DirectFunctionCall2(numeric_gcd,
                                    DirectFunctionCall1(int8_numeric,
                                                        Int64GetDatum(arg0->val.int_value)),
                                    DirectFunctionCall1(int8_numeric,
                                                        Int64GetDatum(arg1->val.int_value))));
    agtv_result.type = AGTV_NUMERIC;

    PG_RETURN_POINTER(agtype_value_to_agtype(&agtv_result));
}

Here to calculate gcd, numeric_gcd is passed in the DirectFunctionCall2. It is a postgress function used to calculate gcd. Here we can directly utilize that to make our own gcd function of Apache AGE.

Compilation

make uninstall
make clean
make PG_CONFIG=/home/abhis/age_installation/postgresql-14.7/bin/pg_config install

Loading age extension

Start Server:

bin/pg_ctl -D demo -l logfile start

Run psql

bin/psql <dbname>

After the code is successfully compiled and psql command line is started we need to load the age extension.

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

This sometimes might not work. In that case, run the command given below and rerun the command to load extension.

DROP EXTENSION age CASCADE;

Query

We can use any of the below syntax to run the query.

SELECT * FROM age_gcd('12', '9');

SELECT * FROM cypher('expr', $$
    RETURN gcd(15, 25)
$$) as (result agtype);

Graph Types in NetworkX

Abhi-Kmr2046 — Sun, 28 May 2023 17:35:58 +0000

NetworkX is a Python package for the creation, manipulation, and study of the structure, dynamics, and functions of complex networks. It provides data structures and methods for storing graphs.
Graphs are basically of four types:

Graph: This a undirected graph, with possible self-loops but no parallel edges.

G = nx.Graph()

DiGraph: This a directed graph, with possible self-loops but no parallel edges.

G = nx.DiGraph()

MultiGraph: This a undirected graph, with possible self-loops and also parallel edges.

G = nx.MultiGraph()

MultiDiGraph: This a directed graph, with possible self-loops and also parallel edges.

G = nx.MultiDiGraph()

Graph Views

For some algorithms it is convenient to temporarily morph a graph to exclude some nodes or edges. NetworkX provides a easier way to accomplish this using Graph Views. This is a view of a graph as SubGraph, Reverse, Directed or Undirected. The graph created using view is a read only graph object.

view = nx.reverse_view(G)

This creates a view of the original graph but edges reversed.

def filter_node(node):
    return node != 5

view = nx.subgraph_view(G, filter_node=filter_node)
view.nodes()

This creates a view of the original graph but it will not contain nodes which do satisfy the condition in filter_node.

How to Work with NetworkX

Abhi-Kmr2046 — Sat, 27 May 2023 19:33:22 +0000

NetworkX is a Python package for the creation, manipulation, and study of the structure, dynamics, and functions of complex networks. It provides software for complex networks, data structures for graphs, digraphs, and multigraphs, many standard graph algorithms, network structure and analysis measures, generators for classic graphs, random graphs, and synthetic networks.

To install networkX library run the command pip install networkx.

Graph Creation

import networkx as nx
G = nx.Graph()

This creates a empty graph. A graph in networkX is collection of nodes (vertices) along with identified pairs of nodes. A node can be any hashable object e.g., string, image, or another Graph etc.

Nodes

To add nodes we can use one of the following functions:

G.add_node(1) creates a single node
G.add_nodes_from([2, 3]) creates nodes 2, 3.

Edges

Similar to edges we also have two function to add edges to the graph.

G.add_edge(1,2) creates a edge between nodes 1 and 2.
G.add_nodes_from([(1, 2), (1, 3)]) creates edges between 1,2 and 1,3.

Visualize Graphs

NetworkX supports basic graph drawing using the matplotlib library.

import networkx as nx
import matplotlib.pyplot as plt
G = nx.complete_graph(6)
nx.draw(G)
plt.show()

This will first create a complete graph with 6 nodes.

plt.show() is important to show the graph image on screen.

Pattern Matching In Apache AGE

Abhi-Kmr2046 — Wed, 26 Apr 2023 16:25:49 +0000

Pattern matching is one of the most fundamental operation in a database system. It has numerous use cases like validating form submissions, performing search and replace operations, cleaning and standardizing data, and searching for specific patterns in a database.
Functions provided by Apache AGE for this are as follows:

Starts With

Performs case-sensitive prefix searching on strings.

SELECT * FROM cypher('graph_name', $$
    MATCH (v:Person)
    WHERE v.name STARTS WITH "J"
    RETURN v.name
$$) AS (names agtype);

Contains

Performs case-sensitive inclusion searching in strings.

SELECT * FROM cypher('graph_name', $$
    MATCH (v:Person)
    WHERE v.name CONTAINS "o"
    RETURN v.name
$$) AS (names agtype);

Ends With

Performs case-sensitive suffix searching on strings.

SELECT * FROM cypher('graph_name', $$
    MATCH (v:Person)
    WHERE v.name ENDS WITH "n"
    RETURN v.name
$$) AS (names agtype);

Regular Expressions

Regex or regular expressions are special sequences used to find or match patterns in strings. AGE supports the use of POSIX regular expressions using the =~ operator.
Example:

SELECT * FROM cypher('graph_name', $$
    MATCH (v:Person)
    WHERE v.name =~ '(?i)JoHn'
    RETURN v.name
$$) AS (names agtype);

We can provide any regular expression after ~= and it will search for the strings that matches that expression.

The special characters used in regex include the backslash , the caret ^, the dollar sign $, the period or dot ., the vertical bar or pipe symbol |, the question mark ?, the asterisk or star *, the plus sign +, the opening parenthesis (, the closing parenthesis ), the opening square bracket [, and the opening curly brace {. These special characters are often called “metacharacters”.

The backslash \ is used to escape metacharacters so they can be treated as literal characters. The caret ^ is used to match the start of a string. The dollar sign $ is used to match the end of a string. The period or dot . is used to match any character except newline. The vertical bar or pipe symbol | is used to match either/or. The question mark ? is used to match zero or one of the preceding character. The asterisk or star * is used to match zero or more of the preceding character. The plus sign + is used to match one or more of the preceding character. The opening parenthesis ( and closing parenthesis ) are used for grouping. The opening square bracket [ and closing square bracket ] are used for character classes. The opening curly brace { and closing curly brace } are used for quantifiers.

Entities in Apache AGE

Abhi-Kmr2046 — Wed, 26 Apr 2023 15:10:26 +0000

An entity has a unique, comparable identity which defines whether or not two entities are equal.

An entity is assigned a set of properties, each of which are uniquely identified in the set by the irrespective property keys.

Simple Entities

GraphId: Simple entities are assigned a unique graphid. A graphid is a unique composition of the entity’s label id and a unique sequence assigned to each label. Note that there will be overlap in ids when comparing entities from different graphs.
Labels: A label is an identifier that classifies vertices and edges into certain categories. Edges are required to have a label, but vertices do not. The names of labels between vertices and edges cannot overlap.
Properties: Both vertices and edges may have properties. Properties are attribute values, and each attribute name should be defined only as a string type.
Vertex: A vertex is the basic entity of the graph, with the unique attribute of being able to exist in and of itself.

# typecasting a map to vertex
SELECT *
FROM cypher('graph', $$
    WITH {id: 0, label: "label_name", properties: {i: 0}}::vertex as v
    RETURN v
$$) AS (v agtype);

Edge: An edge is an entity that encodes a directed connection between exactly two nodes, the source node and the target node. An edge is assigned exactly one edge type.

# typecasting a map to vertex
SELECT *
FROM cypher('graph', $$
    WITH {id: 2, start_id: 0, end_id: 1, label: "label_name", properties: {i: 0}}::edge as e
    RETURN e
$$) AS (e agtype);

Composite Entities

It is combination of multiple simple entities.

Path: A path is a series of alternating vertices and edges. A path must start with a vertex, and have at least one edge.

# typecasting a list to path
SELECT *
FROM cypher('graph', $$
    WITH [{id: 0, label: "label_name_1", properties: {i: 0}}::vertex,
            {id: 2, start_id: 0, end_id: 1, label: "edge_label", properties: {i: 0}}::edge,
           {id: 1, label: "label_name_2", properties: {}}::vertex
           ]::path as p
    RETURN p
$$) AS (p agtype);

Composite Datatypes in Apache AGE

Abhi-Kmr2046 — Wed, 26 Apr 2023 14:55:08 +0000

Composite Data Types is used to store multiple logically related items of Simple Data Types in Apache AGE. This includes Lists and Maps.

List

It is a collection of ordered items. This is similar to list in python.

# query to create a list
SELECT *
FROM cypher('graph', $$
    WITH [0, 1, 2, 3, 4, 5] as lis
    RETURN lis
$$) AS (lis agtype);

To access an individual element in the list we can use square brackets
like all most other programming languages like C/C++, Python.

# query to create a list
SELECT *
FROM cypher('graph', $$
    WITH [0, 1, 2, 3, 4, 5] as lis
    RETURN lis[2]
$$) AS (val agtype);

A list can store elements of all Simple Data Types as well as Composite type like list and map.
It can use negative index, which will start counting from the end and index ranges which returns the elements in the given range.

SELECT *
FROM cypher('graph', $$
    WITH [0, 1, 2, 3, 4, 5] as lis
    RETURN lis[0..3]
$$) AS (val agtype);

Map

A map is a collection of elements where each element is stored as a key-value pair. It is like maps in python.
It can use any data type (Simple or Composite) as key or as value, as shown in the example.

SELECT *
FROM cypher('graph', $$
    WITH {int_key: 1, float_key: 1.0, numeric_key: 1::numeric, bool_key: true, string_key: 'Value'} as m
    RETURN m
$$) AS (m agtype);

SELECT *
FROM cypher('graph', $$
    WITH {int_key: 1, float_key: 1.0, numeric_key: 1::numeric, bool_key: true, string_key: 'Value'} as m
    RETURN m
$$) AS (m agtype);

To access the value for a key we can use the dot (.) convention as in the example.

SELECT *
FROM cypher('graph', $$
    WITH {int_key: 1, float_key: 1.0, numeric_key: 1::numeric, bool_key: true, string_key: 'Value'} as m
    RETURN m.int_key
$$) AS (int_key agtype);

Apache AGE Datatypes

Abhi-Kmr2046 — Wed, 26 Apr 2023 14:32:45 +0000

Apache AGE uses its own custom data type called agetype, which is a super set of Json and a custom implementation of JsonB. It is the only data type returned by AGE.

Simple Data Types

Null: It represents missing or undefined values. Most expressions that take null as input will produce null. Null is not equal to null. Not knowing two values does not imply that they are the same value. So the expression null = null yields null and not true.
Integer: It stores whole numbers and it is a 64 bit field that stores values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. The smallint type is generally used only if disk space is at a premium. The bigint type is designed to be used when the range of the integer type is insufficient.

SELECT *
FROM cypher('graph', $$
    RETURN 1
$$) AS (result agtype);

Float: The data type float is an inexact, variable-precision numeric type, conforming to the IEEE-754 Standard. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error. It also supports values Infinity, -Infinity and Nan.

SELECT *
FROM cypher('graph', $$
    RETURN 1.0
$$) AS (result agtype);

Numeric: The type numeric can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with numeric values yield exact results where possible, e.g., addition, subtraction, multiplication. However, calculations on numeric values are very slow compared to the integer types, or to the floating-point type.

SELECT *
FROM cypher('graph', $$
    RETURN 1.0::numeric
$$) AS (result agtype);

Bool: AGE provides the standard Cypher type boolean. The boolean type can have several states: “true”, “false”, and a third state, “unknown”, which is represented by the Agtype null value. Boolean constants can be represented in Cypher queries by the keywords TRUE, FALSE, and NULL.

SELECT *
FROM cypher('graph', $$
    RETURN TRUE
$$) AS (result agtype);

String: Single (') quotes are used to identify a string. The output will uses double (") quotes. AGE type strings can also contain escape sequences, for example, \t, \b, \n.

SELECT *
FROM cypher('graph', $$
    RETURN 'Example String'
$$) AS (result agtype);

These are the Simple Data Types used in Apache AGE. There are also Composite Data Types which includes List and Maps.

Cost Estimation In PostgreSQL

Abhi-Kmr2046 — Fri, 31 Mar 2023 13:22:31 +0000

Cost estimation in PostgreSQL refers to the process by which the query planner estimates how much time a query will take to execute. The planner uses both a startup cost and a total cost for each operation to determine the cheapest option for executing a query. The costs are calculated using both constants and metadata about the contents of the database, often referred to as "statistics". This is not an absolute performance indicator of the final execution but gives a relative idea of execution of queries in different ways.
The costs of a query are estimated by functions defined in costsize.c and all the operations of executor have their corresponding cost function.
There are three types of cost in PostgreSQL which are as follows:

1. Start-up Cost: It is cost expended before the first tuple is fetched.
2. Run Cost: It is cost to fetch all the tuples.
3. Total Cost: It is sum of both start-up cost and run cost.

Explain command shows both the start-up cost and total cost in the operation. The command can be used like this.

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

Cost Estimation in Sequential Scan

The cost of the sequential scan is estimated by cost_seqscan() function. For this, startup cost is 0 and the run cost is defined using the following formula:

'run cost' = 'cpu run cost' + 'dis run cost'
           = ('cpu tuple cost' + 'cpu operator cost')xNtupule +'seq page cost'xNpage

Here 'seq page cost', 'cpu tuple cost' and 'cpu operator cost' are set in the postgresql.conf file, and the default values are 1.0, 0.01, and 0.0025, respectively.
For Ntuple = 10000 and Npage = 45 the run cost comes out to be 170 which is same as total cost.

Cost Estimation in Sort

The sort path is used for a variety of sorting tasks, including ORDER BY, preprocessing merge join procedures, and more. The cost sort() method calculates the cost of sorting.
If all of the tuples that need to be sorted can be stored in work mem, the quicksort algorithm is used otherwise tuples are broken into multiple parts in temporary files and merge sort algorithm is used.
The start-up cost of the sort path is the cost of sorting the target tuples. The run cost of the sort path is the cost of reading the sorted tuples.
The following formula is used for the cost estimation:

'start up cost' = C + 'comparison cost' x Nsort x log2(Nsort)
'run cost' = 'cpu operator cost' x Nsort

where C is the total cost of index scan, 'comparison cost' = 2 x 'cpu operator cost'.