Re: [PATCHES] [HACKERS] Show INHERIT in \du

[Brendan Jurd]

I've done up a patch per Tom's idea of combining the binary role
attributes into a single column.

Each attribute which differs from the default is listed on a separate
line, like so:

  List of roles
  Role name  |   Attributes   | Member of
-++---
 bob || {readers,writers}
 brendanjurd | Superuser  | {}
 : Create role
 : Create DB
 harry   | No inherit | {}
 jim | 10 connections | {readers}
 readers | No login   | {}
 writers | No login   | {}
(6 rows)

Notes:

 * The patch relies on array_to_string's current treatment of NULL
values in the array; they are ignored.  If that behaviour changes in
the future, the \du output will become very ugly indeed.
 * I'm not sure whether No login and No inherit are the best
phrases to use.  I took my cue from the SQL setting names NOLOGIN and
NOINHERIT, but maybe something more grammatically sensible with
Cannot login and No inheritance would be preferable.
 * If accepted, this patch would supercede the earlier patch mentioned
by Bernd Helmle upthread, which adds LOGIN to the output as a new
column: http://archives.postgresql.org/pgsql-patches/2007-11/msg00014.php

Cheers,
BJ
*** src/bin/psql/describe.c
--- src/bin/psql/describe.c
***
*** 1611,1638  describeRoles(const char *pattern, bool verbose)
PQExpBufferData buf;
PGresult   *res;
printQueryOpt myopt = pset.popt;
!   static const bool trans_columns[] = {false, true, true, true, true, 
false, false};
  
initPQExpBuffer(buf);
  
printfPQExpBuffer(buf,
  SELECT r.rolname AS \%s\,\n
! CASE WHEN r.rolsuper THEN '%s' ELSE '%s' END 
AS \%s\,\n
!CASE WHEN r.rolcreaterole THEN '%s' ELSE '%s' END AS 
\%s\,\n
!  CASE WHEN r.rolcreatedb THEN '%s' ELSE '%s' END AS 
\%s\,\n
! CASE WHEN r.rolconnlimit  0 THEN CAST('%s' AS 
pg_catalog.text)\n
!ELSE CAST(r.rolconnlimit AS 
pg_catalog.text)\n
!   END AS \%s\, \n
ARRAY(SELECT b.rolname FROM 
pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) 
WHERE m.member = r.oid) as \%s\,
  gettext_noop(Role name),
- gettext_noop(yes), 
gettext_noop(no),
  gettext_noop(Superuser),
! gettext_noop(yes), 
gettext_noop(no),
  gettext_noop(Create role),
- gettext_noop(yes), 
gettext_noop(no),
  gettext_noop(Create DB),
! gettext_noop(no limit),
! gettext_noop(Connections),
  gettext_noop(Member of));
  
if (verbose)
--- 1611,1639 
PQExpBufferData buf;
PGresult   *res;
printQueryOpt myopt = pset.popt;
!   static const bool trans_columns[] = {false, true, false, false};
  
initPQExpBuffer(buf);
  
printfPQExpBuffer(buf,
  SELECT r.rolname AS \%s\,\n
!   array_to_string(ARRAY[\n
!   CASE WHEN r.rolsuper THEN '%s' ELSE NULL 
END,\n
! CASE WHEN NOT r.rolinherit THEN '%s' ELSE NULL END,\n
!  CASE WHEN r.rolcreaterole THEN '%s' ELSE NULL END,\n
!CASE WHEN r.rolcreatedb THEN '%s' ELSE NULL 
END,\n
!CASE WHEN NOT r.rolcanlogin THEN '%s' ELSE NULL END,\n
!  CASE WHEN r.rolconnlimit = 0 THEN 
r.rolconnlimit::pg_catalog.text || ' %s' ELSE NULL END\n
!   ], chr(10)) AS \%s\,\n
ARRAY(SELECT b.rolname FROM 
pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) 
WHERE m.member = r.oid) as \%s\,
  gettext_noop(Role name),
  gettext_noop(Superuser),
! gettext_noop(No inherit),
  gettext_noop(Create role),
  gettext_noop(Create DB),
! gettext_noop(No login),
! gettext_noop(connections),
! gettext_noop(Attributes),
  gettext_noop(Member of));
  
if (verbose)

---(end of 

[PATCHES] Proposed patch to change TOAST compression strategy

[Tom Lane]

This proposed patch addresses some issues in TOAST compression strategy that
were discussed last year, but we felt it was too late in the 8.3 cycle to
change the code immediately.  See these threads for background:
http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php
http://archives.postgresql.org/pgsql-general/2007-08/msg01129.php

Specifically, the patch:

* Reduces the minimum datum size to be considered for compression from
256 to 32 bytes, as suggested by Greg Stark.  (Greg later suggested
dropping the threshold clear to zero, but this is pointless since we
must save enough bytes to pay for the longer header.)

* Increases the required compression rate for compressed storage from
20% to 25%, again per Greg's suggestion.

* Replaces force_input_size (size above which compression is forced)
with a maximum size to be considered for compression.  It was agreed
that allowing large inputs to escape the minimum-compression-rate
requirement was not bright, and that indeed we'd rather have a knob
that acted in the other direction.  I set this value to 1MB for the
moment, but it could use some performance studies to tune it.

* Adds an early-failure path to the compressor as suggested by Jan:
if it's been unable to find even one compressible substring in the
first 1KB (parameterizable), assume we're looking at incompressible
input and give up.  (There are various ways this could be done, but
this way seems to add the least overhead to the compressor's inner
loop.)

* Improves the toasting heuristics so that when we have very large
fields with attstorage 'x' or 'e', we will push those out to toast
storage before considering inline compression of shorter fields.
This also responds to a suggestion of Greg's, though my original
proposal for a solution was a bit off base because it didn't fix
the problem for large 'e' fields.

There was some discussion in the earlier threads of exposing some
of the compression knobs to users, perhaps even on a per-column
basis.  I have not done anything about that here.  It seems to me
that if we are changing around the parameters, we'd better get some
experience and be sure we are happy with the design before we set
things in stone by providing user-visible knobs.

I have not done any performance testing of these changes --- does
anyone have specific test scenarios they'd like to try?

regards, tom lane



bin5xGxvAMBU7.bin
Description: toast-strategy.patch

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly