Re: Inconsistent compilation error

On Wed, Apr 18, 2018 at 9:02 PM, <raf(at)raf(dot)org> wrote:

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

*>The line in question is:>> raise notice '% %',
'blah_history.original_id', r;>>Which looks fine. It is not fine. You have
specifed TWO percent signs (%) which requires TWO argumenrts,but you have
only provided ONE -> r.*

*Hence-> ERROR: too many parameters specified for RAISE *

*https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE
<https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE>"
Inside the format string, % is replaced by the string representation of the
next optional argument's value"*--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!