Menu

#29 Fix for PostgreSQL

open
nobody
Bug Fix (25)
5
2005-08-31
2005-08-31
Rui Campos
No

When using exponent with PostgreSQL, the EQL being used
must be remade, so that it doesn't include the id field
(this requires that all modules be inserted under a
specific sequence, and that dependent modules have the
correct parent id).

What happens is that PostgreSQL doesn't update the
"sequence" value, so the next "id" for any table having
"id" will be 1 and that is not correct, violating the
Primary Key.

To avoid this I made an SQL file that resolves the
Sequences to their correct "id"s.

NOTE: THIS ONLY WORKS FOR 0.96.3

But this is just one part of the fix, the second has to
do with using exponent with PostgreSQL, but on a Web
Hoster.

Most Web Hosting solutions remove some permissions on
the Database to ensure privacy, this means in most
cases that you are not a owner of the PUBLIC schema.
This means that using anything similar to
'public'.'sequence_name' will return 0 or 1, it
shouldn't happen, but it does. If you use just
'sequence_name' it works ok. This is strange since it
will default back to PUBLIC schema, but I didn't made
it, so, dunno what's wrong.

So, this means that id columns can't be created with
the serial, instead we should create it by hand, create
the Sequence and then use it as " id integer DEFAULT
nextval'sequence_name' ", thus removing the buggy
'public.sequence_name' that the serial defaults to.

This should be nicely solved by adding a "schema" field
on the installer, allowing PostgreSQL users to specify
a schema, or don't specify one at all. And be used with
the tables and sequences and whatever.
This would be great for PostgreSQL users, since it
would also allow to install exponent in different
schemas instead of using the "prefix_".

What I did was to patch the postgres.php file, to
create the Sequence by hand instead of using the serial
tag.

Hope you guys find a nice solution, since this is just
a workaround.

Discussion

  • Rui Campos

    Rui Campos - 2005-08-31

    SQL Fix

     
  • Rui Campos

    Rui Campos - 2005-08-31

    postgres.php script changed

     
  • Rui Campos

    Rui Campos - 2005-08-31

    Logged In: YES
    user_id=944846

    postgres.php script

     
  • Rui Campos

    Rui Campos - 2005-08-31

    Logged In: YES
    user_id=944846

    One more thing, the first time you try to insert an item in
    any of the tables where the sequence was restarted it will
    fail, but will work fine afterwards.

    Also, in order for this to work correctly I expect you will
    be installing exponent from scratch, else the SQL Fix won't
    work, and the php script won't make much difference, since
    it is made to be used on the Table Creation or Table Alter.

     

Log in to post a comment.