Re: COPY is not working

Hi,

COPY is not working on latest HEAD?
"""
regression=# select * from a;
aa
----
32
56
(2 rows)

regression=# COPY a TO '/tmp/copy_test';
COPY 0
"""

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

2010/4/30 Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>:
> Hi,
>
> COPY is not working on latest HEAD?
> """
> regression=# select * from a;
>  aa
> ----
>  32
>  56
> (2 rows)
>
> regression=# COPY a TO '/tmp/copy_test';
> COPY 0
> """
>

ah! this is because COPY doesn't follow inherited tables... should it?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:

> COPY is not working on latest HEAD?
> """
> regression=# select * from a;
> aa
> ----
> 32
> 56
> (2 rows)
>
> regression=# COPY a TO '/tmp/copy_test';
> COPY 0
> """
>
> --

Please send the actual test pattern and your environment information
to reproduce the misbehavior. It works fine on my machine.

regression=# CREATE TABLE a (aa integer);
CREATE TABLE
regression=# INSERT INTO a VALUES(32), (56);
INSERT 0 2
regression=# select * from a;
aa
----
32
56
(2 rows)

regression=# COPY a TO '/tmp/copy_test';
COPY 2
regression=# \! cat /tmp/copy_test
32
56

$ uname -a
Linux xxx 2.6.29.4-167.fc11.x86_64 #1 SMP Wed May 27 17:27:08 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
$ postgres --version
postgres (PostgreSQL) 9.0beta1

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

On Fri, Apr 30, 2010 at 12:56 AM, Takahiro Itagaki
<itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> wrote:
>
> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
>
>> COPY is not working on latest HEAD?
>> """
>> regression=# select * from a;
>>  aa
>> ----
>>  32
>>  56
>> (2 rows)
>>
>> regression=# COPY a TO '/tmp/copy_test';
>> COPY 0
>> """
>>
>> --
>
> Please send the actual test pattern and your environment information
> to reproduce the misbehavior. It works fine on my machine.
>

it's the regression database generated by "make installcheck", there
is an "a" table that is parent table (it has children), seems like
COPY is not getting the data from the inherited tables but only from
the parent

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:

> ah! this is because COPY doesn't follow inherited tables... should it?

Yes. You can use "COPY (SELECT * FROM a) TO " instead to copy all tuples.

http://developer.postgresql.org/pgdocs/postgres/sql-copy.html
| COPY can only be used with plain tables, not with views.
| However, you can write COPY (SELECT * FROM viewname) TO ....

Should we add "or parent tables" after "not with views"?
To be exact, it would be "'COPY a parent table TO' only copies
tuples in the parent table and does not copy inherited child tables".

regression=# CREATE TABLE a (aa integer);
CREATE TABLE
regression=# CREATE TABLE b () INHERITS (a);
CREATE TABLE
regression=# INSERT INTO b VALUES(32), (56);
INSERT 0 2
regression=# select * from a;
aa
----
32
56
(2 rows)

regression=# COPY a TO '/tmp/copy_test';
COPY 0
regression=# COPY (SELECT * FROM a) TO '/tmp/copy_test';
COPY 2

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

On Fri, Apr 30, 2010 at 1:13 AM, Takahiro Itagaki
<itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> wrote:
>
> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
>
>> ah! this is because COPY doesn't follow inherited tables... should it?
>
> Yes. You can use "COPY (SELECT * FROM a) TO " instead to copy all tuples.
>
> http://developer.postgresql.org/pgdocs/postgres/sql-copy.html
> | COPY can only be used with plain tables, not with views.
> | However, you can write COPY (SELECT * FROM viewname) TO ....
>
> Should we add "or parent tables" after "not with views"?
> To be exact, it would be "'COPY a parent table TO' only copies
> tuples in the parent table and does not copy inherited child tables".
>

+1 on make this clear on the docs

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

2010/4/30 Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>:
> On Fri, Apr 30, 2010 at 1:13 AM, Takahiro Itagaki
> <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> wrote:
>>
>> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
>>
>>> ah! this is because COPY doesn't follow inherited tables... should it?
>>
>> Yes. You can use "COPY (SELECT * FROM a) TO " instead to copy all tuples.
>>
>> http://developer.postgresql.org/pgdocs/postgres/sql-copy.html
>> | COPY can only be used with plain tables, not with views.
>> | However, you can write COPY (SELECT * FROM viewname) TO ....
>>
>> Should we add "or parent tables" after "not with views"?
>> To be exact, it would be "'COPY a parent table TO' only copies
>> tuples in the parent table and does not copy inherited child tables".
>>
>
> +1 on make this clear on the docs

It works with parent table. Depends if the parent table have data or not.
COPY is more like SELECT FROM ONLY table, isn't it ?

>
> --
> Atentamente,
> Jaime Casanova
> Soporte y capacitación de PostgreSQL
> Asesoría y desarrollo de sistemas
> Guayaquil - Ecuador
> Cel. +59387171157
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> writes:
> COPY is not working on latest HEAD?

I've added this to the COPY notes section:

<para>
<command>COPY</command> only deals with the specific table named;
it does not copy data to or from child tables. Thus for example
<literal>COPY <replaceable class="parameter">table</> TO</literal>
shows the same data as <literal>SELECT * FROM ONLY <replaceable
class="parameter">table</></literal>. But <literal>COPY
(SELECT * FROM <replaceable class="parameter">table</>) TO ...</literal>
can be used to dump all of the data in an inheritance hierarchy.
</para>

regards, tom lane

On 4/30/2010 1:56 AM, Jaime Casanova wrote:
> 2010/4/30 Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>:
>> Hi,
>>
>> COPY is not working on latest HEAD?
>> """
>> regression=# select * from a;
>> aa
>> ----
>> 32
>> 56
>> (2 rows)
>>
>> regression=# COPY a TO '/tmp/copy_test';
>> COPY 0
>> """
>>
>
> ah! this is because COPY doesn't follow inherited tables... should it?

The TRUNCATE command's behavior was changed in that regard. What
TRUNCATE did in 8.3 is now TRUNCATE ONLY in 8.4. I don't see a reason
why COPY should not follow suit.

Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> On 4/30/2010 1:56 AM, Jaime Casanova wrote:
>> ah! this is because COPY doesn't follow inherited tables... should it?

> The TRUNCATE command's behavior was changed in that regard. What
> TRUNCATE did in 8.3 is now TRUNCATE ONLY in 8.4. I don't see a reason
> why COPY should not follow suit.

How about "because it's inappropriate for dump/reload, which is the main
use-case for COPY"? In any case it'd be weird for COPY FROM and COPY TO
to behave differently on this point, but I don't really see how COPY
FROM would do anything intelligent for inheritance.

regards, tom lane

On 5/3/2010 10:51 PM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>> On 4/30/2010 1:56 AM, Jaime Casanova wrote:
>>> ah! this is because COPY doesn't follow inherited tables... should it?
>
>> The TRUNCATE command's behavior was changed in that regard. What
>> TRUNCATE did in 8.3 is now TRUNCATE ONLY in 8.4. I don't see a reason
>> why COPY should not follow suit.
>
> How about "because it's inappropriate for dump/reload, which is the main
> use-case for COPY"? In any case it'd be weird for COPY FROM and COPY TO
> to behave differently on this point, but I don't really see how COPY
> FROM would do anything intelligent for inheritance.

Dump would have to switch to the more consistent syntax using the

COPY ONLY <table> ...

form. As simple as that.

You are right that COPY ONLY ... FROM makes no sense at all. But I would
consider that the corner case, not the defining general rule. One could
make exactly the save inconsistency argument about the fact that there
is a SELECT ... FROM ONLY, UPDATE ONLY and DELETE FROM ONLY, but no
INSERT INTO ONLY.

As of right now, all commands that retrieve or affect existing rows,
except for COPY, do honor the ONLY keyword to stop inheritance. And
while pg_dump may be one of the main users of COPY, I think there are
quite a few other. I don't really see why COPY is so special.

Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin