agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedLarge Objects
16+ messages / 13 participants
[nested] [flat]
* Large Objects
@ 1999-07-05 23:57 Eid, Bilal <Bilal_Eid@NAI.com>
0 siblings, 1 reply; 16+ messages in thread
From: Eid, Bilal @ 1999-07-05 23:57 UTC (permalink / raw)
To: pgsql-sql
Hi everyone,
My name is Bilal Eid, I'm software Engineer "consultant" at Network
Associates.
I'm in middle of an ODBC application for the PostgreSQL. On Linux platform.
This application is a security application for Network Associates. I need
your help with the following issue.
The problem I'm facing is that the table and its columns are too large, Some
columns are exceeding 16K, and the whole table might come close to 64k in
total.
I understand that the solution is to use the Large Object technique as it
mention in FAQ, and its syntax Chapter 15 of PostgreSQL Programmer's Guide.
It works fine when I used the interactive on-line tool "psql", following the
example of "Built in registered functions":
CREATE TABLE image (
name text,
raster oid
);
INSERT INTO image (name, raster)
VALUES ('beautiful image', lo_import('/etc/motd'));
SELECT lo_export(image.raster, "/tmp/motd") from image
WHERE name = 'beautiful image';
The problem with that is I have to read and write to a file. In my case I'm
reading from a buffer so I can not use this technique as it.
The other way "programming one using the Large Object Functions:
Oid lo_creat(PGconn *conn, int mode) inv_oid =
lo_creat(INV_READ|INV_WRITE|INV_ARCHIVE);
Oid lo_import(PGconn *conn, text *filename)
int lo_open(PGconn *conn, Oid lobjId, int mode, ...)
int lo_export(PGconn *conn, Oid lobjId, text *filename)
int lo_write(PGconn *conn, int fd, char *buf, int len)
int lo_lseek(PGconn *conn, int fd, int offset, int whence)
int lo_close(PGconn *conn, int fd)
As you see, according to these functions the object will be created and
attached to the database not to a specific table in the database, I tried a
lot of possibility I could think of to make it work. to accessing an
existing object on a table and read/write from it from/to a buffer, but with
out any success.
Again, and in short, I need to attach an object to a table using the iODBC
application and using buffer instead of file as input/output.
Is there any way to do that? I need your help and may be some example to
follow.
Please, Help me I'm running out of time.
Note: I'm already connected to the database at the time of read/write to the
table.
Your help is gratefully appreciated
And thank you in advance
Network Associates Inc.
Bilal Eid
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [SQL] Large Objects
@ 1999-07-06 00:17 Chris Bitmead <cbitmead@ozemail.com.au>
parent: Eid, Bilal <Bilal_Eid@NAI.com>
0 siblings, 0 replies; 16+ messages in thread
From: Chris Bitmead @ 1999-07-06 00:17 UTC (permalink / raw)
To: pgsql-sql
If I understand your question correctly, the answer is that lo_creat
returns an oid. You need to have a column in a regular table of type oid
and store this oid in there. When you query the table later you can get
the oid and pass it to lo_open.
"Eid, Bilal" wrote:
> Again, and in short, I need to attach an object to a table using the iODBC
> application and using buffer instead of file as input/output.
> Is there any way to do that? I need your help and may be some example to
> follow.
> Please, Help me I'm running out of time.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Large Objects
@ 1999-07-06 18:42 Eid, Bilal <Bilal_Eid@NAI.com>
0 siblings, 0 replies; 16+ messages in thread
From: Eid, Bilal @ 1999-07-06 18:42 UTC (permalink / raw)
To: pgsql-sql
Hi everyone,
Anyone can help me, I came to a halt, I tried several ways, but no success.
I need to access, read-in, write-out to an object in a table from/ to a
buffer.
The examples in the Documentation are either through PSQL on-lin-intractive
process. which read/write to a file only.
or the sample program that I need to pass "lobjid" which I do not have when
I start, and it read/write to the database itself (not to a table which has
a column defined as oid).
Please someone give a hint, I'm in a desperate situation.
Thank you all.
Network Associates Inc.
Bilal Eid
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2000-10-24 03:29 pgsql-sql <pgsql-sql@fc.emc.com.ph>
0 siblings, 0 replies; 16+ messages in thread
From: pgsql-sql @ 2000-10-24 03:29 UTC (permalink / raw)
To: pgsql-sql; craig.may@s2.enthdimension.com.au
FROM test.pl of DBD-Pg-0.93.tar ...
######################### test large objects
# create large object from binary file
my ($ascii, $pgin);
foreach $ascii (0..255) {
$pgin .= chr($ascii);
};
my $PGIN = '/tmp/pgin';
open(PGIN, ">$PGIN") or die "can not open $PGIN";
print PGIN $pgin;
close PGIN;
# begin transaction
$dbh->{AutoCommit} = 0;
my $lobjId;
( $lobjId = $dbh->func($PGIN, 'lo_import') )
and print "\$dbh->func(lo_import) ...... ok\n"
or print "\$dbh->func(lo_import) ...... not ok\n";
# end transaction
$dbh->{AutoCommit} = 1;
unlink $PGIN;
# blob_read
# begin transaction
$dbh->{AutoCommit} = 0;
$sth = $dbh->prepare( "" ) or die $DBI::errstr;
my $blob;
( $blob = $sth->blob_read($lobjId, 0, 0) )
and print "\$sth->blob_read ............ ok\n"
or print "\$sth->blob_read ............ not ok\n";
$sth->finish or die $DBI::errstr;
# end transaction
$dbh->{AutoCommit} = 1;
# read large object using lo-functions
# begin transaction
$dbh->{AutoCommit} = 0;
my $lobj_fd; # may be 0
( defined($lobj_fd = $dbh->func($lobjId, $dbh->{pg_INV_READ}, 'lo_open')) )
and print "\$dbh->func(lo_open) ........ ok\n"
or print "\$dbh->func(lo_open) ........ not ok\n";
( 0 == $dbh->func($lobj_fd, 0, 0, 'lo_lseek') )
and print "\$dbh->func(lo_lseek) ....... ok\n"
or print "\$dbh->func(lo_lseek) ....... not ok\n";
my $buf = '';
( 256 == $dbh->func($lobj_fd, $buf, 256, 'lo_read') )
and print "\$dbh->func(lo_read) ........ ok\n"
or print "\$dbh->func(lo_read) ........ not ok\n";
( 256 == $dbh->func($lobj_fd, 'lo_tell') )
and print "\$dbh->func(lo_tell) ........ ok\n"
or print "\$dbh->func(lo_tell) ........ not ok\n";
( $dbh->func($lobj_fd, 'lo_close') )
and print "\$dbh->func(lo_close) ....... ok\n"
or print "\$dbh->func(lo_close) ....... not ok\n";
( $dbh->func($lobjId, 'lo_unlink') )
and print "\$dbh->func(lo_unlink) ...... ok\n"
or print "\$dbh->func(lo_unlink) ...... not ok\n";
# end transaction
$dbh->{AutoCommit} = 1;
# compare large objects
( $pgin cmp $buf and $pgin cmp $blob )
and print "compare blobs .............. not ok\n"
or print "compare blobs .............. ok\n";
#########################
craig.may@s2.enthdimension.com.au writes:
>Hi,
>
>Could someone please provide a demo of creating the type "Lo".
>
>Regards,
>Craig May
>
>Enth Dimension
>http://www.enthdimension.com.au
^ permalink raw reply [nested|flat] 16+ messages in thread
* Large Objects
@ 2000-10-24 10:07 Craig May <craig.may@s2.enthdimension.com.au>
0 siblings, 0 replies; 16+ messages in thread
From: Craig May @ 2000-10-24 10:07 UTC (permalink / raw)
To: pgsql-sql
Hi,
Could someone please provide a demo of creating the type "Lo".
Regards,
Craig May
Enth Dimension
http://www.enthdimension.com.au
^ permalink raw reply [nested|flat] 16+ messages in thread
* Large Objects
@ 2002-02-27 13:45 Hunter, Ray <rhunter@enterasys.com>
0 siblings, 3 replies; 16+ messages in thread
From: Hunter, Ray @ 2002-02-27 13:45 UTC (permalink / raw)
To: pgsql-sql
I have been reading the user manual for largre objects in postgres. What I
do not understand is when and why you would use them?
Can someone point me to some good documentation on this...
Thanks,
Ray Hunter
Firmware Engineer
ENTERASYS NETWORKS
Phone: 801 887-9888
Fax: 801 972-5789
Email: rhunter@enterasys.com <mailto:rhunter@enterasys.com>
www: www.enterasys.com <http://www.enterasys.com;
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2002-02-27 21:07 postgresql <pgsql@symcom.com>
parent: Hunter, Ray <rhunter@enterasys.com>
2 siblings, 0 replies; 16+ messages in thread
From: postgresql @ 2002-02-27 21:07 UTC (permalink / raw)
To: Hunter, Ray <rhunter@enterasys.com>; pgsql-sql
Ray,
A large object is simply a large amount of data. Like a tif file or any
other data that you would like to store. Personally, I would not store a
large object in the database. Instead, use the locale hard disk and refer
to the path to the object. Your front end would receive the path and
request the OS for the file.
ted
-----Original Message-----
From: "Hunter, Ray" <rhunter@enterasys.com>
To: pgsql-sql@postgresql.org
Date: Wed, 27 Feb 2002 08:45:54 -0500
Subject: [SQL] Large Objects
> I have been reading the user manual for largre objects in postgres.
> What I
> do not understand is when and why you would use them?
>
> Can someone point me to some good documentation on this...
>
>
> Thanks,
>
>
> Ray Hunter
> Firmware Engineer
>
> ENTERASYS NETWORKS
>
> Phone: 801 887-9888
> Fax: 801 972-5789
> Email: rhunter@enterasys.com <mailto:rhunter@enterasys.com>
> www: www.enterasys.com <http://www.enterasys.com;
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2002-02-28 01:05 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Hunter, Ray <rhunter@enterasys.com>
2 siblings, 1 reply; 16+ messages in thread
From: Christopher Kings-Lynne @ 2002-02-28 01:05 UTC (permalink / raw)
To: Hunter, Ray <rhunter@enterasys.com>; pgsql-sql
Large ObjectsDon't use them. They were needed when Postgres only supported
8k per row. Now you can just use the 'text' datatype for text data and the
'bytea' datatype for binary data. You have a limit of a few gigs per row
with them.
Chris
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Hunter, Ray
Sent: Wednesday, 27 February 2002 9:46 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Large Objects
I have been reading the user manual for largre objects in postgres. What
I do not understand is when and why you would use them?
Can someone point me to some good documentation on this...
Thanks,
Ray Hunter
Firmware Engineer
ENTERASYS NETWORKS
Phone: 801 887-9888
Fax: 801 972-5789
Email: rhunter@enterasys.com
www: www.enterasys.com
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2002-02-28 02:36 Erle Czar Mantos <erle@hindang.msuiit.edu.ph>
parent: Hunter, Ray <rhunter@enterasys.com>
2 siblings, 0 replies; 16+ messages in thread
From: Erle Czar Mantos @ 2002-02-28 02:36 UTC (permalink / raw)
To: Hunter, Ray <rhunter@enterasys.com>; +Cc: pgsql-sql
Hi, Morning All:
Large objects in PostgreSQL are simply large chunks of
information which you may want to store in a database for reasons
I don't know. Example is if you want to store the actual JPEG file
in the database and not just reference it as a string and just put
it as a file in another directory. A possible reason for hiding the
actual JPEG file in a database is to hide it from your Sys Ad if those
JPEG file contains "PORNO" images (Sorry, no pun intended, it just was the
case with a friend of mine, hhehehe). Anyway, an experimental application
I know of stores geographical images as large objects in a postgresql
database. Good luck with large objects, it may sound hard but after you
get to know it, it just becomes a walk in the park.
Erle
On Wed, 27 Feb 2002, Hunter, Ray wrote:
> I have been reading the user manual for largre objects in postgres. What I
> do not understand is when and why you would use them?
>
> Can someone point me to some good documentation on this...
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2002-02-28 22:56 Alexander Steinert <stony8@gmx.de>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 16+ messages in thread
From: Alexander Steinert @ 2002-02-28 22:56 UTC (permalink / raw)
To: pgsql-sql
> Don't use them. They were needed when Postgres only supported 8k
> per row. Now you can just use the 'text' datatype for text data
> and the 'bytea' datatype for binary data. You have a limit of a
> few gigs per row with them.
The problem with the text or bytea type is, that inserting large amounts
of data causes a big performance loss because everything must go through
the SQL-Parser. I would be glad if someone would correct me.
So far I have found no way to grant integrity / use PG's transactions
for large objects with satisfying performance. lo_import/export for
bytea would be a very nice interface to transfer directly between the
database and files readable/writable by the client process.
Suggestions are welcome.
Stony
^ permalink raw reply [nested|flat] 16+ messages in thread
* Large Objects
@ 2016-10-07 07:10 Jürgen Purtz <juergen@purtz.de>
0 siblings, 2 replies; 16+ messages in thread
From: Jürgen Purtz @ 2016-10-07 07:10 UTC (permalink / raw)
To: pgsql-sql
a) What is the distinction between our data type TEXT and the SQL:2011
data type CLOB which we do not support (T041)?
b) Is there a distinction between the two Postgres data types TEXT and
"CHARACTER VARYING without specifying a length" - or are they only
synonym terms without different functionalities and capabilities?
Kind regards, Jürgen
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2016-10-07 07:24 Thomas Kellerer <spam_eater@gmx.net>
parent: Jürgen Purtz <juergen@purtz.de>
1 sibling, 0 replies; 16+ messages in thread
From: Thomas Kellerer @ 2016-10-07 07:24 UTC (permalink / raw)
To: pgsql-sql
> a) What is the distinction between our data type TEXT and the SQL:2011 data type CLOB which we do not support (T041)?
I'd say there is no difference.
> b) Is there a distinction between the two Postgres data types TEXT and "CHARACTER VARYING without specifying a length"
> or are they only synonym terms without different functionalities and capabilities?
No there is no difference.
Quote from the manual [1]
> Tip: There is no performance difference among these three types,
> apart from increased storage space when using the blank-padded type
Thomas
[1] https://www.postgresql.org/docs/current/static/datatype-character.html
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2016-10-07 07:25 Pavel Stehule <pavel.stehule@gmail.com>
parent: Jürgen Purtz <juergen@purtz.de>
1 sibling, 1 reply; 16+ messages in thread
From: Pavel Stehule @ 2016-10-07 07:25 UTC (permalink / raw)
To: Jürgen Purtz <juergen@purtz.de>; +Cc: pgsql-sql
Hi
2016-10-07 9:10 GMT+02:00 Jürgen Purtz <juergen@purtz.de>:
> a) What is the distinction between our data type TEXT and the SQL:2011
> data type CLOB which we do not support (T041)?
>
TEXT type is classic type - you can do any available operation directly
there. You don't need special conversions.
>
> b) Is there a distinction between the two Postgres data types TEXT and
> "CHARACTER VARYING without specifying a length" - or are they only synonym
> terms without different functionalities and capabilities?
>
TEXT and varchar are pretty same types - you cannot to set limit over TEXT
type - only this is visible difference
Regards
Pavel
>
> Kind regards, Jürgen
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2016-10-07 13:19 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Adrian Klaver @ 2016-10-07 13:19 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; Jürgen Purtz <juergen@purtz.de>; +Cc: pgsql-sql
On 10/07/2016 12:25 AM, Pavel Stehule wrote:
> Hi
>
> 2016-10-07 9:10 GMT+02:00 Jürgen Purtz <juergen@purtz.de
> <mailto:juergen@purtz.de>>:
>
> a) What is the distinction between our data type TEXT and the
> SQL:2011 data type CLOB which we do not support (T041)?
>
>
> TEXT type is classic type - you can do any available operation directly
> there. You don't need special conversions.
>
>
>
> b) Is there a distinction between the two Postgres data types TEXT
> and "CHARACTER VARYING without specifying a length" - or are they
> only synonym terms without different functionalities and capabilities?
>
>
> TEXT and varchar are pretty same types - you cannot to set limit over
> TEXT type - only this is visible difference
See also:
https://www.postgresql.org/message-id/998.1474901921%40sss.pgh.pa.us
>
> Regards
>
> Pavel
>
>
>
> Kind regards, Jürgen
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
> <mailto:pgsql-sql@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> <http://www.postgresql.org/mailpref/pgsql-sql;
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2016-10-07 19:31 Jürgen Purtz <juergen@purtz.de>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 16+ messages in thread
From: Jürgen Purtz @ 2016-10-07 19:31 UTC (permalink / raw)
To: pgsql-sql
My first concern is compatibility with the SQL standard - not the
performance issues. Summarising the actual thread and mail
https://www.postgresql.org/message-id/CAJNY3ivGORG1JSjZmop6k_OCJiLop5UCrWkm6NtwEVZhZpFRUw%40mail.gma...
(ff.) I want to state:
- There are 3 (main) character data types: a) character with fixed
length b) character with variable size and a fix upper length limit and
c) character with variable size and undeclared (but implementation
dependent) upper length limit.
- Types a) and b) are named identical in the standard and in Postgres
(CHAR/VARCHAR). Data type c) is named CLOB in the standard and TEXT in
Postgres. Why this different wording, if there is no real difference
between the terms?
- Our documentation says that Postgres does not support feature T041 of
the standard. But functions like LENGTH, LOWER, TRIM, UPPER,
CONCATENATION, ... works well on TEXT.
Do we have some people in the standardisation committees (ANSI, BSI,
DIN, JISC, ...), who represents the Postgres interests at this level?
Kind regards, Jürgen
On 07.10.2016 15:19, Adrian Klaver wrote:
> On 10/07/2016 12:25 AM, Pavel Stehule wrote:
>> Hi
>>
>> 2016-10-07 9:10 GMT+02:00 Jürgen Purtz <juergen@purtz.de
>> <mailto:juergen@purtz.de>>:
>>
>> a) What is the distinction between our data type TEXT and the
>> SQL:2011 data type CLOB which we do not support (T041)?
>>
>>
>> TEXT type is classic type - you can do any available operation directly
>> there. You don't need special conversions.
>>
>>
>>
>> b) Is there a distinction between the two Postgres data types TEXT
>> and "CHARACTER VARYING without specifying a length" - or are they
>> only synonym terms without different functionalities and
>> capabilities?
>>
>>
>> TEXT and varchar are pretty same types - you cannot to set limit over
>> TEXT type - only this is visible difference
>
> See also:
>
> https://www.postgresql.org/message-id/998.1474901921%40sss.pgh.pa.us
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>> Kind regards, Jürgen
>>
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
>> <mailto:pgsql-sql@postgresql.org>)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>> <http://www.postgresql.org/mailpref/pgsql-sql;
>>
>>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Large Objects
@ 2016-10-07 19:41 Thomas Kellerer <spam_eater@gmx.net>
parent: Jürgen Purtz <juergen@purtz.de>
0 siblings, 0 replies; 16+ messages in thread
From: Thomas Kellerer @ 2016-10-07 19:41 UTC (permalink / raw)
To: pgsql-sql
Jürgen Purtz schrieb am 07.10.2016 um 21:31:
> - Types a) and b) are named identical in the standard and in Postgres
> (CHAR/VARCHAR). Data type c) is named CLOB in the standard and TEXT
> in Postgres.
>Why this different wording, if there is no real
> difference between the terms?
I guess because of historical reasons.
But if you need CLOB, then just define it:
create domain clob as text;
create table foo (id integer, c1 clob);
Although I do agree that it would make sense to create CLOB as an alias for text and BLOB an alias for bytea.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2016-10-07 19:41 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-05 23:57 Large Objects Eid, Bilal <Bilal_Eid@NAI.com>
1999-07-06 00:17 ` Chris Bitmead <cbitmead@ozemail.com.au>
1999-07-06 18:42 Large Objects Eid, Bilal <Bilal_Eid@NAI.com>
2000-10-24 03:29 Re: Large Objects pgsql-sql <pgsql-sql@fc.emc.com.ph>
2000-10-24 10:07 Large Objects Craig May <craig.may@s2.enthdimension.com.au>
2002-02-27 13:45 Large Objects Hunter, Ray <rhunter@enterasys.com>
2002-02-27 21:07 ` postgresql <pgsql@symcom.com>
2002-02-28 01:05 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-28 22:56 ` Alexander Steinert <stony8@gmx.de>
2002-02-28 02:36 ` Erle Czar Mantos <erle@hindang.msuiit.edu.ph>
2016-10-07 07:10 Large Objects Jürgen Purtz <juergen@purtz.de>
2016-10-07 07:24 ` Thomas Kellerer <spam_eater@gmx.net>
2016-10-07 07:25 ` Pavel Stehule <pavel.stehule@gmail.com>
2016-10-07 13:19 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-10-07 19:31 ` Jürgen Purtz <juergen@purtz.de>
2016-10-07 19:41 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox