Re: [GENERAL] Time Series on Postgres (HOWTO?)
I think you want to look at kdb, onetick, and LIM (those are commercial). or potentially mongoDB where you could probably store a compressed ts directly in the db if you want. If you're not going to store each observation as a row, then why use a db at all. why not stick to flat files? -Whit On Fri, Jan 14, 2011 at 7:41 PM, bubba postgres wrote: > I've been googling, but haven't found a good answer to what I should do if I > want to store time series in Postgres. > My current solution is store serialized (compressed) blobs of data. > (So for example store 1 day worth of 1 minute samples (~1440 samples) stored > as one row in a bytea. (Plus meta data) > It would be nice if I could use 1 sample per column,(because updating > individual columns/samples is clear to me) but postgres doesn't compress the > row (which is bad because of high amount of repetitive data.. Easily 10X > bigger. > > I've been considering a Double[] array, which would get compressed, but > before I start down that path (I suppose I need to make some storedprocs to > update individual samples), has anyone built anything like this? Any open > source projects I should look at? > > Thanks. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] file output via trigger/queue
I am in a similar situation described by this thread: http://www.mail-archive.com/pgsql-general@postgresql.org/msg78604.html I need to write a few xml files to be consumed by a vendor application in real-time. The queue / (poll/cronjob) seems simple enough. Before I commit I just wondered if anyone else had gone down this road before. Perhaps with a solution more like: http://lethargy.org/~jesus/writes/pg_amqp-slides-from-pgeast2010. Feedback appreciated. Thanks, Whit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Jira and PostgreSQL
We have Jira + Confluence on postgres. Small site (25 users). No issues. -Whit On Mon, Aug 30, 2010 at 5:26 AM, Jayadevan M wrote: > Hello all, > Has any one worked with Jira on PostgreSQL? > We are considering Jira implementation for our organization (about 1500 > users). > The question is - "Jira on MySQL or Jira on PostgreSQL?" Any > tips/suggestions are welcome. We do not have much expertise in either of > these databases. > Oracle, the database we have expertise in, cannot be considered because of > the license costs :) > > Regards, > Jayadevan > > > > > > > DISCLAIMER: > > "The information in this e-mail and any attachment is intended only for > the person to whom it is addressed and may contain confidential and/or > privileged material. If you have received this e-mail in error, kindly > contact the sender and destroy all copies of the original communication. > IBS makes no warranty, express or implied, nor guarantees the accuracy, > adequacy or completeness of the information contained in this email or any > attachment and is not liable for any errors, defects, omissions, viruses > or for resultant loss or damage, if any, direct or indirect." > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] array question
That's exactly what I needed. Thanks very much!
-Whit
On Fri, Oct 9, 2009 at 12:29 PM, Raymond O'Donnell wrote:
> On 09/10/2009 17:17, Whit Armstrong wrote:
>> Is there any easy way to get this data:
>>
>> kls_dev=# select * from ary_values;
>> agent_name | myval
>> +---
>> a | 1
>> a | 2
>> a | 3
>> b | 4
>> b | 5
>> b | 6
>> (6 rows)
>>
>> to look like this:
>>
>> kls_dev=# select * from ary_test;
>> agent_name | vals
>> +-
>> a | {1,2,3}
>> b | {4,5,6}
>> (2 rows)
>
> Someone (Alvaro?) once posted a really handy aggregate which ought to do
> what you want:
>
> CREATE AGGREGATE array_accum(anyelement) (
> SFUNC=array_append,
> STYPE=anyarray,
> INITCOND='{}'
> );
>
> And then you'd call it like so:
>
> select agent_name, array_accum(myval) from ary_values
> group by agent_name;
>
>
> I hope that helps.
>
> Ray.
>
>
>
> --
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> r...@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> --
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] array question
Is there any easy way to get this data:
kls_dev=# select * from ary_values;
agent_name | myval
+---
a | 1
a | 2
a | 3
b | 4
b | 5
b | 6
(6 rows)
to look like this:
kls_dev=# select * from ary_test;
agent_name | vals
+-
a | {1,2,3}
b | {4,5,6}
(2 rows)
In other words, I would like to group by 'agent_name' and then convert
all the 'myvals' associated w/ that agent_name into an array.
I tried a few simple things like this:
kls_dev=# select array_append(myval) from ary_values group by agent_name;
but no luck so far.
Thanks,
Whit
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partitioning question -- how to guarantee uniqueness across partitions
Thanks, Tom. Let me give a little more detail on my actual data rather than the simple example I sent. I have a 60GB table of loan balances, which I've partitioned into 26 tables. The loan id's are a sequence of 6 characters, so the partitioning rule I've used is the first character of the loan id, which yields roughly equal sized partitions of 2.8 GB or so. Each loan can only have one balance per month, so the primary key on each partition is set to be loan_id and asofdate. However, this data is meant to be available via a rails application, hence, the need for a surrogate key of integers which is unique across the entire set of partitions. Creation of new rows in the partitioned tables should not be an issue under normal circumstances because I see that all of the child tables use the same sequence for generating new id's. However, what makes me nervous is that there is no explicit constraint in the database that prevents duplicate id's from being created, and I'm not sure how the rails app would react if for whatever reason duplicate id keys wound up in the table. Any suggestions? Thanks, Whit On Sun, Jun 28, 2009 at 1:27 PM, Tom Lane wrote: > Whit Armstrong writes: >> I have a simple example copied from the 8.3 manual on partitioning >> (http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html). > >> My question is, if you create a serial type in the parent table which >> is meant to be the primary key across all the partitions, how does one >> guarantee uniqueness of that key? > > One doesn't. That is not an appropriate way to set up a partitioned > table. You need a primary key that can actually be used as a meaningful > partitioning key. In this example, the id is completely useless and > what you should be looking at is making the data_value be the primary > key. > > regards, tom lane > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] partitioning question -- how to guarantee uniqueness across partitions
I have a simple example copied from the 8.3 manual on partitioning (http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html). My question is, if you create a serial type in the parent table which is meant to be the primary key across all the partitions, how does one guarantee uniqueness of that key? This example is contrived, but it shows that there is no constraint to prevent insertion of duplicate 'id' values into separate partition tables. (after running the example there should be two id=100 rows in the table). This makes sense because each partition only guarantees uniqueness for it's own rows, and the primary key constraint on the parent table never comes into play. Do I need to add a test in the trigger function to prevent insertion of duplicate 'id' values? I realize that if I used a partitioning function that was based on ranges of the primary key, then I would be guaranteed unique ranges of id for each table, but most partitioning examples I've seen usually test for date ranges which is similar to this example. Any practical thoughts on this issue would be appreciated. Thanks, Whit begin; CREATE TABLE part_main ( id serial primary key, data_value integer not null ); CREATE TABLE part_10 (CHECK (data_value <= 10)) inherits (part_main); ALTER TABLE part_10 ADD PRIMARY KEY (id); CREATE TABLE part_20 (CHECK (data_value > 10 and data_value <= 20 )) inherits (part_main); ALTER TABLE part_20 ADD PRIMARY KEY (id); CREATE OR REPLACE FUNCTION part_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.data_value <= 10) THEN INSERT INTO part_10 VALUES (NEW.*); ELSEIF ( NEW.data_value > 10 and NEW.data_value <= 20) THEN INSERT INTO part_20 VALUES (NEW.*); ELSE RAISE EXCEPTION 'value out of range. Fix the part_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_part_trigger BEFORE INSERT ON part_main FOR EACH ROW EXECUTE PROCEDURE part_insert_trigger(); insert into part_main (id,data_value) values (100,10), (100,20); commit; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] example of aggregate function for product
I needed to write a product aggregate function, and just happened to
find this example in the nodes to the 8.0 manual:
Ansis 13 Jan 2006 16:41:05
An aggregate multiplication function, an analog of "sum" (the same
should be defined also for other numeric types):
CREATE OR REPLACE FUNCTION mul2(FLOAT,FLOAT)
RETURNS FLOAT AS '
DECLARE
a ALIAS FOR $1;
b ALIAS FOR $2;
BEGIN
RETURN a*b;
END;
' LANGUAGE plpgsql;
CREATE AGGREGATE mul (
sfunc = mul2,
basetype = FLOAT,
stype = FLOAT,
initcond = '1'
);
but that example looks pretty different than the ones found in the 8.3
manual (avg for instance):
CREATE AGGREGATE avg (float8)
(
sfunc = float8_accum,
stype = float8[],
finalfunc = float8_avg,
initcond = '{0,0}'
);
and float8_accum is defined like this (in the postgres function list).
CREATE OR REPLACE FUNCTION float8_accum(double precision[], double precision)
RETURNS double precision[] AS
'float8_accum'
LANGUAGE 'internal' IMMUTABLE STRICT
COST 1;
ALTER FUNCTION float8_accum(double precision[], double precision)
OWNER TO postgres;
COMMENT ON FUNCTION float8_accum(double precision[], double precision)
IS 'aggregate transition function';
Are there any experts out there who have defined a product or
cumulative product function that abides best practices who would be
willing to share?
Thanks,
Whit
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to cancel a query in progress
Thanks. That's a relief. getting a few "page xxx is uninitialized --- fixing" warnings in the vacuum output, but seems like this should be expected since I killed an insert in progress. can anyone confirm that these warnings are ok? WARNING: relation "balances_primary" page 1883404 is uninitialized --- fixing WARNING: relation "balances_primary" page 1883405 is uninitialized --- fixing WARNING: relation "balances_primary" page 1883406 is uninitialized --- fixing -Whit On Wed, Jun 17, 2009 at 12:09 PM, Tom Lane wrote: > Whit Armstrong writes: >> Are there any integrity checks I need to run on the db after this type >> of crash and recovery, or is vacuum --all good enough? > > There isn't anything you need to do. Postgres crashes don't corrupt > on-disk data, as a general rule, and a SIGKILL crash seems particularly > safe from that. > > regards, tom lane > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to cancel a query in progress
Thanks, Tom. Lesson learned. Are there any integrity checks I need to run on the db after this type of crash and recovery, or is vacuum --all good enough? -Whit On Wed, Jun 17, 2009 at 11:19 AM, Tom Lane wrote: > Whit Armstrong writes: >> I had a few queries that were fired from pgAdmin, but failed to stop >> running after I killed the GUI. > >> I tried to stop the queries by killing the pid (of the process running >> the query, not the pid of the server) from the linux command line, and >> much to my surprise, the whole database went down and then recovered >> (using the data in pg_xlog I assume). > > I suppose you used "kill -9"? Don't do that. > >> So, now that I've learned this lesson, what is the proper way to kill >> a query, and can it be done from psql or are there special postgres >> tools to do this. > > kill -INT is a safe query-cancel method. > > regards, tom lane > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to cancel a query in progress
I had a few queries that were fired from pgAdmin, but failed to stop running after I killed the GUI. I tried to stop the queries by killing the pid (of the process running the query, not the pid of the server) from the linux command line, and much to my surprise, the whole database went down and then recovered (using the data in pg_xlog I assume). So, now that I've learned this lesson, what is the proper way to kill a query, and can it be done from psql or are there special postgres tools to do this. Thanks, Whit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] nagios -- number of postgres connections
anyone know a way to get nagios to monitor the number of postgres connections? Thanks, Whit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_relation_size, relation does not exist
Thanks, Frank. That works perfectly. Cheers, Whit On Tue, Jun 16, 2009 at 7:36 AM, Frank Heikens wrote: > The search_path isn't correct. You're looking for the size of tables in > schema 'econ' but you don't mention this schema inside the function > pg_relation_size(). Try to use the schemanames as well, saves you a lot of > problems with assumptions. > > This one should work: > > SELECT > tablename, > pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) > FROM > pg_tables > WHERE > schemaname = 'econ'; > > > Kind regard, > Frank > > > Op 16 jun 2009, om 13:17 heeft Whit Armstrong het volgende geschreven: > >> Does anyone know why I get an unknown relation error when I query for >> relation size? >> >> kls=# select tablename, pg_size_pretty(pg_relation_size(tablename)) >> from pg_tables where schemaname = 'econ' order by tablename; >> ERROR: relation "series_info" does not exist >> kls=# >> >> Is there a better way to do this query to avoid this error? >> >> Thanks, >> Whit >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_relation_size, relation does not exist
Does anyone know why I get an unknown relation error when I query for relation size? kls=# select tablename, pg_size_pretty(pg_relation_size(tablename)) from pg_tables where schemaname = 'econ' order by tablename; ERROR: relation "series_info" does not exist kls=# Is there a better way to do this query to avoid this error? Thanks, Whit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 10 TB database
I have a 300GB database, and I would like to look at partitioning as a possible way to speed it up a bit. I see the partitioning examples from the documentation: http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html Is anyone aware of additional examples or tutorials on partitioning? Thanks, Whit 2009/6/15 Grzegorz Jaśkiewicz : > On Mon, Jun 15, 2009 at 1:00 PM, Artur wrote: >> Hi! >> >> We are thinking to create some stocks related search engine. >> It is experimental project just for fun. >> >> The problem is that we expect to have more than 250 GB of data every month. >> This data would be in two tables. About 50.000.000 new rows every month. > > Well, obviously you need to decrease size of it, by doing some > normalization than. > If some information is the same across table, stick it into separate > table, and assign id to it. > > If you can send me sample of that data, I could tell you where to cut size. > I have that big databases under my wings, and that's where > normalization starts to make sens, to save space (and hence speed > things up). > >> We want to have access to all the date mostly for generating user requesting >> reports (aggregating). >> We would have about 10TB of data in three years. > > For that sort of database you will need partitioning for sure. > > > Napisz do mnie, to moge pomoc prywatnie, moze za niewielka danina ;) > > -- > GJ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] find column OID types with information schema?
ok, got it. thanks for the clarification and the hand holding. -Whit On Mon, Apr 27, 2009 at 1:09 PM, Tom Lane wrote: > Whit Armstrong writes: >> Am I misinterpreting this documentation? Are there cases in which the >> OID's of two tables will collide? I don't see any uniqueness >> constraints on the pg_class table. > > You didn't look too hard: > > regression=# \d pg_class > ... > Indexes: > "pg_class_oid_index" UNIQUE, btree (oid) > "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace) > > All system catalogs that have OIDs at all effectively treat them as a > primary key. If they weren't unique identifiers they wouldn't be good > for much ... > > What the documentation is trying to point out is that the uniqueness > guarantees don't extend across tables. So for example a table and a > datatype could by coincidence have the same OID, since they live in > different system catalogs. > > regards, tom lane > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] find column OID types with information schema?
Thanks, Tom. I guess the answer is, yes, but perhaps you can help me decide. I'm just reading this part of the documentation from the link I posted: "OIDs are not added to user-created tables, unless WITH OIDS is specified when the table is created." and also: The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables. Am I misinterpreting this documentation? Are there cases in which the OID's of two tables will collide? I don't see any uniqueness constraints on the pg_class table. Or are there cases in which a table does not have an OID in the pg_class table? I apologize for the dumb questions, but I'm just a little confused about the internals. Thanks, Whit On Mon, Apr 27, 2009 at 12:29 PM, Tom Lane wrote: > Whit Armstrong writes: >> However, there is no example that uses a schema + tablename. > > If you're into masochism you can do that with a join of pg_class and > pg_namespace. But what's usually easier for one-off queries is to > use the regclass converter: > > select attname, atttypid from pg_attribute > where attrelid = 'myschema.mytable'::regclass; > > Most likely you'll also want > > ... and attnum > 0 and not attisdropped > > to keep down the clutter. > > regards, tom lane > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] find column OID types with information schema?
(I had accidentally replied to Tom only on my reply) the OID's can be found as follows: SELECT * FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable'); from this page: http://www.postgresql.org/docs/8.3/interactive/datatype-oid.html However, there is no example that uses a schema + tablename. -Whit On Mon, Apr 27, 2009 at 11:53 AM, Whit Armstrong wrote: > Thanks, Tom. > > So, it's more like this: > > select attname, atttypid from pg_attribute where attrelid = of my table>; > > hmm, so how do I find the attrelid of my table? I don't see it in pg_tables. > > -Whit > > > > On Mon, Apr 27, 2009 at 11:38 AM, Tom Lane wrote: >> Whit Armstrong writes: >>> Is it possible to find out the OID types of the columns of a table >>> using the information schema? >> >> No. Type OIDs are a Postgres-ism so they are not reflected in the >> standards-mandated contents of the information_schema. If you want >> OIDs you'll need to look at the underlying catalogs (pg_attribute >> in particular). >> >> regards, tom lane >> > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] find column OID types with information schema?
Is it possible to find out the OID types of the columns of a table using the information schema? I see that I can get the character names of the types using this query: select * from information_schema.columns where table_name = 'my_table'; but I don't see a way to find the actual OID types of the columns. Is that possible? Thanks, Whit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq -- reading a timestamp with time zone using binary format
Thanks, Tom. That's just what I needed. -Whit On Mon, Mar 23, 2009 at 7:15 PM, Tom Lane wrote: > Whit Armstrong writes: >> but it is still unclear (at least to me) how to determine as the >> client whether the server has been compiled with the >> HAVE_INT64_TIMESTAMP flag. > > You look at the integer_datetimes parameter. You could execute a > SQL "SHOW" command, but in a libpq client it's sufficient to use > PQparameterStatus(), because this value is sent automatically > during connection startup. > > regards, tom lane > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq -- reading a timestamp with time zone using binary format
how does one determine whether libpq is sending an int64 or a double? I see all of the #ifdefs in the source: #ifdef HAVE_INT64_TIMESTAMP static int64 time2t(const int hour, const int min, const int sec, const fsec_t fsec); #else static double time2t(const int hour, const int min, const int sec, const fsec_t fsec); #endif but it is still unclear (at least to me) how to determine as the client whether the server has been compiled with the HAVE_INT64_TIMESTAMP flag. since both int64 and double use 8 bytes of storage on x86_64, you can't determine the type using PQfsize. Any suggestions would be appreciated. -Whit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
