On Wed, Apr 18, 2018 at 9:02 PM, <r...@raf.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!
