I have a perl script. One portion of it loads data into a PostgreSQL table. It runs as expected from the command line. It fails when launched by another script. I don’t know why.
I feel the key fact is the lack of output from the psql command. Nothing. No errors. No success.
To be clear, the script always runs. What fails is the “echo ‘\copy… ‘ | psql” which does not produce any output.
Some of the code looks like this:
my $PSQL = "/usr/local/bin/psql";
...
# echo "\\\copy vuxml_import from '/tmp/vnhvGB2yN0' WITH (FORMAT TEXT, HEADER false);" | $PSQL "sslmode=require host=pg01.int.unixathome.org user=commits_dvl dbname=freshports.dvl"
# sslcertmode=disable avoids could not open certificate file “/root/.postgresql/postgresql.crt”: Permission denied
my $psql_command = "$PSQL \"sslmode=$FreshPorts::Config::ssl_mode host=$FreshPorts::Config::host user=$FreshPorts::Config::user dbname=$FreshPorts::Config::dbname sslcertmode=disable\"";
my $copy_command = "\\copy vuxml_import from '$file_path' WITH (FORMAT TEXT, HEADER false);";
print "\$copy_command='$copy_command\n";
print "\$psql_command='$psql_command\n";
# save it to the database
eval {
$dbh->do($truncate_sql);
print "committing truncate\n";
# we commit this here because the psql import which follows will deadlock.
$dbh->commit();
print "echo \"$copy_command\" | $psql_command";
print "\n";
# need to check how many rows. Should be at least 5900
# use qx, similar to `backticks` - use chomp to remove the trailing whitespace
chomp(my $output = qx/echo "$copy_command;" | $psql_command/);
print "copy command has finished\n";
print "\$output is '$output'\n";
# grab the number of rows imported: https://stackoverflow.com/questions/3574906/how-to-extract-a-number-from-a-string-in-perl#3574959
my ($count) = $output =~ /(\d+)/;
print "Imported $count items\n";
if ($count < ExpectedCopy) {
$dbh->rollback(); # Rollback on error
# we do not return from this call
FreshPorts::Utilities::ReportError('err', "FATAL: we imported $count items, which is less than our expected minimum value of " . ExpectedCopy . "\n", 1);
# but just in case we do
die("bad number of vuxml_import");
}
};
if ($@) {
warn "Error during COPY: $@";
$dbh->rollback(); # Rollback on error
}
When run by the system, as opposed to by myself, this output appears:
$copy_command='\copy vuxml_import from '/tmp/freshports_vuxml_processing_hgb4z' WITH (FORMAT TEXT, HEADER false); $psql_command='/usr/local/bin/psql "sslmode=require host=pg01.int.unixathome.org user=commits_dvl dbname=freshports.dvl sslcertmode=disable" committing truncate echo "\copy vuxml_import from '/tmp/freshports_vuxml_processing_hgb4z' WITH (FORMAT TEXT, HEADER false);" | /usr/local/bin/psql "sslmode=require host=pg01.int.unixathome.org user=commits_dvl dbname=freshports.dvl sslcertmode=disable" copy command has finished $output is '' Imported items FATAL: we imported items, which is less than our expected minimum value of 5900
If I take line 4 above, and paste it in on the command line as the freshports user, we get:
$ echo "\copy vuxml_import from '/tmp/freshports_vuxml_processing_hgb4z' WITH (FORMAT TEXT, HEADER false);" | /usr/local/bin/psql "sslmode=require host=pg01.int.unixathome.org user=commits_dvl dbname=freshports.dvl sslcertmode=disable" COPY 6010 $
This is the expected result: it imported many rows.
Any ideas?
Questions raised
Does it work without specifying perl?
$ touch ~/signals/vuxml $ id uid=10001(freshports) gid=10001(freshports) groups=10001(freshports) $ ls -l /usr/local/libexec/freshports/process_vuxml. /usr/local/libexec/freshports/process_vuxml.pl /usr/local/libexec/freshports/process_vuxml.sh $ ls -l /usr/local/libexec/freshports/process_vuxml.pl -rwxr-xr-x 1 dvl dvl 14037 Aug 26 20:07 /usr/local/libexec/freshports/process_vuxml.pl $ /usr/local/libexec/freshports/process_vuxml.pl --filename=/jails/freshports/usr/ports/security/vuxml/vuln.xml process_vuxml.pl starts (there is usually a delay before further output) There, the parsefile has completed calculating the sha256 for each vuln doing the findnodes() thing findnodes() has finished, iteration has started ........................................................................................................................................................................................................................................... ........................... 44101b31-8ffd-11e7-b5af-a4badb2f4699 - cancelled: skipping that one for import .... c7e8e955-6c61-11e7-9b01-2047478f2f70 - cancelled: skipping that one for import ........ 04bc4e23-9a70-42cb-9fec-3613632d34bc - cancelled: skipping that one for import ......... 2aedd15f-ca8b-11e6-a9a5-b499baebfeaf - cancelled: skipping that one for import ..................... cf0b5668-4d1b-11e6-b2ec-b499baebfeaf - cancelled: skipping that one for import 61b8c359-4aab-11e6-a7bd-14dae9d210b8 - cancelled: skipping that one for import . c17fe91d-4aa6-11e6-a7bd-14dae9d210b8 - cancelled: skipping that one for import 42ecf370-4aa4-11e6-a7bd-14dae9d210b8 - cancelled: skipping that one for import ....... 95564990-1138-11e6-b55e-b499baebfeaf - cancelled: skipping that one for import ......................................................................... f99a4686-e694-11e3-9032-000c2980a9f3 - cancelled: skipping that one for import ..... 080c5370-886a-11e3-9533-60a44c524f57 - cancelled: skipping that one for import .... cd082cc6-1548-4b8d-a3aa-a007be611a29 - cancelled: skipping that one for import ......................................................... 6887828f-0229-11e0-b84d-00262d5ed8ee - cancelled: skipping that one for import b2a6fc0e-070f-11e0-a6e9-00215c6a37bb - cancelled: skipping that one for import .......... f6b6beaa-4e0e-11df-83fb-0015587e2cc1 - cancelled: skipping that one for import ................................................ ff284bf0-3f32-11dc-a79a-0016179b2dd5 - cancelled: skipping that one for import ................ 28ce7102-4039-11db-a838-00148584c7dd - cancelled: skipping that one for import ... 0a4cd819-0291-11db-bbf7-000c6ec775d9 - cancelled: skipping that one for import ... af2a60ed-da3e-11da-93e0-00123ffe8333 - cancelled: skipping that one for import ..................................... e8c6ade2-6bcc-11d9-8e6f-000a95bc6fae - cancelled: skipping that one for import .... 14e8f315-600e-11d9-a9e7-0001020eed82 - cancelled: skipping that one for import ...... 1f8dea68-3436-11d9-952f-000c6e8f12ef - cancelled: skipping that one for import ........... bef4515b-eaa9-11d8-9440-000347a4fa7d - cancelled: skipping that one for import .. 4764cfd6-d630-11d8-b479-02e0185c0b53 - cancelled: skipping that one for import ...... c7705712-92e6-11d8-8b29-0020ed76ef5a - cancelled: skipping that one for import ...... 6fd9a1e9-efd3-11d8-9837-000c41e2cdad - cancelled: skipping that one for import 3362f2c1-8344-11d8-a41f-0020ed76ef5a - cancelled: skipping that one for import all sha256 saved for each vuln finished with checksum eval() $copy_command='\copy vuxml_import from '/tmp/freshports_vuxml_processing_L1Uxw' WITH (FORMAT TEXT, HEADER false);' $psql_command='/usr/local/bin/psql "sslmode=require host=pg01.int.unixathome.org user=commits_dvl dbname=freshports.dvl sslcertmode=disable"' committing truncate echo "\copy vuxml_import from '/tmp/freshports_vuxml_processing_L1Uxw' WITH (FORMAT TEXT, HEADER false);" | /usr/local/bin/psql "sslmode=require host=pg01.int.unixathome.org user=commits_dvl dbname=freshports.dvl sslcertmode=disable" copy command has finished $output is 'COPY 6010' Imported 6010 items deleting missing vuxml vuln not present in xml files have been deleted from the database. getting list of vulns to update list of vulns to update has been obtained We have 0 vuxml entries to add/update invoking update_modified_vids() Nothing to update Total time: 21 seconds Number of updates: 0 committing process_vuxml.pl finishes
2025-08-27 – Let’s try Python
It seems that having a fever is good for ideas. Last night I concluded I should abandon trying to run this \copy from a shell command use python instead. I have a perfectly working example used for package imports. I have modified it to replace line 28 in the code above.
The new script is:
[13:15 dvl-ingress01 dvl ~/scripts] % cat process_vuxml_import_temp_file.py
#!/usr/local/bin/python
# take a file and load it into a temp table.
import psycopg2
import psycopg2.extras
import configparser # for config.ini parsing
import re # for escaping the database passwords
import syslog # for logging
import sys
import getopt
import os
def main(argv):
inputfile = ''
try:
opts, args = getopt.getopt(argv,"hi:o:",["ifile="])
except getopt.GetoptError:
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print (__file__ + ' -i <inputfile>')
sys.exit()
elif opt in ("-i", "--ifile"):
inputfile = arg
syslog.syslog(syslog.LOG_NOTICE, 'copying in from ' + inputfile)
config = configparser.ConfigParser()
config.read('/usr/local/etc/freshports/config.ini')
DSN = 'host=' + config['database']['HOST'] + ' dbname=' + config['database']['DBNAME'] + ' user=' + config['database']['COMMITS_DBUSER'] + ' password=' + re.escape(config['database']['COMMITS_PASSWORD']) + ' sslcertmode=disable sslmode=require'
dbh = psycopg2.connect(DSN)
curs = dbh.cursor(cursor_factory=psycopg2.extras.DictCursor)
#
# someone else does the truncate
#
curs.execute("select freshports_branch_set('head')")
with open(inputfile, 'r') as f:
curs.copy_from(f, 'vuxml_import', sep = '\t', columns = ['vid', 'checksum'] )
row_count = curs.rowcount
dbh.commit()
dbh.close();
syslog.syslog(syslog.LOG_NOTICE, 'copying completed. %d rows copied.' % row_count)
return row_count
syslog.openlog(ident=os.path.basename(__file__), facility=syslog.LOG_LOCAL3)
syslog.syslog(syslog.LOG_NOTICE, 'Starting up')
if __name__ == "__main__":
row_count = main(sys.argv[1:])
syslog.syslog(syslog.LOG_NOTICE, 'Finishing')
print ('COPY %d' % row_count)
Line 28 now becomes:
chomp(my $output = qx|/usr/local/libexec/freshports/process_vuxml_import_temp_file.py --ifile=$file_path|);
As before, this works from the command line. Now I just have to wait for the next vuxml import event.
