From: Marko Tiikkaja on
On 8/4/10 2:39 PM +0300, Dean Rasheed wrote:
> Does this sound like a useful feature? Is this a sane approach to
> implementing it? If not, has anyone else given any thought as to how
> it might be implemented?

I didn't look at the patch, but so far, I've identified three problems
with the existing view system:

1) You can't re-evaluate the UPDATE expression like an UPDATE on a
table does. Consider for example UPDATE foo SET a=a+1; If the
tuples change before we get to them, we lose data because we
simply can't re-evaluate "a+1" in the trigger.

2) You can't set the number of affected rows.

3) You can't set the RETURNING results. You suggested that
RETURNING for DELETE would return the OLD value, but that seems
broken because that's not necessarily what was deleted. I didn't
understand what you suggestion for UPDATE was; how does PG know
that if the view doesn't have a primary key?

I think these are the main three problems that prevent people from
actually using views, and I think these should be focused on when adding
triggers on VIEWS. I would love to see the feature though.

Any thoughts?


Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Dean Rasheed on
On 4 August 2010 13:22, Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi> wrote:
> On 8/4/10 2:39 PM +0300, Dean Rasheed wrote:
>>
>> Does this sound like a useful feature? Is this a sane approach to
>> implementing it? If not, has anyone else given any thought as to how
>> it might be implemented?
>
> I didn't look at the patch, but so far, I've identified three problems with
> the existing view system:
>
> � �1) You can't re-evaluate the UPDATE expression like an UPDATE on a
> � � � table does. �Consider for example �UPDATE foo SET a=a+1; �If the
> � � � tuples change before we get to them, we lose data because we
> � � � simply can't re-evaluate "a+1" in the trigger.
>

Is this the same problem the writeable CTE patch ran into?
The way I've done this, the OLD values passed to the trigger all come
from a snapshot established at the start of the query, so you're
right, the trigger won't see values changed after the query started,
unless it re-queries for them. I don't see an easy way round that.


> � �2) You can't set the number of affected rows.
>

Yeah, the assumption is that the number of affected rows is the number
of rows in the view that matched the user's WHERE clause. You could
return fewer affected rows by having the trigger return NULL for some
of them, but you can't say that you've affected more than that. So
even if the trigger updates 10 rows in the base tables for a given row
in the view, that still only counts as 1 row affected in the view by
the original query.


> � �3) You can't set the RETURNING results. �You suggested that
> � � � RETURNING for DELETE would return the OLD value, but that seems
> � � � broken because that's not necessarily what was deleted.

Well that's what happens for a table. Alternatively the trigger could
modify OLD, and then have RETURNING return that, but that's not what
happens in a BEFORE DELETE trigger on a table.


> �I didn't
> � � � understand what you suggestion for UPDATE was; how does PG know
> � � � that if the view doesn't have a primary key?

For INSERT and UPDATE the trigger would compute and make the necessary
changes to the base tables, and then return the new contents of the
view's row in a modified copy of NEW, if necessary for RETURNING. This
might include re-computed derived values for example.

If the view doesn't have a PK, or any other way of uniquely
identifying rows then its probably hopeless. That's not a case that
this patch is targeted for.

Regards,
Dean


>
> I think these are the main three problems that prevent people from actually
> using views, and I think these should be focused on when adding triggers on
> VIEWS. �I would love to see the feature though.
>
> Any thoughts?
>
>
> Regards,
> Marko Tiikkaja
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Marko Tiikkaja on
On 8/4/10 4:31 PM +0300, Dean Rasheed wrote:
>> 1) You can't re-evaluate the UPDATE expression like an UPDATE on a
>> table does. Consider for example UPDATE foo SET a=a+1; If the
>> tuples change before we get to them, we lose data because we
>> simply can't re-evaluate "a+1" in the trigger.
>>
>
> Is this the same problem the writeable CTE patch ran into?

No, that was something different.

> Yeah, the assumption is that the number of affected rows is the number
> of rows in the view that matched the user's WHERE clause. You could
> return fewer affected rows by having the trigger return NULL for some
> of them, but you can't say that you've affected more than that. So
> even if the trigger updates 10 rows in the base tables for a given row
> in the view, that still only counts as 1 row affected in the view by
> the original query.

I think that's fine.

>> 3) You can't set the RETURNING results. You suggested that
>> RETURNING for DELETE would return the OLD value, but that seems
>> broken because that's not necessarily what was deleted.
>
> Well that's what happens for a table. Alternatively the trigger could
> modify OLD, and then have RETURNING return that, but that's not what
> happens in a BEFORE DELETE trigger on a table.

I'm not sure I understand. RETURNING in DELETE on a table fetches the
old value after it was DELETEd, so it really is what the tuple was
before the DLETE, not what is seen by the snapshot. In a BEFORE DELETE
trigger, the row is always locked so it can't change after the trigger
is fired.

> For INSERT and UPDATE the trigger would compute and make the necessary
> changes to the base tables, and then return the new contents of the
> view's row in a modified copy of NEW, if necessary for RETURNING. This
> might include re-computed derived values for example.

I see.


Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Dean Rasheed on
On 4 August 2010 14:43, Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi> wrote:
>>> � �3) You can't set the RETURNING results. �You suggested that
>>> � � � RETURNING for DELETE would return the OLD value, but that seems
>>> � � � broken because that's not necessarily what was deleted.
>>
>> Well that's what happens for a table. Alternatively the trigger could
>> modify OLD, and then have RETURNING return that, but that's not what
>> happens in a BEFORE DELETE trigger on a table.
>
> I'm not sure I understand. �RETURNING in DELETE on a table fetches the old
> value after it was DELETEd, so it really is what the tuple was before the
> DLETE, not what is seen by the snapshot. �In a BEFORE DELETE trigger, the
> row is always locked so it can't change after the trigger is fired.
>

Ah, I think I mis-understood. If I understand what you're saying
correctly, you're worried that the row might have been modified in the
same query, prior to being deleted, and you want RETURNING to return
the updated value, as it was when it was deleted.

So yes, you're right, that really is different from a table. I guess
it would have to be handled by the trigger returning a modified copy
of OLD for RETURNING to use.

Regards,
Dean

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Marko Tiikkaja on
On 8/4/10 5:03 PM +0300, Dean Rasheed wrote:
> On 4 August 2010 14:43, Marko Tiikkaja<marko.tiikkaja(a)cs.helsinki.fi> wrote:
>> I'm not sure I understand. RETURNING in DELETE on a table fetches the old
>> value after it was DELETEd, so it really is what the tuple was before the
>> DLETE, not what is seen by the snapshot. In a BEFORE DELETE trigger, the
>> row is always locked so it can't change after the trigger is fired.
>>
>
> Ah, I think I mis-understood. If I understand what you're saying
> correctly, you're worried that the row might have been modified in the
> same query, prior to being deleted, and you want RETURNING to return
> the updated value, as it was when it was deleted.

I'm mainly concerned about concurrently running transactions.


Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers