\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; 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 }; subject | predicate | object ---------------------------------------------------+------------------------------------------------+--------- | | "🐘"@de (1 row) /* 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 4 records. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "1"^^ }; DELETE DATA { "6"^^ }; DELETE DATA { "7"^^ }; DELETE DATA { "9"^^ }; ERROR: SERVER authentication failed (HTTP status 401) HINT: Check the user and password set in the USER MAPPING for the PostgreSQL user "postgres" and try again. 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 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 'fuseki': SELECT ?s ?p ?o {?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 .'. /* cleanup */ ALTER FOREIGN TABLE ft OPTIONS (SET sparql_update_pattern '?s ?p ?o .'); -- restore correct pattern DELETE FROM ft; INFO: SPARQL query sent to 'fuseki': SELECT ?s ?p ?o {?s ?p ?o} INFO: SPARQL returned 4 records. INFO: SPARQL query sent to 'fuseki': DELETE DATA { "1"^^ }; DELETE DATA { "6"^^ }; DELETE DATA { "7"^^ }; DELETE DATA { "9"^^ }; 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