headers
Bart Samwel | 1 Apr 2006 03:26

Re: Unicode is not UTF-8. was :psqlODBC-Driver Test / text

Marc Herbert wrote:
> Johann Zuschlag <zuschlag2 <at> online.de> writes:
> 
>> I've read about the problems with the NULL bytes on Unix machines.
> 
> This problem is not related to Unix at all but to the programming
> language used. Most standard C functions use the zero byte convention
> as a string terminator, so it becomes a forbidden character in C.
> 
> On the other hand String objects in C++ and Java use a separate length
> field, and having NULLs inside a string is a no brainer there.
> 
> The ODBC API has been designed for C and Cobol. Cobol does not forbid
> zero as a character either. When browsing the ODBC spec you'll notice
> it carefully caters for the two ways.
> 
> 
> Guess which programming language is used PostgreSQL.

C++ even introduced a special alternative character type "wchar_t" for 
this, just so that people could handle both 8-bit char* and 16-bit 
wchar_t* strings. In wchar_t* strings, 8-bit NULs are not a problem 
because only 16-bit NULs count (and AFAIK the Unicode standard does 
allows this to be interpreted as a NUL aka end-of-string). The downside 
of this solution is that no application actually uses it, and everybody 
is stuck with 8-bit ASCII plus a random local codepage unless special 
support is added. Why didn't they just upgrade chars to 32 bits and be 
done with it... :-/

Cheers,
(Continue reading)

Permalink | Reply | 2 comments |
headers
Hiroshi Inoue | 1 Apr 2006 03:35
Picon

Re: Unicode is not UTF-8. was :psqlODBC-Driver Test / text

Johann Zuschlag wrote:
> Johann Zuschlag schrieb:
> 
>>
> No, again wrong. Or is it more like this:
> 
> 1.
> a) locale = ISO8859-1
>    backend-1 = LATIN1
> 
> b) locale = UTF-8
>    backend-2 = Unicode

What do you mean by the Unicode and are you really setting b)
as above ?

First note that in PostgreSQL the encoding has nothing to do
with the locale setting. Though PostgreSQL manages the encoding
settings by itself, as for the locale setting it completely relies
on the OS environment. There exists an essential flaw from the first.
Anyway you can change the encoding as you like per database at
createdb time but the locale setting LC_COLLATE and LC_CTYPE are
fixed at initdb time.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
(Continue reading)

Permalink | Reply | 4 comments |
headers
Hiroshi Inoue | 1 Apr 2006 08:00
Picon

Re: Strange Update query ...

Thomas Chabaud wrote:
> Hello,
> I have a problem with update on recordset with Visual Basic 6.
> 
> I'm using the recordset as following :
> 
>   Set rs = New ADODB.Recordset
>   rs.Open "SELECT id,myfield1,myfield2,myfield3 from mytable where 
> id=6",dbConn, adOpenKeyset, adLockOptimistic, adCmdText
> 
>   rs("myfield1").value = myvalue1
>   rs("myfield2").value = myvalue2
>   rs("myfield3").value = myvalue3
>   rs.Update
>   rs.close
>   set rs=Nothing
> 
> But the line is not updated, so I checked the log, and I saw that the 
> query which was generated by odbc driver is :
> 
> UPDATE mytable SET myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3 
> WHERE (id=6 AND myfield1=myvalue1 AND myfield2=myvalue2 AND 
> myfield3=myvalue3 )'

Are they really myvalue..s not oldvalue..s after the "AND .." ?

> I wonder why the odbc driver add those "AND ..." statement, because I 
> think they are useless, and they fail my rs.update

AFAIK the ODBC driver doesn't add them. Maybe ado does it.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Hiroshi Inoue | 1 Apr 2006 08:30
Picon

Re: Error when getting text longer than MaxLongVarcharSize

Åsmund Kveim Lie wrote:
> Hi,
> 
> When fetching text data longer than MaxLongVarcharSize, the driver 
> returns the length of the actual data in the database rather than the 
> truncated length to retrieve.

Not only in the above case but in other all cases the ODBC driver returns
actual length not the truncated length.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Permalink | Reply | 0 comments |
headers
=?ISO-8859-15?Q?H=E9lder_M._Vieira?= | 1 Apr 2006 11:13

Re: Strange Update query ...

I tried to replicate the situation you describe, without success.
I guess ADO for some reason is trying to build an SQL statement 
corresponding to a parameter query.
I'd bet on a ADO misinterpretation of your intention caused by the myvalue1, 
myvalue2 and myvalue3 data types.
Could you post the DIM statements of those variables ?

Helder M. Vieira

>   Set rs = New ADODB.Recordset
>   rs.Open "SELECT id,myfield1,myfield2,myfield3 from mytable where 
> id=6",dbConn, adOpenKeyset, adLockOptimistic, adCmdText
>
>   rs("myfield1").value = myvalue1
>   rs("myfield2").value = myvalue2
>   rs("myfield3").value = myvalue3
>   rs.Update
>   rs.close
>   set rs=Nothing
>
...
>
> UPDATE mytable SET myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3 
> WHERE (id=6 AND myfield1=myvalue1 AND myfield2=myvalue2 AND 
> myfield3=myvalue3 )'

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly
(Continue reading)

