1 Reply Latest reply on Oct 22, 2004 8:42 PM by Thomas Shealy

    Unique pkey constraint problems w/PostgreSQL

    Thomas Shealy Newbie

      Hello,

      I've run into a problem trying out nukes 1.1 RC3 with PostgreSQL. It looks like some entities aren't getting unique primary keys. If I attempt to update and save an HTML page using the HTML module I consistently get an error and the postgres console says:

      ERROR: duplicate key violates unique constraint "nuke_file_pkey"

      This happens for other tables, too, such as the the FAQ tables. I subsequently installed the mysql version of 1.1. RC3 and everything worked fine. Now I'd like to get the PostgreSQL version working.

      I haven't looked into the code yet, and I first wanted to find out if anyone knows the cause of the problem offhand.

      Thanks,
      -tas

      Environment:
      JBOSS 3.2.3
      Nukes 1.1 RC PostgreSQL (installed successfully using nukes-installer.war)
      PostgreSQL 7.4.3

      
      org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "nuke_file_pkey"
      
       at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
       at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
       at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
       at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
       at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
       at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:273)
       at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:324)
       at org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPostgreSQLCreateCommand.executeInsert(JDBCPostgreSQLCreateCommand.java:59)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.performInsert(JDBCAbstractCreateCommand.java:287)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:138)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:554)
       at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:208)
       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.createEntity(CachedConnectionInterceptor.java:269)
       at org.jboss.ejb.EntityContainer.createLocalHome(EntityContainer.java:581)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1043)
       at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
       at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:197)
       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:214)
       at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
       at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:89)
       at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
       at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
       at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:88)
       at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
       at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:98)
       at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:92)
       at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
       at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
       at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:483)
       at org.jboss.ejb.Container.invoke(Container.java:720)
       at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invokeHome(BaseLocalProxyFactory.java:293)
       at org.jboss.ejb.plugins.local.LocalHomeProxy.invoke(LocalHomeProxy.java:110)
       at $Proxy45.create(Unknown Source)
       at org.jboss.nukes.core.modules.html.commands.StoreCommand.execute(StoreCommand.java:104)
       at org.jboss.nukes.core.modules.html.HtmlModule.update(HtmlModule.java:670)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.nukes.module.ModuleSupport.operation(ModuleSupport.java:117)
       at org.jboss.nukes.module.ModuleSupport.process(ModuleSupport.java:91)
       at sun.reflect.GeneratedMethodAccessor81.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.interceptor.ObjectReferenceInterceptor.invoke(ObjectReferenceInterceptor.java:59)
       at org.jboss.mx.interceptor.MBeanAttributeInterceptor.invoke(MBeanAttributeInterceptor.java:43)
       at org.jboss.mx.interceptor.PersistenceInterceptor2.invoke(PersistenceInterceptor2.java:93)
       at org.jboss.nukes.mx.LifeCycleInterceptor.invoke(LifeCycleInterceptor.java:93)
       at org.jboss.mx.server.MBeanInvoker.invoke(MBeanInvoker.java:76)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.nukes.core.Main$LastNext.process(Main.java:557)
       at org.jboss.nukes.core.modules.html.HtmlModule$1.process(HtmlModule.java:259)
       at org.jboss.nukes.core.Main$HandlerNext.process(Main.java:508)
       at org.jboss.nukes.core.modules.core.CoreModule$3.process(CoreModule.java:404)
       at org.jboss.nukes.core.Main$HandlerNext.process(Main.java:508)
       at org.jboss.nukes.core.modules.block.BlockModule$3.process(BlockModule.java:810)
       at org.jboss.nukes.core.Main$HandlerNext.process(Main.java:508)
       at org.jboss.nukes.core.modules.theme.ThemeModule$2.process(ThemeModule.java:255)
       at org.jboss.nukes.core.Main$HandlerNext.process(Main.java:508)
       at org.jboss.nukes.core.modules.user.UserModule$4.process(UserModule.java:1487)
       at org.jboss.nukes.core.Main$HandlerNext.process(Main.java:508)
       at org.jboss.nukes.core.Main.process(Main.java:119)
       at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.nukes.servlet.NukeServlet.doGet(NukeServlet.java:175)
       at org.jboss.nukes.servlet.NukeServlet.doPost(NukeServlet.java:113)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
       at org.jboss.nukes.servlet.TransactionFilter.doFilter(TransactionFilter.java:95)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
       at org.jboss.nukes.servlet.FileUploadFilter.doFilter(FileUploadFilter.java:64)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
       at org.jboss.nukes.servlet.CompressFilter.doFilter(CompressFilter.java:77)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
       at org.jboss.nukes.servlet.ParameterEncodingFilter.doFilter(ParameterEncodingFilter.java:60)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
       at org.jboss.nukes.servlet.DatabasePollerFilter.doFilter(DatabasePollerFilter.java:88)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
       at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
       at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
       at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
       at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197)
       at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:309)
       at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:387)
       at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:673)
       at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:615)
       at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:786)
       at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
       at java.lang.Thread.run(Thread.java:534)
      
      



        • 1. Re: Unique pkey constraint problems w/PostgreSQL
          Thomas Shealy Newbie

          Ok, For the record:

          The problem occurred because the sequences for the serial primary keys of the nukes tables were not incremented after populating the default data installed by the nukes-installer (see postgres.xml in nukes-installer.war).

          For example, the nukes-installer creates the nuke_file table using:

          CREATE TABLE nuke_file (
           pn_id SERIAL NOT NULL PRIMARY KEY,
           pn_fid VARCHAR(255) NOT NULL,
           pn_uid INTEGER DEFAULT '1' NULL,
           pn_content_type VARCHAR(50) NOT NULL,
           pn_content BYTEA NOT NULL,
           pn_timest TIMESTAMP NOT NULL,
           pn_public BOOLEAN DEFAULT false NOT NULL
           );
          



          Consequently during installation, the postgesql console reports:
          NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "nuke_file_pkey" for table "nuke_file"
          

          and
          NOTICE: CREATE TABLE will create implicit sequence "nuke_file_pn_id_seq" for "serial" column "nuke_file.pn_id"
          


          The sequence nuke_file_pn_id_seq starts at the default minvalue which is 1. However, the nukes-installer also inserts fourteen items in the table (see postgres.xml). Consequently the next value should be 15 instead of 1. (postgresql-fetch-seq is defined in standardjbosscmp-jdbc.xml.)

          The sequences can be incremented by a SQL statement in the form SELECT setval([sequence name], max([serial field name])) FROM [table name].

          In the case of nuke_file, the sequence can be set executing the following SQL statement:
          SELECT setval('nuke_file_pn_id_seq', max(pn_id)) FROM nuke_file;
          


          The other tables (e.g, nuke_faq, etc.) can be fixed the same way.