Exploring the Architecture of PostgreSQL based IvorySQL

Migrating from Oracle to open-source databases has traditionally been a complex, costly endeavor. Enter IvorySQL: an open-source, drop-in replacement for PostgreSQL that bridges the gap. Built on the robust foundation of PostgreSQL, IvorySQL introduces full Oracle compatibility while preserving 100% of the standard PostgreSQL features.

In this post, we will take a deep dive into the IvorySQL codebase. We’ll explore the dual-parser architecture, the mechanics of dynamic compatibility switching, and the implementation of Oracle-specific syntax.

What is IvorySQL?

IvorySQL is an Apache 2.0 licensed project maintained by a global development team. Its core philosophy is seamless integration: it is not a fork that diverges, but a compatible extension that adds value.

Key Highlights

• Dual Compatibility Mode: Switch between standard PostgreSQL (pg) and Oracle (oracle) modes at runtime.
• PL/iSQL: A fully Oracle-compatible procedural language that supports Packages, Anonymous blocks, and Oracle-style syntax.
• Dual Parser System: Integrates a dedicated Oracle parser (ora_gram.y) alongside the standard Postgres parser (gram.y).
• NLS Support: Comprehensive National Language Support parameters.

1. Project Structure

To understand how IvorySQL achieves compatibility, we must look at how the code is organized. The project extends the standard PostgreSQL directory tree with distinct modules for Oracle logic.

Plaintext

/src/
├── backend/
│   ├── oracle_parser/   # The heart of the Oracle compatibility layer
│   │   ├── ora_gram.y   # Bison grammar for Oracle SQL
│   │   ├── ora_scan.l   # Flex lexer/scanner
│   │   └── liboracle_parser.c
│   ├── commands/        # SQL processing (includes packagecmds.c)
│   └── ...
├── pl/
│   ├── plisql/          # IvorySQL-specific PL/iSQL implementation
│   └── plpgsql/         # Standard PostgreSQL procedural language
├── oracle_test/         # Oracle-specific regression testing suite
└── contrib/
    └── ivorysql_ora/    # Extensions for Oracle compatibility functions

2. The Core Innovation: Dual Parser Architecture

The most significant architectural change in IvorySQL is the Dual-Parser System. Unlike standard PostgreSQL, which relies solely on one grammar, IvorySQL loads a second parser as a shared module.

How Parser Switching Works

IvorySQL utilizes a hook mechanism to intercept the parsing process. It defines a function pointer that determines which parser processes the incoming SQL string based on the current configuration.

The Hook Mechanism (C Implementation):

// src/backend/parser/parser.c

// Function pointers for parser hooks
typedef List *(*raw_parser_hook_type)(const char *str, RawParseMode mode);
extern raw_parser_hook_type sql_raw_parser;  // The currently active parser
extern raw_parser_hook_type ora_raw_parser;  // The dedicated Oracle parser

List *raw_parser(const char *str, RawParseMode mode)
{
    // The system calls whichever parser is currently pointed to by sql_raw_parser
    return (*sql_raw_parser)(str, mode);
}

Initialization & Runtime Switching

When the database initializes, the Oracle parser is loaded via _PG_init. The switching logic is controlled by the compatible_mode GUC (Grand Unified Configuration) parameter.

// src/backend/utils/misc/ivy_guc.c

void assign_compatible_mode(int newval, void *extra)
{
    if (newval == ORA_PARSER)
        sql_raw_parser = ora_raw_parser;      // Switch to Oracle Parser
    else if (newval == PG_PARSER)
        sql_raw_parser = standard_raw_parser; // Revert to Standard Postgres Parser
}

This allows a DBA to switch modes instantly without restarting the server:

SET compatible_mode = 'oracle';

---

3. Oracle-Specific Grammar Features

IvorySQL doesn’t just “simulate” Oracle; it parses it native-ly. The ora_gram.y file contains definitions for constructs that do not exist in standard PostgreSQL.

Package Support

One of the biggest hurdles in migration is Oracle Packages. IvorySQL supports the CREATE PACKAGE statement natively via its grammar nodes:

/* CreatePackageStmt Implementation */
CREATE opt_or_replace PACKAGE package_names
package_is_or_as package_src
{
    CreatePackageStmt *n = makeNode(CreatePackageStmt);
    n->pkgname = $4;
    n->bodysrc = $6;
    $$ = (Node *)n;
}

Data Types & Functions

The parser recognizes Oracle-specific data types and optimization hints, mapping them to internal IvorySQL structures.

Feature	Examples supported in IvorySQL
Data Types	VARCHAR2, NUMBER(p,s), BINARY_FLOAT, BINARY_DOUBLE, RAW, LONG_RAW See IvorySQL built-in data types and built-in functions
Function Hints	DETERMINISTIC, PIPELINED, AUTHID DEFINER
Anonymous Blocks	BEGIN ... END; or DECLARE ... BEGIN ... END;

Multi-Token Lookahead

The Oracle scanner (ora_scan.l) is smart enough to handle multi-word tokens that are common in Oracle PL/SQL but rare in Postgres.

Example: Combining LONG and RAW into a single token type:

case LONG_P:
    if (next_token == RAW_P) {
        cur_token = LONG_RAW;
    }
    break;

---

4. PL/iSQL and Body Capture

A major challenge in parsing Oracle PL/SQL is that the function body acts differently than in Postgres. In standard Postgres, function bodies are often string constants (dollar-quoted $$). In Oracle, they are part of the syntax stream.

The Solution: Body Capture

Instead of parsing the procedural logic immediately within the SQL parser, IvorySQL “captures” the body as a string by counting block levels (BEGIN/END).

// Logic to capture PL/SQL body as a string
while (cur_token != 0) {
    if (cur_token == BEGIN_P) blocklevel++;
    else if (cur_token == END_P) blocklevel--;
    
    // Continue scanning until the block balances out
    cur_token = internal_yylex(...);
}
// Return the captured string to PL/iSQL for procedural processing
lvalp->core_yystype.str = pstrdup(scanbuf + beginpos);

This elegant approach allows the SQL parser to hand off the complex procedural logic to the dedicated PL/iSQL engine.

---

5. Configuration & Testing

Key Configuration Parameters

IvorySQL introduces several parameters to fine-tune compatibility:

• compatible_mode: Controls the parser mode (pg or oracle).
• identifier_case_switch: Manages how unquoted identifiers are case-converted.
• enable_emptystring_to_NULL: Implements the Oracle behavior where '' equals NULL.
• default_with_rowids: Enables ROWID support for tables.

How to Test

The project includes a robust testing suite. You can run standard PostgreSQL tests alongside IvorySQL specific tests:

1. Standard Regression: Standard PG tests.
2. Oracle Compatibility Check: make oracle-check
3. CI/CD: The GitHub repository maintains workflows for both compatibility modes.

---

Conclusion

IvorySQL represents a sophisticated approach to database compatibility. By operating at the parser level and integrating deeply with the PostgreSQL backend, it offers a robust solution for organizations looking to modernize their database infrastructure without rewriting their applications.

Ready to get started?

• Visit the Docs: docs.ivorysq.org
• View the Code: IvorySQL on GitHub