Inconsistent compilation error

Поиск
Список
Период
Сортировка
От raf@raf.org
Тема Inconsistent compilation error
Дата
Msg-id 20180419010228.jcogqkeu2b7bxh2q@raf.org
обсуждение исходный текст
Ответы Re: Inconsistent compilation error  (Melvin Davidson <melvin6925@gmail.com>)
Re: Inconsistent compilation error  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Inconsistent compilation error  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Inconsistent compilation error  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
Hi,

postgresql-9.5.12 on debian-9

I have a stored function with code that looks like:

    create or replace function tla_audit_delete_thing()
    returns boolean stable language plpgsql as $$
    declare
        r record;
        status boolean := 1;
    begin
        for r in select _.* from blah_history _ where _.original_id not in (select id from blah)
        loop
            raise notice '% %', 'blah_history.original_id', r;
            status := 0;
    end loop;
    [...]
    end
    $$
    security definer
    set search_path = public, pg_temp;
    revoke all on function tla_audit_delete_thing() from public;
    grant execute on function tla_audit_delete_thing() to staff;

And I have a program that loads stored functions from disk
when they are different to what's in the database and I have
just loaded a very old database backup, brought the schema up
to date, and tried to bring the stored functions up to date.

But I'm getting this compilation error when it tries to load this
function:

    ERROR:  too many parameters specified for RAISE
    CONTEXT:  compilation of PL/pgSQL function "tla_audit_delete_thing" near line 9

    Traceback (most recent call last):
      File "lib/loadfunc.py", line 228, in main
        db.cursor().execute(src)
      File "/usr/lib/python2.7/dist-packages/pgdb.py", line 1026, in execute
        return self.executemany(operation, [parameters])
      File "/usr/lib/python2.7/dist-packages/pgdb.py", line 1050, in executemany
        rows = self._src.execute(sql)
    ProgrammingError: ERROR:  too many parameters specified for RAISE
    CONTEXT:  compilation of PL/pgSQL function "tla_audit_delete_thing" near line 9

The line in question is:

    raise notice '% %', 'blah_history.original_id', r;

Which looks fine. The really wierd thing is that this happens when done on a
debian9 host but when I load the function from another host (my macos laptop)
with the same function into the same database, it works fine.

I've never encountered an inconsistency like this before.

Any suggestions as to what might be causing it?

The python versions are slightly different and the pgdb module versions
are different but I wouldn't have thought that that would affect the
compilation performed by the database server itself:

  debian9:       python-2.7.13 pgdb-5.0.3
  macos-10.11.6: python-2.7.14 pgdb-4.2.2

And the sql sent to the database server is identical from both hosts.

And I don't think anything much has changed on the debian host recently.

And it's not just the old backup. The same is happening with other copies of
essentially the same database.

And all the other stored functions were loaded fine. It's just this one that
went wrong.

Thanks in advance for any insights you can share.

cheers,
raf



В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: New website
Следующее
От: Melvin Davidson
Дата:
Сообщение: Re: Inconsistent compilation error