[GENERAL] Large Objects

[haukinger]

Hi all !

I'm working on a database that needs to handle insertion of about 10 large 
objects (50..60GB) a day. It should be able to run 200 days, so it will become 
about 10TB eventually, mostly of 200..500KB large objects.
How does access to large objects work ? I give the oid and get the large 
object... what is done internally ? How (if at all) are the oid's indexed ?

Thanks a lot in advance !

Haukinger
-- 
"Feel free" - 5 GB Mailbox, 50 FreeSMS/Monat ...
Jetzt GMX ProMail testen: www.gmx.net/de/go/mailfooter/promail-out

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Large Objects

[Dan Boitnott]

I need to do some investigation into the way Postgres handles large 
objects for a major project involving large objects.  My questions are:
   * Can large objects be stored within a field value or must they be 
referenced by OID?
   * Are large objects backed up in the normal way or does special 
action have to be taken?
   * If the objects are restored or migrated will they retain their OID?
   * If not, is there another means of referencing them that would be 
persistent through migrations?
   * Is it practical/desirable to store files MIME-Encoded inside a 
text field?
  * The obvious advantages:
 * definitely portable across migrations and backups
 * based on universal technology
 * easy to code
 * easy to reference and version-control
  * The obvious disadvantages:
 * slow, Slow, SLOW
 * significant increase in per-file storage requirements

Any help would be greatly appreciated.  Hope I'm posting to the right 
list.

Dan
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] Large Objects

[David Giffin]

I'm working on integrating Large Objects into a database. I'm courious
about a couple of different things. 1) Can you view large objects from the
psql interface? 2) Is there a way to reference the large object from a
standard query or do I have create a method of running a query then
opening the data from the large object? 3) Is there anyone the has already
has a working model of this in Perl?

Thanks,

David

[GENERAL] large objects

[Lauri Posti]

Hi!

I've been trying to get postgres LO interface to work with python.

I have been successful with  three configurations:
1) pgsql 6.4.2 & PyGreSQL 2.2 on Linux/x86
2) pgsql 6.5beta1 & PyGgeSQL 2.3 on Linux/x86
3) pgsql 6.5beta1 & PyGreSQL 2.3 on SPARC/Solaris 2.6

And failed with all other:
* 6.5beta2 ... 6.5 final on SPARC/Solaris 2.6 and Linux/x86 with PyGreSQL
2.3 & 2.4 in any combination.

* 6.4.2 on SPARC/Solaris 2.6 with PyGreSQL 2.2 .. 2.4 


What i rally need is to get something to work on SPARC/Solaris. The only
vesrion taht has worked is 6.5beta1 but I can't find it anywhere.

with 6.4.2 & PyGreSQL on Solaris (on x86 linus it is ok) the problem is
following:
$python
Python 1.5.2 (#5, Jun 30 1999, 20:15:59)  [GCC egcs-2.91.66 19990314
(egcs-1.1.2  on sunos5
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> from pg import *
>>> c=connect()
>>> o=c.locreate(INV_WRITE)
>>> o.open(INV_WRITE)
>>> o.oid
21089
>>> o.close()
>>> o2=c.locreate(INV_WRITE)
>>> o2.open(INV_WRITE)
>>> 
>>> o2.write("hello")
>>> o2.close()
Traceback (innermost last):
  File "", line 1, in ?
IOError: error while closing large object fd.
>>> 
 

and 6.5 on both Solaris $ Linux the problem is folllowing:
[postgres@liha pgsql]$ python
Python 1.5.2 (#3, Jun 13 1999, 23:57:46)  [GCC egcs-2.91.66 19990314/Linux
(egcs- on linux2
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> from pg import *
>>> con=connect("testdb","",5432,"","","koll","koll") 
>>> lo=con.locreate(INV_WRITE)
>>> lo.oid
18593
>>> lo.open(INV_WRITE)
Traceback (innermost last):
  File "", line 1, in ?
IOError: can't open large object.
>>> 
and backend says:
ERROR:  lo_lseek: invalid large obj descriptor (0)


Any ideas?

Thanks in advance,
Lauri

[GENERAL] large objects

[Tim Joyce]

Hi,

I have an existing table and want to change the type of a column from text to oid.

I have tried pg_dumping and psql -e, but get "broken pipe" when inserting the date 
into the new table.

pastgresql 6.4 on linux

thanks for your help

timj
[EMAIL PROTECTED]

[GENERAL] Large objects...

[Andrew Schmeder]

Hello all, 

I am attempting to use large objects to store chunks of text and binary data.
I am using PHP and sometimes need to do things through psql also.
PHP has a function to "unlink" a large object, i.e. delete it.
Is there an explict way to delete a large object via psql?  I have been using
lo_import to place data, and I want to make sure there are no large objects
floating around that I am not aware of.

Thanks,
Andy

[GENERAL] Large objects

[Luis Martins]

 Hi everyone, I have to insert a few PDF files into my database, and
I am not able to do it ...
  I have to use PHP3, and all I could do was use the lo_import,
but in this case, I am not able to insert any data because I am not an
administrator. Is there any other way of doing it, using, let's say, pg_locreate,
pg_loopen, or something like that ?
  Thanx in advance.
  Luis
-- 
__
    Luis Martins
Eng. Sistemas e Computacao
 UCEH - Univ. do Algarve
__
  email : [EMAIL PROTECTED]
__
 

[GENERAL] Large Objects

[Steven Lacroix]

A couple of questions and concerns about Blobs.

I'm wondering what kind of performance hits do BLOBS have on a database
large database.

Currently working on implementing a database with images.  I guess i'm
looking for some numbers showing the performence.   Note that it would be
for web database project.

Re: [GENERAL] Large Objects

[Albe Laurenz]

[EMAIL PROTECTED] wrote:
> I'm working on a database that needs to handle insertion of 
> about 10 large objects (50..60GB) a day. It should be 
> able to run 200 days, so it will become about 10TB 
> eventually, mostly of 200..500KB large objects.
> How does access to large objects work ? I give the oid and 
> get the large object... what is done internally ? How (if at 
> all) are the oid's indexed ?

I cannot tell you if PostgreSQL will work well with that amount of data;
I leave this to people more experienced with large databases.
Do you have a backup strategy?

Large objects are stored in a system table pg_largeobject in chunks of
2KB.

The oid is a handle for the large object; when you store the oid in
a user table, you basically store a reference or a pointer to that
large object. If you delete the row in that user table, the large object
will still be there, and if you delete the large object, the oid in the
user table will be 'orphaned'. So you need to make sure that you keep
large objects and references to them in sync.

For your quesion - how are large objects accessed - see the
documentation
of the C functions in
http://www.postgresql.org/docs/current/static/lo-interfaces.html

You can create a new large object, which will return the oid of the new
large object. You have to open a large object by oid before you can read
or modify it - this will give you a 'handle' with which you can access
the contents of the large object much like a file in C: you can position
a 'location pointer' and read or write a number of bytes.
There are functions to import and export a large object from and to the
file system.

There are similar functions for most client interfaces.

Yours,
Laurenz Albe

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Large Objects

[Richard Huxton]

[EMAIL PROTECTED] wrote:

Hi all !

I'm working on a database that needs to handle insertion of about
10 large objects (50..60GB) a day. It should be able to run 200
days, so it will become about 10TB eventually, mostly of 200..500KB
large objects. How does access to large objects work ? I give the oid
and get the large object... what is done internally ? How (if at all)
are the oid's indexed ?


Albe's answered your actual question, but I'd wonder if you really want 
to do this?


The key question is whether you need to have the actual objects stored 
under transactional control. If not, just saving them as files will 
prove much more efficient.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Large Objects

[Bruno Wolff III]

On Mon, Dec 27, 2004 at 10:39:48 -0600,
  Dan Boitnott <[EMAIL PROTECTED]> wrote:
> I need to do some investigation into the way Postgres handles large 
> objects for a major project involving large objects.  My questions are:

I don't know the answer to all of your questions.

>* Is it practical/desirable to store files MIME-Encoded inside a 
> text field?

This should be possible if the files aren't too large. bytea is another type
that might be better to use.

>   * The obvious disadvantages:
>  * slow, Slow, SLOW

If you always need to access the whole file this might not be too bad.
But if you only need to access a small part, you are going to pay a big
cost as the whole record will need to be retrieved before you can pick
out the part you want.

>  * significant increase in per-file storage requirements

It might not be too bad as large records can be compressed. That should get
back some of the bloat from uuencoding.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Large Objects

[Frank D. Engel, Jr.]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I'd advise use of BYTEA as well.  It's much simpler to work with than 
the OIDs, and has simpler semantics.  You do need to escape data before 
handing it to the query string, and handle escaped results (see the 
docs), but overall much nicer than working with OIDs.

On Dec 31, 2004, at 1:21 AM, Bruno Wolff III wrote:
On Mon, Dec 27, 2004 at 10:39:48 -0600,
  Dan Boitnott <[EMAIL PROTECTED]> wrote:
I need to do some investigation into the way Postgres handles large
objects for a major project involving large objects.  My questions 
are:
I don't know the answer to all of your questions.
   * Is it practical/desirable to store files MIME-Encoded inside a
text field?
This should be possible if the files aren't too large. bytea is 
another type
that might be better to use.

  * The obvious disadvantages:
 * slow, Slow, SLOW
If you always need to access the whole file this might not be too bad.
But if you only need to access a small part, you are going to pay a big
cost as the whole record will need to be retrieved before you can pick
out the part you want.
 * significant increase in per-file storage requirements
It might not be too bad as large records can be compressed. That 
should get
back some of the bloat from uuencoding.

---(end of 
broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if 
your
  joining column's datatypes do not match


- ---
Frank D. Engel, Jr.  <[EMAIL PROTECTED]>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB1XbY7aqtWrR9cZoRAp6PAJ0UMNDpfeiI2iUaAp3CMIyaxuJNgQCffoqJ
mn4M418e7V9YZX5fwte9Ra0=
=iXtd
-END PGP SIGNATURE-

___
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Large Objects

[Joshua D. Drake]

Frank D. Engel, Jr. wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I'd advise use of BYTEA as well.  It's much simpler to work with than 
the OIDs, and has simpler semantics.  You do need to escape data 
before handing it to the query string, and handle escaped results (see 
the docs), but overall much nicer than working with OIDs.
BYTEA is not always pragmatic. What is the file is 100 megs? 256 megs?
pg_largeobject is more efficient than BYTEA for larger binaries.
Sincerely,
Joshua D. Drake


On Dec 31, 2004, at 1:21 AM, Bruno Wolff III wrote:
On Mon, Dec 27, 2004 at 10:39:48 -0600,
  Dan Boitnott <[EMAIL PROTECTED]> wrote:
I need to do some investigation into the way Postgres handles large
objects for a major project involving large objects.  My questions are:

I don't know the answer to all of your questions.
   * Is it practical/desirable to store files MIME-Encoded inside a
text field?

This should be possible if the files aren't too large. bytea is 
another type
that might be better to use.

  * The obvious disadvantages:
 * slow, Slow, SLOW

If you always need to access the whole file this might not be too bad.
But if you only need to access a small part, you are going to pay a big
cost as the whole record will need to be retrieved before you can pick
out the part you want.
 * significant increase in per-file storage requirements

It might not be too bad as large records can be compressed. That 
should get
back some of the bloat from uuencoding.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if 
your
  joining column's datatypes do not match


- ---
Frank D. Engel, Jr.  <[EMAIL PROTECTED]>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB1XbY7aqtWrR9cZoRAp6PAJ0UMNDpfeiI2iUaAp3CMIyaxuJNgQCffoqJ
mn4M418e7V9YZX5fwte9Ra0=
=iXtd
-END PGP SIGNATURE-

___
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Large Objects

[Michael Ben-Nes]

Joshua D. Drake wrote:
Frank D. Engel, Jr. wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I'd advise use of BYTEA as well.  It's much simpler to work with than 
the OIDs, and has simpler semantics.  You do need to escape data 
before handing it to the query string, and handle escaped results 
(see the docs), but overall much nicer than working with OIDs.

BYTEA is not always pragmatic. What is the file is 100 megs? 256 megs?
Intresting.
What is the size when bytea become inafective ?
Currently i keep all my products images in bytea record. is it practical ?
how slower is it then accessing an image on a file system ( like ext3 ) ?
Cheers
pg_largeobject is more efficient than BYTEA for larger binaries.
Sincerely,
Joshua D. Drake

--
--
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
Cel: 972-52-8555757
Fax: 972-4-6990098
http://www.canaan.net.il
--
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Large Objects

[Martijn van Oosterhout]

On Sat, Jan 01, 2005 at 01:28:04PM +0300, Michael Ben-Nes wrote:
> Joshua D. Drake wrote:
> >Frank D. Engel, Jr. wrote:
> >>I'd advise use of BYTEA as well.  It's much simpler to work with than 
> >>the OIDs, and has simpler semantics.  You do need to escape data 
> >>before handing it to the query string, and handle escaped results 
> >>(see the docs), but overall much nicer than working with OIDs.
> >
> >
> >BYTEA is not always pragmatic. What is the file is 100 megs? 256 megs?
> >
> Intresting.
> What is the size when bytea become inafective ?

I don't think it's so much a matter of effectiveness, it makes no
difference at all in storage space. The issue is that if you store it
in a field, accessing it becomes an all or nothing affair, which means
if it's a 100Mb object, it's all going to be accessed whenever you ask
for it. OTOH, large objects have lo_read/write/seek meaning you can
access small parts at a time.

So I imagine if you're storing large PDF files and all you're doing is
dumping them to a client when they ask, it doesn't matter. But if the
objects have structure and you might be interested in looking inside
them without pulling the whole object down, the LO interface is better
suited.

When you delete a row, the object contained in it goes away too. Large
Objects have a lifecycle outside of normal table values, and so may
need separate managing...

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpGn1cJHmA6x.pgp
Description: PGP signature

Re: [GENERAL] Large Objects

[Joshua D. Drake]

Intresting.
What is the size when bytea become inafective ?
Currently i keep all my products images in bytea record. is it 
practical ?
Well I am going to make the assumption that you product images are small...
sub 100k or something. Bytea is just fine for that. The problem is when
the binary you want to store is 50 megs. When you access that file you
will be using 50 megs of ram to do so.
Large Objects don't work that way, you don't have the memory overhead. So
it really depends on what you want to store.

how slower is it then accessing an image on a file system ( like ext3 ) ?
Well that would be an interesting test. Ext3 is very slow. I would assume
that Ext3 would be faster just because of the database overhead. However 
you gain from having the images in the database for flexibility and 
manageability.

Sincerely,
Joshua D. Drake


Cheers
pg_largeobject is more efficient than BYTEA for larger binaries.
Sincerely,
Joshua D. Drake



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Large Objects

[Karsten Hilbert]

> > >BYTEA is not always pragmatic. What is the file is 100 megs? 256 megs?

> > What is the size when bytea become inafective ?

> I don't think it's so much a matter of effectiveness, it makes no
> difference at all in storage space.
Ah, thanks, good to know. Something new to learn every day...

> The issue is that if you store it
> in a field, accessing it becomes an all or nothing affair, which means
> if it's a 100Mb object, it's all going to be accessed whenever you ask
> for it.
At least for reads you are wrong. You can use substring() on
bytea quite nicely. Remember, however, that that operates on
*bytes*, not characters. Also be careful about encodings being
set for the connection. At least with PostgreSQL < 7.4 we found
we had to "reset client_encoding" despite the docs saying
encodings won't affect bytea field reads.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Large Objects

[Dan Boitnott]

On Jan 1, 2005, at 11:40 AM, Joshua D. Drake wrote:


Intresting.
What is the size when bytea become inafective ?
Currently i keep all my products images in bytea record. is it 
practical ?
Well I am going to make the assumption that you product images are 
small...
sub 100k or something. Bytea is just fine for that. The problem is when
the binary you want to store is 50 megs. When you access that file you
will be using 50 megs of ram to do so.

Large Objects don't work that way, you don't have the memory overhead. 
So
it really depends on what you want to store.

I prefer the _idea_ of using large objects but am worried about the 
implications.  Without them I can back up the database using pg_dump 
and get a single tar file which can perfectly represent the database.  
This gives me (and those on high) the warm-fuzzies.  If I store files 
(PDFs of varying sizes by the way, say from 500k to 50M) as large 
objects, will I still be able to restore the _whole_ database from a 
single pg_dump tar file?


how slower is it then accessing an image on a file system ( like ext3 
) ?
Well that would be an interesting test. Ext3 is very slow. I would 
assume
that Ext3 would be faster just because of the database overhead. 
However you gain from having the images in the database for 
flexibility and manageability.

Sincerely,
Joshua D. Drake


Cheers
pg_largeobject is more efficient than BYTEA for larger binaries.
Sincerely,
Joshua D. Drake



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

---(end of 
broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Large Objects

[Robby Russell]

On Sat, 2005-01-01 at 19:50 -0600, Dan Boitnott wrote:
> On Jan 1, 2005, at 11:40 AM, Joshua D. Drake wrote:
> 
> >
> >>>
> >> Intresting.
> >> What is the size when bytea become inafective ?
> >>
> >> Currently i keep all my products images in bytea record. is it 
> >> practical ?
> >
> > Well I am going to make the assumption that you product images are 
> > small...
> > sub 100k or something. Bytea is just fine for that. The problem is when
> > the binary you want to store is 50 megs. When you access that file you
> > will be using 50 megs of ram to do so.
> >
> > Large Objects don't work that way, you don't have the memory overhead. 
> > So
> > it really depends on what you want to store.
> >
> 
> I prefer the _idea_ of using large objects but am worried about the 
> implications.  Without them I can back up the database using pg_dump 
> and get a single tar file which can perfectly represent the database.  
> This gives me (and those on high) the warm-fuzzies.  If I store files 
> (PDFs of varying sizes by the way, say from 500k to 50M) as large 
> objects, will I still be able to restore the _whole_ database from a 
> single pg_dump tar file?

Yes, you will be able to do this. Your pg_dump


http://www.postgresql.org/docs/current/static/app-pgdump.html
> -b
> --blobs
> 
> Include large objects in dump. 
> 
> 

-Robby


-- 
/***
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON  | www.planetargon.com
* Portland, OR  | [EMAIL PROTECTED]
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
*--- Now supporting PHP5 ---
/


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Large Objects

[Pierre-Frédéric Caillaud]

gives me (and those on high) the warm-fuzzies.  If I store files (PDFs  
of varying sizes by the way, say from 500k to 50M) as large objects,  
will I still be able to restore the _whole_ database from a single  
pg_dump tar file?
Don't forget a thing :
	If you put a webserver in front of this, you will have to load the entire  
object in RAM before you can serve it to the client, which means a little  
number of requests on large files will kick the server into swapalot mode.  
Using filesystem files solves this in the most easy of ways, but creates  
backup problems, but it's not that complex.

---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] Large objects oids

[David Wall]

Since large objects use OIDs, does PG 8.3 have a limit of 4 billion 
large objects across all of my various tables (actually, I presume OIDs 
are used elsewhere besides just large objects)? 

Is there any plan on allowing large objects to support more than 2GB?  
As data gets larger and larger, I can see this being a problem if you'd 
like the ACID properties of a DB to work with video and other large data 
sets.


Since all large objects are stored in the pg_catalog.pg_largeobject 
table, will running 'vacuum full' on it reduce the FSM issues I often 
see with errors like:


WARNING:  relation "pg_catalog.pg_largeobject" contains more than 
"max_fsm_pages" pages with useful free space
HINT:  Consider using VACUUM FULL on this relation or increasing the 
configuration parameter "max_fsm_pages".
NOTICE:  number of page slots needed (1045968) exceeds max_fsm_pages 
(30)


Thanks,
David


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

[GENERAL] Large objects + JDBC

[Marcin Mazurek - Multinet SA - Poznan]

Hi,
I'm put several gifa into a table. I did as a exercise:) form psql using:
INSERT INTO images (id, data)
VALUES (3, lo_import('/usr/local/apache/servlets/images/a.gif'));

but I have a problem with creating Java stream to read these data. Here
serveral lines of code I was using:
PreparedStatement ps=db.prepareStatement("select oid from imag where  id=?");
   ps.setInt(1,1);
   ResultSet rs = ps.executeQuery();
   rs.next(); 
   InputStream is = rs.getBinaryStream(0);
and nothing happens:( Several messages from exceptions:
[13/12/1999 18:11:04:815 CET] ShowImageServlet1: 0
null
Fastpath: ERROR:  lo_tell: large object descriptor (-1) out of range

Anybody know how to read LargeObjects with java?
btw I read about postgres JDBC extensions LargeObject and
LargeObjectManager and everthing would be great except nobody mentioned
that they are not completely implemented:)
Anybody can send me a small exapmle how to read these data?
mazek

Re: [GENERAL] Large objects

[Tatsuo Ishii]

>  Hi everyone, I have to insert a few PDF files into my database, and I
> am not able to do it ...
>   I have to use PHP3, and all I could do was use the lo_import, but in
> this case, I am not able to insert any data because I am not an
> administrator. Is there any other way of doing it, using, let's say,
> pg_locreate, pg_loopen, or something like that ?
>   Thanx in advance.

Of course you can. The essential part of the program should look like:

  pg_exec($con,"begin");
  $oid = pg_locreate($con);
  $lobj = pg_loopen($con,$oid,"w");
  /* read your PDF file contents into $img variable */
  pg_lowrite($lobj,$img);
  pg_loclose($lobj);
  pg_exec($con,"end");
--
Tatsuo Ishii

Re: [GENERAL] Large Objects

[dyp]

Hello Steven,

Tuesday, September 19, 2000, 11:00:02 PM, you wrote:

SL> A couple of questions and concerns about Blobs.

SL> I'm wondering what kind of performance hits do BLOBS have on a database
SL> large database.

SL> Currently working on implementing a database with images.  I guess i'm
SL> looking for some numbers showing the performence.   Note that it would be
SL> for web database project.

It hardly depends on the amount of objects you have.
In current LO implementation there is really huge drawback.
It creates 2 files for each LO. When you have > 1 LOs you are
lost.

There was a patch made by me to provide LO support inside one table.

Without this patch I had approximatly 30 minutes to insert 1 LOs.
With it it was only 5 min neccessary.

Also you should understand that all measures hardly depends on the
size of the LOs you will have. It would be easier for you just write
some small test cases on the random data and measure them.

-- 
Best regards,
 dypmailto:[EMAIL PROTECTED]

Re: [GENERAL] Large objects

[Denis Perchine]

> > I want to make a image catalogue. I will use postgresql, perl and php.
> >
> > What are the advantages of having the images in the database instead of
> > having them out in a directory?
> >
> > After all, to show the images I need them on a directory?
>
> Really, you can show images from database, BUT
> there are no much reasons put in database something, for which you can't
> say WHERE field=some_thing
> Better place images into filesystem.

Except on case... When you would like to be sure in transaction safety...

-- 
Sincerely Yours,
Denis Perchine

--
E-Mail: [EMAIL PROTECTED]
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
--

Re: [GENERAL] Large objects

[Igor Roboul]

On Thu, Nov 02, 2000 at 05:35:04PM +0600, Denis Perchine wrote:
> Except on case... When you would like to be sure in transaction safety...
Ok, but not for image galery.

-- 
Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga", 
Sochi, Russia
http://www.brainbench.com/transcript.jsp?pid=304744

Re: [GENERAL] Large objects

[Igor Roboul]

On Thu, Nov 02, 2000 at 02:39:54PM +0300, Igor Roboul wrote:
> On Thu, Nov 02, 2000 at 05:35:04PM +0600, Denis Perchine wrote:
> > Except on case... When you would like to be sure in transaction safety...
> Ok, but not for image galery.
I have answered on argument about transactions 

-- 
Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga", 
Sochi, Russia
http://www.brainbench.com/transcript.jsp?pid=304744

Re: [GENERAL] Large objects

[Denis Perchine]

> On Thu, Nov 02, 2000 at 05:35:04PM +0600, Denis Perchine wrote:
> > Except on case... When you would like to be sure in transaction safety...
>
> Ok, but not for image galery.

Again... If you can accept that you will have half of image, it's OK.
If not...

-- 
Sincerely Yours,
Denis Perchine

--
E-Mail: [EMAIL PROTECTED]
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
--

Re: [GENERAL] Large objects oids

[Tom Lane]

David Wall <[EMAIL PROTECTED]> writes:
> Since large objects use OIDs, does PG 8.3 have a limit of 4 billion 
> large objects across all of my various tables

Yup, and in practice you'd better have a lot less than that or assigning
a new OID might take a long time.

> (actually, I presume OIDs 
> are used elsewhere besides just large objects)? 

They are, but this isn't relevant to large objects.  The uniqueness
requirement is only per-catalog.

> Is there any plan on allowing large objects to support more than 2GB?  

No, it's not on the radar screen really.

regards, tom lane

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

Re: [GENERAL] Large objects oids

[David Wall]

Tom Lane wrote:

David Wall <[EMAIL PROTECTED]> writes:
  
Since large objects use OIDs, does PG 8.3 have a limit of 4 billion 
large objects across all of my various tables



Yup, and in practice you'd better have a lot less than that or assigning
a new OID might take a long time.
  


What's a rough estimate of "a lot less"?  Are we talking 2 billion, 3 
billion, 1 billion?



(actually, I presume OIDs 
are used elsewhere besides just large objects)? 



They are, but this isn't relevant to large objects.  The uniqueness
requirement is only per-catalog.
  
Isn't there just one catalog per postmaster instance (pg_catalog)?  The 
issue we have is that one postmaster runs a large number of databases 
(let's say 100 for easy calculations), so even with the max 4 billion 
potential OIDs, that would drop each DB to 40 million each.


Part of this is just architectural to us.  We do heavy 
encryption/compression of data (in particular digitally signed XML text) 
and use large objects to store these, but we may need to change these to 
use bytea since they wouldn't use up OIDs and the actual data size tends 
not to be too large (perhaps 10KB compressed and encrypted binary data) 
and can be done in a block.  All that character escaping of binary data, 
though, makes the JDBC-to-Postmaster interface a tad bit ugly, though.



  
Is there any plan on allowing large objects to support more than 2GB?  



No, it's not on the radar screen really.
  


Too bad, but again, we can always work around it, even if means a layer 
that bundles large objects sort of like large objects bundle bytea.  We 
prefer not to store it outside the database as the large files can get 
out of sync from the database (ACID properties) and of course need to be 
backed up separately from the database backups and WAL copying for 
replication.


David

Re: [GENERAL] Large objects oids

[Tom Lane]

David Wall <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Yup, and in practice you'd better have a lot less than that or assigning
>> a new OID might take a long time.

> What's a rough estimate of "a lot less"?  Are we talking 2 billion, 3 
> billion, 1 billion?

It's difficult to say --- the assignment problem occurs if there's a
very long run of consecutive OIDs in use in the table beginning right
at the spot where the OID counter currently is (having wrapped around
one or more times since those objects were created).  So the question is
what is the probability of that.  The fact that the same OID counter
feeds everything (system objects, TOAST objects, and LOs) means that any
given catalog probably hasn't got tremendously long runs, but it could
happen.

Personally I'd feel uncomfortable designing a system that expected to
have more than a few million LOs per database, but that's just a guess.

>> They are, but this isn't relevant to large objects.  The uniqueness
>> requirement is only per-catalog.
>> 
> Isn't there just one catalog per postmaster instance (pg_catalog)?

Sorry, by "catalog" I meant "any table with a unique OID column".
For purposes of this discussion you could consider each database's
pg_largeobject table to be one such catalog.

> The 
> issue we have is that one postmaster runs a large number of databases 
> (let's say 100 for easy calculations), so even with the max 4 billion 
> potential OIDs, that would drop each DB to 40 million each.

No, because the OIDs needn't be unique across databases.  The situation
you describe would actually be best-case as long as the LO creation
activity is well-intermixed across the databases, because that would
minimize the probability of a long run of consecutive OIDs being
assigned to LOs within any one database.  If it was really well-mixed
you could probably approach a billion LOs per DB without noticing any
problems.

regards, tom lane

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

Re: [GENERAL] Large objects + JDBC

[Gunther Schadow]

Marcin Mazurek - Multinet SA - Poznan wrote:
> Hi,
> I'm put several gifa into a table. I did as a exercise:) form psql using:
> INSERT INTO images (id, data)
> VALUES (3, lo_import('/usr/local/apache/servlets/images/a.gif'));

are you sure this lo_import(...) thing in the SQL will work? I have no
idea ...
 
> but I have a problem with creating Java stream to read these data. Here
> serveral lines of code I was using:
> PreparedStatement ps=db.prepareStatement("select oid from imag where  id=?");
>ps.setInt(1,1);
>ResultSet rs = ps.executeQuery();
>rs.next();
>InputStream is = rs.getBinaryStream(0);
> and nothing happens:( Several messages from exceptions:
> [13/12/1999 18:11:04:815 CET] ShowImageServlet1: 0
> null
> Fastpath: ERROR:  lo_tell: large object descriptor (-1) out of range

I see two problems with your above Java code snippet:

#1: you SELECT oid FROM imag ... only the OID? Would you not have to select
the data?  A SELECT that would match your insert above would be:

SELECT data FROM images WHERE id=?;


#2: your index in rs.getBinaryStream is zero, but if I recall correctly
the JDBC ResultSet.getXXX(int) functions start counting at 1, not at
0.  So, you should have called:
 
   PreparedStatement ps=db.prepareStatement(
"SELECT data FROM image WHERE id=?"); // select data instead of oid
   ps.setInt(1,1);
   ResultSet rs = ps.executeQuery();
   if(rs.next()) {
 InputStream is = rs.getBinaryStream(1);  // start with one (1)

   }

I have never done this BLOB stuff, but you might try whether this
fixes the problem. Also, with the JDBC driver comes a test that
does exactly image storing and retrieval. So you can look there
for a working example.

regards
-Gunther
 

-- 
Gunther_Schadow---http://aurora.rg.iupui.edu
Regenstrief Institute for Health Care
1050 Wishard Blvd., Indianapolis IN 46202, Phone: (317) 630 7960
[EMAIL PROTECTED]#include 

begin:vcard 
n:Schadow;Gunther
tel;fax:+1 317 630 6962
tel;home:+1 317 816 0516
tel;work:+1 317 630 7960
x-mozilla-html:FALSE
url:http://aurora.rg.iupui.edu
org:Regenstrief Institute
adr:;;1050 Wishard Blvd;Indianapolis;Indiana;46202;USA
version:2.1
email;internet:[EMAIL PROTECTED]
title:M.D.
fn:Gunther Schadow
end:vcard

[GENERAL] Large Objects and Replication question

[Howard Cole]

Does anyone know of a replication solution that can handle large 
objects? Preferrably on a per database rather than per cluster basis.


Incidentally - out of interest - why doesn't Slony handle large objects?

Thanks.

Howard
www.selestial.com

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

[GENERAL] Large Objects and and Vacuum

[Simon Windsor]

Hi

 

I am struggling with the volume and number of XML files a new application is
storing. The table pg_largeobjects is growing fast, and despite the efforts
of vacuumlo, vacuum and auto-vacuum it keeps on growing in size.

 

The main tables that hold large objects are partitioned and every few days I
drop partition tables older than seven days, but despite all this, the
system is growing in size and not releasing space back to the OS.

 

Using either vacuum full or cluster to fix pg_largeobjects will require a
large amount of work space which I do not have on this server.

 

Is there another method of scanning postgres tables, moving active blocks
and releasing store back to the OS?

 

Failing this, I can see an NFS mount being required.

 

Simon

 

 

Simon Windsor

Eml:   simon.wind...@cornfield.org.uk

Tel: 01454 617689

Mob: 07590 324560

 

"There is nothing in the world that some man cannot make a little worse and
sell a little cheaper, and he who considers price only is that man's lawful
prey."

 

[GENERAL] Large objects in web applications

[wsheldah]

Hi,

Has there been any substantial change in the way large objects are handled with
the coming of 7.1 and the expanded row size limit?  Some old online articles
suggested that would change things, but the current docs seem say I still need
to use functions like lo_import.

Assuming things haven't changed for large objects, it appears that to display an
image, I'll need to:
1. select the image from postgresql, using lo_export() to save it to a uniquely
named temp file (to avoid conflicts with other users).
2. Read/load the temp file and send it out to the browser.
3. Delete the temp file

Is this conceptually correct?  Any pointers to good docs or articles on doing
this with perl?  I know it's straightforward, but I'd just as soon not reinvent
the same mistakes if I can help it.  Thanks,

Wes



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Large Objects and Replication question

[Alexey Klyukin]

On Dec 2, 2009, at 4:23 PM, Howard Cole wrote:

> Does anyone know of a replication solution that can handle large objects? 
> Preferrably on a per database rather than per cluster basis.

Take a look at Mammoth Replicator: 
https://projects.commandprompt.com/public/replicator.

Additionally there is a list of available open-source replication solutions 
here:
http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling

Personally I'm not aware of other solutions supporting large objects, but I'm 
sure there should be more of them :).

> 
> Incidentally - out of interest - why doesn't Slony handle large objects?
> 

Here is an excerpt from the Slony documentation:

Slony-I does not automatically propagate schema changes, nor does it have any 
ability to replicate large objects. There is a single common reason for these 
limitations, namely that Slony-I collects updates using triggers, and neither 
schema changes, large object operations, nor TRUNCATE requests are able to have 
triggers suitable to inform Slony-I when those sorts of changes take place. As 
a result, the only database objects where Slony-I can replicate updates are 
tables and sequences.
http://www.slony.info/documentation/slonyintro.html#INTRODUCTION

I'm not sure that it's up-to-date, since 8.4 has at least on truncate triggers, 
but still there are no triggers on large objects yet.

--
Alexey Klyukin  http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc


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

Re: [GENERAL] Large Objects and Replication question

[Tatsuo Ishii]

> Does anyone know of a replication solution that can handle large 
> objects? Preferrably on a per database rather than per cluster basis.

pgpool-II can handle large objects. However you need to use newer API
of libpq to create large objects:

Oid lo_create(PGconn *conn, Oid lobjId);

I'm not sure Java or some other languages support the equivalent API
though.

You cannot use old API lo_creat() since it relies on OID, which
pgpool-II does not guarantee OIDs can be replicated.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

Re: [GENERAL] Large Objects and Replication question

[Tatsuo Ishii]

BTW

> Additionally there is a list of available open-source replication solutions 
> here:
> http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling

The link http://www.slony2.org/ mentioned in the wiki page above
apparently does nothing to do with Slony-II. Can someone please fix it.
--
Tatsuo Ishii
SRA OSS, Inc. Japan


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

Re: [GENERAL] Large Objects and Replication question

[Alexey Klyukin]

On Dec 2, 2009, at 5:48 PM, Tatsuo Ishii wrote:

> BTW
> 
>> Additionally there is a list of available open-source replication solutions 
>> here:
>> http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling
> 
> The link http://www.slony2.org/ mentioned in the wiki page above
> apparently does nothing to do with Slony-II. Can someone please fix it.

Removed the link.

--
Alexey Klyukin  http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc


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

Re: [GENERAL] Large Objects and Replication question

[Daniel Verite]

Tatsuo Ishii wrote:

>  However you need to use newer API
> of libpq to create large objects:
> 
> Oid lo_create(PGconn *conn, Oid lobjId);
[...]
> You cannot use old API lo_creat() since it relies on OID, which
> pgpool-II does not guarantee OIDs can be replicated.

Does it mean that lo_create(conn, 0) is supported while
lo_creat(conn,INV_READ|INV_WRITE) is not ?
It sounds strange from the user's point of view, because they do the same
thing, don't they?

Best regards,
-- 
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

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

Re: [GENERAL] Large Objects and Replication question

[Tatsuo Ishii]

> >  However you need to use newer API
> > of libpq to create large objects:
> > 
> > Oid lo_create(PGconn *conn, Oid lobjId);
> [...]
> > You cannot use old API lo_creat() since it relies on OID, which
> > pgpool-II does not guarantee OIDs can be replicated.
> 
> Does it mean that lo_create(conn, 0) is supported while
> lo_creat(conn,INV_READ|INV_WRITE) is not ?
> It sounds strange from the user's point of view, because they do the same
> thing, don't they?

Well, I do not recommend to use lo_create(conn, 0) either.

Actually users can use lo_create(conn, 0) or lo_create without any
problem until you try to extract existing large objects by oid.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

Re: [GENERAL] Large Objects and Replication question

[Linos]

Symmetric-ds it is a replication solution that handles large objects, it is 
asynchronous and multi-master, i have been using between 30 separate postgresql 
connected by a slow link and until now i have been working without problems, i 
think this project should be in the wiki.


http://symmetricds.codehaus.org/

Regards,
Miguel Angel.

El 02/12/09 15:23, Howard Cole escribió:

Does anyone know of a replication solution that can handle large
objects? Preferrably on a per database rather than per cluster basis.

Incidentally - out of interest - why doesn't Slony handle large objects?

Thanks.

Howard
www.selestial.com




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

Re: [GENERAL] Large Objects and and Vacuum

[Albe Laurenz]

Please don't send HTML mail to this list.

Simon Windsor wrote:
> I am struggling with the volume and number of XML files a new
application is storing. The table
> pg_largeobjects is growing fast, and despite the efforts of vacuumlo,
vacuum and auto-vacuum it keeps
> on growing in size.

Have you checked if the number of large objects in the database
is growing as well?

Check the result of
SELECT count(DISTINCT loid) FROM pg_largeobject;
over time, or before and after "vacuumlo".

> The main tables that hold large objects are partitioned and every few
days I drop partition tables
> older than seven days, but despite all this, the system is growing in
size and not releasing space
> back to the OS.

Do you also delete the large objects referenced in these dropped tables?
They won't vanish automatically.

If you use large objects in a partitioned table, you probably have
a design problem. Having to clean up after orphaned large objects
will mitigate the performance gain by dropping partitions instead
of deleting data. You might be better off with bytea.

> Using either vacuum full or cluster to fix pg_largeobjects will
require a large amount of work space
> which I do not have on this server.
>
> Is there another method of scanning postgres tables, moving active
blocks and releasing store back to
> the OS?

If VACUUM does not keep pg_largeobject from growing, VACUUM FULL or
something else will not help either.
You have to figure out why your large objects don't get deleted.
Only after they are deleted, VACUUM can free the space.

> Failing this, I can see an NFS mount being required.

Beg your pardon?

Yours,
Laurenz Albe

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

Re: [GENERAL] Large Objects and and Vacuum

[Alban Hertroys]

On 31 December 2011 00:54, Simon Windsor  wrote:
> I am struggling with the volume and number of XML files a new application is
> storing. The table pg_largeobjects is growing fast, and despite the efforts
> of vacuumlo, vacuum and auto-vacuum it keeps on growing in size

I can't help but wonder why you're using large objects for XML files?
Wouldn't a text-field be sufficient? Text-fields get toasted, that
would safe you some space.

Another option would be to use xml-fields, but that depends on whether
you have valid XML and whether you have any desire to make use of any
xml-specific features such fields provide. There will probably be a
performance hit for this.

I do realise that you can stream large objects, that's a typical
use-case for choosing for them, but with XML files that doesn't seem
particularly useful to me; after all, they're not valid if not
complete. You have to read the whole file into memory _somewhere_
before you can interpret them meaningfully. The exception to that rule
is if you're using a SAX-parser (which also explains why those parsers
usually have fairly limited features).

Of course there are valid reasons for choosing to use large objects
for XML files, I assume yours are among them. If they're not, however,
maybe you should have a thorough look at your problem again.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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

Re: [GENERAL] Large Objects and and Vacuum

[John R Pierce]

On 12/30/11 3:54 PM, Simon Windsor wrote:
I am struggling with the volume and number of XML files a new 
application is storing. 


how big are these XML files?  large_object was meant for storing very 
large files, like videos, etc. multi-megabyte to gigabytes.   XML stuff 
is typically a lot smaller than that.


me, I would be decomposing the XML in my application and storing the 
data in proper relational tables, and only generate XML output if I 
absolutely had to send it to another system beyond my control as its 
easily one of the most inefficient methods of data representation out there.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


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

Re: [GENERAL] Large Objects and and Vacuum

[Simon Windsor]

Hi

Thanks for the response.

I am new to small IT company that have recently migrated an Oracle based
system Postgres. The system stores full XML responses, ranging in size from
a few K to over 55MB, and a sub set of key XML fields are stored on a more
permanent basis.

The database design was thus determined by the previous Oracle/Java system,
with empty LOBS being created and data being streamed in.

The data only has to be kept for a few days, and generally the system is
performing well, but as stated in the email, regular use of vacuumlo, vacuum
and autovacuum leaves the OS disc space slowly shrinking.

As a last resort this week, I'm going to get 500+GB of extra file store
added, add a tablespace and move pg_largeobjects to this area. Then use
CLUSTER to rebuild pg_largeobjects back in the default tablespace. This
should fix things I hope, and if needed I'll use Cluster regularly.

Simon 


-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: 02 January 2012 11:18
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Large Objects and and Vacuum

On 12/30/11 3:54 PM, Simon Windsor wrote:
> I am struggling with the volume and number of XML files a new 
> application is storing.

how big are these XML files?  large_object was meant for storing very 
large files, like videos, etc. multi-megabyte to gigabytes.   XML stuff 
is typically a lot smaller than that.

me, I would be decomposing the XML in my application and storing the data in
proper relational tables, and only generate XML output if I absolutely had
to send it to another system beyond my control as its easily one of the most
inefficient methods of data representation out there.



-- 
john r pierceN 37, W 122
santa cruz ca mid-left coast


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


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

Re: [GENERAL] Large Objects and and Vacuum

[Albe Laurenz]

Simon Windsor wrote:
[pg_largeobject keeps growing]

> The data only has to be kept for a few days, and generally the system
is
> performing well, but as stated in the email, regular use of vacuumlo,
vacuum
> and autovacuum leaves the OS disc space slowly shrinking.
> 
> As a last resort this week, I'm going to get 500+GB of extra file
store
> added, add a tablespace and move pg_largeobjects to this area. Then
use
> CLUSTER to rebuild pg_largeobjects back in the default tablespace.
This
> should fix things I hope, and if needed I'll use Cluster regularly.

You did not pay attention.

Did you verify that large objects actually get deleted in your system?
If not, no magic will ever make pg_largeobject shrink.

In current PostgreSQL, CLUSTER essentially does the same as VACUUM FULL.

Yours,
Laurenz Albe

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

[GENERAL] Large objects [BLOB] again - general howto

[Lada 'Ray' Lostak]

Hi there :)

Someone asks about performance of Large objects [LO] in PgSql [PG]. It
was interesting for me, because I didn't work yet with them yet and I will
have to soon. I tried search the web, doc and mailinglists, but I didn't
found any adequate reply. I would be happy, of someone, who have experimence
with them (or who know PG internals :) can make it clean. I think I am not
alone, who is interested in this topic.

In past time, where row data was limited by single tupple (8K-32K), LO
was really needed, if you wanted to store more than 8K per row. Older
implementation issues are described in documentation.

Today, there is 1G limit per row. Is there reason to use LO instead of
storing simple 'text' into database table ? (let's ignore 2G limit on LO,
which can make LO interesting in some cases :)Documentation says it is
obsolvete. But documentation also doesn't tell anything about performance,
suggestion about using, etc.

By 'access' to binary file, I understand:

- reading (and then working with its contents on server side)
- sending (read and send as-is to client, without workaround)
- updating (from contents which I get from somewhere)

LO means storing file using large objects,
column means storing file as 'text' column (standard way).

Let's say I am having 'file' - smaller (tenhts of Kb) - let's say some
JPG image (photo of beer :) The file is 'readed' as is and passed to client
(browser, app, whatever). In this case, would be probably better to store
file as normal column, because it is easier to access it. This is one of
typical use. Every time I will write to that image, SQL parser need to parse
all datas, allocate proper memory,  I think, real differnece will not be
too big, since picture having just tenths of Kb.

But let's have the same picture, but in pretty fine resolution for
printing. File size will be megabytes and more [let's say 10Mb]. Ofcourse,
file should be stored in DB because of data integrity. In this case, we will
need pretty much memory while reading/updating. All the time. And if I will
get many similar requests - and they will go directly to DB, not throw sime
'local cache', it can raise problem.

Using 'text' column as binaru file facts [Am I right ???]:

- time which SQL parser need to parse statements will be
  significant (adequate its size)
- memory which SQL parser need will be at least equal to filesize
- while fetching datas, file will be stored in memory while sending
  result
- when I will receive result, PG will 'release' sql result memory,
  but file stay on 'my' side (still eating memory)
- while updating file, we need 2*file_size memory at least (one for my
  generated SQLstatement, second for PgSql internal process - at least)
- using char/text type is not 'binary safe', so, I need to convert it
  (next  additional memory/CPU)

On the other side, LO facts [Am I right ???]:

- datas to file are not going throw SQL parser (no additional CPU/TIME
  requirements)
- PG uses pg_largeobjects table to store the files 'directly'
- datas are transfered in blocks - there is no nned for memory
- because of binary safe functions, no additional converts needed
- when I am updatinng the file, it doesn't need to be in memory - can be
  copied throw buffer from local stored file and PG also doesn't need to
  have its contents in memory (I don't count buffer/tupple/...)

Please, let me know, where I am true and where I am false.

If the PG at the end uses LO throw standard SQL, then everigth I wrote is
false and there is really no reason for use LO. Then I will miss that
feature. So, I hope it works the assumed way :)

So, my questions:

1. How big are claims of parser while analyzing 'megs' statement ? I
mean CPU/Memory in comparing with LO ?
2. How PG works with LO internally - throw SQL statements or directly ?
3. When I will access LO file, it will be accessed really thow 'buffers'
or PgSql somehow prepare file 'as-is' and then access it ?

If the things works as I assume (but I don't know where to verify right now,
except sources and mailing list, so, I selected faster way :)

Conclusion:

a. For smaller files the difference between CPU/MEMORY usage is small
LO: is not really needed for this case
column: wins, because of consistent/portable work

b. Files, which contents are needed 'as is' (full size) and work with
them on server side it is also not too big difference
LO: files here will probably save additional CPU, but only while
updating contents
column: wins, CPU difference only while updating

c. Larger files, not processed on server-side (typically sent to client)
LO: wins, save a LOTT OF memory while reading and also memory
and cpu time while updating
column: lost, easts redundant CPU & MEMORY, disadvantage

[GENERAL] Large Objects in serializable transaction question

[Andreas=20Sch=F6nbach]

I have a testprogram (using libpq) reading data from a cursor and large objects 
according to the result of the cursor. The cursor is opened in a serializable 
transaction.

Just for test reasons I know tried the following:
I started the test program that reads the data from the cursor and that reads the 
large objects according to the result of the fetch. While the test was running I now 
was dropping all large objects in a parallel session. Since I am using a serializable 
transaction in the test program I still should be able to read all the large objects, 
even if I drop them in a parallel session. But it does not work. I get an error, that 
the large object can't be opened.

I now tried the same with with two simple psql sessions. Here it works the way I 
expected it to.

Has anybody an explanation for that? Is it due to the libpq functions lo_open ...?

Thanks
Andreas
__
Erster Klick - SMS versenden, zweiter Klick - die Telefonnummer im 
Adressbuch speichern bei: http://freemail.web.de/features/?mc=021151


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Large Objects: Sizeof and Deleting Unlinked LOs

[Howard Cole]

Is there an SQL function to determine the size of a large object?

Also, can I safely delete all the large objects in 
pg_catalog.pg_largeobject? For example:


select lo_unlink(loid) from (select distinct loid from 
pg_catalog.pg_largeobject) as loids where loid not in (select my_oid 
from my_only_table_that_uses_large_objects)


Or are there other things stored in there that I don't know about!

Thanks.

Howard Cole
www.selestial.com

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

Re: [GENERAL] Large objects [BLOB] again - general howto

[Jeremiah Jahn]

Here's a quick list of my experiences with BLOB's and such.

Performance is just fine, I get about 1M hits a month and haven't had
any problems. Use a BLOB if you don't need to search though the data.
The main reason being that bytea and text types are parsed. To explain,
your entire SQL statement has to be parsed to make sure it's valid, this
includes you 100 meg jpeg. Slow, REAL slow. 

I have found that it is best to have a separate connection for BLOB's
and one for everything else. Mind you, this is with Java, but the
autocommit settings on the connection don't appear to be thread safe, so
in high traffic you can accidentally cut off a transfer, or stop one
before it ever starts. 

-jj-

On Sun, 2003-11-23 at 10:48, Randolf Richardson, DevNet SysOp 29 wrote:
> > Someone asks about performance of Large objects [LO] in PgSql [PG].  It 
> > was interesting for me, because I didn't work yet with them yet and I
> > will have to soon. I tried search the web, doc and mailinglists, but I
> > didn't found any adequate reply. I would be happy, of someone, who have
> > experimence with them (or who know PG internals :) can make it clean. I
> > think I am not alone, who is interested in this topic.
> 
>   You're certainly not alone.  I haven't done this yet, but will need to 
> in the near future with a web-based database project I'm in the midst of 
> planning at the moment which will involve allowing users to upload PNG, GIF 
> and JPeG images and having them displayed in a sort of "picture album" type 
> layout.
> 
> > In past time, where row data was limited by single tupple (8K-32K), LO 
> > was really needed, if you wanted to store more than 8K per row. Older
> > implementation issues are described in documentation.
> 
>   I'm somewhat concerned about this, but not because of the large object 
> support (which I understand is handled by not storing the object data 
> directly in the row, thus circumventing this limitation altogether), rather 
> I think about scalability with large rows that have a lot of columns that 
> sum up to more than 32,768 bytes in size.
> 
> > Today, there is 1G limit per row. Is there reason to use LO instead of 
> > storing simple 'text' into database table ? (let's ignore 2G limit on
> 
>   That depends on the needs of your application.  If all you're storing 
> is text data, then the choice is yours.  If you're storing binary data, 
> then simple text probably won't be appropriate.
> 
> > LO, which can make LO interesting in some cases :)Documentation says it
> > is obsolvete. But documentation also doesn't tell anything about
> > performance, suggestion about using, etc.
> > 
> > By 'access' to binary file, I understand:
> > 
> > - reading (and then working with its contents on server side)
> > - sending (read and send as-is to client, without workaround)
> > - updating (from contents which I get from somewhere)
> > 
> > LO means storing file using large objects,
> > column means storing file as 'text' column (standard way).
> 
>   I don't know about this.  Hopefully someone else who has experience in 
> this area will jump in here and clear this matter up.
> 
> > Let's say I am having 'file' - smaller (tenhts of Kb) - let's say some 
> > JPG image (photo of beer :) The file is 'readed' as is and passed to
> > client (browser, app, whatever). In this case, would be probably better
> > to store file as normal column, because it is easier to access it. This
> > is one of typical use. Every time I will write to that image, SQL parser
> > need to parse all datas, allocate proper memory,  I think, real
> > differnece will not be too big, since picture having just tenths of Kb.
> 
>   If you can ensure that the image size will never exceed the width of 
> your column, then you'll be okay, but note that image sizes can vary widely 
> depending on a number of factors in addition to image dimensions, one of 
> which that is often overlooked is the possibility of poorly designed 
> compression algorithms in the applications used to generate the JPeG image 
> or the user selecting minimal compression because they want a better 
> quality image (I do this sometimes in the web development I do, but only 
> when it's appropriate).
> 
>   Remember, however, that storing binary data in a column intended for 
> text might cause some problems down the road (even if it doesn't now) since 
> it will contain unexpected data.  You probably should look into the text 
> encoding systems out there to see if there could be some potential problems 
> with, for example, your data being misinterpreted as Chinese text.
> 
> > But let's have the same picture, but in pretty fine resolution for
> > printing. File size will be megabytes and more [let's say 10Mb].
> > Ofcourse, file should be stored in DB because of data integrity. In this
> > case, we will need pretty much memory while reading/updating. All the
> > time. And if I will get many simil

Re: [GENERAL] Large objects [BLOB] again - general howto

[Paul Thomas]

On 25/11/2003 21:55 Jeremiah Jahn wrote:
[snip]
I have found that it is best to have a separate connection for BLOB's
and one for everything else. Mind you, this is with Java, but the
autocommit settings on the connection don't appear to be thread safe, so
in high traffic you can accidentally cut off a transfer, or stop one
before it ever starts.
How could a connection ever have the kind of "thread-safe" behavior you
seem to believe it should have?
--
Paul Thomas
+--+-+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants | 
http://www.thomas-micro-systems-ltd.co.uk   |
+--+-+

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Large objects [BLOB] again - general howto

[Rick Gigger]

Thanks!  This is exactly what I wanted to know when I first asked the
question.  And it is the only response that seems to make sense.  Does
anyone else have experiecne with this?

rg

<
Here's a quick list of my experiences with BLOB's and such.

Performance is just fine, I get about 1M hits a month and haven't had
any problems. Use a BLOB if you don't need to search though the data.
The main reason being that bytea and text types are parsed. To explain,
your entire SQL statement has to be parsed to make sure it's valid, this
includes you 100 meg jpeg. Slow, REAL slow.

I have found that it is best to have a separate connection for BLOB's
and one for everything else. Mind you, this is with Java, but the
autocommit settings on the connection don't appear to be thread safe, so
in high traffic you can accidentally cut off a transfer, or stop one
before it ever starts.

-jj-
>


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Large Objects: Sizeof and Deleting Unlinked LOs

[Harald Fuchs]

In article <4b72aeb3.4000...@selestial.com>,
Howard Cole  writes:

> Is there an SQL function to determine the size of a large object?

I'm using a pgsql helper function for that:


  CREATE FUNCTION lo_size(oid oid) RETURNS integer
  LANGUAGE plpgsql
  AS $$
  DECLARE
fd int;
res int;
i int;
  BEGIN
fd = lo_open(oid, 262144); -- INV_READ
IF fd < 0 THEN
  RAISE EXCEPTION 'lo_open returns %', fd;
END IF;
res = lo_lseek(fd, 0, 2); -- SEEK_END
IF res < 0 THEN
  RAISE EXCEPTION 'lo_lseek returns %', res;
END IF;
i = lo_close(fd);
IF i < 0 THEN
  RAISE EXCEPTION 'lo_close returns %', i;
END IF;
RETURN res;
  END;
  $$;

> Also, can I safely delete all the large objects in
> pg_catalog.pg_largeobject? For example:

> select lo_unlink(loid) from (select distinct loid from
> pg_catalog.pg_largeobject) as loids where loid not in (select my_oid
> from my_only_table_that_uses_large_objects)

Did you have a look at contrib/lo?


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

Re: [GENERAL] large objects,was: Restoring 8.0 db to 8.1

[Chris Browne]

[EMAIL PROTECTED] ("Harald Armin Massa") writes:
>> Not likely to change in the future, no.  Slony uses triggers to manage the
>> changed rows.  We can't fire triggers on large object events, so there's no
>> way for Slony to know what happened.
>
> that leads me to a question I often wanted to ask:
>
> is there any reason to create NEW PostgreSQL databases using Large
> Objects, now that there is bytea and TOAST? (besides of legacy needs)
>
> as much as I read, they take special care in dump/restore; force the
> use of some special APIs on creating, do not work with Slony 

They are useful if you really need to be able to efficiently access
portions of large objects.

For instance, if you find that you frequently need to modify large
objects, in place, that should be much more efficient using the LOB
interface than it would be using a bytea column.

It ought to be a lot more efficient to lo_lseek() to a position,
lo_read() a few bytes, and lo_write() a few bytes than it is to pull
the entire 42MB object out, read off a fragment, and then alter the
tuple.

That being said, I generally prefer bytea because it doesn't force me
into using a pretty weird "captive interface" to access the data.

If I found myself needing to make wacky updates on a large object, I'd
wonder if it wouldn't be better to have it expressed as a set of
tuples so that I'd not have a large object in the first place...
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "linuxdatabases.info")
http://www3.sympatico.ca/cbbrowne/x.html
"...  They are  not ``end  users'' until  someone presupposes  them as
such, as witless cattle." -- <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings