Disadvantages of letting Postgres control key sequences?

Questions and answers regarding general SQL and backend databases

Disadvantages of letting Postgres control key sequences?

Postby Westy » Sat Jul 04, 2015 12:48 am

What are the disadvantages of letting Postgres control primary key sequences, instead of Servoy?

Dean Westover
Choices Software, Inc.
Westy
 
Posts: 852
Joined: Fri Feb 13, 2004 5:27 am
Location: Lynnfield, Massachusetts USA

Re: Disadvantages of letting Postgres control key sequences?

Postby ROCLASI » Sat Jul 04, 2015 8:00 am

Hi Dean,

Disadvantages of letting PostgreSQL specifically control the sequences ?
Short answer: None. :)

Why do you ask ?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Disadvantages of letting Postgres control key sequences?

Postby Westy » Sat Jul 04, 2015 3:11 pm

HI Robert,

I ask because I started out with Servoy controlling all primary key sequences. Then at one point I experimented a little with allowing postgres to control the sequences. My recollection is that my web client only solution did not work properly when postgres was controlling all the primary key sequences. However, I never figured out why. I reverted back to letting Servoy control all sequences. So my reason for asking this question is to find out if there are any known issues with allowing postgres to control all primary key sequences (especially for web client solutions).

This issue of letting Postgres control sequences has returned because I would prefer to be able to import data directly into Postgres.

Dean Westover
Choices Software, Inc.
Westy
 
Posts: 852
Joined: Fri Feb 13, 2004 5:27 am
Location: Lynnfield, Massachusetts USA

Re: Disadvantages of letting Postgres control key sequences?

Postby swingman » Sun Jul 05, 2015 11:45 am

Hi Dean,

I think letting PostgreSQL control the sequences makes life easier.
Issues I have come across over the years:

* when you build data structures you may have to save the mother-record so it gets its pk before you attach children.
* servoy seems to expect the sequence to be named
<table_name>_<pk_name>_seq
for instance
customers_id_seq
otherwise it may not see them.

Hope this helps,
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: Disadvantages of letting Postgres control key sequences?

Postby Westy » Sun Jul 05, 2015 4:28 pm

Hi Christian,

Thank you for the pointers.

Dean
Westy
 
Posts: 852
Joined: Fri Feb 13, 2004 5:27 am
Location: Lynnfield, Massachusetts USA

Re: Disadvantages of letting Postgres control key sequences?

Postby john.allen » Sun Jul 05, 2015 11:41 pm

Hi Dean

I have only ever used sequences in Oracle and web client. That's not postgres but they are very similar and I've never had a problem with it (other than when I screwed something up!). I've never had the issue that Christian mentions - that Servoy expects a certain naming convention - and in fact my sequences are almost always named just 'table_seq'. In my case I have to use sequences because very frequently I am adding records outside of Servoy and that is much easier when there is a db sequence.

John
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Disadvantages of letting Postgres control key sequences?

Postby ROCLASI » Mon Jul 06, 2015 1:14 am

Hi Dean,

When using db sequences with PostgreSQL you set your auto-enter sequence setting to 'db identity', like Christian already explained this assumes a certain naming convention of the sequence object in PostgreSQL. When you create your tables with a DBA tool you can easily let these sequences automatically be created and linked to the table at the same time by using the following datatypes:
- you want the PK column to be an integer with a sequence then set the datatype to 'Serial'
- you want to PK column to be a bigint with a sequence then set the datatype to 'Bigserial'

After creating the tables in this manner you will see that you have the correct datatype (integer or bigint) with a sequence with the proper name linked to this table. it's also added to the default value of the column. So these Serial and Bigserial datatypes are little tricks to create Integer/Bigint columns with a dedicated sequence attached.
Dropping the table will also automatically drop the sequence (like I said, they are linked).

If you don't create the columns this way or you want to add sequences to existing columns you need to create the sequence yourself using the CREATE SEQUENCE 'table_column_seq' syntax.
After you created them you need to add them to the column like so:
Code: Select all
ALTER TABLE tableName ALTER 'columnName' SET DEFAULT nextval('sequenceName')

This last one is not really needed for working with Servoy because Servoy will call this function before it inserts the data. But since you want to import data via other tools then you need to add this so your PK is filled automatically.

As John said you can also use the DB Sequence option where you have to tell Servoy explicitly what the sequence name is, which means you can then use any naming convention you want. But Servoy defaults to db identity when you use PostgreSQL with sequences I believe.

Like Christian said when you create parent and child records you need to save the parent record first to get the PK value to be used as the foreign key on the child records.
If I remember correctly Servoy is suppose to use some temporary/placeholder id value when using db managed sequences. This should allow for creating related records without saving first but I had issues with that so I always save the parent record first. Perhaps that this works as advertised now, I dunno.
Perhaps someone from Servoy can comment on this?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Disadvantages of letting Postgres control key sequences?

Postby Westy » Mon Jul 06, 2015 8:23 pm

Very helpful information. Thank you to all.

Dean Westover
Choices Software, Inc.
Westy
 
Posts: 852
Joined: Fri Feb 13, 2004 5:27 am
Location: Lynnfield, Massachusetts USA

Re: Disadvantages of letting Postgres control key sequences?

Postby huber » Tue Jul 07, 2015 9:53 am

Having to save parent record before being able to add child records is quite a drawback in using db managed sequences for me. But on the other hand, using servoy managed sequences, all record inserts have to come from the Servoy solution.

I am wondering if you (developers) use db managed sequences always in conjunction with a user done save or with auto commit as well?
If the user cancels the insertion after the parent record is created there is always a delete record needed, is it? Or are there other mechanism supported by Servoy?

ROCLASI wrote:Hi Dean,
...

Like Christian said when you create parent and child records you need to save the parent record first to get the PK value to be used as the foreign key on the child records.
If I remember correctly Servoy is suppose to use some temporary/placeholder id value when using db managed sequences. This should allow for creating related records without saving first but I had issues with that so I always save the parent record first. Perhaps that this works as advertised now, I dunno.
Perhaps someone from Servoy can comment on this?


Best regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: Disadvantages of letting Postgres control key sequences?

Postby ROCLASI » Tue Jul 07, 2015 10:59 am

Hi Robert,

I tend to use in-memory transactions (i.e. setAutoSave(false)) where users have to press a save button. This save button will then start a database transaction before it actually saves any data. This way I can rollback everything when an error occurs and ensures my save is atomic.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Disadvantages of letting Postgres control key sequences?

Postby huber » Wed Oct 07, 2015 1:35 pm

Hi Robert

Sounds always easy, but as you might know better than me this can become in a larger application quickly not so easy any more. For example when navigating to another menu without saving, and further to another menu, leaving may be some unsaved transactions open, and so on. What are the states and the context. What if the user actions lead to contradictions with unsaved states? And how to present the whole history to the user? And at what point to force to user to do a save?

Just because the original question was about disadvantages using db sequences. And, this may be debatable, but auto commit applications are easier and usually more intuitiv to use.

But of course, both ways have their (dis)advantages.

Robert

ROCLASI wrote:Hi Robert,

I tend to use in-memory transactions (i.e. setAutoSave(false)) where users have to press a save button. This save button will then start a database transaction before it actually saves any data. This way I can rollback everything when an error occurs and ensures my save is atomic.
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 8 guests

cron