Re: Bug report - Mailing list pgsql-bugs
From | Bruno Wolff III |
---|---|
Subject | Re: Bug report |
Date | |
Msg-id | 20001218102638.A6051@wolff.to Whole thread Raw |
In response to | Re: Bug report (Karel Zak <zakkr@zf.jcu.cz>) |
List | pgsql-bugs |
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 > > >
pgsql-bugs by date: