crash on new system views

Bruce Momjian <maillist@candle.pha.pa.us>

This query crashes the backend. pg_rules is a new system view:

BEGIN WORK;
DECLARE c_matches BINARY CURSOR FOR
SELECT count(*)
FROM pg_attrdef t1, pg_rules t2
WHERE t1.adrelid = t2.oid
END WORK;

Partial backtrace is below. It is crashing in the optimizer.

Bruce,

I'm getting the same here when testing your query.

The odd (maybe not?) thing is that views appear to have
a NULL oid for all tuples.

postgres=> select oid,usename from pg_user;
oid|usename
---+--------
|postgres
^^^

Thinking about it, a view would not actually have an OID for each
tuple as it's not a real table.

It also crashed without the BEGIN or DECLARE ...

postgres=> SELECT count(*) FROM pg_attrdef t1, pg_rules t2 WHERE
t1.adrelid = t2.oid;
pqReadData() -- backend closed the channel unexpectedly.

As usual I have no idea where to look for the problem!!

Keith.

Bruce Momjian <maillist@candle.pha.pa.us>

This query crashes the backend. pg_rules is a new system view:

BEGIN WORK;
DECLARE c_matches BINARY CURSOR FOR
SELECT count(*)
FROM pg_attrdef t1, pg_rules t2
WHERE t1.adrelid = t2.oid
END WORK;

Partial backtrace is below. It is crashing in the optimizer.

Bruce,

I'm getting the same here when testing your query.

The odd (maybe not?) thing is that views appear to have
a NULL oid for all tuples.

This has always been the case. Views don't have oid.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

Bruce Momjian <maillist@candle.pha.pa.us>

Bruce Momjian <maillist@candle.pha.pa.us>

This query crashes the backend. pg_rules is a new system view:

BEGIN WORK;
DECLARE c_matches BINARY CURSOR FOR
SELECT count(*)
FROM pg_attrdef t1, pg_rules t2
WHERE t1.adrelid = t2.oid
END WORK;

Partial backtrace is below. It is crashing in the optimizer.

Bruce,

I'm getting the same here when testing your query.

The odd (maybe not?) thing is that views appear to have
a NULL oid for all tuples.

This has always been the case. Views don't have oid.

OK, so we shouldn't be able to select a column called oid from
any view?

Keith.

The odd (maybe not?) thing is that views appear to have
a NULL oid for all tuples.

This has always been the case. Views don't have oid.

OK, so we shouldn't be able to select a column called oid from
any view?

That's an interesting question. Do we disallow the query? Comments?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

On Tue, 15 Sep 1998, Bruce Momjian wrote:

The odd (maybe not?) thing is that views appear to have
a NULL oid for all tuples.

This has always been the case. Views don't have oid.

OK, so we shouldn't be able to select a column called oid from
any view?

That's an interesting question. Do we disallow the query? Comments?

It sounds reasonable to me...considering that any other query on a
table which references a field that doesn't exist fails...no?

Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

The odd (maybe not?) thing is that views appear to have
a NULL oid for all tuples.

This has always been the case. Views don't have oid.

OK, so we shouldn't be able to select a column called oid from
any view?

That's an interesting question. Do we disallow the query? Comments?

I guess it would be nice if views could show the original oid for all non join or single table views.

Andreas

On Wed, 16 Sep 1998, Andreas Zeugswetter wrote:

The odd (maybe not?) thing is that views appear to have
a NULL oid for all tuples.

This has always been the case. Views don't have oid.

OK, so we shouldn't be able to select a column called oid from
any view?

That's an interesting question. Do we disallow the query? Comments?

I guess it would be nice if views could show the original oid for all
non join or single table views.

IMHO, then the oid should be included as part of the view
definition itself...even on a join operation, having the OID might be
useful...consider a case where you have a join of two tables such that the
select is something like:

select a.oid,a.field,b.field where a.field=b.field;

Having a.oid means that later I can just do:

select * from <view> where oid = <oid#>;

But I think the person setting up the view should be the one
explicitly including the oid value, not the system "guessing"...

I guess it would be nice if views could show the original oid for all
non join or single table views.

IMHO, then the oid should be included as part of the view
definition itself...even on a join operation, having the OID might be
useful...consider a case where you have a join of two tables such that the
select is something like:

select a.oid,a.field,b.field where a.field=b.field;

Having a.oid means that later I can just do:

select * from <view> where oid = <oid#>;

But I think the person setting up the view should be the one
explicitly including the oid value, not the system "guessing"...

While I perfectly understand your argument, I see a disadvantage, that the above
will show the oid column with the select * while on the original table the oid is hidden.

But besides:
regression=> create view tables as select oid, relname as tabname from pg_class;
ERROR: create: system attribute named "oid"

which is perfectly OK I think.

Andreas

IMHO, then the oid should be included as part of the view
definition itself...even on a join operation, having the OID might be
useful...consider a case where you have a join of two tables such that the
select is something like:

select a.oid,a.field,b.field where a.field=b.field;

Having a.oid means that later I can just do:

select * from <view> where oid = <oid#>;

But I think the person setting up the view should be the one
explicitly including the oid value, not the system "guessing"...

This is true. Especially if we look a bit ahead for functions
returning tuple sets, it might be possible in the future to
have a view that calls a functions to retrieve the data. But
this time all the data could be computed at runtime and there
is absolutely no OID the system can guess.

So if there should be one in another case, the creator of the
view must specify it. It must have a different name than oid
but I think this forces the creator to choose a name from
which the user can guess which oid it is in a join view.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #