Thread: pg_dump problems against 7.0
(Sorry I posted this to -hackers by accident initially)
I noticed that the function to get max builtin OID for 7.0 does this:
template1=> SELECT oid from pg_database where datname = 'template1';
oid
-------
17216
(1 row)
However, that is incorrect:
template1=> select oid,relname from pg_class where oid > 17216;
oid | relname
-------+------------
17408 | pg_indexes
17280 | pg_user
17312 | pg_rules
17344 | pg_views
17376 | pg_tables
(5 rows)
Which results in all the system views being dumped. Attached is a
patch. Please tell me if it's OK.
Chris
Index: pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.353
diff -c -r1.353 pg_dump.c
*** pg_dump.c 8 Oct 2003 03:52:32 -0000 1.353
--- pg_dump.c 20 Oct 2003 08:25:24 -0000
***************
*** 5876,5882 ****
int last_oid;
res = PQexec(g_conn,
! "SELECT oid from pg_database where datname = 'template1'");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
--- 5876,5882 ----
int last_oid;
res = PQexec(g_conn,
! "SELECT oid FROM pg_class ORDER BY oid DESC LIMIT 1");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
Index: pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.353
diff -c -r1.353 pg_dump.c
*** pg_dump.c 8 Oct 2003 03:52:32 -0000 1.353
--- pg_dump.c 20 Oct 2003 08:25:24 -0000
***************
*** 5876,5882 ****
int last_oid;
res = PQexec(g_conn,
! "SELECT oid from pg_database where datname = 'template1'");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
--- 5876,5882 ----
int last_oid;
res = PQexec(g_conn,
! "SELECT oid FROM pg_class ORDER BY oid DESC LIMIT 1");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> res = PQexec(g_conn,
> ! "SELECT oid from pg_database where datname = 'template1'");
> res = PQexec(g_conn,
> ! "SELECT oid FROM pg_class ORDER BY oid DESC LIMIT 1");
Won't that result in *everything* being considered a system table?
regards, tom lane
> Won't that result in *everything* being considered a system table?
Doh. Quite right. Well, the 7.0 initdb creates pg_indexes as the last
thing it does. (Even though there are higher oids in pg_attribute,
pg_rewrite and pg_description.), so how about the attached patch?
Chris
Index: pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.353
diff -c -r1.353 pg_dump.c
*** pg_dump.c 8 Oct 2003 03:52:32 -0000 1.353
--- pg_dump.c 21 Oct 2003 00:44:10 -0000
***************
*** 5876,5882 ****
int last_oid;
res = PQexec(g_conn,
! "SELECT oid from pg_database where datname = 'template1'");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
--- 5876,5882 ----
int last_oid;
res = PQexec(g_conn,
! "SELECT oid FROM pg_class WHERE relname='pg_indexes'");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> Won't that result in *everything* being considered a system table?
> Doh. Quite right. Well, the 7.0 initdb creates pg_indexes as the last
> thing it does. (Even though there are higher oids in pg_attribute,
> pg_rewrite and pg_description.), so how about the attached patch?
Seems reasonable, but the patch ought to fix the comment just above,
preferably with the above observation that this is correct by
examination of the now-frozen 7.0 initdb code ...
regards, tom lane
>>Doh. Quite right. Well, the 7.0 initdb creates pg_indexes as the last
>>thing it does. (Even though there are higher oids in pg_attribute,
>>pg_rewrite and pg_description.), so how about the attached patch?
>
>
> Seems reasonable, but the patch ought to fix the comment just above,
> preferably with the above observation that this is correct by
> examination of the now-frozen 7.0 initdb code ...
Attached.
Chris
Index: pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.353
diff -c -r1.353 pg_dump.c
*** pg_dump.c 8 Oct 2003 03:52:32 -0000 1.353
--- pg_dump.c 21 Oct 2003 01:17:58 -0000
***************
*** 5864,5870 ****
/*
* findLastBuiltInOid -
* find the last built in oid
! * we do this by looking up the oid of 'template1' in pg_database,
* this is probably not foolproof but comes close
*/
--- 5864,5871 ----
/*
* findLastBuiltInOid -
* find the last built in oid
! * we do this by assuming that the last thing the now-frozen 7.0.x initdb
! * does is to create the pg_indexes view.
* this is probably not foolproof but comes close
*/
***************
*** 5876,5882 ****
int last_oid;
res = PQexec(g_conn,
! "SELECT oid from pg_database where datname = 'template1'");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
--- 5877,5883 ----
int last_oid;
res = PQexec(g_conn,
! "SELECT oid FROM pg_class WHERE relname='pg_indexes'");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
Is this supposed to be applied to CVS? --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > >>Doh. Quite right. Well, the 7.0 initdb creates pg_indexes as the last > >>thing it does. (Even though there are higher oids in pg_attribute, > >>pg_rewrite and pg_description.), so how about the attached patch? > > > > > > Seems reasonable, but the patch ought to fix the comment just above, > > preferably with the above observation that this is correct by > > examination of the now-frozen 7.0 initdb code ... > > Attached. > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> Is this supposed to be applied to CVS? Well, yes. Assuming Tom doesn't have any problem with it. It's a bug in dumping 7.0 databases, which should be fixed so that people can upgrade to 7.4 with less stress :) Chris
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Christopher Kings-Lynne wrote:
> >>Doh. Quite right. Well, the 7.0 initdb creates pg_indexes as the last
> >>thing it does. (Even though there are higher oids in pg_attribute,
> >>pg_rewrite and pg_description.), so how about the attached patch?
> >
> >
> > Seems reasonable, but the patch ought to fix the comment just above,
> > preferably with the above observation that this is correct by
> > examination of the now-frozen 7.0 initdb code ...
>
> Attached.
>
> Chris
>
> Index: pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.353
> diff -c -r1.353 pg_dump.c
> *** pg_dump.c 8 Oct 2003 03:52:32 -0000 1.353
> --- pg_dump.c 21 Oct 2003 01:17:58 -0000
> ***************
> *** 5864,5870 ****
> /*
> * findLastBuiltInOid -
> * find the last built in oid
> ! * we do this by looking up the oid of 'template1' in pg_database,
> * this is probably not foolproof but comes close
> */
>
> --- 5864,5871 ----
> /*
> * findLastBuiltInOid -
> * find the last built in oid
> ! * we do this by assuming that the last thing the now-frozen 7.0.x initdb
> ! * does is to create the pg_indexes view.
> * this is probably not foolproof but comes close
> */
>
> ***************
> *** 5876,5882 ****
> int last_oid;
>
> res = PQexec(g_conn,
> ! "SELECT oid from pg_database where datname = 'template1'");
> if (res == NULL ||
> PQresultStatus(res) != PGRES_TUPLES_OK)
> {
> --- 5877,5883 ----
> int last_oid;
>
> res = PQexec(g_conn,
> ! "SELECT oid FROM pg_class WHERE relname='pg_indexes'");
> if (res == NULL ||
> PQresultStatus(res) != PGRES_TUPLES_OK)
> {
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> Is this supposed to be applied to CVS?
> Well, yes. Assuming Tom doesn't have any problem with it.
In fact, I was just about to apply it myself (had a bit of a problem
with the comment still...)
regards, tom lane
Applied by Tom. Thanks.
---------------------------------------------------------------------------
Christopher Kings-Lynne wrote:
> >>Doh. Quite right. Well, the 7.0 initdb creates pg_indexes as the last
> >>thing it does. (Even though there are higher oids in pg_attribute,
> >>pg_rewrite and pg_description.), so how about the attached patch?
> >
> >
> > Seems reasonable, but the patch ought to fix the comment just above,
> > preferably with the above observation that this is correct by
> > examination of the now-frozen 7.0 initdb code ...
>
> Attached.
>
> Chris
>
> Index: pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.353
> diff -c -r1.353 pg_dump.c
> *** pg_dump.c 8 Oct 2003 03:52:32 -0000 1.353
> --- pg_dump.c 21 Oct 2003 01:17:58 -0000
> ***************
> *** 5864,5870 ****
> /*
> * findLastBuiltInOid -
> * find the last built in oid
> ! * we do this by looking up the oid of 'template1' in pg_database,
> * this is probably not foolproof but comes close
> */
>
> --- 5864,5871 ----
> /*
> * findLastBuiltInOid -
> * find the last built in oid
> ! * we do this by assuming that the last thing the now-frozen 7.0.x initdb
> ! * does is to create the pg_indexes view.
> * this is probably not foolproof but comes close
> */
>
> ***************
> *** 5876,5882 ****
> int last_oid;
>
> res = PQexec(g_conn,
> ! "SELECT oid from pg_database where datname = 'template1'");
> if (res == NULL ||
> PQresultStatus(res) != PGRES_TUPLES_OK)
> {
> --- 5877,5883 ----
> int last_oid;
>
> res = PQexec(g_conn,
> ! "SELECT oid FROM pg_class WHERE relname='pg_indexes'");
> if (res == NULL ||
> PQresultStatus(res) != PGRES_TUPLES_OK)
> {
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073