You can prevent postgres from using the index on node by changing the reference
in the WHERE clause to an expression, like so:
SELECT * FROM public.stat WHERE node||'' = '1010101010' AND
("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date);
Perhaps this will lead the optimizer to
aravind chandu wrote:
> Hi,
>
> I am writing a stored procedure where the input to it is a
> file.I did not have any idea of how to give input as a file for a stored
> procedure.could you please help me.
I assume that by "a file" you mean a file in the filesystem, outside the
This is the index:
CREATE INDEX idx_stat_date_node_type
ON public.stat
USING btree
(date, node, "type");
explain SELECT * FROM public.stat WHERE node = '1010101010' AND
("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date)
Try changing the index order to node, date rather t
Have you run analyze on the table?
Yes
How selective is the condition node = '1010101010' and the date range. In
particular, do you have an idea what percentange of the table fits into
that date range?
There are around 1000 nodes and there is data for two years, so it must
be around 1/4
Hi
And you can use Londiste from Skytools. It's simpler and better suited for
your task.
http://pgfoundry.org/projects/skytools/
Asko
On Mon, Jul 28, 2008 at 7:44 PM, Garg, Manjit <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I'm stuck to an issue while cloning the pgsql database, can you please
>
Hello!
I have postgresql table that stores dome date range: it has range-begin
and range-end columns of type date. I want to enforce that 1) range-begin
would always before range-end and 2) there would be no range overlaps.
First problem could be solved with CHECK constraint. The only possibility
On Jul 29, 12:08 am, [EMAIL PROTECTED] (Alvaro Herrera)
wrote:
> Raymond C. Rodgers escribió:
>
> > The query in which I'm using array_accum() is building a
> > list of companies and the associated publishers for each. For example:
>
> > SELECT c.company_id, c.company_name, array_accum(p.publishe
Dmitry Teslenko wrote:
These solution work and enforce data integrity as needed. Sadly, database
interface I use (Trolltech Qt 3) can't track when trigger aborts
update or insert. Can I raise exception in trigger or something?
RAISE EXCEPTION 'Failed to update mytable: date range (%,%) overlaps
On Tue, Jul 29, 2008 at 02:25:21PM +0400, Dmitry Teslenko wrote:
> These solution work and enforce data integrity as needed. Sadly, database
> interface I use (Trolltech Qt 3) can't track when trigger aborts
> update or insert. Can I raise exception in trigger or something?
of course. syntax:
RAIS
On Tue, Jul 29, 2008 at 1:25 AM, Poul Møller Hansen <[EMAIL PROTECTED]> wrote:
>
>>> This is the index:
>>> CREATE INDEX idx_stat_date_node_type
>>> ON public.stat
>>> USING btree
>>> (date, node, "type");
>>>
>>>
>>> explain SELECT * FROM public.stat WHERE node = '1010101010' AND
>>> ("date" <=
Hello,
We came accross interesting behaviour of the update statement inside
an after insert or update trigger in PostgreSQL 8.3.1. Briefly, the
update run within one line trigger function takes always 1.5 sec
whereas exactly the same update hitting the same rows takes always 1ms
if run from the ps
We were updating a large set of data (executing a stored procedure
against a large set of data in one statement/transaction) while
autovacuum was running.
The resulting message looked like:
2008-07-28 21:18:08 CDT CONTEXT: automatic vacuum of table
"databasename._lms.sl_log_2" TopMemoryContext: 1
On Tue, 2008-07-29 at 19:25 +0400, Ivan Zolotukhin wrote:
> Any clues? Can anybody suggest how to debug this? Is it possible to
> get an explain of the query within the trigger?
I bet it's the difference between prepared/not prepared plans. The
trigger prepares the plan without considering the act
"John Cheng" <[EMAIL PROTECTED]> writes:
> We were updating a large set of data (executing a stored procedure
> against a large set of data in one statement/transaction) while
> autovacuum was running.
> The resulting message looked like:
> 2008-07-28 21:18:08 CDT CONTEXT: automatic vacuum of tab
"Ivan Zolotukhin" <[EMAIL PROTECTED]> writes:
> In pseudo code it looks like the following. There are 2 tables, empty
> abstract_table with 3 columns (id, col1, col2) and many tables (e.g.
> inherited_table1_with_data) that inherit abstract_table.
> Constraint_exclusion is set up on id column and w
Besides maintenance_work_mem, what else can be changed to improve index
creation?
I just did two tests. One with maintenance_work_mem=128MB and another with
maintenance_work_mem=1GB. Out of 3 single column index, 2 took slightly
longer with the higher value and a third took almost the same.
12GB
Greetings!
What is the best way to convert a time expressed as a timestamp with
time zone into a timestamp in the local time zone without knowing what
the local time zone is?
Thank you.
RobR
On Tuesday 29 July 2008 15:07:46 Rob Richardson wrote:
> Greetings!
>
> What is the best way to convert a time expressed as a timestamp with
> time zone into a timestamp in the local time zone without knowing what
> the local time zone is?
>
> Thank you.
>
> RobR
SELECT extract (epoch from your_tim
On Tuesday 29 July 2008, "Francisco Reyes" <[EMAIL PROTECTED]> wrote:
> Besides maintenance_work_mem, what else can be changed to improve index
> creation?
>
Depends where the bottleneck is.
1 CPU core 100% user/system = buy faster CPU cores.
System in I/O wait = upgrade disk channel.
--
Alan
On 2:53 pm 07/29/08 Alan Hodgson <[EMAIL PROTECTED]> wrote:
> --sar 2 30
Linux 2.6.9-42.ELsmp (trans03) 07/29/2008
12:58:09 PM CPU %user %nice %system %iowait %idle
12:58:11 PM all 12.44 0.00 0.06 0.00 87.50
12:58:13 PM all 12.44 0
On Tuesday 29 July 2008, "Francisco Reyes" <[EMAIL PROTECTED]> wrote:
> On 2:53 pm 07/29/08 Alan Hodgson <[EMAIL PROTECTED]> wrote:
> > --sar 2 30
>
> Linux 2.6.9-42.ELsmp (trans03) 07/29/2008
>
> 12:58:09 PM CPU %user %nice %system %iowait %idle
> 12:58:11 PM all 1
On 3:19 pm 07/29/08 Alan Hodgson <[EMAIL PROTECTED]> wrote:
> sure you only have 4 cores? Hyper-threading?
It seems Hyperthreading is on in that machine. Going to see if I can have
it turned off.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subsc
Thank you very much, sir.
After posting I realized that my question did not cover my problem. I
also need to calculate if a given time is within daylight savings time
or not.
The actual situation is this: I have a table that contains the time at
which an event occurred and an estimate of ho
Greetings again!
A few days ago, I visited a customer's site to talk about administering
our system, which is developed around a PostGres database. One of the
topics was how to back up the database. I described the process of
using PgAdmin to back up and restore a database, and I said a backup
s
On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote:
I was asked how to automate the procedure,
and I couldn't answer.
The options are manifold!
http://www.postgresql.org/docs/8.3/interactive/backup.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make chang
On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote:
Greetings again!
A few days ago, I visited a customer's site to talk about
administering
our system, which is developed around a PostGres database. One of the
topics was how to back up the database. I described the process of
using PgAdmi
On Tue, Jul 29, 2008 at 1:28 PM, Christophe <[EMAIL PROTECTED]> wrote:
>> I was asked how to automate the procedure,
>> and I couldn't answer.
>http://www.postgresql.org/docs/8.3/interactive/backup.html
Regarding the SQL backup option for small databases, I use an OS task
scheduler ( *ni
On Tue, 29 Jul 2008, Francisco Reyes wrote:
Besides maintenance_work_mem, what else can be changed to improve index
creation?
Very large values there haven't been all that helpful for me. I've gotten
better results in this area giving more of the unused memory to
shared_buffers (which you d
I have the following cursor that gives me an error near open.
Can someone please tell me what I am doing wrong??
Bob
DECLARE
procgraphic cursor for select process_id from p_id.p_id, processes_count
where p_id.p_id.p_id_id = processes_count.p_id_id;
begin
Open procgraphic ;
Fetch f
Bob Pawley wrote:
I have the following cursor that gives me an error near open.
Can someone please tell me what I am doing wrong??
DECLARE
procgraphic cursor for select process_id from p_id.p_id,
processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id;
begin
Open procgraphic
Richard Huxton <[EMAIL PROTECTED]> writes:
> Bob Pawley wrote:
>> DECLARE
>> procgraphic cursor for select process_id from p_id.p_id,
>> processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id;
>>
>> begin
>>
>> Open procgraphic ;
> There is no OPEN, you just FETCH
No, he does nee
Following is more complete. The balance of the trigger that is not shown
works when tested separately. I didn't include it because it is quite long.
Bob
DECLARE
process_total integer ;
process_id integer ;
procgraphic cursor for select process_id from p_id.p_id, processes_count
where p_id.p_
Slony-I replication is also a viable choice for backups.
On Tue, Jul 29, 2008 at 1:34 PM, Richard Broersma
<[EMAIL PROTECTED]> wrote:
> On Tue, Jul 29, 2008 at 1:28 PM, Christophe <[EMAIL PROTECTED]> wrote:
>
>>> I was asked how to automate the procedure,
>>> and I couldn't answer.
>>http:
"Bob Pawley" <[EMAIL PROTECTED]> writes:
> Following is more complete. The balance of the trigger that is not shown
> works when tested separately. I didn't include it because it is quite long.
Hmm, I still don't see anything that looks like a syntax error, but
I'll bet this is a name collision r
On Tue, Jul 29, 2008 at 5:42 PM, Bob Pawley <[EMAIL PROTECTED]> wrote:
> begin
Don't you need a ; after your begin...?
--
- David T. Wilson
[EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/m
-- Original message --
From: Tom Lane <[EMAIL PROTECTED]>
> "Bob Pawley" <[EMAIL PROTECTED]> writes:
> > Following is more complete. The balance of the trigger that is not shown
> > works when tested separately. I didn't include it because it is quite long.
>
> Hm
Thanks Tom
Qualifying the column was the solution.
Bob
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "Richard Huxton" <[EMAIL PROTECTED]>; "PostgreSQL"
Sent: Tuesday, July 29, 2008 2:51 PM
Subject: Re: [GENERAL] Cursor
"Bob P
Yes
Bob
- Original Message -
From: "Adrian Klaver" <[EMAIL PROTECTED]>
To: "Tom Lane" <[EMAIL PROTECTED]>; "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "Richard Huxton" <[EMAIL PROTECTED]>; "PostgreSQL"
Sent: Tuesday, July 29, 2008 3:03 PM
Subject: Re: [GENERAL] Cursor
-- Ori
"Bob Pawley" <[EMAIL PROTECTED]> writes:
> Qualifying the column was the solution.
Huh. What was the error message you got, exactly? Because it doesn't
seem like that should have led to a syntax error.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-gen
The syntax error was running the function while not in a trigger.
The trigger gave null as a return.
The error was "syntax error at or near Open".
Bob
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "Richard Huxton" <[EMAIL PROTECTE
On Jul 29, 2008, at 2:35 PM, Tom Lane wrote:
No, he does need an OPEN.
Really? I thought that PG didn't use OPEN:
"The PostgreSQL server does not implement an OPEN statement for
cursors; a cursor is considered to be open when it is declared."
http://www.postgresql.org/docs/8.3/int
On Tue, Jul 29, 2008 at 2:24 PM, Rob Richardson
<[EMAIL PROTECTED]> wrote:
> Greetings again!
>
> A few days ago, I visited a customer's site to talk about administering
> our system, which is developed around a PostGres database. One of the
> topics was how to back up the database. I described t
Christophe wrote:
On Jul 29, 2008, at 2:35 PM, Tom Lane wrote:
> No, he does need an OPEN.
Really? I thought that PG didn't use OPEN:
"The PostgreSQL server does not implement an OPEN statement for
cursors; a cursor is considered to be open when it is declared."
http://www.postgres
On Jul 29, 2008, at 4:51 PM, Klint Gore wrote:
It's different in PL/pgSQL.
Ah, yes, sorry, didn't catch that it was a PL/pgSQL function.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
44 matches
Mail list logo
