Older blog entries for alvherre (starting at number 17)

10 Sep 2009 (updated 10 Sep 2009 at 16:06 UTC) »
Update: This was supposed to be posted elsewhere. It seems Advogato lacks a way to delete posts, so I'm now forced to pester readers with some spanish junk.

Se acaba de publicar una nueva serie de versiones actualizadas de PostgreSQL para todas las ramas soportadas, desde 7.4 hasta 8.4. El anuncio en español apareció, entre otros, en postgresql.cl. (Y este es el anuncio oficial en la lista pgsql-announce). Se recomienda a todos actualizar a esta versión.

Y recuerden que para Windows sólo están soportadas las ramas 8.2, 8.3 y 8.4. En las nuevas versiones se corrigió finalmente el problema que daba lugar al error “could not reattach to shared memory”, así que los usuarios de Windows son los que estarán más contentos.

Disfruten.

14 Jul 2009 (updated 14 Jul 2009 at 22:24 UTC) »


sprintf in SQL

Ever needed to print out something with embedded data? Not an uncommon requirement, I'm afraid. Most people just concatenate stuff together:

SELECT 'A number ' || 42 || ' is a ' || 21 || ' * ' || 2 || ' by any other name'

Eventually it turns out confusing, as it's easy to visually mix the concatenation operator || with the quotes.

PL/pgSQL offers a printf of sorts ... except that you can't use it everywhere you'd like:


RAISE NOTICE ' A number % is a % * % by any other name', 42, 21, 2;


How to capture such a thing so that you can, for instance, use that as a return value? Well, that's what most languages call an "sprintf" function or %-expansion. It's a trivial technique really.

So you want it in SQL? Well, here it is, as a PL/pgSQL function for maximum portability.


CREATE OR REPLACE FUNCTION printf(fmt text, variadic args anyarray) returns text
language plpgsql AS $$
DECLARE
argcnt int = 1;
chrcnt int = 0;
fmtlen int;
CHR text;
output text = '';
BEGIN
fmtlen = LENGTH(fmt);
LOOP
chrcnt = chrcnt + 1;

-- ran out of format string? bail out
IF chrcnt > fmtlen THEN
EXIT;
END IF;

-- grab our char
CHR = substring(fmt, chrcnt, 1);

-- %% means output a single %, and skip them
IF CHR = '%' AND substring(fmt, chrcnt + 1, 1) = '%' THEN
output = output || '%';
chrcnt = chrcnt + 1;
continue;
END IF;

-- a % on its own means output an element from our arg list
IF CHR = '%' THEN
output = output || COALESCE(args[argcnt]::text, '');
argcnt = argcnt + 1;
continue;
END IF;

-- no special case? output the thing
output = output || CHR;
END LOOP;

RETURN output;
END;
$$;


This is not as flexible as the true sprintf function found in C and variants, which allow you to do neat stuff like fill some variables to fixed widths, or specify number of digits after the decimal point for non-integer values. But it's already quite useful. Furthermore, if you really need a given number of decimal you can add a cast to NUMERIC, which you can't do in C ...

The biggest problem it has is that any argument that's not implicitly castable to text must carry an explicit cast. The good thing is that by adding casts you can even print out complex structures like records or arrays.


# select printf('% is a % and % is a %, but %% is a %',
'hello', 'word', 1::text, 'number', 'percent sign');
printf
------------------------------------------------------------
hello is a word and 1 is a number, but % is a percent sign
(1 fila)

alvherre=# select printf('% %% is %',
3.01110001100101::float::numeric(10,2)::text,
ROW(1,2,3,'foo bar')::text);
printf
-----------------------------
3.01 % is (1,2,3,"foo bar")
(1 fila)


I'm sure you can find more interesting uses that than one!

I've uploaded this function to our growing collection of code snippets in our Wiki, so that it won't be lost in the dark corners of this blog.

My take on «Moderating Majordomo Lists with Vim and Mutt»

David,

What I do instead is have these lines in .mutt/muttrc:


