Cursor Error

"Bob Pawley" <rjpawley@shaw.ca> writes:

Could somebody translate this error message for me??
"cursor <unnamed portal 1> is not simply updateable scan of table "p_id"

You're trying to do an "UPDATE WHERE CURRENT OF cursor", right?
What it means is that the cursor definition is too complicated for
Postgres to figure out which row to update. Without seeing the
cursor definition it's hard to say more.

regards, tom lane

Right.

This is the cursor statement.

Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
processes_count
where p_id.p_id.p_id_id = processes_count.p_id_id;

If process_total = 1 Then

Fetch first from procgraphic into process_id;

Update p_id.p_id
set proc_graphic_position = '1'
where current of procgraphic;

I get the same error message when I define the cursor with the same select.

I am not sure how to make the query simpler and still have it access the
right row on fetch.

Bob

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Thursday, July 31, 2008 9:30 AM
Subject: Re: [GENERAL] Cursor Error

"Bob Pawley" <rjpawley@shaw.ca> writes:

Right.
This is the cursor statement.

Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
processes_count
where p_id.p_id.p_id_id = processes_count.p_id_id;

Sorry, we're not bright enough to handle WHERE CURRENT OF on a join
--- per the fine manual,

The cursor must be a simple (non-join, non-aggregate) query on
the UPDATE's target table.

I don't recall offhand whether there's some deep technical reason
for the restriction against joins, or we just didn't get around to
it. In any case, you'll need to change the cursor to return the
table's primary key and use that to target the UPDATE.

regards, tom lane

I have several tables that we have partitioned by physical location. This seems to give us the best overall performance when doing location specific queries. I have a few questions.

1. Is the planner/optimizer intelligent enough to know when we are not doing a query based on location? For example we might have a trailer that is used by multiple locations and we need to know all of the locations where that trailer has been used. Other queries might look for a specific work order that could only be in one of the partitions.

2. How are views handled with partitioned tables? I don't find anything in the documentation that tells me how views are handled. Depending on the view will it only use the partitioned table or will it use the master table?

Best Regards

Michael Gould

On 12:54 pm 07/31/08 "Mike Gould" <mgould@allcoast.net> wrote:

1. Is the planner/optimizer intelligent enough to know when we are
not doing a query based on location?

In short yes.
If the DB doesn't see the condition by which your tables are partitioned it
will search all the partitions.

2. How are views handled with partitioned tables?

Same as with regular queries. A view is just a conveniently "stored" query.
In other words, the plan for the view will be the same plan as the plan for
the query that you made the view from.

Is it allowed to declare a cursor in this manner??

Declare
procgraphic cursor for select p_id.p_id.process_id from p_id.p_id,
processes_count
where p_id.p_id.p_id_id = processes_count.p_id_id;

Bob

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Thursday, July 31, 2008 9:50 AM
Subject: Re: [GENERAL] Cursor Error

Bob Pawley wrote:

Is it allowed to declare a cursor in this manner??

Declare
procgraphic cursor for select p_id.p_id.process_id
from p_id.p_id, processes_count
where p_id.p_id.p_id_id = processes_count.p_id_id;

Using DECLARE instead of OPEN? Yes, but that won't somehow make a cursor
involving a join updatable. See:

http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

DECLARE and OPEN do not have exactly the same meaning, as explained by
the above documentation. To use a cursor defined with DECLARE you must
use OPEN - see section 38.7.2.3 ("Opening a Bound Cursor") of the
documentation.

--
Craig Ringer