Обсуждение: pg_dump of only the structure from a client such as ruby

On Aug 5, 2007, at 16:31 , Perry Smith wrote:

> I'm trying to help out the rails people.  Currently they have
> "rake" scripts which call pg_dump, dropdb, createdb, and psql.  It
> would be nicer if this could be done via a database connection.

Why would this be nicer? What's the advantage? pg_dump in particular
*is* a client application. It isn't called from within another
connection. As loading a schema from a file via psql, that's also
fairly common practice. Alternatives to createdb or dropdb are just
as easily handled by passing a command to psql using its -c flag.
Another point to consider is that using CREATE/DROP DATABASE instead
of the createdb/dropdb client applications requires a database
connection: if you don't have a database to connect to, you can't use
them. Of course, you could initially connect to the postgres database
(or template1 in older version), but that could require configuring
parameters for another database in database.yml. What's the advantage?

Michael Glaesemann
grzm seespotcode net

On Aug 5, 2007, at 6:07 PM, Michael Glaesemann wrote:

>
> On Aug 5, 2007, at 16:31 , Perry Smith wrote:
>
>> I'm trying to help out the rails people.  Currently they have
>> "rake" scripts which call pg_dump, dropdb, createdb, and psql.  It
>> would be nicer if this could be done via a database connection.
>
> Why would this be nicer? What's the advantage? pg_dump in
> particular *is* a client application. It isn't called from within
> another connection. As loading a schema from a file via psql,
> that's also fairly common practice. Alternatives to createdb or
> dropdb are just as easily handled by passing a command to psql
> using its -c flag. Another point to consider is that using CREATE/
> DROP DATABASE instead of the createdb/dropdb client applications
> requires a database connection: if you don't have a database to
> connect to, you can't use them. Of course, you could initially
> connect to the postgres database (or template1 in older version),
> but that could require configuring parameters for another database
> in database.yml. What's the advantage?

Connecting to the postgres database is pretty trivial.  If you want
to create foo_development, you use all config stanza for development
replace the database name with "postgres" (or "template1").  If you
want to dump foo_development, it better be there! :-)

I just submitted a patch to do create_database and drop_database to
the postgresql adapter.  I wanted to complete the other two missing
pieces.

The rake code for postgesql in this area is uglier than the mysql
code.  I mostly just wanted to clean it up.  And, one problem I have
is, often, pg_dump is not in my path.

I find it odd that you are resistant to the idea.  To me, the
advantages are clear if it can be done without a tremendous amount of
work.  Why horse around with the environment variables to set such
things as password, call sh, just to call pg_dump.

Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems

Perry Smith <pedz@easesoftware.com> writes:
> I find it odd that you are resistant to the idea.  To me, the
> advantages are clear if it can be done without a tremendous amount of
> work.

Well, it can't.  pg_dump is an enormously complicated and frequently
changed bit of code, and so you really really don't want to be copying
it into other clients.

For a long time there's been occasional talk of refactoring pg_dump into
a library and wrapper program such that the library could be used by
other clients.  But that would be a large amount of work in itself,
especially if you hoped to design a library API that was rich enough
that it'd be meaningfully more flexible than pg_dump itself is.  Aside
from the sheer work involved, there's the danger of introducing bugs
into what is certainly a critical part of our infrastructure.  So it's
never gotten further than "wouldn't it be nice" discussions.

            regards, tom lane

PS: *please* see if you can get them to take out the usage of pg_dump's
-i switch.  Having code invoke that blindly borders on criminal
negligence.

On Aug 5, 2007, at 7:37 PM, Tom Lane wrote:

> Perry Smith <pedz@easesoftware.com> writes:
>> I find it odd that you are resistant to the idea.  To me, the
>> advantages are clear if it can be done without a tremendous amount of
>> work.
>
> Well, it can't.  pg_dump is an enormously complicated and frequently
> changed bit of code, and so you really really don't want to be copying
> it into other clients.

<snip>

Thanks for the reply.

> PS: *please* see if you can get them to take out the usage of
> pg_dump's
> -i switch.  Having code invoke that blindly borders on criminal
> negligence.

I'll open a bug report and submit a patch...  Thats the most I can do.

Perry Smith ( pedz@easesoftware.com )
Ease Software, Inc. ( http://www.easesoftware.com )

Low cost SATA Disk Systems for IBMs p5, pSeries, and RS/6000 AIX systems

On Aug 5, 2007, at 18:58 , Perry Smith wrote:

> I find it odd that you are resistant to the idea.  To me, the
> advantages are clear if it can be done without a tremendous amount
> of work.  Why horse around with the environment variables to set
> such things as password, call sh, just to call pg_dump.

My resistance was to the idea that creating a connection through
ActiveRecord was preferable to a system call.

I believe that pg_dump/psql are only required if you're using
config.active_record.schema_format = :sql. (I may be mistaken, as I
exclusively use schema_format = :sql so any other possible dependents
are already satisfied.)

When using config.active_record.schema_format = :sql, pg_dump and
psql pretty much need to be in your path. And if psql is already in
your path, psql can be used with the -c flag to run arbitrary
commands (such as CREATE/DROP DATABASE and also CREATE ROLE), so
createdb and dropdb are not really necessary for most purposes. Of
course, you still need a database to connect to. Personally, I don't
want to put postgres/template1 connection information in database.yml
for security reasons: these commands are not run very often and a
great way to keep information secure is to not have it available.
Personally I wish Rails did not include database.yml by default as I
think it's much too easy to load it into an SCM repository. I use a
custom rake task to set up the Rails application to take care of this
as well as other details, including database and role creation. (This
is especially helpful for running tests.)

If you're not using config.active_record.schema_format = :sql, then
perhaps pg_dump and psql are not required, and they aren't
necessarily in your path. In that case it might make sense to use
ActiveRecord for CREATE/DROP DATABASE as you aren't necessarily going
to have psql otherwise in your path. However, in this case in my
opinion it's better for the above-mentioned security reasons not to.

Michael Glaesemann
grzm seespotcode net

On Aug 6, 2007, at 13:17 , Perry Smith wrote:

> I'm using config.active_record.schema_format = :sql.  I like the
> idea of constraints in the db -- I know that goes against a lot of
> rails ideas.

I think most who choose Postgres come to the same conclusion. Though
do take care not to confuse Rails as a whole with ActiveRecord in
particular.

> The :ruby choice does not dump and load constraints (at least not
> in the released version -- I have not verified this on edge Rails
> yet).

I doubt it does. DHH's take on "application database" (as much-
discussed elsewhere) wouldn't make such developments a priority, if
they'd even be considered for ActiveRecord.

> The pg_dump and psql load have one short coming.  I really do not
> like warning messages.  If I have a language loaded in the
> database, the psql load of the database produces two warnings
> (because I'm not loading it as postgres -- just a regular user with
> createdb privilege.
>
> I might be drifting off the original subject but, what I did to
> solve this was to hook up the create_database and drop_database and
> I have it understand the template parameter.  So, now in
> database.yml, I have a template database (like foo_template) and
> foo_test is copied from foo_template -- that avoides the error
> messages and creates a test database with whatever I need in it in
> one step.

I've considered using a similar technique for testing. There was
discussion on rails-core a few weeks ago about various migration/
testing related issues, IIRC. Haven't gotten around to it yet as my
rake tasks and the roles I use have pretty much taken care of the
issue for me.

> One thing I thought about over the past evening is that I could
> just beef up the :ruby schema dump and load to understand the
> couple of things I need for it to understand: constraints and
> functions  But, I'm not sure what kind of quagmire I'm walking in to.

Definitely not worth the effort. The :ruby schema dump is there only
to support the migration SQL DSL. In my opinion, if you're using SQL
not supported by the DSL, there's little reason to use it at all.
Most likely the SQL will not be entirely portable anyway (leaving
aside the point of whether or not that should even be a design goal)
so why take the time to learn another microlanguage? :) It doesn't
take much to have requirements beyond what the migration DSL
provides, as you've already discovered, and to extend the DSL in a
portable way would be quite an endeavor. Quagmire is a good word for it.

Michael Glaesemann
grzm seespotcode net

If you haven't seen it already, there is a rails plugin that adds
support for foreign-key dependencies (among other things) to the
migration domain specific language (DSL):

http://www.redhillonrails.org/#foreign_key_migrations

Another useful plug-in is "Transactional Migrations" which
automatically wraps ActiveRecord migrations inside a transaction, if
your database supports transactional DDL, as Postgres does. This
neatly avoids messy half-completed database migrations and the need to
clean them up by hand.


On 8/6/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
> On Aug 6, 2007, at 13:17 , Perry Smith wrote:
>
> > I'm using config.active_record.schema_format = :sql.  I like the
> > idea of constraints in the db -- I know that goes against a lot of
> > rails ideas.
>
> I think most who choose Postgres come to the same conclusion. Though
> do take care not to confuse Rails as a whole with ActiveRecord in
> particular.
>
> > The :ruby choice does not dump and load constraints (at least not
> > in the released version -- I have not verified this on edge Rails
> > yet).
>
> I doubt it does. DHH's take on "application database" (as much-
> discussed elsewhere) wouldn't make such developments a priority, if
> they'd even be considered for ActiveRecord.
>
> > The pg_dump and psql load have one short coming.  I really do not
> > like warning messages.  If I have a language loaded in the
> > database, the psql load of the database produces two warnings
> > (because I'm not loading it as postgres -- just a regular user with
> > createdb privilege.
> >
> > I might be drifting off the original subject but, what I did to
> > solve this was to hook up the create_database and drop_database and
> > I have it understand the template parameter.  So, now in
> > database.yml, I have a template database (like foo_template) and
> > foo_test is copied from foo_template -- that avoides the error
> > messages and creates a test database with whatever I need in it in
> > one step.
>
> I've considered using a similar technique for testing. There was
> discussion on rails-core a few weeks ago about various migration/
> testing related issues, IIRC. Haven't gotten around to it yet as my
> rake tasks and the roles I use have pretty much taken care of the
> issue for me.
>
> > One thing I thought about over the past evening is that I could
> > just beef up the :ruby schema dump and load to understand the
> > couple of things I need for it to understand: constraints and
> > functions  But, I'm not sure what kind of quagmire I'm walking in to.
>
> Definitely not worth the effort. The :ruby schema dump is there only
> to support the migration SQL DSL. In my opinion, if you're using SQL
> not supported by the DSL, there's little reason to use it at all.
> Most likely the SQL will not be entirely portable anyway (leaving
> aside the point of whether or not that should even be a design goal)
> so why take the time to learn another microlanguage? :) It doesn't
> take much to have requirements beyond what the migration DSL
> provides, as you've already discovered, and to extend the DSL in a
> portable way would be quite an endeavor. Quagmire is a good word for it.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>