pg_dump selectively ignores extension configuration tables
(reposting" apparently I used a verboten word the first
time around <sigh>. Sorry for any duplicates)
The -t and -n options of pg_dump do not dump anything from an extension
configuration table, whereas normal pg_dump will dump the user data.
To see what I mean, in psql do (tested on pg9.2):
8<--------------------------
create extension postgis;
insert into spatial_ref_sys
values(42,'test',42,'foo','bar');
8<--------------------------
Then in bash do:
8<--------------------------
pg_dump test|grep spatial_ref_sys
pg_dump -t spatial_ref_sys test|grep spatial_ref_sys
pg_dump -n public test|grep spatial_ref_sys
8<--------------------------
Is this intentional, or oversight, or missing feature?
Thanks,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 8, 2013 at 5:07 PM, Joe Conway <mail@joeconway.com> wrote:
(reposting" apparently I used a verboten word the first
time around <sigh>. Sorry for any duplicates)The -t and -n options of pg_dump do not dump anything from an extension
configuration table, whereas normal pg_dump will dump the user data.To see what I mean, in psql do (tested on pg9.2):
8<--------------------------
create extension postgis;
insert into spatial_ref_sys
values(42,'test',42,'foo','bar');
8<--------------------------Then in bash do:
8<--------------------------
pg_dump test|grep spatial_ref_sys
pg_dump -t spatial_ref_sys test|grep spatial_ref_sys
pg_dump -n public test|grep spatial_ref_sys
8<--------------------------Is this intentional, or oversight, or missing feature?
Hmm. It doesn't seem right to me. It seems like it should either
dump everything, or dump just the user data portion, when the name
matches. Not entirely sure which - probably the latter?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 03/13/2013 03:07 PM, Robert Haas wrote:
Is this intentional, or oversight, or missing feature?
Hmm. It doesn't seem right to me. It seems like it should either
dump everything, or dump just the user data portion, when the name
matches. Not entirely sure which - probably the latter?
+1
I think it should dump the user data portion, especially since that
matches what pg_dump would do if you did not specify the table or schema.
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Joe Conway <mail@joeconway.com> writes:
I think it should dump the user data portion, especially since that
matches what pg_dump would do if you did not specify the table or schema.
+1
If you don't have time slots to fix that by then, I will have a look at
fixing that while in beta.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 03/13/2013 04:16 PM, Dimitri Fontaine wrote:
Joe Conway <mail@joeconway.com> writes:
I think it should dump the user data portion, especially since that
matches what pg_dump would do if you did not specify the table or schema.+1
If you don't have time slots to fix that by then, I will have a look at
fixing that while in beta.
Here is a patch against 9.1. If there is agreement with the approach
I'll redo for 9.2 and git head and apply.
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
Attachments:
pg_dump_ext_obj.difftext/x-patch; name=pg_dump_ext_obj.diffDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 964823f..4e0b45c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -13883,10 +13883,6 @@ getExtensionMembership(ExtensionInfo extinfo[], int numExtensions)
int nconfigitems;
int nconditionitems;
- /* Tables of not-to-be-dumped extensions shouldn't be dumped */
- if (!curext->dobj.dump)
- continue;
-
if (parsePGArray(extconfig, &extconfigarray, &nconfigitems) &&
parsePGArray(extcondition, &extconditionarray, &nconditionitems) &&
nconfigitems == nconditionitems)
@@ -13896,18 +13892,51 @@ getExtensionMembership(ExtensionInfo extinfo[], int numExtensions)
for (j = 0; j < nconfigitems; j++)
{
TableInfo *configtbl;
+ Oid configtbloid = atooid(extconfigarray[j]);
+ bool dumpobj = curext->dobj.dump;
- configtbl = findTableByOid(atooid(extconfigarray[j]));
+ configtbl = findTableByOid(configtbloid);
if (configtbl && configtbl->dataObj == NULL)
{
/*
- * Note: config tables are dumped without OIDs regardless
- * of the --oids setting. This is because row filtering
- * conditions aren't compatible with dumping OIDs.
+ * Tables of not-to-be-dumped extensions shouldn't be dumped
+ * unless the table or its schema is explicitly included
*/
- makeTableDataInfo(configtbl, false);
- if (strlen(extconditionarray[j]) > 0)
- configtbl->dataObj->filtercond = strdup(extconditionarray[j]);
+ if (!curext->dobj.dump)
+ {
+ /* check table explicitly requested */
+ if (table_include_oids.head != NULL &&
+ simple_oid_list_member(&table_include_oids,
+ configtbloid))
+ dumpobj = true;
+
+ /* check table's schema explicitly requested */
+ if (configtbl->dobj.namespace->dobj.dump)
+ dumpobj = true;
+ }
+
+ /* check table excluded by an exclusion switch */
+ if (table_exclude_oids.head != NULL &&
+ simple_oid_list_member(&table_exclude_oids,
+ configtbloid))
+ dumpobj = false;
+
+ /* check schema excluded by an exclusion switch */
+ if (simple_oid_list_member(&schema_exclude_oids,
+ configtbl->dobj.namespace->dobj.catId.oid))
+ dumpobj = false;
+
+ if (dumpobj)
+ {
+ /*
+ * Note: config tables are dumped without OIDs regardless
+ * of the --oids setting. This is because row filtering
+ * conditions aren't compatible with dumping OIDs.
+ */
+ makeTableDataInfo(configtbl, false);
+ if (strlen(extconditionarray[j]) > 0)
+ configtbl->dataObj->filtercond = strdup(extconditionarray[j]);
+ }
}
}
}
On 03/14/2013 05:23 PM, Joe Conway wrote:
On 03/13/2013 04:16 PM, Dimitri Fontaine wrote:
Joe Conway <mail@joeconway.com> writes:
I think it should dump the user data portion, especially since that
matches what pg_dump would do if you did not specify the table or schema.+1
If you don't have time slots to fix that by then, I will have a look at
fixing that while in beta.Here is a patch against 9.1. If there is agreement with the approach
I'll redo for 9.2 and git head and apply.
Any objections before I commit this?
Thanks,
Joe
--
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
Attachments:
pg_dump_ext_obj-9.3.difftext/x-patch; name=pg_dump_ext_obj-9.3.diffDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 093be9e..069762f 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getExtensionMembership(Archive *fout, Ex
*** 14486,14495 ****
int nconfigitems;
int nconditionitems;
- /* Tables of not-to-be-dumped extensions shouldn't be dumped */
- if (!curext->dobj.dump)
- continue;
-
if (parsePGArray(extconfig, &extconfigarray, &nconfigitems) &&
parsePGArray(extcondition, &extconditionarray, &nconditionitems) &&
nconfigitems == nconditionitems)
--- 14486,14491 ----
*************** getExtensionMembership(Archive *fout, Ex
*** 14499,14519 ****
for (j = 0; j < nconfigitems; j++)
{
TableInfo *configtbl;
! configtbl = findTableByOid(atooid(extconfigarray[j]));
if (configtbl == NULL)
continue;
/*
! * Note: config tables are dumped without OIDs regardless of
! * the --oids setting. This is because row filtering
! * conditions aren't compatible with dumping OIDs.
*/
! makeTableDataInfo(configtbl, false);
! if (configtbl->dataObj != NULL)
{
! if (strlen(extconditionarray[j]) > 0)
! configtbl->dataObj->filtercond = pg_strdup(extconditionarray[j]);
}
}
}
--- 14495,14548 ----
for (j = 0; j < nconfigitems; j++)
{
TableInfo *configtbl;
+ Oid configtbloid = atooid(extconfigarray[j]);
+ bool dumpobj = curext->dobj.dump;
! configtbl = findTableByOid(configtbloid);
if (configtbl == NULL)
continue;
/*
! * Tables of not-to-be-dumped extensions shouldn't be dumped
! * unless the table or its schema is explicitly included
*/
! if (!curext->dobj.dump)
{
! /* check table explicitly requested */
! if (table_include_oids.head != NULL &&
! simple_oid_list_member(&table_include_oids,
! configtbloid))
! dumpobj = true;
!
! /* check table's schema explicitly requested */
! if (configtbl->dobj.namespace->dobj.dump)
! dumpobj = true;
! }
!
! /* check table excluded by an exclusion switch */
! if (table_exclude_oids.head != NULL &&
! simple_oid_list_member(&table_exclude_oids,
! configtbloid))
! dumpobj = false;
!
! /* check schema excluded by an exclusion switch */
! if (simple_oid_list_member(&schema_exclude_oids,
! configtbl->dobj.namespace->dobj.catId.oid))
! dumpobj = false;
!
! if (dumpobj)
! {
! /*
! * Note: config tables are dumped without OIDs regardless of
! * the --oids setting. This is because row filtering
! * conditions aren't compatible with dumping OIDs.
! */
! makeTableDataInfo(configtbl, false);
! if (configtbl->dataObj != NULL)
! {
! if (strlen(extconditionarray[j]) > 0)
! configtbl->dataObj->filtercond = pg_strdup(extconditionarray[j]);
! }
}
}
}
On Mar 25, 2013, at 9:56 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/14/2013 05:23 PM, Joe Conway wrote:
On 03/13/2013 04:16 PM, Dimitri Fontaine wrote:
Joe Conway <mail@joeconway.com> writes:
I think it should dump the user data portion, especially since that
matches what pg_dump would do if you did not specify the table or schema.+1
If you don't have time slots to fix that by then, I will have a look at
fixing that while in beta.Here is a patch against 9.1. If there is agreement with the approach
I'll redo for 9.2 and git head and apply.Any objections before I commit this?
Since, nobody has picked this one.
If there is no objection,then I can test this patch against 9.1 & 9.2.
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Blog:http://vibhork.blogspot.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Vibhor Kumar escribió:
On Mar 25, 2013, at 9:56 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/14/2013 05:23 PM, Joe Conway wrote:
On 03/13/2013 04:16 PM, Dimitri Fontaine wrote:
Joe Conway <mail@joeconway.com> writes:
I think it should dump the user data portion, especially since that
matches what pg_dump would do if you did not specify the table or schema.+1
If you don't have time slots to fix that by then, I will have a look at
fixing that while in beta.Here is a patch against 9.1. If there is agreement with the approach
I'll redo for 9.2 and git head and apply.Any objections before I commit this?
Since, nobody has picked this one.
If there is no objection,then I can test this patch against 9.1 & 9.2.
Thanks, yes, that would be helpful. Things to think about are whether
this affect anything other than tables marked as config for any
extension, and whether behavior is sane for them, (i.e. the "condition"
thingy works right etc).
The whole matter of extension configuration table has been rather
tricky to get right ... hopefully we're not ending up with them being
more broken now.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 03/25/2013 08:12 AM, Vibhor Kumar wrote:
Since, nobody has picked this one.
If there is no objection,then I can test this patch against 9.1 & 9.2.
Here are diffs for 9.1 and 9.2. The previous email was against 9.3 dev.
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
Attachments:
pg_dump_ext_obj-9.1.difftext/x-patch; name=pg_dump_ext_obj-9.1.diffDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 964823f..4e0b45c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -13883,10 +13883,6 @@ getExtensionMembership(ExtensionInfo extinfo[], int numExtensions)
int nconfigitems;
int nconditionitems;
- /* Tables of not-to-be-dumped extensions shouldn't be dumped */
- if (!curext->dobj.dump)
- continue;
-
if (parsePGArray(extconfig, &extconfigarray, &nconfigitems) &&
parsePGArray(extcondition, &extconditionarray, &nconditionitems) &&
nconfigitems == nconditionitems)
@@ -13896,18 +13892,51 @@ getExtensionMembership(ExtensionInfo extinfo[], int numExtensions)
for (j = 0; j < nconfigitems; j++)
{
TableInfo *configtbl;
+ Oid configtbloid = atooid(extconfigarray[j]);
+ bool dumpobj = curext->dobj.dump;
- configtbl = findTableByOid(atooid(extconfigarray[j]));
+ configtbl = findTableByOid(configtbloid);
if (configtbl && configtbl->dataObj == NULL)
{
/*
- * Note: config tables are dumped without OIDs regardless
- * of the --oids setting. This is because row filtering
- * conditions aren't compatible with dumping OIDs.
+ * Tables of not-to-be-dumped extensions shouldn't be dumped
+ * unless the table or its schema is explicitly included
*/
- makeTableDataInfo(configtbl, false);
- if (strlen(extconditionarray[j]) > 0)
- configtbl->dataObj->filtercond = strdup(extconditionarray[j]);
+ if (!curext->dobj.dump)
+ {
+ /* check table explicitly requested */
+ if (table_include_oids.head != NULL &&
+ simple_oid_list_member(&table_include_oids,
+ configtbloid))
+ dumpobj = true;
+
+ /* check table's schema explicitly requested */
+ if (configtbl->dobj.namespace->dobj.dump)
+ dumpobj = true;
+ }
+
+ /* check table excluded by an exclusion switch */
+ if (table_exclude_oids.head != NULL &&
+ simple_oid_list_member(&table_exclude_oids,
+ configtbloid))
+ dumpobj = false;
+
+ /* check schema excluded by an exclusion switch */
+ if (simple_oid_list_member(&schema_exclude_oids,
+ configtbl->dobj.namespace->dobj.catId.oid))
+ dumpobj = false;
+
+ if (dumpobj)
+ {
+ /*
+ * Note: config tables are dumped without OIDs regardless
+ * of the --oids setting. This is because row filtering
+ * conditions aren't compatible with dumping OIDs.
+ */
+ makeTableDataInfo(configtbl, false);
+ if (strlen(extconditionarray[j]) > 0)
+ configtbl->dataObj->filtercond = strdup(extconditionarray[j]);
+ }
}
}
}
pg_dump_ext_obj-9.2.difftext/x-patch; name=pg_dump_ext_obj-9.2.diffDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 1fe9d75..9f6fd2e 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getExtensionMembership(Archive *fout, Ex
*** 14022,14031 ****
int nconfigitems;
int nconditionitems;
- /* Tables of not-to-be-dumped extensions shouldn't be dumped */
- if (!curext->dobj.dump)
- continue;
-
if (parsePGArray(extconfig, &extconfigarray, &nconfigitems) &&
parsePGArray(extcondition, &extconditionarray, &nconditionitems) &&
nconfigitems == nconditionitems)
--- 14022,14027 ----
*************** getExtensionMembership(Archive *fout, Ex
*** 14035,14055 ****
for (j = 0; j < nconfigitems; j++)
{
TableInfo *configtbl;
! configtbl = findTableByOid(atooid(extconfigarray[j]));
if (configtbl == NULL)
continue;
/*
! * Note: config tables are dumped without OIDs regardless of
! * the --oids setting. This is because row filtering
! * conditions aren't compatible with dumping OIDs.
*/
! makeTableDataInfo(configtbl, false);
! if (configtbl->dataObj != NULL)
{
! if (strlen(extconditionarray[j]) > 0)
! configtbl->dataObj->filtercond = pg_strdup(extconditionarray[j]);
}
}
}
--- 14031,14084 ----
for (j = 0; j < nconfigitems; j++)
{
TableInfo *configtbl;
+ Oid configtbloid = atooid(extconfigarray[j]);
+ bool dumpobj = curext->dobj.dump;
! configtbl = findTableByOid(configtbloid);
if (configtbl == NULL)
continue;
/*
! * Tables of not-to-be-dumped extensions shouldn't be dumped
! * unless the table or its schema is explicitly included
*/
! if (!curext->dobj.dump)
{
! /* check table explicitly requested */
! if (table_include_oids.head != NULL &&
! simple_oid_list_member(&table_include_oids,
! configtbloid))
! dumpobj = true;
!
! /* check table's schema explicitly requested */
! if (configtbl->dobj.namespace->dobj.dump)
! dumpobj = true;
! }
!
! /* check table excluded by an exclusion switch */
! if (table_exclude_oids.head != NULL &&
! simple_oid_list_member(&table_exclude_oids,
! configtbloid))
! dumpobj = false;
!
! /* check schema excluded by an exclusion switch */
! if (simple_oid_list_member(&schema_exclude_oids,
! configtbl->dobj.namespace->dobj.catId.oid))
! dumpobj = false;
!
! if (dumpobj)
! {
! /*
! * Note: config tables are dumped without OIDs regardless of
! * the --oids setting. This is because row filtering
! * conditions aren't compatible with dumping OIDs.
! */
! makeTableDataInfo(configtbl, false);
! if (configtbl->dataObj != NULL)
! {
! if (strlen(extconditionarray[j]) > 0)
! configtbl->dataObj->filtercond = pg_strdup(extconditionarray[j]);
! }
}
}
}
On Mar 25, 2013, at 10:17 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Vibhor Kumar escribió:
On Mar 25, 2013, at 9:56 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/14/2013 05:23 PM, Joe Conway wrote:
On 03/13/2013 04:16 PM, Dimitri Fontaine wrote:
Joe Conway <mail@joeconway.com> writes:
I think it should dump the user data portion, especially since that
matches what pg_dump would do if you did not specify the table or schema.+1
If you don't have time slots to fix that by then, I will have a look at
fixing that while in beta.Here is a patch against 9.1. If there is agreement with the approach
I'll redo for 9.2 and git head and apply.Any objections before I commit this?
Since, nobody has picked this one.
If there is no objection,then I can test this patch against 9.1 & 9.2.
Thanks, yes, that would be helpful. Things to think about are whether
this affect anything other than tables marked as config for any
extension, and whether behavior is sane for them, (i.e. the "condition"
thingy works right etc).
Sure, I will test and verify this.
The whole matter of extension configuration table has been rather
tricky to get right ... hopefully we're not ending up with them being
more broken now.
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Blog:http://vibhork.blogspot.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mar 25, 2013, at 10:48 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/25/2013 08:12 AM, Vibhor Kumar wrote:
Since, nobody has picked this one.
If there is no objection,then I can test this patch against 9.1 & 9.2.
Here are diffs for 9.1 and 9.2. The previous email was against 9.3 dev.
Thanks Joe!
will test both for 9.1 and 9.2
-
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Blog:http://vibhork.blogspot.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mar 25, 2013, at 12:01 PM, Vibhor Kumar <vibhor.kumar@enterprisedb.com> wrote:
On Mar 25, 2013, at 10:48 AM, Joe Conway <mail@joeconway.com> wrote:
On 03/25/2013 08:12 AM, Vibhor Kumar wrote:
Since, nobody has picked this one.
If there is no objection,then I can test this patch against 9.1 & 9.2.
Here are diffs for 9.1 and 9.2. The previous email was against 9.3 dev.
Thanks Joe!
will test both for 9.1 and 9.2
I did some testing on this patch with 9.1 and 9.2 source code. Testing included following:
1. Configured PostGIS with 9.1 and 9.2
2. verified all switches of pg_dump with regression db.
3. Checked other extensions, to verify if this impacting those.
Everything is working as expected and I haven't found any issue during my test with this patch.
While testing this patch, some thoughts came in my mind and thought to share on this thread.
Is it possible, if we can have two switches for extension in pg_dump:
1. extension dump with user data in extension tables.
2. User data-only dump from extensions.
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Blog:http://vibhork.blogspot.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 04/04/2013 01:03 PM, Vibhor Kumar wrote:
I did some testing on this patch with 9.1 and 9.2 source code. Testing included following:
1. Configured PostGIS with 9.1 and 9.2
2. verified all switches of pg_dump with regression db.
3. Checked other extensions, to verify if this impacting those.Everything is working as expected and I haven't found any issue during my test with this patch.
Thanks Vibhor. Any other comments or concerns before I commit this?
While testing this patch, some thoughts came in my mind and thought to share on this thread.
Is it possible, if we can have two switches for extension in pg_dump:
1. extension dump with user data in extension tables.
2. User data-only dump from extensions.
This might be worth considering for 9.4
Thanks,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers