Re: Rule system goes weird with SELECT queries

Thanks for the reply. I'll look into setting up CVS -- I've
just
been using the distributed 7.0.2 actually.

Moreover, the situation is even a bit more confused for me.
When
I actually *execute* the 'insert into dummy', I get the
output
of only one select: the second one listed. Is there
something
about executing a list I don't know about, or is this also
old news??

++ kevin

Tom Lane wrote:

"Kevin O'Gorman" <kogorman@pacbell.net> writes:

If I define two rules for the same action, each with
a single select command, I wind up with two selects as
expected, but they are both cross-product selects on the
two tables. This is unexpected.

Rangetable leakage, sounds like --- the two queries are sharing the same
list of rangetable entries, and that's what the planner joins over. Not
sure if it's *good* that they share the same rtable list, or if that's a
bug. In any event, it's old news because current sources don't use the
rtable list to control joining; now there is a separate join tree, which
is definitely not shared. I get

regression=# create rule rule4a as on insert to dummy do instead select * from
d2;
CREATE
regression=# create rule rule4b as on insert to dummy do instead select * from
d3;
CREATE
regression=# explain insert into dummy values(1);
NOTICE: QUERY PLAN:

Seq Scan on d2 (cost=0.00..20.00 rows=1000 width=4)

NOTICE: QUERY PLAN:

Seq Scan on d3 (cost=0.00..20.00 rows=1000 width=4)

EXPLAIN

which looks fine. Can't check your other example, since the grammar
file hasn't been changed to allow it...

I'm not sure whether to recommend that you work from current CVS sources
or not. A couple weeks ago that's what I would have said, but Vadim is
halfway through integrating WAL changes and I'm not sure how stable the
tip really is. You could try the tip, and if it blows up fall back to
a dated retrieval from about 7-Oct. Or you could investigate the way
that the 7.0.* rewriter handles the rtable list for multiple queries,
but that's probably not a real profitable use of your time.

regards, tom lane

--
Kevin O'Gorman (805) 650-6274 mailto:kogorman@pacbell.net
Permanent e-mail forwarder:
mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
At school: mailto:kogorman@cs.ucsb.edu
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html

"There is a freedom lying beyond circumstance,
derived from the direct intuition that life can
be grounded upon its absorption in what is
changeless amid change"
-- Alfred North Whitehead

"Kevin O'Gorman" <kogorman@pacbell.net> writes:

When I actually *execute* the 'insert into dummy', I get the output of
only one select: the second one listed. Is there something about
executing a list I don't know about, or is this also old news??

If you're using psql then that doesn't surprise me, because psql submits
queries via PQexec, and PQexec is not capable of dealing with multiple
result sets per submitted query string --- its API provides no way to
handle that, so it just bit-buckets all but the last command result.

This would work OK in an app that uses PQsendQuery followed by a
PQgetResult loop, however. See
http://www.postgresql.org/devel-corner/docs/postgres/libpq-async.htm

I've been kind of wanting to update psql to use these lower-level
routines, but that item has been languishing in the to-do queue for
a couple years now...

regards, tom lane

Tom Lane wrote:

I'm not sure whether to recommend that you work from current CVS sources
or not. A couple weeks ago that's what I would have said, but Vadim is
halfway through integrating WAL changes and I'm not sure how stable the
tip really is. You could try the tip, and if it blows up fall back to
a dated retrieval from about 7-Oct. Or you could investigate the way
that the 7.0.* rewriter handles the rtable list for multiple queries,
but that's probably not a real profitable use of your time.

regards, tom lane

Well, I tried the tip of the tree today, and initdb fails to
complete,
so I tried going back to '7 Oct 2000 10:00:00 PST' and it's
better,
but regression tests fail on the rule system. It makes the
server die.
Since rules are what I want, this won't do.

I'm not familiar enough with CVS or your changelog system
well enough
to know a good way to find a time-point that might be stable
enough
for me. How would I find out where I need to be??

++ kevin

--
Kevin O'Gorman (805) 650-6274 mailto:kogorman@pacbell.net
Permanent e-mail forwarder:
mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
At school: mailto:kogorman@cs.ucsb.edu
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html

"There is a freedom lying beyond circumstance,
derived from the direct intuition that life can
be grounded upon its absorption in what is
changeless amid change"
-- Alfred North Whitehead

"Kevin O'Gorman" <kogorman@pacbell.net> writes:

so I tried going back to '7 Oct 2000 10:00:00 PST' and it's better,
but regression tests fail on the rule system. It makes the server
die. Since rules are what I want, this won't do.

