Forum OpenACS Q&A: Running the psql COPY command from db_dml

I believe that as a non-super user you can use the copy command with the STDIN or STDOUT parameters, but I don't know how you would write/read the correct file handle from within aolserver

I'd rather use INSERT instead.
Anyway, why do you need to use COPY instead of INSERT?

As for the SQL standart: We are only using postgres, and it works fine here.

Is there a way to use INSERT to insert all the records in one go and not record by record?

db_dml:
Database operation "dml" failed
ERROR: syntax error at or near "\" at character 1
SQL: \copy ml_import_records ( record ) from '/tmp/file1sySZZ'

db_exec_plsql:
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
while executing
"ns_db 0or1row $db "select $function_name ()""
invoked from within
"db_exec_plpgsql $db $full_statement_name $sql $statement_name"
invoked from within
"if {[regexp -nocase -- {^\s*select} $test_sql match]} {
ns_log Debug "PLPGSQL: bypassed anon function"
set selection [..."
("uplevel" body line 6)
invoked from within
"uplevel 1 $code_block "
invoked from within
"db_with_handle -dbn $dbn db {
# plsql calls that are simple selects bypass the plpgsql
# mechanism for creating anonymous fun..."
(procedure "db_exec_plsql" line 57)
invoked from within
"db_exec_plsql import_to_db "\\copy ml_import_records ( record ) from '$temp_filename'""

Anyway, it seems to me like this is about importing into a temporary table, and we will need to run inserts from that table afterwards anyway. Of course this might be doable using one statement for all rows, removing the overhead of 20000 inserts+constraint validations - but if these imports are going to end up in the users table (just a guess), we probably will have to handle them separately at some point anyway.

/Nis

a) Since till now in my development environment the database and the webserver are in the same machine, I never met problems with COPY (except the superuser limitation). But, if I continue using it (COPY), do I gather right from your comments that it will not work on my production environment (where db and web are on different machines)?
And this would be solved (as the superuser limitation) with \copy ?

b) Yes, I will handle them seperatelly at some point. But the way I am doing it with this new "import shell", the import itself is one quick step. Then each record I handle, gets "tagged" as "done" or with an error message. When I handle the individual records, I dont' care if the process stops in the middle for any reason (which was happening with the imports till now because of a server down or whatever), because I always know which records have been processed and which not. Also, if for any reason I want to stop an import in the middle, I also can do it easily, and resume etc.

Old way:

db_dml import_to_db " copy ml_import_records ( record ) from :temp_filename"

the new way:
exec "$psqlpath/psql -h $host -p $port -U $user $dbname < /tmp/$file_with_sql"

Where "$file_with_sql" contains actually the following

\copy ml_import_records ( record ) from 'temp_file_name'

The First one works fine, but the second doesnt. It seems that no matter what I do, only commands like [db_dml] or [file copy] can "see" the file I am uploading (using ad_form).
Even if I try to do an "exec cp temp_file_name new_file" I get the same error:
"no such file or directory"