Re: [SQLObject] two postgres questions

[Bud P. B. <bu...@si...>]

Dear Edmund,

many thanks for the help.  Below some comments..

On Wed, 14 May 2003 14:19:23 -0400
Edmund Lian <el...@in...> wrote:

> Bud P.Bruegger wrote:
> 
> > <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> 
> > At this point, I ran into another small issue with Postgres - its
> > "serial" data type (the equivalent of MySQL's auto_increment) creates
> > a "sequence" which does not get dropped when its parent table is
> > dropped. So if you try to re-create the table, you'll get a name
> > conflict for this sequence. A lot of new users would be confused by
> > this, so Postgres loses a couple points for that.
> 
> This depends on which version of PostgreSQL you are using. The latest 
> versions drop this automatically.

So you seem to confirm that SQLObject is fine as long as it runs with
Postgres7.3 or higher...  

> > Also, MySQL is
> > "smart" enough to increment its auto_increment value when you import
> > data, whereas Postgres' sequence does not get reset when you import
> > data, causing all new inserts to fail.  
> 
> This depends on how you import the data. Are you using the copy command? 
> If so, then rather than importing every column of a table that has an 
> autosequence, you should just import the columns you really want. Leave 
> the autoincrementing column out of the import statement, and it will be 
> autoincremented as you expect.

I actually just quoted the whole paragraph, I'm not currently in need
for this.  But thanks for clarifying the issue.  Surely I'll use it
one day soon.

> > I was thinking of having IDs that are unique across tables--something
> > useful when thinking of implementing inheritance etc.  So I tried to
> > create a global sequence and have the id columns of several tables use
> > it for their default values (DEFAULT nextval('global_oid_seq')).  But
> > that seems to fail.  Can there be only a single column (of a single
> > table) associated with a sequence???
> 
> You should be using triggers for this. I do this now and then, no problems.

My test didn't work since I used the type BIGSERIAL for the id
columns and set the DEFAULT to nextval of my global seqence.  This
way, BIGSERIAL implied a local sequence and default value and I
explicitly also set a global one:  two values assigned as defaults and
an obvious problem.    When I used BIGINT instead as type, it all
worked as expected...  [I did other funny things yesterday to make my
own life harder--I'm hoping for a better day today..]

many thanks

-b