Thread: primary key view failure
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.attrelidAND 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.attrelidAND 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
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
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
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.55rows=138 width=36) -> Seq Scan on pg_class (cost=6.55 rows=138 width=36)
regards, tom lane
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
"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
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