[SQL] vacuum / analyze parent tables on partitioned tables.
Hello, I wrote a script to make sure all tables are vacuumed and analyzed every evening. This works very well, but I have a question: I save in a table the start and end time of a vacuum/analyze. This way I can measure what tables take a long time to vaccum/analyze, and what tables are slow. (and much more). But I have noticed that the parent table of a partitioned table also takes a long time. Here is a snap shot of the following table table_name ; avg runt time; max run time; min run time "f_transaction_1";"00:03:07.8";"00:03:10";"00:03:03" "f_transaction";"00:02:19.8";"00:02:25";"00:02:16" f_tranaction_1 is 15GB data + 12GB of indexes. (I know, a lot of indexes). f_tranaction is totally empy, but also contains all indexes. Which means 0B table zise, and 140kB index size. Does anyone has an idea why in this case the vacuum/analyze takes almost as long on the parent table as on the biggest child table? (the other child tables are smaller, and their vacuum/analyze time is much shorter). wkr, Bert -- Bert Desmet 0477/305361
[SQL] Range types (DATERANGE, TSTZRANGE) in a foreign key with "inclusion" logic
Hello everybody, first a big thank you to all that make the range types possible. They are great, especially if one runs a database to manage a student's university dormitory with a lot of temporal information like rental agreements, room allocations, etc. At the moment we are redesigning our database scheme for PosgreSQL 9.2, because the new range types and especially the "EXCLUSION" constraints allow to put a lot more (business) logic into the database scheme than before. But there is one feature missing (or I am too stupid to find it). Let's say we have some kind of container with a lifetime attribute, i.e. something like that CREATE TABLE container ( id SERIAL PRIMARY KEY, lifetime DATERANGE ); Further, there are items that must be part of the container and these items have a lifetime, too. CREATE TABLE item ( id SERIAL PRIMARY KEY, container_id INTEGER, lifetime DATERANGE, FOREIGN KEY (container_id) REFERENCES container ( id ), EXCLUDE USING gist ( container_id WITH =, lifetime WITH && ) ); The foreign key ensures that items are only put into containers that really exist and the exclude constraint ensure that only one item is member of the same container at any point of time. But actually I need a little bit more logic. The additional contraint is that items must only be put into those containers whose lifetime covers the lifetime of the item. If an item has a lifetime that exceeds the lifetime of the container, the item cannot be put into that container. If an item is already in a container (with valid lifetimes) and later the container or the item is updated such that either lifetime is modified and the contraint is not fullfilled any more, this update must fail. I would like to do someting like: FOREIGN KEY ( container_id, lifetime ) REFERENCES other_table ( id, lifetime ) USING gist ( container_id WITH =, lifetime WITH <@ ) (Of course, this is PosgreSQL-pseudo-code, but it hopefully make clear what I want.) So, now my questions: 1) Does this kind of feature already exist in 9.2? If yes, a link to the documentation would be helpful. 2) If this feature does not directly exist, has anybody a good idea how to mimic the intended behaviour? 3) If neither 1) or 2) applies, are there any plans to integrate such a feature? I found this discussion http://www.postgresql.org/message-id/4f8bb9b0.5090...@darrenduncan.net . Does anybody know about the progress? Having range types and exclusion contraints are nice, as I said in the introdruction. But if the reverse (foreign key with inclusion) would also work, the range type feature would really be amazing. Best regards, Matthias Nagel -- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.na...@gmail.com ICQ: 499797758 Skype: nagmat84 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem with extract(epoch from interval ...
Hi,
I have a little problem with extract epoch from interval. It seems that
the query works if the interval is in a string but not from DB field.
Could someone provide support.
Thanks in advance.
Best regards,
Kaloyan Iliev
db=# begin;
BEGIN
db=# SELECT version();
version
-
PostgreSQL 9.1.6 on amd64-portbld-freebsd9.1, compiled by cc (GCC)
4.2.1 20070831 patched [FreeBSD], 64-bit
(1 row)
db=# SELECT EXTRACT(EPOCH FROM INTERVAL '3 months');
date_part
---
7776000
(1 row)
db=# CREATE TABLE a( b interval);
CREATE TABLE
db=# INSERT INTO a VALUES ('1 month');
INSERT 0 1
db=# INSERT INTO a VALUES ('3 month');
INSERT 0 1
db=# INSERT INTO a VALUES ('6 month');
INSERT 0 1
dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a;
ERROR: syntax error at or near "b"
LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a;
--
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] Problem with extract(epoch from interval ...
Hi 2013/1/23 Kaloyan Iliev : > Hi, > I have a little problem with extract epoch from interval. It seems that the > query works if the interval is in a string but not from DB field. > Could someone provide support. (...) You have a casting error; instead of: > dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a; > ERROR: syntax error at or near "b" > LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a; use SELECT extract(EPOCH FROM b::INTERVAL) FROM a; or SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a; Regards Ian Barwick -- 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] Problem with extract(epoch from interval ...
Thanks, It works perfectly. Regards, Kaloyan Iliev On 23.01.13 13:00, Ian Lawrence Barwick wrote: Hi 2013/1/23 Kaloyan Iliev : Hi, I have a little problem with extract epoch from interval. It seems that the query works if the interval is in a string but not from DB field. Could someone provide support. (...) You have a casting error; instead of: dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a; ERROR: syntax error at or near "b" LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a; use SELECT extract(EPOCH FROM b::INTERVAL) FROM a; or SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a; Regards Ian Barwick -- 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] Re: [SQL] need some magic with generate_series()
I'm sorry to prove that daft. :( generate_series needs the startdate of every project to generate the specific list of monthnumbers for every project. To join against this the list needs to have a column with the project_id. So I get something like this but still I cant reference the columns of the projects within the query that generates the series. with projectstart ( project_id, startdate ) as ( select project_id, startdate fromprojects ) select project_id, m fromprojectstartas p left join ( select p.project_id, to_char ( m, 'MM' )::integer fromgenerate_series ( p.startdate, current_date, '1 month'::interval ) as m ) as x using ( project_id ); Am 23.01.2013 01:08, schrieb Alexander Gataric: I would create a common table expression with the series from Filip and left join to the table you need to report on. Sent from my smartphone - Reply message - From: "Andreas" To: "Filip Rembiałkowski" Cc: "jan zimmek" , Subject: [SQL] need some magic with generate_series() Date: Tue, Jan 22, 2013 4:49 pm Thanks Filip, with your help I came a step further. :) Could I do the folowing without using a function? CREATE OR REPLACE FUNCTION month_series ( date ) RETURNS table ( monthnr integer ) AS $BODY$ select to_char ( m, 'MM' )::integer fromgenerate_series ( $1, current_date, '1 month'::interval ) as m $BODY$ LANGUAGE sql STABLE; select project_id, month_series ( createdate ) fromprojects order by 1, 2; Am 22.01.2013 22:52, schrieb Filip Rembiałkowski: > or even > > select m from generate_series( '20121101'::date, '20130101'::date, '1 > month'::interval) m; > > > > On Tue, Jan 22, 2013 at 3:49 PM, jan zimmek wrote: >> hi andreas, >> >> this might give you an idea how to generate series of dates (or other datatypes): >> >> select g, (current_date + (g||' month')::interval)::date from generate_series(1,12) g; >> >> regards >> jan >> >> Am 22.01.2013 um 22:41 schrieb Andreas : >> >>> Hi >>> I need a series of month numbers like 201212, 201301 MM to join other sources against it. >>> >>> I've got a table that describes projects: >>> projects ( id INT, project TEXT, startdate DATE ) >>> >>> and some others that log events >>> events( project_id INT, createdate DATE, ...) >>> >>> to show some statistics I have to count events and present it as a view with the project name and the month as MM starting with startdate of the projects. >>> >>> My problem is that there probaply arent any events in a month but I still need this line in the output. >>> So somehow I need to have a select that generates: >>> >>> project 7,201211 >>> project 7,201212 >>> project 7,201301 >>> >>> It'd be utterly cool to get this for every project in the projects table with one select. >>> >>> Is there hope? >>> >>> >>> -- >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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] Problem with extract(epoch from interval ...
On 01/23/2013 03:00 AM, Ian Lawrence Barwick wrote: Hi 2013/1/23 Kaloyan Iliev : Hi, I have a little problem with extract epoch from interval. It seems that the query works if the interval is in a string but not from DB field. Could someone provide support. (...) You have a casting error; instead of: dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a; ERROR: syntax error at or near "b" LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a; use SELECT extract(EPOCH FROM b::INTERVAL) FROM a; or SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a; Would it not be simpler: test=> SELECT extract(epoch from b) from a; date_part --- 2592000 7776000 15552000 (3 rows) As the b field is already an interval. Regards Ian Barwick -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to access multicolumn function results?
Hi I've got functions that return a TABLE. If I call it with constant parameters like: SELECT * FROM some_fct( 42 ); I get a table with column names as the result as intended. When I rather call it with the parameter coming from another table I get a set of records where I don't know how to access specific columns: SELECT some_fct( some_id ) FROM some_other_table; Now I get e.g. the result ( id1, value11, value12, value13 ), ( id1, value14, value15, value16 ), ( id2, value24, value25, value26 ), ... How can I split this up to look like a normal table or view with the column names that are defined in the RETURNS TABLE ( ... ) expression of the function. -- 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] How to access multicolumn function results?
Andreas writes: > SELECT some_fct( some_id ) FROM some_other_table; > How can I split this up to look like a normal table or view with the > column names that are defined in the RETURNS TABLE ( ... ) expression of > the function. The easy way is SELECT (some_fct(some_id)).* FROM some_other_table; If you're not too concerned about efficiency, you're done. However this isn't very efficient, because the way the parser deals with expanding the "*" is to make N copies of the function call, as you can see with EXPLAIN VERBOSE --- you'll see something similar to Output: (some_fct(some_id)).fld1, (some_fct(some_id)).fld2, ... If the function is expensive enough that that's a problem, the basic way to fix it is SELECT (ss.x).* FROM (SELECT some_fct(some_id) AS x FROM some_other_table) ss; With a RETURNS TABLE function, this should be good enough. With simpler functions you might have to insert OFFSET 0 into the sub-select to keep the planner from "flattening" it into the upper query and producing the same multiple-evaluation situation. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
