Re: [PATCHES] Error in ORDER BY on check constraints in psql

[Tom Lane]

Christopher Kings-Lynne [EMAIL PROTECTED] writes:
 Everything is sorted by object name in \d table except check 
 constraints for some reason.  It seems it's ordering by the wrong column.
 Seems like a bug to me.

That was probably done deliberately, back in the day when constraints
tended to have uselessly random names like $1 --- sorting by the
constraint text was more helpful.  I agree that now sorting by name
seems like the better thing.

 Attached is the trivial patch.

I think there's nothing wrong with the ORDER BY 1 part ... it's the
fact that the columns are selected in a different order than they'll
be used that seems bizarre to me.  I fixed it like this instead.

regards, tom lane

*** src/bin/psql/describe.c.origFri Oct 14 23:00:19 2005
--- src/bin/psql/describe.c Thu Oct 20 01:09:29 2005
***
*** 1036,1044 
if (tableinfo.checks)
{
printfPQExpBuffer(buf,
! SELECT 
! 
pg_catalog.pg_get_constraintdef(r.oid, true), 
! conname\n
  FROM 
pg_catalog.pg_constraint r\n
WHERE r.conrelid = '%s' AND r.contype 
= 'c' ORDER BY 1,
  oid);
--- 1036,1043 
if (tableinfo.checks)
{
printfPQExpBuffer(buf,
! SELECT r.conname, 
! 
pg_catalog.pg_get_constraintdef(r.oid, true)\n
  FROM 
pg_catalog.pg_constraint r\n
WHERE r.conrelid = '%s' AND r.contype 
= 'c' ORDER BY 1,
  oid);
***
*** 1192,1199 
for (i = 0; i  check_count; i++)
{
printfPQExpBuffer(buf, _(\%s\ %s),
! 
PQgetvalue(result2, i, 1),
! 
PQgetvalue(result2, i, 0));
  
footers[count_footers++] = pg_strdup(buf.data);
}
--- 1191,1198 
for (i = 0; i  check_count; i++)
{
printfPQExpBuffer(buf, _(\%s\ %s),
! 
PQgetvalue(result2, i, 0),
! 
PQgetvalue(result2, i, 1));
  
footers[count_footers++] = pg_strdup(buf.data);
}

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PATCHES] Error in ORDER BY on check constraints in psql

[Christopher Kings-Lynne]

That was probably done deliberately, back in the day when constraints
tended to have uselessly random names like $1 --- sorting by the
constraint text was more helpful.  I agree that now sorting by name
seems like the better thing.


Even in the $x case, it's better to have them sorted in that order 
(ie. the order they were created...)



I think there's nothing wrong with the ORDER BY 1 part ... it's the
fact that the columns are selected in a different order than they'll
be used that seems bizarre to me.  I fixed it like this instead.


Ah, the way that requires effort :)


---(end of broadcast)---
TIP 6: explain analyze is your friend