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

Unique pkey constraint problems w/PostgreSQL

tasj2ee Oct 15, 2004 8:26 PM

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

tasj2ee Oct 22, 2004 8:42 PM (in response to tasj2ee)
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.
Actions