Обсуждение: A problem with the constraint system.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


The problem:
Creating a table with a name containing mixed case that has aconstraint, the creation will fail.  For example:
    mug=> create table "MyTest" ( a int4 default 10 );    ERROR:  mytest: Table does not exist.    mug=> 

The Solution:
I haven't the faintest idea!

This problem bit me when I was trying to move an MS-Access database to 
postgreSQL while keeping MS-Access as the front end for the database.
- -- 
____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/  |LLIE  | (313) 582-1540    | 



-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 5.0i for non-commercial use
Charset: noconv

iQA/AwUBNkpoxaFebSRz8o+3EQI+rwCdH1luSaOd78t0fKax+G4JnDeNndcAoKFV
QfKhqQuqVhRYPYFUTBpCDwBD
=KaAB
-----END PGP SIGNATURE-----

Hello Billy,

giovedì, 12 novembre 98, you wrote:

BGA> -----BEGIN PGP SIGNED MESSAGE-----
BGA> Hash: SHA1


BGA> The problem:

BGA>         Creating a table with a name containing mixed case that has a
BGA>         constraint, the creation will fail.  For example:

BGA>             mug=> create table "MyTest" ( a int4 default 10 );
BGA>             ERROR:  mytest: Table does not exist.
BGA>             mug=> 
BGA> The Solution:
BGA>         I haven't the faintest idea!


I have this error also using CHECK... but the error message goes away
if I create, drop and re-create the table...

prova=> create table "MyTest" ( a int4 check(a>0) );
ERROR:  mytest: Table does not exist.
prova=> create table "MyTest" ( a int4 );
CREATE
prova=> drop table "MyTest";
DROP
prova=> create table "MyTest" ( a int4 check(a>0) );
CREATE

-Jose'-

> BGA> Creating a table with a name containing mixed case that has a
> BGA> constraint, the creation will fail.  For example:
> BGA>   mug=> create table "MyTest" ( a int4 default 10 );
> BGA>   ERROR:  mytest: Table does not exist.

I have a fix for this, though I've only tested with your example. Here
is the patch...

                      - Tom*** ../src/backend/catalog/heap.c.orig    Tue Sep  1 04:27:29 1998
--- ../src/backend/catalog/heap.c    Thu Nov 12 15:09:58 1998
***************
*** 1515,1521 ****
      char        nulls[4] = {' ', ' ', ' ', ' '};
      extern GlobalMemory CacheCxt;

!     sprintf(str, "select 1 from %.*s where %s",
              NAMEDATALEN, rel->rd_rel->relname.data, check->ccsrc);
      setheapoverride(true);
      planTree_list = (List *) pg_parse_and_plan(str, NULL, 0, &queryTree_list, None, FALSE);
--- 1515,1524 ----
      char        nulls[4] = {' ', ' ', ' ', ' '};
      extern GlobalMemory CacheCxt;

!     /* Check for table's existance. Surround table name with double-quotes
!      * to allow mixed-case and whitespace names. - thomas 1998-11-12
!      */
!     sprintf(str, "select 1 from \"%.*s\" where %s",
              NAMEDATALEN, rel->rd_rel->relname.data, check->ccsrc);
      setheapoverride(true);
      planTree_list = (List *) pg_parse_and_plan(str, NULL, 0, &queryTree_list, None, FALSE);