Re: [HACKERS] It would be nice if this could be fixed...

Marc G. Fournier wrote:

On Sat, 17 Apr 1999, Chris Bitmead wrote:

I'm not sure what you're getting at. Yep, you can include the oid field
if you rename it, but it would be nice if you could leave it alone.

A typical scenario is that you create some table and start using it.
Then you find you need some derived field (like quantity*price AS total)
or something. So you may rename say product table to productold, and
create a product view that is SELECT *, quantity*price AS total from
productold.

The problem then arises if your code uses oid, because a view can't have
a field called oid. I'm advocating that you be allowed to create views
that have a field called oid to avoid this problem.

As D'Arcy did ask...which oid would you want used? The one from table a,
or from Table b? They are two distinctly different numbers...the VIEW
itself doesn't have an OID assigned to its rows, only the physical tables
themselves...

Not exactly, because in his example there is only one table
used in the view. But I wonder what an OID from a view might
be good for? Under normal conditions, the OID is only good to
UPDATE/DELETE something that was first SELECTed and later
qualified by the application. But this is BAD design,
because any system attribute is DB specific and leads to
application portability problems. In any case, the primary
key should be used instead of a DB specific row identifier.
So the need of OID tells IMHO some insufficient database
layout.

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

On Mon, 26 Apr 1999, Jan Wieck wrote:

Marc G. Fournier wrote:

On Sat, 17 Apr 1999, Chris Bitmead wrote:

I'm not sure what you're getting at. Yep, you can include the oid field
if you rename it, but it would be nice if you could leave it alone.

A typical scenario is that you create some table and start using it.
Then you find you need some derived field (like quantity*price AS total)
or something. So you may rename say product table to productold, and
create a product view that is SELECT *, quantity*price AS total from
productold.

The problem then arises if your code uses oid, because a view can't have
a field called oid. I'm advocating that you be allowed to create views
that have a field called oid to avoid this problem.

As D'Arcy did ask...which oid would you want used? The one from table a,
or from Table b? They are two distinctly different numbers...the VIEW
itself doesn't have an OID assigned to its rows, only the physical tables
themselves...

Not exactly, because in his example there is only one table
used in the view. But I wonder what an OID from a view might

Wait, I thought his SELECT had a 'FROM a,b' clause in it...no? *raised
eyebrow* If not, I misread, apologies...

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

The Hermit Hacker wrote:

As D'Arcy did ask...which oid would you want used? The one from table a,
or from Table b?

Just like any situation where column names conflict, the answer is
"whichever one I say".

If I have a join then I would say
CREATE view productv as SELECT product.oid, product.name, mfr.name from
product, mfr where product.mfr = mfr.oid;

This is no different from any other case where you join two tables with
same column names. Only difference is that it doesn't work :-(.

They are two distinctly different numbers...the VIEW
itself doesn't have an OID assigned to its rows,

Exactly, so why prevent the user having a column called "oid"?

only the physical tables

themselves...

"D'Arcy J.M. Cain" wrote:

Thus spake Chris Bitmead

It would be much better if you could have an oid column in a view if you
want. Like
CREATE VIEW productv AS SELECT oid, * FROM product;

But that's not allowed. Any reason why?

Because the oid is not included in the view. Consider the following.

CREATE VIEW c AS SELECT a1, a2, b1, b2 FROM a, b WHERE a_key = b_key;

So which oid do you want, the one from table a or the one from table b?
You can, however, do this.

CREATE VIEW c AS SELECT a.oid AS a_oid, b.oid AS b_oid, a1, a2, b1, b2
FROM a, b WHERE a_key = b_key;

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

Jan Wieck wrote:

Not exactly, because in his example there is only one table
used in the view. But I wonder what an OID from a view might
be good for?

The problem with postgres, unlike other object models, is that you can't
add methods to objects, except by creating a new "object" called a view.
(Well I suppose you can write functions or something, but it's not
invisible to the user like a view).

So users start using base tables and their oids and doing SELECTs. Then
someone realises they need a "method" (like quantity * price AS total or
something), so they make a view, and they want to start using the view.
But they want to avoid changing references to "oid" to some new name in
the view.

