Thread: lo_export (server)

PostgreSQL 7.4.5 Redhat 8

I have a cursor:
SELECT  lo_export(document,'/tmp/vendBImage.png') AS image,
             docForeignRecNo AS vendBRecNo
FROM document  AS document
         JOIN lookupCodes
                 ON document.docuLookUpCodesRecNo = lookUpCodes.recNo
                AND lookUpCodes.subClassification = 'P' 
                AND document.docForeignTableName = 'vendorbus'
                AND document.docForeignRecNo IN     
                       (SELECT vendorBusRecNo        
                        FROM propmatch AS match        
                       WHERE match.buyerRecNo = 6) 
ORDER BY vendBRecNo

The cursor works fine - apart from the fact that the output image always stays the same- whatever happens to be the first image returned remains for each row read (I can see this by stopping the cursor after each read and looking at the exported image).
If the cursor is changed to make the output file name unique (ie '/tmp/vendBImage||TRUNC(RANDOM()*10000)||'.png') then I can see the correct image in the unique filename after each row read. Any ideas as to why the original image appears not to be overwritten when using a fixed output file name? (I need to use a fixed name as I need a known value to use in subsequent copy statement - the cursor is read in the program and for each row read the image is copied away)

I have tried running the statement in psql with the same result
SELECT  lo_export(document,'/tmp/vendBImage.png') AS image, docForeignRecNo AS vendBRecNo FROM document  AS document JOIN lookupCodes ON document.docuLookUpCodesRecNo = lookUpCodes.recNo AND lookUpCodes.subClassification = 'P'
AND document.docForeignTableName = 'vendorbus' AND document.docForeignRecNo IN
(3,4,2) order by vendbrecNo;
image | vendbrecno
-------+------------
     1 |          2
     1 |          3
     1 |          3
     1 |          3
     1 |          4
(5 rows)

The image in /tmp at the end is that of record 2. If however, I change the statement to:
SELECT  lo_export(document,'/tmp/vendBImage.png') AS image, docForeignRecNo AS
vendBRecNo FROM document  AS document JOIN lookupCodes ON document.docuLookUpCodesRecNo = lookUpCodes.recNo AND lookUpCodes.subClassification = 'P'
AND document.docForeignTableName = 'vendorbus' AND document.docForeignRecNo IN
(3) order by vendbrecNo;

Then I see image for record 3 - ditto for record 4 (record 4's image and 2's image do differ - as I know that by running this in psql I sholuld only expect to see the image for rec 4)

I feel that I'm doing something really stupid in the code, but I can't see what it is!

Any help would be appreciated.

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769

Steve Tucknott <steve@retsol.co.uk> writes:
> I have a cursor:
> SELECT  lo_export(document,'/tmp/vendBImage.png') AS image,
>              docForeignRecNo AS vendBRecNo
> FROM document  AS document
>          JOIN lookupCodes
>                  ON document.docuLookUpCodesRecNo = lookUpCodes.recNo
>                 AND lookUpCodes.subClassification = 'P'
>                 AND document.docForeignTableName = 'vendorbus'
>                 AND document.docForeignRecNo IN
>                        (SELECT vendorBusRecNo
>                         FROM propmatch AS match
>                        WHERE match.buyerRecNo = 6)
> ORDER BY vendBRecNo

[ itch... ]  This query is assuming that you know the exact timing of
the side-effects of the lo_export() function, which you do not.

I think the problem is that lo_export() will be evaluated before the
ORDER BY step occurs, ie, all the calls are executed in whatever order
the JOIN produces.  Then the sort occurs, and then the cursor mechanism
hands back the rows one at a time.  But by the time you see the first
row, the last execution of lo_export() has already overwritten all the
earlier outputs.

You could probably make it work as

SELECT  lo_export(document,'/tmp/vendBImage.png') AS image,
             docForeignRecNo AS vendBRecNo
FROM
(SELECT *
 FROM
 document  AS document
          JOIN lookupCodes
                  ON document.docuLookUpCodesRecNo = lookUpCodes.recNo
                 AND lookUpCodes.subClassification = 'P'
                 AND document.docForeignTableName = 'vendorbus'
                 AND document.docForeignRecNo IN
                        (SELECT vendorBusRecNo
                         FROM propmatch AS match
                        WHERE match.buyerRecNo = 6)
 ORDER BY vendBRecNo
) subsel;

but this is still assuming a lot more than you ought to about the
execution engine's behavior.  In particular you could easily have
off-by-one problems (say, the engine executing one row ahead of what
you've gotten back), and there's not going to be a lot of sympathy for
treating that as a bug.

My recommendation is not to execute more than one lo_export() per query,
if you expect them to overwrite the same file each time.

            regards, tom lane