Re: [SQL] Hibernate, web application and only one sequence for all primary keys
On Thu, Sep 17, 2009 at 07:47:13AM -0700, rawi wrote: > > > Leo Mannhart wrote: > > > > Caveat: If you use the standard sequence generator in hibernate, it is > > not using the postgres-sequence in the "usual" manner. hibernate itself > > caches 50 ID's as sequence numbers by default. This means, hibernate > > only does a select on the database sequence every 50 numbers. it > > multyplies the database sequence by 50 to get the "real" sequence > > number. it generates the sequence numbers in blocks of 50 numbers or > > according to the sequence cache size. > > That said, you would probably not see any performance bottlenecks > > because of the sequence number generator in the database, even with > > thousands of inserts per second. > > > > Hi Leo, thank you for the explanation! > > I don't know if it is that cool to lose up to 50 IDs on each session-end of > Hibernate... > And what do you suppose it would happen, if I set the cache size of > Hibernate's own sequence (after generation) by hand to 1 instead of 50? I > wouldn't need tausends of inserts per second... > > Kind regards, Rawi > Hi Rawi, If hibernate manages its pool of 50, it can use much lighter-weight processes than using a full SQL query to a database. I would recommend leaving it be. You can afford to lose a few ids in 2**63. Regards, Ken -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting from epoch values in pgsql
On Thu, Sep 17, 2009 at 06:34:39PM +0100, Gavin McCullagh wrote:
> On Thu, 17 Sep 2009, Gavin McCullagh wrote:
>
> > On Thu, 17 Sep 2009, Frank Bax wrote:
> >
> > > Gavin McCullagh wrote:
> > >> SELECT time, to_timestamp(time) AS ts,
> > >> EXTRACT('months',to_timestamp(time)) FROM mdl_log;
> > >> ERROR: syntax error at or near ","
> > >> LINE 1: ...t time, to_timestamp(time) AS ts, extract('months',to_times...
> > >
> > > Try replacing extract('month',value) with extract('months' from value)
> >
> > Makes no difference whether month or months:
>
> Actually, I pasted a different query, but based on the one in my last
> email:
>
> moodle-01-01-2009=# SELECT EXTRACT('months',(TIMESTAMP WITH TIME ZONE 'epoch'
> + mdl_log.time * INTERVAL '1 second'))
> FROM mdl_log;
> ERROR: syntax error at or near ","
> LINE 1: SELECT EXTRACT('months',(TIMESTAMP WITH TIME ZONE 'epoch' + ...
>^
> moodle-01-01-2009=# SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch'
> + mdl_log.time * INTERVAL '1 second'))
> FROM mdl_log;
> ERROR: syntax error at or near ","
> LINE 1: SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + m...
> ^
>
> Gavin
>
Gavin,
I think Frank had the answer:
Try replacing extract('month',value) with extract('months' from value)
Look at the documentation for the syntax for further details.
Regards,
Ken
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] extracting from epoch values in pgsql
Hi folks,
I have a db that I need to draw some stats from. The db itself is from the
web application moodle which, perhaps to be cross-platform, uses unix epoch
times stored as integers throughout (see table description at end of mail).
I'd like to query some stats based on the appearance of objects over time,
ideally per month.
If the "time" were a pgsql timestamp, I'd probably do:
SELECT count(id), EXTRACT('month' FROM TIMESTAMP time) AS logmonth,
EXTRACT('year' FROM TIMESTAMP time) AS logyear
FROM mdl_log
WHERE action='login'
GROUP BY logmonth,logyear;
but it's an epoch time, so I need to convert to a datestamp and then run
EXTRACT on that (as far as I can see. I can do the conversion easily
enough but I can't then pass that to extract(). I've tried:
SELECT EXTRACT('month',(TIMESTAMP WITH TIME ZONE 'epoch' + mdl_log.time *
INTERVAL '1 second'))
FROM mdl_log;
SELECT time, to_timestamp(time) AS ts, EXTRACT('months',to_timestamp(time))
FROM mdl_log;
ERROR: syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('months',to_times...
I also tried doing the extract on the alias "ts".
Am I doing something wrong here? Is this possible or do I need to approach
it in a different way? I've looked through the manual but I can't see a
way to convert epoch->timestamp and then use it further.
Thanks in advance,
Gavin
moodle-01-01-2009=# \d mdl_log
Table "public.mdl_log"
Column | Type | Modifiers
++--
id | integer| not null default
nextval('mdl_log_id_seq'::regclass)
time | integer| not null default 0
userid | integer| not null default 0
ip | character varying(15) | not null default ''::character varying
course | integer| not null default 0
module | character varying(20) | not null default ''::character varying
cmid | integer| not null default 0
url| character varying(100) | not null default ''::character varying
info | character varying(255) | not null default ''::character varying
action | character varying(40) | not null default ''::character varying
Indexes:
"mdl_log_pkey" PRIMARY KEY, btree (id)
"mdl_log_act_ix" btree (action)
"mdl_log_cmi_ix" btree (cmid)
"mdl_log_coursemoduleaction_idx" btree (course, module, action)
"mdl_log_tim_ix" btree ("time")
"mdl_log_usecou_ix" btree (userid, course)
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Hibernate, web application and only one sequence for all primary keys
rawi wrote: Grails/Hibernate wishes per default one sequence for all tables-PKs and all PKs as BigInt. How is that a Hibernate default? Hibernate lets you define a multitude of types as a primary key, and the sequence each uses is a matter of XML or annotation configuration, at least in the Java version of Hibernate which is the only form of it that I've used. I've used both "old-fashioned" Hibernate with *.hbm.xml mapping descriptors, and the new-fangled JPA (Java Persistence API) version. I've used Hibernate with String and (long) integer key types, sequenced and not. For my learning, I use a system on Linux with Java and PostgreSQL. It works just fine. I'm not familiar with Grails. -- Lew -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
