We are getting some weird behaviour - potentially from the PG JDBC driver???. Is anyone else wondering about how the Postgres JDBC driver is behaving (we are using the one that came with Postgres 3.1.x)? Unless something got updated with the 3.5 rc2 installer jar …
Initially, I raised a question under SQL Databases (see recent post - Error Creating Table PSQLException) and interacted with Marcus.
We are looking into the Servoy app server logs today and are beginning to wonder more about the JDBC driver (sorry, but “common” (?) symptoms are very intermittent and are ranging from changes to a solution not saving to Form Save errors (tables showing up as relations)). Does anyone recognize anything going on in this log extract? 3 errors shown here:
2007-06-20 08:51 http-9090-5 ERROR com.servoy.j2db.util.Debug Throwable com.servoy.j2db.persistence.RepositoryException: com.servoy.j2db.persistence.RepositoryException: root object not found at com.servoy.j2db.persistence.Repository.a(Unknown Source)
at com.servoy.j2db.persistence.Repository.a(Unknown Source)
at com.servoy.j2db.persistence.s.a(Unknown Source)
at com.servoy.j2db.persistence.Repository.getRootObject(Unknown Source)
at com.servoy.j2db.persistence.Repository.getActiveRootObjects(Unknown Source)
at com.servoy.j2db.server.servlets.ConfigServlet.a(Unknown Source)
at com.servoy.j2db.server.servlets.ConfigServlet.service(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:228)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:517)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:216)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:634)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:445)
at java.lang.Thread.run(Unknown Source)
Caused by: com.servoy.j2db.persistence.RepositoryException: root object not found
at com.servoy.j2db.persistence.Repository.a(Unknown Source)
… 21 more
2007-06-19 19:48 RMI TCP Connection(682)-192.168.10.195 ERROR com.servoy.j2db.util.Debug select distinct mail_city from tr_orgcon limit ? org.postgresql.util.PSQLException: ERROR: could not open relation with OID 19881 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)
at sun.reflect.GeneratedMethodAccessor123.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.servoy.j2db.persistence.datasource.p.invoke(Unknown Source)
at $Proxy0.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
2007-06-19 19:17 RMI TCP Connection(669)-139.142.250.128 ERROR com.servoy.j2db.util.Debug select orgcon_id from tr_orgcon where upper(lname1) like upper(?) order by lname1 desc limit ? org.postgresql.util.PSQLException: ERROR: could not open relation with OID 17013 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)
at sun.reflect.GeneratedMethodAccessor123.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.servoy.j2db.persistence.datasource.p.invoke(Unknown Source)
at $Proxy0.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Michael Mooney:
…(we are using the one that came with Postgres 3.1.x)? …
I guess you mean Servoy 3.1.x.
I use PostgreSQL for both my Servoy repository and my data.
I dunno what version of the PostgreSQL JDBC driver Servoy bundles with the installer. I tend to download the latest and greatest JDBC driver version from http://jdbc.postgresql.org/download.html to make sure it works with my Pg version (8.2.4).
In the past I had some issues when I upgraded Servoy and I was using a non-matching (version-wise) driver.
I haven’t had a problem (well except the issue just posted) since I kept the versions matched. I am using Servoy 2.2.x, 3.1.x and 3.5.
We are identifying potential issues in our Postgres release (8.2.1) and looking to move to 8.2.4. The release notes for 8.2.2 identify a problem with OID’s and relations. Quote from Postgres website:
Fix incorrect permission check in information_schema.key_column_usage view (Tom)
The symptom is “relation with OID nnnnn does not exist” errors. To get this fix without using initdb, use CREATE OR REPLACE VIEW to install the corrected definition found in share/information_schema.sql. Note you will need to do this in each database.
Will advise if other factors located. This note does not deal directly with the Root Object error in the logs previously shown, but potentially with the PG PSQLException: ERROR: could not open relation with OID xxxxx
Michael Mooney:
…(we are using the one that came with Postgres 3.1.x)? …
I guess you mean Servoy 3.1.x.
I use PostgreSQL for both my Servoy repository and my data.
I dunno what version of the PostgreSQL JDBC driver Servoy bundles with the installer. I tend to download the latest and greatest JDBC driver version from http://jdbc.postgresql.org/download.html to make sure it works with my Pg version (8.2.4).
In the past I had some issues when I upgraded Servoy and I was using a non-matching (version-wise) driver.
I haven’t had a problem (well except the issue just posted) since I kept the versions matched. I am using Servoy 2.2.x, 3.1.x and 3.5.
I usually use the JDBC3 release. Just make sure the version (first column) matches your PostgreSQL version.
I install them in the drivers directory of the servoy directory, but I guess you could install it /Library/Java/Extension as well.
I alos installed pgAdmin III and saw that with my install I got a default (Server > postgres). Now I thought I should create a new server for my project (called TrackIT), but get an error saying the db is missing.
Shouldn’t I create a new server for each (new) project?
Best regards, Robert
PS: What exactly is the Maintenance DB
ROCLASI:
Hi Robert,
I usually use the JDBC3 release. Just make sure the version (first column) matches your PostgreSQL version.
I install them in the drivers directory of the servoy directory, but I guess you could install it /Library/Java/Extension as well.
Template 1 contains everything that you want every database you create to have by default. If you want every database to have PGPSQL or PostGIS you make sure it is installed in template1 before you create database – then you dont have to install into each database individually.
You can create other templates, but that is really more for experts.
And you only need one Server for each physical server, not one for each project.
I read in the docu that template1 is for earlier versions than 8.2, now the template is postgres instead of template1.
So if I have a local installation (MacBook Pro) and one on a server (Mac OS X Server), then I should have 2 Servers defined in pgAdmin?
BTW: I can’t connect Servoy to the db, as I think the Database Server URL and/or Driver description is wrong.
The driver name is actually: postgresql-8.2-506.jdbc3.jar
Thanks, Robert
PS: I would like to use utf8 instead of the default sql_ascii encoding. How can I change that?
swingman:
Hi Robert,
your Maintenance DB should be template1.
Template 1 contains everything that you want every database you create to have by default. If you want every database to have PGPSQL or PostGIS you make sure it is installed in template1 before you create database – then you dont have to install into each database individually.
You can create other templates, but that is really more for experts.
And you only need one Server for each physical server, not one for each project.
Robert Huber:
I read in the docu that template1 is for earlier versions than 8.2, now the template is postgres instead of template1.
So if I have a local installation (MacBook Pro) and one on a server (Mac OS X Server), then I should have 2 Servers defined in pgAdmin?
Correct. You define one connection per server.
Robert Huber:
BTW: I can’t connect Servoy to the db, as I think the Database Server URL and/or Driver description is wrong.
The driver name is actually: postgresql-8.2-506.jdbc3.jar
That is one and the same.
But use the Auto Setup Server combobox at the bottom of the database connections window to create a new connection. This will pre-fill all the fields for you with the correct syntax.
The correct syntax is jdbc:postgresql://localhost/<database_name>
Robert Huber:
PS: I would like to use utf8 instead of the default sql_ascii encoding. How can I change that?
You can set the encoding when you create a database. In PgAdmin you have a combobox for that. In psql you use ```
CREATE DATABASE myDB ENCODING ‘UNICODE’
You can create database using existing ones (template1 is in fact just a normal DB) like so ```
CREATE DATABASE myDB TEMPLATE myOtherDB ENCODING 'UNICODE'
This will also (re)create any data that was in your other DB. Very handy when you want to duplicate a database.
More info at http://www.postgresql.org/docs/8.2/inte … abase.html .
In PgAdmin you simply select the database from the Template combobox.
I managed to connect to the PostgreSQL DB and even more important to me I managed the EOModeler (Apple’s Enterprise Object Modeler tool, free by the way to connect to the pg db, so I am quite happy as we have the whole db design in that tool and can now just create everything out of it
Thanks all of you for the help (more questions to come .-) Robert
ROCLASI:
Robert Huber:
I read in the docu that template1 is for earlier versions than 8.2, now the template is postgres instead of template1.
So if I have a local installation (MacBook Pro) and one on a server (Mac OS X Server), then I should have 2 Servers defined in pgAdmin?
Correct. You define one connection per server.
Robert Huber:
BTW: I can’t connect Servoy to the db, as I think the Database Server URL and/or Driver description is wrong.
The driver name is actually: postgresql-8.2-506.jdbc3.jar
That is one and the same.
But use the Auto Setup Server combobox at the bottom of the database connections window to create a new connection. This will pre-fill all the fields for you with the correct syntax.
The correct syntax is jdbc:postgresql://localhost/<database_name>
Robert Huber:
PS: I would like to use utf8 instead of the default sql_ascii encoding. How can I change that?
You can set the encoding when you create a database. In PgAdmin you have a combobox for that. In psql you use ```
CREATE DATABASE myDB ENCODING ‘UNICODE’
You can create database using existing ones (template1 is in fact just a normal DB) like so ```
CREATE DATABASE myDB TEMPLATE myOtherDB ENCODING 'UNICODE'
This will also (re)create any data that was in your other DB. Very handy when you want to duplicate a database.
More info at http://www.postgresql.org/docs/8.2/inte … abase.html .
In PgAdmin you simply select the database from the Template combobox.
make sure you get the Auto-Vacuum demon running. Maybe the 8.2.x installed does it for you be default. Then PostgreSQL will clean up and optimise your database automatically.
Thanks Robert, but where is the postgresql.conf file? I read it’s in /usr/local/pgsql/data but that’s the only folder which I have no access (although as a admin user I can change that . There is a menu Open postgresql.conf but as well I have to know where the file is.
Regards, Robert
PS: What’s the easiest way to export a solution and it’s data on the Server and import it on a MacBook, for example - to take the solution incl. data to Servoy world )
ROCLASI:
To enable autovacuum you need to set the following parameters in postgresql.conf.
Robert Huber:
Thanks Robert, but where is the postgresql.conf file? I read it’s in /usr/local/pgsql/data but that’s the only folder which I have no access (although as a admin user I can change that :-). There is a menu Open postgresql.conf but as well I have to know where the file is.
Like Christian said that directory is owned by the Postgres user. pgAdmin/Finder can’t access that directory from another user.
Just open a terminal and type the following:
sudo pico /usr/local/pgsql/data/postgresql.conf
``` and hit enter. Then type your root password in (if you had it enabled in NetInfo Manager). Now with the cursor keys or Control-Y (page up) and Control-V (page down) you can scroll thru the file. After changing the settings just hit Control-X (exit) and hit enter again when asking to save it as postgresql.conf.
After this restart PostgreSQL.
> Robert Huber:
> PS: What's the easiest way to export a solution and it's data on the Server and import it on a MacBook, for example - to take the solution incl. data to Servoy world :-))
Like Christian said you can use the command-line tools pg_dump/pg_dumpall and pg_restore but pgAdmin also has support for these tools under the Backup/Restore functionality (right-click on a database node).
Hope this helps.
I experimented with JDBC drivers and for example the postgresql-8.2-506.jdbc3.jar works - as Robert mentioned he uses this one, but postgresql-8.2-506.jdbc4.jar doesn’t work in my environment. Especially it does not work together with EOModeler.
I also try to use SQL2003 datatypes and have choosen integer , float [f], varchar(n) and timestamp [D] so far. Values in [ ] are my choosen corresponding JDBC Adaptor types. Sof far so good, it seems. But what to choose for what is in Sybase image - to store an image, icon, … I can’t get it to work. I am also interested in the JDBC Adatpor type as I have to specify that as well. Best regards, Robert
Those adapter types are these EOModeler things? I never worked with it.
Anyway for BLOB’s you use the Bytea datatype in PostgreSQL. Is that what you were looking for ?
The adaptor types are in fact called value types and are for the object relational mapping from EOModeler to Java Classes, so they should not come into play. I just was a bit unsure and thought it may still have an effect as when using the postgresql-8.2-506.jdbc3.jar JDBC driver, I don’t get the bytea data type. I only get the binary, bit, long binary or varbinary data type!? No idea why I don’t see the bytea
Regards, Robert
ROCLASI:
Hi Robert,
Those adapter types are these EOModeler things? I never worked with it.
Anyway for BLOB’s you use the Bytea datatype in PostgreSQL. Is that what you were looking for ?
I successfully managed to install PostgreSQL on my MacBook Pro locally, and connect pgAdmin3 and EOModeler to it with all of your help
Then I installed the db on our Mac OS X server, it seems successfully as well. But when I want to connect to it from my MacBook, I get the following message:
Error Message:
The server doesn’t grant access to the database: the server reports
FATAL: no pg_hba.conf entry for host “10.0.1.5”, user “trackit”, database “postgres”, SSL off
To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication). PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.
The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings. You’ll probably want to add something like
host all all 192.168.0.0/24 md5
This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24.
You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file. After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.
Can someone give me a hint what’s wrong here and what this pg_hba.conf file is used for?
Running to a meeting so I can’t give you a full response. There are a number of Google doc’s on line on this matter (type in the config file name into google). This file grants access. From the file:
