Debugging PostgreSQL

Hi All,
I've been using MS SQL 2k and it's bundled query analyzer SQL editor for more than 5 years. I am exploring postgres right now and I hit a snag I don't understand...

MS SQL : debugging - use query analyzer, declare variables, plug in code and test...

PG : using PGAdminIII editor :

declare

tst int; (or whatever variable)

there is an error already....syntax error...

I did some research and it seems that variables cannot be declared outside a function scope? Looking at functions within PG, the implementation looks like one massive dynamic sql string....

I wanted to figure out the way PG handles single quotes, double quotes, string concats and such.... problem is that the editor won't even let me declare variables...ahmm help? how does one debug in PG? what tool? anything like query analyzer of MS SQL? why can't I declare variables and print the values out to check and debug? HELP? Thanks in advance...

Hi,

calhoun wrote:
I did some research and it seems that variables cannot be declared outside a function scope? Looking at functions within PG, the implementation looks like one massive dynamic sql string....

Thats correct, you cannot declare variables in plain SQL as you can in SQL Server's T-SQL implementation (or Oracle's anonymous blocks).

calhoun wrote:
I wanted to figure out the way PG handles single quotes, double quotes, string concats and such.... problem is that the editor won't even let me declare variables...ahmm help?

In plain SQL (such as you would use in the pgAdmin query tool), the rule is essentially that you use single quotes around literal strings, and double quotes when required around identifiers. You can use operators and functions in appropriate places as well, for example:

SELECT 'This is part one ' || 'and this is part two of my string' AS "MixedCaseIdentifier"

Generally we would recommend avoiding mixed case identifiers though to avoid the need to quote them.

calhoun wrote:
how does one debug in PG? what tool? anything like query analyzer of MS SQL? why can't I declare variables and print the values out to check and debug? HELP? Thanks in advance...

The operations you describe would generally be done within a function, written in the language of your choice. The most obvious is pl/pgsql (which is similar to Oracle's pl/sql), but some prefer to use others such as pl/Perl, pl/TCL or pl/Python. pgAdmin will allow you to debug pl/pgsql functions - assuming you're running EDB-Postgres, if you browse into the demo database and look at the Functions in the sample schema, you will see one called list_emp. This is a good simple example that you can right-click and debug in pgAdmin. It uses RAISE to output text which is most commonly used for debugging, though you don't really need that when you have the debugger of course

.

For more info on pl/pgsql, please see:

http://postgres.enterprisedb.com/82/doc/dbserver/pg/plpgsql.html

and for the pgAdmin debugger:

http://www.pgadmin.org/docs/1.8/debugger.html

Thanks! Didn't even know about the debugger...I am using it now and it's helpful...Although I do wish it would allow editing during debugging.

Is there a trick between the Editor and the Debugger that will allow them to link or function together? Thanks in advance again.

calhoun wrote:Thanks! Didn't even know about the debugger...I am using it now and it's helpful...Although I do wish it would allow editing during debugging. Is there a trick between the Editor and the Debugger that will allow them to link or function together? Thanks in advance again.

Edit and continue is the primary feature I'd love to add, unfortunately it's far from easy to do due to the way queries are processed in Postgres. It's certainly something we'll be investigating for the future though.

Thanks for all the answers...Hope it's ok to ask another newbie question.

I got the first stored function (ust1)kind of working...basically here is what it does

1) Assumes that a table orig is already created. It's a support function that will be called within other stored functions.
2) ust1 will fill the records of table orig. inside ust1 are temporary tables. I can display the contents to the variables using RAISE...how can I see the contents of the internal temporary tables? In query analyzer, it's possible to have select * from temptable1 anywhere in the script to see what the contents are...in PGAdminIII this results with the error :

ERROR: query has no destination for result data

help? Many, many thanks.

calhoun wrote:
2) ust1 will fill the records of table orig. inside ust1 are temporary tables. I can display the contents to the variables using RAISE...how can I see the contents of the internal temporary tables? In query analyzer, it's possible to have select * from temptable1 anywhere in the script to see what the contents are...in PGAdminIII this results with the error :

ERROR: query has no destination for result data

That's another trick that probably makes more sense in TSQL than PG. I'd create a function to do it for you, something like (adjust the variable types etc to match your table) :

Hi davep,
Thanks so much for spending the time answering my newbie questions. The function you just gave is actually a fear of mine...let me explain.

Our current system, as already mentioned, runs in MS SQL. We do alot of data processing on the DB and the system results in data files which is post-processed and given an acceptable look via Excel and it's macros and/or a typical web display. Some of the current stored procs are about 15-20 pages when printed out and can process something like 9-12 million records per run...which actually takes only minutes using only amd x2's and 2 G of RAM. We tested SQL 2005 and it was waaay slower but we've hit the limit of 32 bit processing already and SQL 2005 doesn't seem to be a viable upgrade path. We studied MYSQL but since it's not fully ACID compliant and has data integrity issues then it is unacceptable..which leaves PG. The last couple of weeks of study seem to show that it's full of features and has zero data integrity problems...the performance concerns will be tested later once we've successfully ported the core stored procs. Currently the concern is focused on a good, easy and fast IDE. We've been testing PG Admin III, Aqua Data, EMS, Maestro , SQLYog, Navicat, etc... I'm worried about the development cycle with the shift to PG. Would you mind explaining why you mentioned that the built-in debugging trick makes more sense in TSQL than PG? Thanks... I'm a bit worried that we'd have to build alot of debugging code for all 3k++ stored procs currently in use.

. It seems a bit ahmm silly that we have this problem...

1) Can display contents of temporary tables when debugging SQL only...however it's not possible to use any variables since variables must be within a function scope.
2) Can use variables within a scope and debug them but it's really hard to display the contents of temporary tables during debugging...

bit of a chicken and egg.

hehehe...

your thoughts will be very much appreciated and thanks in advance.

BTW FC8 currently refuses to install the older build of EnterpriseDB PG.

I will try the latest builds...but I think I read somewhere the FC changed something...

calhoun wrote:Hi davep,
Thanks so much for spending the time answering my newbie questions. The function you just gave is actually a fear of mine...let me explain.

You're welcome. And feel free to drop the 'p' from davep

calhoun wrote:
Our current system, as already mentioned, runs in MS SQL. We do alot of data processing on the DB and the system results in data files which is post-processed and given an acceptable look via Excel and it's macros and/or a typical web display. Some of the current stored procs are about 15-20 pages when printed out and can process something like 9-12 million records per run...which actually takes only minutes using only amd x2's and 2 G of RAM. We tested SQL 2005 and it was waaay slower but we've hit the limit of 32 bit processing already and SQL 2005 doesn't seem to be a viable upgrade path. We studied MYSQL but since it's not fully ACID compliant and has data integrity issues then it is unacceptable..which leaves PG. The last couple of weeks of study seem to show that it's full of features and has zero data integrity problems...the performance concerns will be tested later once we've successfully ported the core stored procs. Currently the concern is focused on a good, easy and fast IDE. We've been testing PG Admin III, Aqua Data, EMS, Maestro , SQLYog, Navicat, etc... I'm worried about the development cycle with the shift to PG. Would you mind explaining why you mentioned that the built-in debugging trick makes more sense in TSQL than PG? Thanks... I'm a bit worried that we'd have to build alot of debugging code for all 3k++ stored procs currently in use. . It seems a bit ahmm silly that we have this problem...

1) Can display contents of temporary tables when debugging SQL only...however it's not possible to use any variables since variables must be within a function scope.
2) Can use variables within a scope and debug them but it's really hard to display the contents of temporary tables during debugging...

bit of a chicken and egg. hehehe...

your thoughts will be very much appreciated and thanks in advance.

As I see it TSQL is more like a scripting language in that you can write a sequential program and include some basic control structures to control the flow of the script.

In SQL though (with the possible exception of the CASE structure that can be used in a query), there aren't the same control structures - just a sequential list of inserts, updates and other statements. In that situation it doesn't make so much sense to be looking at table debug output mid-script as there isn't really anything to debug.

Instead we build our systems using just functions. We don't dump table contents in functions by doing a simple SELECT because, well, where would the output go? To the calling function perhaps? Or some equivalent of stdout? That's why we would use RAISE to display our output through what is probably the closest we have to stdout/stderr in programming terms.

In your case, I think there is no doubt you will have some non-trivial work to do to port 3K+ SPs to Postgres - to help with that, I would consider writing a generic version of the function I posted earlier, that will dynamically output the table data fom any table. You needn't use pl/pgsql for that - consider pl/perl, pl/python, pl/tcl or one of the other procedural language options if you can work more productively in one of those languages (they will certainly be more flexible than pl/pgsql).

Another option would be to consider EnterpriseDB Advanced Server which I believe supports anonymous blocks as part of it's Oracle compatibiity layer. You may find they offer a more familiar way of working.

I hope that gives you some useful ideas

calhoun wrote:
BTW FC8 currently refuses to install the older build of EnterpriseDB PG. I will try the latest builds...but I think I read somewhere the FC changed something...

OK, thanks - I'll pass that on to the appropriate person.

Hi Dave,

Thanks for the reply. Sorry to be a bother but I have very few personal contacts who have extensive experience in PG in the way we plan to use it. Mostly friends have just been using it as a web backend...

Yes we were thinking along those lines of building the generic
debugging functions using the the ANYELEMENT and ANYARRAY dynamic data types...PG seems stock-full of features and experience says that the current concerns are just startup pains.

BTW we tried a port to SYBASE ASE around 2 years ago...after about 3 months of partial porting, optimizing linux and ASE...we had dismal results. Basically the test was :
1) MSDE vs SYBASE ASE - 2 machines, exactly the same hardware.
2) MSDE and WIN2kPro vs Red HAT w/ SYBASE ASE

-- it was a relatively easy port since the language was mostly compatible...

anyways the initial result was RH w/ ASE was 4x slower than MSDE w/ W2kPro...Using MSSQL w/W2k Server it was approximately 5x slower...basically one number/data crunching stored proc took 1 minute in MSDE + Win2kPro and it took 5 minutes in RH + ASE....

we tried MSDE + Win2kPro vs SYBASE ASE + Win2kPro and the results were better but still dismal...SYBASE ASE + Win2kPro was about 2x slower...

Any thoughts with PG + FC or PG + Ubuntu vs MSSQL/MSDE + Windows? I think we will find out around next month or january...since the test port into PG will take longer...BTW we've optimized Linux and PG already the SHMMAX and other config changes necessary in PG...

Cluster-wise though, MS SQL 2k sucks in it's implementation ...that's where PG really tops...

Regards

calhoun wrote:Yes we were thinking along those lines of building the generic
debugging functions using the the ANYELEMENT and ANYARRAY dynamic data types...PG seems stock-full of features and experience says that the current concerns are just startup pains.

Quite probably - Postgres is chock full of useful features and there's a whole host of addons to provide additional functionality. That's what got me using it initially way back in the late 90's!

calhoun wrote:
Any thoughts with PG + FC or PG + Ubuntu vs MSSQL/MSDE + Windows? I think we will find out around next month or january...since the test port into PG will take longer...BTW we've optimized Linux and PG already the SHMMAX and other config changes necessary in PG...

Properly tuned you can generally get comparable or better performance than SQL/MSDE from PG. Sun have published benchmarks putting us within just a few % of Oracle's published figures - and the upcoming 8.3 release includes a number of significant additional performance enhancements.

I would suggest that you do not consider Linux & PG 'optimised' at this stage though. In my experience optimisation is a continuously ongoing task that requires regular review and occasional adjustment as datasets grow and workloads change over time. Your initial config's will hopefully be a good start, but once you start loading data and users onto your new systems you may well find you need to tweak queries or the odd config setting to get the performance you desire. I would expect that to be the case with any DBMS.

For info, EnterpriseDB can help with any performance tuning issues you may have if you need professional help: http://www.enterprisedb.com/tservices/postgresservices.do, or if you want to chat with other users and developers the pgsql-performance mailing list can be a useful resource: http://www.postgresql.org/community/lists/

