Re: BUG #1862: ECPG Connect, host variable trailing blanks

The following bug has been logged online:

Bug reference: 1862
Logged by: James Gray
Email address: jim(dot)gray(at)bull(dot)com
PostgreSQL version: 8.0.3
Operating system: Red Hat Enterprise Linux AS release 4 (Nahant Update 1),
Kernel 2.6.9-11.EL on an ia64
Description: ECPG Connect, host variable trailing blanks
Details:

We are processing a COBOL/SQL program, and feeding the results to ECPG as
C/SQL
(since Postgres does not yet have a Cobol/Sql preprocessor).

The problem that we are having involves a connect statement with host
variables:
EXEC SQL CONNECT TO :target AS :user

Our problem is that we are passed Cobol strings which are blank padded.
Our string strategy works fine for Oracle, but not for Postgres CONNECTs.

For example, if we are trying to connect to:
- database: demo
- user: scott
- password: tiger
the strings must be "demo", "scott" and "tiger".

With trailing blanks user "scott" will not match user "scott ",
which is what we will present if the user had defined the Cobol variable as
PIC X(10).

There does not seem to be an option for Postgres to ignore trailing blanks
in the CONNECT host variables.

This only applies to CONNECT host variables, since trailing blanks in a
CHAR column are ignored in comparisons for all other interactions with
Postgres.

Since this is inconsistent behavior, and also doesn't match Oracle's
behavior,
we are requesting a fix or an option.

On Tue, Sep 06, 2005 at 09:02:47PM +0100, James Gray wrote:
> The problem that we are having involves a connect statement with host
> variables:
> EXEC SQL CONNECT TO :target AS :user
>
> Our problem is that we are passed Cobol strings which are blank padded.
> Our string strategy works fine for Oracle, but not for Postgres CONNECTs.
>
> For example, if we are trying to connect to:
> - database: demo
> - user: scott
> - password: tiger
> the strings must be "demo", "scott" and "tiger".
>
> With trailing blanks user "scott" will not match user "scott ",
> which is what we will present if the user had defined the Cobol variable as
> PIC X(10).

In PostgreSQL, "scott" and "scott " are distinct identifiers,
and both are valid. See "Identifiers and Key Words" in the "SQL
Syntax" chapter of the documentation, especially the part that
discusses quoted identifiers:

http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

Although creating databases, users, tables, etc., with trailing
spaces is probably a bad idea, PostgreSQL does allow such names,
and the trailing spaces are significant.

> This only applies to CONNECT host variables, since trailing blanks in a
> CHAR column are ignored in comparisons for all other interactions with
> Postgres.
>
> Since this is inconsistent behavior, and also doesn't match Oracle's
> behavior, we are requesting a fix or an option.

Identifiers are NAME types, not CHAR types; the difference in
behavior is no more inconsistent than that between VARCHAR and CHAR.
If the strings have trailing spaces but the identifiers on the
server side don't, then strip the spaces on the client side.

--
Michael Fuhr

>Identifiers are NAME types, not CHAR types

From the URL reference that you gave:

UPDATE MY_TABLE SET A = 5;
The tokens MY_TABLE and A are examples of identifiers.

But I don't think it is legal SQL to state this as:

UPDATE :V1 SET :V2 = 5;

In other words, host variables are never considered identifiers in normal
DML statements.

According to the documentation at:
http://www.postgresql.org/docs/8.0/static/ecpg-connect.html

EXEC SQL CONNECT TO :target USER :user;
The last form makes use of the variant referred to above as character
variable reference.

To be consistent with other uses of host variables,
we request that host variables within a connect statement
act like host variables in DML statements.

Oracle seems to think this is the correct approach, and also
"the format of the connection target is not specified in the SQL standard".

Michael Fuhr
<mike(at)fuhr(dot)org>
To
09/07/2005 12:15 James Gray <jim(dot)gray(at)bull(dot)com>
AM cc
pgsql-bugs(at)postgresql(dot)org
Subject
Re: [BUGS] BUG #1862: ECPG Connect,
host variable trailing blanks

On Tue, Sep 06, 2005 at 09:02:47PM +0100, James Gray wrote:
> The problem that we are having involves a connect statement with host
> variables:
> EXEC SQL CONNECT TO :target AS :user
>
> Our problem is that we are passed Cobol strings which are blank padded.
> Our string strategy works fine for Oracle, but not for Postgres CONNECTs.
>
> For example, if we are trying to connect to:
> - database: demo
> - user: scott
> - password: tiger
> the strings must be "demo", "scott" and "tiger".
>
> With trailing blanks user "scott" will not match user "scott ",
> which is what we will present if the user had defined the Cobol variable
as
> PIC X(10).

In PostgreSQL, "scott" and "scott " are distinct identifiers,
and both are valid. See "Identifiers and Key Words" in the "SQL
Syntax" chapter of the documentation, especially the part that
discusses quoted identifiers:

http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

Although creating databases, users, tables, etc., with trailing
spaces is probably a bad idea, PostgreSQL does allow such names,
and the trailing spaces are significant.

> This only applies to CONNECT host variables, since trailing blanks in a
> CHAR column are ignored in comparisons for all other interactions with
> Postgres.
>
> Since this is inconsistent behavior, and also doesn't match Oracle's
> behavior, we are requesting a fix or an option.

Identifiers are NAME types, not CHAR types; the difference in
behavior is no more inconsistent than that between VARCHAR and CHAR.
If the strings have trailing spaces but the identifiers on the
server side don't, then strip the spaces on the client side.

--
Michael Fuhr

On Wed, Sep 07, 2005 at 12:06:10PM -0700, Jim(dot)Gray(at)Bull(dot)com wrote:
> According to the documentation at:
> http://www.postgresql.org/docs/8.0/static/ecpg-connect.html
>
> EXEC SQL CONNECT TO :target USER :user;
> The last form makes use of the variant referred to above as character
> variable reference.

The complete text is:

The last form makes use of the variant referred to above as
character variable reference. You will see in later sections how
C variables can be used in SQL statements when you prefix them
with a colon.

Note "C variables" -- it's talking about the C character type, not
the SQL character type.

> To be consistent with other uses of host variables,
> we request that host variables within a connect statement
> act like host variables in DML statements.

They *do* act the same way: the value is passed to the server, which
handles it according to the appropriate type's semantics. If the
host variable's value is used in a CHAR context then trailing spaces
aren't significant in comparisons; if the value is used in other
contexts like VARCHAR, TEXT, or NAME, then trailing spaces *are*
significant. The server makes the decision, not the ECPG preprocessor
or library, which simply passes the value to the server.

If you think this behavior should be changed then propose it on
pgsql-hackers and discuss it with the developers.

--
Michael Fuhr

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> If you think this behavior should be changed then propose it on
> pgsql-hackers and discuss it with the developers.

I doubt we'll consider changing it. If I haven't missed any of the
details, the critical points here are

(1) User names containing trailing (or embedded) spaces are legal.
Not a very good idea maybe, but legal both per SQL spec and per
our historical practice.

(2) Making ECPG strip trailing spaces would render it unable to
connect as such a user.

I don't think ECPG has any business deciding that trailing spaces are
insignificant. If that's the case in your application environment,
fine: strip the spaces at the application level. But don't expect a
general-purpose library to make that policy choice for you.

regards, tom lane

What do you think of an Postgres option that would enable stripping of
trailing blanks
from connect host variables when turned ON?

This would allow current behavior to be supported by ECPG and the Postgres
server,
but allow applications that need the behavior (like a Cobol/SQL
preprocessor for Postgres),
to make use of it.

When I searched the mail archives, I found that others had run into this
same problem,
in this case with an ODBC driver:
http://archives.postgresql.org/pgsql-interfaces/1998-08/msg00000.php

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
09/08/2005 09:15 PM

To
Michael Fuhr <mike(at)fuhr(dot)org>
cc
Jim(dot)Gray(at)Bull(dot)com, pgsql-bugs(at)postgresql(dot)org, Ken(dot)Rosensteel(at)Bull(dot)com
Subject
Re: [BUGS] BUG #1862: ECPG Connect, host variable trailing blanks

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> If you think this behavior should be changed then propose it on
> pgsql-hackers and discuss it with the developers.

