#1262 When viewing the content of a table in PostgreSQL database, Squirrel-SQL select the column "oid" which does not exist.

[cshong87]

By default, in the latest version of PostgreSQL, the newly created table will not automatically contain "oid" column, unless you enable an option in PostgreSQL configuration.

I am using Liferay ( https://www.liferay.com/ ) with PostgreSQL. The tables created by Liferay do not contain "oid" column.

But, when using Squirrel-SQL to view the content of a table without "oid" column, Squirrel-SQL run an SQL script that query the "oid" column.

Here are the steps to reproduce the bug:
1. In PostgreSQL, create a table without oid column. Or, install Liferay and configure it to use PostgreSQL database.
2. Run Squirrel-SQL, connect to the PostgreSQL database.
3. Select a table without "oid" column, try to view the content without manually running SQL script.
4. Squirrel-SQL will automatically query the "oid" column even though it does not exist, and you will see an error on the screen.

Please read line 535 in the attached log file. Squirrel-SQL running "select tbl............, oid from ......" . I did not run the query manually. But, when I click on the "content" tab to read the content of the table, Squirrel-SQL try to run such query. The "oid" table does not even exist.

This issue only occur on Squirrel-SQL. I never face any problem when using other java based SQL client to view the database.

[cshong87]

As you can see in the attached screenshot, there is no "oid" column. But, an error occurred when I try to view the content.

[Gerd Wagner]

First off all I'm sorry but I can't reproduce your problem.

Here's a bit of technical information what we are doing:
Using straight forward JDBC we try to execute the following statements:

select tbl."uuid_",tbl."addressid",tbl."companyid",tbl."userid",tbl."username",tbl."createdate",tbl."modifieddate",tbl."classnameid",tbl."classpk",tbl."street1",tbl."street2",tbl."street3",tbl."city",tbl."zip",tbl."regionid",tbl."countryid",tbl."typeid",tbl."mailing",tbl."primary_", oid from "public"."address" tbl

select tbl."uuid_",tbl."addressid",tbl."companyid",tbl."userid",tbl."username",tbl."createdate",tbl."modifieddate",tbl."classnameid",tbl."classpk",tbl."street1",tbl."street2",tbl."street3",tbl."city",tbl."zip",tbl."regionid",tbl."countryid",tbl."typeid",tbl."mailing",tbl."primary_" from "public"."address" tbl

select * from "public"."address" tbl

Only when none of these works you see the error. It would be great if you had the chance to try out these statements against your database using plain JDBC. The code would look like that:

package pack;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCTest
{
   public static void main(String[] args) throws SQLException, ClassNotFoundException
   {
      Class.forName("<Your driver class name>");
      Connection con = DriverManager.getConnection("<Your database URL>", "<Your User>", "<Your password>");

      Statement stat = con.createStatement();

      // This line should reproduce the errors in your squirrel-sql.log.
      stat.executeQuery("<The SQLs mentioned above>");

   }
}

[Jjarmolowicz]

Hi Gerd,

I confirm that error exists. When content tab is opened for postgres table that doesn't contain oid than error is raised. Please note that oid column is optional so putting it as a hardcoded in select is not a good option.

[Vincent Verhoeven]

Gerd,

There is a problem with the fallback to other queries, specifically when autocommit is disabled. The first query (with oid) fails, but the next query reuses the (by now failed) transaction, and postgres doesn't allow this.

This makes the 'objects'-view unusable if you're on an oid-less postgres DB with autocommit disabled.