what are correct steps to programmatically write/read large objects to/from a data base table?

[Ian Bell]

Hello,



I have just started working with PostgreSQL (v10.3) running under Window 10.
Also I am new to SQL in general. I am writing test code to see how to
write/read large objects and have a question about the order/number of steps
when executing code written in C/C++ and C#.



I want to write and read binary data as large objects.



1) In PSQL, I do this using the following commands:

create database mydb;

\c mydb;

create table test( name text, mylargevariable oid);

insert into test( name, mylargevariable) values( 'n1',
lo_import('f:/temp/test.txt') );

select convert_from( lo_get(test.large), 'UTF8') from test;



The point of this example is that the large object id value ('OID') is
stored in the 'mylargevariable' field of the table 'test'. My question is
about how to programmatically do this using code written in C/C++ and C#.
I address this in my next point.



2) The PostgreSQL sample code
<https://www.postgresql.org/docs/10/static/lo-examplesect.html> illustrates
how to write/read a large object in C/C++ while the NPGSQL sample code
<http://www.npgsql.org/doc/large-objects.html> illustrates how to
write/read large objects in C#. I am able to successfully compile and run
both sets of sample code. Here are my questions:

a. My understanding is these examples write the binary data as a large
object to the 'pg_largeobject' table. Would you please confirm this is
correct?

b. If I want to be able to retrieve the binary data associated with a
large object stored in the 'pg_largeobject' table at a later time (i.e.
start a new process or run a new application) then I will have to
persist/save/write the OID's referencing the large object to a table when
the large object is first created. So the pseudo code for both C/C++ and C#
programs would be as follow:

i.
Create my database and create my own table which will store large object
OIDs

ii. Create
a large object OID (e.g. call the function 'lo_creat' in the C libpg
library)

iii. Write
the binary data to this large object (e.g. call the function 'lo_write' in
the C libpg library)

iv. Write
the large object OID generated in step ii to a PostgreSQL table that I
created in step i

Would you please confirm these steps and their order are correct? The
reason for asking this question is I want to confirm that i) while non-large
objects require only one write to the data base ii) in contrast,
large-objects require two writes (i.e. the first to write the large object,
the second to insert/write the large object OID to a table field). I
actually have written test code that does just this and it seems to be
working. However, given my lack of experience with PostgreSQL and SQL in
general, I would like confirmation the writing/reading binary data large
object is this simple and that I have not missed something important.



Thank you



Ian

[Laurenz Albe]

Post by Ian Bell
I have just started working with PostgreSQL (v10.3) running under Window 10.
Also I am new to SQL in general. I am writing test code to see how to write/read
large objects and have a question about the order/number of steps when
executing code written in C/C++ and C#.
I want to write and read binary data as large objects.
create database mydb;
\c mydb;
create table test( name text, mylargevariable oid);
insert into test( name, mylargevariable) values( ‘n1’, lo_import(‘f:/temp/test.txt’) );
select convert_from( lo_get(test.large), ‘UTF8’) from test;
The point of this example is that the large object id value (‘OID’) is stored in the
‘mylargevariable’ field of the table ‘test’. My question is about how to programmatically
do this using code written in C/C++ and C#. I address this in my next point.
2) The PostgreSQL sample code illustrates how to write/read a large object in C/C++
while the NPGSQL sample code illustrates how to write/read large objects in C#.
I am able to successfully compile and run both sets of sample code.
a. My understanding is these examples write the binary data as a large object to the
‘pg_largeobject’ table. Would you please confirm this is correct?
b. If I want to be able to retrieve the binary data associated with a large object stored
in the ‘pg_largeobject’ table at a later time (i.e. start a new process or run a
new application) then I will have to persist/save/write the OID’s referencing the
large object to a table when the large object is first created. So the pseudo code
i. Create my database and create my own table which will store large object OIDs
ii. Create a large object OID (e.g. call the function ‘lo_creat’ in the C libpg library)
iii. Write the binary data to this large object (e.g. call the function ‘lo_write’ in the
C libpg library)
iv. Write the large object OID generated in step ii to a PostgreSQL table that I created in step i
Would you please confirm these steps and their order are correct? The reason for asking
this question is I want to confirm that i) while non-large objects require only one write
to the data base ii) in contrast, large-objects require two writes (i.e. the first to write
the large object, the second to insert/write the large object OID to a table field).
I actually have written test code that does just this and it seems to be working.
However, given my lack of experience with PostgreSQL and SQL in general, I would like
confirmation the writing/reading binary data large object is this simple and that I have not
missed something important.

