Hello hackers,

In the pg_stat_activity view, the usesysid is shown as having type Oid.
However pg_shadow says it's an integer.  Is there a reason? Looks like
a bug.

This patch seems to corrects this issue, but I don't know if there's
something else involved.

Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.276
diff -c -r1.276 pg_proc.h
*** src/include/catalog/pg_proc.h	2002/11/08 17:27:03	1.276
--- src/include/catalog/pg_proc.h	2002/11/16 23:18:44
***************
*** 2738,2744 ****
  DESCR("Statistics: PID of backend");
  DATA(insert OID = 1938 (  pg_stat_get_backend_dbid		PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_dbid - _null_ ));
  DESCR("Statistics: Database ID of backend");
! DATA(insert OID = 1939 (  pg_stat_get_backend_userid	PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_userid - _null_ ));
  DESCR("Statistics: User ID of backend");
  DATA(insert OID = 1940 (  pg_stat_get_backend_activity	PGNSP PGUID 12 f f t f s 1 25 "23"	pg_stat_get_backend_activity - _null_ ));
  DESCR("Statistics: Current query of backend");
--- 2738,2744 ----
  DESCR("Statistics: PID of backend");
  DATA(insert OID = 1938 (  pg_stat_get_backend_dbid		PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_dbid - _null_ ));
  DESCR("Statistics: Database ID of backend");
! DATA(insert OID = 1939 (  pg_stat_get_backend_userid	PGNSP PGUID 12 f f t f s 1 23 "23"	pg_stat_get_backend_userid - _null_ ));
  DESCR("Statistics: User ID of backend");
  DATA(insert OID = 1940 (  pg_stat_get_backend_activity	PGNSP PGUID 12 f f t f s 1 25 "23"	pg_stat_get_backend_activity - _null_ ));
  DESCR("Statistics: Current query of backend");
Index: src/backend/utils/adt/pgstatfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/pgstatfuncs.c,v
retrieving revision 1.8
diff -c -r1.8 pgstatfuncs.c
*** src/backend/utils/adt/pgstatfuncs.c	2002/08/20 04:47:52	1.8
--- src/backend/utils/adt/pgstatfuncs.c	2002/11/16 23:18:44
***************
*** 272,278 ****
  	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
  		PG_RETURN_NULL();
  
! 	PG_RETURN_OID(beentry->userid);
  }
  
  
--- 272,278 ----
  	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
  		PG_RETURN_NULL();
  
! 	PG_RETURN_INT32(beentry->userid);
  }
  
  
-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El miedo atento y previsor es la madre de la seguridad" (E. Burke)

Sun, 17 Nov 2002 11:07:41 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
pg_stat_database shows userid as OID
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> In the pg_stat_activity view, the usesysid is shown as having type Oid.
> However pg_shadow says it's an integer.  Is there a reason?

There's been disagreement for a long time over whether userids should be
OIDs or ints.  If you want to introduce consistency then it's going to
take a lot more than a one-line patch.  (First you'll need to convince
the partisans involved which answer is the right one.)

> Looks like a bug.

Not as long as OID is 4 bytes.

I'd recommend not making any piecemeal changes, especially not when
there's not yet a consensus which way to converge.

			regards, tom lane

Sun, 17 Nov 2002 13:16:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_stat_database shows userid as OID 
On Sun, Nov 17, 2002 at 01:16:29PM -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > In the pg_stat_activity view, the usesysid is shown as having type Oid.
> > However pg_shadow says it's an integer.  Is there a reason?
> 
> There's been disagreement for a long time over whether userids should be
> OIDs or ints.  If you want to introduce consistency then it's going to
> take a lot more than a one-line patch.  (First you'll need to convince
> the partisans involved which answer is the right one.)

Oh, I see.  I wasn't aware of this.  I don't really know which answer is
"the right one".  I don't care a lot about this thing either, but I'll
keep it into my list of amusements, and will probably even dig into
the archives sometime.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Criptografï¿½a: Poderosa tï¿½cnica algorï¿½tmica de codificaciï¿½n que es
empleada en la creaciï¿½n de manuales de computadores.

Sun, 17 Nov 2002 17:13:52 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: pg_stat_database shows userid as OID
Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > In the pg_stat_activity view, the usesysid is shown as having type Oid.
> > However pg_shadow says it's an integer.  Is there a reason?
> 
> There's been disagreement for a long time over whether userids should be
> OIDs or ints.  If you want to introduce consistency then it's going to
> take a lot more than a one-line patch.  (First you'll need to convince
> the partisans involved which answer is the right one.)
> 
> > Looks like a bug.
> 
> Not as long as OID is 4 bytes.
> 
> I'd recommend not making any piecemeal changes, especially not when
> there's not yet a consensus which way to converge.

Well, seems we should make it consistent at least.  Let's decide and
make it done.  I think some wanted it to be an int so they could use the
same unix uid for pg_shadow, but I think we aren't using that idea much
anymore.  However, right now, it looks like the super user is '1', and
other users start numbering from 100.  That at least suggests int rather
than oid.

I am not particular in what we choose, but I do think there is a good
argument to make it consistent.

-- 
  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

Sun, 17 Nov 2002 16:24:49 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_stat_database shows userid as OID
In looking at the CLUSTER ALL patch I have applied, I am now wondering
why the ALL keyword is used.  When we do VACUUM, we don't use ALL. 
VACUUM vacuums all tables.  Shouldn't' CLUSTER alone do the same thing. 
And what about REINDEX?  That seems to have a different syntax from the
other two.  Seems there should be some consistency.

-- 
  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

Sun, 17 Nov 2002 16:33:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
CLUSTER ALL syntax
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> I'd recommend not making any piecemeal changes, especially not when
>> there's not yet a consensus which way to converge.

> Well, seems we should make it consistent at least.

I think the original argument stemmed from the idea that we ought to use
pg_shadow's OID column as the user identifier (eliminating usesysid per
se).  This seems like a good idea at first but I think it has a couple
of fatal problems:
  * disappearance of pg_shadow.usesysid column will doubtless break some
    applications
  * if we use OID then it's much more difficult to support explicit
    assignment of userid

> I think some wanted it to be an int so they could use the
> same unix uid for pg_shadow, but I think we aren't using that idea much
> anymore.

I don't think anyone worries about making usesysid match /etc/passwd
anymore, but nonetheless CREATE USER WITH SYSID is still an essential
capability.  What if you drop a user accidentally while he still owns
objects?  You *must* be able to recreate him with the same sysid as
before.  pg_depend cannot save us from this kind of mistake, either,
since users span databases.

So it seems to me that we must keep pg_shadow.usesysid as a separate
column and not try to make it the OID of pg_shadow.

Given that decision, the argument for making it be type OID seems very
weak, so I'd lean to the "use int4" camp myself.  But I'm not sure
everyone agrees.  I think Peter was strongly in favor of OID when he
was revising the session-authorization code (that's why it all uses OID
for user IDs...)

As far as the actual C code goes, I'd lean to creating new typedefs
UserId and GroupId (or some such names) and making all the routine
and variable declarations use those, and not either OID or int4.
But I'm not excited about promoting these typedefs into actual SQL
types, as was done for TransactionId and CommandId; the payback seems
much less than the effort needed.

			regards, tom lane

Sun, 17 Nov 2002 16:39:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_stat_database shows userid as OID 
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> In looking at the CLUSTER ALL patch I have applied, I am now wondering
> why the ALL keyword is used.  When we do VACUUM, we don't use ALL. 
> VACUUM vacuums all tables.  Shouldn't' CLUSTER alone do the same thing. 

I agree, lose the ALL.

> And what about REINDEX?  That seems to have a different syntax from the
> other two.  Seems there should be some consistency.

We don't have a REINDEX ALL, and I'm not in a hurry to invent one.
(Especially, I'd not want to see Alvaro spending time on that instead
of fixing the underlying btree-compaction problem ;-))

			regards, tom lane

Sun, 17 Nov 2002 16:42:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: CLUSTER ALL syntax 
On Sun, Nov 17, 2002 at 04:42:01PM -0500, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > In looking at the CLUSTER ALL patch I have applied, I am now wondering
> > why the ALL keyword is used.  When we do VACUUM, we don't use ALL. 
> > VACUUM vacuums all tables.  Shouldn't' CLUSTER alone do the same thing. 
> 
> I agree, lose the ALL.

Well, in my original patch (the one submitted just when 7.3 was going
into beta) there was no ALL.  I decided to put it in for subsequent
patches for no good reason.

> > And what about REINDEX?  That seems to have a different syntax from the
> > other two.  Seems there should be some consistency.
> 
> We don't have a REINDEX ALL, and I'm not in a hurry to invent one.
> (Especially, I'd not want to see Alvaro spending time on that instead
> of fixing the underlying btree-compaction problem ;-))

Actually, I'm planning to do the freelist thing, then the btree
compaction and then replace the current REINDEX code with the compaction
code, probably including some means to do REINDEX ALL.

It makes me really proud to hear such a note of confidence in my work.
Thank you very much.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Officer Krupke, what are we to do?
Gee, officer Krupke, Krup you! (West Side Story, "Gee, Officer Krupke")

Sun, 17 Nov 2002 19:49:05 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: CLUSTER ALL syntax
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Actually, I'm planning to do the freelist thing, then the btree
> compaction and then replace the current REINDEX code with the compaction
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> code, probably including some means to do REINDEX ALL.

Uh ... no.  The primary purpose of REINDEX is to recover from corrupted
indexes, so it has to be based on a rebuild strategy not a compaction
strategy.

If you want to add a REINDEX ALL for completeness, go ahead, but I think
the need for it will be vanishingly small once vacuum compacts btrees
properly.

			regards, tom lane

Sun, 17 Nov 2002 17:58:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: CLUSTER ALL syntax 

