Обсуждение: pg_dump/restore to convert BLOBs to LZTEXT (optional!)
Now that I *seem* to have a pg_dump/restore that handles BLOBs (for 7.0.2 and 7.1), would there be any value in adding a 'conversion' option to pg_restore (for 7.1)? The idea being that there are probably people out there who use BLOBs for large text fields, who *might* be better served is using LZTEXT (or whatever is appropriate) in 7.1. At this stage I am not sure *how* I would do the conversion, but my initial thought would be to store the BLOB as an LZTEXT in a temp table, then find all tables with BLOB columns, either do an ALTER TABLE ALTER COLUMN <name> LZTEXT (if supported?), or just add a new column? Then set the column value to the new LZTEXT. Another option would be to do 'alter table add <newname> lztext', then load the data, drop the old column and rename the new one... For BLOBS that are referenced in more than one table, this will duplicate the text which may be a problem, but this is only enforcing what should have been the case all along, I think. I'd be interested in feedback from people who think this would be a good/bad idea as well as any ideas on how to implement it nicely... ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes: > For BLOBS that are referenced in more than one table, this will duplicate > the text which may be a problem, As well as break the semantics: if you have a multiply-referenced BLOB then you can update it through any reference and the changes are visible through all the references. Not so after you convert the data into non-BLOB values. Another problem is that even if you manage to restructure the database properly, you can't do anything to help with conversion of the application code that talks to the database (not even the functions and triggers/rules that may appear right in the database). AFAICS converting from BLOB to TOAST storage is going to require a fair amount of actual thought and work on the part of the DB programmer/admin. I don't see that pg_dump can help meaningfully, and I'd just as soon resist feature bloat in pg_dump. regards, tom lane
At 21:10 3/08/00 -0400, Tom Lane wrote: >As well as break the semantics: if you have a multiply-referenced BLOB >then you can update it through any reference and the changes are visible >through all the references. Not so after you convert the data into >non-BLOB values. That's what I meant. People *shouldn't* expect BLOB fields to be updated in more than one table, but the implementation currently allow it (since BLOBs are not implemented as fields). >I don't see that pg_dump can help meaningfully, >and I'd just as soon resist feature bloat in pg_dump. Fine. Thinking about it, even *if* it was implemented as a utility, I suspect (for the reasons you outlined), conversion would be a multi-step process. And a more useful utility would be one that converted an existing database, rather than trying to everything in the 'restore'... Forget I even mentioned it. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/