pg-dev-claude-code

Claude Code best practices for PostgreSQL 17+ kernel and extension development.

A comprehensive configuration collection providing specialized agents, rules, skills, and commands tailored for PostgreSQL development workflows.

Core Principles

1. Extension First

Never modify PostgreSQL kernel source code unless absolutely necessary.

Requirement → Extension? → Hook? → FDW? → Custom Scan? → Kernel Modification (requires confirmation)

When kernel modification is truly required, a confirmation table must be generated documenting:

Why kernel modification is required
Why extension approach is not possible
Risk assessment (High/Medium/Low)

2. Test Coverage Requirements

Code Type	Minimum Coverage
Kernel Modifications	100%
Extension Code	90%
Utility Functions	95%

3. Security First

All SQL must use parameterized queries (SPI_execute_with_args)
All input must be validated
All permissions must be checked
No buffer overflows, no memory leaks

4. PostgreSQL 17+ Only

This configuration targets PostgreSQL 17 and later versions exclusively.

#if PG_VERSION_NUM < 170000
    #error "This extension requires PostgreSQL 17 or later"
#endif

What's Inside

pg-dev-claude-code/
├── agents/                    # Specialized PostgreSQL development agents
│   ├── pg-kernel-architect.md     # Architecture design, technical decisions
│   ├── pg-extension-builder.md    # Extension development workflow
│   ├── pg-code-reviewer.md        # Code quality review
│   ├── pg-security-reviewer.md    # Security vulnerability detection
│   ├── pg-tdd-guide.md            # Test-driven development
│   └── pg-memory-debugger.md      # Memory issue debugging
│
├── rules/                     # Always-follow guidelines
│   ├── pg-coding-style.md         # C99 standards, PostgreSQL conventions
│   ├── pg-security.md             # Security checklist
│   ├── pg-testing.md              # Testing requirements
│   ├── pg-extension-first.md      # Extension-first principle
│   ├── pg-git-workflow.md         # Branch strategy, commit format
│   └── pg-agents.md               # Agent usage guide
│
├── skills/                    # Domain knowledge and workflows
│   ├── pg-kernel-development/     # Kernel API reference
│   ├── pg-extension-development/  # Extension development reference
│   └── pg-testing-workflow/       # Testing workflow reference
│
├── commands/                  # Slash commands for quick execution
│   ├── pg-tdd.md                  # /pg-tdd - TDD development workflow
│   ├── pg-code-review.md          # /pg-code-review - Code review
│   ├── pg-extension-create.md     # /pg-extension-create - Create extension
│   ├── pg-extension-create-by-reqfile.md  # /pg-extension-create-by-reqfile - Requirements-driven extension
│   ├── pg-kernel-create-by-reqfile.md     # /pg-kernel-create-by-reqfile - Requirements-driven kernel
│   ├── pg-test-coverage.md        # /pg-test-coverage - Coverage check
│   └── pg-memory-check.md         # /pg-memory-check - Memory check
│
└── docs/                      # Documentation
    ├── index.md                   # Documentation index
    └── PG-README.md               # Quick start guide & best practices

Installation

Option 1: Install as Plugin (Recommended)

# Add marketplace
/plugins marketplace add flyinweb/pg-dev-claude-code

# Install plugin
/plugins install pg-dev-claude-code@pg-dev-claude-code

Or add to ~/.claude/settings.json:

{
  "extraKnownMarketplaces": {
    "pg-dev-claude-code": {
      "source": {
        "source": "github",
        "repo": "flyinweb/pg-dev-claude-code"
      }
    }
  },
  "enabledPlugins": {
    "pg-dev-claude-code@pg-dev-claude-code": true
  }
}

Option 2: Manual Installation

git clone https://github.com/flyinweb/pg-dev-claude-code.git

