Database design confusing pg_restore, and misc pg_restore issues
От | Rick Yorgason |
---|---|
Тема | Database design confusing pg_restore, and misc pg_restore issues |
Дата | |
Msg-id | 4BE50CCE.6040903@longbowgames.com обсуждение исходный текст |
Ответы |
Re: Database design confusing pg_restore, and misc pg_restore issues
|
Список | pgsql-general |
Hey everyone, I run a website that sells videogames, and different games have different registration systems, so I have a database design that goes something like this: > registration_type enum('none', 'regtype1', 'regtype2') > > products(product_id, registration_type) > > order_item(order_id, product_id, check(order_item_has_reginfo(order_id, product_id))) > > regtype1_reginfo(order_id, product_id, misc rows) > > regtype2_reginfo(order_id, product_id, orthogonally misc rows) > > function order_item_has_reginfo(text, text) returns boolean as $$ > select exists( > select 1 from products where product_id = $2 > and ( > (reg_type = 'none') > or (reg_type = 'regtype1' and (select exists(select 1 from regtype1_reginfo where order_id = $1 and product_id= $2))) > or (reg_type = 'regtype2' and (select exists(select 1 from regtype2_reginfo where order_id = $1 and product_id= $2))) > ) > ) > $$ LANGUAGE 'SQL'; In other words, (order_id, product_id) of order_item is a foreign key to either reginfo1, reginfo2, or nothing, depending on which product it is. The works really well, until I try to use pg_dump/pg_restore, because it attempts to restore order_items before the reginfo tables. To get it to work properly, I need to load the schema, disable the check, load the data, then re-enable the check. I'm interested in either a more painless way of importing backups, or a better design. Incidentally, using --disable-triggers didn't disable checks, and --use-list didn't seem to actually work on my dev machine (Vista x64); it just pretends like everything went fine, without inserting any data. Here's what PowerShell prints out: > PS D:\projects\backup> & 'C:\Program Files (x86)\PostgreSQL\8.4\bin\pg_restore.exe' -v --use-list=backup.list -U blahblah-p 9999 -C backup.db > -- > -- PostgreSQL database dump > -- > > -- Started on 2010-05-07 22:22:02 > > SET statement_timeout = 0; > SET client_encoding = 'UTF8'; > SET standard_conforming_strings = off; > SET check_function_bodies = false; > SET client_min_messages = warning; > SET escape_string_warning = off; > > -- Completed on 2010-05-08 01:15:01 > > -- > -- PostgreSQL database dump complete > -- > > pg_restore.exe : pg_restore: implied data-only restore > At line:1 char:2 > + & <<<< 'C:\Program Files (x86)\PostgreSQL\8.4\bin\pg_restore.exe' -v --use-list=backup.list -U blahblah -p 9999 -C backup.db > + CategoryInfo : NotSpecified: (pg_restore: implied data-only restore:String) [], RemoteException > + FullyQualifiedErrorId : NativeCommandError Thanks for your help, -Rick-
В списке pgsql-general по дате отправления: