Обсуждение: Modelling Oracle Sequences
Hi, I am in the process of porting a number of large MSSQL databases to Postgres. The current id generation model in SQL server is an oracle style sequence generator i wrote using a pretty simple stored proc and table structure. I now need to do the same thnig in postgres. In MSSQL I was able to open an UPDATE cursor, which places a lock on the row in the sequence table. I then update, release and return. Has anyone done something similar or have any pointers on how to do this safely, securely without an update cursor? Any infor appreciated. Cheers, Julian. ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
Hello why can't use normal sequence? There isn't difference betwen Oracle and PostgreSQL? http://www.postgresql.org/docs/7.3/static/sql-createsequence.html regards Pavel On Fri, 21 Nov 2003, Julian North wrote: > Hi, > > I am in the process of porting a number of large MSSQL databases to > Postgres. > > The current id generation model in SQL server is an oracle style sequence > generator i wrote using a pretty simple stored proc and table structure. > > I now need to do the same thnig in postgres. > > In MSSQL I was able to open an UPDATE cursor, which places a lock on the row > in the sequence table. I then update, release and return. > > Has anyone done something similar or have any pointers on how to do this > safely, securely without an update cursor? > > Any infor appreciated. > > Cheers, > > > > Julian. > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
Hi Pavel, Thanks for that. I hadn't realised there was support for proper sequences. Usual asking before fully researching. Thanks, Julian. -----Original Message----- From: Pavel Stehule [mailto:stehule@kix.fsv.cvut.cz] Sent: 21 November 2003 09:42 To: Julian North Cc: 'pgsql-general@postgresql.org' Subject: Re: [GENERAL] Modelling Oracle Sequences Hello why can't use normal sequence? There isn't difference betwen Oracle and PostgreSQL? http://www.postgresql.org/docs/7.3/static/sql-createsequence.html regards Pavel On Fri, 21 Nov 2003, Julian North wrote: > Hi, > > I am in the process of porting a number of large MSSQL databases to > Postgres. > > The current id generation model in SQL server is an oracle style sequence > generator i wrote using a pretty simple stored proc and table structure. > > I now need to do the same thnig in postgres. > > In MSSQL I was able to open an UPDATE cursor, which places a lock on the row > in the sequence table. I then update, release and return. > > Has anyone done something similar or have any pointers on how to do this > safely, securely without an update cursor? > > Any infor appreciated. > > Cheers, > > > > Julian. > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
Julian North wrote: > Hi, > > I am in the process of porting a number of large MSSQL databases to > Postgres. > > The current id generation model in SQL server is an oracle style sequence > generator i wrote using a pretty simple stored proc and table structure. > > I now need to do the same thnig in postgres. > > In MSSQL I was able to open an UPDATE cursor, which places a lock on the row > in the sequence table. I then update, release and return. > > Has anyone done something similar or have any pointers on how to do this > safely, securely without an update cursor? Are you trying to do this? http://developer.postgresql.org/docs/postgres/functions-sequence.html Sequence values are transaction safe in postgreql. You don't have to lock anything to get a correct and unique sequence value. HTH Shridhar