Horton

Thread

  1. char(xx) problem

    Nikolay Mijaylov <nmmm@nmmm.nu> — 1999-12-16T14:54:07Z

    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>

  2. Re: [GENERAL] char(xx) problem

    selkovjr@mcs.anl.gov — 1999-12-17T02:02:45Z

    > 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

  3. Re: [GENERAL] char(xx) problem

    Nikolay Mijaylov <nmmm@nmmm.nu> — 1999-12-17T07:19:54Z

    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
>
> ************

  4. Re: [GENERAL] char(xx) problem

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-12-21T12:08:50Z

    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/

  5. Re: [GENERAL] char(xx) problem

    selkovjr@mcs.anl.gov — 1999-12-22T20:00:48Z

    > > 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

  6. Possible FAQs: single-quote and rename database

    Nathan L. Cutler <livingston@iol.cz> — 1999-12-23T06:41:07Z

    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

  7. Re: [GENERAL] char(xx) problem

    Nikolay Mijaylov <nmmm@nmmm.nu> — 1999-12-23T08:22:33Z

    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
>
> ************

  8. Re: [GENERAL] Possible FAQs: single-quote and rename database

    Bruce Momjian <pgman@candle.pha.pa.us> — 1999-12-23T11:50:08Z

    > 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