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 "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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
