Thread: Limitation
I've been attempting to port applications from Pervasive SQL to PG.
Pervasive is interesting because it runs on top of btrieve. This allow
legacy apps
and SQL systems to co-exist. It's quirky and buggy, but it's better than PG
because it can do the following.
1. Have more than 7 fields on a btree index
2. There exists a table called inmaster it has a char(20) field called
stock_code.
pervasive can create a view (product) that splits the consituent characters
into fields.
PG cannot handle this. see below.
create view product as
Select code As Stock_Code,
Substr(Code,1,1) As Process_ID,
Substr(Code,2,2) As SubProcess_ID,
Substr(Code,4,1) As SubStrate_ID,
Substr(Code,5,2) As Length_ID,
Substr(Code,7,2) As Width_ID,
Substr(Code,9,2) As Thickness_ID,
Substr(Code,11,3) As Face_ID,
Substr(Code,14,1) As Facefinish_ID,
Substr(Code,15,3) As Back_ID
-- Substr(Code,18,1) As Backfinish_ID
From INMASTER;
ERROR: DefineQueryRewrite: rule plan string too big.
This should not occur. Would someone please pick it up and put it on the
TODO list.
Regards
John
John Huttley wrote: > PG cannot handle this. see below. > > create view product as > Select code As Stock_Code, > Substr(Code,1,1) As Process_ID, > Substr(Code,2,2) As SubProcess_ID, > Substr(Code,4,1) As SubStrate_ID, > Substr(Code,5,2) As Length_ID, > Substr(Code,7,2) As Width_ID, > Substr(Code,9,2) As Thickness_ID, > Substr(Code,11,3) As Face_ID, > Substr(Code,14,1) As Facefinish_ID, > Substr(Code,15,3) As Back_ID > -- Substr(Code,18,1) As Backfinish_ID > >From INMASTER; > ERROR: DefineQueryRewrite: rule plan string too big. John, I generate a lot of sql create scripts from perl classes. These scripts tend to hav a lot of whitespaces that make them beautiful to read. But just for me ... that's what PG means to this : PQsendQuery() -- query is too long. Maximum length is 8191 So far, I got along very well just stripping unneeded whitespaces out of the scripts before i run them. Probably a simple s/^ // would already help in your case. Chris -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Christian Rudow E-Mail: Christian.Rudow@thinx.ch ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[Charset iso-8859-1 unsupported, filtering to ASCII...] > I've been attempting to port applications from Pervasive SQL to PG. > Pervasive is interesting because it runs on top of btrieve. This allow > legacy apps > and SQL systems to co-exist. It's quirky and buggy, but it's better than PG > because it can do the following. > > > 1. Have more than 7 fields on a btree index We have never understood why someone would want an index with more than seven columns. -- Bruce Momjian | http://www.op.net/~candle maillist@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
It's called an organicly grown schema. I recently had exposure to such a beast at a customer's site. I won't say whom, but they are a large supplier of medical infrastructure software. This software has been hacked beyond recognition. It is on Oracle and front-ends are built with power-builder, but they have ISAM/CICS and other stuff too. Their original ratty design has been hacked, over the years, in an uncontrolled environment, by every schlock hacker in the SF bay area. I've seen as many as 12 columns in an index. They didn't want it, it's just there and they have to deal with it somehow. A re-design is over 18 months and $5M, but they have to keep current production up as well. > -----Original Message----- > From: owner-pgsql-general@postgreSQL.org > [mailto:owner-pgsql-general@postgreSQL.org]On Behalf Of Bruce Momjian > Sent: Thursday, June 24, 1999 8:40 AM > To: John Huttley > Cc: pgsql-general > Subject: Re: [GENERAL] Limitation > > > [Charset iso-8859-1 unsupported, filtering to ASCII...] > > I've been attempting to port applications from Pervasive SQL to PG. > > Pervasive is interesting because it runs on top of btrieve. > This allow > > legacy apps > > and SQL systems to co-exist. It's quirky and buggy, but > it's better than PG > > because it can do the following. > > > > > > 1. Have more than 7 fields on a btree index > > We have never understood why someone would want an index with > more than > seven columns. > > -- > Bruce Momjian | http://www.op.net/~candle > maillist@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 >
On Thu, 24 Jun 1999, Bruce Momjian wrote:
# > 1. Have more than 7 fields on a btree index
#
# We have never understood why someone would want an index with more than
# seven columns.
Honestly, I think it's the same reason someone would want to
insert data as a single string, then create a view that does substrings to
produce the appearance of a multi-column table (which, I assume is then
selected on from certain fields, causing table scans). Sometimes, people
try to wedge efficiency into an inefficient design, which rarely works.
My approach here would be to substr before insertion, then make a view to
concatenate the results.
--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________