[HACKERS] hot_standby = on
The docs don't seem to contain any discussion I could find on why one might not want hot_standby on. Maybe it's just too obvious to most people, but this seems to be a bit lacking in the docs. cheers andrew -- 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 t...@sss.pgh.pa.us 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
Re: [HACKERS] rfc: changing documentation about xpath
On Thu, Jun 3, 2010 at 16:02, Andrew Dunstan and...@dunslane.net 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. http://www.postgresql.org/mailpref/pgsql-hackers 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
(2010/06/04 18:26), Dimitri Fontaine wrote: Tom Lanet...@sss.pgh.pa.us 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 kai...@kaigai.gr.jp -- 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
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] Did we really want to force an initdb in beta2?
On Thu, Jun 3, 2010 at 11:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Florian Pflug f...@phlo.org 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] Idea for getting rid of VACUUM FREEZE on cold pages
On 6/2/2010 2:16 PM, Robert Haas wrote: On Wed, Jun 2, 2010 at 2:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com 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] Idea for getting rid of VACUUM FREEZE on cold pages
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] [PATCH] Fix leaky VIEWs for RLS
On 04/06/10 07:57, Tom Lane wrote: KaiGai Koheikai...@ak.jp.nec.com 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] Did we really want to force an initdb in beta2?
Dave Page wrote: On Thu, Jun 3, 2010 at 11:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Florian Pflug f...@phlo.org 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 br...@momjian.ushttp://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?
Bruce Momjian br...@momjian.us 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] Synchronization levels in SR
On Thu, Jun 03, 2010 at 10:57:05PM -0400, Robert Haas wrote: On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck janwi...@yahoo.com wrote: What would be the use case for such a query? Monitoring? s/\?/!/; Cheers, David. -- David Fetter da...@fetter.org 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] Idea for getting rid of VACUUM FREEZE on cold pages
Jan Wieck janwi...@yahoo.com 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] [PATCH] Fix leaky VIEWs for RLS
Heikki Linnakangas heikki.linnakan...@enterprisedb.com 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] Exposing the Xact commit order to the user
On Fri, Jun 4, 2010 at 2:32 AM, Robert Haas robertmh...@gmail.com 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] Idea for getting rid of VACUUM FREEZE on cold pages
On Fri, Jun 4, 2010 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jan Wieck janwi...@yahoo.com 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] Did we really want to force an initdb in beta2?
On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us 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
Tom Lane t...@sss.pgh.pa.us wrote: Jan Wieck janwi...@yahoo.com 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] Exposing the Xact commit order to the user
On Fri, Jun 4, 2010 at 10:44 AM, Greg Stark gsst...@mit.edu wrote:
On Fri, Jun 4, 2010 at 2:32 AM, Robert Haas robertmh...@gmail.com 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] Did we really want to force an initdb in beta2?
On Fri, Jun 4, 2010 at 11:06 AM, Dave Page dp...@pgadmin.org wrote: On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us 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] Did we really want to force an initdb in beta2?
Dave Page dp...@pgadmin.org writes: On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane t...@sss.pgh.pa.us 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?
Tom Lane wrote: Dave Page dp...@pgadmin.org writes: On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane t...@sss.pgh.pa.us 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 br...@momjian.ushttp://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 kevin.gritt...@wicourts.gov 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?
Robert Haas robertmh...@gmail.com 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
On Fri, Jun 4, 2010 at 11:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov 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] Idea for getting rid of VACUUM FREEZE on cold pages
Robert Haas robertmh...@gmail.com writes: On Fri, Jun 4, 2010 at 11:45 AM, Tom Lane t...@sss.pgh.pa.us 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
Tom Lane t...@sss.pgh.pa.us 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
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 br...@momjian.ushttp://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
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] Exposing the Xact commit order to the user
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 alvhe...@commandprompt.com 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] Idea for getting rid of VACUUM FREEZE on cold pages
Bruce Momjian br...@momjian.us 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] [PATCH] Fix leaky VIEWs for RLS
On 04/06/10 17:33, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com 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 t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us 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] Idea for getting rid of VACUUM FREEZE on cold pages
Tom Lane wrote: Bruce Momjian br...@momjian.us 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 br...@momjian.ushttp://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
On Fri, Jun 4, 2010 at 1:46 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 04/06/10 17:33, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com 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] Did we really want to force an initdb in beta2?
On Fri, Jun 4, 2010 at 4:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Fri, Jun 4, 2010 at 2:49 PM, Tom Lane t...@sss.pgh.pa.us 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] Did we really want to force an initdb in beta2?
Dave Page dp...@pgadmin.org writes: On Fri, Jun 4, 2010 at 4:30 PM, Tom Lane t...@sss.pgh.pa.us 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?
On Fri, Jun 4, 2010 at 7:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Fri, Jun 4, 2010 at 4:30 PM, Tom Lane t...@sss.pgh.pa.us 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] [PATCH] Fix leaky VIEWs for RLS
Heikki Linnakangas heikki.linnakan...@enterprisedb.com 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] Idea for getting rid of VACUUM FREEZE on cold pages
Bruce Momjian br...@momjian.us 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] Idea for getting rid of VACUUM FREEZE on cold pages
Tom Lane wrote: Bruce Momjian br...@momjian.us 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 br...@momjian.ushttp://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
On 6/3/2010 10:57 PM, Robert Haas wrote: On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck janwi...@yahoo.com 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] [PATCH] Fix leaky VIEWs for RLS
On 04/06/10 22:33, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com 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] Exposing the Xact commit order to the user
On 6/4/2010 10:44 AM, Greg Stark wrote: On Fri, Jun 4, 2010 at 2:32 AM, Robert Haas robertmh...@gmail.com 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 heikki.linnakan...@enterprisedb.com 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] Idea for getting rid of VACUUM FREEZE on cold pages
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Bruce Momjian br...@momjian.us 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] Synchronization levels in SR
On Fri, Jun 4, 2010 at 3:35 PM, Jan Wieck janwi...@yahoo.com wrote: On 6/3/2010 10:57 PM, Robert Haas wrote: On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck janwi...@yahoo.com 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
Excerpts from Bruce Momjian's message of vie jun 04 15:39:07 -0400 2010: Tom Lane wrote: Bruce Momjian br...@momjian.us 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 alvhe...@commandprompt.com 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] Exposing the Xact commit order to the user
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] Synchronization levels in SR
On 6/4/2010 4:22 PM, Robert Haas wrote: On Fri, Jun 4, 2010 at 3:35 PM, Jan Wieck janwi...@yahoo.com 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] Idea for getting rid of VACUUM FREEZE on cold pages
Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of vie jun 04 15:39:07 -0400 2010: Tom Lane wrote: Bruce Momjian br...@momjian.us 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 br...@momjian.ushttp://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
[HACKERS]
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]
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
Re: [HACKERS] including PID or backend ID in relpath of temp rels
On Mon, May 17, 2010 at 2:10 PM, Jim Nasby deci...@decibel.org 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 tdigits_digits 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] recovery getting interrupted is not so unusual as it used to be
On Jun 3, 2010, at 5:25 , Robert Haas wrote: On Wed, Jun 2, 2010 at 10:34 PM, Florian Pflug f...@phlo.org 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] recovery getting interrupted is not so unusual as it used to be
On Fri, Jun 4, 2010 at 8:21 PM, Florian Pflug f...@phlo.org wrote: On Jun 3, 2010, at 5:25 , Robert Haas wrote: On Wed, Jun 2, 2010 at 10:34 PM, Florian Pflug f...@phlo.org 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] functional call named notation clashes with SQL feature
On Wed, May 26, 2010 at 9:28 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Wed, May 26, 2010 at 8:21 PM, Tom Lane t...@sss.pgh.pa.us 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
On Sat, Jun 5, 2010 at 2:20 AM, Robert Haas robertmh...@gmail.com 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
On Fri, Jun 4, 2010 at 9:55 PM, Joseph Adams joeyadams3.14...@gmail.com 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