# Useful macros for Majordomo list administration
macro pager R "|~/bin/majordomo-reject\nd" "Majordomo reject"
macro pager A "|~/bin/majordomo-accept\nd" "Majordomo accept"

And then majordomo-accept and majordomo-reject are symlinks to a Perl program that looks like this:


#!/usr/bin/perl


use warnings; use Net::SMTP;

if ($0 =~ /accept/) { $action = "accept"; $fullaction = "Accepting"; } elsif ($0 =~ /reject/) { $action = "reject-quiet"; $fullaction = "Rejecting"; }

die "No mode defined" unless defined $action;

while (<>) { if (/^Subject: ([0-9A-Z-]{14}) : (CONSULT|REMINDER)/) { $token = $1; last; } }

die "No token defined" unless defined $token;

$smtp = Net::SMTP->new('localhost') or die "new: $!";

$smtp->mail('alvherre@surnet.cl'); $smtp->to('majordomo@postgresql.org');

$smtp->data; $smtp->datasend(<<END_OF_MAIL); From: Moderation Robot <alvherre\@alvh.no-ip.org> To: postgresql.org's Majordomo <majordomo\@postgresql.org> Subject: $fullaction token $token

$action $token END_OF_MAIL $smtp->dataend(); $smtp->quit;

The main upside to this approach is that I don't have to hit "reply" to the email; I can just hit "R" or "A" on the mutt message view.

14 Jun 2007 (updated 14 Jun 2007 at 15:06 UTC) »

O, the joy of these days

Two nights ago I was tired enough to decide that I wanted to upgrade my system. See, this shouldn't be much of an issue — I do that pretty regularly, random packages are automatically upgraded to the newest versions in Debian testing, nothing changes too much, and life is good.

Most of the time.

That night was one of the times that make the previous phrase start with "most of" and not "all".

What I typically do is launch an aptitude upgrade and go to sleep. Which is what I did on that fatidical night. Hilarity ensued the following morning when I got a mail from my early cvsup cronjob, which succintly said


Segmentation fault

"Bah", I said to myself. "Something unexpected must have happened. Care not, because I will run it by hand and all will be well". How ingenuous of me; because when I ran it by hand, not all was well at all! Quite the opposite in fact. I got the same


Segmentation fault

But I surely can fix this little annoyance, can't I? "Sure I can", I eased myself. "I'll just run the crasher under GDB and quickly discover the failure". My ingenuity was still blinding me. Reality struck not long after that:

$ gdb cvsup
(gdb) run -g cvsup.pgsql
Starting program: /home/alvherre/bin/cvsup -g cvsup.pgsql
warning: Lowest section in system-supplied DSO at 0xffffe000
is .hash at ffffe0b4
(no debugging symbols found)
  [repeated about 15 times]


Program received signal SIGSEGV, Segmentation fault. 0xf7285578 in ?? () (gdb) bt #0 0xf7285578 in ?? ()Cannot access memory at address 0xf86cd0

If I have ever seen a more useless backtrace, it must have been in a previous life.

Of course, this cvsup binary was compiled in a machine that I no longer have, the compiler itself is hard to find, let alone compile, so generating anew cvsup binary is probably out of the question; or at least, it will take a very long while to do.

So here I am, pondering whether I should instead try to run this binary in ai386 Sarge chroot jail that I have lying around (which I use for those peskyAdobe Flash wannabee-webapps), waste my time trying to get a new CVSup binary, orjust give up and start using rsync to fetch the Postgres repository instead.

And it promised to be such a lovely, cold, fire-enjoying morn.

It must have been one of those days I should have stayed in bed, becauselater I had to go out to deliver a letter (yes, that dead tree stuff that makes you go out somewhere and pass it by hand to someone else to take care of); a matter of minutes, I said, so I grabbed my bike and pedaled all the way to the bus stop; chained the bike, delivered thestuff quickly and as I went back to unchain the bike ... the key broke in the lock. So I had to walk home, get my pliers, walk back, and dissassemble the lock. Thiswas a matter of a minute or two (not including the walks), after which I felt really safe about that lock. Of course, I trashed it.

