Hi everyone,

I have a query including an inner join, which I execute to obtain a
ResultSet, but when I execute the following piece of code I get an error
message saying that it could not update the table because it could not
find the primary key for a table.
rs.first();
rs.updateString("gene", "TestGen");
rs.updateRow();

Here is my query:
SELECT gene_entropy.gene, gene_entropy.entropy, gene_entropy.iteration,
snp_entropy.snp_id
FROM clustering.gene_entropy INNER JOIN clustering.snp_entropy
USING(gene) limit 5;

And here is the stacktrace:
org.postgresql.util.PSQLException: Für die Tebelle
(clustering.gene_entropy konnte kein Primärschlüssel gefunden werden.
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpda teable(AbstractJdbc2ResultSet.java:1547)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkU pdateable(AbstractJdbc2ResultSet.java:2550)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update Value(AbstractJdbc2ResultSet.java:2796)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update String(AbstractJdbc2ResultSet.java:1309)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update String(AbstractJdbc2ResultSet.java:1394)
at
myapp.ui.event.DBTableFrameListener.actionPerforme d(DBTableFrameListener.java:406)
at
javax.swing.AbstractButton.fireActionPerformed(Abs tractButton.java:1995)
at
javax.swing.AbstractButton$Handler.actionPerformed (AbstractButton.java:2318)
at
javax.swing.DefaultButtonModel.fireActionPerformed (DefaultButtonModel.java:387)
at
javax.swing.DefaultButtonModel.setPressed(DefaultB uttonModel.java:242)
at
javax.swing.plaf.basic.BasicButtonListener.mouseRe leased(BasicButtonListener.java:236)
at
java.awt.AWTEventMulticaster.mouseReleased(AWTEven tMulticaster.java:272)
at java.awt.Component.processMouseEvent(Component.jav a:6038)
at
javax.swing.JComponent.processMouseEvent(JComponen t.java:3260)
at java.awt.Component.processEvent(Component.java:580 3)
at java.awt.Container.processEvent(Container.java:205 8)
at java.awt.Component.dispatchEventImpl(Component.jav a:4410)
at java.awt.Container.dispatchEventImpl(Container.jav a:2116)
at java.awt.Component.dispatchEvent(Component.java:42 40)
at
java.awt.LightweightDispatcher.retargetMouseEvent( Container.java:4322)
at
java.awt.LightweightDispatcher.processMouseEvent(C ontainer.java:3986)
at
java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:3916)
at java.awt.Container.dispatchEventImpl(Container.jav a:2102)
at java.awt.Window.dispatchEventImpl(Window.java:2429 )
at java.awt.Component.dispatchEvent(Component.java:42 40)
at java.awt.EventQueue.dispatchEvent(EventQueue.java: 599)
at
java.awt.EventDispatchThread.pumpOneEventForFilter s(EventDispatchThread.java:273)
at
java.awt.EventDispatchThread.pumpEventsForFilter(E ventDispatchThread.java:183)
at
java.awt.EventDispatchThread.pumpEventsForHierarch y(EventDispatchThread.java:173)
at
java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:168)
at
java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:160)
at
java.awt.EventDispatchThread.run(EventDispatchThre ad.java:121)

I am more than happy and thankful for any advice!



Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Just to clarify: there is of course a primary key and it's included in
the select.
Anyway, I'm still happy for any advice ;-)


Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230




>>> On Wed, Oct 10, 2007 at 2:26 PM, in message
<470CE177.90AD.0080.0@klinik.uni-regensburg.de>, "Christian Rengstl"
<Christian.Rengstl@klinik.uni-regensburg.de> wrote:
> Hi everyone,
>
> I have a query including an inner join, which I execute to obtain a
> ResultSet, but when I execute the following piece of code I get an
error
> message saying that it could not update the table because it could
not
> find the primary key for a table.
> rs.first();
> rs.updateString("gene", "TestGen");
> rs.updateRow();
>
> Here is my query:
> SELECT gene_entropy.gene, gene_entropy.entropy,
gene_entropy.iteration,
> snp_entropy.snp_id
> FROM clustering.gene_entropy INNER JOIN clustering.snp_entropy
> USING(gene) limit 5;
>
> And here is the stacktrace:
> org.postgresql.util.PSQLException: Für die Tebelle
> (clustering.gene_entropy konnte kein Primärschlüssel gefunden
werden.
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpda teable(AbstractJdbc2Result
> Set.java:1547)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkU pdateable(AbstractJdbc2Res
> ultSet.java:2550)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update Value(AbstractJdbc2ResultS
> et.java:2796)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update String(AbstractJdbc2Result
> Set.java:1309)
> at
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update String(AbstractJdbc2Result
> Set.java:1394)
> at
>
myapp.ui.event.DBTableFrameListener.actionPerforme d(DBTableFrameListener.jav
> a:406)
> at
>
javax.swing.AbstractButton.fireActionPerformed(Abs tractButton.java:1995)
> at
>
javax.swing.AbstractButton$Handler.actionPerformed (AbstractButton.java:2318)
> at
>
javax.swing.DefaultButtonModel.fireActionPerformed (DefaultButtonModel.java:3
> 87)
> at
>
javax.swing.DefaultButtonModel.setPressed(DefaultB uttonModel.java:242)
> at
>
javax.swing.plaf.basic.BasicButtonListener.mouseRe leased(BasicButtonListener
> .java:236)
> at
>
java.awt.AWTEventMulticaster.mouseReleased(AWTEven tMulticaster.java:272)
> at java.awt.Component.processMouseEvent(Component.jav a:6038)
> at
> javax.swing.JComponent.processMouseEvent(JComponen t.java:3260)
> at java.awt.Component.processEvent(Component.java:580 3)
> at java.awt.Container.processEvent(Container.java:205 8)
> at java.awt.Component.dispatchEventImpl(Component.jav a:4410)
> at java.awt.Container.dispatchEventImpl(Container.jav a:2116)
> at java.awt.Component.dispatchEvent(Component.java:42 40)
> at
>
java.awt.LightweightDispatcher.retargetMouseEvent( Container.java:4322)
> at
>
java.awt.LightweightDispatcher.processMouseEvent(C ontainer.java:3986)
> at
> java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:3916)
> at java.awt.Container.dispatchEventImpl(Container.jav a:2102)
> at java.awt.Window.dispatchEventImpl(Window.java:2429 )
> at java.awt.Component.dispatchEvent(Component.java:42 40)
> at java.awt.EventQueue.dispatchEvent(EventQueue.java: 599)
> at
>
java.awt.EventDispatchThread.pumpOneEventForFilter s(EventDispatchThread.java
> :273)
> at
>
java.awt.EventDispatchThread.pumpEventsForFilter(E ventDispatchThread.java:18
> 3)
> at
>
java.awt.EventDispatchThread.pumpEventsForHierarch y(EventDispatchThread.java
> :173)
> at
>
java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:168)
> at
>
java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:160)
> at
> java.awt.EventDispatchThread.run(EventDispatchThre ad.java:121)
>
> I am more than happy and thankful for any advice!
>
>
>
> Christian Rengstl M.A.
> Klinik und Poliklinik für Innere Medizin II
> Kardiologie - Forschung
> Universitätsklinikum Regensburg
> B3 1.388
> Franz-Josef-Strauss-Allee 11
> 93053 Regensburg
> Tel.: +49-941-944-7230
>
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do
not
> match


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Only simple result sets coming from a single table, with no joins, are
updateable. Granted, the error message is misleading. The driver has to
parse the query, to extract the name of the table to update or throw an
error if there's more than one table involved. In this case, it doesn't
understand the INNER JOIN, and therefore you get the misleading error
message instead of this: "ResultSet is not updateable. The query that
generated this result set must select only one table, and must select
all primary keys from that table. See the JDBC 2.1 API Specification,
section 5.6 for more details."

Christian Rengstl wrote:
> Hi everyone,
>
> I have a query including an inner join, which I execute to obtain a
> ResultSet, but when I execute the following piece of code I get an error
> message saying that it could not update the table because it could not
> find the primary key for a table.
> rs.first();
> rs.updateString("gene", "TestGen");
> rs.updateRow();
>
> Here is my query:
> SELECT gene_entropy.gene, gene_entropy.entropy, gene_entropy.iteration,
> snp_entropy.snp_id
> FROM clustering.gene_entropy INNER JOIN clustering.snp_entropy
> USING(gene) limit 5;
>
> And here is the stacktrace:
> org.postgresql.util.PSQLException: Für die Tebelle
> (clustering.gene_entropy konnte kein Primärschlüssel gefunden werden.
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpda teable(AbstractJdbc2ResultSet.java:1547)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkU pdateable(AbstractJdbc2ResultSet.java:2550)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.update Value(AbstractJdbc2ResultSet.java:2796)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.update String(AbstractJdbc2ResultSet.java:1309)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.update String(AbstractJdbc2ResultSet.java:1394)
> at
> myapp.ui.event.DBTableFrameListener.actionPerforme d(DBTableFrameListener.java:406)
> at
> javax.swing.AbstractButton.fireActionPerformed(Abs tractButton.java:1995)
> at
> javax.swing.AbstractButton$Handler.actionPerformed (AbstractButton.java:2318)
> at
> javax.swing.DefaultButtonModel.fireActionPerformed (DefaultButtonModel.java:387)
> at
> javax.swing.DefaultButtonModel.setPressed(DefaultB uttonModel.java:242)
> at
> javax.swing.plaf.basic.BasicButtonListener.mouseRe leased(BasicButtonListener.java:236)
> at
> java.awt.AWTEventMulticaster.mouseReleased(AWTEven tMulticaster.java:272)
> at java.awt.Component.processMouseEvent(Component.jav a:6038)
> at
> javax.swing.JComponent.processMouseEvent(JComponen t.java:3260)
> at java.awt.Component.processEvent(Component.java:580 3)
> at java.awt.Container.processEvent(Container.java:205 8)
> at java.awt.Component.dispatchEventImpl(Component.jav a:4410)
> at java.awt.Container.dispatchEventImpl(Container.jav a:2116)
> at java.awt.Component.dispatchEvent(Component.java:42 40)
> at
> java.awt.LightweightDispatcher.retargetMouseEvent( Container.java:4322)
> at
> java.awt.LightweightDispatcher.processMouseEvent(C ontainer.java:3986)
> at
> java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:3916)
> at java.awt.Container.dispatchEventImpl(Container.jav a:2102)
> at java.awt.Window.dispatchEventImpl(Window.java:2429 )
> at java.awt.Component.dispatchEvent(Component.java:42 40)
> at java.awt.EventQueue.dispatchEvent(EventQueue.java: 599)
> at
> java.awt.EventDispatchThread.pumpOneEventForFilter s(EventDispatchThread.java:273)
> at
> java.awt.EventDispatchThread.pumpEventsForFilter(E ventDispatchThread.java:183)
> at
> java.awt.EventDispatchThread.pumpEventsForHierarch y(EventDispatchThread.java:173)
> at
> java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:168)
> at
> java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:160)
> at
> java.awt.EventDispatchThread.run(EventDispatchThre ad.java:121)
>
> I am more than happy and thankful for any advice!


--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

And I suppose there is no reasonable workaround, is there?


Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230




>>> On Wed, Oct 10, 2007 at 2:53 PM, in message
<470CCB67.80106@enterprisedb.com>,
"Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
> Only simple result sets coming from a single table, with no joins,
are
> updateable. Granted, the error message is misleading. The driver has
to
> parse the query, to extract the name of the table to update or throw
an
> error if there's more than one table involved. In this case, it
doesn't
> understand the INNER JOIN, and therefore you get the misleading
error
> message instead of this: "ResultSet is not updateable. The query
that
> generated this result set must select only one table, and must
select
> all primary keys from that table. See the JDBC 2.1 API
Specification,
> section 5.6 for more details."
>
> Christian Rengstl wrote:
>> Hi everyone,
>>
>> I have a query including an inner join, which I execute to obtain a
>> ResultSet, but when I execute the following piece of code I get an
error
>> message saying that it could not update the table because it could
not
>> find the primary key for a table.
>> rs.first();
>> rs.updateString("gene", "TestGen");
>> rs.updateRow();
>>
>> Here is my query:
>> SELECT gene_entropy.gene, gene_entropy.entropy,
gene_entropy.iteration,
>> snp_entropy.snp_id
>> FROM clustering.gene_entropy INNER JOIN clustering.snp_entropy
>> USING(gene) limit 5;
>>
>> And here is the stacktrace:
>> org.postgresql.util.PSQLException: Für die Tebelle
>> (clustering.gene_entropy konnte kein Primärschlüssel gefunden
werden.
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpda teable(AbstractJdbc2ResultS
> et.java:1547)
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkU pdateable(AbstractJdbc2Resu
> ltSet.java:2550)
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update Value(AbstractJdbc2ResultSe
> t.java:2796)
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update String(AbstractJdbc2ResultS
> et.java:1309)
>> at
>>
>
org.postgresql.jdbc2.AbstractJdbc2ResultSet.update String(AbstractJdbc2ResultS
> et.java:1394)
>> at
>>
>
myapp.ui.event.DBTableFrameListener.actionPerforme d(DBTableFrameListener.java
> :406)
>> at
>>
javax.swing.AbstractButton.fireActionPerformed(Abs tractButton.java:1995)
>> at
>>
javax.swing.AbstractButton$Handler.actionPerformed (AbstractButton.java:2318)
>> at
>>
>
javax.swing.DefaultButtonModel.fireActionPerformed (DefaultButtonModel.java:38
> 7)
>> at
>>
javax.swing.DefaultButtonModel.setPressed(DefaultB uttonModel.java:242)
>> at
>>
>
javax.swing.plaf.basic.BasicButtonListener.mouseRe leased(BasicButtonListener.
> java:236)
>> at
>>
java.awt.AWTEventMulticaster.mouseReleased(AWTEven tMulticaster.java:272)
>> at
java.awt.Component.processMouseEvent(Component.jav a:6038)
>> at
>> javax.swing.JComponent.processMouseEvent(JComponen t.java:3260)
>> at java.awt.Component.processEvent(Component.java:580 3)
>> at java.awt.Container.processEvent(Container.java:205 8)
>> at
java.awt.Component.dispatchEventImpl(Component.jav a:4410)
>> at
java.awt.Container.dispatchEventImpl(Container.jav a:2116)
>> at java.awt.Component.dispatchEvent(Component.java:42 40)
>> at
>>
java.awt.LightweightDispatcher.retargetMouseEvent( Container.java:4322)
>> at
>>
java.awt.LightweightDispatcher.processMouseEvent(C ontainer.java:3986)
>> at
>> java.awt.LightweightDispatcher.dispatchEvent(Conta iner.java:3916)
>> at
java.awt.Container.dispatchEventImpl(Container.jav a:2102)
>> at java.awt.Window.dispatchEventImpl(Window.java:2429 )
>> at java.awt.Component.dispatchEvent(Component.java:42 40)
>> at java.awt.EventQueue.dispatchEvent(EventQueue.java: 599)
>> at
>>
>
java.awt.EventDispatchThread.pumpOneEventForFilter s(EventDispatchThread.java:
> 273)
>> at
>>
>
java.awt.EventDispatchThread.pumpEventsForFilter(E ventDispatchThread.java:183
> )
>> at
>>
>
java.awt.EventDispatchThread.pumpEventsForHierarch y(EventDispatchThread.java:
> 173)
>> at
>>
java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:168)
>> at
>>
java.awt.EventDispatchThread.pumpEvents(EventDispa tchThread.java:160)
>> at
>> java.awt.EventDispatchThread.run(EventDispatchThre ad.java:121)
>>
>> I am more than happy and thankful for any advice!
>
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do
not
> match


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Christian Rengstl wrote:
> And I suppose there is no reasonable workaround, is there?

Well, you can always run a good old UPDATE statement yourself.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Well, that's not a big problem. It's just because in my app i am
offering the option to execute stored queries and display and update the
fields using a JTable. The problem is just that some users said it would
be nice if a joined query with fields from two or more tables would be
updateable as well.


Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230




>>> On Wed, Oct 10, 2007 at 3:02 PM, in message
<470CCD6A.4090008@enterprisedb.com>, "Heikki Linnakangas"
<heikki@enterprisedb.com> wrote:
> Christian Rengstl wrote:
>> And I suppose there is no reasonable workaround, is there?
>
> Well, you can always run a good old UPDATE statement yourself.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi.

I'd create an updatable server-side view and issue select against it.
Not sure if primary key information will be propagated, but you can try.

Christian Rengstl wrote:
> And I suppose there is no reasonable workaround, is there?
>
>

>
>>>> On Wed, Oct 10, 2007 at 2:53 PM, in message
>>>>
> <470CCB67.80106@enterprisedb.com>,
> "Heikki Linnakangas" <heikki@enterprisedb.com> wrote:
>
>> Only simple result sets coming from a single table, with no joins,
>>
> are
>
>> updateable. Granted, the error message is misleading. The driver has
>>
> to
>
>> parse the query, to extract the name of the table to update or throw
>>
> an
>
>> error if there's more than one table involved. In this case, it
>>
> doesn't
>
>> understand the INNER JOIN, and therefore you get the misleading
>>
> error
>
>> message instead of this: "ResultSet is not updateable. The query
>>
> that
>
>> generated this result set must select only one table, and must
>>
> select
>
>> all primary keys from that table. See the JDBC 2.1 API
>>
> Specification,
>
>> section 5.6 for more details."
>>



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Wednesday, October 10, 2007, 5:39:47 AM, you wrote:

> Hi.

> I'd create an updatable server-side view and issue select against it.
> Not sure if primary key information will be propagated, but you can try.

I've found that a column in a view named "oid" is automatically
assumed to be a primary key. (You can see the special case in the JDBC
code.) So I include such a column in an updatable view.
--
Best regards,
Andrew mailto:drlaz@attglobal.net


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster