PostgreSQL 9.3 is not returning existing rows with Ruby on Rails or Squirrel

postgresql

I dumped an existing PostgrSQL database (generating "Insert" statements), and imported it on another computer. The source runs Debian (UK) OS, the target Mint (FR) OS.

For some tables, when I try to visualise in Squirrel SQL, I get the following:

Squirrel "Row count" tab displays 351
Squirrel "Content" does not show anything
but executing the query "select * from myTable" returns all the lines

For the same table, when ruby tries to extract one record, it does not find it. But I can check that the record exists in Squirrel.

All indexes were rebuilt, no transaction pending.
The same data base is accessed with Ruby and Squirrel, with the same user.

What could be the reason, how to solve this blocking issue ?

Thanks,
Fred

Best Answer

The source of the issue was the name of one colunm of the table: PCF_index

psql generated "PCF_index" when exporting the schema. This caused trouble to Squirrel when trying to read it.

After the column is renamed pcf_index, the issue was solved.

Related Solutions

Postgresql – In PostgreSQL 9.3 , UNION VIEW with WHERE CLAUSE not taken into account

If the view isn't pushing down the WHERE predicates, maybe a db function that dynamically creates the necessary query and only included the Foreign Tables (or shards) that are necessary?

If you need distributed Postgres, have you looked into Postgres-XC? http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki

PostgreSQL 9.3 – Pagination and Counting Number of Pages

It is not doing an extra bitmap scan. It is doing a bitmap scan instead of the regular index scan.

Why is it doing the bitmap scan instead?

Because it thinks it will be faster that way. Without the LIMIT and the ORDER BY, it anticipates that using the bitmap scan will let it do the IO on the table heap in a more efficient manner. You can see if PostgreSQL is correct in this assessment by temporarily doing a set enable_bitmapscan TO off; (although caching effects may make that experiment difficult to interpret correctly.)

Also, the line:

Rows Removed by Index Recheck: 93006

Suggests that your work_mem setting might be too small to accommodate the bitmap efficiently.

Why does it show up in the explain plan as two entries rather than one?

The ordinary index scan is really two scans in one, a scan over the index, and then a nested look-up over the table. But these two parts are integrated, and only show up as one entry in the execution plan.

The bitmap case also has these two steps, but they are not integrated. It is possible to inject extra steps between them, such as a BitmapOr or a BitmapAnd. It reports them as two separate entries to accommodate this possibility.

Best Answer

Related Solutions

Postgresql – In PostgreSQL 9.3 , UNION VIEW with WHERE CLAUSE not taken into account

PostgreSQL 9.3 – Pagination and Counting Number of Pages

Related Question