Creating a PostGIS database

Inside the pgAdmin tool, run
CREATE DATABASE my_spatial_db TEMPLATE=template_postgis

More details here

Reinstalling PostgreSQL - deleting the "postgres" service account

I was playing around with PostgreSQL 8.4 for my personal project. After my initial installation, I uninstalled it and tried to reinstall it. During the installation, I was prompted to enter the password for the "postgres" service account, which I don't remember. To continue with the installation, I needed to enter the following command and create a new password

net user /delete postgres

Problems creating a JDBC resource with Glassfish 3 and PostgreSQL 8.4 drivers

What I initially thought was a straight-forward task turns out to be more complex than I imagined. After dumping the jars for PostgreSQL 8.4 driver (postgresql-8.3-701.jdbc3.jar
, postgresql-8.4-701.jdbc4.jar) into <GLASSFISH>/domains/domain1/lib/databases, I started the GF 3 server and tried to create a connection pool. I tried to create a javax.sql.ConnectionPoolDataSource resource using org.postgresql.ds.PGConnectionPoolDataSource. However I kept getting this error:

Can't find ConfigModel.Property for attr -isolation-level-guaranteed on interface com.sun.enterprise.config.serverbeans.JdbcConnectionPool

I get this error even when I do not specifiy "guranteed" for the isolation level.

Googling around,  I finally found someone with a similar problem. Seems that there is a bug in some of the earlier builds, so I downloaded the latest build (build 70)and reinstalled GF3.

All's well? Not quite. When I tried to start the server, I get some problem with using port 7676 for the default JMS host. I dont think I've gotten this error before in the older builds. Well, I did change the http-listener-2 port in domain1's domain.xml, not sure if I introduced some typo error there, so I ran

asadamin verify-domain-xml

and checked that the domain.xml is ok. The error is something along the line of

java.net.SocketException: Address family not supported by protocol family: bind

It turns out that for some reason the new GF3 build has problem using Vista's default hosts file (at C:\Windows\System32\drivers\etc).

To get it to work, change

::1 localhost

to

127.0.0.1 localhost
#::1 localhost

Restart the server again.

Now proceed to create the connection pool. However, according to the PostgreSQL driver download website,

It comes in two flavors, JDBC3 and JDBC4. If you are using the 1.6 JVM, then you should use the JDBC4 version.

.....

JDK 1.6 - JDBC4. Support for JDBC4 methods is limited. The driver builds, but the majority of new methods are stubbed out.

So, you'll need to specify an additional property JDBC30DataSource and set it to true when configuring the connection pool. That should be about it, now you can get some real work done.

Important task completed - lessons learnt

Finally completed the important task early this week, the tight deadline didn't allow for much room for errors, but this year's job seems especially problematic. The documentation is quite outdated, and the scheduled timing of the jobs are way off. This affected with the planned execution, and the problem was exacerbated by the shorter working days due to Chinese New Year. Then the network was constantly disconnecting, so there was a need to constantly monitor and rerun the jobs. And it's my first time running the jobs, so needed a lot of help from users, colleagues and some trial and error.

Overall, I did learn some things:

1. Creating an index on database tables is not sufficient to ensure fast queries. Always check the execution plan to see if the index is being used, as sometimes overindexing will cause Oracle to use a full table scan instead.


2. Be careful of ending commands and statements in SQL Plus with "/" instead of ";". If a command such as "spool C:\file.txt" is ended with "/", the last run statement will be run again, which can be a problem.


3. If the rollback segment is insufficient, it may be possible to split the query into smaller batches to run and thus not trouble the DBA.


4. Always check if an object with the same name already exists when creating new objects in scripts and do not commit in the script to save some headaches.


5. Check if CPU usage in Win XP is a bottleneck by checking if the TaskManager constantly reports a usage of 100%.

phpBB forum - using the default DB object

Have been doing a little freelance lately, mainly involving integrating new functionality with phpBB.
Using the existing code speeds up development and minimizes redundant code. Below is the code needed to use the default database object in phpBB.

[php]
define('IN_PHPBB', true);
$phpbb_root_path = './../';
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);

$sql = "SELECT * FROM " . A_TABLE . "
WHERE rank_special = 1
ORDER BY a_title";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR,
'Could not obtain ranks data', '', __LINE__, __FILE__, $sql);

}
else echo "Query successsful";


At least I can say I have some Oracle experience

A lot of my present work revolves around generating reports and listings from Oracle, so my SQL skill has improved somewhat from before. I've also gained some experience with writing PL/SQL, working on SQL*PLus, SQL Loader and a bit of simple database administration. Lots of odds and ends and nothing fancy. If I leave this job at least I can say that I have around 6 months of experience working on Oracle. It's peanuts in terms of experience but at least it's better than nothing I guess.

I've never worked on very large data sets before but now it's a daily affair. Read up on indexing to help speed up queries but I'm still lagging behind in my knowledge of things like SQL tuning. Since much of my work revolves around the database, so the faster the queries on the database, the faster I clear my stuff and go home.

TOra is a competent replacement for TOAD, and if you're looking for a tool to replace SQL*Plus this is definitely it.