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-
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
