Turing tarpits also refer to programming languages that have been specifically contrived so as to make even the most trivial tasks incredibly tedious, while at the same time having a proof (of Turing completeness) that assures you that any computation you are trying to implement is actually possible. This is usually done by going to such a low level that the details of the problem are swamped in the details for performing trivial tasks (compare with emk's definition above), or by making certain important things possible only by going through some horrible contortions.
Examples of horrors of this kind include brainfuck, INTERCAL, Unlambda, and Befunge. Apart from being masochistic exercises that make people believe that some programmers are totally deranged, they do serve a useful purpose for people into a serious study of programming. These languages usually incorporate discarded or unripe concepts for programming that may change the way one looks at problems and thinks while in the act of programming.
Just don't attempt to code any major application using them!