Menu
▾
▴
Re: [Postgres-xc-bugs] Copy Command
|
From: West, W. <ww...@uc...> - 2013-09-24 22:38:38
|
All,
OK since copy is working on your end, I assumed that there might have been some corruption on the original install. I also thought I would simplify the configuration by just installing on a single node initially and adding the second node later.
I reinstalled version 1.0.1 on a single node containing 1 GTM, 1 coordinator and 1 datanode. I started it up and added my table called staging.mutect. I then queried tables, pg_class and pgxc_class with the following results:
postgres=# select * from pgxc_class;
pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets | nodeoids
---------+---------------+----------+-----------------+---------------+----------
24578 | H | 1 | 1 | 4096 | 16384
(1 row)
postgres=# select * from information_schema.tables where table_name = 'mutect';
table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_in
sertable_into | is_typed | commit_action
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+------
--------------+----------+---------------
postgres | staging | mutect | BASE TABLE | | | | | | YES
| NO |
(1 row)
postgres=# select * from pg_class where relname = 'mutect';
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence |
relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+-
--------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+--------+------------
mutect | 24576 | 24580 | 0 | 10 | 0 | 24578 | 0 | 0 | 0 | 24581 | 0 | f | f | p |
r | 26 | 0 | f | f | f | f | f | 10336 | |
(1 row)
It show up in all three tables. With confidence I then reran my copy command and to my great disappointment I received the same error:
\copy staging.mutect from '/tmp/20.mutect.call_stats.out';
ERROR: relation "mutect" does not exist
I am not a C programmer so I am at a loss here. I am guessing that COPY.h queries some table other than tables, pg_class or pgxc_class to get the relation information it needs to proceed and that is where the exception is being thrown. Does anyone on the team have any familiarity with this code so they could send me the table name?
Absent that, are there any other bulk loading utilities for Postgres-xc (even 3rd party apps) that might work for mr or should I revert to an earlier and possibly more stable version?
Thanks again,
Bill West
From: <West>, William West <ww...@uc...<mailto:ww...@uc...>>
Date: Monday, September 23, 2013 10:03 AM
To: Koichi Suzuki <koi...@gm...<mailto:koi...@gm...>>
Cc: "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>
Subject: Re: [Postgres-xc-bugs] Copy Command
Thanks Koichi,
I checked pgxc_class on both nodes. The table appears in the node I created it as seen highlighted in this query result:
postgres=# select * from pgxc_class;
pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets | nodeoids
---------+---------------+----------+-----------------+---------------+-------------
16387 | R | 0 | 0 | 0 | 16384
16396 | H | 1 | 1 | 4096 | 16384
16495 | H | 1 | 1 | 4096 | 16384 16385
16501 | H | 1 | 1 | 4096 | 16384 16385
16544 | H | 1 | 1 | 4096 | 16384 16385
24688 | R | 0 | 0 | 0 | 16384 16385
(6 rows)
When I look at my other node and run the same query I get:
postgres=# select * from pgxc_class;
pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets | nodeoids
---------+---------------+----------+-----------------+---------------+-------------
16386 | H | 1 | 1 | 4096 | 16384
24603 | H | 1 | 1 | 4096 | 16385 16384
24609 | H | 1 | 1 | 4096 | 16385 16384
24652 | H | 1 | 1 | 4096 | 16385 16384
32820 | R | 0 | 0 | 0 | 16385 16384
I believe the highlighted table above is the same table. I can query it on either node however I notice that the pcrelid is different on each node. Is this significant?
Regards,
Bill West
From: Koichi Suzuki <koi...@gm...<mailto:koi...@gm...>>
Date: Sunday, September 22, 2013 6:17 PM
To: William West <ww...@uc...<mailto:ww...@uc...>>
Cc: "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>
Subject: Re: [Postgres-xc-bugs] Copy Command
Sorry for the late response.
Could you check pgxc_class catalog to see if the table is registered to this catalog as well? Usually, if you've created the table with conventional CREATE TABLE command from one of the coordinators, the definition should have propagated to all the coordinators.
We're using COPY command in DBT-2 and does not have any significant problems.
---
Koichi Suzuki
2013/9/19 West, William <ww...@uc...<mailto:ww...@uc...>>
All,
I am attempting to use the copy command (as I previously did successfully in Postgres) to copy data from a file on the file system. When I try to upload data to the XC instance I get a 'relation does not exist' error. However if I check the metadata table, called tables, it is in there:
postgres=# select * from information_schema.tables where table_type = 'BASE TABLE';
table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog |
user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action
---------------+--------------------+-------------------------+------------+------------------------------+----------------------+---------------------------+
--------------------------+------------------------+--------------------+----------+---------------
postgres | staging | mutect | BASE TABLE | | | |
| | YES | NO |
postgres | staging | vcf | BASE TABLE | | | |
| | YES | NO |
postgres | information_schema | sql_features | BASE TABLE | | | |
| | YES | NO |
postgres | gene | vcf | BASE TABLE | | | |
| | YES | NO |
postgres | staging | source_downloads | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_cast | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_am | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_attrdef | BASE TABLE | | | |
| | YES | NO |
postgres | public | products2 | BASE TABLE | | | |
| | YES | NO |
postgres | information_schema | sql_sizing_profiles | BASE TABLE | | | |
| | YES | NO |
postgres | information_schema | sql_sizing | BASE TABLE | | | |
| | YES | NO |
postgres | information_schema | sql_parts | BASE TABLE | | | |
| | YES | NO |
postgres | information_schema | sql_packages | BASE TABLE | | | |
| | YES | NO |
postgres | information_schema | sql_languages | BASE TABLE | | | |
| | YES | NO |
postgres | information_schema | sql_implementation_info | BASE TABLE | | | |
| | YES | NO |
postgres | public | products | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_collation | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_seclabel | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_default_acl | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_foreign_table | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pgxc_group | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pgxc_node | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pgxc_class | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_foreign_server | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_foreign_data_wrapper | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_extension | BASE TABLE | | | |
| | YES | NO |
postgres | pg_catalog | pg_ts_template | BASE TABLE | | | |
| | YES | NO |
postgres=# \copy staging.mutect from '/tmp/20.mutect.call_stats.out';
ERROR: relation "mutect" does not exist
Is this a known bug (version 1.0.3) or is there a different command for Copy in XC? If it is a bug is there any work around for bulk loading data?
Thanks,
Bill West
------------------------------------------------------------------------------
LIMITED TIME SALE - Full Year of Microsoft Training For Just $49.99!
1,500+ hours of tutorials including VisualStudio 2012, Windows 8, SharePoint
2013, SQL 2012, MVC 4, more. BEST VALUE: New Multi-Library Power Pack includes
Mobile, Cloud, Java, and UX Design. Lowest price ever! Ends 9/20/13.
http://pubads.g.doubleclick.net/gampad/clk?id=58041151&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-bugs mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
|
