Messages posted by: davep

pgFrank wrote:I was trying to install 8.3 build 6 on my laptop (OSX 10.4.11, 2GB mem) but that didn't work:
FATAL ERROR: Failed to initialise the database cluster

A second try looked ok, no errors during installation, but I can't start the server:
waiting for server to start... done server started EnterpriseDB Postgre 8.3 server did not start in a timely fashion, please see /Library/ EnterpriseDB/postgres/8.3/data/pg_log/startup.log for details.

This log is missing, I can't find it anywhere, doesn't exist at all.

Do you have any idea how to fix these problems?

Hi,

The installer should have written a logfile to /tmp/edbpg-8.3-server-postflight.log - can you post the contents please?

Also, as linuxNerd has mentioned, please post the output of sysctl -a

Thanks, Dave

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.

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

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.

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

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/

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.

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

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.

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

Anonymous wrote:
also, instead of the fancy java installer an rpm repository from which I could install/upgrade with yum would be far more usable

We are currently working on cross-platform RPMs and a .DEB distribution for users that prefer the platform-standard package manager over the GUI installer.

Anonymous wrote:I cant make slony1 cluster. pgadmin III signals error, slony1 creation scripts not available. i am using -b1.zip version. some slony sql scripts are installed but pgadmin doesnt seems to be able to find them

Hi,

In pgAdmin, please select:

File -> Options

And assuming you installed EnterpriseDB Postgres in the default location, set the Slony-I Path setting to:

C:\Program Files\EnterpriseDB Postgres\8.2\share

If you installed to a different directory, you will need to adjust the path accordingly. I'll request the installer team fix the default value for the next beta.

Anonymous wrote:Just wonder if I have an app running on enterprise db advanced server would it be fully compatible with your db postgres version?

As long as you haven't used any of the Oracle compatibility functionality, then yes, it should be.

You won't have Dynatune or any of the other performance tweaks in EDB though.

Peter wrote:We have built FC7, we have not tested on it yet, as soon as we do, we'll add it to the list.

Thanks Peter - just trying to keep you all on your toes

ashaar wrote:I have just edited my previuous post. I think these got missed out from the list. Thanks pointing it out.

Thanks!! Should I mention Fedora 7?

Peter wrote:We have it working, you can expect to see this sometime in August.

I'll be interested to see the demand for this.

Those that guess who I am (c'mon people, it's not that hard :-p) will never believe this, but I'm looking forward to it.

ashaar wrote:Linux 32 bit installer has been tested on the following platforms:
openSUSE 10.x
Ububtu Dapper Drake
Ububtu Feisty Fawn
Debian Etch
Slackware
RHEL 4

What, no Fedora?