[SQL] Hibernate, web application and only one sequence for all primary keys
Hello! The subject says it... Grails/Hibernate wishes per default one sequence for all tables-PKs and all PKs as BigInt. What would you think about a database with some tens of tables and incidentally low to moderate insert concurrency spread in about the half of the tables from at most 10 concurrent users with some 10-20 inserts each? And (for the eventuality of an unexpected need to scale up in the future - e.g. integration of multiple databases), from about which size would you expect performance penalties due to the sole sequence and the BigInt-PKs? I first intend to deploy it on an Intel Pentium 2 Duo (2.5-2.8GHz) with 3 GB RAM and SATA hard disk under Ubuntu Server. Thank you very much in advance! Regards Rawi -- View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp25490498p25490498.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- 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
SORRY for incorrectly posting this here! I couldn't move it to PostgreSQL - performance... afterwards. While I don't want to double-post: It would be perfect, if the mail list admin - please - could correct my mistake... Regards, Rawi -- View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp25490498p25490895.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- 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: > Hello! > > The subject says it... > > Grails/Hibernate wishes per default one sequence for all tables-PKs and all > PKs as BigInt. > > What would you think about a database with some tens of tables and > incidentally low to moderate insert concurrency spread in about the half of > the tables from at most 10 concurrent users with some 10-20 inserts each? 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. > > And (for the eventuality of an unexpected need to scale up in the future - > e.g. integration of multiple databases), from about which size would you > expect performance penalties due to the sole sequence and the BigInt-PKs? > > I first intend to deploy it on an Intel Pentium 2 Duo (2.5-2.8GHz) with 3 GB > RAM and SATA hard disk under Ubuntu Server. > > Thank you very much in advance! > Regards > Rawi cheers, leo -- 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 writes: > Grails/Hibernate wishes per default one sequence for all tables-PKs and all > PKs as BigInt. Redesign that software; this is fundamentally broken and stupid. regards, tom lane -- 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
Tom Lane-2 wrote: > >> Grails/Hibernate wishes per default one sequence for all tables-PKs and >> all >> PKs as BigInt. > > Redesign that software; this is fundamentally broken and stupid. > Thank you Tom... but redesigning Grails and Hibernate is far beyond my possibilities :) I could work around this in my app and explicitly ask Hibernate to use my hand made sequences in PostgreSQL. While this asks me for some more definition work, I wanted to know, if this is worth the effort... I know it now, thanks :) Kind regards, Rawi -- View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp25490498p25491915.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- 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
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 -- View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp25490498p25491924.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- 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
Tom Lane-2 wrote: > >> Grails/Hibernate wishes per default one sequence for all tables-PKs and >> all >> PKs as BigInt. > > Redesign that software; this is fundamentally broken and stupid. > Hi Tom, its me again... I only need to further understand... What for a source did have the OIDs in the past, as they were standard in each table? I thought they have been also generated by an unique sequence? Regards, Rawi -- View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp25490498p25491931.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- 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
Above all, do not fret about whether or not it is "cool to lose" some ids. There are plenty of integers; the ids need not be consecutive. I don't think Grails requires a single sequence source and I know hibernate does not. Hibernate will allow one to inject any sequence/id generator you wish to use (including one which generates UUIDs). 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 -- 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: > > 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... What you mean "loose 50 IDs"? Sequences are never meant to be gap-free therefore you are not "loosing" IDs at all. OTOH are you saying, that one session is just inserting one row and then disconnects from the database? Then it would be somewhat a waste to use hibernate and all this caching mechanism, but I highly doubt this. Isn't your app running on a middle tier and hibernate will only be shutdown when the app server will shut down? Then there is no "loosing" of IDs either. > 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... Why should you do that? You want to know, how much is the network roundtrip adding to the response time? Just let it how it is; it is a good starting point. > > Kind regards, Rawi > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Rules, functions and RETURNING
Hello list,
I am trying to wirte a rule which calls a PLPgSQL-function upon an
Insert in a table. Here is a somewhat simplified example of what i got
so far:
CREATE TABLE mytable (
mytable_id serial PRIMARY KEY,
something text
);
CREATE OR REPLACE FUNCTION _rule_insert_my(something text)
RETURNS integer AS
$BODY$
BEGIN
-- do something
return mytable_id;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
CREATE OR REPLACE RULE _insert AS
ON INSERT TO mytable DO INSTEAD SELECT
_rule_insert_my(new.something) AS mytable_id;
So far this works quite well. But I got a few situations where I need to
do a query which uses RETURNING to get the value of the newly generated
primary key. Like this one:
INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id;
This breaks because I did not specify a RETURNING-Clause in the rule.
But how can specify RETURNING with SELECT?
Thank your in advance for your help.
regards,
nico
--
Nico Mandery
--
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] extracting from epoch values in pgsql
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)
--
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, 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:
moodle-01-01-2009=# select time, to_timestamp(time) AS ts,
extract('month','to_timestamp(time)') from mdl_log LIMIT 10;
ERROR: syntax error at or near ","
LINE 1: ...ct time, to_timestamp(time) AS ts, extract('month','to_times...
^
moodle-01-01-2009=# select time, to_timestamp(time) AS ts,
extract('months','to_timestamp(time)') from mdl_log LIMIT 10;
ERROR: syntax error at or near ","
LINE 1: ...t time, to_timestamp(time) AS ts, extract('months','to_times...
^
Gavin
--
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
2009/9/17 Gavin McCullagh :
> 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:
>
> moodle-01-01-2009=# select time, to_timestamp(time) AS ts,
> extract('month','to_timestamp(time)') from mdl_log LIMIT 10;
> ERROR: syntax error at or near ","
> LINE 1: ...ct time, to_timestamp(time) AS ts, extract('month','to_times...
> ^
> moodle-01-01-2009=# select time, to_timestamp(time) AS ts,
> extract('months','to_timestamp(time)') from mdl_log LIMIT 10;
> ERROR: syntax error at or near ","
> LINE 1: ...t time, to_timestamp(time) AS ts, extract('months','to_times...
> ^
From manual:
http://www.postgresql.org/docs/current/interactive/functions-datetime.html
date_part('month',to_timestamp(time))
or
extract(month from to_timestamp(time))
Osvaldo
--
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, 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
--
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, 17 Sep 2009, Osvaldo Kussama wrote:
> From manual:
> http://www.postgresql.org/docs/current/interactive/functions-datetime.html
>
> date_part('month',to_timestamp(time))
> or
> extract(month from to_timestamp(time))
Gah. I don't know I missed that. This works fine.
SELECT extract(month from to_timestamp(time))
FROM mdl_log;
Many thanks,
Gavin
--
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
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:
Sorry; my typo. You used a comma in "extract" instead of "from".
--
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
Tom Lane wrote: > rawi writes: >> Grails/Hibernate wishes per default one sequence for all tables-PKs and all >> PKs as BigInt. > > Redesign that software; this is fundamentally broken and stupid. It's a pretty silly default, but it's clearly intended for simple / small databases. In any real database you'll be using one sequence per generated PK. I've had no problems with this with Hibernate, and am a bit puzzled that the OP is trying to fit their DB to Hibernate rather than doing the trivial configuration required to get Hibernate to fit the DB. The Hibernate documentation is pretty good, and covers this sort of thing well. -- Craig Ringer -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rules, functions and RETURNING
On Thursday 17 September 2009 8:35:52 am Nico Mandery wrote:
> Hello list,
>
> I am trying to wirte a rule which calls a PLPgSQL-function upon an
> Insert in a table. Here is a somewhat simplified example of what i got
> so far:
>
> CREATE TABLE mytable (
> mytable_id serial PRIMARY KEY,
> something text
> );
>
>
> CREATE OR REPLACE FUNCTION _rule_insert_my(something text)
> RETURNS integer AS
> $BODY$
> BEGIN
> -- do something
> return mytable_id;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
>
>
> CREATE OR REPLACE RULE _insert AS
> ON INSERT TO mytable DO INSTEAD SELECT
> _rule_insert_my(new.something) AS mytable_id;
>
>
> So far this works quite well. But I got a few situations where I need to
> do a query which uses RETURNING to get the value of the newly generated
> primary key. Like this one:
>
> INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id;
>
> This breaks because I did not specify a RETURNING-Clause in the rule.
> But how can specify RETURNING with SELECT?
>
>
> Thank your in advance for your help.
>
> regards,
> nico
>
> --
> Nico Mandery
I am going to assume that '--do something' is more complicated then getting the
mytable_id. If that is the case why not create an INSERT function/trigger that
does the 'something' and then just do:
INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id;
--
Adrian Klaver
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
