Thread: [postgis-users] pg_dump -s should use add_geometrycolumn(...)
Hi,
pg_dump -s should use add_geometrycolumn(...) instead of creating a column+constraints with normal DDL
Because, when you don't dump the data, then the record in geometry_columns is lost.
Cheers,
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
pg_dump -s should use add_geometrycolumn(...) instead of creating a column+constraints with normal DDL
Because, when you don't dump the data, then the record in geometry_columns is lost.
Cheers,
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote: > Hi, > > pg_dump -s should use add_geometrycolumn(...) instead of creating a > column+constraints with normal DDL > Because, when you don't dump the data, then the record in geometry_columns > is lost. That shouldn't be a problem with postgres 2 anymore as far as I understand things? Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Aug 29, 2012 at 5:23 PM, Andres Freund <andres@2ndquadrant.com> wrote:
That shouldn't be a problem with postgres 2 anymore as far as I understand
things?
Why?
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
On Wednesday, August 29, 2012 05:41:07 PM Willy-Bas Loos wrote: > On Wed, Aug 29, 2012 at 5:23 PM, Andres Freund <andres@2ndquadrant.com>wrote: > > That shouldn't be a problem with postgres 2 anymore as far as I > > understand things? Argh, postgis 2. Two things: * the geometry_columns table is not a table anymore but a view of the postgres catalogs * extensions (9.1+, used by postgis 2 onwards) can declare configuration tables now, so even if you would still need a configuration table it would get dumped in a schema only dump Also, adding postgis support into pg_dump would have been problematic given that pg_dump is part of core postgres and postgis isnt... Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote:
>> pg_dump -s should use add_geometrycolumn(...) instead of creating a
>> column+constraints with normal DDL
>> Because, when you don't dump the data, then the record in geometry_columns
>> is lost.
> That shouldn't be a problem with postgres 2 anymore as far as I understand
> things?
The big picture here is that pg_dump doesn't, and is not likely ever to,
know about anything as extension-specific as add_geometrycolumn().
Rather, if postgis needs some info in a special table geometry_columns,
that should be handled by pg_dump dumping and restoring that table too
(since, again, there is no reason for pg_dump to treat such a table
specially).
Willy hasn't provided enough context for us to know why that approach
might not be working for him, though a first guess is that he tried to
do a selective dump excluding geometry_columns.
I believe there was some discussion awhile back about whether postgis
could dodge this problem by cramming its additional info into type
modifiers (with the geometry-type typmodin and typmodout functions
becoming responsible for accessing the special table). I don't know
if that can fly on syntax grounds, but if it can, I think all the core
infrastructure for it is in place as of 9.1.
regards, tom lane
On Wednesday, August 29, 2012 06:02:24 PM Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote: > >> pg_dump -s should use add_geometrycolumn(...) instead of creating a > >> column+constraints with normal DDL > >> Because, when you don't dump the data, then the record in > >> geometry_columns is lost. > > > > That shouldn't be a problem with postgres 2 anymore as far as I > > understand things? > > The big picture here is that pg_dump doesn't, and is not likely ever to, > know about anything as extension-specific as add_geometrycolumn(). > Rather, if postgis needs some info in a special table geometry_columns, > that should be handled by pg_dump dumping and restoring that table too > (since, again, there is no reason for pg_dump to treat such a table > specially). > > Willy hasn't provided enough context for us to know why that approach > might not be working for him, though a first guess is that he tried to > do a selective dump excluding geometry_columns. > > I believe there was some discussion awhile back about whether postgis > could dodge this problem by cramming its additional info into type > modifiers (with the geometry-type typmodin and typmodout functions > becoming responsible for accessing the special table). I don't know > if that can fly on syntax grounds, but if it can, I think all the core > infrastructure for it is in place as of 9.1. As far as I know, and thats what I tried to refer to, that's done as of postgis 2.0. Yep: "PostgreSQL typmod integration, for an automagical geometry_columns table" Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Aug 29, 2012 at 5:46 PM, Andres Freund <andres@2ndquadrant.com> wrote:
Two things:
* the geometry_columns table is not a table anymore but a view of the postgres
catalogs
Great! I didn't know that yet. It must derive the info from the constraints then.
So that means you can just create the column with standard DDL, and there is no more redundant info. Nice :)
So that means you can just create the column with standard DDL, and there is no more redundant info. Nice :)
* extensions (9.1+, used by postgis 2 onwards) can declare configuration
tables now, so even if you would still need a configuration table it would get
dumped in a schema only dump
That's really cool, advanced stuff. It's interesting that the functions still reside in public, but people probably did spend a few thoughts on that.
(why not a separate catalog with all the functions and types etc? hmz maybe because you'd need to change the search_path, which is in postgresql.conf)
I'll look into the typemod stuff further.
(why not a separate catalog with all the functions and types etc? hmz maybe because you'd need to change the search_path, which is in postgresql.conf)
I'll look into the typemod stuff further.
On Wed, Aug 29, 2012 at 6:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>if postgis needs some info in a special table geometry_columns,
>that should be handled by pg_dump dumping and restoring that table too
Well, pg_dump -s doesn't dump the data, so it would then be missing. But it is actually not data, but functional configuration. That's why creating config tables for extensions (that do get dumped with a schema dump) is awesome stuff! (even if postgis doesn't even use it now)
Thanks for your answers.
Cheers,
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
On 08/30/2012 04:40 PM, Willy-Bas Loos wrote: > (why not a separate catalog with all the functions and types etc? hmz > maybe because you'd need to change the search_path, which is in > postgresql.conf) You can choose which schema an extension goes into when you CREATE EXTENSION. See the documentation. You don't have to set search_path in postgresql.conf ; it can be set per-session with `SET search_path` and can also be set per-user, per-database and per-function. -- Craig Ringer