Rod,

> I think what we want is a clean template without all of the extras that
> template1 has.

We have this, it's called Template0.

Actually, KL, that would solve a lot of these duplicate object problems.  What 
if pg_restore used Template0 and not Template1?    It wouldn't fix the "drop 
public schema" issue but it would solve the others.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Mon, 19 Jul 2004 09:36:17 -0700
Josh Berkus <josh@agliodbs.com>
Re: pg_dump bug fixing
On Mon, 2004-07-19 at 12:36, Josh Berkus wrote:
> Rod,
> 
> > I think what we want is a clean template without all of the extras that
> > template1 has.
> 
> We have this, it's called Template0.

Doesn't work for me. I remove a number of things that are included by
default in template0, but yes, it's close.

> Actually, KL, that would solve a lot of these duplicate object problems.  What 
> if pg_restore used Template0 and not Template1?    It wouldn't fix the "drop 
> public schema" issue but it would solve the others.

Remove the public schema from template0, but leave it in template1. Have
pg_dump treat the public schema the same as all of the other ones.



Mon, 19 Jul 2004 13:01:43 -0400
Rod Taylor <pg@rbt.ca>
Re: pg_dump bug fixing
Rod,

> Remove the public schema from template0, but leave it in template1. Have
> pg_dump treat the public schema the same as all of the other ones.

Hmmm.  No good; it wipes out the primary purpose of Template0, which is to 
restore a corrupted Template1.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Mon, 19 Jul 2004 10:30:21 -0700
Josh Berkus <josh@agliodbs.com>
Re: pg_dump bug fixing
Rod Taylor <pg@rbt.ca> writes:
> Remove the public schema from template0, but leave it in template1.

Does not sound very workable.  One of the functions of template0 is to
be a backup for the virgin state of template1, and you'd lose that.

			regards, tom lane

Mon, 19 Jul 2004 13:30:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump bug fixing 
On Mon, 2004-07-19 at 13:30, Josh Berkus wrote:
> Rod,
> 
> > Remove the public schema from template0, but leave it in template1. Have
> > pg_dump treat the public schema the same as all of the other ones.
> 
> Hmmm.  No good; it wipes out the primary purpose of Template0, which is to 
> restore a corrupted Template1.

Yeah.. This is why my first suggestion was to create a new template for
this purpose -- of which most responses told me about template0.


Mon, 19 Jul 2004 14:23:24 -0400
Rod Taylor <pg@rbt.ca>
Re: pg_dump bug fixing
> Actually, KL, that would solve a lot of these duplicate object problems.  What 
> if pg_restore used Template0 and not Template1?    It wouldn't fix the "drop 
> public schema" issue but it would solve the others.

Not sure what you mean here, but CVS pg_dump dumps like this:

CREATE DATABASE phppgadmin WITH TEMPLATE = template0 OWNER = chriskl 
ENCODING = 'LATIN1';
CREATE DATABASE usa WITH TEMPLATE = template0 OWNER = usadmin ENCODING = 
'LATIN1';

Chris


Tue, 20 Jul 2004 10:13:50 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
In article <1090256502.414.17.camel@jester>,
Rod Taylor <pg@rbt.ca> writes:

> On Mon, 2004-07-19 at 12:36, Josh Berkus wrote:
>> Rod,
>> 
>> > I think what we want is a clean template without all of the extras that
>> > template1 has.
>> 
>> We have this, it's called Template0.

> Doesn't work for me. I remove a number of things that are included by
> default in template0, but yes, it's close.

I think pg_dump should do a kind of "diff" between template1 and the
database in question and include the necessary statements in the dump
to allow pg_restore to "replay" the diff after it has created the
database from template1.


20 Jul 2004 11:57:01 +0200
Harald Fuchs <hf517@protecting.net>
Re: pg_dump bug fixing
KL,

> CREATE DATABASE phppgadmin WITH TEMPLATE = template0 OWNER = chriskl
> ENCODING = 'LATIN1';
> CREATE DATABASE usa WITH TEMPLATE = template0 OWNER = usadmin ENCODING =
> 'LATIN1';

Ok, so that would come under the heading of "already fixed".  Great.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 20 Jul 2004 09:54:37 -0700
Josh Berkus <josh@agliodbs.com>
Re: pg_dump bug fixing
Chris,

I've just found a bit of undesirable functionality which I would call a bug in 
pg_dump.   I'm not sure everyone would, but we'll see.

Problem:  the script which dumps globals such as users (pg_dumpall -g) 
involves deleting *all* users from the pg_shadow table via a direct update to 
that table.   What this means in effect is that, should you attempt to use 
"pg_dumpall -g" to *move* a set of users from one active server to another 
(such as for transferring a database) the resulting pg_dump file will delete 
all of the users which previously existed on that server.

This is a non-trivial accident to have happen on a shared machine; once users 
are dumped, all of their ownerships and permissions go with them.   If you 
have a complex permissions system, better hope you backed up first!

I find this behavior highly undesirable, and consider it a bug.    The globals 
dump should just add users, and not delete any.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Mon, 2 Aug 2004 14:23:19 -0700
Josh Berkus <josh@agliodbs.com>
Re: pg_dump bug fixing
Josh Berkus <josh@agliodbs.com> writes:
> Problem:  the script which dumps globals such as users (pg_dumpall -g) 
> involves deleting *all* users from the pg_shadow table via a direct update to
> that table.

AFAICS that happens only if you've specified the -c (--clean) option.
Hence, I don't think it's a bug.

			regards, tom lane

Mon, 02 Aug 2004 17:39:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump bug fixing 
> 
> This is a non-trivial accident to have happen on a shared machine; once users 
> are dumped, all of their ownerships and permissions go with them.   If you 
> have a complex permissions system, better hope you backed up first!
> 
> I find this behavior highly undesirable, and consider it a bug.    The globals 
> dump should just add users, and not delete any.

Unless the --clean option is passed, yes I agree with you. The other 
issue is that it is silly to have to use pg_dumpall to get the globals. 
A person should be able to pull a pg_dump on a particular database and 
get everything that is required to run that database. Including users.

Joshua D. Drake


> 


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Mon, 02 Aug 2004 15:05:26 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: pg_dump bug fixing
Tom,

> AFAICS that happens only if you've specified the -c (--clean) option.
> Hence, I don't think it's a bug.

Nope, happens even if you don't pass --clean.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Mon, 2 Aug 2004 15:12:37 -0700
Josh Berkus <josh@agliodbs.com>
Re: pg_dump bug fixing
On Mon, 2 Aug 2004, Tom Lane wrote:

> Josh Berkus <josh@agliodbs.com> writes:
>> Problem:  the script which dumps globals such as users (pg_dumpall -g)
>> involves deleting *all* users from the pg_shadow table via a direct update to
>> that table.
>
> AFAICS that happens only if you've specified the -c (--clean) option.
> Hence, I don't think it's a bug.

Is --clean a new option?  Cause I've had the same thing happen to us also 
... thank god for backups :)

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Mon, 2 Aug 2004 19:38:45 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: pg_dump bug fixing 
Josh Berkus <josh@agliodbs.com> writes:
>> AFAICS that happens only if you've specified the -c (--clean) option.
>> Hence, I don't think it's a bug.

> Nope, happens even if you don't pass --clean.

Not in CVS tip ... but you're right, older versions did act that way.
Looks like someone addressed this already.

			regards, tom lane

Mon, 02 Aug 2004 19:31:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump bug fixing 
> I've just found a bit of undesirable functionality which I would call a bug in 
> pg_dump.   I'm not sure everyone would, but we'll see.
> 
> Problem:  the script which dumps globals such as users (pg_dumpall -g) 
> involves deleting *all* users from the pg_shadow table via a direct update to 
> that table.   What this means in effect is that, should you attempt to use 
> "pg_dumpall -g" to *move* a set of users from one active server to another 
> (such as for transferring a database) the resulting pg_dump file will delete 
> all of the users which previously existed on that server.
> 
> This is a non-trivial accident to have happen on a shared machine; once users 
> are dumped, all of their ownerships and permissions go with them.   If you 
> have a complex permissions system, better hope you backed up first!
> 
> I find this behavior highly undesirable, and consider it a bug.    The globals 
> dump should just add users, and not delete any.

Yeah, it's nasty.  One of the fixes that's already in from me is to make 
the DELETE FROM pg_shadow and DELETE FROM pg_group only appear when -c 
mode is set.

Maybe even when -c mode is set we should use DROP USER commands?

Do others agree?

Chris


Tue, 03 Aug 2004 10:00:41 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
>>>AFAICS that happens only if you've specified the -c (--clean) option.
>>>Hence, I don't think it's a bug.
> 
> 
>>Nope, happens even if you don't pass --clean.
> 
> 
> Not in CVS tip ... but you're right, older versions did act that way.
> Looks like someone addressed this already.

Yeah, was one of my fixes.

Should we change it to use DROP USER commands anyway?

