Postgres Pro
Downloads
Home   >   mailing lists

Thread: Starnge things with big datas...

Starnge things with big datas...

From
"F. BROUARD / SQLpro"
Date:
Hi there,

"PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit"
catalog version : PG_9.1_201105231

--> the table :

CREATE  TABLE T_TEST_TAILLE
(TTT_ID            SERIAL NOT NULL PRIMARY KEY,
  TTT_DATAFIX       CHAR(16),
  TTT_DATAVAR       VARCHAR(16),
  TTT_DATAVARBIG    VARCHAR(6000));

--> the inserts :

INSERT INTO T_TEST_TAILLE (TTT_DATAFIX, TTT_DATAVAR, TTT_DATAVARBIG)
            SELECT     NULL, NULL, NULL
UNION ALL  SELECT '', '', ''
UNION ALL  SELECT '12345678', '12345678', REPEAT('*', 3000)
UNION ALL  SELECT '1234567890123456', '1234567890123456',
                   REPEAT('*', 6000)

--> the query

SELECT *, pg_column_size(TTT_DATAFIX),
           char_length(TTT_DATAFIX),
           octet_length(TTT_DATAFIX),
           pg_column_size(TTT_DATAVAR),
           char_length(TTT_DATAVAR),
           octet_length(TTT_DATAFIX),
           pg_column_size(TTT_DATAVARBIG),
           char_length(TTT_DATAVARBIG),
           octet_length(TTT_DATAFIX)
FROM   T_TEST_TAILLE;

!!! PROBLEM !!!

--> at this time the 4th row show nothing in ttt_datavarbig column...
it is supposed to retrive the long '********************* ... '
6000 characters !

What happened ?

--> now, with this insert :

WITH RECURSIVE
S AS (SELECT generate_series AS I FROM generate_series(1, 6000)),
T AS (SELECT I, chr(20 + CAST(ceiling(random()*236) AS SMALLINT)) AS C
       FROM   S),
R AS (SELECT CAST(C AS VARCHAR(6000)) AS CC, C, I
       FROM   T
       WHERE  I = 1
       UNION ALL
       SELECT CAST(CC || T.C AS VARCHAR(6000)),T.C,  T.I
       FROM   T
              INNER JOIN R
                    ON T.I = R.I + 1)
INSERT INTO T_TEST_TAILLE (TTT_DATAVARBIG)
SELECT CC
FROM   R
WHERE  I = 6000;

One row appear with somtehing like :

"YÓ³Ó¡Àÿ-ÅĀöÕñù:’럙͇§-ƒÁbܺúJÆ+÷+~ð ...

6000 characters... ok !

thanks

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

Re: Starnge things with big datas...

From
Andreas Kretschmer
Date:
F. BROUARD / SQLpro <sqlpro@club-internet.fr> wrote:

> Hi there,
>
> "PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit"
> catalog version : PG_9.1_201105231
>
> --> the table :
>
> CREATE  TABLE T_TEST_TAILLE
> (TTT_ID            SERIAL NOT NULL PRIMARY KEY,
>  TTT_DATAFIX       CHAR(16),
>  TTT_DATAVAR       VARCHAR(16),
>  TTT_DATAVARBIG    VARCHAR(6000));
>
> --> the inserts :
>
> INSERT INTO T_TEST_TAILLE (TTT_DATAFIX, TTT_DATAVAR, TTT_DATAVARBIG)
>            SELECT     NULL, NULL, NULL
> UNION ALL  SELECT '', '', ''
> UNION ALL  SELECT '12345678', '12345678', REPEAT('*', 3000)
> UNION ALL  SELECT '1234567890123456', '1234567890123456',
>                   REPEAT('*', 6000)
>
> --> the query
>
> SELECT *, pg_column_size(TTT_DATAFIX),
>           char_length(TTT_DATAFIX),
>           octet_length(TTT_DATAFIX),
>           pg_column_size(TTT_DATAVAR),
>           char_length(TTT_DATAVAR),
>           octet_length(TTT_DATAFIX),
>           pg_column_size(TTT_DATAVARBIG),
>           char_length(TTT_DATAVARBIG),
>           octet_length(TTT_DATAFIX)
> FROM   T_TEST_TAILLE;
>
> !!! PROBLEM !!!
>
> --> at this time the 4th row show nothing in ttt_datavarbig column...
> it is supposed to retrive the long '********************* ... '
> 6000 characters !
>
> What happened ?

try this:

,----[  my test  ]
| kretschmer@tux:~$ psql test -c "SELECT *, pg_column_size(TTT_DATAFIX),
|           char_length(TTT_DATAFIX),
|           octet_length(TTT_DATAFIX),
|           pg_column_size(TTT_DATAVAR),
|           char_length(TTT_DATAVAR),
|           octet_length(TTT_DATAFIX),
|           pg_column_size(TTT_DATAVARBIG),
|           char_length(TTT_DATAVARBIG),
|           octet_length(TTT_DATAFIX)
| FROM   T_TEST_TAILLE;" > out.txt
| kretschmer@tux:~$ ls -l out.txt
| -rw-r--r-- 1 kretschmer kretschmer 37134 2012-04-21 16:32 out.txt
`----

The result contains more than 37.000 chars - i think, including the 6000
+ 3000 '*' plus some '-' for the table and so on - you can read that
file with your favorite eidtor...




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Starnge things with big datas...

From
Guillaume Lelarge
Date:
On Sat, 2012-04-21 at 16:17 +0200, F. BROUARD / SQLpro wrote:
> Hi there,
>
> "PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit"
> catalog version : PG_9.1_201105231
>
> --> the table :
>
> CREATE  TABLE T_TEST_TAILLE
> (TTT_ID            SERIAL NOT NULL PRIMARY KEY,
>   TTT_DATAFIX       CHAR(16),
>   TTT_DATAVAR       VARCHAR(16),
>   TTT_DATAVARBIG    VARCHAR(6000));
>
> --> the inserts :
>
> INSERT INTO T_TEST_TAILLE (TTT_DATAFIX, TTT_DATAVAR, TTT_DATAVARBIG)
>             SELECT     NULL, NULL, NULL
> UNION ALL  SELECT '', '', ''
> UNION ALL  SELECT '12345678', '12345678', REPEAT('*', 3000)
> UNION ALL  SELECT '1234567890123456', '1234567890123456',
>                    REPEAT('*', 6000)
>
> --> the query
>
> SELECT *, pg_column_size(TTT_DATAFIX),
>            char_length(TTT_DATAFIX),
>            octet_length(TTT_DATAFIX),
>            pg_column_size(TTT_DATAVAR),
>            char_length(TTT_DATAVAR),
>            octet_length(TTT_DATAFIX),
>            pg_column_size(TTT_DATAVARBIG),
>            char_length(TTT_DATAVARBIG),
>            octet_length(TTT_DATAFIX)
> FROM   T_TEST_TAILLE;
>
> !!! PROBLEM !!!
>
> --> at this time the 4th row show nothing in ttt_datavarbig column...
> it is supposed to retrive the long '********************* ... '
> 6000 characters !
>
> What happened ?
>

Let me guess. You used pgAdmin, right? The widget pgAdmin uses on its
query tool doesn't allow us to show really big values. Sometimes, it
just doesn't display the cell at all. Kinda lame, and I hope I'll be
able to fix this one day.

Try with psql as Andreas told you, you'll see your long value.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Re: Starnge things with big datas...

From
Guillaume Lelarge
Date:
On Sat, 2012-04-21 at 18:01 +0200, F. BROUARD / SQLpro wrote:
> Le 21/04/2012 16:54, Guillaume Lelarge a écrit :
> > On Sat, 2012-04-21 at 16:17 +0200, F. BROUARD / SQLpro wrote:
> >> Hi there,
> >>
> >> "PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit"
> >> catalog version : PG_9.1_201105231
> >>
> >> -->  the table :
> >>
> >> CREATE  TABLE T_TEST_TAILLE
> >> (TTT_ID            SERIAL NOT NULL PRIMARY KEY,
> >>    TTT_DATAFIX       CHAR(16),
> >>    TTT_DATAVAR       VARCHAR(16),
> >>    TTT_DATAVARBIG    VARCHAR(6000));
> >>
> >> -->  the inserts :
> >>
> >> INSERT INTO T_TEST_TAILLE (TTT_DATAFIX, TTT_DATAVAR, TTT_DATAVARBIG)
> >>              SELECT     NULL, NULL, NULL
> >> UNION ALL  SELECT '', '', ''
> >> UNION ALL  SELECT '12345678', '12345678', REPEAT('*', 3000)
> >> UNION ALL  SELECT '1234567890123456', '1234567890123456',
> >>                     REPEAT('*', 6000)
> >>
> >> -->  the query
> >>
> >> SELECT *, pg_column_size(TTT_DATAFIX),
> >>             char_length(TTT_DATAFIX),
> >>             octet_length(TTT_DATAFIX),
> >>             pg_column_size(TTT_DATAVAR),
> >>             char_length(TTT_DATAVAR),
> >>             octet_length(TTT_DATAFIX),
> >>             pg_column_size(TTT_DATAVARBIG),
> >>             char_length(TTT_DATAVARBIG),
> >>             octet_length(TTT_DATAFIX)
> >> FROM   T_TEST_TAILLE;
> >>
> >> !!! PROBLEM !!!
> >>
> >> -->  at this time the 4th row show nothing in ttt_datavarbig column...
> >> it is supposed to retrive the long '********************* ... '
> >> 6000 characters !
> >>
> >> What happened ?
> >>
> >
> > Let me guess. You used pgAdmin, right? The widget pgAdmin uses on its
> > query tool doesn't allow us to show really big values. Sometimes, it
> > just doesn't display the cell at all. Kinda lame, and I hope I'll be
> > able to fix this one day.
> >
> > Try with psql as Andreas told you, you'll see your long value.
> >
> >
> yes, PGAdminIII
>
> OK, this is bizarre... In SQL Server management Studio, you can fix the
> column limit and the first n octets/chars are shown.
>

Different tools, different behaviours. Anyway, this is clearly a bug in
pgAdmin, I won't deny it.

The solution offered by the tool you mentionned would be a nice one for
pgAdmin.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com