How to install postgres 8.4 on Debian 5 (lenny)

Posted by Eric Bartels

Because I didn’t found this information in one place I’m writing this quick “guide” on how to install PostgreSQL 8.4.x on Debian 5.x “lenny”.

Debian lenny stable packages are bundled with Postgres 8.3.x but thanks to Debian Backports the newer Postgres version is available for lenny.

Simply do it

Add the backports-sources (/etc/apt/sources.list) and update (apt-get update)

deb http://www.backports.org/debian lenny-backports main contrib non-free

Install postgres

apt-get -t lenny-backports install postgresql-8.4

Updating existing lookup fields in SharePoint

Posted by Eric Bartels

Creating lookup-fields only via XML-Definition is not possible. You need the ID of the Web hosting the list and the ID of the list hosting the data the lookup field is connected to. This information can only be retrieved when both entities are created…

Object model?!

When looking into the SPFieldLookup-class you find the properties LookupField, LookupList and LookupWebId. LookupWebId and LookupField can easily be set via the object model. The documentation says that LookupList is read and writeable which is not true. Setting LookupList throws an exception stating out “Cannot change the lookup list of the lookup field.”.

SchemaXml and Regex to the rescue

The SchemaXml-property of a SPField can be used to manipulate the LookupList. One stumbling block is that you do not know the Guid of the LookupList currently stored in the schema. Even if you define a Guid inside your xml-field-definition SharePoint puts in an other Guid. Here a simple regular expression comes to help.

The code for connecting a site-column with a list somewhere in some web:

// Declarations (myWeb is the context given by a feature receiver scoped "web")
var rx = new Regex("List=\"([^\"]*)\"", RegexOptions.IgnoreCase | RegexOptions.ECMAScript);
var root = myWeb.Site.RootWeb;
 
// myListInstance is an instance of a SPList existing somewhere
var lookupField = (SPFieldLookup)root.Fields["MyLookupField"];
var xml = lookupField.SchemaXml;
xml = rx.Replace(xml, String.Format("List=\"{0}\"", myListInstance.ID.ToString("B")));
lookupField.SchemaXml = xml;
// Do not call update here!
// Quote from MSDN "Do not call the Update method when using the SchemaXml  property to modify a field."
 
// Fetch the field again and apply the web-id
var lookupFieldReFetch = (SPFieldLookup)root.Fields["MyLookupField"];
lookupFieldReFetch.LookupWebId = myWeb.ID;
lookupFieldReFetch.LookupField = "Title";
lookupFieldReFetch.Update(true);

This problem still persists in the beta of SharePoint 2010. I hope this will get fixed in the RTM. Creating a lookup-field-connection is far more easy by the way …

MIN, MAX innerhalb eines SELECT-Statement

Posted by Sven Thämar

Manchmal benötigten man sowohl den kleinsten (min) als auch den größten (max) Wert einer indizierten Spalte.

SELECT MIN(id)
     , MAX(id)
FROM big_table

Bei diesem SELECT kommt zwar das richtige Ergebnis, dennoch kann die Antwortzeit sehr lange dauern. Der Grund liegt in einem FAST FULL INDEX SCAN.

Besser ist folgendes:

SELECT MIN(id) min_value
FROM big_table
UNION ALL
SELECT MAX(id) max_value
FROM big_table

oder

SELECT (SELECT MIN(id) from big_table) min_value
       (SELECT MAX(id) from big_table) max_value
FROM dual

Bei beiden alternativen wird ein FULL INDEX SCAN durchgeführt und liefert das Ergebnis (fast) sofort zurück.

Den Umweg sollte man bei großen Tabellen und wo es auf Geschwindigkeit ankommt wählen.

Blockierende SQL-Session

Posted by Sven Thämar

Folgendes SQL-Statement kann man verwenden wenn man prüfen möchte ob es blockierende Sitzungen gibt.

SELECT
     , b.username blocking_username
     , b.osuser blocking_osuser
     , b.machine blocking_machine
     , b.program blocking_program
     , w.sid waiting_sid
     , w.username waiting_username
     , w.osuser waiting_osuser
     , w.machine waiting_machine
     , w.seconds_in_wait
  FROM v$SESSION w
     , v$SESSION b
 WHERE w.blocking_session is not NULL
   AND b.sid = w.blocking_session
ORDER BY w.sid

Das Statement kann jederzeit um weitere Spalten erweitert werden.

Fixing “error while loading shared libraries: libgssapi_krb5.so.2: cannot open shared object file: No such file or directory.”

Posted by Eric Bartels

One of my development systems is running on Debian Lenny (no this one does not host SharePoint). After update the system via apt-get update something went wrong and “core services” (samba, ssh) were out of order.

It seems this is a bug in the packages provided by Debian. After struggling for a while a simple

apt-get install libkrb53 --reinstall

fixed the problem and everything works again.

Next Page »