Thread: pg_dump --clean w/ <= 7.2 server
When running pg_dump --clean against a server that doesn't have schemas the namespace is blank and ends up producing a dump full off things like: DROP TABLE "".tab; The attached patch only includes a schema if one exists. There are numerous comments about the DROPs needing to be fully qualified to avoid conflicting with pg_catalog, but this should be mostly safe because it would require a user defined object to be present in a later server version with the same name, and it is difficult to impossible to drop system objects anyway. Kris Jurka
Attachment
> When running pg_dump --clean against a server that doesn't have schemas > the namespace is blank and ends up producing a dump full off things like: > > DROP TABLE "".tab; Since the person is dumping using 7.5 pg_dump, presumably they will be restoring to 7.5, and it should be: DROP TABLE "public".tab; Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> When running pg_dump --clean against a server that doesn't have schemas
>> the namespace is blank and ends up producing a dump full off things like:
>>
>> DROP TABLE "".tab;
> Since the person is dumping using 7.5 pg_dump, presumably they will be
> restoring to 7.5, and it should be:
> DROP TABLE "public".tab;
Possibly the most correct solution is to assign the name "public" to the
dummy schema that pg_dump creates internally when talking to a pre-7.3
server.
I seem to recall that there was some reason for using "", but I don't
recall what exactly.
regards, tom lane
On Thu, 24 Jun 2004, Tom Lane wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > >> When running pg_dump --clean against a server that doesn't have schemas > >> the namespace is blank and ends up producing a dump full off things like: > >> > >> DROP TABLE "".tab; > > > Since the person is dumping using 7.5 pg_dump, presumably they will be > > restoring to 7.5, and it should be: > > > DROP TABLE "public".tab; > > Possibly the most correct solution is to assign the name "public" to the > dummy schema that pg_dump creates internally when talking to a pre-7.3 > server. I was considering that they might want to restore the dump into another schema and that would be easier with an unqualified name. I don't really understand why the name needs to be fully qualified in the first place. > I seem to recall that there was some reason for using "", but I don't > recall what exactly. > It seems like the only possible reasons are deliberately making it fail or just a lack of testing. There's no way it does anything useful. Kris Jurka
Kris Jurka <books@ejurka.com> writes:
>> Possibly the most correct solution is to assign the name "public" to the
>> dummy schema that pg_dump creates internally when talking to a pre-7.3
>> server.
> I was considering that they might want to restore the dump into another
> schema and that would be easier with an unqualified name. I don't really
> understand why the name needs to be fully qualified in the first place.
Because it's entirely too likely that you'll drop the wrong thing if you
issue an unqualified DROP.
regards, tom lane
Where are we on this? --------------------------------------------------------------------------- Tom Lane wrote: > Kris Jurka <books@ejurka.com> writes: > >> Possibly the most correct solution is to assign the name "public" to the > >> dummy schema that pg_dump creates internally when talking to a pre-7.3 > >> server. > > > I was considering that they might want to restore the dump into another > > schema and that would be easier with an unqualified name. I don't really > > understand why the name needs to be fully qualified in the first place. > > Because it's entirely too likely that you'll drop the wrong thing if you > issue an unqualified DROP. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Where are we on this? --------------------------------------------------------------------------- Kris Jurka wrote: > > When running pg_dump --clean against a server that doesn't have schemas > the namespace is blank and ends up producing a dump full off things like: > > DROP TABLE "".tab; > > The attached patch only includes a schema if one exists. There are > numerous comments about the DROPs needing to be fully qualified to avoid > conflicting with pg_catalog, but this should be mostly safe because it > would require a user defined object to be present in a later server > version with the same name, and it is difficult to impossible to drop > system objects anyway. > > Kris Jurka Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
The last patch of mine you committed just made it all DROP TABLE public.tab; That at least makes it work. Chris Bruce Momjian wrote: > Where are we on this? > > --------------------------------------------------------------------------- > > Kris Jurka wrote: > >>When running pg_dump --clean against a server that doesn't have schemas >>the namespace is blank and ends up producing a dump full off things like: >> >>DROP TABLE "".tab; >> >>The attached patch only includes a schema if one exists. There are >>numerous comments about the DROPs needing to be fully qualified to avoid >>conflicting with pg_catalog, but this should be mostly safe because it >>would require a user defined object to be present in a later server >>version with the same name, and it is difficult to impossible to drop >>system objects anyway.