Re: [HACKERS] damage control mode
On Sun, Feb 7, 2010 at 11:23 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Feb 7, 2010 at 3:37 PM, Josh Berkus j...@agliodbs.com wrote: As between the two, I get the feeling that there is more interest in writeable CTEs. But that impression might be wrong, since it's an unscientific recollection of discussions on -hackers; which are themselves not representative of anything. Writeable CTE is definitely the bigger feature. Effectively, it allows people to do in a single query data-transformation operations which would have taken a stored procedure before. Think of it as comparable to the introduction of callbacks in Perl for coolness. Now if I knew what callbacks in Perl were, I'd probably be impressed. You mean closures? I have not looked at the window functions patch at all, and I haven't looked at the latest version of writeable CTEs, either. I will try to spend some time on it in the next couple of days. My feeling about the last version is that it lacked a lot in the documentation department, and also in the comments department. Since I don't know that code very well, that made it hard for me to assess technical correctness. Hmmm, that's potentially lethal. David Fetter has been doing a lot of presentations on the feature; surely he could turn them into some documentation? David? I would be 100% in favor of some more help on the documentation. I do plan to reread this patch, but I don't know that I can cover the amount of work that needs to be done myself, and as you say, lack of adequate documentation could very well kill this patch. In fact, I'll go so far as to say it's one of the most likely reasons why this patch might not get in. So any resources we can bring to bear on that issue would be well spent. I'm on board to work on the documentation. I think with a few hours of work it should be in a reasonable state. 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] LISTEN/NOTIFY and notification timing guarantees
On Tue, Feb 16, 2010 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: 2. Add an extra lock to serialize writers to the queue, so that messages are guaranteed to be added to the queue in commit order. As long as fwiw, I think you're definitely on the right track. IMO, any scenario where an issued notification ends up being deferred for an indefinite period of time without alerting the issuer should be avoided if at all possible. Just to clarify though, does your proposal block all notifiers if any uncommitted transaction issued a notify? 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] Listen / Notify - what to do when the queue is full
On Thu, Feb 18, 2010 at 12:58 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, 2010-02-15 at 15:00 -0500, Tom Lane wrote: Joachim Wieland j...@mcknight.de writes: We could probably fake this on the Hot Standby in the following way: We introduce a commit record for every notifying transaction and write it into the queue itself. So right before writing anything else, we write an entry which informs readers that the following records are not yet committed. Then we write the actual notifications and commit. In post-commit we return back to the commit record and flip its status. This doesn't seem likely to work --- it essentially makes commit non atomic. There has to be one and only one authoritative reference as to whether transaction X committed. I thought a bit more about this and don't really understand why we need an xid at all. When we discussed this before the role of a NOTIFY was to remind us to refresh a cache, not as a way of delivering a transactional payload. If the cache refresh use case is still the objective why does it matter whether we commit or not when we issue a NOTIFY? Surely, the rare case where we actually abort right at the end of the transaction will just cause an unnecessary cache refresh. notifications serve many more purposes than cache refreshes...it's a generic 'wake up and do something' to the client. For example, one of those things could be for the client to shut down. If the server errors out of the transaction that set up the client to shut down, you probably wouldn't want the client to shut down. I don't think that's a big deal really, but it conflicts with the old behavior. However, being able to send notifications immediately (not at end of transaction) would be exceptionally useful in some cases. This happens when the notifying backend is waiting on some sort of response from the notified client. If you could NOTIFY IMMEDIATELY, then you could ping the client and get the response in a single transaction without using dblink based hacks. 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] scheduler in core
On Sat, Feb 20, 2010 at 4:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this What we need: - a shared catalog - an API for filling the catalog - a scheduler daemon - pg_dump support A shared catalog - Why shared? obviously because we don't want to scan all database's pg_job every time the daemon wake up. Maybe something like: pg_job ( oid -- use the oid as pk jobname jobdatoid -- job database oid jobowner -- for permission's checking jobstarttime -- year to minute jobfrequency -- an interval? jobnexttime or joblasttime jobtype -- if we are going to allow plain sql or executable/shell job types jobexecute or jobscript ) comments about the catalog? An API for filling the catalog - do we want a CREATE JOB SQL synatx? FWIW, Oracle uses functions to create/remove jobs. An scheduler daemon I think we can use 8.3's autovacuum daemon as a reference for this... AFAIK, it's a child of postmaster that sleep for $naptime and then looks for something to do (it also looks in a catalog) and the send a worker to do it that's what we need to do but... for the $naptime i think we can autoconfigure it, when we execute a job look for the next job in queue and sleep until we are going to reach the time to execute it i don't think we need a max_worker parameter, it should launch as many workers as it needs pg_dump support -- dump every entry of the pg_job catalog as a CREATE JOB SQL statement or a create_job() function depending on what we decided ideas? comments? IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. 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] scheduler in core
On Sat, Feb 20, 2010 at 8:06 PM, Joshua D. Drake j...@commandprompt.com wrote: That doesn't mean it isn't a really good idea. It would be nice to have a comprehensive job scheduling solution that allows me to continue abstract away from external solutions and operating system dependencies. +1! Aa scheduler is an extremely common thing to have to integrate with the database. All of our commercial competitors have them, and they are heavily used. Like I noted above, what people want to schedule is going to be stored procedures. Having both would virtually eliminate the need for scripting outside the database, which is a pretty big deal since external scripts are a real pain to keep cross platform. Since there's probably a lot of overlapping problems in those two features, why not tackle both at once? 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] scheduler in core
On Mon, Feb 22, 2010 at 2:29 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Merlin Moncure escribió: Like I noted above, what people want to schedule is going to be stored procedures. Having both would virtually eliminate the need for scripting outside the database, which is a pretty big deal since external scripts are a real pain to keep cross platform. Since there's probably a lot of overlapping problems in those two features, why not tackle both at once? Divide and conquer? When I meant 'tackle', it is more of a 'come to an understanding' thing. Normally I would agree with you anyways, but I think what most people would want to schedule would be stored procedures (sorry to continually repeat myself here, but I really think this should be critical to any scheduling proposal), not functions or ad hoc scripts. 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] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Mon, Feb 22, 2010 at 2:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: I still haven't seen a good reason for not using cron or Task Scheduler or other standard tools. *) provided and popular feature in higher end databases *) the audience you cater to expects it *) IMO, it should simply not be necessary to incorporate a secondary scripting environment to do things like vacuum and backups *) portable. for example, you can dump a database on linux and restore to windows without re-implementing your scheduler/scripts as a consequence, *) off the shelf utilities/pgfoundry projects, etc can rely and utilize scheduling behavior 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] scheduler in core
On Mon, Mar 1, 2010 at 4:43 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? yeah...that's what I meant. plpgsql exceptions are no help because there are many cases where you simply don't want the whole sequence of operations to run in a single transaction. loading lots of data to many tables is one. any operation that depends on transaction commit to do something (like notifications) and then hook on the results is another. you always have the heavy hitting administrative functions like vacuum, etc. another case is if you want a procedure to simply run forever...trivially done in a procedure, impossible in a function. The way people do this stuff now is to involve an 1) external scheduler such as cron and 2) .sql scripts for relatively simple things and/or a external scripting language like bash/perl. The external scheduler has a couple of annoying issues...completely not portable to code against and scheduling sub minute accuracy is a big headache. Also, adjusting the scheduling based on database events is, while not impossible, more difficult than it should be. External .sql scripts are portable but extremely limited. Involving something like perl just so I can jump outside the database to do manual transaction management is fine but ISTM these type of things are much better when done inside the database IMNSHO. Another factor here is that a sizable percentage of our user base is bargain hunters coming in from other systems like oracle and ms sql and having to rely in o/s scheduler is very distasteful to them. It's a hole, one of the last remaining IMO, in postgres being able to provide a complete server side development environment without having to deal with the o/s at all. I stand by my statements earlier. Any moderate level and up complexity database has all kinds of scheduling and scripting going on supporting it. These things really should be part of the database, dump with it, and run in a regular way irregardless of platform and server environment etc. With that, 90% of the code I have to write outside of the database goes away. 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] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/5 François Pérou francois.pe...@free.fr: = All non-aggregate fields must be present in the GROUP BY clause http://drupal.org/node/30 My take is that this is never going to happen unless we are strictly talking about cases where the non-aggregate fields can be unambiguously determined. If we aren't, mysql is wrong to allow this, and developers that depend on it are wrong, and that is pretty much all you are ever going to get from this list. :-) The other stuff is mainly tangential fluff issues (takes 1% extra effort to write portable sql for) except for the flexible multi table delete, which would be nice although I wouldn't expect a strict copy of mysql syntax. I am personally looking at writeable CTE (which didn't make 9.0) to do most of the things I would need to do with a multi table delete feature, plus a quite a few other things. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch (for 9.1) string functions
On Tue, Mar 9, 2010 at 9:30 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
postgres=# select concat('ahaha',10,null,current_date, true);
concat
ahaha,10,,2010-03-09,t
why are there commas in the output?
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch (for 9.1) string functions
On Tue, Mar 9, 2010 at 1:45 PM, Pavel Stehule pavel.steh...@gmail.com wrote: updated version, concat function doesn't use separator btw...very cool stuff. I took a brief look at the sprintf implementation. The main switch: switch (pdesc.field_type) It looks like format codes we choose not to support (like %p) are silently ignored. Is this the correct behavior? 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] Dyamic updates of NEW with pl/pgsql
On Wed, Mar 10, 2010 at 7:50 AM, Andrew Dunstan and...@dunslane.net wrote: 2010/3/9 strk s...@keybit.net: How can a pl/pgsql trigger change the values of dynamic fields in NEW record ? By dynamic I mean that the field name is a variable in the trigger context. I've been told it's easy to do with pl/perl but I'd like to delive a pl/pgsql solution to have less dependencies. Using an hstore in 9.0 it's not too bad, Try something like: Agree 100%. The new hstore going to completely nail a broad class of issues that have historically been awkward in plpgsql functions. (small aside: the other biggie would be able to push a composite type in to an update statement...something like 'update foo set foo = new'). This is really great...some variant of this question is continually asked it seems. 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] Dyamic updates of NEW with pl/pgsql
On Thu, Mar 11, 2010 at 11:24 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Merlin Moncure escribió: (small aside: the other biggie would be able to push a composite type in to an update statement...something like 'update foo set foo = new'). This is really great...some variant of this question is continually asked it seems. Can't you already do that with EXECUTE ... USING NEW? hmm, ah, but you have to specify the columns in NEW, so it doesn't really work for you, does it? right...with inserts you can expand the composite type without listing the columns. updates can't do it because of syntax issues, even if you go dynamic. 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] Dyamic updates of NEW with pl/pgsql
On Fri, Mar 12, 2010 at 3:01 PM, Boszormenyi Zoltan z...@cybertec.at wrote: What's wrong with UPDATE foo SET (foo) = (NEW); ? amen brother! :-) I say though, since you can do: SELECT foo FROM foo; why not UPDATE foo SET foo = new;? 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] Dyamic updates of NEW with pl/pgsql
On Sat, Mar 13, 2010 at 11:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: (This will also be my main objection to letting hstore into core. It has not solved the problem of handling real datatypes.) Is this problem solvable then? Some variant of this question comes up almost weekly. It just doesn't seem right that you should have to write N trigger functions over N tables to a highly related operations. pl/perl is a huge dependency to bring in just to able to do things this. I understand hacking things through the text route is possibly not a direction should be encouraged...but is there an alternative? Is it theoretically possible to write functions that can switch out types based on context while still having static plans? 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] Dyamic updates of NEW with pl/pgsql
On Sat, Mar 13, 2010 at 1:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wonder if it could work to treat the result of a record-fieldname operator as being of UNKNOWN type initially, and resolve its actual type in the parser in the same way we do for undecorated literals and parameters, to wit * you can explicitly cast it, viz (record-fieldname)::bigint * you can let it be inferred from context, such as the type of whatever it's compared to * throw error if type is not inferrable Then at runtime, if the actual type of the field turns out to not be what the parser inferred, either throw error or attempt a run-time type coercion. Throwing error seems safer, because it would avoid surprises of both semantic (unexpected behavior) and performance (expensive conversion you weren't expecting to happen) varieties. But possibly an automatic coercion would be useful enough to justify those risks. the casting rules are completely reasonable. Throwing an error seems like a better choice. Better to be strict now and relax the rules later. record-fieldname takes a string (possibly a variable)? If so, his would nail the problem. This would work with run time typed records (new, etc)? 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] Dyamic updates of NEW with pl/pgsql
On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan and...@dunslane.net wrote:
Not quite, but close. We also need a nice way of querying for field names
(at least) at run time. I've seen that requested several times.
ok. just making sure we were on the same page. wasn't there a
technical objection to querying the fields at runtime? If not, maybe
you could get by with something like:
Integer variant of operator pulls fields by index
somettype v := recvar-3;
integer n := nfields(recordtype);
text[] fields := fieldnames(recordtype);
text fieldname := fieldname(recordtype, 3);
int fieldpos := fieldpos(recordtype, 'a_field');
OK, from archives (Tom wrote) quoting:
So, inventing syntax at will, what you're imagining is something like
modified := false;
for name in names(NEW) loop
-- ignore modified_timestamp
continue if name = 'modified_timestamp';
-- check all other columns
if NEW.{name} is distinct from OLD.{name} then
modified := true;
exit;
end if;
end loop;
if modified then ...
While this is perhaps doable, the performance would take your breath
away ... and I don't mean that in a positive sense. The only way we
could implement that in plpgsql as it stands would be that every
single execution of the IF would invole a parse/plan cycle for the
$1 IS DISTINCT FROM $2 expression. At best we would avoid a replan
when successive executions had the same datatypes for the tested
columns (ie, adjacent columns in the table have the same types).
Which would happen some of the time, but the cost of the replans would
still be enough to sink you.
/end quote
does the parse/plan objection still hold?
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] Dyamic updates of NEW with pl/pgsql
On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane t...@sss.pgh.pa.us wrote: If we make the implementation be such that (rec-field)::foo forces a runtime cast to foo (rather than throwing an error if it's not type foo already) yeah...explicit cast should always do 'best effort' The cost of looking up the ever-changing cast function could still be unpleasant, although I think we could hide it in the executor expression node instead of forcing a whole new parse/plan cycle each time. right. if you do that, it's still going to be faster than the dyna-sql/information schema/perl hacks people are doing right now (assuming they didn't give up and code it in the app). This is rtti for plpgsql, and functions that use it are going have to be understood as being slower and to be avoided if possible, like exception handlers. IMNSHO, this is a small price to pay. 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] Dyamic updates of NEW with pl/pgsql
On Mon, Mar 15, 2010 at 12:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane t...@sss.pgh.pa.us wrote: If we make the implementation be such that (rec-field)::foo forces a runtime cast to foo (rather than throwing an error if it's not type foo already) yeah...explicit cast should always do 'best effort' Probably so. But is it worth inventing some other notation that says expect this field to be of type foo, with an error rather than runtime cast if it's not? If we go with treating the result of - like UNKNOWN, then you wouldn't need that in cases where the parser guesses the right type. But there are going to be cases where you need to override the guess without necessarily wanting to buy into a forced conversion. Maybe. That behaves like oid vector to PQexecParams, right? Suggests a type but does not perform a cast. I see your point but I think it's going to go over the heads of most people...type association vs type coercion. Maybe instead you could just supply typeof function in order to provide very rigorous checking when wanted and presumably allow things like pointing the assignment at a special field. 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] Dyamic updates of NEW with pl/pgsql
On Tue, Mar 16, 2010 at 5:53 PM, Florian Pflug fgp.phlo@gmail.com wrote: which returns the field named field from the record. The expected field type is specified by providing a default value in defval of the expected type. Since that argument's type is ANYELEMENT, just like the return type, the type system copes perfectly with the varying return type. You can choose whether to auto-coerce the field's value if it has a type other than defval's type or whether to raise an error. So in essence I'm using the ANYELEMENT trick to get a poor man's version of your idea that doesn't require core changes. My post about this module got zero responses though... Why should we use what you've already written when we can just write it ourselves? Next you are going to say you're already using it and it works really well :-). I think it's pretty cool. Is it safe to have the main functions immutable and not stable though? Is there any benefit missed by not going through pl/pgsql directly (I'm guessing maybe more elegant caching)? It's a little weird that you can return anyelement from your function in cases that don't guarantee a type from the query. Are there any downsides to doing that? 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] SELECT constant; takes 15x longer on 9.0?
On Tue, Apr 6, 2010 at 1:47 AM, Josh Berkus j...@agliodbs.com wrote: Hackers, Continuing the performance test: DBD, like a number of monitoring systems, does pings on the database which look like this: SELECT 'DBD::Pg ping test'; In our test, which does 5801 of these pings during the test, they take an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ). did your pings change? on my machine the query ';' completes in about 0.05ms but any select takes 0.19 - 0.25ms. 0.77 is awfully high -- there has to be an explanation. 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] SELECT constant; takes 15x longer on 9.0?
On Tue, Apr 6, 2010 at 12:08 PM, David E. Wheeler da...@kineticode.com wrote: On Apr 6, 2010, at 2:32 AM, Takahiro Itagaki wrote: In our test, which does 5801 of these pings during the test, they take an average of 15x longer to execute on 9.0 as 8.4 ( 0.77ms vs. 0.05ms ). Any clue why this would be? Did you use the same configure options between them? Yes. For example, --enable-debug or --enable-cassert. hmm. ssl? (I don't see any interesting difference in time either btw). can you log in w/psql and confirm the difference there w/timing switch? 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] SELECT constant; takes 15x longer on 9.0?
On Tue, Apr 6, 2010 at 3:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Pavel Stehule pavel.steh...@gmail.com writes: it is little bit offtopic. Can we add info about assertation to version() output? Greg has the right idea: show debug_assertions. why not the entire set of configure options? 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] Differential backup
On Tue, Apr 27, 2010 at 10:32 AM, Michael Tharp g...@partiallystapled.com wrote: On 04/27/2010 09:59 AM, Kevin Grittner wrote: Under what circumstances would PostgreSQL modify a file without changing the last modified timestamp or the file size? Do all OSes have sub-second precision mtimes? Because otherwise I could see a scenario such at this: * File is modified * Backup inspects and copies the file in the same second * File is modified again in the same second, so the mtime doesn't change * Backup is run again some time later and sees that the mtime has not changed Even with microsecond precision this kind of scenario makes me squidgy, especially if some OSes decide that skipping frequent mtime updates is OK. Florian's point about clock changes is also very relevant. Since Postgres has the capability to give a better answer about what is in the file, it would be best to use that. Why not just force all files to be checked irregardless of mtime? The proposal only seems a win to me if a fair percentage of the larger files don't change, which strikes me as a relatively low level case to optimize for. Maybe I'm missing the objective, but it looks like the payoff is to avoid scanning large files for checksums. If I was even infinitesimally insecure about rsync missing files because of clock/filesystem issues, I'd simply force it. One cool thing about making postgres 'aware' of last backup time is that you could warn the user in various places that the database is not being properly backed up (pg_dump would have to monitor last_backup_time as well then). Good stuff, but I bet most people who aren't backing up the database also aren't checking the log :-). The block level case seems pretty much covered by the hot standby feature. 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] Differential backup
On Tue, Apr 27, 2010 at 11:13 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Merlin Moncure mmonc...@gmail.com wrote: The proposal only seems a win to me if a fair percentage of the larger files don't change, which strikes me as a relatively low level case to optimize for. That's certainly a situation we face, with a relatively slow WAN in the middle. http://archives.postgresql.org/pgsql-admin/2009-07/msg00071.php I don't know how rare or common that is. hm...interesting read. pretty clever. Your archiving requirements are high. With the new stuff (HS/SR) taken into consideration, would you have done your DR the same way if you had to do it all over again? Part of my concern here is that manual filesystem level backups are going to become an increasingly arcane method of doing things as the HS/SR train starts leaving the station. hm, it would be pretty neat to see some of the things you do pushed into logical (pg_dump) style backups...with some enhancements so that it can skip tables haven't changed and are exhibited in a previously supplied dump. This is more complicated but maybe more useful for a broader audience? Side question: is it impractical to backup via pg_dump a hot standby because of query conflict issues? 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] Invalidating dependent views and functions
On Fri, Apr 30, 2010 at 8:08 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Apr 30, 2010 at 3:33 AM, Scott Bailey arta...@comcast.net wrote: Proposal: Add an invalid flag to pg_class. Invalid objects would be ignored when doing dependency checks for DDL statements. And an exception would be thrown when an invalid object is called. This is similar to what Oracle does. And most Oracle tools have find and compile invalid objects with a statement like: ALTER VIEW foo RECOMPILE; ALTER PACKAGE bar RECOMPILE BODY; Keep in mind that our implementation is apparently quite different from Oracle's. Of course I have no idea what they do under the hood, but we don't even store the original text of the view. Instead, we store a parsed version of the view text that refers to the target objects logically rather than by name. That has some advantages; for example, you can rename a column in some other table that the view uses, and nothing breaks. You can rename a whole table that is used by the view, and nothing breaks. Even if we added storage for the text of the view, recompiling it might result in some fairly astonishing behavior - you might suddenly be referring to tables or columns that were quite different from the ones you originally targeted, if the old ones were renamed out of the way and new, eponymous ones were added. I'm familiar with the view-dependency-hell problem you mention, having fought with it (succesfully, I'm pleased to say, using a big Perl script to manage things - and also - obligatory dig here - to work around our lack of support for CREATE IF NOT EXISTS) on many occasions, but I don't have any brilliant ideas about how to solve it. I would like to eventually support ALTER VIEW ... DROP COLUMN; note that we do now support ADDING columns to a view using CREATE OR REPLACE as long as all the new ones are at the end. But neither of those things is going to help with a case like yours, when you want to change the type of the column. I'm not really sure what to do about that case. We discussed keeping view sources for invalidation purposes in depth earlier. The main takeaway was that recompiling view sources simply doesn't work: if your view definition is: 'select * from table', the recompile would add fields to the view which SQL (unfortunately) expressly forbids. This is maybe solvable, but complicated. aside: I've been lobbying for (somefoo).* to NOT do this, that is, that is allow it to pick up extra fields on somefoo as they appear, with not so great results so far. I happen to think that the way functions are invalidated right now based on table changes actually work pretty well. Plans are invalidated appropriately and functions are dropped if you suffer major argument changes. Before thinking about improving this, you have to grapple with (for starters) the mess of interactions with search_path and function definitions. IOW, functions not getting planned until they are used is a nice property. 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] what is good solution for support NULL inside string_to_array function?
On Tue, May 4, 2010 at 10:05 AM, Pavel Stehule pavel.steh...@gmail.com wrote: and then string_to_array and array_to_string are orthogonal with NULL. I like the behavior, but should it share the name with the 2 argument version given the incompatibility? Maybe make a new function to_string(anyarray, sep, nullsym='') and deprecate the old one? 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] SELECT * in a CREATE VIEW statement doesn't update column set automatically
On Thu, May 6, 2010 at 3:01 PM, Joseph Adams joeyadams3.14...@gmail.com wrote: This isn't exactly a bug, but it could be considered unintuitive behavior. Consider this: by unintuitive you mean: 'explicitly defined in the SQL standard' :-). I happen to agree with you but that's irrelevant. If you absolutely require this use the composite type workaround. 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] SELECT * in a CREATE VIEW statement doesn't update column set automatically
On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan and...@dunslane.net wrote: And many places regard select * in anything other than throw-away queries as bad practice anyway. I have seen people get bitten by it over and over again, and I have worked at companies where it is explicitly forbidden in coding standards. In terms of application queries I generally agree. However, I think this rule does not apply to server side definitions, especially in regards to views and/or composite types. There are cases where you _want_ the view to be define as 'all fields of x'...In fact, it's pretty typical IMNSHO. It may be possible to expose this behavior. I'd like to see: select * from foo -- and -- select (foo).* exhibit different behaviors -- ().* is more a type operator, returning all the fields of foo, than a field list expression. This gives us a cool loophole to exploit for views that really want to be defined with *: create view particular_foos as select (foo).* from foo where something = true; create view something_complex as select (foo).*, (func(foo.field)).*; -- execute func() just one time please! The something_complex case above is a real problem in how it behaves currently -- sometimes without a hassle free workaround. Am I off my rocker? :-) I've made this point many times (prob got annoying a long time ago) but I'm curious if you guys agree... 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] hot update doesn't work?
On Wed, May 12, 2010 at 11:34 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Pavel Stehule pavel.steh...@gmail.com wrote: I would to repeatably update non indexed column of temp table. I expected cheap operation, but it isn't true. You're updating the row 10 times within a single transaction. I don't *think* HOT will reclaim a version of a row until the transaction which completed it is done and no other transactions can see that version any longer. It does raise the question, though -- couldn't a HOT update of a tuple *which was written by the same transaction* do an update in place? I mean, the updating transaction doesn't need to see the old row after this, and other transactions shouldn't see it either. I suspect that somewhere in the subtransaction or referential integrity areas there may be some issues with that, but it would be a clever optimization if it could be pulled off. scripting this outside of transaction does not exhibit the behavior -- even with autovac off relation size tops out arond 57k. vacuuming as it goes seems to top out around 200 row versions before hot catches them. I guess a good way to think of hot is a page level vacuum. 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] hot update doesn't work?
On Wed, May 12, 2010 at 11:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: You're updating the row 10 times within a single transaction. I don't *think* HOT will reclaim a version of a row until the transaction which completed it is done and no other transactions can see that version any longer. It does raise the question, though -- couldn't a HOT update of a tuple *which was written by the same transaction* do an update in place? Well ... in the first place there is not, ever, any such thing as update in place. The correct question to ask is whether we could vacuum away the older elements of the HOT chain on the grounds that they are no longer of interest. What we would see is tuples with xmin equal to xmax and cmin different from cmax. The problem then is to determine whether there are any live snapshots with curcid between cmin and cmax. There is 0 hope of doing that from outside the originating backend. Now if heap_page_prune() is being run by the same backend that generated the in-doubt tuples, which I will agree is likely in a case like this, in principle we could do it. Not sure if it's really worth the trouble and nonorthogonal behavior. update of same row in a single transaction is not going to come up that much and there are a number of simple work arounds to get better performance. isn't it possible to skip the snapshot check for temp tables though? 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] SELECT * in a CREATE VIEW statement doesn't update column set automatically
On Mon, May 17, 2010 at 2:15 PM, Jim Nasby deci...@decibel.org wrote: On May 6, 2010, at 4:29 PM, Merlin Moncure wrote: On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan and...@dunslane.net wrote: And many places regard select * in anything other than throw-away queries as bad practice anyway. I have seen people get bitten by it over and over again, and I have worked at companies where it is explicitly forbidden in coding standards. In terms of application queries I generally agree. However, I think this rule does not apply to server side definitions, especially in regards to views and/or composite types. There are cases where you _want_ the view to be define as 'all fields of x'...In fact, it's pretty typical IMNSHO. It may be possible to expose this behavior. I'd like to see: select * from foo -- and -- select (foo).* exhibit different behaviors -- ().* is more a type operator, returning all the fields of foo, than a field list expression. This gives us a cool loophole to exploit for views that really want to be defined with *: create view particular_foos as select (foo).* from foo where something = true; create view something_complex as select (foo).*, (func(foo.field)).*; -- execute func() just one time please! The something_complex case above is a real problem in how it behaves currently -- sometimes without a hassle free workaround. Am I off my rocker? :-) I've made this point many times (prob got annoying a long time ago) but I'm curious if you guys agree... What you're suggesting makes sense to me. What is the composite type workaround you mentioned? This is definitely an issue I face at work and would love a more elegant solution than drop and re-create the view. Well, the workaround I was specifically talking about was dealing with the problem of composite type return from functions executing the function multiple times: select (func()).*; This gets expanded to select func().f1, func().f2, etc. This is the behavior I think has to go. if func returns foo and foo has 6 columns, func gets executed 6 times for each row. The workaround is this: select (q).f.* from (select func() as f) q; the problem here is that forcing the function call into a subquery can be awkward in non trival queries -- it causes other problems. What you are probably looking for is to be able to add columns to a view without recreating it: create table foo(...); create view v as select foo from foo; now you can just do: select (foo).* from v; small disclaimer: I don't actually do this much, it might cause other issues. postgres is pretty smart about detecting how composite type changes cascade to other structures. This is an exception! postgres=# create table foo(a int, b int, c int); CREATE TABLE postgres=# create view v as select foo from foo; CREATE VIEW postgres=# create view vv as select (v).foo.c; CREATE VIEW postgres=# insert into foo select 1,2,3; INSERT 0 1 postgres=# insert into foo select 2,4,6; INSERT 0 1 alter table foo drop column c; -- uh oh ALTER TABLE postgres=# select * from v; -- this seems ok foo --- (1,2) (2,4) postgres=# select * from vv; -- urk! postgres=# \d+ vv View public.vv Column | Type | Modifiers | Storage | Description +-+---+-+- c | integer | | plain | View definition: SELECT (v.foo).pg.dropped.3 AS c FROM v; I don't actually mind this so much TBH...feature not bug. I hesitated fixing this because I was terrified someone might actually fix it. 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] SELECT * in a CREATE VIEW statement doesn't update column set automatically
On Mon, May 17, 2010 at 2:15 PM, Jim Nasby deci...@decibel.org wrote: On May 6, 2010, at 4:29 PM, Merlin Moncure wrote: On Thu, May 6, 2010 at 3:23 PM, Andrew Dunstan and...@dunslane.net wrote: And many places regard select * in anything other than throw-away queries as bad practice anyway. I have seen people get bitten by it over and over again, and I have worked at companies where it is explicitly forbidden in coding standards. In terms of application queries I generally agree. However, I think this rule does not apply to server side definitions, especially in regards to views and/or composite types. There are cases where you _want_ the view to be define as 'all fields of x'...In fact, it's pretty typical IMNSHO. It may be possible to expose this behavior. I'd like to see: select * from foo -- and -- select (foo).* exhibit different behaviors -- ().* is more a type operator, returning all the fields of foo, than a field list expression. This gives us a cool loophole to exploit for views that really want to be defined with *: create view particular_foos as select (foo).* from foo where something = true; create view something_complex as select (foo).*, (func(foo.field)).*; -- execute func() just one time please! The something_complex case above is a real problem in how it behaves currently -- sometimes without a hassle free workaround. Am I off my rocker? :-) I've made this point many times (prob got annoying a long time ago) but I'm curious if you guys agree... What you're suggesting makes sense to me. What is the composite type workaround you mentioned? This is definitely an issue I face at work and would love a more elegant solution than drop and re-create the view. Well, the workaround I was specifically talking about was dealing with the problem of composite type return from functions executing the function multiple times: select (func()).*; This gets expanded to select func().f1, func().f2, etc. This is the behavior I think has to go. if func returns foo and foo has 6 columns, func gets executed 6 times for each row. The workaround is this: select (q).f.* from (select func() as f) q; the problem here is that forcing the function call into a subquery can be awkward in non trival queries -- it causes other problems. What you are probably looking for is to be able to add columns to a view without recreating it: create table foo(...); create view v as select foo from foo; now you can just do: select (foo).* from v; small disclaimer: I don't actually do this much, it might cause other issues. postgres is pretty smart about detecting how composite type changes cascade to other structures. This is an exception! postgres=# create table foo(a int, b int, c int); CREATE TABLE postgres=# create view v as select foo from foo; CREATE VIEW postgres=# create view vv as select (v).foo.c; CREATE VIEW postgres=# insert into foo select 1,2,3; INSERT 0 1 postgres=# insert into foo select 2,4,6; INSERT 0 1 alter table foo drop column c; -- uh oh ALTER TABLE postgres=# select * from v; -- this seems ok foo --- (1,2) (2,4) postgres=# select * from vv; -- urk! postgres=# \d+ vv View public.vv Column | Type | Modifiers | Storage | Description +-+---+-+- c | integer | | plain | View definition: SELECT (v.foo).pg.dropped.3 AS c FROM v; I don't actually mind this so much TBH...feature not bug. I hesitated fixing this because I was terrified someone might actually fix it. 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] Fixing insecure security definer functions
On 3/29/07, Tom Lane [EMAIL PROTECTED] wrote: As was pointed out awhile ago http://archives.postgresql.org/pgsql-general/2007-02/msg00673.php it's insecure to run a SECURITY DEFINER function with a search_path setting that's under the control of someone who wishes to subvert the function. Even for non-security-definer functions, it seems useful to be able to select the search path for the function to use; we've had requests for that before. Right now, this is possible but tedious and slow, because you really have to use a subtransaction to ensure that the path is reset correctly on exit: BEGIN SET LOCAL search_path = ...; ... useful work here ... EXCEPTION END (In fact it's worse than that, since you can't write an EXCEPTION without at least one WHEN clause, which is maybe something to change?) Also, this approach isn't available in plain SQL functions. I would like to fix this for 8.3. I don't have a patch yet but want to get buy-in on a design before feature freeze. I propose the following, fully-backward-compatible design: 1. Add a text column propath to pg_proc. It can be either NULL or a search path specification (interpreted the same as values for the search_path GUC variable). NULL means use the caller's setting, ie, current behavior. 2. When fmgr.c sees either prosecdef or propath set for a function to be called, it will insert the fmgr_security_definer hook into the call. fmgr_security_definer will be responsible for establishing the correct current-user and/or path settings and restoring them on exit. (We could use two independent hooks, but since these features will often be used together, implementing both with just one hook seems reasonable.) 3. Add optional clauses to CREATE FUNCTION and ALTER FUNCTION to specify the propath value. I suggest, but am not wedded to, PATH 'foo, bar' PATH NONE Since PATH NONE is the default, it's not really needed in CREATE FUNCTION, but it seems useful to allow it for ALTER FUNCTION. fwiw, I think this is a great solution...because the default behavior is preserved you get through without any extra guc settings (although you may want to add one anyways). maybe security definer functions should raise a warning for implicit PATH NONE, and possibly even deprecate that behavior and force people to type it out in future (8.4+) releases. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Fixing insecure security definer functions
On 3/29/07, Stephen Frost [EMAIL PROTECTED] wrote: * Merlin Moncure ([EMAIL PROTECTED]) wrote: fwiw, I think this is a great solution...because the default behavior is preserved you get through without any extra guc settings (although you may want to add one anyways). I agree that the proposed solution looks good. maybe security definer functions should raise a warning for implicit PATH NONE, and possibly even deprecate that behavior and force people to type it out in future (8.4+) releases. While I agree that raising a warning makes sense I don't believe it should be forced. There may be cases where, even in security definer functions, the current search_path should be used (though, of course, care must be taken in writing such functions). I agree...I'm just suggesting to make you explicitly write 'PATH NONE' for security definer functions because of the security risk...just a thought though. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Fwd: patch to suppress psql timing output in quiet mode
[forwarded from -patches] I noticed that when psql accepts input from stdin or -f (but not -c), and timing is set to on in .psqlrc, timing results are printed out to stdout even when -q (quiet) is passed in. This may not be the perfect solution, but it fixes the problem (I'm having problems with bash scripts that are borking on time returned). current behavior: [EMAIL PROTECTED] psql]# echo select 0 | psql -tAq 0 Time: 1.155 ms [EMAIL PROTECTED] psql]# psql -tAqcselect 0 0 merlin Index: common.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/common.c,v retrieving revision 1.133 diff -c -r1.133 common.c *** common.c8 Feb 2007 11:10:27 - 1.133 --- common.c11 Apr 2007 17:20:21 - *** *** 918,924 PQclear(results); /* Possible microtiming output */ ! if (OK pset.timing) printf(_(Time: %.3f ms\n), elapsed_msec); /* check for events that may occur during query execution */ --- 918,924 PQclear(results); /* Possible microtiming output */ ! if (OK pset.timing !pset.quiet) printf(_(Time: %.3f ms\n), elapsed_msec); /* check for events that may occur during query execution */ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug about column references within subqueries used in selects
On 4/12/07, NikhilS [EMAIL PROTECTED] wrote: Hi, Shouldn't the final command below cause a 'column b does not exist error'? create table update_test (a int, b int); create table supdate_test(x int, y int); insert into update_test values (20, 30); insert into supdate_test values (40, 50); select a, (select b from supdate_test) from update_test; a ?column? -- - 2030 Is the problem with the code in colNameToVar or maybe we should add checks in transformSubLink? I don't think so...the columns of update_test are visible to the scalar subquery...that way you can use fields from 'a' to filter the subquery... select a, (select y from supdate_test where x = a) from update_test; merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On 5/11/07, Tom Lane [EMAIL PROTECTED] wrote: BTW, in the array patch as just committed, I was able to get rid of the I am testing this feature, no problem so far. It's fast, and works exactly as advertised! Great work! (aiui, no domain arrays for 8.3?) merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] libpq and Binary Data Formats
On 6/4/07, Wilhansen Li [EMAIL PROTECTED] wrote: First of all, apologies if this was not meant to be a feedback/wishlist mailing list. Binary formats in libpq has been (probably) a long issue (refer to the listings below) and I want to express my hope that the next revision of PostgreSQL would have better support for binary data types in libpq. I am in no doubt that those binary vs. text debates sprouted because of PostgreSQL's (or rather libpq's) ambiguity when it comes to binary data support. One instance is the documentation itself: it didn't really say (correct me if I'm wrong) that binary data is poorly/not supported and that textual data is preferred. Moreover, those ambiguities are only cleared up in mailing lists/irc/forums which make it seem that the arguments for text data is just an excuse to not have proper support for binary data ( e.x. C:Elephant doesn't support Hammer! P: You don't really need Hammer (we don't support it yet), you can do it with Screwdriver.). This is not meant to be a binary vs. text post so I'll reserve my comments for them. Nevertheless, they each have their own advantages and disadvantages especially when it comes to strongly typed languages that neither shouldn't be ignored. I am well-aware of the problems associated with binary formats and backward/forward compatibility: http://archives.postgresql.org/pgsql-hackers/1999-08/msg00374.php but nevertheless, that shouldn't stop PostgreSQL/libpq's hardworking developers from coming up with a solution. The earling link showed the interest of using CORBA to handle PostgreSQL objects but I belive that it's an overkill and would like to propose using ASN.1 instead. However, what's important is not really the binary/text representation. If we look again the the list below, not everyone need binary formats just for speed and efficiency, rather, they need it to be able to easily manipulate data. In other words, the interfaces to extract data is also important. Personally, I wouldn't mind seeing the libpq API extended to support arrays and record structures. PostgreSQL 8.3 is bringing arrays of composite types and the lack of client side support of these structures is becoming increasingly glaring. If set up with text/binary switch, this would deal with at least part of your objections. I think most people here would agree that certain aspects of the documentation of binary formats are a bit weak and could use improvement (although, it's possible that certain formats were deliberately not documented because they may change). A classy move would be to make specific suggestions in -docs and produce a patch. ISTM to me that many if not most people who are looking at binary interfaces to the database are doing it for the wrong reasons and you should consider that when reviewing historical discussions :-). Also, dealing with large bytea types in the databases which is probably the most common use case, is pretty well covered in libpq documentation IMO. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] feature suggestion
On 8/1/07, Rafael Azevedo [EMAIL PROTECTED] wrote: Imagine that you have about 30 fields. Ok, then your first SQL is done. Now, you just have to add 10 more fields. Its very easy to get lost. If we have this implementation, you could just add Field31 = 'text', Field32 = 'text' I have to admit this syntax has a lot of advantages over the insert...values statement, especially in dynamic sql situations. That being said, more and more I just write queries insert..select which would be an awkward fit. mysql compatibility is usually pretty poor justification of a feature (they have a million ways to do everything) and things have to stand on general merit. It is really quite unfortunate the way certain aspects of the sql standard evolved (indirectly causing these types of issues) but that is a topic for another day :) merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] .NET driver
On 8/2/07, Hannu Krosing [EMAIL PROTECTED] wrote: Ühel kenal päeval, N, 2007-08-02 kell 11:24, kirjutas Rohit Khare: I used NPGSQL .NET driver to connect PGSQL 8.2.4 database to VB.NET. As stated on NPGSQL page, it doesn't seem to provide seamless integration and performance with .NET. Instead when I used ODBC, the performance was comparatively better. What's the reason? When can we expect .NET driver that provides seamless integration. What kind of seamless integration are you looking for ? The .net data provider is not as good when working with typed datasets in terms of support from the ide. Normally for other providers the IDE does everything for you, writing update statements and such in a ORM fashion. This is kind of a pain for some of the report designers and other things that want to work with a typed set. It's possible to work around this, it's just a pain, and changes with each release of visual studio. Also, the connection pooling portions are buggy (google LOG: incomplete startup packet). The ODBC driver works pretty good actually. I can't speak about the performance though. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] HOT pgbench results
On 8/7/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: I ran some CPU intensive pgbench tests on HOT. Results are not surprising, HOT makes practically no difference on the total transaction rate, but reduces the need to vacuum: unpatched HOT tps 36803790 WAL written(MB) 53864804 checkpoints 10 9 autovacuums 116 43 autoanalyzes139 60 Here are some more results...all stock except for partial writes, 24 segments (fsync on). hardware is four 15k sas in a raid 10. I am seeing very good results in other real wold scenarios outside of pgbenchanyone is interested drop me a line. Note I cut the transaction runs down to 100k from 1M. *** HOT *** [EMAIL PROTECTED] root]$ time pgbench -c 5 -t 10 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 10 number of transactions actually processed: 50/50 tps = 1156.605130 (including connections establishing) tps = 1156.637464 (excluding connections establishing) real7m12.311s user0m26.784s sys 0m25.429s *** cvs, HOT *** [EMAIL PROTECTED] pgsql]$ time pgbench -c 5 -t 10 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 10 number of transactions actually processed: 50/50 tps = 630.510918 (including connections establishing) tps = 630.520485 (excluding connections establishing) real13m13.019s user0m27.278s sys 0m26.092s ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] HOT pgbench results
On 8/8/07, Merlin Moncure [EMAIL PROTECTED] wrote: On 8/7/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: I ran some CPU intensive pgbench tests on HOT. Results are not surprising, HOT makes practically no difference on the total transaction rate, but reduces the need to vacuum: unpatched HOT tps 36803790 WAL written(MB) 53864804 checkpoints 10 9 autovacuums 116 43 autoanalyzes139 60 Here are some more results...all stock except for partial writes, 24 segments (fsync on). hardware is four 15k sas in a raid 10. I am seeing very good results in other real wold scenarios outside of pgbenchanyone is interested drop me a line. Note I cut the transaction runs down to 100k from 1M. *** HOT *** [EMAIL PROTECTED] root]$ time pgbench -c 5 -t 10 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 10 number of transactions actually processed: 50/50 tps = 1156.605130 (including connections establishing) tps = 1156.637464 (excluding connections establishing) real7m12.311s user0m26.784s sys 0m25.429s *** cvs, HOT *** [EMAIL PROTECTED] pgsql]$ time pgbench -c 5 -t 10 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 10 number of transactions actually processed: 50/50 tps = 630.510918 (including connections establishing) tps = 630.520485 (excluding connections establishing) real13m13.019s user0m27.278s sys 0m26.092s oops! second case was w/o HOT patch applied (but we knew that) :D merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] comunication protocol
On 8/8/07, Omar Bettin [EMAIL PROTECTED] wrote: Hi, I have installed postgresql to my client as a server for a progam (libpq.dll/VCL based) that I wrote for them. Every is working fine, but I noted some general slowness, compared with an older database system (iAnywhere ADS) due (I think) to the text based communication protocol. you should maybe report a couple of specific things (explain analyze, etc) for analysis and make sure your expectations are reasonable. It is possible simple configuration issues or query changes might be the answer here, then again, maybe not. I know there is the possibility to adopt a ssl compressed connection but I think a pure compressed connections could be better. I think you are looking in the wrong direction here. So, I have studied the postgresql sources and I have tried to implement some compression between the backend and the frontend, using pglz_compress/pglz_decompress on be-secure.c and fe-secure.c. At the moment is working good on a local configuration, got some problems on a remote connection due I think a different way to communicate. AFAIK, the fastest possible way to get data off the server, skipping all data and text processing is to write a SPI routine, and stream the data out locally to the server. I am doing exactly this in a particular problem that requires high performance and I can tell you that SPI is fast. http://developer.postgresql.org/pgdocs/postgres/spi-examples.html That way you will bypass the protocol completely. On my computer, I get roughly 300k records/sec raw read performance using libpq and about 1.3m records sec using a hacked SPI and streaming to disk. This may not be helpful for your application but if you are exploring ways to bypass protocol overhead this is where I would start. By the way, your problem might be the VCL driver you are using to access the database. The highest performance driver I have used (which wraps libpq) is the Zeos library which is very fast. There are other ways (other than be-secure and fe-secure) with which the backend comunicate with the frontend? And, do you think this solution could speed up something? Once again, I would start by looking at your application and posting here to make sure you are looking at the right bottlenecks (you _suspect_ the protocol is the problem, but is it really?). this means: * explain analyze/queries (w/how fast you think it should be going) * relevant .conf settings * time measurements from the app merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] comunication protocol
On 8/9/07, Omar Bettin [EMAIL PROTECTED] wrote: Merlin Moncure wrote: AFAIK, the fastest possible way to get data off the server, skipping all data and text processing is to write a SPI routine, and stream the data out locally to the server. I am doing exactly this in a particular problem that requires high performance and I can tell you that SPI is fast. Interesting,do you thing that is possible to implement some send() in the interface? SPI is an interface which allows you to make sql calls from C code (PostgreSQL allows you to link C code compiled as a .so to the server and call -- see numerous examples in contrib). The routine you need to exploit is SPI_getbinval which gives you Datum (essentially a variant) pointing to the internal binary representation of your field. In theory you could collect the data into a buffer and send() it off although thats a lot of work IMO. Also, I would only advise this for fast dumps from a single table (no joins, etc). I have tried ZeosLib and for me is unusable (too slow), I use a strong modified PostgresDAC. I'm suprised -- I know the ZeosLib internals and it's a very thin layer over libpq. Here is what I suggest: * turn on statement logging in the server (set log_min_duration_statement) * make some operations in the app which you suggest are slow -- they will show up in the log * 'explain analyze' the query from the psql console make note of the times and post back (maybe move this thread to the -performance list) merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] crypting prosrc in pg_proc
On 8/9/07, Andrew Dunstan [EMAIL PROTECTED] wrote: Decibel! wrote: This is also related to the desire to be able to restrict access to the catalog tables. Doing so could potentially solve this problem; it solves other issues (such as being able to see all the databases that exist on a server, something that hosting environments care about). You can hide the catalogs, albeit at the cost of some functionality. I did some experimentation a couple of years back with removing public access from the catalogs, removing information_schema and the public schema, etc, and it worked quite well. I set up a user who had access to a single schema, which only contained functions, and the user wasn't able (so far as I could determine) to see anything other than those functions - no tables, no catalogs, no databases, no users. The user was still able to function exactly as intended. The intended scenario was for a web app user, where the web server was subverted, the aim being to restrict the amount of information the intruder could steal. This works very well to stop casual browsing of functions from psql, etc. That said, I am in the camp that securing system catalogs (including pg_proc) is a good and necessary feature. This debate came up a while back with all the usual arguments pro- and con-. IIRC the general conclusion was that if you want to truly encrypt the sources for your functions, the basic idea is to create a new stored procedure language that wraps pl/pgsql and handles encryption there. This would be relatively easy to support as an external module, I think. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] HOT pgbench results
On 8/14/07, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Heikki Linnakangas [EMAIL PROTECTED] wrote: I ran some CPU intensive pgbench tests on HOT. Results are not surprising, HOT makes practically no difference on the total transaction rate, but reduces the need to vacuum: unpatched HOT tps 3680 3790 WAL written(MB) 5386 4804 checkpoints 109 autovacuums 116 43 autoanalyzes139 60 I also ran pgbench with/without HOT using a bit different configurations (pgbench -s10 -c10 -t50). There were 10% performance win on HOT, although the test was CPU intensive and with FILLFACTOR=100%. I'm curious why I am seeing results so different from everybody else (I had almost double tps with HOT). Are you running fsync on/off? Any other changes to postgresql.conf? merlin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [EMAIL PROTECTED]: Re: [GENERAL] array_to_set functions]
On 8/14/07, Bruce Momjian [EMAIL PROTECTED] wrote: TODO item? I would say yes...array_accum is virtually an essential function when working with arrays and the suggested array_to_set (and it's built in cousin, _pg_expand_array) really should not be built around generate_series when a C function is faster and will scale much better. array_to_set, as suggested in SQL, is something only a relative expert with PostgreSQL could be expected to write. Thus could generate_series be relieved from providing the only core function for set returning functions in the documentation. IMO, this part of the documentation could use some expansion anyways :) merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pgparam extension to the libpq api
Attached are some new functions that extend the libpq api to make
calling the parameterized interfaces easier, especially when making
binary calls. IMO, this fills one of the two big missing parts of the
libpq api when making binary database calls, the other being client
side handling of complex structures (arrays, etc).
The code covers two major areas of functionality and isolated for
separate inclusion:
* PGparam (param.c)
* get/set functions for the pgresult (result_ext.c)
We are happy with both pieces but they can be adopted separately or not at all.
The attached code is basically a cleaned up version of wrappers put in
place in our own applications, plus a functional test. The major
ideas were:
* introduce a new opaque structure, PGparam, that handles some of the
more difficult aspects of memory management associated with binary
calls.
* remove the requirement of client side code having to do byte swapping
* make binary calls as efficient as possible, with a minimal amount of
memory allocations
* introduce, as much as possible, no additional portability issues or
additional dependencies to the libpq api.
Here are the interesting and/or possibly controversial pieces:
* For portability purposes, we had the 64 bit integer put function
take a pointer where the other putters take value types. We couldn't
think of any other way to do it because there is not 64 bit portable
integer type in libpq.
* The synchronous execution functions (for example PQparamExec), takes
a pointer to a result and return error status, which is _not_ how the
other flavors of Exec operate, but is very convenient however. If you
pass in NULL the result is discarded for you. We are stuck on this
approach, but we like it.
* The getters check the returned type oid to make sure it is sane.
For this reason, we have to include catalog/pg_type.h and postgres.h
to get to the OID defines (these are not exposed to the interface
however). I don't see a reason why this is not ok.
The 64 bit integer is handled as a pointer in the get/set functions
because as far as we can tell there is no 64 bit integer type we can
count on without introducing compatibility issues.
We considered putting the PGparam struct into the PGconn structure.
In this case, a PGconn pointer would be passed to the PQparamXXX
functions instead of a PGparam, and would lazy allocate the structure
and free it on PQfinish. We are curious for opinions on this.
Writing credits to myself and Andrew Chernow. If this proposal is
accepted, we will write all the documentation and make suitable
changes necessary for inclusion, presumably for the 8.4 release. To
compile the changes see the attached makefile.
What we would really like is to use the backend input and output
functions for data types, rather than reimplementing this within the
client ... ie pqformat.c and similar files. For this reason, we did
not re-implement get/put functions for the geometric types (we thought
about it), etc. Merging the client and the server marshaling may
require some abstraction of the server so formatting functions can be
called from the client api.
Hopefully this will open up the binary interfaces to more developers.
For certain types of queries, binary calls can be a huge win in terms
of efficiency.
merlin
makefile
Description: Binary data
#include stdlib.h
#include string.h
#include pg.h
#include libpq-int.h
/* Supports 250 columns worth of params. If more are needed,
* memory is allocated ... very rare case.
*/
#define COLSTACKSIZE 4096
#define CHKPARAMPTR(p) do{ \
if(!(p)) \
{ \
errno = EINVAL; \
strcpy((p)-errmsg, libpq_gettext(PGparam pointer is NULL)); \
return 0; \
} \
}while(0)
#define PARAM_ARRAY_DECL \
char _stackbuffer[COLSTACKSIZE]; \
char *buf = _stackbuffer; \
char **vals = NULL; \
int *lens = NULL; \
int *fmts = NULL
#define PARAM_ARRAY_ASSIGN do{ \
if(param) \
{ \
int n = (int)((sizeof(void *) * param-vcnt) + \
((sizeof(int) * 2) * param-vcnt)); \
if(n COLSTACKSIZE) \
{ \
buf = (char *)malloc(n); \
if(!buf) \
{ \
printfPQExpBuffer(conn-errorMessage, \
libpq_gettext(cannot allocate parameter column arrays\n)); \
return 0; \
} \
} \
vals = (char **)buf; \
lens = (int *)(buf + (sizeof(void *) * param-vcnt)); \
fmts = lens + param-vcnt; \
for(n=0; n param-vcnt; n++) \
{ \
vals[n] = param-vals[n].data; \
lens[n] = param-vals[n].datal; \
fmts[n] = param-vals[n].format; \
} \
} \
}while(0)
#define PARAM_ARRAY_FREE do{ \
if(buf != _stackbuffer) \
free(buf); \
}while(0)
typedef struct
{
int ptrl;
void *ptr;
int datal;
char *data;
int format;
} PGvalue;
struct pg_param
{
int vcnt;
int vmax;
PGvalue *vals;
int slabsize;
char *slab;
char errmsg[128];
};
PGparam *PQparamCreate(void)
{
return (PGparam *)calloc(1, sizeof(PGparam));
}
void PQparamReset(PGparam *param)
{
if(param)
param-vcnt = 0;
}
char *PQparamErrorMessage(PGparam *param)
{
Re: [HACKERS] pgparam extension to the libpq api
On 8/17/07, Merlin Moncure [EMAIL PROTECTED] wrote: Attached are some new functions that extend the libpq api to make after sending the mail, we noticed some dead code that might be confusing...in PQparamClear there was some legacy code referring to 'slab' which has no effect...ignore. Also slab and slabsize members of PGparam are not supposed to be there. * The synchronous execution functions (for example PQparamExec), takes a pointer to a result and return error status, which is _not_ how the other flavors of Exec operate, but is very convenient however. If you pass in NULL the result is discarded for you. We are stuck on this approach, but we like it. Also, we are _not_ stuck in the **PGresult concept :-). (typo) merlin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] PGparam extension version 0.3
Attached is version 0.3 of the proposed PGparam extension to the libpq
API. I think we are wrapping up our changes in the short term and
will begin using our api for our internal projects. There were a lot
of changes and reorganizations, but the big features are that client
side geometry types were introduced and we folded the PGparam struct
into PGconn which simplifies the interface in our opinion. See the
attached changes file for a complete list. Also attached is a small
test which is a good overview of how the proposed API changes work.
The code has been reorganized into a proper patch so that things are
injected into libpq in what we think are the right places along with
an updated makefile and exports.txt.
There are many things we discussed but did not implement because of
time concerns, for example client side support for binary arrays and a
queryf interface which would map input parameters into the various put
functions. These are exciting things but fairly complex features and
may require some reorganization of code on the backend to do properly.
Hopefully this will help developers who would like to use the high
performance binary interface or optimize access to the database from
their particular language. Assuming the code is acceptable to the
community, we will keep the patch up to date through the 8.4 cycle and
write the documentation.
Things are obviously really busy right now with HOT and getting 8.3
locked down...but comments and suggestions are most welcome.
merlin
pg_param.tgz
Description: GNU Zip compressed data
#if defined(_WIN32) || defined(_WIN64)
# define U64FMT %I64u
typedef unsigned __int64 myuint64_t;
#else
# define U64FMT %llu
typedef unsigned long long myuint64_t;
#endif
//#include pg.h
#include /esilo/src/pgsql/src/interfaces/libpq/libpq-fe.h
#include stdlib.h
#include string.h
#include limits.h
#ifndef TRUE
# define TRUE 1
#endif
#ifndef FALSE
# define FALSE 0
#endif
#define countof(array) (sizeof(array)/sizeof(array[0]))
#define TEST_TBLNAME param_test
static const char *create_table =
CREATE TABLE TEST_TBLNAME (
a_char\char\,
a_bool boolean,
a_int2 int2,
a_int4 int4,
a_int8 int8,
a_float4 float4,
a_float8 float8,
a_text text,
a_nulltext text,
a_byteabytea,
a_macaddr macaddr,
a_pointpoint,
a_lseg lseg,
a_box box,
a_circle circle,
a_path path,
a_polygon polygon);
static const char *insert_command =
INSERT INTO TEST_TBLNAME VALUES
($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17);
static int getresfmt(int argc, char **argv);
int main(int argc, char **argv)
{
int i;
char ch = 0;
int b = 0;
short i2 = 0;
int i4 = 0;
myuint64_t i8;
float f4 = 0;
double f8 = 0;
char *text;
int text_isnull;
unsigned char *bytea2;
unsigned char bytea[4] = {1, 10, 220, 255};
PGmacaddr mac;
PGpoint pt;
PGlseg lseg;
PGbox box;
PGcircle circle;
PGpath path;
PGpolygon polygon;
PGconn *conn;
PGresult *res;
ExecStatusType status;
PGpoint points[64];
int resultFormat = getresfmt(argc, argv);
conn = PQconnectdb(hostaddr=127.0.0.1 user=postgres);
if(PQstatus(conn) != CONNECTION_OK)
{
printf(connection failure\n);
return 1;
}
res = PQexec(conn, create_table);
PQclear(res);
/* clear test table */
res = PQexec(conn, DELETE FROM TEST_TBLNAME);
PQclear(res);
i8 = ULLONG_MAX;
PQputchar(conn, UCHAR_MAX);
PQputbool(conn, TRUE);
PQputint2(conn, USHRT_MAX);
PQputint4(conn, UINT_MAX);
PQputint8(conn, i8); /* pqlib has no 64-bit type. */
PQputfloat4(conn, 111.234f);
PQputfloat8(conn, .234567);
PQputtext(conn, This is some text);
PQputnull(conn);
PQput(conn, bytea, (int)sizeof(bytea), InvalidOid, 1);
mac.a = 1;
mac.b = 2;
mac.c = 3;
mac.d = 4;
mac.e = 5;
mac.f = 6;
PQputmacaddr(conn, mac);
pt.x = -11.23;
pt.y = 23.11;
PQputpoint(conn, pt);
lseg.pts[0].x = 6712;
lseg.pts[0].y = 2517.89;
lseg.pts[1].x = 9087.125;
lseg.pts[1].y = 7821.987;
PQputlseg(conn, lseg);
box.high.x = 100;
box.high.y = 10;
box.low.x = 10;
box.low.y = 1;
PQputbox(conn, box);
circle.center.x = 100;
circle.center.y = 200;
circle.radius = 300;
PQputcircle(conn, circle);
path.closed = 0; /* open path */
path.npts = countof(points);
path.pts = points;
for(i=0; i path.npts; i++)
{
path.pts[i].x = i;
path.pts[i].y = i+1;
}
PQputpath(conn, path);
polygon.npts = 16;
polygon.pts = points;
if(!PQputpolygon(conn, polygon))
printf(Polygon: %s\n, PQerrorMessage(conn));
/* Execute query */
PQparamExec(conn, insert_command, 1, res);
/* check result */
status = PQresultStatus(res);
if(status != PGRES_COMMAND_OK status != PGRES_TUPLES_OK)
{
printf(%s\n, res ? PQresultErrorMessage(res) : PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return 1;
}
printf(\nGetting results in %s
[HACKERS] more problems with the money type
while playing with the binary transport of the money type we found another bug. The following code segfaults the server on 8.3cvs: select '3'::money * 2; this was an accidental discovery by us but is the second serious bug we found with the money type without looking very hard...probably stemming from the bump to 64 bit in jan 07 (8.2 does not have this behavior). aside: since the money type was deprecated, why was it bumped to 64 bits? merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch2 patch status report
On 8/21/07, Magnus Hagander [EMAIL PROTECTED] wrote: OTOH, if we do it as a compat package, we need to set a firm end-date on it, so we don't have to maintain it forever. Given the issues always at hand for doing such an upgrade, my vote is actually for ripping it out completely and take the migration pain once and then be done with it. I would suggest making a pgfoundry project...that's what was done with userlocks. I'm pretty certain no one besides me has ever used the wrappers I created...a lot more people use tsearch2 than userlocks though. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] enum types and binary queries
I noticed that enums are not available to be queried as binary through
the protocol. Is this a known issue? Too late to fix for 8.3? This
is kind of a pain, because it forces any query that returns an enum to
return the entire result as text. afaik, enums are the only POD type
to behave this way.
postgres=# create type foo as enum('foo');
CREATE TYPE
postgres=# copy (select 'foo'::foo) to '/home/postgres/foo.txt' binary;
ERROR: no binary output function available for type foo
merlin
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] enum types and binary queries
On 8/30/07, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: I noticed that enums are not available to be queried as binary through the protocol. What do you think the binary representation ought to be? Copying OIDs seems pretty useless. I'm on the fence on this one. I actually think this would be ok, if you mean pg_enum.oid, or the string would be fine too. I agree that binary protocol is supposed to be fast, and I can prefetch the pg_enum table to the client and do the magic there. Many other binary formats do similarly inscrutable things. One other very small observation: afaict, there appears to be no way to list enum contents in psql (although you can list the enums themselves in the type listing). Maybe this should be possible? I'm willing to take a stab at these things if Andrew is busy. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] HOT is applied
On 9/20/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Tom Lane wrote: I've committed the HOT patch. Thanks, much easier to work with it now that it's in. I'd still like to think about whether we can be smarter about when to invoke pruning, but that's a small enough issue that the patch can go in without it. Yeah. I'm doing some micro-benchmarking, and the attached test case is much slower with HOT. It's spending a lot of time trying to prune, only to find out that it can't. Instead of/in addition to avoiding pruning when it doesn't help, maybe we could make HeapTupleSatisfiesVacuum cheaper. I'm going to continue testing, this is just a heads-up that HOT as committed seriously hurts performance in some cases. (though one can argue that this test case isn't a very realistic one.) well, I ran your test on my box and here are the results: pre hot: run 1: 3617.641 ms run 2: 5195.215 ms run 3: 6760.449 ms after vacuum: run 1: 4171.362 ms run 2: 5513.317 ms run 3: 6884.125 ms post hot: run 1: Time: 7286.292 ms run 2: Time: 7477.089 ms run 3: Time: 7701.229 ms those results aren't exactly terrible, and this case is highly artificial. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HOT is applied
On 9/21/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Merlin Moncure wrote: pre hot: run 1: 3617.641 ms run 2: 5195.215 ms run 3: 6760.449 ms after vacuum: run 1: 4171.362 ms run 2: 5513.317 ms run 3: 6884.125 ms post hot: run 1: Time: 7286.292 ms run 2: Time: 7477.089 ms run 3: Time: 7701.229 ms those results aren't exactly terrible, and this case is highly artificial. Your runtimes seem to be increasing as you repeat the test. Did you remove the DROP TABLE from the beginning? On my laptop, post hot takes ~2x as long as pre hot, even when repeated, which matches the results of your first runs. correct. Well, my first round of results are so far not showing the big gains I saw with hot in some of the earlier patches...so far, it looks approximately to be a wash although with the reduced need to vacuum. i'll test some more when things settle down. merlin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] HOT is applied
On 9/21/07, Merlin Moncure [EMAIL PROTECTED] wrote: Well, my first round of results are so far not showing the big gains I saw with hot in some of the earlier patches...so far, it looks approximately to be a wash although with the reduced need to vacuum. let me correct myself here. I did some longer runs and came up with the following results (these runs are still not really long enough, I need to run some more tests). The smaller, cpu bound cases were blowout victories for hot. The largest run (i/o bound) was very close but there were not enough transactions to force a vacuum, after which hot would probably pull away by some degree. one small aside: I am suspicious that 8.3 improvements to the stats collector overhead are going to reap big benefits. This merits some extra investigation, i think. hardware: 2xintel [EMAIL PROTECTED], 4 cores total 8gb ram 5x15krpm sas, raid 0 data xfs 5x15krpm sas, raid 0, wal xfs fsync on, asynch commit, partial page writes, autovac on not hot build cvs dated 9/14 hot build cvs dated 9/21 merlin (results follow) * without hot: * scaling factor: 1 number of clients: 8 number of transactions per client: 25 number of transactions actually processed: 200/200 tps = 2345.225742 (including connections establishing) tps = 2345.264846 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 8 number of transactions per client: 25 number of transactions actually processed: 200/200 tps = 3038.119776 (including connections establishing) tps = 3038.185492 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 25 number of clients: 8 number of transactions per client: 25 number of transactions actually processed: 200/200 tps = 3671.987348 (including connections establishing) tps = 3672.083077 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 8 number of transactions per client: 25 number of transactions actually processed: 200/200 tps = 4240.424756 (including connections establishing) tps = 4240.542851 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 1000 number of clients: 8 number of transactions per client: 10 number of transactions actually processed: 80/80 tps = 889.890173 (including connections establishing) tps = 889.905128 (excluding connections establishing) * witht hot: * transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 8 number of transactions per client: 25 number of transactions actually processed: 200/200 tps = 3186.553423 (including connections establishing) tps = 3186.622178 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 8 number of transactions per client: 25 number of transactions actually processed: 200/200 tps = 5123.153371 (including connections establishing) tps = 5123.331343 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 25 number of clients: 8 number of transactions per client: 25 number of transactions actually processed: 200/200 tps = 4994.897937 (including connections establishing) tps = 4995.075480 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 8 number of transactions per client: 25 number of transactions actually processed: 200/200 tps = 4675.778153 (including connections establishing) tps = 4675.936096 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 1000 number of clients: 8 number of transactions per client: 10 number of transactions actually processed: 80/80 tps = 893.904762 (including connections establishing) tps = 893.919032 (excluding connections establishing) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Reducing NUMERIC size for 8.3
On 9/24/07, Gregory Stark [EMAIL PROTECTED] wrote:
We previously discussed compressing the numeric data type for small values:
http://archives.postgresql.org/pgsql-hackers/2007-06/msg00715.php
We didn't do this for 8.3 but in any case Tom did suggest we ought to reverse
the weight and sign/dscale so we could do this sometime without introducing
another incompatibility.
I think we also should move the NumericData and declaration to numeric.c and
make the Numeric type an opaque pointer for the rest of the source tree. That
will catch any contrib modules or third-party modules which would be broken by
any new data representation.
--- numeric.h 27 Feb 2007 23:48:10 + 1.24
+++ numeric.h 24 Sep 2007 16:07:24 +0100
@@ -63,8 +63,8 @@
typedef struct NumericData
{
int32 vl_len_;/* varlena header (do not
touch directly!) */
- int16 n_weight; /* Weight of 1st digit */
uint16 n_sign_dscale; /* Sign + display scale */
+ int16 n_weight; /* Weight of 1st digit */
charn_data[1]; /* Digits (really array of
NumericDigit) */
} NumericData;
would this break any application pulling a numeric field as binary
over the protocol?
merlin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Re: [Pg-migrator-general] Composite types break pg_migrated tables
On Thu, Aug 6, 2009 at 4:32 AM, Dimitri Fontainedfonta...@hi-media.com wrote: Tom Lane t...@sss.pgh.pa.us writes: Andrew Dunstan and...@dunslane.net writes: preventing a clash might be fairly difficult. Yeah, I was just thinking about that. The easiest way to avoid collisions would be to make pg_dump (in --binary-upgrade mode) responsible for being sure that *every* new pg_type and pg_class row OID matches what it was in the old DB. As we already have WITH OIDS for CREATE TABLE command, maybe adding support for WITH OID ... to the necessary commands would do the trick? Instead of messing with pg_type, pg_dump would then have to issue a OID 'decorated' command such as CREATE TYPE footype ... WITH OID 27604; Unfortunately it's not enough to just do this with 'create type' and 'create type as', we also have to do this with 'create table'. Some people (like me) use tables as composite types because of the extra flexibility it gives you. So, potentially, OIDs for enums, tables, and types needs to be preserved. I am very much in support for any system that allows creation of a type with a specific OID. This is not just a problem with the migrator, but will allow for more robust transfers of data over the binary protocol (think binary dblink) without resorting to hacks to that do lookups based on typename. IOW, this setting specific OIDs should ideally be exposed at the SQL level and should be able to be done for any type that can be part of a container. 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] dblink bulk operations
On Thu, Aug 6, 2009 at 11:11 AM, Andrew Dunstanand...@dunslane.net wrote:
Last night I needed to move a bunch of data from an OLTP database to an
archive database, and used dblink with a bunch of insert statements. Since I
was moving about 4m records this was distressingly but not surprisingly
slow. It set me wondering why we don't build more support for libpq
operations into dblink, like transactions and prepared queries, and maybe
COPY too. It would be nice to be able to do something like:
select dblink_connect('dbh','dbname=foo');
select dblink_begin('dbh');
you can always exec a sql 'begin'.
select dblink_prepare('dbh','sth','insert into bar values ($1,$2,$3)');
select dblink_exec_prepared('dbh','sth',row(a,b,c)) from bar; -- can
we do this?
The answer to this I think is yes, but not quite that way. Much
better I think is to use 8.4 variable argument functions, use
parametrized features off libpq always, and use the binary protocol
when possible. This does end up running much faster, and easier to
use...(we've done exactly that for our in house stuff). IIRC you can
parameterize 'execute', so the above should work for prepared queries
as well.
If we get the ability to set specific OIDs for types, I can remove
some of the hacks we have to send text for composites and arrays of
composites.
select * from pqlink_exec(connstr, 'select $1 + $2', 3, 4) as R(v int);
v
---
7
(1 row)
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] dblink bulk operations
On Thu, Aug 6, 2009 at 11:11 AM, Andrew Dunstanand...@dunslane.net wrote:
Last night I needed to move a bunch of data from an OLTP database to an
archive database, and used dblink with a bunch of insert statements. Since I
was moving about 4m records this was distressingly but not surprisingly
slow. It set me wondering why we don't build more support for libpq
operations into dblink, like transactions and prepared queries, and maybe
COPY too. It would be nice to be able to do something like:
select dblink_connect('dbh','dbname=foo');
select dblink_begin('dbh');
select dblink_prepare('dbh','sth','insert into bar values ($1,$2,$3)');
select dblink_exec_prepared('dbh','sth',row(a,b,c)) from bar; -- can
we do this?
select dblink_commit('dbh');
select dblink_disconnect('dbh');
thinking about this some more, you can get pretty close with vanilla
dblink with something like (i didn't test):
select dblink_exec('dbh', 'prepare xyz as insert into foo select ($1::foo).*');
select dblink_exec('dbh', 'execute xyz(' || my_foo::text || ')');
This maybe defeats a little bit of what you are trying to achieve
(especially performance), but is much easier to craft for basically
any table as long as the fields match. The above runs into problems
with quoting (composite with bytea in it), but works ok most of the
time.
If you want faster/better, dblink need to be factored to parametrize
queries and, if possible, use binary.
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] Hot standby?
On Tue, Aug 11, 2009 at 4:07 PM, Josh Berkusj...@agliodbs.com wrote: All, So really, the streaming replication patch should be called hot standby, and the hot standby patch should be called read only slaves? And *why* can't we call it log-based replication? +1 *) it _is_ used to replicate a database (replicate means make a copy!) *) our target market will perceive it that way *) sounds cool 'synchronous log-based replication' 'asynchronous log-based replication' or, 'log-based replication', in both synchronous and asynchronous modes 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] COPY speedup
2009/8/12 Pierre Frédéric Caillaud li...@peufeu.com: If you do as much damage to the I/O function API as the other patch did, it will probably be rejected. You mean, as the COPY patch in my previous message, or as another patch ? (I just search the archives and found one about CopyReadLine, but that's probably not what you are talking about) We don't touch datatype APIs lightly, because it affects too much code. regards, tom lane I definitely agree with that. Is there any way to do this that is not as invasive? 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] Adding \ev view editor?
On Tue, Sep 1, 2009 at 2:41 PM, Josh Berkusj...@agliodbs.com wrote: All, I've grown to adore the new \ef function editor. It doesn't seem like it would be that difficult to add a view editor as \ev. While editors would also be good for other objects, I don't think we can do \et or \er etc. because those objects don't support CREATE OR REPLACE. Opinions? Other objects which could take \e? One reason I don't like this proposal is that postgresql does not preserve the original 'create view' statement for editing. The resulting sql that is given to you to edit is hopelessly mangled and I think it's not good to encourage people to modify views in this fashion. Then again, we are only reproducing what pgadmin can already do, so why not? :-) 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] RfD: more powerful any types
On Tue, Sep 8, 2009 at 12:12 PM, Alvaro Herreraalvhe...@commandprompt.com wrote: Hi, I'm seeking more powerful any pseudotypes. In particular, consider a function foo(type1, type2) returns type3 where type1 and type2 can be both any element type, but not necessarily both of the same type. Currently this cannot be made to work: you could try to write the function this way: foo(anyelement, anyelement) returns anyelement but this will force them to be both of the same type, which is not what we want. In my opinion this is a missing feature of our type system. One seemingly trivial idea would be to have anyelement2, anyelement3, and so on. This is not a very good solution, because we'd have to fill the catalogs with a large bunch of new pseudotypes, and the code with a bunch of hardcoded tests -- and there are already 27 cases of ANYELEMENTOID in our code. For a practical example, I am trying to write a function that returns how many NULL arguments it has (this is useful in table CHECK constraints). One simple idea is to use type unknown: CREATE FUNCTION a (unknown, unknown, unknown) RETURNS INT LANGUAGE plpgsql AS $$ DECLARE count int = 0; BEGIN IF $1 IS NULL THEN count = count + 1; END IF; IF $2 IS NULL THEN count = count + 1; END IF; if $3 IS NULL THEN count = count + 1; END IF; RETURN count; END $$; The problem is that it doesn't work. This trivial query does: alvherre=# select a(null, '2', null); a --- 2 (1 fila) But this one does not: alvherre=# select a(null, 2, null); ERROR: function a(unknown, integer, unknown) does not exist and you cannot cast the integer: alvherre=# select a(null, 2::unknown, null); ERROR: cannot cast type integer to unknown Before I spend time trying to figure out how this works, 1. is there agreement that this is a problem and needs fixed, and 2. does anybody have an idea how to attack it? Since you can do all these things and more in C functions, this becomes a right tool/wrong tool problem? plpgsql would be fairly hopeless without some reflection capabilities that we don't currently have...especially if you consider variadic functions which would be hard to reconcile with any behavior changes. I think if you continue going down this road you would end up with a type system along the lines with c++ templates...so you could do: DECLARE foo arg1%type; etc And maybe have unique generated plans for each unique set of supplied input 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] RfD: more powerful any types
On Fri, Sep 11, 2009 at 11:19 AM, Robert Haas robertmh...@gmail.com wrote:
On Fri, Sep 11, 2009 at 10:30 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
I think the main benefit of a sprintf type function for PostgreSQL is
in the formatting (setting length, scale, alignment), not in making
concatenation more pretty.
Exactly, which is why I'm so distressed that this proposal not only
hasn't got that, but is designed so that it's impossible to add it
later.
I like the idea of making concatenation more pretty, quite frankly.
No one has really responded to Pavel's contention that this is what
to_char() is for. Twice the code paths = twice the bugs, twice the
places that have to be updated when some new feature is added, etc.
If you are going to use printf format codes, which is good and useful
being something of a standard, I'd call routine printf (not format)
and actually wrap vsnprintf. The format codes in printf have a very
specific meaning: converting native C types to arrays of characters.
I think that a postgresql implementation should do exactly that:
attempt to convert the passed in datum to the c type in question if
possible (erroring if no cast exists) and then pass it down. The idea
is we are not adding new formatting routines but using a very high
quality existing one...why reinvent the wheel?
so if you did: select printf('%s %3.1f', foo::box, bar::circle);
the box to char* cast would work (using the text cast) but the second
cast would fail unless the user added a cast to float. The code in
question is easy to imagine...parse the format string, and loop the
varargs using the appropriate looked up cast one by one...
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] RfD: more powerful any types
On Fri, Sep 11, 2009 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Is this really all that hard? I'm thinking it could be implemented by using the real C sprintf underneath, passing one % specifier and its corresponding parameter at a time, coerced to whatever the conversion specifier specifies. The only disadvantage I can see of that is that it would lose precision for NUMERIC. I'd really like to be able to write %300.100f and have it Do The Right Thing with a 300-digit numeric input. that could be simply worked around by formatting the numeric in sql and passing it to printf as %s. 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] RfD: more powerful any types
On Fri, Sep 11, 2009 at 12:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: If you are going to use printf format codes, which is good and useful being something of a standard, I'd call routine printf (not format) and actually wrap vsnprintf. The format codes in printf have a very specific meaning: converting native C types to arrays of characters. I think that a postgresql implementation should do exactly that: attempt to convert the passed in datum to the c type in question if possible (erroring if no cast exists) and then pass it down. I think this is a bit too restrictive. Aside from the issue of loss of precision for NUMERIC, do we really want users to have to deal with the fact that long doesn't mean the same thing on every platform? I don't want the same SQL to work on some platforms and fail on others because a particular datatype has a cast to int4 and not to int8, for instance. We should certainly leverage the C library as much as we can for this, but exposing users to every single idiosyncrasy of C is not quite the right thing IMHO. hmm. how about leaving the existing format codes alone and making some safer additional ones that we advice the user to use? It could probably be all fixed up in the vsnprintf layer. 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] RfD: more powerful any types
On Mon, Sep 14, 2009 at 1:42 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
How is it any worse than what people can already do? Anyone who isn't aware
of the dangers of SQL injection has already screwed themselves. You're
basically arguing that they would put a variable inside of quotes, but they
would never use ||.
simply - people use functions quote_literal or quote_ident.
you still have use of those functions:
execute sprintf('select * from %s', quote_ident($1));
sprintf is no more or less dangerous than || operator.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: make plpgsql IN args mutable (v1) [REVIEW]
On Wed, Sep 16, 2009 at 8:59 AM, Robert Haas robertmh...@gmail.com wrote: On Sep 16, 2009, at 8:37 AM, Andrew Dunstan and...@dunslane.net wrote: Abhijit Menon-Sen wrote: At 2009-07-30 13:37:16 -0700, prent...@cisco.com wrote: This patch changes plpgsql IN parameters so they are mutable. Makes sense, applies fine, works fine. How does this compare with PLSQL? I know in Ada an IN argument is in effect a constant. I understand the utility, because I occasionally knock against this restriction, but if it's incompatible with PLSQL I think we should think about it more carefully. At worst it's an upward-compatible extension, or am I wrong? If it's useful, which I think it is, what's the harm? are we guarding against cases like: select _foo, adjust_foo(_foo) from bar; -- adjust_foo is inout ?? 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] happy birthday Tom Lane ...
On Fri, Sep 18, 2009 at 8:09 AM, Andrew Chernow a...@esilo.com wrote: Hans-Juergen Schoenig -- PostgreSQL wrote: Tom, On behalf of the entire PostgreSQL team here in Austria I want to wish you a happy birthday. We hope that you fill be a vital part of PostgreSQL for many years to come. Best regards, Hans-Jürgen Schönig + team Happy 30th Tom ;) so that's where he gets his energy... (happy birthday) 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] Anonymous code blocks
On Sat, Sep 19, 2009 at 8:23 PM, Andrew Dunstan and...@dunslane.net wrote: Dimitri Fontaine wrote: So here are the major points about this patch: - it's missing the returns declaration syntax (default value could be returns void?) - it would be much more friendly to users if it had a default output for queries, the returned object seems a good fit Really? That wasn't my expectation at all. I expected that the code would in effect be always returning void. I think you're moving the goalposts a bit here. I don't think we need a RETURNS clause on it for it to be useful. A note about void returning functionsthere are no send/recv functions for the void type which will cause problems for users of this feature over the binary protocol. You can work around this with normal functions by forcing them to return a value but not with ACB. Is there any reason why void doens't have send/recv? 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] plpgsql function is so slow
On Fri, Sep 25, 2009 at 1:05 AM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Euler == Euler Taveira de Oliveira eu...@timbira.com writes: Euler Ops... forgot to remove it from other test. It seems much Euler better but far from the ideal. :( I've never taken a look at Euler the pl/pgsql code but it could be nice if there would be two Euler path codes: access-data and non-access-data paths. I have no Euler idea if it will be possible (is path type too complex to Euler detect?) but it will certainly improve the non-access-data Euler functions. Like Tom said, this benchmark is silly. Some comparisons (note that in all these cases I've replaced the power(10,8) with a constant, because you weren't comparing like with like there): plpgsql 13.3 sec tcl85 29.9 sec perl5.8 7.7 sec python2.6 11.5 sec C 0.242 sec What this suggests to me is that plpgsql isn't so far off the norm for interpreted scripting languages; sure it's slower than perl, but then most things are; comparing it with C code is just silly. There is, though, one genuine case that's come up a few times in IRC regarding slowness of procedural code in pg, and that's any time someone tries to implement some array-based algorithm in plpgsql. The fact that a[i] is O(i) not O(1) (unless the array type is fixed length) comes as a nasty shock since iterating over an array becomes O(n^2). This is obviously a consequence of the array storage format; is there any potential for changing that to some format which has, say, an array of element offsets at the start, rather than relying on stepping over length fields? Couple points: *) Surely, it's better to encourage use of 'unnest' style approaches for array iteration *) If an array has fixed length elements and doesn't have null elements (a fairly common case), maybe it's worthwhile not generating/storing the lengths vector? *) Wouldn't it be possible to store offsets always, not lengths, since you can calculate the length from the next offset? 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] Writeable CTEs and side effects
On Thu, Oct 8, 2009 at 6:52 AM, Robert Haas robertmh...@gmail.com wrote: up to the main query. For what may turn out to be pretty common uses cases like WITH tuples AS (DELETE FROM big_table_1 RETURNING ...) INSERT INTO big_table_2 ... this is going to suck pretty bad. I Is the above form: with x as (delete .. returning *) insert into y select * from x going to be allowed? I was informed on irc that it wasn't...it would have to be written as: insert into y with x as (delete .. returning *) select * from x IOW, will WITH be able to terminate in update/insert/delete and not just select? 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] Is FOR UPDATE an optimization fence?
On Mon, Oct 12, 2009 at 1:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Oct 11, 2009 at 12:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Of course the downside of changing it is that queries that worked fine before might work differently (and much slower) now; first because not flattening the sub-select might lead to a worse plan, and second because locking more rows takes more time. The alternative would be to let it continue to flatten such sub-selects when possible, and to tell anyone who doesn't want that to stick in OFFSET 0 as an optimization fence. It's an entirely trivial code change either way. I'm inclined to think that we should prevent flattening, on the grounds of least astonishment. The other comment I have is that I *expect* subqueries to be pulled up. So my own personal POLA would not be violated by locking only the rows with a join partner; in fact it would be more likely to be violated by the reverse behavior. I might not be typical, though. My experience is that not pulling up subqueries tends to have disastrous effects on performance, so I'm somewhat biased against creating more situations where that will happen. On further reflection I've decided to stick with the old behavior on this point, at least for the time being. I'm concerned about subtly altering the behavior of existing queries, and I've also realized that changing it isn't as much of a one-liner as I thought. The current behavior of the parser and rewriter really depends on the assumption that there's not much of a semantic difference between FOR UPDATE markings at different syntactic levels, because they will happily push down a FOR UPDATE *into* a sub-select. That is, For the record, I wasn't sure if I agreed with your original point that: select * from a join (select * from b for update) ss on a.x = ss.y; should necessarily be expected to lock all rows from b (does the standard insist on it?). The select inside the join clause describes 'how you get' the records, not that they should be all gotten. Along the same vein, does: create view foo_update as select * from foo for update; necessarily lock all the rows from foo for any query against the view? (It doesn't and IMO shouldn't). ISTM that the particular rows being locked in your first example are not really defined very well. 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] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 12:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: David E. Wheeler da...@kineticode.com writes: I'd sure love $, as it's like shell, Perl, and other stuff. This discussion has gotten utterly off track. The problem I am trying to solve is a non-Oracle-compatible behavior in plpgsql. I have got substantially less than zero interest in proposals that solve the problem by introducing notations that don't even pretend to be compatible. Personally, I'd vote against a GUC option. I just plain don't like the idea that a function could do different things depending on server configuration. TBH, I'm not very happy with #option either. That said, I agree that Oracle method is far better. Maybe invent a new language handler? plpgsql2 or shorten to pgsql? Now you can mess around all you want (and maybe fix some other compatibility warts at the same time). 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] Controlling changes in plpgsql variable resolution
On Tue, Oct 20, 2009 at 10:32 AM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: 1. Invent a GUC that has the settings backwards-compatible, oracle-compatible, throw-error (exact spellings TBD). Factory default, at least for a few releases, will be throw-error. Make it SUSET so that unprivileged users can't break things by twiddling it; but it's still possible for the DBA to set it per-database or per-user. I don't see the logic to making the setting SUSET. The user wrote the function; what logic is there to say the resolution rules are not under their control? That's only sane if you are 100% certain that there could not be a security issue arising from the change of behavior. Otherwise someone could for instance subvert a security-definer function by running it under the setting it wasn't written for. Personally I am not 100% certain of that. Also, I think to GUC that throws an error or not is a lot safer than one that changes resolution semantics. Changing resolution semantics sounds like the autocommit GUC to me. :-O Yeah, that's another reason to not allow it to be changed too easily. Also, I am not really keen on the keep it for a few releases Well, I'm not necessarily saying we would ever change it. Maybe the default could always stay at error. ... maybe just error/no error and using Oracle semantics is the way to go, with 'error' as the default. I'd personally be entirely happy with that, but people with large plpgsql code bases will not be. They're going to want a backward-compatible setting so that this doesn't become a show stopper for migration to 8.5. Any time you can allow someone to deal with a migration issue later instead of right away, it becomes easier for them to migrate. How about warning for release before making the big switch? The text cast change, while ultimately good, maybe could have been stretched out for a release or two...it was painful. I do though absolutely think that it was good in the end to not support a compatibility option in core. Didn't we have a long discussion on big compatibility changes with the consensus that we were to going give a transition release before we dropped a backwards compatibility bomb? I can't help feeling that we are about to jump off into the abyss... 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] Controlling changes in plpgsql variable resolution
On Wed, Oct 21, 2009 at 3:09 PM, Josh Berkus j...@agliodbs.com wrote: Tom has proposed some kind of odd special options syntax to get around this, but I think that's unnecessary. So far on this thread, I haven't seen anyone engineer an actual function exploit by using this setting; I personally can't come up with one off the top of my head which doesn't require the attacker to be the table owner, the function owner, or the superuser themselves. Also keep in mind what we're patching here is an unmaintanable and insecure practice anyway, which is the ambiguous use of variable names which match column names. So, I'm saying: make it a userset. I couldn't disagree more strongly. .conf entries that adjust how plpgsql funtions operate in a very central way will 'fork' plpgsql develoeprs so that you have one group of people using method 'a' and another using method 'b'. Nobody bothers to fix legacy code and now we have a first class mess. All code intended to run on servers you don't control (like library code) now needs to be decorated with 'set local...' which defeats the whole purpose. IMO, guc settings that control how sql behaves should be avoided at all costs. You should be able to look at a piece of code and explicitly determine what it does. At least with #option, knowing the server version and the function body is enough. if you want to support multiple behaviors but don't like #option, i think the only alternative is to version the plpgsql language somehow and decorate 'create function' with the version. Tom didn't like that idea, but it still beats GUC imo. 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] plpgsql EXECUTE will not set FOUND
On Fri, Oct 23, 2009 at 11:07 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: Any change here is *not* a bug fix, it is a change of clearly documented and not-obviously-unreasonable behavior. We have to take seriously the likelihood that it will break existing code. Perhaps plpgsql could support tests of SQLSTATE, and recognize '02000' (the standard value for zero rows affected) to support the desired new semantics? +1 I rarely use found because it's dangerous ...would be nice to have a more rigorous test... 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] Proposal: String key space for advisory locks
On Mon, Oct 26, 2009 at 1:54 AM, Christophe Pettus x...@thebuild.com wrote:
Greetings,
I'd like to propose a potential patch, and wanted to get preliminary
feedback on it before I started looking into the design.
Summary: Add a string key space to the advisory lock functionality.
Rationale:
Right now, the key spaces (the range of unique values that can be used as
identity) for advisory locks are either a bigint or two ints. This is, of
course, technically more than one could imaginably need in any application.
The difficulty arises when the number of potential advisory locks is
related to rows in one or more tables.
For example, suppose one wanted to use advisory locks to signal that a queue
entry is being processed, and entries in that queue have a primary key
that's also a bigint. There's no problem; the advisory lock id is the
primary key for the row.
And, then, one wants to use an advisory lock to signal that a particular
record in another table is being processed in a long-term process. One has
a series of unappealing alternatives at that point, mostly involving
encoding a table ID and the primary key of a record into the 64 bit number,
or just hoping that the primary key doesn't overflow an int, and using the 2
x int form.
If you want to lock records from multiple tables, probably the best
approach is to use a single sequence and pull IDs from it for each
table you want to use advisory locks with. It doesn't even have to be
the primary key (although it can be)...you can even use a domain:
create sequence lock_seq;
create domain lock_val not null default nextval('lock_seq');
create table a_table(lock_val lock_val, ...);
create table b_table(lock_val lock_val, ...);
Regarding your proposal...the lock system is highly optimized and any
suggestion that incurs performance issues is probably not going to
make it...
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] half OOT, plv8js group created ^^
On Mon, Oct 26, 2009 at 11:18 PM, Andrew Dunstan and...@dunslane.net wrote: Kiswono Prayogo wrote: plv8js is a procedural language add-on for PostgreSQL, which means you can define Javascript functions that run inside a PostgreSQL server using google V8 Engine. anyone who want to contribute in plv8js please visit this group ^^ http://code.google.com/p/plv8js/ i'm still new in postgresql and v8, need MASSIVELY LOT of study and help ^^, thank you.. Well, I see you are apparently ignoring the advice we already gave you that V8 is not likely to be the best fit for a PostgreSQL JS procedural language, so that's not a good start. that's a little harsh...he was mostly given some vague advice (spidermonkey might be better...). Maybe it isn't the best, but that doesn't mean it's not worth trying! 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] Proposal: String key space for advisory locks
On Mon, Oct 26, 2009 at 4:30 PM, Josh Berkus j...@agliodbs.com wrote:
Why aren't you satisfied with hashtext('foo') ?
Collisions, mostly.
Why even bother with a hash function when you can just have multiple
table pull from a shared sequence? AFAICT, this solves the OP's
problem with no downsides (I used the approach with excellent results
in a ported cobol app which had pessimistic locking requirement).
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] Proposal: String key space for advisory locks
On Tue, Oct 27, 2009 at 12:43 PM, Josh Berkus j...@agliodbs.com wrote:
Merlin,
Why even bother with a hash function when you can just have multiple
table pull from a shared sequence? AFAICT, this solves the OP's
problem with no downsides (I used the approach with excellent results
in a ported cobol app which had pessimistic locking requirement).
Well, if you have enough tables, the sequence itself becomes a
bottleneck
I wonder if that's a legacy problem...I tested on our development
server w/pgbench -f and measured that nextval('s') scaled almost
linearly (I tested up to 900 clients) at about 70% of 'select 0'. (28k
tps on 4 core dell server vs 40k peak). pgbench does have it's own
scaling problems though. Since I happen to be working on a project
that relies heavily on high traffic sequences, do you have any
specific insights on known scaling problems with sequences?
It also offends my sense of good database design, but that's another
issue entirely.
I basically agree.
More importantly, I think the issues raised here cause developers not to
use advisory locks and instead use solutions more subject to race
conditions, like a locking table. Advisory locks could be a really cool
feature for developers if it was just a bit more usable.
'as is', advisory locks is a fantastic feature that can be used for
signaling, mutexing, etc that are relatively difficult things to do in
the transactional world of sql. My main gripe is that the 'shared id'
method for doing record pessimistic locks is basically a nuclear
missile pointed at your shared buffers if you don't have lot of
discipline in the queries that lock IDs. Maybe this argues for more
of a 'sql exposed' pessimistic lock feature that operates on similar
level as 'for update'...I'm not sure...curious what thoughts you have
about improving them.
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] libpq - extending PQexecParams/PQexecPrepared to specify resultFormat for individual result columns
On Mon, Nov 2, 2009 at 6:00 AM, Ivo Raisr iv...@ivosh.net wrote: Hi guys, I hacked PostgreSQL 8.4.1 libpq and added a new function to specify resultFormat for individual result columns. Are you interested in a patch? (http://www.postgresql.org/docs/8.4/interactive/libpq-exec.html says: There is not currently a provision to obtain different result columns in different formats, although that is possible in the underlying protocol. So I made this possible.) How exactly did you do it? You can't change the arguments of existing libpq functions, you would have had to add new ones... 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] Typed tables
On Thu, Nov 5, 2009 at 12:24 PM, Peter Eisentraut pete...@gmx.net wrote: I'm planning to work on typed tables support. The idea is that you create a table out of a composite type (as opposed to the other way around, which is currently done automatically). CREATE TYPE persons_type AS (name text, bdate date); CREATE TABLE persons OF persons_type; Or the fancy version: CREATE TABLE persons OF persons_type ( PRIMARY KEY (name) ); I use composite types (via tables) all the time but I never use 'create type as'...because by doing so you lose the ability to alter the type with 'alter table'. Am I correct that I could use your idea to make this possible (albeit quite ugly) by: create type foo(a text, b text); create table foo of foo; alter table foo add column c text; drop table foo; -- does this drop the type as well?? 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] Listen / Notify rewrite
On Wed, Nov 11, 2009 at 5:48 PM, A.M. age...@themactionfaction.com wrote: At least with this new payload, I can set the payload to the transaction ID and be certain that all the notifications I sent are processed (and in order even!) but could you explain why the coalescing is still necessary? Christmas comes early this year! :-). three reasons: *) it works that way now...a lot of people use this feature for all kinds of subtle things and the behavior chould change as little as possible *) legacy issues aside, I think it's generally better behavior (how many times do you need to be tapped on the shoulder?) *) since you can trivially differentiate it (using xid, sequence, etc), what's the fuss? 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] Listen / Notify rewrite
On Thu, Nov 12, 2009 at 8:25 AM, Andrew Chernow a...@esilo.com wrote: 2. The payload parameter is optional. A notifying client can either call NOTIFY foo; or NOTIFY foo 'payload';. The length of the payload is currently limited to 128 characters... Not sure if we should allow longer payload strings... Might be a good idea to make the max the same as the max length for prepared transaction GUIDs? Not sure anyone would be shipping those around, but it's a pre-existing limit of about the same size. Yes, sounds reasonable to have the same limit for user-defined identifiers... [..begging..] Can this be increased significantly? I don't get it, is there any technical reason to make the limit soo small? This drastically reduces the usefulness of the payload. I've wanted this feature for quite sometime and it is quite disappointing that I could not even use it because it is unjustifiably limited. +1 What advantage is there in limiting it to a tiny size? This is a 'payload' after all...an arbitrary data block. Looking at the patch I noticed the payload structure (AsyncQueueEntry) is fixed length and designed to lay into QUEUE_PAGESIZE (set to) BLCKSZ sized pages. Couple of questions: *) is BLCKSZ a hard requirement, that is, coming from the slru implementation, or can QUEUE_PAGESIZE be bumped independently of block size. *) why not make the AsyncQueueEntry divide evenly into BLCKSZ, that is, make the whole structure a size that is a multiple of two? (this would make the payload length 'weird') *) is there any downside you see to making the AsyncQueueEntry structure exactly BLCKSZ bytes in size? Are we worried about the downsides of spinning the notifications out to disk? *) Is a variable length AsyncQueueEntry possible? (presumably bounded by the max page size). Or does complicate the implementation too much? merlin 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] Listen / Notify rewrite
On Thu, Nov 12, 2009 at 11:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: Joachim Wieland j...@mcknight.de writes: However I share Greg's concerns that people are trying to use NOTIFY as a message queue which it is not designed to be. Yes. Particularly those complaining that they want to have very large payload strings --- that's pretty much a dead giveaway that it's not being used as a condition signal. Now you might say that yeah, that's the point, we're trying to enable using NOTIFY in a different style. The problem is that if you are trying to use NOTIFY as a queue, you will soon realize that it has the wrong semantics for that --- in particular, losing notifies across a system crash or client crash is OK for a condition notification, not so OK for a message queue. The difference is that the former style assumes that the authoritative data is in a table somewhere, so you can still find out what you need to know after reconnecting. If you are doing messaging you are likely to think that you don't need any backing store for the system state. So while a payload string for NOTIFY has been on the to-do list since forever, I have to think that Greg's got a good point questioning whether it is actually a good idea. You guys are assuming it's being used in a queue, which is only a fraction of cases where this feature is useful. In fact, having a payload can remove the need for a queue completely where is currently required for no other reason to deliver payload messages. I'm sorry, the 128 character limit is simply lame (other than for unsolvable implementation/performance complexity which I doubt is the case here), and if that constraint is put in by the implementation, than the implementation is busted and should be reworked until it's right. A feature that is being used for things not intended is a sign of a strong feature, not a weak one, and the idea that a payload should be length limited in order to prevent use in ways that are 'wrong' is a very weak argument IMO. People have been asking for this feature since the beginning of time, and nobody said: 'please limit it to 128 bytes'. A limit of 4k - 64k is much more appropriate if you even want a hard limit at all... 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] Listen / Notify rewrite
On Thu, Nov 12, 2009 at 11:39 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Nov 12, 2009 at 11:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: Joachim Wieland j...@mcknight.de writes: However I share Greg's concerns that people are trying to use NOTIFY as a message queue which it is not designed to be. Yes. Particularly those complaining that they want to have very large payload strings --- that's pretty much a dead giveaway that it's not being used as a condition signal. Now you might say that yeah, that's the point, we're trying to enable using NOTIFY in a different style. The problem is that if you are trying to use NOTIFY as a queue, you will soon realize that it has the wrong semantics for that --- in particular, losing notifies across a system crash or client crash is OK for a condition notification, not so OK for a message queue. The difference is that the former style assumes that the authoritative data is in a table somewhere, so you can still find out what you need to know after reconnecting. If you are doing messaging you are likely to think that you don't need any backing store for the system state. So while a payload string for NOTIFY has been on the to-do list since forever, I have to think that Greg's got a good point questioning whether it is actually a good idea. I think there could be cases where the person writing the code can know, extrinsic to the system, that lost notifications are OK, and still want to deliver a payload. But I think the idea of enabling a huge payload is not wise, as it sounds like it will sacrifice performance for a feature that is by definition not essential to 'premature optimization is the root of all evil' :-) 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] Listen / Notify rewrite
On Thu, Nov 12, 2009 at 11:40 AM, Merlin Moncure mmonc...@gmail.com wrote: I'm sorry, the 128 character limit is simply lame (other than for unsolvable implementation/performance complexity which I doubt is the case here), and if that constraint is put in by the implementation, than the implementation is busted and should be reworked until it's right. After some reflection, I realized this was an overly strong statement and impolite to the OP. It's easy to yarp from the gallery with the other peanuts :-). It's not the implementation I have an issue with, just the _idea_ that we should be restricted to small payloads for religious reasons...until that came upI was already scheming on how to both extend the patch to be more flexible in terms of payload size, and to backpatch and test it on 8.4 (no point if the community has no interest however). In any event, sorry for the strong words. 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] Listen / Notify rewrite
On Fri, Nov 13, 2009 at 5:35 AM, Greg Stark gsst...@mit.edu wrote: On Fri, Nov 13, 2009 at 1:57 AM, Robert Haas robertmh...@gmail.com wrote: I agree. We frequently reject features on the basis that someone might do something stupid with them. It's lame and counterproductive, and we should stop. The world contains infinite amounts of lameness, but that's the world's problem, not ours. There is zero evidence that this feature is only useful for stupid purposes, and some evidence (namely, the opinions of esteemed community members) that it is useful for at least some non-stupid purposes. This is BS. The problem is not that someone might do something stupid with this feature. The problem is that we're making these other use cases into requirements which will influence the design. This is a classic feature creep situation and the result is normally products which solve none of the use cases especially well. Removing a length restriction is feature creep? Having an flexible payload mechanism improves on notify in the same way that epoll improves on poll. Yes, epoll is overdesigned, highly overused, etc. but it does vastly improve server handling/responsiveness in some situations. Delivering a notification with data saves a round trip back to the server and a transaction which is both helpful in terms of server load and improving latency. On top of that, I don't think saying: hello; here's some data is groundbreaking in terms of network communication paradigms. My interest in this feature is not academic, the project I'm working on could use it with great benefit immediately. Arguments that I am using notify for the set list of use cases improvised by the original authors are not going to hold much water with me :-). IMNSHO, I don't think that keeping payloads limited to a tiny size 'improves' this feature is a winnable argument. That said, I do appreciate simple designs and very much understand trying to keep things simple. So let me ask you this: *) Are you sure that putting a variable length payload into the slru is going to complicate things that badly in terms of implementing this feature? If so, how? *) Wouldn't you agree that variable length would actually benefit 'proper' (small) payloads by allowing more of them to fit in the slru page? *) 8k should be enough for anybody :-) ...so if a variable length structure can be made why not max the payload length at blcksz-hdrsz and call it a day (yes, I am aware that extending the structure will reduce payload maximum length)? I think this should fit quite nicely into the OP's approach and benefits both people who use small payloads and large ones...(I DO think spanning pages is complex and probably unnecessary) 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] Listen / Notify rewrite
On Fri, Nov 13, 2009 at 10:00 AM, Andrew Chernow a...@esilo.com wrote: I think the original OP was close. The structure can still be fixed length but maybe we can bump it to 8k (BLCKSZ)? The problem with this (which I basically agree with) is that this will greatly increase the size of the queue for all participants of this feature if they use the payload or not. I think it boils down to this: is there a reasonably effective way of making the payload variable length (now or in the future)? If not, let's compromise and maybe go with a larger size, maybe 256 or 512 bytes. 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] Inspection of row types in pl/pgsql and pl/sql
On Sat, Nov 14, 2009 at 3:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: This might look neat but I don't think it's actually useful for any production application. We'd need to find some way of expressing it that allows caching of the expression plans. But really I think the entire approach is pretty much backwards from an efficiency standpoint. I would sooner have some sort of primitive changed_columns(NEW, OLD) that spits out a list of the names of changed columns (or maybe the not-changed ones, not sure). It would not require any fundamental restructuring and it would run several orders of magnitude faster than you could ever hope to do it at the plpgsql level. huge +1 to this. This problem comes up all the time...I was in fact this exact moment working on something just like Florian for table auditing purposes...comparing new/old but needing to filter out uninteresting columns. One of those things that should be a SMOP but isn't ;-). I worked out a plpgsql approach using dynamic sql...performance wasn't _that_ bad, but any speedup is definitely welcome. The way I did it was to pass both new and old to a function as text, and build an 'is distinct from' from with the interesting field list querying out fields from the expanded composite type...pretty dirty. 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] Listen / Notify rewrite
On Fri, Nov 13, 2009 at 11:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: (By the way, has anyone yet tried to compare the speed of this implementation to the old code?) I quickly hacked pgbench to take a custom script on connection (for listen), and make pgbench'd 'notify x'; with all clients doing 'listen x'. The old method (measured on a 4 core high performance server) has severe scaling issues due to table bloat (we knew that): ./pgbench -c 10 -t 1000 -n -b listen.sql -f notify.sql run #1 tps = 1364.948079 (including connections establishing) run #2 tps = 573.988495 (including connections establishing) vac full pg_listener ./pgbench -c 50 -t 200 -n -b listen.sql -f notify.sql tps = 844.033498 (including connections establishing) new method on my dual core workstation (max payload 128): ./pgbench -c 10 -t 1 -n -b listen.sql -f notify.sql -hlocalhost postgres tps = 16343.012373 (including connections establishing) ./pgbench -c 20 -t 5000 -n -b listen.sql -f notify.sql -hlocalhost postgres tps = 7642.104941 (including connections establishing) ./pgbench -c 50 -t 5000 -n -b listen.sql -f notify.sql -hlocalhost postgres tps = 3184.049268 (including connections establishing) max payload 2048: ./pgbench -c 10 -t 1 -n -b listen.sql -f notify.sql -hlocalhost postgres tps = 12062.906610 (including connections establishing) ./pgbench -c 20 -t 5000 -n -b listen.sql -f notify.sql -hlocalhost postgres tps = 7229.505869 (including connections establishing) ./pgbench -c 50 -t 5000 -n -b listen.sql -f notify.sql -hlocalhost postgres tps = 3219.511372 (including connections establishing) getting sporadic 'LOG: could not send data to client: Broken pipe' throughout the test. 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] psql \G command -- send query and output using extended format
On Thu, Apr 3, 2008 at 2:43 PM, David Fetter [EMAIL PROTECTED] wrote: On Thu, Apr 03, 2008 at 01:06:26PM -0400, Bruce Momjian wrote: Some sort of \x auto? Sounds interesting ... Yep. Having \df+ go to \x automatically sounds like a really great idea :) you can get pretty good resultsr currently for \df+ if you set up your 'less' pager a particular way. 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] psql \G command -- send query and output using extended format
On Thu, Apr 3, 2008 at 4:08 PM, David Fetter [EMAIL PROTECTED] wrote: On Thu, Apr 03, 2008 at 03:43:50PM -0400, Merlin Moncure wrote: On Thu, Apr 3, 2008 at 2:43 PM, David Fetter [EMAIL PROTECTED] wrote: On Thu, Apr 03, 2008 at 01:06:26PM -0400, Bruce Momjian wrote: Some sort of \x auto? Sounds interesting ... Yep. Having \df+ go to \x automatically sounds like a really great idea :) you can get pretty good resultsr currently for \df+ if you set up your 'less' pager a particular way. Does 'less' have a way to re-arrange columns?!? no, but being able to scroll left/right with the arrow keys is (usually) just as good. that said, \G is pretty neat...I use it once in a while on the 'other' open source database. 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] [PATCHES] libpq type system 0.9a
On Tue, Apr 8, 2008 at 12:59 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Sorry for the bad news. I think we all hoped that enough interest would be generated for this to be accepted. I think that's really unfortunate. Personally, I think that anyone who did any amount of C coding against libpq at all would never have any reason to code in the traditional fashion (PQexec, etc). Anyone who would claim otherwise IMO does not code vs. libpq or does not completely understand what we are trying to do. In particular, I think that the decision to so quickly shut the door on the ability to support arrays and composites in binary on the client side. Contrary to what is stated there have been considerable requests for this on the various lists. I am dismayed that throughout this process there has been no substantive discussion (save for Tom) on what we were trying to do, only to be informed about rejection based on an internal discussion. What issues were raised were opaque and sans reasoning or justification of how that would improve the patch or the functionality (move to separate library for example -- how would this improve things?). Our follow ups were not followed up. We would have been delighted to take suggestions. I attributed the silence to general lack of interest and anticipated this response. However I think that those involved should step back and take a look at what they are walking away from here. 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] [PATCHES] libpq type system 0.9a
On Tue, Apr 8, 2008 at 1:51 PM, Greg Sabino Mullane [EMAIL PROTECTED] wrote: I think you should conduct a wider survey before you make that decision. In particular, I'd like to hear from driver writers like Greg Sabino Mullane and Jeff Davis, as well as regular libpq users. I can state that there would be almost zero chance this would ever be used by DBD::Pg, as it would seem to add overhead with no additional functionality over what we already have. Unless I'm misreading what it does and someone can make the case why I should use it. does DBD::pg move arrays in and out of the server? do you parse them yourself? if you answer yes to either question you should take a second look. 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] [PATCHES] libpq type system 0.9a
On Tue, Apr 8, 2008 at 2:49 PM, Andrew Dunstan [EMAIL PROTECTED] wrote: Better support for arrays and composites is certainly something that people might want, but the problem with this design is that it forces them to buy into a number of other decisions that they don't necessarily want. I could see adding four functions to libpq that create and parse the textual representations of arrays and records. Well, that was the part that interested me, so let me now speak up in favor of better array/record support in libpq. by the way, we handle both text and binary array results...and getting things in binary is _much_ faster. not to mention text is destructive. for example, composite types in text do not return the oid of composite member fields. with our patch, since you can 'pop' a result of a returned composite, or array of composite, you have access to all that information in the result api. so I would argue that allowing text only parsing only recovers a portion of the provided functionality. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
