Postgresql - SELECT FOR UPDATE example

The Postgresql documentation says:

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be blocked until the current transaction ends.

Let's see a little and easy example just to verify if this is true or not :-)

First consider this table (very very simple):

   1 CREATE TABLE "public"."example" (
   2   "id" INTEGER
   3 ) WITHOUT OIDS;

Now insert just one row inside this table, then use this script to create a pl/pgsql function that will act as a transaction (stored functions always run in a transaction) in our example:

   1 CREATE OR REPLACE FUNCTION "public"."for_update_test" (
   2 )
   3 RETURNS integer AS
   4 $body$
   5 DECLARE
   6 
   7         id_query INTEGER;
   8 
   9 BEGIN
  10 
  11         RAISE NOTICE 'START AT: %',timeofday()::timestamp;
  12 
  13         SELECT id FROM example LIMIT 1 INTO id_query FOR UPDATE;
  14 
  15         RAISE NOTICE 'SELECT PERFORMED AT: %',timeofday()::timestamp;
  16 
  17         PERFORM pg_sleep(50);
  18 
  19         RAISE NOTICE 'END AT: %',timeofday()::timestamp;
  20 
  21         RETURN 0;
  22 END;
  23 $body$
  24 LANGUAGE 'plpgsql'
  25 VOLATILE
  26 CALLED ON NULL INPUT
  27 SECURITY INVOKER
  28 COST 100;

Then run the function with Select for_update_test(); inside a SQL prompt of pgAdminIII or any other environment.

After something like 25 seconds make another Select for_update_test(); in a second prompt as I did in the following:

(Please click on the image to see full size)

In the left prompt you can see that the SELECT FOR UPDATE is immediately performed after the transaction begins.

In the right prompt you can see that the SELECT FOR UPDATE is performed only after the first transaction ends.

So, actually the SELECT FOR UPDATE locks the only row of our table.

If you want you can change the SELECT FOR UPDATE adding the NOWAIT clause:

SELECT id FROM example LIMIT 1 INTO id_query FOR UPDATE NOWAIT;

and run the small experiment again. In this case the second function call will return an error because the NOWAIT clause does exactly what its name suggests: it doesn't wait if it encounters a locked row.

In the function I used the instruction timeofday()::timestamp because the NOW() function or the statement CURRENT_TIMESTAMP always return the same Date Time if used inside a transaction. More on this here http://kaiv.wordpress.com/2007/11/02/getting-current-time-inside-a-transaction/

System.Threading.Timer and Garbage Collector

I would like to tell you the problem i ran into some time ago with the System.Threading.Timer.

I used this Timer in a software where periodically i have to retrieve data from one or more solar inverters by a serial port.

Well, at first I declared and initialized the timer in a method (the constructor but this is not relevant), so i wrote something like this:

System.Threading.Timer Tim = new System.Threading.Timer(CallbackMethod, null, 0, Milliseconds);

My program started to have a strange problem. When I ran the software it suddenly stops after a while without raising any exception and without crashing.

For a lot of time i was sure that the problem was in the callback, the place where i ask data to the inverter.

But the problem was not there. At the end i read on msdn:

Note
As long as you are using a Timer, you must keep a reference to it. As with any managed object, a Timer is subject to garbage collection when there are no references to it. The fact that a Timer is still active does not prevent it from being collected.

So the solution is to declare the Timer outside the method, in the same class or as a static object somewhere else. The important thing is that the Timer object does not go out of scope, otherwise the Garbage collector will remove it from the memory.

This explains also why the program wasn't raising any exception. The callback never references the Timer in no way so it just was not executed.

How to enable wcf messages compression in Silverlight (4) and IIS 7

There are many tutorials that explain how to do this. But these are very comprehensive for

the many platforms that can be envolved.

Here i will talk only about my particular situation:

• Silverlight 4
• A Wcf Service with the default visual studio 2010 binding configuration:

   1 <configuration>
   2     <system.serviceModel>
   3         <bindings>
   4             <customBinding>
   5                 <binding name="CustomBinding_ServizioVisualizzatore" >
   6                     <binaryMessageEncoding />   
   7                     <httpTransport maxReceivedMessageSize="2147483647" maxBufferSize="2147483647" />
   8                 </binding>
   9             </customBinding>
  10         </bindings>
  11         <client>
  12             <endpoint address="http://localhost:4534/Visualizzatore.Web/ServizioVisualizzatore.svc"
  13                 binding="customBinding" bindingConfiguration="CustomBinding_ServizioVisualizzatore"
  14                 contract="Servizio.ServizioVisualizzatore" name="CustomBinding_ServizioVisualizzatore" />
  15         </client>
  16     </system.serviceModel>
  17 </configuration>

• IIS 7 on Windows Server 2008 32 bit

Let's begin! First of all you won't have to touch your code, everything is done with one or two settings inside IIS.

IIS will send the data you asked to it( making a call to the web service) to the client in gzip format if the client says that it can manage compressed responses.

How can we see if our client Silverlight application is 'happy' to receive a gzipped response?

Well, let's install Fiddler and have a look at the http request of the service call:

(Please click on the image to see full size)

In the Request headers of the wcf call you can read 'Accept encoding: gzip, deflate'.

It means that the client is saying that it can accept a compressed response.

Below you can see (Transformer tab of the http responses pane) that the content of the response is actually gzipped as we desire.

The last thing to note is the Content-Type of our http request. It is set to 'application/soap+msbin1', we'll use it later.

Now, it's time to set some options within IIS 7:

(Please click on the image to see full size)

The screenshot (Italian language) shows the icon that you should press to open the section where you can enable static and dynamic contents compression.

After this you should open the IIS applicationhost.config file. You should find it in the 'C:\Windows\System32\inetsrv\config' directory.

Here you can add the line '' (please note "application/soap+msbin1") in the httpCompression - dynamicTypes section of the file obtaining something similar to this:

   1 <httpCompression directory="%SystemDrive%\inetpub\temp\IIS Temporary Compressed Files">
   2   <scheme name="gzip" dll="%Windir%\system32\inetsrv\gzip.dll" />
   3     <dynamicTypes>
   4       <add mimeType="text/*" enabled="true" />
   5       <add mimeType="message/*" enabled="true" />
   6       <add mimeType="application/x-javascript" enabled="true" />
   7       <add mimeType="application/soap+msbin1" enabled="true" />
   8       <add mimeType="*/*" enabled="false" />
   9     </dynamicTypes>
  10     <staticTypes>
  11       <add mimeType="text/*" enabled="true" />
  12       <add mimeType="message/*" enabled="true" />
  13       <add mimeType="application/javascript" enabled="true" />
  14       <add mimeType="*/*" enabled="false" />
  15     </staticTypes>
  16 </httpCompression>

Now it should be all done :-). Alternatively you could also set <add mimeType="*/*" enabled="true" /> to enable compression for all kinds of content.