pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcopy command and blobs
6+ messages / 5 participants
[nested] [flat]
* copy command and blobs
@ 2011-01-20 20:12 Madhu Ramachandran <iammadhu@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Madhu Ramachandran @ 2011-01-20 20:12 UTC (permalink / raw)
To: pgsql-performance
hello,
i have a table with OID column.. I want to use the copy command to insert
bunch of rows (1 million).
but iam unable to specify the correct format for the oid type (i have .jpg
files to be stored in this column)..
I tried giving the path to the file, lo_import('pathto file').. appreciate
any tips.. i did search the archives , but couldnt find any answer on this
Thanks again
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: copy command and blobs
@ 2011-01-20 20:17 Mladen Gogala <mladen.gogala@vmsinfo.com>
parent: Madhu Ramachandran <iammadhu@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Mladen Gogala @ 2011-01-20 20:17 UTC (permalink / raw)
To: Madhu Ramachandran <iammadhu@gmail.com>; +Cc: pgsql-performance
Madhu Ramachandran wrote:
> hello,
>
> i have a table with OID column.. I want to use the copy command to
> insert bunch of rows (1 million).
> but iam unable to specify the correct format for the oid type (i have
> .jpg files to be stored in this column)..
Huh? oid is a keyword, an automatically generated row id, and is
deprecated. You shouldn't be doing anything with it, much less copying it.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: copy command and blobs
@ 2011-01-20 21:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mladen Gogala <mladen.gogala@vmsinfo.com>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2011-01-20 21:11 UTC (permalink / raw)
To: Mladen Gogala <mladen.gogala@vmsinfo.com>; +Cc: Madhu Ramachandran <iammadhu@gmail.com>; pgsql-performance
Mladen Gogala <mladen.gogala@vmsinfo.com> writes:
> Madhu Ramachandran wrote:
>> i have a table with OID column.. I want to use the copy command to
>> insert bunch of rows (1 million).
>> but iam unable to specify the correct format for the oid type (i have
>> .jpg files to be stored in this column)..
> Huh? oid is a keyword, an automatically generated row id, and is
> deprecated. You shouldn't be doing anything with it, much less copying it.
I think what the OP actually means is he's thinking of importing some
images as large objects, then storing their OIDs in a user (not system)
column of type oid. COPY can't be used for that though.
It might be better to use a bytea column, if you're willing to deal with
bytea's weird escaping rules.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: copy command and blobs
@ 2011-01-21 22:10 Madhu Ramachandran <iammadhu@gmail.com>
parent: Mladen Gogala <mladen.gogala@vmsinfo.com>
1 sibling, 1 reply; 6+ messages in thread
From: Madhu Ramachandran @ 2011-01-21 22:10 UTC (permalink / raw)
To: Mladen Gogala <mladen.gogala@vmsinfo.com>; +Cc: pgsql-performance
i was looking at
http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html
<http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html>when
they talk about using OID type to store large blobs (in my case .jpg files )
On Thu, Jan 20, 2011 at 3:17 PM, Mladen Gogala <mladen.gogala@vmsinfo.com>wrote:
> Madhu Ramachandran wrote:
>
>> hello,
>>
>> i have a table with OID column.. I want to use the copy command to insert
>> bunch of rows (1 million).
>> but iam unable to specify the correct format for the oid type (i have .jpg
>> files to be stored in this column)..
>>
> Huh? oid is a keyword, an automatically generated row id, and is
> deprecated. You shouldn't be doing anything with it, much less copying it.
>
>
> --
>
> Mladen Gogala Sr. Oracle DBA
> 1500 Broadway
> New York, NY 10036
> (212) 329-5251
> http://www.vmsinfo.com The Leader in Integrated Media Intelligence
> Solutions
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: copy command and blobs
@ 2011-01-23 03:41 Robert Haas <robertmhaas@gmail.com>
parent: Madhu Ramachandran <iammadhu@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Robert Haas @ 2011-01-23 03:41 UTC (permalink / raw)
To: Madhu Ramachandran <iammadhu@gmail.com>; +Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>; pgsql-performance
On Fri, Jan 21, 2011 at 5:10 PM, Madhu Ramachandran <iammadhu@gmail.com> wrote:
> i was looking at
> http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html
> when they talk about using OID type to store large blobs (in my case .jpg
> files )
It's probably worth noting that that document is 9 years old. It
might be worth reading something a little more up-to-date. Perhaps:
http://www.postgresql.org/docs/current/static/largeobjects.html
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: copy command and blobs
@ 2011-02-06 18:15 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Greg Spiegelberg @ 2011-02-06 18:15 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Madhu Ramachandran <iammadhu@gmail.com>; Mladen Gogala <mladen.gogala@vmsinfo.com>; pgsql-performance
On Sat, Jan 22, 2011 at 8:41 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Jan 21, 2011 at 5:10 PM, Madhu Ramachandran <iammadhu@gmail.com>
> wrote:
> > i was looking at
> > http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html
> > when they talk about using OID type to store large blobs (in my case .jpg
> > files )
>
> It's probably worth noting that that document is 9 years old. It
> might be worth reading something a little more up-to-date. Perhaps:
>
> http://www.postgresql.org/docs/current/static/largeobjects.html
>
>
A bit late to respond but better than never!
As of my latest testing in 8.3, I've found that the lo_* functions while
adequate are a bit slow. Our implemented alternative that leverages
pg_read_file() is significantly faster. I believe it is because
pg_read_file() tells the database to go straight to the file system rather
than through the client connection. From memory, I seem to recall this
being about 20% faster than the lo_* option or simple INSERTs.
The downside to pg_read_file() is that the file must be 1) on the same
system as the database and 2) must be under the $PGDATA directory. We opted
to create a directory $PGDATA/public with proper system-side permissions but
open enough to allow the database owner to read the files.
For example,
postgres=# select pg_read_file('public/a_file', 0,
(pg_stat_file('postgresql.conf')).size);
We use this method in conjunction with additional checks to store files in
tables governed by the MD5 hash of the file to prevent duplication.
HTH.
Greg
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-02-06 18:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-20 20:12 copy command and blobs Madhu Ramachandran <iammadhu@gmail.com>
2011-01-20 20:17 ` Mladen Gogala <mladen.gogala@vmsinfo.com>
2011-01-20 21:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-21 22:10 ` Madhu Ramachandran <iammadhu@gmail.com>
2011-01-23 03:41 ` Robert Haas <robertmhaas@gmail.com>
2011-02-06 18:15 ` Greg Spiegelberg <gspiegelberg@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox