Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump: relation "..." already exists warning

pg_dump: relation "..." already exists warning

From
Ennio-Sr
Date:
Hi all,
[Using postgres (PostgreSQL) 7.4.7 on GNU/Linux/Debian-Sarge]

running the command:
$ pg_dump finanz -t ult_qq | psql finanza ennio ult.qq

I can see that table 'ult_qq' is regularly created in 'finanza' and
continues to exist in 'finanz', although pg is firing this warning:

SET
SET
SET
SET
ERROR:  relation "ult_qq" already exists
SET
ERROR:  relation "ult_qq" already exists
SET
SET

which I'm unable to explain. Could it be related to unresolved bug #1379
or am I doing anything wrong?

Thanks for your attention.
Regards,
    Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.   \\?//
 Fa' qualche cosa di cui non sei capace!"  (diceva Henry Miller) ]    (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that.              )=(
 Do something you aren't good at!" (as Henry Miller used to say) ]

Re: pg_dump: relation "..." already exists warning

From
Tom Lane
Date:
Ennio-Sr <nasr.laili@tin.it> writes:
> running the command:
> $ pg_dump finanz -t ult_qq | psql finanza ennio ult.qq

> I can see that table 'ult_qq' is regularly created in 'finanza' and
> continues to exist in 'finanz', although pg is firing this warning:

> SET
> SET
> SET
> SET
> ERROR:  relation "ult_qq" already exists
> SET
> ERROR:  relation "ult_qq" already exists
> SET
> SET

> which I'm unable to explain.

It seems fairly easily explainable to me: the table already exists.
Perhaps because you already restored into this destination database?

            regards, tom lane

Re: pg_dump: relation "..." already exists warning

From
Ennio-Sr
Date:
* Tom Lane <tgl@sss.pgh.pa.us> [170905, 18:05]:
> Ennio-Sr <nasr.laili@tin.it> writes:
> > running the command:
> > $ pg_dump finanz -t ult_qq | psql finanza ennio ult.qq
>
> > I can see that table 'ult_qq' is regularly created in 'finanza' and
> > continues to exist in 'finanz', although pg is firing this warning:
>
> > SET
> > SET
> > SET
> > SET
> > ERROR:  relation "ult_qq" already exists
> > SET
> > ERROR:  relation "ult_qq" already exists
> > SET
> > SET
>
> > which I'm unable to explain.
>
> It seems fairly easily explainable to me: the table already exists.
> Perhaps because you already restored into this destination database?
>
>             regards, tom lane

Well, AAMOF, that command is in a script and follows a 'drop table
ult_qq' instruction:
-----------
# ...... as the table 'ult_qq' was created during a previous running of
# the script,
# we first delete it:
psql finanza -c "drop table ult_qq;"

# psql finanza -c "vacuum full analyze ult_qq;" ## doesn't help !
# and then dump it to the other db (finanza):
pg_dump finanz -t ult_qq | psql finanza ennio ult_qq    [***]

# So that, in 'finanza', we can update our 'dep_tit' table:
psql finanza -c "update dep_tit set ultima_quot=ult_qq.pr_chius, \
data_ult_q=ult_qq.quot_del where dep_tit.cod_tit=ult_qq.cod_tit;"
# .......
------------
[***] I copied this instruction somewhere from: it works, but I don't
know exactly whether, and if yes where, the dump is saved in 'finanz'
also.
May be the warning refers to the presence of 'ult_qq' in finanz? Or,
perhaps, the dropping is still running when the dump is started?

Let me stress again that despite the warning everything is working
smoothly and a _new_ 'ult_qq' is copied to finanza on any subsequent
script running.

Thanks for your time, Tom!
Regards,
    Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.   \\?//
 Fa' qualche cosa di cui non sei capace!"  (diceva Henry Miller) ]    (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that.              )=(
 Do something you aren't good at!" (as Henry Miller used to say) ]

Re: pg_dump: relation "..." already exists warning

From
Tom Lane
Date:
Ennio-Sr <nasr.laili@tin.it> writes:
> ...
> pg_dump finanz -t ult_qq | psql finanza ennio ult_qq    [***]
> ...
> [***] I copied this instruction somewhere from: it works,

... other than the warning it emits telling you that you've got
the psql command line syntax all wrong.  What other warnings and
error messages are you blithely ignoring?  I think you've omitted
the information needed to figure out what's wrong here.

Please post a cut-and-paste of a terminal window session showing this
sequence, not an edited transcript showing only what you think is
important.

BTW, one fairly likely guess about the problem is that you have
different schema search paths in the two databases --- so checking
the ALTER DATABASE defaults for each database wouldn't be a bad
idea.

            regards, tom lane

Re: pg_dump: relation "..." already exists warning

From
Ennio-Sr
Date:
* Tom Lane <tgl@sss.pgh.pa.us> [170905, 23:45]:
> Ennio-Sr <nasr.laili@tin.it> writes:
> > ...
> > pg_dump finanz -t ult_qq | psql finanza ennio ult_qq    [***]
> > ...
> > [***] I copied this instruction somewhere from: it works,
>
> ... other than the warning it emits telling you that you've got
> the psql command line syntax all wrong.  What other warnings and
> error messages are you blithely ignoring?  I think you've omitted
> the information needed to figure out what's wrong here.
>
> Please post a cut-and-paste of a terminal window session showing this
> sequence, not an edited transcript showing only what you think is
> important.
>
> BTW, one fairly likely guess about the problem is that you have
> different schema search paths in the two databases --- so checking
> the ALTER DATABASE defaults for each database wouldn't be a bad
> idea.
>
>             regards, tom lane

OK, reading Doc Manuals (pg 8), III,22.1.1 ('Restoring the dump') -
which I should have done before ... :-) ) I realize that command is
intented for dumping a db directly from one _server_ to another, whereas I
was using it to copy a table in _db_ n.1 to _db_ no.2, in which case the
correct line would be:

pg_dump finanz -t ult_qq > foo | psql finanza ennio < foo

which doesn't emit any warning. May be there is a better way to achieve
my goal (i.e.: using a few values of db n.1 table from within db n.2):
perhaps SCHEMA, but I couldn't understand how this works on my first
reading.

Back to your answer, I'm not sure I understand correctly what you're
requiriring me to do (in particular, I found no instructions on how to
check the ALTER DATABASE defaults right now).
The lines I posted in my original message are not 'an edited transcript'
though the result of piping to foo whatever was produced by the command
( psql finanz ...... > foo 2&>1).

BUT, wait a minute ...
After a few tests (while writing this) I discovered the error came out
because there was a schema which I forgot to drop when I realized I
couldn't understand how it was supposed to work ;-)

Having dropped the schema, both commands work with no error :-)

Sorry for wasting your time.
Regards,
    Ennio.

PS: Could a valid schema allow me to utilize a table in a db from
another db?

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.   \\?//
 Fa' qualche cosa di cui non sei capace!"  (diceva Henry Miller) ]    (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that.              )=(
 Do something you aren't good at!" (as Henry Miller used to say) ]