Patch for psql 8.0, 8.1 and 8.2 backwards compatibility - Mailing list pgsql-patches

"Ugh, I started the wrong version of psql again".

This patch offers basic backwards compatibility, so a version 8.4 psql
can successfully do common operations on Postgres 8.0, 8.1, 8.2 and
8.3.  I expect it's incomplete support, but as of yet I can't find an
actual problem.  To me it is a step forward regardless, as it fixes "\d"
which is pretty crucial even when just popping to an old server to check
on something before an upgrade.

Notes:
1) Patched against cvs as of 19 May 2008.
2) The exact version numbers for the tweaks need help from a guru.
3) The usual version mismatch warning remains, as this is not a promise
of full compatibility.

             Bryce Nesbitt


PS: See also:
http://archives.postgresql.org/pgsql-bugs/2005-08/msg00306.php
http://unixadmintalk.com/f47/improved-df-psql-backward-compatibility-345293/
http://archives.postgresql.org/pgsql-sql/2008-02/msg00088.php

? psql
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.173
diff -c -2 -r1.173 describe.c
*** describe.c    13 May 2008 00:23:17 -0000    1.173
--- describe.c    20 May 2008 05:16:33 -0000
***************
*** 804,810 ****
--- 804,818 ----
      bool        show_modifiers = false;
      bool        retval;
+     char       *indisvalid, *tgconstraint;   // Backwards compatibility differences

      retval = false;

+     /*
+     ** Adjust queries for basic backwards compatibility
+     ** Should allow use with Postgres 8.0, 8.1, 8.2 servers.
+     */
+     indisvalid      = pset.sversion > 82000 ? "i.indisvalid" : "true";
+     tgconstraint    = pset.sversion > 81000 ? "AND t.tgconstraint = 0\n" : "";
+
      /* This output looks confusing in expanded mode. */
      myopt.expanded = false;
***************
*** 985,994 ****

          printfPQExpBuffer(&buf,
!                           "SELECT i.indisunique, i.indisprimary, i.indisclustered, i.indisvalid, a.amname,
c2.relname,\n"
!                     "  pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
!                           "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_ama\n" 
!           "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
!                           "AND i.indrelid = c2.oid",
!                           oid);

          result = PSQLexec(buf.data, false);
--- 993,1002 ----

          printfPQExpBuffer(&buf,
!             "SELECT i.indisunique, i.indisprimary, i.indisclustered, %s , a.amname, c2.relname,\n"
!             "  pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
!             "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
!             "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
!             "AND i.indrelid = c2.oid",
!              indisvalid, oid);

          result = PSQLexec(buf.data, false);
***************
*** 1087,1096 ****
          {
              printfPQExpBuffer(&buf,
!                               "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, "
!                               "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace\n"
!                               "FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
!                               "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
!               "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
!                               oid);
              result = PSQLexec(buf.data, false);
              if (!result)
--- 1095,1104 ----
          {
              printfPQExpBuffer(&buf,
!                 "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, %s, "
!                 " pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace\n"
!                 "FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
!                 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
!                 "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
!                  indisvalid,oid);
              result = PSQLexec(buf.data, false);
              if (!result)
***************
*** 1340,1347 ****
                                "t.tgenabled\n"
                                "FROM pg_catalog.pg_trigger t\n"
!                               "WHERE t.tgrelid = '%s' "
!                               "AND t.tgconstraint = 0\n"
                                "ORDER BY 1",
!                               oid);
              result = PSQLexec(buf.data, false);
              if (!result)
--- 1348,1354 ----
                                "t.tgenabled\n"
                                "FROM pg_catalog.pg_trigger t\n"
!                               "WHERE t.tgrelid = '%s' %s"
                                "ORDER BY 1",
!                               oid,tgconstraint);
              result = PSQLexec(buf.data, false);
              if (!result)