============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Bruno Wolff III
Your email address : [EMAIL PROTECTED]
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.