Hi,

I've been trying to create a debugging function that would receive a tablename and a list of columns and then the function would display all the contents of the table using the RAISE command.

CREATE OR REPLACE FUNCTION usp_PG_DUMPTEMPTABLE(varchar(100), text[])
RETURNS void AS
$BODY$
DECLARE
_temptable ALIAS FOR $1;
_temparray alias for $2;
_i integer;
_max integer;
_tempstring varchar(2000);

BEGIN
_tempstring := 'RAISE INFO''';
for _i in 1 ..array_upper(_temparray, 1)
loop
_tempstring := _tempstring || _temparray[_i] || ' ' ;
end loop;
_tempstring := _tempstring || ''';';

raise info'%', _tempstring;
execute _tempstring;
raise info '---';

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';

for some reason, the EXECUTE command issues an error at the start fo the RAISE command...help?

Error from PG Admin III

ERROR: syntax error at or near "RAISE"
LINE 1: RAISE DEBUG 'SKUID CatID ';
^
QUERY: RAISE DEBUG 'SKUID CatID ';
CONTEXT: PL/pgSQL function "usp_pg_dumptemptable" line 42 at EXECUTE statement

Checked all the docs I could find and I couldn't find any info...

BTW just a simple :
execute 'RAISE INFO ''test ''';

has an error...am still searching docs everywhere..

This message was edited 1 time. Last update was at 21 Nov 2007 04:07:15

calhoun wrote:BTW just a simple :
execute 'RAISE INFO ''test ''';

has an error...am still searching docs everywhere..

EXECUTE is a pl/pgsql statement for executing dynamically constructed SQL. RAISE is also a pl/pgsql statement, not SQL - therefore you can't use RAISE with EXECUTE.

That said, why would you need to? It doesn't achieve anything over just using RAISE directly. The reason you would use EXECUTE is because normally inline queries in pl/pgsql are only parsed & planned once for efficiency, so cannot be dynamic. EXECUTE just feeds the SQL directly to the query engine every time it's used to allow dynamic queries at the expense of extra parse/planner calls..

It was just to test...to figure out why execute _tempstring wasn't working...the function I was building required that the RAISE commands be generated dynamically...after building the RAISE command dynamically

ie RAISE INFO '% % %....%', var1, temp1, tst1...fnl1 ;
(parameters were tablename and an array containing the names of the columns)

I would need to execute the dynamic sql string... oh well it looks like it can't be done ...thanks!

calhoun wrote:It was just to test...to figure out why execute _tempstring wasn't working...the function I was building required that the RAISE commands be generated dynamically...after building the RAISE command dynamically

ie RAISE INFO '% % %....%', var1, temp1, tst1...fnl1 ;
(parameters were tablename and an array containing the names of the columns)

I would need to execute the dynamic sql string... oh well it looks like it can't be done ...thanks!

Sorry, I must be missing something - that's exactly how RAISE does work - see http://postgres.enterprisedb.com/82/doc/dbserver/pg/plpgsql-errors-and-messages.html

Because RAISE is part of pl/pgsql and not SQL, it doesn't need to be planned in the same way as a dynamic SQL statement - it can just be called directly without EXECUTE.

Hi Dave,

This was the original post

CREATE OR REPLACE FUNCTION usp_PG_DUMPTEMPTABLE(varchar(100), text[])
RETURNS void AS
$BODY$
DECLARE
_temptable ALIAS FOR $1;
_temparray alias for $2;
_i integer;
_max integer;
_tempstring varchar(2000);

BEGIN
_tempstring := 'RAISE INFO''';
for _i in 1 ..array_upper(_temparray, 1)
loop
_tempstring := _tempstring || _temparray[_i] || ' ' ;
end loop;
_tempstring := _tempstring || ''';';

raise info'%', _tempstring;
execute _tempstring;
raise info '---';

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';

I need to build the Raise Info dynamically because it's params are dependent on the array passed into the function. I can't think of any other way to do this. I can change the logic to use cursors but even then, I would still need to build the raise info command dynamically. Since Execute cannot call Raise...well then the idea is kaputz.

I think things got confused when I posted :
'
BTW just a simple :
execute 'RAISE INFO ''test ''';

has an error...am still searching docs everywhere..
'
You see when I ran and debugged the function...execute _tempstring gave an error :

ERROR: syntax error at or near "RAISE"
LINE 1: RAISE DEBUG 'SKUID CatID ';

I couldn't figure out why....so a simple test would be just to execute a literal string (instead of a variable holding a dynamic one) with a raise command...turns out execute cannot run the raise command...

Thanks.

Regards,
calhoun

Hi,

The only problem I see with the function above is that you've missed some spaces in a couple of places - most importantly, this:

should be

Note the extra space after 'raise info'. Also, _tempstring := 'RAISE INFO'''; should probably have a space after INFO as well.

Having fixed that, and commented out the 'execute _tempstring' (which you don't need because RAISE can always handle dynamic strings), I get the following:

I would also make an unrelated suggestion - because Postgres folds unquoted identifiers to lower case, it's much easier to stick to lower case all the time rather than use mixed or upper. Trust me, it'll be easier in the long run, even if it does seem a little odd at first

Hi dave, thanks for the help...please consider the function and comments below. Thanks again for all the help....

create or replace function usf_temp()
returns void as
$BODY$
declare
_i integer;
_max integer;
_tempstring text;
_tempstring1 text;
_tempstring2 text;

_tstval1 text;
_tstval2 text;

begin

_tstval1 := 'THIS IS VALUE 1';
_tstval2 := 'ANOTHER VALUE FOR YOU';

-- this is just a test function for usp_PG_DUMPTABLE
-- note that in usp_PG_DUMPTABLE, we'd need to build the format field of raise dynamically since it will be dependent
-- on the array input parameter for the number of % characters within... if the array had 2 elements then '% %',
-- if the array had 3 elements then '% % %'
--- for each of the % character, in the format field, there must be a variable...test this out first if this works...

_tempstring := '''% | %''';

---test the contents of the variable...remove this later

raise info '%', _tempstring;

-- test a 'proper' raise info first...

raise info '% | %', _tstval1, _tstval2;

-- this will obviously work

-- now test that the format field of the raise info uses a dynamic string variable...

raise info _tempstring, _tstval1, _tstval2;

--errors here....raise cannot handle dynamic strings on the first parameter?
-- which means the core idea for usp_PG_DUMPTABLE cannot work since the format field there must be built dynamically to handle the
-- changing number of elements in the array parameter...

raise info '---';

return;
end;
$BODY$
language 'plpgsql';

select usf_temp();

calhoun wrote:Hi dave, thanks for the help...please consider the function and comments below. Thanks again for all the help....

You're welcome.

calhoun wrote:
_tstval1 := 'THIS IS VALUE 1';
_tstval2 := 'ANOTHER VALUE FOR YOU';

-- this is just a test function for usp_PG_DUMPTABLE
-- note that in usp_PG_DUMPTABLE, we'd need to build the format field of raise dynamically since it will be dependent
-- on the array input parameter for the number of % characters within... if the array had 2 elements then '% %',
-- if the array had 3 elements then '% % %'
--- for each of the % character, in the format field, there must be a variable...test this out first if this works...

_tempstring := '''% | %''';

---test the contents of the variable...remove this later

raise info '%', _tempstring;

-- test a 'proper' raise info first...

raise info '% | %', _tstval1, _tstval2;

-- this will obviously work

-- now test that the format field of the raise info uses a dynamic string variable...

raise info _tempstring, _tstval1, _tstval2;

--errors here....raise cannot handle dynamic strings on the first parameter?
-- which means the core idea for usp_PG_DUMPTABLE cannot work since the format field there must be built dynamically to handle the
-- changing number of elements in the array parameter...

Maybe it's because it's Friday evening and I'm feeling a little burned out, but I don't understand why you don't just build the output string for each row using the array upperbound to control a loop as you did in your previous example. In pseudocode, something like:

If you're columns aren't fixed width then you might need a simple function to space-pad them to the same length, but that should be fairly trivial.

Hope this helps

Dave.