I doubt we'll consider changing it. If I haven't missed any of the
details, the critical points here are

(1) User names containing trailing (or embedded) spaces are legal.
Not a very good idea maybe, but legal both per SQL spec and per
our historical practice.

(2) Making ECPG strip trailing spaces would render it unable to
connect as such a user.

I don't think ECPG has any business deciding that trailing spaces are
insignificant. If that's the case in your application environment,
fine: strip the spaces at the application level. But don't expect a
general-purpose library to make that policy choice for you.

regards, tom lane

On Fri, Sep 09, 2005 at 11:02:31AM -0700, Jim(dot)Gray(at)Bull(dot)com wrote:
> This would allow current behavior to be supported by ECPG and the Postgres
> server,
> but allow applications that need the behavior (like a Cobol/SQL
> preprocessor for Postgres),
> to make use of it.

Which preprocessor are you using? I wonder if such a logic should better
be implemented in the Cobol-C transition instead.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

We are working in a rather complex environment, where COBOL/SQL on a
legacy
mainframe has its SQL portions sent via a high speed link to a Unix box.

The SQL is translated to a C/SQL environment in the process and the
Postgres
ECPG preprocessor is used on the Unix side for interfacing with Postgres.

We are already doing a translation from trailing blank Cobol strings
to C null terminated strings in this process for the connection host
variables.

The reasons for requesting a Postgres option to strip trailing blanks in
this case are:
1) we are not the only project to run into the problem of Postgres being
oriented towards C strings
2) other RDBMS systems use the expected trimming of Cobol trailing blanks
3) when porting Oracle Cobol applications to Postgres, this option would
be helpful
4) it might be better to strip the trailing blanks in the application if
only one such application were involved,
but when there are several such applications, centralizing the
behavior in a Postgres option is preferrable

Michael Meskes <meskes(at)postgresql(dot)org>
09/12/2005 04:06 AM

To
Jim(dot)Gray(at)Bull(dot)com
cc
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Ken(dot)Rosensteel(at)Bull(dot)com, Michael Fuhr
<mike(at)fuhr(dot)org>, pgsql-bugs(at)postgresql(dot)org
Subject
Re: [BUGS] BUG #1862: ECPG Connect, host variable trailing blanks

On Fri, Sep 09, 2005 at 11:02:31AM -0700, Jim(dot)Gray(at)Bull(dot)com wrote:
> This would allow current behavior to be supported by ECPG and the
Postgres
> server,
> but allow applications that need the behavior (like a Cobol/SQL
> preprocessor for Postgres),
> to make use of it.

Which preprocessor are you using? I wonder if such a logic should better
be implemented in the Cobol-C transition instead.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot
(De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Jim(dot)Gray(at)Bull(dot)com wrote:
> What do you think of an Postgres option that would enable stripping of
> trailing blanks from connect host variables when turned ON?

I would not support such an option. If we added flags for every single
thing that someone wanted, the system would be unusable.

> This would allow current behavior to be supported by ECPG and the
> Postgres server, but allow applications that need the behavior (like
> a Cobol/SQL preprocessor for Postgres), to make use of it.
>
> When I searched the mail archives, I found that others had run into
> this same problem, in this case with an ODBC driver:
> http://archives.postgresql.org/pgsql-interfaces/1998-08/msg00000.php

Just because Oracle does it doesn't mean we should.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, Sep 16, 2005 at 12:00:59PM -0400, Bruce Momjian wrote:
> Jim(dot)Gray(at)Bull(dot)com wrote:
> > What do you think of an Postgres option that would enable stripping of
> > trailing blanks from connect host variables when turned ON?
>
> I would not support such an option. If we added flags for every single
> thing that someone wanted, the system would be unusable.

[snip]

> Just because Oracle does it doesn't mean we should.

Does Oracle really munge data on the client side? Or does it, like
PostgreSQL, pass the host variable's value as-is to the server, and
the server considers trailing spaces significant or not depending
on the context? Is it the client-side behavior or the server-side
behavior that's different between PostgreSQL and Oracle?

If Oracle strips trailing spaces on the client side, is that a
configurable option? How would you insert significant trailing
spaces into a VARCHAR column if the client library strips them?

--
Michael Fuhr