I am having two problems when using sequences in SA .28 with postgresql
8.1.
1) The sequence is skipping a number.
2) Retrieval of newly created records fail until a disconnect/connect
to the database.
In my test case, the last serial used was 14. When I saved a record, it
was reported that the serial was now 15. I made a new SA object by
retrieving the record from the database. It came back as serial 15.
After dropping the connection and reconnecting, the saved serial number
showed to be 16. SQL queries in a psql session always showed 16.
Immediately below are some relevant definitions. Further below is the
SQL output and injected comments from my test case.
Perhaps someone here can help me identify, and of course solve, the
problem?
Thanks,
Rick
### SA Info:
class BuildCommandTbl(SAObject):
pass
build_commands_table = Table('build_commands', _meta,
Column('bldcmdid', Integer,
Sequence('build_commands_bldcmdid_seq'),
nullable=False, unique=True),
Column('cmdname', String(4095), primary_key=True, nullable=False),
Column('cmdvers', String(128), primary_key=True, nullable=False),
Column('argtemplate', String(4095), nullable=False)
)
BuildCommandTbl_Mapper = mapper(BuildCommandTbl, build_commands_table)
### postgresql properties of the sequence (via pgAdmin III)
# i.e. no custom functions here
CREATE SEQUENCE build_commands_bldcmdid_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 16
CACHE 1;
ALTER TABLE build_commands_bldcmdid_seq OWNER TO postgres;
### stdout and SQL output from the test session
# my post-test comments preceeded with '#'
[2006-12-01 06:05:57,486] [engine]: {}
TEST: Create new objects.
# the __str__ representation of a BuildCommandTbl
# instance before saving
1 internal table:
cmdvers: '1.2.3'
cmdname: 'test 4'
argtemplate: 'cookie%s2'
bldcmdid: None
# note that the apparent serial ID is correct below. The previous
# record was 14, now it is 15. Sems perfect so far.
[2006-12-01 06:05:57,690] [engine]: SELECT schema_version.major,
schema_version.minor, schema_version.patch
FROM schema_version
[2006-12-01 06:05:57,690] [engine]: {}
[2006-12-01 06:05:57,690] [engine]: BEGIN
[2006-12-01 06:05:57,690] [engine]: select
nextval('build_commands_bldcmdid_seq')
[2006-12-01 06:05:57,690] [engine]: None
[2006-12-01 06:05:57,690] [engine]: INSERT INTO build_commands
(cmdname, cmdvers, argtemplate) VALUES (%(cmdname)s, %(cmdvers)s,
%(argtemplate)s)
[2006-12-01 06:05:57,690] [engine]: {'cmdvers': '1.2.3', 'argtemplate':
'cookie%s2', 'cmdname': 'test 4', 'bldcmdid': 15L}
[2006-12-01 06:05:57,704] [engine]: COMMIT
# Just a guess... is the 'select nextval' causing an increment
# as well as the INSERT? Is this the casue of the skipping
# number problem?
# A print indicates that the serial value in the BuildCommandTbl
# object has been updated.
2 internal table:
cmdvers: '1.2.3'
cmdname: 'test 4'
argtemplate: 'cookie%s2'
bldcmdid: 15L
# in a pqsl session:
# myhost=# SELECT * FROM build_commands WHERE cmdname='test 4';
# bldcmdid | cmdname | cmdvers | argtemplate
# ----------+---------+---------+-------------
# 16 | test 4 | 1.2.3 | cookie%s2
# Create a new SA object using a query based on the name.
[2006-12-01 06:05:57,704] [engine]: SELECT build_commands.bldcmdid AS
build_commands_bldcmdid, build_commands.argtemplate AS
build_commands_argtemplate, build_commands.cmdname AS
build_commands_cmdname, build_commands.cmdvers AS
build_commands_cmdvers
FROM build_commands
WHERE build_commands.cmdname = %(build_commands_cmdname)s ORDER BY
build_commands.cmdname
[2006-12-01 06:05:57,704] [engine]: {'build_commands_cmdname': 'test
4'}
# The new object apparently is the same as what we just saved.
# Is SA saying that the retrieved object is identical
# and a cached value is used? (just a wild guess)
3 internal table:
cmdvers: '1.2.3'
cmdname: 'test 4'
argtemplate: 'cookie%s2'
bldcmdid: 15L
# disconnect/reconnect.
# do the same retrieval operation that we just did.
[2006-12-01 06:05:57,815] [engine]: {}
[2006-12-01 06:05:57,815] [engine]: SELECT build_commands.bldcmdid AS
build_commands_bldcmdid, build_commands.argtemplate AS
build_commands_argtemplate, build_commands.cmdname AS
build_commands_cmdname, build_commands.cmdvers AS
build_commands_cmdvers
FROM build_commands
WHERE build_commands.cmdname = %(build_commands_cmdname)s ORDER BY
build_commands.cmdname
[2006-12-01 06:05:57,815] [engine]: {'build_commands_cmdname': 'test
4'}
# The sequence number is different!
# This is what is really in the database.
4 internal table:
cmdvers: '1.2.3'
cmdname: 'test 4'
argtemplate: 'cookie%s2'
bldcmdid: 16
# I'm so confused...
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
