Thread: pgrestore, notice, will create implicit.

pgrestore, notice, will create implicit.

From
"Marcel Franke"
Date:
Hello,

I am trying to restore a database with pg_restore,
(psql 7.4.6 on Linux 2.6.9-5.ELsmp (RedHat 4))
but I do not understand what the specific -notice- messages mean
that the pg_restore produces:

pg_restore -O -Fc -d DataBaseOmr -v </var/tmp/DataBaseOmr.cdump

pg_restore: connecting to database for restore
pg_restore: creating FUNC PROCEDURAL LANGUAGE plpgsql_call_handler()
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: creating ACL public
pg_restore: creating TABLE omroepevent
pg_restore: creating ACL omroepevent
pg_restore: creating FUNCTION funcomroepevent0()
pg_restore: creating TABLE omroepbericht
pg_restore: NOTICE:  CREATE TABLE will create implicit sequence
"omroepbericht_omroepberichtid_seq" for "serial" column
"omroepbericht.omroepberichtid"
pg_restore: creating ACL omroepbericht
pg_restore: creating ACL omroepbericht_omroepberichtid_seq
pg_restore: restoring data for table "omroepevent"
pg_restore: restoring data for table "omroepbericht"
pg_restore: restoring data for table "BLOBS"
pg_restore: restored 0 large objects
pg_restore: creating INDEX omroepbericht_receivedlocal_idx
pg_restore: creating CONSTRAINT omroepbericht_pkey
pg_restore: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit
index "omroepbericht_pkey" for table "omroepbericht"
pg_restore: creating TRIGGER triggeromroepevent0
pg_restore: executing SEQUENCE SET omroepbericht_omroepberichtid_seq
pg_restore: creating COMMENT SCHEMA public

Can someone please tell me what
"will create implicit sequence"
and
"will create implicit index"
means ?

Thanks,
Marcel.

Re: pgrestore, notice, will create implicit.

From
Sean Davis
Date:


On 6/1/06 7:57 AM, "Marcel Franke" <Marcel.Franke@omroep.nl> wrote:

> Hello,
>
> I am trying to restore a database with pg_restore,
> (psql 7.4.6 on Linux 2.6.9-5.ELsmp (RedHat 4))
> but I do not understand what the specific -notice- messages mean
> that the pg_restore produces:

> Can someone please tell me what
> "will create implicit sequence"
> and
> "will create implicit index"
> means ?

Your schema definition probably contains a "serial primary key".  The
"serial" part uses a sequence to do the auto-incremented values, so postgres
needs to create that sequence (which is "implied" by the "serial"
definition).  A primary key needs to have an associated index, so by asking
for a primary key, postgres needs to create the index (a primary key
"implies" the need for an index).

Hope that helps.

Sean


Re: pgrestore, notice, will create implicit.

From
"Marcel Franke"
Date:
Sean:

>Your schema definition probably contains a "serial primary key".
>The "serial" part uses a sequence to do the auto-incremented values, so
postgres needs to create that sequence
>A primary key needs to have an associated index, so by asking for a
primary key, postgres needs to create the index

Thank you very much.

So, if I understand correctly,
the serial primary keys and its index are recreated.

Which means that the value of the primary keys in the restored database
may not be the same
as the value of the primary keys in the original database ?

Kind regards,
Marcel.

Re: pgrestore, notice, will create implicit.

From
Sean Davis
Date:


On 6/1/06 8:38 AM, "Marcel Franke" <Marcel.Franke@omroep.nl> wrote:

>
> Sean:
>
>> Your schema definition probably contains a "serial primary key".
>> The "serial" part uses a sequence to do the auto-incremented values, so
> postgres needs to create that sequence
>> A primary key needs to have an associated index, so by asking for a
> primary key, postgres needs to create the index
>
> Thank you very much.
>
> So, if I understand correctly,
> the serial primary keys and its index are recreated.
>
> Which means that the value of the primary keys in the restored database
> may not be the same
> as the value of the primary keys in the original database ?

No.  The "serial" column only gets an auto-increment value if no value is
specified.  For a restore, those values will be present and used.  Also, the
sequence (from which any new auto-increment values will be drawn) will be
set to the value from the previous database, so new values will start in the
same place.  So, you don't have to put any thought into it--postgres is
doing the right thing here.

Sean


Re: pgrestore, notice, will create implicit.

From
"Marcel Franke"
Date:
Sean:
>The "serial" column only gets an auto-increment value if no value is
specified.
>For a restore, those values will be present and used.
>So, you don't have to put any thought into it--postgres is doing the
right thing here.

Cool.
Thanks !

Marcel.