Re: [HACKERS] problem with archive_command as suggested by documentation
Heikki Linnakangas wrote: > > The documentation states > > > > "The archive command should generally be designed to refuse to overwrite > > any pre-existing archive file." > > > > and suggests an archive_command like "test ! -f .../%f && cp %p .../%f". > > > > We ran into (small) problems with an archive_command similar to this > > as follows: > > > > The server received a fast shutdown request while a WAL segment was being > > archived. > > The archiver stopped and left behind a half-written archive file. > > Hmm, if I'm reading the code correctly, a fast shutdown request > shouldn't kill an ongoing archive command. Maybe it died because of a signal 1, I don't know. But it left behind a half-written file. > > Now when the server was restarted, the archiver tried to archive the same > > WAL segment again and got an error because the destination file already > > existed. > > > > That means that WAL archiving is stuck until somebody manually removes > > the partial archived file. > > Yeah, that's a good point. Even if it turns out that the reason for your > partial write wasn't the fast shutdown request, the archive_command > could be interrupted for some other reason and leave behind a partially > written file behind. > > > I suggest that the documentation be changed so that it does not > > recommend this setup. WAL segment names are unique anyway. > > Well, the documentation states the reason to do that: > > > This is an important safety feature to preserve the > integrity of your archive in case of administrator error > (such as sending the output of two different servers to the > same archive directory) > > which seems like a reasonable concern too. Of course, that's why I did that at first. But isn't it true that the vast majority of people have only one PostgreSQL cluster per machine, and it is highly unlikely that somebody else creates a file with the same name as a WAL segment in the archive directory? > Perhaps it should suggest > something like: > > test ! -f .../%f && cp %p .../%f.tmp && mv .../%f.tmp .../%f > > ie. copy under a different filename first, and rename the file in place > after it's completely written, assuming that mv is atomic. It gets a bit > complicated, though. That's a good idea (although it could lead to race conditions in the extremely rare case that two clusters want to archive equally named files at the same time). I'll write a patch for that and send it as basis for a discussion. Yours, Laurenz Albe -- 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] problem with archive_command as suggested by documentation
On Jan 22, 2009, at 10:18 AM, Albe Laurenz wrote: "The archive command should generally be designed to refuse to overwrite any pre-existing archive file." ... The server received a fast shutdown request while a WAL segment was being archived. The archiver stopped and left behind a half-written archive file. Now when the server was restarted, the archiver tried to archive the same WAL segment again and got an error because the destination file already existed. That means that WAL archiving is stuck until somebody manually removes the partial archived file. I suggest that the documentation be changed so that it does not recommend this setup. WAL segment names are unique anyway. What is your opinion? Is the problem I encountered a corner case that should be ignored? The test is recommended because if you accidentally set two different clusters to archive to the same location you'll trash everything. I don't know of a good work-around; IIRC we used to leave the archive command to complete, but that could seriously delay shutdown so it was changed. I don't think we created an option to control that behavior. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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] deductive databases in postgreSQL
On Jan 22, 2009, at 8:24 AM, Euler Taveira de Oliveira wrote: No one that I know of. Well, it is a long road. The addition of a data type xml is recent (8.3). We lack a set of features like indexing, a new data structure (?), XQuery, XPath improvement and, so on [1]. Don't expect much of these TODO items completed before the next two releases (unless you want to take a stab). You could also possibly pay a consulting company to implement it, but even that isn't as easy as it may sound. :) -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SE-PostgreSQL Updated Revision (r1460)
The patch set of SE-PostgreSQL and related stuff were updated (r1460).
[1/5]
http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1460.patch
[2/5]
http://sepgsql.googlecode.com/files/sepostgresql-utils-8.4devel-3-r1460.patch
[3/5]
http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1460.patch
[4/5]
http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1460.patch
[5/5]
http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1460.patch
I reviewed the patch set by myself, and updated the following items.
However, I would like other hackers to review the code in honesty.
SE-PostgreSQL need any volunteers to review and comment the patch set.
Please give us your support!
List of updates:
- Rebased to the latest CVS HEAD, which includes the column-level
privileges based on the SQL-standard.
(The previous r1425 conflicts in some points.)
- Security policy (sepostgresql-devel.pp) was updated to fit both of
Fedora 10 and rawhide. Test cases are also modified to care the new
security policy.
- Cleanup: NUM_SELINUX_CATALOG was replaced by lengthof() macro to
avoid code duplications.
- Cleanup: sepgsqlCheckEmbeddedProcedure() is renamed to
sepgsqlCheckProcedureInstall() due to its confusable naming.
- Add a new permission: db_procedure:{install}
It enables to prevent malicious user-defined functions are installed
as a part of operators, conversions, types and so on.
The default policy allows to install functions labeled as "sepgsql_proc_t"
only, as an implementation of these facilities.
Meanwhile, functions defined by unprivileged users are labeled as
"user_sepgsql_proc_t" in default, and it is not allowed to install as
an operator and so on.
If DBA want to install user-defined functions for the purpose, he has to
confirm its harmless and relabel it to "sepgsql_proc_t" at first.
In the previous revision, it checked "db_procedure:{execute}" here,
but it is not enough actually, because unprivilged user is allowed to
execute self defined function.
- Code revising: The previous revision always denied required permissions,
when the kernel does not define them within its security policy.
But it can make unexpected behavior when we work SE-PostgreSQL on
a system with legacy security policy which lacks a part of newly
added permissions.
The revised one simply allows actions when these are undefined.
- Fixbug: It required superfluous permissions when we try to update
"security_label" system column but it does not change anything actually.
For example:
UPDATE t SET security_label = security_label;
This query does not change security_label, so we don't need to check
"db_tuple:{relabelfrom}" permission here.
It is obvious we cannot know what tuples are actually relabeled on
sepgsqlExecScan(), so any permission checks for write-operations are
moved to sepgsqlHeapTuple(Insert|Update|Delete) hooks.
- Fixbug: when we update pg_largeobject system catalog by hand, it has
a possibility to create/drop specific largeobject, so we add a check
on "db_blob:{create drop}" when pg_largeobject.loid is modified by
UPDATE statement.
For example:
UPDATE pg_largeobject SET loid = loid::int + 10 WHERE loid = 1234;
It is theoretically same as dropping a largeobject with loid:1234 and
creating a largeobject with loid:1244.
- Fixbug: Tome Lane pointed out a matter when a whole-row-reference on
the relation with RTE_JOIN makes crash at the "Column-Level Privileges"
thread. This revision added a special care for the situation.
It recursively walks on refered JoinExpr and picks up its sources to
check permission to them.
- Code revising: T_SEvalItemRelation and T_SEvalItemAttribute nodes are
integrated into T_SelinuxEvalItem node. In the previous revision,
it simply chains all appeared tables and columns as a list of obsoleted
node on Query->pgaceItem. But it has a trend the length of list grows long.
T_SelinuxEvalItem contains required permissions on a table and an array of
permissions for columns. It enables to keep the length of the list minimum.
Related stuffs in sepgsql/proxy.c is also revised.
- addEvalRelation() / addEvalAttribute() enhanced to handle
T_SelinuxEvalItem.
- Functions to handle inheritance tables and whole-row-reference are clearly
sorted out. expandEvalItemInheritance() handles inheritance tables, and
expandEvalItemWholeRowRefs() handles whole-row-reference.
- Add a hook: pgaceExecuteTruncate()
The previous revision checks permissions on truncated tables and tuples
on pgaceProcessUtility(), but this approach need to extract all the target
including cascaded ones, so it made code duplication.
The new hook is deployed on ExecuteTruncate() and delivers a list of already
opened relations with AccessExclusiveLock.
A new sepgsqlExecuteTruncate() checks needed permission on the hook.
- Cleanup: sepgsqlTupleName() always copied an ide
Re: [HACKERS] reducing statistics write overhead
Euler Taveira de Oliveira writes: > Alvaro Herrera escreveu: >> Well, the problem is precisely how to size the list. I don't like the >> idea of keeping an arbitrary number in memory; it adds another >> mostly-useless tunable that we'll need to answer questions about for all >> eternity. Is it so hard? In particular, rather than making it a tunable, what say we freeze the list size at exactly two, ie each AV worker advertises its current and most recent target table in shared memory. Other workers avoid re-vacuuming those. Then the most work you can "waste" by extra vacuuming is less than the maximum allowed stats file age. I'd have no problem whatsoever in letting that run into multiple seconds, as long as it doesn't get into minutes or hours. 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] reducing statistics write overhead
Alvaro Herrera escreveu:
> Euler Taveira de Oliveira escribió:
>> Alvaro Herrera escreveu:
>>> This could be solved if the workers kept the whole history of tables
>>> that they have vacuumed. Currently we keep only a single table (the one
>>> being vacuumed right now). I proposed writing these history files back
>>> when workers were first implemented, but the idea was shot down before
>>> flying very far because it was way too complex (the rest of the patch
>>> was more than complex enough.) Maybe we can implement this now.
>>>
>> [I don't remember your proposal...] Isn't it just add a circular linked list
>> at AutoVacuumShmemStruct? Of course some lock mechanism needs to exist to
>> guarantee that we don't write at the same time. The size of this linked list
>> would be scale by a startup-time-guc or a reasonable fixed value.
>
> Well, the problem is precisely how to size the list. I don't like the
> idea of keeping an arbitrary number in memory; it adds another
> mostly-useless tunable that we'll need to answer questions about for all
> eternity.
>
[Poking the code a little...] You're right. We could do that but it isn't an
elegant solution. What about tracking that information at table_oids?
struct table_oids {
bool skipit;/* initially false */
Oid relid;
};
--
Euler Taveira de Oliveira
http://www.timbira.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] pg_get_viewdef formattiing
--On Donnerstag, Januar 22, 2009 15:30:35 -0500 Andrew Dunstan wrote: Am I the only person who gets regularly annoyed by pg_get_viewdef() outputting the target list as one long line? I'd like it to put one target per line, indented, if pretty printing. No, so +1 from me. -- Thanks Bernd -- 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] Column-Level Privileges
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Well, the examples I've looked at fit in 80 columns, but it's true that > all the identifiers involved were pretty short. The alternative I think > you're suggesting is Yeah, I see that now. I guess you'd need a column identifier wider than 'Column Access Privileges' or so, which is almost asking for trouble already, or a combination of grantee+privs+grantor greater than around the same, which would require rolenames of >9 characters for grantee and grantor, which is probably not that common. The new stuff added to split the ACL across lines is pretty nice. > which is definitely more compact horizontally, but I think it's harder > to follow. It's also *less* compact vertically, which is not a > negligible consideration either. yea, I'd rather we provide more information on a given row than add additional rows, but I also tend to run my DB-work terminals at 220x70 or so, which seems to indicate I'm in the minority. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Column-Level Privileges
Stephen Frost writes: > One thing that just occured to me is that we could, should we want to, > move the column-level privs over into the 'Access privileges' column by > just adding them on after the table-level privs. We would want to make > sure the table-level privs come first and maybe have some seperator to > indicate that the following are column-level privs. > That might make the display nicer on 80-col systems, though personally > I like larger windows. :) Well, the examples I've looked at fit in 80 columns, but it's true that all the identifiers involved were pretty short. The alternative I think you're suggesting is Access privileges Schema | Name | Type | Access privileges +--+---+--- public | foo | table | postgres=arwdDxt/postgres : joe=r/postgres : bar: : joe=a/postgres : baz: : joe=w/postgres (1 row) which is definitely more compact horizontally, but I think it's harder to follow. It's also *less* compact vertically, which is not a negligible consideration either. 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] Column-Level Privileges
* Tom Lane (t...@sss.pgh.pa.us) wrote: > BTW, something else I'd meant to bring up for discussion is whether > anyone likes the formatting of column privileges in \dp: Well, I kinda like it, but that's not an entirely unbiased opinion. ;) > Access privileges > Schema | Name | Type | Access privileges | Column access privileges > +--+---+---+-- > public | foo | table | postgres=arwdDxt/postgres | bar: >: joe=r/postgres: joe=a/postgres >: baz: >: joe=w/postgres > (1 row) > > (The colons after the column names are something I added on my own > authority to Stephen's original.) sure, makes sense. > This seems a bit ASCII-art-ish to me; it certainly wouldn't be readily > parsable by programs. Now that's not really the design goal for \d > output, and I don't have a better suggestion offhand, but still... > anyone got a better idea? One thing that just occured to me is that we could, should we want to, move the column-level privs over into the 'Access privileges' column by just adding them on after the table-level privs. We would want to make sure the table-level privs come first and maybe have some seperator to indicate that the following are column-level privs. That might make the display nicer on 80-col systems, though personally I like larger windows. :) A couple of things I didn't particularly like: I don't like having to have a separate command to show column-level privs, and I don't really like displaying the column-level privs after the regular \dp output for tables. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] autovacuum, reloptions, and hardcoded pg_class tupdesc
Alvaro Herrera wrote:
> I'm not sure that we have any use for the top level you propose; the
> attached patch just uses the two lower levels, and I think it fits
> autovacuum usage just fine. Thanks for the idea.
Of course, there's no need to pass the relkind; it goes inside the
pg_class tuple.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
diff -u src/backend/access/common/reloptions.c src/backend/access/common/reloptions.c
--- src/backend/access/common/reloptions.c 22 Jan 2009 23:24:04 -
+++ src/backend/access/common/reloptions.c 22 Jan 2009 23:51:22 -
@@ -569,11 +569,12 @@
* in the case of the tuple corresponding to an index, or InvalidOid otherwise.
*/
bytea *
-extractRelOptions(HeapTuple tuple, TupleDesc tupdesc, char relkind, Oid amoptions)
+extractRelOptions(HeapTuple tuple, TupleDesc tupdesc, Oid amoptions)
{
bytea *options;
bool isnull;
Datum datum;
+ Form_pg_class classForm;
datum = fastgetattr(tuple,
Anum_pg_class_reloptions,
@@ -582,13 +583,15 @@
if (isnull)
return NULL;
+ classForm = (Form_pg_class) GETSTRUCT(tuple);
+
/* Parse into appropriate format; don't error out here */
- switch (relkind)
+ switch (classForm->relkind)
{
case RELKIND_RELATION:
case RELKIND_TOASTVALUE:
case RELKIND_UNCATALOGED:
- options = heap_reloptions(relkind, datum, false);
+ options = heap_reloptions(classForm->relkind, datum, false);
break;
case RELKIND_INDEX:
options = index_reloptions(amoptions, datum, false);
diff -u src/backend/utils/cache/relcache.c src/backend/utils/cache/relcache.c
--- src/backend/utils/cache/relcache.c 22 Jan 2009 23:30:45 -
+++ src/backend/utils/cache/relcache.c 22 Jan 2009 23:51:05 -
@@ -374,7 +374,6 @@
*/
options = extractRelOptions(tuple,
GetPgClassDescriptor(),
- relation->rd_rel->relkind,
relation->rd_rel->relkind == RELKIND_INDEX ?
relation->rd_am->amoptions : InvalidOid);
diff -u src/include/access/reloptions.h src/include/access/reloptions.h
--- src/include/access/reloptions.h 22 Jan 2009 23:22:58 -
+++ src/include/access/reloptions.h 22 Jan 2009 23:50:25 -
@@ -243,7 +243,7 @@
bool ignoreOids, bool isReset);
extern List *untransformRelOptions(Datum options);
extern bytea *extractRelOptions(HeapTuple tuple, TupleDesc tupdesc,
- char relkind, Oid amoptions);
+ Oid amoptions);
extern relopt_value *parseRelOptions(Datum options, bool validate,
relopt_kind kind, int *numrelopts);
extern void *allocateReloptStruct(Size base, relopt_value *options,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: FWD: Re: [HACKERS] Updated backslash consistency patch
Stephen Frost writes: > Seeing this list reminded me of a pet-peeve.. \du and \dg actually show > the same info, that's fine, but neither of them show the rolcanlogin > value. +1 for fixing that. >> \dp [PATTERN]list table, view, and sequence access privileges > erp, I don't think I changed this in my column-level privleges patch.. > Should we explicitly mention column in this list? No, I think it's good as-is. Adding column here would suggest that columns are handled exactly parallel to tables, views, or sequences, which of course isn't the case. 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] Column-Level Privileges
BTW, something else I'd meant to bring up for discussion is whether anyone likes the formatting of column privileges in \dp: regression=# create table foo(bar int, baz int); CREATE TABLE regression=# grant select on foo to joe; GRANT regression=# grant insert(bar), update(baz) on foo to joe; GRANT regression=# \dp foo Access privileges Schema | Name | Type | Access privileges | Column access privileges +--+---+---+-- public | foo | table | postgres=arwdDxt/postgres | bar: : joe=r/postgres: joe=a/postgres : baz: : joe=w/postgres (1 row) (The colons after the column names are something I added on my own authority to Stephen's original.) This seems a bit ASCII-art-ish to me; it certainly wouldn't be readily parsable by programs. Now that's not really the design goal for \d output, and I don't have a better suggestion offhand, but still... anyone got a better idea? 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
> Needless to say if you're both waiting for each other nothing will get done. SET deadlock_timeout = '3d'; ;-) -- 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, reloptions, and hardcoded pg_class tupdesc
Tom Lane wrote:
> Alvaro Herrera writes:
> >Right now we just plow
> >ahead using a pg_class seqscan, which avoids locking the relations
> >just for the sake of verifying whether they need work.
>
> We should stick with that, and refactor the reloptions code as needed to
> be able to work from just a pg_class tuple. I'm envisioning a scheme
> like:
>
> bottom level: extract from pg_class tuple, return a palloc'd struct
>
> relcache: logic to cache the result of the above
>
> top level: exported function to return a cached options struct
>
> The autovac scan could use the bottom-level API.
I'm not sure that we have any use for the top level you propose; the
attached patch just uses the two lower levels, and I think it fits
autovacuum usage just fine. Thanks for the idea.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/access/common/reloptions.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/common/reloptions.c,v
retrieving revision 1.18
diff -c -p -r1.18 reloptions.c
*** src/backend/access/common/reloptions.c 12 Jan 2009 21:02:14 - 1.18
--- src/backend/access/common/reloptions.c 22 Jan 2009 23:24:04 -
*** untransformRelOptions(Datum options)
*** 558,563
--- 558,606
return result;
}
+ /*
+ * Extract reloptions from a pg_class tuple.
+ *
+ * This is a very low-level routine, expected to be used by relcache code only.
+ * For other uses, consider grabbing the pointer from the relcache entry
+ * instead.
+ *
+ * tupdesc is pg_class' tuple descriptor. amoptions is the amoptions regproc
+ * in the case of the tuple corresponding to an index, or InvalidOid otherwise.
+ */
+ bytea *
+ extractRelOptions(HeapTuple tuple, TupleDesc tupdesc, char relkind, Oid amoptions)
+ {
+ bytea *options;
+ bool isnull;
+ Datum datum;
+
+ datum = fastgetattr(tuple,
+ Anum_pg_class_reloptions,
+ tupdesc,
+ &isnull);
+ if (isnull)
+ return NULL;
+
+ /* Parse into appropriate format; don't error out here */
+ switch (relkind)
+ {
+ case RELKIND_RELATION:
+ case RELKIND_TOASTVALUE:
+ case RELKIND_UNCATALOGED:
+ options = heap_reloptions(relkind, datum, false);
+ break;
+ case RELKIND_INDEX:
+ options = index_reloptions(amoptions, datum, false);
+ break;
+ default:
+ Assert(false); /* can't get here */
+ options = NULL; /* keep compiler quiet */
+ break;
+ }
+
+ return options;
+ }
/*
* Interpret reloptions that are given in text-array format.
Index: src/backend/utils/cache/relcache.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/cache/relcache.c,v
retrieving revision 1.282
diff -c -p -r1.282 relcache.c
*** src/backend/utils/cache/relcache.c 22 Jan 2009 20:16:06 - 1.282
--- src/backend/utils/cache/relcache.c 22 Jan 2009 23:30:45 -
*** AllocateRelationDesc(Relation relation,
*** 351,358
static void
RelationParseRelOptions(Relation relation, HeapTuple tuple)
{
- Datum datum;
- bool isnull;
bytea *options;
relation->rd_options = NULL;
--- 351,356
*** RelationParseRelOptions(Relation relatio
*** 374,404
* we might not have any other for pg_class yet (consider executing this
* code for pg_class itself)
*/
! datum = fastgetattr(tuple,
! Anum_pg_class_reloptions,
! GetPgClassDescriptor(),
! &isnull);
! if (isnull)
! return;
!
! /* Parse into appropriate format; don't error out here */
! switch (relation->rd_rel->relkind)
! {
! case RELKIND_RELATION:
! case RELKIND_TOASTVALUE:
! case RELKIND_UNCATALOGED:
! options = heap_reloptions(relation->rd_rel->relkind, datum,
! false);
! break;
! case RELKIND_INDEX:
! options = index_reloptions(relation->rd_am->amoptions, datum,
! false);
! break;
! default:
! Assert(false); /* can't get here */
! options = NULL; /* keep compiler quiet */
! break;
! }
/* Copy parsed data into CacheMemoryContext */
if (options)
--- 372,382
* we might not have any other for pg_class yet (consider executing this
* code for pg_class itself)
*/
! options = extractRelOptions(tuple,
! GetPgClassDescriptor(),
! relation->rd_rel->relkind,
! relation->rd_rel->relkind == RELKIND_INDEX ?
! relation->rd_am->amoptions : InvalidOid);
/* Copy parsed data into CacheMemoryContext */
if (options)
Index: src/include/access/reloptions.h
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/access/reloptions.h,v
retrieving revision 1.10
diff -c -p -r1.10 reloptions.h
*** src/include/access/reloptions.h 12 Jan 20
Re: [HACKERS] Pluggable Indexes
Robert Haas writes: >> Of course, there's no much point in an index that's easily corrupted, so >> I understand the desire to implement WAL too -- I'm just pointing out >> that concurrency could have been developed independently. > Anything's possible with enough work, but having good support in -core > makes it easier and -core has usually been receptive to requests for > such things - for example, I think Tom put in quite a bit of work to > getting the right hooks in to enable libpqtypes. Well, in fact, that's an exceedingly apt and instructive comparison. The hooks that went into libpq resulted from several iterations of design against a real, live, working application for those hooks. The proposed rmgr patch is apparently suffering from no such handicap as having been proven to satisfy the needs of real code :-( There are other recent examples of proposed hooks that in fact failed to be useful because of some oversight or other, and it was not until we insisted on seeing a live use of the hooks that this became apparent. (IIRC, one or both of the planner-related hooks that are new in 8.4 had such issues.) I generally agree that pluggable rmgr support would be a good idea, but I would much rather put off making the hooks until we have a live application for them to prove that they are useful and usable. If we make a hook now sans test case, then what happens if we discover later that it's not quite right? We'd have to figure out whether there's a need for backwards-compatible behavior, and we will have a hard time knowing whether there are any live uses of the hook in the field. So my take on this is to wait. If it were actually needed by the hot standby code then of course the above argument would be wrong, but what I gather from the discussion is that it's not. 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] Lock conflict behavior?
On Thu, 2009-01-22 at 18:20 -0500, Tom Lane wrote: > Jeff Davis writes: > > On Wed, 2009-01-21 at 15:08 -0800, Jeff Davis wrote: > >> If we keep the permission check in LockTableCommand(), I can make a > >> patch that produces a more useful error message when the table is > >> removed right before the pg_class_aclcheck(). > > > Attached. > > This is pretty horrid, because it converts any error whatsoever into > "relation does not exist". For counterexamples consider "statement > timeout reached", "query cancelled by user", "pg_class is corrupted", > etc etc. Ah, I see. Well, I guess there's not a better way to handle that error after all. There's no way to tell what exception you're catching specifically, right? Regards, Jeff Davis -- 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] [COMMITTERS] pgsql: Explicitly bind gettext to the correct encoding on Windows.
m...@postgresql.org (Magnus Hagander) writes: > Explicitly bind gettext to the correct encoding on Windows. I have a couple of objections to this patch. First, what happens if it fails to find a matching table entry? (The existing answer is "nothing", but that doesn't seem right.) Second and more critical, it adds still another data structure that has to be maintained when the list of encodings changes, and it doesn't even live in the same file as any existing encoding-information table. What makes more sense to me is to add a table to encnames.c that provides the gettext name of every encoding that we support. 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] Lock conflict behavior?
Jeff Davis writes: > On Wed, 2009-01-21 at 15:08 -0800, Jeff Davis wrote: >> If we keep the permission check in LockTableCommand(), I can make a >> patch that produces a more useful error message when the table is >> removed right before the pg_class_aclcheck(). > Attached. This is pretty horrid, because it converts any error whatsoever into "relation does not exist". For counterexamples consider "statement timeout reached", "query cancelled by user", "pg_class is corrupted", etc etc. 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] Table Partitioning Feature
Amit, You might want to put this on the http://wiki.postgresql.org/wiki/Table_partitioning wiki page. How does your timeline look like for this implementation? I would be happy to contribute C triggers to your implementation. From what I understood in http://archives.postgresql.org/pgsql-hackers/2008-01/msg00269.php, you already have an implementation that parses the grammar and generates rules as if someone had written them. Is this code available? Regarding the use of triggers to push/move data to partitions, what if someone declares triggers on partitions? Especially if you have subpartitions, let's consider the case where there is a trigger on the parent, child and grandchild. If I do an insert in the parent, the user trigger on the parent will be executed, then the partition trigger that decides to move to the grandchild. Are we going to bypass the child trigger? If we also want fast COPY operations on partitioned table, we could have an optimized implementation that could bypass triggers and move the tuple directly to the appropriate child table. Thanks for this big contribution, Emmanuel Hi, We are implementing table partitioning feature to support - the attached commands. The syntax conforms to most of the suggestion mentioned in http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php, barring the following: -- Specification of partition names is optional. System will be able to generate partition names in such cases. -- sub partitioning We are using pgsql triggers to push/move data to appropriate partitions, but we will definitely consider moving to C language triggers as suggested by manu. - Global non-partitioned indexes (that will extend all the partitions). - Foreign key support for tables referring to partitioned tables. Please feel free to post your comments and suggestions. Thanks, Amit Persistent Systems -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development & Consulting -- Web: http://www.frogthinker.org email: m...@frogthinker.org Skype: emmanuel_cecchet -- 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] reducing statistics write overhead
Euler Taveira de Oliveira escribió: > Alvaro Herrera escreveu: > > This could be solved if the workers kept the whole history of tables > > that they have vacuumed. Currently we keep only a single table (the one > > being vacuumed right now). I proposed writing these history files back > > when workers were first implemented, but the idea was shot down before > > flying very far because it was way too complex (the rest of the patch > > was more than complex enough.) Maybe we can implement this now. > > > [I don't remember your proposal...] Isn't it just add a circular linked list > at AutoVacuumShmemStruct? Of course some lock mechanism needs to exist to > guarantee that we don't write at the same time. The size of this linked list > would be scale by a startup-time-guc or a reasonable fixed value. Well, the problem is precisely how to size the list. I don't like the idea of keeping an arbitrary number in memory; it adds another mostly-useless tunable that we'll need to answer questions about for all eternity. -- 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] Frames vs partitions: is SQL2008 completely insane?
Bruce Momjian writes: > Was this dealt with? Yes. 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] reducing statistics write overhead
Alvaro Herrera escreveu: > This could be solved if the workers kept the whole history of tables > that they have vacuumed. Currently we keep only a single table (the one > being vacuumed right now). I proposed writing these history files back > when workers were first implemented, but the idea was shot down before > flying very far because it was way too complex (the rest of the patch > was more than complex enough.) Maybe we can implement this now. > [I don't remember your proposal...] Isn't it just add a circular linked list at AutoVacuumShmemStruct? Of course some lock mechanism needs to exist to guarantee that we don't write at the same time. The size of this linked list would be scale by a startup-time-guc or a reasonable fixed value. -- Euler Taveira de Oliveira http://www.timbira.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] [BUGS] Status of issue 4593
Jeff Davis wrote: > On Mon, 2009-01-12 at 13:35 -0500, Tom Lane wrote: > > I think the behavior Lee is expecting is only implementable with a > > full-table write lock, which is exactly what FOR UPDATE is designed > > to avoid. There are certain properties you don't get with a partial > > lock, and in the end I think we can't do much except document them. > > We have LOCK TABLE for those who need the other behavior. > > > > Lee said specifically that he's not using LIMIT, and there's already a > pretty visible warning in the docs for using LIMIT with FOR UPDATE. > Also, using LIMIT + FOR UPDATE has a dangerous-looking quality to it (at > least to me) that would cause me to do a little more investigation > before relying on its behavior. > > I'm not pushing for FOR UPDATE + ORDER BY to be blocked outright, but I > think it's strange enough that it should be considered some kind of > defect worse than the cases involving LIMIT that you mention. I have added the attached documentation mention to CVS HEAD and 8.3.X. If people want a TODO entry or to issue a WARNING message on use, please let me know. This does seem similar to the FOR UPDATE / LIMIT issue so I handled it similarly. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/select.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v retrieving revision 1.117 diff -c -c -r1.117 select.sgml *** doc/src/sgml/ref/select.sgml 12 Jan 2009 14:06:20 - 1.117 --- doc/src/sgml/ref/select.sgml 22 Jan 2009 22:50:20 - *** *** 1162,1177 It is possible for a SELECT command using both ! LIMIT and FOR UPDATE/SHARE clauses to return fewer rows than specified by LIMIT. This is because LIMIT is applied first. The command selects the specified number of rows, ! but might then block trying to obtain lock on one or more of them. Once the SELECT unblocks, the row might have been deleted or updated so that it does not meet the query WHERE condition anymore, in which case it will not be returned. --- 1162,1192 It is possible for a SELECT command using both ! LIMIT and FOR UPDATE/SHARE clauses to return fewer rows than specified by LIMIT. This is because LIMIT is applied first. The command selects the specified number of rows, ! but might then block trying to obtain a lock on one or more of them. Once the SELECT unblocks, the row might have been deleted or updated so that it does not meet the query WHERE condition anymore, in which case it will not be returned. + + + + Similarly, it is possible for a SELECT command + using ORDER BY and FOR + UPDATE/SHARE to return rows out of order. This is + because ORDER BY is applied first. The command + orders the result, but might then block trying to obtain a lock + on one or more of the rows. Once the SELECT + unblocks, one of the ordered columns might have been modified + and be returned out of order. A workaround is to perform + SELECT ... FOR UPDATE/SHARE and then SELECT + ... ORDER BY. + + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Simon, Your suggestion sounds reasonable and I thank you, but doesn't actually address the plugin discussion at all. It had absolutely zip to do with making building indexes easier; it was about enabling robust index plugins, period. (As well as other worthwhile use cases). It's not a cost benefit decision, its just "can we have it, or not?". The API *is* the right one because we already use it with at least 3 actual implementations. Will it change over time? Of course. OK. Mostly I'm looking at the calendar, and didn't want to see this rejected permanently just because people don't want to hash it out in time for 8.4. --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] autovacuum, reloptions, and hardcoded pg_class tupdesc
Alvaro Herrera writes: > So I've been progressing on revising the autovacuum patch to make it > work with the current reloptions. We have a number of options: > 1. Call heap_open() for every relation that we're going to check, and >examine the reloptions via the relcache. >I'm not sure that I like this very much. I don't like it at all, mainly because it implies taking locks on tables that autovacuum doesn't need to be touching. Even if it's only AccessShareLock, it could result in problems vis-a-vis clients that are holding exclusive table locks. >Right now we just plow >ahead using a pg_class seqscan, which avoids locking the relations >just for the sake of verifying whether they need work. We should stick with that, and refactor the reloptions code as needed to be able to work from just a pg_class tuple. I'm envisioning a scheme like: bottom level: extract from pg_class tuple, return a palloc'd struct relcache: logic to cache the result of the above top level: exported function to return a cached options struct The autovac scan could use the bottom-level API. 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] reducing statistics write overhead
Tom Lane escribió: > Alvaro Herrera writes: > > Martin Pihlak escribi�: > >> [ patch to fool with stats refresh logic in autovac ] > > (1) I still don't understand why we don't just make the launcher request > a new stats file once per naptime cycle, and then allow the workers to > work from that. The problem is workers that spend too much time on a single database. If the stats at the time they both start say that a given table must be vacuumed (consider for example that the first one spent too much time vacuuming some other big table), they could end up both vacuuming that table. The second vacuum would be a waste. This could be solved if the workers kept the whole history of tables that they have vacuumed. Currently we keep only a single table (the one being vacuumed right now). I proposed writing these history files back when workers were first implemented, but the idea was shot down before flying very far because it was way too complex (the rest of the patch was more than complex enough.) Maybe we can implement this now. > (2) The current code in autovacuum.c seems to be redundant with the > logic that now exists in the stats mechanism itself to decide whether a > stats file is too old. Hmm, yeah, possibly. -- 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
[HACKERS] Upcoming back-branch re-releases
The recently discovered memory-context-switch bug in 8.1's autovacuum seems serious enough to force an update release for that branch, since it could result in crashes in an installation that had been working fine for a long while. Although this only affects 8.1, the core committee has concluded that we might as well update the other back branches at the same time. Accordingly, we're planning to wrap tarballs next Thursday (1/29) for public announcement Monday 2/1. If you've got any back-branch bug fixes sitting around, now would be a good time to send them in... 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] reducing statistics write overhead
Alvaro Herrera writes: > Martin Pihlak escribió: >> [ patch to fool with stats refresh logic in autovac ] (1) I still don't understand why we don't just make the launcher request a new stats file once per naptime cycle, and then allow the workers to work from that. (2) The current code in autovacuum.c seems to be redundant with the logic that now exists in the stats mechanism itself to decide whether a stats file is too old. 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] rmgr hooks (v2)
On Wed, 2009-01-21 at 18:38 +0200, Heikki Linnakangas wrote: > Simon Riggs wrote: > > So you *must* replay catalog entries and recreate the original catalog > > in exact synchronisation with reading WAL files. Recreating the catalog > > can only be done by Postgres itself. > > The startup process doesn't have a relcache, so this rmgr patch is > nowhere near enough to enable that. If I understood correctly, the hot > standby patch doesn't change that either. The answer to this question was that it doesn't need a relcache, though perhaps it might be desirable. Catalog tables are scanned with SnapshotNow and so will work correctly without that machinery. We already rely on this within the existing code to update flat files towards the end of recovery. It is true that you can't look at user data, but then I can already do that with Hot Standby, so the plugin isn't needed for that. -- 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] autovacuum, reloptions, and hardcoded pg_class tupdesc
Hi, So I've been progressing on revising the autovacuum patch to make it work with the current reloptions. We have a number of options: 1. Call heap_open() for every relation that we're going to check, and examine the reloptions via the relcache. I'm not sure that I like this very much. Right now we just plow ahead using a pg_class seqscan, which avoids locking the relations just for the sake of verifying whether they need work. However, this is the cleanest option in terms of modularity breakage. 2. Play some dirty tricks in autovacuum and extract the reloptions from the bare pg_class tuple ourselves. I think doing this cleanly requires exporting some internal functions like GetPgClassDescriptor() from relcache.c. 3. Disallow setting reloptions for pg_class, which (I think) allows us to avoid having to use GetPgClassDescriptor, but the rest of it is still a dirty hack. In particular, if we do either (2) or (3), we'll need to keep an eye on that code whenever we modify RelationParseRelOptions. Thoughts? -- 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] Column-Level Privileges
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Applied with revisions. The main externally visible change is that I > implemented per-column REFERENCES privilege, since that's required by > spec. I did some heavy revision of the parsing support too, as per > previous dicussions, and editorial cleanup and bugfixing elsewhere. Great! Glad to hear it, and thanks for the updates and handling REFERENCES. > There are still some significant loose ends though: [...] I'll work on these and plan to finish them by Monday. > * Perhaps it would be appropriate to let LOCK TABLE succeed if you have > proper permissions on at least one column of the table. However, it's > bad enough that LOCK TABLE examines permissions before locking the table > now; I don't think it ought to be grovelling through the columns without > lock. So this might be a place to leave well enough alone. Agreed. Thanks, Stephen signature.asc Description: Digital signature
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, 2009-01-22 at 11:23 -0800, Josh Berkus wrote: > I suggest that we take the rmgr patch and combine it with getting WAL > working properly for Bitmap-on-disk and Hash indexes in 8.5. Having > this patch attached to an actual implementation will show if it's the > correct code to make building new types of indexes easier, or not, > rather than arguing about it in the abstract. Your suggestion sounds reasonable and I thank you, but doesn't actually address the plugin discussion at all. It had absolutely zip to do with making building indexes easier; it was about enabling robust index plugins, period. (As well as other worthwhile use cases). It's not a cost benefit decision, its just "can we have it, or not?". The API *is* the right one because we already use it with at least 3 actual implementations. Will it change over time? Of course. We just "mulled it over" in great detail and it appears this was a popular feature with no technical problems mentioned about the patch. We almost never get 8 people speaking out clearly in favour of something. I'm too busy with Hot Standby to carry on this debate any longer, as everyone knows - though I think the various forms of filibustering need to stop. -- 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
Re: [HACKERS] Column-Level Privileges
On Thu, Jan 22, 2009 at 3:29 PM, Tom Lane wrote: > > * We probably ought to invent has_column_privilege SQL functions > analogous to has_table_privilege; this is not just for completeness, > but is probably necessary to finish the above items. > +1 > * ISTM that COPY with a column list should succeed if you have > SELECT or INSERT privilege on just the mentioned columns. > +1 > * Perhaps it would be appropriate to let LOCK TABLE succeed if you have > proper permissions on at least one column of the table. However, it's > bad enough that LOCK TABLE examines permissions before locking the table > now; I don't think it ought to be grovelling through the columns without > lock. So this might be a place to leave well enough alone. > +1 -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_get_viewdef formattiing
Am I the only person who gets regularly annoyed by pg_get_viewdef() outputting the target list as one long line? I'd like it to put one target per line, indented, if pretty printing. 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] Column-Level Privileges
Stephen Frost writes: > Attached is an updated patch for column-level privileges. Applied with revisions. The main externally visible change is that I implemented per-column REFERENCES privilege, since that's required by spec. I did some heavy revision of the parsing support too, as per previous dicussions, and editorial cleanup and bugfixing elsewhere. There are still some significant loose ends though: * Some of the information_schema views are specified to respond to per-column privileges; the column_privileges and columns views certainly need work now to meet spec, and there might be others. * It might be appropriate to let the pg_stats view expose stats for columns you have select privilege for, even if you haven't got it across the whole table. * We probably ought to invent has_column_privilege SQL functions analogous to has_table_privilege; this is not just for completeness, but is probably necessary to finish the above items. * ISTM that COPY with a column list should succeed if you have SELECT or INSERT privilege on just the mentioned columns. * Perhaps it would be appropriate to let LOCK TABLE succeed if you have proper permissions on at least one column of the table. However, it's bad enough that LOCK TABLE examines permissions before locking the table now; I don't think it ought to be grovelling through the columns without lock. So this might be a place to leave well enough alone. 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, 2009-01-22 at 13:45 +, Simon Riggs wrote: > But this isn't just for me... I have an old proposal here: http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php Of course, the number one problem I ran into was that I never actually wrote the code, not that I needed it to be a plugin ;) But seriously, it might help. I may be able to write it for 8.5+, and then turn it into a plugin and people using 8.4 could benefit. Or maybe it gets rejected from the core and I have to write it as a plugin by copying GiST and modifying it. I think this might be an answer to Heikki's observation that writing a stable index AM takes a long time: it doesn't if you just copy an existing one and modify it slightly. Because I don't need to make any changes to the way WAL is used, ideally I could have a high degree of confidence that it's correct with little effort. Right? I haven't given a lot of thought to whether my improvement could be made a plugin or not, nor have I read your patch, but it seems possible to me. Regards, Jeff Davis -- 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 map and freezing
Euler Taveira de Oliveira wrote: Simon Riggs escreveu: On Tue, 2009-01-20 at 21:51 +0200, Heikki Linnakangas wrote: Attached is a simple patch to only start skipping pages after 20 consecutive pages marked as visible in the visibility map. This doesn't do any "look-ahead", so it will always scan the first 20 pages of a table before it starts to skip pages, and whenever there's even one page that needs vacuuming, the next 19 pages will also be vacuumed. We could adjust that figure 20 according to table size. Or by seq_page_cost/random_page_cost. But I'm leaning towards a simple hard-coded value for now. Yes, sounds good. Can we stick to multiples of 2 as the OS readahead does IIRC? So either 16 or 32. I'd go 32. Agreed. And do it a constant (ALL_VISIBLE_VM_THRESHOLD?). Okay-dokay. I committed this with the constant as a #define, at value 32. -- 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
All, I have a suggestion for the rmgr patch. Currently, there are *no* plans to get WAL working for the new hash indexes, nor is there time. I suggest that we take the rmgr patch and combine it with getting WAL working properly for Bitmap-on-disk and Hash indexes in 8.5. Having this patch attached to an actual implementation will show if it's the correct code to make building new types of indexes easier, or not, rather than arguing about it in the abstract. In other words, I'm suggesting that we move it to commitfest-first for 8.5. It's not like we don't have plenty of features and uncommitted patches for 8.4, and it's not like Simon is going away. --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] [PATCH] EnableDisableTrigger Cleanup & Questions
Jonah H. Harris wrote: On Wed, Jan 21, 2009 at 2:02 PM, Robert Haas wrote: Was there a reason that this cleanup patch wasn't applied? 1. It was submitted after the deadline for CommitFest:November. Well, it's just comment changes... Oh, didn't realize that. That's what I get for replying without reading the patch... Yes :) Committed, thanks. -- 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] Table Partitioning Feature
Amit, Wow, thanks! As you probably know, we're already in freeze for 8.4. So this patch will need to go on the first commitfest for 8.5, in May or June. --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] Visibility map and freezing
Heikki Linnakangas wrote: ITAGAKI Takahiro wrote: - What relation are there between autovacuum_freeze_max_age, vacuum_freeze_min_age and vacuum_freeze_table_age? If we increase one of them, should we also increase the others? Yeah, that's a fair question. I'll try to work a doc patch to explain that better. Ok, how does this sound: +VACUUM normally skips pages that don't have any dead row +versions, but those pages might still have tuples with old XID values. +To replace them too, a scan of the whole table is needed every once +in a while. vacuum_freeze_table_age controls when +VACUUM does that: a whole table sweep is forced if +relfrozenxid is more than +vacuum_freeze_table_age transactions old. Setting it to 0 +makes VACUUM to ignore the visibility map and always scan all +pages. The effective maximum is 0.95 * autovacuum_freeze_max_age; +a setting higher than that will be capped to that maximum. A value +higher than autovacuum_freeze_max_age wouldn't make sense +because an anti-wraparound autovacuum would be triggered at that point +anyway, and the 0.95 multiplier leaves some breathing room to run a manual +VACUUM before that happens. As a rule of thumb, +vacuum_freeze_table_age should be set to a value somewhat +below autovacuum_freeze_max_age. Setting it too close could +lead to anti-wraparound autovacuums, even though the table was recently +manually vacuumed, whereas lower values lead to more frequent whole-table +scans. + -- 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] problem with archive_command as suggested by documentation
Albe Laurenz wrote: The documentation states in http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL "The archive command should generally be designed to refuse to overwrite any pre-existing archive file." and suggests an archive_command like "test ! -f .../%f && cp %p .../%f". We ran into (small) problems with an archive_command similar to this as follows: The server received a fast shutdown request while a WAL segment was being archived. The archiver stopped and left behind a half-written archive file. Hmm, if I'm reading the code correctly, a fast shutdown request shouldn't kill an ongoing archive command. Now when the server was restarted, the archiver tried to archive the same WAL segment again and got an error because the destination file already existed. That means that WAL archiving is stuck until somebody manually removes the partial archived file. Yeah, that's a good point. Even if it turns out that the reason for your partial write wasn't the fast shutdown request, the archive_command could be interrupted for some other reason and leave behind a partially written file behind. I suggest that the documentation be changed so that it does not recommend this setup. WAL segment names are unique anyway. Well, the documentation states the reason to do that: This is an important safety feature to preserve the integrity of your archive in case of administrator error (such as sending the output of two different servers to the same archive directory) which seems like a reasonable concern too. Perhaps it should suggest something like: test ! -f .../%f && cp %p .../%f.tmp && mv .../%f.tmp .../%f ie. copy under a different filename first, and rename the file in place after it's completely written, assuming that mv is atomic. It gets a bit complicated, though. -- 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, 2009-01-22 at 18:13 +0200, Heikki Linnakangas wrote: > Simon Riggs wrote: > > On Thu, 2009-01-22 at 16:15 +0200, Heikki Linnakangas wrote: > >> That might be useful. But again, could just as well be implemented as an > >> external tool like pglesslog. > > > > There is no WAL record for "no-op", at least not one of variable length. > > Hmm, maybe there should be? That seems like a useful thing to have for > external tools. > > > The WAL files can't just have chunks of zeroes in the middle of them, > > they must be CRC valid and chained together in the exact byte position. > > There isn't any way to do this, even if there were, that's a seriously > > complex way of doing that. > > Hmm, I think you could remove the records in the middle, rechain the > remaining ones, recalculate the crc, and put an xlog switch record at > the end. I agree that's seriously complicated, a no-op record would be > much simpler. If someone else suggested that mechanism you'd laugh and rip it to shreds in an instant. You are brilliant at seeing simple, practical ways of doing things and that just ain't one of them. That's why for me this looks less and less like a debate to determine the best way forwards. I'm happy that you've chosen to spend your time on HS and I think we should both return to that, for a rest. I'll be posting a new version shortly. -- 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
Re: [HACKERS] Auto-updated fields
On Wednesday 21 January 2009 20:21:41 Bruce Momjian wrote: > Alvaro Herrera wrote: > > Robert Treat wrote: > > > On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote: > > > > David Fetter wrote: > > > > Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php > > > > > > > 1. Create a generic (possibly overloaded) trigger function, > > > > > bundled with PostgreSQL, which sets a field to some value. For > > > > > example, a timestamptz version might set the field to now(). > > > > > > > > Having the pre defined triggers at hand could be useful, especially > > > > for people not writing triggers so often to get used to it but I'm > > > > really not happy with the idea of magic preprocessing. > > > > > > I have a generic version of this in pagila. > > > > Now that we have a specific file in core for generic triggers (right now > > with a single one), how about adding this one to it? > > Any progress on this? TODO? I think this is a TODO, but not sure who is working on it or what needs to be done. The generic version in pagila is perhaps not generic enough: CREATE FUNCTION last_updated() RETURNS trigger AS $$ BEGIN NEW.last_update = CURRENT_TIMESTAMP; RETURN NEW; END $$ LANGUAGE plpgsql; It requires you name your column last_update, which is what the naming convention is in pagila, but might not work for everyone. Can someone work with that and move forward? Or maybe give a more specific pointer to the generic trigger stuff (I've not looked at it before) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] libpq WSACleanup is not needed
Andrew Chernow wrote: Tom Lane wrote: Andrew Chernow writes: I can try. Where should this be documented? ISTM that the best place is at the top of "30.1 Database Connection Control Functions", since the issue pertains to any connect/disconnect function. Does that sound correct? Should it be a warning or just regular text? Minor platform-specific performance nits are not that important. Think "footnote", not "warning at the top of the page". Its a footnote at the end of the first paragraph in 30.1. Fixed a few things. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ Index: doc/src/sgml/libpq.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/libpq.sgml,v retrieving revision 1.275 diff -C6 -r1.275 libpq.sgml *** doc/src/sgml/libpq.sgml 10 Jan 2009 20:14:30 - 1.275 --- doc/src/sgml/libpq.sgml 22 Jan 2009 17:13:09 - *** *** 59,72 is obtained from the function PQconnectdb or PQsetdbLogin. Note that these functions will always return a non-null object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check whether a connection was successfully made before queries are sent !via the connection object. ! PQconnectdbPQconnectdb Makes a new connection to the database server. --- 59,84 is obtained from the function PQconnectdb or PQsetdbLogin. Note that these functions will always return a non-null object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check whether a connection was successfully made before queries are sent !via the connection object. For windows applications, destroying a !PGconn can be expensive in a few cases. ! ! ! On windows, libpq issues a WSAStartup and WSACleanup on a per ! connection basis. Each WSAStartup increments an internal reference ! count which is decremented by WSACleanup. When calling WSACleanup ! with a reference count of zero, all resources will be freed and all ! DLLs will be unloaded. This is an expensive operation that can take ! as much as 300ms. The overhead can be seen if an application does ! not call WSAStartup and it closes its last PGconn. To avoid this, ! an application should manually call WSAStartup. ! ! PQconnectdbPQconnectdb Makes a new connection to the database server. -- 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] libpq WSACleanup is not needed
Tom Lane wrote: Andrew Chernow writes: I can try. Where should this be documented? ISTM that the best place is at the top of "30.1 Database Connection Control Functions", since the issue pertains to any connect/disconnect function. Does that sound correct? Should it be a warning or just regular text? Minor platform-specific performance nits are not that important. Think "footnote", not "warning at the top of the page". regards, tom lane Its a footnote at the end of the first paragraph in 30.1. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ Index: doc/src/sgml/libpq.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/libpq.sgml,v retrieving revision 1.275 diff -C6 -r1.275 libpq.sgml *** doc/src/sgml/libpq.sgml 10 Jan 2009 20:14:30 - 1.275 --- doc/src/sgml/libpq.sgml 22 Jan 2009 16:51:31 - *** *** 59,72 is obtained from the function PQconnectdb or PQsetdbLogin. Note that these functions will always return a non-null object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check whether a connection was successfully made before queries are sent !via the connection object. ! PQconnectdbPQconnectdb Makes a new connection to the database server. --- 59,84 is obtained from the function PQconnectdb or PQsetdbLogin. Note that these functions will always return a non-null object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check whether a connection was successfully made before queries are sent !via the connection object. For windows applications, destroying a !PGconn can be expensive in a few case. ! ! ! On windows, libpq issues a WSAStartup and WSACleanup on a per ! connection basis. Each WSAStartup increments an internal reference ! count which is decremented by WSACleanup. Calling WSACleanup with ! a reference count of zero, forces all resources to be freed and ! DLLs to be unloaded. This is an expensive operation that can take ! as much as 300ms. This overhead can be seen if an application does ! not call WSAStartup and it closes its last PGconn. To avoid this, ! an application should manually call WSAStartup. ! ! PQconnectdbPQconnectdb Makes a new connection to the database server. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, Jan 22, 2009 at 11:13 AM, Heikki Linnakangas wrote: > Simon Riggs wrote: >> On Thu, 2009-01-22 at 16:15 +0200, Heikki Linnakangas wrote: >>> >>> That might be useful. But again, could just as well be implemented as an >>> external tool like pglesslog. >> >> There is no WAL record for "no-op", at least not one of variable length. > > Hmm, maybe there should be? That seems like a useful thing to have for > external tools. > >> The WAL files can't just have chunks of zeroes in the middle of them, >> they must be CRC valid and chained together in the exact byte position. >> There isn't any way to do this, even if there were, that's a seriously >> complex way of doing that. > > Hmm, I think you could remove the records in the middle, rechain the > remaining ones, recalculate the crc, and put an xlog switch record at the > end. I agree that's seriously complicated, a no-op record would be much > simpler. Would I be pushing my luck if I suggested that maybe a pluggable rmgr would also be much simpler, and we already have a patch for that? :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Robert Haas wrote: On Thu, Jan 22, 2009 at 10:31 AM, Heikki Linnakangas wrote: The fact the patch does not do anything that anyone might ever want is not a sufficient grounds for rejecting it. Huh? That sounds like enough of a reason to me. s/anything that anyone might ever want/everything that anyone might ever want/ Well, if it did at least something that someone might want, the case would be much stronger ;-). Infrastructure changes for recovery was an earlier version of hot standby. That's all I was referring to here. The "infrastrucutre changes for recovery" patch is a prerequisite patch for hot standby. It's included now in the hot standby patch, but it does provide some functionality of its own, so it could be split out and committed separately. And it should, IMO. I am glad to hear that Hot Standby is still on the road to being committed, but even as a regular reader of -hackers I have to say the process has been somewhat murky to me. Either there is a lot of discussion that has been happening off-list, or there are long pauses when either you or Simon aren't really corresponding and it isn't obvious in whose court the ball lies. There hasn't been any substantial discussion off-list. The latter might've true at times. Also, I've been busy with other stuff, and Simon was ill at one point. Based on what I've seen on-list, I sort of thought that Simon was waiting for you to take the next step by committing at least some portion of the patch. Needless to say if you're both waiting for each other nothing will get done. Well, right now I'm waiting for a new version from Simon. But the infrastructure patch is really the first part that should be reviewed in detail (again) and committed. -- 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] Pluggable Indexes
> Of course, there's no much point in an index that's easily corrupted, so > I understand the desire to implement WAL too -- I'm just pointing out > that concurrency could have been developed independently. Anything's possible with enough work, but having good support in -core makes it easier and -core has usually been receptive to requests for such things - for example, I think Tom put in quite a bit of work to getting the right hooks in to enable libpqtypes. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] problem with archive_command as suggested by documentation
The documentation states in http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL "The archive command should generally be designed to refuse to overwrite any pre-existing archive file." and suggests an archive_command like "test ! -f .../%f && cp %p .../%f". We ran into (small) problems with an archive_command similar to this as follows: The server received a fast shutdown request while a WAL segment was being archived. The archiver stopped and left behind a half-written archive file. Now when the server was restarted, the archiver tried to archive the same WAL segment again and got an error because the destination file already existed. That means that WAL archiving is stuck until somebody manually removes the partial archived file. I suggest that the documentation be changed so that it does not recommend this setup. WAL segment names are unique anyway. What is your opinion? Is the problem I encountered a corner case that should be ignored? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, Jan 22, 2009 at 10:31 AM, Heikki Linnakangas wrote: >> The fact the patch does not do anything that anyone might ever want is >> not a sufficient grounds for rejecting it. > > Huh? That sounds like enough of a reason to me. s/anything that anyone might ever want/everything that anyone might ever want/ >> Much ink has been spilled in this space over the size and difficulty >> of reviewing Simon's hot standby patch, on the grounds that it is big >> and changed many things. Of course, Simon did submit an earlier >> version of this patch that was less big and changed fewer things, and >> it was never committed even though Simon responded to all of the >> review comments. > > What patch was that? Infrastructure changes for recovery was an earlier version of hot standby. That's all I was referring to here. > You're confusing things. I'm objecting this rmgr patch, but I'm spending all > the spare time I have to review the hot standby patch. It *does* and *has* > required a lot of fixing to get it into committable form. I feel that it's > pretty close now, but I'm waiting for his latest version and I still need to > go through it more closely before I feel comfortable enough to commit. > > (I should also say that if any of the other committers feels differently and > wants to pick up this rmgr patch and commit it, that's fine with me > (assuming the code is fine)) Hmm, well, not feeling that the patch is a priority for you seems somewhat different than saying that it should be rejected outright. I am glad to hear that Hot Standby is still on the road to being committed, but even as a regular reader of -hackers I have to say the process has been somewhat murky to me. Either there is a lot of discussion that has been happening off-list, or there are long pauses when either you or Simon aren't really corresponding and it isn't obvious in whose court the ball lies. Based on what I've seen on-list, I sort of thought that Simon was waiting for you to take the next step by committing at least some portion of the patch. Needless to say if you're both waiting for each other nothing will get done. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Simon Riggs wrote: On Thu, 2009-01-22 at 16:15 +0200, Heikki Linnakangas wrote: That might be useful. But again, could just as well be implemented as an external tool like pglesslog. There is no WAL record for "no-op", at least not one of variable length. Hmm, maybe there should be? That seems like a useful thing to have for external tools. The WAL files can't just have chunks of zeroes in the middle of them, they must be CRC valid and chained together in the exact byte position. There isn't any way to do this, even if there were, that's a seriously complex way of doing that. Hmm, I think you could remove the records in the middle, rechain the remaining ones, recalculate the crc, and put an xlog switch record at the end. I agree that's seriously complicated, a no-op record would be much simpler. -- 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Robert Haas escribió: > We allow extensibility and hooks in other parts of the database where > the use case is pretty thin and tenuous. I betcha there aren't many > people who try writing their own eqjoinsel() either. The PostGIS guys do implement their own selectivity estimators. In fact it was them that first implemented pluggability in that area, AFAIR. -- 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] Pluggable Indexes
Teodor Sigaev wrote: >> What other constraints are there on such non-in-core indexex? Early (2005) >> GIST indexes were very painful in production environments because vacuuming >> them held locks for a *long* time (IIRC, an hour or so on my database) on >> the indexes locking out queries. Was that just a shortcoming of the >> implementation, or was it a side-effect of them not supporting >> recoverability. > > GiST concurrent algorithm is based on Log Sequence Number of WAL and that > was the reason to implement WAL (and recoverability) first in GiST. Hmm, IIRC it is based on a monotonically increasing number. It could have been anything. LSN was just a monotonically increasing number that would be available if WAL was implemented first (or in parallel). Of course, there's no much point in an index that's easily corrupted, so I understand the desire to implement WAL too -- I'm just pointing out that concurrency could have been developed independently. -- 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Robert Haas wrote: The fact the patch does not do anything that anyone might ever want is not a sufficient grounds for rejecting it. Huh? That sounds like enough of a reason to me. Much ink has been spilled in this space over the size and difficulty of reviewing Simon's hot standby patch, on the grounds that it is big and changed many things. Of course, Simon did submit an earlier version of this patch that was less big and changed fewer things, and it was never committed even though Simon responded to all of the review comments. What patch was that? In fact, even after you took the time to split it back out again, and even after acknowledging that the split-out part was good code and independently useful, you never committed THAT either. And so here we sit in limbo. I did split the "recovery infrastructure" patch from the hot standby patch. I still intend to review and hopefully commit that (I'll need to split the latest version from the hot standby patch again). When I reviewed it for the first time, I just didn't feel that I understood it well enough to commit it. But that's a completely different patch than what we're talking about now. If you now reject this patch because it is small and changes too few things, then will you reject his next patch that is more comprehensive on the grounds that the patch is now too big to review? I won't and haven't rejected a patch because it's too big to review. I admit that a big patch is a lot harder and more time consuming to review, so I might not have the time or desire to review it. But that's a different story. I wonder what Simon has to do to get a patch committed. It's been four months since he started submitting patches, and so far the only thing that's been committed is the pg_stop_backup() wait bug fix. If the code were bad or required a lot of fixing to get it in committable form, that would be completely understandable but no one is alleging that. You're confusing things. I'm objecting this rmgr patch, but I'm spending all the spare time I have to review the hot standby patch. It *does* and *has* required a lot of fixing to get it into committable form. I feel that it's pretty close now, but I'm waiting for his latest version and I still need to go through it more closely before I feel comfortable enough to commit. (I should also say that if any of the other committers feels differently and wants to pick up this rmgr patch and commit it, that's fine with me (assuming the code is fine)) -- 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] libpq WSACleanup is not needed
Andrew Chernow writes: > I can try. Where should this be documented? ISTM that the best place > is at the top of "30.1 Database Connection Control Functions", since the > issue pertains to any connect/disconnect function. Does that sound > correct? Should it be a warning or just regular text? Minor platform-specific performance nits are not that important. Think "footnote", not "warning at the top of the page". 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, Jan 22, 2009 at 9:15 AM, Heikki Linnakangas wrote: >> Immediate use cases for me would be >> >> * ability to filter WAL records based on database or relation > > This patch isn't enough to allow the catalog lookups. Without the catalog > lookups, you might as well implement that as an external tool, like > pglesslog. The fact the patch does not do anything that anyone might ever want is not a sufficient grounds for rejecting it. If it were, zero patches would ever get accepted. If additional changes are needed, Simon or someone else can send a patch later with those changes. Much ink has been spilled in this space over the size and difficulty of reviewing Simon's hot standby patch, on the grounds that it is big and changed many things. Of course, Simon did submit an earlier version of this patch that was less big and changed fewer things, and it was never committed even though Simon responded to all of the review comments. In fact, even after you took the time to split it back out again, and even after acknowledging that the split-out part was good code and independently useful, you never committed THAT either. And so here we sit in limbo. If you now reject this patch because it is small and changes too few things, then will you reject his next patch that is more comprehensive on the grounds that the patch is now too big to review? I wonder what Simon has to do to get a patch committed. It's been four months since he started submitting patches, and so far the only thing that's been committed is the pg_stop_backup() wait bug fix. If the code were bad or required a lot of fixing to get it in committable form, that would be completely understandable but no one is alleging that. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, 2009-01-22 at 16:15 +0200, Heikki Linnakangas wrote: > Simon Riggs wrote: > > Immediate use cases for me would be > > > > * ability to filter WAL records based on database or relation > > This patch isn't enough to allow the catalog lookups. Without the > catalog lookups, you might as well implement that as an external tool, > like pglesslog. It makes it harder, but you can specify oids easily enough. Flat file handling reads pg_database during recovery, so can we. Or you can look in global/pg_database flat file. > > * ability to recover quickly from various types of bug, for example if > > new freespace code caused a corruption we would be able to sidestep it > > and get the database up again quickly without doing resetxlog and losing > > data. > > That might be useful. But again, could just as well be implemented as an > external tool like pglesslog. There is no WAL record for "no-op", at least not one of variable length. The WAL files can't just have chunks of zeroes in the middle of them, they must be CRC valid and chained together in the exact byte position. There isn't any way to do this, even if there were, that's a seriously complex way of doing that. pg_lesslog takes great care to reconstruct the files into the right shape because recovery is such an unforgiving mistress. > (the new FSM implementation isn't WAL-logged, so that particular > scenario isn't very plausible) Yeh, that was just a joke. But the principle applies to any index, as I'm sure you realise. -- 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
Re: [HACKERS] libpq WSACleanup is not needed
Magnus Hagander wrote: Andrew Chernow wrote: Magnus Hagander wrote: In which case, we should perhaps just document the workaround using WSAStartup() yourself, and not bother with either API or connection parameter... I didn't originally agree with this but now I do. Any libpq init function for wsa, would only be replacing an app calling WSAStartup themselves. So, why have it at all. Ok, I think we're fairly agreed that this is the way to proceed then. Do you want to propose some wording for the documentation, or should I try to write something myself? //Magnus I can try. Where should this be documented? ISTM that the best place is at the top of "30.1 Database Connection Control Functions", since the issue pertains to any connect/disconnect function. Does that sound correct? Should it be a warning or just regular text? First attempt: "On windows, libpq issues a WSAStartup and WSACleanup on a per connection basis. Each WSAStartup increments an internal reference count which is decremented by WSACleanup. Calling WSACleanup with a reference count of zero, forces all resources to be freed and DLLs to be unloaded. This is an expensive operation that can take as much as 300ms. This overhead can be seen if an application does not call WSAStartup and it closes its last PGconn. To avoid this, an application should manually call WSAStartup." -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] deductive databases in postgreSQL
Carlos Gonzalez-Cadenas escreveu: > We're looking for a deductive database for our application. Oracle, in > the 11g version, has extended its RDBMS with deductive capabilities, > supporting a subset of OWL-DL[1]. They are able to load an ontology, > perform the inferences, dump the inferred info in the database and after > that answer queries with the asserted plus the inferred info. > > We would like to have similar functionalities in PostgreSQL. Do you know > if there's someone working on that or if there are plans to support it > soon?. If not, what do you think is the best and most efficient way of > implementing it? > No one that I know of. Well, it is a long road. The addition of a data type xml is recent (8.3). We lack a set of features like indexing, a new data structure (?), XQuery, XPath improvement and, so on [1]. Don't expect much of these TODO items completed before the next two releases (unless you want to take a stab). After all of these basic infrastructure, we need a language (SPARQL?) and an inference engine -- this is a big task AFAICT. At this point, don't know if hackers will agree in adding such a big feature in core that is not SQL standard (aka SQL/XML) but maybe it could be an external module. [1] http://wiki.postgresql.org/wiki/XML_Todo -- Euler Taveira de Oliveira http://www.timbira.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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Simon Riggs wrote: Immediate use cases for me would be * ability to filter WAL records based on database or relation This patch isn't enough to allow the catalog lookups. Without the catalog lookups, you might as well implement that as an external tool, like pglesslog. * ability to recover quickly from various types of bug, for example if new freespace code caused a corruption we would be able to sidestep it and get the database up again quickly without doing resetxlog and losing data. That might be useful. But again, could just as well be implemented as an external tool like pglesslog. (the new FSM implementation isn't WAL-logged, so that particular scenario isn't very plausible) -- 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, 2009-01-22 at 14:52 +0200, Heikki Linnakangas wrote: > Oleg Bartunov wrote: > > as I understand, there are already plans to utilize this feature. If so, > > we need to be more attentive by now. > > Is there? If I understood Simon correctly in the last paragraphs of > these emails: > > http://archives.postgresql.org/message-id/1221470800.3913.1229.ca...@ebony.2ndquadrant > http://archives.postgresql.org/message-id/1221555881.3913.1761.ca...@ebony.2ndquadrant > > he has no immediate use for this. Immediate use cases for me would be * ability to filter WAL records based on database or relation * ability to recover quickly from various types of bug, for example if new freespace code caused a corruption we would be able to sidestep it and get the database up again quickly without doing resetxlog and losing data. Medium term * bit map indexes But this isn't just for me... -- 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
Re: [Fwd: Re: [HACKERS] Transactions and temp tables]
Bruce Momjian wrote: Heikki Linnakangas wrote: IMHO, this is just getting too kludgey. We came up with pretty good ideas on how to handle temp tables properly, by treating the same as non-temp tables. That should eliminate all the problems the latest patch did, and also the issues with sequences, and allow all access to temp tables, not just a limited subset. I don't think it's worthwhile to apply the kludge as a stopgap measure, let's do it properly in 8.5. ... Can someone tell me how this should be worded as a TODO item? There already is a todo item about this: "Allow prepared transactions with temporary tables created and dropped in the same transaction, and when an ON COMMIT DELETE ROWS temporary table is accessed " I added a link to the email describing the most recent idea on how this should be implemented. -- 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Oleg Bartunov wrote: as I understand, there are already plans to utilize this feature. If so, we need to be more attentive by now. Is there? If I understood Simon correctly in the last paragraphs of these emails: http://archives.postgresql.org/message-id/1221470800.3913.1229.ca...@ebony.2ndquadrant http://archives.postgresql.org/message-id/1221555881.3913.1761.ca...@ebony.2ndquadrant he has no immediate use for this. -- 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
[HACKERS] Table Partitioning Feature
Hi,
We are implementing table partitioning feature to support
- the attached commands. The syntax conforms to most of the suggestion
mentioned in
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00413.php, barring
the following:
-- Specification of partition names is optional. System will be able to
generate partition names in such cases.
-- sub partitioning
We are using pgsql triggers to push/move data to appropriate partitions,
but we will definitely consider moving to C language triggers as suggested
by manu.
- Global non-partitioned indexes (that will extend all the partitions).
- Foreign key support for tables referring to partitioned tables.
Please feel free to post your comments and suggestions.
Thanks,
Amit
Persistent Systems
1. Creating partition table with "Create table" command.
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]
[ PARTITION BY [ HASH | LIST | RANGE ] (column_name)
[
PARTITIONS num_hash_partitions |
list_patition [,...] |
range_partition [,...]
]
]
where list_partition is:
[Partition_name] VALUES
[ (const_expression[,...]) | DEFAULT]
Database will generate partition name, if it is not specified.
where range_partition is:
[Partition_name]
[
([START const_expression] END const_expression) |
DEFAULT
]
Database will generate partition name, if it is not specified.
Start key word is optional. Default values can be stored in a 'default'
partition.
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
UNIQUE [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
CHECK (expression) |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH
SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ...
] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON
UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
2. Adding/Splitting/Updating/dropping a partition to an existing table with
ALTER TABLE command.
ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name
ALTER TABLE name
SET SCHEMA new_schema
ALTER TABLE name
ADD PARITION [list_partition | range_partition]
ALTER TABLE name
SPLIT PARITION [Partition_name] AT
VALUES const_expression [,...]
[INTO (Partition_name1, Partition_name2)]
The into clause will allow users to provide names of newly created partitions
after the split.
For list partitioning, Partition_name2 will be created with the list of
specified const_expressions, and
Partition_name will be renamed to Partition_name1 after excluding specified
values from its list.
For range partition, a const_expression will indicate the split point.
Partition_name2 will be formed with
max_value and split_point as the range, and Partition_name1 will be formed with
min_value and split_point
range.
ALTER TABLE name
UPDATE PARTITION
[
PARTITIONS TO num_hash_partitions |
[Partition_name | VALUES (const_expression[,...])] TO VALUES
(const_expression[,...]) |
[Partition_name | START (const_expression) END (const_expression) ] TO
START (const_expression) END (const_expression)
]
To change partition key values for list and range partitioned tables, the user
has to either Specify partition name
of partition list/range that identifies the partition to be updated.
ALTER TABLE name
DROP PARITION
Partition_name |
FOR VALUES (const_expression [,...]) |
FOR ([START const_expression] END const_expression)
ALTER TABLE table_name
RENAME Partition_name to Partition_name
where action is one of:
ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP C
Re: [HACKERS] libpq WSACleanup is not needed
Andrew Chernow wrote: > Magnus Hagander wrote: >> >> In which case, we should perhaps just document the workaround using >> WSAStartup() yourself, and not bother with either API or connection >> parameter... >> >> > > I didn't originally agree with this but now I do. Any libpq init > function for wsa, would only be replacing an app calling WSAStartup > themselves. So, why have it at all. Ok, I think we're fairly agreed that this is the way to proceed then. Do you want to propose some wording for the documentation, or should I try to write something myself? //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] [PATCHES] GIN improvements
BTW, gincostestimate could use that information for cost estimation, but is index opening and metapge reading in amcostestimate acceptable? That sounds reasonable to me. I think that's what the index-specific cost estimators are for. Done. Do you expect a performance impact? I'm afraid for that and will test tomorrow. But statistic from index is exact. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ fast_insert_gin-0.24.gz Description: Unix tar archive -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Thu, 2009-01-22 at 10:09 +0200, Heikki Linnakangas wrote: > Simon Riggs wrote: > > Preventing work on new indexes by non-committers has meant that Bitmap > > indexes, which first came out in 2005 have not been usable with > > Postgres. That forced people *away* from Postgres towards Bizgres. Lack > > of Bitmap indexes is a huge issue for many people. It's 2009 now and it > > seems probable that without this patch it will be 2010 at least before > > they see BMIs, and later still before they see other index types. > > No-one is preventing anyone from working on bitmap indexes. > Bitmap indexes required other backend changes, in addition to the rmgr > changes. This rmgr plugin patch is *not* sufficient to enable bitmap > indexes to live as a plugin. > > This patch does *not* bring us any closer to having bitmap indexes. > Don't raise false hopes. I agree those changes would be better but those changes are *not* essential (as has been agreed onlist). They are just a possible tuning feature, amongst many that must prove themselves before they happen. Manipulating multiple large bitmaps on a 1 TB table will still be much more efficient than reading multiple btrees and manipulating those. BMIs are typically much smaller than btrees, so even if they use some memory we will avoid significant amounts of real I/O. BMIs also have a significantly lower time to build, making them much more practical. It is not a false hope since the case is not black/white, just a matter of opinion. -- 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
Re: [HACKERS] Pluggable Indexes
What other constraints are there on such non-in-core indexex? Early (2005) GIST indexes were very painful in production environments because vacuuming them held locks for a *long* time (IIRC, an hour or so on my database) on the indexes locking out queries. Was that just a shortcoming of the implementation, or was it a side-effect of them not supporting recoverability. GiST concurrent algorithm is based on Log Sequence Number of WAL and that was the reason to implement WAL (and recoverability) first in GiST. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] Re: [COMMITTERS] pgsql: Explicitly bind gettext() to the UTF8 locale when in use.
Hiroshi Inoue wrote: > Magnus Hagander wrote: >> Peter Eisentraut wrote: >>> Magnus Hagander wrote: However, one question: The comment currently says it's harmless to do this on non-windows platforms. Does this still hold true? >>> Yes, the non-WIN32 code path appears to be the same, still. But the >>> ifdef WIN32 part we don't want, because that presumes something about >>> the spelling of encoding names in the local iconv library. >>> If we do keep the thing win32 only, I think we should just wrap the whole thing in #ifdef WIN32 and no longer do the codeset stuff at all on Unix - that'll make for cleaner code. >>> Yes, that would be much better. >> >> Something like this then? > > It seems OK to me. Applied. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] deductive databases in postgreSQL
Hi all, We're looking for a deductive database for our application. Oracle, in the 11g version, has extended its RDBMS with deductive capabilities, supporting a subset of OWL-DL[1]. They are able to load an ontology, perform the inferences, dump the inferred info in the database and after that answer queries with the asserted plus the inferred info. We would like to have similar functionalities in PostgreSQL. Do you know if there's someone working on that or if there are plans to support it soon?. If not, what do you think is the best and most efficient way of implementing it? Thank you very much in advance, Carlos [1]: http://www.oracle.com/technology/tech/semantic_technologies/index.html
[HACKERS] SE-PostgreSQL on Linux.conf.au 2009
Currently, Linux.conf.au 2009 is held on Hobart, Tasmania. I had to plan to talk about SE-PostgreSQL and related stuff, but it became unavailable to attend the conference due to the recent economic circumstances unfortunately. :( Russell Coker is one of the core developers in SELinux community. He volunteered to give talks about these stuffs on the slot instead of me, though he was also supposed to talk about SELinux in Debian. He also noted that audiences were reasonably interested, and an attendee introduced SE-PostgreSQL in his session later, as follows: http://etbe.coker.com.au/2009/01/19/security-enhanced-postgresql/ http://etbe.coker.com.au/2009/01/20/status-se-linux-debian-lca2009/ http://etbe.coker.com.au/2009/01/22/se-lapp/ Slide for the talks are available here: * SE-PostgreSQL - "system wide" consistency in access controls - (Mon Jan 19, OSS Database miniconf) http://sepgsql.googlecode.com/files/LCA20090119-sepgsql.pdf * LAPP/SELinux - A secure web application platform powered by SELinux - (Tue Jan 20, Linux Security miniconf) http://sepgsql.googlecode.com/files/LCA20090120-lapp-selinux.pdf I'm happy, if you become interested in these features more. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Pluggable Indexes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, Jan 21, 2009 at 10:48:21PM +, Simon Riggs wrote: > > On Thu, 2009-01-22 at 00:29 +0300, Oleg Bartunov wrote: [...] > > Other question, why don't improve GiST to allow support of more indexes ? > > bitmap indexes could be implemented usin g GiST. [...] > I'll avoid discussing index design with you :-) Oooh. What a pity -- this would allow us lurkers to learn a lot! (Oh, wait, Heikki has taken up that :-) Just wanted to say -- thanks folks - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFJeDvvBcgs9XrR2kYRAviLAJ4jW1rSygrgeA4M73PerFqWXmO4NACeNvV8 GSSnxUyCroSrvpF2PBevBV4= =jhqe -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Extension of Thick Indexes
Hi, We are extending Gokul's Thick index functionality ( http://archives.postgresql.org/pgsql-patches/2008-01/msg00092.php) on 8.4 code base to - fix existing defects - Extend thick index usage for group by, count, nested queries, and "delete/update with join" queries. - Improve cost estimates for using thick indexes, and - Support OR condition queries using thick indexes (which involves computation of bitmap-or) Details of the above features can be seen at http://aurora.regenstrief.org/postgresql/report/6 Please let me know if you have any comments or suggestions. Thanks, Amit Persistent Systems
[HACKERS] Logging conflicted queries on deadlocks
Can you please help me solve the problem of ERROR deadlock detected This is the log incurred in postgressql.log 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "displet" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "screenscrapesource" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "squidmodeconfiguration" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "videosource" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "websource" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "decorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "display" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "application" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "audiodecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "borderdecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "display_snapshot" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "imagedecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "labeldecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statusborderdecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "window" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "applicationwindow" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "datetimedecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "logodecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "perspectivewindow" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "snapshot_window" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "sourcenamedecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statictextdecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statusborderdecorator_linestyle" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statusimagedecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statuslabeldecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "display" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "perspective" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "snapshot" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "application" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "display_snapshot" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "displet" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "message" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "perspectivewindow" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "snapshot_window" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "window" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "applicationwindow" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "decorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "audiodecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "borderdecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "imagedecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "labeldecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statusborderdecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "datetimedecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "logodecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "sourcenamedecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statictextdecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statusborderdecorator_linestyle" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statusimagedecorator" 2009-01-21 15:47:20 IST NOTICE: truncate cascades to table "statuslabeldecorator" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "systemvariablebinding" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "borderdecorator" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "statusborderdecorator_linestyle" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "display" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "display_snapshot" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "snapshot_window" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "application" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "window" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "applicationwindow" 2009-01-21 15:47:21 IST NOTICE: truncate cascades to table "perspectivewindow" 2009-01-21 15:53:29 IST NOTICE: truncate cascades to table "displet" 2009-01-21 15:53:29 IST NOTICE: truncate cascades to table "screenscrapesource" 2009-01-21 15:53:30 IST ERROR: deadlock detected 2009-01-21 15:53:30 IST DETAIL: Process 12648 waits for AccessShareLock on
Re: [HACKERS] Re: [COMMITTERS] pgsql: Explicitly bind gettext() to the UTF8 locale when in use.
Magnus Hagander wrote: Peter Eisentraut wrote: Magnus Hagander wrote: However, one question: The comment currently says it's harmless to do this on non-windows platforms. Does this still hold true? Yes, the non-WIN32 code path appears to be the same, still. But the ifdef WIN32 part we don't want, because that presumes something about the spelling of encoding names in the local iconv library. If we do keep the thing win32 only, I think we should just wrap the whole thing in #ifdef WIN32 and no longer do the codeset stuff at all on Unix - that'll make for cleaner code. Yes, that would be much better. Something like this then? It seems OK to me. regards, Hiroshi Inoue -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Hi all, I hope to raise some valid concerns with the following two stories, a "true story" first then a little fiction that I hope has a lot to do with current reality. Le jeudi 22 janvier 2009, Heikki Linnakangas a écrit : > It's also impossible to do many other things without modifying the > source code. Bitmap indexam had to do it, my clustered indexes had to do > it, GIN had to do it. So we're "only" talking about new index kinds which fit current indexam API, right? > Sure you can. Just Do It, if that's what you want. If you're willing to > write a custom indexam, and run it in production, compiling PostgreSQL > from source and patching it isn't such a stretch. It's all about comfort and product maturity, isn't it? I had performance concerns for prefix matching, ala telecom matches, i.e. the prefix is in the table, not in the literal. And our IRC PostgreSQL guru told me the best way to solve it would be implementing a specific datatype with specific indexing facility. Easy enough? Sure, he said, just write an external module and provide a GiST OPERATOR CLASS. I did just this, wrote a single C file (less than 2000 lines) and I now run my datatype and its GiST index in production. It has already served something like 15 millions lookups and counting. Just works. http://www.postgresql.org/docs/8.3/static/xindex.html http://wiki.postgresql.org/images/3/30/Prato_2008_prefix.pdf http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/prefix/prefix/ I assure you that should I have needed to patch PostgreSQL, I'd be running plpgsql procedural code instead and would be fighting against this real time costing evaluation trigger with such bad perfs. True story. :) > Don't get me wrong, I'm certainly not against pluggable indexes in > principle. I just don't believe this patch brings us closer to that goal > in any significant way. If I understand the matter at all, it brings us closer only when the new index type can be done without changing current indexam API. Which covers BTree, Hash, GiST and GIN, so could probably cover some more. If I were to start developping a new external module index kind, I'd really like to avoid this situation: - so for my new index idea, I'm only to write some custom C code? - yes, an simple external module, following indexam API - great, will I be able to WAL log it from this external module? - of course, it's PostgreSQL we're talking about. - what about recovering my custom index? - oh. now you have to patch core code and run custom PG version - huh? - yes, core team finds the situation comfortable enough as is. - ... > Nothing stops you from starting right now, without this plugin. This is > open source. We're not talking about how great it is to be able to experiment new ideas by forking core code, we're talking about making it easy and comfy to run user code in production environments and being able to still apply minor upgrades strait from the distribution. Or maybe I'm misunderstanding it all. Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
On Wed, 21 Jan 2009, Bruce Momjian wrote: Josh Berkus wrote: Bruce, Plugability adds complexity. Heikki's comment is that adding this patch make the job of creating pluggable indexes 5% easier, while no one is actually working on plugable indexes, and it hard to say that making it 5% easier really advances anything, especially since many of our existing index types aren't WAL-logged. Plugability is not a zero-cost feature. Right. And I'm saying that pluggability is PostgreSQL's main reason for existence, if you look at our place in the future of databases. So it's worth paying *some* cost, provided that the cost/benefit ratio works for the particular patch. To rephrase: I can't judge the rmgr patch one way or the other. I'm only objecting to the idea expressed by Heikki and others that pluggable indexes are stupid and unnecessary. It is cost vs. benefit. No one is saying plugabiity is bad, only that in this case it is more costly than beneficial; of course, that might change some day. as I understand, there are already plans to utilize this feature. If so, we need to be more attentive by now. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Pluggable Indexes
Oleg Bartunov wrote: bitmap indexes could be implemented usin g GiST. Huh, how would that work? Bitmap indexes have a very different structure, AFAICS. -- 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: Pluggable Indexes (was Re: [HACKERS] rmgr hooks (v2))
Simon Riggs wrote: Preventing work on new indexes by non-committers has meant that Bitmap indexes, which first came out in 2005 have not been usable with Postgres. That forced people *away* from Postgres towards Bizgres. Lack of Bitmap indexes is a huge issue for many people. It's 2009 now and it seems probable that without this patch it will be 2010 at least before they see BMIs, and later still before they see other index types. No-one is preventing anyone from working on bitmap indexes. Bitmap indexes required other backend changes, in addition to the rmgr changes. This rmgr plugin patch is *not* sufficient to enable bitmap indexes to live as a plugin. This patch does *not* bring us any closer to having bitmap indexes. Don't raise false hopes. -- 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
