[GENERAL] best practice for || set of rows --> function --> set of rows
Hello Dear List, this is a re-post with a more clear title and rewrite. I have a design issue : My function works on ordered set of rows and returns ordered set of rows (less thant input). I want to use it in standard sql query (in WITH for example). What is best practice for input? I see 3 solutions : _give table name as input || so no order unless I use view, doesn't work with CTE and all. _give array and use unnest/arrayagg || bad performance with big input _give refcursor || non-convenient because of transaction and require 2 separate queries. *Is there another way?* I would like to use it in a single sql statement, which leaves only array, which will perform bad (I may have hundreds of k of rows as input). Also the function need all the row in input to work, and can't operate row by row. It would be like an aggregate, but returning several rows. Of course I read all the doc I could find, but doc doesn't give best practice ! Thank you very much for helping, I am in a stalemate now, and can't progress further. Below is the original message, giving details over what the function do. Cheers, Rémi-C I wrote a plpgsql function to compute union of time range that works : [1,4]U[3,8]U[12,14]U[16,18] ---> [1,8]U[12,14]U[16,18] It works on multiple rows. My issue is a design issue : I want to work on set of row and return set of row. I am aware I could take as input/output array of range but I don't want (memory/casting cost). Currently the function takes a cursor on a table and output a setof record. I would like that the function can blend in multiple subqueries smoothly, as WITH ( first query to get range), (query computing union ), (query using computed union ) etc. Currently I have to execute 2 sql statment : create cursor on ranges; WITH (function to compute union) , (query...) The only kind of function taking set of record as input I know of is aggregate function, but it returns only one row and the output of union can take multiple row. Any insight would be greatly appreciated. Cheers, Rémi-C
Re: [GENERAL] Full text search regression tests
I am sorry this mail was not meant for this list.
[GENERAL] Full text search regression tests
Hello All, Attached patch adds regression tests to check the full-text search capability of pg_bigm. Regards, Beena Emerson bigm-fulltext-search-regression.patch Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] need a hand with my backup strategy please...
Morning I made the mistake of thinking that I could recover to any point in time with a logical backup plus WAL files, unfortunately that is not the case. I was rsync'ing wal files to another system, and set the archive timeout to 5 mins, and the retention to allow for 25 hours worth or so. Unfortunately though if there is heavy load, the wal files will be generated more regularly than the 5 minute max. So I'm back to the drawing board for both reasons above. I've set up pgbarman (seems to be an excellent project - thank you). My question is how often the base backup should be done. Is it reasonable to specify: minimum_redundancy = 1 retention_policy = RECOVERY WINDOW OF 1 DAY and then set up cron to do a base backup every day? I guess also it could be done once a week with a longer retention policy, at the expense of more disk space for wal files. And then run the "barman cron" immediately after which will take care of deleting redundant backups? In fact it doesn't seem to so not sure how to handle that. I think I will stick with the nightly backups using pg_dump of the individual databases, as this is useful for refreshing UAT from production and other tasks. But does this sound like a reasonable strategy for handling PITR? I have read everything I can about this but haven't found a canonical suggestion for implementing. Bonus question is, the barman cron seems to actually apply the incoming wal segments to the base backup. If it does do this, how is it possible to restore to a point in time? Thanks for any help. Cheers, jamie === Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ===
Re: [GENERAL] need a hand with my backup strategy please...
On Sep 17, 2013, at 7:22 AM, "Echlin, Jamie (KFIA 611)" wrote: > Unfortunately though if there is heavy load, the wal files will be generated > more regularly than the 5 minute max. If you wrote the WAL to an external, mounted disk, wouldn't that solve the issue?
Re: [GENERAL] need a hand with my backup strategy please...
>> Unfortunately though if there is heavy load, the wal files will be generated >> more regularly than the 5 minute max. > If you wrote the WAL to an external, mounted disk, wouldn't that solve the > issue? What I'm trying to say is that I configured it to keep (60/5) * 24 segments plus a few spare, because I am switching xlog every 5 mins. But if there is heavy load then they will be generated more often than every 5 mins, so that number won't be enough. But I think it's a moot point because they seem to be useless unless you have all of them up to the last base backup. Which to me indicates I need to do a base backup every day...? Cheers, jamie === Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html === -- 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 ODBC and VBA to pull data from a large object
Good Morning, I had a question about using ODBC with large objects. When I query the pg_largeobject table and return data to an ADO recordset, I can only get the first 255 bytes even though the record contains 2048 bytes of data (all the bytes after the first 255 show as 0). When I checked the type of the record, it was a VarBinary. Is there a way to have all of the data returned to the recordset? Thanks for any help. Adam
Re: [GENERAL] need a hand with my backup strategy please...
Jamie Echlin wrote: > What I'm trying to say is that I configured it to keep (60/5) * 24 segments > plus a few spare, because > I am switching xlog every 5 mins. But if there is heavy load then they will > be generated more often > than every 5 mins, so that number won't be enough. You should delete archived WAL files by age, that way you shouldn't have a problem. > But I think it's a moot point because they seem to be useless unless you have > all of them up to the > last base backup. Which to me indicates I need to do a base backup every > day...? You just need any base backup plus *all* archived WALs since the beginning of the backup. Of course you want a fairly recent backup, otherwise applying the WAL files can take very long. It is a good idea to keep more than one base backup in case something goes wrong (bad backup). Another reason to keep older backups is that you may want to recover to a point in time that lies further in the past, e.g. if it takes some time to discover a problem that requires recovery (corruption, ...). 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] need a hand with my backup strategy please...
Thanks for your answer Laurenz. I was planning to rely on the disk backup (of the base backup) if I wanted to restore to a version before the last on disk base backup. But your point about redundancy is good... I think I will keep two base backups, and do a base backup every day. Over the network this takes 5-10 mins or so, so not a big deal. I'll retain wals for 48 hours. Having to go to a PIT before the last hour or so would be a major undertaking anyway. Cheers, jamie === Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html === -- 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] best practice for || set of rows --> function --> set of rows
On Tue, Sep 17, 2013 at 2:06 AM, Rémi Cura wrote:
> The only kind of function taking set of record as input I know of is
> aggregate function, but it returns only one row and the output of union can
> take multiple row.
This may or may not help (I suggest posting a more complete example of
what you are unable to do):
If your output list of records is (quite) small, you may be able to
get away with using an aggregate function.
CREATE OR REPLACE append3 (anyarray, anyelement) RETURNS anyarray AS
$$
SELECT CASE
WHEN $1 IS NULL THEN ARRAY[$2]
WHEN array_upper($1,1) >= 3 THEN $1
ELSE $1 || $2
END;
$$ LANGUAGE SQL;
CREATE AGGREGATE agg_append3(anyelement) (SFUNC = append3, STYPE = anyarray);
CREATE TABLE foo(a int, b text);
INSERT INTO foo SELECT s, s::text FROM generate_series(1,10) s;
WITH data AS (SELECT unnest(agg_append3(f)) AS d FROM foo f) SELECT
(d).* FROM data;
a | b
---+---
1 | 1
2 | 2
3 | 3
User defined aggregates can be defined over window function partitions:
SELECT a, agg_append3(f) OVER (PARTITION BY a % 2 ORDER BY a DESC) AS
d FROM foo f;
a | d
+-
10 | {"(10,10)"}
8 | {"(10,10)","(8,8)"}
6 | {"(10,10)","(8,8)","(6,6)"}
4 | {"(10,10)","(8,8)","(6,6)"}
2 | {"(10,10)","(8,8)","(6,6)"}
9 | {"(9,9)"}
7 | {"(9,9)","(7,7)"}
5 | {"(9,9)","(7,7)","(5,5)"}
3 | {"(9,9)","(7,7)","(5,5)"}
1 | {"(9,9)","(7,7)","(5,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] need a hand with my backup strategy please...
Jamie Echlin wrote: > I was planning to rely on the disk backup (of the base backup) if I wanted to > restore to a version > before the last on disk base backup. But your point about redundancy is > good... I think I will keep > two base backups, and do a base backup every day. Over the network this takes > 5-10 mins or so, so not > a big deal. > > I'll retain wals for 48 hours. Having to go to a PIT before the last hour or > so would be a major > undertaking anyway. I have seen cases where a software bug in the application gradually caused data in the database to be changed. It took a while to notice that. In such a case you want to have a backup from a month ago or more so that you can extract the data as they were back then and try to repair as much as possible. Also, what if a problem was introduced right before the weekend and noticed immediately afterwards? That might be more than 48 hours ago. There are other scenarios where a backup from longer ago would really help. 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] Using ODBC and VBA to pull data from a large object
On Tue, 2013-09-17 at 08:32 -0400, Adam C Falkenberg wrote: > Good Morning, > > I had a question about using ODBC with large objects. When I query > the pg_largeobject table and return data to an ADO recordset, I can > only get the first 255 bytes even though the record contains 2048 > bytes of data (all the bytes after the first 255 show as 0). When I > checked the type of the record, it was a VarBinary. Is there a way to > have all of the data returned to the recordset? Thanks for any help. > > Adam Microsofts sample http://support.microsoft.com/kb/258038 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] remove everything before the period
Hi,
I am trying to remove everything before the period in other words i only
want to show the values that starts from the period. For instance
897.78 ==> 78
74.25 ==> 25
3657.256 ==> 256
well the code below only shows everything before the period but i want to
show everything after the period
select volume, substring(volume from 1 for position('.' in volume) - 1) as
column
from MyTable;
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/remove-everything-before-the-period-tp5771179.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] using Replace funcion in postgresql
just as FYI the database i am using is Netezza so my data type is CHARACTER VARYING(6). The error i am getting is 'Buffer Overflow'. So the funny thing is when i change this '' to this ' ' then the query runs but it is putting a blank space between the values like this 88 97 but i get an error when i only make with out space like this ''. thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/using-Replace-funcion-in-postgresql-tp5771164p5771171.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] Number of WAL segment
Hi, I am using PostgreSQL 8.3.8!!! How can I increase number of WAL segments in pg_xlog??? Current settings are: checkpoint_segments=10 checkpoint_completion_target=0.5 WAL segments are generated every 10 min and I want to keep WAL segments in pg_xlog for at least 3h. Thanks... -- View this message in context: http://postgresql.1045698.n5.nabble.com/Number-of-WAL-segment-tp5771221.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] How to compare the results of two queries?
I am developing a task in which I need to know how to compare the
results of two queries ...
I thought about creating a procedure which both queries received by
parameters respectively. Then somehow able to run queries and return if
both have the same result. As a feature of the problem, both queries are
selection.
Here I leave a piece of code I want to do.
create or replace function compare(sql1 character varying, sql2
character varying) returns boolean as
$body$
Declare
Begin
--here in some way to run both queries and then compare
End;
$body$
language 'plpgsql';
I've been studying and I found that there EXECUTE but to use it, first
you should have used PREPARE, and in this case the values of the
parameters are already made inquiries.
For example the execution of the function would be something like ...
select compare('select * from table1', 'select * from table2');
For this case the result is false, then the queries are executed on
different tables.
Thanks in advance.
Best regards from Cuba.
__
"Todos el 12 de Septiembre con una Cinta Amarilla"
FIN A LA INJUSTICIA, LIBERENLOS YA!!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.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] best practice for || set of rows --> function --> set of rows
remi.cura wrote > What is best practice for input? There is none; you have options because different scenarios require different solutions. > I see 3 solutions : > _give table name as input || so no order unless I use view, doesn't work > with CTE and all. > _give array and use unnest/arrayagg || bad performance with big input > _give refcursor || non-convenient because of transaction and require 2 > separate queries. To generalize: Input Types: -indirect (refcursor or table name) -direct (parameters w/ data - including arrays) Output Types: -Scalar -Set-Of -Indirect (refcursor, table name) Note that you can standardize on a table name and use the indirect output to communicate other information - like what subset of the table do you want to consider. One thought is to populate an input staging table using some kind of "transaction id"; run a function providing it the "transaction id" upon which it should operate; have said function populate an output table using the same transaction id. In effect you cache both the input and output data and then have your application query those caches (mainly the output cache) to obtain its results. Much more knowledge of the architecture in which the problem needs to operate, and the problem itself, is needed to make reasonable suggestions (as opposed to mere thought starters). I find the statement "non-convenient because of transaction and require 2 separate queries" to be utter nonsense at face value but again that stems from not knowing what limitations you are facing. You can embed "order" information into a table and I am unsure why it would not work with a CTE. I imagine something like: WITH pop ( SELECT populate_table() AS trans_id ) , SELECT * FROM process_table ( SELECT trans_id FROM pop ) ; HTH David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/best-practice-for-set-of-rows-function-set-of-rows-tp5771189p5771265.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 compare the results of two queries?
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Juan Daniel Santana Rodés
> Sent: Tuesday, September 17, 2013 11:00 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to compare the results of two queries?
>
> I am developing a task in which I need to know how to compare the results of
> two queries ...
> I thought about creating a procedure which both queries received by
> parameters respectively. Then somehow able to run queries and return if
> both have the same result. As a feature of the problem, both queries are
> selection.
> Here I leave a piece of code I want to do.
>
> create or replace function compare(sql1 character varying, sql2 character
> varying) returns boolean as $body$ Declare Begin --here in some way to run
> both queries and then compare End; $body$ language 'plpgsql';
>
> I've been studying and I found that there EXECUTE but to use it, first you
> should have used PREPARE, and in this case the values of the parameters are
> already made inquiries.
> For example the execution of the function would be something like ...
>
> select compare('select * from table1', 'select * from table2');
>
> For this case the result is false, then the queries are executed on different
> tables.
> Thanks in advance.
> Best regards from Cuba.
EXECUTE in PgPlsql does not require PREPARE.
So, something like this:
create or replace function compare(sql1 character varying, sql2 character
varying) returns boolean as
$body$
Declare lCount int := 0;
Begin
EXECUTE 'SELECT COUNT(Res.*) FROM ( (' || sql1 || ' EXCEPT ' || sql2 || ')
UNION (' || sql2 || ' EXCEPT ' || sql1 || ') ) Res' INTO lCount;
IF (lCount = 0)
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
End;
$body$ language 'plpgsql';
should work. Be aware, I didn't test it.
Regards,
Igor Neyman
--
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 compare the results of two queries?
On 17/09/2013 15:59, Juan Daniel Santana Rodés wrote: > I am developing a task in which I need to know how to compare the > results of two queries ... > I thought about creating a procedure which both queries received by > parameters respectively. Then somehow able to run queries and return if > both have the same result. As a feature of the problem, both queries are > selection. > Here I leave a piece of code I want to do. > > create or replace function compare(sql1 character varying, sql2 > character varying) returns boolean as > $body$ > Declare > Begin > --here in some way to run both queries and then compare > End; > $body$ > language 'plpgsql'; > > I've been studying and I found that there EXECUTE but to use it, first > you should have used PREPARE, and in this case the values of the EXECUTE in pl/pgsql is different to EXECUTE in ordinary SQL; it's used for executing queries constructed on-the-fly as strings. You don't need to do a PREPARE before EXECUTE in a pl/pgsql function. Here's the relevant place in the docs for this form of EXECUTE: http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] using Replace funcion in postgresql
On 09/16/2013 06:20 PM, karinos57 wrote: just as FYI the database i am using is Netezza so my data type is CHARACTER VARYING(6). The error i am getting is 'Buffer Overflow'. So the funny thing is when i change this '' to this ' ' then the query runs but it is putting a blank space between the values like this 88 97 but i get an error when i only make with out space like this ''. thanks Alright now I am confused. Your subject says using replace in Postgres, yet now you say you are using Netezza, which is it? -- Adrian Klaver adrian.kla...@gmail.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 compare the results of two queries?
On Tue, Sep 17, 2013 at 4:59 PM, Juan Daniel Santana Rodés
wrote:
> For example the execution of the function would be something like ...
>
> select compare('select * from table1', 'select * from table2');
>
> For this case the result is false, then the queries are executed on
> different tables.
I suppose you are taking for sure that both queries references tables
with the same structure, in such case why not computing an hash of
each row to check against the other result set?
About how many rows are we talking? Because it sounds to me like a
good job for a diff-like external tool, is this a possible solution?
Have you considered that the tables could have a different structure
or even just a different layout, in such case a "select *" will return
different results while the data is actually the same?
What is the aim of this?
Luca
--
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 compare the results of two queries?
> -Original Message-
> From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu]
> Sent: Tuesday, September 17, 2013 11:54 AM
> To: Igor Neyman
> Subject: Re: [GENERAL] How to compare the results of two queries?
>
> El 17/09/13 11:27, Igor Neyman escribió:
> > create or replace function compare(sql1 character varying, sql2
> > character varying) returns boolean as $body$ Declare lCount int := 0;
> > Begin
> >
> > EXECUTE 'SELECT COUNT(Res.*) FROM ( (' || sql1 || ' EXCEPT ' || sql2
> > || ') UNION (' || sql2 || ' EXCEPT ' || sql1 || ') ) Res' INTO lCount; IF
> > (lCount
> = 0)
> >RETURN TRUE;
> > ELSE
> >RETURN FALSE;
> > END IF;
> >
> > End;
> > $body$ language 'plpgsql';
>
> Hi, thank for your help...
> I'm trying to execute your code but, when I run the the sentence, it throw a
> exception.
> For example, I run this line...
>
> select compare('select * from point limit 2', 'select * from point');
>
> And, postgres throw the follow exceptio...
>
> ERROR: syntax error at or near "EXCEPT"
> LINE 1: ...COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT sel...
> ^
> QUERY: SELECT COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT
> select * from point) UNION (select * from point EXCEPT select * from point
> limit 2) ) Res
> CONTEXT: PL/pgSQL function "compare" line 5 at EXECUTE statement
>
> ** Error **
>
> ERROR: syntax error at or near "EXCEPT"
> Estado SQL:42601
> Contexto:PL/pgSQL function "compare" line 5 at EXECUTE statement
>
"limit 2" does not work with "EXCEPT".
In the future reply to the list ("Reply All") in order to keep the list in the
conversation.
Regards,
Igor Neyman
--
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] Using ODBC and VBA to pull data from a large object
Thanks for the response. The example you sent is what I tried to follow when I originally worked on this. It works great for the first 255 bytes, but after that it returns 0's. Is there any way to get all of the data in a large object returned to a recordset (not just the first 255 bytes)? Thanks again. Adam C. Falkenberg Quality Engineer Great Lakes Works Phone: (313) 749 - 3758 Cell: (313) 910 - 3195 From: Bret Stern To: Adam C Falkenberg , Cc: pgsql-general@postgresql.org Date: 09/17/2013 10:06 AM Subject:Re: [GENERAL] Using ODBC and VBA to pull data from a large object On Tue, 2013-09-17 at 08:32 -0400, Adam C Falkenberg wrote: > Good Morning, > > I had a question about using ODBC with large objects. When I query > the pg_largeobject table and return data to an ADO recordset, I can > only get the first 255 bytes even though the record contains 2048 > bytes of data (all the bytes after the first 255 show as 0). When I > checked the type of the record, it was a VarBinary. Is there a way to > have all of the data returned to the recordset? Thanks for any help. > > Adam Microsofts sample http://support.microsoft.com/kb/258038
Re: [GENERAL] remove everything before the period
Il 17/09/2013 04:21, karinos57 ha scritto:
Hi,
I am trying to remove everything before the period in other words i only
want to show the values that starts from the period. For instance
897.78 ==> 78
74.25 ==> 25
3657.256 ==> 256
well the code below only shows everything before the period but i want to
show everything after the period
select volume, substring(volume from 1 for position('.' in volume) - 1) as
column
from MyTable;
Try with: " SELECT volume, substring(volume from position('.' in volume)
+ 1) AS column FROM MyTable; "
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation
Greetings, I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant performance degradation. PostgreSQL simply feels slower. Nothing other than the version of PostgreSQL changed yesterday. I used pg_upgrade to perform the upgrade, and ran the generated analyze_new_cluster.sh immediately afterwards, which completed successfully. Prior to the upgrade, I'd generally expect a load average of less than 2.00 on the master, and less than 1.00 on each of the slaves. Since the upgrade, the load average on the master has been in double digits (hitting 100.00 for a few minutes), and the slaves are consistently above 5.00. There are a few things that are jumping out at me as behaving differently since the upgrade. vmstat processes waiting for runtime counts have increased dramatically. Prior to the upgrade the process count would be consistently less than 10, however since upgrading it hovers between 40 & 60 at all times. /proc/interrupts "Local timer interrupts" has increased dramatically as well. It used to hover around 6000 and is now over 20k much of the time. However, I'm starting to suspect that they are both symptoms of the problem rather than the cause. At this point, I'm looking for guidance on how to debug this problem more effectively. thanks -- 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] Using ODBC and VBA to pull data from a large object
Sorry about that. Here's the driver information and some code. Thanks.
Driver Name: PostgreSQL ANSI
Version: 9.02.01.00
constr = "Driver={PostgreSQL ANSI}; Server=servername; Port=5432;
Database=databasename; Uid=username; Pwd=password;"
With conn
.ConnectionString = (constr)
.Open
End With
SQL = "SELECT data FROM pg_largeobject WHERE loid = " & id & " ORDER BY
pageno"
rs.Open SQL, conn
stream.Type = adTypeBinary
stream.Open
' Loop through the recordset and write the binary data to the stream
While Not rs.EOF
stream.Write rs.Fields("data").Value
rs.MoveNext
Wend
Adam
From: Andrew Satori
To: Adam C Falkenberg ,
Date: 09/17/2013 12:02 PM
Subject:Re: [GENERAL] Using ODBC and VBA to pull data from a large
object
You don't say with which driver.
ODBC can be a bit twitchy with data types, and I have seen several drivers
fail when they attempt to read the .Value, some of the driver don't pass
through the adTypeBinarry and allocate a MAX_LENGTH string of 255 for the
read buffer. I haven't tested the current driver from pg.org, but when I
did a few months ago, it correctly handled the .Type field and allocated
the length appropriately.
Some version information and source would make this far easier to resolve.
On Sep 17, 2013, at 11:51 AM, Adam C Falkenberg
wrote:
> Thanks for the response. The example you sent is what I tried to follow
when I originally worked on this. It works great for the first 255 bytes,
but after that it returns 0's. Is there any way to get all of the data in
a large object returned to a recordset (not just the first 255 bytes)?
Thanks again.
>
> Adam
>
>
>
> From:Bret Stern
> To:Adam C Falkenberg ,
> Cc:pgsql-general@postgresql.org
> Date:09/17/2013 10:06 AM
> Subject:Re: [GENERAL] Using ODBC and VBA to pull data from a
large object
>
>
>
> On Tue, 2013-09-17 at 08:32 -0400, Adam C Falkenberg wrote:
> > Good Morning,
> >
> > I had a question about using ODBC with large objects. When I query
> > the pg_largeobject table and return data to an ADO recordset, I can
> > only get the first 255 bytes even though the record contains 2048
> > bytes of data (all the bytes after the first 255 show as 0). When I
> > checked the type of the record, it was a VarBinary. Is there a way to
> > have all of the data returned to the recordset? Thanks for any help.
> >
> > Adam
>
>
> Microsofts sample
> http://support.microsoft.com/kb/258038
>
>
Re: [GENERAL] How to compare the results of two queries?
> -Original Message- > From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu] > Sent: Tuesday, September 17, 2013 12:51 PM > To: Igor Neyman > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] How to compare the results of two queries? > > > > I want to know if there are other way to compare the result of two queries. > Because the arguments will represent a query to execute and it can use > everything sentence of SQL. > If not there are other way, I wish know who are the limitations of EXCEPT. > Greatens!! > __ In the modified function I put both queries in parenthesis, so this should allow pretty much anything in the query. Igor Neyman -- 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] upgrade from 9.2.x to 9.3 causes significant performance degradation
On Tue, 17 Sep 2013 09:19:29 -0700 Lonni J Friedman wrote: > Greetings, > I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming > replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded > from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant > performance degradation. PostgreSQL simply feels slower. Nothing > other than the version of PostgreSQL changed yesterday. I used > pg_upgrade to perform the upgrade, and ran the generated > analyze_new_cluster.sh immediately afterwards, which completed > successfully. > > Prior to the upgrade, I'd generally expect a load average of less than > 2.00 on the master, and less than 1.00 on each of the slaves. Since > the upgrade, the load average on the master has been in double digits > (hitting 100.00 for a few minutes), and the slaves are consistently > above 5.00. > > There are a few things that are jumping out at me as behaving > differently since the upgrade. vmstat processes waiting for runtime > counts have increased dramatically. Prior to the upgrade the process > count would be consistently less than 10, however since upgrading it > hovers between 40 & 60 at all times. /proc/interrupts "Local timer > interrupts" has increased dramatically as well. It used to hover > around 6000 and is now over 20k much of the time. However, I'm > starting to suspect that they are both symptoms of the problem rather > than the cause. > > At this point, I'm looking for guidance on how to debug this problem > more effectively. Don't know what happens but: a) Does analyze_new_cluster.sh include a reindex? If not, indexs are useless because analyze statistics says so. b) Did you configure postgresql.conf on 9.3.0 for your server/load? Perhaps it has default install values. c) What does logs say? > thanks > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general --- --- Eduardo Morras -- 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 compare the results of two queries?
On Tue, 17 Sep 2013 10:59:43 -0400
Juan Daniel Santana Rodés wrote:
> I've been studying and I found that there EXECUTE but to use it, first
> you should have used PREPARE, and in this case the values of the
> parameters are already made inquiries.
> For example the execution of the function would be something like ...
>
> select compare('select * from table1', 'select * from table2');
>
> For this case the result is false, then the queries are executed on
> different tables.
If you create a table with:
CREATE TABLE comp
(
result1 : hstore,
result2 : hstore
);
insert in it the rows from selects:
INSERT INTO comp(result1, result2)
(SELECT * FROM table1, SELECT * FROM table2);
Substitute (SELECT * FROM table, SELECT * FROM table2) with your queries, store
the result of the queries on table1 and table2 tables or use a WITH in the
INSERT.
you can get the differences between both queries using '-' hstore operator:
SELECT (result1 - result2) as LEFT, (result2 - result1) as RIGHT FROM comp;
Or simulating an equal instruction:
SELECT (COUNT(result1 - result2)+COUNT(result2 - result1)=0) FROM comp; -- Not
sure about this one because uses COUNT on a hstore data column.
> Thanks in advance.
> Best regards from Cuba.
--- ---
Eduardo Morras
--
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 compare the results of two queries?
El 17/09/13 12:02, Igor Neyman escribió:
-Original Message-
From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu]
Sent: Tuesday, September 17, 2013 11:54 AM
To: Igor Neyman
Subject: Re: [GENERAL] How to compare the results of two queries?
El 17/09/13 11:27, Igor Neyman escribió:
create or replace function compare(sql1 character varying, sql2
character varying) returns boolean as $body$ Declare lCount int := 0;
Begin
EXECUTE 'SELECT COUNT(Res.*) FROM ( (' || sql1 || ' EXCEPT ' || sql2
|| ') UNION (' || sql2 || ' EXCEPT ' || sql1 || ') ) Res' INTO lCount; IF
(lCount
= 0)
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
End;
$body$ language 'plpgsql';
Hi, thank for your help...
I'm trying to execute your code but, when I run the the sentence, it throw a
exception.
For example, I run this line...
select compare('select * from point limit 2', 'select * from point');
And, postgres throw the follow exceptio...
ERROR: syntax error at or near "EXCEPT"
LINE 1: ...COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT sel...
^
QUERY: SELECT COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT
select * from point) UNION (select * from point EXCEPT select * from point
limit 2) ) Res
CONTEXT: PL/pgSQL function "compare" line 5 at EXECUTE statement
** Error **
ERROR: syntax error at or near "EXCEPT"
Estado SQL:42601
Contexto:PL/pgSQL function "compare" line 5 at EXECUTE statement
"limit 2" does not work with "EXCEPT".
In the future reply to the list ("Reply All") in order to keep the list in the
conversation.
Regards,
Igor Neyman
I want to know if there are other way to compare the result of two queries.
Because the arguments will represent a query to execute and it can use
everything sentence of SQL.
If not there are other way, I wish know who are the limitations of EXCEPT.
Greatens!!
__
"Todos el 12 de Septiembre con una Cinta Amarilla"
FIN A LA INJUSTICIA, LIBERENLOS YA!!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.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 compare the results of two queries?
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Igor Neyman
> Sent: Tuesday, September 17, 2013 12:02 PM
> To: Juan Daniel Santana Rodés; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to compare the results of two queries?
>
>
>
> > -Original Message-
> > From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu]
> > Sent: Tuesday, September 17, 2013 11:54 AM
> > To: Igor Neyman
> > Subject: Re: [GENERAL] How to compare the results of two queries?
> >
> > El 17/09/13 11:27, Igor Neyman escribió:
> > > create or replace function compare(sql1 character varying, sql2
> > > character varying) returns boolean as $body$ Declare lCount int :=
> > > 0; Begin
> > >
> > > EXECUTE 'SELECT COUNT(Res.*) FROM ( (' || sql1 || ' EXCEPT ' ||
> > > sql2
> > > || ') UNION (' || sql2 || ' EXCEPT ' || sql1 || ') ) Res' INTO
> > > || lCount; IF (lCount
> > = 0)
> > >RETURN TRUE;
> > > ELSE
> > >RETURN FALSE;
> > > END IF;
> > >
> > > End;
> > > $body$ language 'plpgsql';
> >
> > Hi, thank for your help...
> > I'm trying to execute your code but, when I run the the sentence, it
> > throw a exception.
> > For example, I run this line...
> >
> > select compare('select * from point limit 2', 'select * from point');
> >
> > And, postgres throw the follow exceptio...
> >
> > ERROR: syntax error at or near "EXCEPT"
> > LINE 1: ...COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT sel...
> > ^
> > QUERY: SELECT COUNT(Res.*) FROM ( (select * from point limit 2
> > EXCEPT select * from point) UNION (select * from point EXCEPT select *
> > from point limit 2) ) Res
> > CONTEXT: PL/pgSQL function "compare" line 5 at EXECUTE statement
> >
> > ** Error **
> >
> > ERROR: syntax error at or near "EXCEPT"
> > Estado SQL:42601
> > Contexto:PL/pgSQL function "compare" line 5 at EXECUTE statement
> >
>
> "limit 2" does not work with "EXCEPT".
>
> In the future reply to the list ("Reply All") in order to keep the list in the
> conversation.
>
> Regards,
> Igor Neyman
>
Well, if you really want to use "limit" clause in your queries, the following
should work (even with the "limit"):
create or replace function compare(sql1 character varying, sql2 character
varying) returns boolean
as $body$
Declare lCount int := 0;
Begin
EXECUTE 'SELECT COUNT(Res.*) FROM ( ((' || sql1 || ') EXCEPT (' || sql2 ||
')) UNION ((' || sql2 || ') EXCEPT (' || sql1 || ')) ) Res' INTO || lCount;
IF (lCount = 0)
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
End;
$body$ language 'plpgsql';
Regards,
Igor Neyman
--
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] Number of WAL segment
Hi, (13/09/17 21:46), tdev457 wrote: Hi, I am using PostgreSQL 8.3.8!!! How can I increase number of WAL segments in pg_xlog??? Current settings are: checkpoint_segments=10 checkpoint_completion_target=0.5 WAL segments are generated every 10 min and I want to keep WAL segments in pg_xlog for at least 3h. In 8.3, the number of wal segments can not be controlled explicitly. # If you can use version 9.0 or higher, please see here: # http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS In any case, It is not possible to control the retention period. I recommend WAL Archiving, if you want to save 3 hour's wal segments with certainty. Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connect postgres to SQLSERVER
HI, Is there a way to connect to a sqlserver like dblink? I just need to execute a Procedure in sqlserver when something happen -- Gracias - Agustín Larreinegabe
Re: [GENERAL] How to compare the results of two queries?
El 17/09/13 12:56, Igor Neyman escribió: -Original Message- From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu] Sent: Tuesday, September 17, 2013 12:51 PM To: Igor Neyman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to compare the results of two queries? I want to know if there are other way to compare the result of two queries. Because the arguments will represent a query to execute and it can use everything sentence of SQL. If not there are other way, I wish know who are the limitations of EXCEPT. Greatens!! __ In the modified function I put both queries in parenthesis, so this should allow pretty much anything in the query. Igor Neyman Thanks. I tested your code and worked fine. Now I only should catch the exception when the results of the querires has diferents munbers of columns. God bless you. __ "Todos el 12 de Septiembre con una Cinta Amarilla" FIN A LA INJUSTICIA, LIBERENLOS YA!! http://www.antiterroristas.cu http://justiciaparaloscinco.wordpress.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] upgrade from 9.2.x to 9.3 causes significant performance degradation
On Tue, Sep 17, 2013 at 9:54 AM, Eduardo Morras wrote: > On Tue, 17 Sep 2013 09:19:29 -0700 > Lonni J Friedman wrote: > >> Greetings, >> I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming >> replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded >> from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant >> performance degradation. PostgreSQL simply feels slower. Nothing >> other than the version of PostgreSQL changed yesterday. I used >> pg_upgrade to perform the upgrade, and ran the generated >> analyze_new_cluster.sh immediately afterwards, which completed >> successfully. >> >> Prior to the upgrade, I'd generally expect a load average of less than >> 2.00 on the master, and less than 1.00 on each of the slaves. Since >> the upgrade, the load average on the master has been in double digits >> (hitting 100.00 for a few minutes), and the slaves are consistently >> above 5.00. >> >> There are a few things that are jumping out at me as behaving >> differently since the upgrade. vmstat processes waiting for runtime >> counts have increased dramatically. Prior to the upgrade the process >> count would be consistently less than 10, however since upgrading it >> hovers between 40 & 60 at all times. /proc/interrupts "Local timer >> interrupts" has increased dramatically as well. It used to hover >> around 6000 and is now over 20k much of the time. However, I'm >> starting to suspect that they are both symptoms of the problem rather >> than the cause. >> >> At this point, I'm looking for guidance on how to debug this problem >> more effectively. > > Don't know what happens but: > > a) Does analyze_new_cluster.sh include a reindex? If not, indexs are useless > because analyze statistics says so. No, it doesn't include a reindex. It merely invokes "vacuumdb --all --analyze-only" with different values for default_statistics_target=1 -c vacuum_cost_delay=0. According to the documentation for pg_upgrade, post-upgrade scripts to rebuild tables and indexes will be generated automatically. Nothing was generated for this purpose, at least not in any obvious place. The analyze_new_cluster.sh script is the only one that was automatically generated as far as I can tell. > b) Did you configure postgresql.conf on 9.3.0 for your server/load? Perhaps > it has default install values. Yes, I'm using the same postgresql.conf as I was using when running 9.2.4. Its definitely not running with default install values. > c) What does logs say? The postgres server logs look perfectly normal, minus a non-trivial slower run time for most queries. There's nothing unusual in any of the OS level logs (/var/log/messages, etc) or dmesg. Do you have any other suggestions? -- 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 compare the results of two queries?
Juan Daniel Santana Rodés wrote:
> I am developing a task in which I need to know how to compare the
> results of two queries ...
> I thought about creating a procedure which both queries received by
> parameters respectively. Then somehow able to run queries and return if
> both have the same result. As a feature of the problem, both queries are
> selection.
Maybe something roughly like this?:
create or replace function rscmp(qry1 text, qry2 text)
returns boolean
language plpgsql
as $$
declare
c int;
begin
execute 'select count(*) from ('
|| qry1
|| ') rs1 full join ('
|| qry2
|| ') rs2 on rs1 = rs2 where rs1 is not distinct from null or rs2 is not
distinct from null'
into c;
return (c = 0);
exception
when sqlstate '42804' then return false;
end;
$$;
--
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] Connect postgres to SQLSERVER
On 9/17/2013 11:05 AM, Agustin Larreinegabe wrote: Is there a way to connect to a sqlserver like dblink? I just need to execute a Procedure in sqlserver when something happen postgresql *is* a sql server.or do you mean Microsoft SQL Server ? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] upgrade from 9.2.x to 9.3 causes significant performance degradation
On Tue, Sep 17, 2013 at 11:22 AM, Lonni J Friedman wrote: > > > c) What does logs say? > > The postgres server logs look perfectly normal, minus a non-trivial > slower run time for most queries. There's nothing unusual in any of > the OS level logs (/var/log/messages, etc) or dmesg. > Are you generally CPU limited or IO limited? Grab one of those slower queries and do an "explain (analyze, buffers)" of it. Preferably with track_io_timing on. Do you still have test/dev/QA/etc copy running on 9.2 for comparison? Cheers, Jeff
Re: [GENERAL] Connect postgres to SQLSERVER
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Agustin Larreinegabe Sent: Tuesday, September 17, 2013 2:06 PM To: PostgreSQL mailing lists Subject: [GENERAL] Connect postgres to SQLSERVER HI, Is there a way to connect to a sqlserver like dblink? I just need to execute a Procedure in sqlserver when something happen -- Gracias - Agustín Larreinegabe - One option is to use "Linked Server" feature to connect from MS SQL Server to Postgres through ODBC driver. Regards, Igor Neyman -- 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 compare the results of two queries?
> -Original Message- > From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu] > Sent: Tuesday, September 17, 2013 1:38 PM > To: Igor Neyman > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] How to compare the results of two queries? > > El 17/09/13 12:56, Igor Neyman escribió: > > > >> -Original Message- > >> From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu] > >> Sent: Tuesday, September 17, 2013 12:51 PM > >> To: Igor Neyman > >> Cc: pgsql-general@postgresql.org > >> Subject: Re: [GENERAL] How to compare the results of two queries? > >> > >> I want to know if there are other way to compare the result of two > queries. > >> Because the arguments will represent a query to execute and it can > >> use everything sentence of SQL. > >> If not there are other way, I wish know who are the limitations of EXCEPT. > >> Greatens!! > >> __ > > > > In the modified function I put both queries in parenthesis, so this should > allow pretty much anything in the query. > > > > Igor Neyman > Thanks. > I tested your code and worked fine. > Now I only should catch the exception when the results of the querires has > diferents munbers of columns. > God bless you. > __ > "Todos el 12 de Septiembre con una Cinta Amarilla" > FIN A LA INJUSTICIA, LIBERENLOS YA!! > http://www.antiterroristas.cu > http://justiciaparaloscinco.wordpress.com And of course, not just number of columns in the result sets, but their types should match as well. Igor Neyman -- 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] Connect postgres to SQLSERVER
On Wed, Sep 18, 2013 at 12:11 AM, Igor Neyman wrote: > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Agustin Larreinegabe > Sent: Tuesday, September 17, 2013 2:06 PM > To: PostgreSQL mailing lists > Subject: [GENERAL] Connect postgres to SQLSERVER > > HI, > > Is there a way to connect to a sqlserver like dblink? > I just need to execute a Procedure in sqlserver when something happen > > -- > Gracias > - > Agustín Larreinegabe > > - > > One option is to use "Linked Server" feature to connect from MS SQL Server to > Postgres through ODBC driver. > > Regards, > Igor Neyman > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Or use a FDW, like JDBC_FDW. -- 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] ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?
On Wed, Sep 11, 2013 at 8:00 PM, Gregory Haase wrote: > Typically how fast is a crash recovery for a ~1TB database with heavy OTLP > load? Are we talking several seconds, several minutes, several hours? > This will depend on how fast your file system is, how frequent your checkpoints are (this is tunable), and how many WAL segments you allow (also tunable). The trade off is if you do less frequent checkpointing and have more WAL segments, you can boost your write speeds, but the cost is longer recovery. Ideally you want to tune the number of WAL segments to be just the right number to keep from forcing checkpoints before your configured timeout to run a checkpoint, and you configure your checkpoint time to whatever duration of time you need to keep your recovery time as short as you want.
Re: [GENERAL] Connect postgres to SQLSERVER
On 17/09/2013 19:05, Agustin Larreinegabe wrote: HI, Is there a way to connect to a sqlserver like dblink? I just need to execute a Procedure in sqlserver when something happen -- Gracias - Agustín Larreinegabe If all you want to do is trigger a single stored proc, one option might be to write a function in an unrestricted procedural language (e.g. pl/PerlU) which connects and runs it using a shell command. It sounds more complicated on the face of it, but it means you don't need to worry so much about configuring drivers to make the two DBs talk to each other, you can just concentrate on the single task you need to work. This will probably seem less good an idea if you need to retrieve data from the stored proc, or if there's a possibility of more complex linkages being needed later anyway. -- Rowan Collins [IMSoP] -- 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] upgrade from 9.2.x to 9.3 causes significant performance degradation
Thanks for your reply. Comments/answers inline below On Tue, Sep 17, 2013 at 11:28 AM, Jeff Janes wrote: > On Tue, Sep 17, 2013 at 11:22 AM, Lonni J Friedman > wrote: >> >> >> > c) What does logs say? >> >> The postgres server logs look perfectly normal, minus a non-trivial >> slower run time for most queries. There's nothing unusual in any of >> the OS level logs (/var/log/messages, etc) or dmesg. > > > Are you generally CPU limited or IO limited? top shows over 90% of the load is in sys space. vmstat output seems to suggest that its CPU bound (or bouncing back & forth): procs ---memory-- ---swap-- -io --system-- -cpu- r b swpd free buff cache si sobibo in cs us sy id wa st 1 0 17308 852016 141104 12707419200101800 6 4 90 0 0 0 0 17308 872316 141104 12707420000 0 988 940 564 1 0 99 0 0 0 0 17308 884288 141104 12707420800 0 1921 1202 2132 1 0 99 0 0 0 0 17308 898728 141104 12707420800 0 0 1064 577 1 0 99 0 0 2 0 17308 914920 141104 12707422400 044 820 427 1 0 99 0 0 0 0 17308 926524 141104 12707427200 048 1173 585 1 0 99 0 0 108 1 17308 753648 141104 12707422400 0 236 9825 3901 12 5 83 0 0 50 0 17308 723156 141104 12707440000 0 144 43481 9105 20 79 1 0 0 45 0 17308 722860 141104 12707441600 0 8 32969 1998 1 97 2 0 0 47 0 17308 738996 141104 12707441600 0 0 34099 1739 1 99 0 0 0 101 0 17308 770220 141104 12707448000 032 38550 5998 7 93 0 0 0 101 0 17308 775732 141104 12707451200 0 156 33889 5809 4 96 0 0 0 99 0 17308 791232 141104 12707454400 0 0 32385 4981 0 100 0 0 0 96 0 17308 803156 141104 12707454400 024 32413 4824 0 100 0 0 0 87 0 17308 811624 141104 12707454400 0 0 32438 4470 0 100 0 0 0 83 0 17308 815500 141104 12707454400 0 0 32489 4159 0 100 0 0 0 80 0 17308 826572 141104 12707455200 033 32582 3948 0 100 0 0 0 73 0 17308 853264 141108 12707455200 052 32833 3840 0 100 0 0 0 73 0 17308 882240 141108 12707456000 0 4 32820 3594 0 100 0 0 0 72 0 17308 892256 141108 12707456000 0 0 32368 3516 0 100 0 0 0 ### iostat consistently shows %util under 1.00 which also suggests that disk IO is not the bottleneck: # iostat -dx /dev/sdb 5 Linux 2.6.32-358.6.2.el6.x86_64 (cuda-db7) 09/17/2013 _x86_64_ (32 CPU) Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb 0.02 0.216.91 31.33 651.60 1121.85 46.38 0.092.25 0.08 0.31 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.000.008.00 0.0093.00 11.62 0.000.28 0.20 0.16 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.000.00 11.00 0.00 125.40 11.40 0.000.16 0.16 0.18 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.000.00 105.00 0.00 3380.40 32.19 0.292.76 0.03 0.34 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.000.00 14.80 0.00 2430.60 164.23 0.000.12 0.09 0.14 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 1.200.00 41.60 0.00 1819.40 43.74 0.020.45 0.05 0.20 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb 0.00 0.000.002.80 0.0032.00 11.43 0.000.00 0.00 0.00 # mpstat also shows a virtually 0 iowait, with a ton of sys (CPU) time: # mpstat 2 10 Linux 2.6.32-358.6.2.el6.x86_64 (cuda-db7) 09/17/2013 _x86_64_ (32 CPU) 12:53:19 PM CPU%usr %nice%sys %iowait%irq %soft %steal %guest %idle 12:53:21 PM all7.360.00 92.580.000.000.03 0.000.000.03 12:53:23 PM all6.350.00 90.430.000.000.03 0.000.003.19 12:53:25 PM all3.130.00 68.200.000.000.02 0.000.00 28.66 12:53:27 PM all6.070.00 68.460.000.000.03 0.000.00 25.44 12:53:29 PM all5.830.00 94.140.000.000.03 0.000.000.00 12:53:31 PM all5.750.00 94.140.000.000.11 0.000.000.00 12:53:33 PM all7.650.00 40.320.000.000.03
Re: [GENERAL] ZFS snapshots - to use pg_start_backup() and pg_stop_backup() or no?
Thanks. If anyone is interested, I added postgresql to the zfstools project and the owner merged my pull request: https://github.com/bdrewery/zfstools I know there are probably 1000 ways to do this out there, but we were already using this code to manage our MySQL zfs snapshotting process, so extending it became the path of least resistance. The tricky part for me was separating the pg_stop_backup command from the first two commands so that you don't get into a situation where you start backup, fail on zfs snapshot create, and then pg_stop_backup is not executed. Maybe not the most elegant, but I'd rather have a failed pg_stop_backup command than a pg_start_backup command that is left open indefinitely. Greg Haase On Tue, Sep 17, 2013 at 12:33 PM, Vick Khera wrote: > > On Wed, Sep 11, 2013 at 8:00 PM, Gregory Haase wrote: > >> Typically how fast is a crash recovery for a ~1TB database with heavy >> OTLP load? Are we talking several seconds, several minutes, several hours? >> > > This will depend on how fast your file system is, how frequent your > checkpoints are (this is tunable), and how many WAL segments you allow > (also tunable). The trade off is if you do less frequent checkpointing and > have more WAL segments, you can boost your write speeds, but the cost is > longer recovery. Ideally you want to tune the number of WAL segments to be > just the right number to keep from forcing checkpoints before your > configured timeout to run a checkpoint, and you configure your checkpoint > time to whatever duration of time you need to keep your recovery time as > short as you want. >
[GENERAL] Something Weird Going on with VACUUM ANALYZE
Hey, This is PostgreSQL 9.1.9. So we've had "vacuumdb -avz" launched via cron at 3am for a few years now, and recently noticed some queries behaving very badly. While checking pg_stat_user_tables, I see this for several hundred of them: relname | last_analyze -+-- some_table | 13-SEP-13 03:27:13.289291 -05:00 another_table | 13-SEP-13 03:33:51.262007 -05:00 yet_another_table | 13-SEP-13 03:23:27.630734 -05:00 Yet last_vacuum shows this: relname | last_vacuum +-- some_table | 17-SEP-13 03:23:41.84311 -05:00 another_table | 17-SEP-13 03:21:25.588267 -05:00 yet_another_table | 17-SEP-13 03:21:28.944848 -05:00 So I thought to myself, "Self, that's pretty freaking odd." The last vacuumdb (with analyze flag enabled) was this morning at 3am. Apparently something magical happened last Friday, and now analyze is broken somehow? Am I missing something, here? The log claims everything worked out OK: 2013-09-17 03:20:37 CDT|STATEMENT: VACUUM (VERBOSE, ANALYZE); 2013-09-17 03:37:31 CDT|LOG: duration: 2246467.567 ms statement: VACUUM (VERBOSE, ANALYZE); These are from the same pid doing the vacuum. What's weird, is that the lines don't match up in time. The reported duration is 37 minutes, and since the vacuum launches at 3:00am, it matches with the last line. If that's the case, what on Earth is that line at 3:20 all about? The durations for the last few days have also been about 50% shorter than historically, which is mysterious all by itself. I mean... WITH stats AS ( SELECT relname, ceil(extract(epoch FROM last_vacuum)) AS vac_sec, ceil(extract(epoch FROM last_analyze)) AS an_sec FROM pg_stat_all_tables WHERE last_vacuum IS NOT NULL AND last_analyze IS NOT NULL ) SELECT sum(CASE WHEN vac_sec = an_sec THEN 1 ELSE 0 END) AS same_vac, sum(CASE WHEN vac_sec != an_sec THEN 1 ELSE 0 END) AS diff_vac FROM stats same_vac | diff_vac --+-- 508 | 739 I can't really figure this out. Any help would be appreciated. Since VACUUM ANALYZE as a single statement is being wonky, I'm willing to use a script like this until we can figure out the cause: DO $$ DECLARE tablename VARCHAR; schemaname VARCHAR; BEGIN FOR schemaname, tablename IN SELECT s.schemaname, s.relname FROM pg_stat_all_tables s WHERE s.last_analyze < CURRENT_DATE LOOP EXECUTE 'ANALYZE ' || quote_ident(schemaname) || '.' || quote_ident(tablename); END LOOP; END; $$ LANGUAGE plpgsql; -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Registration for the German-speaking PostgreSQL Conference is open
The German-speaking PostgreSQL Conference takes place in Oberhausen on November 8th. The registration is now open, please visit the website: http://2013.pgconf.de/ The keynote will be delivered by Peter van Hardenberg who talks about "The World's Largest Postgres Install". In sum we got submissions from 25 different speakers - right now we are in the process of selecting the talks and creating a schedule. More details soon. The Early Bird ends October 7th, one month before the conference. Please be aware that we have a limited number of tickets. See you in Oberhausen -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project -- 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 does this array query fail?
> > Can we please follow list norms (or at least my example since I was the > first to respond) and bottom-post. Absolutely. Gmail did it without my realizing, but my bad and I'm all for following the list conventions. Thanks for taking the time to explain this stuff, which I appreciate. Mostly it makes sense, but a couple of things remain puzzling to me. 1) On what exactly does PG base its decision to interpret the ANY as scalar or not? Or are you saying a sub-query will always be treated as non-scalar, unless it is explicitly cast to an array? 2) Regarding: > In this context PostgreSQL goes looking for an operator - > e.g., equals(=) - with the right-side argument being of the type of the > sub-query column. Why? In this case you have ANY (varchar[]), which as I understand it "=" needs to compare with a varchar. So why is it looking for an array? If it was just varchar = varchar[], I'd get that, but in this case it's ANY(varchar[]), so does PG extract the type of the argument to ANY without paying attention to the ANY itself? There is quite a bit more to this that can be gleaned by reading the > documentation for sub-queries. I'm not sure if there's something specific you're referring to. I had looked at the page on subquery expressions ( http://www.postgresql.org/docs/9.0/static/functions-subquery.html), as well as the following page on "row and array comparisons" to see the two forms of ANY, but don't see anything that covers these nuances in greater depth. Is there another page I should be looking at? Note that "(SELECT ARRAY[...])::text[]" is only a proper solution if... Yeah, I tried to boil down my example, but this is closer to what I was really trying to do: CREATE TEMP TABLE mytable ( codes varchar[] ); INSERT INTO mytable VALUES ( array[ 'pass','fail'] ); INSERT INTO mytable VALUES ( array[ 'found'] ); SELECT 'found' WHERE 'found' =ANY( (SELECT array_agg(code) FROM (SELECT unnest(codes) AS code FROM mytable) foo ) ); And for immediate purposes, found this worked just as well (as a non-scalar subquery, I guess): SELECT 'found' WHERE 'found' =ANY( (SELECT unnest(codes) AS code FROM mytable) ); Thanks again for your help and explanations! Ken
Re: [GENERAL] Why does this array query fail?
Ken Tanzer wrote
> 1) On what exactly does PG base its decision to interpret the ANY as
> scalar or not? Or are you saying a sub-query will always be treated as
> non-scalar, unless it is explicitly cast to an array?
Correct. With respect to a sub-query inside ANY(...) it will be treated as
non-scalar. You can explicitly make it scalar by casting it to an array -
understanding that the query will fail if the sub-query does not actually
conform.
> 2) Regarding:
>
>> In this context PostgreSQL goes looking for an operator -
>> e.g., equals(=) - with the right-side argument being of the type of the
>> sub-query column.
>
>
> Why? In this case you have ANY (varchar[]), which as I understand it "="
> needs to compare with a varchar. So why is it looking for an array? If
> it
> was just varchar = varchar[], I'd get that, but in this case it's
> ANY(varchar[]), so does PG extract the type of the argument to ANY without
> paying attention to the ANY itself?
No. The sub-query version is basically:
varchar = ANY (setof varchar[]) ... which is wrong
varchar[] = ANY (setof varchar[]) ... is correct
The "setof" is the difference; its not trying to look inside the array but
rather looking for an entire array that matches one of the arrays the
sub-query generates.
ARRAY[1,2,3] = ANY ( SELECT col1 FROM (VALUES (ARRAY[2,3,4]::int[]),
(ARRAY[1,2,3])) src (col1) )
In the above ANY has to decide whether {2,3,4} or {1,2,3} is equal to the
input; which must be an array. It does not mean "does the number 1 exist in
any of the supplied arrays". Again, it becomes more clear if you understand
ANY(subquery) can return more than one row.
> Is there another page I should be looking at?
Not that I can think of offhand.
> Note that "(SELECT ARRAY[...])::text[]" is only a proper solution if...
>
>
> Yeah, I tried to boil down my example, but this is closer to what I was
> really trying to do:
>
> CREATE TEMP TABLE mytable ( codes varchar[] );
> INSERT INTO mytable VALUES ( array[ 'pass','fail'] );
> INSERT INTO mytable VALUES ( array[ 'found'] );
> SELECT 'found' WHERE 'found' =ANY(
> (SELECT array_agg(code) FROM (SELECT unnest(codes) AS code FROM
> mytable) foo
> )
> );
>
>
> And for immediate purposes, found this worked just as well (as a
> non-scalar
> subquery, I guess):
>
> SELECT 'found' WHERE 'found' =ANY(
> (SELECT unnest(codes) AS code FROM mytable)
> );
Yes, un-nesting can make the problem go away though it too is unusual. For
the most part either use relations/sets or use arrays (for a specific
component of the schema). Your example mixes the two which makes using that
part of the schema difficult.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771343.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] upgrade from 9.2.x to 9.3 causes significant performance degradation
Hi, On 2013-09-17 09:19:29 -0700, Lonni J Friedman wrote: > I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming > replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded > from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant > performance degradation. PostgreSQL simply feels slower. Nothing > other than the version of PostgreSQL changed yesterday. I used > pg_upgrade to perform the upgrade, and ran the generated > analyze_new_cluster.sh immediately afterwards, which completed > successfully. Where did you get 9.3.0 from? Compiled it yourself? Any chance you compile with --enable-cassert or somesuch? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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 does this array query fail?
Thanks again David. I think that's all making sense to me now, except I want to follow up on your last point: Yes, un-nesting can make the problem go away though it too is unusual. For the > most part either use relations/sets or use arrays (for a specific > component of the schema). Your example mixes the two which makes using > that part of the schema difficult. I'm not sure exactly what you're saying here, but it's important to me because I've recently had to do a lot with arrays, and continue to have to do so. What I'm working with is similar to the example I gave you, but let me be more concrete. I'm working with a social service agency. Every time they see a client they fill out an "encounter" record. Part of what is tracked is what kind of services were provided, which is stored as an array of "service codes" within the encounter. The encounter also has a date. So I frequently have to provide information like "what were all the types of services this client received during the last quarter?" or "show me all the clients who received service X last year." I've learned enough to use ANY, array_agg and unnest to get through these queries, but if I'm going about this wrong or there's a better way to do it I'd love to know about it! Ken
Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation
On Tue, Sep 17, 2013 at 3:47 PM, Andres Freund wrote: > Hi, > > On 2013-09-17 09:19:29 -0700, Lonni J Friedman wrote: >> I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming >> replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded >> from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant >> performance degradation. PostgreSQL simply feels slower. Nothing >> other than the version of PostgreSQL changed yesterday. I used >> pg_upgrade to perform the upgrade, and ran the generated >> analyze_new_cluster.sh immediately afterwards, which completed >> successfully. > > Where did you get 9.3.0 from? Compiled it yourself? Any chance you > compile with --enable-cassert or somesuch? Directly from http://yum.postgresql.org. So unless the RPMs on there are built weird/wrong, I don't think that's the problem. -- 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] Unary Operators
On 15/09/2013 19:32, Andreas Ulbrich wrote: Salvete! I have the following problem. I'd like to define the operator symbol / as a left unary operator for reciprocal value of a number. I did this with a C-function and all the stuff around, but it does not work. Hiere is an extract and simplified example with the same error message as in my complex example: create function reciproce(float) returns float as $$ values(1.0::float / $1) $$ language sql immutable strict; create operator / (rightarg = float, procedure = reciproce); select / 5.0::float; --> ERROR: syntax error at or near "/" LINE 1: select / 5.0; Interesting that it's a syntax error. Defining it as // works fine, so it's not the / symbol in general that's causing the problem, but specifically a token consisting only of a single /, which presumably (nearly) matches some particular rule in the parser. The documentation page on Lexical Structure (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html) doesn't specifically mention that this is reserved when it is describing restrictions on custom operators. However, further down that page it says "The precedence and associativity of the operators is hard-wired into the parser." and "the operator precedence rules also apply to user-defined operators that have the same names as the built-in operators mentioned above". Note that + and - exist both as unary and binary operators, but with different rules; any custom operators, on the other hand, fall into a fairly low-precedence "any other" bucket. My guess is that the "hard-wired" parsing rule for the (binary) / operator is trying to match the / in your query, and failing to find its left-hand argument. The "some other operator" rule, which would be able to look for a unary operator, isn't even getting a chance. This is borne out by looking at the other operators in that table - I've tried ^ and * and get the same error (you need some extra brackets around "SELECT (* 5.0)" otherwise it would look like "SELECT *", which is an entirely different piece of syntax!). Someone who knows more about the internals may come along and tell me I'm barking up completely the wrong tree, but it's at least a logical explanation. If it is true, it should probably either be considered a parser bug, or listed as a restriction on operator creation alongside -- and /* (or both). Regards, -- Rowan Collins [IMSoP] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cannot commit when autoCommit is enabled error
Dear group,
We have been using postgresql-jdbc-8.3-604, and recently we started using
postgresql-jdbc-9.2, everything seems working fine, except we are getting
this error and could not find the solution yet. This related to our
reporting and I could say that it is not happening every report we run. If
you could help, I really appreciated.
Below is the part of the code and error that we are getting. Thanks.
ERROR:
ERROR in createReportTable(): Cannot commit when autoCommit is enabled.
org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
at
org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:703)
at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
at
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at
org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
at $Proxy0.commit(Unknown Source)
at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:40)
at $Proxy125.commit(Unknown Source)
at
com.resolution.scheduler.dao.hibernate.SalesPAFNewDaoHibernate.runStorageSQLQuery(SalesPAFNewDaoHibernate.java:219)
at
com.resolution.scheduler.service.impl.SalesPAFNewManagerImpl.runStorageSQLQuery(SalesPAFNewManagerImpl.java:151)
PART OF THE CODE:
public Integer runStorageSQLQuery(String sqlQuery) throws Exception {
int results=0;
try {
java.sql.Connection conn = getSession().connection();
boolean acs = conn.getAutoCommit();
//System.out.println("...AutoCommit status:
"+acs);
conn.setAutoCommit(false);
java.sql.PreparedStatement st = conn.prepareStatement(sqlQuery);
results = st.executeUpdate();
conn.commit();
conn.setAutoCommit(acs);
st.close();
} catch (Exception e) {
System.err.println("ERROR in runStorageSQLQuery(): " +
e.getMessage() + " sqlQuery: "+sqlQuery);
e.printStackTrace();
}
return new Integer(results);
}
Re: [GENERAL] Why does this array query fail?
Ken Tanzer wrote > So I frequently have to provide information like "what were all the types > of services this client received during the last quarter?" or "show me all > the clients who received service X last year." I've learned enough to use > ANY, array_agg and unnest to get through these queries, but if I'm going > about this wrong or there's a better way to do it I'd love to know about > it! Your example query does not ask those questions. SELECT DISTINCT service_code FROM (SELECT unnest(services_rendered_array) AS service_code FROM services_tables WHERE ...) svcs; SELECT DISTINCT client_id FROM ( SELECT * FROM services_table WHERE 'X' = ANY(services_rendered_array) ) svcs; In neither case do you need to use a sub-query answer the question. Namely, what you describe makes use of arrays only, and not relations (though the allowed array item values could be defined on a table somewhere). Option A: A. T1: session_id, client_id, service_codes[], date Note that A is the basic structured assumed for the two example queries above. Option B: B. T1: session_id, session_date, client_id B. T2: session_id (FK-many), service_code B. T2 would have a single record for each service performed within a given session while A. T1 models the multiple service aspect of a session by using an array. Incorrect Option C: C. T1: session_id, session_date, client_id C. T2: session_id, service_codes[] This makes use of a one-to-many relationship but also embeds yet another "many" aspect within C. T2 This is generally going to be a bad idea as you are now mixing the models together. And note that I do qualify this as generally since you may very well decide that C is an elegant and/or the most correct way to model your domain. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771359.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] Unary Operators
Andreas Ulbrich wrote > create operator ^- (leftarg = float, procedure = reciproce); > works too, but > create operator / (leftarg = float, procedure = reciproce); > not. Do you mean the "^" operator or the "^-" operator? Rowan claims that "^" does not in fact work here... Rowan Collins wrote > I've tried ^ and * > and get the same error (you need some extra brackets around "SELECT (* > 5.0)" otherwise it would look like "SELECT *", which is an entirely > different piece of syntax!). I would tend to concur with the "hard-wired" theory but am in the same boat that I'm not really able to prove so; but figured I'd give my support. This qualifies as documentation improvement potential at minimum so in the interest of getting more notice I'd suggest reconciling the "^" discrepancy - and maybe trying the other supposedly hybrid operators and stating which ones work and don't work for the report - and move this over there. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unary-Operators-tp5770983p5771360.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] Why does this array query fail?
Based on what you described, I think I've generally gone with option A.
Conceptually I like B better, but it's generally more complicated and
seems like overkill for simple checkbox-type options. (But as an aside, I
am looking forward to the time when ELEMENT FKs overcome their performance
issues and become part of Postgres!)
The trouble seems to be that even with Option A (services_codes in an array
within an encounter record), you still kind of end up with option C on a
client level:
client {service_codes}
client {service_codes}
There may be no way around it, but it seems like you end up needing to
write rather cumbersome queries to get at your data. OTOH there's always
room for improvement; since I'm relatively new to working extensively with
arrays, I'm hoping they become more intuitive and less painful as one gets
used to them. :)
SELECT client_id,
COALESCE(
(SELECT array_agg(code) FROM (
SELECT distinct
client_id,unnest(accessed_health_care_non_urgent_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-08-01' AND '2013-08-31'
) foo
),array['(none)'])
AS accessed_health_care_non_urgent_codes
FROM client;
It's probably way more detail than you want, but I've attached the table
structure and pasted in a quarterly report that the query above was taken
from in case you have any pointers or are simply curious.
Thanks again!
Ken
/*
CREATE OR REPLACE VIEW hch_quarterly AS
*/
SELECT *
FROM (
SELECT
export_id,
UPPER(SUBSTRING(name_last,1,2) || SUBSTRING(name_first,1,2) ||
COALESCE(to_char(dob,'MMDDYY'),'')) AS hch_id,
name_last,
name_first,
dob,
'2013-01-01' AS quarter_start_date,
'2013-03-31' AS quarter_end_date,
referral_source_code || COALESCE(' (' || referral_source_other || ')','')
AS referral_source,
facility_code AS living_situation_end,
/*
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(other_living_situation_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS other_sleeping_codes,
*/
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(other_living_situation_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31'
UNION SELECT distinct client_id,moved_from_code AS code
FROM residence_other
WHERE client_id=client.client_id
AND residence_date BETWEEN '2013-01-01' AND '2013-03-31' ) foo
),array['(none)'])) AS other_sleeping_codes,
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(accessed_health_care_non_urgent_codes) AS
code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS accessed_health_care_non_urgent_codes,
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(accessed_services_cd_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS accessed_services_cd_codes,
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(completed_services_cd_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS completed_services_cd_codes,
unnest(COALESCE((SELECT array_agg(code) FROM
(SELECT distinct client_id,unnest(accessed_services_mh_codes) AS code
FROM service_reach
WHERE client_id=client.client_id
AND service_date BETWEEN '2013-01-01' AND '2013-03-31') foo
),array['(none)'])) AS accessed_services_mh_codes,
CASE WHEN client_id IN (SELECT DISTINCT ON (client_id,staff_assign_date)
client_id
FROM staff_assign
WHERE staff_assign_type_code='PAYEE' AND staff_assign_date<='2013-03-31'
AND COALESCE(staff_assign_date_end,'2013-03-31') >= '2013-01-01'
ORDER BY client_id,staff_assign_date DESC) THEN 'Yes' ELSE 'No' END AS
has_payee,
CASE WHEN client_id IN (SELECT client_id
FROM staff_assign
WHERE staff_assign_type_code='OUTREACH' AND staff_assign_date<='2013-03-31'
AND COALESCE(staff_assign_date_end,'2013-03-31') >= '2013-01-01'
ORDER BY client_id,staff_assign_date DESC) THEN 'Yes' ELSE 'No' END AS
outreach_client,
CASE WHEN client_id IN (SELECT client_id
FROM staff_assign
WHERE staff_assign_type_code='CM' AND staff_assign_date<='2013-03-31' AND
COALESCE(staff_assign_date_end,'2013-03-31') >= '2013-01-01'
ORDER BY client_id,staff_assign_date DESC) THEN 'Yes' ELSE 'No' END AS
cm_client,
service_plan_status_code
FROM client
LEFT JOIN (SELECT DISTINCT ON (client_id) *
FROM intake_reach
WHERE intake_reach_date <= '2013-03-31'
ORDER BY client_id,intake_reach_date DESC ) AS ir USING (client_id)
LEFT JOIN (SELECT DISTINCT ON (client_id) *
FROM residence_other
WHERE residence_date <= '2013-03-31' AND
COALESCE(residence_date_end,'2013-03-31')>='2013-01-01'
ORDER BY cli
Re: [GENERAL] Why does this array query fail?
Ken Tanzer wrote > > SELECT client_id, > COALESCE( > (SELECT array_agg(code) FROM ( > SELECT distinct > client_id,unnest(accessed_health_care_non_urgent_codes) AS code > FROM service_reach > WHERE client_id=client.client_id > AND service_date BETWEEN '2013-08-01' AND '2013-08-31' > ) foo > ),array['(none)']) > AS accessed_health_care_non_urgent_codes > FROM client; Equivalent semantics: WITH clients_with_codes AS ( SELECT client_id, array_agg(code) AS client_codes FROM (SELECT client_id, unnest(accessed...) AS code FROM service_reach) foo GROUP BY client_id ) SELECT client_id, COALESCE(client_codes, ARRAY['(none)']) AS client_codes FROM client LEFT JOIN client_with_codes USING (client_id) Should (recommend testing) perform better due to the simple fact that you avoid the correlated sub-query (i.e., a sub-query that references the outer query to obtain some parameter - in this case the client_id of the current row). The goal is to create an uncorrelated sub-query/relation that contains all the data you require then JOIN it with the original outer relation using the same equality you were using in the correlated version. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771366.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] Why does this array query fail?
Ken Tanzer wrote > It's probably way more detail than you want, but I've attached the table > structure and pasted in a quarterly report that the query above was taken > from in case you have any pointers or are simply curious. Is this a quarterly report because that is how long it takes to run? On a smaller scale I've written queries like this. I enrolled in a university database design course shortly thereafter... I would suggest considering how to use functions to encapsulate some of the "medical code collecting" logic. And consider WITH/CTE constructs as well, like I used in my last message, to effectively create temporary named tables for different parts of the query. Might want to move the whole thing into function and pass in the various parameters - namely the date range - instead of hard-coding the values into the view. Those thoughts aside I've done my own share of "write-once, read never" queries and if the performance meets your needs and the maintenance burden is acceptable then sometimes you just leave this in place until someone more knowledgeable (like a future you probably) comes back and decides to toss and rewrite it. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-does-this-array-query-fail-tp5771165p5771367.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