So I think Mother Nature must be against me for some reason. And I think Iknow why: it's probably because I haven't been taking any photos. And whywould that be, you might ask? And I might answer: it is because last friday,as we were going out for the Holden concert, I dropped the bag where I keep my camera — and no! You don't need to guess. I probably went pale for abit, but there was no one there with another camera to take a picture of the event. Colors returned to my face as I observed that the only thing that hadbroken was the UV filter. But still, no one sells UV filters in this little city, nor anyphotographic equipment at all really. So I'm stuck without photos until I can getsomewhere civilized, where they do have stores.

Now, you would say all these things are not really all that much of a problem. And you might even be right. What's more, I would have agreed! So, not havingenough problems, yesterday night I decided that I wanted to upgrade the old fashionedGaim to the new, shiny, non-patent-encumbered, non-trademark-infringing Pidgin. After the process, which was pretty quick, I restarted the thing in order tohave better icons to look at. And now the problem comes — because after the upgrade, Ican't connect to either jabber.commandprompt.com or jabber.postgresql.org. For all intents and purposes, I'm offline.

So this morning I started a good fire first thing after waking up, just to make sure that cold won't be a problem today. Because there will be others — I am sure. And I will leave the camera at home and stay very far away from it so that it doesn't suffer any more damage.

13 Mar 2007 (updated 13 Mar 2007 at 13:19 UTC) »


Magnus,

You just mentioned that there is a facility for translating news, quotes and events in the PostgreSQL website. We (the spanish community) want to know: how does one go about doing that? I looked around the site a bit, and I can't seem to find any link that would allow us to do that.

If there was also a way to translate the whole website, akin to Debian's site which is fully i18n'd, that would surely rock.

Thanks.

Hotmail shipping delivery failure reports with BOM

I just noticed that some messages that I've been discarding thinking
they were spam were in fact delivery failure reports from Hotmail.

The problem is that they show up with an empty header, and my MUA shows
a sender of "@". More junk, I thought, and deleted them on sight
without opening. A couple of days ago I opened one of them and saw
this in the body:


rom: postmaster@mail.hotmail.com
To: pgsql-es-ayuda-owner+M24188@postgresql.org
Date: Thu, 08 Feb 2007 06:23:21 GMT
MIME-Version: 1.0
Content-Type: multipart/report; report-type=delivery-status;
boundary="9B095B5ADSN=_FF160181E5E3413A96EDC9046EF?WOM1.labinte"
X-DSNContext: 7ce717b1 - 1196 - 00000002 - 00000000
Subject: Delivery Status Notification (Failure)
Return-Path: <>


Wow, I thought, this looks suspiciously similar to a mail header. Note the missing F in "From: " though.

I opened it on vim and saw this:
<feff>From: postmaster@mail.hotmail.com

