Thread: char(xx) problem
Hi
we have two servers with installed Linux and PostGreSQL. They are quite
same. One is for development (Linux 2.2.9; 128 MB RAM; very overloaded with
processes), another for real HTTP serving in Internet (Linux 2.2.9; 64 MB
RAM; only http and pgsql)
In one of projects we had two tables:
create table a(
x char(2)
);
create table b(
y char(3)
);
When we try to execute SQL like this:
select * from a, b
where a.x = b.y;
one of servers selects 5-6 records (e.g. all mached records)
another selects empty table.
The database is one and same.
Why happen this?
PgSQL version 6.4, do i need to reinstall PgSQL, reinstall `db space' , or
how to fix it???
(I fix them with making chars with one and same length, and this is right
solution, but i want to have an idea why this difference exists)
--------------------------------------------------------------
The reboots are for hardware upgrades!
"http://www.nmmm.nu; <nmmm@nmmm.nu>
> Hi > we have two servers with installed Linux and PostGreSQL. They are quite > same. One is for development (Linux 2.2.9; 128 MB RAM; very overloaded with > processes), another for real HTTP serving in Internet (Linux 2.2.9; 64 MB > RAM; only http and pgsql) > > In one of projects we had two tables: > > create table a( > x char(2) > ); > > create table b( > y char(3) > ); > > When we try to execute SQL like this: > > select * from a, b > where a.x = b.y; > > one of servers selects 5-6 records (e.g. all mached records) > another selects empty table. > > The database is one and same. What are the versions of pg? I have similar experience. I used to routinely join on char() and text, or on char() attributes of different length. Can't do that anymore. Not sure when the change occurred -- some time between 6.3 and 6.5. The problem is I beleive related to blank padding: it formerly showed through only in selects, now it affects comparison as well. I'm just wondering: are there any alternatives to blank padding? Why is it done in the first place? --Gene
Sorry PG is 6.4.2 -------------------------------------------------------------- The reboots are for hardware upgrades! "http://www.nmmm.nu; <nmmm@nmmm.nu> ----- Original Message ----- From: Gene Selkov, Jr. <selkovjr@mcs.anl.gov> To: Nikolay Mijaylov <nmmm@nmmm.nu> Cc: pgsql-general <pgsql-general@postgreSQL.org> Sent: петък, Декември 17, 1999 04:02 Subject: Re: [GENERAL] char(xx) problem > > Hi > > we have two servers with installed Linux and PostGreSQL. They are quite > > same. One is for development (Linux 2.2.9; 128 MB RAM; very overloaded with > > processes), another for real HTTP serving in Internet (Linux 2.2.9; 64 MB > > RAM; only http and pgsql) > > > > In one of projects we had two tables: > > > > create table a( > > x char(2) > > ); > > > > create table b( > > y char(3) > > ); > > > > When we try to execute SQL like this: > > > > select * from a, b > > where a.x = b.y; > > > > one of servers selects 5-6 records (e.g. all mached records) > > another selects empty table. > > > > The database is one and same. > > What are the versions of pg? > > I have similar experience. I used to routinely join on char() and > text, or on char() attributes of different length. Can't do that > anymore. Not sure when the change occurred -- some time between 6.3 > and 6.5. The problem is I beleive related to blank padding: it formerly > showed through only in selects, now it affects comparison as well. > > I'm just wondering: are there any alternatives to blank padding? Why > is it done in the first place? > > --Gene > > ************
At 4:02 +0200 on 17/12/1999, Gene Selkov, Jr. wrote: > I'm just wondering: are there any alternatives to blank padding? Why > is it done in the first place? That's how fixed-length char type works, since the early days of SQL. You come to expect it, which means that if you use legacy code that has a fixed-width char type, or you decided to use it for its time-saving possibilities, it should behave according to some way which has been established long ago. What I don't get is why, given two bpchar argument, Postgres doesn't just pad the shorter one to the length of the other and then compares, selects and whatnot. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herouth/personal/
> > I'm just wondering: are there any alternatives to blank padding? Why
> > is it done in the first place?
>
> That's how fixed-length char type works, since the early days of SQL. You
> come to expect it, which means that if you use legacy code that has a
> fixed-width char type, or you decided to use it for its time-saving
> possibilities, it should behave according to some way which has been
> established long ago.
I thik I understand why a fixed-size type should be aligned to the
multiples of its size in storage -- that's what accounts for some
speed improvement. I am still not getting the point when it comes to
padding. Because it looks like it draws on speed -- both when you do
the padding and when you trim the results. The question is
whether a null-terminated string would do as well.
My suspicion is that somebody simply didn't like to see the garbage in the
database files, and then it stuck.
> What I don't get is why, given two bpchar argument, Postgres doesn't just
> pad the shorter one to the length of the other and then compares, selects
> and whatnot.
As the original post by Nikolay Mijaylov indicated, there is (was?) a
mechanism for correct comparison between various char(*) and text
types, but whether it works or not depends on the weather outside. I
can witness its existence in the past, as I still have some code that
relies on cross-type comparisons which do not seem to work
anymore. Unfortunately, I did not check since a few versions back, but
if I understood Nikolay Mijaylov right, he claims to have two
installations of the same version that behave differently.
Now these code snippets clearly shows how it was intended to work:
/*****************************************************************************
* Comparison Functions used for bpchar
*****************************************************************************/
static int
bcTruelen(char *arg)
{
char *s = VARDATA(arg);
int i;
int len;
len = VARSIZE(arg) - VARHDRSZ;
for (i = len - 1; i >= 0; i--)
{
if (s[i] != ' ')
break;
}
return i + 1;
}
. . . .
bool
bpchareq(char *arg1, char *arg2)
{
int len1,
len2;
if (arg1 == NULL || arg2 == NULL)
return (bool) 0;
len1 = bcTruelen(arg1);
len2 = bcTruelen(arg2);
if (len1 != len2)
return 0;
return strncmp(VARDATA(arg1), VARDATA(arg2), len1) == 0;
}
What's up with bcTruelen() then? Where does the noise come from?
--Gene
Hello: I have two questions that might be FAQs (apologies in advance): (1) Why does the parser choke on backslashed single-quote characters? Or, in other words, why doesn't this work: testing=> \d bubba Table = bubba +--------------------------+----------------------------------+-------+ | Field | Type | Length| +--------------------------+----------------------------------+-------+ | litbub | varchar() | 60 | +--------------------------+----------------------------------+-------+ testing=> insert '\'' into bubba; ERROR: parser: parse error at or near "'" (2) How does one rename a database? Other than dump/destroydb/restore, obviously. TIA -- Nathan L. Cutler < livingston @ iol.cz > telephone: +420-2-51611648 Livingston Professional Translations fax: +420-2-6514377 ** When "pretty good" is not enough ** Prague, Czech Republic
Yes, u understood me right, I plane to install new version in january, and if the problem still exist, I;ll report it again -------------------------------------------------------------- The reboots are for hardware upgrades! "http://www.nmmm.nu; <nmmm@nmmm.nu> ----- Original Message ----- From: Gene Selkov <selkovjr@mcs.anl.gov> To: Herouth Maoz <herouth@oumail.openu.ac.il> Cc: pgsql-general <pgsql-general@postgresql.org> Sent: сряда, Декември 22, 1999 10:00 Subject: Re: [GENERAL] char(xx) problem > > > > I'm just wondering: are there any alternatives to blank padding? Why > > > is it done in the first place? > > > > That's how fixed-length char type works, since the early days of SQL. You > > come to expect it, which means that if you use legacy code that has a > > fixed-width char type, or you decided to use it for its time-saving > > possibilities, it should behave according to some way which has been > > established long ago. > > I thik I understand why a fixed-size type should be aligned to the > multiples of its size in storage -- that's what accounts for some > speed improvement. I am still not getting the point when it comes to > padding. Because it looks like it draws on speed -- both when you do > the padding and when you trim the results. The question is > whether a null-terminated string would do as well. > > My suspicion is that somebody simply didn't like to see the garbage in the > database files, and then it stuck. > > > What I don't get is why, given two bpchar argument, Postgres doesn't just > > pad the shorter one to the length of the other and then compares, selects > > and whatnot. > > As the original post by Nikolay Mijaylov indicated, there is (was?) a > mechanism for correct comparison between various char(*) and text > types, but whether it works or not depends on the weather outside. I > can witness its existence in the past, as I still have some code that > relies on cross-type comparisons which do not seem to work > anymore. Unfortunately, I did not check since a few versions back, but > if I understood Nikolay Mijaylov right, he claims to have two > installations of the same version that behave differently. > > Now these code snippets clearly shows how it was intended to work: > > > /* **************************************************************************** > * Comparison Functions used for bpchar > * **** ************************************************************************/ > > static int > bcTruelen(char *arg) > { > char *s = VARDATA(arg); > int i; > int len; > > len = VARSIZE(arg) - VARHDRSZ; > for (i = len - 1; i >= 0; i--) > { > if (s[i] != ' ') > break; > } > return i + 1; > } > > > . . . . > > > bool > bpchareq(char *arg1, char *arg2) > { > int len1, > len2; > > if (arg1 == NULL || arg2 == NULL) > return (bool) 0; > len1 = bcTruelen(arg1); > len2 = bcTruelen(arg2); > > if (len1 != len2) > return 0; > > return strncmp(VARDATA(arg1), VARDATA(arg2), len1) == 0; > } > > What's up with bcTruelen() then? Where does the noise come from? > > > --Gene > > ************
> Hello:
>
> I have two questions that might be FAQs (apologies in advance):
>
> (1) Why does the parser choke on backslashed single-quote characters? Or,
> in other words, why doesn't this work:
>
> testing=> \d bubba
> Table = bubba
> +--------------------------+----------------------------------+-------+
> | Field | Type | Length|
> +--------------------------+----------------------------------+-------+
> | litbub | varchar() | 60 |
> +--------------------------+----------------------------------+-------+
> testing=> insert '\'' into bubba;
> ERROR: parser: parse error at or near "'"
INSERT INTO bubba VALUES ('\'');
>
> (2) How does one rename a database? Other than dump/destroydb/restore,
> obviously.
I think you can modify pg_database with new name, stop postmaster,
rename database directory, and restart. Not sure, but that may work.
--
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