1

(( edit after answer: this is a typical XY question/problem, there are no restriction on use absolute path, on PostgreSQL's FileSystem functions. As @LaurenzAlbe comment, "just try". ))


I need to export/import JSON files, CSV files, XML, GeoJSON, etc. Example:

CREATE EXTENSION adminpack;
SELECT pg_catalog.pg_file_write(
  'base/pgsql_tmp/test1_hello.txt',
  E'Hello\n\n\tWorld!',
  false
);

All are temporary (exists in a BEGIN/COMMIT), but no problem?

  1. no risk on filename conflict? (e.g. my filename and a pgsql's managed temporary table filename)

  2. no risk of directory (base) volume overflow? (e.g. when I save a big file there)

  3. no risk on read-and-write sequences over same filename? They will be synchronous on BEGIN/COMMIT sections?


NOTES AND CONTEXT

Sometimes we can say that "database servers aren't designed for general file I/O" (ref), but sometimes we need it (!), to simplify and automatize ETL or interface procedures.

About item 1 and the practical use, to simplify export/import procedures (and not need to remember where is data_directory), is perhaps better to create a ${data_directory}/base/pgsql_tmp/MyArea as symbolic link to other area (e. eg. /tmp/MyRealArea).

1 Answer 1

1

Question 1:

It is a bad idea to create these files in the PostgreSQL data directory. That is for database files only. In addition, you cannot be certain that pgsql_tmp is there at all (it is only created when the first temporary file is written).

Use an absolute path and write the file somewhere else, ideally in /var.

Question 2

Of course there is a risk to fill up the file system - either through the files that you write, or through temporary files that the database writes.

Question 3

That is up to your code; the database does not guarantee that you don't write over the same file in two concurrent transactions.

2
  • Hi Laurenz, thanks! About your "use an absolute ... /var ..." suggestion, seems impossible, pg_catalog.pg_file_write() only writes at $data_directory. Apr 1, 2020 at 14:38
  • 1
    I just tried it and it works fine. Of course the PostgreSQL operating system user (typically postgres) must have write permissions on the directory. Apr 1, 2020 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.