Inconsistent compilation error

From: raf(at)raf(dot)org
To: pgsql-general(at)postgresql(dot)org
Subject: Inconsistent compilation error
Date: 2018-04-19 01:02:29
Message-ID: 20180419010228.jcogqkeu2b7bxh2q@raf.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2018-04-19 01:28:35 Re: Inconsistent compilation error
Previous Message Adrian Klaver 2018-04-19 00:56:50 Re: New website