BUG in 6.5 - GROUP BY inheritance

Chris Bitmead <chris.bitmead@bigfoot.com> writes:

This is from CVS of about a week ago.

SELECT question.title, count(comment.oid) FROM question,
category*,comment WHERE comment.webobject = question.oid and
question.category = category.oid GROUP BY question.title;

See my message from Sunday, "inherited GROUP BY is busted":

: It turns out that pretty much *anything* involving grouping or
: aggregation would fail if the query used inheritance, because the

This query seems to work with current sources (although EXPLAIN
fails for it --- I think that explain.c is out of sync with the
executor).

regards, tom lane

Ok, I upgraded my CVS sources yesterday, and simple inherited/GROUP BY
queries are working again. But a more complex one still seems broken.
This is the query...

httpd=> SELECT question.oid, question.title, question.summary,
question.datetime, category.oid, category.title, category.image, email,
webuser.name, count(comment.oid) FROM question, webuser,
category*,comment WHERE question.webuser = webuser.oid and
question.category = category.oid AND comment.webobject = question.oid
GROUP BY question.oid, question.title, question.summary,
question.datetime, category.oid, category.title, category.image, email,
webuser.name UNION SELECT question.oid, question.title,
question.summary, question.datetime, category.oid, category.title,
category.image, webuser.email, webuser.name, 0 FROM question, webuser,
category* WHERE question.webuser = webuser.oid and question.category =
category.oid;
ERROR: replace_vars_with_subplan_refs: variable not in target list

Tom Lane wrote:

Chris Bitmead <chris.bitmead@bigfoot.com> writes:

This is from CVS of about a week ago.

SELECT question.title, count(comment.oid) FROM question,
category*,comment WHERE comment.webobject = question.oid and
question.category = category.oid GROUP BY question.title;

See my message from Sunday, "inherited GROUP BY is busted":

: It turns out that pretty much *anything* involving grouping or
: aggregation would fail if the query used inheritance, because the

This query seems to work with current sources (although EXPLAIN
fails for it --- I think that explain.c is out of sync with the
executor).

regards, tom lane

--
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

Chris Bitmead <chris.bitmead@bigfoot.com> writes:

httpd=> SELECT question.oid, question.title, question.summary,
question.datetime, category.oid, category.title, category.image, email,
webuser.name, count(comment.oid) FROM question, webuser,
category*,comment WHERE question.webuser = webuser.oid and
question.category = category.oid AND comment.webobject = question.oid
GROUP BY question.oid, question.title, question.summary,
question.datetime, category.oid, category.title, category.image, email,
webuser.name UNION SELECT question.oid, question.title,
question.summary, question.datetime, category.oid, category.title,
category.image, webuser.email, webuser.name, 0 FROM question, webuser,
category* WHERE question.webuser = webuser.oid and question.category =
category.oid;
ERROR: replace_vars_with_subplan_refs: variable not in target list

Would it be possible to have some CREATE TABLE statements for these
tables, so that the problem can be reproduced without so much
reverse-engineering?

BTW, I do not know when "yesterday" your time might have been,
but I committed a critical fix for UNION + GROUP BY in
backend/optimizer/prep/prepunion.c at 10 Jun 02:55 EDT (-0400).
If you don't have that version please update and try again.

regards, tom lane

Just wondering, when I do a COPY in the latest CVS into a file, it makes
the file both where I ask it to AND in
/home/postgres/data/base/<filename>. I've tried removing both files and
doing it again, and it still happens. The one in /home/postgres looks
like some kind of binary file.

This is latest CVS.

Chris Bitmead <chris.bitmead@bigfoot.com> writes:

Just wondering, when I do a COPY in the latest CVS into a file, it makes
the file both where I ask it to AND in
/home/postgres/data/base/<filename>. I've tried removing both files and
doing it again, and it still happens. The one in /home/postgres looks
like some kind of binary file.

Can't reproduce it here. Anyone else seeing this?

If you do something like
COPY int4_tbl TO 'int4out';
you will get the file dumped into the database directory, because that
is the current working directory of the backend; you need to give a
full path in this form of the COPY command to get the file put someplace
more useful. But I don't see how COPY could produce two output files
when it's only doing one fopen()...

It occurs to me that it's a very dangerous thing for server-side COPY
to default to storing into the DB directory; pick the name of an
existing table and boom, you just overwrote your table. Maybe the
server-side COPY command ought to insist on being given an absolute
path?

regards, tom lane

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

--
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

At 02:16 PM 6/16/99 +1000, Chris Bitmead wrote:

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

In my experiences with other mailing lists - yes. If the
mailing list address shows up anywhere accessible by
web spiders, it will be found, and the list will be hit
by spam.

So I guess the question is really "which is worse, getting
hit by occassional spam or having to post from a subscribed
e-mail address?"

I have two e-mail addresses, one which I use for personal
e-mail and lists, so the answer's easy for me. Keep it
closed to subscribers.

If no one anywhere has the e-mail address of the list on
a spider-accesible web page, then opening it *might*
work unless...

Anyone ever posts the list address to Usenet.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, and other goodies at
http://donb.photo.net

On Wed, 16 Jun 1999, Chris Bitmead wrote:

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

There is a pgsql-loopback address you can subscribe to, so that you don't
have to be a member of every list, but, yes, it is required...

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

Well I reckon there should be a pseudo-subscription mechanism, where you
can register yourself as a contributor without actually getting a copy
of the email. I could subscribe under my several email addresses just so
that I can mail from all accounts but I don't really want to receive X
copies of everything. Is there a way to subscribe without getting
anything sent? A kind of suspended account I guess you would call it.

The Hermit Hacker wrote:

On Wed, 16 Jun 1999, Chris Bitmead wrote:

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

There is a pgsql-loopback address you can subscribe to, so that you don't
have to be a member of every list, but, yes, it is required...

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

--
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

At 02:16 PM 6/16/99 +1000, Chris Bitmead wrote:

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

In my experiences with other mailing lists - yes. If the
mailing list address shows up anywhere accessible by
web spiders, it will be found, and the list will be hit
by spam.

So I guess the question is really "which is worse, getting
hit by occassional spam or having to post from a subscribed
e-mail address?"

I have two e-mail addresses, one which I use for personal
e-mail and lists, so the answer's easy for me. Keep it
closed to subscribers.

If no one anywhere has the e-mail address of the list on
a spider-accesible web page, then opening it *might*
work unless...

Anyone ever posts the list address to Usenet.

I think there is a way to add all your e-mail addresses to the list
without getting mail to each address. Not sure how, though.

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

Well I reckon there should be a pseudo-subscription mechanism, where you
can register yourself as a contributor without actually getting a copy
of the email. I could subscribe under my several email addresses just so
that I can mail from all accounts but I don't really want to receive X
copies of everything. Is there a way to subscribe without getting
anything sent? A kind of suspended account I guess you would call it.

That's what loopback does.

The Hermit Hacker wrote:

On Wed, 16 Jun 1999, Chris Bitmead wrote:

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

There is a pgsql-loopback address you can subscribe to, so that you don't
have to be a member of every list, but, yes, it is required...

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

--
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

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

On Wed, 16 Jun 1999, Bruce Momjian wrote:

At 02:16 PM 6/16/99 +1000, Chris Bitmead wrote:

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

In my experiences with other mailing lists - yes. If the
mailing list address shows up anywhere accessible by
web spiders, it will be found, and the list will be hit
by spam.

So I guess the question is really "which is worse, getting
hit by occassional spam or having to post from a subscribed
e-mail address?"

I have two e-mail addresses, one which I use for personal
e-mail and lists, so the answer's easy for me. Keep it
closed to subscribers.

If no one anywhere has the e-mail address of the list on
a spider-accesible web page, then opening it *might*
work unless...

Anyone ever posts the list address to Usenet.

I think there is a way to add all your e-mail addresses to the list
without getting mail to each address. Not sure how, though.

Subscribe all addresses to loopback. It doesn't send mail out, it's
just a place for majordomo to look for ok addresses.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Thus spake Chris Bitmead

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

Spam is inconvenient. The term for mailing lists that don't do this
sort of blocking is "spam amplifier."

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

On Wed, 16 Jun 1999, Chris Bitmead wrote:

Well I reckon there should be a pseudo-subscription mechanism, where you
can register yourself as a contributor without actually getting a copy
of the email. I could subscribe under my several email addresses just so
that I can mail from all accounts but I don't really want to receive X
copies of everything. Is there a way to subscribe without getting
anything sent? A kind of suspended account I guess you would call it.

There is a pgsql-loopback address you can subscribe to, so that you
don't have to be a member of every list, but, yes, it is required...

The Hermit Hacker wrote:

On Wed, 16 Jun 1999, Chris Bitmead wrote:

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

There is a pgsql-loopback address you can subscribe to, so that you don't
have to be a member of every list, but, yes, it is required...

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

--
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

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

"D'Arcy" "J.M." Cain <darcy@druid.net> writes:

Spam is inconvenient. The term for mailing lists that don't do this
sort of blocking is "spam amplifier."

And, in fact, we were getting spammed pretty heavily until the blocking
solution was put in place last summer. See the mailing list archives.
We've been mostly spam-free since then, though.

I for one would object to any thought of taking out the block. The
loopback list seems to me to solve any problems that legitimate users
would have ...

regards, tom lane

At 9:16 PM -0700 6/15/99, Chris Bitmead wrote:

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

I don't know how postgres does it exactly, but NetBSD has open lists and
some aggressive kind of spam filtering based on known spammers. I think
it's been very nice to have discussions, for example, which span both the
port-mac68k@netbsd.org list and e.g. a MkLinux list. The spam filtering
seems to be very effective since I only see a piece of spam on the netbsd
lists once every 3 months or so. I would suggest you consider cooperating
with them to use whatever mechanism they use.

As another specific example there was a person on port-mac68k who was
getting postgres up on a Mac 68k system and having some problems. I tried
to cross-post my responses to the postgres-ports lists, but they never
appeared. I was doing it from this very email address on this very
machine. Aside from the 'now' - 'current' problem I reported earlier, his
were mostly related to interactions among the NetBSD installation, the
NetBSD package system and our install instructions rather than to any
fundamental deficiencies in postgres. However I think he had valid
problems which I think the Postgres documenters could address, and which
would improve our product. I think it is unfortunate that I was not able
to involve one of the postgres lists in the discussion.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu

On Wed, 16 Jun 1999, Henry B. Hotz wrote:

As another specific example there was a person on port-mac68k who was
getting postgres up on a Mac 68k system and having some problems. I tried
to cross-post my responses to the postgres-ports lists, but they never
appeared.

Curious about this, since if its rejected as "non-member submission", it
will get sent back to you...

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

This is a test to make sure I really can post to the ports list.

At 5:23 AM -0700 6/17/99, The Hermit Hacker wrote:

On Wed, 16 Jun 1999, Henry B. Hotz wrote:

As another specific example there was a person on port-mac68k who was
getting postgres up on a Mac 68k system and having some problems. I tried
to cross-post my responses to the postgres-ports lists, but they never
appeared.

Curious about this, since if its rejected as "non-member submission", it
will get sent back to you...

I didn't get a bounce message.

The ports list should allow bug reports from anybody. Also the rest of
what I said still applies: I think it is nice to allow cross-posted
discussions among normally unrelated lists (the person with the problem
wouldn't have been a known subscriber and someone with a solution might be
a subscriber only of the non-postgres lists) and NetBSD has (or had a year
ago) a filtering mechanism that allows it while still effectively blocking
spam.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu

What is the status of nested structures?
Like

CREATE TABLE foo (x int4, y text);
CREATE TABLE bar (z foo, y foo);

Postgres doesn't complain but then again it doesn't seem to do anything
good with it. I can't remember seeing it mentioned in the docs anywhere
but I had the idea from somewhere that the postgres of old supported it.