Re: error messages (autovaccum canceled and syntax errors) whileloading a DUMP

El día jueves, junio 25, 2020 a las 01:11:37p. m. +0200, Matthias Apitz escribió:

>
> Hello
>
> A PG dump file was produced on a 11.4. server the usual ways.
> When it is loaded with
>
> $ gzip -dc newanna_export.dmp | /usr/local/sisis-pap/pgsql/bin/psql -U sisis sisis
>
> it gives lots of error messages in the server log as:
>
> ...
>
> and also syntax errors like:

>
> 2020-06-25 12:02:15.509 CEST [32719] ERROR:  syntax error at or near "order" at character 652
> 2020-06-25 12:02:15.509 CEST [32719] STATEMENT:  select id, brgroup, ins_time, upd_time, task_1, task_2, task_3,
int_sel_1,int_sel_2, int_sel_3, int_sel_4, int_sel_5, str_sel_1, str_sel_2, str_sel_3, dat_sel_1, dat_sel_2, sel_seq_1,
sel_seq_2,int_val_1, int_val_2, int_val_3, int_val_4, int_val_5, str_val_1, str_val_2, str_val_3, str_val_4, str_val_5,
valtype_1,valtype_2, valtype_3, valtype_4, valtype_5, valtype_6, valtype_7, valmode_1, valmode_2, valmode_3, valmode_4,
valmode_5,valmode_6, valmode_7, valname_1, valname_2, valname_3, valname_4, valname_5, valname_6, valname_7, valcont_1,
valcont_2,valcont_3, valcont_4, valcont_5, valcont_6, valcont_7, sel_seq_3 from acq_vardata where id= order by
sel_seq_3
> 2020-06-25 12:06:42.098 CEST [306] ERROR:  syntax error at or near "q" at character 1
> 2020-06-25 12:06:42.098 CEST [306] STATEMENT:  q
>         select count(*) from ig_bigstrings ;
>
> What could be the reason for this?

I looked in the dump file after uncompressing it. The 'syntax error'
comes from: one large table contains in a bytea column Perl code wich
our software reads from the table and executes it with Perl. But, why
the psql tryes to understand this code when it should INSERT it into the
table? This is a bit part of the content of the table:

# function: readAcqVarData\n#\n# parameter: where condition (string)\n# returns: array of records\n#
$recordList->[0]->{'int_val_1'}= "20";\n#          $recordList->[0]->{'str_val_1'} = "string";\n#
$recordList->[1]->{'int_val_1'}= "99";\n#          $recordList->[1]->{'str_val_1'} = "Dummy";\n#\nsub readAcqVarData
{\n\tmy($where) = @_;\n\tmy $recordList = undef;\n\tmy $ipos_valcont_7 = 56; # position valcont_7-Spalte in
select-statement- 1\n\n\t$debug .= "<h3>readAcqVarData() </h3>\\n" if $debugOn;\n\n\t$erg = selectData(\n\t\t
"selectid, brgroup, ins_time, upd_time, task_1, task_2, task_3, "\n\t\t  . "int_sel_1, int_sel_2, int_sel_3, int_sel_4,
int_sel_5,"\n\t\t  . "str_sel_1, str_sel_2, str_sel_3, dat_sel_1, dat_sel_2, "\n\t\t  . "sel_seq_1, sel_seq_2, "\n\t\t
."int_val_1, int_val_2, int_val_3, int_val_4, int_val_5, "\n\t\t  . "str_val_1, str_val_2, str_val_3, str_val_4,
str_val_5,"\n\t\t  . "valtype_1, valtype_2, valtype_3, valtype_4, valtype_5, valtype_6, valtype_7, "\n\t\t  .
"valmode_1,valmode_2, valmode_3, valmode_4, valmode_5, valmode_6, valmode_7, "\n\t\t  . 

        matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!