Thread: TEXT and BLOBS
I want to find out how confused I am. ;-) I'm testing something on a Postgres 7.0.2 database. I made a small table with two columns, one serial and the other TEXT, and tried to make serveral inserts with PHP. Some went OK, until I tried to insert a large amount of text. It failed. Of course, I did it on porpose to check the 8 K tuple limitation. Until here, am I right? So, now I'm gonna try to make inserts to a table just like the other one, but with the slight change of making the text column a oid column. The questions are: 1) where are the blobs stored? 2) Is it posible to insert a chunk of text that has not been saved to a file? 3) where is all the documentation on the behave of BLOBS? All I have found was in Bruce's book. I couldn't find such things in the users documentations. TIA -- "And I'm happy, because you make me feel good, about me." - Melvin Udall ----------------------------------------------------------------- Martín Marqués email: martin@math.unl.edu.ar Santa Fe - Argentina http://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -----------------------------------------------------------------
On Wed, Nov 08, 2000 at 10:36:58AM -0300, Martin A. Marques wrote: > I want to find out how confused I am. ;-) > I'm testing something on a Postgres 7.0.2 database. I made a small table with > two columns, one serial and the other TEXT, and tried to make serveral > inserts with PHP. > Some went OK, until I tried to insert a large amount of text. It failed. Of > course, I did it on porpose to check the 8 K tuple limitation. > Until here, am I right? > So, now I'm gonna try to make inserts to a table just like the other one, but > with the slight change of making the text column a oid column. > The questions are: > 1) where are the blobs stored? > 2) Is it posible to insert a chunk of text that has not been saved to a file? > 3) where is all the documentation on the behave of BLOBS? All I have found > was in Bruce's book. I couldn't find such things in the users documentations. > I cant speak for PHP, but in perl or in C you have a documented module that will handle this things. (in perl its DBD::Pg). You dont need to save the text to a file first, you can write directely into the blob. The principal things are explained in the postgres-programmers-guide you can find at http://www.at.postgresql.org/devel-corner/docs/programmer/ in chapter 16 'Large Objects' Although, dealing with blobs seems to be a dangerous things, cause I didnt find a proper backup-method until now :-( peter -- mag. peter pilsl phone: +43 676 3574035 fax : +43 676 3546512 email: pilsl@goldfisch.at sms : pilsl@max.mail.at pgp-key available
> I cant speak for PHP, but in perl or in C you have a documented module > that will handle this things. (in perl its DBD::Pg). You dont need to > save the text to a file first, you can write directely into the blob. > > The principal things are explained in the postgres-programmers-guide > you can find at > http://www.at.postgresql.org/devel-corner/docs/programmer/ > in chapter 16 'Large Objects' > > Although, dealing with blobs seems to be a dangerous things, cause I > didnt find a proper backup-method until now :-( Which would that method be? I know that with Postgres 7.1 we will have a brand new backup system (WAL), so I'm looking forward to it. ;-) -- "And I'm happy, because you make me feel good, about me." - Melvin Udall ----------------------------------------------------------------- Mart�n Marqu�s email: martin@math.unl.edu.ar Santa Fe - Argentina http://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -----------------------------------------------------------------
On Thu, Nov 09, 2000 at 09:00:13AM -0300, Martin A. Marques wrote: > > I cant speak for PHP, but in perl or in C you have a documented module > > that will handle this things. (in perl its DBD::Pg). You dont need to > > save the text to a file first, you can write directely into the blob. > > > > The principal things are explained in the postgres-programmers-guide > > you can find at > > http://www.at.postgresql.org/devel-corner/docs/programmer/ > > in chapter 16 'Large Objects' > > > > Although, dealing with blobs seems to be a dangerous things, cause I > > didnt find a proper backup-method until now :-( > > Which would that method be? I know that with Postgres 7.1 we will have a > brand new backup system (WAL), so I'm looking forward to it. ;-) > I always thought backuping the files and pg_dump would be enough. (I was sure there would be a way to restore the blobs out of the files when I would need it) Now I have a table with blob-oid's and the xin* files and I cant restore the blobs :-( and noone seems to be able to help me. I was thinking about writing some tools that scans the tables for oid's and save the corresponding blobs to files and restore the blobs/update the tables later. But if (WAL) will do it for me I'm also looking forward for it. When will it come and where can I find information about it ? peter -- mag. peter pilsl phone: +43 676 3574035 fax : +43 676 3546512 email: pilsl@goldfisch.at sms : pilsl@max.mail.at pgp-key available
> I always thought backuping the files and pg_dump would be enough. (I > was sure there would be a way to restore the blobs out of the files > when I would need it) > > Now I have a table with blob-oid's and the xin* files and I cant > restore the blobs :-( and noone seems to be able to help me. > > I was thinking about writing some tools that scans the tables for > oid's and save the corresponding blobs to files and restore the > blobs/update the tables later. But if (WAL) will do it for me I'm also > looking forward for it. When will it come and where can I find > information about it ? Actually there is a version of pg_dump which was written by Philip Warner. You can download this pg_dump for 7.0.2 from: ftp://ftp.rhyme.com.au/pub/postgresql/pg_dump/blobs/ -- Sincerely Yours, Denis Perchine ---------------------------------- E-Mail: dyp@perchine.com HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 ----------------------------------
On Jue 09 Nov 2000 10:11, Peter Pilsl wrote: > > I always thought backuping the files and pg_dump would be enough. (I > was sure there would be a way to restore the blobs out of the files > when I would need it) > > Now I have a table with blob-oid's and the xin* files and I cant > restore the blobs :-( and noone seems to be able to help me. > > I was thinking about writing some tools that scans the tables for > oid's and save the corresponding blobs to files and restore the > blobs/update the tables later. But if (WAL) will do it for me I'm also > looking forward for it. When will it come and where can I find > information about it ? WAL is in the development tree of Postgres-7.1, so I guess it will be in the postgres stable source when 7.1 comes out next year. Saludos... :-) -- "And I'm happy, because you make me feel good, about me." - Melvin Udall ----------------------------------------------------------------- Mart�n Marqu�s email: martin@math.unl.edu.ar Santa Fe - Argentina http://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -----------------------------------------------------------------
----- Original Message ----- From: Martin A. Marques <martin@math.unl.edu.ar> To: <pgsql-general@postgresql.org> Sent: Wednesday, November 08, 2000 8:36 AM Subject: [GENERAL] TEXT and BLOBS > I want to find out how confused I am. ;-) > I'm testing something on a Postgres 7.0.2 database. I made a small table with > two columns, one serial and the other TEXT, and tried to make serveral > inserts with PHP. > Some went OK, until I tried to insert a large amount of text. It failed. Of > course, I did it on porpose to check the 8 K tuple limitation. > Until here, am I right? > So, now I'm gonna try to make inserts to a table just like the other one, but > with the slight change of making the text column a oid column. > The questions are: > 1) where are the blobs stored? > 2) Is it posible to insert a chunk of text that has not been saved to a file? > 3) where is all the documentation on the behave of BLOBS? All I have found > was in Bruce's book. I couldn't find such things in the users documentations. > > TIA > > -- > "And I'm happy, because you make me feel good, about me." - Melvin Udall > ----------------------------------------------------------------- > Martín Marqués email: martin@math.unl.edu.ar > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > Administrador de sistemas en math.unl.edu.ar > ----------------------------------------------------------------- >