Chris


Tue, 03 Aug 2004 10:04:39 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
>> I find this behavior highly undesirable, and consider it a bug.    The 
>> globals dump should just add users, and not delete any.
> 
> 
> Unless the --clean option is passed, yes I agree with you. The other 
> issue is that it is silly to have to use pg_dumpall to get the globals. 
> A person should be able to pull a pg_dump on a particular database and 
> get everything that is required to run that database. Including users.

Another reason to combine pg_dumpall into pg_dump...

Chris


Tue, 03 Aug 2004 10:13:59 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
Chris,

> Another reason to combine pg_dumpall into pg_dump...

No argument here.   Are you thinking of that?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Mon, 2 Aug 2004 20:05:31 -0700
Josh Berkus <josh@agliodbs.com>
Re: pg_dump bug fixing
>>Another reason to combine pg_dumpall into pg_dump...
> 
> No argument here.   Are you thinking of that?

Yeah.  Would be a bit of work though.

Chris


Tue, 03 Aug 2004 11:17:28 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
At 01:17 PM 3/08/2004, Christopher Kings-Lynne wrote:
>Would be a bit of work though.

I've been looking at this for a while now, and will probably give it a go 
for 7.6/8.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 


Tue, 03 Aug 2004 13:40:00 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump bug fixing
> I've been looking at this for a while now, and will probably give it a 
> go for 7.6/8.

Let me know when you do, I'd be interested in collaborating.

Chris


Tue, 03 Aug 2004 12:00:40 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing
At 02:00 PM 3/08/2004, Christopher Kings-Lynne wrote:
>I'd be interested in collaborating.

Sounds good.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 


Tue, 03 Aug 2004 14:05:53 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump bug fixing
Christopher Kings-Lynne wrote:

>> I've been looking at this for a while now, and will probably give it 
>> a go for 7.6/8.
>
>
> Let me know when you do, I'd be interested in collaborating.
>
Command Prompt, if would help could help sponsor this project.

Sincerely,

Joshua D. Drake



> Chris



-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL


Mon, 02 Aug 2004 21:37:09 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: pg_dump bug fixing
Chris,

> I was thinking a pg_export utility that can output to a range of other
> databases SQL formats would also be a good idea.  It would share about
> 90% of the pg_dump code, but I'm trying to think of how to avoid
> duplicating the code.

I'm not really keen on this idea unless you're eager to make a 5-year 
commitment to maintain the code.   The load formats of other RDBMSes change 
all the time -- MySQL is a particularly egregious example, with 2 
incompatible changes in the last year -- and it would become a pain to keep 
track.   

More to the point, there are a number of 3rd-party OSS and proprietary 
utilities which can do this kind of format conversion.   For example, Perl 
DB::Interpolator will cover PG, MySQL, Oracle and MSSQL once that 
functionality is out of beta.

I can see, though, having a --strict-sql switch for pg_dump which would dump 
all database objects in strict SQL92, which should be pretty compatible with 
other systems.   This should also be easier to implement and trivial to 
maintain.  Though it would mean not dumping functions and doing a few data 
type conversions.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 3 Aug 2004 08:29:39 -0700
Josh Berkus <josh@agliodbs.com>
Re: pg_dump bug fixing
> I'm not really keen on this idea unless you're eager to make a 5-year 
> commitment to maintain the code.   The load formats of other RDBMSes change 
> all the time -- MySQL is a particularly egregious example, with 2 
> incompatible changes in the last year -- and it would become a pain to keep 
> track.   

Well, I could do it on pgfoundry, but it would really suck to have to 
dupe all the pg_dump code.  Maybe I will have to.

> More to the point, there are a number of 3rd-party OSS and proprietary 
> utilities which can do this kind of format conversion.   For example, Perl 
> DB::Interpolator will cover PG, MySQL, Oracle and MSSQL once that 
> functionality is out of beta.

Do they convert the sql dumps or dump from the backend?  I really, 
really want to make a mysql2pgsql converter that doesn't really on text 
file parsing.  Modifying mysqldump would be easiest, but the problem is 
licensing I think...

> I can see, though, having a --strict-sql switch for pg_dump which would dump 
> all database objects in strict SQL92, which should be pretty compatible with 
> other systems.   This should also be easier to implement and trivial to 
> maintain.  Though it would mean not dumping functions and doing a few data 
> type conversions.

Yeah, perhaps.  And issuing a log of warnings so you can see what 
information you've lost.

Chris


Wed, 04 Aug 2004 09:50:36 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump bug fixing