Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.klaver@xxxxxxxxxxx

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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux