\pset null '(null)' CREATE SERVER virtuoso FOREIGN DATA WRAPPER rdf_fdw OPTIONS ( endpoint 'http://virtuoso:8890/sparql-auth'); CREATE FOREIGN TABLE ft ( subject rdfnode OPTIONS (variable '?s'), predicate rdfnode OPTIONS (variable '?p'), object rdfnode OPTIONS (variable '?o') ) SERVER virtuoso OPTIONS ( log_sparql 'true', sparql 'SELECT * { GRAPH {?s ?p ?o} }', sparql_update_pattern 'GRAPH { ?s ?p ?o . }' ); CREATE USER MAPPING FOR postgres SERVER virtuoso OPTIONS (user 'dba', password 'secret'); /* bulk INSERT triples */ INSERT INTO ft (subject, predicate, object) SELECT sparql.iri(''), sparql.iri('http://www.w3.org/1999/02/22-rdf-syntax-ns#value'), i::rdfnode FROM generate_series(1,10) AS j (i); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { "1"^^ . } }; INSERT DATA { GRAPH { "2"^^ . } }; INSERT DATA { GRAPH { "3"^^ . } }; INSERT DATA { GRAPH { "4"^^ . } }; INSERT DATA { GRAPH { "5"^^ . } }; INSERT DATA { GRAPH { "6"^^ . } }; INSERT DATA { GRAPH { "7"^^ . } }; INSERT DATA { GRAPH { "8"^^ . } }; INSERT DATA { GRAPH { "9"^^ . } }; INSERT DATA { GRAPH { "10"^^ . } }; /* DELETE single triple */ DELETE FROM ft WHERE object = 8::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "8"^^) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "8"^^ . } }; SELECT * FROM ft WHERE object = 8::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "8"^^) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE range of triples */ DELETE FROM ft WHERE object BETWEEN 2::rdfnode AND 5::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o >= "2"^^) FILTER(?o <= "5"^^) } INFO: SPARQL returned 4 records. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "2"^^ . } }; DELETE DATA { GRAPH { "3"^^ . } }; DELETE DATA { GRAPH { "4"^^ . } }; DELETE DATA { GRAPH { "5"^^ . } }; SELECT * FROM ft WHERE object BETWEEN 2::rdfnode AND 5::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o >= "2"^^) FILTER(?o <= "5"^^) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE triple with special character */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"🐘"@de'); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { "🐘"@de . } }; DELETE FROM ft WHERE object = '"🐘"@de'::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "🐘"@de) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "🐘"@de . } }; SELECT * FROM ft WHERE object = '"🐘"@de'::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "🐘"@de) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE triple with empty literal */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', '""@pt'); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { ""@pt . } }; DELETE FROM ft WHERE object = sparql.strlang('""', 'pt')::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = ""@pt) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { ""@pt . } }; SELECT * FROM ft WHERE object = sparql.strlang('""', 'pt')::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = ""@pt) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE with compound WHERE condition */ DELETE FROM ft WHERE subject = '' AND predicate = '' AND object = 10::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) FILTER(?o = "10"^^) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "10"^^ . } }; SELECT * FROM ft WHERE subject = '' AND predicate = '' AND object = 10::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) FILTER(?o = "10"^^) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE triple with typed literal */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"rdf_fdw"^^'); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { "rdf_fdw"^^ . } }; DELETE FROM ft WHERE object = sparql.strdt('rdf_fdw',''); INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "rdf_fdw"^^) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "rdf_fdw"^^ . } }; SELECT * FROM ft WHERE object = sparql.strdt('rdf_fdw',''); INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "rdf_fdw"^^) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE triple with very long IRI */ INSERT INTO ft (subject, predicate, object) VALUES (('')::rdfnode, '', '"rdf_fdw"^^'); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { "rdf_fdw"^^ . } }; DELETE FROM ft WHERE subject = ('')::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "rdf_fdw"^^ . } }; SELECT * FROM ft WHERE subject = ('')::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE non-existent triple (should succeed silently with DELETE 0) */ DELETE FROM ft WHERE object = 'foo'::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "foo") } INFO: SPARQL returned 0 records. /* DELETE with literals containing escaped quotes */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"\"WWU\""@en'); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { "\"WWU\""@en . } }; DELETE FROM ft WHERE object = '"\"WWU\""@en'::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "\"WWU\""@en) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "\"WWU\""@en . } }; SELECT * FROM ft WHERE object = '"\"WWU\""@en'::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "\"WWU\""@en) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE with literals containing newline */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', E'"Line1\nLine2"@en'); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { "Line1\nLine2"@en . } }; DELETE FROM ft WHERE object = E'"Line1\nLine2"@en'::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "Line1\nLine2"@en) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "Line1\nLine2"@en . } }; SELECT * FROM ft WHERE object = E'"Line1\nLine2"@en'::rdfnode; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "Line1\nLine2"@en) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) /* DELETE triple with RETURNING */ INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"🐘"@de'); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { "🐘"@de . } }; DELETE FROM ft WHERE object = '"🐘"@de'::rdfnode RETURNING OLD.subject, OLD.predicate, OLD.object; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = "🐘"@de) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "🐘"@de . } }; subject | predicate | object ---------------------------------------------------+------------------------------------------------+--------- | | "🐘"@de (1 row) /* invalid credentials test */ ALTER USER MAPPING FOR postgres SERVER virtuoso OPTIONS (SET user 'dba', SET password 'foo'); -- wrong password DELETE FROM ft; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } ERROR: authentication failed on server "virtuoso" (HTTP 401) HINT: Check the credentials in the USER MAPPING for PostgreSQL user "postgres". ALTER USER MAPPING FOR postgres SERVER virtuoso OPTIONS (SET user 'dba', SET password 'secret'); -- restore correct password /* read-only server: blocks DELETE regardless of triple pattern */ ALTER SERVER virtuoso OPTIONS (ADD readonly 'true'); DELETE FROM ft; ERROR: foreign table "ft" does not allow deletes /* table overrides server: server is readonly but table explicitly sets readonly=false */ ALTER FOREIGN TABLE ft OPTIONS (ADD readonly 'false'); DELETE FROM ft WHERE object = ''; -- succeeds: table override allows writes INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = ) } INFO: SPARQL returned 0 records. SELECT * FROM ft WHERE object = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?o = ) } INFO: SPARQL returned 0 records. subject | predicate | object ---------+-----------+-------- (0 rows) ALTER FOREIGN TABLE ft OPTIONS (DROP readonly); /* read-only foreign table: server is writable, table explicitly read-only */ ALTER SERVER virtuoso OPTIONS (SET readonly 'false'); ALTER FOREIGN TABLE ft OPTIONS (ADD readonly 'true'); DELETE FROM ft; ERROR: foreign table "ft" does not allow deletes /* read-write server and foreign table, but no triple pattern */ ALTER SERVER virtuoso OPTIONS (DROP readonly); ALTER FOREIGN TABLE ft OPTIONS (DROP readonly); ALTER FOREIGN TABLE ft OPTIONS (DROP sparql_update_pattern); DELETE FROM ft; ERROR: foreign table "ft" does not allow deletes /* invalid triple patterns */ ALTER FOREIGN TABLE ft OPTIONS (ADD sparql_update_pattern '?s ?p .'); -- missing object variable DELETE FROM ft; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 4 records. ERROR: 'sparql_update_pattern' contains no valid triple patterns HINT: A triple pattern requires at least three components (subject, predicate, object), e.g., '?s ?p ?o .'. ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern ''); -- empty pattern ERROR: empty value in option 'sparql_update_pattern' DELETE FROM ft; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 4 records. ERROR: 'sparql_update_pattern' contains no valid triple patterns HINT: A triple pattern requires at least three components (subject, predicate, object), e.g., '?s ?p ?o .'. /* invalid data type */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern 'GRAPH { ?s ?p ?o . }'); -- restore correct pattern ALTER FOREIGN TABLE ft ALTER COLUMN predicate TYPE text; DELETE FROM ft WHERE predicate = ''; -- should fail, predicate column is now text, not rdfnode WARNING: the rdf_fdw FOREIGN TABLE "ft" has columns using native PostgreSQL types which are deprecated: predicate HINT: Use the "rdfnode" type instead. INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?p = ) } INFO: SPARQL returned 4 records. WARNING: the rdf_fdw FOREIGN TABLE "ft" has columns using native PostgreSQL types which are deprecated: predicate HINT: Use the "rdfnode" type instead. ERROR: invalid data type for DELETE on column "predicate" DETAIL: Only columns of type rdfnode can be used in DELETE operations. ALTER FOREIGN TABLE ft ALTER COLUMN predicate TYPE rdfnode; /* cleanup */ DELETE FROM ft WHERE object IN (SELECT object FROM ft WHERE sparql.lang(object) = 'non-existent-lang'); INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 4 records. INFO: SPARQL query sent to 'virtuoso': SELECT ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(LANG(?o) = "non-existent-lang") } INFO: SPARQL returned 0 records. DELETE FROM ft WHERE object IN (SELECT object FROM ft WHERE sparql.lang(object) <> 'en'); INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 4 records. INFO: SPARQL query sent to 'virtuoso': SELECT ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(LANG(?o) != "en") } INFO: SPARQL returned 4 records. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "1"^^ . } }; DELETE DATA { GRAPH { "6"^^ . } }; DELETE DATA { GRAPH { "7"^^ . } }; DELETE DATA { GRAPH { "9"^^ . } }; SELECT count(*) FROM ft; INFO: SPARQL query sent to 'virtuoso': SELECT * { GRAPH {?s ?p ?o} } INFO: SPARQL returned 0 records. count ------- 0 (1 row) DROP SERVER virtuoso CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to foreign table ft drop cascades to user mapping for postgres on server virtuoso