On Tue, Apr 28, 2015 at 11:32 AM, Robert Haas wrote:
> On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
> wrote:
>
> > I think what we need here is something that does heap_update to tuples
> > at the end of the table, moving them to earlier pages; then wait for old
> > snapshots to die (the infr
On Tue, Apr 28, 2015 at 2:44 PM, Alvaro Herrera
wrote:
>> > I think what we need here is something that does heap_update to tuples
>> > at the end of the table, moving them to earlier pages; then wait for old
>> > snapshots to die (the infrastructure for which we have now, thanks to
>> > CREATE IN
On 4/28/15 1:32 PM, Robert Haas wrote:
More than five years have passed since Heikki posted this, and we still
>haven't found a solution to the problem -- which neverthless keeps
>biting people to the point that multiple "user-space" implementations of
>similar techniques are out there.
Yeah. T
Robert Haas wrote:
> On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
> wrote:
> > I think what we need here is something that does heap_update to tuples
> > at the end of the table, moving them to earlier pages; then wait for old
> > snapshots to die (the infrastructure for which we have now, tha
On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
wrote:
> Heikki Linnakangas wrote:
>> Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
>> and swapping relfilenodes afterwards. More like the VACUUM REWRITE
>> that's been discussed.
>>
>> For the kicks, I looked at what it wo
On 4/25/15 6:30 AM, Simon Riggs wrote:
On 24 April 2015 at 22:36, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote:
Instead of adding forcefsm, I think it would be more useful to
accept a target block number. That way we can actually control where
the new tuple goes. For this partic
On 24 April 2015 at 22:36, Jim Nasby wrote:
> Instead of adding forcefsm, I think it would be more useful to accept a
> target block number. That way we can actually control where the new tuple
> goes. For this particular case we'd presumably go with normal FSM page
> selection logic, but someon
On 4/24/15 5:30 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
It looks like the biggest complaint (aside from allowing a limited number of
tuples to be moved) is in [1] and [2], where Tom is saying that you can't
simply call heap_update() like this without holding an exclusive lock on the
table. I
Jim Nasby wrote:
> It looks like the biggest complaint (aside from allowing a limited number of
> tuples to be moved) is in [1] and [2], where Tom is saying that you can't
> simply call heap_update() like this without holding an exclusive lock on the
> table. Is that because we're not actually cha
On 4/24/15 3:34 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
Honestly, I'd prefer we exposed some way to influence where a new tuple gets
put, and perhaps better ways of accessing tuples on a specific page. That
would make it a lot easier to handle this in userspace, but it would also
make it eas
Jim Nasby wrote:
> Honestly, I'd prefer we exposed some way to influence where a new tuple gets
> put, and perhaps better ways of accessing tuples on a specific page. That
> would make it a lot easier to handle this in userspace, but it would also
> make it easier to do things like concurrent clus
On 4/24/15 2:04 PM, Alvaro Herrera wrote:
Heikki Linnakangas wrote:
Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
and swapping relfilenodes afterwards. More like the VACUUM REWRITE
that's been discussed.
For the kicks, I looked at what it would take to write a utilit
Heikki Linnakangas wrote:
> Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
> and swapping relfilenodes afterwards. More like the VACUUM REWRITE
> that's been discussed.
>
> For the kicks, I looked at what it would take to write a utility like
> that. It turns out to be q
Simon Riggs wrote:
> CTIDs don't help with optimistic locking, though it seems they can.
>
> If you don't hold open the transaction then someone else can update the
> row. That sounds good, but because of HOT it is possible that the same
> CTID with the same PK value occupies that exact CTID value
> Exactly. The application is typically going to throw a "concurrent
> update" type of error when this happens, and we don't want magic
> background operations to cause that.
>
I`d give up the possibility of using CTIDs in the way You explained
for an auto-debloater without blinking an eye. Maybe
On Fri, 2009-09-18 at 08:50 +0200, Albe Laurenz wrote:
> Tom Lane wrote:
> > > I don't see any reason why not breaking the user visible behavior of
> > > tuples CTID between any two major releases,
> >
> > > Am I completely wet here?
> >
> > Completely. This is a user-visible behavior that we h
On Thu, 2009-09-17 at 17:44 -0400, Tom Lane wrote:
> Dimitri Fontaine writes:
> > I don't see any reason why not breaking the user visible behavior of
> > tuples CTID between any two major releases,
>
> > Am I completely wet here?
>
> Completely. This is a user-visible behavior that we have en
Tom Lane wrote:
> > I don't see any reason why not breaking the user visible behavior of
> > tuples CTID between any two major releases,
>
> > Am I completely wet here?
>
> Completely. This is a user-visible behavior that we have encouraged
> people to rely on, and for which there is no easy sub
Tom Lane writes:
> Completely. This is a user-visible behavior that we have encouraged
> people to rely on, and for which there is no easy substitute.
Excited to have self-healing tables (against bloat), I parse this as an
opening. Previously on this thread you say:
> (Actually, the ctid is onl
Dimitri Fontaine writes:
> I don't see any reason why not breaking the user visible behavior of
> tuples CTID between any two major releases,
> Am I completely wet here?
Completely. This is a user-visible behavior that we have encouraged
people to rely on, and for which there is no easy substit
Hannu Krosing writes:
> But maybe something can be tahen from this discussion the other way
> round - maybe we should not be afraid of doing null updates during
> in-place update
The problem for in-place update is that it can't assume that any of the
normal infrastructure (like index insertion or
Hi,
Forewords: re-reading, I hope my english will not make this sound like a
high-kick when I'm just struggling to understand what all this is
about. Sending in order not to regret missing the oportunity I think I'm
seeing...
Tom Lane writes:
> Hannu Krosing writes:
>> On Wed, 2009-09-16 at 21:
On Thu, 2009-09-17 at 14:33 -0400, Greg Smith wrote:
> On Wed, 16 Sep 2009, Tom Lane wrote:
>
> >> * Shrink a table in place - when no space available
> > To be addressed by the UPDATE-style tuple-mover (which could be thought
> > of as VACUUM FULL rewritten to not use any special mechanisms).
>
On Thu, 2009-09-17 at 12:36 -0400, Tom Lane wrote:
> Hannu Krosing writes:
> > On Thu, 2009-09-17 at 12:18 -0400, Tom Lane wrote:
> >> Or for an update without having to hold a transaction open. We have
> >> recommended this type of technique in the past:
>
> > If you need real locking, then jus
On Wed, 16 Sep 2009, Tom Lane wrote:
* Shrink a table in place - when no space available
To be addressed by the UPDATE-style tuple-mover (which could be thought
of as VACUUM FULL rewritten to not use any special mechanisms).
Is there any synergy here with the needs of a future in-place upgrad
On Thu, 2009-09-17 at 12:30 -0400, Tom Lane wrote:
> Simon Riggs writes:
> > The update utility being discussed is in danger of confusing these two
> > goals
> > * compact the table using minimal workspace
> > * compact the table with minimal interruption to concurrent updaters
>
> Actually, the
Hannu Krosing writes:
> On Thu, 2009-09-17 at 12:18 -0400, Tom Lane wrote:
>> Or for an update without having to hold a transaction open. We have
>> recommended this type of technique in the past:
> If you need real locking, then just define a locked (or locked_by or
> locked_until) column and u
On Thu, Sep 17, 2009 at 12:31 PM, Hannu Krosing wrote:
>> Exactly. The application is typically going to throw a "concurrent
>> update" type of error when this happens, and we don't want magic
>> background operations to cause that.
>
> Would'nt current VACUUM FULL or CLUSTER cause much more grie
On Thu, Sep 17, 2009 at 12:18 PM, Tom Lane wrote:
>> It's no different from the situation where another backend UPDATEs the
>> row under your nose, but it's not something you want to do automatically
>> without notice.
>
> Exactly. The application is typically going to throw a "concurrent
> updat
On Thu, 2009-09-17 at 12:18 -0400, Tom Lane wrote:
> Heikki Linnakangas writes:
> > Robert Haas wrote:
> >> On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
> >>> Anything that moves tuples is not acceptable as a hidden background
> >>> operation, because it will break applications that depend o
Simon Riggs writes:
> The update utility being discussed is in danger of confusing these two
> goals
> * compact the table using minimal workspace
> * compact the table with minimal interruption to concurrent updaters
Actually, the update utility is explicitly meant to satisfy both of
those goals
On Thu, 2009-09-17 at 10:32 -0400, Tom Lane wrote:
> Hannu Krosing writes:
> > On Thu, 2009-09-17 at 10:21 -0400, Tom Lane wrote:
> >> because it will break applications that depend on CTID.
>
> > Do you know of any such applications out in the wild ?
>
> Yes, they're out there.
How do they dea
Heikki Linnakangas writes:
> Robert Haas wrote:
>> On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
>>> Anything that moves tuples is not acceptable as a hidden background
>>> operation, because it will break applications that depend on CTID.
>> I'm a bit confused. CTIDs change all the time an
Robert Haas wrote:
> On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
>> Hannu Krosing writes:
>>> On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
possible doesn't make it so.
>>> It depends on what do you mean b
On Thu, 2009-09-17 at 11:25 -0400, Robert Haas wrote:
> On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
> > Hannu Krosing writes:
> >> On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
> >>> VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
> >>> possible doesn't make it
On Thu, 2009-09-17 at 10:45 -0400, Tom Lane wrote:
> Hannu Krosing writes:
> > On Thu, 2009-09-17 at 09:45 -0400, Robert Haas wrote:
> >> Making the code more complicated so that it's easier to tune something
> >> that isn't very hard to tune anyway doesn't seem like a good
> >> trade-off.
>
> >
On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
> Hannu Krosing writes:
>> On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
>>> VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
>>> possible doesn't make it so.
>
>> It depends on what do you mean by "VACUUM FULL"
>
> Anyt
Hannu Krosing writes:
> On Thu, 2009-09-17 at 09:45 -0400, Robert Haas wrote:
>> Making the code more complicated so that it's easier to tune something
>> that isn't very hard to tune anyway doesn't seem like a good
>> trade-off.
> I think that just making sure that pessimal cases don't happen sh
Hannu Krosing writes:
> On Thu, 2009-09-17 at 10:21 -0400, Tom Lane wrote:
>> because it will break applications that depend on CTID.
> Do you know of any such applications out in the wild ?
Yes, they're out there.
regards, tom lane
--
Sent via pgsql-hackers mailing li
On Thu, 2009-09-17 at 10:21 -0400, Tom Lane wrote:
> Hannu Krosing writes:
> > On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
> >> VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
> >> possible doesn't make it so.
>
> > It depends on what do you mean by "VACUUM FULL"
>
On Thu, 2009-09-17 at 09:45 -0400, Robert Haas wrote:
> On Thu, Sep 17, 2009 at 9:35 AM, Joshua Tolley wrote:
> > On Wed, Sep 16, 2009 at 09:48:20PM -0400, Tom Lane wrote:
> >> Seems like there would
> >> be lots of situations where short exclusive-lock intervals could be
> >> tolerated, even thou
Hannu Krosing writes:
> On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
>> VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
>> possible doesn't make it so.
> It depends on what do you mean by "VACUUM FULL"
Anything that moves tuples is not acceptable as a hidden backgrou
On Thu, Sep 17, 2009 at 9:35 AM, Joshua Tolley wrote:
> On Wed, Sep 16, 2009 at 09:48:20PM -0400, Tom Lane wrote:
>> Seems like there would
>> be lots of situations where short exclusive-lock intervals could be
>> tolerated, even though not long ones. So that's another argument
>> for being able
On Wed, Sep 16, 2009 at 09:48:20PM -0400, Tom Lane wrote:
> Seems like there would
> be lots of situations where short exclusive-lock intervals could be
> tolerated, even though not long ones. So that's another argument
> for being able to set an upper bound on how many tuples get moved
> per call
On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
> Simon Riggs writes:
> > What we need is VACUUM FULL CONCURRENTLY and REINDEX CONCURRENTLY.
>
> VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
> possible doesn't make it so.
It depends on what do you mean by "VACUUM FULL
On Wed, 2009-09-16 at 23:12 -0400, Tom Lane wrote:
> Jeff Davis writes:
> > On Wed, 2009-09-16 at 21:48 -0400, Tom Lane wrote:
> >> Yeah, I was just wondering about that myself. Seems like there would
> >> be lots of situations where short exclusive-lock intervals could be
> >> tolerated, even t
Tom Lane wrote:
> Robert Haas writes:
>> On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane wrote:
>>> Simon Riggs writes:
* Shrink a table concurrently - when no dedicated time available
>>> Wishful thinking, which should not stop us from proceeding with the
>>> solutions we know how to implement.
Jeff Davis writes:
> On Wed, 2009-09-16 at 21:48 -0400, Tom Lane wrote:
>> Yeah, I was just wondering about that myself. Seems like there would
>> be lots of situations where short exclusive-lock intervals could be
>> tolerated, even though not long ones.
> But a short-lived exclusive lock can t
On Wed, 2009-09-16 at 21:48 -0400, Tom Lane wrote:
> Yeah, I was just wondering about that myself. Seems like there would
> be lots of situations where short exclusive-lock intervals could be
> tolerated, even though not long ones.
But a short-lived exclusive lock can turn into a long-lived exclu
Robert Haas writes:
> On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane wrote:
>> Simon Riggs writes:
>>> * Shrink a table concurrently - when no dedicated time available
>>
>> Wishful thinking, which should not stop us from proceeding with the
>> solutions we know how to implement.
> The UPDATE-style
On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane wrote:
> Simon Riggs writes:
>> The way I read the thread so far is that there are multiple
>> requirements:
>
>> * Shrink a table efficiently - when time and space available to do so
>
> To be addressed by the CLUSTER-based solution (VACUUM REWRITE or
>
Simon Riggs writes:
> The way I read the thread so far is that there are multiple
> requirements:
> * Shrink a table efficiently - when time and space available to do so
To be addressed by the CLUSTER-based solution (VACUUM REWRITE or
whatever we call it).
> * Shrink a table in place - when no
Simon Riggs writes:
> I think it would be useful to have an additional option to force VACUUM
> to wait for the lock so it can truncate. It's annoying to have to re-run
> VACUUM just to give it a chance at the lock again.
It would be better to separate out the truncate-what-you-can behavior
as an
On Wed, 2009-09-16 at 21:00 -0400, Tom Lane wrote:
> But if
> we get rid of old-style VACUUM FULL then we do need something to cover
> those few-and-far-between situations where you really do desperately
> need to compact a table in place; and a utility like this seems like a
> reasonable solutio
Simon Riggs writes:
> What we need is VACUUM FULL CONCURRENTLY and REINDEX CONCURRENTLY.
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
possible doesn't make it so.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresq
On Wed, 2009-09-16 at 20:36 -0400, Tom Lane wrote:
> Heikki Linnakangas writes:
> > For the kicks, I looked at what it would take to write a utility like
> > that. It turns out to be quite trivial, patch attached.
>
> I don't think you've really thought this through; particularly not this:
>
>
>Well, Andrew McNamara just posted today:
>http://archives.postgresql.org/message-id/20090916063341.0735c5ac...@longblack.object-craft.com.au
>
>Had VACUUM FULL not been available, though, I'm pretty sure he would've
>come up with something else instead.
Indeed I would have. And it was our own sla
On Wed, 2009-09-16 at 11:40 -0700, Jeff Davis wrote:
> Another thing to think about is that lazy vacuum only shrinks the heap
> file if it happens to be able to acquire an access exclusive lock.
> Because vacuum can't be run inside a transaction block, I don't think
> there's currently a way to e
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
> I was just going to post that we should make a decision about this,
> because ISTM there's some code in Simon's hot standby patch that is only
> required to support VACUUM FULL. If we make the decision that we drop
> VACUUM FULL in 8.
Heikki Linnakangas writes:
> I'm in favor of removing VACUUM FULL in 8.5. To replace it, we should offer:
> 1) VACUUM REWRITE, which is like CLUSTER but doesn't use an index, and
Check, although I'm not eager to make REWRITE a fully reserved word,
which is what this would entail. I would propos
Heikki Linnakangas writes:
> For the kicks, I looked at what it would take to write a utility like
> that. It turns out to be quite trivial, patch attached.
I don't think you've really thought this through; particularly not this:
> + rel = heap_open(relid, AccessShareLock);
You can NOT modi
Robert Haas wrote:
> Heikki Linnakangas wrote:
>> Hannu Krosing wrote:
>>> On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
2) Another utility that does something like UPDATE ... WHERE ctid > ? to
> I also wonder whether we should consider teaching regular VACUUM to do
> a little
Robert Haas wrote:
> I think this should be in core, not a contrib module.
+1
> I also wonder whether we should consider teaching regular VACUUM to
> do a little of this every time it's run. Right now, once your table
> gets bloated, it stays bloated forever, until you intervene. Making
>
On Wed, Sep 16, 2009 at 1:42 PM, Josh Berkus wrote:
> On 9/16/09 11:20 AM, Kevin Grittner wrote:
>> Josh Berkus wrote:
>>
>>> a) To date, I have yet to hear a single person bring up an actual
>>> real-life use-case where VACUUM FULL was desireable and REWRITE
>>> would not be.
>>
>> Would rewrite
On Wed, Sep 16, 2009 at 4:53 PM, Heikki Linnakangas
wrote:
> Hannu Krosing wrote:
>> On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
>>> 2) Another utility that does something like UPDATE ... WHERE ctid > ? to
>>> move tuples to lower pages. It will be different from current VACUUM
>>
On Wed, 2009-09-16 at 23:53 +0300, Heikki Linnakangas wrote:
> For the kicks, I looked at what it would take to write a utility like
> that. It turns out to be quite trivial, patch attached. It uses the same
> principle as VACUUM FULL, scans from the end, moving tuples to
> lower-numbered pages unt
On Wed, 2009-09-16 at 23:53 +0300, Heikki Linnakangas wrote:
> Hannu Krosing wrote:
> > On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
> >> 2) Another utility that does something like UPDATE ... WHERE ctid > ? to
> >> move tuples to lower pages. It will be different from current VACUU
Hannu Krosing wrote:
> On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
>> 2) Another utility that does something like UPDATE ... WHERE ctid > ? to
>> move tuples to lower pages. It will be different from current VACUUM
>> FULL in some ways. It won't require a table lock, for example, b
Hannu,
> If it is a sequence of
>
> 1. ordinary VACUUM (it can't run out of FSM anymore, no?)
> 2. a process moving live tuples from end (using reverse seqscan) to free
> space found scanning in first-to-last direction, either one tuple at a
> time or one page at a time, until the two scans meet
On Wed, 2009-09-16 at 11:48 -0700, Josh Berkus wrote:
> Hannu,
>
> > The only case is when you are out of disk space and can't afford to
> > write out a full set of live rows.
>
> Well, it's actually rather specific. You need to have:
>
> a) *Some* free disk space (FULL requires extra disk) but
On Wed, 2009-09-16 at 13:20 -0500, Kevin Grittner wrote:
> Josh Berkus wrote:
>
> > a) To date, I have yet to hear a single person bring up an actual
> > real-life use-case where VACUUM FULL was desireable and REWRITE
> > would not be.
>
> Would rewrite have handled this?:
>
> http://archive
On Wed, 2009-09-16 at 11:10 -0700, Josh Berkus wrote:
> Hackers,
>
> Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
> http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
>
> Of note:
>
> a) To date, I have yet to hear a single person brin
Hannu,
> The only case is when you are out of disk space and can't afford to
> write out a full set of live rows.
Well, it's actually rather specific. You need to have:
a) *Some* free disk space (FULL requires extra disk) but not enough to
copy one entire table and its indexes.
b) be already d
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
> I was just going to post that we should make a decision about this,
> because ISTM there's some code in Simon's hot standby patch that is only
> required to support VACUUM FULL. If we make the decision that we drop
> VACUUM FULL in 8.5
On Wed, 2009-09-16 at 11:10 -0700, Josh Berkus wrote:
> Hackers,
>
> Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
> http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
>
> Of note:
>
> a) To date, I have yet to hear a single person brin
On 9/16/09 11:20 AM, Kevin Grittner wrote:
> Josh Berkus wrote:
>
>> a) To date, I have yet to hear a single person bring up an actual
>> real-life use-case where VACUUM FULL was desireable and REWRITE
>> would not be.
>
> Would rewrite have handled this?:
>
> http://archives.postgresql.org/
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
> 2) Another utility that does something like UPDATE ... WHERE ctid > ? to
> move tuples to lower pages. It will be different from current VACUUM
> FULL in some ways. It won't require a table lock, for example, but it
> won't be able to m
Josh Berkus wrote:
> Hackers,
>
> Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
> http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
>
> Of note:
>
> a) To date, I have yet to hear a single person bring up an actual
> real-life use-case
Josh Berkus wrote:
> a) To date, I have yet to hear a single person bring up an actual
> real-life use-case where VACUUM FULL was desireable and REWRITE
> would not be.
Would rewrite have handled this?:
http://archives.postgresql.org/pgsql-hackers/2009-09/msg01016.php
-Kevin
--
Sent via
Hackers,
Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
Of note:
a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and
On fre, 2009-08-21 at 20:07 -0400, Tom Lane wrote:
> As of SQL99 it's supposed to be legal if you're grouping by a primary key
> (or some other cases where the other columns can be proved functionally
> dependent on the grouping columns, but that's the most useful one).
> We haven't got round to im
Andrew Dunstan writes:
> Jean-Michel Pouré wrote:
>> Why can't PostgreSQL add the required field automatically? Could this be
>> added to PostgreSQL to-do-list?
> Isn't that contrary to the standard?
As of SQL99 it's supposed to be legal if you're grouping by a primary key
(or some other cases w
Greg Stark wrote:
If Postgres changed on this front it would be to support the SQL
Standard concept of "functional dependency". In cases where some
columns are guaranteed to be unique you can leave them out of the
GROUP BY but still use them in the select list. This isn't MySQL's
behaviour of j
On Friday 21 August 2009 04:01:36 pm Andrew Dunstan wrote:
> Jean-Michel Pouré wrote:
> >> BTW, why don't we have a multi-argument version of CONCAT()? In 8.4,
> >> it
> >> would be possible ... I should add it to mysqlcompat library.
> >
> > yes. In PostgreSQL core ...
>
> No. That is exactly wh
Greg Stark writes:
> The first step is probably to do the opposite of what we're talking
> about here: cases where people *have* added extra columns to the GROUP
> BY key so they can use those columns in their select list. We can
> remove those columns from the sort or hash comparison key if there
2009/8/22 Stephen Frost :
>> This is a bit trickier than it looks because it makes the validity of a
>> query dependent on the existence of an appropriate uniqueness
>> constraint; thus for example DROP CONSTRAINT might invalidate a stored
>> rule or view. See prior discussions.
>
> Ah, yes. Coul
* Greg Stark (gsst...@mit.edu) wrote:
> So not many uses of it in MySQL actually *would* be valid if we
> implemented the shortcut. But MySQL doesn't enforce that so it serves
> that purpose as well as what we get out of DISTINCT ON.
That's probably a good thing- if they're valid then we'd probabl
2009/8/22 Stephen Frost :
> * Greg Stark (gsst...@mit.edu) wrote:
>> You would have to specify the key. I think typically you would have
>> something like:
>>
>> SELECT a.*, sum(b.col)
>> FROM a,b
>> GROUP BY a.pk
>
> Ahhh, ok, this makes more sense. This is SQL standard?
Incidentally it make
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost writes:
> > Right. It strikes me as a relativly small amount of work to get the
> > initial "just add the columns to the group by" logic implemented.
>
> Well, no, you *aren't* adding the columns to the GROUP BY. You're just
> not throwing
Stephen Frost writes:
> Right. It strikes me as a relativly small amount of work to get the
> initial "just add the columns to the group by" logic implemented.
Well, no, you *aren't* adding the columns to the GROUP BY. You're just
not throwing the error. You really don't want to add redundant
* Greg Stark (gsst...@mit.edu) wrote:
> You would have to specify the key. I think typically you would have
> something like:
>
> SELECT a.*, sum(b.col)
>FROM a,b
> GROUP BY a.pk
Ahhh, ok, this makes more sense. This is SQL standard? Do we have a
TODO for it?
> The database knows that it
2009/8/22 Stephen Frost :
> Hrmm. That sounds kinda neat, but you'd still have to specify one of
> the columns in the GROUP BY, I presume? Or could you just say 'GROUP
> BY' without any columns, and have it GROUP BY the key of the table
> you're using?
You would have to specify the key. I think
* Greg Stark (gsst...@mit.edu) wrote:
> select distinct on (a) a,b,c from a ORDER BY a,b,c
>
> But Postgres insists you have an ORDER BY which has to agree with the
> DISTINCT ON columns and provide some extra column(s) to determine
> which values of b,c are chosen.
Not quite technically correct.
2009/8/21 Jean-Michel Pouré :
> PostgreSQL requires all non-aggregated fields to be present in the GROUP
> BY clause (I fixed 10 such issues in Drupal code).
> http://drupal.org/node/30
>
> Why can't PostgreSQL add the required field automatically? Could this be
> added to PostgreSQL to-do-list
Jean-Michel Pouré wrote:
BTW, why don't we have a multi-argument version of CONCAT()? In 8.4,
it
would be possible ... I should add it to mysqlcompat library.
yes. In PostgreSQL core ...
No. That is exactly where it shouldn't go. And frankly, Drupal
developers should stop writ
On 8/21/09 3:17 PM, Tom Lane wrote:
> David Fetter writes:
>> On Fri, Aug 21, 2009 at 04:19:43PM -0400, Tom Lane wrote:
>>> Josh Berkus writes:
BTW, why don't we have a multi-argument version of CONCAT()?
>>> Why wouldn't people use the SQL-standard || operator instead?
>
>> Because by defa
David Fetter writes:
> On Fri, Aug 21, 2009 at 04:19:43PM -0400, Tom Lane wrote:
>> Josh Berkus writes:
>>> BTW, why don't we have a multi-argument version of CONCAT()?
>>
>> Why wouldn't people use the SQL-standard || operator instead?
> Because by default, MySQL uses that as, get this, "logic
On Fri, Aug 21, 2009 at 04:19:43PM -0400, Tom Lane wrote:
> Josh Berkus writes:
> > BTW, why don't we have a multi-argument version of CONCAT()?
>
> Why wouldn't people use the SQL-standard || operator instead?
Because by default, MySQL uses that as, get this, "logical OR."
Cheers,
David (grate
> I've registered for the Drupal site so that I can fix and/or expand
> some
> of your items.
Thanks. I corrected the index on dual fields page.
> If you want to discuss Drupal & PostgreSQL again, please post on the
> pgsql-advocacy list or the pgsql-php mailing lists. pgsql-hackers
> isn't
> th
Josh Berkus writes:
> BTW, why don't we have a multi-argument version of CONCAT()?
Why wouldn't people use the SQL-standard || operator instead?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
1 - 100 of 123 matches
Mail list logo