I totally agree with what you have said.  Peter, can you clarify your
reasoning for OID for user/group id?

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> I'd recommend not making any piecemeal changes, especially not when
> >> there's not yet a consensus which way to converge.
> 
> > Well, seems we should make it consistent at least.
> 
> I think the original argument stemmed from the idea that we ought to use
> pg_shadow's OID column as the user identifier (eliminating usesysid per
> se).  This seems like a good idea at first but I think it has a couple
> of fatal problems:
>   * disappearance of pg_shadow.usesysid column will doubtless break some
>     applications
>   * if we use OID then it's much more difficult to support explicit
>     assignment of userid
> 
> > I think some wanted it to be an int so they could use the
> > same unix uid for pg_shadow, but I think we aren't using that idea much
> > anymore.
> 
> I don't think anyone worries about making usesysid match /etc/passwd
> anymore, but nonetheless CREATE USER WITH SYSID is still an essential
> capability.  What if you drop a user accidentally while he still owns
> objects?  You *must* be able to recreate him with the same sysid as
> before.  pg_depend cannot save us from this kind of mistake, either,
> since users span databases.
> 
> So it seems to me that we must keep pg_shadow.usesysid as a separate
> column and not try to make it the OID of pg_shadow.
> 
> Given that decision, the argument for making it be type OID seems very
> weak, so I'd lean to the "use int4" camp myself.  But I'm not sure
> everyone agrees.  I think Peter was strongly in favor of OID when he
> was revising the session-authorization code (that's why it all uses OID
> for user IDs...)
> 
> As far as the actual C code goes, I'd lean to creating new typedefs
> UserId and GroupId (or some such names) and making all the routine
> and variable declarations use those, and not either OID or int4.
> But I'm not excited about promoting these typedefs into actual SQL
> types, as was done for TransactionId and CommandId; the payback seems
> much less than the effort needed.
> 
> 			regards, tom lane
> 
> ---------------------------(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

Sun, 17 Nov 2002 18:37:52 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_stat_database shows userid as OID
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > In looking at the CLUSTER ALL patch I have applied, I am now wondering
> > why the ALL keyword is used.  When we do VACUUM, we don't use ALL. 
> > VACUUM vacuums all tables.  Shouldn't' CLUSTER alone do the same thing. 
> 
> I agree, lose the ALL.

Good.  I can take care of that or someone can submit a patch.

> > And what about REINDEX?  That seems to have a different syntax from the
> > other two.  Seems there should be some consistency.
> 
> We don't have a REINDEX ALL, and I'm not in a hurry to invent one.
> (Especially, I'd not want to see Alvaro spending time on that instead
> of fixing the underlying btree-compaction problem ;-))

My point for REINDEX was a little different.  The man pages shows:

	REINDEX { DATABASE | TABLE | INDEX } <replaceable
		class="PARAMETER">name</replaceable> [ FORCE ]

where we don't have ALL but we do have DATABASE.  Do we need that
tri-valued secodn field for reindex because you can reindex a table _or_
and index, and hence DATABASE makes sense?  I am just asking.

-- 
  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

Sun, 17 Nov 2002 18:43:38 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: CLUSTER ALL syntax
On Sun, Nov 17, 2002 at 06:43:38PM -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:

> > > And what about REINDEX?  That seems to have a different syntax from the
> > > other two.  Seems there should be some consistency.
> > 
> > We don't have a REINDEX ALL, and I'm not in a hurry to invent one.
> > (Especially, I'd not want to see Alvaro spending time on that instead
> > of fixing the underlying btree-compaction problem ;-))
> 
> My point for REINDEX was a little different.  The man pages shows:
> 
> 	REINDEX { DATABASE | TABLE | INDEX } <replaceable
> 		class="PARAMETER">name</replaceable> [ FORCE ]
> 
> where we don't have ALL but we do have DATABASE.  Do we need that
> tri-valued secodn field for reindex because you can reindex a table _or_
> and index, and hence DATABASE makes sense?  I am just asking.

REINDEX DATABASE is for system indexes only, it's not the same that one
would think of REINDEX alone (which is all indexes on all tables, isn't
it?).

What I don't understand is what are the parameters in the
ReindexDatabase function for.  For example, the boolean all is always
false in tcop/utility.c (and there are no other places that the function
is called).  Also, the database name is checked to be equal to a
"constant" value, the database name that the standalone backend is
connected to.  Why are those useful?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No renuncies a nada. No te aferres a nada"

Sun, 17 Nov 2002 22:31:40 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: CLUSTER ALL syntax
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> What I don't understand is what are the parameters in the
> ReindexDatabase function for.  For example, the boolean all is always
> false in tcop/utility.c (and there are no other places that the function
> is called).  Also, the database name is checked to be equal to a
> "constant" value, the database name that the standalone backend is
> connected to.  Why are those useful?

Well, passing all=true would implement REINDEX ALL ...

As for the database name, we could perhaps change the syntax to just
REINDEX DATABASE; not sure if it's worth the trouble.

			regards, tom lane

Sun, 17 Nov 2002 20:36:04 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: CLUSTER ALL syntax 
> In looking at the CLUSTER ALL patch I have applied, I am now wondering
> why the ALL keyword is used.  When we do VACUUM, we don't use ALL. 
> VACUUM vacuums all tables.  Shouldn't' CLUSTER alone do the same thing. 
> And what about REINDEX?  That seems to have a different syntax from the
> other two.  Seems there should be some consistency.

Yeah - I agree!

Chris


Mon, 18 Nov 2002 09:53:07 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: CLUSTER ALL syntax
Alvaro Herrera wrote:
> 
> On Sun, Nov 17, 2002 at 06:43:38PM -0500, Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
> > > > And what about REINDEX?  That seems to have a different
> > > > syntax from the other two.  Seems there should be some > > > > consistency.
> > >
> > > We don't have a REINDEX ALL, and I'm not in a hurry to invent one.
> > > (Especially, I'd not want to see Alvaro spending time on that
> > > instead of fixing the underlying btree-compaction problem ;-))
> >
> > My point for REINDEX was a little different.  The man pages shows:
> >
> >       REINDEX { DATABASE | TABLE | INDEX } <replaceable
> >               class="PARAMETER">name</replaceable> [ FORCE ]
> >
> > where we don't have ALL but we do have DATABASE.  Do we need that
> > tri-valued secodn field for reindex because you can reindex a
> > table _or_ and index, and hence DATABASE makes sense?  I am just
> > asking.
> 
> REINDEX DATABASE is for system indexes only, it's not the same that one
> would think of REINDEX alone (which is all indexes on all tables, isn't
> it?).

Probably You don't understand the initial purpose of REINDEX.
It isn't an SQL standard at all and was intended to recover
corrupted system indexes. It's essentially an unsafe operation
and so the operation was inhibited other than under standalone
postgres. I also made the command a little hard to use to avoid
unexpected invocations e.g. REINDEX DATABASE requires an unnecessary
database name parameter or FORCE is still needed though it's a
requisite parameter now.

REINDEX is also used to compact indexes now. It's good but
the purpose is different from the initial one and we would
have to reorganize the functionalities e.g. the table data
isn't needed to compact the indexes etc.  
 
> What I don't understand is what are the parameters in the
> ReindexDatabase function for.  For example, the boolean all
> is always false in tcop/utility.c (and there are no other
> places that the function is called). 

I intended to implement the *true* case also then
but haven't done it yet, sorry.

regards,
Hiroshi Inoue
	http://w2422.nsk.ne.jp/~inoue/

Mon, 18 Nov 2002 11:31:42 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: CLUSTER ALL syntax
Alvaro Herrera wrote:
> On Sun, Nov 17, 2002 at 04:42:01PM -0500, Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > In looking at the CLUSTER ALL patch I have applied, I am now wondering
> > > why the ALL keyword is used.  When we do VACUUM, we don't use ALL. 
> > > VACUUM vacuums all tables.  Shouldn't' CLUSTER alone do the same thing. 
> > 
> > I agree, lose the ALL.
> 
> Well, in my original patch (the one submitted just when 7.3 was going
> into beta) there was no ALL.  I decided to put it in for subsequent
> patches for no good reason.

I have updated CVS to make the syntax CLUSTER rather than CLUSTER ALL.

-- 
  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

Mon, 18 Nov 2002 12:17:08 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: CLUSTER ALL syntax

Does anyone want userid to be an OID?  Peter?  Anyone?

If not, I will add it to the TODO list or work on the patch myself.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> I'd recommend not making any piecemeal changes, especially not when
> >> there's not yet a consensus which way to converge.
> 
> > Well, seems we should make it consistent at least.
> 
> I think the original argument stemmed from the idea that we ought to use
> pg_shadow's OID column as the user identifier (eliminating usesysid per
> se).  This seems like a good idea at first but I think it has a couple
> of fatal problems:
>   * disappearance of pg_shadow.usesysid column will doubtless break some
>     applications
>   * if we use OID then it's much more difficult to support explicit
>     assignment of userid
> 
> > I think some wanted it to be an int so they could use the
> > same unix uid for pg_shadow, but I think we aren't using that idea much
> > anymore.
> 
> I don't think anyone worries about making usesysid match /etc/passwd
> anymore, but nonetheless CREATE USER WITH SYSID is still an essential
> capability.  What if you drop a user accidentally while he still owns
> objects?  You *must* be able to recreate him with the same sysid as
> before.  pg_depend cannot save us from this kind of mistake, either,
> since users span databases.
> 
> So it seems to me that we must keep pg_shadow.usesysid as a separate
> column and not try to make it the OID of pg_shadow.
> 
> Given that decision, the argument for making it be type OID seems very
> weak, so I'd lean to the "use int4" camp myself.  But I'm not sure
> everyone agrees.  I think Peter was strongly in favor of OID when he
> was revising the session-authorization code (that's why it all uses OID
> for user IDs...)
> 
> As far as the actual C code goes, I'd lean to creating new typedefs
> UserId and GroupId (or some such names) and making all the routine
> and variable declarations use those, and not either OID or int4.
> But I'm not excited about promoting these typedefs into actual SQL
> types, as was done for TransactionId and CommandId; the payback seems
> much less than the effort needed.
> 
> 			regards, tom lane
> 
> ---------------------------(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

Fri, 22 Nov 2002 00:40:26 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_stat_database shows userid as OID
OK, with three people thinking we need to make usesysid more constently
int4, and no one objecting, I have applied this patch, and that attached
patch which makes usesysid constently int4, and not oid.

Catalog version updated.  initdb required.

If there are more places that think usesysid is oid, please let me know.
AclId already existed for this purpose, so I used that rather than int32
directly.

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> Hello hackers,
> 
> In the pg_stat_activity view, the usesysid is shown as having type Oid.
> However pg_shadow says it's an integer.  Is there a reason? Looks like
> a bug.
> 
> This patch seems to corrects this issue, but I don't know if there's
> something else involved.
> 
> Index: src/include/catalog/pg_proc.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
> retrieving revision 1.276
> diff -c -r1.276 pg_proc.h
> *** src/include/catalog/pg_proc.h	2002/11/08 17:27:03	1.276
> --- src/include/catalog/pg_proc.h	2002/11/16 23:18:44
> ***************
> *** 2738,2744 ****
>   DESCR("Statistics: PID of backend");
>   DATA(insert OID = 1938 (  pg_stat_get_backend_dbid		PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_dbid - _null_ ));
>   DESCR("Statistics: Database ID of backend");
> ! DATA(insert OID = 1939 (  pg_stat_get_backend_userid	PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_userid - _null_ ));
>   DESCR("Statistics: User ID of backend");
>   DATA(insert OID = 1940 (  pg_stat_get_backend_activity	PGNSP PGUID 12 f f t f s 1 25 "23"	pg_stat_get_backend_activity - _null_ ));
>   DESCR("Statistics: Current query of backend");
> --- 2738,2744 ----
>   DESCR("Statistics: PID of backend");
>   DATA(insert OID = 1938 (  pg_stat_get_backend_dbid		PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_dbid - _null_ ));
>   DESCR("Statistics: Database ID of backend");
> ! DATA(insert OID = 1939 (  pg_stat_get_backend_userid	PGNSP PGUID 12 f f t f s 1 23 "23"	pg_stat_get_backend_userid - _null_ ));
>   DESCR("Statistics: User ID of backend");
>   DATA(insert OID = 1940 (  pg_stat_get_backend_activity	PGNSP PGUID 12 f f t f s 1 25 "23"	pg_stat_get_backend_activity - _null_ ));
>   DESCR("Statistics: Current query of backend");
> Index: src/backend/utils/adt/pgstatfuncs.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/pgstatfuncs.c,v
> retrieving revision 1.8
> diff -c -r1.8 pgstatfuncs.c
> *** src/backend/utils/adt/pgstatfuncs.c	2002/08/20 04:47:52	1.8
> --- src/backend/utils/adt/pgstatfuncs.c	2002/11/16 23:18:44
> ***************
> *** 272,278 ****
>   	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
>   		PG_RETURN_NULL();
>   
> ! 	PG_RETURN_OID(beentry->userid);
>   }
>   
>   
> --- 272,278 ----
>   	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
>   		PG_RETURN_NULL();
>   
> ! 	PG_RETURN_INT32(beentry->userid);
>   }
>   
>   
> -- 
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "El miedo atento y previsor es la madre de la seguridad" (E. Burke)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@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

Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/catalog/aclchk.c,v
retrieving revision 1.78
diff -c -c -r1.78 aclchk.c
*** src/backend/catalog/aclchk.c	24 Sep 2002 23:14:25 -0000	1.78
--- src/backend/catalog/aclchk.c	4 Dec 2002 05:16:29 -0000
***************
*** 893,899 ****
   * Exported routine for checking a user's access privileges to a table
   */
  AclResult
! pg_class_aclcheck(Oid table_oid, Oid userid, AclMode mode)
  {
  	AclResult	result;
  	bool		usesuper,
--- 893,899 ----
   * Exported routine for checking a user's access privileges to a table
   */
  AclResult
! pg_class_aclcheck(Oid table_oid, AclId userid, AclMode mode)
  {
  	AclResult	result;
  	bool		usesuper,
***************
*** 991,997 ****
   * Exported routine for checking a user's access privileges to a database
   */
  AclResult
! pg_database_aclcheck(Oid db_oid, Oid userid, AclMode mode)
  {
  	AclResult	result;
  	Relation	pg_database;
--- 991,997 ----
   * Exported routine for checking a user's access privileges to a database
   */
  AclResult
! pg_database_aclcheck(Oid db_oid, AclId userid, AclMode mode)
  {
  	AclResult	result;
  	Relation	pg_database;
***************
*** 1054,1060 ****
   * Exported routine for checking a user's access privileges to a function
   */
  AclResult
! pg_proc_aclcheck(Oid proc_oid, Oid userid, AclMode mode)
  {
  	AclResult	result;
  	HeapTuple	tuple;
--- 1054,1060 ----
   * Exported routine for checking a user's access privileges to a function
   */
  AclResult
! pg_proc_aclcheck(Oid proc_oid, AclId userid, AclMode mode)
  {
  	AclResult	result;
  	HeapTuple	tuple;
***************
*** 1107,1113 ****
   * Exported routine for checking a user's access privileges to a language
   */
  AclResult
! pg_language_aclcheck(Oid lang_oid, Oid userid, AclMode mode)
  {
  	AclResult	result;
  	HeapTuple	tuple;
--- 1107,1113 ----
   * Exported routine for checking a user's access privileges to a language
   */
  AclResult
! pg_language_aclcheck(Oid lang_oid, AclId userid, AclMode mode)
  {
  	AclResult	result;
  	HeapTuple	tuple;
***************
*** 1157,1163 ****
   * Exported routine for checking a user's access privileges to a namespace
   */
  AclResult
! pg_namespace_aclcheck(Oid nsp_oid, Oid userid, AclMode mode)
  {
  	AclResult	result;
  	HeapTuple	tuple;
--- 1157,1163 ----
   * Exported routine for checking a user's access privileges to a namespace
   */
  AclResult
! pg_namespace_aclcheck(Oid nsp_oid, AclId userid, AclMode mode)
  {
  	AclResult	result;
  	HeapTuple	tuple;
***************
*** 1218,1224 ****
   * Ownership check for a relation (specified by OID).
   */
  bool
! pg_class_ownercheck(Oid class_oid, Oid userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
--- 1218,1224 ----
   * Ownership check for a relation (specified by OID).
   */
  bool
! pg_class_ownercheck(Oid class_oid, AclId userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
***************
*** 1244,1250 ****
   * Ownership check for a type (specified by OID).
   */
  bool
! pg_type_ownercheck(Oid type_oid, Oid userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
--- 1244,1250 ----
   * Ownership check for a type (specified by OID).
   */
  bool
! pg_type_ownercheck(Oid type_oid, AclId userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
***************
*** 1270,1276 ****
   * Ownership check for an operator (specified by OID).
   */
  bool
! pg_oper_ownercheck(Oid oper_oid, Oid userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
--- 1270,1276 ----
   * Ownership check for an operator (specified by OID).
   */
  bool
! pg_oper_ownercheck(Oid oper_oid, AclId userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
***************
*** 1296,1302 ****
   * Ownership check for a function (specified by OID).
   */
  bool
! pg_proc_ownercheck(Oid proc_oid, Oid userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
--- 1296,1302 ----
   * Ownership check for a function (specified by OID).
   */
  bool
! pg_proc_ownercheck(Oid proc_oid, AclId userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
***************
*** 1322,1328 ****
   * Ownership check for a namespace (specified by OID).
   */
  bool
! pg_namespace_ownercheck(Oid nsp_oid, Oid userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
--- 1322,1328 ----
   * Ownership check for a namespace (specified by OID).
   */
  bool
! pg_namespace_ownercheck(Oid nsp_oid, AclId userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
***************
*** 1349,1355 ****
   * Ownership check for an operator class (specified by OID).
   */
  bool
! pg_opclass_ownercheck(Oid opc_oid, Oid userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
--- 1349,1355 ----
   * Ownership check for an operator class (specified by OID).
   */
  bool
! pg_opclass_ownercheck(Oid opc_oid, AclId userid)
  {
  	HeapTuple	tuple;
  	AclId		owner_id;
Index: src/backend/catalog/namespace.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/catalog/namespace.c,v
retrieving revision 1.40
diff -c -c -r1.40 namespace.c
*** src/backend/catalog/namespace.c	11 Nov 2002 22:19:21 -0000	1.40
--- src/backend/catalog/namespace.c	4 Dec 2002 05:16:36 -0000
***************
*** 1365,1371 ****
  static void
  recomputeNamespacePath(void)
  {
! 	Oid			userId = GetUserId();
  	char	   *rawname;
  	List	   *namelist;
  	List	   *oidlist;
--- 1365,1371 ----
  static void
  recomputeNamespacePath(void)
  {
! 	AclId		userId = GetUserId();
  	char	   *rawname;
  	List	   *namelist;
  	List	   *oidlist;
Index: src/backend/catalog/pg_conversion.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/catalog/pg_conversion.c,v
retrieving revision 1.8
diff -c -c -r1.8 pg_conversion.c
*** src/backend/catalog/pg_conversion.c	2 Nov 2002 18:41:21 -0000	1.8
--- src/backend/catalog/pg_conversion.c	4 Dec 2002 05:16:37 -0000
***************
*** 37,43 ****
   */
  Oid
  ConversionCreate(const char *conname, Oid connamespace,
! 				 int32 conowner,
  				 int32 conforencoding, int32 contoencoding,
  				 Oid conproc, bool def)
  {
--- 37,43 ----
   */
  Oid
  ConversionCreate(const char *conname, Oid connamespace,
! 				 AclId conowner,
  				 int32 conforencoding, int32 contoencoding,
  				 Oid conproc, bool def)
  {
Index: src/backend/commands/cluster.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/commands/cluster.c,v
retrieving revision 1.97
diff -c -c -r1.97 cluster.c
*** src/backend/commands/cluster.c	23 Nov 2002 18:26:45 -0000	1.97
--- src/backend/commands/cluster.c	4 Dec 2002 05:16:37 -0000
***************
*** 804,814 ****
  
  /* Get a list of tables that the current user owns and
   * have indisclustered set.  Return the list in a List * of rvsToCluster
!  * with the tableOid and the indexOid on which the table is already 
   * clustered.
   */
  List *
! get_tables_to_cluster(Oid owner)
  {
  	Relation		indRelation;
  	HeapScanDesc	scan;
--- 804,814 ----
  
  /* Get a list of tables that the current user owns and
   * have indisclustered set.  Return the list in a List * of rvsToCluster
!  * with the tableOid and the indexOid on which the table is already
   * clustered.
   */
  List *
! get_tables_to_cluster(AclId owner)
  {
  	Relation		indRelation;
  	HeapScanDesc	scan;
Index: src/backend/utils/adt/pgstatfuncs.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/pgstatfuncs.c,v
retrieving revision 1.8
diff -c -c -r1.8 pgstatfuncs.c
*** src/backend/utils/adt/pgstatfuncs.c	20 Aug 2002 04:47:52 -0000	1.8
--- src/backend/utils/adt/pgstatfuncs.c	4 Dec 2002 05:16:38 -0000
***************
*** 272,278 ****
  	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
  		PG_RETURN_NULL();
  
! 	PG_RETURN_OID(beentry->userid);
  }
  
  
--- 272,278 ----
  	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
  		PG_RETURN_NULL();
  
! 	PG_RETURN_INT32(beentry->userid);
  }
  
  
Index: src/include/miscadmin.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/miscadmin.h,v
retrieving revision 1.111
diff -c -c -r1.111 miscadmin.h
*** src/include/miscadmin.h	3 Oct 2002 17:07:53 -0000	1.111
--- src/include/miscadmin.h	4 Dec 2002 05:16:39 -0000
***************
*** 202,208 ****
  
  extern char *GetUserNameFromId(Oid userid);
  
! extern Oid	GetUserId(void);
  extern void SetUserId(Oid userid);
  extern Oid	GetSessionUserId(void);
  extern void SetSessionUserId(Oid userid);
--- 202,214 ----
  
  extern char *GetUserNameFromId(Oid userid);
  
! /*
!  * AclId		system identifier for the user, group, etc.
!  *				XXX Perhaps replace this type by OID?
!  */
! typedef uint32 AclId;
! 
! extern AclId GetUserId(void);
  extern void SetUserId(Oid userid);
  extern Oid	GetSessionUserId(void);
  extern void SetSessionUserId(Oid userid);
Index: src/include/catalog/catversion.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/catalog/catversion.h,v
retrieving revision 1.166
diff -c -c -r1.166 catversion.h
*** src/include/catalog/catversion.h	25 Nov 2002 18:12:11 -0000	1.166
--- src/include/catalog/catversion.h	4 Dec 2002 05:16:39 -0000
***************
*** 53,58 ****
   */
  
  /*							yyyymmddN */
! #define CATALOG_VERSION_NO	200211251
  
  #endif
--- 53,58 ----
   */
  
  /*							yyyymmddN */
! #define CATALOG_VERSION_NO	200212031
  
  #endif
Index: src/include/catalog/pg_conversion.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/catalog/pg_conversion.h,v
retrieving revision 1.7
diff -c -c -r1.7 pg_conversion.h
*** src/include/catalog/pg_conversion.h	2 Nov 2002 02:33:03 -0000	1.7
--- src/include/catalog/pg_conversion.h	4 Dec 2002 05:16:40 -0000
***************
*** 19,24 ****
--- 19,26 ----
  #ifndef PG_CONVERSION_H
  #define PG_CONVERSION_H
  
+ #include "miscadmin.h"
+ 
  /* ----------------
   *		postgres.h contains the system type definitions and the
   *		CATALOG(), BOOTSTRAP and DATA() sugar words so this file
***************
*** 84,90 ****
  #include "nodes/parsenodes.h"
  
  extern Oid ConversionCreate(const char *conname, Oid connamespace,
! 				 int32 conowner,
  				 int32 conforencoding, int32 contoencoding,
  				 Oid conproc, bool def);
  extern void ConversionDrop(Oid conversionOid, DropBehavior behavior);
--- 86,92 ----
  #include "nodes/parsenodes.h"
  
  extern Oid ConversionCreate(const char *conname, Oid connamespace,
! 				 AclId conowner,
  				 int32 conforencoding, int32 contoencoding,
  				 Oid conproc, bool def);
  extern void ConversionDrop(Oid conversionOid, DropBehavior behavior);
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.276
diff -c -c -r1.276 pg_proc.h
*** src/include/catalog/pg_proc.h	8 Nov 2002 17:27:03 -0000	1.276
--- src/include/catalog/pg_proc.h	4 Dec 2002 05:16:48 -0000
***************
*** 2738,2744 ****
  DESCR("Statistics: PID of backend");
  DATA(insert OID = 1938 (  pg_stat_get_backend_dbid		PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_dbid - _null_ ));
  DESCR("Statistics: Database ID of backend");
! DATA(insert OID = 1939 (  pg_stat_get_backend_userid	PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_userid - _null_ ));
  DESCR("Statistics: User ID of backend");
  DATA(insert OID = 1940 (  pg_stat_get_backend_activity	PGNSP PGUID 12 f f t f s 1 25 "23"	pg_stat_get_backend_activity - _null_ ));
  DESCR("Statistics: Current query of backend");
--- 2738,2744 ----
  DESCR("Statistics: PID of backend");
  DATA(insert OID = 1938 (  pg_stat_get_backend_dbid		PGNSP PGUID 12 f f t f s 1 26 "23"	pg_stat_get_backend_dbid - _null_ ));
  DESCR("Statistics: Database ID of backend");
! DATA(insert OID = 1939 (  pg_stat_get_backend_userid	PGNSP PGUID 12 f f t f s 1 23 "23"	pg_stat_get_backend_userid - _null_ ));
  DESCR("Statistics: User ID of backend");
  DATA(insert OID = 1940 (  pg_stat_get_backend_activity	PGNSP PGUID 12 f f t f s 1 25 "23"	pg_stat_get_backend_activity - _null_ ));
  DESCR("Statistics: Current query of backend");
Index: src/include/utils/acl.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/utils/acl.h,v
retrieving revision 1.47
diff -c -c -r1.47 acl.h
*** src/include/utils/acl.h	4 Sep 2002 20:31:45 -0000	1.47
--- src/include/utils/acl.h	4 Dec 2002 05:16:48 -0000
***************
*** 22,37 ****
  #ifndef ACL_H
  #define ACL_H
  
  #include "nodes/parsenodes.h"
  #include "utils/array.h"
  
  
- /*
-  * AclId		system identifier for the user, group, etc.
-  *				XXX Perhaps replace this type by OID?
-  */
- typedef uint32 AclId;
- 
  #define ACL_ID_WORLD	0		/* placeholder for id in a WORLD acl item */
  
  /*
--- 22,32 ----
  #ifndef ACL_H
  #define ACL_H
  
+ #include "miscadmin.h"
  #include "nodes/parsenodes.h"
  #include "utils/array.h"
  
  
  #define ACL_ID_WORLD	0		/* placeholder for id in a WORLD acl item */
  
  /*
***************
*** 204,214 ****
  extern void aclcheck_error(AclResult errcode, const char *objectname);
  
  /* ownercheck routines just return true (owner) or false (not) */
! extern bool pg_class_ownercheck(Oid class_oid, Oid userid);
! extern bool pg_type_ownercheck(Oid type_oid, Oid userid);
! extern bool pg_oper_ownercheck(Oid oper_oid, Oid userid);
! extern bool pg_proc_ownercheck(Oid proc_oid, Oid userid);
! extern bool pg_namespace_ownercheck(Oid nsp_oid, Oid userid);
! extern bool pg_opclass_ownercheck(Oid opc_oid, Oid userid);
  
  #endif   /* ACL_H */
--- 199,209 ----
  extern void aclcheck_error(AclResult errcode, const char *objectname);
  
  /* ownercheck routines just return true (owner) or false (not) */
! extern bool pg_class_ownercheck(Oid class_oid, AclId userid);
! extern bool pg_type_ownercheck(Oid type_oid, AclId userid);
! extern bool pg_oper_ownercheck(Oid oper_oid, AclId userid);
! extern bool pg_proc_ownercheck(Oid proc_oid, AclId userid);
! extern bool pg_namespace_ownercheck(Oid nsp_oid, AclId userid);
! extern bool pg_opclass_ownercheck(Oid opc_oid, AclId userid);
  
  #endif   /* ACL_H */
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /cvsroot/pgsql-server/src/test/regress/expected/rules.out,v
retrieving revision 1.68
diff -c -c -r1.68 rules.out
*** src/test/regress/expected/rules.out	21 Nov 2002 22:26:01 -0000	1.68
--- src/test/regress/expected/rules.out	4 Dec 2002 05:16:50 -0000
***************
*** 1274,1280 ****
   pg_locks                 | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status() l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean);
   pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
   pg_settings              | SELECT a.name, a.setting FROM pg_show_all_settings() a(name text, setting text);
!  pg_stat_activity         | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = (u.usesysid)::oid));
   pg_stat_all_indexes      | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char");
   pg_stat_all_tables       | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char") GROUP BY c.oid, n.nspname, c.relname;
   pg_stat_database         | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit FROM pg_database d;
--- 1274,1280 ----
   pg_locks                 | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status() l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean);
   pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
   pg_settings              | SELECT a.name, a.setting FROM pg_show_all_settings() a(name text, setting text);
!  pg_stat_activity         | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.usesysid));
   pg_stat_all_indexes      | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char");
   pg_stat_all_tables       | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char") GROUP BY c.oid, n.nspname, c.relname;
   pg_stat_database         | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit FROM pg_database d;
Wed, 4 Dec 2002 00:20:41 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] pg_stat_database shows userid as OID