Thread: plpgsql: Debug function?
Is there a way to debug a PL/pgSQL function? It's behaving very irradic! I have two function, one that works and one that doesn't. The part that don't work in func2 is 'SELECT INTO ... ...' and I can't figgure out why it doesnt't work! -- Turbo __ _ Debian GNU Unix _IS_ user friendly - it's just ^^^^^ / /(_)_ __ _ ___ __ selective aboutwho its friends are / / | | '_ \| | | \ \/ / Debian Certified Linux Developer _ /// / /__| | | | | |_| |> < Turbo Fredriksson turbo@tripnet.se \\\/ \____/_|_| |_|\__,_/_/\_\ Stockholm/Sweden colonel fissionable assassination Cuba World Trade Center strategic smuggle congress Rule Psix cryptographic SDI critical Kennedy Semtex FBI [See http://www.aclu.org/echelonwatch/index.html for more about this]
>>>>> "Joel" == Joel Burton <jburton@scw.org> writes: Joel> On 25 Jul 2001, Turbo Fredriksson wrote: >> Is there a way to debug a PL/pgSQL function? It's behaving very >> irradic! Joel> It's crude, but you can output debugging statements w/ RAISE Joel> NOTICE or catch flawed assumptions by RAISEEXCEPTION. That's what I've been doing... The problem is that a 'SELECT INTO ...' in the function don't work, but the actual SELECT in psql works fine! The variable I'm SELECT'ing into don't get initialized... -- Turbo __ _ Debian GNU Unix _IS_ user friendly - it's just ^^^^^ / /(_)_ __ _ ___ __ selective aboutwho its friends are / / | | '_ \| | | \ \/ / Debian Certified Linux Developer _ /// / /__| | | | | |_| |> < Turbo Fredriksson turbo@tripnet.se \\\/ \____/_|_| |_|\__,_/_/\_\ Stockholm/Sweden radar Marxist counter-intelligence 747 Kennedy Serbian CIA NSA SEAL Team 6 FSF [Hello to all my fans in domestic surveillance] Clinton FBI Soviet class struggle [See http://www.aclu.org/echelonwatch/index.html for more about this]
On 25 Jul 2001, Turbo Fredriksson wrote: > Is there a way to debug a PL/pgSQL function? It's behaving very irradic! It's crude, but you can output debugging statements w/ RAISE NOTICE or catch flawed assumptions by RAISE EXCEPTION. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
On 25 Jul 2001, Turbo Fredriksson wrote: > >>>>> "Joel" == Joel Burton <jburton@scw.org> writes: > > Joel> On 25 Jul 2001, Turbo Fredriksson wrote: > >> Is there a way to debug a PL/pgSQL function? It's behaving very > >> irradic! > > Joel> It's crude, but you can output debugging statements w/ RAISE > Joel> NOTICE or catch flawed assumptions by RAISE EXCEPTION. > > That's what I've been doing... > > The problem is that a 'SELECT INTO ...' in the function don't work, but > the actual SELECT in psql works fine! > > The variable I'm SELECT'ing into don't get initialized... Can you post a simple, reproducible example? -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
> Is there a way to debug a PL/pgSQL function? It's behaving very > irradic! > Joel> It's crude, but you can output debugging statements w/ RAISE > Joel> NOTICE or catch flawed assumptions by RAISE EXCEPTION. Also try turning on query logging, so you can see in the postmaster log the queries plpgsql is sending to the SQL engine. This is especially helpful for catching unexpected substitutions or lack of substitutions of plpgsql variables, as in Morgan Curley's recent problem over in pgsql-sql. A volunteer to improve plpgsql's debugging/tracing facilities would be favorably received... regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: >> Is there a way to debug a PL/pgSQL function? It's behaving very >> irradic! Joel> It's crude, but you can output debugging statements w/ RAISE Joel> NOTICE or catch flawed assumptions by RAISEEXCEPTION. Tom> Also try turning on query logging, so you can see in the Tom> postmaster log the queries plpgsql is sending tothe SQL Tom> engine. Would that be the 'debug_print_query = true' in posgresql.conf? It IS true, but still nothing in the syslog... -- Turbo __ _ Debian GNU Unix _IS_ user friendly - it's just ^^^^^ / /(_)_ __ _ ___ __ selective aboutwho its friends are / / | | '_ \| | | \ \/ / Debian Certified Linux Developer _ /// / /__| | | | | |_| |> < Turbo Fredriksson turbo@tripnet.se \\\/ \____/_|_| |_|\__,_/_/\_\ Stockholm/Sweden PLO Legion of Doom domestic disruption Clinton spy Rule Psix Nazi quiche radar fissionable BATF SDI bomb security NSA [See http://www.aclu.org/echelonwatch/index.html for more about this]
On 26 Jul 2001, Turbo Fredriksson wrote: > >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: > > Would that be the 'debug_print_query = true' in posgresql.conf? > > It IS true, but still nothing in the syslog... I'm not sure. But I expect the logging would go out the postmaster's logs, not necessarily syslog. Take care, Bill