rdf_fdw

rdf_fdw : Foreign data wrapper for RDF triplestores over SPARQL endpoints

Overview

Packages

Source

pig build pkg rdf_fdw;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install rdf_fdw;		# install via package name, for the active PG version

pig install rdf_fdw -v 18;   # install for PG 18
pig install rdf_fdw -v 17;   # install for PG 17
pig install rdf_fdw -v 16;   # install for PG 16
pig install rdf_fdw -v 15;   # install for PG 15
pig install rdf_fdw -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION rdf_fdw;

Usage

Sources:

• PGXN rdf_fdw 3.0.0
• rdf_fdw 3.0 README
• rdf_fdw 3.0 changelog
• rdf_fdw 3.0 control file

rdf_fdw is a PostgreSQL foreign data wrapper for querying RDF triplestores over SPARQL endpoints. It exposes SPARQL result variables as foreign-table columns, supports pushdown for common SQL clauses, includes a native rdfnode type for RDF terms, provides SPARQL 1.1 helper functions, and can perform SPARQL INSERT, UPDATE, and DELETE through writable foreign tables.

Distribution 3.0.0 provides SQL extension 3.0. This release repairs RDF comparison, arithmetic and pushdown correctness. GROUP BY, DISTINCT, UNION and unique constraints distinguish RDF term spelling; sparql.uri() now returns rdfnode, so text assignments need an explicit cast. Some unsafe pushdown cases execute locally.

Create the Extension

CREATE EXTENSION IF NOT EXISTS rdf_fdw;

SELECT rdf_fdw_version();
SELECT * FROM rdf_fdw_settings;

Before upgrading from 2.x, save and drop indexes on rdfnode columns, and dependent views, materialized views or SQL-body functions that sort, group or deduplicate them. Recreate these objects after the upgrade; REINDEX alone is insufficient. Use the appropriate command for a fresh installation or a prepared upgrade:

CREATE EXTENSION rdf_fdw WITH VERSION '3.0';
ALTER EXTENSION rdf_fdw UPDATE TO '3.0';

Register a SPARQL Endpoint

CREATE SERVER dbpedia
FOREIGN DATA WRAPPER rdf_fdw
OPTIONS (
  endpoint          'https://dbpedia.org/sparql',
  enable_pushdown   'true',
  request_timeout   '60',
  max_response_size '104857600',
  readonly          'true'
);

Useful server options include:

• endpoint: SPARQL endpoint URL; required.
• batch_size: number of rows per SPARQL UPDATE batch.
• enable_pushdown: enables SQL-to-SPARQL pushdown.
• format: expected SPARQL result MIME type.
• http_proxy: proxy URL; proxy credentials belong in USER MAPPING.
• connect_timeout: connection timeout.
• request_timeout: complete HTTP request timeout.
• max_response_size: maximum response body size in bytes; 0 means unlimited.
• readonly: prevents INSERT, UPDATE, and DELETE before requests reach the endpoint.
• request_max_redirect: 0 refuses redirects; a positive value enables and limits them. -1 is rejected. request_redirect is deprecated.

Use max_response_size for public or untrusted endpoints because rdf_fdw loads retrieved RDF data into memory before converting it for PostgreSQL.

User Mapping

CREATE USER MAPPING FOR postgres
SERVER dbpedia
OPTIONS (
  user 'sparql_user',
  password 'secret'
);

v2.6.0 adds Bearer-token authentication:

CREATE USER MAPPING FOR postgres
SERVER dbpedia
OPTIONS (
  token 'eyJhbGciOi...'
);

Proxy credentials are also USER MAPPING options:

CREATE USER MAPPING FOR app_user
SERVER dbpedia
OPTIONS (
  proxy_user 'proxy-user',
  proxy_password 'proxy-secret'
);

Foreign Tables with rdfnode Columns

Declare foreign-table columns as rdfnode to preserve RDF terms, IRIs, blank nodes, language tags, and XSD datatypes.

CREATE FOREIGN TABLE dbpedia_films (
  film rdfnode OPTIONS (variable '?film'),
  name rdfnode OPTIONS (variable '?name'),
  year rdfnode OPTIONS (variable '?year')
)
SERVER dbpedia
OPTIONS (
  sparql $$
    SELECT ?film ?name ?year
    WHERE {
      ?film a dbo:Film ;
            rdfs:label ?name ;
            dbo:releaseDate ?year .
      FILTER (lang(?name) = 'en')
    }
  $$
);

