Re: Bug report

I will see if I can narrow things down a bit. The advantage of the complicated
query was that it was killing the backend making the problem obvious.
Just using to_char with 'RN' doesn't always do that. Sometimes I get warings
during the queries that hint at memory corruption.

I am pretty sure that the problem is related to calling to_char(column, 'RN')
where column has null values. When I changed my complicated query to use
case when to avoid calling to_char for null values the problems went away.

If that description isn't enough to narrow down where to look, I can try
to give you something else. I suspect because of the kind of problem, you
will need loaded tables.

I haven't played with the export commands yet, but I could see if I could
get an exported version of the instance that you could load and test
queries on and put that on a web page.

If you want me to do that let me know.

Thanks for looking into this.

On Mon, Dec 18, 2000 at 08:54:25AM +0100,
  Karel Zak <zakkr@zf.jcu.cz> wrote:
>
> On Fri, 15 Dec 2000, Bruce Momjian wrote:
>
> > I think this is fixed in the current snapshot.
> >
> > > 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;
>
>  Hmm.. I don't know. It's total unreadable bug report. Can you report
> simple data select (how data go into your query - for example same query
> but without to_char())?
>
>  Thanks.
>                 Karel
>
>
> > >
> > > 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.
> > >
> >
> >
> > --
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 853-3000
> >   +  If your life is a hard drive,     |  830 Blythe Avenue
> >   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> >
>