Thread: JDBC and primary Key missing
Having a strange problem with Sun studio 4 update 1 talking to postgreSQL 7.3. (Redhat version) Using the latest Postgres JDBC driver (both latest stable and dev releases)- in Sun studio I'm having problems mapping my container manager persitence entity beans to postgres tables. I make a database schema and entity bean from the postgres datasource (everything's fine at this stage) but when I try to map the ejb fields to tables in the postgres schema, for deployment, the dialogue reports "Table 'x' has no primary keys defined" I've checked my keys and indexes and I definitley have a primary key defined. Is there something I'm missing? I have the database in MySQL also and it works fine with Sun - but I'd much rather have a postgres backend. I've searched the web and newsgroups and can find hardly any information on this topic, your help would be very much appreciated regards Nuno D. Leitao
Nuno, Can you send the table schema ? There are some peculiarities about what our driver thinks is a primary key. Dave On Wed, 2003-04-23 at 10:52, Nuno Duarte Leitao wrote: > Having a strange problem with Sun studio 4 update 1 talking to postgreSQL > 7.3. (Redhat version) > > Using the latest Postgres JDBC driver (both latest stable and dev > releases)- in Sun studio I'm having problems mapping my container manager > persitence entity beans to postgres tables. > > I make a database schema and entity bean from the postgres datasource > (everything's fine at this stage) but when I try to map > the ejb fields to tables in the postgres schema, for deployment, the > dialogue reports "Table 'x' has no primary keys defined" > > I've checked my keys and indexes and I definitley have a primary key > defined. Is there something I'm missing? > > I have the database in MySQL also and it works fine with Sun - but I'd > much > rather have a postgres backend. > > I've searched the web and newsgroups and can find hardly any information > on > this topic, your help would be very much appreciated > > > regards > Nuno D. Leitao > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Dave Cramer <Dave@micro-automation.net>
i am seeing some odd behavior and wonder if anyone can help me explain it and fix it... i've written a java task that runs in a thread and monitors disk space on the partition where i have my $PGDATA. if the space available drops below a certain level, i remove some old records ... and vacuum. the odd thing is that running via jdbc the space seems to not be recovered. example - if i start with 100,000 records of approximately 1K size and i remove 50,000, using 'df' in another shell i see no space recovery. from another shell, using psql, i run vacuum and again i see no gains. the really odd thing is that if i do this whole scenario in psql - create 100,000, delete the oldest 50,000, and vacuum - i *do* see the space recovered. even better (or worse), if i do the whole test in java, verify that little or no space is recovered, then run psql and delete a single records and vacuum, bam(!) i get a boatload of space back. note that in the java task i explicitly set auto-commit to true and i run queries from psql that show results that reflect the deletions, so i don't see this as a transaction problem. also, if i shut down postgresql i see the correct (reduced) record count but still no space recovery. i can restart it, run psql, and vacuum, but still no space recovery... but if i then delete a record and vacuum again, i get space, lots of space (i.e. megabytes). any ideas anyone? thanks john --- john guthrie psynapse technologies
Exactly what you expect! See http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=routine-vacu uming.html Excerpt: 8.2. Routine Vacuuming PostgreSQL's VACUUM command must be run on a regular basis for several reasons: 1) To recover disk space occupied by updated or deleted rows. > -----Oorspronkelijk bericht----- > Van: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]Namens John Guthrie > Verzonden: Wednesday, April 23, 2003 17:45 > Aan: pgsql-jdbc@postgresql.org > Onderwerp: [JDBC] JDBC delete not recovering space? > > > i am seeing some odd behavior and wonder if anyone can help me explain it > and fix it... > > i've written a java task that runs in a thread and monitors disk space on > the partition where i have my $PGDATA. if the space available > drops below a > certain level, i remove some old records ... and vacuum. > > the odd thing is that running via jdbc the space seems to not be > recovered. > example - if i start with 100,000 records of approximately 1K size and i > remove 50,000, using 'df' in another shell i see no space recovery. from > another shell, using psql, i run vacuum and again i see no gains. > > the really odd thing is that if i do this whole scenario in psql - create > 100,000, delete the oldest 50,000, and vacuum - i *do* see the space > recovered. > > even better (or worse), if i do the whole test in java, verify that little > or no space is recovered, then run psql and delete a single records and > vacuum, bam(!) i get a boatload of space back. > > note that in the java task i explicitly set auto-commit to true and i run > queries from psql that show results that reflect the deletions, so i don't > see this as a transaction problem. also, if i shut down > postgresql i see the > correct (reduced) record count but still no space recovery. i can restart > it, run psql, and vacuum, but still no space recovery... but if i then > delete a record and vacuum again, i get space, lots of space (i.e. > megabytes). > > any ideas anyone? thanks > > john > > --- > john guthrie > psynapse technologies > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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
What version of postres are you running, if it is 7.3.x only vacuum full will recover space Dave On Wed, 2003-04-23 at 11:45, John Guthrie wrote: > i am seeing some odd behavior and wonder if anyone can help me explain it > and fix it... > > i've written a java task that runs in a thread and monitors disk space on > the partition where i have my $PGDATA. if the space available drops below a > certain level, i remove some old records ... and vacuum. > > the odd thing is that running via jdbc the space seems to not be recovered. > example - if i start with 100,000 records of approximately 1K size and i > remove 50,000, using 'df' in another shell i see no space recovery. from > another shell, using psql, i run vacuum and again i see no gains. > > the really odd thing is that if i do this whole scenario in psql - create > 100,000, delete the oldest 50,000, and vacuum - i *do* see the space > recovered. > > even better (or worse), if i do the whole test in java, verify that little > or no space is recovered, then run psql and delete a single records and > vacuum, bam(!) i get a boatload of space back. > > note that in the java task i explicitly set auto-commit to true and i run > queries from psql that show results that reflect the deletions, so i don't > see this as a transaction problem. also, if i shut down postgresql i see the > correct (reduced) record count but still no space recovery. i can restart > it, run psql, and vacuum, but still no space recovery... but if i then > delete a record and vacuum again, i get space, lots of space (i.e. > megabytes). > > any ideas anyone? thanks > > john > > --- > john guthrie > psynapse technologies > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 -- Dave Cramer <Dave@micro-automation.net>
it's version 7.2.2 on RedHat 8.0 - sorry i forgot to mention that. and the jdbc driver is pg73rc1jdbc3.jar. john --- john guthrie ----- Original Message ----- From: "Dave Cramer" <Dave@micro-automation.net> To: "John Guthrie" <jguthrie@psynapsetech.net> Cc: <pgsql-jdbc@postgresql.org> Sent: Wednesday, April 23, 2003 11:54 AM Subject: Re: [JDBC] JDBC delete not recovering space? > What version of postres are you running, if it is 7.3.x only vacuum full > will recover space > > Dave > On Wed, 2003-04-23 at 11:45, John Guthrie wrote: > > i am seeing some odd behavior and wonder if anyone can help me explain it > > and fix it... > > > > i've written a java task that runs in a thread and monitors disk space on > > the partition where i have my $PGDATA. if the space available drops below a > > certain level, i remove some old records ... and vacuum. > > > > the odd thing is that running via jdbc the space seems to not be recovered. > > example - if i start with 100,000 records of approximately 1K size and i > > remove 50,000, using 'df' in another shell i see no space recovery. from > > another shell, using psql, i run vacuum and again i see no gains. > > > > the really odd thing is that if i do this whole scenario in psql - create > > 100,000, delete the oldest 50,000, and vacuum - i *do* see the space > > recovered. > > > > even better (or worse), if i do the whole test in java, verify that little > > or no space is recovered, then run psql and delete a single records and > > vacuum, bam(!) i get a boatload of space back. > > > > note that in the java task i explicitly set auto-commit to true and i run > > queries from psql that show results that reflect the deletions, so i don't > > see this as a transaction problem. also, if i shut down postgresql i see the > > correct (reduced) record count but still no space recovery. i can restart > > it, run psql, and vacuum, but still no space recovery... but if i then > > delete a record and vacuum again, i get space, lots of space (i.e. > > megabytes). > > > > any ideas anyone? thanks > > > > john > > > > --- > > john guthrie > > psynapse technologies > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: 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 > -- > Dave Cramer <Dave@micro-automation.net> >