Our application was working quite well when using COPY INTO when inserting data into our tables as the default MonetDB user “monetdb” into the system schema (sys).

We have subsequently added users and granted table access to the various users.  At this point, INSERT works successfully, but COPY INTO does not.  The error message for the COPY INTO statement indicated that we needed to use “ON CLIENT” in our load.  That makes no difference.

Here are the relevant commands to the system (note the user names, passwords, schema names, and tables names were modified for this example):

Program that sets up our basic database does this:

START TRANSACTION;

CREATE USER admin WITH UNENCRYPTED PASSWORD ‘***’ NAME ‘Admin User' SCHEMA sys;

CREATE SCHEMA test AUTHORIZATION admin;

ALTER USER admin SET SCHEMA test;

CREATE USER update WITH UNENCRYPTED PASSWORD ‘***’ NAME ‘Update User’ SCHEMA test;

COMMIT;

Then, the program that creates the tables does the following:

START TRANSACTION;

CREATE MERGE TABLE x (…);

GRANT ALL ON x TO “update”;

CREATE TABLE x0 (…);

GRANT ALL ON x0 TO “update”;

CREATE TABLE x1 (…);

GRANT ALL ON x1 TO “update”;

CREATE TABLE x2 (…);

GRANT ALL ON x2 TO “update”;

CREATE TABLE x3 (…);

GRANT ALL ON x3 TO “update”;

COMMIT;

And we insert (or try to) (4 threads ingest into their own table, the merge table is used to combine the result set on queries):

START TRANSACTION;

COPY 100000 OFFSET 2 RECORDS INTO x0 FROM ‘/dev/shm/MonetDB/foo.csv’ (columnlist) ON CLIENT DELIMETERS ‘,’,’\n’;

This returns an error: !/dev/shm/MonetDB/foo.csv’: cannot retrieve files.

Because of company policy, I can’t provide the schema, but the columns in the csv file and the schema differ, which is why I am using the (…) and (columnlist) in the example.

Again, when I omit the CREATE USER, CREATE SCHEMA, and GRANT clauses, everything works correctly.

Any ideas on what is needed to make it work?  Right now, the ingest application and MonetDB are on the same server.  In the future, that might not be true.

We are running 11.33.3 on CentOS 7.

Thanks,

Dave