PostgreSQL and serial columns for legacy DBs

Hi,

I've been trying to get Lightspeed to work with a legacy PostgreSQL DB using serial PK, autoincrementing columns, one per table within the schema in which the table is defined. I can make it work by using a single sequence in the 'public' schema, but this is very different from how the sequences work on our legacy DBs.

I've poured over the documentation and support forums looking for information regarding the serial type use. I can't find much and what I've found so far seems to imply that this is not supported.

Is there more documentation describing how to use this type somewhere else? Does Lightspeed not support this in PostgreSQL?

I'm a bit new to this ORM on .NET so I'm sure I'm doing something wrong. I have much more experience using Ruby on Rails and other ORMS like RoR's ActiveRecord and Data Mapper. Can someonehelp with this issue?

Thanks!

We support serial/autoincrementing columns via the IdentityColumn identity method:

// LightSpeedContext context;
context.IdentityMethod = IdentityMethod.IdentityColumn;

When you set this option, LightSpeed will query the database after each INSERT to find out the value allocated by the serial column (for that table).

A couple of notes on this:

- I have noticed a couple of potential issues relating to case sensitivity of table and column names when we issue the currval() query to get the ID allocated by the serial column -- let us know if you run into these.

- When dragging and dropping tables onto the designer, we do not automatically infer the IdentityColumn method from a PostgreSQL serial column.  However, if all your tables are using serial columns, then it is better to set the IdentityMethod at the context level anyway, as shown above.  You may also receive warnings that the designer is defaulting the identity type to Int32 -- this should not be a problem as that is what you want anyway.

Hi  Ivan,

Thank you for responding so quickly.

I believe I am setting the context to identity in the web config section, although it may be incorrect or in the wrong location:

  <lightSpeedContexts>

    <add name="Default"

         connectionStringName="Development"

         dataProvider="PostgreSql8"

         identityMethod="IdentityColumn"

         pluralizeTableNames="False" />

  </lightSpeedContexts>

I get a NpgsqlException error stating that the relation "response" is missing when Lightspeed calls the following currval sql request after doing an insert "SELECT currval(pg_get_serial_sequence('Response', 'id')". The insert is into a table called webcore.Response, webcore is the schema and appears to be missing from the currval call.

I believe the problem is both case issues and a missing schema in front of the table name.

I will test the missing schema myself by creating the Response table in the legacy schema. Does lightspeed use the schema in this case or do all tables need to be in the same schema?

Thanks Ivan.

Yep, that context is set up correctly: the fact that it's doing a SELECT currval(...) confirms that it is trying to obtain the ID allocated by the serial column.  The issue you're seeing appears to be a bug where we're not respecting the schema name (and there may be case issues as well).

In general, LightSpeed is happy for tables to be in any schema.  The LightSpeedContext.Schema specifies the default schema, and you can override this on a per-entity basis by setting the Schema property in the designer or TableAttribute.Schema in handwritten code.

However from my tests it seems the PostgreSQL identity column handler is completely ignoring the schema name, so this will currently work only if the Response table is in the public schema.  I'll get this fixed for you but I won't be able to get it into tonight's nightly -- hopefully in the 7 October nightly though.

Hi Ivan,

I tested your theory last night and you are correct (of course). This works fine if Response is in public. I wish we could just run this way, but we have 5 schemas that we need to access.

I'll be on the lookout for October 7 nightly. Sorry to be a pain, but you're in New Zealand so are you a day ahead of us here in US Central Standard Time?

Thank you for your prompt attention to this matter Ivan. I'm under the gun so I'm extremely appreciative!

Hi Paul,

I've committed a candidate fix for this and it will be in the 7 October nightly, which will be available sometime during the morning of 6 October CST (I think around 0800 but the time zone doodad is giving me inconsistent answers: but you're right, the 7 October date refers to New Zealand time, which is nearly a day ahead of US time).

This should sort out the schema issue, and should work correctly with tables across all of your schemas.  However, depending on your database definitions, it's possible you may still hit case sensitivity issues (the symptom will be an error message claiming that "webcore.Response" doesn't exist or doesn't have an "id" column even though it blatantly does).  These aren't manifesting in our test environment, so if this happens, could you post the CREATE TABLE definition for one of the tables that produces the error so we can try to get a repro?  Thanks!

You have to be the most responsive company I've ever dealt with!

I'm installing now. I'll get feedback to you ASAP.

Thanks Ivan! 

Hi Ivan,

Sorry for the delayed reply. At first it would not work. All posting failed because the currval request came back null. I got it to work, but I had to drop the table and the sequence before it did. I'm not quite sure why this is so. Also, this was the result of only a few postings, not an exhaustive test.

Again, thank you for your help and quick turn around for this fix Ivan!