On Wed, 2006-05-24 at 00:15, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > On Tue, May 23, 2006 at 10:19:25AM +0200, Csaba Nagy wrote:
> > > On Mon, 2006-05-22 at 23:55, Jim C. Nasby wrote:
> > > > BTW, there's a bug/issue with CLUSTER that makes it not entirely
> > > > transaction safe.
> > >
>
> > would be nice to have an "exclude-table" option on it. I actually
> > started working on a patch to allow that, I will make it just good
> > enough for my purpose (very poor C skills here). Would that be
> > interesting for others ?
>
> Well, being able to have finer control over what you're d
- Original Message
>From: Jim C. Nasby <[EMAIL PROTECTED]>
>To: Shelby Cain <[EMAIL PROTECTED]>
>Cc: Csaba Nagy <[EMAIL PROTECTED]>; [EMAIL PROTECTED]; Postgres >general
>mailing list ; [EMAIL PROTECTED]
>Sent: Tuesday, May 23, 2006 5:19:58 PM
&g
On Mon, May 22, 2006 at 03:49:01PM -0700, Shelby Cain wrote:
> My experience with job queues comes from clients that mostly use Oracle as
> the backend. However, even with Oracle a queue table should be storing
> information about a job and not have records unnecessarily locked simply
> becaus
Jim C. Nasby wrote:
> On Tue, May 23, 2006 at 10:19:25AM +0200, Csaba Nagy wrote:
> > On Mon, 2006-05-22 at 23:55, Jim C. Nasby wrote:
> > > BTW, there's a bug/issue with CLUSTER that makes it not entirely
> > > transaction safe.
> >
> > For God's sake, don't fix that one ! I rely on it... now ser
On Tue, May 23, 2006 at 10:19:25AM +0200, Csaba Nagy wrote:
> On Mon, 2006-05-22 at 23:55, Jim C. Nasby wrote:
> > BTW, there's a bug/issue with CLUSTER that makes it not entirely
> > transaction safe.
>
> For God's sake, don't fix that one ! I rely on it... now seriously,
> until there's a way to
On Tue, May 23, 2006 at 10:30:35AM +0200, Csaba Nagy wrote:
> On Tue, 2006-05-23 at 00:04, Jim C. Nasby wrote:
> > I can't imagine how bad this would be if the database actually had
> > hour-long reports that had to run... and luckily the system is quiet at
> > night when pg_dump runs.
>
> BTW, pg
On Tue, 2006-05-23 at 00:04, Jim C. Nasby wrote:
> I can't imagine how bad this would be if the database actually had
> hour-long reports that had to run... and luckily the system is quiet at
> night when pg_dump runs.
BTW, pg_dump is the only thing that stays in my way with the CLUSTER
strategy a
On Mon, 2006-05-22 at 23:55, Jim C. Nasby wrote:
> BTW, there's a bug/issue with CLUSTER that makes it not entirely
> transaction safe.
For God's sake, don't fix that one ! I rely on it... now seriously,
until there's a way to tell the DB that an exclusive lock is enough to
vacuum all dead rows, e
- Original Message
>From: Jim C. Nasby <[EMAIL PROTECTED]>
>To: Shelby Cain <[EMAIL PROTECTED]>
>Cc: Csaba Nagy <[EMAIL PROTECTED]>; [EMAIL PROTECTED]; >Postgres general
>mailing list ; >[EMAIL PROTECTED]
>Sent: Monday, May 22, 2006 5:04:51 PM
> >Sent: Friday, May 19, 2006 11:46:42 AM
> >Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE
> >
> >Well, sometimes it's not that easy. How would you handle a batch
> >processing system which stores the incoming requests in a queue table in
> >the data bas
On Fri, May 19, 2006 at 05:19:37PM +0200, Csaba Nagy wrote:
> > Since Postgresql's MVCC system doesn't require rollback segments I guess
> > the performance question is the more important of the two. If there could
> > be a performance gain in Postgresql with "delete from X where Y limit Z" vs
- Original Message
>From: Csaba Nagy <[EMAIL PROTECTED]>
>To: Shelby Cain <[EMAIL PROTECTED]>
>Cc: [EMAIL PROTECTED]; Postgres general mailing list
>>; [EMAIL PROTECTED]
>Sent: Friday, May 19, 2006 11:46:42 AM
>Subject: Re: [GENERAL] allow LIMIT in UPD
Martijn van Oosterhout writes:
> Err, you don't need an index on ctid because the ctid represents that
> physical location of the tuple on disk. ctids are what indexes use to
> refer to tuples...
> # explain DELETE FROM t WHERE ctid=(SELECT ctid FROM t WHERE pronargs=1 LIMIT
> 1);
This doesn't
> >Personally, I have never wanted a DELETE or UPDATE with LIMIT. The one
> >time I did something similar in Oracle, I used partitions, and just dropped
> >or truncated the partition containing the old data.
> >
>
> Yeah, that’s the proper way to handle the issue assuming that sufficient
> foret
>- Original Message
>From: [EMAIL PROTECTED]
>To: Csaba Nagy <[EMAIL PROTECTED]>
>Cc: Postgres general mailing list ; >[EMAIL
>PROTECTED]
>Sent: Friday, May 19, 2006 10:43:43 AM
>Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE
>
>Personally, I
On Fri, 2006-05-19 at 17:43, [EMAIL PROTECTED] wrote:
> Regarding Oracle's ROWNUM - since they have to use that instead of OFFSET
> and LIMIT, that isn't much of an argument for the Oracle way. When
> converting queries into Oracle SQL, I always _really_ miss OFFSET and
> LIMIT. They are much eas
<[EMAIL PROTECTED]> To: Postgres general
mailing list
Sent by: cc:
Subject: Re: [GENER
> Err, you don't need an index on ctid because the ctid represents that
> physical location of the tuple on disk. ctids are what indexes use to
> refer to tuples...
OK, then how you explain this:
db=# prepare test_001(bigint, bigint, smallint) as
db-# DELETE FROM big_table
db-# WHERE ctid IN
db-#
On Fri, May 19, 2006 at 04:22:42PM +0200, Csaba Nagy wrote:
> "Then use ctid."
>
> For the problem at hand in your post it is a good solution, except
> that it will cause a full table scan cause I guess few people have
> indexes on ctid. Or you have to write your queries really contrived,
> by dup
> Now the question... why would you do that instead of doing everything in one
> big transaction on Oracle? I guess performance is one reason. Oracle's (at
> least with 8/8i) performance seems to tank very quickly on deletes as you
> increase the number of records you delete in a single transa
>- Original Message
>From: Tom Lane <[EMAIL PROTECTED]>
>To: Csaba Nagy <[EMAIL PROTECTED]>
>Cc: Postgres general mailing list
>Sent: Friday, May 19, 2006 9:31:24 AM
>Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE
>
>You can't possi
On Fri, 2006-05-19 at 16:31, Tom Lane wrote:
> Csaba Nagy <[EMAIL PROTECTED]> writes:
> > I DO NOT CARE about which rows are deleted.
>
> You can't possibly think that that holds true in general.
I agree that it is not true in the general case, but then I also don't
want to use DELETE with LIMIT
Csaba Nagy <[EMAIL PROTECTED]> writes:
> I DO NOT CARE about which rows are deleted.
You can't possibly think that that holds true in general.
> The fact that it is
> nondeterministic can be very clearly specified in the documentation if
> you think it is such a bad thing, but nondeterministic is
On Fri, 2006-05-19 at 15:51, Tom Lane wrote:
> Csaba Nagy <[EMAIL PROTECTED]> writes:
> > Currently the LIMIT clause is not allowed in UPDATE or DELETE
> > statements. I wonder how easy it would be to allow it, and what people
> > think about it ?
>
> This has been proposed before, and rejected be
Csaba Nagy <[EMAIL PROTECTED]> writes:
> Currently the LIMIT clause is not allowed in UPDATE or DELETE
> statements. I wonder how easy it would be to allow it, and what people
> think about it ?
This has been proposed before, and rejected before, and the arguments
against are just as strong as the
> That would spare a HashAggregate and an index scan. The index scan would
> very likely not be a problem, as the same index entries are visited in
> the subquery and likely are cached, and the HashAggregate should be also
> fast for the max 1000 rows it has to handle, but they are still
> complete
> -- sample data
> CREATE TEMP TABLE tab (id serial primary key, n int, t text);
> INSERT INTO tab(n) SELECT * FROM generate_series(1,1000);
> -- say, you want such an update:
> UPDATE tab SET t = 'aqq' WHERE n > 10;
> -- but with limit:
> UPDATE tab SET t = 'aqq' WHERE id in (SELECT id FROM tab WH
> I asked that question a while ago..
>
> http://archives.postgresql.org/pgsql-general/2005-04/msg00386.php
>
> and got this response:
>
> http://archives.postgresql.org/pgsql-general/2005-04/msg00387.php
>
> Works quite well :)
I don't doubt that it works well, in fact that's what I plan to u
On 5/19/06, Csaba Nagy <[EMAIL PROTECTED]> wrote:
Hi all,
Currently the LIMIT clause is not allowed in UPDATE or DELETE
statements. I wonder how easy it would be to allow it, and what people
think about it ? For our application it would help a lot when processing
things chunk-wise to avoid long
On 5/19/06, Csaba Nagy <[EMAIL PROTECTED]> wrote:
Hi all,
Currently the LIMIT clause is not allowed in UPDATE or DELETE
statements. I wonder how easy it would be to allow it, and what people
think about it ? For our application it would help a lot when processing
things chunk-wise to avoid long
31 matches
Mail list logo
