Обсуждение: Plperl return_next and bytea binary data?

Hi,

I'm trying to generate graphics dynamically from a plperl function, like this:

------------------------------------
CREATE OR REPLACE FUNCTION test_gd
(
    IN a            int4,
    IN b            int4,

    OUT val            int4,
    OUT image        bytea
)
RETURNS SETOF record
AS

$$
    use GD;

    $im = new GD::Image(100,100);
    $white = $im->colorAllocate(255,255,255);
    $black = $im->colorAllocate(0,0,0);
    $im->transparent($white);
    $im->interlaced('false');
    $im->line(0,0,150,150,gdBrushed);
    $im->rectangle(10,10,89,89,$black);
    $im->arc(50,50,95,75,0,360,$black);
    $im->fill(50,50,$black);

    return_next
    (
        {
            val => $_[0] * $_[1],
            image => $im->gif()
        }
    );

    return undef;
$$

LANGUAGE 'plperlu';
------------------------------------

The image, in hexadecimal, is:

------------------------------------
00000000:47 49 46 38 39 61 64 00 64 00 80 00 00 ff ff ff    GIF89ad.d.€..ÿÿÿ
00000010:00 00 00 21 f9 04 01 00 00 00 00 2c 00 00 00 00    ...!ù......,....
00000020:64 00 64 00 00 02 fe 84 8f a9 cb ed 0f a3 9c b4    d.d...þ„©Ëí.£œ´
00000030:da 8b b3 de bc fb 0f 86 e2 48 96 e6 89 a6 ea ca    Ú‹³Þ¼û.†âH–扦êÊ
00000040:b6 ee 0b 63 c1 4c d7 f6 8d e7 fa ce f7 b3 1c 2b    ¶î.cÁL×öçúÎ÷³.+
00000050:05 80 c1 d1 f0 72 14 f9 7e 4a 22 67 e9 eb 24 2b    .€ÁÑðr.ù~J"géë$+
00000060:53 24 f4 5a a5 3a a9 d8 2e 0d b9 8d 78 c7 37 4a    S$ôZ¥:©Ø..¹xÇ7J
00000070:56 72 66 90 d7 65 71 58 cd 8e db 20 69 3a 5d 8e    Vrf×eqXÍŽÛ i:]Ž
00000080:ff 3a ea 0f 7e fe 5f d3 c0 b7 07 07 68 c8 94 30    ÿ:ê.~þ_ÓÀ·..hÈ”0
00000090:28 b8 70 d8 88 78 a0 a8 96 e8 48 39 15 c9 88 50    (¸p؈x ¨–èH9.ɈP
000000a0:a9 09 e9 a4 e9 69 70 a9 70 e4 49 0a 10 9a 68 4a    ©.é¤éip©päI..šhJ
000000b0:aa 0a a4 ba 6a d5 5a fa 0a fb c9 35 fb 79 9a 6a    ª.¤ºjÕZú.ûÉ5ûyšj
000000c0:bb 79 3a a4 bb 69 8a 06 ea 4b c9 d9 37 3a ec 08    »y:¤»iŠ.êKÉÙ7:ì.
000000d0:39 98 84 9c 5c bc 28 dc 6c 98 49 a8 2c 0d 88 2a    9˜„œ\¼(Ül˜I¨,.ˆ*
000000e0:69 7d 9d 97 2d 6a c9 fd 37 89 99 19 de ed 4d 5d    i}—-jÉý7‰™.ÞíM]
000000f0:6e 2e 27 4a be bd be d6 3e ae 0e 1f 3f ff fc 5e    n.'J¾½¾Ö>®..?ÿü^
00000100:ef e5 0e fa ad 4f c6 0f d8 a4 7f 63 02 66 21 08    ïå.ú­OÆ.ؤc.f!.
00000110:50 1e be 68 08 b1 18 f4 d7 f0 ca c3 81 11 a1 4c    P.¾h.±.ô×ðÊÁ.¡L
00000120:a4 57 31 8a c2 b1 7e 14 33 f6 30 58 c5 a3 c5 7b    ¤W1ŠÂ±~.3ö0XÅ£Å{
00000130:0b 45 2e d9 58 d2 e4 47 74 29 55 ee 40 29 0c a3    .E.ÙXÒäGt)Uî@).£
00000140:cb 36 ca b4 31 9c 49 93 23 3f 9c 3a 58 7a e3 99    Ë6Ê´1œI“#?œ:Xzã™
00000150:23 1d b4 5c 40 03 2d fc 76 b3 28 33 45 c7 94 ea    #.´\@.-üv³(3EÇ”ê
00000160:11 68 c7 a9 51 5e 52 bf e0 aa 6a d5 02 d6 47 13    .hÇ©Q^R¿àªjÕ.ÖG.
00000170:b6 b2 92 fa d5 29 11 a5 50 b5 24 35 a9 d3 ec d9    ¶²’úÕ).¥Pµ$5©ÓìÙ
00000180:8c 47 83 c9 ac 28 54 ed db 7f 30 dd 42 24 18 b0    ŒGƒÉ¬(TíÛ0ÝB$.°
00000190:eb 1e ba 36 e5 16 0a d7 e7 0d 23 c0 81 c1 a0 69    ë.º6å..×ç.#ÀÁ i
000001a0:66 d7 ef 1d 5b 66 04 8b 89 a5 38 b1 2c 6c 19 70    f×ï.[f.‹‰¥8±,l.p
000001b0:b5 9d 6c 6f 83 e5 b4 20 46 86 d8 5c b6 c8 07 d0    µloƒå´ F†Ø\¶È.Ð
000001c0:5e 45 9b 3e 8d 3a b5 ea d5 ac 5b bb 7e 0d 3b b6    ^E›>:µêÕ¬[»~.;¶
000001d0:ec d9 b4 6b bb 2e 00 00 3b                         ìÙ´k»...;
------------------------------------

By sniffing the network, I could see that the image is not transferred completely to the client.

Only the first part is being transferred:

------------------------------------
00000000:47 49 46 38 39 61 64                               GIF89ad
------------------------------------

The problem seems to come from the return_next, in conjunction with binary data:

------------------------------------
    return_next
    (
        {
            val => $_[0] * $_[1],
            image => $im->gif()
        }
    );
------------------------------------

Doe anybody know how to solve that problem? When saving data to the hard disk, with GD and perl, examples show that you
haveto force the stream in binary mode, like this: 

------------------------------------
open (MYOUTFILE, ">/tmp/outfile.gif") || die 'Cannot open output file';
binmode MYOUTFILE;
print MYOUTFILE $im->gif();
close MYOUTFILE;
------------------------------------

I tried, and it works...

Is there a trick in order to force binary mode with return_next too?


Thanks for your help!


----------------------------------
Philippe Lang, Ing. Dipl. EPFL
Attik System
rte de la Fonderie 2
1700 Fribourg
Switzerland
http://www.attiksystem.ch

Tel:  +41 (26) 422 13 75
Fax:  +41 (26) 422 13 76

On Mon, Jul 24, 2006 at 11:43:39AM +0200, Philippe Lang wrote:
> The problem seems to come from the return_next, in conjunction with binary data:
>
> ------------------------------------
>     return_next
>     (
>         {
>             val => $_[0] * $_[1],
>             image => $im->gif()
>         }
>     );
> ------------------------------------

I don't know exact how pl/perl works, but given that it has no idea
what type the data is, chances are it's passing it through
cstring-to-text conversion. You probably want to force it to return
bytea or some such (without going through cstring-to-bytea conversion
hopefully). I don't see a way to do it in the documentation
though...


--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:

> On Mon, Jul 24, 2006 at 11:43:39AM +0200, Philippe Lang wrote:
>> The problem seems to come from the return_next, in conjunction with
>> binary data:
>>
>> ------------------------------------
>>     return_next
>>     (
>>         {
>>             val => $_[0] * $_[1],
>>             image => $im->gif()
>>         }
>>     );
>> ------------------------------------
>
> I don't know exact how pl/perl works, but given that it has
> no idea what type the data is, chances are it's passing it
> through cstring-to-text conversion. You probably want to
> force it to return bytea or some such (without going through
> cstring-to-bytea conversion hopefully). I don't see a way to
> do it in the documentation though...

Casting $im->gif() to bytea with $im->gif()::bytea does not help. It even makes things slightly worse: the image
returned(ethereal sniff) is completely empty, where before it was filled with the first characters "GIF89ad" of the
image.

Still searching...

Philippe

pgsql-general-owner@postgresql.org wrote:

> Martijn van Oosterhout wrote:
>
>> On Mon, Jul 24, 2006 at 11:43:39AM +0200, Philippe Lang wrote:
>>> The problem seems to come from the return_next, in conjunction with
>>> binary data:
>>>
>>> ------------------------------------
>>>     return_next
>>>     (
>>>         {
>>>             val => $_[0] * $_[1],
>>>             image => $im->gif()
>>>         }
>>>     );
>>> ------------------------------------
>>
>> I don't know exact how pl/perl works, but given that it has no idea
>> what type the data is, chances are it's passing it through
>> cstring-to-text conversion. You probably want to force it to return
>> bytea or some such (without going through cstring-to-bytea conversion
>> hopefully). I don't see a way to do it in the documentation though...
>
> Casting $im->gif() to bytea with $im->gif()::bytea does not
> help. It even makes things slightly worse: the image returned
> (ethereal sniff) is completely empty, where before it was
> filled with the first characters "GIF89ad" of the image.
>
> Still searching...

I finally found a solution to my problem by using a base64 encoded gif image, in conjunction with a text column:

----------------------------------
CREATE OR REPLACE FUNCTION test_gd
(
    IN a            int4,
    IN b            int4,

    OUT val            int4,
    OUT image        text
)
RETURNS SETOF record
AS

$$
    use GD;
    use MIME::Base64::Perl;

    $im = new GD::Image(100,100);
    $white = $im->colorAllocate(255,255,255);
    $black = $im->colorAllocate(0,0,0);
    $red = $im->colorAllocate(255,0,0);
    $im->transparent($white);
    $im->rectangle(0,0,89,89,$black);
    $im->arc(50,30,95,75,0,360,$black);
    $im->fill(50,50,$red);

    $image = $im->gif();

    return_next
    (
        {
            val => $_[0] * $_[1],
            image => encode_base64($image, '')
        }
    );

    return undef;
$$

LANGUAGE 'plperlu';
----------------------------------

For the small technical drawings this system is suppose to handle, this is just fine.


Juste one more question: what is the maximum size of a TEXT column in PG 8.1.4?

---------------
Philippe Lang
Attik System

Philippe Lang wrote:

> Juste one more question: what is the maximum size of a TEXT column in
> PG 8.1.4?

A handful of bytes less than 1 GB.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

> Philippe Lang wrote:
>
>> Juste one more question: what is the maximum size of a TEXT column in
>> PG 8.1.4?
>
> A handful of bytes less than 1 GB.

It should be ok then... :)

---------------
Philippe Lang
Attik System