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

Поиск
Список
Период
Сортировка

BYTEA vs BLOB

От
Eugene Yin
Дата:
Try to migrate from Oracle to Postgres (9.4.5) on Linux OS.

I have some photos stored in a table, to make it simple, the current (Oracle) table looks like:

test_tab (photo BLOB)
LOB ("photo") store as BASICFILE (tablespace "MYLOB" disable storage in row...

That disable storage in row will only allow a reference to be stored in the table while the actual BLOB data is stored outside the table, still inside the Oracle database, though (just NOT in the computer's file system).  When issue the delete from test_tab where id= 12345 lateron, the BLOB data will also get deleted, even if the BLOB value was stored out of the row at the first place.


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 still within the database) for efficiency purpose?



Thanks to help.

Eugene


Re: BYTEA vs BLOB

От
John DeSoi
Дата:
> 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.

Re: BYTEA vs BLOB

От
Eugene Yin
Дата:
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?



Thanks

Eugene


On Thursday, January 14, 2016 8:05 AM, John DeSoi <desoi@pgedit.com> wrote:



> 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 still within 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.



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: BYTEA vs BLOB

От
Andreas Joseph Krogh
Дата:
På lørdag 16. januar 2016 kl. 17:08:05, skrev Eugene Yin <eugeneymail@ymail.com>:
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?
 
Here's how I converted a BYTEA-column to OID:
 
The table origo_file_rawdata contains a column named 'data' of type BYTEA. The trick is to add a new column, 'lo_data' of type=OID, populate it, then drop the old column and rename 'lo_data' to 'data':
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;
 
Hope this helps.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Вложения

Re: BYTEA vs BLOB

От
Eugene Yin
Дата:
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


On Sunday, January 17, 2016 7:02 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:


På lørdag 16. januar 2016 kl. 17:08:05, skrev Eugene Yin <eugeneymail@ymail.com>:
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?
 
Here's how I converted a BYTEA-column to OID:
 
The table origo_file_rawdata contains a column named 'data' of type BYTEA. The trick is to add a new column, 'lo_data' of type=OID, populate it, then drop the old column and rename 'lo_data' to 'data':
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;
 
Hope this helps.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 


Вложения

Re: BYTEA vs BLOB

От
Andreas Joseph Krogh
Дата:
På søndag 17. januar 2016 kl. 17:56:33, skrev Eugene Yin <eugeneymail@ymail.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
 
No particular reason.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Вложения

Re: BYTEA vs BLOB

От
Eugene Yin
Дата:
What does 131072 mean?  Why not use a bigger or smaller number?     


On Sunday, January 17, 2016 9:02 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:


På søndag 17. januar 2016 kl. 17:56:33, skrev Eugene Yin <eugeneymail@ymail.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
 
No particular reason.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 


Вложения

Re: BYTEA vs BLOB

От
Andreas Joseph Krogh
Дата:
På søndag 17. januar 2016 kl. 18:37:34, skrev Eugene Yin <eugeneymail@ymail.com>:
What does 131072 mean?  Why not use a bigger or smaller number?    
 
I don't know, found it in an example and it works.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Вложения

Сайт использует файлы cookie для корректной работы и повышения удобства. Нажимая кнопку «Принять» или продолжая пользоваться сайтом, вы соглашаетесь на их использование в соответствии с Политикой в отношении обработки cookie ООО «ППГ», в том числе на передачу данных из файлов cookie сторонним статистическим и рекламным службам. Вы можете управлять настройками cookie через параметры вашего браузера