Re: [HACKERS] SE-PostgreSQL?
Robert Haas wrote: I think the best thing for this patch right now is to move it to Returned with Feedback. I can't see any way that this patch is going to be made committable for this CommitFest, and I think that pretending otherwise is only encouraging KaiGai to do another of his lighting rework-and-resubmits. While those are very impressive, they're not getting us where we need to be. I think that what KaiGai needs to do here is get the spec written (with the help of Greg Williamson and anyone else who is willing to pitch in), and submit it for comments. I don't think there will be a problem getting that reviewed outside of a CommitFest, and it's not a patch anyway, so the time that it gets submitted is not crucial. What is crucial is that it is a good spec that everyone can read, and hopefully understand and discuss. There is no point writing any more code, or submitting any more patches, until we have agreement on what those patches are supposed to do. I also agree that the easy understandable specification what SE-PostgreSQL tries to achieve is more important than implementation. I described it from the scratch again. Here is an initial draft: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft I would like to improve documentation quality and fix its specification during the discussion. I am going to go ahead and mark this as Returned with Feedback. Agreed. -- OSS Platform Development Division, NEC 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] query decorrelation in postgres
I am sorry for not elaborating on that. What I meant by de-correlation was optimizing a query to get rid of sub-queirs by using joins. eg. In the TPC-H schema, a query to find out the names of suppliers who supply parts having size 100 *Query with nested subqueries:* SELECT S_NAME FROM SUPPLIER WHERE S_SUPPKEY IN ( SELECT PS_SUPPKEY FROM PARTSUPP WHERE PS_PARTKEY IN ( SELECT P_PARTKEY FROM PART WHERE P_SIZE 100 ) *Query with joins without subqueries:* SELECT S_NAME FROM SUPPLIER INNER JOIN PARTSUPP ON S_SUPPKEY = PS_SUPPKEY INNER JOIN PART ON P_PARTKEY = PS_PARTKEY WHERE P_SIZE 100 Thanks, Mahendra On Thu, Jul 23, 2009 at 9:02 PM, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: mahendra chavan mah...@gmail.com wrote: I am a master's student in computer science at IIT Bombay. As part of my project, I need to get a decorrelated version of a SQL query. Please could anyone let me know if we have query decorrelation feature implemented in postgres ? What do you mean by query decorrelation? Is it an addtional method for query optimization? At least there is no word 'decorrelation' in the postgres documentation. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Re: [HACKERS] Aggregate-function space leakage
2009/7/24 Tom Lane t...@sss.pgh.pa.us: I think that WindowAgg does not need any changes because it already does MemoryContextResetAndDeleteChildren(winstate-wincontext) at partition boundaries. Hitoshi, do you agree? I do. Looking closer, temporal space management of Agg is getting similar to WindowAgg's partition localMemory strategy. We might be able to get them one. Anyway, the choice sounds better now, though I will test array_agg more. 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] Determining client_encoding from client locale
On Fri, Jul 24, 2009 at 04:12, Jaime Casanovajcasa...@systemguards.com.ec wrote: On Mon, Jul 6, 2009 at 10:00 AM, Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: Here's my first attempt at setting client_encoding automatically from locale. Sorry for the many mails on this issue.. i will do a recolect of my findings: 1) it introduces a dependency for -lpgport when compiling a client that uses libpq http://archives.postgresql.org/pgsql-hackers/2009-07/msg01511.php For other parts of libpgport that are needed, we pull in the individual source files. We specifically *don't* link libpq with libpgport, for a reason. There's a comment in the Makefile that explains why. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] SE-PostgreSQL Specifications
Excellent ... I'll try to have something tomorrow (Friday PDT) but I've got some non-work related issues which may keep from giving this a good look until the weekend (FWIW). I'll post any questions I have. Thanks, Greg W. - Original Message From: KaiGai Kohei kai...@ak.jp.nec.com To: Robert Haas robertmh...@gmail.com Cc: pgsql-hackers@postgresql.org; KaiGai Kohei kai...@kaigai.gr.jp; Greg Williamson gwilliamso...@yahoo.com; Sam Mason s...@samason.me.uk; Joshua Brindle met...@manicmethod.com Sent: Thursday, July 23, 2009 9:54:10 PM Subject: SE-PostgreSQL Specifications Here is the initial draft of SE-PostgreSQL specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft I've described it from the scratch again with paying attention for the people knowing nothing about SELinux. In some points, it uses comparison between the database privilege mechanism and SE-PostgreSQL for easy understanding. Please point out, if ... - Its composition can be improved. - Here is not enough introductions for what user wants to know. - Here is too much explanations, more brief one will be available. - Here is not easy understandable for database folks. - Here is not enough English quality. - And so on... In addition, I would like to fix its specifications during the discussion. Thanks, -- OSS Platform Development Division, NEC 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] WIP: plpython3
On Friday 24 July 2009 01:23:40 James Pye wrote: Here are the features that I plan/hope to implement before submitting any patch: * Native Typing [Python types that represent Postgres types] * Reworked function structure (Python modules, not function fragments) * Improved SQL interfaces (prepared statement objects[2]) * Better SRF support(?) (uses iterators, will support composites, vpc mat) * Direct function calls (to other Postgres functions) * IST support (with xact(): ...) * Full tracebacks for Python exceptions(CONTEXT support) * Cached bytecode (presuming a procache attributes patch would be acceptable[3]) While various of these ideas may be good, I think you are setting yourself up for a rejection. There is a lot of plpython code already out there, and many years have gone into debugging plpython to work well, so rewriting everything and setting everyone up for a flag day, or requiring the parallel maintenance of old and new versions of plpython is not going to work. Plus, tying all of this up with Python 3 will make totally sure that no one expect a minority will be able to use it. As far as I can tell, most of the features you list above could very well be implemented in the current language handler, using separate, isolated patches. I don't see why everything needs to be written from scratch. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] DefaultACLs
Hi, I'd still like to have opinion from one of the commiters on the VIEW problem which also affects grant on all patch ( see http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and I fear returned with feedback might prevent that until next commit fest. I see potential for confusion in that GRANT ON TABLE x works if x is a base table or a view, but GRANT ON ALL TABLES would not affect views. Maybe you need to make up a different syntax to affect only base tables, e.g., GRANT ON ALL BASE TABLES. That's not what I mean the problem is what is the best way of handling the views in implementation itself (there were IIRC 3 possible solutions devised and I don't think we have consensus on which is better). Peter is raising a good question here and it's not related to the implementation. What he is saying is that in your new implementation if GRANT ON ALL TABLES is invoked, it will affect only RELKIND_TABLE objects. Whereas the GRANT ON TABLE affects both RELKIND_TABLE and RELKIND_VIEW types of objects (with and without your patch). We could have brought in the differentiation with this patch to treat views and tables separately. So a GRANT ON TABLE would just affect tables. But I guess that will break existing user scripts which assume it works against VIEWS too. I don't know how acceptable the ON ALL BASE TABLES sounds to all. Regards, Nikhils In short, 1. add ACL_OBJECT_VIEW into GrantObjectType enum and track that inside code 2. create new enum with table, view, function and sequence objects in it (that works well for DefaultACLs but not for GRANT ON ALL) 3. add some boolean into GrantStmt that would indicate that relation is a view (that works for GRANT ON ALL but does not solve anything for DefaultACLs) Currently DefaultACLs patch uses method 2 (because Stephen does not like method 1) and GRANT ON ALL patch uses method 1 and it might be better if both patches uses only one of those. If we went with method 1 we probably should just ditch GrantObjectType alltogether and work with subset of ObjectType as other commands do (I haven't found any reason for GrantObjectType to exist other than having single object type for both TABLE and VIEW). And If we choose not to use method 1 then we should probably go with 2 for DefaultACLs and 3 for GRANT ON ALL. That is unless somebody has a better solution. -- Regards Petr Jelinek (PJMODOS) -- 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] When is a record NULL?
On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote: Well, a ROW is an ordered set of values, each one of which may be either NULL or NOT NULL. Right. It doesn't really make sense to talk about the ROW itself being NULL or NOT NULL, only its member values (but for extra confusion, contrast with the treatment of arrays, which can themselves be NULL). Well then maybe a record (row) should *never* be null. It does make sense, however, to talk about the ROW's member values being entirely NULL or entirely NOT NULL, and that's what the IS NULL and IS NOT NULL tests tell you about. Ah! So that's where the three-valued logic comes in to play with records: try=# SELECT ROW(1, NULL) IS NULL, ROW (1, 1) IS NULL, ROW(NULL, NULL) IS NULL; ?column? | ?column? | ?column? --+--+-- f| f| t I guess the spec authors figured they might as well make IS [NOT] NULL do something useful when applied to a row rather than throwing an error. I tend to agree. Frankly, I find the state where a record with a NULL and a not-null value being neither NULL nor not NULL bizarre. I hope that provides some clarity. It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the whole thing totally bizarre. Is it me? 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] When is a record NULL?
David E. Wheeler wrote:
On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
I guess the spec authors figured they might as well make IS [NOT] NULL
do something useful when applied to a row rather than throwing an
error. I tend to agree.
Frankly, I find the state where a record with a NULL and a not-null
value being neither NULL nor not NULL bizarre.
I'm guessing the justification (and presumably this was worked out based
on the behaviour of one or more of the big DB providers and then
justified afterwards) is that the composite is partially unknown. Of
course you should either introduce a new code or throw an error, but
four-valued logic isn't going to win you any friends.
If the argument *is* that because you know part of the overall value the
composite isn't null then I'd argue that ('abc' || null) isn't null
either. After all, the first three characters are perfectly well
established.
I hope that provides some clarity.
It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the
whole thing totally bizarre. Is it me?
Yes, just you. None of the rest of us have any problems with this at all :-)
--
Richard Huxton
Archonet Ltd
--
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] When is a record NULL?
2009/7/24 David E. Wheeler da...@kineticode.com: It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the whole thing totally bizarre. Is it me? *shrug* The IS [NOT] NULL tests mean something different when applied to a ROW than they do when applied to a scalar value or an array. SELECT 1 IS NULL = means is this scalar set to the special value NULL?. SELECT ROW(1, 2) IS NULL = means are all the member values of this row set to the special value NULL? So it is wrong to talk about ROW(NULL, NULL) being NULL. It doesn't have the property of being NULL or not NULL, because it is a composite value. ROW(NULL, NULL) IS NULL returns true, but that is not the same as saying that it actually is NULL, because of the different semantics above. It's slightly different semantics from what you get with ordinary scalar values, but that is cognisant with the fact that composites are fundamentally different things from ordinary scalar values. Cheers, BJ -- 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: plpython3
On Jul 24, 2009, at 1:21 AM, Peter Eisentraut wrote: While various of these ideas may be good, I think you are setting yourself up for a rejection. Right, I supposed that that may be the case or at least that you would feel this way based on your messages from the prior thread. There is a lot of plpython code already out there, and many years have gone into debugging plpython to work well, so rewriting everything and setting everyone up for a flag day, or requiring the parallel maintenance of old and new versions of plpython is not going to work. Does this mean that you are no longer of the opinion that a separate implementation is acceptable under the circumstances that it provides major advantages? Or are you of the opinion that the listed features do not provide major advantages? Or, perhaps, more appropriately, that the transitional features do not provide major advantages? [transitional features being native typing and reworked function structure] As far as I can tell, most of the features you list above could very well be implemented in the current language handler, using separate, isolated patches. I don't see why everything needs to be written from scratch. That's why I tried to highlight native typing and the reworked function structure. Those two features, not to mention Python 3, make it a distinct-enough beast to justify a different code base, IMO. The rest are icing. Icing is delicious. I see Python 3 as a good opportunity to change the interfaces and fix the design of the PL. I dunno. I have time to give it some TLC, and I'm not terribly excited about trying to tack features onto something that I find kinda gross. -- 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] join removal
On Jul 17, 2009, at 04:27 , Robert Haas wrote: - INNER joins are more complex because what happens on the inner side of the join can potentially wipe out rows from the result. With a LEFT join, it's sufficient to prove that the inner rel is at least unique enough, but for an INNER join, we have to prove that it's exactly UNIQUE enough. I think we can only provide this when the inner rel is a base relation with a unique index over EXACTLY (not a subset of) the relevant columns AND there is a foreign key relationship from the outer rel to the inner rel over the join columns. Reasoning on foreign key relationships opens up for other optimization opportunities as well, so being able to prove that a join cannot alter the number of rows would be nice. For example, Limit-operators can possibly be pushed below a join that does not alter the result set, to reduce the amount of work done by the join. Also, we can prove that uniqueness properties are kept. To put both examples in context, consider tables A and B defined as follows: Table public.a Column | Type | Modifiers +-+--- id | integer | not null Indexes: a_pkey PRIMARY KEY, btree (id) Referenced by: TABLE b CONSTRAINT b_id_fkey FOREIGN KEY (id) REFERENCES a(id) Table public.b Column | Type | Modifiers +-+--- id | integer | not null Indexes: b_pkey PRIMARY KEY, btree (id) Foreign-key constraints: b_id_fkey FOREIGN KEY (id) REFERENCES a(id) The query plan for SELECT DISTINCT a.id FROM b JOIN a USING (id) ORDER BY a.id ASC LIMIT 10 is this: QUERY PLAN - Limit (cost=0.00..7.20 rows=10 width=4) - Unique (cost=0.00..36.72 rows=51 width=4) - Merge Join (cost=0.00..36.59 rows=51 width=4) Merge Cond: (b.id = a.id) - Index Scan using b_pkey on b (cost=0.00..29.02 rows=51 width=4) - Index Scan using a_pkey on a (cost=0.00..13.77 rows=101 width=4) In this case we know that joining A does not alter the result set, because of the FK from B.id to A.id. Also, because B.id is also unique, the uniqueness of A.id is retained. Thus, the plan can be optimized to something like QUERY PLAN - Merge Join (...) Merge Cond: (b.id = a.id) - Limit (...) - Index Scan using a_pkey on a (...) - Index Scan using b_pkey on b (...) Perhaps these (and other) future opportunities make infrastructure changes for proper join removal support more worthwhile. -- Alex Brasetvik -- 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] join regression failure on cygwin
Tom Lane wrote: I don't personally have a problem with just forcing use of our own erand48 on Cygwin; it's not a lot of code and it would make the behavior of that build more like the MSVC build. But it's curious that such a simple library function is seemingly broken on Cygwin ... especially when their random() and srandom() evidently work. It appears on Googling a bit that the erand48() is buggy in that it requires the seed to have been initialized with srand48() or it will constantly return 0.0. So I think just forcing use of ours is the safe way to go. It might have been fixed since I installed Cygwin, although I can't find a reference to that, and I don't feel like triangulating it anyway. 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] When is a record NULL?
Does this also apply at the column level? In Postgres is If column IS NOT NULL or If column IS NULL? Do all columns in the record have to be NULL to have IF column IS NULL = true? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] When is a record NULL?
Brendan Jurd dire...@gmail.com writes: So it is wrong to talk about ROW(NULL, NULL) being NULL. It doesn't have the property of being NULL or not NULL, because it is a composite value. ROW(NULL, NULL) IS NULL returns true, but that is not the same as saying that it actually is NULL, because of the different semantics above. It's worse than that, because there actually is also such a thing as the row value being NULL --- ie, there's no row structure at all. At least internally, that's a completely different thing from having a row all of whose fields are null. SQL doesn't provide a test for this case that's separate from the test involving null-ness of individual fields. Not much we can do about it though. I'm not entirely sure that exposing the distinction would be helpful anyway ... 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] join regression failure on cygwin
Andrew Dunstan and...@dunslane.net writes: It appears on Googling a bit that the erand48() is buggy in that it requires the seed to have been initialized with srand48() or it will constantly return 0.0. Huh, and that sends us into an infinite loop? I'll take a look at that. Even though it's surely nonrandom, it doesn't seem like pathological behavior of the RNG should wedge us completely. So I think just forcing use of ours is the safe way to go. Agreed, but I'm curious ... 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] When is a record NULL?
On Thu, Jul 23, 2009 at 06:46:25PM -0700, David E. Wheeler wrote: Yes, but given that the standard says that `ROW(1, NULL)` is NULL, then I would expect it to be NOT DISTINCT from `ROW(2, NULL)`. Wait, didn't we decide upthread that the standard said ROW(1, NULL) isn't NULL? (From Tom): This is per SQL standard. IS NULL is true if *all* the record's fields are null; IS NOT NULL is true if *none* of them are. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] join removal
On Fri, Jul 24, 2009 at 7:53 AM, Alex Brasetvika...@brasetvik.com wrote: On Jul 17, 2009, at 04:27 , Robert Haas wrote: - INNER joins are more complex because what happens on the inner side of the join can potentially wipe out rows from the result. With a LEFT join, it's sufficient to prove that the inner rel is at least unique enough, but for an INNER join, we have to prove that it's exactly UNIQUE enough. I think we can only provide this when the inner rel is a base relation with a unique index over EXACTLY (not a subset of) the relevant columns AND there is a foreign key relationship from the outer rel to the inner rel over the join columns. Reasoning on foreign key relationships opens up for other optimization opportunities as well, so being able to prove that a join cannot alter the number of rows would be nice. For example, Limit-operators can possibly be pushed below a join that does not alter the result set, to reduce the amount of work done by the join. Interesting, I hadn't thought about that, but it's an excellent point. Another case that comes up is: A LEFT JOIN (B INNER JOIN C ON Pbc) ON Pab In general, this doesn't commute, because you need to emit a NULL-extended copy of A whenever Pab has no match in B INNER JOIN C ON Pbc. But if you know that Pbc will always be satisfied for exactly one row in B, then you can decide to implement the join between B and C as a left join rather than an inner join, so you get this: A LEFT JOIN (B LEFT JOIN C ON Pbc) ON Pab Now it commutes: (A LEFT JOIN B ON Pab) LEFT JOIN C ON Pbc I'm going to try to get the basic join removal code (for left joins, which don't need foreign-key deduction) done for CommitFest 2009-09. The next step is the foreign key deduction so we can remove inner joins, but I'm not sure I'll have that for 8.5 unless someone wants to either pitch in or cough up some money. Reordering joins around limits is, I suspect, very difficult indeed, so should probably be a project for phase 3. ...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] When is a record NULL?
Joshua Tolley eggyk...@gmail.com writes: On Thu, Jul 23, 2009 at 06:46:25PM -0700, David E. Wheeler Yes, but given that the standard says that `ROW(1, NULL)` is NULL, then I would expect it to be NOT DISTINCT from `ROW(2, NULL)`. Wait, didn't we decide upthread that the standard said ROW(1, NULL) isn't NULL? David misspoke in the quoted statement, as I believe he figured out soon thereafter. For that row value, neither IS NULL nor IS NOT NULL will return true. The spec defines them in such a way that they are not inverses for row values. SQL2008 points out: NOTE 219 - For all R, R IS NOT NULL has the same result as NOT R IS NULL if and only if R is of degree 1. Table 14, null predicate semantics, specifies this behavior. That table looks like this: R ISR IS NOT NOT R IS NOT R IS NOT _Expression___NULLNULL__NULL__NULL_ | degree 1: null | true_ | false_ | false_ | true_ | || | || | | degree 1: not | false_| true_ | true_ | false_ | null | degree 1:| true_ | false_ | false_ | true_ | | all null | | || | || | || | | degree 1:| false_| false_ | true_ | true_ | | some null | | || | || | || | | degree 1:| false_| true_ | true_ | false_ | |_none_null__|___|_||__| 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] query decorrelation in postgres
mahendra chavan mah...@gmail.com wrote: What I meant by de-correlation was optimizing a query to get rid of sub-queirs by using joins. eg. In the TPC-H schema, a query to find out the names of suppliers who supply parts having size 100 *Query with nested subqueries:* SELECT S_NAME FROM SUPPLIER WHERE S_SUPPKEY IN ( SELECT PS_SUPPKEY FROM PARTSUPP WHERE PS_PARTKEY IN ( SELECT P_PARTKEY FROM PART WHERE P_SIZE 100 ) *Query with joins without subqueries:* SELECT S_NAME FROM SUPPLIER INNER JOIN PARTSUPP ON S_SUPPKEY = PS_SUPPKEY INNER JOIN PART ON P_PARTKEY = PS_PARTKEY WHERE P_SIZE 100 Those two queries aren't exactly identical, because you could get duplicate rows in the second which would not be there in the first. Optimizations to pull up subqueries into a higher level FROM clause as joins have been in PostgreSQL for as long as I've been using it, but the ability to do the specific optimization you show there (without the duplicates) was added in version 8.4 using semi-joins. I don't think any syntax was added to explicitly write a query using semi-joins, though; it's an optimization of EXISTS, IN, ANY, etc. To see how the planner chooses to execute a given query against a particular schema which has a particular set of statistics about the data distributions, use the EXPLAIN option. http://www.postgresql.org/docs/8.4/interactive/sql-explain.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] join regression failure on cygwin
I wrote:
Andrew Dunstan and...@dunslane.net writes:
It appears on Googling a bit that the erand48() is buggy in that it
requires the seed to have been initialized with srand48() or it will
constantly return 0.0.
Huh, and that sends us into an infinite loop? I'll take a look at that.
Even though it's surely nonrandom, it doesn't seem like pathological
behavior of the RNG should wedge us completely.
The answer is that a constant RNG result sends this bit of
geqo_selection() into a tight loop:
int first,
second;
first = linear(root, pool-size, bias);
second = linear(root, pool-size, bias);
if (pool-size 1)
{
while (first == second)
second = linear(root, pool-size, bias);
}
Not sure if it's worth trying to do something about that, or exactly
what we'd do anyway. Even if we hacked this up somehow, a constant RNG
result would pretty much break GEQO for any useful purpose. So it could
be argued that having the regression tests fail here is a good thing...
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] join regression failure on cygwin
Tom Lane wrote:
I wrote:
Andrew Dunstan and...@dunslane.net writes:
It appears on Googling a bit that the erand48() is buggy in that it
requires the seed to have been initialized with srand48() or it will
constantly return 0.0.
Huh, and that sends us into an infinite loop? I'll take a look at that.
Even though it's surely nonrandom, it doesn't seem like pathological
behavior of the RNG should wedge us completely.
The answer is that a constant RNG result sends this bit of
geqo_selection() into a tight loop:
int first,
second;
first = linear(root, pool-size, bias);
second = linear(root, pool-size, bias);
if (pool-size 1)
{
while (first == second)
second = linear(root, pool-size, bias);
}
Not sure if it's worth trying to do something about that, or exactly
what we'd do anyway. Even if we hacked this up somehow, a constant RNG
result would pretty much break GEQO for any useful purpose. So it could
be argued that having the regression tests fail here is a good thing...
Right. Let's let sleeping dogs lie. I think at most a code comment is
the only action called for.
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] display previous query string of idle-in-transaction
daveg da...@sonic.net wrote: On Thu, Jun 04, 2009 at 10:22:41PM -0400, Robert Haas wrote: maybe make a separate column called idle that's a boolean, or something, and let the query column contain the most recent query (whether or not it's still executing). +1 I like this idea a lot. Possibly it would be useful to have the end time of the last query too, then one could find idle sessions that were old and truly idle rather than just waiting for a busy client to send the next query. select ... from pg_stat_activity where idle and last_statement_endtime now() - interval '1 minute'; +1 Of course, you might be more interested in those which are idle in a transaction, but that's easily done with these changes -- just throw in xact_start IS NULL. -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] display previous query string of idle-in-transaction
On Fri, Jul 24, 2009 at 10:47 AM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: daveg da...@sonic.net wrote: On Thu, Jun 04, 2009 at 10:22:41PM -0400, Robert Haas wrote: maybe make a separate column called idle that's a boolean, or something, and let the query column contain the most recent query (whether or not it's still executing). +1 I like this idea a lot. Possibly it would be useful to have the end time of the last query too, then one could find idle sessions that were old and truly idle rather than just waiting for a busy client to send the next query. select ... from pg_stat_activity where idle and last_statement_endtime now() - interval '1 minute'; +1 Hmm, I don't think we'd need two columns for this, actually. You could just have one column last_statement_endtime (not sure if it's the best name, but something along those lines) which would be NULL if the statement was still in progress and the appropriate timestamp if not. You could infer idle from whether or not that column was NULL. Of course, you might be more interested in those which are idle in a transaction, but that's easily done with these changes -- just throw in xact_start IS NULL. Surely if xact_start is NULL it is not in a transaction at all? ...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] display previous query string of idle-in-transaction
Robert Haas robertmh...@gmail.com wrote: Hmm, I don't think we'd need two columns for this, actually. You could just have one column last_statement_endtime (not sure if it's the best name, but something along those lines) which would be NULL if the statement was still in progress and the appropriate timestamp if not. You could infer idle from whether or not that column was NULL. That would lose the ability to tell what the idle time was before the latest statement began, but maybe that's not interesting enough to justify another column Of course, you might be more interested in those which are idle in a transaction, but that's easily done with these changes -- just throw in xact_start IS NULL. Surely if xact_start is NULL it is not in a transaction at all? That's exactly the point I was trying to make. Sorry if that appeared to be saying anything else. -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] display previous query string of idle-in-transaction
Robert Haas robertmh...@gmail.com writes: Hmm, I don't think we'd need two columns for this, actually. You could just have one column last_statement_endtime (not sure if it's the best name, but something along those lines) which would be NULL if the statement was still in progress and the appropriate timestamp if not. You could infer idle from whether or not that column was NULL. Yeah, but where idle or where not idle is a lot easier to type. I think the extra column is justified on usability grounds. I'm also not entirely convinced that we want last_statement_endtime, because introducing that will cost us an extra kernel call per query in a lot of scenarios. And gettimeofday() is not cheap everywhere. Another question is that this proposal effectively redefines the current_query column as not the current query, but something that might be better be described as latest_query. Should we change the name? We'd probably break some client code if we did, but on the other hand the semantics change might break such code anyway. Intentional breakage might not be such a bad thing if it forces people to take a fresh look at their code. 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] query decorrelation in postgres
Thank you for you response. I was looking for a query rewriting mechanism which would be outside the optimizer and will do this kind of transformations at the query level. ~Mahendra On Fri, Jul 24, 2009 at 7:32 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: mahendra chavan mah...@gmail.com wrote: What I meant by de-correlation was optimizing a query to get rid of sub-queirs by using joins. eg. In the TPC-H schema, a query to find out the names of suppliers who supply parts having size 100 *Query with nested subqueries:* SELECT S_NAME FROM SUPPLIER WHERE S_SUPPKEY IN ( SELECT PS_SUPPKEY FROM PARTSUPP WHERE PS_PARTKEY IN ( SELECT P_PARTKEY FROM PART WHERE P_SIZE 100 ) *Query with joins without subqueries:* SELECT S_NAME FROM SUPPLIER INNER JOIN PARTSUPP ON S_SUPPKEY = PS_SUPPKEY INNER JOIN PART ON P_PARTKEY = PS_PARTKEY WHERE P_SIZE 100 Those two queries aren't exactly identical, because you could get duplicate rows in the second which would not be there in the first. Optimizations to pull up subqueries into a higher level FROM clause as joins have been in PostgreSQL for as long as I've been using it, but the ability to do the specific optimization you show there (without the duplicates) was added in version 8.4 using semi-joins. I don't think any syntax was added to explicitly write a query using semi-joins, though; it's an optimization of EXISTS, IN, ANY, etc. To see how the planner chooses to execute a given query against a particular schema which has a particular set of statistics about the data distributions, use the EXPLAIN option. http://www.postgresql.org/docs/8.4/interactive/sql-explain.html -Kevin
Re: [HACKERS] bytea vs. pg_dump
Bernd Helmle maili...@oopsware.de writes: I've attached a slightly edited patch which fixes a compiler warning in encode.c, too. Bernd, are you done reviewing this or did you intend to do more? It's still marked as needs review on the commitfest page. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] When is a record NULL?
On Fri, 2009-07-24 at 09:31 -0400, Tom Lane wrote: Brendan Jurd dire...@gmail.com writes: It's worse than that, because there actually is also such a thing as the row value being NULL --- ie, there's no row structure at all. At least internally, that's a completely different thing from having a row all of whose fields are null. Here is an example: select * from (values (row(NULL)), (null)) v; But it's hard to do much useful with row values once you have them. You can't even count them: select count(column1) from (values (row(NULL)), (null)) v; ERROR: record type has not been registered SQL doesn't provide a test for this case that's separate from the test involving null-ness of individual fields. Not much we can do about it though. I'm not entirely sure that exposing the distinction would be helpful anyway ... There's no single test, but you can see if it's an empty row with: x IS NULL AND x IS DISTINCT FROM NULL And you can see if it's a real NULL by: x IS NULL AND x IS NOT DISTINCT FROM NULL It's funny until you try it -- then it's just scary. 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] uuid contrib don't compile in OpenSolaris
Hi all, I have some issues to compile uuid contrib of 8.4 version. Touching something i see that the gmake don't find uuid.h. (pfexec gmake -d) Touching more, i add uuid.h into the uuid directory and i had a error message: missing separator. So i google a little and i find something: http://www.cygwin.com/faq/faq.programming.html#faq.programming.make-spaces I read the source and it explain that some issues were fixed into Debian, but other platforms didn't tested yet. If this is really, we are using 8.4 final, and it don't sounds consistent at all. BTW, somebody patched this ? If not, i will continue my workaround on this and try to propose something to fix it. -- Emanuel Calvo Franco www.emanuelcalvofranco.com.ar ArPug.com.ar / aosug.com.ar -- Emanuel Calvo Franco www.emanuelcalvofranco.com.ar ArPug.com.ar / aosug.com.ar -- 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] display previous query string of idle-in-transaction
On Fri, Jul 24, 2009 at 11:15 AM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Hmm, I don't think we'd need two columns for this, actually. You could just have one column last_statement_endtime (not sure if it's the best name, but something along those lines) which would be NULL if the statement was still in progress and the appropriate timestamp if not. You could infer idle from whether or not that column was NULL. Yeah, but where idle or where not idle is a lot easier to type. I think the extra column is justified on usability grounds. I'm also not entirely convinced that we want last_statement_endtime, because introducing that will cost us an extra kernel call per query in a lot of scenarios. And gettimeofday() is not cheap everywhere. I hate redundancy, but I don't care enough to argue about it. Another question is that this proposal effectively redefines the current_query column as not the current query, but something that might be better be described as latest_query. Should we change the name? We'd probably break some client code if we did, but on the other hand the semantics change might break such code anyway. Intentional breakage might not be such a bad thing if it forces people to take a fresh look at their code. +1 for intentional breakage. I like the name, too. ...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] When is a record NULL?
On Fri, Jul 24, 2009 at 09:31:13AM -0400, Tom Lane wrote:
Brendan Jurd dire...@gmail.com writes:
So it is wrong to talk about ROW(NULL, NULL) being NULL. It doesn't
have the property of being NULL or not NULL, because it is a composite
value. ROW(NULL, NULL) IS NULL returns true, but that is not the
same as saying that it actually is NULL, because of the different
semantics above.
It's worse than that, because there actually is also such a thing as
the row value being NULL --- ie, there's no row structure at all.
At least internally, that's a completely different thing from having
a row all of whose fields are null.
Hope nobody minds if I go off on a somewhat pedagogic bent here!
Not quite sure what you mean by the above; but I think maybe something
like:
SELECT NULL::RECORD AS r;
PG may well treat this internally as a special case, but from a type
level I don't see any difference between the above and, say:
SELECT ROW(1)::RECORD AS r;
In both cases we get a result that has exactly one column and this
column is of type RECORD (or ROW depending how you spell things).
The fact that it happens to be a NULL *value* in one case shouldn't
affect things at the level of *types*--unless PG has suddenly become
dependently-typed which I don't believe it wants to be.
I'm also aware that PG's handling of types with ROW values is somewhat
inconsistent when compared to other values, for example:
SELECT (r).a
FROM (
SELECT a,b
FROM (VALUES
(1,2),
(2,3)) x(a,b)) r;
here, we can look inside the RECORD named by r and pull out the value
associated with attribute a, but inside:
SELECT (r).a
FROM (VALUES
(ROW(1,2)),
(ROW(2,3))) x(r);
we get a message saying that the record type has not been registered
when I'd expect to get an error saying that it doesn't know which
attribute a is. We also fail to get an error in the following case:
SELECT r
FROM (VALUES
(ROW(1,2)),
(ROW('a','b'))) x(r);
which (to me) seems wrong. The (allegedly) static types in PG appear
dynamic when it comes to RECORDs.
I'd be interested in fixing this behavior but every time I've started
trying to fix this I've tended to get horribly lost in the code.
SQL doesn't provide a test for this case that's separate from the test
involving null-ness of individual fields. Not much we can do about
it though. I'm not entirely sure that exposing the distinction would
be helpful anyway ...
I think it would; I tend to write the following and have just realized
that it doesn't do what I thought it did:
SELECT a.*
FROM tbla a
LEFT JOIN tblb b ON a.id = b.id
WHERE b IS NULL;
The intuition being that the row valued b would only be considered to
be NULL (i.e. IS NULL returned TRUE) when the LEFT JOIN failed. The
standard way to write this is of course to write WHERE b.id IS NULL,
but I'm unsure why it's necessary to look inside the record b to get
out attribute id to see if it's NULL when it should just be possible
to look at b directly.
--
Sam http://samason.me.uk/
--
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] uuid contrib don't compile in OpenSolaris
Emanuel Calvo Franco escribió: Hi all, I have some issues to compile uuid contrib of 8.4 version. Touching something i see that the gmake don't find uuid.h. (pfexec gmake -d) Touching more, i add uuid.h into the uuid directory and i had a error message: missing separator. Did you use gmake the second time? What was the exact make and compiler error message? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] uuid contrib don't compile in OpenSolaris
Hi all, I have some issues to compile uuid contrib of 8.4 version. Touching something i see that the gmake don't find uuid.h. (pfexec gmake -d) Touching more, i add uuid.h into the uuid directory and i had a error message: missing separator. Did you use gmake the second time? What was the exact make and compiler error message? Every time I execute the gmake, the error is the same (uuid.h not found). WHen i copy uuid.h in the current dir of uuid contrib, the error changes to missing separator. As I say, looking in the web i found that maybe is a bad character (a hidden tab or something like this). This occurs only in opensolaris 200906 (i didn't test on previous versions). -- Emanuel Calvo Franco www.emanuelcalvofranco.com.ar ArPug.com.ar / aosug.com.ar -- 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: plpython3
On Fri, 2009-07-24 at 04:24 -0700, James Pye wrote: I see Python 3 as a good opportunity to change the interfaces and fix the design of the PL. I dunno. I have time to give it some TLC, and I'm not terribly excited about trying to tack features onto something that I find kinda gross. If someone wants to actually take the time to create a better plpython, I say more power to him. It is a bit unfortunate that it is tied explicitly to python 3 but I can see advantages to that as well. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] When is a record NULL?
Sam Mason s...@samason.me.uk wrote: On Fri, Jul 24, 2009 at 09:31:13AM -0400, Tom Lane wrote: Hope nobody minds if I go off on a somewhat pedagogic bent here! Not as long as you don't mind replies in kind. ;-) The fact that it happens to be a NULL *value* in one case Well, according to Codd (and I tend to go with him on this) there is no such thing. NULL is a way to flag a place where a value could be stored, but is not -- because is unknown or is not applicable in that context. (He seemed to feel it was a big weakness of SQL that it didn't differentiate between these two conditions, but that's another argument.) NULL value is an oxymoron. SQL doesn't provide a test for this case that's separate from the test involving null-ness of individual fields. Not much we can do about it though. I'm not entirely sure that exposing the distinction would be helpful anyway ... I think it would The distinction between not having a tuple and having a tuple for which you don't know any applicable values seems thin. I'm not sure what that would really mean. -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] WIP: Deferrable unique constraints
On Wed, 2009-07-22 at 12:25 +0100, Dean Rasheed wrote: OK, here's an updated patch. One thing that Alvaro mentioned that you didn't do yet is use the macro to return from the function (either PG_RETURN_VOID() or PG_RETURN_NULL()). You seem to be following the document here: http://www.postgresql.org/docs/8.4/static/trigger-example.html So I'm not going to hold you up on this issue. It's passed my review, and I'm marking it as such on the commitfest page. Thanks! 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] When is a record NULL?
On Fri, Jul 24, 2009 at 5:15 AM, David E. Wheelerda...@kineticode.com wrote: On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote: Well, a ROW is an ordered set of values, each one of which may be either NULL or NOT NULL. Right. It doesn't really make sense to talk about the ROW itself being NULL or NOT NULL, only its member values (but for extra confusion, contrast with the treatment of arrays, which can themselves be NULL). Well then maybe a record (row) should *never* be null. I disagree, and I think our current way of treating things is incorrect (although harmless). I rowtype can be null: select null::somerowtype; I think the following should _not_ return true: select (null, null)::somerowtype is null; The reasoning being that while the rowtype members are null, the record variable itself is not; these are two distinct cases and should be checked for and treated differently. Another line of reasoning for this is that if something gives 'true' for the is null operator, it should behave as null does, giving null for any operations on it and giving null for STRICT functions, to give a couple of examples. create table foo (a int, b int); select (null, null)::foo is null; ?column? -- t create or replace function doit(foo) returns void as $$ begin raise notice '!'; end; $$ language plpgsql strict; select doit(row(null, null)::foo); NOTICE: ! -- what???!? I think this is wrong, and if the sql standard sez it is so, then the standard is wrong :-). merlin -- 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] When is a record NULL?
On Fri, Jul 24, 2009 at 1:35 PM, Merlin Moncuremmonc...@gmail.com wrote: On Fri, Jul 24, 2009 at 5:15 AM, David E. Wheelerda...@kineticode.com wrote: On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote: Well, a ROW is an ordered set of values, each one of which may be either NULL or NOT NULL. Right. It doesn't really make sense to talk about the ROW itself being NULL or NOT NULL, only its member values (but for extra confusion, contrast with the treatment of arrays, which can themselves be NULL). Well then maybe a record (row) should *never* be null. I disagree, and I think our current way of treating things is incorrect (although harmless). I rowtype can be null: select null::somerowtype; I think the following should _not_ return true: select (null, null)::somerowtype is null; The reasoning being that while the rowtype members are null, the record variable itself is not; these are two distinct cases and should be checked for and treated differently. Another line of reasoning for this is that if something gives 'true' for the is null operator, it should behave as null does, giving null for any operations on it and giving null for STRICT functions, to give a couple of examples. create table foo (a int, b int); select (null, null)::foo is null; ?column? -- t create or replace function doit(foo) returns void as $$ begin raise notice '!'; end; $$ language plpgsql strict; select doit(row(null, null)::foo); NOTICE: ! -- what???!? I think this is wrong, and if the sql standard sez it is so, then the standard is wrong :-). Just to clarify...I think this is the right behavior to run the function that way, but (null,null) is null == true is not correct. There are not 'degrees' of null...something is either null or it isn't. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH v4] [libpq] Try to avoid manually masking SIGPIPEs on every send()
Jeremy Kerr j...@ozlabs.org writes:
Currently, libpq will wrap each send() call on the connection with
two system calls to mask SIGPIPEs. This results in 3 syscalls instead
of one, and (on Linux) can lead to high contention on the signal
mask locks in threaded apps.
We have a couple of other methods to avoid SIGPIPEs:
sockopt(SO_NOSIGPIPE) and the MSG_NOSIGNAL flag to send().
This change attempts to use these if they're available at compile-
and run-time. If not, we drop back to manipulating the signal mask as
before.
Applied with revisions --- those macro definitions were still a mess
:-(. In particular, ({...}) is a gcc-ism.
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] When is a record NULL?
Merlin Moncure mmonc...@gmail.com writes: I think the following should _not_ return true: select (null, null)::somerowtype is null; Take it up with the SQL standards committee. The spec is 100% not ambiguous about this. 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] When is a record NULL?
Jeff Davis pg...@j-davis.com writes: There's no single test, but you can see if it's an empty row with: x IS NULL AND x IS DISTINCT FROM NULL And you can see if it's a real NULL by: x IS NULL AND x IS NOT DISTINCT FROM NULL Hmmm ... that suggests that we may not be handling IS DISTINCT FROM correctly for rowtypes. I haven't read that part of the spec, but I would've guessed that the spec wants it to be consistent with IS NULL. 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] When is a record NULL?
On Jul 24, 2009, at 3:17, Brendan Jurd dire...@gmail.com wrote: ROW(NULL, NULL) IS NULL returns true, but that is not the same as saying that it actually is NULL I think my head just exploded. Loving the dark corners of SQL… 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] When is a record NULL?
On Jul 24, 2009, at 11:10, Tom Lane t...@sss.pgh.pa.us wrote: Hmmm ... that suggests that we may not be handling IS DISTINCT FROM correctly for rowtypes. I haven't read that part of the spec, but I would've guessed that the spec wants it to be consistent with IS NULL. Yes, that's precisely what I was trying to get at last night. 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] When is a record NULL?
On Fri, 2009-07-24 at 13:35 -0400, Merlin Moncure wrote: I think the following should _not_ return true: select (null, null)::somerowtype is null; The reasoning being that It's not at all clear to me that you can reason effectively about SQL logic semantics. Upon which laws are you reasoning? Certainly not 2VL boolean logic. The standard is what it is. If it says that some NULLs are red and some NULLs are blue, then we'd probably support it. 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] When is a record NULL?
On Fri, 2009-07-24 at 14:10 -0400, Tom Lane wrote: Hmmm ... that suggests that we may not be handling IS DISTINCT FROM correctly for rowtypes. I haven't read that part of the spec, but I would've guessed that the spec wants it to be consistent with IS NULL. Our behavior appears to match the standard, which covers this case in 8.15.General Rules.1.c.iv. 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] bytea vs. pg_dump
--On Freitag, Juli 24, 2009 11:38:06 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Bernd, are you done reviewing this or did you intend to do more? It's still marked as needs review on the commitfest page. I hoped to get more profiling data like Andrew suggested, but haven't enough time to do it :( The customer machine i can test on is not available all the time, too. I haven't looked very detailed into the source, if you plan to start a review of your own, feel free. I don't believe i can do very much this weekend... -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] When is a record NULL?
On Fri, Jul 24, 2009 at 2:05 PM, Tom Lanet...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: I think the following should _not_ return true: select (null, null)::somerowtype is null; Take it up with the SQL standards committee. The spec is 100% not ambiguous about this. I'm not proposing to change the current behavior...just playing devil's advocate here. Still, why: *) is select null::foo is distinct from (null, null)::foo; true? (null should not be distinct from null) *) does (null, null) allow STRICT functions to execute? *) is (null, null)::text not null? The upshot of this is that 'is null' really means 'is approximately null', or our handling of the three above cases is wrong. Or put another way, I'd like a clarification: is (null, null) really null (in which case I'd say our handling of it is out of standard), or just a special case for the IS NULL operator, so that it returns null but in all other respect not null? ISTM you can't have it both ways. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Multicore builds on MSVC
Hi! I have a simple one-line patch to enable the use of multiple CPUs or cores when building with MSVC. It is only inside the C compiler itself, but it makes about a 30-35% reduction in compile time on a fairly CPU-limited dual-CPU VM. It would probably do a lot more on for example a quadcore. It pushes the CPU to 100% when building the larger projects like postgres.exe and psql.exe, but obviously stays a lot lower for building all the small single-file projects, since it's only the C compiler that uses it. I can measure no performance decrease on single-core CPUs. Probably because the switch makes the compiler figure out how many CPUs that are available... Since we only support one compiler (Visual Studio 2005) for this, I see the risk of this as very low. The only downside is if you use this on a development machine, it will use up all your CPU whereas previously it used only one core. I haven't even seen normal operations on the machine slow down thouhg... I'm going to apply this for HEAD. I'm considering backpatching as well, to speed up all build machines. Comments on that? -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] [PATCH] user mapping extension to pg_ident.conf
On Wed, Jul 22, 2009 at 8:57 AM, Magnus Hagandermag...@hagander.net wrote: On Wed, Jul 22, 2009 at 14:53, Tom Lanet...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Yup, you would need a protocol change that would allow the client to change its mind about what the username was after it got the auth challenge. And then what effects does that have on username-sensitive pg_hba.conf decisions? We go back and change our minds about the challenge type, perhaps? The whole thing seems like a nonstarter to me. challenge type? Not sure I understand what you are referring to here. The point is that pg_hba.conf allows the selection of auth method to depend on username. What happens if, after being told auth method is (say) Kerberos, the client comes back and wants to use a different username that should have resulted in a different auth method according to pg_hba.conf? It's not hard to construct scenarios where that would be seen as a security breach. Oh. Now I get it. Good point. Forgot about the username being part of that. Yeah, that basicalliy says it has to be a client-side implementation only. I believe this means that this patch is rejected, so I am marking it as such on commitfest.postgresql.org. However, it sounds like there would be room for a client-side patch offering functionality in this area, if Lars or someone else wanted to develop such a thing for a future CommitFest. Hopefully I've understood the situation correctly... ...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] Multicore builds on MSVC
On Fri, Jul 24, 2009 at 8:07 PM, Magnus Hagandermag...@hagander.net wrote: I'm going to apply this for HEAD. I'm considering backpatching as well, to speed up all build machines. Comments on that? Let's see how it goes in the BF for HEAD, and then backpatch if it looks good. I'm keen to get the potential speedup on 8.3 8.4. -- Dave Page EnterpriseDB UK: 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] psql - small fix in \du
On Thursday 23 July 2009 13:29:51 Andreas Wenk wrote: attached you can find an updated patch. The changes are: - change \du and \dg to \du+ and \dg+ in the docu - change the same in psql/help.c - change the translation files in psql/po Fixed, thanks. -- 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] explain refactoring v4
Robert Haas robertmh...@gmail.com writes: OK, here it is again. Changes are the same as the previous version, but this one should apply cleanly after today's pgindent run. As I was poking through this I noticed that it makes at least one small change in the output format: what had been Subquery Scan ss will now be Subquery Scan on ss, because of the unification of code that wasn't really entirely consistent into one subroutine ExplainScanTarget. This is not likely to matter to human readers but it might to programs. OTOH we presumably expect programs to migrate to using a more program-friendly EXPLAIN output format with 8.5. Does anyone have strong feelings about whether we need to be bug-compatible with the old formatting? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
On Thu, Jul 23, 2009 at 09:04, Magnus Hagandermag...@hagander.net wrote: On Thu, Jul 23, 2009 at 08:04, Tsutomu Yamadatsut...@sraoss.co.jp wrote: Hello, Thank you for correcting patch. However, I think the following block have to use VirualFree*Ex*(). (yes, this should never happen, maybe there is actually no problem. but for logical correctness) That is definitely correct. I have updated the patch in my tree and will make sure to include that in the eventual commit. FYI, and others, I have received a couple of off-list reports from people testing out the patch, and so far only positive results. I have applied this patch to HEAD so we can get buildfarm coverage. Holding back on the batckpatch for a bit longer. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Proposal: More portable way to support 64bit platforms
On Friday 26 June 2009 12:07:24 Tsutomu Yamada wrote: Included is a conceptual patch to use intptr_t. Comments are welcome. After closer inspection, not having a win64 box available, I have my doubts whether this patch actually does anything. Foremost, it doesn't touch the definition of the Datum type, which ought to be at the core of a change like this. Now I see that you call this a conceptual patch. Perhaps we should wait until you have developed it into a complete patch? -- 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] When is a record NULL?
On Fri, Jul 24, 2009 at 7:49 PM, Merlin Moncuremmonc...@gmail.com wrote: Still, why: *) is select null::foo is distinct from (null, null)::foo; true? (null should not be distinct from null) *) does (null, null) allow STRICT functions to execute? *) is (null, null)::text not null? These are all good questions. Are you interested in checking what the spec says should happen in these cases? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] SE-PostgreSQL Specifications
On Fri, Jul 24, 2009 at 01:07:54AM -0700, Greg Williamson wrote: Here is the initial draft of SE-PostgreSQL specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft Hey, this is really cool. Think it is a nice introduction. Fixed some of the really obvious language stuff and an example but the English is quite good. One thing I know people are going to ask: why did you use names like create and delete and not the usual names that postgresql itself uses. I suspect the answer is because that's the standard naming used in SELinux, but I think you need to spell that out somewhere. The same for the db_* convention. Do you have a reference to naming conventions for SELinux permissions? I need to think it over some more, but it's a really good start. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] explain refactoring v4
Robert Haas robertmh...@gmail.com writes: OK, here it is again. Changes are the same as the previous version, but this one should apply cleanly after today's pgindent run. Applied with some minor editorialization/further cleanup. I left the Subquery Scan on ss business as-is. If anyone complains it would be an easy thing to suppress the on, but it'd be ugly, and I'm not convinced anyone will care. It's not like it's the first time we ever changed the output of EXPLAIN ... 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] explain refactoring v4
On Fri, Jul 24, 2009 at 4:02 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: OK, here it is again. Changes are the same as the previous version, but this one should apply cleanly after today's pgindent run. As I was poking through this I noticed that it makes at least one small change in the output format: what had been Subquery Scan ss will now be Subquery Scan on ss, because of the unification of code that wasn't really entirely consistent into one subroutine ExplainScanTarget. Wow, nice catch. This is not likely to matter to human readers but it might to programs. OTOH we presumably expect programs to migrate to using a more program-friendly EXPLAIN output format with 8.5. Does anyone have strong feelings about whether we need to be bug-compatible with the old formatting? I kind of doubt it. Based on previous discussions, I gather that PGadmin et al have to be adjusted for each release anyway. But I also don't think it's a big deal if we make it work the way it used to. ...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] explain refactoring v4
On Fri, Jul 24, 2009 at 5:11 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: OK, here it is again. Changes are the same as the previous version, but this one should apply cleanly after today's pgindent run. Applied with some minor editorialization/further cleanup. Thanks. I mostly finished the rework of the generic options patch last night, but I was so sleepy that I couldn't stay up long enough to fully test it. I'll try to get that out tonight, or at the latest tomorrow. Hopefully your minor editorialization and further cleanup won't create too many merge conflicts. I left the Subquery Scan on ss business as-is. If anyone complains it would be an easy thing to suppress the on, but it'd be ugly, and I'm not convinced anyone will care. It's not like it's the first time we ever changed the output of EXPLAIN ... Fine with me either way. ...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] When is a record NULL?
On Fri, Jul 24, 2009 at 4:29 PM, Greg Starkgsst...@mit.edu wrote: On Fri, Jul 24, 2009 at 7:49 PM, Merlin Moncuremmonc...@gmail.com wrote: Still, why: *) is select null::foo is distinct from (null, null)::foo; true? (null should not be distinct from null) *) does (null, null) allow STRICT functions to execute? *) is (null, null)::text not null? These are all good questions. Are you interested in checking what the spec says should happen in these cases? I don't have a copy :-). (Am I being obtuse, and not noticing something obvious?) I think though that one of three cases is true: *) postgresql is not treating (null, null) as null except in the case where checked with 'is null'. *) postgresql is following spec, which basically contradicts itself. *) the behavior is undefined If the first case is true (i bet it is), then many things we do with composite types are wrong on some level, seriously in some cases. for example: select count(rowtype) from foo; select distinct rowtype from foo; select * from foo join bar using (rowtype); can all give the wrong answer. regardless, I think the sql standard definition of null for rowtypes is lame -- the way null works for arrays is much better, in that the elements inside the array an be null, independently can the array itself. I like the current behavior of composites (all composite fields being null is a pretty rare case in practice), and would argue against bringing the database closer to spec if it turns out that we are doing it incorrectly. merin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] contrib/xml2 pfree bug
Didn't we just clean up a mess in our XML handling to do with memory handlers? It looks like contrib/xml2 might have similar problems. Here's the relevant part of a back trace from a core dump: Program terminated with signal 11, Segmentation fault. #0 0x0069300a in pfree () (gdb) bt #0 0x0069300a in pfree () #1 0x00356c42e0ee in xmlCleanupCharEncodingHandlers () from /usr/lib64/libxml2.so.2 #2 0x00356c436675 in xmlCleanupParser () from /usr/lib64/libxml2.so.2 #3 0x2aaab072c5b6 in xslt_process () from /bk//dbinst-84/lib/postgresql/pgxml.so this was generated from the following call (XML afficionados will realise I was trying to pretty print the XML): select xslt_process( cb_ob_invoice_xml(1,1)::text, $$xsl:stylesheet version=1.0 xmlns:xsl=http://www.w3.org/1999/XSL/Transform; xsl:output method=xml indent=yes / xsl:template match=* xsl:copy xsl:copy-of select=@* / xsl:apply-templates / /xsl:copy /xsl:template xsl:template match=comment()|processing-instruction() xsl:copy / /xsl:template /xsl:stylesheet $$::text ); 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] Proposal: More portable way to support 64bit platforms
Peter, * Peter Eisentraut (pete...@gmx.net) wrote: After closer inspection, not having a win64 box available, I have my doubts whether this patch actually does anything. Foremost, it doesn't touch the definition of the Datum type, which ought to be at the core of a change like this. Do you need access to a Win64 box? I can provide you access to a Win64 system, which Dave Page and Magnus already have access to, if it would be useful.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] explain refactoring v4
On Fri, Jul 24, 2009 at 23:11, Robert Haasrobertmh...@gmail.com wrote: On Fri, Jul 24, 2009 at 4:02 PM, Tom Lanet...@sss.pgh.pa.us wrote: This is not likely to matter to human readers but it might to programs. OTOH we presumably expect programs to migrate to using a more program-friendly EXPLAIN output format with 8.5. Does anyone have strong feelings about whether we need to be bug-compatible with the old formatting? I kind of doubt it. Based on previous discussions, I gather that PGadmin et al have to be adjusted for each release anyway. But I also don't think it's a big deal if we make it work the way it used to. They do. Though for now, the old pgadmin still works (AFAIK) against HEAD. But that's usually just a matter of time anyway, and it's not something that's officially supported, AFAIK. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Proposal: More portable way to support 64bit platforms
On Fri, Jul 24, 2009 at 10:35 PM, Stephen Frostsfr...@snowman.net wrote: Peter, * Peter Eisentraut (pete...@gmx.net) wrote: After closer inspection, not having a win64 box available, I have my doubts whether this patch actually does anything. Foremost, it doesn't touch the definition of the Datum type, which ought to be at the core of a change like this. Do you need access to a Win64 box? I can provide you access to a Win64 system, which Dave Page and Magnus already have access to, if it would be useful.. I haven't got round to installing a build env on there yet btw. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Enable SSPI on cygwin
Attached is my patch to enable SSPI on cygwin.
--
Reini Urban
http://phpwiki.org/ http://murbreak.at/
--- origsrc/postgresql-8.4.0/configure.in 2009-06-27 02:14:47.0 +0200
+++ src/postgresql-8.4.0/configure.in 2009-07-02 09:02:25.921875000 +0200
@@ -907,7 +907,11 @@ if test $with_gssapi = yes ; then
AC_SEARCH_LIBS(gss_init_sec_context, [gssapi_krb5 gss 'gssapi -lkrb5 -lcrypto'], [],
[AC_MSG_ERROR([could not find function 'gss_init_sec_context' required for GSSAPI])])
else
-LIBS=$LIBS -lgssapi32
+if test $PORTNAME = cygwin; then
+ LIBS=$LIBS -lsecur32
+else
+ LIBS=$LIBS -lgssapi32
+fi
fi
fi
--- origsrc/postgresql-8.4.0/src/backend/libpq/auth.c 2009-06-25 13:30:08.0 +0200
+++ src/postgresql-8.4.0/src/backend/libpq/auth.c 2009-07-02 09:07:55.93750 +0200
@@ -159,6 +159,9 @@ static krb5_principal pg_krb5_server;
*
*/
#ifdef ENABLE_GSS
+#ifdef __CYGWIN__
+#define WIN32
+#endif
#if defined(HAVE_GSSAPI_H)
#include gssapi.h
#else
--- origsrc/postgresql-8.4.0/src/backend/postmaster/postmaster.c 2009-06-26 22:29:04.0 +0200
+++ src/postgresql-8.4.0/src/backend/postmaster/postmaster.c 2009-07-02 09:02:26.421875000 +0200
@@ -371,6 +371,9 @@ typedef struct
HANDLE PostmasterHandle;
#endif
+#endif
+#ifdef EXEC_BACKEND
+
static pid_t backend_forkexec(Port *port);
static pid_t internal_forkexec(int argc, char *argv[], Port *port);
@@ -442,6 +445,7 @@ static void ShmemBackendArrayAdd(Backend
static void ShmemBackendArrayRemove(Backend *bn);
#endif /* EXEC_BACKEND */
+
#define StartupDataBase() StartChildProcess(StartupProcess)
#define StartBackgroundWriter() StartChildProcess(BgWriterProcess)
#define StartWalWriter() StartChildProcess(WalWriterProcess)
@@ -1142,7 +1146,7 @@ checkDataDir(void)
*
* XXX can we safely enable this check on Windows?
*/
-#if !defined(WIN32) !defined(__CYGWIN__)
+#ifndef WIN32
if (stat_buf.st_uid != geteuid())
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -1164,7 +1168,7 @@ checkDataDir(void)
* be proper support for Unix-y file permissions. Need to think of a
* reasonable check to apply on Windows.
*/
-#if !defined(WIN32) !defined(__CYGWIN__)
+#ifndef WIN32
if (stat_buf.st_mode (S_IRWXG | S_IRWXO))
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
--- origsrc/postgresql-8.4.0/src/include/libpq/libpq-be.h 2009-06-11 16:49:11.0 +0200
+++ src/postgresql-8.4.0/src/include/libpq/libpq-be.h 2009-07-02 09:12:54.203125000 +0200
@@ -47,6 +47,9 @@
#ifdef ENABLE_SSPI
#define SECURITY_WIN32
+#ifdef __CYGWIN__
+#include windows.h
+#endif
#if defined(WIN32) !defined(WIN32_ONLY_COMPILER)
#include ntsecapi.h
#endif
--- origsrc/postgresql-8.4.0/src/include/libpq/libpq.h 2009-01-01 18:23:59.0 +0100
+++ src/postgresql-8.4.0/src/include/libpq/libpq.h 2009-07-02 09:02:26.703125000 +0200
@@ -20,6 +20,10 @@
#include lib/stringinfo.h
#include libpq/libpq-be.h
+#ifdef __CYGWIN__
+#undef WIN32
+#endif
+
/*
* PQArgBlock
* Information (pointer to array of this structure) required
--- origsrc/postgresql-8.4.0/src/include/miscadmin.h 2009-06-11 16:49:08.0 +0200
+++ src/postgresql-8.4.0/src/include/miscadmin.h 2009-07-02 09:02:26.765625000 +0200
@@ -78,7 +78,7 @@ extern PGDLLIMPORT volatile uint32 CritS
/* in tcop/postgres.c */
extern void ProcessInterrupts(void);
-#ifndef WIN32
+#if !defined(WIN32) || defined(__CYGWIN__)
#define CHECK_FOR_INTERRUPTS() \
do { \
--- origsrc/postgresql-8.4.0/src/include/port/cygwin.h 2007-07-25 14:22:53.0 +0200
+++ src/postgresql-8.4.0/src/include/port/cygwin.h 2009-07-02 09:02:26.84375 +0200
@@ -19,3 +19,10 @@
#define PGDLLIMPORT __declspec (dllimport)
#endif
+
+/*
+ * Always build with SSPI support. Keep it as a #define in case
+ * we want a switch to disable it sometime in the future.
+ */
+#define ENABLE_SSPI 1
+
--- origsrc/postgresql-8.4.0/src/interfaces/libpq/Makefile 2009-01-05 10:27:19.0 +0100
+++ src/postgresql-8.4.0/src/interfaces/libpq/Makefile 2009-07-20 13:18:59.296875000 +0200
@@ -63,6 +63,9 @@ endif
ifeq ($(PORTNAME), win32)
SHLIB_LINK += -lshfolder -lwsock32 -lws2_32 -lsecur32 $(filter -leay32 -lssleay32 -lcomerr32 -lkrb5_32, $(LIBS))
endif
+ifeq ($(PORTNAME), cygwin)
+SHLIB_LINK += -lsecur32
+endif
SHLIB_EXPORTS = exports.txt
--- origsrc/postgresql-8.4.0/src/interfaces/libpq/fe-connect.c 2009-06-11 16:49:13.0 +0200
+++ src/postgresql-8.4.0/src/interfaces/libpq/fe-connect.c 2009-07-20 13:14:11.21875 +0200
@@ -22,12 +22,16 @@
#include time.h
#include unistd.h
+#ifdef __CYGWIN__
+#undef WIN32
+#endif
+
#include libpq-fe.h
#include libpq-int.h
#include fe-auth.h
#include pg_config_paths.h
-#ifdef WIN32
+#if defined(WIN32) !defined(__CYGWIN__)
#include win32.h
#ifdef _WIN32_IE
#undef _WIN32_IE
---
Re: [HACKERS] Proposal: More portable way to support 64bit platforms
Dave, * Dave Page (dp...@pgadmin.org) wrote: On Fri, Jul 24, 2009 at 10:35 PM, Stephen Frostsfr...@snowman.net wrote: Do you need access to a Win64 box? I can provide you access to a Win64 system, which Dave Page and Magnus already have access to, if it would be useful.. I haven't got round to installing a build env on there yet btw. Anything we can do to help..? If you can tell us what you'd like installed, I can probably have someone install it, provided it's not horribly complicated. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Enable SSPI on cygwin
On Fri, Jul 24, 2009 at 23:47, Reini Urbanrur...@x-ray.at wrote: Attached is my patch to enable SSPI on cygwin. Is it really such a good idea to keep doing #define WIN32 and #undef WIN32 in multiple places? It seems very fragile to me - we should keep the defines for cygwin and win32 separate. Even if that means that the patch changes a bunch of places into (win32 or cygwin). Either that, or we need to declare a global define somewhere that is WIN32_OR_CYGWIN. Doing it this way makes it much harder to spot what the defines actually mean. IIRC, we discussed that before, and came to a similar conclusion, but I could be wrong. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] When is a record NULL?
On Jul 24, 2009, at 9:40 AM, Jeff Davis wrote: There's no single test, but you can see if it's an empty row with: x IS NULL AND x IS DISTINCT FROM NULL And you can see if it's a real NULL by: x IS NULL AND x IS NOT DISTINCT FROM NULL It's funny until you try it -- then it's just scary. Amen to that. So here's what I'm doing, essentially (some error handling removed for clarity): FETCH have INTO rec_have; FETCH want INTO rec_want; WHILE NOT rec_have IS NULL OR NOT rec_want IS NULL LOOP IF rec_have IS DISTINCT FROM rec_want THEN RETURN false; END IF; rownum = rownum + 1; FETCH have INTO rec_have; FETCH want INTO rec_want; END LOOP; RETURN true; So far this seems to work for the tests I've thrown at it, telling me when two cursors return results that are row-by-row equivalent, including when columns have the NULLs, though I've not yet tried rows that are nothing but nulls. That's probably not very useful, but it is possible. Does this look like it's a reasonable implementation for what I'm testing? Have I missed anything in the swirl of the bizarre that this thread has triggered? Thanks, 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] Enable SSPI on cygwin
Reini,
the style of this patch has previously been criticized by both Magnus
and me.
We do not define WIN32 *anywhere* in our sources, and we don't want to
start. In fact, IIRC we carefully removed all such code years ago,
because it caused us lots of grief.
Please find a way to code your patch that doesn't involve defining and
undefining WIN32.
cheers
andrew
Reini Urban wrote:
Attached is my patch to enable SSPI on cygwin.
--- origsrc/postgresql-8.4.0/configure.in 2009-06-27 02:14:47.0
+0200
+++ src/postgresql-8.4.0/configure.in 2009-07-02 09:02:25.921875000 +0200
@@ -907,7 +907,11 @@ if test $with_gssapi = yes ; then
AC_SEARCH_LIBS(gss_init_sec_context, [gssapi_krb5 gss 'gssapi -lkrb5
-lcrypto'], [],
[AC_MSG_ERROR([could not find function
'gss_init_sec_context' required for GSSAPI])])
else
-LIBS=$LIBS -lgssapi32
+if test $PORTNAME = cygwin; then
+ LIBS=$LIBS -lsecur32
+else
+ LIBS=$LIBS -lgssapi32
+fi
fi
fi
--- origsrc/postgresql-8.4.0/src/backend/libpq/auth.c 2009-06-25 13:30:08.0 +0200
+++ src/postgresql-8.4.0/src/backend/libpq/auth.c 2009-07-02
09:07:55.93750 +0200
@@ -159,6 +159,9 @@ static krb5_principal pg_krb5_server;
*
*/
#ifdef ENABLE_GSS
+#ifdef __CYGWIN__
+#define WIN32
+#endif
#if defined(HAVE_GSSAPI_H)
#include gssapi.h
#else
--- origsrc/postgresql-8.4.0/src/backend/postmaster/postmaster.c
2009-06-26 22:29:04.0 +0200
+++ src/postgresql-8.4.0/src/backend/postmaster/postmaster.c2009-07-02
09:02:26.421875000 +0200
@@ -371,6 +371,9 @@ typedef struct
HANDLE PostmasterHandle;
#endif
+#endif
+#ifdef EXEC_BACKEND
+
static pid_t backend_forkexec(Port *port);
static pid_t internal_forkexec(int argc, char *argv[], Port *port);
@@ -442,6 +445,7 @@ static void ShmemBackendArrayAdd(Backend
static void ShmemBackendArrayRemove(Backend *bn);
#endif /* EXEC_BACKEND */
+
#define StartupDataBase() StartChildProcess(StartupProcess)
#define StartBackgroundWriter() StartChildProcess(BgWriterProcess)
#define StartWalWriter() StartChildProcess(WalWriterProcess)
@@ -1142,7 +1146,7 @@ checkDataDir(void)
*
* XXX can we safely enable this check on Windows?
*/
-#if !defined(WIN32) !defined(__CYGWIN__)
+#ifndef WIN32
if (stat_buf.st_uid != geteuid())
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -1164,7 +1168,7 @@ checkDataDir(void)
* be proper support for Unix-y file permissions. Need to think of a
* reasonable check to apply on Windows.
*/
-#if !defined(WIN32) !defined(__CYGWIN__)
+#ifndef WIN32
if (stat_buf.st_mode (S_IRWXG | S_IRWXO))
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
--- origsrc/postgresql-8.4.0/src/include/libpq/libpq-be.h 2009-06-11
16:49:11.0 +0200
+++ src/postgresql-8.4.0/src/include/libpq/libpq-be.h 2009-07-02
09:12:54.203125000 +0200
@@ -47,6 +47,9 @@
#ifdef ENABLE_SSPI
#define SECURITY_WIN32
+#ifdef __CYGWIN__
+#include windows.h
+#endif
#if defined(WIN32) !defined(WIN32_ONLY_COMPILER)
#include ntsecapi.h
#endif
--- origsrc/postgresql-8.4.0/src/include/libpq/libpq.h 2009-01-01
18:23:59.0 +0100
+++ src/postgresql-8.4.0/src/include/libpq/libpq.h 2009-07-02
09:02:26.703125000 +0200
@@ -20,6 +20,10 @@
#include lib/stringinfo.h
#include libpq/libpq-be.h
+#ifdef __CYGWIN__
+#undef WIN32
+#endif
+
/*
* PQArgBlock
* Information (pointer to array of this structure) required
--- origsrc/postgresql-8.4.0/src/include/miscadmin.h2009-06-11
16:49:08.0 +0200
+++ src/postgresql-8.4.0/src/include/miscadmin.h2009-07-02
09:02:26.765625000 +0200
@@ -78,7 +78,7 @@ extern PGDLLIMPORT volatile uint32 CritS
/* in tcop/postgres.c */
extern void ProcessInterrupts(void);
-#ifndef WIN32
+#if !defined(WIN32) || defined(__CYGWIN__)
#define CHECK_FOR_INTERRUPTS() \
do { \
--- origsrc/postgresql-8.4.0/src/include/port/cygwin.h 2007-07-25
14:22:53.0 +0200
+++ src/postgresql-8.4.0/src/include/port/cygwin.h 2009-07-02
09:02:26.84375 +0200
@@ -19,3 +19,10 @@
#define PGDLLIMPORT __declspec (dllimport)
#endif
+
+/*
+ * Always build with SSPI support. Keep it as a #define in case
+ * we want a switch to disable it sometime in the future.
+ */
+#define ENABLE_SSPI 1
+
--- origsrc/postgresql-8.4.0/src/interfaces/libpq/Makefile 2009-01-05
10:27:19.0 +0100
+++ src/postgresql-8.4.0/src/interfaces/libpq/Makefile 2009-07-20
13:18:59.296875000 +0200
@@ -63,6 +63,9 @@ endif
ifeq ($(PORTNAME), win32)
SHLIB_LINK += -lshfolder
Re: [HACKERS] When is a record NULL?
On Jul 24, 2009, at 11:37 AM, Jeff Davis wrote: Hmmm ... that suggests that we may not be handling IS DISTINCT FROM correctly for rowtypes. I haven't read that part of the spec, but I would've guessed that the spec wants it to be consistent with IS NULL. Our behavior appears to match the standard, which covers this case in 8.15.General Rules.1.c.iv. Good, because what I'm doing is comparing rows returned by two different cursors using IS DISTINCT FROM, and what I need the comparison to do is to ensure that two rows are not equivalent (distinct) unless all of their values are the same, column to column, including NULLs. 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] When is a record NULL?
On Fri, Jul 24, 2009 at 12:30:39PM -0500, Kevin Grittner wrote: Sam Mason s...@samason.me.uk wrote: The fact that it happens to be a NULL *value* in one case Well, according to Codd (and I tend to go with him on this) there is no such thing. NULL is a way to flag a place where a value could be stored, but is not -- because is unknown or is not applicable in that context. (He seemed to feel it was a big weakness of SQL that it didn't differentiate between these two conditions, but that's another argument.) NULL value is an oxymoron. I think then maybe we're talking about different things; I was trying to draw attention to the distinction between types and values---types allow some invariants of the code to be automatically checked before it is run, a value only has meaning at run time with the set of possible values an expression is defined over being constrained by its type. In this dichotomy a NULL is most definitely a value and with my current experience I don't understand the distinction you're trying to draw. SQL doesn't provide a test for this case that's separate from the test involving null-ness of individual fields. Not much we can do about it though. I'm not entirely sure that exposing the distinction would be helpful anyway ... I think it would The distinction between not having a tuple and having a tuple for which you don't know any applicable values seems thin. I'm not sure what that would really mean. Other languages/type systems do define this precisely. For example, in object orientated languages there's a big difference between a reference to an object being NULL and some member of an object being NULL. Databases obviously have their own semantics, but the distinction is well defined. Any implementation that tries to be faithful to a standard has its hands somewhat tied and PG is no exception. PG currently seems to be some hybrid half way between, it internally knows there is a distinction between the two but it doesn't like to expose this. For example (and this appears particularly awkward because of annoying limitations in the syntax PG accepts): SELECT y FROM (SELECT 1) x(a) LEFT JOIN (SELECT 1,2) y(a,b) ON FALSE; I think it should be valid to express this as: SELECT (SELECT 1,2 WHERE FALSE); but PG doesn't like sub-queries returning two columns--but this is material for another discussion. This returns a single row whose only attribute is NULL (i.e. it's rendered as '') and not as a record whose attributes are all NULL (i.e. rendered as '(,)'). The fact PG does the former says that your mental model isn't congruent with PGs behavior. If your model is correct then when the IS DISTINCT FROM operator works on RECORDs the following should return FALSE for all of the following: SELECT NULL IS DISTINCT FROM ROW(NULL); SELECT NULL IS DISTINCT FROM ROW(NULL,NULL); SELECT NULL IS DISTINCT FROM ROW(NULL,ROW(NULL,NULL)); SELECT ROW(NULL) IS DISTINCT FROM ROW(NULL,ROW(NULL,NULL)); i.e. there is *no* difference between a NULL record and a record consisting entirely of NULLs. -- Sam http://samason.me.uk/ -- 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] contrib/xml2 pfree bug
Andrew Dunstan and...@dunslane.net writes: Didn't we just clean up a mess in our XML handling to do with memory handlers? It looks like contrib/xml2 might have similar problems. Yeah, it's using xmlMemSetup(), and being even less careful than the core code was :-(. Do we feel like fixing it, or is it time to rip it out? 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] SE-PostgreSQL Specifications
Martijn van Oosterhout wrote:
On Fri, Jul 24, 2009 at 01:07:54AM -0700, Greg Williamson wrote:
Here is the initial draft of SE-PostgreSQL specifications:
http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft
Hey, this is really cool. Think it is a nice introduction. Fixed some
of the really obvious language stuff and an example but the English is
quite good.
Thanks, but I found an incorrect change at the trusted procedure section.
Old)
CREATE TABLE customer (
cid integer primary key,
cname varchar(32),
credit varchar(32)
- SECURITY_LABEL = 'system_u:object_r:sepgsql_secret_table_t:s0'
- );
New)
CREATE TABLE customer (
cid integer primary key,
cname varchar(32),
credit varchar(32)
+ ) SECURITY_LABEL = 'system_u:object_r:sepgsql_secret_table_t:s0';
This example intends to assign secret label on the credit column,
not whole of the table. Note that the default security context shall
be assigned on the table and rest of columns in this case.
| For example, when the customer table is defined as follows,
| unprivileged users cannot see the contents of customer.credit
| because it is labeled as sepgsql_secret_table_t which means
| all the accesses are denied from confined domains.
Is the description inadequate to note the example tries to assign
a certain security context on the customer.credit column?
BTW, in the later case, the table is labeled as secret, then columns
inherit table's security context in the default, so the table and all
the columns are labeled as secret.
One thing I know people are going to ask: why did you use names like
create and delete and not the usual names that postgresql itself
uses. I suspect the answer is because that's the standard naming used
in SELinux, but I think you need to spell that out somewhere.
The same for the db_* convention. Do you have a reference to naming
conventions for SELinux permissions?
http://oss.tresys.com/repos/refpolicy/trunk/policy/flask/access_vectors
All the object classes managed in userspace object manager have its prefix.
For example, X-window objects has x_* prefix.
Kenel objects don't have any prefix, such as file.
Referring the other base object classes, it uses create, getattr,
setattr, relabelfrom and relabelto commonly. However, a permission
to remove the object itself is named reflecting to the characteristics of
the object.
For example, file:{unlink} and ipc:{destroy} mean a permission to remove
itself. I believe drop is an appropriate naming for database objects.
TODO: add description at the Object classes and permissions why object
classes are prefixed by db_*
I need to think it over some more, but it's a really good start.
Have a nice day,
--
KaiGai Kohei kai...@kaigai.gr.jp
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/xml2 pfree bug
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Didn't we just clean up a mess in our XML handling to do with memory handlers? It looks like contrib/xml2 might have similar problems. Yeah, it's using xmlMemSetup(), and being even less careful than the core code was :-(. Do we feel like fixing it, or is it time to rip it out? Well, we don't have an XSLT processor in core code. If we get one, we should rip this module out from HEAD. But this is a bug in released code - we don't want to rip that out, right? It works OK in some circumstances, but crashing it was trivially easy. 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] SE-PostgreSQL Specifications
KaiGai, * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: Here is the initial draft of SE-PostgreSQL specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft Thanks for this, it really does help, I believe. I've been reviewing it and am also planning on helping refine and improve upon it. I'd like to spend time working on the patch as well but I'm hesitant to commit to that right now due to other obligations. We'll see how it goes. Thanks again, Stephen signature.asc Description: Digital signature
Re: [HACKERS] SE-PostgreSQL Specifications
On Fri, Jul 24, 2009 at 6:35 PM, Stephen Frostsfr...@snowman.net wrote: Thanks for this, it really does help, I believe. I've been reviewing it and am also planning on helping refine and improve upon it. I'd like to spend time working on the patch as well but I'm hesitant to commit to that right now due to other obligations. We'll see how it goes. At this point, I think refining and improving the spec is probably considerably more important than writing the code. ...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] When is a record NULL?
On Jul 24, 2009, at 2:59 PM, David E. Wheeler wrote: FETCH have INTO rec_have; FETCH want INTO rec_want; WHILE NOT rec_have IS NULL OR NOT rec_want IS NULL LOOP IF rec_have IS DISTINCT FROM rec_want THEN RETURN false; END IF; rownum = rownum + 1; FETCH have INTO rec_have; FETCH want INTO rec_want; END LOOP; RETURN true; Bah. It fails to do what I want when I pass cursors that return: VALUES (NULL, NULL), (NULL, NULL) VALUES (NULL, NULL) So when it gets to that second row in the first cursor, it doesn't know it's a row with NULLs as opposed to an empty row. So this bit: WHILE NOT rec_have IS NULL OR NOT rec_want IS NULL LOOP Obviously isn't detecting the difference. I tried WHILE (NOT rec_have IS NULL AND rec_have IS DISTINCT FROM NULL) OR (NOT rec_want IS NULL AND rec_want IS DISTINCT FROM NULL) and WHILE (NOT rec_have IS NULL AND NOT rec_have IS NOT DISTINCT FROM NULL) OR (NOT rec_want IS NULL AND NOT rec_want IS NOT DISTINCT FROM NULL) But they didn't work, either. There's got to be a way to do this; better, there ought to be an easy way to tell the difference. :-( Thanks, 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] Non-blocking communication between a frontend and a backend (pqcomm)
Fujii Masao masao.fu...@gmail.com writes: On Wed, Jul 22, 2009 at 2:20 AM, Robert Haasrobertmh...@gmail.com wrote: Are you planning to update this patch based on Martin's review? Sure. Attached is an updated patch. I looked at this patch. I don't see how we can consider accepting it by itself. It adds a bunch of code that is not used anywhere and hence can't be tested, in service of goals explained nowhere, but presumably part of some other patch that hasn't been reviewed and might or might not get accepted when it is presented. The only thing that's really clear is that it pokes holes in the abstraction (such as it is) presented by pqcomm.c. The reason I want to see the calling code is that I doubt this is a very useful API extension as-is. I can see the point of probing to see if any more bytes are available, but it's not clear that there is a reason to collect only part of a message once the client has sent one. I am also thinking that if you do need the ability to get control back without blocking on the socket, you probably will need that for writes as well as reads; and this patch doesn't cover the write case. I think you should just submit this with the code that uses it, so we can evaluate whether the overall concept is a good one or not. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY WITH CSV FORCE QUOTE * -- REVIEW
Josh Berkus wrote:
Stuff someone else should do:
a. review code
b. review code format
I am done with this review.
I have reviewed this and made a small tweak in the docco. I'm just about
ready to commit this, but I'm still slightly worried that passing NULL
to denote all columns in this piece of grammar:
| FORCE QUOTE '*'
{
$$ = makeDefElem(force_quote, NULL);
}
might be less than robust - it just feels slightly hacky, so I'd
appreciate others' thoughts. If nobody else is bothered I will commit
the patch.
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] COPY WITH CSV FORCE QUOTE * -- REVIEW
Andrew Dunstan and...@dunslane.net writes:
I have reviewed this and made a small tweak in the docco. I'm just about
ready to commit this, but I'm still slightly worried that passing NULL
to denote all columns in this piece of grammar:
| FORCE QUOTE '*'
{
$$ = makeDefElem(force_quote, NULL);
}
might be less than robust - it just feels slightly hacky, so I'd
appreciate others' thoughts.
I agree, that's ugly. Why don't you use an A_Star node?
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] Non-blocking communication between a frontend and a backend (pqcomm)
I wrote: I am also thinking that if you do need the ability to get control back without blocking on the socket, you probably will need that for writes as well as reads; and this patch doesn't cover the write case. Oh, another gripe: I'll bet a nickel that this doesn't work very nicely under SSL. Bytes available on the socket doesn't necessarily equate to decrypted payload bytes being available. Depending on how you're using secure_poll, that might be okay, but it seems like a hazard waiting to trap unwary maintainers. 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] SE-PostgreSQL Specifications
On Sat, Jul 25, 2009 at 07:23:22AM +0900, KaiGai Kohei wrote: Thanks, but I found an incorrect change at the trusted procedure section. Old) CREATE TABLE customer ( cid integer primary key, cname varchar(32), credit varchar(32) - SECURITY_LABEL = 'system_u:object_r:sepgsql_secret_table_t:s0' - ); New) CREATE TABLE customer ( cid integer primary key, cname varchar(32), credit varchar(32) + ) SECURITY_LABEL = 'system_u:object_r:sepgsql_secret_table_t:s0'; This example intends to assign secret label on the credit column, not whole of the table. Note that the default security context shall be assigned on the table and rest of columns in this case. The show_credit() function in this section would seem to leak authority as well; it seems possible to determine if customers exist that otherwise may otherwise hidden. For example, imagine we have a row in the customer table with cid=1 whose security label would normally prevent. We can perform: SELECT show_credit(1); and, as far as I can tell, this call would succeed. -- Sam http://samason.me.uk/ -- 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] COPY WITH CSV FORCE QUOTE * -- REVIEW
Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
I have reviewed this and made a small tweak in the docco. I'm just about
ready to commit this, but I'm still slightly worried that passing NULL
to denote all columns in this piece of grammar:
| FORCE QUOTE '*'
{
$$ = makeDefElem(force_quote, NULL);
}
might be less than robust - it just feels slightly hacky, so I'd
appreciate others' thoughts.
I agree, that's ugly. Why don't you use an A_Star node?
OK, Done and committed. Nice little addition.
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] SE-PostgreSQL Specifications
Sam Mason wrote: On Sat, Jul 25, 2009 at 07:23:22AM +0900, KaiGai Kohei wrote: Thanks, but I found an incorrect change at the trusted procedure section. Old) CREATE TABLE customer ( cid integer primary key, cname varchar(32), credit varchar(32) - SECURITY_LABEL = 'system_u:object_r:sepgsql_secret_table_t:s0' - ); New) CREATE TABLE customer ( cid integer primary key, cname varchar(32), credit varchar(32) + ) SECURITY_LABEL = 'system_u:object_r:sepgsql_secret_table_t:s0'; This example intends to assign secret label on the credit column, not whole of the table. Note that the default security context shall be assigned on the table and rest of columns in this case. The show_credit() function in this section would seem to leak authority as well; it seems possible to determine if customers exist that otherwise may otherwise hidden. For example, imagine we have a row in the customer table with cid=1 whose security label would normally prevent. We can perform: SELECT show_credit(1); and, as far as I can tell, this call would succeed. This example shows that confined client cannot read credit card number without using trusted procedure, but trusted procedure returns masked one. It does not intend to hide existence of entries within customer table. See the third box in the section. postgres=# SELECT cid, cname FROM customer; cid | cname -+--- 10 | jack 13 | adam 14 | liza (3 rows) This query does not read from customer.credit, so it should be succeeded. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Lock Wait Statistics (next commitfest)
Tom Lane wrote: Mark Kirkwood mar...@paradise.net.nz writes: Yeah, enabling log_lock_waits is certainly another approach, however you currently miss out on those that are deadlock_timeout - and potentially they could be the source of your problem (i.e millions of waits all deadlock_timeout but taken together rather significant). This shortcoming could be overcome by making the cutoff wait time decoupled from deadlock_timeout (e.g a new parameter log_min_lock_wait_time or similar). The reason that they're tied together is to keep from creating unreasonable complexity (and an unreasonable number of extra kernel calls) in management of the timeout timers. You will find that you can't just wave your hand and decree that they are now decoupled. Thanks Tom - I did wonder if there was a deeper reason they were tied together! Cheers 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] Proposal: More portable way to support 64bit platforms
On Fri, Jul 24, 2009 at 10:53 PM, Stephen Frostsfr...@snowman.net wrote: Dave, * Dave Page (dp...@pgadmin.org) wrote: On Fri, Jul 24, 2009 at 10:35 PM, Stephen Frostsfr...@snowman.net wrote: Do you need access to a Win64 box? I can provide you access to a Win64 system, which Dave Page and Magnus already have access to, if it would be useful.. I haven't got round to installing a build env on there yet btw. Anything we can do to help..? If you can tell us what you'd like installed, I can probably have someone install it, provided it's not horribly complicated. :) Well, if you have a spare few minutes, VC++ 2005 Express, and the platform SDK would be useful. Thanks. -- Dave Page EnterpriseDB UK: 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] Lock Wait Statistics (next commitfest)
Mark Kirkwood wrote: Jaime Casanova wrote: On Fri, Jul 17, 2009 at 3:38 AM, Mark Kirkwoodmar...@paradise.net.nz wrote: With respect to the sum of wait times being not very granular, yes - quite true. I was thinking it is useful to be able to answer the question 'where is my wait time being spent' - but it hides cases like the one you mention. What would you like to see? would max and min wait times be a useful addition, or are you thinking along different lines? track number of locks, sum of wait times, max(wait time). but actually i started to think that the best is just make use of log_lock_waits send the logs to csvlog and analyze there... Right - I'll look at adding max (at least) early next week. I'm also thinking of taking a look at amalgamating transaction type lock waits. This seems like a good idea because: - individually, and viewed at a later date, I don't think they individual detail is going to be useful - there will be a lot of them - I think the statistical data (count, sum elapsed, max elapsed) may be sufficiently interesting Cheers 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] SE-PostgreSQL Specifications
On Sat, Jul 25, 2009 at 09:16:47AM +0900, KaiGai Kohei wrote: Sam Mason wrote: The show_credit() function in this section would seem to leak authority as well; it seems possible to determine if customers exist that otherwise may otherwise hidden. For example, imagine we have a row in the customer table with cid=1 whose security label would normally prevent. We can perform: SELECT show_credit(1); and, as far as I can tell, this call would succeed. This example shows that confined client cannot read credit card number without using trusted procedure, but trusted procedure returns masked one. It does not intend to hide existence of entries within customer table. This would seem to imply that all user defined trusted code has to perform its own permission checks. How is MAC any different from DAC in the presence of code such as: CREATE OR REPLACE FUNCTION show_customers () RETURNS SETOF RECORD LANGUAGE 'sql' SECURITY_LABEL = 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0' AS 'SELECT * FROM customer'; (I hope I've modified the example correctly!) -- Sam http://samason.me.uk/ -- 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] Non-blocking communication between a frontend and a backend (pqcomm)
On Fri, Jul 24, 2009 at 7:21 PM, Tom Lanet...@sss.pgh.pa.us wrote: Fujii Masao masao.fu...@gmail.com writes: On Wed, Jul 22, 2009 at 2:20 AM, Robert Haasrobertmh...@gmail.com wrote: Are you planning to update this patch based on Martin's review? Sure. Attached is an updated patch. I looked at this patch. I don't see how we can consider accepting it by itself. It adds a bunch of code that is not used anywhere and hence can't be tested, in service of goals explained nowhere, but presumably part of some other patch that hasn't been reviewed and might or might not get accepted when it is presented. The only thing that's really clear is that it pokes holes in the abstraction (such as it is) presented by pqcomm.c. The reason I want to see the calling code is that I doubt this is a very useful API extension as-is. I can see the point of probing to see if any more bytes are available, but it's not clear that there is a reason to collect only part of a message once the client has sent one. I am also thinking that if you do need the ability to get control back without blocking on the socket, you probably will need that for writes as well as reads; and this patch doesn't cover the write case. I think you should just submit this with the code that uses it, so we can evaluate whether the overall concept is a good one or not. This was split out from Synch Rep based on my suggestion to submit separately any parts that are separately committable, but that doesn't seem to be the case given your comments here. I guess the question is whether it's necessary and/or desirable to put in the effort to create a general-purpose facility, or whether we should be satisfied with the minimum level of infrastructure necessary to support Synch Rep and just incorporate it into that patch. Thoughts? ...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] SE-PostgreSQL Specifications
Sam Mason wrote:
On Sat, Jul 25, 2009 at 09:16:47AM +0900, KaiGai Kohei wrote:
Sam Mason wrote:
The show_credit() function in this section would seem to leak authority
as well; it seems possible to determine if customers exist that
otherwise may otherwise hidden. For example, imagine we have a row
in the customer table with cid=1 whose security label would normally
prevent. We can perform:
SELECT show_credit(1);
and, as far as I can tell, this call would succeed.
This example shows that confined client cannot read credit card number
without using trusted procedure, but trusted procedure returns masked one.
It does not intend to hide existence of entries within customer table.
This would seem to imply that all user defined trusted code has to
perform its own permission checks. How is MAC any different from DAC in
the presence of code such as:
CREATE OR REPLACE FUNCTION show_customers () RETURNS SETOF RECORD
LANGUAGE 'sql'
SECURITY_LABEL = 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0'
AS 'SELECT * FROM customer';
(I hope I've modified the example correctly!)
In this case, confined users cannot create a function labeled as
'system_u:object_r:sepgsql_trusted_proc_exec_t:s0', because it is
controlled by db_procedure:{create} permission.
Confined user can create a function with user_sepgsql_proc_exec_t
(which is the default one for confined users), but it is not a trusted
procedure,
so the SELECT * FROM customer is executed with confined user's privileges as
is,
then it will be failed due to the lack of permission on the customer.credit.
Thanks,
--
KaiGai Kohei kai...@kaigai.gr.jp
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] DefaultACLs
On Sun, Jul 19, 2009 at 06:13:32PM +0200, Petr Jelinek wrote: Hello, while writing some basic docs I found bug in dependency handling when doing SET on object type that already had some default privileges. Attached patch fixes it, it also fixes thinko in parser (DROPing GRANT OPTION behaves like REVOKE now). And there is also initial version of those basic docs included (but you have to pardon my english as I didn't pass it to Stephen for proofreading due to discovery of that bug). Am I the only one that gets this on make check, with this version (from src/test/regress/log/initdb.log): selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /home/josh/devel/pgsrc/pg85/src/test/regress/./tmp_check/data/base/1 ... FATAL: relation pg_namespace_default_acl already exists child process exited with exit code 1 initdb: data directory /home/josh/devel/pgsrc/pg85/src/test/regress/./tmp_check/data not removed at user's request -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] WIP: plpython3
On Fri, Jul 24, 2009 at 5:23 AM, James Pyeli...@jwp.name wrote: That also means that maintaining a separate, parallel code base for a Python 3 variant can only be acceptable if it gives major advantages. I'm not particularly interested in Python 3.x support yet (we are still back on 2.4, soon to hop to 2.5 or 2.6. For us 3.1 is probably 2 years away at the earliest). I am interested in improved plpython though. * Reworked function structure (Python modules, not function fragments) I think it would be an improvement to move away from function fragments. One thing I would like to be able to do is have my Python test suite import my plpython and run tests on it. This would be much easier to do if instead of 'import Postgres' to pull in the api, an object was passed into the entry point which provides the interface to PostgreSQL. This way I can pass in a mock object. This is also useful outside of the test suite - the same module can be used as a stored procedure or by your Python application - your web application can use the same validators as your check constraints for instance. The second feature, function structure, is actually new to the PL. Originally PL/Py took a pl/python-like approach to triggers and functions. *Currently*, I want to change procedures to be Python modules with specific entry points used to handle an event. Mere invocation: main. Or, a trigger event: before_insert, after_insert, before_update, etc. So, a regular function might look like: CREATE OR REPLACE FUNCTION foo(int) RETURNS int LANGUAGE plpython3u AS $python$ import Postgres def main(i): return i $python$; Despite the signature repetition, this is an improvement for the user and the developer. The user now has an explicit initialization section that is common to Python(it's a module). The PL developer no longer needs to munge the source, and can work with common Python APIs to manage and introspect the procedure's module(...thinking: procedure settings..). I'd like a way to avoid initialization on module import if possible. Calling an initialization function after module import, if it exists, would do this. CREATE FUNCTION foo(int) RETURNS in LANGUAGE plpythonu AS $python$ [initialization on module import] def pg_init(pg): [initialization after module import] def pg_main(pg, i): return i $python$; Thoughts? [...it still has a *long* ways to go =] I tend to dislike magic function names, but perhaps it is the most usable solution. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] ECPG dynamic cursor, SQLDA support
On Thu, Jun 25, 2009 at 11:18 AM, Michael Meskesmes...@postgresql.org wrote: On Wed, Jun 24, 2009 at 11:51:57AM +0200, Boszormenyi Zoltan wrote: attached is our latest patch extending ECPG: Just as a short explanation, the older versions were sent to me only and I reviewed them. I haven't found time to to review this one yet though. Are you planning to review this one soon? ...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: plpython3
On Jul 24, 2009, at 7:08 PM, Stuart Bishop wrote: I'm not particularly interested in Python 3.x support yet (we are still back on 2.4, soon to hop to 2.5 or 2.6. For us 3.1 is probably 2 years away at the earliest). I am interested in improved plpython though. Two years would hopefully be enough time to work out most of the new bugs. =) This way I can pass in a mock object. This is also useful outside of the test suite - the same module can be used as a stored procedure or by your Python application - your web application can use the same validators as your check constraints for instance. Hmm. import sys sys.modules[Postgres] = mock_pg_module Would that not suffice? I'd like a way to avoid initialization on module import if possible. Calling an initialization function after module import, if it exists, would do this. CREATE FUNCTION foo(int) RETURNS in LANGUAGE plpythonu AS $python$ [initialization on module import] def pg_init(pg): [initialization after module import] def pg_main(pg, i): return i $python$; I do like this idea. However, it may already be possible under the current design with some explicit main() management: CREATE ... $python$ import Postgres def usual(*args): ... def init(*args): global main ... main = usual return usual(*args) main = init $python$; Perhaps ugly, but I imagine a construct could be created to clean it up: CREATE ... $python$ import Postgres def usual(*args): ... def init(*args): ... return usual(*args) main = call_once_then(init, lambda: globals()['main'] = usual) $python$; Hmm, still ugly tho, no? Well, the above examples aren't actually consistent with your design, but perhaps it achieves the desired result? I tend to dislike magic function names, but perhaps it is the most usable solution. Indeed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] proposal: support empty string as separator for string_to_array
Hello
I have one idea, that should simplify string to char array
transformation. The base is idea: between every char is empty string,
so empty string is regular separator for string_to_array function.
This behave is inversion of array_to_string function behave:
postgres=# select array_to_string(array['a','b','c'],'');
array_to_string
-
abc
(1 row)
postgres=# select string_to_array('abc','');
string_to_array
-
{a,b,c}
(1 row)
Notes, ideas???
Regards
Pavel Stehule
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