That is all correct, and I think you have understood large objects well.

Have a look at the "lo" contrib: https://www.postgresql.org/docs/current/static/lo.html
Some of its functionality might help you to manage the large objects.

I'd like to remark that adding large "bytea" values to a table also affects
two tables: The table itself and the "TOAST table" where the bytea will
be stored out of line. But of course it is only one client-server
round trip.

Yours,
Laurenz Albe

[Ian Bell]

Hello Able,

Thanks for confirming.

I was not aware of the lo module and indeed have not yet gotten to the appendices in the Postgre manual. Your reference was very helpful. It answered another question I had.

Thank you,

Ian

-----Original Message-----
From: Laurenz Albe [mailto:***@cybertec.at]
Sent: 12 April, 2018 02:43
To: ***@ianbellsoftware.com; pgsql-***@lists.postgresql.org
Subject: Re: what are correct steps to programmatically write/read large objects to/from a data base table?

Post by Ian Bell
I have just started working with PostgreSQL (v10.3) running under Window 10.
Also I am new to SQL in general. I am writing test code to see how to
write/read large objects and have a question about the order/number of
steps when executing code written in C/C++ and C#.
I want to write and read binary data as large objects.
create database mydb;
\c mydb;
create table test( name text, mylargevariable oid); insert into test(
name, mylargevariable) values( ‘n1’, lo_import(‘f:/temp/test.txt’) );
select convert_from( lo_get(test.large), ‘UTF8’) from test;
The point of this example is that the large object id value (‘OID’) is stored in the
‘mylargevariable’ field of the table ‘test’. My question is about how to programmatically
do this using code written in C/C++ and C#. I address this in my next point.
2) The PostgreSQL sample code illustrates how to write/read a large object in C/C++
while the NPGSQL sample code illustrates how to write/read large objects in C#.
I am able to successfully compile and run both sets of sample code.
a. My understanding is these examples write the binary data as a large object to the
‘pg_largeobject’ table. Would you please confirm this is correct?
b. If I want to be able to retrieve the binary data associated with a large object stored
in the ‘pg_largeobject’ table at a later time (i.e. start a new process or run a
new application) then I will have to persist/save/write the OID’s referencing the
large object to a table when the large object is first created. So the pseudo code
i. Create my database and create my own table which will store large object OIDs
ii. Create a large object OID (e.g. call the function ‘lo_creat’ in the C libpg library)
iii. Write the binary data to this large object (e.g. call the function ‘lo_write’ in the
C libpg library)
iv. Write the large object OID generated in step ii to a PostgreSQL table that I created in step i
Would you please confirm these steps and their order are correct? The
reason for asking this question is I want to confirm that i) while
non-large objects require only one write to the data base ii) in
contrast, large-objects require two writes (i.e. the first to write the large object, the second to insert/write the large object OID to a table field).
I actually have written test code that does just this and it seems to be working.
However, given my lack of experience with PostgreSQL and SQL in
general, I would like confirmation the writing/reading binary data
large object is this simple and that I have not missed something important.

That is all correct, and I think you have understood large objects well.

Have a look at the "lo" contrib: https://www.postgresql.org/docs/current/static/lo.html
Some of its functionality might help you to manage the large objects.

I'd like to remark that adding large "bytea" values to a table also affects two tables: The table itself and the "TOAST table" where the bytea will be stored out of line. But of course it is only one client-server round trip.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com