A Byte-order-mark :-( Apparently someone at Microsoft is dumb enough to stash that thing in front of their mail.

Saturating the market with the idiotic Windows stuff was not enough -- now they have to inflict upon us a new level of stupidity.

I wonder when will come the day when all mail generated in Microsoft systems is incompatible with everyone else. For now, I suggest the poor fellows using Hotmail to switch to something else (Gmail.com seems very popular these days. If you want an invitation, just ask.)

Buildfarm struggles

It seems the buildfarm has been failing not only on Win32, but also on other machines! The fact is that on Win32 the failure is only much more probable. So probable, in fact, it seems to happen everytime.

The failing test is the stats test, which tries to measure whether the stat system is actually counting operations on the tables. Initially it was thought that autovacuum was the cause, but some investigation suggests that not to be the case.

Magnus has been helping me pinpoint the problem. The first thing we tried was to have autovacuum use a "reasonable" setting for vacuum_cost_delay. "Give it 10 milliseconds," I told him, confident that such a low setting was enough to cause the scheduler to let the stats system to run and thus increment the counters. The theory was that autovacuum being enabled caused stats not to have time to run in the 2 seconds that the test sleeps.

It didn't work though, so he raised it to 100ms and then 1000ms, to no avail. The test still failed.

Next, he raised the 2 seconds sleep to 10 seconds. It didn't work either. So he turned autovacuum off, and reran the test. Guess what? The test still failed!!

Then he checked the test manually, and it turned out that the pgstat views show the table to always have counters on 0!

We haven't been able to pinpoint the exact cause, but now it's looking like the autovacuum change wasn't the culprit; maybe it was the autovacuum change plus something else. We're not sure.

Still investigating ...

20 Jan 2007 (updated 20 Jan 2007 at 01:08 UTC) »


It looks like my McNaught comet photos now made into NowPublic: The comet tail seen round the world. Fact is, I was pretty impressed when I saw it. It was certainly quite amazing.

On other news, I managed to get the whole buildfarm in red for several hours with a pg_regress patch. The fix to the problem was quite simple, but since I'm spending some time researching a new appt. to move to, it took longer than expected. Sadly, all the Windows machines are still failing a test with amazing reproducibility. We need some Windows hacker involved in order to fix it though ... the failure is pretty bizarre.

18 Oct 2006 (updated 18 Oct 2006 at 18:50 UTC) »
a couple o'pics

FYI, I've uploaded some of the pictures I took at the PostgreSQL Anniversary Summit. In doing so I hit the Flickr limit for free accounts, so I'll continue next month :-P

They can be seen here.

A nice gift beats dollars

Got a new book collection today. Yay! Huh, and that has to do with a PostgreSQL-related blog exactly what, you ask?

The story goes like this. Back in 2004, I went to Caracas, Venezuela, for a couple of weeks. During my stay, I was asked to do some consulting for a courier company that was using PostgreSQL for its mission-critical database. It was getting very slow, they whined, and had been approached by DB2 salespeople to replace it. The company that did the Linux support for them was not very fond of ditching their free database for some proprietary stuff, so they asked me if I could have a look and try to "make it faster".

Turns out they were running PostgreSQL 7.1, which was already quite old, and had some horrible queries and very big tables. Most of the queries did not properly use indexes, so their I/O channels were saturated by the constant seqscans. They needed a lot of tuning, caring, and query refining; but what they needed the most was an upgrade. So that's what I focused on at that time.

Of course, I was paid for this work.

When I got home, however, they were still having some issues, and on IM I kept giving suggestions on how to improve queries, pointing the appropriate indexes to create, suggesting FSM configuration improvements and proper vacuum scheduling, etc. This went on for almost two years. In those months I never saw a dime from them and I didn't really care, because it was entertaining most of the time, and a good experience; and I already had Command Prompt's wage, or EnterpriseDB's before that, so it felt a bit unethical to behave like we had a support contract.

A couple of weeks ago, however, they were having problems again, queries were taking too long, customers were starting to be annoyed and a pretty girl was starting to pull her hair out — so one sunny saturday her and I sat down and studied some of the problems and finally figured'em out. Just before I left, I tossed my (shameless plug) Amazon wishlist URL to give her an idea.

Today I got this:

For you the not-curious-enough-to-click, that's "The Complete Calvin and Hobbes". That's right, ten kilogram and ten years of Calvin and Hobbes in the most beautiful hardcover edition I can imagine. Who wants two years of support's money? I'll take a nice gift like this any day. This one will keep me busy for quite a while. Thank you Jacqueline!

Don't get me wrong — I, like most of you, need the dollars anyway for paying the rent and other mundane stuff. I also enjoy being able to eat :-) and will happily continue working on stuff I don't quite enjoy as much as doing PostgreSQL support over Jabber, just to have that privilege. I still have fun by hacking on the challenge that is Mammoth Replicator. But I'm a Calvin and Hobbes lover and things like these cannot be beat by mere checks.

8 older entries...

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!