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

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

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

2020-06-25 11:58:42.909 CEST [11004] LOG: checkpoints are occurring too frequently (24 seconds apart)
2020-06-25 11:58:42.909 CEST [11004] HINT: Consider increasing the configuration parameter "max_wal_size".
2020-06-25 11:59:30.776 CEST [31295] ERROR: canceling autovacuum task
2020-06-25 11:59:30.776 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.d01buch"
2020-06-25 11:59:41.289 CEST [31282] ERROR: canceling autovacuum task
2020-06-25 11:59:41.289 CEST [31282] CONTEXT: automatic analyze of table "sisis.public.d03geb"
2020-06-25 11:59:43.515 CEST [31282] ERROR: canceling autovacuum task
2020-06-25 11:59:43.515 CEST [31282] CONTEXT: automatic analyze of table "sisis.public.d11loesch"
2020-06-25 11:59:45.838 CEST [31295] ERROR: canceling autovacuum task
2020-06-25 11:59:45.838 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.d11rueck"
2020-06-25 12:00:17.007 CEST [31295] ERROR: canceling autovacuum task
2020-06-25 12:00:17.007 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_statistic"
2020-06-25 12:00:50.333 CEST [31295] ERROR: canceling autovacuum task
2020-06-25 12:00:50.333 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_targetlog"
2020-06-25 12:00:53.658 CEST [31295] ERROR: canceling autovacuum task
2020-06-25 12:00:53.658 CEST [31295] CONTEXT: automatic analyze of table "sisis.public.idm_targettab"
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
...

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

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

--
Matthias Apitz, ✉ guru(at)unixarea(dot)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!