I'm going to post this once in a new thread, then point people to this thread, as I've posted this information in numerous threads so far. It may be a candidate for a sticky - though that's down to the moderators.
There is an architectural limit in SD at 2GB. Originally the limit was at 1GB, but there was a way found to extend the SD standard to permit 2GB cards. Even so, some devices don't work properly with 2GB cards.
There are various 4GB "SD" cards on the market, but they break the original SD card specification. This can lead to problems in use. There is a new SD HC specification that, amongst other things, removes the 2GB limit - see this press release for details, including confirmation of the 2GB architectural limit in SD. However, at the moment, very few devices support SD HC. Some Panasonic devices do. SD HC compatible devices are likely to have the SD HC logo on them, as do SD HC cards.
At the time I originally wrote this post, no Canon cameras officially support SD HC. On 24 August 2006, Canon announced the A630, A640 and A710 IS, all of which have SD HC support. I suspect from now on, all new Canon cameras with SD slots will support SD HC, but check the specifications. Those that have asked Canon seem usually to get the response that 2GB is the maximum size of SD card that can be used in their cameras that don't explicitly claim SD HC support.
Partitions larger than 2GB require FAT32 support if you're going to use FAT. However, just supporting FAT32 isn't enough - you also need hardware compatibility with the card. There are no size related hardware compatibility issues with CompactFlash - apparently the specification doesn't contain any architectural limits on card size - so for CompactFlash, you just need to support FAT32 to be able to use cards larger than 2GB.
This may be why some think of the 2GB limit as being related to FAT32 support - older DSLRs that don't support FAT32 can't be used with 4GB or larger CompactFlash cards unless the cards have an ability to operate as a bank of 2GB cards using a switch.
Personally, I'd leave the 4GB "SD" cards alone. You may be fortunate in buying a 4GB "SD" card to get one that works without issues in all your devices (camera, card reader - possibly other devices such as a PDA). Problems with these cards may be subtle - it would be a disaster if all was well until you filled the card beyond 2GB, at which point some subtle corruption took place.
I'm not familiar enough with the SD and SD HC specifications (which are restricted distribution - to get full details even of the non-CPRM stuff you have to enter into an agreement with the SecureDigital Association) to know precisely what the details are. Depending on how the SD interface is implemented in the camera, it may be possible to add SD HC support with a firmware upgrade - but that's not saying Canon will provide such an upgrade. It could be that the SD support in the current crop of Canon cameras is implemented using a hardware component that can't be reprogrammed to support SD HC.
Reports that a particular combination works may be misleading. Many "brands" of SD cards are simply a label stuck on a card bought in bulk. There are far fewer manufacturers than there are brands of cards. In the past, even Lexar bought in cards, though a couple of years ago they started making their own.
Someone may buy a 4GB card from one of these rebranders, and post that it works. Someone else may read this result, buy a card of the same brand and find that it doesn't work because it's a card of a different design, possibly from a different manufacturer altogether.
The big two of the memory card world - Sandisk and Lexar - do not sell 4GB "SD" cards (I keep calling them "SD" cards because they violate the SD specification). At the time of writing, Lexar's range of SD cards stop at 2GB.
Sandisk sell a 4GB SD HC card, and have posted this FAQ on their web site. At the moment, you get a USB 2 SD HC reader with the card, presumably because most card readers aren't SD HC compatible.
SD HC apparently has an architectural limit at 32GB.