Bug report - Mailing list pgsql-bugs

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


Your name        :    Bruno Wolff III
Your email address    :    bruno@wolff.to


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : Linux 2.2.16-4 (Redhatisms)

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.3

  Compiler used (example:  gcc 2.8.0)        : N/A installed via RPM


Please enter a FULL description of your problem:
------------------------------------------------
I am getting problems when using to_char to convert an int4 to roman numeral
and to_char to convert a timestamp to a string in a view. The errors
vary, but it looks like there is some sort of memory corruption.

The select that has the problem is:

select areaid, lname, fmname, aname, trim(to_char(gen, 'RN')), to_char(touched, 'YYYY-MM-DD') from cname_web order by
areaid;

Here is a sample of one kind of error I was getting with the about query in
test.sql:
Script started on Wed Dec 13 22:41:31 2000
[bruno@wolff test]$ psql area
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

area=> \i test.sql
psql:test.sql:1: pqReadData() -- backend closed the channel unexpectedly.
    This probably means the backend terminated abnormally
    before or while processing the request.
psql:test.sql:1: connection to server was lost
[bruno@wolff test]$ exit
exit

Script done on Wed Dec 13 22:41:47 2000

This is the script I used to create the tables:

-- Definitions for the AREA database
-- Author: Bruno Wolff III
-- Last Revision: December 9, 2000

-- Privacy levels
-- This table is used in views to change fields to null if the privacy
-- level of the request is less than the privacy level of the row.

-- priv   Table name
-- pname  Name to be used to reference the privacy level
-- pord   A number used to order the privacy levels
-- ptxt   A text description that can be used in a prompt

-- pname values:
-- any       The data can be used for anything
-- web       The data can be placed on public web pages
-- request   The data can be given out in response to one off requests
-- member    The data can be given to other AREA members
-- td        The data can be given to tournament directors
-- admin     The data can be always be seen by the AREA administrators

create table priv (
  pname text primary key,
  pord int4 unique constraint pord_nonnegative check (pord >= 0),
  ptxt text
);

-- Allow access to it from the web server account
-- Doing that breaks the default, so grant access to the admin account

grant select on priv to nobody;
grant all on priv to bruno;

-- The number used isn't relevant as only the ordering matters.
-- However leaving room to insert new levels in without changing
-- old ones seems like a good idea.

copy priv from stdin using delimiters ',';
any,0,No restrictions on access
web,100,Allow anonymous access via the web
request,200,Allow access via one off requests
member,300,Allow access by people believed to be AREA members
td,400,Allow access by tournament directors
admin,500,Access by AREA administrators is always allowed
\.

-- The current name table definition.
-- This is the primary definition for AREA members
-- There will also need to be a history table to track areaid and name changes
-- so that the admins have a way to check on records to resolve problems.
-- Names are limited to US ASCII characters. They can contain letters (a-z)
-- with case being maintained. They may also have space, ', or - between two
-- letters. They will be sorted using the ASCII ordering with uppercase
-- characters treated as the lowercase equivalent.

-- cname    Table name
-- areaid   Current AREA ID of a person
-- lname    Current last name of a person
-- fmname   Current first and middle names of a person
-- aname    Current alternate first and middle names of a person
-- gen      Generation number (Sr or I > 1, Jr or II > 2, III > 3, IV > 4, etc)
--          Note this limitation matches that of the postgres routine that
--          prints roman numerals.
-- privacy  Privacy level for their name data
-- touched  When the areaid or name (not privacy) information last changed

create table cname (
  areaid text primary key constraint bad_char_in_id check
    (areaid ~ '^[A-Z0-9]+(-[A-Z0-9]+)*(\\.[0-9]{2,})?$')
    constraint missing_lead_zeros check (areaid !~ '^[0-9]{1,4}(\\.[0-9]+)?$'),
  lname text not null constraint bad_last_name check
    (lname ~ '^[A-Za-z]+(( |\'|-)[A-Za-z]+)*$'),
  fmname text constraint bad_first_or_mid_name check
    (fmname ~ '^[A-Za-z]+(( |\'|-)[A-Za-z]+)*$'),
  aname text constraint bad_alt_name check
    (aname ~ '^[A-Za-z]+(( |\'|-)[A-Za-z]+)*$'),
  gen int4 constraint bad_generation check (gen > 0 and gen < 4000),
  privacy text not null constraint bad_privacy references priv,
  touched timestamp default 'now' not null
);

-- Explicitly grant full access to cname to the admin account.

grant all on cname to bruno;

-- Web view of the above table
-- areaids are always considered public.
-- The name fields will only be available to the web server for
-- those people that said it was OK. This will include search
-- queries using these fields.
-- priv isn't needed and should be considered admin access only

create view cname_web as select
  areaid,
  case when (select pord from priv where pname = 'web') >=
    (select pord from priv where pname = privacy) then
    lname else null end as lname,
  case when (select pord from priv where pname = 'web') >=
    (select pord from priv where pname = privacy) then
    fmname else null end as fmname,
  case when (select pord from priv where pname = 'web') >=
    (select pord from priv where pname = privacy) then
    aname else null end as aname,
  case when (select pord from priv where pname = 'web') >=
    (select pord from priv where pname = privacy) then
    gen else null end as gen,
  case when (select pord from priv where pname = 'web') >=
    (select pord from priv where pname = privacy) then
    touched else null end as touched
  from cname;

-- Allow access to it from the web server account
-- Doing that breaks the default, so grant access to the admin account

grant select on cname_web to nobody;
grant all on cname_web to bruno;

-- Game definition table
-- This is the primary definition for games.
-- Titles and Publishers may only contain printable US ASCII characters and
-- imbedded spaces. Sorting will be done using the US ASCII colating
-- sequence with uppercase letters treated as lowercase.

-- games    Table name
-- gameid   Initially will be excel sheet name
-- title    The game title
-- publish  Optional publisher string
-- touched  Last time gameid, title, or publisher (not ratings) changed

create table games (
  gameid text primary key constraint bad_char_in_id check
    (gameid ~ '^[A-Z0-9]+$'),
  title text not null constraint bad_char_in_title check
    (title ~ '^[\041-\176]+( [\041-\176]+)*$'),
  publish text constraint bad_char_in_publish check
    (publish ~ '^[\041-\176]+( [\041-\176]+)*$'),
  touched timestamp default 'now' not null
);

-- Allow access to it from the web server account
-- Doing that breaks the default, so grant access to the admin account

grant select on games to nobody;
grant all on games to bruno;

-- Table of WBC codes for games
-- There might be muliple entries for a single code or for a single game
-- because WBC events don't map 1 to 1 to games.

-- wbc     Table name
-- code    WBC code
-- gameid  gameid from games table

create table wbc (
  code text not null constraint bad_char_in_code check
    (code ~ '^[A-Z0-9]+$'),
  gameid text not null constraint bad_gameid references games,
  unique (code, gameid)
);

-- Allow access to it from the web server account
-- Doing that breaks the default, so grant access to the admin account

grant select on wbc to nobody;
grant all on wbc to bruno;

-- Current AREA ratings
-- Note this record should only be created for person - game pairs where
-- either the person has at least one recorded rated game or has specifically
-- notified AREA that they have an interest in the game.
-- This table should be used to retrive data even though it isn't the
-- ultimate source for data. It is too expensive to rederive this information
-- from the base data. If something bad happens, this table should be
-- rebuildable from a transaction table that includes actions for the
-- results of rated games, expressing interest in a game, or making hand
-- corrections to fix problems.

-- crate    Table name
-- areaid   From the cname table
-- gameid   From the games table
-- rate     Current AREA rating
-- frq      Number of times payed rated games of this game
--          If frq is 0 the player has expressed interest in the game.
-- opp      Total number of different opponents played
-- rmp      Total number of rated games played remotely (PBM, PBEM, VASL, etc.)
-- trn      Total number of tournaments played in (with rated games)
-- touched  Timestamp from when information in this record was changed
--          Typically this should be the last time a match was entered
--          for this person.

create table crate (
  areaid text constraint bad_areaid references cname,
  gameid text constraint bad_gameid references games,
  rate int4 default 5000 not null constraint rate_nonnegative check (rate >= 0),
  frq int4 default 0 not null constraint frq_nonnegative check (frq >= 0),
  opp int4 default 0 not null constraint opp_nonnegative check (opp >= 0),
  rmp int4 default 0 not null constraint rmp_nonnegative check (rmp >= 0),
  trn int4 default 0 not null constraint trn_nonnegative check (trn >= 0),
  touched timestamp default 'now' not null,
  primary key (areaid, gameid)
);

-- Allow access to it from the web server account
-- Doing that breaks the default, so grant access to the admin account

grant select on crate to nobody;
grant all on crate to bruno;

Most of the data in the tables is available at:
http://wolff.to/area/test/show.cgi

I am using this for my own testing so the tables get nuked and rebuilt
on occasion, but the base data should stay the same.

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

This is spelled out in the problem description.




If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

I don't know how to fix this.