Unable to query the main table but able to query the individual shards

[Sanjana Sridhar]

Hello,

I'm suddenly having trouble with one of the tables I have sharded. I cannot directly query the table I get the following error - Could not receive query results. But if I access each individual shard and query it, I'm getting the results. Does anyone have any insight into why this might happen or how this can be fixed? Will be very helpful.

 

(All the other sharded tables in the database are fine and can be accessed)

Thank you.

[Jason Petersen]

On Aug 24, 2015, at 9:50 AM, Sanjana Sridhar <sanjana....@gmail.com> wrote:

I'm suddenly having trouble with one of the tables I have sharded. I cannot directly query the table I get the following error - Could not receive query results. But if I access each individual shard and query it, I'm getting the results. Does anyone have any insight into why this might happen or how this can be fixed? Will be very helpful.

Can you dump the relevant parts of your schema here (redacted or edited if necessary, I’m just looking for structure)? In addition, the output of the following queries would be great:

• SELECT relation_id::regclass AS table_name, partition_method, key FROM pgs_distribution_metadata.partition;
  
• SELECT relation_id::regclass AS table_name, s.id, storage, node_name, node_port, min_value || ' TO ' || max_value AS range  FROM pgs_distribution_metadata.shard s, pgs_distribution_metadata.shard_placement sp WHERE s.id = sp.shard_id;

That error appears when there is a problem fetching a result set from a worker during a SELECT query. Unfortunately, it doesn’t appear to log much else, so we may have to do some more investigation.

Are the hosts reachable from the master? Try using ping or similar to check…

The master connects to the worker hosts over TCP: can you use psql from the master to reach workers?

 (All the other sharded tables in the database are fine and can be accessed)

That’s really bizarre. At first I thought maybe your master was in some sort of read-only mode and so the TEMP tables used to stage worker results were having problems. But it sounds as though your other sharded tables work, so something weird must be going on…

--

Jason Petersen

Software Engineer | Citus Data

303.736.9255

ja...@citusdata.com

[Sanjana Sridhar]

(Forgot to reply to the group as well )

Hello Jason,

Thanks for getting back to me.

The results of the queries you provided. Table5 is the one giving me the problem. It does not interact with any other table in the database. I can use psql from the master to connect to the workers.

The schema for table5 is as follows:

table5

id      character(64)

data  json

The results of the queries:

SELECT relation_id::regclass AS table_name, partition_method, key FROM pgs_distribution_metadata.partition;

"table1";   "h";    "table1_id"

"table2";   "h";    "table2_id"

"table3";   "h";   "table3_id"

"table4";   "h";   "table4_id"

"table5";   "h";    "table5_id"  --> The table giving me the error

SELECT relation_id::regclass AS table_name, s.id, storage, node_name, node_port, min_value || ' TO ' || max_value AS range  FROM pgs_distribution_metadata.shard s, pgs_distribution_metadata.shard_placement sp WHERE s.id = sp.shard_id;

'table1'| 10066| 't'| '172.x.x.93'| 5432| '-2147483648 TO -1879048194'

'table1'| 10066| 't'| '172.x.x.221'| 5432| '-2147483648 TO -1879048194'

'table1'| 10067| 't'| '172.x.x.221'| 5432| '-1879048193 TO -1610612739'

'table1'| 10067| 't'| '172.x.x.93'| 5432| '-1879048193 TO -1610612739'

'table1'| 10068| 't'| '172.x.x.93'| 5432| '-1610612738 TO -1342177284'

'table1'| 10068| 't'| '172.x.x.221'| 5432| '-1610612738 TO -1342177284'

'table1'| 10069| 't'| '172.x.x.221'| 5432| '-1342177283 TO -1073741829'

'table1'| 10069| 't'| '172.x.x.93'| 5432| '-1342177283 TO -1073741829'

'table1'| 10070| 't'| '172.x.x.93'| 5432| '-1073741828 TO -805306374'

'table1'| 10070| 't'| '172.x.x.221'| 5432| '-1073741828 TO -805306374'

