Fwd: Re: [gollem] storing files in PostgreSQL

Chuck Hagenbuch chuck at horde.org
Thu Apr 17 19:59:33 PDT 2003



----- Forwarded message from skeller at first-texas.com -----
    Date: Thu, 17 Apr 2003 17:21:00 -0500
    From: Sid Keller <skeller at first-texas.com>
Reply-To: Sid Keller <skeller at first-texas.com>
 Subject: Re: [gollem] storing files in PostgreSQL
      To: Chuck Hagenbuch <chuck at horde.org>

I'm using PostgreSQL v. 7.1.3.  I couldn't find a datatype of BLOB or LOB
and it
appeared to me that postgresql used a datatype of OID.  He is an example
from a
postgresql book.

create table images
(id       serial,
 prodid   int8,
 image    OID
);

horde=# insert into table images (prodid, image)
horde=# values( '1234',lo_import('/tmp/1234.jpg'));

horde=# select * from images;
id  | prodid  | image
----+---------+-------
  1 | 1234    | 51745

horde=# select lo_export (image.image, '/tmp/export.jpg')
horde=# from   image
horde=# where  prodid = '1234';

lo_import, lo_export, lo_unlink are postgresql functions that handle BLOBs.

Here is my some info from my postgresql logfile.  I left out part of the
insert
statement (the vfs_data field) that was inserting the 1st_texas3.gif file
into
the database.  I'll try to find something in the apache logs tomorrow.

2003-04-17 16:13:07 DEBUG:  query: SELECT vfs_name, vfs_type, vfs_data,
vfs_modified, vfs_owner
                                   FROM horde_vfs
                                   WHERE vfs_path = ''

2003-04-17 16:13:07 DEBUG:  ProcessQuery
2003-04-17 16:13:07 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:07 DEBUG:  StartTransactionCommand
2003-04-17 16:13:07 DEBUG:  query: SELECT vfs_name, vfs_path
                                   FROM horde_vfs
                                   WHERE vfs_path =
                                   AND vfs_type = 2
2003-04-17 16:13:07 ERROR:  parser: parse error at or near "AND"
2003-04-17 16:13:07 DEBUG:  AbortCurrentTransaction
2003-04-17 16:13:08 DEBUG:  StartTransactionCommand
2003-04-17 16:13:08 DEBUG:  query: BEGIN;ROLLBACK;
2003-04-17 16:13:08 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:08 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:08 DEBUG:  StartTransactionCommand
2003-04-17 16:13:08 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:08 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:08 DEBUG:  StartTransactionCommand
2003-04-17 16:13:08 DEBUG:  query: BEGIN;ROLLBACK;
2003-04-17 16:13:08 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:08 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:08 DEBUG:  StartTransactionCommand
2003-04-17 16:13:08 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:08 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:10 DEBUG:  StartTransactionCommand
2003-04-17 16:13:10 DEBUG:  query: SELECT vfs_name, vfs_type, vfs_data,
vfs_modified, vfs_owner
                                   FROM horde_vfs
                                   WHERE vfs_path = 'admin'


2003-04-17 16:13:10 DEBUG:  ProcessQuery
2003-04-17 16:13:10 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:10 DEBUG:  StartTransactionCommand
2003-04-17 16:13:10 DEBUG:  query: SELECT vfs_name, vfs_path
                                   FROM horde_vfs
                                   WHERE vfs_path = admin
                                   AND vfs_type = 2
2003-04-17 16:13:10 ERROR:  Attribute 'admin' not found
2003-04-17 16:13:10 DEBUG:  AbortCurrentTransaction
2003-04-17 16:13:10 DEBUG:  StartTransactionCommand
2003-04-17 16:13:10 DEBUG:  query: BEGIN;ROLLBACK;
2003-04-17 16:13:10 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:10 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:10 DEBUG:  StartTransactionCommand
2003-04-17 16:13:10 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:10 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:10 DEBUG:  StartTransactionCommand
2003-04-17 16:13:10 DEBUG:  query: BEGIN;ROLLBACK;
2003-04-17 16:13:10 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:10 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:10 DEBUG:  StartTransactionCommand
2003-04-17 16:13:10 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:10 DEBUG:  CommitTransactionCommand
/usr/local/bin/postmaster child[15130]: starting with (postgres -d4 -v131072
-p
hordetest )
FindExec: found "/usr/local/bin/postgres" using argv[0]
2003-04-17 16:13:26 DEBUG:  connection: host=127.0.0.1 user=horde
database=hordetest
2003-04-17 16:13:26 DEBUG:  InitPostgres
2003-04-17 16:13:26 DEBUG:  StartTransactionCommand
2003-04-17 16:13:26 DEBUG:  query: SELECT vfs_id
                                   FROM horde_vfs
                                   WHERE vfs_path  = 'admin'
                                   AND vfs_name = '1st_texas3.gif'

2003-04-17 16:13:26 DEBUG:  ProcessQuery
2003-04-17 16:13:26 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:26 DEBUG:  StartTransactionCommand
2003-04-17 16:13:26 DEBUG:  query: SELECT NEXTVAL('horde_vfs_seq')
2003-04-17 16:13:26 DEBUG:  ProcessQuery
2003-04-17 16:13:26 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:26 DEBUG:  StartTransactionCommand
2003-04-17 16:13:26 DEBUG:  query: insert into horde_logs
                                   values(NOW(), 'HORDE', 7, '[gollem] SQL
Query
by Horde_SQL::insertBlob():
                                                  query = "INSERT INTO
horde_vfs
(vfs_id, vfs_type, vfs_path, vfs_name,
                                                  vfs_modified, vfs_owner,
vfs_data)
                                                  VALUES (?, ?, ?, ?, ?, ?,
?)"
[on line 129 of "/var/www/horde/lib/SQL.php"]')


2003-04-17 16:13:26 DEBUG:  ProcessQuery
2003-04-17 16:13:26 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:26 DEBUG:  StartTransactionCommand
2003-04-17 16:13:26 DEBUG:  query: INSERT INTO horde_vfs (vfs_id, vfs_type,
vfs_path, vfs_name, vfs_modified, vfs_owner,
                                                          vfs_data)
                                                          VALUES ('12', 1,
'admin', '1st_texas3.gif', 1050614006,

'skeller at first-texas.com', '4749463839619a009000f7000004')
2003-04-17 16:13:26 ERROR:  oidin: error reading
"4749463839619a009000f7000004":
Result too large
2003-04-17 16:13:26 DEBUG:  AbortCurrentTransaction
2003-04-17 16:13:26 DEBUG:  StartTransactionCommand
2003-04-17 16:13:26 DEBUG:  query: SELECT vfs_name, vfs_type, vfs_data,
vfs_modified, vfs_owner
                                   FROM horde_vfs
                                   WHERE vfs_path = 'admin'

2003-04-17 16:13:26 DEBUG:  ProcessQuery
2003-04-17 16:13:26 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:26 DEBUG:  StartTransactionCommand
2003-04-17 16:13:26 DEBUG:  query: SELECT vfs_name, vfs_path
                                   FROM horde_vfs
                                   WHERE vfs_path = admin
                                   AND vfs_type = 2
2003-04-17 16:13:26 ERROR:  Attribute 'admin' not found
2003-04-17 16:13:26 DEBUG:  AbortCurrentTransaction
2003-04-17 16:13:26 DEBUG:  StartTransactionCommand
2003-04-17 16:13:26 DEBUG:  query: BEGIN;ROLLBACK;
2003-04-17 16:13:26 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:26 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:26 DEBUG:  StartTransactionCommand
2003-04-17 16:13:26 DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2003-04-17 16:13:26 DEBUG:  CommitTransactionCommand
2003-04-17 16:13:30 DEBUG:  StartTransactionCommand
2003-04-17 16:13:30 DEBUG:  query: insert into horde_logs
                                   values(NOW(), 'HORDE', 6, '[gollem]
192.168.1.102 logout [on line 55 of
                                   "/var/www/horde/gollem/login.php"]')
/usr/local/bin/postmaster: BackendStartup: pid 9482 user horde db hordetest
socket 5
/usr/local/bin/postmaster child[9482]: starting with (postgres -d4 -v131072
-p
hordetest )
FindExec: found "/usr/local/bin/postgres" using argv[0]
2003-04-17 16:13:31 DEBUG:  connection: host=127.0.0.1 user=horde
database=hordetest
2003-04-17 16:13:31 DEBUG:  InitPostgres
2003-04-17 16:13:31 DEBUG:  StartTransactionCommand
2003-04-17 16:13:31 DEBUG:  query: insert into horde_logs
                                   values(NOW(), 'HORDE', 5, '[imp] Logout
for
skeller at first-texas.com [192.168.1.102]
                                   from {localhost:143} [on line 38 of
"/var/www/horde/imp/login.php"]')


Sid


Quoting Chuck Hagenbuch <chuck at horde.org>:

> Quoting Sid Keller <skeller at first-texas.com>:
>
> > I've tried storing files in a postgresql database but have not had any
> > luck.  I can successfully create folders, but I have not been able to
> > upload files.  When I try to upload a file, gollem states that the file
> > has been received, however the file is not uploaded.  Any ideas?
>
> Not from just that. Log entries (horde, postgres, apache), error messages,
> anything?
>
> -chuck
>
> --
> Charles Hagenbuch, <chuck at horde.org>
> "... It is not more light we need, but more warmth! We die of cold, not of
> darkness. It is not the night that kills, but the frost." - Miguel de
> Unamuno
>
> --
> Gollem mailing list
> Frequently Asked Questions: http://horde.org/faq/
> To unsubscribe, mail: gollem-unsubscribe at lists.horde.org
>


-------------------------------------------------
1st Texas Home Health
Where patients come 1st



----- End forwarded message -----


-chuck

--
Charles Hagenbuch, <chuck at horde.org>
"... It is not more light we need, but more warmth! We die of cold, not of
darkness. It is not the night that kills, but the frost." - Miguel de
Unamuno


More information about the gollem mailing list