\pset null '(null)' CREATE SERVER fuseki FOREIGN DATA WRAPPER rdf_fdw OPTIONS ( endpoint 'http://fuseki:3030/dt/sparql', update_url 'http://fuseki:3030/dt/update'); CREATE FOREIGN TABLE ft ( subject rdfnode OPTIONS (variable '?s'), predicate rdfnode OPTIONS (variable '?p'), object rdfnode OPTIONS (variable '?o') ) SERVER fuseki OPTIONS ( log_sparql 'true', sparql 'SELECT * WHERE {?s ?p ?o}', sparql_update_pattern '?s ?p ?o .' ); CREATE USER MAPPING FOR postgres SERVER fuseki OPTIONS (user 'admin', 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 'fuseki': INSERT DATA { "1"^^ }; INSERT DATA { "2"^^ }; INSERT DATA { "3"^^ }; INSERT DATA { "4"^^ }; INSERT DATA { "5"^^ }; INSERT DATA { "6"^^ }; INSERT DATA { "7"^^ }; INSERT DATA { "8"^^ }; INSERT DATA { "9"^^ }; INSERT DATA { "10"^^ }; /* DELETE single triple */ DELETE FROM ft WHERE object = 8::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?o = "8"^^) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "8"^^ }; SELECT * FROM ft WHERE object = 8::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?o >= "2"^^) FILTER(?o <= "5"^^) } INFO: SPARQL returned 4 records. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "2"^^ }; DELETE DATA { "3"^^ }; DELETE DATA { "4"^^ }; DELETE DATA { "5"^^ }; SELECT * FROM ft WHERE object BETWEEN 2::rdfnode AND 5::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': INSERT DATA { "🐘"@de }; DELETE FROM ft WHERE object = '"🐘"@de'::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?o = "🐘"@de) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "🐘"@de }; SELECT * FROM ft WHERE object = '"🐘"@de'::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': INSERT DATA { ""@pt }; DELETE FROM ft WHERE object = sparql.strlang('""', 'pt')::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?o = ""@pt) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'fuseki': DELETE DATA { ""@pt }; SELECT * FROM ft WHERE object = sparql.strlang('""', 'pt')::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) FILTER(?o = "10"^^) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "10"^^ }; SELECT * FROM ft WHERE subject = '' AND predicate = '' AND object = 10::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': INSERT DATA { "rdf_fdw"^^ }; DELETE FROM ft WHERE object = sparql.strdt('rdf_fdw',''); INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?o = "rdf_fdw"^^) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "rdf_fdw" }; SELECT * FROM ft WHERE object = sparql.strdt('rdf_fdw',''); INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': INSERT DATA { "rdf_fdw"^^ }; DELETE FROM ft WHERE subject = ('')::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?s = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "rdf_fdw" }; SELECT * FROM ft WHERE subject = ('')::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': INSERT DATA { "\"WWU\""@en }; DELETE FROM ft WHERE object = '"\"WWU\""@en'::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?o = "\"WWU\""@en) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "\"WWU\""@en }; SELECT * FROM ft WHERE object = '"\"WWU\""@en'::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': INSERT DATA { "Line1\nLine2"@en }; DELETE FROM ft WHERE object = E'"Line1\nLine2"@en'::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?o = "Line1\nLine2"@en) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "Line1\nLine2"@en }; SELECT * FROM ft WHERE object = E'"Line1\nLine2"@en'::rdfnode; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': INSERT DATA { "🐘"@de }; DELETE FROM ft WHERE object = '"🐘"@de'::rdfnode RETURNING OLD.subject, OLD.predicate, OLD.object; ERROR: missing FROM-clause entry for table "old" LINE 2: RETURNING OLD.subject, OLD.predicate, OLD.object; ^ /* invalid credentials test */ ALTER USER MAPPING FOR postgres SERVER fuseki OPTIONS (SET user 'admin', SET password 'foo'); -- wrong password DELETE FROM ft; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o} INFO: SPARQL returned 5 records. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "1"^^ }; DELETE DATA { "6"^^ }; DELETE DATA { "7"^^ }; DELETE DATA { "9"^^ }; DELETE DATA { "🐘"@de }; ERROR: authentication failed on server "fuseki" (HTTP 401) HINT: Check the credentials in the USER MAPPING for PostgreSQL user "postgres". ALTER USER MAPPING FOR postgres SERVER fuseki OPTIONS (SET user 'admin', SET password 'secret'); -- restore correct password /* read-only server: blocks DELETE regardless of triple pattern */ ALTER SERVER fuseki 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 'fuseki': SELECT ?s ?p ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(?o = ) } INFO: SPARQL returned 0 records. SELECT * FROM ft WHERE object = ''; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?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 fuseki 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 fuseki 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 'fuseki': SELECT ?s ?p ?o {?s ?p ?o} INFO: SPARQL returned 5 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 'fuseki': SELECT ?s ?p ?o {?s ?p ?o} INFO: SPARQL returned 5 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 '?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 'fuseki': SELECT ?s ?p ?o {?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 'fuseki': SELECT ?s ?p ?o {?s ?p ?o} INFO: SPARQL returned 5 records. INFO: SPARQL query sent to 'fuseki': SELECT ?o {?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 'fuseki': SELECT ?s ?p ?o {?s ?p ?o} INFO: SPARQL returned 5 records. INFO: SPARQL query sent to 'fuseki': SELECT ?o {?s ?p ?o ## rdf_fdw pushdown conditions ## FILTER(LANG(?o) != "en") } INFO: SPARQL returned 5 records. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "1"^^ }; DELETE DATA { "6"^^ }; DELETE DATA { "7"^^ }; DELETE DATA { "9"^^ }; DELETE DATA { "🐘"@de }; SELECT count(*) FROM ft; INFO: SPARQL query sent to 'fuseki': SELECT * {?s ?p ?o} INFO: SPARQL returned 0 records. count ------- 0 (1 row) DROP SERVER fuseki CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to foreign table ft drop cascades to user mapping for postgres on server fuseki