Hi,
I wonder if anyone can throw some light on a problem I'm having with pgsql.
I'm using postgresql-6.3.2 on RedHat 5.2 and using the Perl DBD:Pg module
to handle a relational database I have setup.
Essentially what I am trying to do is maintain a list of Audio tracks from
CD's. I have setup a TRACKS table and an Album Table (and several others).
The TRACKS table has an 'AlbumRef' attribute which is shared by the same
attribute in the 'ALBUMS' table. The idea being to show which tracks belong
to which Albums. So far so good.
I have written a function in PERL that adds tracks to the TRACKS table and
looks up the Album name in 'ALBUMS' in order to retrieve the 'AlbumRef'
attribute and place it into the TRACKS table, this seems to work OK.
However, if the Album name isn't found in the 'ALBUMS' table then the
function Generates a new 'AlbumRef' and inserts this along with the Album
Name into the 'ALBUMS' table. This is where my problems start. As several
tracks from the same Album are inserted into the 'TRACKS' table one after
another, then just the first track should create a new entry in the
'ALBUMS' table - which it does, however so do the second, third and
sometimes fourth tracks (the number is not consistent), resulting in
multiple identical entries in the 'ALBUMS' table.
I have turned the autocommit off and manually do a commit after each
insert, yet it seems that the selects for the Album Name are failing,
causing another entry for the ALBUM to be generated.
What makes this particularly confusing is that I do exactly the same thing
for the 'ARTISTS' table using the same perl functions - which works just fine!
Any ideas? Please!
Graham...