\c connects as another user instead I want in psql

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

Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)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

On Wed, 2 May 2001, Tom Lane wrote:

> Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)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

Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)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

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(at)gmx(dot)net http://funkturm.homeip.net/~peter

> Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)gmx(dot)net http://funkturm.homeip.net/~peter

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(at)Yahoo(dot)com #

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

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

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

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

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(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)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

Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)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

On Mon, 14 May 2001, Tom Lane wrote:

> Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)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