Backend dies upon UPDATE of DATE field with CASE WHEN, 6.5.3 - Mailing list pgsql-bugs

==============================================
                       POSTGRESQL BUG REPORT TEMPLATE
==============================================

Your name        :    Emils Klotins
Your email address    :emils@mail.usis.bkc.lv


System Configuration
---------------------
  Architecture (example: Intel Pentium)      :
Intel Pentium II, 128MB RAM

  Operating System (example: Linux 2.0.26 ELF)     :
Linux 2.0.36 ELF, RedHat 5.2 distribution

  PostgreSQL version (example: PostgreSQL-6.5.3):
PostgreSQL-6.5.3

  Compiler used (example:  gcc 2.8.0)        :
gcc 2.7.2.3

Please enter a FULL description of your problem:
------------------------------------------------

1. I have a table with the following structure:

CREATE TABLE "authors" (
        "author_id" int4 DEFAULT nextval ( 'author_ids' ) NOT
NULL,
        "firstname" text NOT NULL,
        "firstname_original" text,
        "lastname" text NOT NULL,
        "lastname_original" text,
        "alias1" text,
        "alias1_original" text,
        "birthdate" date,
        "birthplace" text,
        "birthplace_original" text,
        "deathdate" date,
        "deathplace" text,
        "deathplace_original" text,
        "commentary" text,
        "address_id" int4);

2. Attached is the authors table dump I got from:
pg_dump -a -t authors mydb > auth.data (approx 0.5KB)

3. If I try the following update in the psql monitor:

SET DATESTYLE TO 'German';
UPDATE authors set firstname='dasd',
lastname='asdadasd',
birthdate=(CASE WHEN (text '12-12-1956')=(text '') THEN
NULL ELSE '12-12-1956' END),
birthplace='asdasd',commentary='',
lastname_original='asdasd', firstname_original='asdfg',
birthplace_original='' WHERE author_id=56

I get UPDATE 1, however, on all subsequent UPDATEs for that
row, backend dies with the following error message.

> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated
abnormally
>         before or while processing the request.
> We have lost the connection to the backend, so further
processing
> is impossible.  Terminatin g.

4. I also get the same error if after UPDATE I try to SELECT
the address_id field from that row (including of course trying to
"SELECT *" ). If I try to select birthdate from that row, I will get
a date of '01-15-2000' no matter what the UPDATE statement
should have made it. (Before the update birthdate was NULL)

5. Postmaster remains alive,  and I can immediately
reconnect. This error by the way, disconnects ALL backends
active at that moment (maybe it should be that way, I am just
noting it FYI).

6. The above error does NOT happen if the result of the above
query inserts a NULL into the date field. Ie the query:
"UPDATE authors set firstname='dasd',
lastname='asdadasd',
birthdate=(CASE WHEN (text '')=(text '') THEN NULL ELSE ''
END),
birthplace='asdasd',commentary='',
lastname_original='asdasd', firstname_original='asdfg',
birthplace_original='' WHERE author_id=56"

works fine.

7. The problem can be repeated with the table in question and
is irrelevant of specific rows (author_ids).

8. In case of interest, why should I use such a CASE statement: this query is executed via a web server
and the actual query form is:

<sqlquery quote=+ query="SET DATESTYLE TO 'German';UPDATE authors set firstname='#firstname#',
lastname='#lastname#', birthdate=CASE WHEN (text '#birthdate#')=(text '') THEN NULL ELSE '#birthdate#'  END,
birthplace='#birthplace#',commentary='#commentary#', lastname_original='#lastname_original#',
firstname_original='#firstname_original#', birthplace_original='#birthplace_original#' WHERE author_id=#id#">

8.1. I thought that maybe webserver added some garbage to
the query being passed to the backend -- it was known to add
0x00 to the end of the values, so I copied & pasted the actual
query, manually reentered the date values and ran it via psql.
Result did not change.

9. Attached in file pg.core is the output of the gdb 'bt'
command from the core, as suggested by Mr. Tom Lane.

I shall be happy to answer any additional questions and/or
send any additional info/db structure/contents, if that would be
of any help.

Best regards,

Emils Klotins

\connect - postgres
COPY "authors" FROM stdin;
26    Janis    Jânis    Mitrevics    Mitrçvics    \N    \N    \N            \N    \N    \N        76
29    Ojars    Ojârs    Petersons    Pçtersons    \N    \N    03-10-1956    Rîga        \N    \N    \N    Izglîtîba:\
1975 - 1980 T.Zaïkalna Latvijas Valsts Mâkslas akadçmijas dizaina nodaïa\
1970 - 1975 Rîgas Lietiðíâs mâkslas vidusskolas koktçlniecîbas nodaïa\
\
Nodarboðanâs:\
no 1991. gada pasniedzçjs Latvijas Mâkslas akadçmijas vizuâlâs komunikâcijas nodaïâ    79
28    Olegs    Oïegs    Tillbergs    Tillbergs    \N    \N    \N    Saulkrasti        \N    \N    \N        78
30    Inta    Inta    Ruka    Ruka    \N    \N    \N            \N    \N    \N        80
55    testa    abc    autors    def    \N    \N    \N            \N    \N    \N        116
\.
This GDB was configured as "i386-redhat-linux"...(no debugging symbols found)...

warning: core file may not match specified executable file.
Core was generated by `/usr/bin/postgres localhost myusername_ommited my_db_ommited SEL'.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /lib/libcrypt.so.1...done.
Reading symbols from /lib/libnsl.so.1...done.
Reading symbols from /lib/libdl.so.2...done.
Reading symbols from /lib/libm.so.6...done.
Reading symbols from /usr/lib/libreadline.so.3...done.
Reading symbols from /usr/lib/libhistory.so.3...done.
Reading symbols from /lib/libtermcap.so.2...done.
Reading symbols from /usr/lib/libncurses.so.4...done.
Reading symbols from /lib/libc.so.6...done.
Reading symbols from /lib/ld-linux.so.2...done.
Reading symbols from /lib/libnss_files.so.1...done.
#0  0x80611be in nocachegetattr ()
(gdb) bt
#0  0x80611be in nocachegetattr ()
#1  0x808fe41 in ExecEndNode ()
#2  0x80908e4 in ExecEvalExpr ()
#3  0x8090b28 in ExecTargetListLength ()
#4  0x8090cc5 in ExecProject ()
#5  0x8090d69 in ExecScan ()
#6  0x80956d7 in ExecSeqScan ()
#7  0x808f82a in ExecProcNode ()
#8  0x808e945 in ExecCheckPerms ()
#9  0x808e2df in ExecutorRun ()
#10 0x80db1e9 in ProcessPortal ()
#11 0x80db24c in ProcessQuery ()
#12 0x80d9c56 in pg_exec_query_dest ()
#13 0x80d9b33 in pg_parse_and_plan ()
#14 0x80dac24 in PostgresMain ()
#15 0x80c55d2 in PostmasterMain ()
#16 0x80c513e in PostmasterMain ()
#17 0x80c48aa in PostmasterMain ()
#18 0x80c4450 in PostmasterMain ()
#19 0x809d0ce in main ()
(gdb)