'table1'| 10071| 't'| '172.x.x.221'| 5432| '-805306373 TO -536870919'

'table1'| 10071| 't'| '172.x.x.93'| 5432| '-805306373 TO -536870919'

'table1'| 10072| 't'| '172.x.x.93'| 5432| '-536870918 TO -268435464'

'table1'| 10072| 't'| '172.x.x.221'| 5432| '-536870918 TO -268435464'

'table1'| 10073| 't'| '172.x.x.221'| 5432| '-268435463 TO -9'

'table1'| 10073| 't'| '172.x.x.93'| 5432| '-268435463 TO -9'

'table1'| 10074| 't'| '172.x.x.93'| 5432| '-8 TO 268435446'

'table1'| 10074| 't'| '172.x.x.221'| 5432| '-8 TO 268435446'

'table1'| 10075| 't'| '172.x.x.221'| 5432| '268435447 TO 536870901'

'table1'| 10075| 't'| '172.x.x.93'| 5432| '268435447 TO 536870901'

'table1'| 10076| 't'| '172.x.x.93'| 5432| '536870902 TO 805306356'

'table1'| 10076| 't'| '172.x.x.221'| 5432| '536870902 TO 805306356'

'table1'| 10077| 't'| '172.x.x.221'| 5432| '805306357 TO 1073741811'

'table1'| 10077| 't'| '172.x.x.93'| 5432| '805306357 TO 1073741811'

'table1'| 10078| 't'| '172.x.x.93'| 5432| '1073741812 TO 1342177266'

'table1'| 10078| 't'| '172.x.x.221'| 5432| '1073741812 TO 1342177266'

'table1'| 10079| 't'| '172.x.x.221'| 5432| '1342177267 TO 1610612721'

'table1'| 10079| 't'| '172.x.x.93'| 5432| '1342177267 TO 1610612721'

'table1'| 10080| 't'| '172.x.x.93'| 5432| '1610612722 TO 1879048176'

'table1'| 10080| 't'| '172.x.x.221'| 5432| '1610612722 TO 1879048176'

'table1'| 10081| 't'| '172.x.x.221'| 5432| '1879048177 TO 2147483647'

'table1'| 10081| 't'| '172.x.x.93'| 5432| '1879048177 TO 2147483647'

'table2'| 10082| 't'| '172.x.x.93'| 5432| '-2147483648 TO -1879048194'

'table2'| 10082| 't'| '172.x.x.221'| 5432| '-2147483648 TO -1879048194'

'table2'| 10083| 't'| '172.x.x.221'| 5432| '-1879048193 TO -1610612739'

'table2'| 10083| 't'| '172.x.x.93'| 5432| '-1879048193 TO -1610612739'

'table2'| 10084| 't'| '172.x.x.93'| 5432| '-1610612738 TO -1342177284'

'table2'| 10084| 't'| '172.x.x.221'| 5432| '-1610612738 TO -1342177284'

'table2'| 10085| 't'| '172.x.x.221'| 5432| '-1342177283 TO -1073741829'

'table2'| 10085| 't'| '172.x.x.93'| 5432| '-1342177283 TO -1073741829'

'table2'| 10086| 't'| '172.x.x.93'| 5432| '-1073741828 TO -805306374'

'table2'| 10086| 't'| '172.x.x.221'| 5432| '-1073741828 TO -805306374'

'table2'| 10087| 't'| '172.x.x.221'| 5432| '-805306373 TO -536870919'

'table2'| 10087| 't'| '172.x.x.93'| 5432| '-805306373 TO -536870919'

'table2'| 10088| 't'| '172.x.x.93'| 5432| '-536870918 TO -268435464'

'table2'| 10088| 't'| '172.x.x.221'| 5432| '-536870918 TO -268435464'

'table2'| 10089| 't'| '172.x.x.221'| 5432| '-268435463 TO -9'

'table2'| 10089| 't'| '172.x.x.93'| 5432| '-268435463 TO -9'

'table2'| 10090| 't'| '172.x.x.93'| 5432| '-8 TO 268435446'

'table2'| 10090| 't'| '172.x.x.221'| 5432| '-8 TO 268435446'

'table2'| 10091| 't'| '172.x.x.221'| 5432| '268435447 TO 536870901'

'table2'| 10091| 't'| '172.x.x.93'| 5432| '268435447 TO 536870901'

'table2'| 10092| 't'| '172.x.x.93'| 5432| '536870902 TO 805306356'

'table2'| 10092| 't'| '172.x.x.221'| 5432| '536870902 TO 805306356'

'table2'| 10093| 't'| '172.x.x.221'| 5432| '805306357 TO 1073741811'

'table2'| 10093| 't'| '172.x.x.93'| 5432| '805306357 TO 1073741811'

'table2'| 10094| 't'| '172.x.x.93'| 5432| '1073741812 TO 1342177266'

'table2'| 10094| 't'| '172.x.x.221'| 5432| '1073741812 TO 1342177266'

'table2'| 10095| 't'| '172.x.x.221'| 5432| '1342177267 TO 1610612721'

'table2'| 10095| 't'| '172.x.x.93'| 5432| '1342177267 TO 1610612721'

'table2'| 10096| 't'| '172.x.x.93'| 5432| '1610612722 TO 1879048176'

'table2'| 10096| 't'| '172.x.x.221'| 5432| '1610612722 TO 1879048176'

'table2'| 10097| 't'| '172.x.x.221'| 5432| '1879048177 TO 2147483647'

'table2'| 10097| 't'| '172.x.x.93'| 5432| '1879048177 TO 2147483647'

'table3'| 10098| 't'| '172.x.x.93'| 5432| '-2147483648 TO -1879048194'

'table3'| 10098| 't'| '172.x.x.221'| 5432| '-2147483648 TO -1879048194'

'table3'| 10099| 't'| '172.x.x.221'| 5432| '-1879048193 TO -1610612739'

'table3'| 10099| 't'| '172.x.x.93'| 5432| '-1879048193 TO -1610612739'

'table3'| 10100| 't'| '172.x.x.93'| 5432| '-1610612738 TO -1342177284'

'table3'| 10100| 't'| '172.x.x.221'| 5432| '-1610612738 TO -1342177284'

'table3'| 10101| 't'| '172.x.x.221'| 5432| '-1342177283 TO -1073741829'

'table3'| 10101| 't'| '172.x.x.93'| 5432| '-1342177283 TO -1073741829'

'table3'| 10102| 't'| '172.x.x.93'| 5432| '-1073741828 TO -805306374'

'table3'| 10102| 't'| '172.x.x.221'| 5432| '-1073741828 TO -805306374'

'table3'| 10103| 't'| '172.x.x.221'| 5432| '-805306373 TO -536870919'

'table3'| 10103| 't'| '172.x.x.93'| 5432| '-805306373 TO -536870919'

'table3'| 10104| 't'| '172.x.x.93'| 5432| '-536870918 TO -268435464'

'table3'| 10104| 't'| '172.x.x.221'| 5432| '-536870918 TO -268435464'

'table3'| 10105| 't'| '172.x.x.221'| 5432| '-268435463 TO -9'

'table3'| 10105| 't'| '172.x.x.93'| 5432| '-268435463 TO -9'

'table3'| 10106| 't'| '172.x.x.93'| 5432| '-8 TO 268435446'

'table3'| 10106| 't'| '172.x.x.221'| 5432| '-8 TO 268435446'

'table3'| 10107| 't'| '172.x.x.221'| 5432| '268435447 TO 536870901'

'table3'| 10107| 't'| '172.x.x.93'| 5432| '268435447 TO 536870901'

'table3'| 10108| 't'| '172.x.x.93'| 5432| '536870902 TO 805306356'

'table3'| 10108| 't'| '172.x.x.221'| 5432| '536870902 TO 805306356'

'table3'| 10109| 't'| '172.x.x.221'| 5432| '805306357 TO 1073741811'

'table3'| 10109| 't'| '172.x.x.93'| 5432| '805306357 TO 1073741811'

'table3'| 10110| 't'| '172.x.x.93'| 5432| '1073741812 TO 1342177266'

'table3'| 10110| 't'| '172.x.x.221'| 5432| '1073741812 TO 1342177266'

'table3'| 10111| 't'| '172.x.x.221'| 5432| '1342177267 TO 1610612721'

'table3'| 10111| 't'| '172.x.x.93'| 5432| '1342177267 TO 1610612721'

'table3'| 10112| 't'| '172.x.x.93'| 5432| '1610612722 TO 1879048176'

'table3'| 10112| 't'| '172.x.x.221'| 5432| '1610612722 TO 1879048176'

'table3'| 10113| 't'| '172.x.x.221'| 5432| '1879048177 TO 2147483647'

'table3'| 10113| 't'| '172.x.x.93'| 5432| '1879048177 TO 2147483647'

'table4'| 10114| 't'| '172.x.x.93'| 5432| '-2147483648 TO -1879048194'

'table4'| 10114| 't'| '172.x.x.221'| 5432| '-2147483648 TO -1879048194'

'table4'| 10115| 't'| '172.x.x.221'| 5432| '-1879048193 TO -1610612739'

'table4'| 10115| 't'| '172.x.x.93'| 5432| '-1879048193 TO -1610612739'

'table4'| 10116| 't'| '172.x.x.93'| 5432| '-1610612738 TO -1342177284'

'table4'| 10116| 't'| '172.x.x.221'| 5432| '-1610612738 TO -1342177284'

'table4'| 10117| 't'| '172.x.x.221'| 5432| '-1342177283 TO -1073741829'

'table4'| 10117| 't'| '172.x.x.93'| 5432| '-1342177283 TO -1073741829'

'table4'| 10118| 't'| '172.x.x.93'| 5432| '-1073741828 TO -805306374'

'table4'| 10118| 't'| '172.x.x.221'| 5432| '-1073741828 TO -805306374'

'table4'| 10119| 't'| '172.x.x.221'| 5432| '-805306373 TO -536870919'

'table4'| 10119| 't'| '172.x.x.93'| 5432| '-805306373 TO -536870919'

'table4'| 10120| 't'| '172.x.x.93'| 5432| '-536870918 TO -268435464'

'table4'| 10120| 't'| '172.x.x.221'| 5432| '-536870918 TO -268435464'

'table4'| 10121| 't'| '172.x.x.221'| 5432| '-268435463 TO -9'

'table4'| 10121| 't'| '172.x.x.93'| 5432| '-268435463 TO -9'

'table4'| 10122| 't'| '172.x.x.93'| 5432| '-8 TO 268435446'

'table4'| 10122| 't'| '172.x.x.221'| 5432| '-8 TO 268435446'

'table4'| 10123| 't'| '172.x.x.221'| 5432| '268435447 TO 536870901'

'table4'| 10123| 't'| '172.x.x.93'| 5432| '268435447 TO 536870901'

'table4'| 10124| 't'| '172.x.x.93'| 5432| '536870902 TO 805306356'

'table4'| 10124| 't'| '172.x.x.221'| 5432| '536870902 TO 805306356'

'table4'| 10125| 't'| '172.x.x.221'| 5432| '805306357 TO 1073741811'

'table4'| 10125| 't'| '172.x.x.93'| 5432| '805306357 TO 1073741811'

'table4'| 10126| 't'| '172.x.x.93'| 5432| '1073741812 TO 1342177266'

'table4'| 10126| 't'| '172.x.x.221'| 5432| '1073741812 TO 1342177266'

'table4'| 10127| 't'| '172.x.x.221'| 5432| '1342177267 TO 1610612721'

'table4'| 10127| 't'| '172.x.x.93'| 5432| '1342177267 TO 1610612721'

'table4'| 10128| 't'| '172.x.x.93'| 5432| '1610612722 TO 1879048176'

'table4'| 10128| 't'| '172.x.x.221'| 5432| '1610612722 TO 1879048176'

'table4'| 10129| 't'| '172.x.x.221'| 5432| '1879048177 TO 2147483647'

'table4'| 10129| 't'| '172.x.x.93'| 5432| '1879048177 TO 2147483647'

'table5'| 10130| 't'| '172.x.x.93'| 5432| '-2147483648 TO -1879048194'

'table5'| 10130| 't'| '172.x.x.221'| 5432| '-2147483648 TO -1879048194'

'table5'| 10131| 't'| '172.x.x.221'| 5432| '-1879048193 TO -1610612739'

'table5'| 10131| 't'| '172.x.x.93'| 5432| '-1879048193 TO -1610612739'

'table5'| 10132| 't'| '172.x.x.93'| 5432| '-1610612738 TO -1342177284'

'table5'| 10132| 't'| '172.x.x.221'| 5432| '-1610612738 TO -1342177284'

'table5'| 10133| 't'| '172.x.x.221'| 5432| '-1342177283 TO -1073741829'

'table5'| 10133| 't'| '172.x.x.93'| 5432| '-1342177283 TO -1073741829'

'table5'| 10134| 't'| '172.x.x.93'| 5432| '-1073741828 TO -805306374'

'table5'| 10134| 't'| '172.x.x.221'| 5432| '-1073741828 TO -805306374'

'table5'| 10135| 't'| '172.x.x.221'| 5432| '-805306373 TO -536870919'

'table5'| 10135| 't'| '172.x.x.93'| 5432| '-805306373 TO -536870919'

'table5'| 10136| 't'| '172.x.x.93'| 5432| '-536870918 TO -268435464'

'table5'| 10136| 't'| '172.x.x.221'| 5432| '-536870918 TO -268435464'

'table5'| 10138| 't'| '172.x.x.93'| 5432| '-8 TO 268435446'

'table5'| 10138| 't'| '172.x.x.221'| 5432| '-8 TO 268435446'

'table5'| 10139| 't'| '172.x.x.221'| 5432| '268435447 TO 536870901'

'table5'| 10139| 't'| '172.x.x.93'| 5432| '268435447 TO 536870901'

'table5'| 10140| 't'| '172.x.x.93'| 5432| '536870902 TO 805306356'

'table5'| 10140| 't'| '172.x.x.221'| 5432| '536870902 TO 805306356'

'table5'| 10142| 't'| '172.x.x.93'| 5432| '1073741812 TO 1342177266'

'table5'| 10142| 't'| '172.x.x.221'| 5432| '1073741812 TO 1342177266'

'table5'| 10143| 't'| '172.x.x.221'| 5432| '1342177267 TO 1610612721'

'table5'| 10143| 't'| '172.x.x.93'| 5432| '1342177267 TO 1610612721'

'table5'| 10144| 't'| '172.x.x.93'| 5432| '1610612722 TO 1879048176'

'table5'| 10144| 't'| '172.x.x.221'| 5432| '1610612722 TO 1879048176'

'table5'| 10145| 't'| '172.x.x.221'| 5432| '1879048177 TO 2147483647'

'table5'| 10145| 't'| '172.x.x.93'| 5432| '1879048177 TO 2147483647'

'table5'| 10137| 't'| '172.x.x.221'| 5432| '-268435463 TO -9'

'table5'| 10137| 't'| '172.x.x.93'| 5432| '-268435463 TO -9'

'table5'| 10141| 't'| '172.x.x.221'| 5432| '805306357 TO 1073741811'

'table5'| 10141| 't'| '172.x.x.93'| 5432| '805306357 TO 1073741811'

Yes as I was able to access all other tables except this one I was really confused as to what might be the cause of this error.

Thanks,

Sanjana

[Sanjana Sridhar]

Also an update regarding this problem:

I can access the details in the table if I query by the partition_id but general queries like SELECT * / SELECT count(*) FROM table5 do not work

On Tuesday, August 25, 2015 at 7:37:12 PM UTC-4, Jason Petersen wrote:

[Sanjana Sridhar]

Hello Jason,

Just wondering if you have any insight on this. I am now having the same problems with another table as well. 

Thanks,

Sanjana

On Tuesday, August 25, 2015 at 7:37:12 PM UTC-4, Jason Petersen wrote: