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
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 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
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
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
"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
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
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
-- 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
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
"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
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:
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_
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
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
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
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
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 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 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
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
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
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
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 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:
> 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 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
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
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
"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
"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
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
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
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
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" <=
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
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 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
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
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
>
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
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
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
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
44 matches
Mail list logo
