Re: [HACKERS] [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
2009/11/25 Jeff Davis pg...@j-davis.com: On Wed, 2009-11-25 at 07:36 +0100, Pavel Stehule wrote: Moving records from a function to a table can be done with: INSERT INTO mytable SELECT * FROM myfunc(); And that already works fine. It works, but COPY FROM myfunc() should be significantly faster. You can skip tuple store. If SRFs use a tuplestore in that situation, it sounds like that should be fixed. Why do we need to provide alternate syntax involving COPY? It isn't problem of SRF function design. It allow both mode - row and tuplestor. This is problem of INSERT statement, resp. INSERT INTO SELECT implementation. Regards Pavel 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] SE-PgSQL patch review
KaiGai Kohei kai...@ak.jp.nec.com wrote: Internal structures http://wiki.postgresql.org/wiki/SEPostgreSQL_Architecture#Interaction_between_pg_security_system_catalog In SELinux model, massive number of objects shares a limited number of security context (e.g more than 100 tables may have a same one), this design (it stores security label OID within the tuple header) is well suitable for database objects. What plan do you have for system columns added by the patch (datsecon, nspsecon, relsecon, etc) after we have securty_id system column? Will we have duplicated features then? Even if system tables don't use security_id columns, should the data type of *secon be oid instead of text? I think pg_security described in the wiki page is useful even if we only have object-level security. How about adding pg_security and changing the type of *secon to oid? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent 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 4/4] Add tests to dblink covering use of COPY TO FUNCTION
On Wed, 2009-11-25 at 09:23 +0100, Pavel Stehule wrote: If SRFs use a tuplestore in that situation, it sounds like that should be fixed. Why do we need to provide alternate syntax involving COPY? It isn't problem of SRF function design. It allow both mode - row and tuplestor. select * from generate_series(1,10) limit 1; That statement takes a long time, which indicates to me that it's materializing the result of the SRF. And there's no insert there. This is problem of INSERT statement, resp. INSERT INTO SELECT implementation. If tmp is a new table, and zero is a table with a million zeros in it, then: insert into tmp select 1/i from zero; fails instantly. That tells me that it's not materializing the result of the select; rather, it's feeding the rows in one at a time. Can show me in more detail what you mean? I'm having difficulty understanding your short replies. 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] SE-PgSQL patch review
Itagaki Takahiro wrote: KaiGai Kohei kai...@ak.jp.nec.com wrote: Internal structures http://wiki.postgresql.org/wiki/SEPostgreSQL_Architecture#Interaction_between_pg_security_system_catalog In SELinux model, massive number of objects shares a limited number of security context (e.g more than 100 tables may have a same one), this design (it stores security label OID within the tuple header) is well suitable for database objects. What plan do you have for system columns added by the patch (datsecon, nspsecon, relsecon, etc) after we have securty_id system column? Will we have duplicated features then? In my plan, these fields will be removed when we add security system column support. Even if system tables don't use security_id columns, should the data type of *secon be oid instead of text? I think pg_security described in the wiki page is useful even if we only have object-level security. How about adding pg_security and changing the type of *secon to oid? The reason why the current version stores security context in plain text is to minimize the scale of changeset as I have been pointed out many times for a long time. :( The pg_security catalog support requires about additional 1KL to the current patch. It seems to me it goes against to the previous suggestions. -- keep it smaller, and step-by-step enhancement BTW, If you don't have any complaints about new syntax in CREATE TABLE statement, I'll revise the patch soon. 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] enable-thread-safety defaults?
2009/11/24 Tom Lane t...@sss.pgh.pa.us: Magnus Hagander mag...@hagander.net writes: ISTM that it should be as simple as the attached patch. Seems to work for me :-) But I'm no autoconf guru, so maybe I missed something? This patch sort of begs the question what about enable-thread-safety-force? That looks even more like a wart now than it did before. Agreed. But how about we try it piece-by-piece, which is we start with this to see if it actually hits any of our bf platforms? -- Magnus Hagander Me: 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] Partitioning option for COPY
On Tue, 2009-11-24 at 10:08 -0500, Emmanuel Cecchet wrote: Itagaki Takahiro wrote: I just edited a wiki page for this discussion. I hope it can be a help. http://wiki.postgresql.org/wiki/Table_partitioning I guess the problem of handling user triggers is still open. If we allow triggers on partitions, badly written logic could lead to infinite loops in routing. In the case of COPY, an after statement trigger could change all the routing decisions taken for each row. A simple update to the row can cause it to move between partitions, no ? I am not sure what the semantic should be if you have triggers defined on the parent and child tables. Which triggers do you fire if the insert is on the parent table but the tuple ends up in a child table? I'd propose that triggers on both parent table and selected child are executed. 1. first you execute before triggers on parent table, which may change which partition the row belongs to 2. then you execute before triggers on selected child table 2.1 if this changes the child table selection repeat from 2. 3. save the tuple in child table 4. execute after triggers of the final selected child table 5. execute after triggers of parent table order of 4. and 5. is selected arbitrarily, others are determined by flow. If the new implementation hides the child tables, If you hide child tables, you suddenly need a lot of new syntax to unhide them, so that partitions can be manipulated. Currently it is easy to do it with INHERIT / NO INHERIT. it might be safer to not allow triggers on child tables altogether and use the parent table as the single point of entry to access the partition (and define triggers). With the current proposed implementation, would it be possible to define a view using child tables? the child tables are there, and they _are_ defined, either implicitly (using constraints, which constraint exclusion resolves to a set of child tables) or explicitly, using child table names directly. -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- Sent 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 4/4] Add tests to dblink covering use of COPY TO FUNCTION
On Tue, 2009-11-24 at 21:13 -0800, Jeff Davis wrote: On Tue, 2009-11-24 at 23:44 -0500, Tom Lane wrote: If you do that, then there is no possibility of ever using this feature except with C-coded functions, which seems to me to remove most of whatever use-case there was. It fits the use case involving dblink (or dblink-like modules). Maybe the patch's performance should be tested with and without copying the buffer, to see if we're losing anything significant. If we can do almost as well copying the data and passing that as a bytea value to the function, then I agree that would be better. I'd make this dependent on funtion signature - if it takes bytea or text, then call it with (binary) rows - if it takes rowtype (of some hypothetic table), then resolve rows to this rowtype I still don't see any reason to force it to be record by record though. If the point is to push data from a table into a remote table, why should the copied data be translated out of binary format into a record, and then back into binary form to send to the remote system? Currently, the second argument to copy is a source or destination of bytes, not records. So forcing it to deal with records is inconsistent. Regards, Jeff Davis -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- Sent 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 4/4] Add tests to dblink covering use of COPY TO FUNCTION
2009/11/25 Jeff Davis pg...@j-davis.com: On Wed, 2009-11-25 at 09:23 +0100, Pavel Stehule wrote: If SRFs use a tuplestore in that situation, it sounds like that should be fixed. Why do we need to provide alternate syntax involving COPY? It isn't problem of SRF function design. It allow both mode - row and tuplestor. select * from generate_series(1,10) limit 1; That statement takes a long time, which indicates to me that it's materializing the result of the SRF. And there's no insert there. This is missing optimalisation. If I understand - PostgreSQL wait for complete result set - so in this case materialisation is necessary. In your query pg do materialisation too early. postgres=# select * from generate_series(1,10) limit 1; generate_series ─ 1 (1 row) Time: 59,540 ms postgres=# select generate_series(1,10) limit 1; generate_series ─ 1 (1 row) Time: 1,107 ms But usually we can process all rows from SRF function - so problem with LIMIT isn't significant. I am testing: 1. postgres=# select count(*) from generate_series(1,100); count ─ 100 (1 row) Time: 930,720 ms 2. postgres=# select count(*) from (select generate_series(1,100)) x; count ─ 100 (1 row) Time: 276,511 ms 2. is significantly faster then 1 (there are not SRF materialisation) postgres=# create table foo(a integer); CREATE TABLE postgres=# insert into foo select generate_series(1,100); INSERT 0 100 Time: 4274,869 ms postgres=# insert into foo select * from generate_series(1,100); INSERT 0 100 Time: 4814,756 ms postgres=# copy foo to '/tmp/xxx'; COPY 100 Time: 1033,078 ms postgres=# set synchronous_commit to off; SET postgres=# copy foo from '/tmp/xxx'; COPY 100 Time: 2749,277 ms postgres=# insert into foo select generate_series(1,100); INSERT 0 100 Time: 3948,734 ms generate_function is fast and simple - but still COPY is about 30% faster This is problem of INSERT statement, resp. INSERT INTO SELECT implementation. If tmp is a new table, and zero is a table with a million zeros in it, then: insert into tmp select 1/i from zero; fails instantly. That tells me that it's not materializing the result of the select; rather, it's feeding the rows in one at a time. I thing, so materialisation is every time, when you use any SQL statement without cursor. Can show me in more detail what you mean? I'm having difficulty understanding your short replies. I thing, so COPY tab from/to fce() should be used for very large import export, where INSERT SELECT needs minimally one materialisation. p.s. I am sorry - I am not native speaker - so I am speaking in short replies. Pavel 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] Hot Standby remaining issues
I've put up a wiki page with the issues I see with the patch as it stands. They're roughly categorized by seriousness. http://wiki.postgresql.org/wiki/Hot_Standby_TODO New issues can and probably will still pop up, let's add them to the list as they're found so that we know what still needs to be done. You had a list of work items at the hot standby main page, but I believe it's badly out-of-date. Please move any still relevant items to the above list, if any. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Why is 0 returned in PQcmdTuples?
Hi, I know it's documented that CREATE TABLE ... AS SELECT ... and SELECT ... INTO TEMP ... returns 0 as number of affected tuples via PQcmdTuples(). Why? How much work would it be to pass this info up? The question arose because in ECPG, such statements like the above returns 0 in sqlca.sqlerrd[2], but Informix returns the number of tuples copied. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby remaining issues
On Wed, 2009-11-25 at 13:00 +0200, Heikki Linnakangas wrote: I've put up a wiki page with the issues I see with the patch as it stands. They're roughly categorized by seriousness. http://wiki.postgresql.org/wiki/Hot_Standby_TODO New issues can and probably will still pop up, let's add them to the list as they're found so that we know what still needs to be done. You had a list of work items at the hot standby main page, but I believe it's badly out-of-date. Please move any still relevant items to the above list, if any. I've linked the two pages together and identified the ones I'm currently working on, plus added a few comments. -- Simon Riggs www.2ndQuadrant.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] garbage in psql -l
On tis, 2009-11-24 at 14:19 -0500, Tom Lane wrote: I wonder whether the most prudent solution wouldn't be to prevent default use of linestyle=unicode if ~/.psqlrc hasn't been read. More generally, it would probably be safer if we used linestyle=unicode only if the client encoding has been set on the client by some explicit action, that is, either via PGCLIENTENCODING or an \encoding statement, but *not* when it is just defaulted from the server encoding. Can we easily detect this difference? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] garbage in psql -l
On ons, 2009-11-25 at 00:14 +, Roger Leigh wrote: For the specific case here, where the locale is KOI8-R, we can determine at runtime that this isn't a UTF-8 locale and stay using ASCII. I'll be happy to send a patch in to correct this specific case. There is already a proposed patch to set the client encoding from the locale, but it is pending on some other API refactoring. The problem at hand, however, is that psql -l happens before the client encoding settings are applied, which should be fixed in any case. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] garbage in psql -l
On tis, 2009-11-24 at 14:19 -0500, Tom Lane wrote: I think you're being overoptimistic to assume that that's going to eliminate the issue. It might patch things for Oleg's particular configuration; but the real problem IMO is that people are depending on ~/.psqlrc to set encoding/locale related behavior, and that file isn't read before executing -l/-c (not to mention -X). The -l/-c case should probably be fixed. If the output contains non-ASCII data, then it's not going to display correctly. Not so much a problem for -l, but definitely for -c, and of course with the Unicode line drawing now in fact also for -l. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tsearch parser inefficiency if text includes urls or emails - new version
On Saturday 14 November 2009 15:33:00 Kevin Grittner wrote: Andres Freund wrote: On Saturday 14 November 2009 01:03:33 Kevin Grittner wrote: It is in context format, applies cleanly, and passes make check. Unfortunately the latter is not saying much - I had a bug there and it was not found by the regression tests. Perhaps I should take a stab and add at least some more... Sounds like a good idea. The one thing to avoid is anything with a long enough run time to annoy those that run it many times a day. Hm. There actually are tests excercising the part where I had a bug... Strange. It was a bug involving uninitialized data so probably the regression tests where just lucky. It is in context format, applies cleanly, and passes make check. Next I read through the code, and have the same question that Andres posed 12 days ago. His patch massively reduces the cost of the parser recursively calling itself for some cases, and it seems like the least invasive way to modify the parser to solve this performance problem; but it does beg the question of why a state machine like this should recursively call itself when it hits certain states. I was wondering about that as well. I am not completely sure but to me it looks like its just done to reduce the amount of rules and states. I'm assuming that's the reason, but didn't dig deep enough to be sure. I suspect to be really sure, I'd have to set it up without the recursion and see what breaks. I can't imagine it would be anything which couldn't be fixed by adding enough states; but perhaps they ran into something where these types would require so many new states that the recursion seemed like the lesser of evils. This is similar to my understanding... I have to say that that code is not exactly clear and well documented... Yeah. I was happy with the level of documentation that you added with your new code, but what was there before is mighty thin. If you gleaned enough information while working on it to feel comfortable adding documentation anywhere else, that would be a good thing. It definitely would be a good thing. But that would definitely be seperate patch. But I fear my current leel of knowledge is sufficient and also I am not sure if I can make myself interested enough in that part. So far the only vote is to proceed with the mitigation, which was my preference, and apparently yours -- so I guess we're at 3 to 0 in favor of that. I'll mark the patch as Waiting on Author so you can add any comments and regression tests you feel are appropriate. By the way, I found one typo in the comments -- it should by useful, not usefull. Ok, will update. I liked what I saw so far, but need to spend more time desk-checking for correctness, testing to confirm that it doesn't change results, and confirming the performance improvement. Thanks again for your reviewing! Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Application name patch - v3
Hi, On Thursday 22 October 2009 15:07:13 Dave Page wrote: Updated patch attached. Per discussion, this: - Changes the envvar name to PGAPPNAME - Removes support for setting application_name in the startup packet, and instead sends an explicit SET command as part of the connection setup in PQconnectPoll. In order to avoid adding to the application-visible connection states, this is overloaded on the CONNECTION_SETENV state which is only used in the v2 protocol at present and seems like an ideal fit for such a similar use. Other features are as per the last version. One more question: Per my reading of the discussion (which very well might be flawed), wasnt the plan to limit the availale characters in the application name to ascii? Greetings, Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Application name patch - v3
On Wed, Nov 25, 2009 at 1:22 PM, Andres Freund and...@anarazel.de wrote: Hi, On Thursday 22 October 2009 15:07:13 Dave Page wrote: Updated patch attached. Per discussion, this: - Changes the envvar name to PGAPPNAME - Removes support for setting application_name in the startup packet, and instead sends an explicit SET command as part of the connection setup in PQconnectPoll. In order to avoid adding to the application-visible connection states, this is overloaded on the CONNECTION_SETENV state which is only used in the v2 protocol at present and seems like an ideal fit for such a similar use. Other features are as per the last version. One more question: Per my reading of the discussion (which very well might be flawed), wasnt the plan to limit the availale characters in the application name to ascii? That was suggested, but I thought the eventual outcome was to not bother. -- 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] Application name patch - v3
On Wednesday 25 November 2009 14:28:14 Dave Page wrote: On Wed, Nov 25, 2009 at 1:22 PM, Andres Freund and...@anarazel.de wrote: Hi, On Thursday 22 October 2009 15:07:13 Dave Page wrote: Updated patch attached. Per discussion, this: - Changes the envvar name to PGAPPNAME - Removes support for setting application_name in the startup packet, and instead sends an explicit SET command as part of the connection setup in PQconnectPoll. In order to avoid adding to the application-visible connection states, this is overloaded on the CONNECTION_SETENV state which is only used in the v2 protocol at present and seems like an ideal fit for such a similar use. Other features are as per the last version. One more question: Per my reading of the discussion (which very well might be flawed), wasnt the plan to limit the availale characters in the application name to ascii? That was suggested, but I thought the eventual outcome was to not bother. Then I dont see any reason to delay any further (sorry!). I personally would prefer making it a bit more strict but it surely is not imporant. Markes as ready for comitter. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] garbage in psql -l
On Tue, Nov 24, 2009 at 22:35, Tom Lane t...@sss.pgh.pa.us wrote: Oleg Bartunov o...@sai.msu.su writes: what's benefit of using linestyle=unicode ? I like old ASCII style for console. Well, I have to grant that it looks pretty spiffy on a unicode-enabled display. Whether that's enough reason to risk breaking things for people with non-unicode-enabled displays is certainly worth debating. Maybe we should just make the default be linestyle=ascii all the time, and tell people to turn it on in their ~/.psqlrc if they want it. FWIW, this patch also *completely* breaks the default windows installs, which will have the database in UTF-8 but there is *never* any UTF-8 support on the console (only UTF-16). So +1 for not making it the default... Or at least have logic based on the *client* to figure out what the default should be, in case that can be made consistent. -- Magnus Hagander Me: 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] Partitioning option for COPY
Hannu Krosing wrote: On Tue, 2009-11-24 at 10:08 -0500, Emmanuel Cecchet wrote: Itagaki Takahiro wrote: I just edited a wiki page for this discussion. I hope it can be a help. http://wiki.postgresql.org/wiki/Table_partitioning I guess the problem of handling user triggers is still open. If we allow triggers on partitions, badly written logic could lead to infinite loops in routing. In the case of COPY, an after statement trigger could change all the routing decisions taken for each row. A simple update to the row can cause it to move between partitions, no ? Yes. I am not sure what the semantic should be if you have triggers defined on the parent and child tables. Which triggers do you fire if the insert is on the parent table but the tuple ends up in a child table? I'd propose that triggers on both parent table and selected child are executed. 1. first you execute before triggers on parent table, which may change which partition the row belongs to 2. then you execute before triggers on selected child table 2.1 if this changes the child table selection repeat from 2. 3. save the tuple in child table 4. execute after triggers of the final selected child table What if that trigger changes again the child table selection? 5. execute after triggers of parent table Same here, what if the trigger changes the child table selection. Do we re-execute triggers on the new child table? Also it is debatable whether we should execute an after trigger on a table where nothing was really inserted. order of 4. and 5. is selected arbitrarily, others are determined by flow. Also the description omits the execution of before and after statement triggers. While those can apply to the parent table (but the same question about what happens if the after statement modifies routing decision still applies), what does it mean in the case of COPY to have statement triggers on the child tables? You cannot know in advance where the tuples are going to go and fire the before statement triggers. If you had to fire after statement triggers, in which order would you fire them? If the new implementation hides the child tables, If you hide child tables, you suddenly need a lot of new syntax to unhide them, so that partitions can be manipulated. Currently it is easy to do it with INHERIT / NO INHERIT. Agreed, but I think that we will discover some restrictions that will apply to child tables. Emmanuel -- Emmanuel Cecchet Aster Data Web: http://www.asterdata.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] Hot Standby and cancelling idle queries
Recent change: An idle-in-transaction transaction can also hold a temporary file. Think of an open cursor, for example. Therefore, remove the distinction between CONFLICT_MODE_ERROR and CONFLICT_MODE_ERROR_IF_NOT_IDLE, idle-in-transaction backends need to be killed too when a tablespace is dropped. Open cursors still have snapshots, so they would not be treated as idle in transaction. If the user has a held cursor then they can keep it, since it has already read the database and released the snapshot. So this change seems both unnecessary and harsh, since even if it is necessary we can work out how to avoid this easily enough. -- Simon Riggs www.2ndQuadrant.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] operator exclusion constraints
On Wed, Nov 25, 2009 at 3:23 AM, Jeff Davis pg...@j-davis.com wrote: I was thinking maybe you call BuildIndexValueDescription twice and make the error message say something like output of first call conflicts with output of second call. Do you really think that's a better error message, or are you just trying to re-use similar code? Let's start from how the error message should read, and then see if we can re-use some code to make it look that way. It's one of the most visible aspects of the feature, and it needs to be reasonably concise and understandable in the simple case, but contain all of the necessary information. I think it's better to avoid the = when describing the conflict. I tend to read it as equals even though it's just punctuation in this case, so it would be distracting. I could change it to a colon, I suppose. I disagree wholeheartedly. :-) My ideal error message is something like: DETAIL: (a, b, c)=(1, 2, 3) conflicts with (a, b, c)=(4, 5, 6) In particular, I think it's very important that we only emit the columns which are part of the operator exclusion constraints, and NOT all the columns of the tuple. The entire tuple could be very large - one of the columns not involved in the constraint could be a 4K block of text, for example, and spitting that out only obscures the real source of the problem. You could argue that one of the columns involved in the constraint could be a 4K block text, too, but in practice I think the constraint columns are likely to be short nine times out of ten. The equals sign is equating the column names to the column values, not the values to each other, so I don't see that as confusing. create table test (a int4[], exclude using gist (a with =)); ERROR: operator does not exist: integer[] = integer[] Thanks, fixed. I am now using compatible_oper_opid(), which will find any operators that don't require binary-incompatible coercion of the operands. Do you think there's any reason to support binary-incompatible coercion of the operands? I can't think of a single use case, and if you really need to, you can coerce the types explicitly in the expression. My operator-class-fu is insufficient to render judgment on this point. I think the thing to do is look at a bunch of non-built-in opclasses and check for POLA violations. ...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-PgSQL patch review
2009/11/24 KaiGai Kohei kai...@ak.jp.nec.com: BTW, I plan the following steps for the row-level security. | * A facility to put security label OID within the tuple header. | * System column support to print out the security context. | (This system column shall be writable to relabel) | * Pure-SQL row-level security checks, something like Oracle Private | Database which allows user defined access control decision function. | * SELinux aware row-level checks on the virtual private database stuff. V It can be implemented as one of the decision making functions. I think we're getting ahead of ourselves talking about row-level security at this point, but FWIW I have a lot of concerns about how the previous version of this feature was designed. In particular, I think we should set up row-level security in a way that (1) allows it to be used for purposes other than SE-Linux and (2) allows row-filtering to take advantage of indices. If I have a table with a million rows, but only rights to see 100 of them, the system administrator should be able to define an index that will allow the 100 I can see to be fetched via a bitmap-index scan rather than a seq-scan with a probe for every row. ...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] Partitioning option for COPY
On Wed, Nov 25, 2009 at 5:03 AM, Hannu Krosing ha...@2ndquadrant.com wrote: I'd propose that triggers on both parent table and selected child are executed. I was thinking we should make the partitioning decision FIRST, before any triggers are fired, and then fire only those triggers relevant to the selected partition. If the BEFORE triggers on the partition modify the tuple in a way that makes it incompatible with the table constraints on that partition, the insert (or update) fails. Firing triggers on more than one table is pretty substantially incompatible with what we do elsewhere and I'm not clear what we get in exchange. What is the use case for this? ...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] Partitioning option for COPY
Robert Haas wrote: On Wed, Nov 25, 2009 at 5:03 AM, Hannu Krosing ha...@2ndquadrant.com wrote: I'd propose that triggers on both parent table and selected child are executed. I was thinking we should make the partitioning decision FIRST, before any triggers are fired, and then fire only those triggers relevant to the selected partition. If the BEFORE triggers on the partition modify the tuple in a way that makes it incompatible with the table constraints on that partition, the insert (or update) fails. Firing triggers on more than one table is pretty substantially incompatible with what we do elsewhere and I'm not clear what we get in exchange. What is the use case for this? I don't have a use case for this but I was puzzled with that when I had to implement trigger support in COPY with partitioning. I came to the same conclusion as you and made the operation fail if the trigger was trying to move the tuple to another partition. However, I had a problem with after row triggers that I had to call synchronously to be able to detect the change. We will need something to tell us that an after row trigger did not mess with the routing decision. Emmanuel -- Emmanuel Cecchet Aster Data Web: http://www.asterdata.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] Partitioning option for COPY
On Wed, Nov 25, 2009 at 9:21 AM, Emmanuel Cecchet m...@asterdata.com wrote: Robert Haas wrote: On Wed, Nov 25, 2009 at 5:03 AM, Hannu Krosing ha...@2ndquadrant.com wrote: I'd propose that triggers on both parent table and selected child are executed. I was thinking we should make the partitioning decision FIRST, before any triggers are fired, and then fire only those triggers relevant to the selected partition. If the BEFORE triggers on the partition modify the tuple in a way that makes it incompatible with the table constraints on that partition, the insert (or update) fails. Firing triggers on more than one table is pretty substantially incompatible with what we do elsewhere and I'm not clear what we get in exchange. What is the use case for this? I don't have a use case for this but I was puzzled with that when I had to implement trigger support in COPY with partitioning. I came to the same conclusion as you and made the operation fail if the trigger was trying to move the tuple to another partition. However, I had a problem with after row triggers that I had to call synchronously to be able to detect the change. We will need something to tell us that an after row trigger did not mess with the routing decision. *scratches head* I'm confused. Only a BEFORE ROW trigger can possibly change anything... the return value of an AFTER ROW trigger is ignored. ...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] garbage in psql -l
Peter Eisentraut pete...@gmx.net writes: On tis, 2009-11-24 at 14:19 -0500, Tom Lane wrote: I think you're being overoptimistic to assume that that's going to eliminate the issue. It might patch things for Oleg's particular configuration; but the real problem IMO is that people are depending on ~/.psqlrc to set encoding/locale related behavior, and that file isn't read before executing -l/-c (not to mention -X). The -l/-c case should probably be fixed. If the output contains non-ASCII data, then it's not going to display correctly. Not so much a problem for -l, but definitely for -c, and of course with the Unicode line drawing now in fact also for -l. I'm not sure that the fix won't be worse than the disease here. The historical behavior is that .psqlrc isn't read before executing -c commands, and I don't find it difficult at all to imagine that changing that will break some people's scripts. 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] garbage in psql -l
Magnus Hagander mag...@hagander.net writes: FWIW, this patch also *completely* breaks the default windows installs, which will have the database in UTF-8 but there is *never* any UTF-8 support on the console (only UTF-16). Hmm ... so how do we work with utf-8 data in the database? That should be the same case AFAICS. 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] garbage in psql -l
On Wed, Nov 25, 2009 at 15:41, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: FWIW, this patch also *completely* breaks the default windows installs, which will have the database in UTF-8 but there is *never* any UTF-8 support on the console (only UTF-16). Hmm ... so how do we work with utf-8 data in the database? That should be the same case AFAICS. It breaks, yes :-), and you have to set the client encoding manually. But it works in the simple case where your data is ASCII for example. Which usually means it's fine for listing databases, or database objects, or such things. But with the unicode line patch, it's basically completely useless, not just partially annoying. -- Magnus Hagander Me: 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] garbage in psql -l
Magnus Hagander mag...@hagander.net writes: On Wed, Nov 25, 2009 at 15:41, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: FWIW, this patch also *completely* breaks the default windows installs, which will have the database in UTF-8 but there is *never* any UTF-8 support on the console (only UTF-16). Hmm ... so how do we work with utf-8 data in the database? That should be the same case AFAICS. It breaks, yes :-), and you have to set the client encoding manually. But it works in the simple case where your data is ASCII for example. Which usually means it's fine for listing databases, or database objects, or such things. But with the unicode line patch, it's basically completely useless, not just partially annoying. Right, so that's the Windows version of the comment I made that people with ASCII-only data have not previously had to be very careful about getting their locale/encoding environment lined up just so. I'm now pretty well convinced that this patch is *not* worth the amount of pain it will cause if it's invoked by default, no matter what restrictions we try to place on that. We should just make the default be linestyle=ascii, period. A logically-separate issue that bears on this is whether to read .psqlrc before doing -l/-c commands. I'm still concerned about that, but I suppose we could tell people to add -X if it breaks things for them. 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] garbage in psql -l
On Wed, November 25, 2009 10:04 am, Tom Lane wrote: I'm now pretty well convinced that this patch is *not* worth the amount of pain it will cause if it's invoked by default, no matter what restrictions we try to place on that. We should just make the default be linestyle=ascii, period. +1 I've thought this all along, to be honest. 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
[HACKERS] cvs chapters in our docs
Attached is a patch which adds a chapter to git in our documentation, around where we have several chapters about cvs today. It also removes a few very out of date comments about cvs (really, nobody has a 28k8 modem and does cvs over it today. Even your cellphone is orders of magnitude faster than that). Other than this patch, I would suggest that we completely remove the following two chapters: http://www.postgresql.org/docs/8.4/static/cvs-tree.html -- because anybody doing anything with branching or tagging today is *not* going to be using cvs, they will be using git. Let's not lead people down the wrong path :-) http://www.postgresql.org/docs/8.4/static/cvsup.html -- does anybody ever use this? It's a complete PITA to get cvsup working on any platform I know of. And since we already allow both rsync and git to get the full repository, there's not much point to it. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ cvs_docs.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Initial refactoring of plperl.c [PATCH]
Following on from my earlier draft plperl.c refactoring patch, here's a
new version that's complete (from my perspective at least).
I've started work on the enhancements to plperl I outlined on pg-general
(in the Wishlist of PL/Perl Enhancements for 8.5 thread).
I have a working implementation of those changes, plus some performance
enhancements, that I'm now re-working into a clean set of tested and
polished patches.
This patch is a first step that doesn't add any extra functionality.
It refactors the internals to make adding the extra functionality
easier (and more clearly visible).
Changes in this patch:
- Changed MULTIPLICITY check from runtime to compiletime.
No loads the large Config module.
- Changed plperl_init_interp() to return new interp
and not alter the global interp_state
- Moved plperl_safe_init() call into check_interp().
- Removed plperl_safe_init_done state variable
as interp_state now covers that role.
- Changed plperl_create_sub() to take a plperl_proc_desc argument.
- Simplified return value handling in plperl_create_sub.
- Added a test for the effect of the utf8fix function.
- Changed perl.com link in the docs to perl.org and tweaked
wording to clarify that require, not use, is what's blocked.
- Moved perl code in large multi-line C string literal macros
out to plc_*.pl files.
- Added a test2macro.pl utility to convert the plc_*.pl files to
macros in a perlchunks.h file which is #included
I'd appreciate any feedback on the patch.
Tim.
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 49631f2..4c26561 100644
*** a/doc/src/sgml/plperl.sgml
--- b/doc/src/sgml/plperl.sgml
***
*** 14,20
para
PL/Perl is a loadable procedural language that enables you to write
productnamePostgreSQL/productname functions in the
!ulink url=http://www.perl.com;Perl programming language/ulink.
/para
para
--- 14,20
para
PL/Perl is a loadable procedural language that enables you to write
productnamePostgreSQL/productname functions in the
!ulink url=http://www.perl.org;Perl programming language/ulink.
/para
para
*** SELECT * FROM perl_set();
*** 298,304
use strict;
/programlisting
in the function body. But this only works in applicationPL/PerlU/
!functions, since literaluse/ is not a trusted operation. In
applicationPL/Perl/ functions you can instead do:
programlisting
BEGIN { strict-import(); }
--- 298,305
use strict;
/programlisting
in the function body. But this only works in applicationPL/PerlU/
!functions, since the literaluse/ triggers a literalrequire/
!which is not a trusted operation. In
applicationPL/Perl/ functions you can instead do:
programlisting
BEGIN { strict-import(); }
diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile
index a3c3495..8989b14 100644
*** a/src/pl/plperl/GNUmakefile
--- b/src/pl/plperl/GNUmakefile
*** PSQLDIR = $(bindir)
*** 45,50
--- 45,55
include $(top_srcdir)/src/Makefile.shlib
+ plperl.o: perlchunks.h
+
+ perlchunks.h: plc_*.pl
+ $(PERL) text2macro.pl --strip='^(\#.*|\s*)$$' plc_*.pl perlchunks.htmp
+ mv perlchunks.htmp perlchunks.h
all: all-lib
*** submake:
*** 65,71
$(MAKE) -C $(top_builddir)/src/test/regress pg_regress$(X)
clean distclean maintainer-clean: clean-lib
! rm -f SPI.c $(OBJS)
rm -rf results
rm -f regression.diffs regression.out
--- 70,76
$(MAKE) -C $(top_builddir)/src/test/regress pg_regress$(X)
clean distclean maintainer-clean: clean-lib
! rm -f SPI.c $(OBJS) perlchunks.htmp perlchunks.h
rm -rf results
rm -f regression.diffs regression.out
diff --git a/src/pl/plperl/expected/plperl.out b/src/pl/plperl/expected/plperl.out
index c8a8fdb..e9f5324 100644
*** a/src/pl/plperl/expected/plperl.out
--- b/src/pl/plperl/expected/plperl.out
*** $$ LANGUAGE plperl;
*** 555,557
--- 555,564
SELECT perl_spi_prepared_bad(4.35) as double precision;
ERROR: type does_not_exist does not exist at line 2.
CONTEXT: PL/Perl function perl_spi_prepared_bad
+ --
+ -- Test compilation of unicode regex
+ --
+ CREATE OR REPLACE FUNCTION perl_unicode_regex(text) RETURNS INTEGER AS $$
+ # see http://rt.perl.org/rt3/Ticket/Display.html?id=47576
+ return ($_[0] =~ /\x{263A}|happy/i) ? 1 : 0; # unicode smiley
+ $$ LANGUAGE plperl;
diff --git a/src/pl/plperl/plc_perlboot.pl b/src/pl/plperl/plc_perlboot.pl
index ...f4739df .
*** a/src/pl/plperl/plc_perlboot.pl
--- b/src/pl/plperl/plc_perlboot.pl
***
*** 0
--- 1,50
+ SPI::bootstrap();
+ use vars qw(%_SHARED);
+
+ sub ::plperl_warn {
+ (my $msg = shift) =~ s/\(eval \d+\) //g;
+ elog(NOTICE, $msg);
+ }
+ $SIG{__WARN__} = \::plperl_warn;
+
+ sub ::plperl_die {
+ (my $msg = shift) =~ s/\(eval \d+\) //g;
+ die $msg;
+ }
+ $SIG{__DIE__} = \::plperl_die;
+
+ sub
Re: [HACKERS] garbage in psql -l
Andrew Dunstan wrote: On Wed, November 25, 2009 10:04 am, Tom Lane wrote: I'm now pretty well convinced that this patch is *not* worth the amount of pain it will cause if it's invoked by default, no matter what restrictions we try to place on that. We should just make the default be linestyle=ascii, period. +1 I've thought this all along, to be honest. +1 from me too - I really prefer to be on the more conservative side and keeping ASCII as the default output seems to be the mroe sensible thing for the time being. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby and cancelling idle queries
Simon Riggs si...@2ndquadrant.com writes: An idle-in-transaction transaction can also hold a temporary file. Think of an open cursor, for example. Therefore, remove the distinction between CONFLICT_MODE_ERROR and CONFLICT_MODE_ERROR_IF_NOT_IDLE, idle-in-transaction backends need to be killed too when a tablespace is dropped. Um ... I think you have forgotten about WITH HOLD cursors, which will also have temp files. Implication: whatever you are thinking of here would require killing EVERY session. Conclusion: you need a different design. 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] Partitioning option for COPY
It seems like the easiest way to resolve this without weird corner cases is to say that we fire triggers belonging to the parent table. The individual partition child tables either shouldn't have triggers at all, or we should restrict the cases in which those are considered applicable. As an example, what are you going to do with statement-level triggers? Fire them for *every* child whether it receives a row or not? Doesn't seem like the right thing. Again, this solution presupposes an explicit concept of partitioned tables within the system... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ALTER TABLE, find_composite_type_dependencies and locking
Hi I'm currently investigating how much work it'd be to implement arrays of domains since I have a client who might be interested in sponsoring that work. The comments around the code handling ALTER DOMAIN ADD CONSTRAINT are pretty clear about the lack of proper locking in that code - altering a domain while simultaneously add a column with that domain as a type might result in inconsistencies between the data in that column and the domain's constraints after both transactions committed. I do, however, suspect that ALTER TABLE is plagued by similar problems. Currently, during the rewrite phase of ALTER TABLE, find_composite_type_dependencies is used to verify that the table's row type (or any type directly or indirectly depending on that type) is not used as a column's type anywhere in the database. But since this code does not take any permanent locks on the visited types, it seems that adding such a column concurrently is not prevented. If the original ALTER TABLE changed a column's type, data inserted into the newly added column before the original ALTER TABLE committed will have a type different from what the catalog says after the original ALTER TABLE commits. Or at least so I think - I haven't yet tested that theory... I am aware that since a commit fest is currently running, now might not be the best time to bring up this topic. Since I feared forgetting this all together, I decided to still post now, though. I figured people still have to option to ignore this for now if they're busy with getting those patches committed. best regards, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
On Tue, Nov 24, 2009 at 10:39 PM, Pavel Stehule pavel.steh...@gmail.com wrote: yes - it is two features - and should be solved independently There are some common problems though. I was thinking about this with some mind towards my existing mental model of thinking of specifying some parameters up-front and getting a stream of records or bytes (depending on what feature you are referring to) as a form of currying, combined with the not-complete revulsion as to using aggregates as a base for such functionality What if we extended aggregates to support a function as an initial condition which could be called with parameters when initializing the aggregate? If you squint at it just right, the current form is that of a value/constant -- effectively the zero-parameter function. Here's a gist of what I want to realize: SELECT (avg())(column_name) FROM ... This is a vanilla average. That's not very interesting since avg only has one default initial value. However, at Truviso we have encountered a real case where we wanted SUM to be initialized to 0 instead of NULL. I had to create a new aggregate with that as an initial condition, which is fine because we only needed one extra standard behavior. But perhaps instead it could have been written this way: SELECT (sum(0))(column_name) FROM ... That way people could get 'zero' rather than NULL when their query yields no rows. You could also imagine some code out there that may have a running-sum of sorts, and may want to seed SUM to some non-zero, non-NULL initial value as set by the application. At that point we may be able to abuse the aggregate infrastructure to doing what we want in the case of these COPY extensions more easily... fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tsearch parser inefficiency if text includes urls or emails - new version
Andres Freund and...@anarazel.de wrote: On Saturday 14 November 2009 15:33:00 Kevin Grittner wrote: Andres Freund wrote: I had a bug there and it was not found by the regression tests. Perhaps I should take a stab and add at least some more... Sounds like a good idea. Hm. There actually are tests excercising the part where I had a bug... Strange. It was a bug involving uninitialized data so probably the regression tests where just lucky. OK. I won't be looking for extra tests. I have to say that that code is not exactly clear and well documented... Yeah. I was happy with the level of documentation that you added with your new code, but what was there before is mighty thin. If you gleaned enough information while working on it to feel comfortable adding documentation anywhere else, that would be a good thing. It definitely would be a good thing. But that would definitely be seperate patch. But I fear my current leel of knowledge is sufficient and also I am not sure if I can make myself interested enough in that part. Fair enough. I won't be looking for new comments for the old code. By the way, I found one typo in the comments -- it should by useful, not usefull. Ok, will update. Given how trivial that is, I'm putting this back in Needs Review status, and resuming my review work. Barring surprises, I should wrap this up whenever I can free up a two or three hours. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] float value is rounded
Hello I was surprised with rounding relative not big value. Is it correct? postgres=# select 499188.546875::real; float4 499189 (1 row) 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
Re: [HACKERS] [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
Jeff Davis pg...@j-davis.com writes: On Wed, 2009-11-25 at 09:23 +0100, Pavel Stehule wrote: If SRFs use a tuplestore in that situation, it sounds like that should be fixed. Why do we need to provide alternate syntax involving COPY? It isn't problem of SRF function design. It allow both mode - row and tuplestor. select * from generate_series(1,10) limit 1; That statement takes a long time, which indicates to me that it's materializing the result of the SRF. Yeah. This is certainly fixable if someone wants to do the legwork of refactoring ExecMakeTableFunctionResult(). It was done that way originally just to keep down the complexity of introducing the function-as-table-source feature at all. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] float value is rounded
Pavel Stehule pavel.steh...@gmail.com writes: I was surprised with rounding relative not big value. Is it correct? You do realize float4 has only 6 decimal digits of precision? 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] float value is rounded
2009/11/25 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I was surprised with rounding relative not big value. Is it correct? You do realize float4 has only 6 decimal digits of precision? To today I though so float4 is more practical :). I am smarter now Pavel 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
Fwd: [HACKERS] Writeable CTE patch
Argh hit the wrong reply button... -- Forwarded message -- From: Alex Hunsaker bada...@gmail.com Date: Wed, Nov 25, 2009 at 10:20 Subject: Re: [HACKERS] Writeable CTE patch To: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi On Mon, Nov 23, 2009 at 14:33, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: Hi, Sorry for the delay, I've been very busy for the last two weeks. Attached is the latest version of the patch. Heh, sorry about my delay. The snapshot update code is still the same, I have no good idea what, if anything, should be done to it. Me neither. In addition to that, I decided to keep the code in ExecutePlan() as it was in the last patch. Fine with me. I think I've taken this patch about as far as I can take it. So I'm going to mark it as ready for commiter. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Add solaris path for docbook COLLATEINDEX
collateindex.pl is stored in /usr/share/sgml/docbook/. Attached fix modify docbook.m4 to find correct path. It would be nice also backported the fix back at least to 8.2. Thanks Zdenek diff -r 2d87758e836b config/docbook.m4 --- a/config/docbook.m4 Sun Nov 22 22:06:30 2009 + +++ b/config/docbook.m4 Wed Nov 25 21:27:09 2009 +0100 @@ -93,7 +93,7 @@ [AC_REQUIRE([PGAC_PATH_DOCBOOK_STYLESHEETS])dnl if test -n $DOCBOOKSTYLE; then AC_PATH_PROGS(COLLATEINDEX, collateindex.pl, [], -[$DOCBOOKSTYLE/bin $PATH]) +[$DOCBOOKSTYLE/bin $DOCBOOKSTYLE/.. $PATH]) else AC_PATH_PROGS(COLLATEINDEX, collateindex.pl) fi])# PGAC_PATH_COLLATEINDEX -- Sent 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 4/4] Add tests to dblink covering use of COPY TO FUNCTION
On Wed, 2009-11-25 at 11:32 +0100, Pavel Stehule wrote: 1. postgres=# select count(*) from generate_series(1,100); count ─ 100 (1 row) Time: 930,720 ms 2. postgres=# select count(*) from (select generate_series(1,100)) x; count ─ 100 (1 row) Time: 276,511 ms 2. is significantly faster then 1 (there are not SRF materialisation) I think case #1 can be fixed. generate_function is fast and simple - but still COPY is about 30% faster My quick tests are not consistent enough, so I will have to try with more data. The times look similar to me so far. If there is a difference, I wonder what it is? I thing, so materialisation is every time, when you use any SQL statement without cursor. I don't think that is true. Here's an expanded version of my previous example: create table zero(i int); create table tmp(j int); insert into zero select 0 from generate_series(1,100); -- all 0 insert into tmp select 1/i from zero; -- error immediately, doesn't wait The error would take longer if it materialized the table zero. But instead, it passes the first tuple to the function for / before the other tuples are read, and gets an error immediately. So no materialization. I worry that we're getting further away from the original problem. Let's allow functions to get the bytes of data from a COPY, like the original proposal. I am not sure COPY is the best mechanism to move records around when INSERT ... SELECT already does that. 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] cvs chapters in our docs
On ons, 2009-11-25 at 16:27 +0100, Magnus Hagander wrote: Attached is a patch which adds a chapter to git in our documentation, around where we have several chapters about cvs today. It also removes a few very out of date comments about cvs I think this whole chapter could be removed and the relevant information added to the web site or the wiki. (Btw., it's spelled Git, not GIT.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] garbage in psql -l
On Wed, Nov 25, 2009 at 09:39:32AM -0500, Tom Lane wrote:
Peter Eisentraut pete...@gmx.net writes:
On tis, 2009-11-24 at 14:19 -0500, Tom Lane wrote:
I think you're being overoptimistic to assume that that's going to
eliminate the issue. It might patch things for Oleg's particular
configuration; but the real problem IMO is that people are depending
on ~/.psqlrc to set encoding/locale related behavior, and that file
isn't read before executing -l/-c (not to mention -X).
The -l/-c case should probably be fixed. If the output contains
non-ASCII data, then it's not going to display correctly. Not so much a
problem for -l, but definitely for -c, and of course with the Unicode
line drawing now in fact also for -l.
I'm not sure that the fix won't be worse than the disease here.
The historical behavior is that .psqlrc isn't read before executing
-c commands, and I don't find it difficult at all to imagine that
changing that will break some people's scripts.
The following patch adds in an nl_langinfo(CODESET) check in
addition to the existing client encoding check. With the
patch applied, unicode table formatting will be enabled only if
these three conditions are met:
1) The system supports nl_langinfo (i.e. is POSIX/SUS)
2) nl_langinfo reports UTF-8 is the locale codeset
3) The client encoding is UTF-8
This will fix the issue that was reported (in a KOI8-R locale,
the nl_langinfo check will fail). It will additionally
fix the problem for Microsoft Windows users; since their
systems don't support nl_langinfo, automatic unicode
support will not be compiled in and so the default will always
be ASCII.
With the patch applied, the only group of users which might
be negatively affected are users with a misconfigured terminal
who additionally meet the above three criteria. However, the
two primary groups of users for whom this was a genuine bug
(non-UTF-8 locale and Windows) would find this patch should
ensure psql will continue default to ASCII output. This will
fix the -l/-c problem for them as well.
Regards,
Roger
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
index 69d5814..8916ffa 100644
--- a/src/bin/psql/print.c
+++ b/src/bin/psql/print.c
@@ -21,6 +21,9 @@
#endif
#include locale.h
+#ifdef HAVE_LANGINFO_H
+#include langinfo.h
+#endif
#include catalog/pg_type.h
#include pqsignal.h
@@ -2552,8 +2555,16 @@ get_line_style(const printTableOpt *opt)
{
if (opt-line_style != NULL)
return opt-line_style;
- else if (opt-encoding == pg_get_utf8_id())
+#if (defined(HAVE_LANGINFO_H) defined(CODESET))
+ /* If a UTF-8 locale is available, and the client encoding is
+* also UTF-8, switch to UTF-8 box drawing characters
+*/
+ else if ((pg_strcasecmp(nl_langinfo(CODESET), UTF-8) == 0 ||
+ pg_strcasecmp(nl_langinfo(CODESET), utf8) == 0 ||
+ pg_strcasecmp(nl_langinfo(CODESET), CP65001) == 0)
+opt-encoding == pg_get_utf8_id())
return pg_utf8format;
+#endif
else
return pg_asciiformat;
}
--
.''`. Roger Leigh
: :' : Debian GNU/Linux http://people.debian.org/~rleigh/
`. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/
`-GPG Public Key: 0x25BFB848 Please GPG sign your mail.
signature.asc
Description: Digital signature
[HACKERS] Spontaneous PITR standby activiation
Last night we had the standby server in a PITR setup seemingly spontaneously believe it had recovered completely and startup a new timeline; I'm running this information by you guys to see if what had happened is considered a bug, or if there is a simple explanation. So some questions here: 1) is there a hard limit of the number of times the archive_command will attempt? I didn't see anything documented about this in the PITR or config docs, so I'm guessing the 10 failures I saw in the log were just coincidental. 2) are the archive_command failures in the master's log responsible for the redo records? 3) would a Pg forced shutdown cause issues with the generated WAL when replaying? 4) at first I thought it had to do with a bug/failure in pg_standby, but I'm wondering if it has to do with the record with zero length referenced in the standby's logs. Thoughts? Details: The postgres partition had completely filled up on the master, causing the postmaster to shut down. We removed some cruft from the partition and were able to get the server started back up at this time. When checking on the standby server, I noticed that it had switched out of recovery mode and was archiving its own WAL, etc, and was perfectly connectable from the command line. The archive_command is: cp -i %p /mnt/pg_wal_archive/%f cat %p | ssh backupserver cat - /mnt/pg_wal_archive/.%f; if test -e /mnt/pg_wal_archive/%f ; then rm /mnt/pg_wal_archive/.%f ; exit 1; else mv -f /mnt/ pg_wal_archive/.%f /mnt/pg_wal_archive/%f ; fi The recovery.conf file is a fairly vanilla pg_standby invocation: restore_command = '/usr/lib/postgresql/8.3/bin/pg_standby -c -d -s 2 -t /tmp/pgsql.trigger. /db_data/pg_wal_archive %f %p %r 2 / db_data/pg_log/warmstandby.log' Some possibly relevant settings from the master: name | setting --+--- archive_mode | on archive_timeout | 60 checkpoint_completion_target | 0.7 checkpoint_segments | 100 checkpoint_timeout | 600 checkpoint_warning | 30 commit_delay | 0 commit_siblings | 5 fsync| on full_page_writes | on synchronous_commit | on wal_buffers | 128 wal_sync_method | fdatasync wal_writer_delay | 200 Relevant lines from the standby: 2009-11-25 04:03:52 UTC [6315]: [7478-1] LOG: restored log file 000206420001 from archive 2009-11-25 04:03:58 UTC [6315]: [7479-1] LOG: restored log file 000206420002 from archive 2009-11-25 04:44:09 UTC [6315]: [7480-1] LOG: restored log file 000206420003 from archive 2009-11-25 04:45:22 UTC [6315]: [7481-1] LOG: record with zero length at 642/3FFEB38 2009-11-25 04:45:22 UTC [6315]: [7482-1] LOG: redo done at 642/3FFEAF0 2009-11-25 04:45:22 UTC [6315]: [7483-1] LOG: last completed transaction was at log time 2009-11-25 04:04:03.067187+00 2009-11-25 04:45:22 UTC [6315]: [7484-1] LOG: restored log file 000206420003 from archive 2009-11-25 04:45:28 UTC [6315]: [7485-1] LOG: selected new timeline ID: 3 2009-11-25 04:45:28 UTC [6315]: [7486-1] LOG: restored log file 0002.history from archive 2009-11-25 04:45:28 UTC [6315]: [7487-1] LOG: archive recovery complete 2009-11-25 04:45:28 UTC [6315]: [7488-1] LOG: checkpoint starting: shutdown immediate 2009-11-25 04:45:29 UTC [6315]: [7489-1] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 14 recycled; write=0.009 s, sync=0.010 s, total=0.404 s 2009-11-25 04:45:30 UTC [22614]: [1-1] LOG: autovacuum launcher started 2009-11-25 04:45:30 UTC [6313]: [1-1] LOG: database system is ready to accept connections 2009-11-25 04:55:30 UTC [22612]: [1-1] LOG: checkpoint starting: time From the master: aws-oi-db-master-11192009:/var/log/postgresql# date Wed Nov 25 14:40:44 EST 2009 aws-oi-db-master-11192009:/var/log/postgresql# grep WARNING postgresql-2009-11-25.log 2009-11-25 00:00:34 EST [30401]: [126-1] WARNING: transaction log file 000206420003 could not be archived: too many failures 2009-11-25 00:01:36 EST [30401]: [133-1] WARNING: transaction log file 000206420003 could not be archived: too many failures 2009-11-25 00:02:34 EST [30401]: [140-1] WARNING: transaction log file 000206420003 could not be archived: too many failures 2009-11-25 00:03:34 EST [30401]: [147-1] WARNING: transaction log file 000206420003 could not be archived: too many failures 2009-11-25 00:04:34 EST [30401]: [154-1] WARNING: transaction log file 000206420003 could not be archived: too many failures 2009-11-25 00:05:34 EST [30401]: [161-1] WARNING: transaction log file 000206420003 could not be archived: too many failures
Re: [HACKERS] cvs chapters in our docs
On Wed, Nov 25, 2009 at 22:07, Peter Eisentraut pete...@gmx.net wrote: On ons, 2009-11-25 at 16:27 +0100, Magnus Hagander wrote: Attached is a patch which adds a chapter to git in our documentation, around where we have several chapters about cvs today. It also removes a few very out of date comments about cvs I think this whole chapter could be removed and the relevant information added to the web site or the wiki. (Btw., it's spelled Git, not GIT.) Completely, or replaced with a reference to pages on the web/wiki? I don't disagree - if people are fine with that, it sounds good to me. -- Magnus Hagander Me: 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] Application name patch - v3
Dave Page dp...@pgadmin.org writes: On Wed, Nov 25, 2009 at 1:22 PM, Andres Freund and...@anarazel.de wrote: One more question: Per my reading of the discussion (which very well might be flawed), wasnt the plan to limit the availale characters in the application name to ascii? That was suggested, but I thought the eventual outcome was to not bother. I think that's really essential, not optional. The proposed patch will transfer the application name from one backend to another without any encoding conversion. If it contains non-ASCII characters that will result in injection of badly-encoded data inside the backend, which is something we have been trying hard to avoid in recent versions. The only other thing you could do about this would be to try to convert the data from the source backend's encoding to the target's. Which would lead to assorted failure scenarios when no conversion is possible. ISTM restricting the name to ASCII-only is the most reasonable tradeoff. Of course, as a speaker of English I may be a bit biased here --- but doing nothing about the issue doesn't seem acceptable. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] force index problem in 8.4.1
Hi there, I tried to force query to use index by specifying high execution cost, but without success, even seqscan cost doesn't changed. This is 8.4.1 =# explain select count(*) from spots where coordinates @ '(0,0),(0.1,0.1)'::box; QUERY PLAN Aggregate (cost=26620.84..26620.85 rows=1 width=0) - Seq Scan on spots (cost=0.00..25328.12 rows=517085 width=0) Filter: (coordinates @ '(0.1,0.1),(0,0)'::box) (3 rows) Time: 1.944 ms =# alter FUNCTION pt_contained_box(point, box) COST 1; ALTER FUNCTION Time: 369.800 ms =# explain select count(*) from spots where coordinates @ '(0,0),(0.1,0.1)'::box; QUERY PLAN Aggregate (cost=26620.84..26620.85 rows=1 width=0) - Seq Scan on spots (cost=0.00..25328.12 rows=517085 width=0) Filter: (coordinates @ '(0.1,0.1),(0,0)'::box) (3 rows) Time: 0.891 ms Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Deleted WAL files held open by backends in Linux
I ran across the subject issue and spent some time puzzling over it. It seemed strange that the only backends which were holding open deleted WAL files were ones where the connection was established with a login which has no write permissions. Eventually, I vaguely recalled there was such an issue discussed in recent months and found it in the archives. It was this: http://archives.postgresql.org/pgsql-bugs/2009-09/msg00144.php As a DBA I feel that it's irritating and a potential time waster. I guess it is a stretch to imagine that a database would have enough read-only connections to exhaust resources by holding open one deleted WAL file each; unless they have, say, 200 such connections and they're cutting things so close that a wasted 3.2GB of disk space at the WAL file location will run them out. I'm not sure whether Tom's comment that There is zero hope of making that work. referred to the idea that we could close deleted WAL files or to something else. Is a fix feasible? This was observed on: PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (SUSE Linux) SUSE Linux Enterprise Server 10 (x86_64) VERSION = 10 PATCHLEVEL = 2 Linux version 2.6.16.60-0.39.3-smp (ge...@buildhost) (gcc version 4.1.2 20070115 (SUSE Linux)) #1 SMP Mon May 11 11:46:34 UTC 2009 -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] Partitioning option for COPY
On Wed, 2009-11-25 at 08:39 -0500, Emmanuel Cecchet wrote: Hannu Krosing wrote: On Tue, 2009-11-24 at 10:08 -0500, Emmanuel Cecchet wrote: Itagaki Takahiro wrote: I just edited a wiki page for this discussion. I hope it can be a help. http://wiki.postgresql.org/wiki/Table_partitioning I guess the problem of handling user triggers is still open. If we allow triggers on partitions, badly written logic could lead to infinite loops in routing. In the case of COPY, an after statement trigger could change all the routing decisions taken for each row. A simple update to the row can cause it to move between partitions, no ? Yes. I am not sure what the semantic should be if you have triggers defined on the parent and child tables. Which triggers do you fire if the insert is on the parent table but the tuple ends up in a child table? I'd propose that triggers on both parent table and selected child are executed. 1. first you execute before triggers on parent table, which may change which partition the row belongs to 2. then you execute before triggers on selected child table 2.1 if this changes the child table selection repeat from 2. 3. save the tuple in child table 4. execute after triggers of the final selected child table What if that trigger changes again the child table selection? 5. execute after triggers of parent table Same here, what if the trigger changes the child table selection. Do we re-execute triggers on the new child table? After triggers can't change tuple, thus cant change routing. Also it is debatable whether we should execute an after trigger on a table where nothing was really inserted. order of 4. and 5. is selected arbitrarily, others are determined by flow. Also the description omits the execution of before and after statement triggers. While those can apply to the parent table (but the same question about what happens if the after statement modifies routing decision still applies), what does it mean in the case of COPY to have statement triggers on the child tables? What statement triggers do you mean ? I don't think we have ON COPY triggers ? You cannot know in advance where the tuples are going to go and fire the before statement triggers. If you had to fire after statement triggers, in which order would you fire them? If the new implementation hides the child tables, If you hide child tables, you suddenly need a lot of new syntax to unhide them, so that partitions can be manipulated. Currently it is easy to do it with INHERIT / NO INHERIT. Agreed, but I think that we will discover some restrictions that will apply to child tables. I think we should keep the possibility to populate partitions offline and then plug then into table as partitions (current INHERIT) and also to extract partition into separate table (NO INHERIT). -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning option for COPY
On Wed, 2009-11-25 at 11:30 -0500, Tom Lane wrote: It seems like the easiest way to resolve this without weird corner cases is to say that we fire triggers belonging to the parent table. The individual partition child tables either shouldn't have triggers at all, or we should restrict the cases in which those are considered applicable. Agreed. maybe allow only ROW-level AFTER triggers (for logging late arrivals and updates on tables partitioned on time for example ) As an example, what are you going to do with statement-level triggers? Fire them for *every* child whether it receives a row or not? Doesn't seem like the right thing. Again, this solution presupposes an explicit concept of partitioned tables within the system... For explicit partitioned tables with hidden partitions it is of course best to not add extra effort for allowing triggers to be defined on those (hidden) partitions. If the partition tables are visible, some trigger support would be good. regards, tom lane -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning option for COPY
On Wed, Nov 25, 2009 at 11:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: It seems like the easiest way to resolve this without weird corner cases is to say that we fire triggers belonging to the parent table. The individual partition child tables either shouldn't have triggers at all, or we should restrict the cases in which those are considered applicable. As an example, what are you going to do with statement-level triggers? Fire them for *every* child whether it receives a row or not? Doesn't seem like the right thing. Just the tables that get a row? I don't know, your way may be best, but it seems like tables on individual partitions might be useful in some situations. Again, this solution presupposes an explicit concept of partitioned tables within the system... ...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] operator exclusion constraints
On Wed, 2009-11-25 at 09:02 -0500, Robert Haas wrote: I disagree wholeheartedly. :-) My ideal error message is something like: DETAIL: (a, b, c)=(1, 2, 3) conflicts with (a, b, c)=(4, 5, 6) In particular, I think it's very important that we only emit the columns which are part of the operator exclusion constraints, and NOT all the columns of the tuple. The entire tuple could be very large - one of the columns not involved in the constraint could be a 4K block of text, for example, and spitting that out only obscures the real source of the problem. You could argue that one of the columns involved in the constraint could be a 4K block text, too, but in practice I think the constraint columns are likely to be short nine times out of ten. The equals sign is equating the column names to the column values, not the values to each other, so I don't see that as confusing. Ok, fair enough. But how do you feel about: (a: 1, b: 2, c: 3) as a tuple representation instead? I think it's closer to the way people expect a tuple to be represented. I can change it in one place so that the unique violations are reported that way, as well (as long as there are no objections). It still doesn't contain the operators, but they can look at the constraint definition for that. My operator-class-fu is insufficient to render judgment on this point. I think the thing to do is look at a bunch of non-built-in opclasses and check for POLA violations. Ok, I'll consider this more. 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] Application name patch - v3
On Wednesday 25 November 2009 23:01:35 Tom Lane wrote: Dave Page dp...@pgadmin.org writes: On Wed, Nov 25, 2009 at 1:22 PM, Andres Freund and...@anarazel.de wrote: One more question: Per my reading of the discussion (which very well might be flawed), wasnt the plan to limit the availale characters in the application name to ascii? That was suggested, but I thought the eventual outcome was to not bother. I think that's really essential, not optional. The proposed patch will transfer the application name from one backend to another without any encoding conversion. If it contains non-ASCII characters that will result in injection of badly-encoded data inside the backend, which is something we have been trying hard to avoid in recent versions. Isn't that similarly the case with pg_stat_activity? ISTM restricting the name to ASCII-only is the most reasonable tradeoff. Of course, as a speaker of English I may be a bit biased here --- but doing nothing about the issue doesn't seem acceptable. I actually having a hard time imaging a use case where this would be a real problem... I have to admit though that while I am not from a English speaking country but from Germany the amount of non ASCII chars used there routinely is not that big, so ... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Jeff Davis pg...@j-davis.com writes: On Wed, 2009-11-25 at 09:02 -0500, Robert Haas wrote: I disagree wholeheartedly. :-) My ideal error message is something like: DETAIL: (a, b, c)=(1, 2, 3) conflicts with (a, b, c)=(4, 5, 6) Ok, fair enough. But how do you feel about: (a: 1, b: 2, c: 3) as a tuple representation instead? This seems like change for the sake of change. We've been reporting this type of error (in the context of foreign keys) using the first syntax for a very long time. I don't feel a need to rearrange it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] float value is rounded
On Wed, Nov 25, 2009 at 07:46:11PM +0100, Pavel Stehule wrote: 2009/11/25 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I was surprised with rounding relative not big value. Is it correct? You do realize float4 has only 6 decimal digits of precision? To today I though so float4 is more practical :). Floats have never been safe with respect to operations like =, and won't become so. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Application name patch - v3
Andres Freund and...@anarazel.de writes: On Wednesday 25 November 2009 23:01:35 Tom Lane wrote: I think that's really essential, not optional. The proposed patch will transfer the application name from one backend to another without any encoding conversion. If it contains non-ASCII characters that will result in injection of badly-encoded data inside the backend, which is something we have been trying hard to avoid in recent versions. Isn't that similarly the case with pg_stat_activity? Well, we do still have some un-plugged holes there, but that's not an excuse for adding more. 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] force index problem in 8.4.1
Oleg Bartunov o...@sai.msu.su writes: I tried to force query to use index by specifying high execution cost, but without success, even seqscan cost doesn't changed. This is 8.4.1 I think you altered the wrong function. The function underlying point @ box is on_pb() not pt_contained_box ... in fact, I don't even see a function named that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PgSQL patch review
KaiGai Kohei kai...@kaigai.gr.jp wrote: -- keep it smaller, and step-by-step enhancement I'd prefer smaller concept rather than smaller patch. I think the philosophy of SE-PgSQL itself is ok, but there are some issues in the design and implementation: No interaction with existing features * SE-PgSQL injects security-context-based access control, but there are no interaction between it and the existing role-based access control. * SE-PgSQL introduces concept of security context, but there are no interaction between it and the existing context-related features. (ex. pg_hba.conf and Application name patch) This is just an idea, but how about implementing context-based access control based on role-based ACL? We will not use security context directly for access control, but it forbid to use ROLEs in some conditions. An example of implementation image is: =# ALTER ROLE role VALID ON SECURITY CONTEXT '...' For example, this could allow us to modify rows only with a particular application, only from particular machine, and only in particular hour. Since we've already supported object- and column-level ACL, I think we can the same capability of the patch using security-context-to-role mapper. Or, is it not ideal in the policy of SELinux? Postgres is not prepared to receive SE-PgSQL We depend on superuser too heavily. As KaiGai-san mentioned, we use if (superuser()) instead of ACL in some places. It is a bad manner. We should centralize access control in one module (maybe aclcheck.c), and SE-PgSQL should be implemented only in the module. If possible, it might be good for SE-PgSQL to replace all of the role-based access control layer in postgres because it is hard for users to maintain both Postgres ROLEs and SELinux settings consistently. Do we need pluggable ACL layer before accepting SE-PgSQL? Need to reconsider row-level security control Row-level security control is excluded from the patch, but we'd better also considering it at first. You mentioned as: In SELinux model, massive number of objects shares a limited number of security context (e.g more than 100 tables may have a same one) but I'm not sure what application do you suppose. For example, if we protect web application from SQL injection attacks, the password column for each row can be read only from the end user represented by the row. The number of security labels will be same as the number of end users (= rows). Actual benefits of SE-PgSQL SE-PgSQL will be committed step-by-step -- but could you explain which step can solve which problem in the real world? Imagine that SQL injections, measure for SOX Act, divulgation of personal information, They are security holes in terms of a whole application, but not a hole in terms of database, because database cannot always distinguish between legal and illegal data access (ex. correction of wrong data vs. rigging of benefits). Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timezones (in 8.5?)
hernan The support of timezones is really crippled hernan now. Crippled how? Well, among other things, no builtin date-timetype allows me to save the timezone (or even the offset). No type allows to treat this three datetimes as different values. '2010-07-27 10:30 GMT+4' '2010-07-27 09:30 GMT+5' '2010-07-27 10:30 GMT+0' The ANSI spec at least permits that. The example you gave is easily handled in pg as follows: Well, using compound types one can handle practically everything... My point is expressiveness. Basic datatypes should ideally correspond to the most typical data that one which to store/retrive/manipulate in a DB. And my claim is that most date-time values found in real life can be neatly classified in the types I mentioned (basically: physical instants of time, or civil date-times), and that they should not be confused. Hence, for example an operation as [TIMESTAMP] + 1 MONTH should not be allowed (incompatible types). Hence, when I ask PG store the datetime 2010-Jul-27, 10:30:00 (at TZ Chile/Santiago), it should not do (as today) ok, let me check the zic table for that TZ... aha, offset +4, so you meant the UTC time 2010-Jul-27 14:30:00 ...saved But I didn't mean that, I meant what I said (a civil date). The bridging (conversion to physical time) should only be made when (if) needed. I also claim, BTW, that the DB should never rely on its local TZ. If some SQL query (eg: select all orders confirmed in January) can return different sets by changing the TZ of the DB server, something is wrong. If you're writing a calendaring app that wants to allow storing both kinds of events (I've yet to see such an app that actually makes this distinction, most seem to work on the assumption that timezones don't change), all the tools for it are currently available in postgres. I'd said that calendar events are the most typical case of civil date-times (most other date-times, i think, are in fact timestamps, i.e. physical times: eg when a record was created, a blog post, etc). When I record an appointment with my dentist at 9:30 (at my TZ) I'm not thinking of a point of time, but a civil date-time. PG does not me allow to save (cleanly and robustly) such a basic data item. You must resort to a compound type, and plug the semantic yourself. I think that, if the date-time types were more consistent and natural, there would be no need to make assumptions about timezones specifications can change or not, the issue would not arise. (BTW, in my country the timezones indeed change, and most unpredictably ,sadly; but that's not my motivation) Hernán J. González Buenos Aires, Argentina http://hjg.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] Timezones (in 8.5?)
On Nov 19, 2009 1:18am, Andrew Gierth and...@tao11.riddles.org.uk wrote:
Right, but including more data in a single type is the wrong approach,
since it complicates the semantics and interferes with normalization.
For example, if you have a type T which incorporates a timestamp and a
timezone, what semantics does the T = T operator have? What semantics
apply if the definitions of timezones change?
I dont get the thing about normalization, there's complete ortoghonality in
my approach.
And when you say complicates the semantic I'd say enrich the semantics
(and even
clarify it) so that it fits more neatly to the typical usage of dates and
times in real life.
For datetimes with tz the equality (and comparison) operator is not
trivial, roughly
in the same sense that date-time arithmetic is not trivial when one stops
thinking of
datetimes as physical time. So is life. Should the datetimes
'2010-07-27 9:30 Chile' and '2010-07-27 10:30 Argentine' (GMT+4 and GMT+3
respec)
be considered equal? It's arguable; but the ambiguity (just a matter of
adoption) reflects
reality. We can discuss it and adopt some consistent criteria.
What if you're storing
times of events at specific places; in that case you want to associate
the timezone with the _place_ not the event (so that if the timezone
rules change, moving the place from one timezone to another, you only
have to change the place, not all the events that refer to it).
I'm not sure I undestand you here. I'm claiming that timezone rules
alterations
(zic files changes) should always be supported by the db implementation,
without
needing of touching your data. And I believe that timestamps (ie physical
times)
are in practice almost never associated to timezone information. If you
want to store
the instant of last solar eclipse you normally store the timestamp, a
timezone
might only be useful for displaying (or as an adittional info, not really
associated to the event)
A border case would be store the instant of the death of John Lennon. You
might
store the TZ here if you are interested in the civil time (so you can
answer, for example,
¿how many rock stars died in morning/afternoon?). But then, again, you are
here actually
storing a civil date (local date-time plus TZ). The only problematic case i
can envision
is to intend to store a physical time in the future with TZ, but frankly it
is difficult to
think of this scenario (and even more difficult to think of needing to
operate with that
data as a whole; hence, in this case, to store the two fields separatadely
makes sense).
I'm being dense, and this might be a lost cause, but anyway, perhaps some
day in the future
this might be of some use:
I strongly believe that, if one could sample the real needings and usage of
date-time types in
applications in this world, and taking apart types DATE (very frequent, but
rather straightforward),
and TIME (not so relevant) and intervals (other issues here, much related
to datetimes), the
overwhelming majority would fall ( conceptually) into these three types:
- TIMESTAMP (physical time - no TZ - no civil time implied)
- LOCAL DATETIME (civil time, no TZ)
- DATETIME (civil time with TZ = togheter with zic tables, implies a
physical time)
And of these three -I'd bet- the first is (conceptually) the most common,
by a wide margin.
As the name TIMESTAMP implies, it frequently records the moment of a event
(in the DB corresponds
frequently to the creation or alteration of a record, frequently via
a now() default or such).
Examples: the timestamp of messages in a mailing list, or issues in a
bugtracker, or posts/articles in a blog/Cms.
Sometimes it is modifiable by the user. Sometimes it is displayed (as a
civil date, of course) according
to some TZ implied somewhere else. It's normal that users with differnt TZ
sees this event each
with its own TZ; and one is not directly interested on obtaining (say)
an inherent civil datetime for the
event (for example one is not interested in asking what posts where
generated at midnight
acording to the localtime of the user that created it).
The LOCAL DATETIME is only of use for civil date-times, when one is not
directly interested in
asociate events with real (physicial time) - this cannot be compared with a
real time (it cant trigger alarms, eg)
Or, more rarely, when the TZ is implied somehere else (in the application,
not it the DB server!).
The DATETIME is equivalent to the compound type {LOCAL_DATETIME,TZ}. Here
the civil date-time is again
the primary concept one deals with, but in a given place in the world (TZ),
so it implies also (with the assistance of a zic table)
a real time. This type is, IMHO, less frequent than the others. The typical
use is for calendars or schedulers.
One could, a propos Andrew's observation, consider a fourth type: TIMESTAMP
WITH TZ. But it seems overkill:
except for ZIC changes, the correspondence with DATETIME
Re: [HACKERS] Deleted WAL files held open by backends in Linux
Kevin Grittner kevin.gritt...@wicourts.gov wrote: I guess it is a stretch to imagine that a database would have enough read-only connections to exhaust resources by holding open one deleted WAL file each; unless they have, say, 200 such connections and they're cutting things so close that a wasted 3.2GB of disk space at the WAL file location will run them out. AFAIK, we rename and reuse old WAL segment files. So, we don't waste disk space unless checkpoint_segments are too small. Also, if you are worried about disk space, how about adding ftruncate(to 0 byte) before unlink() ? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent 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 4/4] Add tests to dblink covering use of COPY TO FUNCTION
2009/11/25 Jeff Davis pg...@j-davis.com: On Wed, 2009-11-25 at 11:32 +0100, Pavel Stehule wrote: 1. postgres=# select count(*) from generate_series(1,100); count ─ 100 (1 row) Time: 930,720 ms 2. postgres=# select count(*) from (select generate_series(1,100)) x; count ─ 100 (1 row) Time: 276,511 ms 2. is significantly faster then 1 (there are not SRF materialisation) I think case #1 can be fixed. generate_function is fast and simple - but still COPY is about 30% faster My quick tests are not consistent enough, so I will have to try with more data. The times look similar to me so far. If there is a difference, I wonder what it is? I thing, so materialisation is every time, when you use any SQL statement without cursor. I don't think that is true. Here's an expanded version of my previous example: create table zero(i int); create table tmp(j int); insert into zero select 0 from generate_series(1,100); -- all 0 insert into tmp select 1/i from zero; -- error immediately, doesn't wait The error would take longer if it materialized the table zero. But instead, it passes the first tuple to the function for / before the other tuples are read, and gets an error immediately. So no materialization. this show nothing. It working like: 1. EXECUTE SELECT 0 FROM generate_series(1,...); 2. STORE RESULT TO TABLE zero; 3. EXECUTE SELECT 1/i FROM zero; 4. STORE RESULT TO TABLE tmp; Problem is in seq execution. Result is stored to destination after execution - so any materialisation is necessary, I worry that we're getting further away from the original problem. Let's allow functions to get the bytes of data from a COPY, like the original proposal. I am not sure COPY is the best mechanism to move records around when INSERT ... SELECT already does that. In one single case hack I prefer using any hook and feature stored contrib. I don't see a general using for this feature. Regards Pavel Stehule 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] Deleted WAL files held open by backends in Linux
Kevin Grittner kevin.gritt...@wicourts.gov writes: I ran across the subject issue and spent some time puzzling over it. ... I'm not sure whether Tom's comment that There is zero hope of making that work. referred to the idea that we could close deleted WAL files or to something else. Is a fix feasible? The reason I was dismissive of that is this scenario: a backend is told to execute a data-modifying transaction (so it has to write and commit some WAL records) and then the client promptly goes to sleep and stays that way for awhile. The backend has nothing to do either, so it's just sitting waiting on the client socket. There is noplace reasonable here to make it decide to close its open WAL file. We could possibly do a pre-emptive close() after transaction commit, but that would result in a useless distributed overhead of opening and closing the current WAL file when a backend is engaged in a series of transactions. On Linux this simply isn't an issue, anyway --- if you can't afford one open file descriptor per backend for WAL, you're going to have a far bigger problem with the backend's habit of caching descriptors for data files. On Windows it might be a problem, but I think we have got workarounds for all the important cases. The important point is that we want any overhead for this to be pushed into non-performance-critical code paths. It seemed strange that the only backends which were holding open deleted WAL files were ones where the connection was established with a login which has no write permissions. A backend would never open a WAL file unless it had to write a WAL record, so I'm having a hard time believing that these were totally read-only transactions. Can you give specifics? 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-PgSQL patch review
Itagaki Takahiro wrote:
KaiGai Kohei kai...@kaigai.gr.jp wrote:
-- keep it smaller, and step-by-step enhancement
I'd prefer smaller concept rather than smaller patch.
Its difference is unclear for me.
In this CF, I separated most of separatable concepts to reduce
size of the patch, as follows:
- No access controls except for databases, schemas, tables and columns
- No row-level access control granurality
- No security OID and text translation
- No access control decision cache
Needless to say, these feature can be added later, step-by-step.
But the core of SE-PgSQL is access control based on the SELinux policy.
It is an atomic feature, so unseparatable.
No interaction with existing features
* SE-PgSQL injects security-context-based access control, but there are
no interaction between it and the existing role-based access control.
If the interaction means restriction of available pairs between
a certain database role and a certain security context of the
client, it may be available as I followed on the next section.
However, SELinux accept only security context as an identifiers
of processes, files, tables and so on. If you mention to consider
database role identifier in access control decision, it is not
possible.
* SE-PgSQL introduces concept of security context, but there are
no interaction between it and the existing context-related features.
(ex. pg_hba.conf and Application name patch)
This is just an idea, but how about implementing context-based access
control based on role-based ACL? We will not use security context directly
for access control, but it forbid to use ROLEs in some conditions.
An example of implementation image is:
=# ALTER ROLE role VALID ON SECURITY CONTEXT '...'
For example, this could allow us to modify rows only with a particular
application, only from particular machine, and only in particular hour.
Since we've already supported object- and column-level ACL, I think
we can the same capability of the patch using security-context-to-role
mapper. Or, is it not ideal in the policy of SELinux?
Basically, it is not bad idea to restrict available database roles by
the security context of the client.
However, we need to revise the concept a bit.
Please remind its principle. SE-PgSQL applies all the its access controls
according to the security policy of SELinux.
SELinux defines all the access control rules as a relationship between
a couple of security contexts.
So, this idea can be rewritten as follows:
1. add a security context to pg_authid system column
2. add db_role object class in the security policy
(It needs discussion in the SELinux community)
3. It checks db_role:{loginas} permission between the client and
the security context of the db_authid entry.
For example, if the security policy allows system_u:system_r:httpd_t:s0
(a typical web server) domain to login as a database role labeled as
system_u:object_r:web_role_t:s0, we can assign this context on a certain
database role to be performs as web users.
ALTER ROLE role SECURITY CONTEXT ( 'system_u:object_r:web_role_t:s0' );
Please note that I basically agree to implement this relationshipt,
but it can be implemented as an incremental patch, as long as the
core feature is merged first.
Postgres is not prepared to receive SE-PgSQL
We depend on superuser too heavily. As KaiGai-san mentioned, we use
if (superuser()) instead of ACL in some places. It is a bad manner.
We should centralize access control in one module (maybe aclcheck.c),
and SE-PgSQL should be implemented only in the module.
If possible, it might be good for SE-PgSQL to replace all of the
role-based access control layer in postgres because it is hard for
users to maintain both Postgres ROLEs and SELinux settings consistently.
Do we need pluggable ACL layer before accepting SE-PgSQL?
I already tried this approach in the commit fest#2.
But it was a big failure. :(
I don't think it is a right direction.
In addition, former cases (SELinux, X-window, ...) does not integrate
its default access control stuff and the optional access controls.
Need to reconsider row-level security control
Row-level security control is excluded from the patch, but we'd better
also considering it at first. You mentioned as:
In SELinux model, massive number of objects shares a limited number of
security context (e.g more than 100 tables may have a same one)
but I'm not sure what application do you suppose. For example,
if we protect web application from SQL injection attacks, the
password column for each row can be read only from the end user
represented by the row. The number of security labels will be same
as the number of end users (= rows).
I need to admit the current proposal is a lite version, so some of
functionality may be lack.
For example, see the page.24 of this slids:
http://sepgsql.googlecode.com/files/JLS2009-KaiGai-LAPP_SELinux.pdf
It assigns a
Re: [HACKERS] garbage in psql -l
Roger Leigh rle...@codelibre.net writes: The following patch adds in an nl_langinfo(CODESET) check in addition to the existing client encoding check. I think the consensus is pretty clear that we should just set the default linestyle to ascii and not try to be cute with it. (I had already committed a patch to that effect before I saw your message.) The right way for people who want to see the nice graphics is to put \pset linestyle unicode into their .psqlrc. At this point I think the only open issue is whether to try to fix things so that .psqlrc will be executed for non-interactive cases like -c/-l. (Hm, is .psqlrc executed for psql -f file? Should it be?) 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] ALTER TABLE, find_composite_type_dependencies and locking (Confirmed)
Florian G. Pflug wrote: I do, however, suspect that ALTER TABLE is plagued by similar problems. Currently, during the rewrite phase of ALTER TABLE, find_composite_type_dependencies is used to verify that the table's row type (or any type directly or indirectly depending on that type) is not used as a column's type anywhere in the database. But since this code does not take any permanent locks on the visited types, it seems that adding such a column concurrently is not prevented. If the original ALTER TABLE changed a column's type, data inserted into the newly added column before the original ALTER TABLE committed will have a type different from what the catalog says after the original ALTER TABLE commits. Or at least so I think - I haven't yet tested that theory... I was able to confirm that this is an actual bug in 8.5. I did, however, need to use an array-of-composite type. With only nested composite types it seems that CheckAttributeType() protects against the race, because it follows the dependency chain and opens each type's relation in AccessShareLock mode. This blocks once the traversal hits the type which is being altered, hence forcing the table creation to wait for the concurrent alter table to complete. Create two types in session 1 session 1: create table t1 (t1_i int); session 1: create type t2 as (t2_t1 t1); Warm the type cache in session 2 (A simple select array[row(row(-1))::t2] would probably suffice) session 2: create table bug (bug_t2s t2[]); session 2: insert into bug (bug_t2s) values (array[row(row(-1))::t2]); session 2: select bug.bug_t2s[1].t2_t1.t1_i from bug; [select correctly returns one row containing -1] session 2: drop table bug; Alter type of t1_i in session 1 session 1: alter table t1 alter column t1_i type varchar; [Pause session 1 using gdb *right* after the call to find_composite_type_dependencies in ATRewriteTable returned] Create the bug table in session 2, and insert record session 2: create table bug (bug_t2s t2[]); session 2: insert into bug (bug_t2s) values (array[row(row(-1))::t2]); session 2: select bug.bug_t2s[1].t2_t1.t1_i from bug; [select correctly returns one row containing -1] Complete the alter table in session 1 [Resume session 1 using gdb] session 1: select bug.bug_t2s[1].t2_t1.t1_i from bug; [select returns bogus string. On my 8.5 debug+cassert build, its a long chain of \x7F\x7F\x7F\x...] Don't have any good idea how to fix this, yet. If CheckAttributeType() really *does* offer sufficient protected in the non-array case, extending that to the general case might work. But OTOH it might equally well be that a more sophisticated race exists even in the non-array case, and I simply didn't manage to trigger it... best regards, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
On Wed, November 25, 2009 3:56 pm, Jeff Davis wrote: I worry that we're getting further away from the original problem. Let's allow functions to get the bytes of data from a COPY, like the original proposal. I am not sure COPY is the best mechanism to move records around when INSERT ... SELECT already does that. I am not at all sure I think that's a good idea, though. We have pg_read_file() for getting raw bytes from files. Building that into COPY does not strike me as a good fit. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
On Wed, Nov 25, 2009 at 9:35 PM, Andrew Dunstan and...@dunslane.net wrote: On Wed, November 25, 2009 3:56 pm, Jeff Davis wrote: I worry that we're getting further away from the original problem. Let's allow functions to get the bytes of data from a COPY, like the original proposal. I am not sure COPY is the best mechanism to move records around when INSERT ... SELECT already does that. I am not at all sure I think that's a good idea, though. We have pg_read_file() for getting raw bytes from files. Building that into COPY does not strike me as a good fit. I think we speak of the opposite direction... fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby and cancelling idle queries
Simon Riggs wrote: Recent change: An idle-in-transaction transaction can also hold a temporary file. Think of an open cursor, for example. Therefore, remove the distinction between CONFLICT_MODE_ERROR and CONFLICT_MODE_ERROR_IF_NOT_IDLE, idle-in-transaction backends need to be killed too when a tablespace is dropped. Open cursors still have snapshots, so they would not be treated as idle in transaction. A backend is idle-in-transaction whenever a transaction is open and the backend is waiting for a command from the client. Whether it has active snapshots or open cursors doesn't affect that. If the user has a held cursor then they can keep it, since it has already read the database and released the snapshot. A held cursor can keep a temp file open. I suspect you missed the context of this change. It's about the code in tablespc.c, to kill all backends that might have a temporary file in a tablespace that's being dropped. It's not about tuple visibility but temporary files. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] force index problem in 8.4.1
On Wed, 25 Nov 2009, Tom Lane wrote: Oleg Bartunov o...@sai.msu.su writes: I tried to force query to use index by specifying high execution cost, but without success, even seqscan cost doesn't changed. This is 8.4.1 I think you altered the wrong function. The function underlying point @ box is on_pb() not pt_contained_box ... in fact, I don't even see a function named that. sorry, I forgot to say, that I created operator CREATE OR REPLACE FUNCTION pt_contained_box(point, box) RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE 'C' IMMUTABLE RETURNS NULL ON NULL INPUT; CREATE OPERATOR @ ( LEFTARG = point, RIGHTARG = box, PROCEDURE = pt_contained_box, COMMUTATOR = '@', RESTRICT = contsel, JOIN = contjoinsel ); Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Backup history file should be replicated in Streaming Replication?
Hi, In current SR, since a backup history file is not replicated, the standby always starts an archive recovery without a backup history file, and a wrong minRecoveryPoint might be used. This is not a problem for SR itself, but would cause trouble when SR cooperates with Hot Standby. HS begins accepting read-only queries after a recovery reaches minRecoveryPoint (i.e., a database has become consistent). So, a wrong minRecoveryPoint would execute read-only queries on an inconsistent database. A backup history file should be replicated at the beginning of the standby's recovery. This problem should be addressed right now? Or, I should wait until current simple SR patch has been committed? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Wed, 2009-11-25 at 15:59 -0800, Jeff Davis wrote: My operator-class-fu is insufficient to render judgment on this point. I think the thing to do is look at a bunch of non-built-in opclasses and check for POLA violations. Ok, I'll consider this more. In cases where the operator class type is different from the search operator's operands' types, one of the following is usually true: * There is a binary cast from the opclass type (same as expression type) to the search operator's operands' types, or it is otherwise compatible (e.g. ANYARRAY). * There is a candidate function that's a better match (e.g. there may be an =(int8, int8) on int2_ops, but there's also =(int2, int2)). * The left and right operand types are different, and therefore do not work with operator exclusion constraints anyway (e.g. full text search @@). After installing all contrib modules, plus my period module, and postgis, there appear to be no instances that violate these assumptions (according to a join query and some manual testing). In theory there could be, however. It's kind of ugly to make it work, and a challenge to test it, so for now I'll only accept operators returned by compatible_oper(). If you disagree, I can make it work. 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] [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
On Thu, 2009-11-26 at 05:01 +0100, Pavel Stehule wrote: It working like: 1. EXECUTE SELECT 0 FROM generate_series(1,...); 2. STORE RESULT TO TABLE zero; 3. EXECUTE SELECT 1/i FROM zero; 4. STORE RESULT TO TABLE tmp; Problem is in seq execution. Result is stored to destination after execution - so any materialisation is necessary, My example showed that steps 3 and 4 are not executed sequentially, but are executed together. If 3 was executed entirely before 4, then the statement: insert into tmp select 1/i from zero; would have to read the whole table zero before an error is encountered. However, the statement errors immediately, showing that steps 3 and 4 are pipelined. 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] Spontaneous PITR standby activiation
David Christensen wrote: 1) is there a hard limit of the number of times the archive_command will attempt? I didn't see anything documented about this in the PITR or config docs, so I'm guessing the 10 failures I saw in the log were just coincidental. There's no limit. It will try forever. 2) are the archive_command failures in the master's log responsible for the redo records? No, archive_command failures shouldn't affect the standby. 3) would a Pg forced shutdown cause issues with the generated WAL when replaying? No. 4) at first I thought it had to do with a bug/failure in pg_standby, but I'm wondering if it has to do with the record with zero length referenced in the standby's logs. Thoughts? That basically means that WAL replay reached end of WAL. The rest of the WAL file is probably full of zeros. It's quite normal in crash recovery, for example. But I have no explanation for why such a WAL file was archived. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH 4/4] Add tests to dblink covering use of COPY TO FUNCTION
On Thu, 2009-11-26 at 00:35 -0500, Andrew Dunstan wrote: On Wed, November 25, 2009 3:56 pm, Jeff Davis wrote: I worry that we're getting further away from the original problem. Let's allow functions to get the bytes of data from a COPY, like the original proposal. I am not sure COPY is the best mechanism to move records around when INSERT ... SELECT already does that. I am not at all sure I think that's a good idea, though. We have pg_read_file() for getting raw bytes from files. Building that into COPY does not strike me as a good fit. I think we're in agreement. All I mean is that the second argument to COPY should produce/consume bytes and not records. I'm not discussing the internal implementation at all, only semantics. In other words, STDIN is not a source of records, it's a source of bytes; and likewise for STDOUT. 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] cvs chapters in our docs
On ons, 2009-11-25 at 22:15 +0100, Magnus Hagander wrote: On Wed, Nov 25, 2009 at 22:07, Peter Eisentraut pete...@gmx.net wrote: On ons, 2009-11-25 at 16:27 +0100, Magnus Hagander wrote: Attached is a patch which adds a chapter to git in our documentation, around where we have several chapters about cvs today. It also removes a few very out of date comments about cvs I think this whole chapter could be removed and the relevant information added to the web site or the wiki. (Btw., it's spelled Git, not GIT.) Completely, or replaced with a reference to pages on the web/wiki? I think the appendix in question could be removed completely, if the content is adequately covered elsewhere. In the installation instructions chapter, there is a section Getting the Source, which could warrant a link or reference to the appropriate instructions on the web site. -- Sent 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-PgSQL patch review
On Thu, Nov 26, 2009 at 11:15:46AM +0900, Itagaki Takahiro wrote: No interaction with existing features * SE-PgSQL injects security-context-based access control, but there are no interaction between it and the existing role-based access control. And there shouldn't be, I think. SE-PgSQL is MAC which means that what someone can access is configured elsewhere. This example is non-sensical: =# ALTER ROLE role VALID ON SECURITY CONTEXT '...' When someone logs in they have a security context and what they can access is then decided. You can't reconfigure what someone has access to with anything within the DB (other than label changes), the SE-PgSQL rules are elsewhere. That's what Mandatory refers to. but I'm not sure what application do you suppose. For example, if we protect web application from SQL injection attacks, the password column for each row can be read only from the end user represented by the row. The number of security labels will be same as the number of end users (= rows). This example is also strange: the program that needs to read the password need to be able to see all rows because by definition the user cannot be logged in yet. After you login there is no need to be able to read your own password. So column-access control is fine here. And even if there are lots of contexts, so what? Security is not free, but given SE-PgSQL is in use in the real world, clearly people think the tradeoffs are worth it. Finally, this is not about protection against SQL injection, it's protection against people in Sales reading data belonging to Finance. Actual benefits of SE-PgSQL SE-PgSQL will be committed step-by-step -- but could you explain which step can solve which problem in the real world? Imagine that SQL injections, measure for SOX Act, divulgation of personal information, They are security holes in terms of a whole application, but not a hole in terms of database, because database cannot always distinguish between legal and illegal data access (ex. correction of wrong data vs. rigging of benefits). As far as I can tell, just about all the interesting cases are for row-level security. While MAC on tables and columns will be interesting, my guess the real uptake will be when row-level control is in. 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] Backup history file should be replicated in Streaming Replication?
Fujii Masao wrote: In current SR, since a backup history file is not replicated, the standby always starts an archive recovery without a backup history file, and a wrong minRecoveryPoint might be used. This is not a problem for SR itself, but would cause trouble when SR cooperates with Hot Standby. But the backup history file is included in the base backup you start replication from, right? After that, minRecoveryPoint is stored in the control file and advanced as the recovery progresses. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
