pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
primary 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