Bingo 1.8x PostgreSQL install bug

[vrodionov]

I have identified two bugs in the latest PostgreSQL version of Bingo (1.8.0beta.r1). All the testing was performed on Linux x64.

1. Bingo does not work properly if the schema name other than the default "bingo" is selected during install. Steps to reproduce:
   • During install, specify a non-default schema like so: ./bingo-pg-install.sh -pglibdir -schema someschema
   • Observe that "someschema" with all the functions and config tables is properly created
   • Create a table "someschema.molecules"
   • Attempt to create a Bingo index on "someschema.molecules":

create index bin_index on someschema.molecules using bingo_idx (mol someschema.molecule);

• Observe unexpected output: ERROR: error: bingo cursor access: internal error: can not prepare or open a cursor: relation "bingo.bingo_config" does not exist SQL state: XX000

2. Bingo won't create an index on a table located in another schema in the same database.

Assume Bingo is installed in the default schema, "bingo" Assume the target table is "someshema.compounds"

• The following statement:

create index bin_index on someschema.compounds using bingo_idx (mol bingo.molecule);

Produces the following error message:

ERROR: error: bingo cursor access: internal error: can not prepare or open a cursor: permission denied for schema bingo SQL state: XX000

However, if I copy the table "compounds" verbatim into schema "bingo", the index is produced as expected without any issues.

The permission error clearly does not have anything to do with actual permissions, as all the queries are run as postgres. I have verified that the account has full access to every object in the database.

It looks like the default schema name is either hardcoded somewhere, or is not properly picked up by the cartridge code.

[adkinsn]

Can someone provide an update on this? This is a problem we have encountered.

[vrodionov]

Here is a workaround for issue 2, Bingo being unable to create an index on a table located in another (non-"bingo") schema.

1. Create your table in the "bingo" schema and then index it. This produces the expected result. If you are not already committed to a specific database structure or do not care about your schema names, this is a perfectly acceptable solution.

2. You can create a table with the required structure in "bingo" schema, and then index it. Once this is done, you can copy this table to another schema like so:

CREATE TABLE yourschema.tablename (LIKE bingo.tablename INCLUDING ALL);

This LIKE/INCLUDING ALL procedure makes it possible to have a Bingo index on a table outside of "bingo" schema. Once the copy is done, you can DROP TABLE bingo.tablename CASCADE.

Note that workaround 2 becomes a bit tedious if you have any constraints such as foreign keys on your original table, and you are adding Bingo to an existing architecture. For this situation, my suggested pseudocode will look something like that:

CREATE TABLE bingo.tablename (LIKE yourschema.tablename INCLUDING ALL); CREATE INDEX bingo_index ON bingo.tablename USING bingo_idx (mol bingo.molecule); ALTER TABLE yourschema.tablename DROP ; <- you will have to do it one by one ALTER TABLE yourschema.tablename RENAME TO tablename_old; CREATE TABLE yourschema.tablename (LIKE bingo.tablename INCLUDING ALL); DROP TABLE bingo.tablename CASCADE; INSERT INTO yourschema.tablename SELECT * FROM yourschema.tablename_old; DROP TABLE yourschema.tablename_old CASCADE; ALTER TABLE yourschema.tablename ADD ; <- you will have to do it one by one

Note that one solution that does NOT work is moving a table created elsewhere to "bingo" schema. The following sequence of statements:

ALTER TABLE someschema.sometable SET SCHEMA bingo; CREATE INDEX bingo_index ON bingo.sometable USING bingo_idx (mol bingo.molecule);

...will duly produce the dreaded permissions error: bingo cursor access: internal error: can not prepare or open a cursor: permission denied for schema bingo SQL state: XX000

This indicates that somehow the history of the table is important (how???). I have tried limited debugging on Bingo code, and I do not see how either the "bingo.bingo_config" name is special, or how the history of the table creation could impact indexing. That being said, I hope the suggested workarounds are helpful. If it is not possible to fix the bug directly in Bingo code (it could be "downstream" from some bug in Postgres itself), maybe the solution could be just coding in this workaround to be transparent for the user?