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

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


Mon, 26 Apr 1999 11:38:05 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
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) #



Mon, 26 Apr 1999 16:54:11 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] It would be nice if this could be fixed...
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 


Mon, 26 Apr 1999 13:39:44 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] It would be nice if this could be fixed...
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

Tue, 27 Apr 1999 10:07:41 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] It would be nice if this could be fixed...
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

Tue, 27 Apr 1999 10:18:00 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] It would be nice if this could be fixed...
=================================================================
                        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.

Sun, 02 May 1999 13:35:08 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] It would be nice if this could be fixed...
> 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

Mon, 03 May 1999 17:04:29 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] It would be nice if this could be fixed...
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

Thu, 06 May 1999 12:12:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] It would be nice if this could be fixed... 
> 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

Thu, 6 May 1999 23:05:10 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] It would be nice if this could be fixed...

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

Mon, 10 May 1999 12:52:57 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] It would be nice if this could be fixed...
> 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

Mon, 10 May 1999 17:33:13 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] It would be nice if this could be fixed...
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

Mon, 10 May 1999 23:54:16 +0000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] It would be nice if this could be fixed...

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

Mon, 10 May 1999 20:09:23 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] It would be nice if this could be fixed...