Re: Problem with sequences using psycopg

On Fri, Jun 10, 2005 at 12:40:29PM +1000, Adam Ward wrote:
> When creating an object, and not specifying a sequence field, I get the
> following error.

[ error snipped ]

Yes, as you've discovered, and I apparently forgot to document
properly, you have to tell PyDO what the sequence name is for
postgresql.  The Sequence constructor takes an additional parameter,
"sequence", for this purpose.  Hence, this would typical:

   class Chimp(PyDO):
       fields=(Sequence('id', 'chimp_id_seq'),)

As it happens, if you use the "guess_columns" feature, PyDO will try
to figure this out, but it expects the sequence name to be the name
postgresql will automatically give a sequence created for a serial
column; if you use your own idiosyncratically named sequence, it won't
find it (i.e., "id integer not null primary key default
nextval('my_idiosyncratic_seq')").  (Also, I seem to recall that
postgresql is some older versions may truncate the sequence name if
the table name is very long for serial columns, but I can't reproduce
that with postgresql 8, and the driver doesn't do so.)  However,
anything other than normal ids declared with a SERIAL or BIGSERIAL
type falls outside of what we should be expected to handle
automatically.

> After a little bit of hacking around I tried:
> class User(PyDO):
>     connectionAlias = 'db'
>     table = 'users'
>     mutable = True
>     refetch = True
>     fields = (name='id', sequence='public.users_id_seq')

I presume that is a typo, and you meant:

      fields =(Sequence('id', 'public.users_id_seq'),)

or something similar.  

> Shouldn't PyDO automatically find the sequence name for a given field?

I hadn't thought so in the past, as inferring it on the basis of table
and field name isn't completely foolproof, and the only time real
introspection -- querying the db catalogs to get table structure --
should take place is when you've asked for it.  But I'm changing my
mind; at least in the case of postgresql, the database itself enforces
a naming convention for the most common use of sequences, and giving a
driver the option to infer a default value for a sequence name
therefore seems like a good keystroke-saving feature.

I'll look into it adding it.  I'll need to refactor the dbi api a bit.

Cheers,

js

-- 
Jacob Smullyan

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFCqQ/suqamFyFXXLIRAuBUAKDHeQEHg6UyJR/FTMgjjmpvn3PgQACgkpk4
o3VnRlNaDkIQ5RsTOpFL2TA=
=+TJD
-----END PGP SIGNATURE-----