Thread: JDBC and primary Key missing

JDBC and primary Key missing

From
"Nuno Duarte Leitao"
Date:
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


Re: JDBC and primary Key missing

From
Dave Cramer
Date:
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>


JDBC delete not recovering space?

From
"John Guthrie"
Date:
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


Re: JDBC delete not recovering space?

From
"Jeroen Habets"
Date:
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


Re: JDBC delete not recovering space?

From
Dave Cramer
Date:
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>


Re: JDBC delete not recovering space?

From
"John Guthrie"
Date:
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>
>