Re: Bug report

I think this is fixed in the current snapshot.

> ============================================================================
> POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
>
> Your name : Bruno Wolff III
> Your email address : bruno(at)wolff(dot)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(at)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(at)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.
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026