Re: COPY TO looses on view

I created a simple view from an existing table. I can select on the
view ok but COPY TO "does nothing" (no error, no output).

I did this under:
psql (PostgreSQL) 7.1
Linux payson 2.2.16 #1 Thu Oct 5 01:30:41 GMT 2000 ppc unknown
&
Linux mallard 2.4.0-4GB #1 Wed Jan 24 15:55:09 GMT 2001 i686 unknown

This is a "Minor Annoyance" since there is a work around (by creating
a temp table from the view; see below).

Thank you for your care and feeding of PostgreSQL!

-sp-

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
hbase=> create view foo as select * from subject;
hbase=> CREATE

hbase=> select count(*) from subject;
hbase=> count
-------
1372
(1 row)

hbase=> copy foo to stdout
hbase-> ;
hbase=> copy foo to stdout;
hbase=> copy subject to stdout;
00001 \N \N \N \N
00002 \N \N \N \N
...
04054 \N \N M \N
hbase=> select count(*) from foo;
hbase=> count
-------
1372
(1 row)

hbase=> CREATE TEMP TABLE snafu AS SELECT * from foo;
hbase=> SELECT

hbase=> select count(*) from snafu;
hbase=> count
-------
1372
(1 row)

hbase=> copy snafu to stdout
hbase-> ;
00001 \N \N \N \N
00002 \N \N \N \N
...
04054 \N \N M \N

Steve Pothier <pothiers(at)aries(dot)tucson(dot)saic(dot)com> writes:
> I created a simple view from an existing table. I can select on the
> view ok but COPY TO "does nothing" (no error, no output).

It should raise an error. Thanks for pointing that out.

regards, tom lane

Thank _you_.

I didn't see anything in the documentation under COPY TO that
indicated it should not work with views. Since you say it should
raise and error, the documentation should probably be updated too.

-Steve Pothier-

Date: Thu, 28 Jun 2001 10:56:29 -0400
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org

Steve Pothier <pothiers(at)aries(dot)tucson(dot)saic(dot)com> writes:
> I created a simple view from an existing table. I can select on the
> view ok but COPY TO "does nothing" (no error, no output).

It should raise an error. Thanks for pointing that out.

regards, tom lane

>
> Thank _you_.
>
> I didn't see anything in the documentation under COPY TO that
> indicated it should not work with views. Since you say it should
> raise and error, the documentation should probably be updated too.
>
> -Steve Pothier-
>
> Date: Thu, 28 Jun 2001 10:56:29 -0400
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: pgsql-bugs(at)postgresql(dot)org
>
> Steve Pothier <pothiers(at)aries(dot)tucson(dot)saic(dot)com> writes:
> > I created a simple view from an existing table. I can select on the
> > view ok but COPY TO "does nothing" (no error, no output).
>
> It should raise an error. Thanks for pointing that out.

OK, the following patch throws an error if you try to COPY TO/FROM
views.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, the following patch throws an error if you try to COPY TO/FROM
> views.

This is probably the wrong direction to approach it from: rather than
disallowing wrong things one at a time, we should only allow COPY if the
relkind is plain relation. Think about toast rels, indexes, ...

regards, tom lane

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, the following patch throws an error if you try to COPY TO/FROM
> > views.
>
> This is probably the wrong direction to approach it from: rather than
> disallowing wrong things one at a time, we should only allow COPY if the
> relkind is plain relation. Think about toast rels, indexes, ...

Yes, I thought about that. I tried indexes and it failed because of the
heap_open call. I wonder if heap_open() is the place for more checks,
not even COPY: Probably not.

In fact, there is no reason you shouldn't be able to COPY out a view,
except that it doesn't work. :-)

Here are the reltypes. I didn't want to start disabling them from COPY
until I was sure. SEQUENCE is invalidated in COPY FROM, but not COPY
TO. No idea why. Anyway, I guess I was trying to avoid the research to
do it right. I will add it to my list.

#define RELKIND_INDEX 'i' /* secondary index */
#define RELKIND_RELATION 'r' /* ordinary cataloged heap */
#define RELKIND_SPECIAL 's' /* special (non-heap) */
#define RELKIND_SEQUENCE 'S' /* SEQUENCE relation */
#define RELKIND_UNCATALOGED 'u' /* temporary heap */
#define RELKIND_TOASTVALUE 't' /* moved off huge values */
#define RELKIND_VIEW 'v' /* view */

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026