Hi All,

Having recently upgraded a host to 9.2.1, I was somewhat astounded to see my users break their application on startup. It seems that in 9.2.1 the sequence owner is no longer defaulted to the database owner (as per tables, etc....)

The default of database owner is on 9.0.4, 9.1.2, 9.2.0, and even on a 10.0..0 site I have.

Martin Bowes

Try this....It won't break the installation.
sql iidbdb << SQL_END
create user martinb;
\p\g
create user martinc;
\p\g
SQL_END

createdb -umartinc bowtest

sql -umartinc bowtest << SQL_END
create sequence nextid;
\p\g
grant next on sequence nextid to user martinb;
\p\g
\q
SQL_END

# If the problem exists then the first select will generate error:
# E_US1914 RETRIEVE: Sequence 'nextid' referenced in next/current value was
# not found.
sql -umartinb bowtest << SQL_END
select nextid.nextval;
\p\g
/*
** But this one with the owner present is OK.
*/
select martinc.nextid.nextval;
\p\g
\q
SQL_END

destroydb -umartinc bowtest
sql iidbdb << SQL_END
drop user martinb;
\p\g
drop user martinc;
\p\g
\q
SQL_END

I have tried this against: Ingres v9.2.1 p13836 on Win32 and it works
for me:





INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation

Ingres Microsoft Windows Version II 9.2.1 (int.w32/103) login

Thu Aug 26 11:03:13 2010



continue

* * /* SQL Startup File */

create user martinb;

Executing . . .



continue

* * create user martinc;

Executing . . .



continue

*

Your SQL statement(s) have been committed.



Ingres Version II 9.2.1 (int.w32/103) logout

Thu Aug 26 11:03:13 2010

Creating database 'bowtest' . . .



Creating DBMS System Catalogs . . .

Modifying DBMS System Catalogs . . .

Creating Standard Catalog Interface . . .

Creating Front-end System Catalogs . . .



Creation of database 'bowtest' completed successfully.



INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation

Ingres Microsoft Windows Version II 9.2.1 (int.w32/103) login

Thu Aug 26 11:03:53 2010



continue

* * /* SQL Startup File */

create sequence nextid;

Executing . . .



continue

* * grant next on sequence nextid to user martinb;

Executing . . .



continue

* Your SQL statement(s) have been committed.



Ingres Version II 9.2.1 (int.w32/103) logout

Thu Aug 26 11:03:54 2010

INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation

Ingres Microsoft Windows Version II 9.2.1 (int.w32/103) login

Thu Aug 26 11:03:57 2010



continue

* * /* SQL Startup File */

select nextid.nextval;

Executing . . .





+-------------+

+-------------+

+-------------+

(1 row)

continue

* * * * * /*

** But this one with the owner present is OK.

*/

select martinc.nextid.nextval;

Executing . . .





+-------------+

+-------------+

+-------------+

(1 row)

continue

* Your SQL statement(s) have been committed.



Ingres Version II 9.2.1 (int.w32/103) logout

Thu Aug 26 11:03:58 2010

Destroying database 'bowtest' . . .





Destruction of database 'bowtest' completed successfully.



INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation

Ingres Microsoft Windows Version II 9.2.1 (int.w32/103) login

Thu Aug 26 11:04:05 2010





From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 26 August 2010 10:30
To: Ingres and related product discussion forum
Subject: [Info-Ingres] sequence owner is not defaulted in 9.2.1



Hi All,



Having recently upgraded a host to 9.2.1, I was somewhat astounded to
see my users break their application on startup. It seems that in 9.2.1
the sequence owner is no longer defaulted to the database owner (as per
tables, etc...)



The default of database owner is on 9.0.4, 9.1.2, 9.2.0, and even on a
10.0.0 site I have.



Martin Bowes



Try this....It won't break the installation.

sql iidbdb << SQL_END

create user martinb;

\p\g

create user martinc;

\p\g

SQL_END



createdb -umartinc bowtest



sql -umartinc bowtest << SQL_END

create sequence nextid;

\p\g

grant next on sequence nextid to user martinb;

\p\g

\q

SQL_END



# If the problem exists then the first select will generate error:

# E_US1914 RETRIEVE: Sequence 'nextid' referenced in next/current value
was

# not found.

sql -umartinb bowtest << SQL_END

select nextid.nextval;

\p\g

/*

** But this one with the owner present is OK.

*/

select martinc.nextid.nextval;

\p\g

\q

SQL_END



destroydb -umartinc bowtest

sql iidbdb << SQL_END

drop user martinb;

\p\g

drop user martinc;

\p\g

\q

SQL_END

Hi John,

I've just been told that this might be Bug 123638, which was introduced with the fix for bug 123351. (Users are allowed to
delete DBA owned sequences).

Marty

From: John Smedley [mailto:John.Smedley (AT) ingres (DOT) com]
Sent: 26 August 2010 11:15
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] sequence owner is not defaulted in 9.2.1

I have tried this against: Ingres v9.2.1 p13836 on Win32 and it works for me:


INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres Microsoft Windows Version II 9.2.1 (int.w32/103) login
Thu Aug 26 11:03:13 2010

continue
* * /* SQL Startup File */
create user martinb;
Executing . . .

continue
* * create user martinc;
Executing . . .

continue
*
Your SQL statement(s) have been committed.

Ingres Version II 9.2.1 (int.w32/103) logout
Thu Aug 26 11:03:13 2010
Creating database 'bowtest' . . .

Creating DBMS System Catalogs . . .
Modifying DBMS System Catalogs . . .
Creating Standard Catalog Interface . . .
Creating Front-end System Catalogs . . .

Creation of database 'bowtest' completed successfully.

INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres Microsoft Windows Version II 9.2.1 (int.w32/103) login
Thu Aug 26 11:03:53 2010

continue
* * /* SQL Startup File */
create sequence nextid;
Executing . . .

continue
* * grant next on sequence nextid to user martinb;
Executing . . .

continue
* Your SQL statement(s) have been committed.

Ingres Version II 9.2.1 (int.w32/103) logout
Thu Aug 26 11:03:54 2010
INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres Microsoft Windows Version II 9.2.1 (int.w32/103) login
Thu Aug 26 11:03:57 2010

continue
* * /* SQL Startup File */
select nextid.nextval;
Executing . . .


+-------------+
(1 row)
continue
* * * * * /*
** But this one with the owner present is OK.
*/
select martinc.nextid.nextval;
Executing . . .


+-------------+
(1 row)
continue
* Your SQL statement(s) have been committed.

Ingres Version II 9.2.1 (int.w32/103) logout
Thu Aug 26 11:03:58 2010
Destroying database 'bowtest' . . .


Destruction of database 'bowtest' completed successfully.

INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres Microsoft Windows Version II 9.2.1 (int.w32/103) login
Thu Aug 26 11:04:05 2010


From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 26 August 2010 10:30
To: Ingres and related product discussion forum
Subject: [Info-Ingres] sequence owner is not defaulted in 9.2.1

Hi All,

Having recently upgraded a host to 9.2.1, I was somewhat astounded to see my users break their application on startup. It seems that in 9.2.1 the sequence owner is no longer defaulted to the database owner (as per tables, etc....)

The default of database owner is on 9.0.4, 9.1.2, 9.2.0, and even on a 10.0..0 site I have.

Martin Bowes

Try this....It won't break the installation.
sql iidbdb << SQL_END
create user martinb;
\p\g
create user martinc;
\p\g
SQL_END

createdb -umartinc bowtest

sql -umartinc bowtest << SQL_END
create sequence nextid;
\p\g
grant next on sequence nextid to user martinb;
\p\g
\q
SQL_END

# If the problem exists then the first select will generate error:
# E_US1914 RETRIEVE: Sequence 'nextid' referenced in next/current value was
# not found.
sql -umartinb bowtest << SQL_END
select nextid.nextval;
\p\g
/*
** But this one with the owner present is OK.
*/
select martinc.nextid.nextval;
\p\g
\q
SQL_END

destroydb -umartinc bowtest
sql iidbdb << SQL_END
drop user martinb;
\p\g
drop user martinc;
\p\g
\q
SQL_END

Works on my 9.2.1 installations:-

$head -2 $II_SYSTEM/ingres/version.rel
II 9.2.1 (a64.lnx/103)NPTL
13827

$./run_bowestest
INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres Linux Version II 9.2.1 (a64.lnx/103)NPTL login
Thu Aug 26 11:29:15 2010
continue
* * /* SQL Startup File */
create user martinb;
Executing . . .
continue
* * create user martinc;
Executing . . .
continue
* Your SQL statement(s) have been committed.
Ingres Version II 9.2.1 (a64.lnx/103)NPTL logout
Thu Aug 26 11:29:15 2010
Creating database 'bowtest' . . .
Creating DBMS System Catalogs . . .
Modifying DBMS System Catalogs . . .
Creating Standard Catalog Interface . . .
Creating Front-end System Catalogs . . .
Creation of database 'bowtest' completed successfully.
INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres Linux Version II 9.2.1 (a64.lnx/103)NPTL login
Thu Aug 26 11:29:18 2010
continue
* * /* SQL Startup File */
create sequence nextid;
Executing . . .
continue
* * grant next on sequence nextid to user martinb;
Executing . . .
continue
* Your SQL statement(s) have been committed.
Ingres Version II 9.2.1 (a64.lnx/103)NPTL logout
Thu Aug 26 11:29:18 2010
INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres Linux Version II 9.2.1 (a64.lnx/103)NPTL login
Thu Aug 26 11:29:18 2010
continue
* * /* SQL Startup File */
select nextid.nextval;
Executing . . .

┌─────────────┐
│col1 │
├─────────────┤
│ 1│
└─────────────┘
(1 row)
continue
* * * * * /*
** But this one with the owner present is OK.
*/
select martinc.nextid.nextval;
Executing . . .

┌─────────────┐
│col1 │
├─────────────┤
│ 2│
└─────────────┘
(1 row)
continue
* Your SQL statement(s) have been committed.
Ingres Version II 9.2.1 (a64.lnx/103)NPTL logout
Thu Aug 26 11:29:18 2010
Destroying database 'bowtest' . . .

Destruction of database 'bowtest' completed successfully.
INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres Linux Version II 9.2.1 (a64.lnx/103)NPTL login
Thu Aug 26 11:29:19 2010
continue
* * /* SQL Startup File */
drop user martinb;
Executing . . .
continue
* * drop user martinc;
Executing . . .
continue
* Your SQL statement(s) have been committed.
Ingres Version II 9.2.1 (a64.lnx/103)NPTL logout
Thu Aug 26 11:29:19 2010