Thread: Re: [HACKERS] It would be nice if this could be fixed...
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
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,
Pennsylvania19026
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, Pennsylvania19026
> 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.
> > =================================================================
> > 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
Works now, thanks to Tom Lane:
test=> create table aaa(a text);CREATEtest=> create table bbb(b text);CREATEtest=> select coalesce(a,b) from
aaa,bbb;case----(0rows)test=> insert into aaa values('aaa');INSERT 19090 1test=> insert into bbb values('bbb');INSERT
190911test=> 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,
Pennsylvania19026