Photo Organizer

  • Status Closed
  • Percent Complete
  • Task Type Bug Report
  • Category Backend / Core → Database
  • Assigned To No-one
  • Operating System Linux
  • Severity Medium
  • Priority Very Low
  • Reported Version 2.36
  • Due in Version Undecided
  • Due Date Undecided
  • Votes
  • Private
Attached to Project: Photo Organizer
Opened by jeffrobins - 2009-06-21
Last edited by pizza - 2009-08-13

FS#412 - Recursive redundancies make it impossible to restore an unmodified database

It is possible to create recursive redundancies that make it impossible to restore an unmodified database. For example, the "thumb_ver" field in table "folder" references a photo that is in a nested folder (actually many tables in the middle of this). The nested folder cannot be added until the first folder is added, but the first folder cannot be added because the "thumb_ver" field references a non-existent thumbnail. I will try to add more detail later, but I have uploaded a dump of my database for now. The dump was created using the backup button in webmin. To sucessfully load the table "folder" all of the "thumb_ver" values must be NULL.

   po_db.sql (16.89 MiB)
Closed by  pizza
2009-08-13 15:19
Reason for closing:  Not a bug
Additional comments about closing:  As discussed via email, the problem turned out to be pl/pgsql not being installed in the database.
pizza commented on 2009-06-22 01:55

How are you doing the DB restore? Also through webmin? The dump looks like a standard pg_dump, and if you look at it carefully you'll see that the thumb_ver field constraint (as well as all other constraints) isn't added until the very end, well after the data is imported.

I'll try loading this into a throwaway database later tonight.

Oh, what version of postgresql are you using?

pizza commented on 2009-06-22 02:14

I just imported the dumpfile into a scratch database (postgresql 8.3.7), and it worked fine.

psql -U postgres template1
create database temp;
create user po_user;
\c temp
\i /tmp/po_db.sql

and after a large pile of output, I have fully restored database, complete with all thumb_vers (13 in this dump)

I'm using version 8.3.7. I'll have to try that later tonight. I've been using the restore command through webmin and the execute SQL commands from webmin and phpPgAdmin. Did the restore create the language as well?

PS: I meant dependencies instead of redundancies in my original post. That's the problem when you're working hard on one problem and then switch quickly to another. Also, the sleep deprivation doesn't help.

I finally had time to try your restore method. Sorry it took so long.

I get a bunch of errors when I import the database. I have attached the output of the commands.

Sorry, now it's attached.

pizza commented on 2009-06-30 18:04

From the look of the output, it seems you don't have pl/pgsql support in your new postgresql installation.

At least in the Fedora Linux world, this is part of the 'postgresql-server' package.

Okay. The system I was testing on did not have pl/pgsql support installed. In Mandriva 2009.1 there is a PostgreSQL package to add PostgreSQL and a PostgreSQL-plpgsql to add pl/pgsql support to the database. Due to other factors I have been unable to test the system with the pl/pgsql support installed. I will hopefully get to that this weekend or possibly sooner. I also had the problem with a hosted system, but I don't know if they were missing the pl/pgsql support as well.


Available keyboard shortcuts


Task Details

Task Editing