Обсуждение: BYTEA vs BLOB

<div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida
Grande,sans-serif;font-size:13px"><div id="yui_3_16_0_1_1452584894329_20555">Try to migrate from Oracle to Postgres
(9.4.5)on Linux OS.</div><div id="yui_3_16_0_1_1452584894329_20555"><br /></div><div
id="yui_3_16_0_1_1452584894329_20555">Ihave some photos stored in a table, to make it simple, the current (Oracle)
tablelooks like:</div><div id="yui_3_16_0_1_1452584894329_20555"><br /></div><div class=""
id="yui_3_16_0_1_1452584894329_20613"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><b>test_tab</b> <spanid="yui_3_16_0_1_1452584894329_20620" style="line-height:
17.7272720336914px;">(photoBLOB)</span></div><div class="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617"
style="margin-top:0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida
Grande',sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height: 17.7272720336914px;">LOB ("photo")
storeas BASICFILE (tablespace "MYLOB" <strong class="" id="yui_3_16_0_1_1452584894329_20619" style="margin: 0px;
padding:0px; border: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline;">disable storage in
row</strong>...</div><divclass="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617" style="margin-top: 0px;
margin-bottom:0px; border: 0px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif;
vertical-align:baseline; color: rgb(61, 61, 61); line-height: 17.7272720336914px;"><br /></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;">That <spanclass="" id="yui_3_16_0_1_1452584894329_20660" style="font-family: inherit; font-style:
inherit;font-weight: 700; line-height: 17.7272720336914px;">disable storage in row </span><span class=""
id="yui_3_16_0_1_1452584894329_20691"style="font-family: inherit; font-style: inherit; line-height:
17.7272720336914px;">willonly allow a reference to be stored in the table while the actual BLOB data is stored outside
thetable, still inside the Oracle database, though (just NOT in the computer's file system).  </span><span class=""
id="yui_3_16_0_1_1452584894329_20654"style="line-height: 17.7272720336914px;">When issue the </span><strong class=""
id="yui_3_16_0_1_1452584894329_20656"style="line-height: 17.7272720336914px; margin: 0px; padding: 0px; border: 0px;
vertical-align:baseline;">delete from test_tab where id= 12345 </strong><span class="" style="line-height:
17.7272720336914px;margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">lateron</span><span class=""
id="yui_3_16_0_1_1452584894329_20658"style="line-height: 17.7272720336914px;">, the BLOB data will also get deleted,
evenif the BLOB value was stored out of the row at the first place.</span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" id="yui_3_16_0_1_1452584894329_20768" style="line-height: 17.7272720336914px;">Now
ifI migrate the </span><span class="" id="yui_3_16_0_1_1452584894329_20806" style="line-height:
17.7272720336914px;">Oracle </span><spanclass="" id="yui_3_16_0_1_1452584894329_20808" style="line-height:
17.7272720336914px;">tableto Postgres and change the data type to <b id="yui_3_16_0_1_1452584894329_20906">BYTEA</b>,
howwill the photo file (</span><b class="" id="yui_3_16_0_1_1452584894329_20906" style="line-height:
17.7272720336914px;">BYTEA) </b><spanclass="" id="yui_3_16_0_1_1452584894329_20913" style="line-height:
17.7272720336914px;">bestored?</span></div><div class="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617"
style="margin-top:0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida
Grande',sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height: 17.7272720336914px;"><span class=""
style="line-height:17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" id="yui_3_16_0_1_1452584894329_20853" style="line-height: 17.7272720336914px;">1)
Thewhole photo data will be stored inside the table?</span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;">Or</span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" id="yui_3_16_0_1_1452584894329_20854" style="line-height: 17.7272720336914px;">2)
Onlythe reference to the data is stored inside the table, the data itself will be stored outside the table (but still
withinthe database) for efficiency purpose?</span></div><div class="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617"
style="margin-top:0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida
Grande',sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height: 17.7272720336914px;"><span class=""
style="line-height:17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" id="yui_3_16_0_1_1452584894329_20922" style="line-height:
17.7272720336914px;">Thanksto help.</span></div><div class="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617"
style="margin-top:0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida
Grande',sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height: 17.7272720336914px;"><span class=""
style="line-height:17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;">Eugene</span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="margin-top: 0px; margin-bottom: 0px; border: 0px; font-family: 'Helvetica
Neue',Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-align: baseline; color: rgb(61, 61, 61); line-height:
17.7272720336914px;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div></div> 

> On Jan 12, 2016, at 7:45 PM, Eugene Yin <eugeneymail@ymail.com> wrote:
>
> Now if I migrate the Oracle table to Postgres and change the data type to BYTEA, how will the photo file (BYTEA) be
stored?
>
> 1) The whole photo data will be stored inside the table?
>
> Or
>
> 2) Only the reference to the data is stored inside the table, the data itself will be stored outside the table (but
stillwithin the database) for efficiency purpose? 
>

