[SQL] PostgreSQL ontop of FreeBSD jails, maybe there is still hope!
It seems there are certain projects running at the moment that will eventually make possible to run PostgreSQL on FreeBSD's jail (virtual server on plain iron speed). Pre jail resource limits: http://freebsdfoundation.blogspot.com/2010/07/resource-containers-project.html Further generalization improvements: http://freebsdfoundation.blogspot.com/2010/06/update-on-jail-based-virtualization.html "Further, the project includes generalization of the virtual network stack framework, factoring out common code. This will provide an infrastructure and will ease virtualization of further subsystems like SYSV/Posix IPC with minimal overhead. All further virtualized subsystems will immediately benefit from shared debugging facilities, an essential feature for early adopters of the new technology." By solving the SYSV/IPC problems, PostgreSQL will be able to be run on jails, even easier and more robustly than it is the case now. Lets hope those guys will deliver smth good. -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PostgreSQL ontop of FreeBSD jails, maybe there is still hope!
On Wed, Jul 21, 2010 at 7:47 AM, Achilleas Mantzios wrote: > It seems there are certain projects running at the moment that will > eventually make possible > to run PostgreSQL on FreeBSD's jail (virtual server on plain iron speed). We've been doing that across the project infrastructure for 10 years or more. The only issue we run into is that we need to use a unique port in each jail as shared memory isn't entirely isolated between jails. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PostgreSQL ontop of FreeBSD jails, maybe there is still hope!
Στις Wednesday 21 July 2010 10:57:45 ο/η Dave Page έγραψε: > On Wed, Jul 21, 2010 at 7:47 AM, Achilleas Mantzios > wrote: > > It seems there are certain projects running at the moment that will > > eventually make possible > > to run PostgreSQL on FreeBSD's jail (virtual server on plain iron speed). > > We've been doing that across the project infrastructure for 10 years > or more. The only issue we run into is that we need to use a unique > port in each jail as shared memory isn't entirely isolated between > jails. Thats a pretty known workaround, still inconvenient and annoying for massive scale virtual environments. > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise Postgres Company > -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] UUID for Postgresql 8.4
Hi All, I have a column in my Postgresql database tables which need UUID. Is there any function in Pgsql for UUID generation. Please help me in this regard. -- Regards, -- Trinath Somanchi,
Re: [SQL] UUID for Postgresql 8.4
In response to Trinath Somanchi : > Hi All, > > I have a column in my Postgresql database tables which need UUID. > > Is there any function in Pgsql for UUID generation. Please help me in this > regard. http://www.postgresql.org/docs/8.4/static/uuid-ossp.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UUID for Postgresql 8.4
There's contrib module for it, uuid-ossp. It should be available as a package for most OSes and distributions, I guess. Hi All, > > I have a column in my Postgresql database tables which need UUID. > > Is there any function in Pgsql for UUID generation. Please help me in this > regard. > > -- > Regards, > -- > Trinath Somanchi, >
Re: [SQL] UUID for Postgresql 8.4
On Wed, Jul 21, 2010 at 05:49:53PM +0530, Trinath Somanchi wrote: > Hi All, > > I have a column in my Postgresql database tables which need UUID. > > Is there any function in Pgsql for UUID generation. Please help me in this > regard. > > -- > Regards, > -- > Trinath Somanchi, Check the page about the UUID type in the manual: http://www.postgresql.org/docs/8.4/interactive/datatype-uuid.html It contains some suggestions for generating them, including the contrib/uuid-ossp module: http://www.postgresql.org/docs/8.4/interactive/uuid-ossp.html Cheers, Ken -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Aggregates (last/first) not behaving
I have PostgreSQL 8.3.9 [PostgreSQL 8.3.9 on i386-apple-darwin10.3.0,
compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build
5646) (dot 1)]
and the custom first and last aggregates from:
http://wiki.postgresql.org/wiki/First_(aggregate)
http://wiki.postgresql.org/wiki/Last_(aggregate)
I have a simple table, of two columns. The first is a timestamp and is the
primary key, the second is an integer. I've loaded the table up with
values, one for every minute, for a whole year. Some SQL to recreate the
table and the aggregates can be retrieved from:
http://blog.devauld.ca/aggregate_test.zip (File is approximately 180KB)
Now when I try to make use of the first and last aggregates, I get:
# select first(t), last(t) from test group by extract(day from t);
first|last
-+-
2009-01-01 00:00:00 | 2009-01-01 17:02:00
2009-01-02 10:07:00 | 2009-01-02 10:06:00
2009-01-03 20:15:00 | 2009-01-03 20:14:00
2009-01-04 00:00:00 | 2009-01-04 23:59:00
2009-01-05 00:00:00 | 2009-01-05 23:59:00
2009-01-06 16:31:00 | 2009-01-06 16:30:00
2009-01-07 00:00:00 | 2009-01-07 23:49:00
2009-01-08 11:09:00 | 2009-01-08 11:42:00
2009-01-09 11:08:00 | 2009-01-09 00:51:00
2009-01-10 11:33:00 | 2009-01-10 23:37:00
2009-01-11 13:05:00 | 2009-01-11 23:59:00
2009-01-12 23:55:00 | 2009-01-12 23:47:00
2009-01-13 01:50:00 | 2009-01-13 23:36:00
2009-01-14 23:55:00 | 2009-01-14 23:41:00
2009-01-15 00:47:00 | 2009-01-15 23:40:00
2009-01-16 00:29:00 | 2009-01-16 23:38:00
2009-01-17 00:09:00 | 2009-01-17 23:37:00
2009-01-18 23:48:00 | 2009-01-18 23:37:00
2009-01-19 23:56:00 | 2009-01-19 23:39:00
2009-01-20 07:14:00 | 2009-01-20 23:36:00
2009-01-21 23:40:00 | 2009-01-21 23:41:00
2009-01-22 02:57:00 | 2009-01-22 23:40:00
2009-01-23 23:56:00 | 2009-01-23 23:38:00
2009-01-24 09:34:00 | 2009-01-24 23:37:00
2009-01-25 23:50:00 | 2009-01-25 23:37:00
2009-01-26 23:48:00 | 2009-01-26 23:39:00
2009-01-27 06:36:00 | 2009-01-27 23:37:00
2009-01-28 23:59:00 | 2009-01-28 23:41:00
2009-01-29 16:12:00 | 2009-01-29 23:40:00
2009-01-30 21:11:00 | 2009-01-30 23:39:00
2009-01-31 20:12:00 | 2009-01-31 16:20:00
(31 rows)
For some reason the aggregates are not falling into the proper group. I
can't blame timezones as the results are all over the map, and first/last
relationship is broken as in some cases 'last' is chronologically before
'first'
If I explicitly retrieve the values for midnight each day:
# select t, v from test where extract(hour from t) = 0 and extract(minute
from t) = 0;
t | v
-+---
2009-01-01 00:00:00 | 0
2009-01-02 00:00:00 | 1440
2009-01-03 00:00:00 | 2880
2009-01-04 00:00:00 | 4320
2009-01-05 00:00:00 | 5760
2009-01-06 00:00:00 | 7200
2009-01-07 00:00:00 | 8640
2009-01-08 00:00:00 | 10080
2009-01-09 00:00:00 | 11520
2009-01-10 00:00:00 | 12960
2009-01-11 00:00:00 | 14400
2009-01-12 00:00:00 | 15840
2009-01-13 00:00:00 | 17280
2009-01-14 00:00:00 | 18720
2009-01-15 00:00:00 | 20160
2009-01-16 00:00:00 | 21600
2009-01-17 00:00:00 | 23040
2009-01-18 00:00:00 | 24480
2009-01-19 00:00:00 | 25920
2009-01-20 00:00:00 | 27360
2009-01-21 00:00:00 | 28800
2009-01-22 00:00:00 | 30240
2009-01-23 00:00:00 | 31680
2009-01-24 00:00:00 | 33120
2009-01-25 00:00:00 | 34560
2009-01-26 00:00:00 | 36000
2009-01-27 00:00:00 | 37440
2009-01-28 00:00:00 | 38880
2009-01-29 00:00:00 | 40320
2009-01-30 00:00:00 | 41760
2009-01-31 00:00:00 | 43200
(31 rows)
I get back the values for which I am seeking. The pain is in finding the
last record in the day before. I would have thought that grouping by
date_trunc on month would have yeilded similar results to above:
# select first(t), first(v) from test group by date_trunc('day', t);
first| first
-+---
2009-01-01 00:00:00 | 0
2009-01-02 10:07:00 | 2047
2009-01-03 20:15:00 | 4095
2009-01-04 00:00:00 | 4320
2009-01-05 00:00:00 | 5760
2009-01-06 17:33:00 | 8253
2009-01-07 16:56:00 | 9656
2009-01-08 17:28:00 | 11128
2009-01-09 21:14:00 | 12794
2009-01-10 05:47:00 | 13307
2009-01-11 16:42:00 | 15402
2009-01-12 16:30:00 | 16830
2009-01-13 20:14:00 | 18494
2009-01-14 23:59:00 | 20159
2009-01-15 22:17:00 | 21497
2009-01-16 23:57:00 | 23037
2009-01-17 18:32:00 | 24152
2009-01-18 20:15:00 | 25695
2009-01-19 07:58:00 | 26398
2009-01-20 22:16:00 | 28696
2009-01-21 17:31:00 | 29851
2009-01-22 16:37:00 | 31237
2009-01-23 23:59:00 | 33119
2009-01-24 21:13:00 | 34393
2009-01-25 22:17:00 | 35897
2009-01-26 16:42:00 | 37002
2009-01-27 16:30:00 | 38430
2009-01-28 16:52:00 | 39892
2009-01-29 23:59:00 | 41759
2009-01-30 10:19:00 | 42379
2009-01-31 14:58:00 | 44098
(31 rows)
Looking at the plan:
# explain select first(t), first(v) from test group by date_trunc('day', t);
QUERY PLAN
-
