\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'); INSERT INTO ft (subject, predicate, object) VALUES ('', '', '"Westfälische Wilhelms-Universität Münster"@de'); INFO: SPARQL query sent to 'virtuoso': INSERT DATA { GRAPH { "Westfälische Wilhelms-Universität Münster"@de . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+------------------------------------------------ | | "Westfälische Wilhelms-Universität Münster"@de (1 row) UPDATE ft SET object = '"University of Münster"@en' WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "Westfälische Wilhelms-Universität Münster"@de . } }; INSERT DATA { GRAPH { "University of Münster"@en . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+---------------------------- | | "University of Münster"@en (1 row) UPDATE ft SET object = '""'::rdfnode WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "University of Münster"@en . } }; INSERT DATA { GRAPH { "" . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+-------- | | "" (1 row) UPDATE ft SET object = '🐘'::rdfnode WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "" . } }; INSERT DATA { GRAPH { "🐘" . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+-------- | | "🐘" (1 row) /* update using existing column */ UPDATE ft SET object = predicate WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "🐘" . } }; INSERT DATA { GRAPH { . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+---------------------------------------------- | | (1 row) /* update literals with quotes */ UPDATE ft SET object = '"\"text with quotes\""'::rdfnode WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { . } }; INSERT DATA { GRAPH { "\"text with quotes\"" . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+------------------------ | | "\"text with quotes\"" (1 row) /* update literals containing newlines */ UPDATE ft SET object = '"text \n newline"'::rdfnode WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "\"text with quotes\"" . } }; INSERT DATA { GRAPH { "text \n newline" . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+----------- | | "text + | | newline" (1 row) /* update to xsd:string literal (graphdb might omit xsd:string datatype) */ UPDATE ft SET object = '"text xsd string"^^'::rdfnode WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "text \n newline" . } }; INSERT DATA { GRAPH { "text xsd string"^^ . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+-------------------------------------------------------------- | | "text xsd string"^^ (1 row) /* update to xsd:int literal */ UPDATE ft SET object = 42::rdfnode WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "text xsd string"^^ . } }; INSERT DATA { GRAPH { "42"^^ . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+---------------------------------------------- | | "42"^^ (1 row) /* update to xsd:decimal literal */ UPDATE ft SET object = 42.37::rdfnode WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "42"^^ . } }; INSERT DATA { GRAPH { "42.37"^^ . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+----------------------------------------------------- | | "42.37"^^ (1 row) /* update to xsd:long literal */ UPDATE ft SET object = 423712345678911::rdfnode WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "42.37"^^ . } }; INSERT DATA { GRAPH { "423712345678911"^^ . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+--------------------------------------------------------------- | | "423712345678911"^^ (1 row) /* update with NULL - must fail (after SELECT since FDW must fetch OLD values first) */ UPDATE ft SET object = NULL WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. ERROR: NULL value in column "object" violates RDF constraint DETAIL: SPARQL variable '?o' cannot be NULL in RDF triple patterns. HINT: RDF triples require all components to be non-NULL. Filter NULL values in your query or provide default values. SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+--------------------------------------------------------------- | | "423712345678911"^^ (1 row) /* update rdfnode with a blank node */ UPDATE ft SET object = sparql.bnode() WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. ERROR: blank nodes are not allowed in UPDATE operations HINT: Blank nodes are document-local and have no global meaning. Use IRIs instead. /* update with RETURNING */ UPDATE ft SET object = '"Westfälische Wilhelms-Universität Münster"@de' WHERE subject = '' AND predicate = '' RETURNING OLD.subject, OLD.predicate, OLD.object, NEW.subject AS new_subject, NEW.predicate AS new_predicate, NEW.object AS new_object; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "423712345678911"^^ . } }; INSERT DATA { GRAPH { "Westfälische Wilhelms-Universität Münster"@de . } }; subject | predicate | object | new_subject | new_predicate | new_object -------------------------------+----------------------------------------------+---------------------------------------------------------------+-------------------------------+----------------------------------------------+------------------------------------------------ | | "423712345678911"^^ | | | "Westfälische Wilhelms-Universität Münster"@de (1 row) /* invalid credentials test */ ALTER USER MAPPING FOR postgres SERVER virtuoso OPTIONS (SET user 'dba', SET password 'foo'); -- wrong password UPDATE ft SET object = '"University of Münster"@en' WHERE subject = '' AND predicate = ''; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?s = ) FILTER(?p = ) } 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 UPDATE regardless of triple pattern */ ALTER SERVER virtuoso OPTIONS (ADD readonly 'true'); UPDATE ft SET object = '"foo"@en'; ERROR: foreign table "ft" does not allow updates /* table overrides server: server is readonly but table explicitly sets readonly=false */ ALTER FOREIGN TABLE ft OPTIONS (ADD readonly 'false'); UPDATE ft SET object = '"foo"@en'; -- succeeds: table override allows writes INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "Westfälische Wilhelms-Universität Münster"@de . } }; INSERT DATA { GRAPH { "foo"@en . } }; SELECT * FROM ft WHERE subject = ''; 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. subject | predicate | object -------------------------------+----------------------------------------------+---------- | | "foo"@en (1 row) 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'); UPDATE ft SET object = '"foo"@en'; ERROR: foreign table "ft" does not allow updates /* 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); UPDATE ft SET object = '"foo"@en'; ERROR: foreign table "ft" does not allow updates /* invalid triple patterns */ ALTER FOREIGN TABLE ft OPTIONS (ADD sparql_update_pattern '?s ?p .'); -- missing object variable UPDATE ft SET object = '"foo"@en'; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 1 record. 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' UPDATE ft SET object = '"foo"@en'; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 1 record. 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; UPDATE ft SET object = '"foo"@en'; 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} } INFO: SPARQL returned 1 record. 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 UPDATE on column "predicate" DETAIL: Only columns of type rdfnode can be used in UPDATE operations. ALTER FOREIGN TABLE ft ALTER COLUMN predicate TYPE rdfnode; /* cleanup */ UPDATE ft SET object = '"updated"'::rdfnode WHERE subject IN (SELECT subject FROM ft WHERE predicate = ''); INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?p = ) } INFO: SPARQL returned 0 records. UPDATE ft SET object = '"updated"'::rdfnode WHERE subject IN (SELECT subject FROM ft WHERE predicate <> ''); INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p { GRAPH {?s ?p ?o} ## rdf_fdw pushdown conditions ## FILTER(?p != ) } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "foo"@en .} }; INSERT DATA { GRAPH { "updated" .} }; DELETE FROM ft; INFO: SPARQL query sent to 'virtuoso': SELECT ?s ?p ?o { GRAPH {?s ?p ?o} } INFO: SPARQL returned 1 record. INFO: SPARQL query sent to 'virtuoso': DELETE DATA { GRAPH { "updated" .} }; 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