Dump/Restore ordering problem?

Поиск
Список
Период
Сортировка
Искать
От
D. Dante Lorenso
Тема
Dump/Restore ordering problem?
Дата
Msg-id
4002EA82.9000602@lorenso.com
Список
Дерево обсуждения
Dump/Restore ordering problem? "D. Dante Lorenso" <dante@lorenso.com>
Re: Dump/Restore ordering problem? Sai Hertz And Control Systems <sank89@sancharnet.in>
Re: Dump/Restore ordering problem? Tom Lane <tgl@sss.pgh.pa.us>

First I created a function that selected the next available pin
code from a table of pre-defined pin codes:

    CREATE FUNCTION "public"."get_next_pin_code" () RETURNS varchar AS'
    DECLARE
        my_pin_code VARCHAR;
    BEGIN
        ...
        /* this is the pincode we just fetched */
        RETURN (my_pin_code);
    END;
    'LANGUAGE 'plpgsql';

Then I created a table that used that function to set a default value:

    CREATE TABLE "public"."account" (
        "acct_id" BIGSERIAL,
        ...,
       "acct_pin_code" VARCHAR(16) NOT NULL DEFAULT get_next_pin_code()
    ) WITH OIDS;

But, now when I pg_dump and pg_restore this database to another server,
there seems to be a problem with the ordering of the dump in that the
account table is not recreated because the function get_next_pin_code()
is not yet defined.  It seems like the function is not being created
until AFTER the table is created and this causes an ordering problem.

To dump and restore I've been doing this:

    pg_dump -U dbuser -Ft dbname | pg_restore -c -Ft | psql -U dbuser -h 
db.otherdbhost.com dbname

I've been able to work around this by creating a TRIGGER that sets the
default value instead of defining it in the table definition, but that
just seems like a hack.  Is there something I need to do to make the
dependency ordering work smarter during a dump/restore?  Or is this the
right way to do it?

Dante



  

В списке pgsql-general по дате отправления
От: scott.marlowe
Дата:
Сообщение: Re: Case sensitivity
От: Dave Smith
Дата:
Сообщение: Vacuum Error
FAQ