This may be a reported bug. 7.1beta4.

I use user names mostly as numbers. E.g. 1050, 1060, 1092.
Sometimes I got strange result when I try to reconnect:

tir=> \c - 1022
You are now connected as new user 1022.
tir=> select user;
 current_user
--------------
 1022
(1 row)

(It's OK.)

tir=> \c - 1060
You are now connected as new user 1060.
tir=> select user;
 current_user
--------------
 1092
(1 row)

This is the problematic point. Is this a solved bug?

TIA, Zoltan


Wed, 2 May 2001 18:40:30 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
\c connects as another user instead I want in psql
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> tir=> \c - 1060
> You are now connected as new user 1060.
> tir=> select user;
>  current_user
> --------------
>  1092
> (1 row)

Is it possible that 1060 and 1092 have the same usesysid in pg_shadow?

			regards, tom lane

Wed, 02 May 2001 18:32:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: \c connects as another user instead I want in psql 
On Wed, 2 May 2001, Tom Lane wrote:

> Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> > tir=> \c - 1060
> > You are now connected as new user 1060.
> > tir=> select user;
> >  current_user
> > --------------
> >  1092
> > (1 row)
> 
> Is it possible that 1060 and 1092 have the same usesysid in pg_shadow?
Hmmm. That was the problem. Thanks! By the way, could you please define a
unique constraint on column 'usesysid' in future in PostgreSQL?

Zoltan


Thu, 3 May 2001 11:18:15 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: \c connects as another user instead I want in psql 
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
>> Is it possible that 1060 and 1092 have the same usesysid in pg_shadow?

> Hmmm. That was the problem. Thanks! By the way, could you please define a
> unique constraint on column 'usesysid' in future in PostgreSQL?

Yup, there should be one (and one on usename, too).  Not sure why it's
been overlooked so far.

			regards, tom lane

Thu, 03 May 2001 09:07:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: \c connects as another user instead I want in psql 
Kovacs Zoltan writes:

> By the way, could you please define a unique constraint on column
> 'usesysid' in future in PostgreSQL?

The usesysid column will be removed and the oid column will be used
instead.  That one tends to be unique, but an index will still be added.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 3 May 2001 16:02:09 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: \c connects as another user instead I want in psql 
> Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> >> Is it possible that 1060 and 1092 have the same usesysid in pg_shadow?
> 
> > Hmmm. That was the problem. Thanks! By the way, could you please define a
> > unique constraint on column 'usesysid' in future in PostgreSQL?
> 
> Yup, there should be one (and one on usename, too).  Not sure why it's
> been overlooked so far.

TODO item has:

	* Add unique indexes to pg_shadow.usename and pg_shadow.usesysid

I overlooked it long ago because there is no cache lookup on that
column.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 3 May 2001 11:16:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: \c connects as another user instead I want in psql
> Kovacs Zoltan writes:
> 
> > By the way, could you please define a unique constraint on column
> > 'usesysid' in future in PostgreSQL?
> 
> The usesysid column will be removed and the oid column will be used
> instead.  That one tends to be unique, but an index will still be added.

Really?  We are removing usesysid?  Seems the admin will no longer be
able to choose the users id, right?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 3 May 2001 11:17:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: \c connects as another user instead I want in psql
Bruce Momjian writes:

> Really?  We are removing usesysid?  Seems the admin will no longer be
> able to choose the users id, right?

Not that this was ever useful.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 3 May 2001 17:49:27 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: \c connects as another user instead I want in psql
Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > Really?  We are removing usesysid?  Seems the admin will no longer be
> > able to choose the users id, right?
> 
> Not that this was ever useful.

    Except for re-adding users.

Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Mon, 7 May 2001 10:42:41 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: \c connects as another user instead I want in psql
>>> Really?  We are removing usesysid?  Seems the admin will no longer be
>>> able to choose the users id, right?
>> 
>> Not that this was ever useful.

>     Except for re-adding users.

Yes.  In theory, the correct answer to that is to add referential
integrity checks that prevent you from dropping a user that still
owns any objects.  In practice, this is impractical because users
span a whole database installation.  We have no reasonable way to
check whether the user owns objects in other databases that cannot
be seen from the DB where we are issuing the DROP USER command.

Therefore, for the foreseeable future it will be important to be
able to reverse a DROP USER command --- ie, recreate a user with
the same user identifier previously used.

After thinking about that for awhile, I am inclined to change my
previous position: we should not switch over to using the OIDs of
pg_shadow rows as user identifiers.  usesysid should continue to
exist.  Ditto for groups --- grosysid can't go away either.

I think the original motivation for wanting to eliminate these columns
was that we need usesysid and grosysid to be distinct (can't use the
same ID for both a user and a group).  Using OIDs as IDs would fix
that, but it's overkill.  Wouldn't it be sufficient to use an
installation-wide sequence object to assign new IDs for new users and
groups?  We have no such animals at the present, but I see no reason
why we couldn't make one.

			regards, tom lane

Mon, 07 May 2001 11:11:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: \c connects as another user instead I want in psql 
> I think the original motivation for wanting to eliminate these columns
> was that we need usesysid and grosysid to be distinct (can't use the
> same ID for both a user and a group).  Using OIDs as IDs would fix
> that, but it's overkill.  Wouldn't it be sufficient to use an
> installation-wide sequence object to assign new IDs for new users and
> groups?  We have no such animals at the present, but I see no reason
> why we couldn't make one.

Updated TODO to show both options:

* Add unique indexes to pg_shadow.usename and pg_shadow.usesysid or
  switch to pg_shadow.oid as user id

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 7 May 2001 14:40:33 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: \c connects as another user instead I want in psql
> After thinking about that for awhile, I am inclined to change my
> previous position: we should not switch over to using the OIDs of
> pg_shadow rows as user identifiers.  usesysid should continue to
> exist.  Ditto for groups --- grosysid can't go away either.
> 
> I think the original motivation for wanting to eliminate these columns
> was that we need usesysid and grosysid to be distinct (can't use the
> same ID for both a user and a group).  Using OIDs as IDs would fix
> that, but it's overkill.  Wouldn't it be sufficient to use an
> installation-wide sequence object to assign new IDs for new users and
> groups?  We have no such animals at the present, but I see no reason
> why we couldn't make one.

One thing on the TODO list is to allow people to soecify OID's on
INSERT.  There is no reason we should disallow it, and it could come in
handy for fixing deleted rows.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 7 May 2001 15:53:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: \c connects as another user instead I want in psql
In 7.1.1 the following statement doesn't work (backend closes
immediately):

SELECT INTO var1, var2 col1, col2 FROM table WHERE conditions;

In 7.1 (final) this problem doesn't occur.

Workaround:

var1 := col1 FROM table WHERE conditions;
var2 := col2 FROM table WHERE conditions;

(Of course I'd better not rewrite my 200K code of PLGSQL... :-)

TIA, Zoltan

-- 
                         Kov\'acs, Zolt\'an
                         kovacsz@pc10.radnoti-szeged.sulinet.hu
                         http://www.math.u-szeged.hu/~kovzol
                         ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz


Mon, 14 May 2001 10:38:08 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
bug in PLPGSQL
Kovacs Zoltan wrote:
> 
> In 7.1.1 the following statement doesn't work (backend closes
> immediately):
> 
> SELECT INTO var1, var2 col1, col2 FROM table WHERE conditions;
> 
> In 7.1 (final) this problem doesn't occur.
> 

It's a known bug.
If you in a hurry, please apply the latest change for
src/pl/plpgsql/src/pl_exec.c by Tom.

regards,
Hiroshi Inoue

Mon, 14 May 2001 18:48:24 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: bug in PLPGSQL
There are SELECT INTO statements which work properly. Here is an example
which closes the backend:

CREATE FUNCTION plpgsql_call_handler ( ) RETURNS opaque AS '/usr/local/pgsql-7.1.1/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

create table foo(x int4, y int4);

create function bugtest(int4) returns int4 as '
declare
    _x int4;
    _y int4;
begin
    select into _x,_y
		x, y from foo where x = $1 limit 1;
    return x;
end;
' language 'plpgsql';

select bugtest(5);

If the WHERE clause doesn't contain any input parameters (i.e. $1), I
don't get into any trouble.

Zoltan

-- 
                         Kov\'acs, Zolt\'an
                         kovacsz@pc10.radnoti-szeged.sulinet.hu
                         http://www.math.u-szeged.hu/~kovzol
                         ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz


Mon, 14 May 2001 11:52:38 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: bug in PLPGSQL
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> In 7.1.1 the following statement doesn't work (backend closes
> immediately):

> SELECT INTO var1, var2 col1, col2 FROM table WHERE conditions;

Would you mind providing a complete test case, so that we don't waste
time guessing at context?

			regards, tom lane

Mon, 14 May 2001 10:26:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in PLPGSQL 
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> If the WHERE clause doesn't contain any input parameters (i.e. $1), I
> don't get into any trouble.

Is this the known bug with failure if the SELECT returns zero rows?

			regards, tom lane

Mon, 14 May 2001 10:41:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: bug in PLPGSQL 
On Mon, 14 May 2001, Tom Lane wrote:

> Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> > If the WHERE clause doesn't contain any input parameters (i.e. $1), I
> > don't get into any trouble.
> 
> Is this the known bug with failure if the SELECT returns zero rows?
Yes, it is. I haven't known this bug yet, however I read the mailing lists
first. I also tried your patch and it works now greatly. Thanks,

Zoltan


Mon, 14 May 2001 18:13:07 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: Re: bug in PLPGSQL 