0001041: JMS durable subscriptions ( topics) do not work with Postgres 8.1.2

Viewing Issue Advanced Details [ Jump to Notes ]

[ View Simple ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0001041

[Resin]

major

always

04-06-06 13:02

04-10-06 16:09

Reporter

peden

View Status

public

Assigned To

ferg

Priority

normal

Resolution

fixed

Platform

Status

closed

Projection

none

OS Version

ETA

none

Fixed in Version

3.0.19

Product Version

3.0.19

Product Build

Summary

0001041: JMS durable subscriptions ( topics) do not work with Postgres 8.1.2

Description

Code to handle sequences appears to be missing from JdbcQueueConsumer.java. This causes an exception when using a Postgres 8.1.2 data source with a slightly modified version of the Resin text JMS example.

To reproduce this problem:

1) Start with the text JMS example.
2) Use a Postgres 8.1.2 data source.
3) Change the example to use a topic instead of a queue:
a)in web.xml, change the type of resource "queue" from com.caucho.jms.jdbc.JdbcQueue to com.caucho.jms.jdbc.JdbcTopic
b) in web.xml, change the <queue-name> field of resource "queue" to <topic-name>
4) Run the sample.
5) Receive the following error:
[12:41:55.859] java.lang.reflect.InvocationTargetException
[12:41:55.859] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[12:41:55.859] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[12:41:55.859] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[12:41:55.859] at java.lang.reflect.Method.invoke(Method.java:585)
[12:41:55.859] at com.caucho.loader.StartListener.environmentStart(StartListener.java:73)
[12:41:55.859] at com.caucho.loader.EnvironmentClassLoader.start(EnvironmentClassLoader.java:318)
[12:41:55.859] at com.caucho.server.webapp.Application.start(Application.java:1578)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startImpl(DeployController.java:587)
[12:41:55.859] at com.caucho.server.deploy.StartAutoRedeployAutoStrategy.startOnInit(StartAutoRedeployAutoStrategy.java:72)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startOnInit(DeployController.java:475)
[12:41:55.859] at com.caucho.server.deploy.DeployContainer.start(DeployContainer.java:158)
[12:41:55.859] at com.caucho.server.webapp.ApplicationContainer.start(ApplicationContainer.java:651)
[12:41:55.859] at com.caucho.server.host.Host.start(Host.java:385)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startImpl(DeployController.java:587)
[12:41:55.859] at com.caucho.server.deploy.StartAutoRedeployAutoStrategy.startOnInit(StartAutoRedeployAutoStrategy.java:72)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startOnInit(DeployController.java:475)
[12:41:55.859] at com.caucho.server.deploy.DeployContainer.start(DeployContainer.java:158)
[12:41:55.859] at com.caucho.server.host.HostContainer.start(HostContainer.java:467)
[12:41:55.859] at com.caucho.server.resin.ServletServer.start(ServletServer.java:945)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startImpl(DeployController.java:587)
[12:41:55.859] at com.caucho.server.deploy.AbstractDeployControllerStrategy.start(AbstractDeployControllerStrategy.java:56)
[12:41:55.859] at com.caucho.server.deploy.DeployController.start(DeployController.java:483)
[12:41:55.859] at com.caucho.server.resin.ResinServer.start(ResinServer.java:478)
[12:41:55.859] at com.caucho.server.resin.Resin.init(Resin.java)
[12:41:55.859] at com.caucho.server.resin.Resin.main(Resin.java:623)
[12:41:55.859] Caused by: com.caucho.jms.JMSExceptionWrapper: Returning autogenerated keys is not supported.
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopicConsumer.createTopic(JdbcTopicConsumer.java:190)
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopicConsumer.<init>(JdbcTopicConsumer.java:107)
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopic.createConsumer(JdbcTopic.java:148)
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopic.createConsumer(JdbcTopic.java:81)
[12:41:55.859] at com.caucho.jms.session.SessionImpl.createConsumer(SessionImpl.java:378)
[12:41:55.859] at com.caucho.jms.session.SessionImpl.createConsumer(SessionImpl.java:344)
[12:41:55.859] at com.caucho.jms.resource.ListenerResource.start(ListenerResource.java:121)
[12:41:55.859] ... 25 more
[12:41:55.859] Caused by: org.postgresql.util.PSQLException: Returning autogenerated keys is not supported.
[12:41:55.859] at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:352)
[12:41:55.859] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[12:41:55.859] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[12:41:55.859] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[12:41:55.859] at java.lang.reflect.Method.invoke(Method.java:585)
[12:41:55.859] at org.postgresql.ds.common.PooledConnectionImpl$ConnectionHandler.invoke(PooledConnectionImpl.java:348)
[12:41:55.859] at $Proxy0.prepareStatement(Unknown Source)
[12:41:55.859] at com.caucho.sql.ManagedConnectionImpl.prepareStatement(ManagedConnectionImpl.java:403)
[12:41:55.859] at com.caucho.sql.UserConnection.prepareStatement(UserConnection.java:282)
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopicConsumer.createTopic(JdbcTopicConsumer.java:166)
[12:41:55.859] ... 31 more
[12:41:55.859] java.lang.reflect.InvocationTargetException
[12:41:55.859] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[12:41:55.859] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[12:41:55.859] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[12:41:55.859] at java.lang.reflect.Method.invoke(Method.java:585)
[12:41:55.859] at com.caucho.loader.StartListener.environmentStart(StartListener.java:73)
[12:41:55.859] at com.caucho.loader.EnvironmentClassLoader.start(EnvironmentClassLoader.java:318)
[12:41:55.859] at com.caucho.server.webapp.Application.start(Application.java:1578)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startImpl(DeployController.java:587)
[12:41:55.859] at com.caucho.server.deploy.StartAutoRedeployAutoStrategy.startOnInit(StartAutoRedeployAutoStrategy.java:72)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startOnInit(DeployController.java:475)
[12:41:55.859] at com.caucho.server.deploy.DeployContainer.start(DeployContainer.java:158)
[12:41:55.859] at com.caucho.server.webapp.ApplicationContainer.start(ApplicationContainer.java:651)
[12:41:55.859] at com.caucho.server.host.Host.start(Host.java:385)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startImpl(DeployController.java:587)
[12:41:55.859] at com.caucho.server.deploy.StartAutoRedeployAutoStrategy.startOnInit(StartAutoRedeployAutoStrategy.java:72)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startOnInit(DeployController.java:475)
[12:41:55.859] at com.caucho.server.deploy.DeployContainer.start(DeployContainer.java:158)
[12:41:55.859] at com.caucho.server.host.HostContainer.start(HostContainer.java:467)
[12:41:55.859] at com.caucho.server.resin.ServletServer.start(ServletServer.java:945)
[12:41:55.859] at com.caucho.server.deploy.DeployController.startImpl(DeployController.java:587)
[12:41:55.859] at com.caucho.server.deploy.AbstractDeployControllerStrategy.start(AbstractDeployControllerStrategy.java:56)
[12:41:55.859] at com.caucho.server.deploy.DeployController.start(DeployController.java:483)
[12:41:55.859] at com.caucho.server.resin.ResinServer.start(ResinServer.java:478)
[12:41:55.859] at com.caucho.server.resin.Resin.init(Resin.java)
[12:41:55.859] at com.caucho.server.resin.Resin.main(Resin.java:623)
[12:41:55.859] Caused by: com.caucho.jms.JMSExceptionWrapper: Returning autogenerated keys is not supported.
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopicConsumer.createTopic(JdbcTopicConsumer.java:190)
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopicConsumer.<init>(JdbcTopicConsumer.java:107)
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopic.createConsumer(JdbcTopic.java:148)
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopic.createConsumer(JdbcTopic.java:81)
[12:41:55.859] at com.caucho.jms.session.SessionImpl.createConsumer(SessionImpl.java:378)
[12:41:55.859] at com.caucho.jms.session.SessionImpl.createConsumer(SessionImpl.java:344)
[12:41:55.859] at com.caucho.jms.resource.ListenerResource.start(ListenerResource.java:121)
[12:41:55.859] ... 25 more
[12:41:55.859] Caused by: org.postgresql.util.PSQLException: Returning autogenerated keys is not supported.
[12:41:55.859] at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:352)
[12:41:55.859] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[12:41:55.859] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[12:41:55.859] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[12:41:55.859] at java.lang.reflect.Method.invoke(Method.java:585)
[12:41:55.859] at org.postgresql.ds.common.PooledConnectionImpl$ConnectionHandler.invoke(PooledConnectionImpl.java:348)
[12:41:55.859] at $Proxy0.prepareStatement(Unknown Source)
[12:41:55.859] at com.caucho.sql.ManagedConnectionImpl.prepareStatement(ManagedConnectionImpl.java:403)
[12:41:55.859] at com.caucho.sql.UserConnection.prepareStatement(UserConnection.java:282)
[12:41:55.859] at com.caucho.jms.jdbc.JdbcTopicConsumer.createTopic(JdbcTopicConsumer.java:166)
[12:41:55.859] ... 31 more

