Re: [HACKERS] display previous query string of idle-in-transaction
After taking look at our monitoring system i think some hint about previous SQL might be useful. dbadb70db_nameWARNING1long transactions, duration 2690min user=postgres pid=7887 waiting=False query=IDLE in transaction Currently i have no idea what exactly did i kill without digging in logs which might have rotated anyway by now. regards, Asko On Tue, May 12, 2009 at 6:37 PM, decibel deci...@decibel.org wrote: On Mar 27, 2009, at 2:36 AM, Simon Riggs wrote: Not really. I want to understand the actual problem with idle-in-transaction so we can consider all ways to solve it, rather than just focus on one method. I have to distinct problems with idle in transaction. One is reporting users / the tools they're using. I'll often find transactions that have been open for minutes or hours. But, that's not a big deal for me, because that's only impacting londiste slaves, and I have no problem just killing those backends. What does concern me is seeing idle in transaction from our web servers that lasts anything more than a few fractions of a second. Those cases worry me because I have to wonder if that's happening due to bad code. Right now I can't think of any way to figure out if that's the case other than a lot of complex logfile processing (assuming that would even work). But if I knew what the previous query was, I'd at least have half a chance to know what portion of the code was responsible, and could then look at the code to see if the idle state was expected or not. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] create if not exists (CINE)
It was just yesterday when i wondering why we don't have this feature (i was trying to use it and it wasn't there :). The group of people who think it's unsafe should not use the feature. Clearly this feature would be useful when managing large amounts of servers and would simplify our release process. On Wed, May 6, 2009 at 5:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, May 5, 2009 at 9:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: The argument was not about whether that is the plain meaning of the phrase; it was about whether that is a safe and useful behavior for a command to have. There is a pretty substantial group of people who think that it would be quite unsafe, which is why we failed to arrive at a consensus that this is a good thing to implement. Who are these people other than you, In the thread that went into this in most detail http://archives.postgresql.org//pgsql-hackers/2005-10/msg00632.php it seemed that wanting CINE was a minority opinion, and in any case a number of pretty serious issues were raised. and did you read the rest of my email? Yes, I did. I'm not any more convinced than I was before. In particular, the example you give is handled reasonably well without *any* new features, if one merely ignores object already exists errors. It sounds pretty amazing. Ignoring errors as a suggested way to use PostgreSQL. We run our release scripts inside transactions (with exception of concurrent index creation). So if something unexpected happens we are left still in working state. PostgreSQL ability to do DDL changes inside transaction was one of biggest surprises/improvements when switching from Oracle. Now you try to bring us down back to the level of Oracle :) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table data exclusion patch for pg_dump
How do you use -s to exclude data for some tables from otherwise full dump? Dump schema and data separately? On Fri, May 1, 2009 at 6:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Vadim Trochinsky m...@vadim.ws writes: This is a patch that allows choosing not to dump the data for the selected tables. Why wouldn't you just use -s ? 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] Out parameters handling
On Sat, Mar 7, 2009 at 9:29 PM, Dimitri Fontaine dfonta...@hi-media.comwrote: In fact, maybe a new option to set the OUT parameters prefix to use from within the function body would do? Le 7 mars 09 à 19:56, Dimitri Fontaine a écrit : CREATE OR REPLACE FUNCTION test_out ( IN a integer, IN b integer, OUT s integer ) RETURNS setof integer SET out_prefix TO 'v_' LANGUAGE PLPGSQL AS $f$ That's what we also would like to have. In addition it should also make out parameters unusable without that prefix. Then we could make it our coding standard and feel relatively safe again. Those two following lines would be deprecated: DECLARE v_s ALIAS FOR $3; BEGIN FOR v_s IN SELECT generate_series(a, b) LOOP v_s := v_s * v_s; RETURN NEXT; END LOOP; RETURN; END; $f$; CREATE FUNCTION dim=# SELECT * FROM test_out(2, 4); s 4 9 16 (3 rows) -- dim
[HACKERS] Out parameters handling
Hi It was one of my worst Friday's finding out that this brain dead implementation of out parameters had been part of fuck up again. This time we did notice it two days too late. I wish for a way to use out parameters in functions only through some predefined prefix like in triggers new and old. Means i would like to limit referencing to out parameters to one prefix only defined in the beginning of declare section of stored procedure. It really sucks what kind of mistakes you can pass to production unknowingly. I would much prefer a way to prevent such nonsense. Here was the case where out parameters were with same names with select into field names resulting in null outcome. Just yesterday we had similar case with update statement. regards Asko
Re: [HACKERS] [PATCHES] updated hash functions for postgresql v1
Did this change hashtext() visible to users? We have been using it quite widely for partitioning our databases. If so then it should be marked quite visibly in release notes as there might be others who will be hit by this. regards Asko On Mon, Feb 9, 2009 at 11:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kenneth Marshall k...@rice.edu writes: I have updated the patch posted by Jeff Davis on January 9th to include the micro-patch above as well as updated the polymorphism regressions tests. This applies cleanly to the latest CVS pull. Applied --- thanks for being persistent about resolving the doubts on this. One thing that apparently neither of you realized was that the polymorphism results were varying between bigendian and littleendian machines; I suppose you are using different hardware and that's why you didn't agree on what the results should be. Since we already agreed we were going to tolerate endianness dependence in the hash functions, I fixed that by adding some ORDER BYs. 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] Summary: changes needed in function defaults behavior
I would agree with making it stricter. It would force people to do less stupid things. Our main use case for default parameter will be getting rid of all the old versions of functions with shorter parameter lists by just creating new versions of old functions with additional default parameters. We don't use views much but all the fuss and restrictions that surround them gives me a feeling that there might be something to be improved in how they are implemented/hacked into the PostgreSQL. What might be the use case for foo(f1 int) foo(f1 int, f2 int = 42) foo(f1 int, f2 int = 42, f3 int = 43) ? When i have function in database foo(f1 int) and do create or replace foo(f1 int, f2 int = 42) I would expect foo to get replaced. Current implementation seems to make us go through drop create sequence. regards, Asko PS. Any chance for lifting the restriction for changing function return type without dropping the function. On Thu, Dec 18, 2008 at 12:51 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: * Two functions that could match a given call after adding defaults are considered ambiguous only if they would add the same number of defaults; otherwise we prefer the one with fewer parameters. This generalizes the rule that an exact match (no defaults) is preferred over one that requires adding defaults. Experimenting with the revised code, I found a curious case that might be worth worrying about. Consider the example that started all this: create function foo(f1 int, f2 int = 42, f3 int = 43) ... create view v1 as select foo(11); The patch I've got correctly reverse-lists v1 as select foo(11). Now suppose we add create function foo(f1 int, f2 int = 42) ... or even create function foo(f1 int) ... The view is still gonna reverse-list as select foo(11) --- in fact, we really haven't got much choice about that. However, if dumped and reloaded along with one of these shorter-argument-list functions, the view will be reconstituted as a reference to the shorter function instead of the original 3-argument function. I'm not sure how critical this is, since you'd have to be pretty dumb to put together a set of functions like this that didn't work compatibly. Still, this is the first instance I know of in which dump/reload isn't going to be guaranteed to match the same function as was being called in the dumped database. If we think this is critical enough to be worth sacrificing something for, what I'd suggest is that we abandon the concept that shorter argument lists are allowed to win over longer ones. This would mean that foo(f1) foo(f1 int, f2 int = 42) foo(f1 int, f2 int = 42, f3 int = 43) would all be considered equally good matches for a call foo(11) and so you'd get an ambiguous function failure. While that doesn't prevent you getting into this sort of trouble, what it would do is ensure that the dump reload gives an error instead of silently picking the wrong function. Also, you'd most likely have gotten a few failures and thus been shown the error of your ways before you dumped the old DB at all. Thoughts? 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] WIP: default values for function parameters
On Fri, Dec 12, 2008 at 8:05 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Michael Meskes mes...@postgresql.org writes:
On Fri, Dec 12, 2008 at 10:06:30AM -0500, Tom Lane wrote:
Hmm ... actually, ecpg might be a problem here anyway. I know it has
special meaning for :name, but does it allow space between the colon
and the name? If it does then the colon syntax loses. If it doesn't
No. Here's the lexer rule:
SQL:{identifier}(((-|\.){identifier})|(\[{array}\]))*
No space possible between : and {identifier}.
Excellent. I checked that psql's colon-variable feature behaves the
same. So it looks like the proposed name: value syntax would indeed
not break any existing features. Barring better ideas I think we should
go with that one.
+1
name: value should be good enough
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] user-based query white list
Hi We use plproxy for this kind of security enhancement. We create plpgsql functions that do whats needed and then we create so called proxy database that contains only plproxy interfaces for these functions. Users get access only to proxy database. This way it is easier to rest assured that users don't get access by accident to something they should not. regards, Asko On Sat, Dec 6, 2008 at 8:21 PM, Andrew Chernow [EMAIL PROTECTED] wrote: Looking for a way to limited a user to a specific set of queries. I don't think this can be done right now ... or can it? Has this feature request surfaced in the past? I currently need this as an extra security measure for a libpq client app (want to block arbitrary queries from malicious attackers). The easiest way I found was to add some query_string checks into backend/tcop/postgres.c for the 'Q' and 'P' commands in PostgresMain(). Seems to work just fine. If it doesn't match, I issue an ereport FATAL since that is seen as a malicious query execution attempt. I think it is something rather simple to design/implement (probably use a table of user allowed queries, support regex matches, etc.. loaded at session startup and SIGHUP). -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Transactions within a function body
On Thu, Oct 2, 2008 at 6:46 PM, Bob Henkel [EMAIL PROTECTED] wrote: Have you looked at creating a function in perl and creating a new connection? Or using a dblink query which can create a new connection? These two methods work. I have used them to insert to a log table regardless of the parent transaction being commited or rolled back. A old example I posted of using pl/perl can be found here - http://www.postgresqlforums.com/forums/viewtopic.php?f=4t=647 The key is opening a new session which using dblink or pl/perl dbi connection will do. This is not ideal or efficient. It would be nice if you could just do autonomous transactions natively in pl/pgsql, but I find this method works for the cases where you need it(logging, huge batch processing tasks where it's not ideal to process everything in one transaction). The same can be done with plProxy which is quite efficient but yes opening connections is not. So if used extensively it would be clever to use pgBouncer to reuse connections. Thanks for interesting idea. Bob Hi all. Is there a way to have (sub)transactions within a function body? I'd like to execute some code (a transaction!) inside a function and later decide whether that transaction is to be committed or not. Thanks. On Thu, Oct 2, 2008 at 10:40 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Gurjeet Singh escribió: I have seen this feature being asked for, and this work-around suggested so many times. If plpgql does it internally, why not provide a clean interface for this? Is there some road-block, or that nobody has ever tried it? Initially we aimed at just exposing SAVEPOINT and ROLLBACK TO in functions, but ran into the problem that the SPI stack needs to be dealt with appropriately and you can't do it if the user is able to modify it arbitrarily by calling transaction-modifying commands. That's when the EXCEPTION idea came up. We never went back and studied whether we could have fixed the SPI limitation, but it's not trivial. -- Alvaro Herrera http://www.CommandPrompt.com/ http://www.commandprompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list ([EMAIL PROTECTED]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] Proposal: move column defaults into pg_attribute along with attacl
On Mon, Sep 22, 2008 at 5:41 AM, Stephen Frost [EMAIL PROTECTED] wrote: * Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: If we were to accept the pg_attrdef approach, why aren't we doing a pg_attracl table instead of adding a column to pg_attribute? That's actually not an unreasonable question. If you were to do that then you could attach OIDs to the attribute ACLs, which might be a nicer representation in pg_shdepend than you were thinking of using. What bugs me about this is that it comes across as poor database design- both of these really are attributes of a column. We're creating seperate tables for each so we can induce a cleaner ID for them, which just isn't the right approach imv. This would also be another table to go deal with when a column is removed, and a less-than-obvious place to look for this information from the user's perspective. It's also the case that the items in these tables and the columns they're attached to really are one-to-one, there's no many-to-one or one-to-many relationship between them.. That's exactly the impression i get also :) At the end of the day, this approach feels like more of a kludge to me to keep the dependency system simple rather than making the dependency system support the real-world system layout, which is that columns don't have their own IDs. Maybe we could approach this another way- what about creating a new table which is pg_attrcolids that has both pg_attrdef and pg_attracl rolled into it? Then at least we're accepting that we need a distinct ID for columns, but keeping them in one specific place? Is there a reason we would need a seperate ID for each? It also strikes me to wonder about possible future support for re-ordering columns, though I don't immediately see a way to use this as a step towards supporting that. Thanks, Stephen -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkjXBdkACgkQrzgMPqB3kijuVwCfU2C0TMgd1HYsaDY+wxRSTUph YKsAnjtzysLoTpo3jWJMSxjmU23/RMaT =OvBL -END PGP SIGNATURE-
Re: [HACKERS] using hash index when BETWEEN is specified
On Wed, Sep 10, 2008 at 1:39 PM, Zdenek Kotala [EMAIL PROTECTED]wrote:
I has played with new hash index implementation and I tried following
command:
postgres=# select * from test where id between 1 and 5;
Time: 9651,033 ms
postgres=# explain select * from test where id between 1 and 5;
QUERY PLAN
-
Seq Scan on test (cost=0.00..141681.00 rows=1 width=4)
Filter: ((id = 1) AND (id = 5))
(2 rows)
Hash index is created on id column. However when I use
postgres=# explain select * from test where id in (1,2,3,4,5);
QUERY PLAN
-
Bitmap Heap Scan on test (cost=22.24..332.53 rows=83 width=4)
Recheck Cond: (id = ANY ('{1,2,3,4,5}'::integer[]))
- Bitmap Index Scan on test_idx (cost=0.00..22.22 rows=83 width=0)
Index Cond: (id = ANY ('{1,2,3,4,5}'::integer[]))
(4 rows)
Time: 1,352 ms
I'm not planner guru but it seems to me that BETWEEN clause could be
rewritten as a IN clause for integer data types and small interval.
Where should the line be drawn.
Define small :)
Zdenek
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reducing statistics write overhead
On Sat, Sep 6, 2008 at 2:29 AM, Euler Taveira de Oliveira [EMAIL PROTECTED] wrote: Martin Pihlak escreveu: I suspected that, but somehow managed to overlook it :( I guess it was too tempting to use it. I'll start looking for alternatives. If you can't afford a 500 msec pgstat time, then you need to make it tunable. Additional parameter in config file. Not good. Another ideas are (i) turn on/off pgstat per table or database and (ii) make the pgstat time tunable per table or database. You can use the reloptions column to store these info. These workarounds are much simpler than that you proposed and they're almost for free. Does not seem simple to me. Why would dba's want extra management work. We want all the stats to be there as we don't know when we need to look at it. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reducing statistics write overhead
Too frequent read protection is already handled in the patch but these comments might lead it into new directions. Current implementation had this same limit that file was written no more than once per 500 ms. On Sat, Sep 6, 2008 at 9:12 PM, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Some sort of if stats were requested in the last 500 ms, just tell the requester to read the existing file. Things that come to mind: - autovacuum could use a more frequent stats update in certain cases BTW, we could implement that by, instead of having a global tunable, including a field in the request message saying how stale an existing file is acceptable for this requestor. 500ms might be the standard value but autovac could use a smaller number. 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] Withdraw PL/Proxy from commitfest
On Fri, Sep 5, 2008 at 7:37 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: So, you'll implement the part of SQL-MED that deals with specifying remote connections, e.g something like CREATE CONNECTION (no, I haven't looked at what the syntax actually is)? Yeah, that sounds like a good idea. We should get that into core, and modify contrib/dblink to use it as well. It's just a small part of SQL-MED, but it's a start, and it's useful for these other projects. Yes that's the plan. Marko Kreen wrote: In the previous discussion there was mentioned that Postgres should move to the SQL-MED direction in remote connection handling. SQL-MED specifies that connections should have names and referenced everywhere using names. PL/Proxy currently does not conform to that standard - it uses connection strings directly. Although it could made work with SQL-MED backend, it would look ugly. So I'd like to withdraw PL/Proxy from commitfest and rework it's connection handling scheme to be also name-connstr based. Idea will be that it will have user-definable connection handling backend, which operates on named connections. And in the future we can plug in a backend that reuses connection info from builtin SQL-MED store. Although the current connection handling works and is secure it has a deficiency that it's bit hard to hide the password that is used for connecting. User can either play with table/function permissions and SECURITY DEFINER functions but that's complex. Or he can put passwords into .pgpass - this is easy and secure but has the problem that the file is not manageable from inside database. So PL/Proxy needs new SQL-MED based scheme that fixes it. When this is ready we can re-discuss the builtin vs. PL-based remote functions. As I don't plan to work on it near-term there is no point polluting the commitfest page with it. [ There was a attempt to paint the .pgpass based password handling insecure because dblink makes the file world-readable. I still fail to see how this any way points to flaws of the scheme... ] -- 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] IN vs EXISTS equivalence
On Wed, Sep 3, 2008 at 9:17 AM, daveg [EMAIL PROTECTED] wrote: On Thu, Aug 14, 2008 at 06:50:09PM +0100, Simon Riggs wrote: On Fri, 2008-08-08 at 16:23 -0400, Tom Lane wrote: NOT IN is a lot trickier, condition: you must also assume that the comparison operator involved never yields NULL for non-null inputs. That might be okay for btree comparison functions but it's not a very comfy assumption in general; we certainly haven't got any explicit knowledge that any functions are guaranteed to act that way. So this case might be worth doing later ... Just found this comment, after reading what you said on other thread about NOT IN. NOT IN is a serious performance issue for most people. We simply can't say to people you were told not to. If we can fix it easily for the majority of cases, we should. We can't let the it won't work in certain cases reason prevent various A suggestion: what about adding an attribute to functions to declare that they never return null? And if function still returns null then error will be raised? Then you will end up adding NOT NULL also to IN and OUT parameters. IIRC it was possible in Oracle to declare local variables NOT NULL. declare foo(int, int) returns int immutable not null as ... -dg -- David Gould [EMAIL PROTECTED] 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Cleanup of GUC units code
On Wed, Sep 3, 2008 at 11:20 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Marko Kreen wrote: On 9/2/08, Peter Eisentraut [EMAIL PROTECTED] wrote: Marko Kreen wrote: In the meantime, here is simple patch for case-insensivity. You might be able to talk me into accepting various unambiguous, common alternative spellings of various units. But for instance allowing MB and Mb to mean the same thing is insane. How would the docs for that look like? And anyway, what is wrong with Mb for megabytes? From infamous wikipedia: A *megabit* is a unit of informationhttp://en.wikipedia.org/wiki/Informationor computer storage, abbreviated *Mbit* (or *Mb*). To me playing with case of acronyms and even depending on it seems more insane. It would make much more sense to have case insensitive set of acronyms and (thanks Tom for pointing out) some sanity checks when configuration is loaded to notify user when wrong ones are used for some context. I doesn't seem completely unreasonable to me that we'd want to express something in megabits/second in the future. For example, instead of vacuum_cost_delay, it would be cool to specify a bandwidth allowance. Megabits/second is a completely reasonable unit for that. Or a limit on network bandwidth. There are less confusing (better) acronyms kbit/s and mbit/s available for that. FWIW, I don't feel very strongly either way. I'm more than happy with the status quo. The hint in the error message very clearly spells out what the valid values are, so it's immediately clear what you need to fix if you get that wrong. Is the database down during that time? -- 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] Cleanup of GUC units code
Anything that will reduce potential downtime should be way to go. To me it seems that Peter uses the loudest voice and others just don't care enough. Using kB for kilobyte seems quite alien and confusing. I have not noticed that to be used in software i use in my everyday work and could not find any either with quick search. On Wed, Sep 3, 2008 at 12:15 AM, Tom Lane [EMAIL PROTECTED] wrote: Gregory Stark [EMAIL PROTECTED] writes: Marko Kreen [EMAIL PROTECTED] writes: Uh. So you want force proper units in presentations at the price of everyday admin operations? Does not seem like a sensible tradeoff. It didn't to anyone else when Peter wrote the current version either, but as the person willing to actually do the work and write the code Peter got to make the decision. Nobody else stepped up to do the work to change it and we can't exactly force Peter to do work he doesn't agree with. It's not that, in my mind: it's that Peter feels more strongly about it than the rest of us. This proposal has come up before and he's successfully argued it down each time. He does have a point about there being some potential for confusion; and the arguments on the other side are not much better than I'm lazy. Being lazy myself, I'd prefer a case insensitive implementation; but I don't feel strongly enough about it to want to override Peter's opinion. 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] Attaching error cursor position to invalid constant values
On Mon, Sep 1, 2008 at 12:59 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Tom Lane wrote: Does anyone think this might be too chatty? No. +1 -- 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] Proposal: new border setting in psql
As stated above this format is mainly good for copy paste and may require occasional manual tweaking. Users should be people who use psql in their everyday work and on the other hand need to publish data from database in some other places. Would you please bring examples of some widespread applications that would be happy to digest these formats? regards, Asko PS: For me current capabilities of psql (\a and \f) have been quite enough so far when pasting occasionally to wiki, openoffice or chat message. On Fri, Aug 29, 2008 at 11:00 AM, Cédric Villemain [EMAIL PROTECTED] wrote: Le Friday 29 August 2008, Greg Smith a écrit : On Fri, 29 Aug 2008, Tom Lane wrote: You're ignoring the fact that D'Arcy's patch doesn't output valid ReST. It outputs something that might pass for ReST, but only so long as there are no special characters in the data. I agree that it's a bad idea to say explicitly that it's ReST mode output, because it's not. But it works just fine for that purpose on almost every table I would generate on a typical day. My databases are mainly filled with plain alphanumeric text and numbers. If I can dump 99% of them into ReST using this new border but 1% require me to manually tweak by escaping some characters, that's still very useful to me. I'd hate to see a focus on the corner cases drive this feature away. We use ReST a lot, and it will be very usefull to have this ouput. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org
Re: [HACKERS] Bogus TODO item
On Wed, Aug 20, 2008 at 9:15 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Tom Lane wrote: Idly thumbing through the new TODO list, I noticed that the second item from the bottom (about how we don't want optional AS) has been superseded by events ... http://archives.postgresql.org/pgsql-committers/2008-02/msg00172.php Good point, removed. I didn't mark it as done becuase it is unclear what done means for a not wanted item. ;-) Done away with. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal sql: labeled function params
Would AS be harder to implement? select foo(10 AS a, 20 AS b); select foo(20 AS b, 20 AS a); select x(0 = 1 AS a); other fantasies select foo(10 a, 20 b); select foo(a 10, b 20); regards, Asko On Wed, Aug 20, 2008 at 4:26 PM, Pavel Stehule [EMAIL PROTECTED]wrote: 2008/8/20 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: I understand now why Oracle use = symbol for named params. This isn't used so operator - so implementation is trivial. You really didn't understand the objection at all, did you? The point is not about whether there is any built-in operator named =. The point is that people might have created user-defined operators named that. I understand well, so only I don't see better solution. Yes, everyone who used = should have problems, but it is similar with .. new keywords, etc. Probably easy best syntax doesn't exist :(. I haven't idea who use = now and how often, and if this feature is possible in pg, but there are not technical barriers. regards Pavel Stehule 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] Proposal: new border setting in psql
Proposed formats don't look easier to read for humans. I doubt that they are more common or easier to process by machines than just COPY query TO STDOUT CSV; The reason for this is to allow the output to be fed directly into any system using Restructured text The idea would be to use psql as backend for some other system? Or what do you mean by fed directly? On Thu, Aug 21, 2008 at 10:54 PM, D'Arcy J.M. Cain [EMAIL PROTECTED] wrote: On Thu, 21 Aug 2008 15:03:23 -0400 Tom Lane [EMAIL PROTECTED] wrote: D'Arcy J.M. Cain [EMAIL PROTECTED] writes: I would like to propose a new border setting. That code is horrendously overcomplicated and unreadable already :-( I'm not too eager to add more variants to it. Actually, I already made the code changes and they were surprisingly simple. The reason for this is to allow the output to be fed directly into any system using Restructured text as described in http://docutils.sourceforge.net/docs/user/rst/quickref.html. Is that *really* going to work? What about quoting/escaping conventions? ReST is pretty good with that stuff. Also, how many of those any systems actually exist? Markup conventions are a dime a dozen. That I can't say. My impression was that it was reasonably well known. However, while ReST was *my* reason for proposing this it was also important to me that the format stand by itself. I think it does. It also follows the documentation in that it is an extension to border 2 but with more borders, just like border 2 is more than border 1, etc. It's a consistent progression. On the whole I think it ought to be sufficient to support XML output for people who want easily-machine-readable query output. Perhaps. The problem is that it still means running it through an external program. That's fine for scripted processes but not for ad hoc queries. Perhaps what we really need is the ability for users to install their own formatting functions. After all, we can define everything else. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent 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: plan invalidation vs stored procedures
The lack of plan invalidation is limitation that also has two bugs attached to it. I agree that full fledged patch to fix all the isssues should not be done in 8.3. I can't agree that effort to get the bugs fixed already in 8.3 should not be made. I can understand that hackers here have learned to live with these bugs and limitations but not all the users are reading these flame wars here and most of them are not even aware of these bugs until they are hit by them. Sql function bug is such that users probably won't even understand what hit them and how the data got mangled. - If there is nothing that can be done in 8.3 at least warning should be added into the documentation. It will be just one more don't in our long list don'ts for our developers. ERROR: cache lookup failed for function. - Could the plan be marked as invalid so it would fail only once so the next call to the function would get replanned and work again. At least it would be better than losing parts of application for indeterminate time. - Should update pg_proc set proname = proname; be the current solution to the problem or has someone something better to offer. We could scan released code for DROP FUNCTION and generate plan invalidation statement as last item of transaction releasing the code. - Could some less dangerous looking mechanism be added to 8.3 that wouldn't make users not used to PostgreSQL limitations gasp for air when they see the workarounds :) Calling the problem limitation will not make it go away. I am quite sure that new users consider it a bug until thay are converted to perceive it as lmitation. No matter how many time the usage of functions in database is called corner case it does not make it a corner case. In my experience it is quite common practice on all the database systems i have worked with. I do get the impression that Tom who would prefer to get all the pl's out of PostgreSQL and live happily ever after with pure SQL standard. On Wed, Aug 20, 2008 at 11:27 AM, Dimitri Fontaine [EMAIL PROTECTED]wrote: Le mercredi 20 août 2008, Tom Lane a écrit : That just begs the question of what's the difference between a bug and a limitation. AFAICS, having such a policy/guideline/whatchacallit in place wouldn't have done a single thing to stop the current flamewar, because the people who want this thing back-patched are insisting that it's a bug, while those who don't are saying it's a long-known limitation. As a person who previously insisted it was a bug, I'd like to take the opportunity to claim that I didn't realize this was a limitation of the design of plan invalidation, which now seems related to DDL operations. Realizing this earlier would have resulted in no mail at all on this thread from here. There's certainly a balance between -hackers readers not doing their homework and people in the know choosing not to re-estate known things... Also, there are a whole lot more considerations in a backpatch decision than just is it a bug. The (estimated) risk of creating new bugs and the extent to which the patch will change behavior that apps might be relying on are two big reasons why we might choose not to back-patch a bug fix. And this way the project works is what leads its users not to fear minor upgrades, which is something I (we all?) highly value. Regards, -- dim
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Thanks for a nice replay Andrew. So best solution for 8.3 is update pg_proc set proname = proname; whenever you need to drop and create functions or some in house patch. Lets get on with 8.4 Asko On Wed, Aug 20, 2008 at 4:16 PM, Andrew Sullivan [EMAIL PROTECTED]wrote: On Wed, Aug 20, 2008 at 03:12:43PM +0300, Asko Oja wrote: - If there is nothing that can be done in 8.3 at least warning should be added into the documentation. It will be just one more don't in our long list don'ts for our developers. I am in favour of that change in the 8.3 branch. ERROR: cache lookup failed for function. - Could the plan be marked as invalid so it would fail only once so the next call to the function would get replanned and work again. At least it would be better than losing parts of application for indeterminate time. That seems to me to be a behaviour change, not a bug fix. I agree that the current behaviour is pretty annoying. That is not the same thing as a bug except in the loosest sense. The system works as specified, and therefore it's not a bug. If the specification is wrong, you need a new specification; that's a bug fix that is usually pronounced major release. - Could some less dangerous looking mechanism be added to 8.3 that wouldn't make users not used to PostgreSQL limitations gasp for air when they see the workarounds :) I think it a very bad idea even to suggest that we start undertaking things like adding mechanisms to minor releases, even with smileys at the end of the sentence. I appreciate (possibly more than many hackers) the limitations that are imposed on users by some of the decisions historically taken by developers in some of the previous major releases. But I very strongly agree with Dimitri: the super-conservative approach to maintenance releases that this project takes is a really big benefit to users, and is ultra important in mission critical environments. Otherwise, it becomes practically impossible to get minor releases into production. If you have to worry about the possibility of major changes between minor versions, you will have to treat every release as a major release. I don't think we have sufficient commercial integration support yet that we can follow the lead of the Linux kernel, where the system vendor has the effective obligation to make sure your kernel actually works. In addition, if someone wants to develop back-patches for 8.3 that give it new functionality otherwise planned for 8.4, I see nothing wrong with them doing so. That's the advantage offered by having the source. But the idea that the new functionality should be patched back by the project because one is impatient is not on. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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: plan invalidation vs stored procedures
the user what we he can or can't replace in functions. It should be up to the user to decide what and how to replace so that all surrounding applications will say working. regards Asko PS: It all confuses poor developers Hi Asko, I work on web backend and am in the process of changing infodb.eurorates_exchange() and infodb._eurorates_lookup db functions found in dbs. Problem is the _eurorates_lookup function did not use IN OUT params but a type, we have been told we should update all functions to use IN OUT params. In doing so I will also need to drop the function when it comes to deployment. This function is in the accounts partition and I know we are not supposed to drop functions in partitions due to cache problems it creates. Could you tell me what I should do? Thanks On Tue, Aug 19, 2008 at 3:29 AM, Tom Lane [EMAIL PROTECTED] wrote: Asko Oja [EMAIL PROTECTED] writes: For users of stored procedures it is protection from downtime. For Skype it has been around 20% of databse related downtime this year. Perhaps Skype needs to rethink how they are modifying functions. The reason that this case wasn't covered in 8.3 is that there didn't seem to be a use-case that justified doing the extra work. I still haven't seen one. Other than inline-able SQL functions there is no reason to invalidate a stored plan based on the fact that some function it called changed contents. regards, tom lane
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Polite answers lead to polite discussions. Caling other people names lead to flame wars. It's perfectly ok for Skype to keep our own build of 8.3 with given patch and make it available for whoever might want it. At least now there is almost good enough description why the patch was needed althou it would have been more pleasant if the discussion had been constructive. We didn't keep close enough watch on the list when 8.3 plan invalidation was discussed and it came as bad surprise to us that some parts important to us were left out. By the way it's real nice what you are doing with in and exists improvements. Thanks. regards Asko On Tue, Aug 19, 2008 at 8:06 PM, Tom Lane [EMAIL PROTECTED] wrote: Dimitri Fontaine [EMAIL PROTECTED] writes: Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I could understand this level of complaining if this were a new problem that'd appeared in 8.3. But *every PG version that we've ever released* behaves the same way with respect to function drop/recreate. If the Skype folk have developed a way of working that is guaranteed not to work with any released version, one has to wonder what they were thinking. If you need to DROP rather than CREATE OR REPLACE functions, then 8.3 doesn't make things better for you than prior releases did, but it does't make them worse either. Making things better for that case is unequivocally a new feature. And it's rather a corner case at that, else there would have been enough prior complaints to put it on the radar screen for 8.3. What we've got at this point is a submitted patch for a new feature that hasn't even been accepted into HEAD yet. Lobbying to get it back-patched is entirely inappropriate IMHO. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. People are expected to use same workarounds as Skype is using. For us another unneccessary downtime week ago was what set us moving/thinking :). When you use software with limitations then you learn to live with them. Good thing about postgres you can do something yourself to get some of the limitations removed. As Pavel said you are probably using your own build anyway so one more patch should not be a problem. regards Asko On Tue, Aug 19, 2008 at 8:48 PM, Pavel Stehule [EMAIL PROTECTED]wrote: 2008/8/19 Dimitri Fontaine [EMAIL PROTECTED]: Le mardi 19 août 2008, Tom Lane a écrit : [ shrug... ] You have not found a bug in plan invalidation. You have found omitted functionality --- functionality that was *intentionally* omitted from the 8.3 version. Thanks a lot for this clarification, now I understand you viewpoint. So, the 8.3 fix would be about documenting this intentionnal omit in the great manual, maybe in a Limits section of the sql-createfunction page? Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I'm having a project here where the project manager wants a database function API to keep data logic at serverside, should I tell him to reconsider this while 8.4 is not ready? You could to use patched 8.3. We would then have to go live with an 8.3 based solution containing middleware code, then port it again to SQL functions when 8.4 is out stable. Not appealing, but I sure understand the no new feature in stable code base argument here. This problem isn't too hard without pooling. Not all systems are global - so usually is possible to find some window and recreate functions and close all user connections. Regards Pavel Stehule Regards, -- dim -- Sent 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: plan invalidation vs stored procedures
Every thread we are concerned in turns into something strange thing that is almost entirely differnet from the original intention. First thread we started was with the intention to discuss how we should handle the problem. Instead of discussion it was trolled into oblivion. Then we thought so what if no discussion we will submit a patch maybe people will understand we are serious. Nothing relevant came up. Spent week more to refine patch into something that looks good enough. And now we are having discusion what is bug and what s not in this thread. In the first message Martin asked There are probably a lot of details that I have overlooked. I'd be really thankful for some constructive comments and criticism. Especially, what needs to be done to have this in the core. Feedback appreciated. Can we get back to the topic? PS: We have 1+ functions (including lots of duplicates) PS: We are able to be as arrogant as any of you but we can get more things done with constructive comments. On Wed, Aug 20, 2008 at 2:53 AM, Andrew Dunstan [EMAIL PROTECTED] wrote: Tom Lane wrote: Also, there are a whole lot more considerations in a backpatch decision than just is it a bug. The (estimated) risk of creating new bugs and the extent to which the patch will change behavior that apps might be relying on are two big reasons why we might choose not to back-patch a bug fix. Right. And even if it is a bug the question might be what sort of bug is it? We might well be prepared to take some risks with code stability to plug security or data corruption bugs, a lot more than we would for other sorts of bugs. Even if this were considered a bug instead of a limitation, it doesn't come into the class of things we should be rushing to fix in the stable branches, unless the fix is fairly obvious and of limited impact, which is clearly not the case. cheers andrew
Re: [HACKERS] Compatibility types, type aliases, and distinct types
In my experience synonyms as well as rules are hacks and should be avoided althou there are cases where they can save some work for dba's during transitions from one situation to better one. There is also another possible way one might want to create a compatibility type. Instead of creating a new type, create an alias for an existing type, much like we currently have built-in mappings for int - int4, bigint - int8, etc. The difference here is that the type you put in is not the same as the one you get dumped out. So depending on taste and requirements, a user might want to choose the distinct type or the alias route. Example or two would be helpful here where you expect this kind of functionality be useful. Could you use it for defining Oracle compatibel varchar2 and how would it work then? On Mon, Aug 18, 2008 at 3:33 PM, Stephen Frost [EMAIL PROTECTED] wrote: * Peter Eisentraut ([EMAIL PROTECTED]) wrote: There is also another possible way one might want to create a compatibility type. Instead of creating a new type, create an alias for an existing type, much like we currently have built-in mappings for int - int4, bigint - int8, etc. The difference here is that the type you put in is not the same as the one you get dumped out. So depending on taste and requirements, a user might want to choose the distinct type or the alias route. The alias route gets me thinking about Oracle synonyms.. That'd be nice to have in PG for a number of object types. Most recently I was wishing I could create a schema synonym, though being able to do tables/views would have worked as well in that case, just a bit more work. What do you think about adding this kind of support to PostgreSQL? Obviously, some details need to be worked out, but most of this is actually straightforward catalog manipulation. I like the concept. Not sure how much I'd end up using it, personally. Thanks, Stephen -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkipbCgACgkQrzgMPqB3kiinmwCfROrhdu8YDpzsJvOtvpSW147O SOQAn3y/4MGadFz9VqDsmcm8fiKuxsn5 =gdfU -END PGP SIGNATURE-
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Does it change of result some queries? Patch in itself is not changing what the queries return. It just gets rid of error condition from which Postgres itself is not able to recover. It is protection to server's hang? For users of stored procedures it is protection from downtime. For Skype it has been around 20% of databse related downtime this year. On Mon, Aug 18, 2008 at 12:05 PM, Pavel Stehule [EMAIL PROTECTED]wrote: 2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? It is protection to server's hang? Regards, -- dim
Re: [HACKERS] proposal sql: labeled function params
Not able to means not implementable o not implemented ? On Sun, Aug 17, 2008 at 6:59 PM, Pavel Stehule [EMAIL PROTECTED]wrote: Hannu it's not possible inNot able to plpgsql, because we are not able iterate via record. Pavel 2008/8/17 Hannu Krosing [EMAIL PROTECTED]: On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Actually the most natural syntax to me is just f(name=value) similar to how UPDATE does it. It has the added benefit of _not_ forcing us to make a operator reserved (AFAIK = can't be used to define new ops) *What* are you thinking? I think that we could achieve what Pavel was after by allowing one to define something similar to keyword arguments in python. maybe allow input RECORD type, which is instantiated at call time by giving extra arguments to function call: CREATE FUNCTION f_kw(r record) and then if you call it like this: SELECT ... f_kw(name='bob', age=7::int) then function gets as its input a record which can be accessed in pl/pgsql like r.name r.age and if terseness is really appreciated then the it could also be called like this SELECT ... f_kw(name, age) from people where name='bob'; which is rewritten to SELECT ... f_kw(name=name, age=age) from people where name='bob'; not sure if we should allow defining SETOF RECORD and then enable calling it with SELECT * FROM f_kw( VALUES(name='bob', age=7::int), VALUES(name='bill', age=42::int ); or somesuch -- Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PgFoundry] Unsigned Data Types
I can say that we have had several times to use bigint instead because of the lack of uint type in postgres. On Sun, Aug 17, 2008 at 9:03 PM, Ryan Bradetich [EMAIL PROTECTED]wrote: On Sat, Aug 16, 2008 at 10:53 AM, Decibel! [EMAIL PROTECTED] wrote: On Aug 15, 2008, at 1:00 AM, Ryan Bradetich wrote: Here is the first pass at the unsigned data type I have been working on. I am planning on adding these to the September 2008 commitfest wiki page. The unsigned data type is not targeted for core, but for the uint PgFoundry project. Is the intention for the types to go into pg_catalog? It'd be nice if you could specify what schema they should be installed in. An uninstall would also be good. The pg_catalog made since to me at first (especially for my application), but on reflection I believe you are right. I will remove the references to the pg_catalog schema and allow the user to add the unsigned data type to any schema. Good catch on the uninstall script. I should have written this as well. I will post an update to the wiki later tonight. Thanks for doing this, I've wished we had uint types in the past, and I'm sure I will again in the future! I am glad it is useful. I needed it for my current project, and I was hoping others could use it as well. Thanks, - Ryan
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Hi We need plan invalidation fix in 8.3 also at least it would make migrating from 8.2 to 8.3 much more attractive. Currenlty we are having problems related to plan invalidation couple of times per week (mainly we have to let developers change their code before we release it into live databases but it feels like sitting on ticking bomb after previous downtime). Is it possible to get it into some official 8.3.x release or should we do it in house? Who should add it into september commitfest? Asko On Fri, Aug 15, 2008 at 2:13 PM, Martin Pihlak [EMAIL PROTECTED]wrote: Tom Lane wrote: Martin Pihlak [EMAIL PROTECTED] writes: Changing statement result type is also currently prohibited in StorePreparedStatement. There maybe good reasons for this, How about the SQL spec says so? Admittedly, it's a bit of a jump from views to prepared statements, but the spec is perfectly clear that altering a table doesn't alter any views dependent on it: SQL99 11.11 add column definition saith As you said it is a bit of a jump ... For one thing view definitions are persistent whereas statements are bound to be replanned sooner or later - reconnects etc. Disallowing replanning after invalidation just postpones it and meanwhile the cached plans are left unusable (cached plan must not change result). IMHO the problem should be left for the application to handle. Because this is where it will end up anyway. Attached is a patch that implements plan invalidation on function DROP, REPLACE and ALTER. Function oids used by the query are collected in analyze phase and stored in PlannedStmt. Only plans that reference the altered function are invalidated. The patch also enables replanning on result set change. regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch: Add columns via CREATE OR REPLACE VIEW
ALTER VIEW does not sound useful for me. CREATE OR REPLACE VIEW should create or replace view and only thing that should be same is the name of the view. It's up to Postgres to invalidate all plans and up to developer to make sure that all places where his view is used will stay still working. All this discussion about matching up columns and AI seems totally useless to me :) On Fri, Aug 8, 2008 at 4:41 AM, Robert Haas [EMAIL PROTECTED] wrote: Well, my feeling is that if we are inventing a new feature we ought not paint ourselves into a corner by failing to consider what will happen when obvious extensions to the feature are attempted. Whether the present patch is self-consistent is not the question --- the question is do we have a self-consistent vision of how we will later do the other stuff like renaming, changing column type, etc. If we can work out that design, I think that's great. However, it doesn't actually 100% matter whether we know the one true way that we will definitely implement those features - it only matters that none of the things we might choose are inconsistent with what we're doing now. In order to avoid being AI-complete, REPLACE VIEW needs some kind of straightforward algorithm for matching up the old and new target lists. AFAICS, the only thing to decide here is what you want to use as the key. There are three possibilities that I can think of: [1] name, [2] position, [3] both name and position. It's axiomatic that REPLACE VIEW can't be given the capability to make any modification that involves changing the key field, so in [1] you can't rename columns, in [2] you can't reorder columns, and in [3] you can't do either. Furthermore, in [2], you also can't support dropping columns, because a drop is indistinguishable from renaming and retyping every column from the point of the drop onwards. Therefore, the maximum set of operations REPLACE VIEW can potentially support in each scenario are: [1] add column, change type, drop column, reorder columns [2] add column, change type, rename [3] add column, change type, drop column The actual set of operations supported may be less either because of implementation limitations or because you don't want to provide users with a foot-gun. ISTM that allowing REPLACE VIEW to do renames in scenario [2] can be pretty much rejected outright as a violation of the principle of least surprise - there is an enormous danger of someone simultaneously renaming and retyping a whole series of columns when they instead intended to drop a column. Similarly, in scenario [1] or [3], ISTM that allowing someone to drop columns using REPLACE VIEW is something of a foot-gun unless we are in scenario [1] and reordering columns is also implemented, because users who don't RTFM will try to reorder columns and it will succeed and fail erratically according to whether there are dependencies that prevent dropping and re-adding whatever subset of columns need to be shuffled to create the same effect as would be produced by reordering. However, in any scenario, I can't see how adding columns or changing column types is likely to produce any confusion or user-unexpected behavior. Perhaps I'm missing something? Personally, I favor scenario [1]. I hardly ever rename database columns, and I don't mind needing to ALTER VIEW RENAME COLUMN on those rare occasions when I do, but I add new columns to my tables (which then also need to be added to my views) on a regular basis. If I could keep groups of related columns together in the table and view definitions without having to drop and recreate the objects, that would be awesome. But I'm not sure it's worth the amount of implementation that would be required to get there, especially if all of that implementation would need to be done by me (and double-especially if none of it would likely be included in -core). Of course, as I said before, nothing we do in REPLACE VIEW precludes having a powerful implementation of ALTER VIEW. But I think the coding to make ALTER VIEW do these operations is a lot trickier, because you have to deal with modifying the query that's already in place piecemeal as you make your changes to the view. It's not that it can't be done, but I doubt it can be done in an 8K patch, and as mentioned upthread, it certainly can't be done in a fully general way... you will still frequently need to CREATE OR REPLACE VIEW afterwards. To put that another way, ALTER TABLE is a complicated beast because you have to worry about how you're going to handle the existing data, and ALTER VIEW will be a complicated beast for the analogous reason that you need to worry about handing the existing rewrite rule. But at the moment when a REPLACE VIEW command is executed, that problem goes away, because now you have the query in your hand and just need to make the relation match it without breaking any of the dependencies. ...Robert --
Re: [HACKERS] Avoiding Application Re-test
It would make PostgreSQL too much like Oracle ;) Let's keep PostgreSQL simple and compact please. I prefer applications retest when migrating to new PostgreSQL version. In this case surprises happen then you expect them not in some unforeseen point of time in the future. Keeping all this old functionality around will make maintenance and adding new stuff harder. It also complicates tracking problems where in addition to db version you need to find out what version it is supposed to emulate. On Thu, Aug 7, 2008 at 5:17 PM, Simon Riggs [EMAIL PROTECTED] wrote: Tom's recent changes to allow hash distinct (yay!) prompted something that I'd thought about previously. Subtle changes in the output of queries can force an application retest, which then can slow down or prevent an upgrade to the latest release. We always assume the upgrade itself is the problem, but the biggest barrier I see is the cost and delay involved in upgrading the application. We could invent a new parameter called enable_sort_distinct, but thats way too specific and horrible. What I would like is a parameter called sql_compatibility which has settings such as 8.3, 8.4 etc.. By default it would have the value 8.4, but for people that want to upgrade *without* retesting their application, they could set it to 8.3. Every time we introduce a feature that changes output, we just put an if test in saying sql_compatibility = X, (the release we added feature). Straightforward, futureproof. Cool. Not foolproof, but still worth it. This would allow many users to upgrade to 8.4 for new features, yet without changing apps. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plan invalidation vs stored procedures
Don't you think we try to be careful but still we manage to overlook several times in year something and cause some stupid downtime. On Wed, Aug 6, 2008 at 9:13 PM, Merlin Moncure [EMAIL PROTECTED] wrote: On Wed, Aug 6, 2008 at 2:20 AM, Marko Kreen [EMAIL PROTECTED] wrote: But the main problem is that if the DROP/CREATE happens, the failure mode is very nasty - you get permanent error on existing backends. (Main case I'm talking about is functions calling other functions.) Some sorta recovery mode would be nice to have, it does not even need function perfectly. Giving error once and then recover would be better than requiring manual action from admin. sure -- this a known issue --, but the point is that there are not that many reasons why you have to drop/create a function if you are careful. hiding function prototypes is actually pretty powerful although you have to deal with creating the extra types. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plan invalidation vs stored procedures
Hi Sadly PostgreSQL inability to invalidate plan cache when function is dropped causes us downtime and costs money. ERROR: cache lookup failed for function 24865) This time our developers just rewrote function to use OUT parameters instead of return type. Currently i had to forbid dropping functions in our most critical databases but that makes developers unhappy. And as i understand it is not fixed in 8.3: Comment from code * Currently, we use only relcache invalidation events to invalidate plans. * This means that changes such as modification of a function definition do * not invalidate plans using the function. This is not 100% OK --- for * example, changing a SQL function that's been inlined really ought to * cause invalidation of the plan that it's been inlined into --- but the * cost of tracking additional types of object seems much higher than the * gain, so we're just ignoring them for now. So we will have to get it fixed and better would be to do it so that solution suits everybody. Our current workaround include updating pg_proc after release or letting pgBouncer to reconnect all connections but neither solution is good and cause us to lose valuable minutes in error flood when we miss some crucial drop function. Asko On Tue, Aug 5, 2008 at 1:40 PM, Pavel Stehule [EMAIL PROTECTED]wrote: Hello try version 8.3. There lot of dependencies are solved. Regards Pavel Stehule 2008/8/5 Martin Pihlak [EMAIL PROTECTED]: Howdy, What is the status of plan invalidation vs stored procedures? From the initial design discussion I understand that function change handling was postponed to some time in the future. Is anybody already working on that or maybe some ideas of how to implement this? The business case for the feature is that most of our db logic is inside stored procedures and hence use cached plans. Every time a function is dropped and recreated we get a storm of cache lookup failed errors. If we are lucky, the DBA will detect it and apply appropriate workarounds. If not ... things get messy. We are considering of hacking up a proprietary solution to address our specific problems (e.g. invalidate every plan on pg_proc changes). But I think that this is something that would be useful to a wider audience and deserves a more general solution. How about it? regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plan invalidation vs stored procedures
Hi Thanks for pointing to another thing to fix :) postgres=# create type public.ret_status as ( status integer, status_text text); CREATE TYPE postgres=# create or replace function pavel ( i_param text ) returns public.ret_status as $$ select 200::int, 'ok'::text; $$ language sql; CREATE FUNCTION postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. Asko On Tue, Aug 5, 2008 at 4:00 PM, Pavel Stehule [EMAIL PROTECTED]wrote: 2008/8/5 Martin Pihlak [EMAIL PROTECTED]: Pavel Stehule wrote: Hello try version 8.3. There lot of dependencies are solved. Yes, 8.3 was the version I was testing with. Same results on the HEAD: $ psql -e -f test.sql select version(); version -- PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2) (1 row) create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 regards, Martin use CREATE OR REPLACE FUNCTION syntax without DROP FUNCTION, CREATE FUNCTION .. 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] plan invalidation vs stored procedures
This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? I totally agree we should get this fixed first :) postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. On Tue, Aug 5, 2008 at 4:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Martin Pihlak [EMAIL PROTECTED] writes: create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? 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] plan invalidation vs stored procedures
postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; CREATE FUNCTION postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text, more_text OUT text ) returns record as $$ select 200::int, 'ok'::text, 'tom'::text; $$ language sql; ERROR: cannot change return type of existing function DETAIL: Row type defined by OUT parameters is different. HINT: Use DROP FUNCTION first. On Tue, Aug 5, 2008 at 5:04 PM, Asko Oja [EMAIL PROTECTED] wrote: This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? I totally agree we should get this fixed first :) postgres=# create or replace function pavel ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. On Tue, Aug 5, 2008 at 4:51 PM, Tom Lane [EMAIL PROTECTED] wrote: Martin Pihlak [EMAIL PROTECTED] writes: create function foo() returns integer as $$ begin return 1; end; $$ language plpgsql; CREATE FUNCTION prepare c1 as select * from foo(); PREPARE execute c1; foo - 1 (1 row) drop function foo(); DROP FUNCTION create function foo() returns integer as $$ begin return 2; end; $$ language plpgsql; CREATE FUNCTION execute c1; psql:test.sql:11: ERROR: cache lookup failed for function 36555 This is simply a bad, wrong, stupid way to do it. Why do you not use CREATE OR REPLACE FUNCTION? 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] Status of DISTINCT-by-hashing work
Sounds very much like 80% 20% story. 80% that was easy to do is done and now 20% that is complex and progress is slow is left to be done. Sounds very familiar from the comment in plan cache invalidation :) On Tue, Aug 5, 2008 at 5:51 PM, Tom Lane [EMAIL PROTECTED] wrote: I've pretty much finished the project I got a bee in my bonnet about last week, which is to teach SELECT DISTINCT how to (optionally) use hashing for grouping in the same way that GROUP BY has been able to do for awhile. There are still two places in the system that hard-wire the use of sorting for duplicate elimination: * Set operations (UNION/INTERSECT/EXCEPT) * Aggregate functions with DISTINCT I'm thinking of trying to fix set operations before I leave this topic, but I'm not sure it's worth the trouble to change DISTINCT aggregates. They'd be a lot more work (since there's no executor infrastructure in place that could be used) and the return on investment seems low. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] \ef function in psql
Marko is talking about types created with CREATE TYPE CREATE FUNCTION fraud.get_user_status( i_key_user text ) RETURNS ret_get_user_status AS $$ Current pg_dump annoyingly removes schem reference from type. On Wed, Jul 23, 2008 at 6:19 PM, Tom Lane [EMAIL PROTECTED] wrote: Marko Kreen [EMAIL PROTECTED] writes: [ re pg_get_functiondef ] Please make it use full qualified names (schema.name) for both function name and result types. Current search_path juggling the pg_dump does is major PITA. Qualifying the function name seems like a good idea, but I'd advise against tinkering with the datatype references. It'll be hard to do correctly and it will make things very substantially uglier. Do you really want to show, eg, pg_catalog.int4 rather than integer? If you leave the backend code do what it wants to do here, the only way that there would be a problem is if someone changed their search_path in between pg_get_functiondef and trying to re-load the function definition. Which certainly ain't gonna happen for \ef, and it seems a bit implausible for any other use-case either. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] \ef function in psql
Not so sure about omitting OR REPLACE. In my experience it is more often needed than not. Main argument for omitting might be to protect hackers from carelesse users :) On Wed, Jul 23, 2008 at 5:50 PM, Tom Lane [EMAIL PROTECTED] wrote: Abhijit Menon-Sen [EMAIL PROTECTED] writes: At 2008-07-17 18:28:19 -0400, [EMAIL PROTECTED] wrote: It wouldn't take a whole lot to convince me that a pg_get_functiondef would be useful, although I don't foresee either of those applications wanting to use it because of their backward-compatibility constraints. What would the function return? CREATE OR REPLACE FUNCTION ...? Would that be good enough for everyone who might want to call it? I think I'd go with CREATE FUNCTION for simplicity. It would be easy enough for something like \ef to splice in OR REPLACE before shipping the command back to the server. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TABLE-function patch vs plpgsql
Tom RETURNS TABLE columns semantically just the same as OUT parameters. I hope you are not proposing to create another case of crippled OUT parameters that are quite problematic to use together with inline sql or has it gotten fixed on the road (we are still using 8.2 on most of our servers). On Fri, Jul 18, 2008 at 2:13 AM, Tom Lane [EMAIL PROTECTED] wrote: I've been working on the TABLE-function patch, and I am coming to the conclusion that it's really a bad idea for plpgsql to not associate variables with output columns --- that is, I think we should make RETURNS TABLE columns semantically just the same as OUT parameters. Here are some reasons: 1. It's ludicrous to argue that standards compliance requires the behavior-as-submitted. plpgsql is not specified by the SQL standard. 2. Not having the parameter names available means that you don't have access to their types either, which is a big problem for polymorphic functions. Read the last couple paragraphs of section 38.3.1: http://developer.postgresql.org/pgdocs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES as well as the following 38.3.2. How would you do those things with a polymorphic TABLE column? 3. Not treating the parameters as assignable variables makes RETURN NEXT nearly worthless in a TABLE function. Since they're not assignable, you can't use the parameterless form of RETURN NEXT (which'd return the current values of the variables). The only alternative available is to return a record or row variable; but there's no convenient way to declare such a variable, since after all the whole point here is that the function's output rowtype is anonymous. 4. It's a whole lot easier to explain things if we can just say that OUT parameters and TABLE parameters work alike. This is especially true when they actually *are* alike for all the other available PLs. If we insist on the current definition then we are eventually going to need to kluge up some solutions to #2 and #3, which seems like make-work to me when we already have smooth solutions to these problems for OUT parameters. Comments? For the archives, here is the patch as I currently have it (with the no-plpgsql-variables behavior). But unless I hear a good argument to the contrary, I'm going to change that part before committing. 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] Do we really want to migrate plproxy and citext into PG core distribution?
Hi hackers Just my non hacker view on the pl/proxy matter. From FAQ: PL/Proxy is compact language for remote calls between PostgreSQL databases. Why we submitted pl/proxy into core at all? 1. Current core distribution contains dblink which sucks both usability wise and security wise but being part of core distribution will be first thing people are going to try out. We wanted to save people losing couple of days trying out dblink before looking for other alternatives like it happend with us. 2. Various languages are part of core distribution and pl/proxy by adding possibility to call remotely procedures created with these languages seems to be logical extension to PostgreSQL in general. And it makes it essential for pl/proxy to stay compatible with all the developments in function calling syntax. 3. And last but not least to make it easier to use for whoever who might need to do remote procedure calls between PostgreSQL servers. So i rephrase your question: Would capability to do remote procedure calls useful addition to PostgreSQL feature set? In my experience when organization grows out of one database on one server remote calls are needed quite soon. About citext. Skype is using various hacks and workarounds because there was no such type in PostgreSQL and i understand others also. To me it seems to be choice between couple of developers doing it once and for all and hundreds of developers inventing the wheel every day and not to mention hours spent debugging over various layers of applications. It just shows how hackers have totally different point of view on things from people who are using the program:) But again i am just a manager and should be lower than grass in hackers list :) regards. Asko skype: askoja PS: I am sorry for this reply coming so late didn't want to spoil my vacation :) On Mon, Jul 21, 2008 at 10:43 PM, Tom Lane [EMAIL PROTECTED] wrote: The current commitfest queue has two entries that propose to migrate existing pgfoundry projects (or improved versions thereof) into our core distribution. The more I think about this the less happy I am with it. From a maintenance point of view there seems little need for either project to get integrated: they don't appear to have much of any code that is tightly tied to backend innards. From a features point of view, yeah they're cool, but there are scads of cool things out there. From a project-management point of view, it's insanity to set a presumption that pgfoundry is just a proving ground for code that should eventually get into core once it's mature enough or popular enough or whatever. We *have to* encourage the development of a cloud of subprojects around the core, or core will eventually collapse of its own weight. We have not got the manpower to deal with an ever-inflating collection of allegedly core code. If anything, we ought to be working to push more stuff out of the core distro so that we can focus on the functionality that has to be there. So my feeling is that we should not accept either of these patches. Now, there is some value in submitting the code for review --- certainly citext is a whole lot better than it was a few weeks ago. I think it would be a good idea to be open to reviewing pgfoundry code with the same standards we'd use if we were going to integrate it. Perhaps commitfest is not the right venue for that, though, if only because of the possibility of confusion over what's supposed to happen. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Do we really want to migrate plproxy and citext into PG core distribution?
Hi One of reasons to get PL/proxy into core is to make it available to Windows users also. The idea is to get to the situation createlang plproxy mydb If we can achieve this without putting plproxy into core then i would like to hear how. Asko On Fri, Jul 25, 2008 at 2:19 AM, Tom Lane [EMAIL PROTECTED] wrote: Robert Haas [EMAIL PROTECTED] writes: ISTM that if that if you're willing to admit, even with caveats, that PL/perl, PL/tcl, or PL/python doesn't need to be in core, then excluding anything else from core on the basis that it doesn't need to be there is silly. You are merely setting up a straw man, as no one has suggested such a policy. Any specific decision of this type is going to involve a combination of factors, and that's only one. 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] Add dblink function to check if a named connection exists
Just use plproxy and skip all the hassle of dblink :)
On Mon, Jun 2, 2008 at 3:14 AM, Joe Conway [EMAIL PROTECTED] wrote:
Tom Lane wrote:
Tommy Gildseth [EMAIL PROTECTED] writes:
One obvious disadvantage of this approach, is that I need to connect and
disconnect in every function. A possible solution to this, would be having a
function f.ex dblink_exists('connection_name') that returns true/false
depending on whether the connection already exists.
Can't you do this already?
SELECT 'myconn' = ANY (dblink_get_connections());
A dedicated function might be a tad faster, but it probably isn't going
to matter compared to the overhead of sending a remote query.
I agree. The above is about as simple as
SELECT dblink_exists('dtest1');
and probably not measurably slower. If you still think a dedicated function
is needed, please send the output of some performance testing to justify it.
If you really want the notational simplicity, you could use an SQL function
to wrap it:
CREATE OR REPLACE FUNCTION dblink_exists(text)
RETURNS bool AS $$
SELECT $1 = ANY (dblink_get_connections())
$$ LANGUAGE sql;
contrib_regression=# SELECT dblink_exists('dtest1');
dblink_exists
---
f
(1 row)
I guess it might be worthwhile adding the SQL function definition to
dblink.sql.in as an enhancement in 8.4.
Joe
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
