Hello all,

PostgreSQL *still* has a bug where PQcmdStatus() won't return the
number of rows updated. But that is essential for applications, since
without it of course we don't know if the updates/delete/insert
commands succeded. Even worst, on interfaces like Delphi/dbExpress the
program will return an error message and rollback transaction thinking
nothing have been updated. In other words, unusable.

This render views useless (I either use view with rules and don't get
my program working) and won't allow me to proper use security settings
on PostgreSQL...

This is a *major* issue in my opinion that appeared on a May thread
but I can't see it done on version 7.2.2. Even worst, I can't see
nothing on the TODO file.

Will this fix finally  appear on 7.3 ? Any ways to work around this ?
How can I know at least if *something* succeeded, or how many rows
(the proper behavior)?

Thank you very much.

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Fri, 6 Sep 2002 15:10:15 -0300
Steve Howe <howe@carcass.dhs.org>
Rule updates and PQcmdstatus() issue
Steve Howe wrote:
> Hello all,
> 
> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
> number of rows updated. But that is essential for applications, since
> without it of course we don't know if the updates/delete/insert
> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
> program will return an error message and rollback transaction thinking
> nothing have been updated. In other words, unusable.
> 
> This render views useless (I either use view with rules and don't get
> my program working) and won't allow me to proper use security settings
> on PostgreSQL...
> 
> This is a *major* issue in my opinion that appeared on a May thread
> but I can't see it done on version 7.2.2. Even worst, I can't see
> nothing on the TODO file.
> 
> Will this fix finally  appear on 7.3 ? Any ways to work around this ?
> How can I know at least if *something* succeeded, or how many rows
> (the proper behavior)?

I see on TODO:

	* Return proper effected tuple count from complex commands [return]

and that "return" link has a discussion of possible fixes.
Unfortunately, no fix was agreed upon so there is no fix in 7.3.

And, on top of that, I can't even think of a workaround.  At best,
perhaps someone can write you a patch to fix this.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 6 Sep 2002 14:22:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rule updates and PQcmdstatus() issue
Hello Bruce,

Friday, September 6, 2002, 3:22:13 PM, you wrote:

BM> Steve Howe wrote:
>> Hello all,
>> 
>> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
>> number of rows updated. But that is essential for applications, since
>> without it of course we don't know if the updates/delete/insert
>> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
>> program will return an error message and rollback transaction thinking
>> nothing have been updated. In other words, unusable.
>> 
>> This render views useless (I either use view with rules and don't get
>> my program working) and won't allow me to proper use security settings
>> on PostgreSQL...
>> 
>> This is a *major* issue in my opinion that appeared on a May thread
>> but I can't see it done on version 7.2.2. Even worst, I can't see
>> nothing on the TODO file.
>> 
>> Will this fix finally  appear on 7.3 ? Any ways to work around this ?
>> How can I know at least if *something* succeeded, or how many rows
>> (the proper behavior)?

BM> I see on TODO:

BM>         * Return proper effected tuple count from complex commands [return]
Sorry, I missed it because I check the v7.2.2 TODO.

BM> and that "return" link has a discussion of possible fixes.
BM> Unfortunately, no fix was agreed upon so there is no fix in 7.3.
So all the databases that uses rules will still be broken ? I don't
believe you guys are so unconcerned about this...

BM> And, on top of that, I can't even think of a workaround.  At best,
BM> perhaps someone can write you a patch to fix this.
Let's hope so... and I disagree about the 'write for me' point; it's
for *everyone using rules*. They are useless, currently... and it's
broken for months and nothing agreed until know... I just can't
believe in it.
What do you do when you have to update a view ?

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Fri, 6 Sep 2002 19:38:54 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue

I am not any happier about it than you are.  Your report is good because
it is the first case where returning the wrong value actually breaks
software.  You may be able to justify adding a fix during beta by saying
it is a bug fix.

Of course, someone is going to have to generate a patch and champion the
cause.  This stuff doesn't happen by magic.

---------------------------------------------------------------------------

Steve Howe wrote:
> Hello Bruce,
> 
> Friday, September 6, 2002, 3:22:13 PM, you wrote:
> 
> BM> Steve Howe wrote:
> >> Hello all,
> >> 
> >> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
> >> number of rows updated. But that is essential for applications, since
> >> without it of course we don't know if the updates/delete/insert
> >> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
> >> program will return an error message and rollback transaction thinking
> >> nothing have been updated. In other words, unusable.
> >> 
> >> This render views useless (I either use view with rules and don't get
> >> my program working) and won't allow me to proper use security settings
> >> on PostgreSQL...
> >> 
> >> This is a *major* issue in my opinion that appeared on a May thread
> >> but I can't see it done on version 7.2.2. Even worst, I can't see
> >> nothing on the TODO file.
> >> 
> >> Will this fix finally  appear on 7.3 ? Any ways to work around this ?
> >> How can I know at least if *something* succeeded, or how many rows
> >> (the proper behavior)?
> 
> BM> I see on TODO:
> 
> BM>         * Return proper effected tuple count from complex commands [return]
> Sorry, I missed it because I check the v7.2.2 TODO.
> 
> BM> and that "return" link has a discussion of possible fixes.
> BM> Unfortunately, no fix was agreed upon so there is no fix in 7.3.
> So all the databases that uses rules will still be broken ? I don't
> believe you guys are so unconcerned about this...
> 
> BM> And, on top of that, I can't even think of a workaround.  At best,
> BM> perhaps someone can write you a patch to fix this.
> Let's hope so... and I disagree about the 'write for me' point; it's
> for *everyone using rules*. They are useless, currently... and it's
> broken for months and nothing agreed until know... I just can't
> believe in it.
> What do you do when you have to update a view ?
> 
> ------------- 
> Best regards,
>  Steve Howe                           mailto:howe@carcass.dhs.org
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 6 Sep 2002 20:52:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rule updates and PQcmdstatus() issue
Hello Bruce,

Friday, September 6, 2002, 9:52:18 PM, you wrote:


BM> I am not any happier about it than you are.  Your report is good because
BM> it is the first case where returning the wrong value actually breaks
BM> software.  You may be able to justify adding a fix during beta by saying
BM> it is a bug fix.
Actually I think it must have happened with someone else, but they
must have quit using rules or something...
Actually I can't ensure security in the system without rules.

BM> Of course, someone is going to have to generate a patch and champion the
BM> cause.  This stuff doesn't happen by magic.
I understand your point. I just was hoping to see more concern about
the issue by the developers... but that's been broken for months.

Unhappily I can't do it myself because it would take weeks to get
familiar with the inners of PostgreSQL...

Let's hope someone realize how serious is this and make a fix.

Thanks again...
------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Fri, 6 Sep 2002 22:30:12 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue
Steve Howe wrote:
> Hello Bruce,
> 
> Friday, September 6, 2002, 9:52:18 PM, you wrote:
> 
> 
> BM> I am not any happier about it than you are.  Your report is good because
> BM> it is the first case where returning the wrong value actually breaks
> BM> software.  You may be able to justify adding a fix during beta by saying
> BM> it is a bug fix.
> Actually I think it must have happened with someone else, but they
> must have quit using rules or something...
> Actually I can't ensure security in the system without rules.
> 
> BM> Of course, someone is going to have to generate a patch and champion the
> BM> cause.  This stuff doesn't happen by magic.
> I understand your point. I just was hoping to see more concern about
> the issue by the developers... but that's been broken for months.
> 
> Unhappily I can't do it myself because it would take weeks to get
> familiar with the inners of PostgreSQL...

Well, there was a big discussion, and I did bring up the issue in early
August to see if I could get a resolution to it and was told no
conclusion could be made.

I suggest you read the TODO detail on the item and make a proposal on
how it _should_ work and if you can get agreement from everyone, you may
be able to nag someone into doing a patch.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 6 Sep 2002 21:58:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rule updates and PQcmdstatus() issue
Hello Bruce,

Friday, September 6, 2002, 10:58:13 PM, you wrote:

BM> Well, there was a big discussion, and I did bring up the issue in early
BM> August to see if I could get a resolution to it and was told no
BM> conclusion could be made.

BM> I suggest you read the TODO detail on the item and make a proposal on
BM> how it _should_ work and if you can get agreement from everyone, you may
BM> be able to nag someone into doing a patch.
I think it should return the number of rows modified in the context of
the view, and not exactly that of each of the tables affected. And
this would not work well with PQcmdStatus() because it returns a
single integer entry only.

This was working on some previous build, wasn't it ? What was the
previous behavior ? Shouldn't the patch follow that way ?

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Fri, 6 Sep 2002 23:52:47 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue
Steve Howe <howe@carcass.dhs.org> writes:
> BM> I suggest you read the TODO detail on the item and make a proposal on
> BM> how it _should_ work and if you can get agreement from everyone, you may
> BM> be able to nag someone into doing a patch.

> I think it should return the number of rows modified in the context of
> the view, and not exactly that of each of the tables affected.

That's so vague as to be useless.  What is "in the context of the view"?
How does that notion help us resolve the uncertainties discussed in the
TODO thread?

> This was working on some previous build, wasn't it ? What was the
> previous behavior ? Shouldn't the patch follow that way ?

The old behavior was quite broken too, just not in a way that affected
you.  We will not be reverting the change that fatally broke it (namely
altering the order of RULE applications for INSERTs) and so "go back
to the old code" isn't a workable answer at all.

I don't think fixing the code is the hard part; agreeing on what the
behavior should be in complex cases is the hard part.

			regards, tom lane

Sat, 07 Sep 2002 16:42:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rule updates and PQcmdstatus() issue 
Tom Lane wrote:
> Steve Howe <howe@carcass.dhs.org> writes:
> > BM> I suggest you read the TODO detail on the item and make a proposal on
> > BM> how it _should_ work and if you can get agreement from everyone, you may
> > BM> be able to nag someone into doing a patch.
> 
> > I think it should return the number of rows modified in the context of
> > the view, and not exactly that of each of the tables affected.
> 
> That's so vague as to be useless.  What is "in the context of the view"?
> How does that notion help us resolve the uncertainties discussed in the
> TODO thread?
> 
> > This was working on some previous build, wasn't it ? What was the
> > previous behavior ? Shouldn't the patch follow that way ?
> 
> The old behavior was quite broken too, just not in a way that affected
> you.  We will not be reverting the change that fatally broke it (namely
> altering the order of RULE applications for INSERTs) and so "go back
> to the old code" isn't a workable answer at all.
> 
> I don't think fixing the code is the hard part; agreeing on what the
> behavior should be in complex cases is the hard part.

Yes, Steve, if you want a fix, you better read the TODO detail and come
up with a proposal and try to sell it to the group.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 7 Sep 2002 17:22:40 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rule updates and PQcmdstatus() issue
Hello Tom,

Saturday, September 7, 2002, 5:42:33 PM, you wrote:

TL> Steve Howe <howe@carcass.dhs.org> writes:
>> BM> I suggest you read the TODO detail on the item and make a proposal on
>> BM> how it _should_ work and if you can get agreement from everyone, you may
>> BM> be able to nag someone into doing a patch.

>> I think it should return the number of rows modified in the context of
>> the view, and not exactly that of each of the tables affected.

TL> That's so vague as to be useless.  What is "in the context of the view"?
TL> How does that notion help us resolve the uncertainties discussed in the
TL> TODO thread?
I just mean that PQcmdStatus() should not return a value for each
changed table but how many rows "viewable by the view" it could
change.
Again, I'm not that aware of the inners of PostgreSQL to feel
comfortable to do a better suggestion.

>> This was working on some previous build, wasn't it ? What was the
>> previous behavior ? Shouldn't the patch follow that way ?

TL> The old behavior was quite broken too, just not in a way that affected
TL> you.  We will not be reverting the change that fatally broke it (namely
TL> altering the order of RULE applications for INSERTs) and so "go back
TL> to the old code" isn't a workable answer at all.
I didn't mean to revert the code but to make it work like the older
version did. I was unaware that it was broken too, but the removal now
broke the whole views/rules so I wonder what could be worst...
Also, it should have affected thousands of users, not just me. Unless
nobody uses views...

TL> I don't think fixing the code is the hard part; agreeing on what the
TL> behavior should be in complex cases is the hard part.
I understand your point and I'll try to give a proper solution but
since I'm not familiar with the PostgreSQL inners, I wonder how good
could it be...

Thanks :)

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Sat, 7 Sep 2002 18:28:09 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue
Steve Howe wrote:
> 
> Hello all,
> 
> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
> number of rows updated. But that is essential for applications, since
> without it of course we don't know if the updates/delete/insert
> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
> program will return an error message and rollback transaction thinking
> nothing have been updated. In other words, unusable.
> 
> This render views useless (I either use view with rules and don't get
> my program working) and won't allow me to proper use security settings
> on PostgreSQL...
> 
> This is a *major* issue in my opinion that appeared on a May thread
> but I can't see it done on version 7.2.2. Even worst, I can't see
> nothing on the TODO file.
> 
> Will this fix finally  appear on 7.3 ? Any ways to work around this ?
> How can I know at least if *something* succeeded, or how many rows
> (the proper behavior)?

And of course, in the case you insert into a real table you expect if a
trigger procedure suppressed your original INSERT, but fired a cascade
of other triggers by doing a mass UPDATE somewhere else instead, that
all these caused UPDATEs and whatnot's are summed up and returned
instead, right? Or what is proper behavior here?

So please, "proper behavior" is not allways what your favorite tool
expects. And just because you cannot "fix" your tool doesn't make that
behavior any more "proper".


Jan

> 
> Thank you very much.
> 
> -------------
> Best regards,
>  Steve Howe                           mailto:howe@carcass.dhs.org
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Mon, 09 Sep 2002 10:15:47 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Rule updates and PQcmdstatus() issue
Steve Howe wrote:
> 
> Hello Bruce,
> 
> Friday, September 6, 2002, 9:52:18 PM, you wrote:
> 
> BM> I am not any happier about it than you are.  Your report is good because
> BM> it is the first case where returning the wrong value actually breaks
> BM> software.  You may be able to justify adding a fix during beta by saying
> BM> it is a bug fix.
> Actually I think it must have happened with someone else, but they
> must have quit using rules or something...
> Actually I can't ensure security in the system without rules.
> 
> BM> Of course, someone is going to have to generate a patch and champion the
> BM> cause.  This stuff doesn't happen by magic.
> I understand your point. I just was hoping to see more concern about
> the issue by the developers... but that's been broken for months.
> 
> Unhappily I can't do it myself because it would take weeks to get
> familiar with the inners of PostgreSQL...
> 
> Let's hope someone realize how serious is this and make a fix.

Seems you at least realized how serious it is. Even if you cannot code
the "proper" solution, could you please make a complete table of all
possible situations and the expected returns? With complete I mean
including all combinations of rules, triggers, deferred constraints and
the like. Or do you at least see now where in the discussion we got
stuck?

It doesn't help to cry for a quick hack that fixes your particular
problem. That only leads to the situation that someday we have a final
fix that changes the behavior for your case again and then you cry again
and ask us not to break backwards compatibility.


Thanks for your patience and understanding,
Jan

> 
> Thanks again...
> -------------
> Best regards,
>  Steve Howe                           mailto:howe@carcass.dhs.org
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Mon, 09 Sep 2002 10:26:20 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Rule updates and PQcmdstatus() issue
Steve Howe wrote:
> 
> Hello Bruce,
> 
> Friday, September 6, 2002, 10:58:13 PM, you wrote:
> 
> BM> Well, there was a big discussion, and I did bring up the issue in early
> BM> August to see if I could get a resolution to it and was told no
> BM> conclusion could be made.
> 
> BM> I suggest you read the TODO detail on the item and make a proposal on
> BM> how it _should_ work and if you can get agreement from everyone, you may
> BM> be able to nag someone into doing a patch.
> I think it should return the number of rows modified in the context of
> the view, and not exactly that of each of the tables affected. And
> this would not work well with PQcmdStatus() because it returns a
> single integer entry only.
> 
> This was working on some previous build, wasn't it ? What was the
> previous behavior ? Shouldn't the patch follow that way ?

In previous versions rules even fired in different orders. We cannot get
back to that, because it was the reason for total failure of rules at
all. So no, the patch should follow that way.

You say that the return should be the rows modified in the context of
the view. Er ... what is that? You mean only INSERTS, UPDATES and
DELETES made by rule actions directly to any table referenced by the
view itself count, not if a modification to another third table or view
triggers back a modification to one of these base tables in return ...
would that be through a rule or a trigger?

What about a view over views, that has rules that in turn get rewritten
by the rewrite rules of the views it consists of? What is that views
context in detail?


Jan

> 
> -------------
> Best regards,
>  Steve Howe                           mailto:howe@carcass.dhs.org
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Mon, 09 Sep 2002 10:36:56 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Rule updates and PQcmdstatus() issue
Hello Jan,

Monday, September 9, 2002, 11:26:20 AM, you wrote:

JW> Steve Howe wrote:
>> 
>> Hello Bruce,
>> 
>> Friday, September 6, 2002, 9:52:18 PM, you wrote:
>> 
>> BM> I am not any happier about it than you are.  Your report is good because
>> BM> it is the first case where returning the wrong value actually breaks
>> BM> software.  You may be able to justify adding a fix during beta by saying
>> BM> it is a bug fix.
>> Actually I think it must have happened with someone else, but they
>> must have quit using rules or something...
>> Actually I can't ensure security in the system without rules.
>> 
>> BM> Of course, someone is going to have to generate a patch and champion the
>> BM> cause.  This stuff doesn't happen by magic.
>> I understand your point. I just was hoping to see more concern about
>> the issue by the developers... but that's been broken for months.
>> 
>> Unhappily I can't do it myself because it would take weeks to get
>> familiar with the inners of PostgreSQL...
>> 
>> Let's hope someone realize how serious is this and make a fix.

JW> Seems you at least realized how serious it is. Even if you cannot code
"At least" ?... What do you mean by that ?

JW> the "proper" solution, could you please make a complete table of all
JW> possible situations and the expected returns? With complete I mean
JW> including all combinations of rules, triggers, deferred constraints and
JW> the like. Or do you at least see now where in the discussion we got
JW> stuck?
I had seen and the proposal was posted two days ago.

JW> It doesn't help to cry for a quick hack that fixes your particular
JW> problem. That only leads to the situation that someday we have a final
JW> fix that changes the behavior for your case again and then you cry again
JW> and ask us not to break backwards compatibility.
See, I'm not crying. I'm just another user who needs something
working. The whole problem was that the PostgreSQL knew the problem
existed, had a brief discussion on the subject, and couldn't reach an
agreement. That's ok for me, I understand... but releasing versions
known to be broken is something I can't understand.

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Mon, 9 Sep 2002 14:06:39 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue
Hello Jan,

Monday, September 9, 2002, 11:26:20 AM, you wrote:

JW> Seems you at least realized how serious it is. Even if you cannot code
JW> the "proper" solution, could you please make a complete table of all
JW> possible situations and the expected returns? With complete I mean
JW> including all combinations of rules, triggers, deferred constraints and
JW> the like. Or do you at least see now where in the discussion we got
JW> stuck?
By the way, I don't think triggers and constraints are in focus here,
just as rules other then "DO INSTEAD".
These should be transparent to the user.
I suggest you to read the proposal posted to get aware of the
discussion.

Thanks.

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Mon, 9 Sep 2002 14:14:49 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue
Hello Jan,

Monday, September 9, 2002, 11:15:47 AM, you wrote:

JW> Steve Howe wrote:
>> 
>> Hello all,
>> 
>> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
>> number of rows updated. But that is essential for applications, since
>> without it of course we don't know if the updates/delete/insert
>> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
>> program will return an error message and rollback transaction thinking
>> nothing have been updated. In other words, unusable.
>> 
>> This render views useless (I either use view with rules and don't get
>> my program working) and won't allow me to proper use security settings
>> on PostgreSQL...
>> 
>> This is a *major* issue in my opinion that appeared on a May thread
>> but I can't see it done on version 7.2.2. Even worst, I can't see
>> nothing on the TODO file.
>> 
>> Will this fix finally  appear on 7.3 ? Any ways to work around this ?
>> How can I know at least if *something* succeeded, or how many rows
>> (the proper behavior)?

JW> And of course, in the case you insert into a real table you expect if a
JW> trigger procedure suppressed your original INSERT, but fired a cascade
JW> of other triggers by doing a mass UPDATE somewhere else instead, that
JW> all these caused UPDATEs and whatnot's are summed up and returned
JW> instead, right? Or what is proper behavior here?
What is documented, and what is expected: PQcmdStatus(),
PQcmdTuples()and PQoidValue() returning the information they should be.

JW> So please, "proper behavior" is not allways what your favorite tool
JW> expects. And just because you cannot "fix" your tool doesn't make that
JW> behavior any more "proper".
Do you have any word more appropriate ?

And just so that you know, I can't "fix" my tool because I have other
job to do (and a lot of that and that job uses PostgreSQL), and
unhappily I couldn't join the development team and thus I'm not aware
of how it works internally. The reason isn't that I just don't have
intellectual capacity.

And it looks like *you* overhauled the query rewrite rule system, so
what we are talking is something that must have passed through you. So
instead of offending me, your "proper" behavior would be try to help
and suggest a solution for the problem, as other developers are doing.

Thanks again.

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Mon, 9 Sep 2002 14:43:46 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue
> existed, had a brief discussion on the subject, and couldn't reach an
> agreement. That's ok for me, I understand... but releasing versions
> known to be broken is something I can't understand.
 -9' the postmaster

If we didn't do that, then Postgresql never would have been released in
the first place, nor any date between then and now.

There has been, and currently is a ton of known broken, wonky, or
incomplete stuff -- but it's felt that the current version has a lot
more to offer than the previous version, so it's being released.

This works for *all* software.  If you never release, nothing gets
better.


I suspect it'll be several more major releases before we begin to
consider it approaching completely functional.

-- 
  Rod Taylor


09 Sep 2002 13:55:18 -0400
Rod Taylor <rbt@rbt.ca>
Re: Rule updates and PQcmdstatus() issue

On Mon, 9 Sep 2002, Steve Howe wrote:

> JW> Steve Howe wrote:
> >>
> >> Hello all,
> >>
> >> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
> >> number of rows updated. But that is essential for applications, since
> >> without it of course we don't know if the updates/delete/insert
> >> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
> >> program will return an error message and rollback transaction thinking
> >> nothing have been updated. In other words, unusable.

As a note, I assume you realize that it returning any number doesn't
guarantee that the command succeeded if you assume succeeding means doing
what the statement sent would appear to do. ;) Although I think
we need to change the current behavior, we are turning a false "failure"
into a potentially false "success" (I did an update, it said two rows were
changed but there's no visible data change in the entire system?)
Fortunately, the likely bad effects from the false "success" are probably
only going to happen in somewhat degenerate cases.

I quote "failure" and "success" because there's already a notion of
success and failure which is raising an exception condition or not (AFAICT
0 rows is a completion condition - the statement succeeded but nothing was
modified). As such, using the count to determine success of the statement
is wrong for an interface, but it may be meaningful for applications
attempting to apply some sort of business logic.


Mon, 9 Sep 2002 11:15:29 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Rule updates and PQcmdstatus() issue
Steve Howe wrote:
> 
> Hello Jan,
> 
> Monday, September 9, 2002, 11:15:47 AM, you wrote:
> 
> JW> So please, "proper behavior" is not allways what your favorite tool
> JW> expects. And just because you cannot "fix" your tool doesn't make that
> JW> behavior any more "proper".
> Do you have any word more appropriate ?
> [...]
> And it looks like *you* overhauled the query rewrite rule system, so
> what we are talking is something that must have passed through you. So
> instead of offending me, your "proper" behavior would be try to help
> and suggest a solution for the problem, as other developers are doing.

See, and exactly here lies the problem. Indeed, I spent about 3 months
of my spare time back in 95 or so to fix it, after I spent many more
months over years to get familiar with the internals.

Now, instead of even trying to spend some serious amount of time
yourself, you give some vague hints about the functionality that might
make your problems disappear, name that a proposal and expect someone
else to do what you need for free. This is not exactly how open source
works.

We should surely keep this on a much more technical level and avoid any
personal offendings. To do so, please explain to me why you think that
triggers and constraints are out of focus here? What is the difference
between a trigger, a rule and an instead rule from a business process
oriented point of view? I think there is none at all. They are just
different techniques to do one and the same, implement business logic in
the database system.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Mon, 09 Sep 2002 15:56:04 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Rule updates and PQcmdstatus() issue
Hello Jan,

Monday, September 9, 2002, 4:56:04 PM, you wrote:

JW> Steve Howe wrote:
>> 
>> Hello Jan,
>> 
>> Monday, September 9, 2002, 11:15:47 AM, you wrote:
>> 
>> JW> So please, "proper behavior" is not allways what your favorite tool
>> JW> expects. And just because you cannot "fix" your tool doesn't make that
>> JW> behavior any more "proper".
>> Do you have any word more appropriate ?
>> [...]
>> And it looks like *you* overhauled the query rewrite rule system, so
>> what we are talking is something that must have passed through you. So
>> instead of offending me, your "proper" behavior would be try to help
>> and suggest a solution for the problem, as other developers are doing.

JW> See, and exactly here lies the problem. Indeed, I spent about 3 months
JW> of my spare time back in 95 or so to fix it, after I spent many more
JW> months over years to get familiar with the internals.

JW> Now, instead of even trying to spend some serious amount of time
JW> yourself, you give some vague hints about the functionality that might
JW> make your problems disappear, name that a proposal and expect someone
JW> else to do what you need for free. This is not exactly how open source
JW> works.
As I told you, this would demand weeks and I just don't have time to
do it. Other developers offered to make a fix and asked me to do that
proposal. And so I did.
It's sad that just you don't seem to be trying to help in
any way. Other developers had considered the proposal and are actually
voting and giving constructive ideas on the subject.

JW> We should surely keep this on a much more technical level and avoid any
JW> personal offendings. To do so, please explain to me why you think that
JW> triggers and constraints are out of focus here? What is the difference
JW> between a trigger, a rule and an instead rule from a business process
JW> oriented point of view? I think there is none at all. They are just
JW> different techniques to do one and the same, implement business logic in
JW> the database system.
Because the affected commands are supposed to give you back
information on what your INSERT/UPDATE/DELETE commands, not what is
making behind the scenes.

And it seems that other people in the thread agree with me, please
read thread.

Since you are probably very familiar with the rules system, why don't
you vote on a proposal too, or just suggest yours. Your opinion is
very important. I'm not saying I'm the truth owner; I'm just another
developer who needs a feature working again.

Thank you.

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Mon, 9 Sep 2002 18:05:27 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue
Jan Wieck wrote:
> We should surely keep this on a much more technical level and avoid any
> personal offendings. To do so, please explain to me why you think that
> triggers and constraints are out of focus here? What is the difference
> between a trigger, a rule and an instead rule from a business process
> oriented point of view? I think there is none at all. They are just
> different techniques to do one and the same, implement business logic in
> the database system.

All the problems here are coming from INSTEAD rules.  We don't have
INSTEAD triggers or contraints.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 9 Sep 2002 22:11:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rule updates and PQcmdstatus() issue
Steve Howe wrote:
> Because the affected commands are supposed to give you back
> information on what your INSERT/UPDATE/DELETE commands, not what is
> making behind the scenes.
> 
> And it seems that other people in the thread agree with me, please
> read thread.
> 
> Since you are probably very familiar with the rules system, why don't
> you vote on a proposal too, or just suggest yours. Your opinion is
> very important. I'm not saying I'm the truth owner; I'm just another
> developer who needs a feature working again.

Jan actually did vote in the first round which appears in TODO.detail. 
He voted that if the INSTEAD rule had only _one_ statement, return that,
if not, return nothing.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 9 Sep 2002 22:13:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rule updates and PQcmdstatus() issue
On Mon, 9 Sep 2002, Bruce Momjian wrote:

> Jan Wieck wrote:
> > We should surely keep this on a much more technical level and avoid any
> > personal offendings. To do so, please explain to me why you think that
> > triggers and constraints are out of focus here? What is the difference
> > between a trigger, a rule and an instead rule from a business process
> > oriented point of view? I think there is none at all. They are just
> > different techniques to do one and the same, implement business logic in
> > the database system.
>
> All the problems here are coming from INSTEAD rules.  We don't have
> INSTEAD triggers or contraints.

Sure we do, well sort of. :)
Make a before trigger that does a different statement and returns NULL
to abort the original action on that row.



Mon, 9 Sep 2002 19:18:07 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Rule updates and PQcmdstatus() issue
On Mon, 2002-09-09 at 22:11, Bruce Momjian wrote:
> Jan Wieck wrote:
> > We should surely keep this on a much more technical level and avoid any
> > personal offendings. To do so, please explain to me why you think that
> > triggers and constraints are out of focus here? What is the difference
> > between a trigger, a rule and an instead rule from a business process
> > oriented point of view? I think there is none at all. They are just
> > different techniques to do one and the same, implement business logic in
> > the database system.
> 
> All the problems here are coming from INSTEAD rules.  We don't have
> INSTEAD triggers or contraints.

Well.. Triggers could be exclusively INSTEAD.  A trigger could easily
write a few things to a number of other tables, and return NULL in a
BEFORE trigger which would prevent execution of the requested command.



-- 
  Rod Taylor


09 Sep 2002 22:20:31 -0400
Rod Taylor <rbt@rbt.ca>
Re: Rule updates and PQcmdstatus() issue
Hello Bruce,

Monday, September 9, 2002, 11:13:20 PM, you wrote:

BM> Steve Howe wrote:
>> Because the affected commands are supposed to give you back
>> information on what your INSERT/UPDATE/DELETE commands, not what is
>> making behind the scenes.
>> 
>> And it seems that other people in the thread agree with me, please
>> read thread.
>> 
>> Since you are probably very familiar with the rules system, why don't
>> you vote on a proposal too, or just suggest yours. Your opinion is
>> very important. I'm not saying I'm the truth owner; I'm just another
>> developer who needs a feature working again.

BM> Jan actually did vote in the first round which appears in TODO.detail. 
BM> He voted that if the INSTEAD rule had only _one_ statement, return that,
BM> if not, return nothing.
We still need Tom's word and Hiroshi, since they were the most related
to the subject, and the other developer's opinion... :)

------------- 
Best regards,
 Steve Howe                           mailto:howe@carcass.dhs.org


Tue, 10 Sep 2002 00:25:48 -0300
Steve Howe <howe@carcass.dhs.org>
Re: Rule updates and PQcmdstatus() issue
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Mon, 9 Sep 2002, Bruce Momjian wrote:
>> All the problems here are coming from INSTEAD rules.  We don't have
>> INSTEAD triggers or contraints.

> Sure we do, well sort of. :)
> Make a before trigger that does a different statement and returns NULL
> to abort the original action on that row.

