Skip site navigation (1) Skip section navigation (2)

Re: lo_export (server)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>
Cc: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: lo_export (server)
Date: 2004-11-29 22:50:12
Message-ID: 1750.1101768612@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Steve Tucknott <steve(at)retsol(dot)co(dot)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

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2004-11-30 04:47:54
Subject: Re: What causes 'FATAL: invalid cache id: 30' in my postgresql log?
Previous:From: Steve TucknottDate: 2004-11-29 21:24:37
Subject: lo_export (server)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group