Under normal conditions, the OID is only good to
UPDATE/DELETE something that was first SELECTed and later
qualified by the application. But this is BAD design,
because any system attribute is DB specific and leads to
application portability problems.

A unique identifier for an object is NOT Db specific in the object
database ODMG world. I want to use Postgres like a bad Object database,
not like a good RDBMS.

I'd like to put up a web page soon to list what needs to be done to
Postgres in order for it to support the Object Database Management Group
(ODMG) standard. The basic answer is "not a lot", but there are a few
things. One thing to understand is that for an object database, the oid
is absolutely fundamental.

Anyway, Postgres is portable, so by extension my app is portable if I
use it.

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

=================================================================
POSTGRESQL BUG REPORT TEMPLATE
=================================================================

Your name : Chris Bitmead
Your email address : chris@tech.com.au

System Configuration
---------------------
Architecture : Intel x86

Operating System : Linux 2.0.36

PostgreSQL version : Latest Snapshot as at May 2, 1999

Compiler used : gcc 2.7.2.3

Please enter a FULL description of your problem:
------------------------------------------------

COALESCE sql function causes postgres to CRASH!

e.g.

SELECT story.title,story.image, mfr.image FROM story, mfr where
story.category= mfr.oid;
title |image |image
--------------+------------------+--------------------
Canon |/icon/critique.jpg|/icon/canon.gif
Nikon | |/icon/nikon.gif
Olympus | |/icon/olympus.gif
New Arca | |/icon/arca-swiss.gif
New Hasselblad| |/icon/hasselblad.gif
(5 rows)

httpd=> SELECT story.title, COALESCE(story.image, mfr.image) FROM story,
mfr where story.category= mfr.oid;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.

COALESCE sql function causes postgres to CRASH!
httpd=> SELECT story.title, COALESCE(story.image, mfr.image)
httpd-> FROM story, mfr where story.category= mfr.oid;

This is a known problem which I was hoping someone would pick up and
try to fix. Not sure I'll have time to look at it before v6.5 is
released.

The problem is in combining columns from multiple tables in the
COALESCE result. There are commented-out examples in the regression
test which illustrate the "feature". Other features of COALESCE seem
to work OK...

- Tom

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

COALESCE sql function causes postgres to CRASH!
httpd=> SELECT story.title, COALESCE(story.image, mfr.image)
httpd-> FROM story, mfr where story.category= mfr.oid;

The problem is in combining columns from multiple tables in the
COALESCE result.

I see at least part of the problem: flatten_tlistentry forgets to
recurse into the 'expr' part of a CaseWhen node. There may be some
other contributing bugs in setrefs.c.

There are dozens of routines in the backend that know all about how to
walk a parse tree --- or, in some cases like this one, not quite all
about how to walk a parse tree :-(. I just spent some time yesterday
teaching a couple of other routines about ArrayRef nodes, for example,
and I've seen way too many other bugs of exactly this ilk.

I think it'd be a good idea to try to centralize this knowledge so that
there are fewer places to change to add a new node type. For example,
a routine that wants to examine all the Var nodes in a tree should be
able to look something like this:

if (IsA(node, Var))
{
process var node;
}
else
standard_tree_walker(node, myself, ...);

rather than having another copy of a bunch of error-prone boilerplate.

regards, tom lane

I think it'd be a good idea to try to centralize this knowledge so that
there are fewer places to change to add a new node type. For example,
a routine that wants to examine all the Var nodes in a tree should be
able to look something like this:

if (IsA(node, Var))
{
process var node;
}
else
standard_tree_walker(node, myself, ...);

rather than having another copy of a bunch of error-prone boilerplate.

That is an interesting idea. The current code clearly needs cleanup and
is error-prone.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Chris, any chance you can send a small reproducable test case for this,
with INSERT's and CREATE table. Thanks.

=================================================================
POSTGRESQL BUG REPORT TEMPLATE
=================================================================

Your name : Chris Bitmead
Your email address : chris@tech.com.au

System Configuration
---------------------
Architecture : Intel x86

Operating System : Linux 2.0.36

PostgreSQL version : Latest Snapshot as at May 2, 1999

Compiler used : gcc 2.7.2.3

Please enter a FULL description of your problem:
------------------------------------------------

COALESCE sql function causes postgres to CRASH!

e.g.

SELECT story.title,story.image, mfr.image FROM story, mfr where
story.category= mfr.oid;
title |image |image
--------------+------------------+--------------------
Canon |/icon/critique.jpg|/icon/canon.gif
Nikon | |/icon/nikon.gif
Olympus | |/icon/olympus.gif
New Arca | |/icon/arca-swiss.gif
New Hasselblad| |/icon/hasselblad.gif
(5 rows)

httpd=> SELECT story.title, COALESCE(story.image, mfr.image) FROM story,
mfr where story.category= mfr.oid;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Chris, any chance you can send a small reproducable test case for this,
with INSERT's and CREATE table. Thanks.

COALESCE sql function causes postgres to CRASH!
e.g.
httpd=> SELECT story.title, COALESCE(story.image, mfr.image) FROM story,
mfr where story.category= mfr.oid;

Not necessary. This was a known problem documented in the regression
tests, and Tom Lane just fixed it a day or two ago. The problem was
with including more than one table in a COALESCE or CASE expression
result.

- Tom

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

Bruce Momjian wrote:

Chris, any chance you can send a small reproducable test case for this,
with INSERT's and CREATE table. Thanks.

Sure. Here it is....

httpd=> create table aaa(a text);
CREATE
httpd=> create table bbb(b text);
CREATE
httpd=> select coalesce(a,b) from aaa,bbb;
case
----
(0 rows)

httpd=> insert into aaa values('aaa');
INSERT 84818 1
httpd=> insert into bbb values('bbb');
INSERT 84819 1
httpd=> select coalesce(a,b) from aaa,bbb;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.

Works now, thanks to Tom Lane:

test=> create table aaa(a text);
CREATE
test=> create table bbb(b text);
CREATE
test=> select coalesce(a,b) from aaa,bbb;
case
----
(0 rows)

test=> insert into aaa values('aaa');
INSERT 19090 1
test=> insert into bbb values('bbb');
INSERT 19091 1
test=> select coalesce(a,b) from aaa,bbb;
case
----
aaa
(1 row)

Bruce Momjian wrote:

Chris, any chance you can send a small reproducable test case for this,
with INSERT's and CREATE table. Thanks.

Sure. Here it is....

httpd=> create table aaa(a text);
CREATE
httpd=> create table bbb(b text);
CREATE
httpd=> select coalesce(a,b) from aaa,bbb;
case
----
(0 rows)

httpd=> insert into aaa values('aaa');
INSERT 84818 1
httpd=> insert into bbb values('bbb');
INSERT 84819 1
httpd=> select coalesce(a,b) from aaa,bbb;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.

=================================================================
POSTGRESQL BUG REPORT TEMPLATE
=================================================================

Your name : Chris Bitmead
Your email address : chris@tech.com.au

System Configuration
---------------------
Architecture : Intel x86

Operating System : Linux 2.0.36

PostgreSQL version : Latest Snapshot as at May 2, 1999

Compiler used : gcc 2.7.2.3

Please enter a FULL description of your problem:
------------------------------------------------

COALESCE sql function causes postgres to CRASH!

e.g.

SELECT story.title,story.image, mfr.image FROM story, mfr where
story.category= mfr.oid;
title |image |image
--------------+------------------+--------------------
Canon |/icon/critique.jpg|/icon/canon.gif
Nikon | |/icon/nikon.gif
Olympus | |/icon/olympus.gif
New Arca | |/icon/arca-swiss.gif
New Hasselblad| |/icon/hasselblad.gif
(5 rows)

httpd=> SELECT story.title, COALESCE(story.image, mfr.image) FROM story,
mfr where story.category= mfr.oid;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.

--
Bruce Momjian                        |  http://www.op.net/~candle
maillist@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

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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