[REL6.4] Mixed case table name problems with some fixes.
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Billy G. Allie
Your email address : Bill.Allie@mug.org
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel 486DX2
Operating System (example: Linux 2.0.26 ELF) : UnixWare 7.0
PostgreSQL version (example: PostgreSQL-6.4) : PostgreSQL-6.4
Compiler used (example: gcc 2.8.0) : Optimizing C Compilation System
(CCS) 3.2 08/18/98 (u701)
Please enter a FULL description of your problem:
------------------------------------------------
There are a number of problems with using mixed case table names:
1. Using constraints on tables whose name contains mixed case will fail.
2. Creating triggers on tables whose name contains mixed case will fail.
3. In pgsql, the command '\d *' will fail.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
1. Create a table that has a mixed case name and a constraint such as
a default or a primary key.
2. Create a trigger on a table with a mixed case name.
3. In psql, execute the '\d *' command in a database that has tables
mixed case names.
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
The following patch file fixes problems 1 and 3. Thomas G. Lockhart's
patch to fix problem 1 was incomplete. I added the additional changes
to 'heap.c' that were needed to complete the fix.
Attachments:
uw7-0.patchapplication/x-patch; name=uw7-0.patchDownload
*** src/backend/catalog/heap.c.orig Sat Nov 14 22:20:46 1998
--- src/backend/catalog/heap.c Sat Nov 14 22:25:27 1998
***************
*** 1444,1450 ****
extern GlobalMemory CacheCxt;
start:;
! sprintf(str, "select %s%s from %.*s", attrdef->adsrc, cast,
NAMEDATALEN, rel->rd_rel->relname.data);
setheapoverride(true);
planTree_list = (List *) pg_parse_and_plan(str, NULL, 0, &queryTree_list, None, FALSE);
--- 1444,1453 ----
extern GlobalMemory CacheCxt;
start:;
! /* Surround table name with double quotes to allow mixed-case and
! * whitespaces in names. - BGA 1998-11-14
! */
! sprintf(str, "select %s%s from \"%.*s\"", attrdef->adsrc, cast,
NAMEDATALEN, rel->rd_rel->relname.data);
setheapoverride(true);
planTree_list = (List *) pg_parse_and_plan(str, NULL, 0, &queryTree_list, None, FALSE);
***************
*** 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);
--- 1518,1527 ----
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);
*** src/bin/psql/psql.c.orig Sun Nov 15 00:56:34 1998
--- src/bin/psql/psql.c Sun Nov 15 01:32:54 1998
***************
*** 460,471 ****
perror("malloc");
/* load table table */
for (i = 0; i < nColumns; i++)
{
! table[i] = (char *) malloc(PQgetlength(res, i, 1) * sizeof(char) + 1);
if (table[i] == NULL)
perror("malloc");
! strcpy(table[i], PQgetvalue(res, i, 1));
}
PQclear(res);
--- 460,476 ----
perror("malloc");
/* load table table */
+ /* Put double quotes around the table name to allow for mixed-case
+ * and whitespaces in the table name. - BGA 1998-11-14
+ */
for (i = 0; i < nColumns; i++)
{
! table[i] = (char *) malloc(PQgetlength(res, i, 1) * sizeof(char) + 3);
if (table[i] == NULL)
perror("malloc");
! strcpy(table[i], "\"");
! strcat(table[i], PQgetvalue(res, i, 1));
! strcat(table[i], "\"");
}
PQclear(res);