Permalink | Reply | 3 comments |
headers
Hiroshi Inoue | 2 Apr 2006 01:12
Picon

Re: Strange Update query ...

Hélder M. Vieira wrote:

> I tried to replicate the situation you describe, without success.
> I guess ADO for some reason is trying to build an SQL statement 
> corresponding to a parameter query.
> I'd bet on a ADO misinterpretation of your intention caused by the 
> myvalue1, myvalue2 and myvalue3 data types.
> Could you post the DIM statements of those variables ?

Isn't myfieldx timestamp or float type ?
If I remember correctly, ADO builds the SQL statement
  UPDATE mytable SET 
myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3 WHERE (id=6 AND
     myfield1=current_value1 AND myfield2=current_value2 AND 
myfield3=current_value3 )'

The clauses after the "AND" are for optimistic concurrency control by 
vaues but
the clause could be false if it contains timestamp or float field,

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Permalink | Reply | 1 comment |
headers
=?ISO-8859-15?Q?H=E9lder_M._Vieira?= | 2 Apr 2006 13:44

Re: Strange Update query ...

Hiroshi, I made several tests, and the log always shows an UPDATE statement 
filled with constant values, such as:

'UPDATE mytable SET myfield1=4,myfield2=5,myfield3=6
WHERE (id=6 AND myfield1=1 AND myfield2=2 AND myfield3=3 )'

I tried with several data types, with and without explicit declaration of 
the 'myvaluex' variables, and never saw variable names in the generated 
UPDATE statement.

Thomas Chabaud's log shows:

'UPDATE mytable SET myfield1=myvalue1,myfield2=myvalue2,myfield3=myvalue3
WHERE (id=6 AND myfield1=myvalue1 AND myfield2=myvalue2 AND 
myfield3=myvalue3 )'

I can't figure out how variable names could appear in the UPDATE statement, 
so I think Thomas Chabaud should provide some information about the nature 
of those variables.

Hélder M. Vieira

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Permalink | Reply | 0 comments |
headers
Bruce Momjian | 3 Apr 2006 05:40
Picon

Re: problem about maximum row size ?

Tom Lane wrote:
> zhaoxin <zhaox <at> necas.nec.com.cn> writes:
> > I have a question about maximum row size .
> > In pgsql FAQ, I can find this description :
> >   ...
> >   Maximum size for a row? 1.6TB
> >   ...
> 
> > does it mean 1600 text column in a table and 1G every column ?
> 
> > but, when I test this case by psqlodbc, I got some error :
> 
> This was posted in a fairly randomly chosen list, but I think it does
> expose an error in the FAQ: you can't fit 1600 TOAST pointers into one
> row and so the 1.6TB figure is overoptimistic.
> 
> Since TOAST pointers are 20 bytes, a reasonable number for the maximum
> number of large toasted fields is about 400, which would make the
> correct entry for this question 400Gb.  This could be improved by using
> a non-default block size, so this should be listed as one of the limits
> affected by block size.

FAQ updated with new number, and mention that increasing block size
quadruples it.

--

-- 
  Bruce Momjian   http://candle.pha.pa.us

  + If your life is a hard drive, Christ can be your backup. +
(Continue reading)

Permalink | Reply | 0 comments |
headers
Thomas Chabaud | 3 Apr 2006 09:05
Picon

Re: Strange Update query ...

Hélder M. Vieira a écrit :
> I tried to replicate the situation you describe, without success.
> I guess ADO for some reason is trying to build an SQL statement 
> corresponding to a parameter query.
> I'd bet on a ADO misinterpretation of your intention caused by the 
> myvalue1, myvalue2 and myvalue3 data types.
> Could you post the DIM statements of those variables ?
> 
> 
> Helder M. Vieira
> 
> 
> 
> 

I have find the bug, it was an error in the code, there was a
function before my code which changed the values, and the values I supposed 
to be new values in the update statement were in fact the old values ...

Sorry for the waste of time, and thanks a lot for your explanations.

Regards,

Thomas

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
(Continue reading)

Permalink | Reply | 0 comments |
headers
Dave Page | 3 Apr 2006 10:29
Picon

Re: problem about maximum row size ?


> -----Original Message-----
> From: pgsql-odbc-owner <at> postgresql.org 
> [mailto:pgsql-odbc-owner <at> postgresql.org] On Behalf Of Bruce Momjian
> Sent: 03 April 2006 04:41
> To: Tom Lane
> Cc: zhaoxin; pgsql-odbc <at> postgresql.org
> Subject: Re: [ODBC] problem about maximum row size ?
> 
> FAQ updated with new number, and mention that increasing 
> block size quadruples it.

I've updated the limitations page on the website, though I didn't bother
with the blocksize hack on there.

Whilst we're on the subject, is 16TB for a table still correct given CE
partitioning?

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Permalink | Reply | 1 comment |

Gmane