Re: [HACKERS] Bug in visibility hint bit

2009-08-25 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: ... But really, I don't think such communication should be necessary, and the xlrec.all_visible_cleared and xlrec.new_all_visible_cleared fields are unneeded. Just assume they are true. It seems like the worst thing that can happen is that we call

Re: [HACKERS] DELETE syntax on JOINS

2009-08-25 Thread Jean-Michel Pouré
With proper foreign keys and ON DELETE CASCADE, why would supporting such syntax even be necessary? Porting existing abstraction layers from ANSI JOINs to ON DELETE CASCADE is complicated. What I don't get is why this is such a usability issue. Subqueries in DELETE FROM work perfectly well,

Re: [HACKERS] Slaying the HYPOTamus

2009-08-25 Thread Paul Matthews
Greg Stark wrote: We're implementing things like box_distance and point_distance which as it happens will already be doing earlier arithmetic on the doubles, so whatever HYPOT() does had better be consistent with that or the results will be just an inexplicable mishmash. Let's look at

[HACKERS] setting up scan keys

2009-08-25 Thread Markus Wanner
Hi, I'm currently having difficulties understanding how to setup scan keys for simple index scans. What I want to achieve is scanning a primary key index (thus unique constraint - and as far as I know only nbtree can handle these, right?). Up until now I've used something along the

Re: [HACKERS] Unicode UTF-8 table formatting for psql text output

2009-08-25 Thread Roger Leigh
On Sun, Aug 23, 2009 at 06:33:49PM -0400, Alvaro Herrera wrote: Roger Leigh escribió: +#if (defined(HAVE_LANGINFO_H) defined(CODESET)) + if (!strcmp(nl_langinfo(CODESET), UTF-8)) + text_format = utf8format; +#endif I think you should also try to match to UTF8, and do it

Re: [HACKERS] pg_hba.conf: samehost and samenet

2009-08-25 Thread Magnus Hagander
On Wed, Aug 19, 2009 at 15:02, Stef Walterstef-l...@memberwebs.com wrote: Magnus Hagander wrote: On Wed, Aug 19, 2009 at 03:58, Stef Walterstef-l...@memberwebs.com wrote: Attached is a new patch, which I hope addresses all the concerns raised. I think you forgot to actually attach the

Re: [HACKERS] [PATCH] plpythonu datatype conversion improvements

2009-08-25 Thread Peter Eisentraut
On mån, 2009-08-17 at 11:55 -0400, Alvaro Herrera wrote: Peter Eisentraut wrote: I have reworked this patch a bit and extended the plpython test suite around it. Current copy attached. I think the errcontext bits should be committed separately to get them out of the way (and to ensure

Re: [HACKERS] DELETE syntax on JOINS

2009-08-25 Thread Bruce Momjian
Josh Berkus wrote: All, DELETE FROM target t USING t LEFT JOIN other_table ot ON ... but we have always considered that the target is *not* to be identified with any member of the FROM/USING clause, so it would be a serious compatibility break to change that now. What I don't get

Re: [HACKERS] DELETE syntax on JOINS

2009-08-25 Thread Robert Haas
On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkusj...@agliodbs.com wrote: All, DELETE FROM target t USING t LEFT JOIN other_table ot ON ... but we have always considered that the target is *not* to be identified with any member of the FROM/USING clause, so it would be a serious compatibility

Re: [HACKERS] setting up scan keys

2009-08-25 Thread Tom Lane
Markus Wanner mar...@bluegap.ch writes: Up until now I've used something along the following lines to setup the scan keys for a BTGreaterStrategy scan: procinfo = index_getprocinfo(index_rel, i + 1, BTORDER_PROC); ScanKeyEntryInitializeWithInfo(skeys[i],

Re: [HACKERS] DELETE syntax on JOINS

2009-08-25 Thread Andrew Dunstan
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkusj...@agliodbs.com wrote: What I don't get is why this is such a usability issue. Subqueries in DELETE FROM work perfectly well, and provide more flexibility than most users know

[HACKERS] return HeapTuple

2009-08-25 Thread Werner Echezuria
Hi, I wanna return a group of rows, like when you do SELECT columns FROM table, but I'm getting some troubles, I don't know if I have to use HeapTuple or Datum. I'm using bison to parse sqlf to sql this way: -- some code %% query: /* empty string */ | query command ; command: '\n' |

Re: [HACKERS] DELETE syntax on JOINS

2009-08-25 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Mon, Aug 24, 2009 at 9:31 PM, Josh Berkusj...@agliodbs.com wrote: What I don't get is why this is such a usability issue.  Subqueries in DELETE FROM work perfectly well, and provide more flexibility than most users know what to do with. It's both

Re: [HACKERS] DELETE syntax on JOINS

2009-08-25 Thread Greg Stark
On Tue, Aug 25, 2009 at 2:50 PM, Tom Lanet...@sss.pgh.pa.us wrote: On the performance front: yeah, you can recast most joins as subqueries, but you tend to end up with the equivalent of a nestloop plan.  Works okay for small numbers of rows, scales horribly. Well that's our problem isn't it? I

Re: [HACKERS] DELETE syntax on JOINS

2009-08-25 Thread Tom Lane
Greg Stark gsst...@mit.edu writes: You would have to write something like UPDATE foo set a = (select aa from bar where...) b = (select bb from bar where...) and then the optimizer would have to notice the duplicates and consolidate them? That seems inconvenient (and

Re: [HACKERS] Bug in date arithmetic

2009-08-25 Thread Kevin Grittner
David Fetter da...@fetter.org wrote: On Mon, Aug 24, 2009 at 07:48:06PM -0400, Tom Lane wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: I realize I'm in a minority on this, but I would also prefer an error. I expect things like SELECT date + (INTERVAL '1' YEAR) to just

[HACKERS] Feature / Enhancement request.

2009-08-25 Thread Melvin Davidson
It would really be nice if pg_class were altered to add two new columns: relcreatedat relmoddat. Both would be of type timestamp. relcreatedat would contain the timestamp the object was created. Likewise, relmoddat would contain the last timestamp the object was altered / modified. This would

[HACKERS] SET syntax in INSERT

2009-08-25 Thread Rob Wultsch
Given the recent discussion of DELETE syntax on JOINS I thought it might be interesting to bring a bit MySQL syntax that is in somewhat widespread use, generally create somewhat cleaner code and I imagine would not break much if implemented. MySQL allows INSERTs of the form: INSERT INTO t SET

Re: [HACKERS] SET syntax in INSERT

2009-08-25 Thread Pavel Stehule
2009/8/25 Rob Wultsch wult...@gmail.com: Given the recent discussion of DELETE syntax on JOINS  I thought it might be interesting to bring a bit MySQL syntax that is in somewhat widespread use, generally create somewhat cleaner code and I imagine would not break much if implemented. MySQL

Re: [HACKERS] SET syntax in INSERT

2009-08-25 Thread Heikki Linnakangas
Pavel Stehule wrote: 2009/8/25 Rob Wultsch wult...@gmail.com: Given the recent discussion of DELETE syntax on JOINS I thought it might be interesting to bring a bit MySQL syntax that is in somewhat widespread use, generally create somewhat cleaner code and I imagine would not break much if

Re: [HACKERS] Feature / Enhancement request.

2009-08-25 Thread Jaime Casanova
On Tue, Aug 25, 2009 at 11:18 AM, Melvin Davidsonmelvin.david...@rackspace.com wrote: This would be very useful for user tables that are meant to exist for only a specific length of time. eg: 1  month. that's a weird concept Likewise, relmoddat would be useful for determining when someone

Re: [HACKERS] SET syntax in INSERT

2009-08-25 Thread Rob Wultsch
On Tue, Aug 25, 2009 at 10:36 AM, Pavel Stehulepavel.steh...@gmail.com wrote: 2009/8/25 Rob Wultsch wult...@gmail.com: Given the recent discussion of DELETE syntax on JOINS  I thought it might be interesting to bring a bit MySQL syntax that is in somewhat widespread use, generally create

Re: [HACKERS] pg_hba.conf: samehost and samenet

2009-08-25 Thread Stef Walter
Magnus Hagander wrote: On Wed, Aug 19, 2009 at 15:02, Stef Walterstef-l...@memberwebs.com wrote: Magnus Hagander wrote: On Wed, Aug 19, 2009 at 03:58, Stef Walterstef-l...@memberwebs.com wrote: Attached is a new patch, which I hope addresses all the concerns raised. I think you forgot to

Re: [HACKERS] SET syntax in INSERT

2009-08-25 Thread Pavel Stehule
For an insert with many columns or with large value this syntax can significantly improve readability. So it wasn't invented here, so what? I don't see a downside to allowing this syntax other than MySQL used it first, and there are multiple upsides (readability, easier transitions).

Re: [HACKERS] pg_hba.conf: samehost and samenet

2009-08-25 Thread Alvaro Herrera
Stef Walter wrote: Magnus Hagander wrote: and not just use SIOCGIFCONF for all Unixen? I do know that using SIOCGIFCONF on AIX comes with strange wrinkles and variable length data structures etc... getifaddrs() on AIX is a far more maintainable interface. Clearly the getifaddrs code

Re: [HACKERS] SET syntax in INSERT

2009-08-25 Thread Andrew Dunstan
Rob Wultsch wrote: -1 PostgreSQL isn't MySQL! For an insert with many columns or with large value this syntax can significantly improve readability. So it wasn't invented here, so what? I don't see a downside to allowing this syntax other than MySQL used it first, and there are

Re: [HACKERS] SET syntax in INSERT

2009-08-25 Thread Alvaro Herrera
Heikki Linnakangas escribió: I do understand the point, though - it's much easier to edit and debug long statements when the value is close to the column name. I find that the INSERT .. SELECT makes that a lot nicer: INSERT INTO t

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Hmm. As stated, I would expect pg_ctl to make it worse. I've been playing with this, and I think the problem was that we wanted a non-zero exit from the script if the start failed. That's trivial with pg_ctl -w but not running postgres directly. I guess

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Andrew Dunstan
Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: Hmm. As stated, I would expect pg_ctl to make it worse. I've been playing with this, and I think the problem was that we wanted a non-zero exit from the script if the start failed. That's trivial with pg_ctl -w but not

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Alvaro Herrera
Kevin Grittner wrote: The reason is that we don't want certain other processes attempting to start until and unless the database they use has started successfully. This is something we're not quite ready on, yet. We need some mechanism that allows scripts to verify not only that postmaster

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: Here's a snippet from my F11 system: $SU -l postgres -c $PGENGINE/postmaster -p '$PGPORT' -D '$PGDATA' ${PGOPTS} $PGLOG 21 /dev/null sleep 2 pid=`pidof -s $PGENGINE/postmaster` if [ $pid ] [ -f

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Chander Ganesan
Alvaro Herrera wrote: Kevin Grittner wrote: The reason is that we don't want certain other processes attempting to start until and unless the database they use has started successfully. This is something we're not quite ready on, yet. We need some mechanism that allows scripts to

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Kevin Grittner
Chander Ganesan chan...@otg-nc.com wrote: Alvaro Herrera wrote: Kevin Grittner wrote: The reason is that we don't want certain other processes attempting to start until and unless the database they use has started successfully. This is something we're not quite ready on, yet. We need

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Thanks Andrew, Alvaro, and Chander. You've given me some thoughts to toss around. Of course, any of these is going to be somewhat more complex than using [ pg_ctl -w ] Yeah. I wonder if we shouldn't expend a bit more effort to make that way

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Of course, this is a complete kluge --- it assumes the postmaster will create its pidfile in less than two seconds. And for that matter, it's not very proof against the case of a pre-existing postmaster. But in any case, it (intentionally) doesn't wait

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Alvaro Herrera
Chander Ganesan wrote: Alvaro Herrera wrote: This is something we're not quite ready on, yet. We need some mechanism that allows scripts to verify not only that postmaster started, but also that it has finished recovery. You can sort-of do it by attempting a connection and checking the

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: The two ways I can see to do that are to add a command line switch to the postmaster, or to pass the PID as an environment variable, say PG_GRANDPARENT_PID. The latter is a bit uglier but it would require touching much less code (and documentation).

Re: [HACKERS] pg_hba.conf: samehost and samenet

2009-08-25 Thread Alvaro Herrera
Something is very wrong here -- this message does not have a message-id! Stef Walter wrote: Magnus Hagander wrote: On Wed, Aug 19, 2009 at 15:02, Stef Walterstef-l...@memberwebs.com wrote: Magnus Hagander wrote: On Wed, Aug 19, 2009 at 03:58, Stef Walterstef-l...@memberwebs.com wrote:

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Kevin Grittner
Alvaro Herrera alvhe...@commandprompt.com wrote: That's within my definition of ugly, yes :-) My ideal tool would do something like $ pg_ping -h foo -p IN_RECOVERY $ echo $? 2 $ # sleep a bit ... $ pg_ping -h foo -p READY $ echo $? 0 Cool, but how would you do that

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: You're thinking that pg_ctl would capture it's parent PID and pass it to the postmaster one way or the other? That seems like it covers the specific issue you were referencing up-thread. It has been bubbling around in my head that we have

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Alvaro Herrera alvhe...@commandprompt.com wrote: That's within my definition of ugly, yes :-) My ideal tool would do something like $ pg_ping -h foo -p IN_RECOVERY $ echo $? 2 $ # sleep a bit ... $ pg_ping -h foo -p

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Alvaro Herrera
Kevin Grittner wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: That's within my definition of ugly, yes :-) My ideal tool would do something like $ pg_ping -h foo -p IN_RECOVERY $ echo $? 2 $ # sleep a bit ... $ pg_ping -h foo -p READY $ echo $?

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Andrew Dunstan
Tom Lane wrote: But in any case, it (intentionally) doesn't wait for the postmaster to be ready to accept connections, so it's not solving Kevin's problem. Maybe we need a --wait mode for pg_ctl status that would test connecting to the database the same way it

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Only if they are running at times when your postmaster(s) aren't ... Well, those rsync scripts for pushing the PITR base backup and the WAL stream to other machines are crontab jobs which kick off once per minute. Still, just from a security point of

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Still, this seems like it's not as deterministic as it should be. Is there any reasonable way to pin it down beyond the PID? Like also saving a start time into the postmaster.pid file and checking that

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Alvaro Herrera
Kevin Grittner wrote: You wouldn't object to using either of those in a Linux service script, though, would you? Yeah, operating-system-specific init scripts do not need to be portable :-) Of course, they need to work across a wide range of Linux systems ... -- Alvaro Herrera

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: stuff like vacuum scripts could surely be run from a different userid. My first thought was they have to run as the database superuser. (In our case, that is the same as the OS user running the cluster.)

Re: [HACKERS] Feature / Enhancement request.

2009-08-25 Thread Josh Berkus
On 8/25/09 9:18 AM, Melvin Davidson wrote: It would really be nice if pg_class were altered to add two new columns: relcreatedat relmoddat. I don't think that altering pg_class would really cover all the various cases of what people want for auditing functionality. It would be more useful

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: How would you get the latter in a portable fashion? (Do not mention ps please ... and I don't want to hear about lsof either ...) You wouldn't object to using either of those in a Linux service script,

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Aidan Van Dyk
* Tom Lane t...@sss.pgh.pa.us [090825 18:43]: How would you get the latter in a portable fashion? (Do not mention ps please ... and I don't want to hear about lsof either ...) Can postmaster keep a exclusive lock on its own pid file the entire time it's running? If you can open it and lock

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Tom Lane
Aidan Van Dyk ai...@highrise.ca writes: Can postmaster keep a exclusive lock on its own pid file the entire time it's running? That's been discussed, but file locking isn't all that portable or trustworthy :-( regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] We should Axe /contrib/start-scripts

2009-08-25 Thread Chander Ganesan
Tom Lane wrote: Aidan Van Dyk ai...@highrise.ca writes: Can postmaster keep a exclusive lock on its own pid file the entire time it's running? That's been discussed, but file locking isn't all that portable or trustworthy :-( regards, tom lane What about

Re: [HACKERS] TODO item: Allow more complex user/database default GUC settings

2009-08-25 Thread Alvaro Herrera
Alvaro Herrera wrote: Implementation-side, it requires a new catalog (pg_settings), with the following columns: So, I've come up with the attached patch. It does not have the new command yet, so you can do ALTER USER and ALTER DATABASE and it works, but there's no way to set

Re: [HACKERS] Unicode UTF-8 table formatting for psql text output

2009-08-25 Thread Alvaro Herrera
Roger Leigh escribió: An updated copy of the patch is attached. I give it a try. It looks very reasonable. We could argue about the exact chars to use but that can be changed later. Did you give expanded output a look? (\x) I find it a bit weird that the first line shows a single-pixel

[HACKERS] Difficulty in partial writable TOAST value

2009-08-25 Thread KaiGai Kohei
I've considered the way to implement access controls on the largeobject feature since the first commit fest. One suggestion was that it may be implementable as a partial read/write interface to TOAST values (which should be newly added), and existing largeobject feature performs as a simple