Re: Inconsistent compilation error - Mailing list pgsql-general

Adrian Klaver wrote:

> On 04/18/2018 06:02 PM, raf@raf.org wrote:
> > Hi,
> > 
> 
> > 
> > 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
> 
> Hmm, wonder if there is an oops in the below:
> 
> http://www.pygresql.org/contents/changelog.html
> 
> Version 5.0 (2016-03-20)
> Changes in the DB-API 2 module (pgdb):
> "SQL commands are always handled as if they include parameters, i.e. literal
> percent signs must always be doubled. This consistent behavior is necessary
> for using pgdb with wrappers like SQLAlchemy."

well spotted! but i'm not sure. it depends on what they mean by
"literal percent signs". that might just mean percent signs that
appear in SQL string literals that need to be output ultimately
as actual percent signs but i thought that they always had to be
doubled. so i'm not sure what they are saying has changed in
that version. so maybe you are right.

but if they are suggesting that every single percent sign needs
to be doubled by the caller before passing sql to the pgdb
module, that sounds like an annoying change to have made.
but no doubt they have their reasons.

i've encountered other new behaviour with pygresql-5+ that i had
to find ways to disable/revert so it's not surprising that there
might be other oddities to encounter. i'm surprised it's only
become a problem now.

i think you're definitely right. when i change my function
loading program to duplicate all percent signs in all the source
code before passing it to pgdb, they all load successfully and a
subsequent audit of the code inside the database and on disk
still shows that they match, so pgdb must be de-duplicating all
the duplicated percent signs.

thanks so much for spotting this.

cheers,
raf