pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedprimary key view failure
8+ messages / 3 participants
[nested] [flat]
* primary key view failure
@ 1999-08-05 13:52 rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 14:26 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: rob caSSon @ 1999-08-05 13:52 UTC (permalink / raw)
To: pgsql-sql
i have a perfectly valid sql statement that returns expected results, but
when i create a view based on the same sql, i get 0. similar views all
work correctly:
an example (to return primary keys):
SELECT pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisprimary = 't';
relname |attname
---------------+-------
active_sessions|sid
auth_user |uid
arraytest |control
(3 rows)
but w/ this:
CREATE VIEW blah AS
SELECT pg_class.relname, pg_attribute.attname
FROM pg_class, pg_attribute, pg_index
WHERE pg_class.oid = pg_attribute.attrelid AND
pg_class.oid = pg_index.indrelid AND
pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisprimary = 't';
SELECT * FROM blah;
relname|attname
-------+-------
(0 rows)
any pointers....i've seen similar references to this in the list
archives, but no leads....never really thought of creating all of this
stuff as views....still pretty green, i guess.
tia,
rob
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] primary key view failure
1999-08-05 13:52 primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
@ 1999-08-05 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 15:24 ` Re: [SQL] primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 16:09 ` Re: [SQL] primary key view failure Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 1999-08-05 14:26 UTC (permalink / raw)
To: rob caSSon <rob@styro.lib.muohio.edu>; +Cc: pgsql-sql
rob caSSon <rob@styro.lib.muohio.edu> writes:
> i have a perfectly valid sql statement that returns expected results, but
> when i create a view based on the same sql, i get 0.
Your example seems to work fine for me with current sources.
What version are you running?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] primary key view failure
1999-08-05 13:52 primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 14:26 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-08-05 15:24 ` rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 15:47 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: rob caSSon @ 1999-08-05 15:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
should have sent this along in the first place....
using latest unstable debian package...6.5.1-4
(maybe i should break down and compile, although apt-get is wonderful)
can anyone replicate this?
tia,
rob
On Thu, 5 Aug 1999, Tom Lane wrote:
> rob caSSon <rob@styro.lib.muohio.edu> writes:
> > i have a perfectly valid sql statement that returns expected results, but
> > when i create a view based on the same sql, i get 0.
>
> Your example seems to work fine for me with current sources.
> What version are you running?
>
> regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] primary key view failure
1999-08-05 13:52 primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 14:26 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 15:24 ` Re: [SQL] primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
@ 1999-08-05 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 1999-08-05 15:47 UTC (permalink / raw)
To: rob caSSon <rob@styro.lib.muohio.edu>; +Cc: pgsql-sql
rob caSSon <rob@styro.lib.muohio.edu> writes:
> should have sent this along in the first place....
> using latest unstable debian package...6.5.1-4
OK, then it evidently is a bug or porting problem. Hard to say
what though; we need more evidence.
Is there any difference in the plans you get from "explain" of the
direct query and the view query? I get this either way:
Hash Join (cost=83.94 rows=19044 width=94)
-> Nested Loop (cost=53.62 rows=552 width=58)
-> Seq Scan on pg_index (cost=2.19 rows=1 width=20)
-> Seq Scan on pg_attribute (cost=51.43 rows=1104 width=38)
-> Hash (cost=6.55 rows=138 width=36)
-> Seq Scan on pg_class (cost=6.55 rows=138 width=36)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] primary key view failure
1999-08-05 13:52 primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 14:26 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-08-05 16:09 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-08-05 16:29 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Ross J. Reedstrom @ 1999-08-05 16:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: rob caSSon <rob@styro.lib.muohio.edu>; pgsql-sql
On Thu, Aug 05, 1999 at 10:26:16AM -0400, Tom Lane wrote:
> rob caSSon <rob@styro.lib.muohio.edu> writes:
> > i have a perfectly valid sql statement that returns expected results, but
> > when i create a view based on the same sql, i get 0.
>
> Your example seems to work fine for me with current sources.
> What version are you running?
>
> regards, tom lane
I just got the same results as Rob, with a 6.5.0 install, and a hopefully
helpful error message:
idas_proto=> SELECT * FROM blah;
relname|attname
-------+-------
(0 rows)
idas_proto=> \d blah
ERROR: get_ruledef of blah: unknown node type 111 in check_if_rte_used()
idas_proto=> select version();
version
--------------------------------------------------------------
PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3
(1 row)
idas_proto=>
So, is this fixed in 6.5.1, or is it something that needs backporting from
current?
Ross
P.S. Rob, good show, using an example pulling from the system tables: makes it
really easy for others to test.
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] primary key view failure
1999-08-05 13:52 primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 14:26 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 16:09 ` Re: [SQL] primary key view failure Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
@ 1999-08-05 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 17:57 ` Re: [SQL] primary key view failure Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 1999-08-05 16:29 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: rob caSSon <rob@styro.lib.muohio.edu>; pgsql-sql
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> On Thu, Aug 05, 1999 at 10:26:16AM -0400, Tom Lane wrote:
>> rob caSSon <rob@styro.lib.muohio.edu> writes:
>>>> i have a perfectly valid sql statement that returns expected results, but
>>>> when i create a view based on the same sql, i get 0.
>>
>> Your example seems to work fine for me with current sources.
>> What version are you running?
> I just got the same results as Rob, with a 6.5.0 install,
OK, I went back and tried it with 6.5.1 and indeed see the same failure
as Rob. So it's something that's been fixed in the current sources.
I dunno what --- but I did a *lot* of work on the parser's array support
last month, and that might have something to do with it...
> and a hopefully helpful error message:
> idas_proto=> \d blah
> ERROR: get_ruledef of blah: unknown node type 111 in check_if_rte_used()
That one is still there in current sources. Will clean it up.
> So, is this fixed in 6.5.1, or is it something that needs backporting from
> current?
A backport from current may not be too practical, unless someone wants
to try to identify exactly what's failing in 6.5.*. I bet it's an array
issue, but beyond that I'm clueless.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] primary key view failure
1999-08-05 13:52 primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 14:26 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 16:09 ` Re: [SQL] primary key view failure Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-08-05 16:29 ` Re: [SQL] primary key view failure Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-08-05 17:57 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 0 replies; 8+ messages in thread
From: Ross J. Reedstrom @ 1999-08-05 17:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Thu, Aug 05, 1999 at 12:29:34PM -0400, Tom Lane wrote:
>
> > and a hopefully helpful error message:
> > idas_proto=> \d blah
> > ERROR: get_ruledef of blah: unknown node type 111 in check_if_rte_used()
>
> That one is still there in current sources. Will clean it up.
>
> > So, is this fixed in 6.5.1, or is it something that needs backporting from
> > current?
>
> A backport from current may not be too practical, unless someone wants
> to try to identify exactly what's failing in 6.5.*. I bet it's an array
> issue, but beyond that I'm clueless.
>
Ah, I was being to hopeful, then. I went to get a look at what the view
had been parsed into, and got an error message pointing at a function,
and hoped that that would cause you to say, 'Ah ha! That's what's
wrong!' Well, at least we found a different (uncorrelated?) bug to
stomp :-|
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] primary key view failure
@ 1999-08-05 15:58 rob caSSon <rob@styro.lib.muohio.edu>
0 siblings, 0 replies; 8+ messages in thread
From: rob caSSon @ 1999-08-05 15:58 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
should have sent this along in the first place....
using latest unstable debian package...6.5.1-4
(maybe i should break down and compile, although apt-get is wonderful)
can anyone replicate this?
tia,
rob
On Thu, 5 Aug 1999, Tom Lane wrote:
> rob caSSon <rob@styro.lib.muohio.edu> writes:
> > i have a perfectly valid sql statement that returns expected results, but
> > when i create a view based on the same sql, i get 0.
>
> Your example seems to work fine for me with current sources.
> What version are you running?
>
> regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~1999-08-05 17:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-05 13:52 primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 15:24 ` rob caSSon <rob@styro.lib.muohio.edu>
1999-08-05 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 16:09 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-08-05 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 17:57 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-08-05 15:58 Re: [SQL] primary key view failure rob caSSon <rob@styro.lib.muohio.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox