Re: error messages (autovaccum canceled and syntax errors) whileloading a DUMP - Mailing list pgsql-general

On 6/25/20 11:03 AM, Matthias Apitz wrote:
> El día Donnerstag, Juni 25, 2020 a las 08:59:49 -0700, Adrian Klaver escribió:
> 
>> 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?
> 
> 
> $ /usr/local/sisis-pap/pgsql/bin/pg_dump --file=newanna_export.dmp --compress=9 --user=sisis newanna
> 
> 

>>
>> What was logged before?:
>>
>> 2020-06-25 12:02:15.509 CEST [32719] ERROR:  syntax error at or near "order"
>> at character 652
> 
> 

> 2020-06-25 12:00:55.375 CEST [31295] ERROR:  canceling autovacuum task
> 2020-06-25 12:00:55.375 CEST [31295] CONTEXT:  automatic analyze of table "sisis.public.idm_tasktab"
> 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
> ...


If this was coming from the restore of the dump file I would expect to 
see a COPY line just prior to the error. To me it looks like something 
other then the restore is running that query and that the query itself 
is flawed.

> 
> 
>>
>> 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 do not understand this request, what is 'large_table' and what is
> 'row_below'?

The "large_table" is whatever table the Perl scripts are stored in. 
'row_below' is whatever row identifier there is for the row that stores 
this particular script. Just trying to confirm that the script will run 
when the row it is part of is INSERTed into a table. I'm guessing not.

You could also just manually run the script to see if the query is 
valid. I'm guessing it is not.

> 
> Thanks
> 
>     matthias
> 
>>>
>>> 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
>>>
>>
>>
>> -- 
>> Adrian Klaver
>> adrian.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com