Re: [HACKERS] patch: Add columns via CREATE OR REPLACE VIEW
ALTER VIEW does not sound useful for me. CREATE OR REPLACE VIEW should create or replace view and only thing that should be same is the name of the view. It's up to Postgres to invalidate all plans and up to developer to make sure that all places where his view is used will stay still working. All this discussion about matching up columns and AI seems totally useless to me :) On Fri, Aug 8, 2008 at 4:41 AM, Robert Haas [EMAIL PROTECTED] wrote: Well, my feeling is that if we are inventing a new feature we ought not paint ourselves into a corner by failing to consider what will happen when obvious extensions to the feature are attempted. Whether the present patch is self-consistent is not the question --- the question is do we have a self-consistent vision of how we will later do the other stuff like renaming, changing column type, etc. If we can work out that design, I think that's great. However, it doesn't actually 100% matter whether we know the one true way that we will definitely implement those features - it only matters that none of the things we might choose are inconsistent with what we're doing now. In order to avoid being AI-complete, REPLACE VIEW needs some kind of straightforward algorithm for matching up the old and new target lists. AFAICS, the only thing to decide here is what you want to use as the key. There are three possibilities that I can think of: [1] name, [2] position, [3] both name and position. It's axiomatic that REPLACE VIEW can't be given the capability to make any modification that involves changing the key field, so in [1] you can't rename columns, in [2] you can't reorder columns, and in [3] you can't do either. Furthermore, in [2], you also can't support dropping columns, because a drop is indistinguishable from renaming and retyping every column from the point of the drop onwards. Therefore, the maximum set of operations REPLACE VIEW can potentially support in each scenario are: [1] add column, change type, drop column, reorder columns [2] add column, change type, rename [3] add column, change type, drop column The actual set of operations supported may be less either because of implementation limitations or because you don't want to provide users with a foot-gun. ISTM that allowing REPLACE VIEW to do renames in scenario [2] can be pretty much rejected outright as a violation of the principle of least surprise - there is an enormous danger of someone simultaneously renaming and retyping a whole series of columns when they instead intended to drop a column. Similarly, in scenario [1] or [3], ISTM that allowing someone to drop columns using REPLACE VIEW is something of a foot-gun unless we are in scenario [1] and reordering columns is also implemented, because users who don't RTFM will try to reorder columns and it will succeed and fail erratically according to whether there are dependencies that prevent dropping and re-adding whatever subset of columns need to be shuffled to create the same effect as would be produced by reordering. However, in any scenario, I can't see how adding columns or changing column types is likely to produce any confusion or user-unexpected behavior. Perhaps I'm missing something? Personally, I favor scenario [1]. I hardly ever rename database columns, and I don't mind needing to ALTER VIEW RENAME COLUMN on those rare occasions when I do, but I add new columns to my tables (which then also need to be added to my views) on a regular basis. If I could keep groups of related columns together in the table and view definitions without having to drop and recreate the objects, that would be awesome. But I'm not sure it's worth the amount of implementation that would be required to get there, especially if all of that implementation would need to be done by me (and double-especially if none of it would likely be included in -core). Of course, as I said before, nothing we do in REPLACE VIEW precludes having a powerful implementation of ALTER VIEW. But I think the coding to make ALTER VIEW do these operations is a lot trickier, because you have to deal with modifying the query that's already in place piecemeal as you make your changes to the view. It's not that it can't be done, but I doubt it can be done in an 8K patch, and as mentioned upthread, it certainly can't be done in a fully general way... you will still frequently need to CREATE OR REPLACE VIEW afterwards. To put that another way, ALTER TABLE is a complicated beast because you have to worry about how you're going to handle the existing data, and ALTER VIEW will be a complicated beast for the analogous reason that you need to worry about handing the existing rewrite rule. But at the moment when a REPLACE VIEW command is executed, that problem goes away, because now you have the query in your hand and just need to make the relation match it without breaking any of the dependencies. ...Robert --
Re: [HACKERS] Parsing of pg_hba.conf and authentication inconsistencies
Stephen Frost wrote: Magnus, * Magnus Hagander ([EMAIL PROTECTED]) wrote: I thought of another issue with this. My grand plan includes being able to do username mapping (per pg_ident.conf) for other authentication methods than ident. Specifically this would be interesting for all external methods, like gssapi/sspi/kerberos/ldap. I was originally planning to allow each row in pg_hba to specify it's own pg_ident.conf if necessary (so you could map LDAP and GSSAPI differently, for example, or map two different kerberos realms differently). To be able to load these, the postmaster would need to know about them, which means it'd have to parse that data out anyway. I certainly like the concept of having them be in seperate files. The other way to do that is to simply say that all external mapping will use pg_ident.conf, and the only thing you can specify on a per-row basis is use map: yes or no. This decreases the flexibility, but would not require the postmaster to do the parsing. I don't think it makes sense to have multiple different auth types using the same mappings... For Kerberos, as an example, we should support [EMAIL PROTECTED] as an option for the mapping, but that might not make sense for LDAP, which might have cn=User,ou=people,dc=example,dc=com, and neither of those really make sense for ident. Mashing all of those together would make each auth type supporting the mapping have to search through the list trying to make sense of some mappings and throwing away others, just ugly all around.. Yeah. I think the question there is just - how likely is it that the same installation actually uses 1 authentication method. Personally, I think it's not very uncommon at all, but fact remains that as long as you only use one of them at a time, using a shared file doesn't matter. What do people think about these? I know Stephen for example really want that feature so - would that restriction make it a lot less useful for you? If we really wanted to keep it to a single *file*, then I think there should be a way to key rows in the pg_hba.conf to sets-of-rows in the mapping file. eg: have an option of 'mapkey=xyz' in pg_hba, and then 'xyz' as the first column of the mapping file, with it being repeated across rows to form that mapping table. Yuck. Honestly, that seems even uglier :-) It wouldn't be very easy/clean to do that w/o breaking the existing structure of pg_ident though, which makes me feel like using seperate files is probably the way to go. Yeah, thats my feeling as well. Now, can someone figure out a way to do that without parsing the file in the postmaster? (And if we do parse it, there's no point in not storing the parsed version, IMHO). And if not, the question it comes down to is which is most important - keeping the parsing away, or being able to do this ;-) //Magnus -- 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: Add columns via CREATE OR REPLACE VIEW
If you accept the idea that column identity should be based on column name, then the only two operations that are really necessary are CREATE OR REPLACE VIEW and ALTER VIEW RENAME COLUMN, and it is 100% clear what the semantics of those operations should be. +1 I think this would be an easily useable and understandable concept. I also fully support Robert's reasoning in his next reply to Tom, detailing why his patch's provided functionality is acceptable. Andreas PS: alter view in O does not change the base definition, it only allows modifying view constraints. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Oprofile with postgresql
Hi all:
Recently i do a test of postgresql. To get more information of the
functions in PostgreSQL, i use
opgprof and opannotate, which are two tools in Oprofile. But i can't work
with the tools correctly.
PostgreSQL is compiled with -g option and the errors are like this:
opgprof error: parse_filename() invalid filename:
/var/lib/oprofile/samples/current/{root}/var/lib/oprofile/samples/current/{root}/home/ubuntu/tpcc-uva/bin/tm/{dep}/{anon:[vdso]}/7208.0x7fff7a972000.0x7fff7a974000/CPU_CLK_UNHALTED.9.0.all.all.all/{dep}/{root}/var/lib/oprofile/samples/current/{root}/home/ubuntu/tpcc-uva/bin/tm/{dep}/{anon:[vdso]}/7208.0x7fff7a972000.0x7fff7a974000/CPU_CLK_UNHALTED.9.0.all.all.all/{cg}/{root}/usr/bin/oprofiled/CPU_CLK_UNHALTED.9.0.all.all.all
and opannotate
warning: [heap] (tgid:7302 range:0x8e7000-0xa18000) could not be found.
warning: [vdso] (tgid:7295 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7296 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7297 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7298 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7299 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7300 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7301 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7302 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
opannotate (warning): unable to open for reading: aset.c
opannotate (warning): unable to open for reading: heaptuple.c
opannotate (warning): unable to open for reading: bufmgr.c
opannotate (warning): unable to open for reading: execQual.c
opannotate (warning): unable to open for reading: list.c
opannotate (warning): unable to open for reading: nbtree.c
opannotate (warning): unable to open for reading: fmgr.c
opannotate (warning): unable to open for reading: catcache.c
opannotate (warning): unable to open for reading: nodeIndexscan.c
opannotate (warning): unable to open for reading: clauses.c
opannotate (warning): unable to open for reading: ri_triggers.c
opannotate (warning): unable to open for reading: lock.c
opannotate (warning): unable to open for reading: planner.c
opannotate (warning): unable to open for reading: selfuncs.c
opannotate (warning): unable to open for reading: postgres.c
opannotate (warning): unable to open for reading: trigger.c
...
if i want to use the two tools on PostgreSQL , what should i do?
Best wishes
Yyan
Re: [HACKERS] For what should pg_stop_backup wait?
On Fri, 2008-08-08 at 11:47 +0900, Fujii Masao wrote:
On Thu, Aug 7, 2008 at 11:34 PM, Simon Riggs [EMAIL PROTECTED] wrote:
In this situation, the history file (00010004.0020.backup)
is behind the stoppoint (00010004) in the alphabetic order.
So, pg_stop_backup should wait for both the stoppoint and the history
file, I think.
OK, I see that now.
! while (!XLogArchiveCheckDone(stopxlogfilename, false))
If a concurrent checkpoint removes the status file before
XLogArchiveCheckDone,
pg_stop_backup continues waiting forever. This is undesirable behavior.
I think it will only get removed by the second checkpoint, not the
first. So the risk of that happening seems almost certainly impossible.
But we'll put in a check just in case.
Yes, statement_timeout may help. But, I don't want to use it, because the
*successful* backup is canceled.
How about checking whether the stoppoint was archived by comparing with
the last WAL archived. The archiver process can tell the last WAL archived.
Or, we can calculate it from the status file.
I think its easier to test whether the stopxlogfilename still exists in
pg_xlog. If not, we know it has been archived away. We can add that as
an extra condition inside the loop.
So thinking we should test XLogArchiveCheckDone() for both
stopxlogfilename and history file and then stat for the stop WAL file:
BackupHistoryFileName(histfilepath, ThisTimeLineID, _logId, _logSeg,
startpoint.xrecoff %
XLogSegSize);
seconds_before_warning = 60;
waits = 0;
while (!XLogArchiveCheckDone(histfilepath, false) ||
!XLogArchiveCheckDone(stopxlogfilename, false))
{
struct stat stat_buf;
charxlogpath[MAXPGPATH];
/*
* Check to see if file has already been archived and WAL file
* removed by a concurrent checkpoint
*/
snprintf(xlogpath, MAXPGPATH, XLOGDIR /%s, stopxlogfilename);
if (XLogArchiveCheckDone(histfilepath, false)
stat(xlogpath, stat_buf) != 0)
break;
CHECK_FOR_INTERRUPTS();
pg_usleep(100L);
if (++waits = seconds_before_warning)
{
seconds_before_warning *= 2; /* This wraps in 10
years... */
elog(WARNING, pg_stop_backup() waiting for archive to
complete
(%d seconds delay),
waits);
}
}
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Verbosity of Function Return Type Checks
Hi,
Yesterday I needed to fiddle with PostgreSQL internals to be able to
debug a PL/pgSQL procedure returning a set of records. I attached the
patch I used to increase the verbosity of error messages related with
function return type checks. I'll be appreciated if any developer could
commit this patch (or a similar one) into the core.
Regards.
Index: src/pl/plpgsql/src/pl_exec.c
===
RCS file: /projects/cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.216
diff -u -r1.216 pl_exec.c
--- src/pl/plpgsql/src/pl_exec.c 16 May 2008 18:34:51 - 1.216
+++ src/pl/plpgsql/src/pl_exec.c 8 Aug 2008 11:52:02 -
@@ -190,7 +190,7 @@
Oid reqtype, int32 reqtypmod,
bool isnull);
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
-static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
+static void validate_tupdesc_compat(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
static void plpgsql_create_econtext(PLpgSQL_execstate *estate);
static void free_var(PLpgSQL_var *var);
@@ -386,11 +386,12 @@
{
case TYPEFUNC_COMPOSITE:
/* got the expected result rowtype, now check it */
- if (estate.rettupdesc == NULL ||
- !compatible_tupdesc(estate.rettupdesc, tupdesc))
+ if (!estate.rettupdesc)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg(returned record type does not match expected record type)));
+ errmsg(returned record type does not match
+ expected record type)));
+ validate_tupdesc_compat(tupdesc, estate.rettupdesc);
break;
case TYPEFUNC_RECORD:
@@ -707,11 +708,8 @@
rettup = NULL;
else
{
- if (!compatible_tupdesc(estate.rettupdesc,
-trigdata-tg_relation-rd_att))
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg(returned tuple structure does not match table of trigger event)));
+ validate_tupdesc_compat(trigdata-tg_relation-rd_att,
+estate.rettupdesc);
/* Copy tuple to upper executor memory */
rettup = SPI_copytuple((HeapTuple) DatumGetPointer(estate.retval));
}
@@ -2202,10 +2200,7 @@
errmsg(record \%s\ is not assigned yet,
rec-refname),
errdetail(The tuple structure of a not-yet-assigned record is indeterminate.)));
- if (!compatible_tupdesc(tupdesc, rec-tupdesc))
- ereport(ERROR,
-(errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg(wrong record type supplied in RETURN NEXT)));
+ validate_tupdesc_compat(rec-tupdesc, tupdesc);
tuple = rec-tup;
}
break;
@@ -2311,10 +2306,7 @@
stmt-params);
}
- if (!compatible_tupdesc(estate-rettupdesc, portal-tupDesc))
- ereport(ERROR,
-(errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg(structure of query does not match function result type)));
+ validate_tupdesc_compat(portal-tupDesc, estate-rettupdesc);
while (true)
{
@@ -5138,23 +5130,32 @@
}
/*
- * Check two tupledescs have matching number and types of attributes
+ * Validates compatibility of supplied TupleDesc's by checking # and type of
+ * available arguments.
*/
-static bool
-compatible_tupdesc(TupleDesc td1, TupleDesc td2)
+static void
+validate_tupdesc_compat(TupleDesc td1, TupleDesc td2)
{
- int i;
+ int i;
if (td1-natts != td2-natts)
- return false;
+ ereport(ERROR,
+(errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg(Number of returned columns (%d) does not match
+ expected column count (%d).,
+ td1-natts, td2-natts)));
for (i = 0; i td1-natts; i++)
- {
if (td1-attrs[i]-atttypid != td2-attrs[i]-atttypid)
- return false;
- }
-
- return true;
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg(Returned record type (%s) does not match
+ expected record type (%s) in column %d (%s).,
+ format_type_with_typemod(td1-attrs[i]-atttypid,
+ td1-attrs[i]-atttypmod),
+ format_type_with_typemod(td2-attrs[i]-atttypid,
+ td2-attrs[i]-atttypmod),
+ (1+i), NameStr(td2-attrs[i]-attname;
}
/* --
--
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: Add columns via CREATE OR REPLACE VIEW
Zeugswetter Andreas OSB sIT [EMAIL PROTECTED] writes: If you accept the idea that column identity should be based on column name, then the only two operations that are really necessary are CREATE OR REPLACE VIEW and ALTER VIEW RENAME COLUMN, and it is 100% clear what the semantics of those operations should be. +1 It's nice, it's simple, and it's unimplementable. At least not without huge changes in the representation of stored views, which would likely lead to failure to follow spec-required behavior in other ways. Other views are going to refer to the columns of this one by *number*, not name, and it's not clear to me how you're going to preserve column number identity with this approach. 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] For what should pg_stop_backup wait?
On Fri, 2008-08-08 at 12:57 +0100, Simon Riggs wrote:
Yes, statement_timeout may help. But, I don't want to use it, because the
*successful* backup is canceled.
How about checking whether the stoppoint was archived by comparing with
the last WAL archived. The archiver process can tell the last WAL archived.
Or, we can calculate it from the status file.
I think its easier to test whether the stopxlogfilename still exists in
pg_xlog. If not, we know it has been archived away. We can add that as
an extra condition inside the loop.
So thinking we should test XLogArchiveCheckDone() for both
stopxlogfilename and history file and then stat for the stop WAL file:
This seems better.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Index: src/backend/access/transam/xlog.c
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.316
diff -c -r1.316 xlog.c
*** src/backend/access/transam/xlog.c 13 Jul 2008 20:45:47 - 1.316
--- src/backend/access/transam/xlog.c 8 Aug 2008 13:56:40 -
***
*** 1165,1170
--- 1165,1184
/* Retry creation of the .ready file */
if (create_if_missing)
XLogArchiveNotify(xlog);
+ else
+ {
+ char xlogpath[MAXPGPATH];
+
+ snprintf(xlogpath, MAXPGPATH, XLOGDIR /%s, xlog);
+
+ /*
+ * Check to see if the WAL file has been removed by checkpoint,
+ * which implies it has already been archived, and explains why we
+ * can't see a status file for it.
+ */
+ if (stat(xlogpath, stat_buf) != 0)
+ return true;
+ }
return false;
}
***
*** 6721,6735
CleanupBackupHistory();
/*
! * Wait until the history file has been archived. We assume that the
! * alphabetic sorting property of the WAL files ensures the last WAL
! * file is guaranteed archived by the time the history file is archived.
*
* We wait forever, since archive_command is supposed to work and
* we assume the admin wanted his backup to work completely. If you
* don't wish to wait, you can SET statement_timeout = xx;
*
! * If the status file is missing, we assume that is because it was
* set to .ready before we slept, then while asleep it has been set
* to .done and then removed by a concurrent checkpoint.
*/
--- 6735,6748
CleanupBackupHistory();
/*
! * Wait until both the last WAL file filled during backup and the
! * history file have been archived.
*
* We wait forever, since archive_command is supposed to work and
* we assume the admin wanted his backup to work completely. If you
* don't wish to wait, you can SET statement_timeout = xx;
*
! * If the status files are missing, we assume that is because it was
* set to .ready before we slept, then while asleep it has been set
* to .done and then removed by a concurrent checkpoint.
*/
***
*** 6739,6745
seconds_before_warning = 60;
waits = 0;
! while (!XLogArchiveCheckDone(histfilepath, false))
{
CHECK_FOR_INTERRUPTS();
--- 6752,6759
seconds_before_warning = 60;
waits = 0;
! while (!XLogArchiveCheckDone(histfilepath, false) ||
! !XLogArchiveCheckDone(stopxlogfilename, false))
{
CHECK_FOR_INTERRUPTS();
--
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] Oprofile with postgresql
=?GB2312?B?tN7R0mNjdWl5eWFuQHNpbmEuY29t?= [EMAIL PROTECTED] writes: Hi all: Recently i do a test of postgresql. To get more information of the functions in PostgreSQL, i use opgprof and opannotate, which are two tools in Oprofile. But i can't work with the tools correctly. I think you need to read the oprofile documentation a bit more and possibly contact an oprofile mailing list for help. This isn't a Postgres-related question. FWIW, I've used oprofile many times with postgres; it works as expected. I'd be the first to agree it's got a steep learning curve though. 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] Parsing of pg_hba.conf and authentication inconsistencies
Magnus, * Magnus Hagander ([EMAIL PROTECTED]) wrote: Yeah. I think the question there is just - how likely is it that the same installation actually uses 1 authentication method. Personally, I think it's not very uncommon at all, but fact remains that as long as you only use one of them at a time, using a shared file doesn't matter. We use multiple authentication types *alot*.. ident, md5, kerberos, and gssapi are all currently in use on our systems. ident for local unix logins, md5 for 'role' accounts and software the doesn't understand kerberos, kerberos/gssapi depending on the age of the client library connecting. Oh, and we use pam too.. We use some mappings now with ident, which I'd expect to continue to do, and I've got definite plans for mappings under Kerberos/GSSAPI once it's supported.. It wouldn't be very easy/clean to do that w/o breaking the existing structure of pg_ident though, which makes me feel like using seperate files is probably the way to go. Yeah, thats my feeling as well. Now, can someone figure out a way to do that without parsing the file in the postmaster? (And if we do parse it, there's no point in not storing the parsed version, IMHO). And if not, the question it comes down to is which is most important - keeping the parsing away, or being able to do this ;-) I don't have an answer wrt the parsing issue, but I definitely want to be able to do this. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Verbosity of Function Return Type Checks
Volkan YAZICI wrote: Yesterday I needed to fiddle with PostgreSQL internals to be able to debug a PL/pgSQL procedure returning a set of records. I attached the patch I used to increase the verbosity of error messages related with function return type checks. I'll be appreciated if any developer could commit this patch (or a similar one) into the core. I think this is a good idea, but the new error messages need more work. Have a look at the message style guidelines please, http://www.postgresql.org/docs/8.3/static/error-style-guide.html Particularly I think you need to keep the original errmsg() and add the new messages as errdetail(). (I notice that there's the slight problem that the error messages are different for the different callers.) Also, please use context diffs. -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] CommitFest July Over
Markus, Maybe it's just me, but I don't quite understand the concept of RRR. If I get some spare cycles to review patches, I certainly want to choose mysqlf which patch I'm going to review. Why is the CF Manager doing any assignment of patches? This was actually by request of several reviewers, who said they could do something but didn't have a preference which patch to review. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] autovacuum and TOAST tables
Hi,
Here's a patch to make autovacuum process TOAST tables separately from
main tables.
The most important change is that when called from autovac, vacuum does
not process the TOAST table at all. It will only do so when the stats
for the TOAST table say that it needs vacuuming. (A user-invoked vacuum
still processes TOAST tables normally.)
Per previous discussion, the autovac code is now doing two passes over
pg_class.
There's two things I'm not happy about in this patch:
1. it uses a List to keep the mapping of heap-toast Oids. This is
needed to be able to fetch the main rel's pg_autovacuum entry to process
the toast table. This incurs in O(n^2) behavior.
2. the expected relkind business is gone; it's not easy to pass the
correct relkind down from autovac, and at the same time have a
reasonable thing to pass down from user-invoked vacuum. Right now what
the patch does is check that the rel to vacuum is either
RELKIND_RELATION or _TOASTVALUE.
(I admit that my unhappiness about the second is mild, though.)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/commands/vacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.375
diff -c -p -r1.375 vacuum.c
*** src/backend/commands/vacuum.c 5 Jun 2008 15:47:32 - 1.375
--- src/backend/commands/vacuum.c 8 Aug 2008 16:42:02 -
*** static BufferAccessStrategy vac_strategy
*** 213,220
static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
const char *stmttype);
static void vac_truncate_clog(TransactionId frozenXID);
! static void vacuum_rel(Oid relid, VacuumStmt *vacstmt, char expected_relkind,
! bool for_wraparound);
static void full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt);
static void scan_heap(VRelStats *vacrelstats, Relation onerel,
VacPageList vacuum_pages, VacPageList fraged_pages);
--- 213,220
static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
const char *stmttype);
static void vac_truncate_clog(TransactionId frozenXID);
! static void vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast,
! bool for_wraparound);
static void full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt);
static void scan_heap(VRelStats *vacrelstats, Relation onerel,
VacPageList vacuum_pages, VacPageList fraged_pages);
*** static Size PageGetFreeSpaceWithFillFact
*** 268,273
--- 268,276
* OID to be processed, and vacstmt-relation is ignored. (The non-invalid
* case is currently only used by autovacuum.)
*
+ * do_toast is passed as FALSE by autovacuum, because it processes TOAST
+ * tables separately.
+ *
* for_wraparound is used by autovacuum to let us know when it's forcing
* a vacuum for wraparound, which should not be auto-cancelled.
*
*** static Size PageGetFreeSpaceWithFillFact
*** 281,287
* at transaction commit.
*/
void
! vacuum(VacuumStmt *vacstmt, Oid relid,
BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel)
{
const char *stmttype = vacstmt-vacuum ? VACUUM : ANALYZE;
--- 284,290
* at transaction commit.
*/
void
! vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel)
{
const char *stmttype = vacstmt-vacuum ? VACUUM : ANALYZE;
*** vacuum(VacuumStmt *vacstmt, Oid relid,
*** 433,439
Oid relid = lfirst_oid(cur);
if (vacstmt-vacuum)
! vacuum_rel(relid, vacstmt, RELKIND_RELATION, for_wraparound);
if (vacstmt-analyze)
{
--- 436,442
Oid relid = lfirst_oid(cur);
if (vacstmt-vacuum)
! vacuum_rel(relid, vacstmt, do_toast, for_wraparound);
if (vacstmt-analyze)
{
*** vac_truncate_clog(TransactionId frozenXI
*** 975,982
* At entry and exit, we are not inside a transaction.
*/
static void
! vacuum_rel(Oid relid, VacuumStmt *vacstmt, char expected_relkind,
! bool for_wraparound)
{
LOCKMODE lmode;
Relation onerel;
--- 978,984
* At entry and exit, we are not inside a transaction.
*/
static void
! vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound)
{
LOCKMODE lmode;
Relation onerel;
*** vacuum_rel(Oid relid, VacuumStmt *vacstm
*** 1013,1020
* by autovacuum; it's used to avoid cancelling a vacuum that was
* invoked in an emergency.
*
! * Note: this flag remains set until CommitTransaction or
! * AbortTransaction. We don't want to clear it until we reset
* MyProc-xid/xmin, else OldestXmin might appear to go backwards,
* which is probably Not Good.
*/
--- 1015,1022
* by autovacuum; it's used to avoid cancelling a vacuum that was
Re: [HACKERS] Proposal of SE-PostgreSQL patches [try#2]
KaiGai Kohei wrote: On the WiKi of CommitFest:Sep, http://wiki.postgresql.org/wiki/CommitFest:2008-09 The entry of SE-PostgreSQL points a message when I submitted older version of our patch set. But the latest ones are listed on another message. Please add a link to the following message for our convenience: http://archives.postgresql.org/pgsql-hackers/2008-07/msg01365.php Hey, given the amount of work still to go on this, don't you think you should get a wiki account so that you can add comments yourself? --Josh -- 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] Avoiding Application Re-test
[EMAIL PROTECTED] (Simon Riggs) writes: Every time we introduce a feature that changes output, we just put an if test in saying sql_compatibility = X, (the release we added feature). Straightforward, futureproof. Cool. This is somewhat like the way that some shells try to emulate others; for instance, zsh tries to emulate sh or ksh when invoked by those names. There are two problems with this: a) The conspicuous phrase, try to emulate Which leaves open several questions: - What if we can't? - What about when we don't want to (e.g. - because the new version has a new behaviour that we *DO* want)? - What if some semantic change takes place that we didn't realize should have been open to the try to emulate? Such as, where there's an ordering change... b) It requires adding a new, not-previous-existant effort to classify changes and put in logic to allow controlling whether we use legacy logic or new logic. That seems likely to get mighty messy to me, requiring blabbering control code throughout the code base. I don't think we actually get the future proofness that you're suggesting. It would be nice if we could, but that seems unrealistic. -- let name=cbbrowne and tld=linuxdatabases.info in String.concat @ [name;tld];; http://linuxfinances.info/info/spiritual.html Rules of the Evil Overlord #204. I will hire an entire squad of blind guards. Not only is this in keeping with my status as an equal opportunity employer, but it will come in handy when the hero becomes invisible or douses my only light source. http://www.eviloverlord.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] autovacuum and TOAST tables
Alvaro Herrera [EMAIL PROTECTED] writes: There's two things I'm not happy about in this patch: 1. it uses a List to keep the mapping of heap-toast Oids. This is needed to be able to fetch the main rel's pg_autovacuum entry to process the toast table. This incurs in O(n^2) behavior. Use a dynahash table instead? 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
[HACKERS] Replay attack of query cancel
It occurred to me a while ago that our query cancel messages are sent unencrypted, even when SSL is otherwise used. That's not a big issue on its own, because the cancellation message only contains the backend PID and the cancellation key, but it does open us to a replay attack. After the first query in a connection has been cancelled, an eavesdropper can reuse the backend PID and cancellation key to cancel subsequent queries on the same connection. We discussed this on the security list, and the consensus was that this isn't worth a quick fix and a security release, because - it only affects applications that use query cancel, which is rare - it only affects SSL encrypted connections (the point is moot non-encrypted connections, as you can just snatch the cancel key from the initial message) - it only let's you cancel queries, IOW it's only a DOS attack. - there's no simple fix. However, it is something to keep in mind, and perhaps fix for the next release. One idea for fixing this is to make cancellation keys disposable, and automatically issue a new one through the main connection when one is used, but that's not completely trivial, and requires a change in both the clients and the server. Another idea is to send the query cancel message only after SSL authentication, but that is impractical for libpq because we PQcancel needs to be callable from a signal handler. -- 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] Replay attack of query cancel
Heikki Linnakangas wrote: One idea for fixing this is to make cancellation keys disposable, and automatically issue a new one through the main connection when one is used, but that's not completely trivial, and requires a change in both the clients and the server. Another idea is to send the query cancel message only after SSL authentication, but that is impractical for libpq because we PQcancel needs to be callable from a signal handler. I wonder if we can do something diffie-hellman'ish, where we have a parameter exchanged in the initial SSL'ed handshake, which is later used to generate new cancel keys each time the previous one is used. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] IN vs EXISTS equivalence
Kevin Grittner [EMAIL PROTECTED] writes: I'm adding some NOT EXISTS examples to the thread for completeness of what someone might want to address while working on it. For two queries which can easily be shown (to a human viewer, anyway) to return identical results, I see performance differences of over five orders of magnitude. I've been looking at this a bit and have an action plan worked out. I believe that the optimizable cases for EXISTS are those where the EXISTS() is either at the top level of WHERE, or just underneath a NOT, and the contained subselect: * has no set operations (UNION etc), grouping, set-returning functions in the SELECT list, LIMIT, or a few other funny cases * references outer-level variables only in its WHERE clause. Given these conditions, an EXISTS is equivalent to a standard semijoin between the outer relations used in its WHERE clause and the sub-select with the WHERE removed, where the join condition is just the WHERE clause. (A semijoin returns one copy of each LHS row for which there exists at least one RHS row satisfying the join condition.) Similarly, a NOT EXISTS is equivalent to a standard anti-semijoin. (An anti-semijoin returns one copy of each LHS row for which there exists no RHS row satisfying the join condition.) So what I think we should do is implement JOIN_SEMI and JOIN_ANTI as variant outer-join types in the planner and executor, and convert EXISTS into those. JOIN_SEMI would replace the existing JOIN_IN support. (It's effectively the same thing so far as the executor is concerned, but there are some places in the planner that assume an IN join condition consists of one or more btree equality operators. I'm envisioning folding the current planner support for IN into the more general outer-join planning infrastructure, and fixing it so that it doesn't assume the join clause must be of that form.) Explicit support for JOIN_ANTI is probably not strictly necessary: we could represent it using the LEFT JOIN WHERE rhs-variable IS NULL hack. However this only works if the join's ON-condition can be proved strict for at least one RHS variable, which is an assumption I'd rather not require for optimizing EXISTS. Also, the planner should be able to make much better estimates of the size of an antijoin result if it has an explicit concept that that's what's happening. (The existing kluge in nulltestsel() is not only ugly but has little hope of ever giving an accurate estimate.) So I'd prefer to go the other way: make the planner recognize the IS NULL trick and remove the IS NULL clause in favor of marking the LEFT JOIN as being really an antijoin. As far as IN goes: IN at the top level of WHERE can still be optimized to a semijoin under the same conditions as now. NOT IN is a lot trickier, for the same reason that typically trips up novices who try to use it: if any row of the subselect produces a NULL comparison result, then it is impossible for the NOT IN to result in TRUE, which means that it does not function as a standard antijoin. I thought about optimizing it only in the case where we can prove that the subselect outputs and the compared-to values are known NOT NULL (which in typical cases we could prove by looking for NOT NULL constraints on those table columns). The trouble with this is that that's not a sufficient condition: you must also assume that the comparison operator involved never yields NULL for non-null inputs. That might be okay for btree comparison functions but it's not a very comfy assumption in general; we certainly haven't got any explicit knowledge that any functions are guaranteed to act that way. So this case might be worth doing later but I'm not feeling excited about it. We generally tell people to avoid NOT IN and I'm happy to keep on saying that. Comments? 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] Replay attack of query cancel
Alvaro Herrera [EMAIL PROTECTED] writes: I wonder if we can do something diffie-hellman'ish, where we have a parameter exchanged in the initial SSL'ed handshake, which is later used to generate new cancel keys each time the previous one is used. Seems like the risk of getting out of sync would outweigh any benefits. Lose one cancel message in the network, you have no hope of getting any more accepted. 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] Replay attack of query cancel
Alvaro Herrera wrote: Heikki Linnakangas wrote: One idea for fixing this is to make cancellation keys disposable, and automatically issue a new one through the main connection when one is used, but that's not completely trivial, and requires a change in both the clients and the server. Another idea is to send the query cancel message only after SSL authentication, but that is impractical for libpq because we PQcancel needs to be callable from a signal handler. I wonder if we can do something diffie-hellman'ish, where we have a parameter exchanged in the initial SSL'ed handshake, which is later used to generate new cancel keys each time the previous one is used. Seems much more complex than needed. IIRC, the protocol allows us (at least does not explicitly forbid) to issue new cancel keys at any time. And libpq will, again IIRC, accept them. So we could just change the server to issue a new cancel key whenever it has used the previous one (since the new key will then be sent over the encrypted channel, we're safe). The problem was (third IIRC here :-P) in other clients, such as the JDBC driver (I think that one was checked specifically) which currently only accept the BackendKeyData message during startup. All drivers not based on libpq would have to be checked and potentially updated, but it's sitll a lot easier than DHing or so. //Magnus -- 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] Replay attack of query cancel
Magnus Hagander [EMAIL PROTECTED] writes: The problem was (third IIRC here :-P) in other clients, such as the JDBC driver (I think that one was checked specifically) which currently only accept the BackendKeyData message during startup. All drivers not based on libpq would have to be checked and potentially updated, but it's sitll a lot easier than DHing or so. The other problem was getting the new cancel key from the postmaster to the backend and thence to the client (hopefully in a timely manner), recognizing that (a) we don't want the postmaster touching shared memory very much, and certainly not engaging in any locking behavior; (b) backends feel free to ignore SIGINT when they're not doing anything. Certainly the prospect of a de facto protocol change is the bigger problem, but there are nontrivial implementation issues in the server too. If we were going to make it a de jure protocol change (ie new version number) instead of just hoping nobody notices the behavioral difference, I'd be inclined to think about widening the cancel key, too. 32 bits ain't that much randomness anymore. 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] IN vs EXISTS equivalence
Tom Lane [EMAIL PROTECTED] wrote: I believe that the optimizable cases for EXISTS are those where the EXISTS() is either at the top level of WHERE, or just underneath a NOT, The rest of the plan makes sense to me, but this part seems narrow. There's probably a good reason for that which is beyond my depth, but attached is a view that is used for calculating statistics for a database which is primarily used for case management purposes. If EXISTS could also be optimized in the contexts used there, it would be great. For context, this view references three other non-trivial views (MatterHistSearch, MatterHistStage, MatterHistStatus), and does perform acceptably, so it's not a matter of complaining if it can't work here, just providing a real-world example of other useful contexts for EXISTS which might be worth covering if possible. This view is used in a large number of queries, mostly either selecting a single date with other selection criteria or counting rows which match a set of matterNo values selected on complex criteria. By the way, this view was totally unusable under 8.2. Showing how it worked under 8.3 was all it took to get them to expedite the upgrade. These views, possible because of improvements in 8.3, saved countless programmer days (to quote one of the programmers). -Kevin CREATE VIEW MatterDateStat AS SELECT S.matterNo, CAST(D.date AS DateT) AS date, S.newStageCode AS stage, CAST(COALESCE(O.newStatusCode, 'OP') AS MatterStatusCodeT) AS status, ( EXISTS ( SELECT * FROM MatterHistSearch MHS1 JOIN MatterEventCode MEC1 ON (MEC1.matterEventCode = MHS1.matterEventCode) WHERE MHS1.matterNo = S.matterNo AND MHS1.date = D.date AND MEC1.newMaintCode = 'INA' AND NOT EXISTS ( SELECT * FROM MatterHistSearch MHS2 JOIN MatterEventCode MEC2 ON (MEC2.matterEventCode = MHS2.matterEventCode) WHERE MHS2.matterNo = S.matterNo AND MHS2.date = D.date AND (MHS2.date, MHS2.matterHistRowOrder) (MHS1.date, MHS1.matterHistRowOrder) AND MEC2.removeMaintCode = 'INA' ) ) ) AS isOnHold, S.parentMatter, S.matterHistSeqNo, -- NULL means that the stage is defaulting from the matter filing date. S.areaOfLawCode, S.county, S.enteredDate, S.filedDate, S.grievInvestigator, S.intakeInvestigator, S.isSelfNotification, S.litigationMatterNo, S.matterType, S.oldMatterNo, S.reportMethodCode, S.respondent, S.sccaCaseNo, S.takenBy, L.isPublic, L.matterDispoCode, L.matterDispoDate -- Oldest filedDate for a matter in the database is used. (Older ones not likely to appear now.) FROM (SELECT DATE '1974-05-15' + generate_series(0, (CURRENT_DATE - DATE '1974-05-15')) AS date) D JOIN MatterHistStage S ON ( S.date = D.date AND NOT EXISTS ( SELECT * FROM MatterHistStage S2 WHERE S2.matterNo = S.matterNo AND S2.date = D.date AND (S2.date, S2.matterHistRowOrder) (S.date, S.matterHistRowOrder) ) ) JOIN Matter L -- Litigation matter, if present; otherwise the original matter. ON (L.matterNo = COALESCE(S.litigationMatterNo, S.matterNo)) LEFT JOIN MatterHistStatus O ON ( O.matterNo = S.matterNo AND O.date = D.date AND NOT EXISTS ( SELECT * FROM MatterHistStatus O2 WHERE O2.matterNo = O.matterNo AND O2.date = D.date AND (O2.date, O2.matterHistRowOrder) (O.date, O.matterHistRowOrder) ) ) WHERE COALESCE(O.newStatusCode, 'OP') 'CL' ; -- 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] autovacuum and TOAST tables
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
There's two things I'm not happy about in this patch:
1. it uses a List to keep the mapping of heap-toast Oids. This is
needed to be able to fetch the main rel's pg_autovacuum entry to process
the toast table. This incurs in O(n^2) behavior.
Use a dynahash table instead?
Right, the attached patch does that.
Note that this patch allows a toast table to be vacuumed by the user:
alvherre=# vacuum pg_toast.pg_toast_39970;
VACUUM
I don't have a problem with that, but if anyone thinks this is not a
good idea, please speak up.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/commands/vacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.375
diff -c -p -r1.375 vacuum.c
*** src/backend/commands/vacuum.c 5 Jun 2008 15:47:32 - 1.375
--- src/backend/commands/vacuum.c 8 Aug 2008 16:42:02 -
*** static BufferAccessStrategy vac_strategy
*** 213,220
static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
const char *stmttype);
static void vac_truncate_clog(TransactionId frozenXID);
! static void vacuum_rel(Oid relid, VacuumStmt *vacstmt, char expected_relkind,
! bool for_wraparound);
static void full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt);
static void scan_heap(VRelStats *vacrelstats, Relation onerel,
VacPageList vacuum_pages, VacPageList fraged_pages);
--- 213,220
static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
const char *stmttype);
static void vac_truncate_clog(TransactionId frozenXID);
! static void vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast,
! bool for_wraparound);
static void full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt);
static void scan_heap(VRelStats *vacrelstats, Relation onerel,
VacPageList vacuum_pages, VacPageList fraged_pages);
*** static Size PageGetFreeSpaceWithFillFact
*** 268,273
--- 268,276
* OID to be processed, and vacstmt-relation is ignored. (The non-invalid
* case is currently only used by autovacuum.)
*
+ * do_toast is passed as FALSE by autovacuum, because it processes TOAST
+ * tables separately.
+ *
* for_wraparound is used by autovacuum to let us know when it's forcing
* a vacuum for wraparound, which should not be auto-cancelled.
*
*** static Size PageGetFreeSpaceWithFillFact
*** 281,287
* at transaction commit.
*/
void
! vacuum(VacuumStmt *vacstmt, Oid relid,
BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel)
{
const char *stmttype = vacstmt-vacuum ? VACUUM : ANALYZE;
--- 284,290
* at transaction commit.
*/
void
! vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel)
{
const char *stmttype = vacstmt-vacuum ? VACUUM : ANALYZE;
*** vacuum(VacuumStmt *vacstmt, Oid relid,
*** 433,439
Oid relid = lfirst_oid(cur);
if (vacstmt-vacuum)
! vacuum_rel(relid, vacstmt, RELKIND_RELATION, for_wraparound);
if (vacstmt-analyze)
{
--- 436,442
Oid relid = lfirst_oid(cur);
if (vacstmt-vacuum)
! vacuum_rel(relid, vacstmt, do_toast, for_wraparound);
if (vacstmt-analyze)
{
*** vac_truncate_clog(TransactionId frozenXI
*** 975,982
* At entry and exit, we are not inside a transaction.
*/
static void
! vacuum_rel(Oid relid, VacuumStmt *vacstmt, char expected_relkind,
! bool for_wraparound)
{
LOCKMODE lmode;
Relation onerel;
--- 978,984
* At entry and exit, we are not inside a transaction.
*/
static void
! vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound)
{
LOCKMODE lmode;
Relation onerel;
*** vacuum_rel(Oid relid, VacuumStmt *vacstm
*** 1013,1020
* by autovacuum; it's used to avoid cancelling a vacuum that was
* invoked in an emergency.
*
! * Note: this flag remains set until CommitTransaction or
! * AbortTransaction. We don't want to clear it until we reset
* MyProc-xid/xmin, else OldestXmin might appear to go backwards,
* which is probably Not Good.
*/
--- 1015,1022
* by autovacuum; it's used to avoid cancelling a vacuum that was
* invoked in an emergency.
*
! * Note: these flags remain set until CommitTransaction or
! * AbortTransaction. We don't want to clear them until we reset
* MyProc-xid/xmin, else OldestXmin might appear to go backwards,
* which is probably Not Good.
*/
*** vacuum_rel(Oid relid, VacuumStmt *vacstm
*** 1087,1096
}
/*
! * Check that it's a plain table; we used to do this
Re: [HACKERS] autovacuum and TOAST tables
Alvaro Herrera [EMAIL PROTECTED] writes: Note that this patch allows a toast table to be vacuumed by the user: I don't have a problem with that, but if anyone thinks this is not a good idea, please speak up. The permissions on pg_toast will prevent anyone but a superuser from doing that anyway, so it's no big deal. Possibly more interesting is what happens if someone drops the parent table while VACUUM is working independently on the toast table. Does DROP take exclusive lock on a toast table? Probably, but it needs to be checked. I think preventing that scenario was one reason why the vacuuming was tied together way back when. (The same goes for any other parent-table DDL action that would affect the toast table; CLUSTER or TRUNCATE for instance.) 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] Visibility Groups
On Thu, Aug 07, 2008 at 01:30:27PM +0100, Gregory Stark wrote: Simon Riggs [EMAIL PROTECTED] writes: Currently, we calculate a single OldestXmin across all snapshots on the assumption that any transaction might access any table. I propose creating Visibility Groups that *explicitly* limit the ability of a transaction to access data outside its visibility group(s). By default, visibility_groups would be NULL, implying potential access to all tables. Once set, any attempt to lock an object outside of a transactions defined visibility_groups will result in an error: ERROR attempt to lock table outside of visibility group(s): foo HINT you need to set a different value for visibility_groups A transaction can only ever reduce or restrict its visibility_groups, it cannot reset or add visibility groups. Hm, so backing up a bit from the specific proposed interface, the key here is being able to explicitly mark which tables your transaction will need in the future? Is it always just a handful of heavily updated tables that you want to protect? In that case we could have a lock type which means I'll never need to lock this object. Then a session could issue LOCK TABLE foo IN INACCESSIBLE MODE or something like that. That requires people to hack up their pg_dump or replication script though which might be awkward. Perhaps the way to do that would be to preemptively take locks on all the objects that you'll need, then have a command to indicate you won't need any further objects beyond those. +1 -dg -- David Gould [EMAIL PROTECTED] 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
