Lists: | pgsql-hackers |
---|
From: | Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | pg_restore --single-transaction and --clean |
Date: | 2010-02-10 04:48:55 |
Message-ID: | 20100210134854.4801.52131E4D@oss.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
As another glitch in pg_restore, a combination of options
--single-transaction and --clean raises errors if we restore data
into an empty database. The reason is pg_restore uses DROP <OBJECT>.
The cleanup command fails if the target object doesn't exist.
Is it a TODO item to replace "DROP" into "DROP IF EXISTS"
for cleanup commands in pg_restore?
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: pg_restore --single-transaction and --clean |
Date: | 2010-02-10 15:16:15 |
Message-ID: | 16241.1265814975@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> Is it a TODO item to replace "DROP" into "DROP IF EXISTS"
> for cleanup commands in pg_restore?
No. We try to avoid using nonstandard SQL in dumps.
regards, tom lane
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: pg_restore --single-transaction and --clean |
Date: | 2010-02-10 16:17:22 |
Message-ID: | 603c8f071002100817u7a624b86p289f02e9d0389bf4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Wed, Feb 10, 2010 at 10:16 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
>> Is it a TODO item to replace "DROP" into "DROP IF EXISTS"
>> for cleanup commands in pg_restore?
>
> No. We try to avoid using nonstandard SQL in dumps.
How often do we succeed? It seems unlikely that our dumps would be
restorable into any other database.
...Robert
From: | "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> |
---|---|
To: | "Robert Haas" <robertmhaas(at)gmail(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Takahiro Itagaki" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: pg_restore --single-transaction and --clean |
Date: | 2010-02-10 16:27:15 |
Message-ID: | 4B728A03020000250002F132@gw.wicourts.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> We try to avoid using nonstandard SQL in dumps.
>
> How often do we succeed? It seems unlikely that our dumps would
> be restorable into any other database.
When we were running in a mixed environment we had several occasions
where it was useful to feed pg_dump --column-inserts output into
Sybase databases. It was very nice to have that. I think we did
sometimes have to filter it through sed to deal with BOOLEAN vs BIT
issues.
-Kevin
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: pg_restore --single-transaction and --clean |
Date: | 2010-02-10 16:30:30 |
Message-ID: | 20100210163030.GG4922@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Kevin Grittner escribió:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> >> We try to avoid using nonstandard SQL in dumps.
> >
> > How often do we succeed? It seems unlikely that our dumps would
> > be restorable into any other database.
>
> When we were running in a mixed environment we had several occasions
> where it was useful to feed pg_dump --column-inserts output into
> Sybase databases. It was very nice to have that. I think we did
> sometimes have to filter it through sed to deal with BOOLEAN vs BIT
> issues.
Maybe we should have a --compatible-mode or some such that enables these
things, instead of staying away from useful PG-only features.
The problem would then be how to test it ...
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Robert Haas <robertmhaas(at)gmail(dot)com>, Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: pg_restore --single-transaction and --clean |
Date: | 2010-02-10 16:43:53 |
Message-ID: | 17434.1265820233@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Kevin Grittner escribi:
>> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> We try to avoid using nonstandard SQL in dumps.
>>> How often do we succeed? It seems unlikely that our dumps would
>>> be restorable into any other database.
>> When we were running in a mixed environment we had several occasions
>> where it was useful to feed pg_dump --column-inserts output into
>> Sybase databases. It was very nice to have that. I think we did
>> sometimes have to filter it through sed to deal with BOOLEAN vs BIT
>> issues.
> Maybe we should have a --compatible-mode or some such that enables these
> things, instead of staying away from useful PG-only features.
Well, the subtext of my comment was really that this case isn't useful
enough to justify introducing a nonstandard construct into dumps.
IMO the whole *point* of --single-transaction is to fail if the database
isn't in the state you thought it was. If you want to restore into an
empty DB with --single-transaction, don't use --clean. Problem solved.
--clean has got other issues anyway with a DB that isn't in exactly the
expected state. If the inter-object dependencies aren't quite what they
were in the source, drops are likely to fail because dependent objects
still remain. Should we therefore make all pg_dump's drop commands
CASCADE? I don't think so; the side-effects could be nasty.
regards, tom lane
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Robert Haas <robertmhaas(at)gmail(dot)com>, Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: pg_restore --single-transaction and --clean |
Date: | 2010-02-10 16:56:00 |
Message-ID: | 20100210165600.GI4922@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Tom Lane escribió:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Kevin Grittner escribi:
> >> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>>> We try to avoid using nonstandard SQL in dumps.
>
> >>> How often do we succeed? It seems unlikely that our dumps would
> >>> be restorable into any other database.
>
> >> When we were running in a mixed environment we had several occasions
> >> where it was useful to feed pg_dump --column-inserts output into
> >> Sybase databases. It was very nice to have that. I think we did
> >> sometimes have to filter it through sed to deal with BOOLEAN vs BIT
> >> issues.
>
> > Maybe we should have a --compatible-mode or some such that enables these
> > things, instead of staying away from useful PG-only features.
>
> Well, the subtext of my comment was really that this case isn't useful
> enough to justify introducing a nonstandard construct into dumps.
That's true, but this is not the first time we've left out some feature
from dumps because they would make them standards-incompatible. If we
have enough of these (and I have no idea that we do), maybe we could
start here. This is of course just a future TODO item, not something to
consider for 9.0.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.