Re: [GENERAL] Join Bad Performance on different data types
On Tue, Mar 4, 2014 at 1:13 PM, Sameer Kumar sameer.ku...@ashnik.comwrote: On Tue, Mar 4, 2014 at 2:57 PM, Adarsh Sharma eddy.ada...@gmail.comwrote: I tried creating simple and gin indexes on the column(t_ids) but still not helping. Anyone has any idea or faced this before. Postgresql version is 9.2. have you done a vacuum analyze or analyze after this step? You might have to disable sequential scans set enable_seqscan=off; And then fire the query. Thanks Sameer. yes i already did vacuum analyze but i tried enable_seqscan=off; this time and explain analyze finished in 34 seconds. PLAN - HashAggregate (cost=10651634346.70..10651780073.12 rows=4163612 width=64) (actual time=34375.675..34764.705 rows=751392 loops=1) - Nested Loop (cost=100.03..10646590270.49 rows=336271747 width=64) (actual time=0.217..24988.534 rows=6541944 loops=1) - Nested Loop (cost=100.02..10012318364.23 rows=33628639 width=116) (actual time=0.177..3427.380 rows=1431 loops=1) - Nested Loop (cost=100.01..10001045237.36 rows=3368723 width=38) (actual time=0.138..3373.767 rows=1431 loops=1) - Nested Loop (cost=100.00..1097742.23 rows=340181 width=38) (actual time=0.047..2151.183 rows=418145 loops=1) Join Filter: (td.entity_type_id = gtt.id) Rows Removed by Join Filter: 1269335 - Seq Scan on graph5 td (cost=100.00..1077008.13 rows=345413 width=33) (actual time=0.020..1231.823 rows=421870 loops=1) - Materialize (cost=0.00..9.33 rows=4 width=13) (actual time=0.000..0.001 rows=4 loops=421870) - Index Scan using geo_type_pkey on graph6 gtt (cost=0.00..9.31 rows=4 width=13) (actual time=0.009..0.012 rows=4 loops=1) - Index Scan using graph2_pkey on graph2 gcr (cost=0.01..2.69 rows=10 width=33) (actual time=0.002..0.002 rows=0 loops=418145) Index Cond: (id = ANY (td.graph3_id)) - Index Scan using graph3_pkey on graph3 gtd (cost=0.01..3.25 rows=10 width=115) (actual time=0.035..0.036 rows=1 loops=1431) Index Cond: (id = ANY (gcr.t_ids)) - Index Scan using graph1_pkey on graph1 glt (cost=0.01..18.51 rows=10 width=55) (actual time=0.085..9.082 rows=4572 loops=1431) Index Cond: (id = ANY (gtd.lat_long_id_list)) Total runtime: 34810.040 ms Is dere any way i can rewrite the query so that i need not to set seqscan-off, because i dont want to embed one more line in application code and also dont want to change global setting in postgresql.conf to disable seqscan. Thanks This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Re: [GENERAL] Join Bad Performance on different data types
On Tue, Mar 4, 2014 at 4:19 PM, Adarsh Sharma eddy.ada...@gmail.com wrote: Is dere any way i can rewrite the query so that i need not to set seqscan-off, because i dont want to embed one more line in application code and also dont want to change global setting in postgresql.conf to disable seqscan. You can use a specific user for this query and set parameter's value at user level. But if you don't have any way of doing that then you pretty much have to embed one more line in application code [though I have never understood why application teams do not want to do that]. It should be fine, unless you are developing a product which can be used with someother RDBMS and hence you don't want to introduce a PostgreSQL specific line in code. Best Regards, *Sameer Kumar | Database Consultant* *ASHNIK PTE. LTD.* 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533 M: *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com *[image: icons]* [image: Email patch] http://www.ashnik.com/ This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s). inline: image005.jpginline: image006.jpg
Re: [GENERAL] How to recovery failed master after failover
I wonder how to quickly recovery failed master? If I directly startup the failed master as slave ( assign proper parameter), is there any problem? For example, I don't do any copy operation in script of recovery_1st_stage_command and recovery_2st_stage_command. According to this document: https://wiki.postgresql.org/wiki/Synchronous_Transfer, can I only copy AWL for recovering failed master? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-recovery-failed-master-after-failover-tp5794524p5794547.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] Join Bad Performance on different data types
2014-03-04 10:19 GMT+02:00 Adarsh Sharma eddy.ada...@gmail.com: PLAN - HashAggregate (cost=10651634346.70..10651780073.12 rows=4163612 width=64) (actual time=34375.675..34764.705 rows=751392 loops=1) - Nested Loop (cost=100.03..10646590270.49 rows=336271747 width=64) (actual time=0.217..24988.534 rows=6541944 loops=1) - Nested Loop (cost=100.02..10012318364.23 rows=33628639 width=116) (actual time=0.177..3427.380 rows=1431 loops=1) - Nested Loop (cost=100.01..10001045237.36 rows=3368723 width=38) (actual time=0.138..3373.767 rows=1431 loops=1) - Nested Loop (cost=100.00..1097742.23 rows=340181 width=38) (actual time=0.047..2151.183 rows=418145 loops=1) Join Filter: (td.entity_type_id = gtt.id) Rows Removed by Join Filter: 1269335 - Seq Scan on graph5 td (cost=100.00..1077008.13 rows=345413 width=33) (actual time=0.020..1231.823 rows=421870 loops=1) - Materialize (cost=0.00..9.33 rows=4 width=13) (actual time=0.000..0.001 rows=4 loops=421870) - Index Scan using geo_type_pkey on graph6 gtt (cost=0.00..9.31 rows=4 width=13) (actual time=0.009..0.012 rows=4 loops=1) - Index Scan using graph2_pkey on graph2 gcr (cost=0.01..2.69 rows=10 width=33) (actual time=0.002..0.002 rows=0 loops=418145) Index Cond: (id = ANY (td.graph3_id)) - Index Scan using graph3_pkey on graph3 gtd (cost=0.01..3.25 rows=10 width=115) (actual time=0.035..0.036 rows=1 loops=1431) Index Cond: (id = ANY (gcr.t_ids)) - Index Scan using graph1_pkey on graph1 glt (cost=0.01..18.51 rows=10 width=55) (actual time=0.085..9.082 rows=4572 loops=1431) Index Cond: (id = ANY (gtd.lat_long_id_list)) Total runtime: 34810.040 ms Is dere any way i can rewrite the query so that i need not to set seqscan-off, because i dont want to embed one more line in application code and also dont want to change global setting in postgresql.conf to disable seqscan. Could you, kindly, also post `EXPLAIN` output of your original and modified queries also, leaving out all `enable_...` setting at their defaults. Just explain, without `analyze`. -- Victor Y. Yegorov
[GENERAL] log_statement per table
Hello, I would like to log statements that modify a small subset of tables in a databases. (not all tables, because the log files become too big in that case and I also worry about performance) I currently use log_statement='mod' but I didn't find a way to limit this to the set of tables I want. What is the best way to do this? -- David Janssens -- 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] Read tables of sql server with postgres
So, there's no way to do this? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Read-tables-of-sql-server-with-postgres-tp5793046p5794581.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] Read tables of sql server with postgres
On Tue, Mar 4, 2014 at 6:48 AM, loref85 lore...@yahoo.com.ar wrote: So, there's no way to do this? You might want to give jdbc-fdw a try. not sure what's wrong with odbc -- looks like a driver mismatch issue. merlin -- 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] Role Inheritance Without Explicit Naming?
Le 2014-03-03 à 10:53, Adrian Klaver a écrit :
On 03/02/2014 08:48 PM, François Beausoleil wrote:
Hi all,
I have four roles involved:
meetphil - the database owner, should not login
mpwebui - the role the web application logs in as, should have very limited
privileges, but should be able to SET ROLE to a user that has the correct
privileges, should login
mpusers - the main group for regular users, the group on which I'll grant
default privileges, should not login
francois - one of the roles that has the right to do stuff, should login
I've gist'd everything here: https://gist.github.com/francois/9318054 (also
appended at the end of this email).
In a fresh cluster, I create my users:
$ psql -U meetphil -d meetphil
psql (9.1.5)
Type help for help.
meetphil= \du
List of roles
Role name | Attributes | Member of
---++---
colette || {mpusers}
francois || {mpusers}
meetphil || {}
mpusers | Cannot login | {}
mpwebui | No inheritance | {mpusers}
postgres | Superuser, Create role, Create DB, Replication | {}
rene || {mpusers}
If I am following correctly what you want is something like this:
-- mpusers
| |
\|/ |
francois mpwebui
In other words access sibling roles through a parent role. Is this correct?
Yes, when you put it that way, it looks like it. I'm just exploring ideas on
how to secure access to the database. I'm exploring alternatives.
Bye,
François
smime.p7s
Description: S/MIME cryptographic signature
Re: [GENERAL] Read tables of sql server with postgres
On 03/04/2014 04:48 AM, loref85 wrote: So, there's no way to do this? In your original post you said you found a version of odbc_fdw that compiled for 9.2+, but hung on a select due to this error: ERROR: odbc_fdw::odbcGetTableSize: Could not retrieve table size Have you tried to contact the author of the odbc_fdw extension and see what they have to say? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Segmentation fault
hello team ,
i am trying to enter this insert command in postgres
insert into mmsuper.notification
values('101','12','13','حب|welcome|आपकास्वागतहै','bye','goodbye','low
balance',5);
now when i am trying to paste this in postgres in Solaris , it is giving me
error
fm_db_Server1-# insert into mmsuper.notification
values('101','12','13','Segmentation Fault
and comes out of postgres prompt
also when i am trying to press up arrow key , then also it is giving me
above error.
But in Linux this is working fine ..without any error
Please help it is very very urgent..
Thanks
Akshat
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Segmentation-fault-tp5794571.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] log_statement per table
On Mar 4, 2014, at 2:19 AM, David Janssens davi...@almacom.co.th wrote: Hello, I would like to log statements that modify a small subset of tables in a databases. (not all tables, because the log files become too big in that case and I also worry about performance) I currently use log_statement='mod' but I didn't find a way to limit this to the set of tables I want. What is the best way to do this? You might want to look at trigger based audit logs. Some example code, and a couple of useful packages: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE https://github.com/disqus/pg_audit http://pgfoundry.org/projects/tablelog/ http://jimmyg.org/blog/2007/audit-tables-in-postgresql-with-debian.html http://www.varlena.com/GeneralBits/104.php Cheers, Steve -- 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] Why is varchar_pattern_ops needed?
Tom Lane wrote: Albe Laurenz laurenz.a...@wien.gv.at writes: Is there anything that varchar_pattern_ops is needed for that text_pattern_ops cannot provide? Lack of surprise? If you're creating a pattern index on a varchar column, you'd likely expect to need to mention varchar_pattern_ops. The idea that varchar is an alias for text might be second nature to old Postgres hands, but it's not to most of the world. Thanks for the explanation! 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] Segmentation fault
ajay akmani1...@gmail.com writes:
hello team ,
i am trying to enter this insert command in postgres
insert into mmsuper.notification
values('101','12','13','Øب|welcome|à¤à¤ªà¤à¤¾à¤¸à¥à¤µà¤¾à¤à¤¤à¤¹à¥','bye','goodbye','low
balance',5);
now when i am trying to paste this in postgres in Solaris , it is giving me
error
fm_db_Server1-# insert into mmsuper.notification
values('101','12','13','Segmentation Fault
and comes out of postgres prompt
also when i am trying to press up arrow key , then also it is giving me
above error.
This suggests you've got a broken readline library. Can you do the
paste successfully when psql is started with the --no-readline switch?
If so, you need to get a more up-to-date copy of libreadline and relink
psql against 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] Segmentation fault
On 03/04/2014 03:03 AM, ajay wrote:
hello team ,
i am trying to enter this insert command in postgres
insert into mmsuper.notification
values('101','12','13','حب|welcome|आपकास्वागतहै','bye','goodbye','low
balance',5);
now when i am trying to paste this in postgres in Solaris , it is giving me
error
fm_db_Server1-# insert into mmsuper.notification
values('101','12','13','Segmentation Fault
and comes out of postgres prompt
also when i am trying to press up arrow key , then also it is giving me
above error.
But in Linux this is working fine ..without any error
Well this has encoding/locale issues written all over it.
So what is the encoding/locale for your Linux machine vs the Solaris
machine?
The same for the Postgres instances on each or are you inserting into
the same Postgres instance?
Please help it is very very urgent..
Thanks
Akshat
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Segmentation-fault-tp5794571.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Adrian Klaver
adrian.kla...@aklaver.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] Segmentation fault
On 03/04/2014 07:39 AM, Adrian Klaver wrote:
CCing list:
quote author='Adrian Klaver-4'
On 03/04/2014 03:03 AM, ajay wrote:
hello team ,
i am trying to enter this insert command in postgres
insert into mmsuper.notification
values('101','12','13','حب|welcome|आपकास्वागतहै','bye','goodbye','low
balance',5);
now when i am trying to paste this in postgres in Solaris , it is giving
me
error
fm_db_Server1-# insert into mmsuper.notification
values('101','12','13','Segmentation Fault
and comes out of postgres prompt
also when i am trying to press up arrow key , then also it is giving me
above error.
But in Linux this is working fine ..without any error
Well this has encoding/locale issues written all over it.
So what is the encoding/locale for your Linux machine vs the Solaris
machine?
The same for the Postgres instances on each or are you inserting into
the same Postgres instance?
Please help it is very very urgent..
Thanks
Akshat
thanks for the reply ,..
When i am trying to enter anything else other then some Arabic , Hindi
.. that case it does not give this error.
like if i enter..
insert into table values('welcome',1);
then it does not give any error and get successfully
I would try Toms suggestion about readline. If that does not work then
we are back to questions I asked above about the encodings you are
working with.
Thank
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using XML_PARSE_HUGE in operations on xml fields?
Hello,
I ran into trouble with an xpath expression on a large XML file:
SELECT id, xpath('//tei:div/descendant::tei:head/text()', x, ARRAY[ARRAY['tei',
'http://www.tei-c.org/ns/1.0']]) AS stuff FROM test WHERE id=1;
returns:
ERROR: could not parse XML document
DETAIL: line 491482: internal error: Huge input lookup
स्थापितः।। /p
^
line 491482: Extra content at the end of the document
स्थापितः।। /p
^
I think this is an error that comes from the libxml2 library, since
pretty much the same thing happens with xmllint if you pass the
`--memory' option.
This is the libxml2 documentation which is relevant here, I think:
#define XML_MAX_DICTIONARY_LIMIT
Maximum size allowed by the parser for a dictionary by default This is
not a limitation of the parser but a safety boundary feature, use
XML_PARSE_HUGE option to override it. Introduced in 2.9.0
(see http://xmlsoft.org/html/libxml-parserInternals.html#XML_MAX_LOOKUP_LIMIT)
So I was wondering if and how I could set that XML_PARSE_HUGE option
in postgresql? I couldn't find anything in the docs or in this list's
archives.
If you want to replicate the problem quickly, with one approx 37MB xml
file and one smaller one:
createdb xmlpost
psql xmlpost
CREATE TABLE test (id integer, x xml);
\set content `curl
http://sarit.indology.info/downloads/mahabharata-devanagari.xml`
INSERT INTO test (SELECT 1, (SELECT XMLPARSE (DOCUMENT :'content')));
\set content `curl
http://sarit.indology.info/downloads/ratnakIrti-nibandhAvali.xml`
INSERT INTO test (SELECT 2, (SELECT XMLPARSE (DOCUMENT :'content')));
SELECT id, xpath('count(//tei:div/descendant::tei:head)', x, ARRAY[ARRAY['tei',
'http://www.tei-c.org/ns/1.0']]) AS stuff FROM test WHERE id=1; -- fails
SELECT id, xpath('count(//tei:div/descendant::tei:head)', x, ARRAY[ARRAY['tei',
'http://www.tei-c.org/ns/1.0']]) AS stuff FROM test WHERE id=2; -- works: 13
Thanks for any hints,
--
patrick
--
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] Role Inheritance Without Explicit Naming?
On 03/04/2014 06:00 AM, François Beausoleil wrote:
Le 2014-03-03 à 10:53, Adrian Klaver a écrit :
On 03/02/2014 08:48 PM, François Beausoleil wrote:
Hi all,
I have four roles involved:
meetphil - the database owner, should not login
mpwebui - the role the web application logs in as, should have very limited
privileges, but should be able to SET ROLE to a user that has the correct
privileges, should login
mpusers - the main group for regular users, the group on which I'll grant
default privileges, should not login
francois - one of the roles that has the right to do stuff, should login
I've gist'd everything here: https://gist.github.com/francois/9318054 (also
appended at the end of this email).
In a fresh cluster, I create my users:
$ psql -U meetphil -d meetphil
psql (9.1.5)
Type help for help.
meetphil= \du
List of roles
Role name | Attributes | Member of
---++---
colette || {mpusers}
francois || {mpusers}
meetphil || {}
mpusers | Cannot login | {}
mpwebui | No inheritance | {mpusers}
postgres | Superuser, Create role, Create DB, Replication | {}
rene || {mpusers}
If I am following correctly what you want is something like this:
-- mpusers
| |
\|/ |
francois mpwebui
In other words access sibling roles through a parent role. Is this correct?
Yes, when you put it that way, it looks like it. I'm just exploring ideas on
how to secure access to the database. I'm exploring alternatives.
Well my experience is that Postgres will not automatically do the above.
As you have found, you have to explicitly grant from one sibling to
another. There are others on this list that deal with more complicated
set ups then me and might have better ideas. In which case both of us
will learn something:)
Bye,
François
--
Adrian Klaver
adrian.kla...@aklaver.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] Segmentation fault
thanks for the reply ,..
When i am trying to enter anything else other then some Arabic , Hindi ..
that case it does not give this error.
like if i enter..
insert into table values('welcome',1);
then it does not give any error and get successfully
Thanks
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Segmentation-fault-tp5794571p5794624.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
[GENERAL] SQL question on chunking aggregates
Hi all:
I have a table that has multiple records for a single owner_id. I'm able to
use array_arg to combine the records into a single row, which works fine. I'm
using this sql:
select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue +
revalbuildingvalues) from parcel group by owner_id;
Which results in the following (sometimes there's only one record per
aggregate, sometimes multiple):
1030600;{154191};{244690}
1030900;{22202};{217210}
1031130;{113135,113138,113132,113130,113133,113
127,113126,113131,113129,113136,113125,113
137,113134,113
128};{7700,7700,7700,7700,7700,7700,7700,7700,7700,191770,7700,7700,7700,7700}
What I want to do, is where there are more than 5 rows involved in the
aggregate, as in the last example, to split it into multiple rows of 5
aggregated rows. It's for a mailing list and I want to combine like addresses
into one record, but if I'm over 5, I have to print the rest on a separate
letter.
1031130;{113135,113138,113132,113130,113
133};{7700,7700,7700,7700,7700}
1031130;{113127,113126,113131,113129,113
136};{7700, 7700,7700,7700,191770}
1031130;{113125,113137,113134,113
128};{7700,7700,7700,7700}
It looks like I should be able to use the window function to do this, but I've
been unsuccessful. The following runs, but doesn't seem to have any effect:
select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue +
revalbuildingvalues) from parcel group by owner_id
window mywindow as (rows between current row and 5 following);
Does anyone have any suggestions on what I should try?
-Owen
Re: [GENERAL] SQL question on chunking aggregates
On Tue, Mar 4, 2014 at 1:49 PM, Owen Hartnett o...@clipboardinc.com wrote:
Hi all:
I have a table that has multiple records for a single owner_id. I'm able to
use array_arg to combine the records into a single row, which works fine.
I'm using this sql:
select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue +
revalbuildingvalues) from parcel group by owner_id;
Which results in the following (sometimes there's only one record per
aggregate, sometimes multiple):
1030600;{154191};{244690}
1030900;{22202};{217210}
1031130;{113135,113138,113132,113130,113
133,113127,113126,113131,113129,113136,113
125,113137,113134,113
128};{7700,7700,7700,7700,7700,7700,7700,7700,7700,191770,7700,7700,7700,7700}
What I want to do, is where there are more than 5 rows involved in the
aggregate, as in the last example, to split it into multiple rows of 5
aggregated rows. It's for a mailing list and I want to combine like
addresses into one record, but if I'm over 5, I have to print the rest on a
separate letter.
1031130;{113135,113138,113132,113130,113
133};{7700,7700,7700,7700,7700}
1031130;{113127,113126,113131,113129,113
136};{7700, 7700,7700,7700,191770}
1031130;{113125,113137,113134,113
128};{7700,7700,7700,7700}
It looks like I should be able to use the window function to do this, but
I've been unsuccessful. The following runs, but doesn't seem to have any
effect:
select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue +
revalbuildingvalues) from parcel group by owner_id
window mywindow as (rows between current row and 5 following);
Does anyone have any suggestions on what I should try?
-Owen
I didn't test it, but something along the lines of:
select
owner_id,
array_agg(maplot),
array_agg(totalvalues)
from
(
select
owner_id,
trim(maplot) as maplot,
revallandvalue + revalbuildingvalues as totalvalues,
row_number() over (partition by owner_id) as n
from parcel
) q
group by owner_id, (n - 1)/5;
merlin
--
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] log_statement per table
David Janssens davi...@almacom.co.th writes: Hello, I would like to log statements that modify a small subset of tables in a databases. (not all tables, because the log files become too big in that case and I also worry about performance) I currently use log_statement='mod' but I didn't find a way to limit this to the set of tables I want. What is the best way to do this? Below is not a perfect solution and exercise for reader to disable logging after mods on this table. Below is tested on 9.1 and works as per the trivial example... But if you don't reset the log_statement setting again in an affter statement trigger other tables modified in same transaction are going to log as well. And this is wherein lies the rub, if you had already set log_statement to something non-default earlier in same transaction, the trigger is going to unconditionally reset it. Perhaps there's a way around this too but if so, I'm not going to divert cycles to thinking of it right now. HTH begin; create table foo ( a int ); create function foo() returns trigger as $$ begin set local log_statement to 'all'; return null; end $$ language plpgsql; create trigger foo before insert or update or delete on foo execute procedure foo(); commit; insert into foo select 1; -- David Janssens -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
Roy Anderson roy.ander...@gmail.com wrote: We have an OLTP database and no data warehouse. We are currently planning out a build for a data warehouse however (possibly using Hadoop). X is recommending that we convert our current, normalized OLTP database into a flattened Star Schema. I'm not going to repeat good advice you've already gotten in other answers, but I will point out that complex reporting off of normalized data is often much faster if you have been able to use natural keys, even if you need to go to multi-column primary keys to do so. One of the biggest down-sides of synthetic primary keys (where, for example, you might have a single-column PK column called id in every table) is that forces one particular route to navigate the tables. With natural keys a complex query often finds intriguing plans to give the results you ask for using plans you might never have thought of, and which can be orders of magnitude faster than the plans which would be possible if the joins are all done using synthetic keys. Beyond that, I would say that I would never jump to some star schema automatically. There are various ways data can be summarized for faster reporting, and a flattened star schema is only one option, which is not always the fastest option -- or even an improvement over 3NF. At the risk of repeating a little, I recommend looking at what it would take to generate the reports you want of the current data; and only denormalize where something is too slow, using the summarization which appears to be the most sensible for the use case. As an aside, I had a case where auditors wanted a particular report off of a 3 TB OLTP database. One programmer tried to write it using imperative code and looping. Based on how far it got in the first 5 hours, it would have taken a year to complete. Rewritten with a couple CTEs as a single declarative query (one SELECT statement) it ran in ten minutes. No star schema needed -- just some clear thinking, and making use of the power of declarative coding and a great optimizer. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
On Tue, Mar 4, 2014 at 2:15 PM, Kevin Grittner kgri...@ymail.com wrote: Roy Anderson roy.ander...@gmail.com wrote: We have an OLTP database and no data warehouse. We are currently planning out a build for a data warehouse however (possibly using Hadoop). X is recommending that we convert our current, normalized OLTP database into a flattened Star Schema. I'm not going to repeat good advice you've already gotten in other answers, but I will point out that complex reporting off of normalized data is often much faster if you have been able to use natural keys, even if you need to go to multi-column primary keys to do so. One of the biggest down-sides of synthetic primary keys (where, for example, you might have a single-column PK column called id in every table) is that forces one particular route to navigate the tables. With natural keys a complex query often finds intriguing plans to give the results you ask for using plans you might never have thought of, and which can be orders of magnitude faster than the plans which would be possible if the joins are all done using synthetic keys. If we ever happen to meet, you just bought yourself a steak dinner with this email. Natural key database design has to my great displeasure become something of a lost art. Data modeling and performance expectations have really suffered as a consequence of that knowledge gap. Now, natural keys have issues also -- update performance on the key in particular -- so you have to be nimble and adjust the model as appropriate to the task at hand. merlin -- 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] SQL question on chunking aggregates
Merlin Moncure-2 wrote On Tue, Mar 4, 2014 at 1:49 PM, Owen Hartnett lt; owen@ gt; wrote: It looks like I should be able to use the window function to do this, but I've been unsuccessful. The following runs, but doesn't seem to have any effect: select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue + revalbuildingvalues) from parcel group by owner_id window mywindow as (rows between current row and 5 following); Does anyone have any suggestions on what I should try? -Owen I didn't test it, but something along the lines of: select owner_id, array_agg(maplot), array_agg(totalvalues) from ( select owner_id, trim(maplot) as maplot, revallandvalue + revalbuildingvalues as totalvalues, row_number() over (partition by owner_id) as n from parcel ) q group by owner_id, (n - 1)/5; Yeah, a window cannot work because it cannot be defined to provide disjoint subsets. In most cases multiple invocations of array_agg(...) - at the same level in a query - will see the same row order but that is not something that it is wise to rely upon. Any time you want to have synchronized array_agg(...) calls you should add identical explicit ORDER BY clauses to them; or better yet combine that data into a custom datatype and then store that in the array. The solution is as Merlin presents; you need to use integer division to bucket the rows and then call the array_agg(...) using those groups. I like to keep the bucket ID around in order to capture the original order but as shown it is not a requirement. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-question-on-chunking-aggregates-tp5794680p5794694.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] Offending My Tender Sensibilities -OR- OLTP on a Star Schema
On 03/04/2014 01:40 PM, Merlin Moncure wrote: On Tue, Mar 4, 2014 at 2:15 PM, Kevin Grittner kgri...@ymail.com wrote: Roy Anderson roy.ander...@gmail.com wrote: We have an OLTP database and no data warehouse. We are currently planning out a build for a data warehouse however (possibly using Hadoop). X is recommending that we convert our current, normalized OLTP database into a flattened Star Schema. I'm not going to repeat good advice you've already gotten in other answers, but I will point out that complex reporting off of normalized data is often much faster if you have been able to use natural keys, even if you need to go to multi-column primary keys to do so. One of the biggest down-sides of synthetic primary keys (where, for example, you might have a single-column PK column called id in every table) is that forces one particular route to navigate the tables. With natural keys a complex query often finds intriguing plans to give the results you ask for using plans you might never have thought of, and which can be orders of magnitude faster than the plans which would be possible if the joins are all done using synthetic keys. If we ever happen to meet, you just bought yourself a steak dinner with this email. Natural key database design has to my great displeasure become something of a lost art. Data modeling and performance expectations have really suffered as a consequence of that knowledge gap. Now, natural keys have issues also -- update performance on the key in particular -- so you have to be nimble and adjust the model as appropriate to the task at hand. merlin Do you make a distinction between a key and an index? I'm not picking up on design-by-natural-key and what that entails. Especially the notion that the natural key of a given item might be mutable. What stops it from colliding with the next item? (I have not had the pleasure of working in a domain where natural keys are obvious if they existed at all. What's in a name, after all. )
Re: [GENERAL] log_statement per table
On Tue, Mar 4, 2014 at 9:19 PM, David Janssens davi...@almacom.co.thwrote: Hello, I would like to log statements that modify a small subset of tables in a databases. (not all tables, because the log files become too big in that case and I also worry about performance) I currently use log_statement='mod' but I didn't find a way to limit this to the set of tables I want. What is the best way to do this? Not sure if this solution would help or may be you have already looked at this possibility. If you know which user is performing the activities on those tables, you can look at the possibility of setting the log_statement='all' at a user level by saying alter role username set log_statement='all' . This would log all the statements executed by that particular user. You can think of doing this if you think do not end up having a big logfile. Or In my experience, i have configured a separate user with separate set of logging mechanism and use to monitor all the activities performed by that user. Venkata Balaji N Sr. Database Administrator Fujitsu Australia
Re: [GENERAL] How to recovery failed master after failover
On Tue, Mar 4, 2014 at 6:26 PM, leo dazhou...@gmail.com wrote: I wonder how to quickly recovery failed master? If I directly startup the failed master as slave ( assign proper parameter), is there any problem? Yep, if the master has got ahead of the slave in term of WAL replay where WAL forked after the slave promotion, you won't be able to reconnect an old master as a slave. In this case, what you need to do, is either to rewind the old master using for example pg_rewind (which is not a tool part of Postgres core) or create a new node from scratch with a base backup and some archives, and then reconnect it. Depending on the size of data in server, this could take some time because of network latency and if you create a new base backup, especially if both nodes are on different (distant?) servers. -- Michael -- 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 recovery failed master after failover
I find a solution to short the recover time by configure parameter Synchronous Transfer. Refer to : https://wiki.postgresql.org/wiki/Synchronous_Transfer. But I don't which postgreSQL will enable this parameter, I install 9.3.3-1 on redhat, but I don't find this parameter in postgresql.conf. Is there anyone use this parameter? Leo -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-recovery-failed-master-after-failover-tp5794524p5794720.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] How to recovery failed master after failover
On Wed, Mar 5, 2014 at 2:14 PM, leo dazhou...@gmail.com wrote: I find a solution to short the recover time by configure parameter Synchronous Transfer. Refer to : https://wiki.postgresql.org/wiki/Synchronous_Transfer. But I don't which postgreSQL will enable this parameter, I install 9.3.3-1 on redhat, but I don't find this parameter in postgresql.conf. Is there anyone use this parameter? Not sure if you have been through the below links. They might be helpful for you. http://www.postgresql.org/message-id/CAF8Q-Gy7xa60HwXc0MKajjkWFEbFDWTG=ggyu1kmt+s2xcq...@mail.gmail.com http://postgresql.1045698.n5.nabble.com/Using-ini-file-to-setup-replication-td5764411.html Venkata Balaji N Fujitsu Australia