Details? AFAIK, the system was operational on 7-Oct; I did not pick
that date out of the air. There was a broken version of the
expected/rules.out file in place right around then --- see
http://www.postgresql.org/cgi/cvsweb.cgi/pgsql/src/test/regress/expected/rules.out
But that'd just have caused a bogus comparison failure, not a server
crash. (What was *in the expected file* was a report of a server
crash :-(, so if you didn't look carefully at the diff you might've
gotten confused...)

If you want a more exact timestamp, try 7-Oct-2000 00:00 PDT which
predates the BEOS patch breakage, or 8-Oct-2000 00:00 PDT which follows
cleanup. If either of those fail on your system it'd be useful to know
about.

regards, tom lane

Tom Lane wrote:

"Kevin O'Gorman" <kogorman@pacbell.net> writes:

so I tried going back to '7 Oct 2000 10:00:00 PST' and it's better,
but regression tests fail on the rule system. It makes the server
die. Since rules are what I want, this won't do.

Details? AFAIK, the system was operational on 7-Oct; I did not pick
that date out of the air. There was a broken version of the
expected/rules.out file in place right around then --- see
http://www.postgresql.org/cgi/cvsweb.cgi/pgsql/src/test/regress/expected/rules.out
But that'd just have caused a bogus comparison failure, not a server
crash. (What was *in the expected file* was a report of a server
crash :-(, so if you didn't look carefully at the diff you might've
gotten confused...)

If you want a more exact timestamp, try 7-Oct-2000 00:00 PDT which
predates the BEOS patch breakage, or 8-Oct-2000 00:00 PDT which follows
cleanup. If either of those fail on your system it'd be useful to know
about.

regards, tom lane

It's odd. I had already tried "8 Oct 2000 10:00:00 PDT" on
one system
(RedHat Linux 6.1), and it had worked. Today I'm building
on a Caldera
2.3 system, and both the 00:00 and 10:00 builds fail.

I've attached the output of the make. Could I have a bad
copy of this
source file? How could I tell (not knowing much about CVS,
I'm
disinclined to perform random experiments).

++ kevin

--
Kevin O'Gorman (805) 650-6274 mailto:kogorman@pacbell.net
Permanent e-mail forwarder:
mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
At school: mailto:kogorman@cs.ucsb.edu
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html

"There is a freedom lying beyond circumstance,
derived from the direct intuition that life can
be grounded upon its absorption in what is
changeless amid change"
-- Alfred North Whitehead

"Kevin O'Gorman" <kogorman@pacbell.net> writes:

It's odd. I had already tried "8 Oct 2000 10:00:00 PDT" on one system
(RedHat Linux 6.1), and it had worked. Today I'm building on a
Caldera 2.3 system, and both the 00:00 and 10:00 builds fail.

Hm. Portability bug maybe? But I can't tell with no info.

I've attached the output of the make.

Uh, it looked more like an amazon.com search from here...

regards, tom lane

Tom Lane wrote:

"Kevin O'Gorman" <kogorman@pacbell.net> writes:

It's odd. I had already tried "8 Oct 2000 10:00:00 PDT" on one system
(RedHat Linux 6.1), and it had worked. Today I'm building on a
Caldera 2.3 system, and both the 00:00 and 10:00 builds fail.

Hm. Portability bug maybe? But I can't tell with no info.

I've attached the output of the make.

Uh, it looked more like an amazon.com search from here...

regards, tom lane

Uh, so it does. How embarassing. I've been having MORE trouble
with Netscape... Anyway, here it is again.

In the meantime, I did a diff with the version on a system that
made okay, and there are no source differences in the pg_backup_custom.c
file.

If we get browser junk again, here is the tail of the file
via cut-and-paste; there are about 100 lines of error output total:

pg_backup_custom.c: In function `_DoDeflate':
pg_backup_custom.c:846: `z_streamp' undeclared (first use in this function)
pg_backup_custom.c:846: parse error before `zp'
pg_backup_custom.c:849: `ctx' undeclared (first use in this function)
pg_backup_custom.c:852: `AH' undeclared (first use in this function)
pg_backup_custom.c:854: `zp' undeclared (first use in this function)
pg_backup_custom.c:854: `flush' undeclared (first use in this function)
pg_backup_custom.c: In function `_EndDataCompressor':
pg_backup_custom.c:912: `ctx' undeclared (first use in this function)
pg_backup_custom.c:912: parse error before `)'
pg_backup_custom.c:913: `z_streamp' undeclared (first use in this function)
pg_backup_custom.c:918: `zp' undeclared (first use in this function)
make[3]: *** [pg_backup_custom.o] Error 1
make[3]: Leaving directory `/usr/local/src/pgsql/src/bin/pg_dump'
make[2]: *** [all] Error 2
make[2]: Leaving directory `/usr/local/src/pgsql/src/bin'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/usr/local/src/pgsql/src'
make: *** [all] Error 2
[kevin@trixie pgsql]$ exit
Script done, file is typescript

++ kevin

--
Kevin O'Gorman (805) 650-6274 mailto:kogorman@pacbell.net
Permanent e-mail forwarder: mailto:Kevin.O'Gorman.64@Alum.Dartmouth.org
At school: mailto:kogorman@cs.ucsb.edu
Web: http://www.cs.ucsb.edu/~kogorman/index.html
Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html

"There is a freedom lying beyond circumstance,
derived from the direct intuition that life can
be grounded upon its absorption in what is
changeless amid change"
-- Alfred North Whitehead

"Kevin O'Gorman" <kogorman@pacbell.net> writes:

pg_backup_custom.c: In function `_DoDeflate':
pg_backup_custom.c:846: `z_streamp' undeclared (first use in this function)
pg_backup_custom.c:846: parse error before `zp'
pg_backup_custom.c:849: `ctx' undeclared (first use in this function)
pg_backup_custom.c:852: `AH' undeclared (first use in this function)
pg_backup_custom.c:854: `zp' undeclared (first use in this function)
pg_backup_custom.c:854: `flush' undeclared (first use in this function)
pg_backup_custom.c: In function `_EndDataCompressor':
pg_backup_custom.c:912: `ctx' undeclared (first use in this function)
pg_backup_custom.c:912: parse error before `)'
pg_backup_custom.c:913: `z_streamp' undeclared (first use in this function)
pg_backup_custom.c:918: `zp' undeclared (first use in this function)

Hmm. Looks like Philip neglected to see to it that pg_dump will compile
when libz is not present --- the "#include <zlib.h>" is properly ifdef'd
out, but the code isn't. Over to you, Philip ...

regards, tom lane