Re: [GENERAL] PostgreSQL Point In Time Recovery
Jeff Janes wrote I restore from my base backup plus WAL quite often. It is how I get a fresh dev or test instance when I want one. (It is also how I have confidence that everything is working well and that I know what I'm doing should the time come to do a real restore). When that starts to take an annoyingly long time, I run a new base backup. How often that is, can be anywhere from days to months, depending on what's going on in the database. Cheers, Jeff That makes sense. So we take a new base backup once we feel Hey , recovery may take time. Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775872.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] PostgreSQL Point In Time Recovery
Alan Hodgson wrote That's basically what warm standby's do, isn't it? As long as they keep recovery open it should work. A warn standby will be almost in sync with the primary, right? So recovery to point-in-time (like 10 AM this morning) won't be possible. We need a base, but it shouldn't be so old that it takes hours to catchup- that was my thought. As John mentioned, looking at the WAL/transaction numbers, time to recover etc need to be looked at. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775874.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] (collation) Building postgresql on FreeBSD, the pros and cons of icu
On 23/10/2013 16:44, Tom Lane wrote: Patrick Dung patrick_...@yahoo.com.hk writes: By default, FreeBSD ports does not build postgresql with icu (http://www.icu-project.org/). Postgres does not have any option to use ICU, default or otherwise. Nor is it likely to happen in future, judging from previous discussions of the idea. Hi Tom, Patrick FreeBSD indeed has a config option to build with ICU, just # /usr/ports/databases/postgresql93-server # make config and you will be able to see this. The relevant README is here : http://people.freebsd.org/~girgen/postgresql-icu/README.html Patrick also you may build postgresql by hand and apply the patch manually from : /usr/ports/databases/postgresql93-server Although being in a non-english speaking company, i have not tried this neither at work or at home. Hope that helps. regards, tom lane -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recheck conditions on indexes
Hi, I'm just wondering: in the execution plan such as this one, is the Recheck Cond phase what it apparently looks like: an additional check on the data returned by indexes, and why is it necessary? I would have though that indexes are accurate enough? cms= explain analyze select * from users where other_ids-'OIB'='70328909364' or code='0023017009'; QUERY PLAN -- Bitmap Heap Scan on users (cost=8.52..39.21 rows=10 width=330) (actual time=0.042..0.044 rows=2 loops=1) Recheck Cond: (((other_ids - 'OIB'::text) = '70328909364'::text) OR ((code)::text = '0023017009'::text)) - BitmapOr (cost=8.52..8.52 rows=10 width=0) (actual time=0.035..0.035 rows=0 loops=1) - Bitmap Index Scan on users_other_ids_oib (cost=0.00..4.26 rows=9 width=0) (actual time=0.023..0.023 rows=1 loops=1) Index Cond: ((other_ids - 'OIB'::text) = '70328909364'::text) - Bitmap Index Scan on users_code (cost=0.00..4.26 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1) Index Cond: ((code)::text = '0023017009'::text) Total runtime: 0.082 ms (8 rows) Both indexes are plain btrees, the first one is on the expression on the hstore field (other_ids-'OIB') and the second one on a plain text field. Also, why is it using the Bitmap Index Scan in both cases? A plain query for code='foo' uses a plain index scan. This is PostgreSQL 9.1. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Recheck conditions on indexes
Ivan Voras wrote: I'm just wondering: in the execution plan such as this one, is the Recheck Cond phase what it apparently looks like: an additional check on the data returned by indexes, and why is it necessary? I would have though that indexes are accurate enough? cms= explain analyze select * from users where other_ids-'OIB'='70328909364' or code='0023017009'; QUERY PLAN -- Bitmap Heap Scan on users (cost=8.52..39.21 rows=10 width=330) (actual time=0.042..0.044 rows=2 loops=1) Recheck Cond: (((other_ids - 'OIB'::text) = '70328909364'::text) OR ((code)::text = '0023017009'::text)) - BitmapOr (cost=8.52..8.52 rows=10 width=0) (actual time=0.035..0.035 rows=0 loops=1) - Bitmap Index Scan on users_other_ids_oib (cost=0.00..4.26 rows=9 width=0) (actual time=0.023..0.023 rows=1 loops=1) Index Cond: ((other_ids - 'OIB'::text) = '70328909364'::text) - Bitmap Index Scan on users_code (cost=0.00..4.26 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1) Index Cond: ((code)::text = '0023017009'::text) Total runtime: 0.082 ms (8 rows) Both indexes are plain btrees, the first one is on the expression on the hstore field (other_ids-'OIB') and the second one on a plain text field. Also, why is it using the Bitmap Index Scan in both cases? A plain query for code='foo' uses a plain index scan. This is PostgreSQL 9.1. Just because there is an entry in the index does not imply that the corresponding table entry is visible for this transaction. To ascertain that, the table row itself has to be checked. PostgreSQL 9.2 introduced index only scan which avoids that additional step if it is safe to do so. Yours, Laurenz Albe -- 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] Replication and fsync
DDT wrote: According to manual, when you set synchronous_commit to on, the transaction commits will wait until master and slave flush the commit record of transaction to the physical storage, so I think even if turn off the fsync on master is safe for data consistency and data will not be lost if slave physical storage is not damaged. I don't think that this is safe. What if the master crashes and becomes corrupted as a consequence? It will start sending corrupted data to the slave, which will replay it, thus becoming corrupted itself. Yours, Laurenz Albe -- 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] Recheck conditions on indexes
On 25/10/2013 11:06, Albe Laurenz wrote: Just because there is an entry in the index does not imply that the corresponding table entry is visible for this transaction. To ascertain that, the table row itself has to be checked. Understood. PostgreSQL 9.2 introduced index only scan which avoids that additional step if it is safe to do so. It doesn't help in this case - the plan for the same query on a copy of the database on 9.3 is exactly the same. signature.asc Description: OpenPGP digital signature
[GENERAL] pg_upgrade 9.1.9 -9.3.1
Hello, I'm evaluating pg_upgrade and there seems to be something wrong with my test: the data get copied within the old data directory instead of the new one Do I have to explicitely set more option or define some environment variables ? If this is of concern, there are some redirections with symlinks within the old $PGDATA ./pg_upgrade \ --old-datadir /data/postgresql-data-9\ --new-datadir /pgdata/postgresql_93-data-9\ --old-bindir /opt/intershop/postgresql-9.1.9-9/bin\ --new-bindir /opt/intershop/postgresql-9.3.1-9/bin = ll /data/postgresql-data-9/tblspc_data/cicpg_logs/ drwx-- 3 isdb9 isgrp9 4096 Oct 21 15:48 PG_9.1_201105231 drwx-- 3 isdb9 isgrp9 4096 Oct 25 12:26 PG_9.3_201306121 thanks, Marc Mamin -- 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] Count of records in a row
On 2013-10-24 17:09, Robert James wrote:
On 10/22/13, Elliot yields.falseh...@gmail.com wrote:
It looks like you already found a solution, but here's one with a CTE. I
cobbled this together from an older query I had for doing something
similar, for which I unfortunately lost the original source of this
approach. Also, this implies that there is something that gives an
ordering to these rows (in this case, the field i).
create temp table data (i int, val char);
insert into data (val, i)
values
('A',1),
('A',2),
('A',3),
('B',4),
('C',5),
with x
as
(
select i,
row_number() over () as xxx,
val,
row_number() over (partition by val order by i asc)
- row_number() over () as d
from data
order by i
)
select val,
count(*)
from x
group by d,
val
order by min(i)
;
Elliot - Thanks for this great solution; I've tested in on my data and
it gives great results.
I'd like to understand your code. I believe I understand most of it.
Can you explain what 'd' is?
And this clause row_number() over (partition by val order by i asc) -
row_number() over () as d?
(Hey, while I'm at it, is there a descriptive name for x too?)
Thanks
Glad I could help. It's easier to understand if you break apart the CTE.
I'm also moving around the order by i to clean this up a little. Sorry
for the formatting.
Running this:
select i,
val,
row_number() over (partition by val order by i asc) as class_i,
row_number() over (order by i asc) as overall_i,
row_number() over (partition by val order by i asc)
- row_number() over () as d
from data
Yields this:
ivalclass_ioverall_id
1A110
2A220
3A330
4B14-3
5C15-4
6A46-2
7D17-6
8A58-3
9A69-3
10D210-8
11D311-8
12B212-10
13C213-11
14C314-11
class_i counts the row number within a class and overall_i counts the
overall row number in the sequence. Here's just one class extracted to
emphasize that:
ivalclass_ioverall_id
1A110
2A220
3A330
6A46-2
8A58-3
9A69-3
Within a given consecutive run of a particular class the difference
between class_i and overall_i will always be the same (because they're
both increasing by the same amount) but that difference between runs
will always be different (because each run starts the sequences at
different offsets). d is the difference of the two. Because that value
segments the runs, all that needs to be done is group by it and count
the items in the group to get the length of the runs.
The xxx column was junk left over from copying and pasting and
verifying. Apologies :). This is a cleaned up version:
with x
as
(
select i,
val,
row_number() over (partition by val order by i asc)
- row_number() over (order by i asc) as d
from data
)
select val,
count(*)
from x
group by d,
val
order by min(i)
;
--
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_upgrade 9.1.9 -9.3.1
On 10/25/13, 7:20 AM, Marc Mamin wrote: Hello, I'm evaluating pg_upgrade and there seems to be something wrong with my test: the data get copied within the old data directory instead of the new one Do I have to explicitely set more option or define some environment variables ? If this is of concern, there are some redirections with symlinks within the old $PGDATA ./pg_upgrade \ --old-datadir /data/postgresql-data-9\ --new-datadir /pgdata/postgresql_93-data-9\ --old-bindir /opt/intershop/postgresql-9.1.9-9/bin\ --new-bindir /opt/intershop/postgresql-9.3.1-9/bin = ll /data/postgresql-data-9/tblspc_data/cicpg_logs/ drwx-- 3 isdb9 isgrp9 4096 Oct 21 15:48 PG_9.1_201105231 drwx-- 3 isdb9 isgrp9 4096 Oct 25 12:26 PG_9.3_201306121 It appears you are using tablespaces. In that case, that's normal. -- 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_upgrade 9.1.9 -9.3.1
From: Peter Eisentraut [mailto:pete...@gmx.net] On 10/25/13, 7:20 AM, Marc Mamin wrote: Hello, I'm evaluating pg_upgrade and there seems to be something wrong with my test: the data get copied within the old data directory instead of the new one Do I have to explicitely set more option or define some environment variables ? If this is of concern, there are some redirections with symlinks within the old $PGDATA ./pg_upgrade \ --old-datadir /data/postgresql-data-9\ --new-datadir /pgdata/postgresql_93-data-9\ --old-bindir /opt/intershop/postgresql-9.1.9-9/bin\ --new-bindir /opt/intershop/postgresql-9.3.1-9/bin = ll /data/postgresql-data-9/tblspc_data/cicpg_logs/ drwx-- 3 isdb9 isgrp9 4096 Oct 21 15:48 PG_9.1_201105231 drwx-- 3 isdb9 isgrp9 4096 Oct 25 12:26 PG_9.3_201306121 It appears you are using tablespaces. In that case, that's normal. which means I cannot take advantage of a separate new file system for the copy and first have to organize enough free place in the old one. correct ? thanks, Marc -- 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] Recheck conditions on indexes
Ivan Voras ivo...@freebsd.org writes: I'm just wondering: in the execution plan such as this one, is the Recheck Cond phase what it apparently looks like: an additional check on the data returned by indexes, and why is it necessary? Bitmap indexscans are potentially lossy. If the bitmap recording all the tuple locations reported by the index gets too big, we compress entries by remembering only that a particular page has to be visited, not the precise tuples on that page. Once this happens, the indexed condition has to be rechecked at each tuple on the page, once we finally get to the point of visiting it. The recheck condition isn't used on pages that didn't become lossy. Recheck conditions are also used for cases where the index isn't able to test the query WHERE condition exactly, such as anchored LIKE conditions. That case doesn't apply to your example, though. Also, why is it using the Bitmap Index Scan in both cases? We don't support ORed index conditions in plain index scans, much less use of more than one index. The only mechanism that can implement that is a BitmapOr. 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] Need help how to manage a couple of daily DB copies.
Le vendredi 25 octobre 2013 à 04:50 +0200, Andreas a écrit :
well, not quite
We are not talking about files but databases within the db server.
Lets keep 3 copies total
the idea is to start with the database db_test today (2013/10/24)
2013/10/25: rename db_test to db_test_13025 and import the latest
dump into a new db_test
2013/10/26: rename db_test to db_test_13026 ... import
2013/10/27: rename db_test to db_test_13027 ... import
2013/10/28: rename db_test to db_test_13028 ... import
Now we've got db_test and 4 older copies.
Find the oldest copy and drop it. -- drop db_test_131025
or better every day drop every copy but the 3 newest.
and so on
this needs to be done by an external cron script or probaply by a
function within the postgres database or any other administrative database.
The point is to give the assistant a test-db where he could mess things up.
In the event he works longer than a day on a task his work shouldn't be
droped completely when the test-db gets automatically replaced.
I assume db_test is created from a dump file? if that's the case, and if
your system allows it, using logrotate on the dump is very
straithforward; e.g. to rotate an archive everyday, keeping a weekly
archive over 52 two weeks, simply create the
file /etc/logrotate.d/myapp :
#Create rotation for myapp's backups
/var/backups/myapp/myapp.gz {
weekly
missingok
rotate 52
notifempty
}
--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance
--
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] PostgreSQL Point In Time Recovery
On Thursday, October 24, 2013 11:13:34 PM Jayadevan wrote: Alan Hodgson wrote That's basically what warm standby's do, isn't it? As long as they keep recovery open it should work. A warn standby will be almost in sync with the primary, right? So recovery to point-in-time (like 10 AM this morning) won't be possible. We need a base, but it shouldn't be so old that it takes hours to catchup- that was my thought. As John mentioned, looking at the WAL/transaction numbers, time to recover etc need to be looked at. Well, yeah. The point was that you possibly could run it for a while to catch up without taking a new base backup if you desired. You should also keep copies of it for PITR. -- 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] Count of records in a row
Ingenious! I actually think, however, there was a subtle bug in, though I see you fixed it. The line: - row_number() over () as d needs to be: - row_number() over (order by i asc) as d I discovered this when working your code into my application. I got very, very weird results - with one order of columns in the select, I got the correct answer, but with another one I didn't. After much debugging, I realized that the original version (- row_number over ()) wasn't defined! So, depending on how I wrote the select statement, Postgres could pick different orders! But I see your cleaned up version already fixed this! On 10/25/13, Elliot yields.falseh...@gmail.com wrote: Glad I could help. It's easier to understand if you break apart the CTE. I'm also moving around the order by i to clean this up a little. Sorry for the formatting. Running this: select i, val, row_number() over (partition by val order by i asc) as class_i, row_number() over (order by i asc) as overall_i, row_number() over (partition by val order by i asc) - row_number() over () as d from data Yields this: ivalclass_ioverall_id 1A110 2A220 3A330 4B14-3 5C15-4 6A46-2 7D17-6 8A58-3 9A69-3 10D210-8 11D311-8 12B212-10 13C213-11 14C314-11 class_i counts the row number within a class and overall_i counts the overall row number in the sequence. Here's just one class extracted to emphasize that: ivalclass_ioverall_id 1A110 2A220 3A330 6A46-2 8A58-3 9A69-3 Within a given consecutive run of a particular class the difference between class_i and overall_i will always be the same (because they're both increasing by the same amount) but that difference between runs will always be different (because each run starts the sequences at different offsets). d is the difference of the two. Because that value segments the runs, all that needs to be done is group by it and count the items in the group to get the length of the runs. The xxx column was junk left over from copying and pasting and verifying. Apologies :). This is a cleaned up version: with x as ( select i, val, row_number() over (partition by val order by i asc) - row_number() over (order by i asc) as d from data ) select val, count(*) from x group by d, val order by min(i) ; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Detecting change in event properties
I have a table of (timed) events, and I'm interested in marking events whose properties have changed from the previous event. I believe this can be done with window functions, but I'm not sure how. What window function can give me a field from the _previous_ row? (To elaborate, I'm interested in: * Finding field x of the _previous_ row * Finding field x of the _next_ row * Finding field x of the _previous_ row that meets a certain criteria (which the current row may or may not meet) ) I must say that window functions are amazing - they're a whole new world, really. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Window functions and relational algebra
I'm just discovering the power of window functions. Is there any mathematical formalism for them, similar to relational algebra? It would seem to me that window functions aren't expressable in pure relational algebra, but that a well defined extension to it would be possible to express them; at least for those of us so inclinded, being able to think in terms of an alegbra would make reasoning about them much more powerful. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Increasing CPU usage of PostgreSQL
Hello All, I am using jdbcrunner-1.2 to run PostgreSQL performance tests. For certain tests, i need to increase the CPU usage of the servers especially at user level. I tried using both tpcc and tpcb load with scale factor of 100. Even after setting the number of client connections as high as 420, I am unable to achieve high CPU usage. It is hardly 3 percent. I think this is because most of the client connections are idle. The tpcc and tpcb transactions seem to be consuming very less CPU. In postgresql.conf file, I have lowered checkpoint segments to 1 in order to overwhelm the server with checkpoints but no significant increase in iowait of the CPU. Can somebody suggest a better idea to load PostgreSQL servers to increase CPU usage.? Configurations of my machine is: Processors: Xeon E5-2650 Processor Kit Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T, 20 MB) * 2 nos RAM : 32GB DDR3-1600 REG Memory Kit 8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800) HDD: 450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos 1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s Disk Speed : 10,000 RPM RAID Controller (512MB, RAID 0/1) Thank you, -- 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] Increasing CPU usage of PostgreSQL
On Fri, Oct 25, 2013 at 8:29 AM, Rahila Syed rahilasye...@gmail.com wrote: Hello All, I am using jdbcrunner-1.2 to run PostgreSQL performance tests. For certain tests, i need to increase the CPU usage of the servers especially at user level. I tried using both tpcc and tpcb load with scale factor of 100. Even after setting the number of client connections as high as 420, I am unable to achieve high CPU usage. It is hardly 3 percent. I think this is because most of the client connections are idle. The tpcc and tpcb transactions seem to be consuming very less CPU. In postgresql.conf file, I have lowered checkpoint segments to 1 in order to overwhelm the server with checkpoints but no significant increase in iowait of the CPU. Can somebody suggest a better idea to load PostgreSQL servers to increase CPU usage.? Configurations of my machine is: Processors: Xeon E5-2650 Processor Kit Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T, 20 MB) * 2 nos RAM : 32GB DDR3-1600 REG Memory Kit 8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800) HDD: 450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos 1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s Disk Speed : 10,000 RPM RAID Controller (512MB, RAID 0/1) My guess is that you're maxing out your IO subsystem long before you're maxing out CPU. What does iostat -xd 10 have to say about it? To understand recursion, one must first understand recursion. -- 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] Detecting change in event properties
On 10/25/13, Robert James srobertja...@gmail.com wrote: I have a table of (timed) events, and I'm interested in marking events whose properties have changed from the previous event. I believe this can be done with window functions, but I'm not sure how. What window function can give me a field from the _previous_ row? (To elaborate, I'm interested in: * Finding field x of the _previous_ row * Finding field x of the _next_ row * Finding field x of the _previous_ row that meets a certain criteria (which the current row may or may not meet) ) The first two are actually trivial - lag(field_x) over (order by [same order as query]) and lead(...). But the last one seems ellusive - How can I find the value of field x on the previous row WHERE a criteria is met? Is it possible to do this at all with a window function? -- 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] Detecting change in event properties
Robert James srobertja...@gmail.com writes: (To elaborate, I'm interested in: * Finding field x of the _previous_ row * Finding field x of the _next_ row * Finding field x of the _previous_ row that meets a certain criteria (which the current row may or may not meet) ) The first two are actually trivial - lag(field_x) over (order by [same order as query]) and lead(...). Right. But the last one seems ellusive - How can I find the value of field x on the previous row WHERE a criteria is met? Is it possible to do this at all with a window function? I don't see any way to achieve that with any of the built-in window functions, but I believe it could be done by a custom window function. Are you up for some C coding? 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] Window functions and relational algebra
Robert James wrote I'm just discovering the power of window functions. Is there any mathematical formalism for them, similar to relational algebra? It would seem to me that window functions aren't expressable in pure relational algebra, but that a well defined extension to it would be possible to express them; at least for those of us so inclinded, being able to think in terms of an alegbra would make reasoning about them much more powerful. The underlying relation the widow is placed over is fully defined from the relational algebra in surrounding query; but given that it can only see a simple relation, and cannot itself perform union/except/intersect nor joins and restrictions (i.e., it cannot remove rows for the final output), I'm not sure how relational algebra would even be a valid concept in this context. The idea of window functions is that you take the an already existing source relation and simply add columns/attributes whose underlying formulas are able to see any or all of the existing rows in the source relation. I guess you can call this simple projection onto the underlying relation but being limited to add only projection makes calling that operation relational a stretch. I find this line of reasoning quite simple and elegant and do not see what trying to extend relational algebra would provide; but my tendencies in this area are toward the practical and away from the underlying theory and foundational math. SQL is not pure relational and so when reasoning about SQL it is necessary to incorporate different frameworks than just relational algebra into your thinking. In this case the relational aspects are initially processed then special functional projections are applied to construct a final relation. That relation can then be algebraically joined to other relations or returned to the caller. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Window-functions-and-relational-algebra-tp5775942p5775957.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Detecting change in event properties
On 10/25/13, Tom Lane t...@sss.pgh.pa.us wrote: Robert James srobertja...@gmail.com writes: (To elaborate, I'm interested in: * Finding field x of the _previous_ row * Finding field x of the _next_ row * Finding field x of the _previous_ row that meets a certain criteria (which the current row may or may not meet) ) The first two are actually trivial - lag(field_x) over (order by [same order as query]) and lead(...). Right. But the last one seems ellusive - How can I find the value of field x on the previous row WHERE a criteria is met? Is it possible to do this at all with a window function? I don't see any way to achieve that with any of the built-in window functions, but I believe it could be done by a custom window function. Are you up for some C coding? Hmmm... certainly nothing I would trust on a production db. Is there a way to do it without C not using window functions? Perhaps with some type of JOIN? -- 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] Detecting change in event properties
Hello 2013/10/25 Robert James srobertja...@gmail.com On 10/25/13, Tom Lane t...@sss.pgh.pa.us wrote: Robert James srobertja...@gmail.com writes: (To elaborate, I'm interested in: * Finding field x of the _previous_ row * Finding field x of the _next_ row * Finding field x of the _previous_ row that meets a certain criteria (which the current row may or may not meet) ) The first two are actually trivial - lag(field_x) over (order by [same order as query]) and lead(...). Right. But the last one seems ellusive - How can I find the value of field x on the previous row WHERE a criteria is met? Is it possible to do this at all with a window function? I don't see any way to achieve that with any of the built-in window functions, but I believe it could be done by a custom window function. Are you up for some C coding? Hmmm... certainly nothing I would trust on a production db. Is there a way to do it without C not using window functions? Perhaps with some type of JOIN? you can write a table function with inner loop cycle over cursor Regards Pavel -- 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] Detecting change in event properties
On 2013-10-25 13:35, Robert James wrote:
On 10/25/13, Robert James srobertja...@gmail.com wrote:
I have a table of (timed) events, and I'm interested in marking events
whose properties have changed from the previous event.
I believe this can be done with window functions, but I'm not sure
how. What window function can give me a field from the _previous_
row?
(To elaborate, I'm interested in:
* Finding field x of the _previous_ row
* Finding field x of the _next_ row
* Finding field x of the _previous_ row that meets a certain criteria
(which the current row may or may not meet)
)
The first two are actually trivial - lag(field_x) over (order by [same
order as query]) and lead(...).
But the last one seems ellusive - How can I find the value of field x
on the previous row WHERE a criteria is met? Is it possible to do this
at all with a window function?
Maybe a custom aggregate that takes the last item in a set?
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT $2;
$$;
CREATE AGGREGATE public.last (
sfunc= public.last_agg,
basetype = anyelement,
stype= anyelement
);
Same set up as last time:
create temp table data (i int, val char);
insert into data (val, i)
values
('A',1),
('A',2),
('A',3),
('B',4),
('C',5),
('A',6),
('D',7),
('A',8),
('A',9),
('D',10),
('D',11),
('B',12),
('C',13),
('C',14)
;
And usage with a case like this? I read somewhere that filtering in
aggregates is coming soon-ish (or maybe already?) to avoid the case, but
this should suffice.
select i, val, last(case val when 'B' then i end) over (order by i asc)
from data
order by i asc
;
ivallast
1ANULL
2ANULL
3ANULL
4B4
5C4
6A4
7D4
8A4
9A4
10D4
11D4
12B12
13C12
14C12
--
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] Detecting change in event properties
Von: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]quot; im Auftrag von quot;Elliot [yields.falseh...@gmail.com] Gesendet: Freitag, 25. Oktober 2013 20:33 On 2013-10-25 13:35, Robert James wrote: On 10/25/13, Robert James srobertja...@gmail.com wrote: I have a table of (timed) events, and I'm interested in marking events whose properties have changed from the previous event. I believe this can be done with window functions, but I'm not sure how. What window function can give me a field from the _previous_ row? (To elaborate, I'm interested in: * Finding field x of the _previous_ row * Finding field x of the _next_ row * Finding field x of the _previous_ row that meets a certain criteria (which the current row may or may not meet) ) The first two are actually trivial - lag(field_x) over (order by [same order as query]) and lead(...). But the last one seems ellusive - How can I find the value of field x on the previous row WHERE a criteria is met? Is it possible to do this at all with a window function? Maybe a custom aggregate that takes the last item in a set? Hello, I would misuse GUC variables for this. (using the functions current_setting and set_config) define a set get and switch fuction (I use operators for better readability) something like: select 'a' == 'foo' 'a' select 'b' == 'foo' 'a' select == 'foo' 'b' and in your query: SELECT case when test then col == 'foo' else == 'foo' end regards, Marc Mamin -- 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] Detecting change in event properties
Robert James wrote * Finding field x of the _previous_ row that meets a certain criteria (which the current row may or may not meet) ) This question could be phrased better. I provide an answer to my interpretation below. You'll need to play with the frame definition because I don't yet have that syntax memorized and am too lazy to figure it out right now. The following gives, for every row, the last val having a value less than 25. It does this by converting all other values to NULL than returning the most proximate value that is not null. The ORDER BY in the OVER() clause gives you an unbounded preceding to current row frame by default so the current row is a valid value for the final answer. WITH data (key, val) AS ( VALUES (1,10),(2,20),(3,30),(4,10),(5,25) ) SELECT key, val, array_last_nonnull(array_agg(CASE WHEN val 25 THEN val ELSE NULL END) OVER (ORDER BY key)) FROM data where array_last_nonnull(...) is defined as: CREATE OR REPLACE FUNCTION array_last_nonnull(in_array anyarray) RETURNS anyelement AS $$ SELECT unnest FROM ( SELECT unnest, row_number() OVER () AS array_index FROM ( SELECT unnest($1) ) explode ) filter WHERE unnest IS NOT NULL ORDER BY array_index DESC LIMIT 1; $$ LANGUAGE sql STRICT IMMUTABLE ; This is probably not the most preformant solution but it is fairly simple, easy to debug (i.e., you can always view the array_agg data), and gives you a benchmark to compare against should you attempt alternatives. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Detecting-change-in-event-properties-tp5775959p5775971.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Detecting change in event properties
Marc Mamin-2 wrote I would misuse GUC variables for this. (using the functions current_setting and set_config) define a set get and switch fuction (I use operators for better readability) something like: select 'a' == 'foo' 'a' select 'b' == 'foo' 'a' select == 'foo' 'b' and in your query: SELECT case when test then col == 'foo' else == 'foo' end Is it possible to alter GUC on a record-by-record basis? Is this something you have actually done? Even if it does technically work this seems like a last-resort kind of solution. The syntax (though that could be hidden in a wrapper function) is definitely unusual and the abuse of the GUC system in this manner is surprising. How would this interface with a window function? The main consideration is dealing with multiple partitions and the fact that a window column calculation requires the use of a function while this solution would seem to preclude that. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Detecting-change-in-event-properties-tp5775959p5775975.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Detecting change in event properties
Elliot wrote Maybe a custom aggregate that takes the last item in a set? CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $2; $$; CREATE AGGREGATE public.last ( sfunc= public.last_agg, basetype = anyelement, stype= anyelement ); Conceptually similar to my array_last_nonnull(array_agg(...)) methodology and the GUC methodology but has the advantage of saving minimal state (compared to the array_agg()) and not abusing GUC for storage of the single-value state. The example would need the same alteration to the frame clause but otherwise would appear to work in the manner presumed by the OP's original question. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Detecting-change-in-event-properties-tp5775959p5775977.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] (collation) Building postgresql on FreeBSD, the pros and cons of icu
On Wednesday, October 23, 2013 10:00 PM, Patrick Dung patrick_...@yahoo.com.hk wrote: On Wednesday, October 23, 2013 9:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Patrick Dung patrick_...@yahoo.com.hk writes: By default, FreeBSD ports does not build postgresql with icu (http://www.icu-project.org/ ). Postgres does not have any option to use ICU, default or otherwise. Nor is it likely to happen in future, judging from previous discussions of the idea. regards, tom lane OK, now I understand that FreeBSD case, they have a specific patch to use icu. The default PostgreSQL does use ICU. Thanks for pointing that out. Thanks, Patrick
Re: [GENERAL] (collation) Building postgresql on FreeBSD, the pros and cons of icu
On Friday, October 25, 2013 3:12 PM, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: On 23/10/2013 16:44, Tom Lane wrote: Patrick Dung patrick_...@yahoo.com.hk writes: By default, FreeBSD ports does not build postgresql with icu (http://www.icu-project.org/). Postgres does not have any option to use ICU, default or otherwise. Nor is it likely to happen in future, judging from previous discussions of the idea. Hi Tom, Patrick FreeBSD indeed has a config option to build with ICU, just # /usr/ports/databases/postgresql93-server # make config and you will be able to see this. The relevant README is here : http://people.freebsd.org/~girgen/postgresql-icu/README.html Patrick also you may build postgresql by hand and apply the patch manually from : /usr/ports/databases/postgresql93-server Although being in a non-english speaking company, i have not tried this neither at work or at home. Hope that helps. Hi Achilleas, Sorry I have hit to send button too fast in the last mail... Yes, I know FreeBSD has a specific patch to use ICU on Postgresql. And officially Postgresql, do not come with ICU patch natively. Thanks. Patrick
