Shridhar Daithankar <[EMAIL PROTECTED]> writes:
> This is not a bug. It is just that people find it confusing when
> postgresql planner consider seemingly same type as different.
It certainly is a bug, or at least a deficiency: PostgreSQL planner
*could* use the index to process the query, but the
Ivar Zarans <[EMAIL PROTECTED]> writes:
> > qry = "UPDATE table1 SET status = %s WHERE recid = '%s'"
> > cursor.execute(qry, status, recid)
>
> Yes, this helps. But then it sort of obsoletes PyPgSQL-s own quoting
> logic. I would prefer to take care of this all by myself or trust some
> underlyin
Ivar Zarans wrote:
On Fri, Dec 05, 2003 at 01:23:43PM +, Richard Huxton wrote:
Better IMHO would be: "UPDATE table1 SET status = %s WHERE recid = %s::int8"
Thanks for the hint!
Which makes the wrapper class need:
def __str__( self ):
I just spent 2 days tracking this error down in my own code, actually.
What I wound up doing is having the two places where I generate the
queries (everything in my system goes through those two points, as I'm
using a middleware layer) check values used as identifying fields for
the presence o
On Fri, Dec 05, 2003 at 07:21:38PM +0530, Shridhar Daithankar wrote:
> planner consider seemingly same type as different. e.g. treating int8 as
> different than int4. Obvious thinking is they should be same. But given
> postgresql's flexibility with create type, it is difficult to promote.
OK,
On Fri, Dec 05, 2003 at 01:23:43PM +, Richard Huxton wrote:
> Better IMHO would be: "UPDATE table1 SET status = %s WHERE recid = %s::int8"
Thanks for the hint!
> unexpectedly in this scenario. The reason is that the literal number is
> treated as int4, whereas quoted it is marked as type un
Ivar Zarans wrote:
On Fri, Dec 05, 2003 at 06:19:46PM +0530, Shridhar Daithankar wrote:
is correct SQL, but not correct, considering PostgreSQL bugs.
Personally I don't consider a bug but anyways.. You are the one facing
problem so I understand..
Well, if this is not bug, then what is considerat
On Friday 05 December 2003 12:49, Shridhar Daithankar wrote:
> Ivar Zarans wrote:
> > It seems, that PyPgSQL query quoting is not aware of this performance
> > problem (to which Cristopher referred) and final query, sent to server
> > is correct SQL, but not correct, considering PostgreSQL bugs.
>
On Fri, Dec 05, 2003 at 06:19:46PM +0530, Shridhar Daithankar wrote:
> >is correct SQL, but not correct, considering PostgreSQL bugs.
>
> Personally I don't consider a bug but anyways.. You are the one facing
> problem so I understand..
Well, if this is not bug, then what is consideration behin
Ivar Zarans wrote:
It seems, that PyPgSQL query quoting is not aware of this performance
problem (to which Cristopher referred) and final query, sent to server
is correct SQL, but not correct, considering PostgreSQL bugs.
Personally I don't consider a bug but anyways.. You are the one facing proble
On Fri, Dec 05, 2003 at 10:08:20AM +, Richard Huxton wrote:
> > numeric constant must be typecasted in order to function properly.
> >
> > Is this normal behaviour of fields with bigint type?
>
> As Christopher says, normal (albeit irritating). Not sure it applies here -
> all the examples y
On Friday 05 December 2003 02:07, Ivar Zarans wrote:
> I have played around with explain and explain analyze and noticed one
> interesting oddity:
[snip]
> Why first example, where recid is given as numeric constant, is using
> sequential scan, but second example, where recid is given as string
> c
Why first example, where recid is given as numeric constant, is using
sequential scan, but second example, where recid is given as string
constant works with index scan, as expected? Third example shows, that
numeric constant must be typecasted in order to function properly.
Is this normal behaviou
I have played around with explain and explain analyze and noticed one
interesting oddity:
===
explain UPDATE table1 SET status = 'SKIP' WHERE recid = 196641;
Seq Scan on table1 (cost=0.00..16709.97 rows=1 width=199)
Filter: (recid = 196641)
===
explain UPDATE table1 SET status = 'SKIP' WHER
On Thu, Dec 04, 2003 at 10:45:21PM +, Richard Huxton wrote:
> If you've got the time, try putting together a small test-script with some
> dummy data and see if it's reproducible. I'm sure the other Python users
> would be interested in seeing where the problem is.
Tried with test-script, b
On Thursday 04 December 2003 22:13, Ivar Zarans wrote:
> On Thu, Dec 04, 2003 at 08:23:36PM +, Richard Huxton wrote:
> > Ah - it's probably not the update but the IN. You can rewrite it using
> > PG's non-standard FROM:
> >
> > UPDATE t1 SET status='done' FROM t_tmp WHERE t1.rec_id = t_tmp.rec_
On Thursday 04 December 2003 19:59, William Yu wrote:
> Ivar Zarans wrote:
> > I am experiencing strange behaviour, where simple UPDATE of one field is
> > very slow, compared to INSERT into table with multiple indexes. I have
> > two tables - one with raw data records (about 24000), where one fiel
On Thu, Dec 04, 2003 at 08:23:36PM +, Richard Huxton wrote:
> Ah - it's probably not the update but the IN. You can rewrite it using PG's
> non-standard FROM:
>
> UPDATE t1 SET status='done' FROM t_tmp WHERE t1.rec_id = t_tmp.rec_id;
This was one *very useful* hint! Using this method i got
On Thursday 04 December 2003 19:51, Ivar Zarans wrote:
>
> My second tests were done with temporary table and update query as:
> "UPDATE table1 SET Status = 'done' WHERE recid IN (SELECT recid FROM
> temptable)". It is still slower than INSERT, but more or less
> acceptable. Compared to my first te
Ivar Zarans wrote:
I am experiencing strange behaviour, where simple UPDATE of one field is
very slow, compared to INSERT into table with multiple indexes. I have
two tables - one with raw data records (about 24000), where one field
In Postgres and any other DB that uses MVCC (multi-version concurr
On Thu, Dec 04, 2003 at 08:23:36PM +, Richard Huxton wrote:
> Ah - it's probably not the update but the IN. You can rewrite it using PG's
> non-standard FROM:
>
> UPDATE t1 SET status='done' FROM t_tmp WHERE t1.rec_id = t_tmp.rec_id;
Thanks for the hint. I'll try this.
> Now that doesn't e
On Thu, Dec 04, 2003 at 02:23:20PM -0500, Jeff wrote:
> > Most interesting is, that insert takes 0.004 seconds in average, but
> > update takes 0.255 seconds in average. Processing of 24000 records
> > took around 1 hour 20 minutes.
>
> Do you have an index on recid?
Yes, this is primary key of
On Thu, 4 Dec 2003 20:57:51 +0200
Ivar Zarans <[EMAIL PROTECTED]> wrote:
.
> table1 is updated with new value (done). Update statement itself is
> extremely simple: "update table1 set status = 'done' where recid =
> ..."
>
> Most interesting is, that insert takes 0.004 seconds in average, but
> u
23 matches
Mail list logo
