sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] overriding new and calling the original method doesn't work

[Ian B. <ia...@co...>]

On Dec 15, 2003, at 5:30 PM, Guenther Starnberger wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Monday 15 December 2003 20:13, Ian Bicking wrote:
>
> hi,
>
>> Yes, it seems like classmethods are a little funny.  I think I've
>> gotten it to work with super(MyClass).new(**kw)
>
> somehow this doesn't work (with python 2.2.3 - i haven't tried it with 
> python
> 2.3 yet). AttributeError: 'super' object has no attribute 'new'.

maybe:

def new(cls, **kw):
     yada yada
     return super(MyClass, cls).new(**kw)
new = classmethod(new0

?

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] overriding new and calling the original method doesn't work

[Guenther S. <gs...@sy...>]

=2D----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Monday 15 December 2003 20:13, Ian Bicking wrote:

hi,

> Yes, it seems like classmethods are a little funny.  I think I've
> gotten it to work with super(MyClass).new(**kw)

somehow this doesn't work (with python 2.2.3 - i haven't tried it with pyth=
on=20
2.3 yet). AttributeError: 'super' object has no attribute 'new'.

cu
/gst
=2D----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/3kP+ZtF7I/+gjcERAkUhAJ9Lk+oMSx1vat44kgKTZMYK9GoRRACg+aMT
P3IdtxnZ16aL9plG5ZKk6HE=3D
=3Dl85a
=2D----END PGP SIGNATURE-----

Re: [SQLObject] overriding new and calling the original method doesn't work

[Ian B. <ia...@co...>]

On Dec 15, 2003, at 4:26 AM, Guenther Starnberger wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> hi,
>
> some time ago there was a thread on this mailinglist, about overiding 
> the
> new() method in classes inherited from SQLObject.
> (http://thread.gmane.org/gmane.comp.python.sqlobject/906)
>
> according to a posting in this thread it is possible to call the 
> 'original'
> SQLObject new() method by: 'SQLObject.new(cls, **kw)'.
>
> but somehow this doesn't work for me :/
>
> class Account(SQLObject):
> __name = StringCol(alternateID = True)
> __password = StringCol()
>
> __def new(cls, **kw):
> ____print "FOO"
> ____SQLObject.new(cls, **kw)
>
> __new = classmethod(new)
>
> calling Account.new just results in the following exception when 
> SQLObject.new
> gets called:
>
> TypeError: new() takes exactly 1 non-keyword argument (2 given)
>
> i tried some variantions of calling SQLObject.new (e.g. without cls, 
> ...) but
> they didn't work.
>
> any ideas how it is possible to call SQLObject.new?

Yes, it seems like classmethods are a little funny.  I think I've 
gotten it to work with super(MyClass).new(**kw)


--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] boolean types & python 2.2.x

[Ian B. <ia...@co...>]

On Dec 6, 2003, at 3:55 PM, Greg Brown wrote:
> I've found a minor problem using boolean columns on Pythons that don't 
> have a builtin boolean type:
>
>   File "SQLObject/DBConnection.py", line 72, in _runWithConnection
>     val = meth(conn, *args)
>   File "SQLObject/DBConnection.py", line 457, in _queryInsertID
>     q = self._insertSQL(table, names, values)
>   File "SQLObject/DBConnection.py", line 154, in _insertSQL
>     ', '.join([self.sqlrepr(v) for v in values])))
>   File "SQLObject/DBConnection.py", line 365, in sqlrepr
>     return sqlrepr(v, self.dbName)
>   File "SQLObject/Converters.py", line 168, in sqlrepr
>     raise ValueError, "Unknown SQL builtin type: %s for %s" % \
> ValueError: Unknown SQL builtin type: <type 'instance'> for TRUE
>
> For Python 2.2, a BOOL class is being defined to simulate the 
> important behavior of 2.3's builtin 'bool' type.  The problem has to 
> do with how the BOOL is being added to the ConverterRegistry.  It's 
> being registered as:
>
> registerConverter(type(TRUE), BoolConverter)
>
> ...which is how the rest of the converters are being registered in 
> Converters.py.  This doesn't work because all the others are new-style 
> classes that can be discriminated by type(x) while BOOL is an 
> old-style class, and the type() of all old-style classes is 
> 'instance'.  Old-style classes are being correctly registered with the 
> ConverterRegistry in SQLBuilder.py, with only the Class object being 
> passed into registerConverter() instead of the type() of the class.
>
> It seems like the cleanest fix is to make BOOL a new-style class by 
> changing it's definition to:
>
> class BOOL(object):
>
> so that it can be registered in the same way as all other types in the 
> Converters.py module regardless of whether it's on Python 2.2 or 2.3.
>
> This is with SQLObject 0.5.1, by the way.

Yes, it sounds like it's an oversight, and I couldn't test it because I 
don't have any version of Python 2.2 where True and False aren't their 
own objects.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] overriding new and calling the original method doesn't work

[Guenther S. <gs...@sy...>]

=2D----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

hi,

some time ago there was a thread on this mailinglist, about overiding the=20
new() method in classes inherited from SQLObject.
(http://thread.gmane.org/gmane.comp.python.sqlobject/906)

according to a posting in this thread it is possible to call the 'original'=
=20
SQLObject new() method by: 'SQLObject.new(cls, **kw)'.

but somehow this doesn't work for me :/

class Account(SQLObject):
__name =3D StringCol(alternateID =3D True)
__password =3D StringCol()

__def new(cls, **kw):
____print "FOO"
____SQLObject.new(cls, **kw)

__new =3D classmethod(new)

calling Account.new just results in the following exception when SQLObject.=
new=20
gets called:

TypeError: new() takes exactly 1 non-keyword argument (2 given)

i tried some variantions of calling SQLObject.new (e.g. without cls, ...) b=
ut=20
they didn't work.

any ideas how it is possible to call SQLObject.new?

(i need to override new, because i want to crypt the password before storin=
g=20
it. part of the username is used as salt by the algorithm which i have to u=
se=20
(because it is used by the XUF zope user folder), so i need to know both=20
values at the same time to crypt the password).

cu
/gst
=2D----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/3YxtZtF7I/+gjcERAmZVAJsHV7pCo81EfTNOdy3p9OcZfCCqMQCfXOvJ
HthMCSGEiQLLilQa1Om2rNE=3D
=3DkX5h
=2D----END PGP SIGNATURE-----

[SQLObject] newbie-question examples

[Manfred N. <Man...@mn...>]

Hi all,

little trouble with examples:

My hope was :
set config.py with correct conn=bla...
and all examples run.

personaddress.py do it right
people.py doesn't !!

Is this only because the programms have
a different design
or is there something I missunderstood ??

Thanks for help
Manfred

[SQLObject] Patch: Database Errors not Releasing Pooled Connections

[Ken K. <ke...@ke...>]

I experienced trouble where with SQLObject, if something you do raises a
MySQL error (such as a duplicate key on insertion), SQLObject does not
ever free the database connection used when that error was raised.
Eventually this will lead to a "too many connections" error from MySQLdb.

I searched google for SQLObject and "too many connections" (the error
you'll eventually get) and it didn't turn up anything so I assume I'm the
first one to get this error.

Upon exploring the source code, I think I have isolated the problem. Your
DBAPI.runWithConnection method from SQLObject/DBConnection.py has the
following definition:


    def _runWithConnection(self, meth, *args):
        conn = self.getConnection()
        val = meth(conn, *args)
        self.releaseConnection(conn)
        return val

In the event that meth(...) raises an error, releaseConnection is never
called. To allow the exception to be raised like it is now but the
database connection released regardless, I suggest:


    def _runWithConnection(self, meth, *args):
        conn = self.getConnection()
        try:
            val = meth(conn, *args)
        finally:
            self.releaseConnection(conn)
        return val

I've attached a patch which applies the above change. Do with it as you may.

Thanks
Ken

Re: [SQLObject] Instanciating objects as strings giving different results from ints

[Ian B. <ia...@co...>]

On Dec 11, 2003, at 5:26 PM, Ken Kinder wrote:
> I'm having a bit of confusion here. It seems that if you instanciate a
> SQLObject with a string, and another with an int, even of those are the
> same row, they are not evaluated as being the same object. Example 
> code:
>
>>>> class MyClass(SQLObject):
> ...     field = StringCol(length=20)
> ...
>>>> MyClass.createTable()
>>>> MyClass.new(field='spam')
> <MyClass 1 field='spam'>
>>>> MyClass.new(field='eggs')
> <MyClass 2 field='eggs'>
>>>> spam = MyClass(1)
>>>> spam2 = MyClass('1')
>>>> spam2 is spam
> False
>
> Shouldn't SQLObject know to cast the id to an int?

It could, except now you can have non-int IDs too, and we haven't added 
a way to declare which one you are using.

Anyway, for the moment try:

class MyClass(SQLObject):
     def _init(self, id, connection=None):
         SQLObject._init(self, int(id), connection=connection)

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] Instanciating objects as strings giving different results from ints

[Sidnei da S. <si...@aw...>]

On Thu, Dec 11, 2003 at 05:26:56PM -0600, Ken Kinder wrote:
| I'm having a bit of confusion here. It seems that if you instanciate a
| SQLObject with a string, and another with an int, even of those are the
| same row, they are not evaluated as being the same object. Example code:
| 
| >>> class MyClass(SQLObject):
| ...     field = StringCol(length=20)
| ...
| >>> MyClass.createTable()
| >>> MyClass.new(field='spam')
| <MyClass 1 field='spam'>
| >>> MyClass.new(field='eggs')
| <MyClass 2 field='eggs'>
| >>> spam = MyClass(1)
| >>> spam2 = MyClass('1')
| >>> spam2 is spam
| False
| 
| Shouldn't SQLObject know to cast the id to an int?

This has been asked in the list around two months ago. Search the archives.

-- 
Sidnei da Silva <si...@aw...>
http://awkly.org - dreamcatching :: making your dreams come true
http://plone.org/about/team#dreamcatcher

All language designers are arrogant. Goes with the territory...
	-- Larry Wall

[SQLObject] Instanciating objects as strings giving different results from ints

[Ken K. <ke...@ke...>]

I'm having a bit of confusion here. It seems that if you instanciate a
SQLObject with a string, and another with an int, even of those are the
same row, they are not evaluated as being the same object. Example code:

>>> class MyClass(SQLObject):
...     field = StringCol(length=20)
...
>>> MyClass.createTable()
>>> MyClass.new(field='spam')
<MyClass 1 field='spam'>
>>> MyClass.new(field='eggs')
<MyClass 2 field='eggs'>
>>> spam = MyClass(1)
>>> spam2 = MyClass('1')
>>> spam2 is spam
False

Shouldn't SQLObject know to cast the id to an int?

Thanks
Ken

Re: [SQLObject] Forcing Cache Refresh

[Andrew B. <an...@ex...>]

On Wednesday 03 December 2003 11:34 am, you wrote:
> On Dec 3, 2003, at 9:03 AM, Andrew Barilla wrote:
> > I'm a fairly new user to SQLObject and Webware but I've got my new
> > site up and
> > running.  The only thing is that I have set _cacheValues = False in my
> > SQLObjects so that if I update data, it's reflected on the view-only
> > part of
> > the website.  Is this the only way to do this or can I force a refresh
> > of the
> > cache?  I looked through the mailing list archives but couldn't find
> > anything
> > definite on this topic.
>
> You can force a cache refresh on any instance by calling .refresh(), or
> .expire() to cause a refresh sometime in the future (when the instance
> is used again).
>
> When you want to expire a whole batch, you'll just run through the
> objects and do this.  DBConnection.Transaction.rollback does this, as a
> model.
>
> --
> Ian Bicking | ia...@co... | http://blog.ianbicking.org

The DBConnection.Transaction.rollback code pointed me in the right direction 
and with some code I found in the mailing list archives, I modified the 
SQLObject class to include this method.

    def expireAll(cls):
        Caches = cls._connection.cache.allSubCaches()
        subCaches = [(sub, sub.allIDs()) for sub in Caches]
        for subCache, ids in subCaches:
            for id in ids:
                inst = subCache.tryGet(id)
                if inst is not None:
                    inst.expire()

    expireAll = classmethod(expireAll)

It's working for everything except one of my objects that's used to fill a 
FormKit dropdown box.  That must be cached somewhere else, but it's not that 
big of a deal.

Thanks for your help,
Andy

[SQLObject] boolean types & python 2.2.x

[Greg B. <ge...@po...>]

I've found a minor problem using boolean columns on Pythons that don't 
have a builtin boolean type:

   File "SQLObject/DBConnection.py", line 72, in _runWithConnection
     val = meth(conn, *args)
   File "SQLObject/DBConnection.py", line 457, in _queryInsertID
     q = self._insertSQL(table, names, values)
   File "SQLObject/DBConnection.py", line 154, in _insertSQL
     ', '.join([self.sqlrepr(v) for v in values])))
   File "SQLObject/DBConnection.py", line 365, in sqlrepr
     return sqlrepr(v, self.dbName)
   File "SQLObject/Converters.py", line 168, in sqlrepr
     raise ValueError, "Unknown SQL builtin type: %s for %s" % \
ValueError: Unknown SQL builtin type: <type 'instance'> for TRUE

For Python 2.2, a BOOL class is being defined to simulate the important 
behavior of 2.3's builtin 'bool' type.  The problem has to do with how 
the BOOL is being added to the ConverterRegistry.  It's being 
registered as:

registerConverter(type(TRUE), BoolConverter)

...which is how the rest of the converters are being registered in 
Converters.py.  This doesn't work because all the others are new-style 
classes that can be discriminated by type(x) while BOOL is an old-style 
class, and the type() of all old-style classes is 'instance'.  
Old-style classes are being correctly registered with the 
ConverterRegistry in SQLBuilder.py, with only the Class object being 
passed into registerConverter() instead of the type() of the class.

It seems like the cleanest fix is to make BOOL a new-style class by 
changing it's definition to:

class BOOL(object):

so that it can be registered in the same way as all other types in the 
Converters.py module regardless of whether it's on Python 2.2 or 2.3.

This is with SQLObject 0.5.1, by the way.

- greg

Re: [SQLObject] SQLObject and mysql 4.0+ problem...

[Oisin M. <oi...@en...>]

Hi,

Does anyone use mysql4 and sqlobject together at all?

Should I just stick with mysql3+sqlobject?

om

-- 
Oisin Mulvihill
Engines Of Creation

Email: oi...@en...
Work: +353 1 6791602
Mobile: +353 868191540

Re: [SQLObject] PostgreSQL: costly count

[Frank B. <fb...@fo...>]

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> My impression is that this is an internal value kept to do query 
> optimization (since table size effects that considerably).  It may not 
> be entirely accurate, and doesn't get updated immediately.  It can't be 
> used with any query.

Yes, you're right. A pity. ;)

> If you are doing some sort of paging, you might simply want to cache 
> the results of the count.  Also, if you are doing paging with ordering 
> (which you probably want), then paging and slicing will only save you 
> from fetching and constructing all the objects, but the database still 
> has to look at all the rows (because it can't sort them until it does 
> that).  So it might be faster to pull and cache the full results once, 
> on the assumption that the user will be looking at later results 
> eventually.

Thanks for this tip. I did something similar now for my shop
(normalmailorder.de). As I translate from URLs to SQL queries in a
constant way and as the product DB is never written partly, but always
changed by a single CSV file import as a whole once per week, I can be
sure, to always get the same count depending on the HTTP-request URL.
So I now introduced a simple CountCache table, that has two fields,
"url" and "count". If there's no CountCache.byUrl(some_url), the count
results are fetched from the DB and written to the CountCache. If the
URL already is in the Cache, this one gets used. On CSV-import the
table is emptied. I could also rebuild it from the URLs used so far,
maybe I'll do that later.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Re: Importing classes from seperate files

[Luke O. <lu...@me...>]

>
> When I run the test01.py app I get the AttributeError: 'NoneType'  as I
> listed in a previous post.  My best guess is the __connection__ variable
> isn't available when the class is declared.  I've tested this by
> inserting the following code into both files:

Yep, absolutely right. A good portion of SQLObject's work is done at
declaration time (or really, import time).

your __connection__ code isn't working because SQLObject only looks for it in
the module's namespace (in this case, in the _ProcReport file), not in current
global namespace.

several possibilities to fix this, one simple one assuming your goal is to have
one central place with __connection__ for a group of objects, is to put the
__connection__ in a separate file, and import it in the SQLObject-derived
modules:

dsn.py:

connection = MySQLConnection....

_ProcReport.py:

from dsn import connection
__connection__ = connection
(or maybe)
from dsn import connection as __connection__


And now it exists at module import time, within the _ProcReport module
namespace.

If you have a more specific thing you're trying to solve by having a separate
connection definition, let us know.

- Luke

PS, all code above is non-tested, although I do almost exactly the same thing
except use the per-class "_connection" attribute.)

Re: [SQLObject] PostgreSQL: costly count

[Ian B. <ia...@co...>]

On Dec 4, 2003, at 4:05 PM, Frank Barknecht wrote:
> Hallo,
> Frank Barknecht hat gesagt: // Frank Barknecht wrote:
>
>> Now, how to put this to use?
>
> And related: This does not count the results from where-claused
> queries like: "SELECT COUNT(*) FROM normal WHERE artist LIKE
> '%Britney%';", or does it (somehow)?

My impression is that this is an internal value kept to do query 
optimization (since table size effects that considerably).  It may not 
be entirely accurate, and doesn't get updated immediately.  It can't be 
used with any query.

If you want a query to be fast, you may want to use full-text search, 
or otherwise partition your table more, e.g., an artist table, and you 
do something like "SELECT COUNT(normal.id) FROM normal, artist WHERE 
normal.artist_id = artist.id AND (artist.first_name = 'Britney' OR 
artist.last_name = 'Britney')".  Everything in that query can be 
indexed, and so it will be quite fast (and more importantly, scale to 
large databases).  LIKE doesn't scale well (but full text scales 
better).

If you are doing some sort of paging, you might simply want to cache 
the results of the count.  Also, if you are doing paging with ordering 
(which you probably want), then paging and slicing will only save you 
from fetching and constructing all the objects, but the database still 
has to look at all the rows (because it can't sort them until it does 
that).  So it might be faster to pull and cache the full results once, 
on the assumption that the user will be looking at later results 
eventually.

Hmm... a useful feature would be to implement equality and hashing for 
query objects (which are immutable).   Then you could construct a 
query, and use that as a dictionary/cache key for the select results.  
That might be too difficult, though, because AND(a, b) == AND(b, a), 
but those logic relationships are hard to find.  You can always get the 
query's string/SQL representation, and cache based on that.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] PostgreSQL: costly count

[Frank B. <fb...@fo...>]

Hallo,
Frank Barknecht hat gesagt: // Frank Barknecht wrote:

> Now, how to put this to use?

And related: This does not count the results from where-claused
queries like: "SELECT COUNT(*) FROM normal WHERE artist LIKE
'%Britney%';", or does it (somehow)?

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] PostgreSQL: costly count

[Frank B. <fb...@fo...>]

Hallo,
Sidnei da Silva hat gesagt: // Sidnei da Silva wrote:

> | >>>
> | example:
> | On our message boards each post is a row.  The powers that be like to 
> | know
> | how many posts there are total (In addition to 'today')-
> | select count(*) from posts is how it has been
> | done on our informix db.  With our port to PG I instead select reltuples
> | pg_class.
> | <<<
> | 
> | pg_class is a mysterious table indeed, but it's got lots of good magic 
> | in it.  (I'm not even sure if this is the right magic, but worth a try)
> 
> Humm... It seems to be the right one, but how can I measure which one
> is faster? In my 11k table, both are blazingly fast :)

Wow, this runs at the speed of light:

fbar=> explain select reltuples from pg_class where relname = 'normal';
NOTICE:  QUERY PLAN:

Seq Scan on pg_class  (cost=0.00..4.49 rows=1 width=4)

EXPLAIN
fbar=> explain select count(*)from normal;
NOTICE:  QUERY PLAN:

Aggregate  (cost=13239.34..13239.34 rows=1 width=0)
  ->  Seq Scan on normal  (cost=0.00..13098.07 rows=56507 width=0)

EXPLAIN

Doing both selects by hand, the one with pg_class returns practically
immediatly, the standard count has a duration of about 3.4 seconds, as
far as my rhythm feel tells me (but I'm a musician, so that's quite
accurate ;)

Now, how to put this to use?

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] PostgreSQL: costly count

[Sidnei da S. <si...@aw...>]

| >>>
| example:
| On our message boards each post is a row.  The powers that be like to 
| know
| how many posts there are total (In addition to 'today')-
| select count(*) from posts is how it has been
| done on our informix db.  With our port to PG I instead select reltuples
| pg_class.
| <<<
| 
| pg_class is a mysterious table indeed, but it's got lots of good magic 
| in it.  (I'm not even sure if this is the right magic, but worth a try)

Humm... It seems to be the right one, but how can I measure which one
is faster? In my 11k table, both are blazingly fast :)

zope3=# select reltuples from pg_class where relname = 'aliquot';
 reltuples 
-----------
     11801
(1 row)

zope3=# select count(*) from aliquot;
 count 
-------
 11801
(1 row)


-- 
Sidnei da Silva <si...@aw...>
http://awkly.org - dreamcatching :: making your dreams come true
http://plone.org/about/team#dreamcatcher

The trouble with computers is that they do what you tell them, not what
you want.
		-- D. Cohen

Re: [SQLObject] PostgreSQL: costly count

[Ian B. <ia...@co...>]

On Dec 4, 2003, at 11:00 AM, J-P Lee wrote:
> Hmm.. the thread seems to suggest the same thing.  I'm not sure if you 
> can get around the sequential scan.  count(id) saves you the time of 
> fetching all field contents.  As the thread points out, this time diff 
> is significant for large tables.  For small ones, though, it's 
> probably the same as count(*).

count(*) at least has the potential to be more easily optimized (and 
now that I think about it, I believe MySQL optimizes all instances of 
count(*)).  If you are counting any particular field, you have to look 
for NULLs (which don't get counted).  If you're clever you can tell 
that the primary key column can't be NULL, with count(*) you don't need 
to be clever at all.  And of course there's the optimization that you 
don't have to keep any information during your scan, except to 
increment a counter for each result (which is true of any count, except 
count(distinct ...)).

Oh, but I see from the discussion that it's all about transactions, and 
that MySQL does the same thing with transactions as well.  Anyway, 
found this in that thread:

 >>>
example:
On our message boards each post is a row.  The powers that be like to 
know
how many posts there are total (In addition to 'today')-
select count(*) from posts is how it has been
done on our informix db.  With our port to PG I instead select reltuples
pg_class.
<<<

pg_class is a mysterious table indeed, but it's got lots of good magic 
in it.  (I'm not even sure if this is the right magic, but worth a try)

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

Re: [SQLObject] PostgreSQL: costly count

[Frank B. <fb...@fo...>]

Hallo,
Frank Barknecht hat gesagt: // Frank Barknecht wrote:

> This message however:
> http://archives.postgresql.org/pgsql-performance/2003-10/msg00093.php
> suggests doing a "count(pkey)" instead: 
>   
>   <quote>
>   Have you tried doing
>   
>   SELECT count(pkey)
>   
>   rather than count(*) where pkey is the primary key (assuming you
>   have a single field that is a primary key or a unique indexed key).
>   This is MUCH faster in my experience.  
>   </qoute>
> 
> Note that the pkey is not the id-colmun, but an index on this column.

Ah, it seems I probably misread that message, and the author was
indeed talking about the id-column. Well, at least with my 60.000
records table it wasn't faster.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] PostgreSQL: costly count

[Frank B. <fb...@fo...>]

Hallo,
J-P Lee hat gesagt: // J-P Lee wrote:

> Hmm.. the thread seems to suggest the same thing.  I'm not sure if you 
> can get around the sequential scan.  count(id) saves you the time of 
> fetching all field contents.  

Fetching field contents is not the problem, I guess, because a count
doesn't (or shouldn't) fetch any contents (we didn't ask for them).

This message however:
http://archives.postgresql.org/pgsql-performance/2003-10/msg00093.php
suggests doing a "count(pkey)" instead: 
  
  <quote>
  Have you tried doing
  
  SELECT count(pkey)
  
  rather than count(*) where pkey is the primary key (assuming you
  have a single field that is a primary key or a unique indexed key).
  This is MUCH faster in my experience.  
  </qoute>

Note that the pkey is not the id-colmun, but an index on this column.

I couldn't try this yet, but it might be worth a try.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] PostgreSQL: costly count

[J-P L. <jp...@si...>]

Frank Barknecht wrote:

>I did,  it's largely the same as count(*) on Postgres, and also uses a
>sequential scan through the whole DB. I'm just reading through this
>looong thread on pgsql-performance:
>http://archives.postgresql.org/pgsql-performance/2003-10/msg00058.php
>  
>
Hmm.. the thread seems to suggest the same thing.  I'm not sure if you 
can get around the sequential scan.  count(id) saves you the time of 
fetching all field contents.  As the thread points out, this time diff 
is significant for large tables.  For small ones, though, it's probably 
the same as count(*).

--
J-P

Re: [SQLObject] PostgreSQL: costly count

[Frank B. <fb...@fo...>]

Hallo,
J-P Lee hat gesagt: // J-P Lee wrote:

> This is a common problem with most db's.  select count(id) is usually 
> the prefered way to do it. (Although I haven't tested the speed diff).

I did,  it's largely the same as count(*) on Postgres, and also uses a
sequential scan through the whole DB. I'm just reading through this
looong thread on pgsql-performance:
http://archives.postgresql.org/pgsql-performance/2003-10/msg00058.php

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] PostgreSQL: costly count

[J-P L. <jp...@si...>]

This is a common problem with most db's.  select count(id) is usually 
the prefered way to do it. (Although I haven't tested the speed diff).


Ian Bicking wrote:

> On Dec 4, 2003, at 9:10 AM, Frank Barknecht wrote:
>
>> Hallo,
>>
>> getting at the lenght of a result with SQLResult's count() finction
>> issues a "select count(*) from X". In my PostgreSQL table, this is
>> quite expensive, and I am wondering, why. Analyzing the query shows,
>> that no index is used:
>
>
>> => explain SELECT count(*) from normal;
>> NOTICE:  QUERY PLAN:
>
>
> I don't think an index could be used in this instance.  You aren't 
> finding specific rows, you are finding how many total rows there are.
>
> It's kind of dumb that PostgreSQL can't figure this out more quickly, 
> since it must have internal records of the table's size already 
> calculated.  I know MySQL has specific optimizations for this case 
> ("SELECT count(*) FROM blah").  If you figure something out, be sure 
> to get back to us, maybe there's something that can be added to 
> SQLObject to fix this.