# Copy to Claude config
cp pg-dev-claude-code/agents/*.md ~/.claude/agents/
cp pg-dev-claude-code/rules/*.md ~/.claude/rules/
cp pg-dev-claude-code/commands/*.md ~/.claude/commands/
cp -r pg-dev-claude-code/skills/* ~/.claude/skills/

Quick Start

Create New Extension

# Create extension skeleton
/pg-extension-create zbyte_feature "My PostgreSQL Extension"

# Build and install
cd zbyte_feature
make && make install

# Test
make installcheck

Extension naming convention: zbyte_<feature_name>

Requirements-Driven Extension Development

Create extension from a requirements markdown file:

/pg-extension-create-by-reqfile requirements.md

Requirements file structure:

# zbyte_my_feature

## Description
[What the extension does]

## Functional Requirements
- [Requirement 1]

## API Specification
### Functions
- `zbyte_func(param type) RETURNS type`

## Validation Conditions
- [ ] [Test case with expected result]

## Security Requirements
- All input validated

## Notes
- [Important notes]

Requirements-Driven Kernel Development

For kernel modifications (requires explicit confirmation):

/pg-kernel-create-by-reqfile kernel_requirements.md

WARNING: Kernel modifications are the LAST resort. The command will verify that extension/hook/FDW approaches are not possible before proceeding.

TDD Development Workflow

/pg-tdd "Add new function to process data"

Workflow:

RED - Write failing tests (test/sql/test_feature.sql)
GREEN - Implement minimal code (src/zbyte_feature.c)
REFACTOR - Improve code quality
COVERAGE - Verify >= 90% coverage

Code Review

/pg-code-review src/zbyte_feature.c

Review includes:

Memory management (palloc/pfree pairing)
SQL injection protection
Error handling patterns
PostgreSQL coding standards

Test Coverage Check

/pg-test-coverage

Memory Check

/pg-memory-check

Uses Valgrind or AddressSanitizer to detect memory issues.

Commands Reference

This plugin provides 7 specialized slash commands for PostgreSQL development:

/pg-extension-create

Create a new PostgreSQL extension skeleton with all necessary files and structure.

/pg-extension-create <extension_name> [description]

Features:

Creates complete extension directory structure
Generates PGXS Makefile
Creates control file with metadata
Scaffolds C source files with PostgreSQL boilerplate
Sets up SQL installation script
Creates basic test files

Generated Structure:

zbyte_<feature>/
├── Makefile                    # PGXS build configuration
├── zbyte_<feature>.control     # Extension metadata
├── README.md                   # Extension documentation
├── sql/
│   └── zbyte_<feature>--1.0.sql    # SQL installation script
├── src/
│   ├── zbyte_<feature>.c       # Main C source
│   └── zbyte_<feature>.h       # Header file
└── test/
    ├── sql/
    │   └── test_basic.sql      # Test SQL
    └── expected/
        └── test_basic.out      # Expected output

Example:

/pg-extension-create zbyte_json_utils "JSON utility functions for PostgreSQL"

/pg-extension-create-by-reqfile

Create a complete PostgreSQL extension from a requirements markdown file using TDD workflow.

/pg-extension-create-by-reqfile <requirements_file.md>

Features:

Parses requirements file for functional specs
Auto-generates test cases from Validation Conditions
Implements full RED-GREEN-REFACTOR TDD cycle
Validates security requirements
Checks test coverage (minimum 90%)
Runs memory checks

Requirements File Structure:

# zbyte_feature_name

## Description
[What the extension does]

## Functional Requirements
- [Requirement 1]
- [Requirement 2]

## API Specification
### Functions
- `function_name(param type) RETURNS type`
  - Description: [what it does]

## Validation Conditions
- [ ] [Test case with expected result]

## Security Requirements
- All input validated
- Parameterized queries only

## Notes
- [Important notes]

Workflow Phases:

Requirements Analysis - Parse and validate requirements
Project Scaffolding - Create directory structure
TDD Development - RED → GREEN → REFACTOR
Validation - Tests, coverage, memory check, security review
Documentation - Generate README and usage docs

Example:

/pg-extension-create-by-reqfile ~/projects/json_validator_requirements.md

/pg-kernel-create-by-reqfile

Create PostgreSQL kernel modifications from requirements file (LAST RESORT ONLY).

/pg-kernel-create-by-reqfile <requirements_file.md>

⚠️ CRITICAL WARNING: Kernel modifications are the absolute last resort. Before proceeding, this command will verify:

Extension approach is NOT possible
Hook mechanism is NOT sufficient
FDW cannot solve the problem
Custom Scan is NOT applicable

User MUST explicitly confirm after reviewing the analysis.

Required Sections in Requirements File:

Why Kernel Modification Required - Mandatory explanation
Extension Analysis - What was tried, why insufficient
Hook Analysis - Relevant hooks examined, why insufficient
Affected Components - Parser/Planner/Executor/Storage/etc.
Risk Assessment - High/Medium/Low with mitigation strategy

Validation Requirements:

100% test coverage (mandatory for kernel code)
All existing regression tests must pass
Memory safety verified via Valgrind/ASan
Security review completed
Performance benchmarks met

Output Includes:

Confirmation table with risk assessment
Modified files list with line counts
Full validation status report
Patch generation instructions

/pg-tdd

Execute PostgreSQL Test-Driven Development workflow for new features or bug fixes.

/pg-tdd <feature_description>

TDD Workflow:

RED - Write failing tests first
GREEN - Implement minimal code to pass tests
REFACTOR - Improve code quality
COVERAGE - Verify >= 90% coverage

Test Template Generated:

-- test/sql/test_feature.sql

-- Setup
CREATE EXTENSION IF NOT EXISTS zbyte_feature;

-- Positive tests
SELECT zbyte_feature.new_function('valid_input');

-- Boundary tests
SELECT zbyte_feature.new_function(NULL);
SELECT zbyte_feature.new_function('');

-- Error tests (negative cases)
\set ON_ERROR_STOP off
SELECT zbyte_feature.new_function('invalid_input');
\set ON_ERROR_STOP on

-- Cleanup
DROP EXTENSION zbyte_feature;

Commands Used:

# Run specific test
make installcheck REGRESS="test_feature"

# View test differences
cat regression.diffs

# Run with coverage
make COVERAGE=1 installcheck
lcov --summary coverage.info

Example:

/pg-tdd "Add zbyte_json_validate function that validates JSON against schema"

/pg-code-review

Perform comprehensive PostgreSQL code review for quality and security.

/pg-code-review [file_path]

Review Categories:

Severity	Category	Examples
Critical	Must Fix	malloc instead of palloc, SQL injection, unhanded errors
High	Fix Before Release	Lock escalation, version incompatibility, memory cleanup
Medium	Recommended	Code style, comments, naming conventions

Review Checklist:

Memory Management:

Uses palloc/pfree instead of malloc/free
Memory contexts properly managed
Exception paths clean up memory (PG_TRY/PG_CATCH)

Security:

SQL uses parameterized queries (SPI_execute_with_args)
All user input validated
Permission checks implemented (ACL_*)
No buffer overflows possible

Concurrency:

Lock usage appropriate
Hook calls predecessor correctly
No race conditions

Style:

PostgreSQL coding standards followed
Comments clear and accurate
Naming conventions respected

Output Format:

[CRITICAL] SQL Injection Vulnerability
File: src/zbyte_feature.c:142
Issue: String concatenation in SQL query
Fix: Use SPI_execute_with_args with parameters

// Wrong
sprintf(query, "SELECT * FROM users WHERE id = %s", user_input);
SPI_execute(query, false, 0);

// Correct
Oid types[1] = {TEXTOID};
Datum values[1] = {CStringGetTextDatum(user_input)};
SPI_execute_with_args("SELECT * FROM users WHERE id = $1",
                      1, types, values, NULL, false, 0);

/pg-test-coverage

Check test coverage and generate detailed report.

/pg-test-coverage [target_directory]

Coverage Requirements:

Code Type	Minimum Coverage
Kernel Modifications	100%
Extension Code	90%
Utility Functions	95%

Workflow:

# 1. Clean build
make clean

# 2. Build with coverage instrumentation
make COVERAGE=1

# 3. Run tests
make installcheck COVERAGE=1

# 4. Generate report
lcov --capture --directory . --output-file coverage.info
lcov --summary coverage.info
genhtml coverage.info --output-directory coverage_html

Output Report:

Coverage Report
===============

Lines......: 92.5% (185 of 200 lines)
Functions..: 95.0% (19 of 20 functions)
Branches...: 88.0% (70 of 80 branches)

Status: ✅ PASS (>= 90%)

Uncovered Code:
- src/utils.c:45-52 (error handling branch)
- src/hooks.c:123 (edge case)

Recommendations:
1. Add error input tests to cover utils.c:45-52
2. Add boundary tests to cover hooks.c:123

/pg-memory-check

Detect memory leaks and memory issues using Valgrind or AddressSanitizer.

/pg-memory-check [test_command]

Tools Supported:

1. Valgrind:

# Build debug version
make clean
CFLAGS="-g -O0" make

# Run with Valgrind
valgrind --leak-check=full \
         --show-leak-kinds=all \
         --track-origins=yes \
         --error-exitcode=1 \
         postgres -D $PGDATA &

# Execute tests
psql -c "CREATE EXTENSION zbyte_feature;"
psql -c "SELECT zbyte_feature.test_function('test');"

# Stop and analyze
pg_ctl stop

2. AddressSanitizer:

# Build with ASan
make clean
CC=clang CFLAGS="-fsanitize=address -g" make

# Set environment
export ASAN_OPTIONS="detect_leaks=1"

# Run tests
make installcheck

Common Issues Detected:

Issue	Problem	Solution
Memory Leak	`pstrdup()` without `pfree()`	Add `pfree()` before return
Use After Free	SPI value used after `SPI_finish()`	Copy value with `pstrdup()` first
Buffer Overflow	Array index out of bounds	Add bounds checking
Context Leak	MemoryContext not deleted	Call `MemoryContextDelete()`

Output Report:

Memory Check Report
===================

Tool: valgrind
Status: ✅ No memory leaks

Details:
- Heap usage: 1,234 bytes
- Freed: 1,234 bytes
- Leaked: 0 bytes

Or if issues found:

Status: ❌ Memory leaks detected

Issues:
1. src/zbyte_feature.c:42
   Leaked 128 bytes (pstrdup not freed)

Fix Recommendations:
1. Call pfree() before function returns
2. Use Memory Context for automatic management

Agents

Agent	Purpose	When to Use
`pg-kernel-architect`	Architecture design	Major design decisions, kernel vs extension analysis
`pg-extension-builder`	Extension development	Creating and building extensions
`pg-code-reviewer`	Code quality review	Before commits, PR reviews
`pg-security-reviewer`	Security analysis	Security-critical code, SQL handling
`pg-tdd-guide`	TDD workflow	Feature development with tests
`pg-memory-debugger`	Memory debugging	Memory leaks, context issues

Key API Reference

Memory Management

// Allocation (NEVER use malloc/free)
void *ptr = palloc(size);
void *ptr = palloc0(size);  // Zero-initialized
pfree(ptr);

// Strings
char *str = pstrdup(original);
char *str = psprintf("%s-%d", prefix, num);

Memory Context

// Create context
MemoryContext ctx = AllocSetContextCreate(
    CurrentMemoryContext, "MyContext", ALLOCSET_DEFAULT_SIZES);

// Switch context
MemoryContext old = MemoryContextSwitchTo(ctx);
// ... allocations in new context ...
MemoryContextSwitchTo(old);

// Cleanup
MemoryContextDelete(ctx);

SPI (Parameterized Queries)

SPI_connect();

Oid types[1] = {INT4OID};
Datum values[1] = {Int32GetDatum(id)};

// ALWAYS use parameterized queries - NEVER string concatenation
int ret = SPI_execute_with_args(
    "SELECT * FROM users WHERE id = $1",
    1, types, values, NULL, false, 0);

SPI_finish();

Hook Pattern

static planner_hook_type prev_planner_hook = NULL;

void _PG_init(void)
{
    prev_planner_hook = planner_hook;
    planner_hook = my_planner_hook;
}

PlannedStmt *my_planner_hook(Query *parse, ...)
{
    // Your logic here...

    // MUST call predecessor
    if (prev_planner_hook)
        return prev_planner_hook(parse, ...);
    else
        return standard_planner(parse, ...);
}

Error Handling

// Standard error reporting
ereport(ERROR,
    (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
     errmsg("invalid input: %s", input),
     errdetail("Expected positive integer"),
     errhint("Use a value greater than 0")));

// Exception handling
PG_TRY();
{
    // Risky operations
}
PG_CATCH();
{
    // Cleanup
    PG_RE_THROW();
}
PG_END_TRY();

Testing

Test Types

Type	Tool	Purpose
SQL Regression	pg_regress	Basic functionality
Isolation	isolation tester	Concurrency
TAP	prove	Complex scenarios
Memory	Valgrind/ASan	Memory safety

Running Tests

# Regression tests
make installcheck

# With coverage
make COVERAGE=1 installcheck
lcov --capture --directory . --output-file coverage.info
genhtml coverage.info --output-directory coverage_html

Security Checklist

No SQL injection (use parameterized queries)
All input validated
Permission checks (ACL_SELECT, ACL_INSERT, etc.)
No buffer overflow
No memory leaks
No sensitive information disclosure
SECURITY DEFINER functions reviewed

Git Workflow

Branch Strategy

main (stable)
  └── feature/xxx (development)
        └── PR → Code Review → Merge

Commit Message Format

<type>(<scope>): <subject>

<body>

<footer>

Types: feat, fix, docs, test, refactor, perf, chore

Documentation

References

Official Documentation

Community Resources

Contributing

Contributions are welcome. Please follow:

Extension-first principle
90%+ test coverage
PostgreSQL coding standards
Security best practices

License

MIT - Use freely, modify as needed.

Remember: PostgreSQL code quality requirements are extremely high. Extension first, testing is mandatory, security first.

Name		Name	Last commit message	Last commit date
Latest commit History 2 Commits
.claude-plugin		.claude-plugin
.claude		.claude
agents		agents
commands		commands
contexts		contexts
docs		docs
examples		examples
hooks		hooks
mcp-configs		mcp-configs
plugins		plugins
rules		rules
scripts		scripts
skills		skills
tests		tests
.gitignore		.gitignore
CONTRIBUTING.md		CONTRIBUTING.md
README.md		README.md

flyinweb/pg-dev-claude-code

Folders and files

Latest commit

History

Repository files navigation

pg-dev-claude-code

Core Principles

1. Extension First

2. Test Coverage Requirements

3. Security First

4. PostgreSQL 17+ Only

What's Inside

Installation

Option 1: Install as Plugin (Recommended)

Option 2: Manual Installation

Quick Start

Create New Extension

Requirements-Driven Extension Development

Requirements-Driven Kernel Development

TDD Development Workflow

Code Review

Test Coverage Check

Memory Check

Commands Reference

/pg-extension-create

/pg-extension-create-by-reqfile

/pg-kernel-create-by-reqfile

/pg-tdd

/pg-code-review

/pg-test-coverage

/pg-memory-check

Agents

Key API Reference

Memory Management

Memory Context

SPI (Parameterized Queries)

Hook Pattern

Error Handling

Testing

Test Types

Running Tests

Security Checklist

Git Workflow

Branch Strategy

Commit Message Format

Documentation

References

Official Documentation

Community Resources

Contributing

License

About

Resources

Contributing

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Contributors 9

Uh oh!

Languages

Packages