I think we can reasonably leave the side-effects of triggers out of the
discussion.  PQcmdStatus numbers have never included side-effects of
triggers in the past, and I see no reason for them to start now.

I think it's reasonable to exclude both triggers and non-INSTEAD rules
from the status count, on the grounds that these normally represent
"add-on" actions and not the "real" action.  The cases that get
interesting are those that involve multiple INSTEAD actions (either from
multiple INSTEAD rules, or a single rule with multiple commands in its
body) and those cases where the INSTEAD action is a different type from
the original command (eg, ON UPDATE DO INSTEAD INSERT...).

			regards, tom lane

Tue, 10 Sep 2002 09:24:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Rule updates and PQcmdstatus() issue 

On Tue, 10 Sep 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Mon, 9 Sep 2002, Bruce Momjian wrote:
> >> All the problems here are coming from INSTEAD rules.  We don't have
> >> INSTEAD triggers or contraints.
>
> > Sure we do, well sort of. :)
> > Make a before trigger that does a different statement and returns NULL
> > to abort the original action on that row.
>
> I think we can reasonably leave the side-effects of triggers out of the
> discussion.  PQcmdStatus numbers have never included side-effects of
> triggers in the past, and I see no reason for them to start now.
>

I agree, I was just commenting on the instead trigger comment.


Tue, 10 Sep 2002 08:25:10 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Rule updates and PQcmdstatus() issue 
Bruce Momjian wrote:
> 
> Jan Wieck wrote:
> > We should surely keep this on a much more technical level and avoid any
> > personal offendings. To do so, please explain to me why you think that
> > triggers and constraints are out of focus here? What is the difference
> > between a trigger, a rule and an instead rule from a business process
> > oriented point of view? I think there is none at all. They are just
> > different techniques to do one and the same, implement business logic in
> > the database system.
> 
> All the problems here are coming from INSTEAD rules.  We don't have
> INSTEAD triggers or contraints.

So a BEFORE INSERT trigger on table1 that does an UPDATE to table2 and
then returns NULL is not effectively the same as an ON INSERT ... DO
INSTEAD UPDATE ... rule? Hmmm, the end result is exactly the same so
what do you call it?

I think we will have no chance to really return the number of
VIEW-tuples affected. So any implementation is only a guess and we could
simply return fixed 42 if "some" tuples where affected at all. This
return is as wrong (according to Steve) as everything else but at least
we have a clear definition what it means.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Tue, 10 Sep 2002 17:27:32 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Rule updates and PQcmdstatus() issue
Jan Wieck wrote:
> Bruce Momjian wrote:
> > 
> > Jan Wieck wrote:
> > > We should surely keep this on a much more technical level and avoid any
> > > personal offendings. To do so, please explain to me why you think that
> > > triggers and constraints are out of focus here? What is the difference
> > > between a trigger, a rule and an instead rule from a business process
> > > oriented point of view? I think there is none at all. They are just
> > > different techniques to do one and the same, implement business logic in
> > > the database system.
> > 
> > All the problems here are coming from INSTEAD rules.  We don't have
> > INSTEAD triggers or contraints.
> 
> So a BEFORE INSERT trigger on table1 that does an UPDATE to table2 and
> then returns NULL is not effectively the same as an ON INSERT ... DO
> INSTEAD UPDATE ... rule? Hmmm, the end result is exactly the same so
> what do you call it?

Well, yes, functionally it is the same and we would have trouble dealing
with that too.  I didn't know you could NULL return from a trigger and
it would exit the statement.

> I think we will have no chance to really return the number of
> VIEW-tuples affected. So any implementation is only a guess and we could
> simply return fixed 42 if "some" tuples where affected at all. This
> return is as wrong (according to Steve) as everything else but at least
> we have a clear definition what it means.

Yes, my guess is that accumulating everything with the same tags is the
closest we are going to get and does return the proper values in simple
multi-statement INSTEAD rules.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 10 Sep 2002 18:32:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Rule updates and PQcmdstatus() issue
Jan Wieck writes:

> I think we will have no chance to really return the number of
> VIEW-tuples affected. So any implementation is only a guess and we could
> simply return fixed 42 if "some" tuples where affected at all. This
> return is as wrong (according to Steve) as everything else but at least
> we have a clear definition what it means.

Maybe we should return something to the effect of "unknown, but something
happened".  I can see that returning 0 in case of doubt might confuse
applications.

-- 
Peter Eisentraut   peter_e@gmx.net


Wed, 11 Sep 2002 21:59:10 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Rule updates and PQcmdstatus() issue