PostgreSQL case sensitivity issue
lazarus / 16-Databases

----------------------------------------------------------------------
Чебурашка · 2022-10-24 12:13

Hello,
I use the TPGConnection as follows:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i connection1
:= TPGConnection.Create(nil);connection2 :=
TPGConnection.Create(nil);try    Writeln('Set up first connection to
create db');    connection1.HostName := '192.168.56.1';   
connection1.Params.Add(Format('port=%s', [5432]));   
connection1.DatabaseName := 'ExampleDBName';    connection1.UserName
:= 'postgres';    connection1.Password := 'postgres';    
connection1.CreateDB();    Writeln('Done creating db');     
Writeln('Set up second connection to open db');   
connection2.HostName := '192.168.56.1';   
connection2.Params.Add(Format('port=%s', [5432]));   
connection2.DatabaseName := 'ExampleDBName';    connection2.UserName
:= 'postgres';    connection2.Password := 'postgres';    
connection2.Open(); // <-- raises PostgreSQL: FATAL:  database
"ExampleDBName" does not exist    Writeln('Done opening db'); finally 
  connection1.Destroy();    connection2.Destroy();end; 
The db is actually created, but its name is lowercased to
'exampledbname'. This is a known behaviour of PG server that
lowercases all the unquoted identifiers (as requested by the SQL
standard, as some stack overflow expert kindly explained to me).

The problem is that on the second connect, the db name seems is
received with its own case by PG server with the quotes that result in
not lowercasing.
I am not sure 100% why, but in TPGConnection code I see:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TPQConnection.CreateDB;begin  ExecuteDirectPG('CREATE DATABASE '
+DatabaseName);end; procedure TPQConnection.DoInternalConnect;var 
ASQLDatabaseHandle   : PPGConn;  T : TPQTranConnection; begin{$IfDef
LinkDynamically}  InitialisePostgres3;{$EndIf}   inherited
DoInternalConnect;   FConnectString := '';  if (UserName <> '') then
FConnectString := FConnectString + ' user=''' + UserName + '''';  if
(Password <> '') then FConnectString := FConnectString + '
password=''' + Password + '''';  if (HostName <> '') then
FConnectString := FConnectString + ' host=''' + HostName + '''';  if
(DatabaseName <> '') then FConnectString := FConnectString + '
dbname=''' + DatabaseName + '''';  if (Params.Text <> '') then
FConnectString := FConnectString + ' '+Params.Text;  
ASQLDatabaseHandle := PQconnectdb(pchar(FConnectString));  try   
CheckConnectionStatus(ASQLDatabaseHandle);  except   
DoInternalDisconnect;    raise;  end;   // This only works for
pg>=8.0, so timestamps won't work with earlier versions of pg which
are compiled with integer_datetimes on  if PQparameterStatus<>nil
then    FIntegerDateTimes :=
PQparameterStatus(ASQLDatabaseHandle,'integer_datetimes') = 'on'; 
T:=TPQTranConnection.Create;  T.FPGConn:=ASQLDatabaseHandle; 
T.FTranActive:=false;  AddConnection(T);end; 
Apparently, CreateDB sends unquoted name, while the connection
parameters are packed with (single) quote.

I would expect that also CreateDB does the same with quoting (quote
either none or both).
Does it make sense?

I tryed overriding CreateDB like this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TModifiedPQConnection.CreateDB;begin  ExecuteDirectPG('CREATE DATABASE
"' + DatabaseName + '"');end; 

And the behaviour is ok, DB is created with its own case and the
following connection open in the original example works as I would
expect.

Please comment, thank you.

----------------------------------------------------------------------
LacaK · 2022-10-26 16:54

According to https://www.postgresql.org/docs/current/libpq-
connect.html#LIBPQ-CONNSTRING
There is "To write an empty value, or a value containing spaces,
surround it with single quotes, for example keyword = 'a value'."
So it is not clear to me if single quote works here just like double
quotes used in quoted identfiers (case sensitive).

Try change:
if (DatabaseName <> '') then FConnectString := FConnectString + '
dbname=''' + DatabaseName + '''';
to:
if (DatabaseName <> '') then FConnectString := FConnectString + '
dbname=' + DatabaseName;

----------------------------------------------------------------------
MarkMLl · 2022-10-26 17:19

I've been looking at this thread for a couple of days and it gives me
no pleasure :-/

I think this takes us back to the discussion of a couple of months ago
about the desirability of having the database support library log
absolutely everything it sends of receives... or at the very least the
commands with absolutely no attempt at windowdressing.

Failing that, I've got a vague recollection of investigating this sort
of thing by telling the Postgres server to log commands.

MarkMLl

"Watch it, rozzer."
"I have been watching it sir, and it gives me no pleasure."