Re: [HACKERS] functional call named notation clashes with SQL feature

[Joseph Adams]

On Fri, Jun 4, 2010 at 9:55 PM, Joseph Adams  wrote:
> If I had to choose between => and := for parameter naming, I'd go with
> := because it seems more SQLish to me.

On second thought, => might actually be a very intuitive syntax for
defining dictionary types like hstore and json, since it matches PHP's
associative array syntax, as in:

hstore('key1' => 'value1', 'key2' => 'value2') -- hypothetical SQL
array('key1' => 'value1', 'key2' => 'value2') // PHP

That way, when people see =>, they can think "dictionary" whether
they're in PHP or SQL.

Note that this is a bit different than what I suggested earlier:

hstore(key1 => 'value1', key2 => 'value2')

Identifier names were used instead of literal names, which conflicts
with the other approach.  Also, the other approach is more flexible,
as the user can generate names with expressions at runtime.

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

Re: [HACKERS] recovery getting interrupted is not so unusual as it used to be

[Greg Stark]

On Sat, Jun 5, 2010 at 2:20 AM, Robert Haas  wrote:
>> I've tried to keep this as similar as possible to the existing message while 
>> making it less ambiguous about cause and effect.
>>
>> "If this has occurred more than once corrupt data might be the cause and you 
>> might need to choose an earlier recovery target".

> If the database system is exiting unexpectedly during archive
> recovery, some data might be corrupted and you might need to choose an
> earlier recovery target.

I think you've missed the key addition in Florian's suggestions. The
"might be the cause" tips the user off to what's going on. Your
statement is just as ambiguous as the original message in that it
could be (and usually would be) read as saying that the interruption
of recovery could cause the corruption.

I would probably write it as "If this is happening repeatedly it might
be caused by corrupt data. Try choosing an earlier recovery target
prior to the corruption.". Florian's phrasing seemed ok to me too
though.


-- 
greg

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

Re: [HACKERS] functional call named notation clashes with SQL feature

[Joseph Adams]

On Wed, May 26, 2010 at 9:28 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Wed, May 26, 2010 at 8:21 PM, Tom Lane  wrote:
>>> If we go with the spec's syntax I think we'd have no realistic choice
>>> except to forbid => altogether as an operator name.  (And no, I'm not
>>> for that.)
>
>> I suppose the most painful thing about doing that is that it would
>> break hstore.  Are there other commonly-used modules that rely on =>
>> as an operator name?
>
> There don't seem to be any other contrib modules that define => as an
> operator name, but I'm not sure what's out there on pgfoundry or
> elsewhere.  The bigger issue to me is not so much hstore itself as that
> this is an awfully attractive operator name for anything container-ish.
> Wasn't the JSON-datatype proposal using => for an operator at one stage?
> (The current wiki page for it doesn't seem to reflect any such idea,
> though.)  And I think I remember Oleg & Teodor proposing such an
> operator in conjunction with some GIN-related idea or other.
>
>> In spite of the difficulties, I'm reluctant to give up on it.  I
>> always thought that the "AS" syntax was a crock and I'm not eager to
>> invent another crock to replace it.  Being compatible with the SQL
>> standard and with Oracle is not to be taken lightly.
>
> Yeah, I know.  Though this could end up being one of the bits of the
> spec that we politely decline to follow, like upper-casing identifiers.
> Still, it's a good idea to think again before we've set the release
> in stone ...
>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

I didn't really consider using => for json because it would interfere
with hstore (one of the signatures is text => text returns hstore, for
instance).  I am considering using -> as a json subscript operator
(which is what hstore does) as it shouldn't interfere with hstore (as
far as I know).

Here's a thought:  suppose we did use the foo (name => value) syntax
for naming parameters.  It could still be used in a very similar way
for hstore:

hstore(key1 => 'value1', key2 => 'value2')

One advantage here is that => wouldn't be exclusive to hstore anymore.  E.g.:

json(key1 => 'value1', key2 => 'value2')

However, note that the left hand of => is an identifier here, whereas
the left hand of hstore's current => operator is either text, text[],
or hstore.

If I had to choose between => and := for parameter naming, I'd go with
:= because it seems more SQLish to me.

I wonder if the foo (name : value) syntax would be possible/desirable.
 Or maybe foo ({name: value}) :-)


Joey Adams

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

Re: [HACKERS] recovery getting interrupted is not so unusual as it used to be

[Robert Haas]

On Fri, Jun 4, 2010 at 8:21 PM, Florian Pflug  wrote:
> On Jun 3, 2010, at 5:25 , Robert Haas wrote:
>> On Wed, Jun 2, 2010 at 10:34 PM, Florian Pflug  wrote:
 Oh.  Well, if that's the case, then I guess I lean toward applying the
 patch as-is.  Then there's no need for the caveat "and without manual
 intervention".
>>>
>>> That still leaves the messages awfully ambiguous concerning the cause (data 
>>> corruption) and the effect (crash during recovery).
>>>
>>> How about
>>> "If this has occurred more than once, it is probably caused by corrupt data 
>>> and you have to use the latest backup for recovery"
>>> for the crash recovery case and
>>> "If this has occurred more than once, it is probably caused by corrupt data 
>>> and you have to choose an earlier recovery target"
>>> for the PITR case.
>>>
>>> I don't see why currently only the PITR-case includes the "more than once" 
>>> clause. Its probably supposed to prevent unnecessarily alarming the user if 
>>> the "crash" was in fact a stray SIGKILL or an out-of-memory condition, 
>>> which seems equally likely in both cases.
>>
>> I've applied the patch for now - we can fix the wording of the other
>> messages with a follow-on patch if we agree on what they should say.
>> I don't like the use of the phrase "you have to", particularly...  I
>> would tend to leave the archive recovery message alone and change the
>> crash recovery message to be more like it.
>
> Since a loose log of this shed gave me quite a bump on my forehead once, one 
> last attempt at fixing it.
>
> I've tried to keep this as similar as possible to the existing message while 
> making it less ambiguous about cause and effect.
>
> "If this has occurred more than once corrupt data might be the cause and you 
> might need to choose an earlier recovery target".
> and
> "If this has occurred more than once corrupt data might be the cause and you 
> might need to restore from backup".

How about:

If the database system is exiting unexpectedly during archive
recovery, some data might be corrupted and you might need to choose an
earlier recovery target.
If the database system is exiting unexpectedly during crash recovery,
some data might be corrupted and you might need to restore from
backup.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] recovery getting interrupted is not so unusual as it used to be

[Florian Pflug]

On Jun 3, 2010, at 5:25 , Robert Haas wrote:
> On Wed, Jun 2, 2010 at 10:34 PM, Florian Pflug  wrote:
>>> Oh.  Well, if that's the case, then I guess I lean toward applying the
>>> patch as-is.  Then there's no need for the caveat "and without manual
>>> intervention".
>> 
>> That still leaves the messages awfully ambiguous concerning the cause (data 
>> corruption) and the effect (crash during recovery).
>> 
>> How about
>> "If this has occurred more than once, it is probably caused by corrupt data 
>> and you have to use the latest backup for recovery"
>> for the crash recovery case and
>> "If this has occurred more than once, it is probably caused by corrupt data 
>> and you have to choose an earlier recovery target"
>> for the PITR case.
>> 
>> I don't see why currently only the PITR-case includes the "more than once" 
>> clause. Its probably supposed to prevent unnecessarily alarming the user if 
>> the "crash" was in fact a stray SIGKILL or an out-of-memory condition, which 
>> seems equally likely in both cases.
> 
> I've applied the patch for now - we can fix the wording of the other
> messages with a follow-on patch if we agree on what they should say.
> I don't like the use of the phrase "you have to", particularly...  I
> would tend to leave the archive recovery message alone and change the
> crash recovery message to be more like it.

Since a loose log of this shed gave me quite a bump on my forehead once, one 
last attempt at fixing it.

I've tried to keep this as similar as possible to the existing message while 
making it less ambiguous about cause and effect.

"If this has occurred more than once corrupt data might be the cause and you 
might need to choose an earlier recovery target".
and
"If this has occurred more than once corrupt data might be the cause and you 
might need to restore from backup".

best regards,
Florian Pflug


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

Re: [HACKERS] including PID or backend ID in relpath of temp rels

[Robert Haas]

On Mon, May 17, 2010 at 2:10 PM, Jim Nasby  wrote:
> Any particular reason not to use directories to help organize things? IE:
>
> base/database_oid/pg_temp_rels/backend_pid/relfilenode
>
> Perhaps relfilenode should be something else.
>
> This seems to have several advantages:
>
> 1: It's more organized. If you want to see all the files for a single backend 
> you have just one place to look. Finding everything is still easy via 
> filesystem find.
> 2: Cleanup becomes easier. When a backend exits, it's entire directory goes 
> away. On server start, everything under pg_temp_rels goes away. Unfortunately 
> we still have a race condition with cleaning up if a backend dies and can't 
> run it's own cleanup, though I think that anytime that happens we're going to 
> restart everything anyway.
> 3: It separates all the temporary stuff away from real files.
>
> The only downside I see is some extra code to create the backend_pid 
> directory.

I thought this was a good idea when you first proposed it, but on
further review I've changed my mind.  There are several places in the
code that rely on checking whether the database directory within any
given tablespace is empty to determine whether that database is using
that tablespace.  While I could rewrite all of that logic to do the
right thing, I think it's unnecessary pain.

I talked with Tom Lane about this a little bit at PGcon and opined
that we probably only need to clean out stray temporary files at
startup.  So what I'm tempted to do is just write a function that goes
through all tablespace/database combinations and scans each directory
for files with a name like t_ and blows them away.
This will leave the catalog entries pointing at nothing, but we
already have working code in autovacuum.c to clean up the catalog
entries, and I believe that will work just fine even if the underlying
files have been removed earlier.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS]

[Peter Geoghegan]

This is really a postgreSQL developers list; I suggest you post user
level questions to the -general list

-- 
Regards,
Peter Geoghegan

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

[HACKERS]

[daniel cordero]

Hi all

i wish know when will be possible to create databases and tables on my local 
sql server but only like symlink to remote data specifying existing username 
and password, fully transparent for app, explain: no external libraries needed, 
no code adaptation for existing singledb apps, just 
  
_
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Bruce Momjian]

Alvaro Herrera wrote:
> Excerpts from Bruce Momjian's message of vie jun 04 15:39:07 -0400 2010:
> > Tom Lane wrote:
> > > Bruce Momjian  writes:
> > > > With in-place VACUUM FULL gone in 9.0, will there be as much need for
> > > > xmin/xmax forensics?
> > > 
> > > You know perfectly well that no one could answer that question.
> > > (Or at least not answer it on the basis of facts available today.)
> > 
> > Well, guess then.  In the past, how many forensic cases were needed for
> > in-place VACUUM FULL bugs, vs. other cases?
> 
> I don't understand the question.  I know I have debugged a bunch of
> cases of data corruption, and having xmin/xmax around has been truly
> useful.  VACUUM FULL has never been involved (that I know of -- most of
> our customers tend not to run it AFAIK), so why would I care about
> whether it's gone in 9.0?  Note that it's not always about PG bugs; but
> in the cases where xmin=FrozenXid for all/most involved tuples, the
> problems are more difficult to track down.
> 
> Yes, VACUUM FULL had bugs too -- I, for one, welcome our new
> not-in-place VACUUM FULL overlord.

OK, so we had lots of forensics the didn't involve VACUUM FULL.  That's
what I wanted to know.
-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

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

Re: [HACKERS] Synchronization levels in SR

[Jan Wieck]

On 6/4/2010 4:22 PM, Robert Haas wrote:

On Fri, Jun 4, 2010 at 3:35 PM, Jan Wieck  wrote:

So that justifies adding code, that the community needs to maintain and
document, to the core system. If only I could find some monitoring case for
transaction commit orders ... sigh!


Dude, I'm not the one arguing with you... actually I don't think
anyone really is, any more, except about details.


I know. You actually pretty much defend my case. Sorry for lacking smiley's.

This is an old habit I have. A good friend from Germany once suspected 
one of my emails to be a spoof because I actually used a smiley.



Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

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

Re: [HACKERS] Exposing the Xact commit order to the user

[Jan Wieck]

On 6/4/2010 12:52 PM, Alvaro Herrera wrote:

Excerpts from Jan Wieck's message of jue jun 03 19:52:19 -0400 2010:

On 6/3/2010 7:11 PM, Alvaro Herrera wrote:



> Why not send separate numbers of tuple inserts/updates/deletes, which we
> already have from pgstats?

We only have them for the entire database. The purpose of this is just a 
guesstimate about what data volume to expect if I were to select all log 
from a particular transaction.


But we already have per table counters.  Couldn't we aggregate them per
transaction as well, if this feature is enabled?  I'm guessing that this
is going to have some uses besides Slony; vague measurements could turn
out to be unusable for some of these.


We have them per table and per index, summarized over all transactions. 
It is debatable if bloating this feature with detailed statistics is 
useful or not, but I'd rather not have that bloat at the beginning, 
because otherwise I know exactly what is going to happen. People will 
just come back and say "zero impact my a..".



Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Alvaro Herrera]

Excerpts from Bruce Momjian's message of vie jun 04 15:39:07 -0400 2010:
> Tom Lane wrote:
> > Bruce Momjian  writes:
> > > With in-place VACUUM FULL gone in 9.0, will there be as much need for
> > > xmin/xmax forensics?
> > 
> > You know perfectly well that no one could answer that question.
> > (Or at least not answer it on the basis of facts available today.)
> 
> Well, guess then.  In the past, how many forensic cases were needed for
> in-place VACUUM FULL bugs, vs. other cases?

I don't understand the question.  I know I have debugged a bunch of
cases of data corruption, and having xmin/xmax around has been truly
useful.  VACUUM FULL has never been involved (that I know of -- most of
our customers tend not to run it AFAIK), so why would I care about
whether it's gone in 9.0?  Note that it's not always about PG bugs; but
in the cases where xmin=FrozenXid for all/most involved tuples, the
problems are more difficult to track down.

Yes, VACUUM FULL had bugs too -- I, for one, welcome our new
not-in-place VACUUM FULL overlord.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Synchronization levels in SR

[Robert Haas]

On Fri, Jun 4, 2010 at 3:35 PM, Jan Wieck  wrote:
> On 6/3/2010 10:57 PM, Robert Haas wrote:
>>
>> On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck  wrote:
>>>
>>> On 5/27/2010 4:31 PM, Bruce Momjian wrote:

 Also, what would be cool would be if you could run a query on the master
 to view the SR commit mode of each slave.
>>>
>>> What would be the use case for such a query?
>>
>> Monitoring?
>
> So that justifies adding code, that the community needs to maintain and
> document, to the core system. If only I could find some monitoring case for
> transaction commit orders ... sigh!

Dude, I'm not the one arguing with you... actually I don't think
anyone really is, any more, except about details.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Tom Lane]

Bruce Momjian  writes:
> Tom Lane wrote:
>> Bruce Momjian  writes:
>>> With in-place VACUUM FULL gone in 9.0, will there be as much need for
>>> xmin/xmax forensics?
>> 
>> You know perfectly well that no one could answer that question.
>> (Or at least not answer it on the basis of facts available today.)

> Well, guess then.

I already told you my opinion on this matter.  Since you're prepared
to discount that, I don't see why you'd put any credence in my
evidence-free guesses.

regards, tom lane

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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Tom Lane]

Heikki Linnakangas  writes:
> On 04/06/10 22:33, Tom Lane wrote:
>> A counterexample: suppose we had a form of type "text" that carried a
>> collation specifier internally, and the comparison routine threw an
>> error if asked to compare values with incompatible specifiers.  An index
>> built on a column of all the same collation would work fine.  A query
>> that tried to compare against a constant of a different collation would
>> throw an error.

> I can't take that example seriously. First of all, tacking a collation 
> specifier to text values would be an awful hack.

Really?  I thought that was under serious discussion.  But whether it
applies to text or not is insignificant; I believe there are cases just
like this in existence today for some datatypes (think postgis).

The real point is that the comparison constant is under the control of
the attacker, and it's not part of the index.  Therefore "it didn't
throw an error during index construction" proves nothing whatever.

> ... Secondly, it would be a 
> bad idea to define the b-tree comparison operators to throw an error;

You're still being far too trusting, by imagining that only *designed*
error conditions matter here.  Think about overflows, out-of-memory,
(perhaps intentionally) corrupted data, etc etc.

I think the only real fix would be something like what Marc suggested:
if there's a security view involved in the query, we simply don't give
the client the real error message.  Of course, now our "security
feature" is utterly disastrous on usability as well as performance
counts ...

regards, tom lane

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

Re: [HACKERS] Exposing the Xact commit order to the user

[Jan Wieck]

On 6/4/2010 10:44 AM, Greg Stark wrote:

On Fri, Jun 4, 2010 at 2:32 AM, Robert Haas  wrote:

I find the skeptical attitude on this thread altogether unwarranted.
Jan made his case and, at least IMHO, presented it pretty clearly.


Just to be clear I think the idea of exposing commit order is a
no-brainer.  The specific interface is what I was questioning.

A function which takes a starting xid and a number of transactions to
return seems very tied to one particular application. I could easily
see other systems such as a multi-master system instead only wanting
to compare two transactions to find out which committed first. Or
non-replication applications where you have an LSN and want to know
whether a given transaction had committed by that time.


Read the proposal again. I mean the original mail that started this 
tread. The function does NOT take an xid as argument.


Being able to compare two xid's against each other with respect to their 
commit order is eventually useful. The serial number of the data set, 
returned by the SRF as proposed, would perfectly satisfy that need. But 
not the way you envision for multimaster. Multimaster would ask "did xid 
X from server A commit before or after xid Y from server B?" That is a 
question completely outside the scope of this proposal.


Please keep it real.


Jan




--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Heikki Linnakangas]

On 04/06/10 22:33, Tom Lane wrote:

Heikki Linnakangas  writes:

On 04/06/10 17:33, Tom Lane wrote:

Maybe the entire idea is unworkable.  I certainly don't find any comfort
in your proposal in the above-referenced message to trust index
operators; where is it written that those don't throw errors?



Let's consider b-tree operators for an index on the secure table, for
starters. Surely a b-tree index comparison operator can't throw an error
on any value that's in the table already, you would've gotten an error
trying to insert that.


Man, are *you* trusting.

A counterexample: suppose we had a form of type "text" that carried a
collation specifier internally, and the comparison routine threw an
error if asked to compare values with incompatible specifiers.  An index
built on a column of all the same collation would work fine.  A query
that tried to compare against a constant of a different collation would
throw an error.


I can't take that example seriously. First of all, tacking a collation 
specifier to text values would be an awful hack. Secondly, it would be a 
bad idea to define the b-tree comparison operators to throw an error; it 
would be a lot more useful to impose an arbitrary order on the 
collations, so that all values with collation A are considered smaller 
than values with collation B. We do that for types like box; smaller or 
greater than don't make much sense for boxes, but we implement them in a 
pretty arbitrary way anyway to make it possible to build a b-tree index 
on them, and for the planner to use merge joins on them, and implement 
DISTINCT using sort etc.



I'm not sure. But indexable
operations are what we care about the most; the order of executing those
determines if you can use an index scan or not.


Personally, I care just as much about hash and merge join operators...


Hash seems safe too. Don't merge joins just use the default b-tree operator?

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Synchronization levels in SR

[Jan Wieck]

On 6/3/2010 10:57 PM, Robert Haas wrote:

On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck  wrote:

On 5/27/2010 4:31 PM, Bruce Momjian wrote:

Also, what would be cool would be if you could run a query on the master
to view the SR commit mode of each slave.


What would be the use case for such a query?


Monitoring?


So that justifies adding code, that the community needs to maintain and 
document, to the core system. If only I could find some monitoring case 
for transaction commit orders ... sigh!



Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > With in-place VACUUM FULL gone in 9.0, will there be as much need for
> > xmin/xmax forensics?
> 
> You know perfectly well that no one could answer that question.
> (Or at least not answer it on the basis of facts available today.)

Well, guess then.  In the past, how many forensic cases were needed for
in-place VACUUM FULL bugs, vs. other cases?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Tom Lane]

Bruce Momjian  writes:
> With in-place VACUUM FULL gone in 9.0, will there be as much need for
> xmin/xmax forensics?

You know perfectly well that no one could answer that question.
(Or at least not answer it on the basis of facts available today.)

regards, tom lane

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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Tom Lane]

Heikki Linnakangas  writes:
> On 04/06/10 17:33, Tom Lane wrote:
>> Maybe the entire idea is unworkable.  I certainly don't find any comfort
>> in your proposal in the above-referenced message to trust index
>> operators; where is it written that those don't throw errors?

> Let's consider b-tree operators for an index on the secure table, for 
> starters. Surely a b-tree index comparison operator can't throw an error 
> on any value that's in the table already, you would've gotten an error 
> trying to insert that.

Man, are *you* trusting.

A counterexample: suppose we had a form of type "text" that carried a
collation specifier internally, and the comparison routine threw an
error if asked to compare values with incompatible specifiers.  An index
built on a column of all the same collation would work fine.  A query
that tried to compare against a constant of a different collation would
throw an error.

> I'm not sure. But indexable 
> operations are what we care about the most; the order of executing those 
> determines if you can use an index scan or not.

Personally, I care just as much about hash and merge join operators...

regards, tom lane

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Dave Page]

On Fri, Jun 4, 2010 at 7:30 PM, Tom Lane  wrote:
> Dave Page  writes:
>> On Fri, Jun 4, 2010 at 4:30 PM, Tom Lane  wrote:
>>> XLOG_PAGE_MAGIC --- bump on incompatible change in WAL contents
>
>> How can I get that from an existing data directory? I don't see it in
>> pg_controldata output (unless it has a non-obvious alias).
>
> You'd need to pull it out of one of the WAL files.  I'm not sure it's
> worth the trouble ...

Urgh, no. Probably not.


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Tom Lane]

Dave Page  writes:
> On Fri, Jun 4, 2010 at 4:30 PM, Tom Lane  wrote:
>> XLOG_PAGE_MAGIC --- bump on incompatible change in WAL contents

> How can I get that from an existing data directory? I don't see it in
> pg_controldata output (unless it has a non-obvious alias).

You'd need to pull it out of one of the WAL files.  I'm not sure it's
worth the trouble ...

regards, tom lane

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Dave Page]

On Fri, Jun 4, 2010 at 4:30 PM, Tom Lane  wrote:
> Dave Page  writes:
>> On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane  wrote:
>>> Right, because the catalog contents didn't change.  Seems to me you'd
>>> better teach the installers to look at PG_CONTROL_VERSION too.
>
>> Hmm, is there anything else that might need to be checked?
>
> Offhand I can think of three internal version-like numbers:
>
> CATALOG_VERSION_NO --- bump if initial system catalog contents would be
> inconsistent with backend code
>
> PG_CONTROL_VERSION --- bump when contents of pg_control change

They're easy enough.

> XLOG_PAGE_MAGIC --- bump on incompatible change in WAL contents

How can I get that from an existing data directory? I don't see it in
pg_controldata output (unless it has a non-obvious alias).


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Robert Haas]

On Fri, Jun 4, 2010 at 1:46 PM, Heikki Linnakangas
 wrote:
> On 04/06/10 17:33, Tom Lane wrote:
>>
>> Heikki Linnakangas  writes:
>>>
>>> On 04/06/10 07:57, Tom Lane wrote:

 The proposal some time back in this thread was to trust all built-in
 functions and no others.
>>
>>> I thought I debunked that idea already
>>> (http://archives.postgresql.org/pgsql-hackers/2009-10/msg01428.php). Not
>>> all built-in functions are safe. Consider casting integer to text, for
>>> example.
>
> (I meant "text to integer", of course)
>
>> Maybe the entire idea is unworkable.  I certainly don't find any comfort
>> in your proposal in the above-referenced message to trust index
>> operators; where is it written that those don't throw errors?
>
> Let's consider b-tree operators for an index on the secure table, for
> starters. Surely a b-tree index comparison operator can't throw an error on
> any value that's in the table already, you would've gotten an error trying
> to insert that.
>
> Now, is it safe to expand that thinking to b-tree operators in general, even
> if there's no such index on the table? I'm not sure. But indexable
> operations are what we care about the most; the order of executing those
> determines if you can use an index scan or not.

Another idea I had was... would it be safe to trust functions defined
by the same user who owns the view?  If he's granted access to the
view and the function to some other user, presumably he doesn't mind
them being used together?  Or is that too optimistic?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > The idea that thousands of Postgres installations are slower just so we
> > can occasionally debug xmin/xmax issues seems way off balance to me.
> 
> There's no evidence whatsoever that the scope of the problem is that large.
> 
> > If people want debugging, let them modify the freeze age settings;  the
> > defaults should not favor debugging when there is a measurable cost
> > involved.  How many times in the past five years have we even needed
> > such debugging information, and also are cases where we could not have
> > told the user to change freeze settings to get us that info?
> 
> You're missing the point here: this is something we need when trying
> to make sense of cases that are hard or impossible to reproduce.
> Retroactively changing the freeze policy isn't possible.

With in-place VACUUM FULL gone in 9.0, will there be as much need for
xmin/xmax forensics?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Kevin Grittner]

Tom Lane  wrote:
> Bruce Momjian  writes:
>> The idea that thousands of Postgres installations are slower just
>> so we can occasionally debug xmin/xmax issues seems way off
>> balance to me.
> 
> There's no evidence whatsoever that the scope of the problem is
> that large.
 
Well, are we agreed that the current approach means that insertion
of a heap tuple normally requires it to be written to disk three
times, with two of those WAL-logged?  And that deletion of a tuple
generally requires the same?  I'd say that constitutes prima facie
evidence that any PostgreSQL installation doing any significant
number of writes is slower because of this.  Are you suggesting
there aren't thousands of such installations, or that the repeated
disk writes are generally free?
 
-Kevin

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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Heikki Linnakangas]

On 04/06/10 17:33, Tom Lane wrote:

Heikki Linnakangas  writes:

On 04/06/10 07:57, Tom Lane wrote:

The proposal some time back in this thread was to trust all built-in
functions and no others.



I thought I debunked that idea already
(http://archives.postgresql.org/pgsql-hackers/2009-10/msg01428.php). Not
all built-in functions are safe. Consider casting integer to text, for
example.


(I meant "text to integer", of course)


Maybe the entire idea is unworkable.  I certainly don't find any comfort
in your proposal in the above-referenced message to trust index
operators; where is it written that those don't throw errors?


Let's consider b-tree operators for an index on the secure table, for 
starters. Surely a b-tree index comparison operator can't throw an error 
on any value that's in the table already, you would've gotten an error 
trying to insert that.


Now, is it safe to expand that thinking to b-tree operators in general, 
even if there's no such index on the table? I'm not sure. But indexable 
operations are what we care about the most; the order of executing those 
determines if you can use an index scan or not.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Tom Lane]

Bruce Momjian  writes:
> The idea that thousands of Postgres installations are slower just so we
> can occasionally debug xmin/xmax issues seems way off balance to me.

There's no evidence whatsoever that the scope of the problem is that large.

> If people want debugging, let them modify the freeze age settings;  the
> defaults should not favor debugging when there is a measurable cost
> involved.  How many times in the past five years have we even needed
> such debugging information, and also are cases where we could not have
> told the user to change freeze settings to get us that info?

You're missing the point here: this is something we need when trying
to make sense of cases that are hard or impossible to reproduce.
Retroactively changing the freeze policy isn't possible.

regards, tom lane

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

Re: [HACKERS] Exposing the Xact commit order to the user

[Alvaro Herrera]

Excerpts from Jan Wieck's message of jue jun 03 19:52:19 -0400 2010:
> On 6/3/2010 7:11 PM, Alvaro Herrera wrote:

> > Why not send separate numbers of tuple inserts/updates/deletes, which we
> > already have from pgstats?
> 
> We only have them for the entire database. The purpose of this is just a 
> guesstimate about what data volume to expect if I were to select all log 
> from a particular transaction.

But we already have per table counters.  Couldn't we aggregate them per
transaction as well, if this feature is enabled?  I'm guessing that this
is going to have some uses besides Slony; vague measurements could turn
out to be unusable for some of these.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Marc Munro]

On Fri, 2010-06-04 at 10:33 -0400, Tom Lane wrote:
> Hmm ... that's a mighty interesting example, because it shows that any
> well-meaning change in error handling might render seemingly-unrelated
> functions "unsafe".  And we're certainly not going to make error
> messages stop showing relevant information just because of this.

Although that looks like a show-stopper, I think it can be worked
around.  Errors in operations on security views could simply be caught
and conditionally rewritten.  The original error could still appear in
the logs but the full details need not be reported to unprivileged
users.

If that can be done, then we would still need to be able to identify
trusted functions and views used for security purposes, and ensure that
(please excuse my terminology if it is incorrect) untrusted quals do not
get pushed down inside secured views.  If all of that can be done along
with the error trapping, then we may have a solution.

My big concern is still about performance, particularly when joining
between multiple security views and other objects.  I don't expect to
get security for free but I don't want to see unnecessary barriers to
optimisation.

__
Marc


signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Bruce Momjian]

Kevin Grittner wrote:
> Fair enough.  I was thinking of them both as debugging features,
> which had various ideas roiling around in my head.  Having run
> hundreds of databases 24/7 for years without ever needing this
> information, but paying the cost for it one way or another every
> day, my perspective is that it would be A Good Thing if it could
> just be turned on when needed.  If you have recurring bug that can
> be arranged, but in those cases you have other options; so I'm
> assuming you want this kept because it is primarily of forensic
> value after a non-repeatable bug has munged something?
>  
> Another thought bouncing around was that these breadcrumbs are
> expensive; I was trying to think of some other way to capture the
> information which would be cheaper, but I haven't thought of
> anything, and I'm far from certain that cheaper breadcrumbs to
> answer the need can be developed.  The best thought I've had so far
> is that if someone kept WAL files long enough the evidence might be
> in there somewhere

The idea that thousands of Postgres installations are slower just so we
can occasionally debug xmin/xmax issues seems way off balance to me.
If people want debugging, let them modify the freeze age settings;  the
defaults should not favor debugging when there is a measurable cost
involved.  How many times in the past five years have we even needed
such debugging information, and also are cases where we could not have
told the user to change freeze settings to get us that info?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Kevin Grittner]

Tom Lane  wrote:
 
> But Kevin's question seemed to be based on the assumption that
> runtime cost was the only negative.  It wouldn't be terribly hard
> to make a variant of cassert that skips two or three of the most
> expensive things (particularly memory context checking and
> CLOBBER_FREED_MEMORY), and from a cost perspective that would be
> totally reasonable to run in production.  We haven't done it
> because of the stability issue.
 
Fair enough.  I was thinking of them both as debugging features,
which had various ideas roiling around in my head.  Having run
hundreds of databases 24/7 for years without ever needing this
information, but paying the cost for it one way or another every
day, my perspective is that it would be A Good Thing if it could
just be turned on when needed.  If you have recurring bug that can
be arranged, but in those cases you have other options; so I'm
assuming you want this kept because it is primarily of forensic
value after a non-repeatable bug has munged something?
 
Another thought bouncing around was that these breadcrumbs are
expensive; I was trying to think of some other way to capture the
information which would be cheaper, but I haven't thought of
anything, and I'm far from certain that cheaper breadcrumbs to
answer the need can be developed.  The best thought I've had so far
is that if someone kept WAL files long enough the evidence might be
in there somewhere
 
-Kevin

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Tom Lane]

Robert Haas  writes:
> On Fri, Jun 4, 2010 at 11:45 AM, Tom Lane  wrote:
>> The reason for not recommending cassert in production builds is not
>> cost but stability.

> We routinely castigate people for benchmarking done with cassert
> turned on, and tell them their numbers are meaningless.

I didn't say it wasn't expensive ;-).  But Kevin's question seemed to
be based on the assumption that runtime cost was the only negative.
It wouldn't be terribly hard to make a variant of cassert that skips
two or three of the most expensive things (particularly memory context
checking and CLOBBER_FREED_MEMORY), and from a cost perspective that
would be totally reasonable to run in production.  We haven't done it
because of the stability issue.

regards, tom lane

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Fri, Jun 4, 2010 at 11:45 AM, Tom Lane  wrote:
> "Kevin Grittner"  writes:
>> In my experience with my own environment, I can honestly say that
>> it's clear that not freezing tuples quickly adds more cost than
>> running with cassert on.  If we had to run in production with one or
>> the other, I would definitely choose cassert from a performance
>> perspective; which one would do more to find bugs?  Why do we view
>> them so differently?
>
> The reason for not recommending cassert in production builds is not
> cost but stability.  Per the fine manual:
>
>         Also, having the tests turned on won't necessarily enhance the
>         stability of your server!  The assertion checks are not categorized
>         for severity, and so what might be a relatively harmless bug will
>         still lead to server restarts if it triggers an assertion
>         failure.  This option is not recommended for production use, but
>         you should have it on for development work or when running a beta
>         version.

We routinely castigate people for benchmarking done with cassert
turned on, and tell them their numbers are meaningless.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Tom Lane]

Robert Haas  writes:
> It would be nice to have all of these documented somewhere along with
> the criteria for bumping each one.

Go for it.  I think you have all the raw data in this thread.

regards, tom lane

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Tom Lane]

"Kevin Grittner"  writes:
> In my experience with my own environment, I can honestly say that
> it's clear that not freezing tuples quickly adds more cost than
> running with cassert on.  If we had to run in production with one or
> the other, I would definitely choose cassert from a performance
> perspective; which one would do more to find bugs?  Why do we view
> them so differently?

The reason for not recommending cassert in production builds is not
cost but stability.  Per the fine manual:

 Also, having the tests turned on won't necessarily enhance the
 stability of your server!  The assertion checks are not categorized
 for severity, and so what might be a relatively harmless bug will
 still lead to server restarts if it triggers an assertion
 failure.  This option is not recommended for production use, but
 you should have it on for development work or when running a beta
 version.

regards, tom lane

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Bruce Momjian]

Tom Lane wrote:
> Dave Page  writes:
> > On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane  wrote:
> >> Right, because the catalog contents didn't change. ?Seems to me you'd
> >> better teach the installers to look at PG_CONTROL_VERSION too.
> 
> > Hmm, is there anything else that might need to be checked?
> 
> Offhand I can think of three internal version-like numbers:
> 
> CATALOG_VERSION_NO --- bump if initial system catalog contents would be
> inconsistent with backend code
> 
> PG_CONTROL_VERSION --- bump when contents of pg_control change
> 
> XLOG_PAGE_MAGIC --- bump on incompatible change in WAL contents

pg_upgrade never views these in their raw format so does not need to
check them.  (It does look at pg_controldata text output.)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Tom Lane]

Dave Page  writes:
> On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane  wrote:
>> Right, because the catalog contents didn't change.  Seems to me you'd
>> better teach the installers to look at PG_CONTROL_VERSION too.

> Hmm, is there anything else that might need to be checked?

Offhand I can think of three internal version-like numbers:

CATALOG_VERSION_NO --- bump if initial system catalog contents would be
inconsistent with backend code

PG_CONTROL_VERSION --- bump when contents of pg_control change

XLOG_PAGE_MAGIC --- bump on incompatible change in WAL contents

regards, tom lane

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Robert Haas]

On Fri, Jun 4, 2010 at 11:06 AM, Dave Page  wrote:
> On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane  wrote:
>> Bruce Momjian  writes:
>>> Dave Page wrote:
 Shouldn't we have bumped the catversion? The installers can't tell
 that beta1 clusters won't work with beta2 :-(
>>
>>> That is an interesting point.  Tom bumped the pg_control version, but
>>> not the catalog version.
>>
>> Right, because the catalog contents didn't change.  Seems to me you'd
>> better teach the installers to look at PG_CONTROL_VERSION too.
>
> Hmm, is there anything else that might need to be checked?

XLOG_PAGE_MAGIC, for one.  PG_PAGE_LAYOUT_VERSION doesn't change very
often, but might also fall into the same category.

Tablespace directory paths depend on the value of PG_MAJORVERSION.

It would be nice to have all of these documented somewhere along with
the criteria for bumping each one.  It's relatively easy for a new
committer (ahem) to not realize that there's a version number that
needs to be bumped someplace, and recent experience has shown that
even an experienced committer can goof.  :-)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] Exposing the Xact commit order to the user

[Robert Haas]

On Fri, Jun 4, 2010 at 10:44 AM, Greg Stark  wrote:
> On Fri, Jun 4, 2010 at 2:32 AM, Robert Haas  wrote:
>> I find the skeptical attitude on this thread altogether unwarranted.
>> Jan made his case and, at least IMHO, presented it pretty clearly.
>
> Just to be clear I think the idea of exposing commit order is a
> no-brainer.  The specific interface is what I was questioning.

OK, thanks for that clarification.

> A function which takes a starting xid and a number of transactions to
> return seems very tied to one particular application. I could easily
> see other systems such as a multi-master system instead only wanting
> to compare two transactions to find out which committed first. Or
> non-replication applications where you have an LSN and want to know
> whether a given transaction had committed by that time.
>
> So one possible interface would be to do something like
> xids_committed_between(lsn_start, lsn_end) -- and yes, possibly with
> an optional argument to limit the number or records returned.

I'm imagining that the backend data storage for this would be a file
containing, essentially, a struct for each commit repeated over and
over again, packed tightly.  It's easy to index into such a file using
a sequence number (give me the 1000'th commit) but searching by LSN
would require (a) storing the LSNs and (b) binary search.  Maybe it's
worth adding that complexity, but I'm not sure that it is.  Keeping
the size of this file small is important for ensuring that it has
minimal performance impact (which is also why I'm not sold on trying
to include the tuple counters that Jan proposed - I think we can solve
the problem he's worried about there more cleanly in other ways).

> So you could do:
>
> old := select pg_current_xlog_location();
> while (1)
> {
>  sleep 60s;
>  new := select pg_current_xlog_location()
>  process(select xids_committed_between(old,new))
>  old := new
> }
>
> This might be more useful for PITR recovery for example where you want
> to find out what transactions committed between now and some known
> point of corruption.

This could also be done by selecting the current commit sequence
number, getting the XIDs committed between the two commit sequence
numbers, etc.

> I could also see it being useful to have a function
> pg_xlog_location_of_commit(xid). That would let you run recovery until
> a particular transaction committed or test whether your replica is
> caught up to a particular commit. It could be useful for monitoring
> Hot Standby slaves.

Well, you'd need to index the commit data to make that work, I think,
so that adds a lot of complexity.  The implementation as proposed lets
you find the commits after a known point in order of occurrence, but
it doesn't let you inquire about the location of a particular commit.
If you want to run recovery until a particular transaction commits, we
could teach the recovery code to look for the commit record for that
XID and then pause at that point (or just before that point, if
someone wanted that as an alternative behavior), which would be much
simpler than using this mechanism.  And if you want to check whether
slaves are caught up, it would probably be better to use LSN rather
than commits, because you could be caught up on commits but way behind
on WAL replay.

>>  I think
>> we should be very careful about assuming that we understand
>> replication and its needs better than someone who has spent many years
>> developing one of the major PostgreSQL replication solutions.
>
> Well the flip side of that is that we want an interface that's useful
> for more than just one replication system. This is something basic
> enough that I think it will be useful for more than just replication
> if we design it generally enough. It should be useful for
> backup/restore processes and monitoring as well as various forms of
> replication including master-slave trigger based systems but also
> including PITR-based replication, log-parsing systems, multi-master
> trigger based systems, 2PC-based systems, etc.

Making it general enough to serve multiple needs is good, but we've
got to make sure that the extra complexity is buying us something.
Jan seems pretty confident that this could be used by Londiste also,
though it would be nice to have some confirmation from the Londiste
developer(s) on that.  I think it may also have applications for
distributed transactions and multi-master replication, but I am not
too sure it helps much for PITR-based replication or log-parsing
systems.  We want to design something that is good, but trying to
solve too many problems may end up solving none of them well.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Kevin Grittner]

Tom Lane  wrote:
> Jan Wieck  writes:
 
>> I just see a lot of cost caused by this "safety range". I yet
>> have to see its real value, other than "feel good".
> 
> Jan, you don't know what you're talking about.  I have repeatedly
> had cases where being able to look at xmin was critical to
> understanding a bug.  I *will not* hold still for a solution that
> effectively reduces min_freeze_age to zero.
 
In my experience with my own environment, I can honestly say that
it's clear that not freezing tuples quickly adds more cost than
running with cassert on.  If we had to run in production with one or
the other, I would definitely choose cassert from a performance
perspective; which one would do more to find bugs?  Why do we view
them so differently?
 
-Kevin

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Dave Page]

On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane  wrote:
> Bruce Momjian  writes:
>> Dave Page wrote:
>>> Shouldn't we have bumped the catversion? The installers can't tell
>>> that beta1 clusters won't work with beta2 :-(
>
>> That is an interesting point.  Tom bumped the pg_control version, but
>> not the catalog version.
>
> Right, because the catalog contents didn't change.  Seems to me you'd
> better teach the installers to look at PG_CONTROL_VERSION too.

Hmm, is there anything else that might need to be checked?

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Robert Haas]

On Fri, Jun 4, 2010 at 10:18 AM, Tom Lane  wrote:
> Jan Wieck  writes:
>> On 6/2/2010 3:10 PM, Alvaro Herrera wrote:
>>> I'd prefer a setting that would tell the system to freeze all tuples
>>> that fall within a safety range whenever any tuple in the page is frozen
>>> -- weren't you working on a patch to do this?  (was it Jeff Davis?)
>
>> I just see a lot of cost caused by this "safety range". I yet have to
>> see its real value, other than "feel good".
>
> Jan, you don't know what you're talking about.  I have repeatedly had
> cases where being able to look at xmin was critical to understanding
> a bug.  I *will not* hold still for a solution that effectively reduces
> min_freeze_age to zero.

So, we're talking in circles here.  I've already proposed a method
that would avoid the need to wipe out the xmins:

http://archives.postgresql.org/pgsql-hackers/2010-05/msg01485.php

And you said that if we were going to do that we might as well just
freeze sooner:

http://archives.postgresql.org/pgsql-hackers/2010-05/msg01548.php

If you don't want to freeze sooner, let's go back to the method
described in the first email.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] Exposing the Xact commit order to the user

[Greg Stark]

On Fri, Jun 4, 2010 at 2:32 AM, Robert Haas  wrote:
> I find the skeptical attitude on this thread altogether unwarranted.
> Jan made his case and, at least IMHO, presented it pretty clearly.

Just to be clear I think the idea of exposing commit order is a
no-brainer.  The specific interface is what I was questioning.

A function which takes a starting xid and a number of transactions to
return seems very tied to one particular application. I could easily
see other systems such as a multi-master system instead only wanting
to compare two transactions to find out which committed first. Or
non-replication applications where you have an LSN and want to know
whether a given transaction had committed by that time.

So one possible interface would be to do something like
xids_committed_between(lsn_start, lsn_end) -- and yes, possibly with
an optional argument to limit the number or records returned.

So you could do:

old := select pg_current_xlog_location();
while (1)
{
  sleep 60s;
  new := select pg_current_xlog_location()
  process(select xids_committed_between(old,new))
  old := new
}

This might be more useful for PITR recovery for example where you want
to find out what transactions committed between now and some known
point of corruption.

I could also see it being useful to have a function
pg_xlog_location_of_commit(xid). That would let you run recovery until
a particular transaction committed or test whether your replica is
caught up to a particular commit. It could be useful for monitoring
Hot Standby slaves.


>  He
> then answered, multiple times, numerous questions which were already
> addressed in the original email, as well as various others.

I think I did miss some of the original description. That might have
caused some of the difficulty as I was asking questions about
something he assumed he had already answered.

>  I think
> we should be very careful about assuming that we understand
> replication and its needs better than someone who has spent many years
> developing one of the major PostgreSQL replication solutions.

Well the flip side of that is that we want an interface that's useful
for more than just one replication system. This is something basic
enough that I think it will be useful for more than just replication
if we design it generally enough. It should be useful for
backup/restore processes and monitoring as well as various forms of
replication including master-slave trigger based systems but also
including PITR-based replication, log-parsing systems, multi-master
trigger based systems, 2PC-based systems, etc.



-- 
greg

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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Tom Lane]

Heikki Linnakangas  writes:
> On 04/06/10 07:57, Tom Lane wrote:
>> The proposal some time back in this thread was to trust all built-in
>> functions and no others.

> I thought I debunked that idea already 
> (http://archives.postgresql.org/pgsql-hackers/2009-10/msg01428.php). Not 
> all built-in functions are safe. Consider casting integer to text, for 
> example. Seems innocent at first glance, but it's not; if the input is 
> not a valid integer, it throws an error which contains the input string, 
> revealing it.

Hmm ... that's a mighty interesting example, because it shows that any
well-meaning change in error handling might render seemingly-unrelated
functions "unsafe".  And we're certainly not going to make error
messages stop showing relevant information just because of this.

Maybe the entire idea is unworkable.  I certainly don't find any comfort
in your proposal in the above-referenced message to trust index
operators; where is it written that those don't throw errors?

regards, tom lane

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Tom Lane]

Jan Wieck  writes:
> On 6/2/2010 3:10 PM, Alvaro Herrera wrote:
>> I'd prefer a setting that would tell the system to freeze all tuples
>> that fall within a safety range whenever any tuple in the page is frozen
>> -- weren't you working on a patch to do this?  (was it Jeff Davis?)

> I just see a lot of cost caused by this "safety range". I yet have to 
> see its real value, other than "feel good".

Jan, you don't know what you're talking about.  I have repeatedly had
cases where being able to look at xmin was critical to understanding
a bug.  I *will not* hold still for a solution that effectively reduces
min_freeze_age to zero.

regards, tom lane

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

Re: [HACKERS] Synchronization levels in SR

[David Fetter]

On Thu, Jun 03, 2010 at 10:57:05PM -0400, Robert Haas wrote:
> On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck  wrote:
> > What would be the use case for such a query?
> 
> Monitoring?

s/\?/!/;

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Tom Lane]

Bruce Momjian  writes:
> Dave Page wrote:
>> Shouldn't we have bumped the catversion? The installers can't tell
>> that beta1 clusters won't work with beta2 :-(

> That is an interesting point.  Tom bumped the pg_control version, but
> not the catalog version.

Right, because the catalog contents didn't change.  Seems to me you'd
better teach the installers to look at PG_CONTROL_VERSION too.

regards, tom lane

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Bruce Momjian]

Dave Page wrote:
> On Thu, Jun 3, 2010 at 11:21 PM, Tom Lane  wrote:
> > Florian Pflug  writes:
> >> On Jun 3, 2010, at 19:00 , Tom Lane wrote:
> >>> Maybe we should just get rid of the hint.
> >
> >> FYI, Robert Haas suggested the same in the thread that lead to this patch 
> >> being applied. The arguments against doing that is that a real crash 
> >> during recovery *is* something to be quite alarmed about.
> >
> > After some discussion among core we're going to leave it as-is. ?Anybody
> > who doesn't want to initdb for beta2 can test out pg_upgrade ;-)
> 
> Shouldn't we have bumped the catversion? The installers can't tell
> that beta1 clusters won't work with beta2 :-(

That is an interesting point.  Tom bumped the pg_control version, but
not the catalog version.  I am unclear how that affects people's
visibility about incompatibility.  (pg_upgrade will not care.)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Heikki Linnakangas]

On 04/06/10 07:57, Tom Lane wrote:

KaiGai Kohei  writes:

(2010/06/04 11:55), Robert Haas wrote:

A (very) important part of this problem is determining which quals are
safe to push down.


At least, I don't have an idea to distinguish trusted functions from
others without any additional hints, because we support variable kind
of PL languages. :(


The proposal some time back in this thread was to trust all built-in
functions and no others.


I thought I debunked that idea already 
(http://archives.postgresql.org/pgsql-hackers/2009-10/msg01428.php). Not 
all built-in functions are safe. Consider casting integer to text, for 
example. Seems innocent at first glance, but it's not; if the input is 
not a valid integer, it throws an error which contains the input string, 
revealing it.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Jan Wieck]

On 6/2/2010 3:10 PM, Alvaro Herrera wrote:

Excerpts from Robert Haas's message of mié jun 02 14:16:33 -0400 2010:


We could, but I think we'd be better off just freezing at the time we
mark the page PD_ALL_VISIBLE and then using the visibility map for
both purposes.  Keeping around the old xmin values after every tuple
on the page is visible to every running transaction is useful only for
forensics, and building a whole new freeze map just to retain that
information longer (and eventually force a massive anti-wraparound
vacuum) seems like overkill.


Reducing the xid wraparound horizon "a bit" is reasonable, but moving it
all the way forward to OldestXmin is a bit much, methinks.


Why?



Besides, there's another argument for not freezing tuples immediately:
they may be updated shortly thereafter, causing extra churn for no gain.


What extra churn does it create if the tuple can be frozen before the 
bgwriter ever writes the page in the first place?




I'd prefer a setting that would tell the system to freeze all tuples
that fall within a safety range whenever any tuple in the page is frozen
-- weren't you working on a patch to do this?  (was it Jeff Davis?)


I just see a lot of cost caused by this "safety range". I yet have to 
see its real value, other than "feel good".



Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

[Jan Wieck]

On 6/2/2010 2:16 PM, Robert Haas wrote:

On Wed, Jun 2, 2010 at 2:05 PM, Tom Lane  wrote:

Alvaro Herrera  writes:

The problem is that vacuum doesn't know that a certain part of the table
is already frozen.  It needs to scan it completely anyways.  If we had a
"frozen" map, we could mark pages that are completely frozen and thus do
not need any vacuuming; but we don't (I don't recall the reasons for
this.  Maybe it's just that no one has gotten around to it, or maybe
there's something else).


Offhand I think the reason is that you'd have to trust the frozen bit
to be 100% correct (or at least never set to 1 in error).  Currently,
both the FSM and visibility forks are just hints, and we won't suffer
data corruption if they're wrong; so we don't get too tense about WAL
logging or fsync'ing updates.  I believe Heikki is looking into what
it'd take to make the visibility map 100% reliable, in connection with
the desire for index-only scans.  If we get that and the overhead isn't
too terrible maybe we could build a frozen-status map the same way.


We could, but I think we'd be better off just freezing at the time we
mark the page PD_ALL_VISIBLE and then using the visibility map for
both purposes.  Keeping around the old xmin values after every tuple
on the page is visible to every running transaction is useful only for
forensics, and building a whole new freeze map just to retain that
information longer (and eventually force a massive anti-wraparound
vacuum) seems like overkill.


Agreed.

The whole business of minimum freeze age always struck me as leaving 
bread crumbs behind. Other than forensics, what is the actual value of 
that overhead?



Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

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

Re: [HACKERS] Did we really want to force an initdb in beta2?

[Dave Page]

On Thu, Jun 3, 2010 at 11:21 PM, Tom Lane  wrote:
> Florian Pflug  writes:
>> On Jun 3, 2010, at 19:00 , Tom Lane wrote:
>>> Maybe we should just get rid of the hint.
>
>> FYI, Robert Haas suggested the same in the thread that lead to this patch 
>> being applied. The arguments against doing that is that a real crash during 
>> recovery *is* something to be quite alarmed about.
>
> After some discussion among core we're going to leave it as-is.  Anybody
> who doesn't want to initdb for beta2 can test out pg_upgrade ;-)

Shouldn't we have bumped the catversion? The installers can't tell
that beta1 clusters won't work with beta2 :-(


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

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

Re: [HACKERS] PITR Recovery Question

[Florian Pflug]

On Jun 4, 2010, at 7:05 , Gnanakumar wrote:
>> If some of those WAL segments still reside in pg_xlog, you'll either need
> to teach your restore_command to fetch them from there. Note that you cannot
> recover "in reverse".
> 
> My pg_xlog/ and walarchive/ directory locations are
> "/usr/local/pgsql/data/pg_xlog" and "/mnt/pitr/walarchive" respectively.
> 
> If my normal restore command is: restore_command='cp
> "/mnt/pitr/walarchive/%f "%p"', how should I instruct restore command to
> fetch?  Should I just replace this with something like restore_command='cp
> /usr/local/pgsql/data/pg_xlog/%f "%p"'.  Also you have mentioned that we
> cannot recover "in reverse", what I understand from this is that even though
> if I replace the restore command pointing to pg_xlog/ directory, this will
> not work out in this situation?  Is my understanding right?

If you point it at a cluster's own pg_xlog directory, it won't work.

You might want to re-ead the section on the recovery process in the PTITR 
documentation, at
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BACKUP-PITR-RECOVERY

If you have further questions, please take this discussion to pgsql-general.

best regards,
Florian Pflug


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

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[KaiGai Kohei]

(2010/06/04 18:26), Dimitri Fontaine wrote:

Tom Lane  writes:

The proposal some time back in this thread was to trust all built-in
functions and no others.  That's a bit simplistic, no doubt, but it
seems to me to largely solve the performance problem and to do so with
minimal effort.  When and if you get to a solution that's committable
with respect to everything else, it might be time to think about
more flexible answers to that particular point.


What about trusting all "internal" and "C" language function instead? My
understanding is that "internal" covers built-in functions, and as you
need to be a superuser to CREATE a "C" language function, surely you're
able to accept that by doing so you get to trust it?

How useful would that be?


If we trust all the "C" language functions, it also means DBA can never
install any binary functions having side-effect (e.g, pg_file_write() in
the contrib/adminpack ) without security risks.

If we need an intelligence to identify what functions are trusted and
what ones are untrusted, it will eventually need a hint to mark a certain
function as trusted, won't it?

Thanks,
--
KaiGai Kohei 

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

Re: [HACKERS] rfc: changing documentation about xpath

[Nikolay Samokhvalov]

On Thu, Jun 3, 2010 at 16:02, Andrew Dunstan  wrote:
>
>
> Denis I. Polukarov wrote:
>
>>  Hi!
>>
>> I'm to face a problem, and not at once resolve it.
>>
>>
> [default namespace mapped in xml "xmlns=" attribute requires corresponding
> mapping in third param of xpath()]
>
> It's a tolerably subtle point, and I'm not sure it's really
> PostgreSQL-specific. But if you think the docs need improvement, then please
> suggest a patch with the extra wording you think would make things clearer.
>
>  
>

You are absolutely right, it's not really Postgres-specific, it's XML
specific, but every novice using xpath encounters with this unclear point.
So, small docs patch is sent to -docs.

Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS

[Dimitri Fontaine]

Tom Lane  writes:
> The proposal some time back in this thread was to trust all built-in
> functions and no others.  That's a bit simplistic, no doubt, but it
> seems to me to largely solve the performance problem and to do so with
> minimal effort.  When and if you get to a solution that's committable
> with respect to everything else, it might be time to think about
> more flexible answers to that particular point.

What about trusting all "internal" and "C" language function instead? My
understanding is that "internal" covers built-in functions, and as you
need to be a superuser to CREATE a "C" language function, surely you're
able to accept that by doing so you get to trust it?

How useful would that be?
-- 
dim

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