[Info-Ingres] sequence owner is not defaulted in 9.2.1

Martin Bowes

unread,

Aug 26, 2010, 5:29:46 AM8/26/10

to Ingres and related product discussion forum

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

John Smedley

unread,

Aug 26, 2010, 6:14:49 AM8/26/10

to Ingres and related product discussion forum

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

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

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

|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 (int.w32/103) logout

Thu Aug 26 11:03:58 2010

Destroying database 'bowtest' . . .

Destruction of database 'bowtest' completed successfully.

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

Thu Aug 26 11:04:05 2010

Martin Bowes

unread,

Aug 26, 2010, 6:34:12 AM8/26/10

to Ingres and related product discussion forum, John Smedley

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

rthdavid

unread,

Aug 26, 2010, 6:38:45 AM8/26/10

to

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