Steps To Reproduce

Additional Information

The problem is apparently caused by code to handle sequences missing from JdbcTopicConsumer.createTopic(). The source for JdbcQueueConsumer.createQueue() has code to handle the case where sequences are used generate the s_id field of the consumer table.

I have pasted a proposed new version of the routine below that handles this case:

  private void createTopic()
    throws JMSException
  {
        try {
            DataSource dataSource = _jdbcManager.getDataSource();
            String consumerTable = _jdbcManager.getConsumerTable();
            String messageTable = _jdbcManager.getMessageTable();
            String consumerSequence = _jdbcManager.getConsumerSequence();

            Connection conn = dataSource.getConnection();
            try {
                String sql = ("SELECT MAX(m_id)" + " FROM " + messageTable + " WHERE queue=?");

                long max = -1;

                PreparedStatement pstmt;
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1, _topic.getId());

                ResultSet rs = pstmt.executeQuery();
                if (rs.next())
                    max = rs.getLong(1);
                rs.close();
                if (consumerSequence != null) {
                    sql = _jdbcManager.getMetaData().selectSequenceSQL(
                            consumerSequence);
                    pstmt = conn.prepareStatement(sql);

                    long id = 0;

                    rs = pstmt.executeQuery();
                    if (rs.next())
                        id = rs.getLong(1);
                    else
                        throw new RuntimeException(
                                "Expected result in customer create");
                    sql = ("INSERT INTO " + consumerTable + " (s_id, queue, expire, read_id, ack_id) VALUES (?,?,?,?,?)");

                    pstmt = conn.prepareStatement(sql);

                    pstmt.setLong(1, id);
                    pstmt.setInt(2, _topic.getId());
                    pstmt.setLong(3, Alarm.getCurrentTime() + TOPIC_TIMEOUT);
                    pstmt.setLong(4, max);
                    pstmt.setLong(5, max);

                    pstmt.executeUpdate();
                    _consumerId = id;
                } else {
                    sql = ("INSERT INTO " + consumerTable + " (queue, expire, read_id, ack_id) VALUES (?,?,?,?)");

                    pstmt = conn.prepareStatement(sql,
                            PreparedStatement.RETURN_GENERATED_KEYS);

                    pstmt.setInt(1, _topic.getId());
                    pstmt.setLong(2, Alarm.getCurrentTime() + TOPIC_TIMEOUT);
                    pstmt.setLong(3, max);
                    pstmt.setLong(4, max);

                    pstmt.executeUpdate();

                    ResultSet rsKeys = pstmt.getGeneratedKeys();

                    if (rsKeys.next()) {
                        _consumerId = rsKeys.getLong(1);
                    } else
                        throw new JMSException(L
                                .l("consumer insert didn't create a key"));

                    rsKeys.close();
                    pstmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (SQLException e) {
            throw new JMSExceptionWrapper(e);
        }
    }

Attached Files

Relationships

Notes
(0001067) peden 04-09-06 18:34	Since I have decided not to use topics in the application that I am developing (at least for a while), the priority of this bug could be reduced ;-)

(0001077) ferg 04-10-06 16:09	ejb/6a9a

Issue History
Date Modified	Username	Field	Change
04-06-06 13:02	peden	New Issue
04-09-06 18:34	peden	Note Added: 0001067
04-10-06 16:09	ferg	Note Added: 0001077
04-10-06 16:09	ferg	Assigned To	=> ferg
04-10-06 16:09	ferg	Status	new => closed
04-10-06 16:09	ferg	Resolution	open => fixed
04-10-06 16:09	ferg	Fixed in Version	=> 3.0.19

Mantis 1.0.0rc3[^]

31 total queries executed.
27 unique queries executed.