Re: [HACKERS] Constraint Exclusion + Joins?

[Tom Lane]

"Brandon Black" <[EMAIL PROTECTED]> writes:
> Is this sort of dynamic constraint exclusion on the radar?

What sort of fantasy have you got in mind?

It doesn't seem likely to me that testing constraints explicitly against
each row from the other table would be a win compared to letting the
indexes do it.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Constraint Exclusion + Joins?

[Brandon Black]

On 4/30/06, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:

On Fri, 28 Apr 2006, Brandon Black wrote:

> I dug around in CVS to have a look for this, and I did eventually find
> it (well, I found the corresponding docs patch that removed the note
> about not working for joins).  I see it's in MAIN but not in
> 8_1_STABLE.  Does that mean it's headed for 8.2.x when that comes
> about? (Sorry, I'm not terribly familiar with how you guys handle all
> of this).

Yes.



Perhaps I'm confused about the meaning of the removal of the
JOINs-related caveat from the constraint exclusion docs in MAIN.  What
I was intending to ask about was constraint exclusion kicking in where
the constrained column is being joined to a column of another table,
with no constants involved.

For a contrived example:

--

CREATE TABLE basic (
  basic_id INTEGER NOT NULL PRIMARY KEY,
  basic_data TEXT
);

CREATE TABLE basic_sub1 (
 PRIMARY KEY (basic_id),
 CHECK ( basic_id >= 0 AND basic_id < 100 )
) INHERITS (basic);

CREATE TABLE basic_sub2 (
 PRIMARY KEY (basic_id),
 CHECK ( basic_id >= 100 AND basic_id < 200 )
) INHERITS (basic);

[...]

CREATE TABLE jstuff (
   jstuff_id INTEGER NOT NULL PRIMARY KEY,
   jstuff_data TEXT
);

EXPLAIN ANALYZE SELECT basic.* FROM basic JOIN jstuff ON
(basic.basic_id = jstuff.jstuff_id) WHERE jstuff_data = 'foo';

--

I tried things like the above with small test data sets against cvs
just now on my home machine, and constraint exclusion doesn't seem to
apply here (even if all of the joined jstuff rows have ids which only
match the constraint for basic_sub3, all basic_subX's seem to get
scanned, as is the case I'm seeing in my real code against 8.1.3).  Is
this sort of dynamic constraint exclusion on the radar?

Without it, some inheritance-based partitioning constructs which could
otherwise be written as a single query have to be done as loops with
seperate nested statements (to extract constants and then manually
plug them into the next statement down the chain), which  seems to
make it more difficult (or nearly impossible) to support varying
where/grouping/ordering/count of the resultant inner query rows from
client code efficiently without writing a seperate plpgsql function
for every possible variation.

-- Brandon

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Tom Lane]

Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> One argument against it is that it'd break trying to log who-did-what
>> by the expedient of having a column default CURRENT_USER:
>>  blame_me text default current_user

> No reason there couldn't be a separate function that returns the _actual_ user
> rather than the effective user.

... except that the SQL spec says the above should work.  In any case,
if you're thinking of SESSION_USER, that's not really the right thing
either.  Imagine that the INSERT is being executed by a SECURITY DEFINER
function --- the owner of the function, not the session user, is really
the one that should be "blamed" with the insert.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Greg Stark]

Tom Lane <[EMAIL PROTECTED]> writes:

> One argument against it is that it'd break trying to log who-did-what
> by the expedient of having a column default CURRENT_USER:
>   blame_me text default current_user

No reason there couldn't be a separate function that returns the _actual_ user
rather than the effective user. Sort of like -- well actually the unix
precedents here are more confusing than helpful.


-- 
greg


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] InsertXLogFile in pg_resetxlog

[Jonah H. Harris]

Just to update everyone, I've refactored a good amount of the
rebuild-control-values-from-WAL code and should have it ready for
-patches tomorrow.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[elein]

On Mon, May 01, 2006 at 07:47:06PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > I think a big point that's being missed here is that SERIAL *is* trying
> > to be simple. If you need something more sophisticated or complex you
> > shouldn't be using SERIAL at all, you should be doing the stuff
> > yourself, by hand.
> 
> I agree with this point in the abstract, but one important proviso is
> that it has to be *possible* to do it by hand.  One good thing about
> the "SERIAL is just a macro" approach is that it keeps us honest about
> making sure that SERIAL isn't exploiting any weird internal behaviors
> that are hard to duplicate for handmade sequence defaults.  We've
> already broken that to some extent by having the hidden dependency,
> and that in turn means that fairly-reasonable expectations like
> "pg_get_serial_sequence should find the column's associated sequence"
> don't work on handmade sequences.  I don't want to go much further in
> that direction.  If there's a usability problem we're trying to solve
> for SERIALs, we should make sure the problem gets solved for handmade
> sequences too.
> 
>   regards, tom lane

I agree with Tom's proviso and add one of my own, mentioned earlier.
It should be easy to use a sequence w/alter sequence almost all of
the time.  The majority of the crowd should be able to use SERIAL in
the majority of cases.  One reason I am adamant about this is the
v. useful dependencies that are (should be) set between the table 
and the sequence when it is declared as a SERIAL.

--elein

> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Christopher Kings-Lynne]

Sure, but there's no reason that would couldn't allow that with a true
black-box SERIAL, either. In  fact, you can do it today if you want,
just by creating a wrapper around nextval(pg_get_serial_sequence()).


Or just use lastval()

Chris


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote:
>> [EMAIL PROTECTED] writes:
>>> Ah. I was wondering about that. When I saw the first poster tag
>>> 'SECURITY DEFINER' on the end of the expression I assumed it was
>>> something that I didn't know you could do... :-)
>> 
>> No, he was inventing syntax that doesn't exist.

> Which begs the question, how hard would it be to add that syntax?

Well, we could.  The arguments against would come down to (a) nonstandard
syntax, and (b) possibly needing to make SECURITY a more-reserved word.
(We could avoid point (b) by using something that's already pretty
reserved --- one idea that comes to mind is DEFAULT ... AS OWNER.)

The discussion I was having with Bruno this morning essentially amounted
to doing this automatically, rather than having syntax to enable it.
I guess that backwards compatibility and spec compatibility might be
good arguments for not doing it automatically, though.

I could live with something like this if there's not major objections
out there.

BTW, has anyone looked into whether any of the other major DBs have
something similar?  You'd think anyone with sequence-like objects
would have run into this issue.  If there is precedent we might want
to follow it.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I think a big point that's being missed here is that SERIAL *is* trying
> to be simple. If you need something more sophisticated or complex you
> shouldn't be using SERIAL at all, you should be doing the stuff
> yourself, by hand.

I agree with this point in the abstract, but one important proviso is
that it has to be *possible* to do it by hand.  One good thing about
the "SERIAL is just a macro" approach is that it keeps us honest about
making sure that SERIAL isn't exploiting any weird internal behaviors
that are hard to duplicate for handmade sequence defaults.  We've
already broken that to some extent by having the hidden dependency,
and that in turn means that fairly-reasonable expectations like
"pg_get_serial_sequence should find the column's associated sequence"
don't work on handmade sequences.  I don't want to go much further in
that direction.  If there's a usability problem we're trying to solve
for SERIALs, we should make sure the problem gets solved for handmade
sequences too.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Lukas Smith]

Jim C. Nasby wrote:

On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote:

[EMAIL PROTECTED] writes:

Ah. I was wondering about that. When I saw the first poster tag
'SECURITY DEFINER' on the end of the expression I assumed it was
something that I didn't know you could do... :-)

No, he was inventing syntax that doesn't exist.


Which begs the question, how hard would it be to add that syntax? I
suspect it would be useful in cases besides sequences, and certainly
seems to be a lot less of a hassle than having to wrap stuff in an extra
function just to get that capability...


In all the internal purity and technical concerns it helps PostGreSQL to 
have an easy migration path for MySQL refugees. Anyways I think its 
quite clear that there is more need for a black box than a macro.


regards,
Lukas

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Jim C. Nasby]

On Mon, May 01, 2006 at 11:25:33AM -0400, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > Ah. I was wondering about that. When I saw the first poster tag
> > 'SECURITY DEFINER' on the end of the expression I assumed it was
> > something that I didn't know you could do... :-)
> 
> No, he was inventing syntax that doesn't exist.

Which begs the question, how hard would it be to add that syntax? I
suspect it would be useful in cases besides sequences, and certainly
seems to be a lot less of a hassle than having to wrap stuff in an extra
function just to get that capability...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Jim C. Nasby]

On Sun, Apr 30, 2006 at 11:45:14AM +0200, Svenne Krap wrote:
> Tom Lane wrote:
> >In short, I think there's a reasonably good case to be made for losing the
> >hidden dependency and re-adopting the viewpoint that saying SERIAL is
> >*exactly* the same as making a sequence and then making a default
> >expression that uses the sequence.  Nothing behind the curtain.
> >  
> I speak more as a user than a hacker, but I do still lurk here ;)
> 
> The way sequences are handled is imho one of the strongest features. The 
> possiblity to query nextval is bordering on divine.

Sure, but there's no reason that would couldn't allow that with a true
black-box SERIAL, either. In  fact, you can do it today if you want,
just by creating a wrapper around nextval(pg_get_serial_sequence()).
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Logging pg_autovacuum

[Larry Rosenman]

Larry Rosenman wrote:
> Jim C. Nasby wrote:
>> On Mon, May 01, 2006 at 12:28:21PM -0500, Larry Rosenman wrote:
>>> Since both vacuum and autovacuum will be cutting stats records, do
>>> we want to just have the autovacuum
>>> stats record have the fact that it was autovacuum that did the
>>> vacuum? 
>>> 
>>> Or, is there a way when vacuum is run by autovacuum that I can get a
>>> flag to set that says this (vacuum|analyze) was done by the
>>> autovacuum daemon? 
>>> 
>>> I agree that the existing stats calls are good, but I'm still
>>> reading code to see whether I can determine
>>> at the time they are cut that this was autovacuum that did it.
>> 
>> I think noting autovac vacuums/analyzes seperately is
pg-dev/vacuum-time-patch-WIP.txt'nice-to-have'
>> but not all that important. It'd probably be pretty easy to tell the
>> difference just knowing what (if any) manual vacuums your system
>> runs. 
>> 
>> While we're looking at logging, are you going to add stats stuff for
>> the bgwriter as well, or should we add this to the TODO?
> 
> I was going to do that after I got some comfort with what I'm doing
> here.

I've put a WIP patch up for comments:

http://www.lerctr.org/~ler/pg-dev/vacuum-time-patch-WIP.txt

this is *NOT* for application, as I still need to add access to the new
fields to
the views, etc.  

I'm looking to get comments on it. 





-- 
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Jim C. Nasby]

On Sun, Apr 30, 2006 at 01:42:37PM +0300, Hannu Krosing wrote:
> > I do see the benefits with regard to simplified implementation, and
> > flexibility.
> > 
> > As a compromise, I could see either choice being correct. I don't
> > see either direction as being both user friendly and simple.
> 
> You can be user friendly and simple only if the user wants to do simple
> things, or if you can exactly predict what a user wants, else you have
> to grant some power to the user, and that involves complexity or at
> least a learning curve.

I think a big point that's being missed here is that SERIAL *is* trying
to be simple. If you need something more sophisticated or complex you
shouldn't be using SERIAL at all, you should be doing the stuff
yourself, by hand. 99% of the time people just need a nice, simple
autonumber field that behaves as expected. Namely, you don't have to
assign seperate permissions for it, and when you drop the table or
column, you don't end up with some other 'hidden' thing still hanging
around.

In other words, I think we should actually be moving towards #1, and
only allow 'tweaking under the hood' that makes good sense.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Automatic free space map filling

[Dawid Kuroczko]

On 5/1/06, Martijn van Oosterhout  wrote:
On Mon, May 01, 2006 at 01:19:30PM -0500, Jim C. Nasby wrote:> ISTM that tying this directly to maintenance_work_mem is a bit> confusing, since the idea is to keep vacuum transaction duration down so> that it isn't causing dead tuples to build up itself. It seems like it
> would be better to have vacuum start a fresh transaction after a certain> number of tuples have died. But since there's no way to actually measure> that without having row level stats turned on, maybe number of
> transactions or length of time would be good surrogates.AIUI, vacuum starts a fresh cycle because it's accumulated a certainnumber of dead tuples to clean up. Isn't that what you're asking for?maintenance_work_mem is the limit on the amount of deleted tuple
information that can be stored (amongst other things I'm sure)...Hmm, one idea, which may (or may not) be interesting for largetable vacuum is allowing a syntax similar to:VACUUM table WHERE some_col > now()-'1 hour'::interval;
I.e. Let vacuum run "piggyback" on some index.  This would allowfor a quick vacuum of a fraction of a large table.  Especially whenthe table is large, and only some data (new data) are being modified.
The vacuum for such a table would: 1. scan the index accoriding to the where criteria and create bitmap  of blocks to look at. 2. go through these blocks and vacuum them.Hmm, another perhaps silly idea -- a special index kind for tracking
tuple deaths.  Ie -- something like whenever tuple is updated/deleted,insert an entry into such index, using last session the tuple is visiblefor as a key.  Then, perhaps, vacuum could scan such an index and
find tuples which are candidates for removal.  I lack the knowledge ofPostgreSQL's internals, so forgive me if I am writing somethingcompletely insane. :)   Regards,   Dawid

Re: [HACKERS] InsertXLogFile in pg_resetxlog

[Jonah H. Harris]

On 5/1/06, Tom Lane <[EMAIL PROTECTED]> wrote:

Definitely bad, very bad.  Please put back the lock-checking code.


That's what I was thinking.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] InsertXLogFile in pg_resetxlog

[Tom Lane]

"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> While refactoring the patch, I've noticed that this patch allowed
> pg_resetxlog to proceed while the server could potentially be up... is
> this the desired behavior or should we require the lock file to be
> removed first (as it was prior to this patch)?

Definitely bad, very bad.  Please put back the lock-checking code.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] InsertXLogFile in pg_resetxlog

[Jonah H. Harris]

On 5/1/06, Jonah H. Harris <[EMAIL PROTECTED]> wrote:

I just scanned it, and it's pretty ugly overall.  Did one of you guys
want to clean it up?  If not, I'll do it today.


While refactoring the patch, I've noticed that this patch allowed
pg_resetxlog to proceed while the server could potentially be up... is
this the desired behavior or should we require the lock file to be
removed first (as it was prior to this patch)?

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Automatic free space map filling

[Martijn van Oosterhout]

On Mon, May 01, 2006 at 01:19:30PM -0500, Jim C. Nasby wrote:
> ISTM that tying this directly to maintenance_work_mem is a bit
> confusing, since the idea is to keep vacuum transaction duration down so
> that it isn't causing dead tuples to build up itself. It seems like it
> would be better to have vacuum start a fresh transaction after a certain
> number of tuples have died. But since there's no way to actually measure
> that without having row level stats turned on, maybe number of
> transactions or length of time would be good surrogates.

AIUI, vacuum starts a fresh cycle because it's accumulated a certain
number of dead tuples to clean up. Isn't that what you're asking for?
maintenance_work_mem is the limit on the amount of deleted tuple
information that can be stored (amongst other things I'm sure)...

> Since it sounds like we'd want the transaction to start only at the
> start of a clean cycle it could just check the limits at the start of
> each cycle. That would prevent it from wrapping the vacuum of each small
> table with a (rather pointless) new transaction.

Every table has to be in its own transaction since thats the duration
of the locks. Vacuum handling multiple tables in one transaction leaves
you open to deadlocks.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] Automatic free space map filling

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
>>> Alvaro Herrera <[EMAIL PROTECTED]> writes:
 So for you it would certainly help a lot to be able to vacuum the first
 X pages of the big table, stop, release locks, create new transaction,
 continue with the next X pages, lather, rinse, repeat.

>>> Bruce and I were discussing this the other day; it'd be pretty easy to
>>> make plain VACUUM start a fresh transaction immediately after it
>>> finishes a scan heap/clean indexes/clean heap cycle.

> Except that wouldn't help when vacuuming a lot of small tables; each one
> would get it's own transaction.

What's your point?  There's only a problem for big tables, and VACUUM
already does use a new transaction for each table.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Automatic free space map filling

[Jim C. Nasby]

On Fri, Apr 28, 2006 at 03:58:16PM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > > So for you it would certainly help a lot to be able to vacuum the first
> > > X pages of the big table, stop, release locks, create new transaction,
> > > continue with the next X pages, lather, rinse, repeat.
> > 
> > > This is perfectly doable, it only needs enough motivation from a
> > > knowledgeable person.
> > 
> > Bruce and I were discussing this the other day; it'd be pretty easy to
> > make plain VACUUM start a fresh transaction immediately after it
> > finishes a scan heap/clean indexes/clean heap cycle.  The infrastructure
> > for this (in particular, session-level locks that won't be lost by
> > closing the xact) is all there.  You'd have to figure out how often to
> > start a new xact ... every cycle is probably too often, at least for
> > smaller maintenance_work_mem settings ... but it'd not be hard or
> > involve any strange changes in system semantics.
> 
> Should this be a TODO?  One item of discussion was taht people should
> just increase their workmem so the job can be done faster in larger
> batches.

Except that wouldn't help when vacuuming a lot of small tables; each one
would get it's own transaction.

ISTM that tying this directly to maintenance_work_mem is a bit
confusing, since the idea is to keep vacuum transaction duration down so
that it isn't causing dead tuples to build up itself. It seems like it
would be better to have vacuum start a fresh transaction after a certain
number of tuples have died. But since there's no way to actually measure
that without having row level stats turned on, maybe number of
transactions or length of time would be good surrogates.

Since it sounds like we'd want the transaction to start only at the
start of a clean cycle it could just check the limits at the start of
each cycle. That would prevent it from wrapping the vacuum of each small
table with a (rather pointless) new transaction.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Logging pg_autovacuum

[Larry Rosenman]

Jim C. Nasby wrote:
> On Mon, May 01, 2006 at 12:28:21PM -0500, Larry Rosenman wrote:
>> Since both vacuum and autovacuum will be cutting stats records, do we
>> want to just have the autovacuum
>> stats record have the fact that it was autovacuum that did the
>> vacuum? 
>> 
>> Or, is there a way when vacuum is run by autovacuum that I can get a
>> flag to set that says this (vacuum|analyze) was done by the
>> autovacuum daemon? 
>> 
>> I agree that the existing stats calls are good, but I'm still reading
>> code to see whether I can determine
>> at the time they are cut that this was autovacuum that did it.
> 
> I think noting autovac vacuums/analyzes seperately is 'nice-to-have'
> but not all that important. It'd probably be pretty easy to tell the
> difference just knowing what (if any) manual vacuums your system runs.
> 
> While we're looking at logging, are you going to add stats stuff for
> the bgwriter as well, or should we add this to the TODO?

I was going to do that after I got some comfort with what I'm doing
here. 



-- 
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX  78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: [EMAIL PROTECTED]
Web: www.pervasive.com

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] CVSup server seems to be down

[Marc G. Fournier]

On Mon, 1 May 2006, Alvaro Herrera wrote:


Hi,

Since yesterday (at least), the CVSup server has been down:

$ cvsup -g cvsup.pgsql
Cannot connect to cvsup.postgresql.org: Connection refused
Will retry at 14:08:50


I hope it has not been deprecated?


nope, just upgrade postgresql.org to FreeBSD 6.x/amd64 ... should be fixed 
now ...



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] Logging pg_autovacuum

[Jim C. Nasby]

On Mon, May 01, 2006 at 12:28:21PM -0500, Larry Rosenman wrote:
> Since both vacuum and autovacuum will be cutting stats records, do we
> want to just have the autovacuum
> stats record have the fact that it was autovacuum that did the vacuum?
> 
> Or, is there a way when vacuum is run by autovacuum that I can get a
> flag to set that says this
> (vacuum|analyze) was done by the autovacuum daemon?
> 
> I agree that the existing stats calls are good, but I'm still reading
> code to see whether I can determine
> at the time they are cut that this was autovacuum that did it.

I think noting autovac vacuums/analyzes seperately is 'nice-to-have' but
not all that important. It'd probably be pretty easy to tell the
difference just knowing what (if any) manual vacuums your system runs.

While we're looking at logging, are you going to add stats stuff for the
bgwriter as well, or should we add this to the TODO?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Logging pg_autovacuum

[Jim C. Nasby]

On Sat, Apr 29, 2006 at 10:05:51AM -0700, Mark Dilger wrote:
> Should we make the whole postgres logging system configurable, similar to 
> log4j
> (or log4perl) rather than special-casing the autovacuum logs?  Do we want to 
> see
> options added piecemeal to the conf file such as "autovacuum_messages=silent"?

This does sound like a better idea, especially since there's call for
improving bgwriter and stats collector logging as well.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] CVSup server seems to be down

[Alvaro Herrera]

Hi,

Since yesterday (at least), the CVSup server has been down:

$ cvsup -g cvsup.pgsql 
Cannot connect to cvsup.postgresql.org: Connection refused
Will retry at 14:08:50


I hope it has not been deprecated?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Logging pg_autovacuum

[Jim C. Nasby]

On Thu, Apr 27, 2006 at 08:12:23PM +, Chris Browne wrote:
> Having some ability to collect statistics about "we recovered 42 pages
> from table foo at 12:45" would seem useful both from an immediate
> temporal perspective where it could suggest whether specific tables
> were being vacuumed too (seldom|often), and from a more
> global/analytic perspective of perhaps suggesting better kinds of
> vacuuming policies.  (In much the same way that I'd like to have some
> way of moving towards an analytically better value for
> default_statistics_target than 10...)
> 
> If people are interested, I could provide a copy of the "analyze
> VACUUM stats" script...

Yeah, I'm interested. Though just like EXPLAIN output this seems like
another case where having the ability to log this information into the
database itself would be incredibly valuable for anyone wanting to do
later analysis.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Logging pg_autovacuum

[Larry Rosenman]

Martijn van Oosterhout wrote:
> On Fri, Apr 28, 2006 at 04:08:41PM -0400, Robert Treat wrote:
>> The first is to add a column(s) to pg_class to hold last
>> vaccum/analyze time 
>> for each table.  The upsides would be that this puts the information
>> in a readily accessable place that can be viewed from third party
>> tools and 
>> queried against for easier management along with accomplishing what
>> the 
>> current logging is giving you.
> 
> You know, rather than adding new columns to pg_class, why not extend
> the stats collector to collect this information. This seems to fall
> well within its mandate and it already has the necessary
> infrastructure to store info like this. The people likely to want
> this sort of info are likely to be running the stats collector
> anyway. The GUC then becomes.
> 
> stats_autovacuum= on
> 
> No more fiddling with log levels or parsing log file, just:
> 
> select  from pg_stats_autovacuum;
> 
> If this table is empty, it isn't running. How often do you want to
> know if it was running 5 hours ago?
> 
> Thoughts?
[put just to the list, unless folks still want personal CC's.].

Since both vacuum and autovacuum will be cutting stats records, do we
want to just have the autovacuum
stats record have the fact that it was autovacuum that did the vacuum?

Or, is there a way when vacuum is run by autovacuum that I can get a
flag to set that says this
(vacuum|analyze) was done by the autovacuum daemon?

I agree that the existing stats calls are good, but I'm still reading
code to see whether I can determine
at the time they are cut that this was autovacuum that did it.

Thanks for the nice introductory project :) 

LER 




-- 
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX  78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: [EMAIL PROTECTED]
Web: www.pervasive.com

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Tom Lane]

Bruno Wolff III <[EMAIL PROTECTED]> writes:
> The summary is that I was suggesting that default expressions, triggers and
> constraints should all run as the table owner instead of the invoker as
> there was little use for them to need the access of the invoker, while there
> was benefit in having them run as the owner.

I can see doing this for defaults and constraints, but there is a serious
objection for triggers: you could not use a trigger with
new.blame_me := current_user;
as a more-bulletproof implementation of the tracking column I was on
about before.  Furthermore, there already is a way to express the
desired behavior for triggers (when it is in fact the desired behavior):
make the trigger function SECURITY DEFINER.

> In addition there is a mild security issue in that default expressions
> and constraints could be used as trojans so that inserting data into a
> table could allow that table owner the ability to do things they
> shouldn't be doing to the invoker's table.

This risk would exist anyway unless you put strange constraints on
RESET ROLE.  It's in general no problem to get back to the outermost
level's security settings.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Bruno Wolff III]

I went back to see if I could find the discussion about this in the past.
It was less than I thought. Most it was me posting with some feedback from
Rod Taylor. The thread started with the subject "What user to defaults execute
as?" on general, but I mutated the subject to "setuid for defaults, constraints
and triggers (Was: What user to [sic] defaults execute as?)".

The summary is that I was suggesting that default expressions, triggers and
constraints should all run as the table owner instead of the invoker as
there was little use for them to need the access of the invoker, while there
was benefit in having them run as the owner. In addition there is a mild
security issue in that default expressions and constraints could be used as
trojans so that inserting data into a table could allow that table owner the
ability to do things they shouldn't be doing to the invoker's table. Though
in practice anyone granted to the ability to create functions (which you need
to exploit this) is already very highly trusted.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] InsertXLogFile in pg_resetxlog

[Jonah H. Harris]

On 5/1/06, Tom Lane <[EMAIL PROTECTED]> wrote:

This certainly looks like it was written by someone who'd just learned
about lists yesterday :-(.  I wonder how many other problems there are
in that resetxlog patch?  I didn't bother to look at it at all myself.
Anyone have time to review it?


I just scanned it, and it's pretty ugly overall.  Did one of you guys
want to clean it up?  If not, I'll do it today.


--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] InsertXLogFile in pg_resetxlog

[Tom Lane]

Martijn van Oosterhout  writes:
> May I propose the entire part of that function after the comment /* the
> list is empty. */ be replaced with something like the following (or
> whatever idiom people prefer for singly-linked lists):

This certainly looks like it was written by someone who'd just learned
about lists yesterday :-(.  I wonder how many other problems there are
in that resetxlog patch?  I didn't bother to look at it at all myself.
Anyone have time to review it?

http://archives.postgresql.org/pgsql-committers/2006-04/msg00299.php

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Tom Lane]

[EMAIL PROTECTED] writes:
> Ah. I was wondering about that. When I saw the first poster tag
> 'SECURITY DEFINER' on the end of the expression I assumed it was
> something that I didn't know you could do... :-)

No, he was inventing syntax that doesn't exist.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[mark]

On Mon, May 01, 2006 at 11:18:13AM -0400, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > If the user is specifying the default expression, they can specify
> > SECURITY DEFINER themselves, yes?
> Not unless they write a wrapper function to be a security definer
> and call nextval().

Ah. I was wondering about that. When I saw the first poster tag
'SECURITY DEFINER' on the end of the expression I assumed it was
something that I didn't know you could do... :-)

mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Tom Lane]

[EMAIL PROTECTED] writes:
> If the user is specifying the default expression, they can specify
> SECURITY DEFINER themselves, yes?

Not unless they write a wrapper function to be a security definer
and call nextval().

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[mark]

On Mon, May 01, 2006 at 10:29:12AM -0400, Tom Lane wrote:
> A cheesy compromise would be to switch userid for default-evaluation
> only if the expression contains any volatile functions.  I find this
> idea pretty ugly, but it would allow us to still behave per-spec
> for CURRENT_USER while getting the results we want for nextval().
> (current_user() is marked "stable".)

If the user is specifying the default expression, they can specify
SECURITY DEFINER themselves, yes?

So it's really only the default definition of 'SERIAL' columns for
new tables. SERIAL isn't per-spec, yes? So it could change in 8.2
without problem?

Cheers,
mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] RELKIND_SPECIAL

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> How about we remove RELKIND_SPECIAL?  It was there only to support
> the XactLockTable hack, but we don't need that anymore.

Go for it.  Don't forget to remove the documentation mentions
(catalog.sgml at least).  Might be a good idea to leave the code
for the case in psql/describe.c, though, just so psql doesn't choke
if run on an old database?

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] Is a SERIAL column a "black box", or not?

[Tom Lane]

Bruno Wolff III <[EMAIL PROTECTED]> writes:
> I suggested a long time ago that default expressions should always be
> executed as the owner of the table. This got shot down, but I don't remember
> if it was because people thought the idea was bad in itself or if it was
> the work involved (which I wasn't in a position to do).

The more I think about it the better I like that idea.  It seems like a
natural and unsurprising semantics, whereas ideas involving implicit
GRANTs seem to me to violate the principle of least surprise.  It fixes
the problem for both serial and handmade sequences --- indeed, it fixes
related problems for functions other than nextval().  And it doesn't
require introduction of any new syntax.

One argument against it is that it'd break trying to log who-did-what
by the expedient of having a column default CURRENT_USER:
blame_me text default current_user
You could still make use of session_user for this, but that's not really
the right thing if the INSERT is being done from a security-definer
function.  I don't find this objection very compelling, because such a
default is pretty fragile anyway: it could be broken just by assigning
explicitly to the column.  You'd be better off doing the logging by
having a BEFORE trigger that sets the column value.  However, I suspect
that the SQL spec demands that such a default behave as it currently
does, which means that changing this would violate spec.

A cheesy compromise would be to switch userid for default-evaluation
only if the expression contains any volatile functions.  I find this
idea pretty ugly, but it would allow us to still behave per-spec
for CURRENT_USER while getting the results we want for nextval().
(current_user() is marked "stable".)

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] WITH/WITH RECURSIVE implementation discussion

[Jonah H. Harris]

On 5/1/06, Mark Cave-Ayland <[EMAIL PROTECTED]> wrote:

The latest discussion I found related to this appears to be here:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg00564.php which
indicates that Jonah is hoping to work on this for 8.2, but I don't see this
item as being worked on in the TODO list - does that mean that development
on this has halted?


No, in between all my other work here at EnterpriseDB, I'm still
working on it.  I hope to get it submitted to -patches for review in
the next two weeks.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[HACKERS] WITH/WITH RECURSIVE implementation discussion

[Mark Cave-Ayland]

Hi folks,

Looking ahead at some of the projects I'll be working on in the future, I'm
seeing that having an implementation of WITH/WITH RECURSIVE for working with
tree structures is going to be a very useful feature and so would like to
re-start the discussions on this to see whether this could be achieved for
PG 8.2.

The latest discussion I found related to this appears to be here:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg00564.php which
indicates that Jonah is hoping to work on this for 8.2, but I don't see this
item as being worked on in the TODO list - does that mean that development
on this has halted?


Kind regards,

Mark.


WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] inet increment with int

[Patrick Welche]

On Sat, Apr 29, 2006 at 10:24:48PM -0400, Bruce Momjian wrote:
> 
> FYI, 8.2 will have this and more based on this applied patch:
>   
>   Add INET/CIDR operators: and, or, not, plus int8, minus int8, and inet
>   minus inet.

I know, I'm already using it :-)

Thanks,

Patrick

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] InsertXLogFile in pg_resetxlog

[Martijn van Oosterhout]

There's been some new code added to pg_resetxlog which is confusing
enough that Coverity is convinced there's a possible memory leak in
InsertXLogFile. I think it may actually be a bug. At the least this bit
needs rewriting to make it clearer what it does.

What I think happens is this:

1. Assume the xlogfilelist has more than two entries already
2. In the loop CmpXLogFileOT returns true the first time, false the
second

At this point Prev = xlogfilelist and Curr = xlogfilelist->next and
append2end = false. With these conditions all if tests fail and the
file is never linked into the list.

May I propose the entire part of that function after the comment /* the
list is empty. */ be replaced with something like the following (or
whatever idiom people prefer for singly-linked lists):

--- cut ---
/* currp points to memory location where the pointer needs to be updated */
XLogFileName **currp = &xlogfilelist;

while( *currp && CmpXLogFileOT( NewSegFile, *currp ) )
currp = &( (*currp)->next );

NewSegFile->next = *currp;
*currp = NewSegFile;
--- cut ---

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature