Re: [HACKERS] wCTE behaviour
On 2010-11-11 17:50, Marko Tiikkaja wrote:
Just to be clear, the main point is whether they see the data
modifications or not. The simplest case to point out this behaviour is:
WITH t AS (DELETE FROM foo)
SELECT * FROM foo;
And the big question is: what state of foo should the SELECT
statement see?
Since t is not referenced in the query, foo should not be deleted at
all, like
WITH t AS (SELECT nextval('seq'))
SELECT * FROM foo
does not update the sequence.
But if t is referenced..
WITH t AS (DELETE FROM foo RETURNING *)
SELECT * FROM foo NATURAL JOIN t;
Since the extension of t can only be known by deleting foo, it makes
sense that this query cannot return rows. Select the rows from foo that
I just deleted.
regards,
Yeb Havinga
--
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] Restructuring plancache.c API
On 2010-11-11 23:21, Tom Lane wrote: I've been thinking about supporting automatic replan of cached plans using specific parameter values, as has been discussed several times, at greatest length in this thread: http://archives.postgresql.org/pgsql-hackers/2010-02/msg00607.php .. I want to rearrange it so there's an explicit notion of three levels of cacheable object: 1. Raw parse tree + source string. These obviously never change. In the context of cached plans and specific parameter values, a idea for the future might be to also consider a cached plan for planning of simple queries. A way to do this is by regarding all constants in a simple query as parameters, and look for a cached plan for that parameterized query. To lower the chance for choosing a bad plan for the actual parameter values, a cached plan could also store the actual parameter values used during planning. (where planning was done with constants, not parameters, this would require back replacing the actual values as constants in the parameterized query). Based on exact match on the raw parse tree of the parameterized source tree and neighbourhood of the actual parameter values of the cached and current query, a plan could be chosen or not. If replanning was chosen, this new plan could also be stored as new cached plan of the same query but with different parameter values. It would require one more level in the plan cache 1 raw parse tree of parameterized query 2 one or more source string + actual parameter values (these were the replaced constants) then for each entry in level 2 the remaining levels. regards, Yeb Havinga -- 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] multi-platform, multi-locale regression tests
On 11/10/2010 10:58 PM, Peter Eisentraut wrote: One thing to aim for, perhaps, would be to make all tools in use produce a common output format, at least optionally, so that creating a common test run dashboard or something like that is more easily possible. TAP and xUnit come to mind. Note that dtester features a TAP reporter. However, the way Kevin uses dtester, that probably won't give useful results. (As he uses custom print statements to do more detailed reporting than TAP could ever give you). Regards Markus Wanner -- 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] TODO Alter Table Rename Constraint
OK, I see. Thanks for mentioning it. Are there other problems with the suggestion? Or should the work like that? Viktor 2010/11/10 Robert Haas robertmh...@gmail.com On Wed, Nov 10, 2010 at 6:32 AM, Viktor Valy vili0...@gmail.com wrote: Thanks for your answer! I'm not really familiar with inheritance, but I wonder how this issue is handled in other cases, for instance renaming an index, which invokes internal a constraint rename too. Is that relevant or is the renaming of constraints so special? Indexes can't be inherited, so the problem doesn't arise in that case. Is there a solution for the test-cases you have posted? Or is this yet a problem? We had a bug related to the handling of ALTER TABLE .. ADD/DROP CONSTRAINT for those test cases, which I fixed. I think we still have a similar problem with ALTER TABLE .. ADD/DROP ATTRIBUTE, which I haven't fixed because it's hard and I haven't had time, and no one seems to care that much. My point was just that whatever patch you come up with for ALTER TABLE .. RENAME CONSTRAINT should probably be tested against those cases to see if it behaves correctly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] security hooks on object creation
I revised my patch according to the prior suggestions.
Invocation of the hooks is encapsulated within macro, not function:
+ #define InvokeObjectAccessHook0(access,classId,objectId,subId)\
+ do {\
+ if (object_access_hook) \
+ (*object_access_hook)((access),(classId),(objectId),(subId)); \
+ } while(0)
The 0 of tail means that it does not takes any arguments except for
object-ids, like syscache code.
It will reduce impact when we want to add arguments of the hooks.
In the previous version, it just support seven object classes that is
allowed to assign security labels. But, Robert pointed out the purpose
of post-creation hook is limited to security labeling. So, I expand its
coverage into all the commentable object classes.
- relations: heap_create_with_catalog()
- constraint: CreateConstraintEntry()
- conversion: ConversionCreate()
- schema: NamespaceCreate()
- operator:OperatorCreate() and OperatorShellMake()
- procedure: ProcedureCreate()
- type:TypeCreate() and TypeShellMake()
- database:createdb()
- cast:CreateCast()
- opfamily:CreateOpFamily()
- opclass: DefineOpClass()
- language:create_proc_lang()
- attribute: ATExecAddColumn()
- tablespace: CreateTableSpace()
- trigger: CreateTrigger()
- ts_parser: DefineTSParser()
- ts_dict: DefineTSDictionary()
- ts_template: DefineTSTemplate()
- ts_config: DefineTSConfiguration()
- role:CreateRole()
- rule:InsertRule()
- largeobject: inv_create()
The post-creation hooks are put on the place just after adding dependency
of the new object, if the object class uses dependency mechanism.
I believe it will be a clear guidance for the future maintenance works.
Thanks,
(2010/11/11 7:41), KaiGai Kohei wrote:
(2010/11/11 3:00), Robert Haas wrote:
On Wed, Nov 10, 2010 at 8:33 AM, KaiGai Koheikai...@kaigai.gr.jp wrote:
(2010/11/10 13:06), Robert Haas wrote:
In this patch, we put InvokeObjectAccessHook0 on the following functions.
- heap_create_with_catalog() for relations/attributes
- ATExecAddColumn() for attributes
- NamespaceCreate() for schemas
- ProcedureCreate() for aggregates/functions
- TypeCreate() and TypeShellMake() for types
- create_proc_lang() for procedural languages
- inv_create() for large objects
I think you ought to try to arrange to avoid the overhead of a
function call in the common case where nobody's using the hook.
That's why I originally suggested making InvokeObjectAccessHook() a
macro around the actual function call.
Hmm. Although I have little preference here, the penalty to call
an empty function (when no plugins are installed) is not visible,
because frequency of DDL commands are not high.
Even so, is it necessary to replace them by macros?
It's a fair point. I'm open to other opinions but my vote is to shove
a macro in there. A pointer test is cheaper than a function call, and
doesn't really complicate things much.
Since I have no strong preference function call here, so, I'll revise my
patch according to your vote.
Thanks,
--
KaiGai Kohei kai...@ak.jp.nec.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] security hooks on object creation
(2010/11/12 19:34), KaiGai Kohei wrote:
I revised my patch according to the prior suggestions.
I'm sorry. I revised my patch, but not attached.
Please see this attached one.
Thanks,
Invocation of the hooks is encapsulated within macro, not function:
+ #define InvokeObjectAccessHook0(access,classId,objectId,subId)\
+ do {\
+ if (object_access_hook) \
+ (*object_access_hook)((access),(classId),(objectId),(subId)); \
+ } while(0)
The 0 of tail means that it does not takes any arguments except for
object-ids, like syscache code.
It will reduce impact when we want to add arguments of the hooks.
In the previous version, it just support seven object classes that is
allowed to assign security labels. But, Robert pointed out the purpose
of post-creation hook is limited to security labeling. So, I expand its
coverage into all the commentable object classes.
- relations:heap_create_with_catalog()
- constraint: CreateConstraintEntry()
- conversion: ConversionCreate()
- schema: NamespaceCreate()
- operator: OperatorCreate() and OperatorShellMake()
- procedure:ProcedureCreate()
- type: TypeCreate() and TypeShellMake()
- database: createdb()
- cast: CreateCast()
- opfamily: CreateOpFamily()
- opclass: DefineOpClass()
- language: create_proc_lang()
- attribute:ATExecAddColumn()
- tablespace: CreateTableSpace()
- trigger: CreateTrigger()
- ts_parser:DefineTSParser()
- ts_dict: DefineTSDictionary()
- ts_template: DefineTSTemplate()
- ts_config:DefineTSConfiguration()
- role: CreateRole()
- rule: InsertRule()
- largeobject: inv_create()
The post-creation hooks are put on the place just after adding dependency
of the new object, if the object class uses dependency mechanism.
I believe it will be a clear guidance for the future maintenance works.
Thanks,
(2010/11/11 7:41), KaiGai Kohei wrote:
(2010/11/11 3:00), Robert Haas wrote:
On Wed, Nov 10, 2010 at 8:33 AM, KaiGai Koheikai...@kaigai.gr.jp wrote:
(2010/11/10 13:06), Robert Haas wrote:
In this patch, we put InvokeObjectAccessHook0 on the following functions.
- heap_create_with_catalog() for relations/attributes
- ATExecAddColumn() for attributes
- NamespaceCreate() for schemas
- ProcedureCreate() for aggregates/functions
- TypeCreate() and TypeShellMake() for types
- create_proc_lang() for procedural languages
- inv_create() for large objects
I think you ought to try to arrange to avoid the overhead of a
function call in the common case where nobody's using the hook.
That's why I originally suggested making InvokeObjectAccessHook() a
macro around the actual function call.
Hmm. Although I have little preference here, the penalty to call
an empty function (when no plugins are installed) is not visible,
because frequency of DDL commands are not high.
Even so, is it necessary to replace them by macros?
It's a fair point. I'm open to other opinions but my vote is to shove
a macro in there. A pointer test is cheaper than a function call, and
doesn't really complicate things much.
Since I have no strong preference function call here, so, I'll revise my
patch according to your vote.
Thanks,
--
KaiGai Kohei kai...@ak.jp.nec.com
*** a/src/backend/catalog/heap.c
--- b/src/backend/catalog/heap.c
***
*** 63,68
--- 63,69
#include utils/acl.h
#include utils/builtins.h
#include utils/fmgroids.h
+ #include utils/hooks.h
#include utils/inval.h
#include utils/lsyscache.h
#include utils/relcache.h
***
*** 1188,1193 heap_create_with_catalog(const char *relname,
--- 1189,1198
}
}
+ /* Post creation of new relation */
+ InvokeObjectAccessHook0(OAT_POST_CREATE,
+ RelationRelationId, relid, 0);
+
/*
* Store any supplied constraints and defaults.
*
*** a/src/backend/catalog/pg_constraint.c
--- b/src/backend/catalog/pg_constraint.c
***
*** 25,30
--- 25,31
#include utils/array.h
#include utils/builtins.h
#include utils/fmgroids.h
+ #include utils/hooks.h
#include utils/lsyscache.h
#include utils/rel.h
#include utils/syscache.h
***
*** 360,365 CreateConstraintEntry(const char *constraintName,
--- 361,370
DEPENDENCY_NORMAL);
}
+ /* Post creation of a new constraint */
+ InvokeObjectAccessHook0(OAT_POST_CREATE,
+ ConstraintRelationId, conOid, 0);
+
return conOid;
}
*** a/src/backend/catalog/pg_conversion.c
--- b/src/backend/catalog/pg_conversion.c
***
*** 27,32
--- 27,33
#include utils/acl.h
#include utils/builtins.h
#include utils/fmgroids.h
+ #include utils/hooks.h
#include utils/rel.h
#include utils/syscache.h
Re: [HACKERS] multi-platform, multi-locale regression tests
Markus Wanner wrote: Note that dtester features a TAP reporter. However, the way Kevin uses dtester, that probably won't give useful results. (As he uses custom print statements to do more detailed reporting than TAP could ever give you). According to the TAP draft standard, any line not beginning with 'ok', 'not ok', or '#' is a comment and must be ignored by a TAP consumer. They are considered comments, and the assumption is that there can be many of them. http://testanything.org/wiki/index.php/TAP_at_IETF:_Draft_Standard Since my more detailed output would all be considered ignorable comments, I think it's OK. It's there for human readers who want more detail, but otherwise must have no impact on a compliant consumer. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] We need index-only scans
On Wed, Nov 10, 2010 at 4:04 AM, Bruce Momjian br...@momjian.us wrote: We last researched index-only scans, also called covering indexes, in September of 2008, but have made little progress on it since. Many have been waiting for Heikki to implement this but I talked to him and he doesn't have time. I believe it is time for the community to move forward and I would like to assemble a team to work on this feature. We might not be able to implement it for Postgres 9.1, but hopefully we can make some progress on this. Just so everyone is on the same page Even once we have index-only scans they won't be anywhere near as useful with Postgres as they are with Oracle and other databases. At least not unless we find a solution for a different problem -- our inability to scan btree indexes sequentially. In Oracle Fast Full Index scans are particularly useful for things like unconstrained select count(*). Since the scan can scan through the index sequentially and the index is much smaller than the table it can count all the values fairly quickly even on a very wide table. In Postgres, aside from the visibility issues we have a separate problem. In order to achieve high concurrency we allow splits to occur without locking the index. And the new pages can be found anywhere in the index, even to the left of the existing page. So a sequential scan could miss some data if the page it's on is split and some of the data is moved to be to the left of where our scan is. It's possible this is a non-issue in the future due to large RAM sizes and SSDs. Large amounts of RAM mean perhaps indexes will be in memory much of the time and SSDs might mean that scanning the btree in index order might not really be that bad. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] multi-platform, multi-locale regression tests
On 11/12/2010 02:27 PM, Kevin Grittner wrote: According to the TAP draft standard, any line not beginning with 'ok', 'not ok', or '#' is a comment and must be ignored by a TAP consumer. They are considered comments, and the assumption is that there can be many of them. I stand corrected. Do you actually use the TapReporter? Maybe I confused with the CursesReporter, which gets rather confused by custom output. Regards Markus Wanner -- 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] multi-platform, multi-locale regression tests
Markus Wanner wrote: I stand corrected. Do you actually use the TapReporter? No. I know so little about TAP that I wasn't aware that dtester output was in the TAP format until I saw your post on this thread, so I went searching for the format to see what I might do to become more compliant -- and found that through sheer luck I happened to be compliant with the proposed spec. :-) Maybe I confused with the CursesReporter, which gets rather confused by custom output. I can check what that requires. Perhaps I can cause the detail output to not confuse that. [off to check...] -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] We need index-only scans
Excerpts from Greg Stark's message of vie nov 12 10:33:28 -0300 2010: In Postgres, aside from the visibility issues we have a separate problem. In order to achieve high concurrency we allow splits to occur without locking the index. And the new pages can be found anywhere in the index, even to the left of the existing page. So a sequential scan could miss some data if the page it's on is split and some of the data is moved to be to the left of where our scan is. Eh? If a transaction splits a page and put some new tuples to the left of another transaction's scan (assuming a forward scan), then necessarily the second transaction cannot see those tuples anyway -- the inserter must have done the split after you got your snapshot. A transaction cannot split a page that other transaction has a scan stopped in, because there are buffer locks involved. Therefore the scan cannot miss any tuples. Saith src/backend/access/nbtree/README: Lehman and Yao don't require read locks, but assume that in-memory copies of tree pages are unshared. Postgres shares in-memory buffers among backends. As a result, we do page-level read locking on btree pages in order to guarantee that no record is modified while we are examining it. This reduces concurrency but guaranteees correct behavior. An advantage is that when trading in a read lock for a write lock, we need not re-read the page after getting the write lock. Since we're also holding a pin on the shared buffer containing the page, we know that buffer still contains the page and is up-to-date. We support the notion of an ordered scan of an index as well as insertions, deletions, and simple lookups. A scan in the forward direction is no problem, we just use the right-sibling pointers that LY require anyway. (Thus, once we have descended the tree to the correct start point for the scan, the scan looks only at leaf pages and never at higher tree levels.) To support scans in the backward direction, we also store a left sibling link much like the right sibling. (This adds an extra step to the LY split algorithm: while holding the write lock on the page being split, we also lock its former right sibling to update that page's left-link. This is safe since no writer of that page can be interested in acquiring a write lock on our page.) A backwards scan has one additional bit of complexity: after following the left-link we must account for the possibility that the left sibling page got split before we could read it. So, we have to move right until we find a page whose right-link matches the page we came from. (Actually, it's even harder than that; see deletion discussion below.) Page read locks are held only for as long as a scan is examining a page. To minimize lock/unlock traffic, an index scan always searches a leaf page to identify all the matching items at once, copying their heap tuple IDs into backend-local storage. The heap tuple IDs are then processed while not holding any page lock within the index. We do continue to hold a pin on the leaf page, to protect against concurrent deletions (see below). In this state the scan is effectively stopped between pages, either before or after the page it has pinned. This is safe in the presence of concurrent insertions and even page splits, because items are never moved across pre-existing page boundaries --- so the scan cannot miss any items it should have seen, nor accidentally return the same item twice. The scan must remember the page's right-link at the time it was scanned, since that is the page to move right to; if we move right to the current right-link then we'd re-scan any items moved by a page split. We don't similarly remember the left-link, since it's best to use the most up-to-date left-link when trying to move left (see detailed move-left algorithm below). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] We need index-only scans
On 12.11.2010 15:51, Alvaro Herrera wrote: Excerpts from Greg Stark's message of vie nov 12 10:33:28 -0300 2010: In Postgres, aside from the visibility issues we have a separate problem. In order to achieve high concurrency we allow splits to occur without locking the index. And the new pages can be found anywhere in the index, even to the left of the existing page. So a sequential scan could miss some data if the page it's on is split and some of the data is moved to be to the left of where our scan is. Eh? It took me a while to understand what Greg meant as well. You can't scan a B-tree index in *physical order*, You have to first descend to the leftmost leaf, and follow the right pointers from there until you reach the rightmost leaf. That is a lot slower than seqscanning a file in physical order. We solved that for VACUUM, taking advantage of the fact that there can only be one VACUUM on a table at a time. Maybe that mechanism could be generalized to all scans, but it would require some thinking.. -- 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] duplicate connection failure messages
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have developed the attached patch to report whether IPv4 or IPv6 are being used. What's the use of that exactly? It doesn't really respond to Peter's concern, I think. Peter liked: And I agree it's not very friendly in this specific case - I wonder if we should log it as localhost (127.0.0.1) and localhost (::1) (and similar for any other case that returns more than one address). What this will show is: localhost (IPv4) localhost (IPv6) Is that good? I can't figure out how to do ::1 because when you supply a host _name_, there is no reverse mapping done. Looking at the code, we test for a host name, then a host ip, and don't assume they are both set. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Simplifying replication
Robert Haas wrote: On Thu, Nov 11, 2010 at 10:13 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Thu, Oct 28, 2010 at 1:13 AM, Josh Berkus j...@agliodbs.com wrote: I sort of agree with you that the current checkpoint_segments parameter is a bit hard to tune, at least if your goal is to control the amount of disk space that will be used by WAL files. ?But I'm not sure your proposal is better. ?Instead of having a complicated formula for predicting how much disk space would get used by a given value for checkpoint_segments, we'd have a complicated formula for the amount of WAL that would force a checkpoint based on max_wal_size. Yes, but the complicated formula would then be *in our code* instead of being inflicted on the user, as it now is. I don't think so - I think it will just be inflicted on the user in a different way. ?We'd still have to document what the formula is, because people will want to understand how often a checkpoint is going to get forced. So here's an example of how this could happen. ?Someone sets max_wal_size = 480MB. ?Then, they hear about the checkpoint_completion_target parameter, and say, ooh, goody, let me boost that. ?So they raise it from 0.5 to 0.9. ?Now, all of a sudden, they're getting more frequent checkpoints. ?Performance may get worse Uh, checkpoint_completion_target only controls flushing of buffers between checkpoints, not the frequency of checkpoints. According to the formula in our fine documentation, if you increase checkpoint_completion_target, the maximum number of WAL files also increases. This makes sense: the files from the last checkpoint can't be removed until further along into the next cycle. Therefore, if you wanted to increase the checkpoint_completion_target while keeping the maximum amount of WAL on disk the same, you'd need to trigger checkpoints more frequently. Do we recycle WAL files between checkpoints or just at checkpoint time? I thought it was only at checkpoint time. Also, there was talk that a larger WAL directory would slow recovery, but I thought it was only the time since the last checkpoint that controlled that. [ Again, sorry for my late reading of this and other threads. ] -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] multi-platform, multi-locale regression tests
On 11/12/2010 02:43 PM, Kevin Grittner wrote: Markus Wanner wrote: I stand corrected. Do you actually use the TapReporter? No. I know so little about TAP that I wasn't aware that dtester output was in the TAP format Well, there are three kinds of reporters: StreamReporter, TapReporter and CursesReporter. By default, either curser or stream is chosen, depending on whether or not dtester thinks its stdout is a terminal or not. To make dtester report in TAP format, you'd need to specify that upon creation of the Runner: runner = dtester.runner.Runner( \ reporter=dtester.reporter.StreamReporter( \ sys.stdout, sys.stderr, showTimingInfo=False)) I can check what that requires. Perhaps I can cause the detail output to not confuse that. [off to check...] The CursesReporter moves up and down the lines to write results to concurrently running tests. It's only useful on a terminal and certainly gets confused by anything that moves the cursor (which a plain 'print' certainly does). The best solution would probably be to allow the reporters to write out comment lines. (However, due to the ability of running tests concurrently, these comment lines could only be appended at the end, without clear visual connection to a specific test. As long as you are only running on test at a time, that certainly doesn't matter). Regards Markus Wanner -- 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: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Magnus Hagander wrote: On Fri, Nov 12, 2010 at 03:49, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: On Fri, Sep 17, 2010 at 05:51, Ashesh Vashi ashesh.va...@enterprisedb.com wrote: Hi Mark, On of my college (Sujeet) has found a way to reproduce the same behaviour. 1. Installed PG 9.0 on Win XP SP3 2. Stop the Postgresql-9.0 service from service manager console 3. Create pgpass.conf in postgres (service account) user's profile with an incorrect password deliberately. (Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html) 4. Now start the postgresql-9.0 service, it will return an error and the status ?? shows stopped 5. However i could connect to the psql shell and get the prompt which means ??? the server is running. I took a quick look at the code, and from what I can tell this is because PQconnectionNeedsPassword() always returns false if a pgpass.conf has been used. There is no handling the case where pgpass is used, but has an incorrect password. Does anybody recall the specific reason for this? Do we need a way for pg_ctl to figure this out, or do we need to change it in PQconnecitonNeedsPassword()? I was not able to reproduce this failure on my BSD system using GIT head: ? ? ? ?$ psql test ? ? ? ?psql: FATAL: ?password authentication failed for user postgres ? ? ? ?password retrieved from file /u/postgres/.pgpass ? ? ? ?$ pg_ctl status ? ? ? ?pg_ctl: server is running (PID: 710) ? ? ? ?/usr/var/local/pgsql/bin/postgres -i The problem is not in pg_ctl status, it's in pg_ctl start. They're different codepaths - status never tries to actually connect, it just checks if the process is alive. Uh, I still cannot reproduce the failure: $ psql postgres psql: FATAL: password authentication failed for user postgres password retrieved from file /u/postgres/.pgpass $ pg_ctl stop waiting for server to shut down done server stopped $ pg_ctl -l /dev/null start server starting (Got to love that new 9.0 pgpass error message.) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] We need index-only scans
Greg Stark wrote: On Wed, Nov 10, 2010 at 4:04 AM, Bruce Momjian br...@momjian.us wrote: We last researched index-only scans, also called covering indexes, in September of 2008, but have made little progress on it since. ?Many have been waiting for Heikki to implement this but I talked to him and he doesn't have time. I believe it is time for the community to move forward and I would like to assemble a team to work on this feature. ?We might not be able to implement it for Postgres 9.1, but hopefully we can make some progress on this. Just so everyone is on the same page Even once we have index-only scans they won't be anywhere near as useful with Postgres as they are with Oracle and other databases. At least not unless we find a solution for a different problem -- our inability to scan btree indexes sequentially. In Oracle Fast Full Index scans are particularly useful for things like unconstrained select count(*). Since the scan can scan through the index sequentially and the index is much smaller than the table it can count all the values fairly quickly even on a very wide table. In Postgres, aside from the visibility issues we have a separate problem. In order to achieve high concurrency we allow splits to occur without locking the index. And the new pages can be found anywhere in the index, even to the left of the existing page. So a sequential scan could miss some data if the page it's on is split and some of the data is moved to be to the left of where our scan is. It's possible this is a non-issue in the future due to large RAM sizes and SSDs. Large amounts of RAM mean perhaps indexes will be in memory much of the time and SSDs might mean that scanning the btree in index order might not really be that bad. Agreed. I updated the index-only scans wiki for this: http://wiki.postgresql.org/wiki/Index-only_scans test speed improvement for scans of the entire index (this involves random I/O) * we can't scan the index in physical order like vacuum does -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] multi-platform, multi-locale regression tests
Markus Wanner wrote: Well, there are three kinds of reporters: StreamReporter, TapReporter and CursesReporter. By default, either curser or stream is chosen, depending on whether or not dtester thinks its stdout is a terminal or not. The CursesReporter moves up and down the lines to write results to concurrently running tests. It's only useful on a terminal and certainly gets confused by anything that moves the cursor (which a plain 'print' certainly does). Ah, well that explains some problems I've had with getting my output to behave quite like I wanted! Thanks for that summary! I'm pretty sure I've been getting the CursesReporter; I'll switch to TapReporter. The best solution would probably be to allow the reporters to write out comment lines. (However, due to the ability of running tests concurrently, these comment lines could only be appended at the end, without clear visual connection to a specific test. As long as you are only running on test at a time, that certainly doesn't matter). Not sure what the best answer is for Curses -- would it make any sense to output a disk file with one of the other formats in addition to the screen, and direct detail to the file? Perhaps a separate file for each test, to make it easy to keep comments associated with the test? (Just brainstorming here.) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] We need index-only scans
On 11/12/2010 09:17 AM, Bruce Momjian wrote: Greg Stark wrote: On Wed, Nov 10, 2010 at 4:04 AM, Bruce Momjianbr...@momjian.us wrote: We last researched index-only scans, also called covering indexes, in September of 2008, but have made little progress on it since. ?Many have been waiting for Heikki to implement this but I talked to him and he doesn't have time. I believe it is time for the community to move forward and I would like to assemble a team to work on this feature. ?We might not be able to implement it for Postgres 9.1, but hopefully we can make some progress on this. Just so everyone is on the same page Even once we have index-only scans they won't be anywhere near as useful with Postgres as they are with Oracle and other databases. At least not unless we find a solution for a different problem -- our inability to scan btree indexes sequentially. In Oracle Fast Full Index scans are particularly useful for things like unconstrained select count(*). Since the scan can scan through the index sequentially and the index is much smaller than the table it can count all the values fairly quickly even on a very wide table. In Postgres, aside from the visibility issues we have a separate problem. In order to achieve high concurrency we allow splits to occur without locking the index. And the new pages can be found anywhere in the index, even to the left of the existing page. So a sequential scan could miss some data if the page it's on is split and some of the data is moved to be to the left of where our scan is. It's possible this is a non-issue in the future due to large RAM sizes and SSDs. Large amounts of RAM mean perhaps indexes will be in memory much of the time and SSDs might mean that scanning the btree in index order might not really be that bad. Agreed. I updated the index-only scans wiki for this: http://wiki.postgresql.org/wiki/Index-only_scans test speed improvement for scans of the entire index (this involves random I/O) * we can't scan the index in physical order like vacuum does For unconstrained select count(*), why does scanning in index order matter? 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] MULTISET and additional functions for ARRAY
Robert Haas robertmh...@gmail.com writes: On Fri, Nov 12, 2010 at 12:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: The problem is not with the type system: as long as you give multisets different type OIDs from arrays, everything will work fine. And thus you must create a THIRD copy of every entry in pg_type. That doesn't qualify as a problem? [ shrug... ] It's less of a problem than the possible alternatives. IMO anyway. OIDs are cheap ... replacing OIDs with some sort of ill-specified composite key throughout the system is not. But I'm still not convinced that this feature is useful enough to justify the implementation effort. AFAICS there's nothing here that you couldn't get with some non-default operators on regular arrays, with orders of magnitude less work and less impact on the rest of the system. The only reason to consider implementing it as a separate type category is the SQL committee decided to invent some syntax --- and given their lousy taste in syntax, I get less enthused every year about duplicating it. 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] We need index-only scans
Excerpts from Heikki Linnakangas's message of vie nov 12 11:01:39 -0300 2010: It took me a while to understand what Greg meant as well. You can't scan a B-tree index in *physical order*, You have to first descend to the leftmost leaf, and follow the right pointers from there until you reach the rightmost leaf. That is a lot slower than seqscanning a file in physical order. Oh, that makes more sense. I'm not sure that can be supported sanely (i.e. not locking the whole index) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Bruce Momjian br...@momjian.us writes: Uh, I still cannot reproduce the failure: I would imagine you need -w option on the start. The whole issue here is whether start's wait-for-server-start code works. 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] wCTE behaviour
Yeb Havinga yebhavi...@gmail.com writes: On 2010-11-11 17:50, Marko Tiikkaja wrote: Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? Since t is not referenced in the query, foo should not be deleted at all, Yeah, that's another interesting question: should we somehow force unreferenced CTEs to be evaluated anyhow? Now that I think about it, there was also some concern about the possibility of the outer query not reading the CTE all the way to the end, ie WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all. From an implementation standpoint that makes it more attractive to do the DELETE first and stick its results in a tuplestore --- but I still think we should view that as an implementation detail, not as part of the specification. 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] We need index-only scans
On Fri, Nov 12, 2010 at 8:33 AM, Greg Stark gsst...@mit.edu wrote: On Wed, Nov 10, 2010 at 4:04 AM, Bruce Momjian br...@momjian.us wrote: We last researched index-only scans, also called covering indexes, in September of 2008, but have made little progress on it since. Many have been waiting for Heikki to implement this but I talked to him and he doesn't have time. I believe it is time for the community to move forward and I would like to assemble a team to work on this feature. We might not be able to implement it for Postgres 9.1, but hopefully we can make some progress on this. Just so everyone is on the same page Even once we have index-only scans they won't be anywhere near as useful with Postgres as they are with Oracle and other databases. At least not unless we find a solution for a different problem -- our inability to scan btree indexes sequentially. I have very little doubt that our first attempts to chip away at this problem are going to be a bit rough around the edges. Here's another problem to mull over: a large insert-only table will never be vacuumed; therefore, the visibility map bits will never become set; therefore, the index-only scan optimization won't apply (and the user may not realize it or understand why it's happening). But the journey of a thousand miles begins with the first step. I think we need to focus our first effort on making the visibility map crash-safe. Then we can implement the basic feature, which I would characterize this way: if performing an index-scan, and all the attributes we need are available from the index tuple, then skip the heap fetch when the visibility map bit is set. This requires minimal planner support - just an adjustment of the costing model for index scans; although to do it right I think we're going to need statistics on what fraction of pages in the heap have the visibility map bit set. Then, we can work on refinements, of which I think there will be many, including the one you listed. Another is to bubble up heap fetches in the plan tree - so for example if you eventually need to return some attributes that aren't in the index tuple, you could consider performing some other join based on the index columns and then do the heap fetches for the remaining attributes (and visibility checks) later. I am not confident that we can get even a basic implementation of index-only scans into 9.1 at this point, and we're certainly not going to get all the kinks worked out. So I agree with you that we shouldn't set expectations above the level at which they can be met, but, I'd be happy if we can make a start on it. ...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: [HACKERS] TODO Alter Table Rename Constraint
On Fri, Nov 12, 2010 at 4:28 AM, Viktor Valy vili0...@gmail.com wrote: OK, I see. Thanks for mentioning it. Are there other problems with the suggestion? Or should the work like that? I think you'll just need to give it a try and see how it goes. I think we've covered most of the possible sticking points that I know about, but of course there could be some I don't know about. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Fri, Nov 12, 2010 at 10:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Yeb Havinga yebhavi...@gmail.com writes: On 2010-11-11 17:50, Marko Tiikkaja wrote: Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? Since t is not referenced in the query, foo should not be deleted at all, Yeah, that's another interesting question: should we somehow force unreferenced CTEs to be evaluated anyhow? Now that I think about it, there was also some concern about the possibility of the outer query not reading the CTE all the way to the end, ie WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all. From an implementation standpoint that makes it more attractive to do the DELETE first and stick its results in a tuplestore --- but I still think we should view that as an implementation detail, not as part of the specification. Yeah, I think we have to force any DML statements in CTEs to run to completion, whether we need the results or not, and even if they are unreferenced. Otherwise it's going to be really confusing, I fear. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Fri, Nov 12, 2010 at 10:25:51AM -0500, Tom Lane wrote: Yeb Havinga yebhavi...@gmail.com writes: On 2010-11-11 17:50, Marko Tiikkaja wrote: Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? Since t is not referenced in the query, foo should not be deleted at all, Yeah, that's another interesting question: should we somehow force unreferenced CTEs to be evaluated anyhow? Yes. Now that I think about it, there was also some concern about the possibility of the outer query not reading the CTE all the way to the end, ie WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all. Yes. From an implementation standpoint that makes it more attractive to do the DELETE first and stick its results in a tuplestore --- but I still think we should view that as an implementation detail, not as part of the specification. Right :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Fri, Nov 12, 2010 at 10:50:52AM -0500, Robert Haas wrote: On Fri, Nov 12, 2010 at 10:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Yeb Havinga yebhavi...@gmail.com writes: On 2010-11-11 17:50, Marko Tiikkaja wrote: Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? Since t is not referenced in the query, foo should not be deleted at all, Yeah, that's another interesting question: should we somehow force unreferenced CTEs to be evaluated anyhow? Now that I think about it, there was also some concern about the possibility of the outer query not reading the CTE all the way to the end, ie WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all. From an implementation standpoint that makes it more attractive to do the DELETE first and stick its results in a tuplestore --- but I still think we should view that as an implementation detail, not as part of the specification. Yeah, I think we have to force any DML statements in CTEs to run to completion, whether we need the results or not, and even if they are unreferenced. Otherwise it's going to be really confusing, I fear. Yes, and as we add more things--COPY is the first but probably not the last--to CTEs, this no action-at-a-distance behavior will become even more important. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ADMIN] locales and encodings Oh MY!
On Fri, Nov 12, 2010 at 12:45 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: Hi Mark, Il 12/11/10 03:31, mark ha scritto: I have listed what I think I will be doing with regards to initdb. if anyone sees problems with the following mixture during my dump - init- restore I would be most keen in hearing about it. FYI, PostgreSQL 8.4 introduced database level collation. Before that, yes, you need to re-init your data dir. Guess I missed that, I have 8.3.X boxes in production and 9.0.1 boxes in dev so I guess only someone of them will require a re-init. initdb /path/to/data/dir --lc_ctype=C --lc_collation=C --lc_message=en_US.UTF8 --lc_monetary=en_US.UTF8 --lc_numeric=en_US.UTF8 --lc_time=en_US.UTF8 -E UTF8 Maybe you meant --lc_collate ? Yes I did mean lc_collate - thanks Cheers, Gabriele With how similar straight C and en_US.UTF8 are it was suggested to me, by persons who are far more C knowledgeable then I in my office, that this is something the PG community could fix . A fix being so that col LIKE 'foo%' could use btree indexes in locales like en_US.UTF8 (and probably some others). @hackers - is the request unreasonable ? anyone got any idea of the price tag to make that happen ? -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- 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] SQL/MED estimated time of arrival?
Hi Gentlemen, Thank you for the time estimate and the interface discussion. It sounds like the PostgreSQL SQL/MED code will be very useful when it is done. Our product provides read-only access to files, so updates/inserts/deletes aren't an issue for us. One thing that is not clear to me is indexing support. Will it be possible to index a SQL/MED table as if it were a regular table? What would be the equivalent of Informix's row ids? Eric. ** Eric Davies, M.Sc. Senior Programmer Analyst Barrodale Computing Services Ltd. 1095 McKenzie Ave., Suite 418 Victoria BC V8P 2L5 Canada Tel: (250) 704-4428 Web: http://www.barrodale.com Email: e...@barrodale.com **
Re: [HACKERS] MULTISET and additional functions for ARRAY
On Fri, Nov 12, 2010 at 12:53:09AM -0500, Robert Haas wrote: On Fri, Nov 12, 2010 at 12:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Nov 11, 2010 at 10:02 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: If we reuse type IDs of arrays for multisets, the multisets would have some special typmod. For example, typmod = 0 means multiset, and positive value means array with max cardinality. Note that the SQL standard doesn't mention about multi-dimensional arrays. So, we can use typmod = -1 as a free-size and free-dimensional array for backward compatibility. I would really like to see us fix our type system so that it doesn't require this type of awful hack. But maybe that's asking too much of a patch to implement this feature. The problem is not with the type system: as long as you give multisets different type OIDs from arrays, everything will work fine. It will absolutely not work to try to use typmod to make the behavior vary like that ... but Itagaki-san knew that already. And thus you must create a THIRD copy of every entry in pg_type. That doesn't qualify as a problem? Yes, and I've started a separate thread on this along with a page on the wiki. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Refactoring the Type System
Folks, For the past couple of years, I've been hearing from the PostGIS people among others that our type system just isn't flexible enough for their needs. It's really starting to show its age, or possibly design compromises that seemed reasonable a decade or more ago, but are less so now. To that end, I've put up a page on the wiki that includes a list of issues to be addressed. It's intended to be changed, possibly completely. http://wiki.postgresql.org/wiki/Refactor_Type_System What might the next version of the type system look like? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Uh, I still cannot reproduce the failure:
I would imagine you need -w option on the start. The whole issue
here is whether start's wait-for-server-start code works.
Thanks, I am now able to reproduce this. I was able to get this to
report the .pgpass problem:
$ psql postgres
psql: FATAL: password authentication failed for user postgres
password retrieved from file /u/postgres/.pgpass
$ pg_ctl stop
waiting for server to shut down done
server stopped
$ pg_ctl -w -l /dev/null start
waiting for server to startFATAL: password authentication failed
for user postgres
password retrieved from file /u/postgres/.pgpass
.FATAL: password authentication failed for user postgres
password retrieved from file /u/postgres/.pgpass
.FATAL: password authentication failed for user postgres
password retrieved from file /u/postgres/.pgpass
.^C
I basically report the connection error string if it starts with FATAL:.
I originally tried to check for an ERRCODE_INVALID_PASSWORD error field
(see // comments), but it seems there is no way to access this, i.e.
PQgetResult(conn) on a connection failure is always NULL.
Anyway, perhaps FATAL is a better test because it will report any major
failure, not just a .pgpass one.
Patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
index 14d36b5..df71c16 100644
*** a/src/bin/pg_ctl/pg_ctl.c
--- b/src/bin/pg_ctl/pg_ctl.c
*** typedef enum
*** 70,75
--- 70,78
} CtlCommand;
#define DEFAULT_WAIT 60
+ //
+ ///* This is part of the protocol so just define it */
+ //#define ERRCODE_INVALID_PASSWORD 28P01
static bool do_wait = false;
static bool wait_set = false;
*** test_postmaster_connection(bool do_check
*** 511,516
--- 514,523
if ((conn = PQconnectdb(connstr)) != NULL
(PQstatus(conn) == CONNECTION_OK ||
PQconnectionNeedsPassword(conn)))
+ // /* only works with = 9.0 servers */
+ // (PQgetResult(conn)
+ // strcmp(PQresultErrorField(PQgetResult(conn), PG_DIAG_SQLSTATE),
+ // ERRCODE_INVALID_PASSWORD) == 0)))
{
PQfinish(conn);
success = true;
*** test_postmaster_connection(bool do_check
*** 518,523
--- 525,533
}
else
{
+ /* report fatal errors like invalid .pgpass passwords */
+ if (strncmp(PQerrorMessage(conn), FATAL:, strlen(FATAL:)) == 0)
+ fputs(PQerrorMessage(conn), stderr);
PQfinish(conn);
#if defined(WIN32)
--
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] wCTE behaviour
2010/11/13 Robert Haas robertmh...@gmail.com: On Fri, Nov 12, 2010 at 10:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Yeb Havinga yebhavi...@gmail.com writes: On 2010-11-11 17:50, Marko Tiikkaja wrote: Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? Since t is not referenced in the query, foo should not be deleted at all, Yeah, that's another interesting question: should we somehow force unreferenced CTEs to be evaluated anyhow? Now that I think about it, there was also some concern about the possibility of the outer query not reading the CTE all the way to the end, ie WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all. From an implementation standpoint that makes it more attractive to do the DELETE first and stick its results in a tuplestore --- but I still think we should view that as an implementation detail, not as part of the specification. Yeah, I think we have to force any DML statements in CTEs to run to completion, whether we need the results or not, and even if they are unreferenced. Otherwise it's going to be really confusing, I fear. One thing that has annoyed me while designing this feature is if as Tom suggests the all queries are executed in the same snapshot and optimized as the current read-only CTE does we are tempted to support recursive and forward-reference in even DML CTE. It explodes out my head and I'd like not to think about it if we can. On the other hand, different-snapshot, serialized execution model occurs the problem I originally rose in the previous thread, in which the space to store the data shared among different plans is missing. It's of course doable, but the easier implementation the better. I'm inclined to agree with the same snapshot model, that is not only easier to implement but also fits the current SQL processing design and the existing CTE specification. Not only from the developer's view but consistency from user's view. Whatever the standard says on the DML *subquery*, we're going to create our new *CTE* feature. Yes, this is CTE. For recursive and forward-reference issue, we can just forbid them in DML CTE at first. Regards, -- Hitoshi Harada -- 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] Refactoring the Type System
On 11/12/2010 11:34 AM, David Fetter wrote: Folks, For the past couple of years, I've been hearing from the PostGIS people among others that our type system just isn't flexible enough for their needs. It's really starting to show its age, or possibly design compromises that seemed reasonable a decade or more ago, but are less so now. This is so general as to be quite meaningless to me. What is it that is wanted that we don't have. (And don't say flexibility, that's way too general - say something much more concrete and specific. If you want flexibility we can store everything as text, but I doubt you'll like the result.) 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] knngist - 0.8
Robert, it is great you are taking this on. This is really a well-known
area of the code for you, but not so much for Teodor and Oleg, so I am
sure they appreciate your assistance.
---
Robert Haas wrote:
On Sat, Oct 16, 2010 at 9:54 PM, Robert Haas robertmh...@gmail.com wrote:
Thinking about it that way, perhaps we could add an integer column
amop_whats_it_good_for that gets used as a bit field. ?That wouldn't
require changing the index structure, although it might break some
other things.
I gave this a shot (though I called it amoppurpose rather than
amop_whats_it_good_for) and I think it's a reasonable way to proceed.
Proof-of-concept patch attached. ?This just adds the column (using the
existing padding space), defines AMOP_SEARCH and AMOP_ORDER, and makes
just about everything ignore anything not marked AMOP_SEARCH,
attached. ?This would obviously need some more hacking to pay
attention to AMOP_ORDER where relevant, etc. and to create some actual
syntax around it. ?Currently CREATE OPERATOR CLASS / ALTER OPERATOR
FAMILY have this bit:
OPERATOR strategy_number ( op_type [ , op_type ] )
knngist-0.9 implements this:
[ORDER] OPERATOR strategy_number ( op_type [, op_type ] )
...but with the design proposed above that's not quite what we'd want,
because amoppurpose is a bit field, so you could have one or both of
the two possible purposes. ?Perhaps:
OPERATOR strategy_number ( op_type [ , op_type ] ) [ FOR { SEARCH |
ORDER } [, ...] ]
With the default being FOR SEARCH.
Slightly-more-fleshed out proof of concept patch attached, with actual
syntax, documentation, and pg_dump support added. This might be
thought of as a subset of the builtin_knngist_core patch, without the
parts that make it actually do something useful (which is mostly
match_pathkey_to_index - which I'm still rather hoping to abstract in
some way via the access method interface, though I'm currently unsure
what the best way to do that is).
I notice that builtin_knngist_core checks whether the return type of
an ordering operator has a built-in btree opclass. I'm not sure
whether we should bother checking that, because even if it's true I
don't think there's anything preventing it from becoming false later.
I think it's probably sufficient to just check this condition at plan
time and silently skip trying to build knn-type index paths if it's
not met.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
[ Attachment, skipping... ]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] [ADMIN] locales and encodings Oh MY!
mark wrote: A fix being so that col LIKE 'foo%' could use btree indexes in locales like en_US.UTF8 (and probably some others). How about specifying an opclass?: http://www.postgresql.org/docs/current/interactive/indexes-opclass.html -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ask for review of MERGE
Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: rhaas=# create table concurrent (x integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index concurrent_pkey for table concurrent CREATE TABLE rhaas=# insert into x values (1); rhaas=# begin; BEGIN rhaas=# insert into concurrent values (2); INSERT 0 1 switch to a different window rhaas=# update concurrent set x=x where x=2; UPDATE 0 That surprised me. I would have thought that the INSERT would have created an in doubt tuple which would block the UPDATE. What is the reason for not doing so? When Kevin gets surprised, I get worried. LOL -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] knngist questions
1. Is KNNGIST intended to work if there's more than one pathkey? If so, how? Example: SELECT * FROM tab ORDER BY this_point- '(0,0)', this_point- '(1,1)' Why not, if distances from two points to '(0,0)' are equal, it's need to compare distances to '(1,1)'. Nothing new here, KNN-GiST supports it, that a reason why StackElem struct has variable-size array for distances. In practice, it could be used for finding closest restaurant with some fish in its name (pg_trgm has a support of disttance): SELECT * FROM restaurants ORDER BY MY_COORDS - r_coords, 'fish' - r_name; 2. I'm concerned by the fact that the new consistent() methods only return 0 or -1.0 for non-leaf pages. If we have a query like: M-m, it's returns 0 or -1.0 only for operation from WHERE clause, for ORDER BY operation it should return = 0 value. That's why consistentFn shoulf be able to distinguish source of operation (actually, it's need because now we allow boolean distances, if distance could not be a boolean then operation could not come from WHERE clause) SELECT * FROM tab WHERE this_point- '(0,0)' For point, it's incorrect query: non-boolean operations could not present in WHERE clause. you'd need to have the consistent function for each page return a minimum and maximum distance to '(0,0)'. If you have one page that Only minimum distance which guarantees that in it's sub-tree there is no more close point. For R-tree it's rather obvious because keys on inner pages are actually a bounding box of its subtree. has a minimum distance of 0 and a maximum distance of 100 and another page which has a minimum distance of 101 and a maximum distance of 200, you don't even need to look at the second page until all the keys from the first one have been processed. If there's a third page with Right, it works so, until I made a big mistake in code. But performance test allows me to believe that I didn't :) It's read a page (for the start - root page) and puts all keys in binary tree ordered by distance and then takes left-most key from binary tree. If that key is pointer to heap, GiST returns it to postgres, if it's a pointer to index's page then repeat loop with new page. tree up front. If you are already doing something like this somewhere in the code, please point me in the right direction... getNextNearest() function: it gets next pointer from tree with a help of getNextDataPointer(), and then, depending of pointer type (to index or heap page) returns a pointer or call processPage() which puts new pointers into tree. May be, not obvious thing that all pointers on the same distance are stored in single binary tree node (struct StackElem). Each pointer is represented by DataPointer which contains information needed for support concurrency. List of that structs is keeped semi-ordered: pointer to heap are always in the beginning of list to increase response time and memory requirement. 3. I've been scratching my head over the following bit of code and it doesn't make any sense to me. As far as I can tell, this is effectively comparing the number of columns in the ORDER BY clause to the number of restriction clauses applicable to the relation being scanned. Those two quantities don't seem to have much to do with each other, so either I'm confused or the code is. It doesn't seem like it Oops, it seems to me that's artefact of developing, sorry. I'm not very familiar with planner/optimizer so that required a lot of my brain and I just missed that after another attempt to get it work. should matter anyway, since I don't think we're planning on any AMs being both amoptionalkey and amcanorderbyop. Agree. -- 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] GIN vs. Partial Indexes
Josh Berkus wrote: On 10/08/2010 02:44 PM, Robert Haas wrote: In any case, I would expect that GIN could actually do this quite efficiently. What we'd probably want is a concept of a null word, with empty indexable rows entered in the index as if they contained the null word. So there'd be just one index entry with a posting list of however many such rows there are. So, given the lack of objections to this idea, do we have a plan for fixing GIN? Is this a TODO? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] GIN vs. Partial Indexes
On 11/12/2010 01:11 PM, Bruce Momjian wrote: Josh Berkus wrote: On 10/08/2010 02:44 PM, Robert Haas wrote: In any case, I would expect that GIN could actually do this quite efficiently. What we'd probably want is a concept of a null word, with empty indexable rows entered in the index as if they contained the null word. So there'd be just one index entry with a posting list of however many such rows there are. So, given the lack of objections to this idea, do we have a plan for fixing GIN? Is this a TODO? Yes. 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] GIN vs. Partial Indexes
Andrew Dunstan wrote: On 11/12/2010 01:11 PM, Bruce Momjian wrote: Josh Berkus wrote: On 10/08/2010 02:44 PM, Robert Haas wrote: In any case, I would expect that GIN could actually do this quite efficiently. What we'd probably want is a concept of a null word, with empty indexable rows entered in the index as if they contained the null word. So there'd be just one index entry with a posting list of however many such rows there are. So, given the lack of objections to this idea, do we have a plan for fixing GIN? Is this a TODO? Yes. OK, can you add it or give me wording, or it is already on the TODO list? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] knngist - 0.8
Slightly-more-fleshed out proof of concept patch attached, with actual syntax, documentation, and pg_dump support added. This might be thought of as a subset of the builtin_knngist_core patch, without the parts that make it actually do something useful (which is mostly match_pathkey_to_index - which I'm still rather hoping to abstract in some way via the access method interface, though I'm currently unsure what the best way to do that is). I don't see in your patch how to propagate knowledge of kind of operation (AMOP_SEARCH or AMOP_ORDER) to GiST and consistent method. For both of them they aren't distinguishable. That's not acceptably for both, because GiST needs to choose right traversal algorithm, consistentFn needs role to decide return infinity or false (-1) value. My variants informs GiST by SK_ORDER flags and consistentFn looks at strategy number (strategy numbers are different for different purposes). I notice that builtin_knngist_core checks whether the return type of an ordering operator has a built-in btree opclass. I'm not sure Actually, GiST doesn't use that knowledge, check is done only to be sure that operation returns orderable data type. whether we should bother checking that, because even if it's true I don't think there's anything preventing it from becoming false later. I think it's probably sufficient to just check this condition at plan time and silently skip trying to build knn-type index paths if it's not met. It's already do it: you can not ORDER BY over non-orderable data type. That check just make diagnostic earlier. -- 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] wCTE behaviour
Hi all, It appears that we have a consensus on the behaviour. I'm going to take some time off this weekend to get a patch with this behaviour to the next commitfest. Regards, Marko Tiikkaja -- 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] GIN vs. Partial Indexes
Bruce Momjian br...@momjian.us writes: OK, can you add it or give me wording, or it is already on the TODO list? It's already there. 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] wCTE behaviour
On Sat, Nov 13, 2010 at 01:50:46AM +0900, Hitoshi Harada wrote: 2010/11/13 Robert Haas robertmh...@gmail.com: On Fri, Nov 12, 2010 at 10:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Yeb Havinga yebhavi...@gmail.com writes: On 2010-11-11 17:50, Marko Tiikkaja wrote: Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? Since t is not referenced in the query, foo should not be deleted at all, Yeah, that's another interesting question: should we somehow force unreferenced CTEs to be evaluated anyhow? Now that I think about it, there was also some concern about the possibility of the outer query not reading the CTE all the way to the end, ie WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all. From an implementation standpoint that makes it more attractive to do the DELETE first and stick its results in a tuplestore --- but I still think we should view that as an implementation detail, not as part of the specification. Yeah, I think we have to force any DML statements in CTEs to run to completion, whether we need the results or not, and even if they are unreferenced. Otherwise it's going to be really confusing, I fear. One thing that has annoyed me while designing this feature is if as Tom suggests the all queries are executed in the same snapshot and optimized as the current read-only CTE does we are tempted to support recursive and forward-reference in even DML CTE. It explodes out my head and I'd like not to think about it if we can. Does this have about the same head-explodiness as the mutually recursive CTEs described in the SQL standard? More? Less? On the other hand, different-snapshot, serialized execution model occurs the problem I originally rose in the previous thread, in which the space to store the data shared among different plans is missing. It's of course doable, but the easier implementation the better. I'm inclined to agree with the same snapshot model, that is not only easier to implement but also fits the current SQL processing design and the existing CTE specification. Not only from the developer's view but consistency from user's view. Whatever the standard says on the DML *subquery*, we're going to create our new *CTE* feature. Yes, this is CTE. For recursive and forward-reference issue, we can just forbid them in DML CTE at first. Sounds good :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: extensible enums
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 10/24/2010 08:12 PM, Tom Lane wrote: This shows that the bitmapset optimization really is quite effective, at least for cases where all the enum labels are sorted by OID after all. That motivated me to change the bitmapset setup code to what's attached. This is potentially a little slower at initializing the cache, but it makes up for that by still marking most enum members as sorted even when a few out-of-order members have been inserted. That's nice. It's a tradeoff though. Bumping up the cost of setting up the cache won't have much effect on a creating a large index, but could affect to performance of retail comparisons significantly. But this is probably worth it. You'd have to work hard to create the perverse case that could result in seriously worse cache setup cost. Well, notice that I moved the caching into typcache.c, rather than having it be associated with query startup. So unless you're actively frobbing the enum definition, that's going to be paid only once per session. Thanks for modifying pg_upgrade so it works with this new format. The changes look good and cleaner than what I had to do for 9.0. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] WIP: extensible enums
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 10/24/2010 08:12 PM, Tom Lane wrote: This shows that the bitmapset optimization really is quite effective, at least for cases where all the enum labels are sorted by OID after all. That motivated me to change the bitmapset setup code to what's attached. This is potentially a little slower at initializing the cache, but it makes up for that by still marking most enum members as sorted even when a few out-of-order members have been inserted. That's nice. It's a tradeoff though. Bumping up the cost of setting up the cache won't have much effect on a creating a large index, but could affect to performance of retail comparisons significantly. But this is probably worth it. You'd have to work hard to create the perverse case that could result in seriously worse cache setup cost. Well, notice that I moved the caching into typcache.c, rather than having it be associated with query startup. So unless you're actively frobbing the enum definition, that's going to be paid only once per session. FYI, I marked the TODO item for adding enums as completed. The TODO item used to also mention renaming or removing enums, but I have seen few requests for that so I removed that suggestion. We can always re-add it if there is demand. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] WIP: extensible enums
On 11/12/2010 01:40 PM, Bruce Momjian wrote: FYI, I marked the TODO item for adding enums as completed. The TODO item used to also mention renaming or removing enums, but I have seen few requests for that so I removed that suggestion. We can always re-add it if there is demand. Renaming an item would not be terribly hard. Removing one is that nasty case. There are all sorts of places the old value could be referred to: table data, view definitions, check constraints, functions etc. 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] [ADMIN] locales and encodings Oh MY!
With how similar straight C and en_US.UTF8 are it was suggested to me, by persons who are far more C knowledgeable then I in my office, that this is something the PG community could fix . A fix being so that col LIKE 'foo%' could use btree indexes in locales like en_US.UTF8 (and probably some others). @hackers - is the request unreasonable ? anyone got any idea of the price tag to make that happen ? I thought it already did that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: extensible enums
Andrew Dunstan and...@dunslane.net writes: On 11/12/2010 01:40 PM, Bruce Momjian wrote: FYI, I marked the TODO item for adding enums as completed. The TODO item used to also mention renaming or removing enums, but I have seen few requests for that so I removed that suggestion. We can always re-add it if there is demand. Renaming an item would not be terribly hard. Removing one is that nasty case. There are all sorts of places the old value could be referred to: table data, view definitions, check constraints, functions etc. Well, you can rename an item today if you don't mind doing a direct UPDATE on pg_enum. I think that's probably sufficient if the demand only amounts to one or two requests a year. I'd say leave it off the TODO list till we see if there's more demand than that. 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] multi-platform, multi-locale regression tests
On Nov 12, 2010, at 6:28 AM, Kevin Grittner wrote: The CursesReporter moves up and down the lines to write results to concurrently running tests. It's only useful on a terminal and certainly gets confused by anything that moves the cursor (which a plain 'print' certainly does). Ah, well that explains some problems I've had with getting my output to behave quite like I wanted! Thanks for that summary! I'm pretty sure I've been getting the CursesReporter; I'll switch to TapReporter. Oh, that would be great, because I can then have the TAP stuff I plan to add just run your tests and harness the results along with everything else. Best, David -- 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] WIP: extensible enums
On Fri, Nov 12, 2010 at 1:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: On 11/12/2010 01:40 PM, Bruce Momjian wrote: FYI, I marked the TODO item for adding enums as completed. The TODO item used to also mention renaming or removing enums, but I have seen few requests for that so I removed that suggestion. We can always re-add it if there is demand. Renaming an item would not be terribly hard. Removing one is that nasty case. There are all sorts of places the old value could be referred to: table data, view definitions, check constraints, functions etc. Well, you can rename an item today if you don't mind doing a direct UPDATE on pg_enum. I think that's probably sufficient if the demand only amounts to one or two requests a year. I'd say leave it off the TODO list till we see if there's more demand than that. I'd say put it on and mark it with an [E]. We could use some more [E]asy items for that list. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ADMIN] locales and encodings Oh MY!
Robert Haas robertmh...@gmail.com writes: With how similar straight C and en_US.UTF8 are it was suggested to me, by persons who are far more C knowledgeable then I in my office, that this is something the PG community could fix . A fix being so that col LIKE 'foo%' could use btree indexes in locales like en_US.UTF8 (and probably some others). is the request unreasonable ? anyone got any idea of the price tag to make that happen ? I thought it already did that. No, and the odds of it ever happening are insignificant. The sort order associated with en_US (and other dictionary order locales) is just too randomly different from what you need to optimize a LIKE search. (Whoever told you en_US sorts similarly to C is nuts.) The solution if you want the database's prevailing sort order to be en_US is to put an extra text_pattern_ops index on the column you want to do LIKE searches on. We might eventually have the ability to spell that put a C-locale index on the column, but text_pattern_ops is the way to do it today. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ADMIN] locales and encodings Oh MY!
On Fri, Nov 12, 2010 at 12:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: With how similar straight C and en_US.UTF8 are it was suggested to me, by persons who are far more C knowledgeable then I in my office, that this is something the PG community could fix . A fix being so that col LIKE 'foo%' could use btree indexes in locales like en_US.UTF8 (and probably some others). is the request unreasonable ? anyone got any idea of the price tag to make that happen ? I thought it already did that. No, and the odds of it ever happening are insignificant. The sort order associated with en_US (and other dictionary order locales) is just too randomly different from what you need to optimize a LIKE search. (Whoever told you en_US sorts similarly to C is nuts.) The solution if you want the database's prevailing sort order to be en_US is to put an extra text_pattern_ops index on the column you want to do LIKE searches on. We might eventually have the ability to spell that put a C-locale index on the column, but text_pattern_ops is the way to do it today. regards, tom lane Ok I hear you loud and clear.I am going to eat the overhead until I get to 9.0.1, currently on 8.3.X in some places. I will either take an outage and do a dump - re-init-restore or inplace upgrade and then do some locking, copy, drop old, rename new db path. thanks all. ..: Mark -- 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] WIP: extensible enums
Robert Haas robertmh...@gmail.com writes: On Fri, Nov 12, 2010 at 1:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, you can rename an item today if you don't mind doing a direct UPDATE on pg_enum. I think that's probably sufficient if the demand only amounts to one or two requests a year. I'd say leave it off the TODO list till we see if there's more demand than that. I'd say put it on and mark it with an [E]. We could use some more [E]asy items for that list. We don't need to add marginally-useful features just because they're easy. If it doesn't have a real use-case, the incremental maintenance cost of more code is a good reason to reject it. 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] B-tree parent pointer and checkpoints
On 11.11.2010 20:34, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: Hmm, we don't currently keep track of that when we descend the tree to choose the target page, but perhaps an extra Consistent call to check that would be acceptable. We already call Penalty for every tuple on each internal node on the way, so compared to that one more call should not be too expensive. If we do that, I think it would simplify the algorithm quite a bit to just update all the parents on the way down, instead of traversing up from the bottom after inserting the tuple to the leaf. Oh, that's a really good idea, I think. But what about page splits? I guess in the case of a split, you'd be replacing the parent entry anyway, so having previously updated it to something larger doesn't really cause a problem other than wasting a few cycles --- which are probably still less than you save by not having to traverse back up. I started looking at this, and run into a problem with page splits. The right-links in GiST work differently from b-tree, a right-link is only followed if we detect a concurrent page split. A concurrent split is detected by comparing the NSN field on the child page against the LSN that we saw on the parent when walking down. It means that if you just leave the incompletely split page in the tree, where one half is missing the parent pointer, scans will not find any tuples on that page. They would at first, but as soon as the the parent page is updated due to some unrelated insertion, the LSN of the parent is bumped above the NSN stored on the child, and the page becomes invisible to scanners. We avoid that problem during normal operation by keeping the parent page locked while the child is split, until the downlink is inserted into the parent. That blocks any other modifications to the parent page that would bump the LSN, until our downlink has been inserted. That doesn't work after crash recovery, as all the locks are released. I think we can work around that with a small modification to the page split algorithm. In a nutshell, when the child page is split, put a flag on the left half indicating that the rightlink must always be followed, regardless of the NSN. When the downlink is inserted to the parent, clear the flag. Setting and clearing of these flags need to be performed during WAL replay as well. So to split a page: (0. Lock the page to be split) 1. Split the page. Mark the rightlink in the left half with a flag indicating that it always needs to be followed. 2. Lock the parent. 3. Insert downlink. (The parent may need to be split too) 4. Clear the flag in the child, and update NSN to the LSN of the downlink insertion record. 5. Release child. 6. If the parent was split in step 3, goto 2. If we crash between steps 1 and 3, the rightlink will have the flag, so scans will know to always follow it. If we crash after step 3, recovery will replay steps 3 and 4, so scans will see the downlinks as usual. After a crash, the tree can be fixed up later by vacuum or subsequent inserts, by performing steps 2-4. -- 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] wCTE behaviour
Tom Lane t...@sss.pgh.pa.us writes: WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all The counter-example that jumps to mind is unix pipes. It's read-only at the consumer level but as soon as you stop reading, the producer stops. I guess that's only talking about the surprise factor, though. I'm not sure how far we go with the SIGPIPE analogy, but I wanted to say that maybe that would not feel so strange to some people if the DELETE were not run to completion but only until the reader is done. What about this one: WITH d AS (DELETE FROM foo RETURNING id), q AS (INSERT INTO queue SELECT 'D', id FROM d) SELECT * FROM q ORDER BY id LIMIT 10; For next example, replace INSERT with a MERGE to remove a previously existing 'I' or 'U' event in the queue when we add a 'D'. Bonus points if wCTE allows to implement the query without resorting to MERGE at all, which would be nice in my mind. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Refactoring the Type System
Andrew Dunstan and...@dunslane.net writes: This is so general as to be quite meaningless to me. What is it that is wanted that we don't have. (And don't say flexibility, that's way too general - say something much more concrete and specific. If you want flexibility we can store everything as text, but I doubt you'll like the result.) The way I understand it is (unsurprisingly) related to user data in extensions. PostGIS maintains a table of user attributes related to their types, if I've understood correctly. Things that won't fit in the typmod, that will be different depending on the columns or some other environment meta-data, and that will have consequences on the meaning and running of user queries. Ok, that's not that more precise, but that's a typmod which does not fit in 32 bits so is saved away in some PostGIS table and referred to from the main storage. About all the other problems with the type system flexibility that I've read on, I think they are in the type inference category: we like the strong typing of the database system but would like it to get forgotten about. The paramount of this I think was the proposal of the LAMBDA expressions at the time the DO utility statement appeared. I don't know how far in the type inference system we want to go (but I think we already have parts of that in our implicit cast rules). Maybe we want to think about having (user) functions be types, too. Also, tables are some types already, and JOINs and resultsets are relations too, so they are some types too. I don't know how far the current typing system is considering tables and joins and relation as the same thing as types, but there's something there with NULL handling and some ROW and record facilities that we see surprised people about in -bugs and other places. Well, just my very unorganised 2¢, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] multi-platform, multi-locale regression tests
David E. Wheeler wrote: On Nov 12, 2010, at 6:28 AM, Kevin Grittner wrote: I'll switch to TapReporter. Oh, that would be great, because I can then have the TAP stuff I plan to add just run your tests and harness the results along with everything else. I switched it with this patch: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=da7932fd5d71a64e1a2ebba598dfe6874c978d2d I have a couple questions: (1) Any idea why it finds the success of the tests unexpected?: # ri-trigger: test started ['wxry1', 'c1', 'r2', 'wyrx2', 'c2'] committed ['wxry1', 'r2', 'c1', 'wyrx2', 'c2'] rolled back ['wxry1', 'r2', 'wyrx2', 'c1', 'c2'] rolled back ['wxry1', 'r2', 'wyrx2', 'c2', 'c1'] rolled back ['r2', 'wxry1', 'c1', 'wyrx2', 'c2'] rolled back ['r2', 'wxry1', 'wyrx2', 'c1', 'c2'] rolled back ['r2', 'wxry1', 'wyrx2', 'c2', 'c1'] rolled back ['r2', 'wyrx2', 'wxry1', 'c1', 'c2'] rolled back ['r2', 'wyrx2', 'wxry1', 'c2', 'c1'] rolled back ['r2', 'wyrx2', 'c2', 'wxry1', 'c1'] committed rollback required: 8 / 8 commit required: 2 / 2 commit preferred: 0 / 0 ok 3 - ri-trigger (UNEXPECTED) (2) If I wanted something to show in the TAP output, like the three counts at the end of the test, what's the right way to do that? (I suspect that printing with a '#' character at the front of the line would do it, but that's probably not the proper way...) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all The counter-example that jumps to mind is unix pipes. It's read-only at the consumer level but as soon as you stop reading, the producer stops. I guess that's only talking about the surprise factor, though. I'm not sure how far we go with the SIGPIPE analogy, but I wanted to say that maybe that would not feel so strange to some people if the DELETE were not run to completion but only until the reader is done. I can see that there's a fair argument for that position in cases like the above, but the trouble is that there are also cases where it's very hard for the user to predict how many rows will be read. As examples, mergejoins may stop short of reading all of one input depending on what the last key value is from the other, and semijoins or antijoins will stop whenenever they hit a match in the inner input. I think in the join cases we had better establish a simple rule it'll get executed to completion. We could maybe do things differently if the outer query is non-join with a LIMIT, but that seems pretty inconsistent. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] copydir prototype
This patch: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=bb0fe9feb9fd75a6aaa960028a9f810c883b0fc4 ...did not tidy up after itself as well as it might have. In particular, it left the prototype for copydir() in src/include/port.h, while moving the source file from src/port/copydir.c to src/backend/storage/file/copydir.c. I think this should probably be cleaned up, but I'm a little uncertain where the best place to put that prototype is. I am tempted to just put this in a new header file named according to our usual conventions, namely src/include/storage/copydir.c, but since there's only one public function at present perhaps someone would like to argue for including it in some other, already-exstant header. A patch I'm working on needs to expose the copy_file() function, which is currently static to copydir.c, so maybe it would be preferable to rename copydir.c to copy.c and add the header as src/include/storage/copy.h. Thoughts? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] copydir prototype
Robert Haas robertmh...@gmail.com writes: This patch: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=bb0fe9feb9fd75a6aaa960028a9f810c883b0fc4 ...did not tidy up after itself as well as it might have. In particular, it left the prototype for copydir() in src/include/port.h, while moving the source file from src/port/copydir.c to src/backend/storage/file/copydir.c. I think this should probably be cleaned up, but I'm a little uncertain where the best place to put that prototype is. I am tempted to just put this in a new header file named according to our usual conventions, namely src/include/storage/copydir.c, but since there's only one public function at present perhaps someone would like to argue for including it in some other, already-exstant header. copydir.h I assume you meant? Seems reasonable. A patch I'm working on needs to expose the copy_file() function, which is currently static to copydir.c, so maybe it would be preferable to rename copydir.c to copy.c and add the header as src/include/storage/copy.h. -1 for that. Aside from being generally opposed to inessential file renamings, I think this will risk confusion with commands/copy.[ch]. 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] wCTE behaviour
Tom Lane t...@sss.pgh.pa.us writes: I can see that there's a fair argument for that position in cases like the above, but the trouble is that there are also cases where it's very hard for the user to predict how many rows will be read. As examples, mergejoins may stop short of reading all of one input depending on what the last key value is from the other, and semijoins or antijoins will stop whenenever they hit a match in the inner input. Oh. Indeed, I now understand what you mean by surprises. I keep forgetting that DML and JOINs can live together… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] copydir prototype
On Fri, Nov 12, 2010 at 4:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: This patch: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=bb0fe9feb9fd75a6aaa960028a9f810c883b0fc4 ...did not tidy up after itself as well as it might have. In particular, it left the prototype for copydir() in src/include/port.h, while moving the source file from src/port/copydir.c to src/backend/storage/file/copydir.c. I think this should probably be cleaned up, but I'm a little uncertain where the best place to put that prototype is. I am tempted to just put this in a new header file named according to our usual conventions, namely src/include/storage/copydir.c, but since there's only one public function at present perhaps someone would like to argue for including it in some other, already-exstant header. copydir.h I assume you meant? Seems reasonable. A patch I'm working on needs to expose the copy_file() function, which is currently static to copydir.c, so maybe it would be preferable to rename copydir.c to copy.c and add the header as src/include/storage/copy.h. -1 for that. Aside from being generally opposed to inessential file renamings, I think this will risk confusion with commands/copy.[ch]. Good point. OK, I'll just go with copydir.h then. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] multi-platform, multi-locale regression tests
On Nov 12, 2010, at 12:39 PM, Kevin Grittner wrote: (2) If I wanted something to show in the TAP output, like the three counts at the end of the test, what's the right way to do that? (I suspect that printing with a '#' character at the front of the line would do it, but that's probably not the proper way...) That is the proper way, but dtest might have a method for you to do that. If not, just do this before you print: $printme =~ s/^/# /g; Best, David -- 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] Restructuring plancache.c API
Excerpts from Tom Lane's message of jue nov 11 19:21:34 -0300 2010: I've been thinking about supporting automatic replan of cached plans using specific parameter values, as has been discussed several times, at greatest length in this thread: http://archives.postgresql.org/pgsql-hackers/2010-02/msg00607.php There doesn't seem to be full consensus about what the control method ought to be, but right at the moment I'm thinking about mechanism not policy. I think that what we need to do is restructure the API of plancache.c to make it more amenable to returning throwaway plans. It can already do that to some extent using the fully_planned = false code path, but that's not the design center and it was shoehorned in in perhaps a less than clean fashion. I want to rearrange it so there's an explicit notion of three levels of cacheable object: I was wondering if this could help with the separation of labour of functions in postgres.c that we were talking about a couple of weeks ago. The main impedance mismatch, so to speak, is that those functions aren't at all related to caching of any sort; but then, since you're looking for a new name for the source file, I return to my earlier suggestion of a generic queries.c or some such, which could handle all these issues. (Of course, querycache.c doesn't make any sense.) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: extensible enums
Excerpts from Bruce Momjian's message of vie nov 12 15:40:28 -0300 2010: FYI, I marked the TODO item for adding enums as completed. The TODO item used to also mention renaming or removing enums, but I have seen few requests for that so I removed that suggestion. We can always re-add it if there is demand. I'm sure there's going to be more demand for ENUM features, now that they are more usable. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Restructuring plancache.c API
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Tom Lane's message of jue nov 11 19:21:34 -0300 2010: I think that what we need to do is restructure the API of plancache.c to make it more amenable to returning throwaway plans. I was wondering if this could help with the separation of labour of functions in postgres.c that we were talking about a couple of weeks ago. Yeah, it was in the back of my mind that this patch might create some merge conflicts for that one, but I figured we could deal with that when the time came. I wasn't intending to refactor the behavior of pg_analyze_and_rewrite or pg_plan_queries, just change where they might get called from, so I think any conflict will be inessential and easily resolved. The main impedance mismatch, so to speak, is that those functions aren't at all related to caching of any sort; but then, since you're looking for a new name for the source file, I return to my earlier suggestion of a generic queries.c or some such, which could handle all these issues. (Of course, querycache.c doesn't make any sense.) I thought about querycache.c too, but it seems to carry the wrong connotations --- in mysql-land I believe they use that term to imply caching a query's *results*. But queries.c seems so generic as to convey no information at all. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.4-vintage problem in postmaster.c
Hi, Stefan Kaltenbrunner reported a problem in postmaster via IM to me. I thought I had nailed down the bug, but after more careful reading of the code, turns out I was wrong. The reported problem is that postmaster shuts itself down with this error message: 2010-11-12 10:19:05 CET FATAL: no free slots in PMChildFlags array I thought that canAcceptConnections() was confused about what the result of CountChildren() meant, but apparently not. This is a change from the 8.3 code that didn't have the ChildSlots stuff -- previously, if canAcceptConnections failed to report CAC_TOOMANY, it would just fail later when trying to add the backend to the shared-inval queue, as stated in the comment therein. In the new code, however, failure to keep an accurate count means that we fail later in AssigPostmasterChildSlot with a FATAL error, leading to overall shutdown. In postmaster.c, this all happens before forking, so I see no way for the system to be confused due to multiple processes starting in parallel. If you suspect that this may have to do with some race condition on starting many backends quickly, you would probably be right. The evidence from the log (which thankfully is set to DEBUG3, though most other settings about it seem to be rather broken) says that there were many backend starting just before the FATAL message: 2010-11-12 10:18:55 CET DEBUG: forked new backend, pid=2632 socket=348 2010-11-12 10:18:55 CET DEBUG: forked new backend, pid=840 socket=348 2010-11-12 10:18:55 CET DEBUG: forked new backend, pid=2972 socket=348 2010-11-12 10:18:55 CET DEBUG: forked new backend, pid=2724 socket=348 2010-11-12 10:18:57 CET DEBUG: forked new backend, pid=840 socket=348 2010-11-12 10:18:57 CET DEBUG: forked new backend, pid=2724 socket=348 2010-11-12 10:18:57 CET DEBUG: forked new backend, pid=2632 socket=348 2010-11-12 10:19:00 CET DEBUG: forked new backend, pid=2724 socket=348 2010-11-12 10:19:01 CET DEBUG: forked new backend, pid=2972 socket=348 2010-11-12 10:19:01 CET DEBUG: forked new backend, pid=2724 socket=348 2010-11-12 10:19:02 CET DEBUG: forked new backend, pid=2984 socket=348 2010-11-12 10:19:02 CET DEBUG: forked new backend, pid=840 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=2984 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=840 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=2984 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=2972 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=840 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=2724 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=2972 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=2904 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=840 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=1280 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=2984 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=2904 socket=348 2010-11-12 10:19:04 CET DEBUG: forked new backend, pid=840 socket=348 2010-11-12 10:19:05 CET DEBUG: forked new backend, pid=2724 socket=348 This is Windows 2000 Server --- I guess the PIDs being reused rather quickly is not something to worry particularly about. (Also note that log_line_prefix does not include the PID so it's not easy to learn much more from the log, according to Stefan). -- Álvaro Herrera alvhe...@alvh.no-ip.org -- 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] WIP: extensible enums
On Nov 12, 2010, at 2:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Nov 12, 2010 at 1:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, you can rename an item today if you don't mind doing a direct UPDATE on pg_enum. I think that's probably sufficient if the demand only amounts to one or two requests a year. I'd say leave it off the TODO list till we see if there's more demand than that. I'd say put it on and mark it with an [E]. We could use some more [E]asy items for that list. We don't need to add marginally-useful features just because they're easy. If it doesn't have a real use-case, the incremental maintenance cost of more code is a good reason to reject it. If we allow users to name objects, we ought to make every effort to also allow renaming them. In my mind, the only way renaming is too marginal to be useful is if the feature itself is too marginal to be useful. ...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: [HACKERS] WIP: extensible enums
On Fri, 2010-11-12 at 14:20 -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Nov 12, 2010 at 1:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, you can rename an item today if you don't mind doing a direct UPDATE on pg_enum. I think that's probably sufficient if the demand only amounts to one or two requests a year. I'd say leave it off the TODO list till we see if there's more demand than that. I'd say put it on and mark it with an [E]. We could use some more [E]asy items for that list. We don't need to add marginally-useful features just because they're easy. If it doesn't have a real use-case, the incremental maintenance cost of more code is a good reason to reject it. Perhaps we should remove the ability to rename tables and databases too. It would certainly lighten the code path. JD regards, tom lane -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] B-tree parent pointer and checkpoints
On Fri, Nov 12, 2010 at 7:20 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I think we can work around that with a small modification to the page split algorithm. In a nutshell, when the child page is split, put a flag on the left half indicating that the rightlink must always be followed, regardless of the NSN. When the downlink is inserted to the parent, clear the flag. Setting and clearing of these flags need to be performed during WAL replay as well. Does this not cause duplicate results? Or does GIST already have to be prepared to deal with duplicate results? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Refactoring the Type System
On Fri, 2010-11-12 at 12:03 -0500, Andrew Dunstan wrote: On 11/12/2010 11:34 AM, David Fetter wrote: Folks, For the past couple of years, I've been hearing from the PostGIS people among others that our type system just isn't flexible enough for their needs. It's really starting to show its age, or possibly design compromises that seemed reasonable a decade or more ago, but are less so now. This is so general as to be quite meaningless to me. What is it that is wanted that we don't have. Some kind of generics, type generators, or type interfaces (like an interface in Java or type class in haskell). A real subtyping system might also be nice. That being said, a few details are left to be decided (an understatement). 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] Refactoring the Type System
On Fri, Nov 12, 2010 at 6:12 PM, Jeff Davis pg...@j-davis.com wrote: That being said, a few details are left to be decided (an understatement). Best... comment... ever. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Refactoring the Type System
On Fri, 2010-11-12 at 08:34 -0800, David Fetter wrote: Folks, For the past couple of years, I've been hearing from the PostGIS people among others that our type system just isn't flexible enough for their needs. It's really starting to show its age, or possibly design compromises that seemed reasonable a decade or more ago, but are less so now. To that end, I've put up a page on the wiki that includes a list of issues to be addressed. It's intended to be changed, possibly completely. http://wiki.postgresql.org/wiki/Refactor_Type_System What might the next version of the type system look like? This problems (as stated) strikes me as pretty overwhelming. 1. As far as I can tell, we have the best type system of any SQL DBMS. 2. Getting a type system right is a hard problem by itself, and there isn't any obvious consensus (although I think there is some agreement on some issues). 3. Type systems are more challenging for a DBMS because you need to account for things like storage, indexing, and optimization in ways that programming languages don't (consider: when comparing an int4 and an int8, you may want to coerce based on what indexes you have available). 4. SQL standard issues. In particular, I think that any modern type system will run into pretty severe problems with NULLs in one way or another. I think we'd have to pay very close attention to the standard when designing a new type system, because I suspect that retrofitting the standard onto a system we invent independently would be a disaster. 5. Backwards compatibility issues. I think the best we'll do is be able to hack on some of the things that we actively want and have clear use cases for, such as type interfaces. We might have to give up on some of the more ambitious ideas that involve propagating interesting information through the type inference system; or having any real type that wasn't declared with CREATE TYPE. Consider that right now we bundle the element type information along with the array _value_. Ideas welcome. Particularly if there are a few clear use cases. 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
[HACKERS] CommitFest 2010-11: Call for Reviewers
It's the time of year again where leaves are falling, temperatures dropping, and patches cry out looking for one last review before the start of the holiday season. The patches submitted so far are listed at https://commitfest.postgresql.org/action/commitfest_view?id=8 , featuring a mix of brand new ones with some going through their second or third round of rework. Expect to see a furious weekend of last minute patches that arrive just before the deadline too. The official cut-off to start the CommitFest is Mon Nov 15 00:00:00 UTC. If you're interested in reviewing a patch but haven't done so before, the process is outlined at the following page: http://wiki.postgresql.org/wiki/Reviewing_a_Patch http://wiki.postgresql.org/wiki/RRReviewers All you have to do to claim a patch is update the CommitFest application page to put your name down as the reviewer, then do the review in a timely fashion. Look for the patches with the bright red Nobody listed in the reviewer field. If you have questions about which patch to work on, please try to keep e-mail traffic related to topics like patch selection and volunteering for a new round-robin assignment to the pgsql-rrreviewers mailing list. Whereas the pgsql-hackers list is the right destination for the actual review itself. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD -- 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] multi-platform, multi-locale regression tests
I wrote: (1) Any idea why it finds the success of the tests unexpected? Should anyone else run into this, it's controlled by this in the test scheduling definitions (the tdef values): 'xfail': True There are other test flags you can override here, like 'skip' to skip a test. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
Excerpts from Dimitri Fontaine's message of vie nov 12 17:13:59 -0300 2010: Tom Lane t...@sss.pgh.pa.us writes: WITH t AS (DELETE FROM foo RETURNING *) SELECT * FROM t LIMIT 1; How many rows does this delete? I think we concluded that we should force the DELETE to be run to conclusion even if the outer query didn't read it all The counter-example that jumps to mind is unix pipes. It's read-only at the consumer level but as soon as you stop reading, the producer stops. I guess that's only talking about the surprise factor, though. It's not that straighforward though, in that the producer could stop a bit ahead of what the consumer reads, due to there being a buffer in the middle. Witness this simple example $ cat producer #!/bin/sh for i in `seq 1 1000`; do echo $i /tmp/mylog echo $i done $ chmod a+x producer $ ./producer | head -5 1 2 3 4 5 $ cat /tmp/mylog 1 2 3 4 5 6 7 I certainly wouldn't want our implementation to behave like this. I'm not sure how far we go with the SIGPIPE analogy, but I wanted to say that maybe that would not feel so strange to some people if the DELETE were not run to completion but only until the reader is done. What about this one: WITH d AS (DELETE FROM foo RETURNING id), q AS (INSERT INTO queue SELECT 'D', id FROM d) SELECT * FROM q ORDER BY id LIMIT 10; Personally I find this one less surprising: WITH d AS (DELETE FROM foo LIMIT 10 RETURNING id), q AS (INSERT INTO queue SELECT 'D', id FROM d) SELECT * FROM q ORDER BY id; -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Refactoring the Type System
On 11/12/2010 07:07 PM, Jeff Davis wrote: On Fri, 2010-11-12 at 08:34 -0800, David Fetter wrote: Folks, For the past couple of years, I've been hearing from the PostGIS people among others that our type system just isn't flexible enough for their needs. It's really starting to show its age, or possibly design compromises that seemed reasonable a decade or more ago, but are less so now. To that end, I've put up a page on the wiki that includes a list of issues to be addressed. It's intended to be changed, possibly completely. http://wiki.postgresql.org/wiki/Refactor_Type_System What might the next version of the type system look like? This problems (as stated) strikes me as pretty overwhelming. 1. As far as I can tell, we have the best type system of any SQL DBMS. 2. Getting a type system right is a hard problem by itself, and there isn't any obvious consensus (although I think there is some agreement on some issues). 3. Type systems are more challenging for a DBMS because you need to account for things like storage, indexing, and optimization in ways that programming languages don't (consider: when comparing an int4 and an int8, you may want to coerce based on what indexes you have available). 4. SQL standard issues. In particular, I think that any modern type system will run into pretty severe problems with NULLs in one way or another. I think we'd have to pay very close attention to the standard when designing a new type system, because I suspect that retrofitting the standard onto a system we invent independently would be a disaster. 5. Backwards compatibility issues. I think the best we'll do is be able to hack on some of the things that we actively want and have clear use cases for, such as type interfaces. We might have to give up on some of the more ambitious ideas that involve propagating interesting information through the type inference system; or having any real type that wasn't declared with CREATE TYPE. Consider that right now we bundle the element type information along with the array _value_. Yeah, composites too, IIRC. It's a bit sad. But those are really just warts show the difficulties we face in implementing types. I'm still waiting for some seriously different yet possible thing we could do. (And I agree we do have about the best type system around). 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] WIP: extensible enums
Joshua D. Drake wrote: On Fri, 2010-11-12 at 14:20 -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Nov 12, 2010 at 1:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, you can rename an item today if you don't mind doing a direct UPDATE on pg_enum. I think that's probably sufficient if the demand only amounts to one or two requests a year. I'd say leave it off the TODO list till we see if there's more demand than that. I'd say put it on and mark it with an [E]. We could use some more [E]asy items for that list. We don't need to add marginally-useful features just because they're easy. If it doesn't have a real use-case, the incremental maintenance cost of more code is a good reason to reject it. Perhaps we should remove the ability to rename tables and databases too. It would certainly lighten the code path. OK, got it. Added incomplete TODO item: Allow renaming and deleting enumerated values from an existing enumerated data type -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] WIP: extensible enums
On 11/12/2010 09:18 PM, Bruce Momjian wrote: OK, got it. Added incomplete TODO item: Allow renaming and deleting enumerated values from an existing enumerated data type I have serious doubts that deleting will ever be sanely doable. 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] WIP: extensible enums
Andrew Dunstan wrote: On 11/12/2010 09:18 PM, Bruce Momjian wrote: OK, got it. Added incomplete TODO item: Allow renaming and deleting enumerated values from an existing enumerated data type I have serious doubts that deleting will ever be sanely doable. True. Should we not mention it then? I can't think of many objects we can't delete. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Label switcher function
The attached patch allows the security label provider to switch security label of the client during execution of certain functions. I named it as label switcher function; also called as trusted- procedure in SELinux community. This feature is quite similar idea toward security definer function, or set-uid program on operating system. It allows label providers to switch its internal state that holds security label of the client, then restore it. If and when a label provider said the function being invoked is a label-switcher, fmgr_security_definer() traps this invocation and set some states just before actual invocations. We added three new hooks for security label provider. The get_client_label and set_client_label allows the PG core to save and restore security label of the client; which is mostly just an internal state of plugin module. And, the get_switched_label shall return NULL or a valid label if the supplied function is a label switcher. It also informs the PG core whether the function is switcher or not. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp pgsql-switcher-function.1.patch Description: application/octect-stream -- 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] plan time of MASSIVE partitioning ...
Tom Lane wrote: Leonardo Francalanci m_li...@yahoo.it writes: Cases with lots of irrelevant indexes. Zoltan's example had 4 indexes per child table, only one of which was relevant to the query. In your test case there are no irrelevant indexes, which is why the runtime didn't change. Mmh... I must be doing something wrong. It looks to me it's not just the irrelevant indexes: it's the order by that counts. Ah, I oversimplified a bit: actually, if you don't have an ORDER BY or any mergejoinable join clauses, then the possibly_useful_pathkeys test in find_usable_indexes figures out that we aren't interested in the sort ordering of *any* indexes, so the whole thing gets short-circuited. You need at least the possibility of interest in sorted output from an indexscan before any of this code runs. FYI, I always wondered if the rare use of mergejoins justified the extra planning time of carrying around all those joinpaths. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] plan time of MASSIVE partitioning ...
Bruce Momjian br...@momjian.us writes: FYI, I always wondered if the rare use of mergejoins justified the extra planning time of carrying around all those joinpaths. They're hardly rare. 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] max_wal_senders must die
Josh Berkus wrote: None of us know. What I do know is that I don't want PostgreSQL to be slower out of the box. Understandable. So it seems like the answer is getting replication down to one configuration variable for the common case. That eliminates the cycle of oops, need to set X and restart/reload without paying performance penalties on standalone servers. Right. I propose that we set max_wal_senders to unlimited when wal_level = hot_standby. When they tell us they are using hot_standby via wal_level, why make them change another setting (max_wal_senders)? Basically, we don't need to turn everything on by default, but some settings should trigger other behavior automatically. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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: Improved parallel make support
Andrew Dunstan and...@dunslane.net writes: On 11/12/2010 03:16 PM, Peter Eisentraut wrote: Improved parallel make support Looks like this patch has pretty comprehensively broken the MSVC build system. I'll see what I can recover from the wreckage. There are also at least three non-Windows buildfarm members failing like so: gmake -C src all gmake[1]: Entering directory `/home/pgbuild/pgbuildfarm/HEAD/pgsql.6736/src' gmake[1]: *** virtual memory exhausted. Stop. gmake[1]: Leaving directory `/home/pgbuild/pgbuildfarm/HEAD/pgsql.6736/src' gmake: *** [all-src-recursive] Error 2 I think we may have pushed too far in terms of what actually works reliably across different make versions. 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] max_wal_senders must die
Bruce Momjian br...@momjian.us writes: Right. I propose that we set max_wal_senders to unlimited when wal_level = hot_standby. It's a memory allocation parameter ... you can't just set it to unlimited, at least not without a nontrivial amount of work. 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
