((
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?
-
no risk on filename conflict? (e.g. my filename and a pgsql's managed temporary table filename)
-
no risk of directory (
base
) volume overflow? (e.g. when I save a big file there) -
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
).
Best Answer
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.