Hi Doug,

On Tue, 25 Oct 2005 11:14 pm, Douglas McNaught wrote:
> Perl doesn't have case-insensitive strings, while SQL has
> case-insensitive (unless quoted) identifiers.  

As I see it, the issue is that once the perl tools send stuff into the Slony 
database functions (e.g. determineIdxnameUnique) the table comparison is 
being done case-sensitively:

if (select PGC.relname
                from "pg_catalog".pg_class PGC,
                    "pg_catalog".pg_namespace PGN
                where @NAMESPACE at .slon_quote_brute(PGN.nspname) || ''.'' ||
                    @NAMESPACE at .slon_quote_brute(PGC.relname) = 
 v_tab_fqname_quoted
                    and PGN.oid = PGC.relnamespace) is null then
        raise exception ''Slony-I: table % not found'', v_tab_fqname_quoted;
    end if;

Changing the comparison to:

where lower(@NAMESPACE at .slon_quote_brute(PGN.nspname) || ''.'' ||
                    @NAMESPACE at .slon_quote_brute(PGC.relname)) =  
lower(v_tab_fqname_quoted)

would solve the issue (there're probably a few other places you'd need a 
similar change). 

As noted by you and Andrew, that'd break Slony for people who want to have 
tables differentiated only by the case of the name (e.g. ACCOUNT, account and 
aCcOuNt) in the same database and schema. I reckon that's a corner-case, but 
I'm willing to revise my opinion if someone can provide me with a real-life 
example.

Alternately, you could tackle the issue in the perl tools, perhaps allowing 
pam_tools.conf to have a "foldNamesToLowerCase" variable so that the output 
that is sent to slonik has object names folded if the user so desires.

> There's a fundamental 
> mismatch there that means someone is going to be unhappy.  

Well yeah, this is my point: I'm a software developer, I'm used to things 
blowing up in my face. It still took me by surprise, and cost me time 
figuring out why it occurred. I suspect people less-accustomed to PostgreSQL 
might take longer to figure it out, or worse yet, give up on using PostgreSQL 
and Slony. I want to remove obstacles to adoption.

> Making the 
> Perl stuff fold case would mean that people who use mixed case
> identifiers and quote them consistently would lose.

Using the change I propose above, no. Only people who want 3 different tables 
called account, ACCOUNT and AcCoUnT in the one schema would lose. If that's 
considered a real problem, make case-folding configurable (e.g. in the perl 
tools) then everyone can win.

Regards, Philip.

-----------------
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.