Thread: COPY TO looses on view

COPY TO looses on view

From
Steve Pothier
Date:
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

Re: COPY TO looses on view

From
Tom Lane
Date:
Steve Pothier <pothiers@aries.tucson.saic.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

Re: COPY TO looses on view

From
Steve Pothier
Date:
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@sss.pgh.pa.us>
   Cc: pgsql-bugs@postgresql.org

   Steve Pothier <pothiers@aries.tucson.saic.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

Re: COPY TO looses on view

From
Bruce Momjian
Date:
>
> 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@sss.pgh.pa.us>
>    Cc: pgsql-bugs@postgresql.org
>
>    Steve Pothier <pothiers@aries.tucson.saic.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: src/backend/commands/copy.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.139
diff -c -r1.139 copy.c
*** src/backend/commands/copy.c    2001/06/08 21:16:48    1.139
--- src/backend/commands/copy.c    2001/07/11 21:50:30
***************
*** 307,312 ****
--- 307,314 ----
      {                            /* copy from file to database */
          if (rel->rd_rel->relkind == RELKIND_SEQUENCE)
              elog(ERROR, "You cannot change sequence relation %s", relname);
+         if (rel->rd_rel->relkind == RELKIND_VIEW)
+             elog(ERROR, "You cannot copy view %s", relname);
          if (pipe)
          {
              if (IsUnderPostmaster)
***************
*** 330,335 ****
--- 332,339 ----
      }
      else
      {                            /* copy from database to file */
+         if (rel->rd_rel->relkind == RELKIND_VIEW)
+             elog(ERROR, "You cannot copy view %s", relname);
          if (pipe)
          {
              if (IsUnderPostmaster)

Re: COPY TO looses on view

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.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

Re: COPY TO looses on view

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026