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

On 6/25/20 5:54 AM, Matthias Apitz wrote:
>
>
> 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

What was the command to produce the dump?

>>
>> 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?

What was logged before?:

2020-06-25 12:02:15.509 CEST [32719] ERROR: syntax error at or near
"order" at character 652

What happens if you do as a test something like?:

CREATE TABLE test_table AS SELECT * FROM large_table WHERE row_id =
row_below;

>
> 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 "select id, 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
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com