Database design confusing pg_restore, and misc pg_restore issues

From: Rick Yorgason <rick(at)longbowgames(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Database design confusing pg_restore, and misc pg_restore issues
Date: 2010-05-08 07:03:42
Message-ID: 4BE50CCE.6040903@longbowgames.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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-

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Gage 2010-05-08 08:11:32 Resetting serial type after "delete from table"
Previous Message Mike Christensen 2010-05-08 04:12:46 peer-to-peer replication with Postgres