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 than
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
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
help,
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.publisher_name)
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 (%,%)
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:
RAISE
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 = '2008-06-30'::date 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
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:
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
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 table
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 works
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_time_field)
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
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 12.44
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
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
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
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
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
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 ( *nix
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
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
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
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 need an OPEN. The
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
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.
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 rather
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:
-- 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.
Hmm, I still
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
pgsql-general@postgresql.org
Sent: Tuesday, July 29, 2008 2:51 PM
Subject: Re: [GENERAL]
Yes
Bob
- Original Message -
From: Adrian Klaver [EMAIL PROTECTED]
To: Tom Lane [EMAIL PROTECTED]; Bob Pawley [EMAIL PROTECTED]
Cc: Richard Huxton [EMAIL PROTECTED]; PostgreSQL
pgsql-general@postgresql.org
Sent: Tuesday, July 29, 2008 3:03 PM
Subject: Re: [GENERAL] Cursor
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
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 PROTECTED];
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.
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 the
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.
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:
44 matches
Mail list logo