Native PostgreSQL column types are deprecated for RDF values in v2.6.0. Existing native-typed tables continue to work, but they emit warnings and lose RDF term details.

Querying and Pushdown

SELECT film, sparql.lex(name) AS title
FROM dbpedia_films
WHERE name = '"The Matrix"@en'::rdfnode
ORDER BY year
LIMIT 10;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT *
FROM dbpedia_films
WHERE film = '<http://dbpedia.org/resource/The_Matrix>'::rdfnode;

rdf_fdw can push down WHERE, LIMIT, ORDER BY, DISTINCT, and supported comparisons/functions. Use EXPLAIN VERBOSE to inspect the generated remote SPARQL.

Prefix Management

rdf_fdw provides catalog tables and helper functions under the sparql schema for reusable SPARQL prefixes:

SELECT sparql.add_context('default', 'Default SPARQL prefix context');
SELECT sparql.add_prefix('default', 'rdf',  'http://www.w3.org/1999/02/22-rdf-syntax-ns#');
SELECT sparql.add_prefix('default', 'rdfs', 'http://www.w3.org/2000/01/rdf-schema#');
SELECT sparql.add_prefix('default', 'xsd',  'http://www.w3.org/2001/XMLSchema#');

Data Modification

Writable foreign tables translate PostgreSQL INSERT, UPDATE, and DELETE into SPARQL UPDATE requests. The example assumes an administrator-created rdf_write server connected to a writable endpoint you control; keep the public DBpedia server read-only. Every update-pattern variable must map to a non-null rdfnode column, and sparql_update_pattern is mandatory.

CREATE FOREIGN TABLE writable_triples (
  subject rdfnode OPTIONS (variable '?s'),
  predicate rdfnode OPTIONS (variable '?p'),
  object rdfnode OPTIONS (variable '?o')
)
SERVER rdf_write
OPTIONS (
  sparql 'SELECT ?s ?p ?o WHERE { ?s ?p ?o }',
  sparql_update_pattern '?s ?p ?o .',
  readonly 'false'
);
INSERT INTO writable_triples(subject, predicate, object) VALUES (
  '<http://example.org/film/1>'::rdfnode,
  '<http://www.w3.org/2000/01/rdf-schema#label>'::rdfnode,
  '"Example Film"@en'::rdfnode
);

Use readonly = true to disable writes for a server or table. An explicit table setting of readonly = false overrides a server setting of readonly = true; enforce read-only access at the remote endpoint with its credentials and access controls when writes must be prohibited.

Each remote write is committed immediately by the triplestore. PostgreSQL ROLLBACK does not undo it; plan application-level compensation and do not assume cross-system atomicity.

Clone a Foreign Table

CALL rdf_fdw_clone_table(
  foreign_table := 'dbpedia_films',
  target_table  := 'dbpedia_films_local',
  fetch_size    := 1000,
  create_table  := true
);

rdf_fdw_clone_table() copies data from a foreign table into a local table in batches. v2.5 fixed several round-trip issues for RDF terms during cloning.

SPARQL Functions

The sparql schema implements many SPARQL 1.1 functions and aggregates, including:

• aggregates such as sparql.sum, sparql.avg, sparql.min, sparql.max, sparql.group_concat, and sparql.sample
• RDF term helpers such as sparql.isiri, sparql.isblank, sparql.isliteral, sparql.datatype, sparql.iri, sparql.strdt, and sparql.strlang
• string functions such as sparql.strlen, sparql.substr, sparql.ucase, sparql.lcase, sparql.contains, and sparql.replace
• numeric, date/time, hash, and custom convenience functions

Caveats

• PostgreSQL 9.5 or newer is the upstream baseline.
• Retrieved RDF data is accumulated in memory before conversion. Set max_response_size, use LIMIT, and keep remote result sets bounded.
• Prefer rdfnode columns. Native PostgreSQL typed columns are deprecated for RDF terms and will lose IRI/language/datatype information.
• Store secrets in USER MAPPING; do not put proxy credentials or endpoint tokens into SERVER options.
• Public SPARQL endpoints can be slow or rate-limited. Use connect_timeout, request_timeout, retries, and local materialization when needed.
• The extension is not relocatable in 3.0 and also creates the fixed sparql schema. Review the 3.0 migration requirements before changing existing installations.