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-
