Re: [SQL] Rules and sequences
In response to Tom Lane : Ben Morrow b...@morrow.me.uk writes: I am trying to implement a fairly standard 'audit table' setup, but using rules instead of triggers (since it should be more efficient). Rules are sufficiently tricky that I would never, ever rely on them for auditing. Use a simple AFTER trigger instead. There are a ready solution: http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Check set of date intervals
Hi all, have such relation A: PERIOD_ID | DATE_START | DATE_END | OTHER_ATTRIBUTES... 1 | 01.01.2010 | 01.02.2010 2 | 03.02.2010 | 04.03.2010 . .. I want to search among periods for the set of periods which completely covers passed search period. As example - if I pass search period '15.01.2010 to 15.02.2010' the result set must be empty because there is gap between 01.02 and 03.02. please, suggest an idea how to implement this in SQL without writing a procedure. Thanks, Anton
Re: [SQL] Check set of date intervals
In response to Anton Gavazuk : Hi all, have such relation A: PERIOD_ID | DATE_START | DATE_END | OTHER_ATTRIBUTES... 1 | 01.01.2010 | 01.02.2010 2 | 03.02.2010 | 04.03.2010 . .. I want to search among periods for the set of periods which completely covers passed search period. As example - if I pass search period '15.01.2010 to 15.02.2010' the result set must be empty because there is gap between 01.02 and 03.02. please, suggest an idea how to implement this in SQL without writing a procedure. There are a really nice additional contrib module from Jeff Davis, described here: http://thoughts.j-davis.com/2010/03/09/temporal-postgresql-roadmap/ Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Check set of date intervals
In response to A. Kretschmer :
please, suggest an idea how to implement this in SQL without writing a
procedure.
There are a really nice additional contrib module from Jeff Davis,
described here:
http://thoughts.j-davis.com/2010/03/09/temporal-postgresql-roadmap/
short example, with your data:
test=# select * from rel_a;
id | start_date | end_date | p
+++
1 | 01.01.2010 | 01.02.2010 | [01.01.2010 00:00:00 CET, 01.02.2010 00:00:00
CET)
2 | 03.02.2010 | 04.03.2010 | [03.02.2010 00:00:00 CET, 04.03.2010 00:00:00
CET)
(2 rows)
test=# select * from rel_a where contains(p, period('15.01.2010'::date,
'15.02.2010'::date));
id | start_date | end_date | p
++--+---
(0 rows)
test=# select * from rel_a where contains(p, period('15.01.2010'::date,
'25.01.2010'::date));
id | start_date | end_date | p
+++
1 | 01.01.2010 | 01.02.2010 | [01.01.2010 00:00:00 CET, 01.02.2010 00:00:00
CET)
(1 row)
The column p is created with:
test=# alter table rel_a add column p period;
and
test=# update rel_a set p = period(start_date, end_date);
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Check set of date intervals
In response to Anton Gavazuk : Hi Andreas, great thanks for the response, please, answer to the list, not to me, okay? unfortunately function just tests every row - it doesnt construct set of periods which would cover choosed period. That's hard to achieve ... maybe you have to create a table with contiguous periods. Somewhere i have found this code-snippet: test=*# select * from t1; a 1 2 3 4 6 7 8 10 11 12 13 (11 rows) test=*# WITH RECURSIVE RecCols (LeftBoundary, Value) AS (SELECT a, a FROM t1 WHERE (a - 1) NOT IN (SELECT a FROM t1) UNION ALL SELECT p.LeftBoundary, c.a FROM RecCols AS p, t1 AS c WHERE c.a = p.Value + 1) SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols GROUP BY LeftBoundary ORDER BY LeftBoundary; leftboundary | rightboundary --+--- 1 | 4 6 | 8 10 |13 (3 rows) Maybe that's the way you have to go ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question about slow queries...
Hi, I have a question about a query that starts out fine and over time slows to a halt - but only on a webhosted site. Locally it does fine. The query is a singleton select (no joins), hitting a table with about 5,000 records in it. Over time the query slows to a crawl and I have to dump and rebuild/repopulate to restore efficiency. Vacuum does nothing but a dump and reload fixes the problem and the query runs lightning fast again -- for a period of a week or so. As stated this only happens on the webhosted site (bluehost.com). Locally I have the same table with 118,000 records in it and it runs very fast. (Related: even a SELECT COUNT(*) on the the target table takes forever when the lethargy has set in). The only thing that might be a factor that I can see is that my ORDER BY uses an unindexed date - however, we have a sister site on the same webhost (diff server) and it does not experience slowdowns. With everything working properly (table rebuilt just yesterday) explain analyze produces this on the webhost: Sort (cost=121547.89..121558.43 rows=4214 width=4620) (actual time=386.172..386.224 rows=89 loops=1) Sort Key: visit_date - Seq Scan on client_service_note (cost=0.00..100334.19 rows=4214 width=4620) (actual time=0.019..385.917 rows=89 loops=1) Filter: (client_id = 385) Total runtime: 386.335 ms And locally: Sort (cost=535.82..536.04 rows=88 width=696) (actual time=2.140..2.156 rows=88 loops=1) Sort Key: visit_date Sort Method: quicksort Memory: 65kB - Seq Scan on client_service_note (cost=0.00..532.98 rows=88 width=696) (actual time=0.014..1.988 rows=88 loops=1) Filter: (client_id = 385) Total runtime: 2.295 ms My query is terse: SELECT * FROM client_service_note WHERE client_id = 385 ORDER BY visit_date DESC; Locally I'm running 8.4.2, the webhost is 8.1.18 Anyone have any thoughts on what I'm not seeing?? thanks, Tom -- Thomas Good, Senior Database Administrator Residential Services, Behavioral Health Services Bayley Seton Campus, SVCMCNY 75 Vanderbilt Avenue, Room 5-47 Staten Island, NY 10304 718.818.5528 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Do not understand SETOF RECORD - therefore can not use ODBC-link
Hi,
We are trying to make use of module ODBC-link. We follow the
instructions as read in README.TXT, including the given examples.
Connecting to an external Oracle database is successful: = oratest=#
select odbclink.connect('ONT_KIS', 'sbm_beheer', 'password');
= connect
= -
= 1
= (1 row)
Then we try to select some data:
= oratest=# select odbclink.query(1, 'SELECT * FROM mytable') as
result(id int4, t text, d decimal); = ERROR: syntax error at or near
( = LINE 1: ...bclink.query(1, 'SELECT * FROM mytable') as
result(id int4, ...
= ^
For some reason as result( generates the error. ODBC-link README.TXT
reads: = All three forms of the function odbclink.query() returns
SETOF RECORD, so = 1. it can return different query results = 2.
it must be properly casted to the expected result structure
The ODBC connection seems to work, when I select without using the as
result-part, I get another error message. = oratest=# select
odbclink.query(1, 'SELECT * FROM mytable'); = ERROR: function
returning record called in context that cannot accept type record
So it seems the functions returns data, but I do not know how to
display it. Since I do not understand how to handle SETOF RECORD I can
not solve the problem myself.
We're running PostgreSQL 8.4.3, ODBC-link 1.0.
Any help will be highly appreciated.
TIA,
Harrie Rodenbach
--
--
Met vriendelijke groet / with kind regards,
Harrie Rodenbach
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Fw: Re: [SQL] help
--- On Thu, 27/5/10, James Kitambara jameskitamb...@yahoo.co.uk wrote:
From: James Kitambara jameskitamb...@yahoo.co.uk
Subject: Re: [SQL] help
To: Nicholas I nicholas.domni...@gmail.com
Date: Thursday, 27 May, 2010, 14:50
Hello Mr. Nicholas,
You can try the following:
THIS IS WHAT I TRIED TO SOLVE YOUR PROBLEM, BUT IN ORACLE DBMS
(SORRY I DON'T HAVE POSTGRESQL INSTALL ON MY COMPUTER)
i GUESS YOU CAN TRY TO CHANGE THE SQL COMMANDS IN POSTGRESQL
create table numbers (
id number not null primary key,
description varchar2(100)
);
insert into numbers values (300, 'Three hundred (300)');
insert into numbers values (350, 'Three hundred fifty (350)');
insert into numbers values (6709, 'Six thousand seven hundred and nine (6709)');
select id, description, substr(description, instr(description, '(') +1,
instr(description, ')')-(instr(description, '(') +1)) extracted_character
from numbers;
This is the Results:
ID DESCRIPTION EXTRACTED_CHARACTERS
300 Three hundred 300
350 Tree hundred fifty 350
6709 Six thousand seven hundred and nine 6709
But if you run the following query you will get exactlly what:
select substr(description, instr(description, '(') +1, instr(description,
')')-(instr(description, '(') +1)) extracted_character
from numbers:
This is the Results:
EXTRACTED_CHARACTERS
-
300
350
6709
James Kitambara
B.Sc. With Computer Science and Statistics (Hons),
--- On Wed, 5/5/10, Nicholas I nicholas.domni...@gmail.com wrote:
From: Nicholas I nicholas.domni...@gmail.com
Subject: [SQL] help
To: pgsql-sql@postgresql.org
Date: Wednesday, 5 May, 2010, 4:05
Hi,
I have a table in which the data's are entered like,
Example:
One (1)
Two (2)
Three (3)
I want to extract the data which is only within the parentheses.
that is
1
2
3
Thank You
Nicholas I
Re: [SQL] Question about slow queries...
Good, Thomas tg...@svcmcny.org writes: I have a question about a query that starts out fine and over time slows to a halt - but only on a webhosted site. Locally it does fine. The query is a singleton select (no joins), hitting a table with about 5,000 records in it. Over time the query slows to a crawl and I have to dump and rebuild/repopulate to restore efficiency. Vacuum does nothing but a dump and reload fixes the problem and the query runs lightning fast again -- for a period of a week or so. If a dump and reload fixes it then you have table-bloat or index-bloat problems. A simple VACUUM won't fix that once it's happened. You need to VACUUM more often so that it doesn't get bloated in the first place. Locally I'm running 8.4.2, the webhost is 8.1.18 ... and the reason for the difference is probably that 8.4.x has a reasonably decent autovacuum facility, which 8.1.x does not. Suggest bugging your webhost provider to provide a less obsolete version of Postgres. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Do not understand SETOF RECORD - therefore can not use ODBC-link
Harrie Rodenbach marktenveil...@gmail.com writes: = oratest=# select odbclink.query(1, 'SELECT * FROM mytable') as result(id int4, t text, d decimal); = ERROR: syntax error at or near ( = LINE 1: ...bclink.query(1, 'SELECT * FROM mytable') as result(id int4, ... You need that to be select * from odbclink.query ... the as business is only allowed in FROM clause. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question about slow queries...
In response to Good, Thomas : Hi, I have a question about a query that starts out fine and over time slows to a halt - but only on a webhosted site. Locally it does fine. The query is a singleton select (no joins), hitting a table with about 5,000 records in it. Over time the query slows to a crawl and I have Okay, about 5000 records. to dump and rebuild/repopulate to restore efficiency. Vacuum does nothing but a dump and reload fixes the problem and the query runs lightning fast again -- for a period of a week or so. As stated this okay With everything working properly (table rebuilt just yesterday) explain analyze produces this on the webhost: Sort (cost=121547.89..121558.43 rows=4214 width=4620) (actual time=386.172..386.224 rows=89 loops=1) Sort Key: visit_date - Seq Scan on client_service_note (cost=0.00..100334.19 rows=4214 width=4620) (actual time=0.019..385.917 rows=89 loops=1) Filter: (client_id = 385) Total runtime: 386.335 ms Well, estimated rows= 4214, nealy the whole table.Because of this the planner choose a seq-scan. But - real only 89 rows with this condition. Again: estimated rows=4214, real rows=89. This is a big difference! I think, you have a lot of dead tuples in this table - many updates and/or deletes inserts. You should check your vacuum-strategy. Other reason, maybe, wrong statistics. You can try to increase statistics target. (alter table ... ALTER [ COLUMN ] column SET STATISTICS 100 or more) And locally: Sort (cost=535.82..536.04 rows=88 width=696) (actual time=2.140..2.156 rows=88 loops=1) Sort Key: visit_date Sort Method: quicksort Memory: 65kB - Seq Scan on client_service_note (cost=0.00..532.98 rows=88 width=696) (actual time=0.014..1.988 rows=88 loops=1) Filter: (client_id = 385) Total runtime: 2.295 ms Do you have an INDEX on client_id? My query is terse: SELECT * FROM client_service_note WHERE client_id = 385 ORDER BY visit_date DESC; Locally I'm running 8.4.2, the webhost is 8.1.18 There are many improvements between 8.1 and 8.4 ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rules and sequences
Quoth t...@sss.pgh.pa.us (Tom Lane): Ben Morrow b...@morrow.me.uk writes: I am trying to implement a fairly standard 'audit table' setup, but using rules instead of triggers (since it should be more efficient). Rules are sufficiently tricky that I would never, ever rely on them for auditing. Use a simple AFTER trigger instead. OK, thanks. Ben -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Extending Regular Expression bounds limit of 255.
In the docs
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE
it says that Regular Expression bounds {m,n} that m and n can be 0-255. Is
there a way to extend the upper limit. We are trying to be consistent
between Regular Expressionimplementation an the other implementations do not
have this limit.
Brent DeSpain
Schweitzer Engineering Laboratories, Inc.
Re: [SQL] Extending Regular Expression bounds limit of 255.
On 28 May 2010 07:33, Brent DeSpain bd.postg...@gmail.com wrote:
In the docs
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE
it says that Regular Expression bounds {m,n} that m and n can be 0-255. Is
there a way to extend the upper limit. We are trying to be consistent
between Regular Expressionimplementation an the other implementations do not
have this limit.
Interesting. The POSIX standard for REs dictates this limit; which
implementations
you're using don't adhere to it?
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extending Regular Expression bounds limit of 255.
No. Strangely enough they don't in enforce this limit.
Brent DeSpain
Schweitzer Engineering Laboratories, Inc.
On Thu, May 27, 2010 at 2:59 PM, Andrej andrej.gro...@gmail.com wrote:
On 28 May 2010 07:33, Brent DeSpain bd.postg...@gmail.com wrote:
In the docs
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE
it says that Regular Expression bounds {m,n} that m and n can be 0-255.
Is
there a way to extend the upper limit. We are trying to be consistent
between Regular Expressionimplementation an the other implementations do
not
have this limit.
Interesting. The POSIX standard for REs dictates this limit; which
implementations
you're using don't adhere to it?
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes
concise.
http://www.american.edu/econ/notes/htmlmail.htm
Re: [SQL] Extending Regular Expression bounds limit of 255.
It looks like most of our tools are using the Perl version of regular
expressions with an upper limit of a bound being 32766. Is there any way to
change this in PG? Or can I change from POSIX to Perl?
Brent DeSpain
Schweitzer Engineering Laboratories, Inc.
On Thu, May 27, 2010 at 4:44 PM, Brent DeSpain bd.postg...@gmail.comwrote:
No. Strangely enough they don't in enforce this limit.
Brent DeSpain
Schweitzer Engineering Laboratories, Inc.
On Thu, May 27, 2010 at 2:59 PM, Andrej andrej.gro...@gmail.com wrote:
On 28 May 2010 07:33, Brent DeSpain bd.postg...@gmail.com wrote:
In the docs
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE
it says that Regular Expression bounds {m,n} that m and n can be 0-255.
Is
there a way to extend the upper limit. We are trying to be consistent
between Regular Expressionimplementation an the other implementations do
not
have this limit.
Interesting. The POSIX standard for REs dictates this limit; which
implementations
you're using don't adhere to it?
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes
concise.
http://www.american.edu/econ/notes/htmlmail.htm
