Re: Bug in PL/pgSQL GET DIAGNOSTICS?

Thomas Lockhart wrote:
> ...
> > Why you object to that, and insist it must be an environment variable
> > instead (if that is indeed what you're doing), I'm not sure....
>
> Well, what I was hoping for, but no longer expect, is that features
> (store xlog in another area) can be implemented and applied without
> rejection by the new gatekeepers. It is a feature that we do not have
> now, and could have implemented for 7.3.
>
> No need to rehash the points which were not understood in the
> "discussion".
>
> I have no fundamental objection to extending and replacing
> implementation features as positive contributions to development. I do
> have trouble with folks rejecting features without understanding the
> issues, and sorry, there was a strong thread of "why would anyone want
> to put storage on another device" to the discussion.

I believe the discussion was "Why not use symlinks?" I think we have
addressed that issue with the GUC variable solution. Certainly we all
recognize the value of moving storage to another drive. It is mentioned
in the SGML docs and other places.

In fact, I tried to open a dialog with you on this issue several times,
but when I got no reply, I had to remove PGXLOG. If we had continued
discussion, we might have come up with the GUC compromise.

> There has been a fundamental shift in the quality and civility of
> discussions over issues over the last couple of years, and I was naively
> hoping that we could work through that on this topic. Not happening, and
> not likely too.

My impression is that things have been getting better in the past six
months. There is more open discussion, and more voting, meaning one
group isn't making all the decisions.

I have worked to limit the sway of any "new gatekeepers". People are
encouraged to vote, and we normally accept that outcome. I think
gatekeepers should sway only in the force of their arguments. Do you
feel this was not followed on the PGXLOG case, or is the concept in
error?

I certainly have been frustrated when my features were not accepted, but
I have to accept the vote of the group.

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

On Fri, 20 Sep 2002, Bruce Momjian wrote:

> In fact, I tried to open a dialog with you on this issue several times,
> but when I got no reply, I had to remove PGXLOG. If we had continued
> discussion, we might have come up with the GUC compromise.

Ya know, I'm sitting back and reading this, and other threads, and
assimilating what is being bantered about, and start to think that its
time to cut back on the gatekeepers ...

Thomas implemented an option that he felt was useful, and that doesn't
break anything inside of the code ... he provided 2 methods of being able
to move the xlog's to another location (through command line and
environment variable, both of which are standard methods for doing such in
server software) ... but, because a small number of ppl "voted" that it
should go away, it went away ...

You don't :vote: on stuff like this ... if you don't like it, you just
don't use it ... nobody is forcing you to do so. If you think there are
going to be idiots out here that aren't going to use it right, then you
document it appropriately, with *strong* wording against using it ...

Marc G. Fournier wrote:
>
> On Fri, 20 Sep 2002, Bruce Momjian wrote:
>
> > In fact, I tried to open a dialog with you on this issue several times,
> > but when I got no reply, I had to remove PGXLOG. If we had continued
> > discussion, we might have come up with the GUC compromise.
>
> Ya know, I'm sitting back and reading this, and other threads, and
> assimilating what is being bantered about, and start to think that its
> time to cut back on the gatekeepers ...
>
> Thomas implemented an option that he felt was useful, and that doesn't
> break anything inside of the code ... he provided 2 methods of being able
> to move the xlog's to another location (through command line and
> environment variable, both of which are standard methods for doing such in
> server software) ... but, because a small number of ppl "voted" that it
> should go away, it went away ...
>
> You don't :vote: on stuff like this ... if you don't like it, you just
> don't use it ... nobody is forcing you to do so. If you think there are
> going to be idiots out here that aren't going to use it right, then you
> document it appropriately, with *strong* wording against using it ...

I understand your thought of reevaluating how we decide things.

However, if you don't accept voting as a valid way to determine if a
patch is acceptible, what method do you suggest? I don't think we want
to go down the road of saying that you can't vote "no" on a feature
addition.

We just rejected a patch today on LIMIT with UPDATE/DELETE via an
informal vote, and I think it was a valid rejection.

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

On Sun, 22 Sep 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> >
> > On Fri, 20 Sep 2002, Bruce Momjian wrote:
> >
> > > In fact, I tried to open a dialog with you on this issue several times,
> > > but when I got no reply, I had to remove PGXLOG. If we had continued
> > > discussion, we might have come up with the GUC compromise.
> >
> > Ya know, I'm sitting back and reading this, and other threads, and
> > assimilating what is being bantered about, and start to think that its
> > time to cut back on the gatekeepers ...
> >
> > Thomas implemented an option that he felt was useful, and that doesn't
> > break anything inside of the code ... he provided 2 methods of being able
> > to move the xlog's to another location (through command line and
> > environment variable, both of which are standard methods for doing such in
> > server software) ... but, because a small number of ppl "voted" that it
> > should go away, it went away ...
> >
> > You don't :vote: on stuff like this ... if you don't like it, you just
> > don't use it ... nobody is forcing you to do so. If you think there are
> > going to be idiots out here that aren't going to use it right, then you
> > document it appropriately, with *strong* wording against using it ...
>
> I understand your thought of reevaluating how we decide things.
>
> However, if you don't accept voting as a valid way to determine if a
> patch is acceptible, what method do you suggest? I don't think we want
> to go down the road of saying that you can't vote "no" on a feature
> addition.
>
> We just rejected a patch today on LIMIT with UPDATE/DELETE via an
> informal vote, and I think it was a valid rejection.

Its not the concept of 'the vote', its what is being voted on that I have
a major problem with ... for instance, with the above LIMIT patch ... you
are talking about functionality ... I haven't seen that thread yet, so am
not sure why it was rejected, but did the submitter agree with the
reasons? Assuming he did, is this something he's going to re-submit later
after makign fixes?

See, that is one thing I have enjoyed over the years ... someone submit's
a patch and a few ppl jump on top of it, point out a few problems iwth it
and the submitter re-submits with appropriate fixes ...

Actually, I just went to my -patches folder and read the thread ... first
off, the 'informal vote' appears to have consisted of Tom Lane and Alvaro
Herrera, which isn't a vote ... second of all, in that case, the
implementation of such, I believe, would go against SQL specs, no? Second
of all, doesn't it just purely go against the point of a RDBMS if there
are multiple rows in a table with nothing to identify them except for the
ctid/oid? *scratch head*

My point is, the use of an ENVIRONMENT variable for pointing ot a
directory is nowhere near on the scale of implementing an SQL statement
(or extension) that serves to take us steps backwards against the progress
we've made to improve our compliance ...

one has been removed due to personal preferences and nothign else ... the
other rejected as it will break (unless I've misread things?) standard,
accepted procedures ...

"Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> Ya know, I'm sitting back and reading this, and other threads, and
> assimilating what is being bantered about, and start to think that
> its time to cut back on the gatekeepers ...

On the contrary, the quality of code accepted into a DBMS is really
important. If you disagree with the definition of "code quality" that
some developers are employing, then we can discuss that -- but I think
that as the project matures, we should be more picky about the
features we implement, not less.

> Thomas implemented an option that he felt was useful, and that
> doesn't break anything inside of the code

The problem with this line of thinking is that "it doesn't break
stuff" is not sufficient reason for adding a new feature. The burden
of proof is on the person implementing the new feature.

> ... he provided 2 methods of being able to move the xlog's to
> another location

Yes, but why do we need 2 different ways to do exactly the same thing?

> but, because a small number of ppl "voted" that it should go away,
> it went away ...

They didn't just vote, they provided reasons why they thought the
feature was brain-damaged -- reasons which have not be persuasively
refuted, IMHO. If you'd like to see this feature in the code, might I
suggest that you spend less time complaining about "gate keepers"
(hint: it's called code review), and more time explaining exactly why
the feature is worth having?

Cheers,

Neil

--
Neil Conway <neilc(at)samurai(dot)com> || PGP Key ID: DB3C29FC

Marc G. Fournier wrote:
> > However, if you don't accept voting as a valid way to determine if a
> > patch is acceptible, what method do you suggest? I don't think we want
> > to go down the road of saying that you can't vote "no" on a feature
> > addition.
> >
> > We just rejected a patch today on LIMIT with UPDATE/DELETE via an
> > informal vote, and I think it was a valid rejection.
>
> Its not the concept of 'the vote', its what is being voted on that I have
> a major problem with ... for instance, with the above LIMIT patch ... you
> are talking about functionality ... I haven't seen that thread yet, so am
> not sure why it was rejected, but did the submitter agree with the
> reasons? Assuming he did, is this something he's going to re-submit later
> after makign fixes?
>
> See, that is one thing I have enjoyed over the years ... someone submit's
> a patch and a few ppl jump on top of it, point out a few problems iwth it
> and the submitter re-submits with appropriate fixes ...
>
> Actually, I just went to my -patches folder and read the thread ... first
> off, the 'informal vote' appears to have consisted of Tom Lane and Alvaro
> Herrera, which isn't a vote ... second of all, in that case, the
> implementation of such, I believe, would go against SQL specs, no? Second
> of all, doesn't it just purely go against the point of a RDBMS if there
> are multiple rows in a table with nothing to identify them except for the
> ctid/oid? *scratch head*
>
> My point is, the use of an ENVIRONMENT variable for pointing ot a
> directory is nowhere near on the scale of implementing an SQL statement
> (or extension) that serves to take us steps backwards against the progress
> we've made to improve our compliance ...

The issue isn't really compliance because LIMIT in SELECT isn't
compliant either, so adding it to UPDATE/DELETE is just as non-standard
as in SELECT. The real question we vote on, I think, is, "Should this
feature be given to our users? What value does it provide, and what
confusion does it cause? Does the standard suggest anything?"

I think that is the usual criteria. For LIMIT on UPDATE/DELETE, it
provides little value, and adds confusion, i.e. an extra clause in those two
commands that really doesn't add any functionality.

Now, for the PG_XLOG environment variable/-X flag, it is almost the same
result, i.e. it doesn't add much value (use a symlink) and does add
confusion (oops, I forgot to set it).

The idea of having the pg_xlog location in GUC I think was a good
compromise, but too late to be discovered. If the patch author had
continued discussion at the time, I think it would be in 7.3.

> one has been removed due to personal preferences and nothign else ... the
> other rejected as it will break (unless I've misread things?) standard,
> accepted procedures ...

PG_XLOG was remove for a few reasons:

It didn't add much functionality
It was ugly to add -X to all those commands
It was error-prone

Again, the same criteria. Are you saying the criteria I mentioned above
is wrong?

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

"Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> You don't :vote: on stuff like this ...

Why not, exactly?

I wasn't aware that any of core had a non-vetoable right to apply
any patch we liked regardless of the number and strength of the
objections. AFAIK, we resolve differences of opinion by discussion,
followed by a vote if the discussion doesn't produce a consensus.

It was pretty clear that Thomas' original patch lost the vote, or
would have lost if we'd bothered to hold a formal vote. I don't
see anyone arguing against the notion of making XLOG location more
easily configurable --- it was just the notion of making it depend
on environment variables that scared people.

regards, tom lane

On Sun, 22 Sep 2002, Tom Lane wrote:
>
> It was pretty clear that Thomas' original patch lost the vote, or
> would have lost if we'd bothered to hold a formal vote.

Hasn't there just been a formal vote on this?

> I don't
> see anyone arguing against the notion of making XLOG location more
> easily configurable --- it was just the notion of making it depend
> on environment variables that scared people.

And it's obvious it was centred on the use of an environment variable from the
subject line, it's still got PGXLOG in capitals in it.

--
Nigel J. Andrews

"Nigel J. Andrews" wrote:
<snip>
>
> And it's obvious it was centred on the use of an environment variable from the
> subject line, it's still got PGXLOG in capitals in it.

Actually, to be really precise, my original email asked for an
environment variable. But only because I'd thought about it from the
point of view of us already having a PGDATA environment variable and
hadn't considered alternatives nor seen Thomas's stuff.

Personally, I don't care if it's a -X, or an environment variable, or a
GUC option. I'm just extremely positive that we should have an
alternative to using symlinks for this (they don't work properly on NT).

After following the discussion for a while I'm inclined to think that we
should indeed have the GUC version, and *maybe* have the environment
variable or the -X.

The only thing bad about the -X is it's ability to trash your data if
you forget it or get it wrong, and it's really easy to do in a decent
scale environment with many servers. Marc has already suggested we
might as well have something about a particular pg_xlog directory that
PostgreSQL can use to check it's validity upon startup, so that could
solve the data damaging issue.

So, this thread has migrated away from a PGXLOG environment variable to
discuss PGXLOG in general (good or bad) and also has implementation
points too (about which people have been arguing).

Regards and best wishes,

Justin Clift

> --
> Nigel J. Andrews
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

On Sun, 22 Sep 2002, Marc G. Fournier wrote:

> Thomas implemented an option that he felt was useful, and that doesn't
> break anything inside of the code ... he provided 2 methods of being able
> to move the xlog's to another location (through command line and
> environment variable, both of which are standard methods for doing such in
> server software) ... but, because a small number of ppl "voted" that it
> should go away, it went away ...

The option as he implemented it did make the system more fragile.
You can't back up an environment variable, it's separated from other
configuration information, and it's more easily changed without
realizing it. We should be building systems that are as resilient to
human failure as possible, not opening up more possibilities of failure.

We already have a place for configuration information: the configuration
file. If I created a patch to move a variable out of the configuration
file and make it an environment variable instead, everybody would
(rightly) think I was nuts, and the patch certainly would not be
accepted. So why should the situation be different for new configuration
information?

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Tom Lane wrote:
>
> "Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> > You don't :vote: on stuff like this ...
>
> Why not, exactly?
>
> I wasn't aware that any of core had a non-vetoable right to apply
> any patch we liked regardless of the number and strength of the
> objections. AFAIK, we resolve differences of opinion by discussion,
> followed by a vote if the discussion doesn't produce a consensus.
>
> It was pretty clear that Thomas' original patch lost the vote, or
> would have lost if we'd bothered to hold a formal vote. I don't
> see anyone arguing against the notion of making XLOG location more
> easily configurable --- it was just the notion of making it depend
> on environment variables that scared people.

And AFAICS it is scary only because screwing that up will simply corrupt
your database. Thus, a simple random number (okay, and a timestamp of
initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
totally sufficient safety mechanism to prevent starting with the wrong
XLOG directory.

Can we get that instead of ripping out anything?

Jan

--

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

Jan Wieck wrote:
> Tom Lane wrote:
> >
> > "Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> > > You don't :vote: on stuff like this ...
> >
> > Why not, exactly?
> >
> > I wasn't aware that any of core had a non-vetoable right to apply
> > any patch we liked regardless of the number and strength of the
> > objections. AFAIK, we resolve differences of opinion by discussion,
> > followed by a vote if the discussion doesn't produce a consensus.
> >
> > It was pretty clear that Thomas' original patch lost the vote, or
> > would have lost if we'd bothered to hold a formal vote. I don't
> > see anyone arguing against the notion of making XLOG location more
> > easily configurable --- it was just the notion of making it depend
> > on environment variables that scared people.
>
> And AFAICS it is scary only because screwing that up will simply corrupt
> your database. Thus, a simple random number (okay, and a timestamp of
> initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> totally sufficient safety mechanism to prevent starting with the wrong
> XLOG directory.
>
> Can we get that instead of ripping out anything?

Well, the problem is that Thomas stopped communicating, perhaps because
some were too aggressive in criticizing the patch. Once that happened,
there was no way to come up with a solution, and that's why it was
removed.

Also, we are in the process of removing args and moving them to GUC so I
don't see why we would make WAL an exception. It isn't changed that
often.

FYI, I am about to do the same removal for the SSL stuff too. Bear is
no longer responding. It is on the open items list now. If I can't
find someone who can review the good/bad parts of our SSL changes, it
might all be yanked out.

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

P O S T G R E S Q L

7 . 3 O P E N I T E M S

Current at ftp://momjian.postgresql.org/pub/postgresql/open_items.

Source Code Changes
-------------------
Schema handling - ready? interfaces? client apps?
Drop column handling - ready for all clients, apps?
Fix BeOS, QNX4 ports
Fix AIX large file compile failure of 2002-09-11 (Andreas)
Get bison upgrade on postgresql.org for ecpg only (Marc)
Fix vacuum btree bug (Tom)
Fix client apps for autocommit = off
Fix clusterdb to be schema-aware
Change log_min_error_statement to be off by default (Gavin)
Fix return tuple counts/oid/tag for rules
Loading 7.2 pg_dumps
functions no longer public executable
languages no longer public usable
Add schema dump option to pg_dump
Make SET not start a transaction with autocommit off, document it
Add GRANT EXECUTE to all /contrib functions
Revert or fix SSL change

On Going
--------
Security audit

Documentation Changes
---------------------
Document need to add permissions to loaded functions and languages
Move documation to gborg for moved projects

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

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> And AFAICS it is scary only because screwing that up will simply corrupt
> your database. Thus, a simple random number (okay, and a timestamp of
> initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> totally sufficient safety mechanism to prevent starting with the wrong
> XLOG directory.

> Can we get that instead of ripping out anything?

Sure, if someone wants to do that it'd go a long way towards addressing
the safety issues.

But given that, I think a GUC variable is the most appropriate control
mechanism; as someone else pointed out, we've worked long and hard to
make GUC useful and feature-ful, so it seems silly to invent new
configuration items that bypass GUC. The safety concerns were the main
reason I liked a symlink or separate file, but if we attack the safety
problem directly then we might as well go for convenience in how you
actually set the configuration value.

regards, tom lane

On Tue, 24 Sep 2002, Jan Wieck wrote:

> And AFAICS it is scary only because screwing that up will simply corrupt
> your database. Thus, a simple random number (okay, and a timestamp of
> initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> totally sufficient safety mechanism to prevent starting with the wrong
> XLOG directory.

But still, why set up a situation where your database might not
start? Why not set it up so that if you get just *one* environment
or command-line variable right, you can't set another inconsistently
and screw up your start anyway? Why store configuration information
outside of the database data directory in a form that's not easily
backed up, and not easily found by other utilities?

It's almost like people *don't* want to put this in the config file
or something....

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Hi everyone,

In order to clarify things, how about we do a formal vote with specific
details like this:

*******

Are you for...

- pg_xlog directory changeable at all, not using symlinks?

Yes/No

- a PGXLOG environment variable to do this?

Yes/No

- a -X command line option to do this?

Yes/No

- a GUC (postgresql.conf) option to do this?

Yes/No

- altering the format of the pg_xlog directory so that it
can't be used with the wrong database instance?

Yes/No

*******

Does this seem reasonable?

:-)

Regards and best wishes,

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

On Wed, 25 Sep 2002, Curt Sampson wrote:

> On Tue, 24 Sep 2002, Jan Wieck wrote:
>
> > And AFAICS it is scary only because screwing that up will simply corrupt
> > your database. Thus, a simple random number (okay, and a timestamp of
> > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> > totally sufficient safety mechanism to prevent starting with the wrong
> > XLOG directory.
>
> But still, why set up a situation where your database might not
> start? Why not set it up so that if you get just *one* environment
> or command-line variable right, you can't set another inconsistently
> and screw up your start anyway? Why store configuration information
> outside of the database data directory in a form that's not easily
> backed up, and not easily found by other utilities?
>
> It's almost like people *don't* want to put this in the config file
> or something....

Curt, did you see my post about this earlier? I'll repeat it now, just in
case anyone else missed it.

Problem:
- People need to move the pg_xlog directory around on heavily
loaded systems to improve performance

Constraints:
- Windows can't reliably use links to do this.
- If the pg_xlog directory is moved wrong or referenced incorrectly, data
corruption may occur. This makes using a switch or environmental var
dangerous

I consider using a GUC in the postgresql.conf file to be better than any
other option listed so far, but it is still a dangerous place for it to
be.

So, the way I think that would work best would be:

If there's a directory called pg_xlog in the $PGDATA directory, then use
that.

If there's a file called pg_xlog in the $PGDATA directory, then it will
contain the path to the real pg_xlog directory.

If you want to move the pg_xlog directory, you called a custom script
called "mvpgxlog" or something like it that:

1: Checks to make sure the database is shut down
2: Checks to make sure the destination path has enough free space for the
xlogs
3: If these are both true (and whatever logic we need here for safety)
then copy the current pg_xlog directory contents to the new pg_xlog (even
if we are already using an alternative location, this should work), set
proper permissions, rename / move the pg_xlog file / directorry, then
edit/create the $PGDATA/pg_xlog file to point to the new directory.

This method has several advantages, and no real disadvantages I can think
of. The advantages are:

- It makes it easy to move the pg_xlog directory.
- It works equally well for Windows and Unix.
- Gets rid of another GUC setting people can scram their database with.
- It is easy to backup your pg_xlog setting.
- If painted green it should not rust.

How's that sound for a general theory of operation?

I don't see the gain of having a file called pg_xlog vs. using GUC.

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

scott.marlowe wrote:
> On Wed, 25 Sep 2002, Curt Sampson wrote:
>
> > On Tue, 24 Sep 2002, Jan Wieck wrote:
> >
> > > And AFAICS it is scary only because screwing that up will simply corrupt
> > > your database. Thus, a simple random number (okay, and a timestamp of
> > > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> > > totally sufficient safety mechanism to prevent starting with the wrong
> > > XLOG directory.
> >
> > But still, why set up a situation where your database might not
> > start? Why not set it up so that if you get just *one* environment
> > or command-line variable right, you can't set another inconsistently
> > and screw up your start anyway? Why store configuration information
> > outside of the database data directory in a form that's not easily
> > backed up, and not easily found by other utilities?
> >
> > It's almost like people *don't* want to put this in the config file
> > or something....
>
> Curt, did you see my post about this earlier? I'll repeat it now, just in
> case anyone else missed it.
>
> Problem:
> - People need to move the pg_xlog directory around on heavily
> loaded systems to improve performance
>
> Constraints:
> - Windows can't reliably use links to do this.
> - If the pg_xlog directory is moved wrong or referenced incorrectly, data
> corruption may occur. This makes using a switch or environmental var
> dangerous
>
> I consider using a GUC in the postgresql.conf file to be better than any
> other option listed so far, but it is still a dangerous place for it to
> be.
>
> So, the way I think that would work best would be:
>
> If there's a directory called pg_xlog in the $PGDATA directory, then use
> that.
>
> If there's a file called pg_xlog in the $PGDATA directory, then it will
> contain the path to the real pg_xlog directory.
>
> If you want to move the pg_xlog directory, you called a custom script
> called "mvpgxlog" or something like it that:
>
> 1: Checks to make sure the database is shut down
> 2: Checks to make sure the destination path has enough free space for the
> xlogs
> 3: If these are both true (and whatever logic we need here for safety)
> then copy the current pg_xlog directory contents to the new pg_xlog (even
> if we are already using an alternative location, this should work), set
> proper permissions, rename / move the pg_xlog file / directorry, then
> edit/create the $PGDATA/pg_xlog file to point to the new directory.
>
> This method has several advantages, and no real disadvantages I can think
> of. The advantages are:
>
> - It makes it easy to move the pg_xlog directory.
> - It works equally well for Windows and Unix.
> - Gets rid of another GUC setting people can scram their database with.
> - It is easy to backup your pg_xlog setting.
> - If painted green it should not rust.
>
> How's that sound for a general theory of operation?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

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

I do.

The problem is that if you change the location of pg_xlog and do one thing
wrong, poof, your database is now corrupt. Like Tom said earlier, imagine
a command like switch called "please-dont-scram-my-database" and if you
ever forgot it then your data is gone.

Is it better to move such a switch into the postgresql.conf file? Imagine
a GUC setting called "butter-and-bread" that when set would delete all
your data. That's what the equivalent here is, if you make a single
mistake.

Having a FILE called pg_xlog isn't the fix here, it's the result of the
fix, which is to take all the steps of moving the pg_xlog directory and
put them into one script file the user doesn't need to understand to do it
right. I.e. idiot proof the system as much as possible.

We could do it much simpler, if everyone was on Unix. We could just write
a script that would do everything the same but instead of using a file
called pg_xlog, would make a link. the reason for the file is to make it
more transportable to brain damaged OSes like Windows.

Do you really think the GUC variable is a safe way of referencing the
pg_xlog directory all by itself? I can see MANY posts to the lists that
will go like this:

I just installed Postgresql 7.4 and it's been working fine. I needed more
speed, so I looked up the GUC for the pg_xlog and set it to /vol/vol3/ on
my machine. Now my database won't come up. I set it back but it still
won't come up. What can I do to fix that?

Here's the email we'd get from my solution:

Hey, I just tried to move my pg_xlog directory with the mvpgxlog script,
and it gave an error of "permission denied on destination". What does that
mean?

The choice is yours.

On Wed, 25 Sep 2002, Bruce Momjian wrote:

>
> I don't see the gain of having a file called pg_xlog vs. using GUC.
>
> ---------------------------------------------------------------------------
>
> scott.marlowe wrote:
> > On Wed, 25 Sep 2002, Curt Sampson wrote:
> >
> > > On Tue, 24 Sep 2002, Jan Wieck wrote:
> > >
> > > > And AFAICS it is scary only because screwing that up will simply corrupt
> > > > your database. Thus, a simple random number (okay, and a timestamp of
> > > > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> > > > totally sufficient safety mechanism to prevent starting with the wrong
> > > > XLOG directory.
> > >
> > > But still, why set up a situation where your database might not
> > > start? Why not set it up so that if you get just *one* environment
> > > or command-line variable right, you can't set another inconsistently
> > > and screw up your start anyway? Why store configuration information
> > > outside of the database data directory in a form that's not easily
> > > backed up, and not easily found by other utilities?
> > >
> > > It's almost like people *don't* want to put this in the config file
> > > or something....
> >
> > Curt, did you see my post about this earlier? I'll repeat it now, just in
> > case anyone else missed it.
> >
> > Problem:
> > - People need to move the pg_xlog directory around on heavily
> > loaded systems to improve performance
> >
> > Constraints:
> > - Windows can't reliably use links to do this.
> > - If the pg_xlog directory is moved wrong or referenced incorrectly, data
> > corruption may occur. This makes using a switch or environmental var
> > dangerous
> >
> > I consider using a GUC in the postgresql.conf file to be better than any
> > other option listed so far, but it is still a dangerous place for it to
> > be.
> >
> > So, the way I think that would work best would be:
> >
> > If there's a directory called pg_xlog in the $PGDATA directory, then use
> > that.
> >
> > If there's a file called pg_xlog in the $PGDATA directory, then it will
> > contain the path to the real pg_xlog directory.
> >
> > If you want to move the pg_xlog directory, you called a custom script
> > called "mvpgxlog" or something like it that:
> >
> > 1: Checks to make sure the database is shut down
> > 2: Checks to make sure the destination path has enough free space for the
> > xlogs
> > 3: If these are both true (and whatever logic we need here for safety)
> > then copy the current pg_xlog directory contents to the new pg_xlog (even
> > if we are already using an alternative location, this should work), set
> > proper permissions, rename / move the pg_xlog file / directorry, then
> > edit/create the $PGDATA/pg_xlog file to point to the new directory.
> >
> > This method has several advantages, and no real disadvantages I can think
> > of. The advantages are:
> >
> > - It makes it easy to move the pg_xlog directory.
> > - It works equally well for Windows and Unix.
> > - Gets rid of another GUC setting people can scram their database with.
> > - It is easy to backup your pg_xlog setting.
> > - If painted green it should not rust.
> >
> > How's that sound for a general theory of operation?
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I don't see the gain of having a file called pg_xlog vs. using GUC.

Well, the point is to have a safety interlock --- but I like Jan's
idea of using matching identification files in both directories.
With that, a GUC variable seems just fine.

regards, tom lane

On Wed, 25 Sep 2002, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I don't see the gain of having a file called pg_xlog vs. using GUC.
>
> Well, the point is to have a safety interlock --- but I like Jan's
> idea of using matching identification files in both directories.
> With that, a GUC variable seems just fine.

Agreed, the interlock is a great idea. I hadn't seen that one go by.

Hi,

I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS.

In a PL/pgSQL function I want to insert into a table and get the OID back.
That usually works with
GET DIAGNOSTICS last_oid = RESULT_OID;
right after the insert statement.

But if the table that I insert to has a rule (or perhaps a trigger?) that
updates another table, the RESULT_OID after the insert will be 0 (zero).

Can this be fixed (I have no such problem with JDBC and getLastOID())?

Testcase:

CREATE TABLE pltest (
id BIGINT default cs_nextval('invoice_invoice_id') NOT NULL,
t TEXT,
primary key (id)
);

CREATE TABLE plcounter (
counter INTEGER NOT NULL
);

CREATE FUNCTION pltestfunc(integer) RETURNS BOOLEAN AS'
DECLARE
lastOID OID;
BEGIN
FOR i IN 1..$1 LOOP
INSERT INTO pltest (t) VALUES (\'test\');
GET DIAGNOSTICS lastOID = RESULT_OID;
RAISE NOTICE \'RESULT_OID: %\', lastOID;
IF lastOID <= 0 THEN
RAISE EXCEPTION \'RESULT_OID is zero\';
END IF;
END LOOP;
RETURN true;
END;
' LANGUAGE 'plpgsql';

-- comment out the rule and the test will work
CREATE RULE pltest_insert AS
ON INSERT TO pltest DO
UPDATE plcounter SET counter=counter+1;

INSERT INTO plcounter VALUES (0);
SELECT pltestfunc(10);
SELECT * FROM pltest;

DROP FUNCTION pltestfunc(integer);
DROP TABLE pltest;

Regards,
Michael

Curt Sampson wrote:
>
> On Tue, 24 Sep 2002, Jan Wieck wrote:
>
> > And AFAICS it is scary only because screwing that up will simply corrupt
> > your database. Thus, a simple random number (okay, and a timestamp of
> > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> > totally sufficient safety mechanism to prevent starting with the wrong
> > XLOG directory.
>
> But still, why set up a situation where your database might not
> start? Why not set it up so that if you get just *one* environment
> or command-line variable right, you can't set another inconsistently
> and screw up your start anyway? Why store configuration information
> outside of the database data directory in a form that's not easily
> backed up, and not easily found by other utilities?

With the number of screws our product has, there are so many
possible combinations that don't work, why worry about one more
or less?

Seriously, if you move around files, make symlinks or adjust
config variable to reflect that, there's allways the possibility
that you fatfinger it and cannot startup. The point is not to
make it pellethead-safe so that the damned thing will start
allways, but to make it pellethead-safe so that an attempt to
start with wrong settings doesn't blow away the whole server.

>
> It's almost like people *don't* want to put this in the config file
> or something....

I want to have it it the config file. Just that that doesn't
prevent anything. And if we have a "signature" file in the xlog
and data directories, you can make it dummy-safe as you like ...
if the config option is set wrong, first search for it on all
drives before bailing out and if found, postmaster corrects the
config setting. That way the admin can play hide and seek with
our database ... ;-)

Jan

--

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

"scott.marlowe" wrote:

> Having a FILE called pg_xlog isn't the fix here, it's the result of the
> fix, which is to take all the steps of moving the pg_xlog directory and
> put them into one script file the user doesn't need to understand to do it
> right. I.e. idiot proof the system as much as possible.

And your script/program cannot modify postgresql.conf instead of
creating a new file?

Please remember: "A fool with a tool is still a fool". You can
provide programs and scripts as many as you want. There have
allways been these idiots who did stuff like truncating pg_log
...

Jan

--

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

Jan Wieck wrote:
> > It's almost like people *don't* want to put this in the config file
> > or something....
>
> I want to have it it the config file. Just that that doesn't
> prevent anything. And if we have a "signature" file in the xlog
> and data directories, you can make it dummy-safe as you like ...
> if the config option is set wrong, first search for it on all
> drives before bailing out and if found, postmaster corrects the
> config setting. That way the admin can play hide and seek with
> our database ... ;-)

Let's get it into GUC and see what problems people have. We may find
out no one has difficulty.

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

On Wed, 25 Sep 2002, Jan Wieck wrote:

> "scott.marlowe" wrote:
>
> > Having a FILE called pg_xlog isn't the fix here, it's the result of the
> > fix, which is to take all the steps of moving the pg_xlog directory and
> > put them into one script file the user doesn't need to understand to do it
> > right. I.e. idiot proof the system as much as possible.
>
> And your script/program cannot modify postgresql.conf instead of
> creating a new file?

That's a minor point. It could be anywhere. It's just that much like a
symlink is visible from the shell with a simple ls -l, so too is pg_xlog
being a file an obvious sign that pg_xlog doesn't live here anymore.

> Please remember: "A fool with a tool is still a fool". You can
> provide programs and scripts as many as you want. There have
> allways been these idiots who did stuff like truncating pg_log

So, should we take out seatbelts from cars, safeties from guns, and have
everyone run about with sharp sticks too? :-) I know that the second we
make something more idiot proof, someone will make a better idiot, but
that doesn't mean we shouldn't make things more idiot proof, we should
just try to anticipate the majority of idiots (and let's face it, we can
all be idiots at the right moments sometimes.)

But, I have a few more questions about the signature file solution. Is
the signature file going to be updated by date or something everytime the
database is started up and shut down? If not, then it's quite possible
that someone could copy the pg_xlog dir somewhere, run it for a while,
then they change it back to the base pg_xlog will the database know that
those xlogs are stale and not start up, or will it start up and corrupt
the database with the old xlogs? As long as there's a time stamp in both
places it should work fine.

scott.marlowe wrote:
> On Wed, 25 Sep 2002, Jan Wieck wrote:
> So, should we take out seatbelts from cars, safeties from guns, and have
> everyone run about with sharp sticks too? :-) I know that the second we
> make something more idiot proof, someone will make a better idiot, but
> that doesn't mean we shouldn't make things more idiot proof, we should
> just try to anticipate the majority of idiots (and let's face it, we can
> all be idiots at the right moments sometimes.)

Can we wait for someone to be injured in a car accident before putting
in heavy seat belts?

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

"scott.marlowe" wrote:
> [...]
> But, I have a few more questions about the signature file solution. Is
> the signature file going to be updated by date or something everytime the
> database is started up and shut down? If not, then it's quite possible
> that someone could copy the pg_xlog dir somewhere, run it for a while,
> then they change it back to the base pg_xlog will the database know that
> those xlogs are stale and not start up, or will it start up and corrupt
> the database with the old xlogs? As long as there's a time stamp in both
> places it should work fine.

Good question. Actually, I think it'd be a perfect place and use
for a copy of the controlfile.

Jan

--

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

Bruce Momjian wrote:
>
> scott.marlowe wrote:
> > On Wed, 25 Sep 2002, Jan Wieck wrote:
> > So, should we take out seatbelts from cars, safeties from guns, and have
> > everyone run about with sharp sticks too? :-) I know that the second we
> > make something more idiot proof, someone will make a better idiot, but
> > that doesn't mean we shouldn't make things more idiot proof, we should
> > just try to anticipate the majority of idiots (and let's face it, we can
> > all be idiots at the right moments sometimes.)

Sure, been there, done that ...

>
> Can we wait for someone to be injured in a car accident before putting
> in heavy seat belts?

About the car seatbelts I have a theory. If we would not have
seatbelts, and instead of Airbags sharp sticks instantly killing
the driver in the case of an accident, most of these wannabe
Racing-Champs on our streets would either drive more reasonable
or get removed by natural selection. Maybe the overall number of
accidents would drop below the actual number of deaths in traffic
(remember, we only kill the drivers on purpose, not anyone else
in the car) ... and for sure the far lower number of *only*
crippled or disabled victims will take a big burden off of the
healthcare and wellfare system ...

Okay, okay, enough proof of the first statement ... back to
business.

Jan B-)

--

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Can we wait for someone to be injured in a car accident before putting
> in heavy seat belts?

Not the analogy you wanted to make ... if you knew there was a serious
risk, that's called negligence in most American courts. Ask Ford about
the Pinto ...

regards, tom lane

"Michael Paesold" <mpaesold(at)gmx(dot)at> writes:
> I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS.

Hm. This seems to be SPI's version of the same definitional issue
we're contending with for status data returned from an interactive
query: SPI is currently set up to return the status of the last
querytree it executes, which is probably the wrong thing to do in the
presence of rule rewrites. But I'm hesitant to change SPI until we know
what we're going to do for interactive query status.

regards, tom lane

Tom Lane wrote:

> "Michael Paesold" <mpaesold(at)gmx(dot)at> writes:
> > I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS.
>
> Hm. This seems to be SPI's version of the same definitional issue
> we're contending with for status data returned from an interactive
> query: SPI is currently set up to return the status of the last
> querytree it executes, which is probably the wrong thing to do in the
> presence of rule rewrites. But I'm hesitant to change SPI until we know
> what we're going to do for interactive query status.
>
> regards, tom lane

So this is not going to be fixed for 7.3 I suggest, no? Can you add the
issue to the TODO list or can this thread be added to any appropriate TODO
item?

Regards,
Michael Paesold

Michael Paesold wrote:
> Tom Lane wrote:
>
> > "Michael Paesold" <mpaesold(at)gmx(dot)at> writes:
> > > I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS.
> >
> > Hm. This seems to be SPI's version of the same definitional issue
> > we're contending with for status data returned from an interactive
> > query: SPI is currently set up to return the status of the last
> > querytree it executes, which is probably the wrong thing to do in the
> > presence of rule rewrites. But I'm hesitant to change SPI until we know
> > what we're going to do for interactive query status.
> >
> > regards, tom lane
>
> So this is not going to be fixed for 7.3 I suggest, no? Can you add the
> issue to the TODO list or can this thread be added to any appropriate TODO
> item?

I already have a TODO item:

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

I am unsure if it will be fixed in 7.3 or not. It is still on the open
items list, and I think we have a general plan to fix it.

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I already have a TODO item:
> * Return proper effected tuple count from complex commands [return]
> I am unsure if it will be fixed in 7.3 or not. It is still on the open
> items list, and I think we have a general plan to fix it.

I got distracted and wasn't following the thread a few days ago about
the topic. Did people come to a consensus about how it should work?

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I already have a TODO item:
> > * Return proper effected tuple count from complex commands [return]
> > I am unsure if it will be fixed in 7.3 or not. It is still on the open
> > items list, and I think we have a general plan to fix it.
>
> I got distracted and wasn't following the thread a few days ago about
> the topic. Did people come to a consensus about how it should work?

Well, sort of. It was similar to your original proposal. See the TODO
link for details. I am heading out for 2 hours and will summarize when
I return.

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

On Wed, 25 Sep 2002, Jan Wieck wrote:

> With the number of screws our product has, there are so many
> possible combinations that don't work, why worry about one more
> or less?

That's just silly, so I won't even bother replying.

> Seriously, if you move around files, make symlinks or adjust
> config variable to reflect that, there's allways the possibility
> that you fatfinger it and cannot startup.

True. But once your symlink is in place, it is stored on disk in the
postgres data directory. An environment variable is a transient setting
in memory, which means that you have to have a program set it, and you
have to make sure that program gets run before any startup, be it an
automated startup from /etc/rc on boot or a manual startup.

> I want to have it it the config file.

Well, then we're agreed.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I already have a TODO item:
> > * Return proper effected tuple count from complex commands [return]
> > I am unsure if it will be fixed in 7.3 or not. It is still on the open
> > items list, and I think we have a general plan to fix it.
>
> I got distracted and wasn't following the thread a few days ago about
> the topic. Did people come to a consensus about how it should work?

OK, I am back. I think the most promising proposal was from you, Tom:

http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html

It basically breaks down the three results (tag, oid, tuple count), and
the INSTEAD/non-INSTEAD behavior.

I actually got a big chuckle from this paragraph:

Come on, guys, work with me a little here. I've thrown out several
alternative suggestions already, and all I've gotten from either of
you is refusal to think about the problem.

I liked the "work with me" phrase.

To summarize, with non-INSTEAD, we get the tag, oid, and tuple count of
the original query. Everyone agrees on that.

For non-INSTEAD, we have:

1) return original tag
2) return oid if all inserts in the rule insert only one row
3) return tuple count of all commands with the same tag

For item 2, it is possible to have multiple INSERTS in the rule and
return an oid if the sum of the inserts is only one row.

Item 3 is the most controversial. Some say sum all tuple counts, i.e.
sum INSERT/UPDATE/DELETE. That just seems to messy to me. I think
summing only the matching tags has the highest probability of returning
a meaningful number.

Also, item 2 and 3 work well together with INSERT because a tuple count
of 1 returns an oid, while > 1 does not, which is consistent with a
non-rule insert.

(FYI, I am still working SSL.)

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, I am back. I think the most promising proposal was from you, Tom:
> http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html

But that wasn't a specific proposal --- it was more or less an
enumeration of the possibilities. What are we picking?

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, I am back. I think the most promising proposal was from you, Tom:
> > http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html
>
> But that wasn't a specific proposal --- it was more or less an
> enumeration of the possibilities. What are we picking?

The rest of my message explains your poposal while clarifying certain
options you gave in the email.

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

On Wed, 25 Sep 2002 21:40:03 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>Item 3 is the most controversial. Some say sum all tuple counts, i.e.
>sum INSERT/UPDATE/DELETE. That just seems to messy to me. I think
>summing only the matching tags has the highest probability of returning
>a meaningful number.

[Trying to keep it short this time]

I still believe that there is more than one correct answer; it just
depends on what the dba intends. So I proposed a syntax change for
letting the dba explicitly mark the statements she/he wants to affect
tuple count and oid.

-> http://archives.postgresql.org/pgsql-hackers/2002-09/msg00720.php

Unfortunately I tried to summarize all other proposals and the mail
got so long that nobody read it to the end :-(

Servus
Manfred

Curt Sampson wrote:
>
> On Wed, 25 Sep 2002, Jan Wieck wrote:
>
> > With the number of screws our product has, there are so many
> > possible combinations that don't work, why worry about one more
> > or less?
>
> That's just silly, so I won't even bother replying.

Curt,

it might sound silly on first sight and isolated. But it was in reply
to:

>>> But still, why set up a situation where your database might not
>>> start? Why not set it up so that if you get just *one* environment
>>> or command-line variable right, you can't set another inconsistently
>>> and screw up your start anyway? Why store configuration information
>>> outside of the database data directory in a form that's not easily
>>> backed up, and not easily found by other utilities?

Apply that argumentation to all of our commandline switches and config
options and we end up with something that behaves like Microsoft
products ... they know everything better, you cannot tune them, they
work ... and you needed a bigger machine anyway.

I am absolutely not in favour of the PGXLOG environment variable. But if
someone else wants it, it doesn't bother me because I wouldn't use it
and it cannot hurt me.

I am simply against this "I think it's wrong so you have to change your
behaviour" attitude.

Jan

--

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

On Thu, 26 Sep 2002, Jan Wieck wrote:

> >>> But still, why set up a situation where your database might not
> >>> start? Why not set it up so that if you get just *one* environment
> >>> or command-line variable right, you can't set another inconsistently
> >>> and screw up your start anyway? Why store configuration information
> >>> outside of the database data directory in a form that's not easily
> >>> backed up, and not easily found by other utilities?
>
> Apply that argumentation to all of our commandline switches and config
> options and we end up with something that behaves like Microsoft
> products ... they know everything better, you cannot tune them, they
> work ... and you needed a bigger machine anyway.

Talk about a straw man! I have repeatedly said:

I WANT THE FEATURE THAT LETS YOU TUNE THE LOCATION OF THE LOG FILE!

Read it again, and again, until you understand that we both want
that feature.

Then realize, I just want it implemented in a way that makes it
less likely that people will find themselves in a situation where
the server doesn't start.

> I am absolutely not in favour of the PGXLOG environment variable. But if
> someone else wants it, it doesn't bother me because I wouldn't use it
> and it cannot hurt me.

Responsible programmers, when confronted with a more accident-prone
and less accident-prone way of doing something, chose the less
accident-prone way of doing things. That way people who are naive,
or tired, or just having a bad day are less likely to come to harm.

Using the config file is not only safer, it's actually more
convenient. And since we're going to have the config file option
anyway, removing the environment variable option means that others
have less documentation to read, and will spend less time wondering
why there's two different ways to do the same thing. And naive
people won't chose the wrong way because they don't know any better.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Manfred Koizar wrote:
> On Wed, 25 Sep 2002 21:40:03 -0400 (EDT), Bruce Momjian
> <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> >Item 3 is the most controversial. Some say sum all tuple counts, i.e.
> >sum INSERT/UPDATE/DELETE. That just seems to messy to me. I think
> >summing only the matching tags has the highest probability of returning
> >a meaningful number.
>
> [Trying to keep it short this time]
>
> I still believe that there is more than one correct answer; it just
> depends on what the dba intends. So I proposed a syntax change for
> letting the dba explicitly mark the statements she/he wants to affect
> tuple count and oid.
>
> -> http://archives.postgresql.org/pgsql-hackers/2002-09/msg00720.php
>
> Unfortunately I tried to summarize all other proposals and the mail
> got so long that nobody read it to the end :-(

That is an interesting idea; some syntax in the rule that marks the
items. The one downside to that is the fact the rule writer has to
make adjustments. Perhaps we could implement the behavoir I described
and add such tagging later.

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

Bruce Momjian writes:

> To summarize, with non-INSTEAD, we get the tag, oid, and tuple count of
> the original query. Everyone agrees on that.
>
> For non-INSTEAD, we have:

[I think this is the INSTEAD part.]

> 1) return original tag
> 2) return oid if all inserts in the rule insert only one row
> 3) return tuple count of all commands with the same tag

I think proper encapsulation would require us to simulate the original
command, hiding the fact that something else happened internally. I know
it's really hard to determine the "virtual" count of an update or delete
if the command had acted on a permament base table, but I'd rather
maintain the encapsulation of updateable views and return "unknown" in
that case.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > To summarize, with non-INSTEAD, we get the tag, oid, and tuple count of
> > the original query. Everyone agrees on that.
> >
> > For non-INSTEAD, we have:
>
> [I think this is the INSTEAD part.]

Sorry, yes.

> > 1) return original tag
> > 2) return oid if all inserts in the rule insert only one row
> > 3) return tuple count of all commands with the same tag
>
> I think proper encapsulation would require us to simulate the original
> command, hiding the fact that something else happened internally. I know
> it's really hard to determine the "virtual" count of an update or delete
> if the command had acted on a permament base table, but I'd rather
> maintain the encapsulation of updateable views and return "unknown" in
> that case.

Well, let's look at the common case. For proper view rules, these would
all return the right values because the UPDATE in the rule would be
returned. Is that what you mean?

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

Bruce Momjian writes:

> Well, let's look at the common case. For proper view rules, these would
> all return the right values because the UPDATE in the rule would be
> returned. Is that what you mean?

I guess that really depends on whether the rules are written to properly
constrain the writes to the view to the set of rows visible by the view.
For example, if a view v1 selects from a single table t1 constrained by a
search condition, and I do UPDATE v1 SET ...; without a condition, does
that affect all rows in t1? If not, then both our proposals are
equivalent, if yes, then the it's the user's fault, I suppose.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > Well, let's look at the common case. For proper view rules, these would
> > all return the right values because the UPDATE in the rule would be
> > returned. Is that what you mean?
>
> I guess that really depends on whether the rules are written to properly
> constrain the writes to the view to the set of rows visible by the view.
> For example, if a view v1 selects from a single table t1 constrained by a
> search condition, and I do UPDATE v1 SET ...; without a condition, does
> that affect all rows in t1? If not, then both our proposals are
> equivalent, if yes, then the it's the user's fault, I suppose.

Well, since we found that we can't get a perfect solution, I started to
think of the common cases. First, there is the "log changes" type of
rule, but that isn't INSTEAD, so it doesn't even apply here. We already
know we want to return the result of the main query.

CREATE RULE service_request_update AS -- UPDATE rule
ON UPDATE TO service_request
DO
INSERT INTO service_request_log (customer_id, description, mod_type)
VALUES (old.customer_id, old.description, 'U');

CREATE RULE service_request_delete AS -- DELETE rule
ON DELETE TO service_request
DO
INSERT INTO service_request_log (customer_id, description, mod_type)
VALUES (old.customer_id, old.description, 'D');

Second, there is the updatable view rule, that is INSTEAD, and relies on
the primary key of the table:

CREATE RULE view_realtable_insert AS -- INSERT rule
ON INSERT TO view_realtable
DO INSTEAD
INSERT INTO realtable
VALUES (new.col);

CREATE RULE view_realtable_update AS -- UPDATE rule
ON UPDATE TO view_realtable
DO INSTEAD
UPDATE realtable
SET col = new.col
WHERE col = old.col;

CREATE RULE view_realtable_delete AS -- DELETE rule
ON DELETE TO view_realtable
DO INSTEAD
DELETE FROM realtable
WHERE col = old.col;

It is my understanding that the proposed rule result improvements will
return the proper values in these cases. That is why I like the current
proposal. It also makes any extra non-tag matching queries in the rule
not affect the result, which seems best.

Does anyone else have a common rule that would return incorrect results
using the proposed rules?

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

On Sat, 28 Sep 2002 13:41:04 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>Does anyone else have a common rule that would return incorrect results
>using the proposed rules?

CREATE VIEW twotables AS
SELECT ... FROM table1 INNER JOIN table2 ON ... ;

CREATE RULE twotables_insert AS -- INSERT rule
ON INSERT TO twotables
DO INSTEAD (
INSERT INTO table1 VALUES (new.pk, new.col1);
INSERT INTO table2 VALUES (new.pk, new.col2)
);

CREATE RULE twotables_update AS -- UPDATE rule
ON UPDATE TO twotables
DO INSTEAD (
UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk;
UPDATE table2 SET col2 = new.col2 WHERE pk = old.pk
);

CREATE RULE twotables_delete AS -- DELETE rule
ON DELETE TO twotables
DO INSTEAD (
DELETE FROM table1 WHERE pk = old.pk;
DELETE FROM table2 WHERE pk = old.pk
);

CREATE VIEW visible AS
SELECT ... FROM table3
WHERE deleted = 0;

CREATE RULE visible_delete AS -- DELETE rule
ON DELETE TO visible
DO INSTEAD
UPDATE table3
SET deleted = 1
WHERE pk = old.pk;

Servus
Manfred

OK, that is a good example. It would return the sum of the matching
tags. You are suggesting here that it would be better to take the
result of the last matching tag command, right?

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

Manfred Koizar wrote:
> On Sat, 28 Sep 2002 13:41:04 -0400 (EDT), Bruce Momjian
> <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> >Does anyone else have a common rule that would return incorrect results
> >using the proposed rules?
>
> CREATE VIEW twotables AS
> SELECT ... FROM table1 INNER JOIN table2 ON ... ;
>
> CREATE RULE twotables_insert AS -- INSERT rule
> ON INSERT TO twotables
> DO INSTEAD (
> INSERT INTO table1 VALUES (new.pk, new.col1);
> INSERT INTO table2 VALUES (new.pk, new.col2)
> );
>
> CREATE RULE twotables_update AS -- UPDATE rule
> ON UPDATE TO twotables
> DO INSTEAD (
> UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk;
> UPDATE table2 SET col2 = new.col2 WHERE pk = old.pk
> );
>
> CREATE RULE twotables_delete AS -- DELETE rule
> ON DELETE TO twotables
> DO INSTEAD (
> DELETE FROM table1 WHERE pk = old.pk;
> DELETE FROM table2 WHERE pk = old.pk
> );
>
> CREATE VIEW visible AS
> SELECT ... FROM table3
> WHERE deleted = 0;
>
> CREATE RULE visible_delete AS -- DELETE rule
> ON DELETE TO visible
> DO INSTEAD
> UPDATE table3
> SET deleted = 1
> WHERE pk = old.pk;
>
> Servus
> Manfred
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

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

On Sat, 28 Sep 2002 19:20:43 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>OK, that is a good example. It would return the sum of the matching
>tags. You are suggesting here that it would be better to take the
>result of the last matching tag command, right?

The examples were meant to support my previous suggestion of
explicitly marking the statement you want to be counted, something
like:

CREATE VIEW twotables AS
SELECT ... FROM table1 INNER JOIN table2 ON ... ;

CREATE RULE twotables_insert AS -- INSERT rule
ON INSERT TO twotables
DO INSTEAD (
COUNT INSERT INTO table1 VALUES (new.pk, new.col1);
INSERT INTO table2 VALUES (new.pk, new.col2)
);

CREATE RULE twotables_update AS -- UPDATE rule
ON UPDATE TO twotables
DO INSTEAD (
COUNT UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk;
UPDATE table2 SET col2 = new.col2 WHERE pk = old.pk
);

CREATE RULE twotables_delete AS -- DELETE rule
ON DELETE TO twotables
DO INSTEAD (
COUNT DELETE FROM table1 WHERE pk = old.pk;
DELETE FROM table2 WHERE pk = old.pk
);

CREATE VIEW visible AS
SELECT ... FROM table3
WHERE deleted = 0;

CREATE RULE visible_delete AS -- DELETE rule
ON DELETE TO visible
DO INSTEAD
COUNT UPDATE table3
SET deleted = 1
WHERE pk = old.pk;

One argument against automatically "don't count non-INSTEAD rules and
count the last statement in INSTEAD rules": sql-createrule.html says:
| for view updates: there must be an unconditional INSTEAD rule [...]
| If you want to handle all the useful cases in conditional rules, you
| can; just add an unconditional DO INSTEAD NOTHING rule [...]
| Then make the conditional rules non-INSTEAD

CREATE RULE v_update AS -- UPDATE rule
ON UPDATE TO v
DO INSTEAD NOTHING;

CREATE RULE v_update2 AS -- UPDATE rule
ON UPDATE TO v WHERE <condition1>
DO (
COUNT ...
);

CREATE RULE v_update3 AS -- UPDATE rule
ON UPDATE TO v WHERE <condition2>
DO (
COUNT ...
);

Servus
Manfred

We have talked about possible return values for RULES, particularly
INSTEAD rule. Manfred has a nice example here, so I propose we handle
INSTEAD rules this way: that we return the oid and tuple count of the
last INSTEAD rule query with a tag matching the main query. The
returned tag, of course, would be the tag of the main query. This works
for Manfred's case, and it works for my case when there is only one
action in the INSTEAD rule. If there is more than one matching tag in
the INSTEAD rule, the user has the option to place the query he wants
for the return at the end of the rule. This does give the user some
control over what is returned.

Comments?

I think non-INSTEAD rules already return the tag, oid, and tuple count of
the main query, right?

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

Manfred Koizar wrote:
> On Sat, 28 Sep 2002 19:20:43 -0400 (EDT), Bruce Momjian
> <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> >OK, that is a good example. It would return the sum of the matching
> >tags. You are suggesting here that it would be better to take the
> >result of the last matching tag command, right?
>
> The examples were meant to support my previous suggestion of
> explicitly marking the statement you want to be counted, something
> like:
>
> CREATE VIEW twotables AS
> SELECT ... FROM table1 INNER JOIN table2 ON ... ;
>
> CREATE RULE twotables_insert AS -- INSERT rule
> ON INSERT TO twotables
> DO INSTEAD (
> COUNT INSERT INTO table1 VALUES (new.pk, new.col1);
> INSERT INTO table2 VALUES (new.pk, new.col2)
> );
>
> CREATE RULE twotables_update AS -- UPDATE rule
> ON UPDATE TO twotables
> DO INSTEAD (
> COUNT UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk;
> UPDATE table2 SET col2 = new.col2 WHERE pk = old.pk
> );
>
> CREATE RULE twotables_delete AS -- DELETE rule
> ON DELETE TO twotables
> DO INSTEAD (
> COUNT DELETE FROM table1 WHERE pk = old.pk;
> DELETE FROM table2 WHERE pk = old.pk
> );
>
> CREATE VIEW visible AS
> SELECT ... FROM table3
> WHERE deleted = 0;
>
> CREATE RULE visible_delete AS -- DELETE rule
> ON DELETE TO visible
> DO INSTEAD
> COUNT UPDATE table3
> SET deleted = 1
> WHERE pk = old.pk;
>
> One argument against automatically "don't count non-INSTEAD rules and
> count the last statement in INSTEAD rules": sql-createrule.html says:
> | for view updates: there must be an unconditional INSTEAD rule [...]
> | If you want to handle all the useful cases in conditional rules, you
> | can; just add an unconditional DO INSTEAD NOTHING rule [...]
> | Then make the conditional rules non-INSTEAD
>
> CREATE RULE v_update AS -- UPDATE rule
> ON UPDATE TO v
> DO INSTEAD NOTHING;
>
> CREATE RULE v_update2 AS -- UPDATE rule
> ON UPDATE TO v WHERE <condition1>
> DO (
> COUNT ...
> );
>
> CREATE RULE v_update3 AS -- UPDATE rule
> ON UPDATE TO v WHERE <condition2>
> DO (
> COUNT ...
> );
>
> Servus
> Manfred
>

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> We have talked about possible return values for RULES, particularly
> INSTEAD rule. Manfred has a nice example here, so I propose we handle
> INSTEAD rules this way: that we return the oid and tuple count of the
> last INSTEAD rule query with a tag matching the main query.

Hmm ... that's subtly different from what I'd seen discussed before.
I thought the idea was

1. If no INSTEAD rule: return tag, count, and OID of original
query, regardless of what is added by non-INSTEAD rules.
(I think this part is not controversial.)
2. If any INSTEAD rule: return tag, count, and OID of the last
executed query that has the same tag as the original query.
If no substituted query matches the original query's tag,
return original query's tag with zero count and OID.
(This is where the going gets tough.)

I think you just modified the second part of that to restrict it to
queries that were added by INSTEAD rules. This is doable but it's
not a trivial change --- in particular, I think it implies adding
another field to Query data structure so we can mark INSTEAD-added
vs non-INSTEAD-added queries. Which means an initdb because it breaks
stored rules.

Offhand I think this might be worth doing, because I like that subtle
change in behavior. But we should understand exactly what we're doing
here...

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > We have talked about possible return values for RULES, particularly
> > INSTEAD rule. Manfred has a nice example here, so I propose we handle
> > INSTEAD rules this way: that we return the oid and tuple count of the
> > last INSTEAD rule query with a tag matching the main query.
>
> Hmm ... that's subtly different from what I'd seen discussed before.
> I thought the idea was
>
> 1. If no INSTEAD rule: return tag, count, and OID of original
> query, regardless of what is added by non-INSTEAD rules.
> (I think this part is not controversial.)
> 2. If any INSTEAD rule: return tag, count, and OID of the last
> executed query that has the same tag as the original query.
> If no substituted query matches the original query's tag,
> return original query's tag with zero count and OID.
> (This is where the going gets tough.)
>
> I think you just modified the second part of that to restrict it to
> queries that were added by INSTEAD rules. This is doable but it's
> not a trivial change --- in particular, I think it implies adding
> another field to Query data structure so we can mark INSTEAD-added
> vs non-INSTEAD-added queries. Which means an initdb because it breaks
> stored rules.

I am confused how yours differs from mine. I don't see how the last
matching tagged query would not be from an INSTEAD rule. Are you
thinking multiple queries in the query string?

> Offhand I think this might be worth doing, because I like that subtle
> change in behavior. But we should understand exactly what we're doing
> here...

Seems we are adding up reasons for initdb. :-)

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I am confused how yours differs from mine. I don't see how the last
> matching tagged query would not be from an INSTEAD rule.

You could have both INSTEAD and non-INSTEAD rules firing for the same
original query. If the alphabetically-last rule is a non-INSTEAD rule,
then there's a difference.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I am confused how yours differs from mine. I don't see how the last
> > matching tagged query would not be from an INSTEAD rule.
>
> You could have both INSTEAD and non-INSTEAD rules firing for the same
> original query. If the alphabetically-last rule is a non-INSTEAD rule,
> then there's a difference.

How do we get multiple rules on a query? I thought it was mostly
INSERT/UPDATE/DELETE, and those all operate on a single table.

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

On Thu, 3 Oct 2002 22:21:27 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>so I propose we handle
>INSTEAD rules this way: that we return the oid and tuple count of the
>last INSTEAD rule query with a tag matching the main query.

Bruce, this won't work for this example

>> CREATE RULE visible_delete AS -- DELETE rule
>> ON DELETE TO visible
>> DO INSTEAD
>> COUNT UPDATE table3
>> SET deleted = 1
>> WHERE pk = old.pk;

because here we don't have a rule query with a matching tag. Same
applies for

>> CREATE RULE v_update AS -- UPDATE rule
>> ON UPDATE TO v
>> DO INSTEAD NOTHING;

I wrote:
>> One argument against automatically "don't count non-INSTEAD rules and
>> count the last statement in INSTEAD rules"

Seems I introduced a little bit of confusion here by argueing against
something that has never been proposed before. Funny, that this
non-existent proposal is now seriously discussed :-(

Has the idea of extending the syntax to explicitly mark queries as
COUNTed already been rejected? If yes, I cannot help here. If no, I
keep telling you that this approach can emulate most of the other
possible solutions still under discussion.

Bruce wrote:
>If there is more than one matching tag in
>the INSTEAD rule, the user has the option to place the query he wants
>for the return at the end of the rule.

Are you sure this is always possible without unwanted side effects?

Servus
Manfred

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I am confused how yours differs from mine. I don't see how the last
> matching tagged query would not be from an INSTEAD rule.
>>
>> You could have both INSTEAD and non-INSTEAD rules firing for the same
>> original query. If the alphabetically-last rule is a non-INSTEAD rule,
>> then there's a difference.

> How do we get multiple rules on a query? I thought it was mostly
> INSERT/UPDATE/DELETE, and those all operate on a single table.

You can create as many rules as you want. One reasonably likely
scenario is that you have a view, you make an ON INSERT DO INSTEAD
rule to support insertions into the view (by inserting into some
underlying table(s) instead), and then you add some not-INSTEAD
rules to perform logging into other tables that aren't part of the
view but just keep track of activity.

You'd not want the logging activity to usurp the count result for this
setup, I think, even if it happened last. (Indeed, that might be
*necessary*, if for some reason it needed to access the rows inserted
into the view's base table.)

This approach would give us a general principle that applies in all
cases: not-INSTEAD rules don't affect the returned command result.
Perhaps that would answer Manfred's thought that we should be able
to label which rules affect the result. If you have any INSTEAD rules,
then it doesn't matter exactly how many you have, so you can mark them
INSTEAD or not to suit your fancy.

regards, tom lane

Tom Lane wrote:
> You can create as many rules as you want. One reasonably likely
> scenario is that you have a view, you make an ON INSERT DO INSTEAD
> rule to support insertions into the view (by inserting into some
> underlying table(s) instead), and then you add some not-INSTEAD
> rules to perform logging into other tables that aren't part of the
> view but just keep track of activity.
>
> You'd not want the logging activity to usurp the count result for this
> setup, I think, even if it happened last. (Indeed, that might be
> *necessary*, if for some reason it needed to access the rows inserted
> into the view's base table.)
>
> This approach would give us a general principle that applies in all
> cases: not-INSTEAD rules don't affect the returned command result.
> Perhaps that would answer Manfred's thought that we should be able
> to label which rules affect the result. If you have any INSTEAD rules,
> then it doesn't matter exactly how many you have, so you can mark them
> INSTEAD or not to suit your fancy.

Oh, I like that, and rules fire alphabetically, right?

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

Manfred Koizar wrote:
> On Thu, 3 Oct 2002 22:21:27 -0400 (EDT), Bruce Momjian
> <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> >so I propose we handle
> >INSTEAD rules this way: that we return the oid and tuple count of the
> >last INSTEAD rule query with a tag matching the main query.
>
> Bruce, this won't work for this example
>
> >> CREATE RULE visible_delete AS -- DELETE rule
> >> ON DELETE TO visible
> >> DO INSTEAD
> >> COUNT UPDATE table3
> >> SET deleted = 1
> >> WHERE pk = old.pk;
>
> because here we don't have a rule query with a matching tag. Same
> applies for

True, but because we have said we are going to return the tag of the
original command, I don't think we have anything valid to return in this
case to match the tag.

> >> CREATE RULE v_update AS -- UPDATE rule
> >> ON UPDATE TO v
> >> DO INSTEAD NOTHING;

This is OK because the default is return zeros.

> I wrote:
> >> One argument against automatically "don't count non-INSTEAD rules and
> >> count the last statement in INSTEAD rules"
>
> Seems I introduced a little bit of confusion here by argueing against
> something that has never been proposed before. Funny, that this
> non-existent proposal is now seriously discussed :-(
>
> Has the idea of extending the syntax to explicitly mark queries as
> COUNTed already been rejected? If yes, I cannot help here. If no, I

Well, I am hoping to find something that was automatic. If we do our
best, and we still get complains, we can add some syntax. I am
concerned that adding syntax is just over-designing something that isn't
necessary.

> keep telling you that this approach can emulate most of the other
> possible solutions still under discussion.
>
> Bruce wrote:
> >If there is more than one matching tag in
> >the INSTEAD rule, the user has the option to place the query he wants
> >for the return at the end of the rule.
>
> Are you sure this is always possible without unwanted side effects?

I am sure it isn't always possible, but let's do our best and see how
people react.

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

"Michael Paesold" <mpaesold(at)gmx(dot)at> writes:
> In a PL/pgSQL function I want to insert into a table and get the OID back.
> That usually works with
> GET DIAGNOSTICS last_oid = RESULT_OID;
> right after the insert statement.

> But if the table that I insert to has a rule (or perhaps a trigger?) that
> updates another table, the RESULT_OID after the insert will be 0 (zero).

As of CVS tip, this example produces the results I believe you want:

regression=# SELECT pltestfunc(10);
NOTICE: RESULT_OID: 282229
NOTICE: RESULT_OID: 282230
NOTICE: RESULT_OID: 282231
NOTICE: RESULT_OID: 282232
NOTICE: RESULT_OID: 282233
NOTICE: RESULT_OID: 282234
NOTICE: RESULT_OID: 282235
NOTICE: RESULT_OID: 282236
NOTICE: RESULT_OID: 282237
NOTICE: RESULT_OID: 282238
pltestfunc
------------
t
(1 row)

regression=# SELECT * FROM pltest;
id | t
----+------
1 | test
2 | test
3 | test
4 | test
5 | test
6 | test
7 | test
8 | test
9 | test
10 | test
(10 rows)

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Michael Paesold" <mpaesold(at)gmx(dot)at> writes:
> > In a PL/pgSQL function I want to insert into a table and get the OID
back.
> > That usually works with
> > GET DIAGNOSTICS last_oid = RESULT_OID;
> > right after the insert statement.
>
> > But if the table that I insert to has a rule (or perhaps a trigger?)
that
> > updates another table, the RESULT_OID after the insert will be 0 (zero).
>
> As of CVS tip, this example produces the results I believe you want:
>
> regression=# SELECT pltestfunc(10);
> NOTICE: RESULT_OID: 282229
> NOTICE: RESULT_OID: 282230
> NOTICE: RESULT_OID: 282231
...
[snip]

That is very good news. I appreciate that you got it fixed for 7.3. I am
sure I am only one of many who have use for that, but thanks anyway!

Best Regards,
Michael Paesold