I guess the effects are supposed to show up normally but I can never get them to appear in the game.
That's because there's a hidden a requirement for the pixie and firefly shows to spawn. There are two requirements for the light show: it must be the correct time of day (23 by default) and the lot must contain a Wishing Well. You can only see and remove the requirement for the wishing well by looking at the script for the object. However, we don't currently have a Wishing Well in game. It might be a future premium content item, or it might be that the Wishing Well was scrapped all together.
I tried to make the pixies and fireflies spawn with with fountains and the Fountain of Youth from Hidden Springs, but those don't work. So I wrote a mod to "fix" it (It requires an object scripting mod), and it's currently in the moderation queue at ModTheSims. I will add it to my blog as well, but I forgot to save a copy of the post so I have to write it from scratch, which means I'll need more time.