This is a good summary of your options:

https://www.microolap.com/products/connectivity/postgresdac/help/tipsandtricks_byteavsoid.htm

John DeSoi, Ph.D.

<div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida
Grande,sans-serif;font-size:13px"><div dir="ltr" id="yui_3_16_0_1_1452841393493_27146">When use Ora2Pg to migrate the
Oracleto Pg, the BLOB data type in Oracle will supposedly be converted into BYTEA in Pg.  Is this achievable?   <br
/></div><divdir="ltr" id="yui_3_16_0_1_1452841393493_27481"><br /></div><div dir="ltr"
id="yui_3_16_0_1_1452841393493_27482">Ifso, after the data become BYTEA, can I further convert the BYTEA into OID data
type,and how to?</div><div dir="ltr" id="yui_3_16_0_1_1452841393493_27501"><br /></div><div dir="ltr"
id="yui_3_16_0_1_1452841393493_27519"><br/></div><div dir="ltr" id="yui_3_16_0_1_1452841393493_27520"><br /></div><div
dir="ltr"id="yui_3_16_0_1_1452841393493_27521">Thanks <br /></div><div dir="ltr"
id="yui_3_16_0_1_1452841393493_27522"><br/></div><div dir="ltr" id="yui_3_16_0_1_1452841393493_27523">Eugene<br
/></div><divid="yui_3_16_0_1_1452841393493_26693"><span></span></div><div class="qtdSeparateBR"><br /><br /></div><div
class="yahoo_quoted"style="display: block;"><div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial,
LucidaGrande, sans-serif; font-size: 13px;"><div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial,
LucidaGrande, sans-serif; font-size: 16px;"><div dir="ltr"><font face="Arial" size="2"> On Thursday, January 14, 2016
8:05AM, John DeSoi <desoi@pgedit.com> wrote:<br /></font></div><br /><br /><div class="y_msg_container"><div
class="yqt2845761187"id="yqtfd14951"><br clear="none" />> On Jan 12, 2016, at 7:45 PM, Eugene Yin <<a
href="mailto:eugeneymail@ymail.com"shape="rect" ymailto="mailto:eugeneymail@ymail.com">eugeneymail@ymail.com</a>>
wrote:<brclear="none" />> <br clear="none" />> Now if I migrate the Oracle table to Postgres and change the data
typeto BYTEA, how will the photo file (BYTEA) be stored?<br clear="none" />> <br clear="none" />> 1) The whole
photodata will be stored inside the table?<br clear="none" />> <br clear="none" />> Or<br clear="none" />> <br
clear="none"/>> 2) Only the reference to the data is stored inside the table, the data itself will be stored outside
thetable (but still within the database) for efficiency purpose?</div><br clear="none" />> <br clear="none" /><br
clear="none"/>This is a good summary of your options:<br clear="none" /><br clear="none" /><a
href="https://www.microolap.com/products/connectivity/postgresdac/help/tipsandtricks_byteavsoid.htm"shape="rect"
target="_blank">https://www.microolap.com/products/connectivity/postgresdac/help/tipsandtricks_byteavsoid.htm</a><br
clear="none"/><br clear="none" />John DeSoi, Ph.D.<br clear="none" /><br clear="none" /><br clear="none" /><br
clear="none"/>-- <br clear="none" />Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"
shape="rect"ymailto="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br clear="none" />To make changes
toyour subscription:<br clear="none" /><a href="http://www.postgresql.org/mailpref/pgsql-sql" shape="rect"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/><br /></div></div></div></div></div> 

When use Ora2Pg to migrate the Oracle to Pg, the BLOB data type in Oracle will supposedly be converted into BYTEA in Pg.  Is this achievable?  

 

If so, after the data become BYTEA, can I further convert the BYTEA into OID data type, and how to?

begin;

alter table origo_file_rawdata add column lo_data oid;

do $$
declare
    loid oid;
    lfd integer;
    lsize integer;
    d origo_file_rawdata;
begin
    for d IN (select * from origo_file_rawdata) loop
        loid := lo_create(0);
        lfd := lo_open(loid,131072);
        lsize := lowrite(lfd, d.data);
        perform lo_close(lfd);
    update origo_file_rawdata set lo_data = loid where entity_id = d.entity_id;
    end loop;
end;
$$;

alter table origo_file_rawdata alter column lo_data set not null;
alter table origo_file_rawdata drop column data;
alter table origo_file_rawdata rename lo_data to data;
commit;

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

lfd := lo_open(loid,131072);

Why use the file size 131072, instead of other number?

Are there other options?  I mean, under what circumstance, use 131072, or use other size?

Thanks

Eugene

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

What does 131072 mean?  Why not use a bigger or smaller number?    

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com