postgresql and serial primary key

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

postgresql and serial primary key

Luca Ferrari
Hi all,
I'm a problem with PostgreSQL serial field used as keys, and it seems
there's something in DBIx I'm not understanding.
My Result has a definition for the column as follows:

__PACKAGE__->add_columns(
  "pk",
  {
    data_type         => "integer",
    is_auto_increment => 1,
    is_nullable       => 0,
    sequence          => "tickets_pk_seq",
  }, ...


when I want to add a new field I do the following (in a catalyst
application) in the controller;

my $current_ticket = $c->model( 'DB::Ticket' )->find_or_new( { pk => $pk } );
...
$current_ticket->update_or_insert();

the problem is that for a new record the pk field is still null, and
therefore the sequence is not called since the value NULL is placed in
the query.
Moreover, since my Result has has_many objects, the null value is
propagated to the child objects.
Is there a way to auto-query the sequence when the record is going to
be inserted? I don't want to query it always to avoid sequence-holes.
Similarly, I don't want to find_or_create records since the user could
not end inserting it and I would have "blank" tuples.
Suggestions?

Thanks,
Luca

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: postgresql and serial primary key

Maddy, Noel
Luca,

IIRC, if you pass any value (even NULL) for the pk column, then it bypasses the sequence.

You'll need to drop the pk column completely when $pk is undefined.

I'd just use PerlX::Maybe, like this:

my $current_ticket = $c->model('DB::Ticket')->find_or_new( maybe pk => $pk );

HTH,
Noel
________________________________________
From: Luca Ferrari [[hidden email]]
Sent: Monday, February 02, 2015 12:02 PM
To: DBIx::Class
Subject: [Dbix-class] postgresql and serial primary key

Hi all,
I'm a problem with PostgreSQL serial field used as keys, and it seems
there's something in DBIx I'm not understanding.
My Result has a definition for the column as follows:

__PACKAGE__->add_columns(
  "pk",
  {
    data_type         => "integer",
    is_auto_increment => 1,
    is_nullable       => 0,
    sequence          => "tickets_pk_seq",
  }, ...


when I want to add a new field I do the following (in a catalyst
application) in the controller;

my $current_ticket = $c->model( 'DB::Ticket' )->find_or_new( { pk => $pk } );
...
$current_ticket->update_or_insert();

the problem is that for a new record the pk field is still null, and
therefore the sequence is not called since the value NULL is placed in
the query.
Moreover, since my Result has has_many objects, the null value is
propagated to the child objects.
Is there a way to auto-query the sequence when the record is going to
be inserted? I don't want to query it always to avoid sequence-holes.
Similarly, I don't want to find_or_create records since the user could
not end inserting it and I would have "blank" tuples.
Suggestions?

Thanks,
Luca

_______________________________________________

________________________________

CONFIDENTIALITY NOTICE: This email contains information from the sender that may be CONFIDENTIAL, LEGALLY PRIVILEGED, PROPRIETARY or otherwise protected from disclosure. This email is intended for use only by the person or entity to whom it is addressed. If you are not the intended recipient, any use, disclosure, copying, distribution, printing, or any action taken in reliance on the contents of this email, is strictly prohibited. If you received this email in error, please contact the sending party by reply email, delete the email from your computer system and shred any paper copies.

Note to Patients: There are a number of risks you should consider before using e-mail to communicate with us. See our Privacy & Security page on www.henryford.com for more detailed information as well as information concerning MyChart, our new patient portal. If you do not believe that our policy gives you the privacy and security protection you need, do not send e-mail or Internet communications to us.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: postgresql and serial primary key

Luca Ferrari
Ciao,

On Mon, Feb 2, 2015 at 8:32 PM, Maddy, Noel <[hidden email]> wrote:
> Luca,
>
> IIRC, if you pass any value (even NULL) for the pk column, then it bypasses the sequence.
>
> You'll need to drop the pk column completely when $pk is undefined.
>
> I'd just use PerlX::Maybe, like this:
>
> my $current_ticket = $c->model('DB::Ticket')->find_or_new( maybe pk => $pk );

correct!
In my case I use a less elegant technique building the hash ref to
find_or_new before invoking it, so that I can add the pk or not:

$params_ref = {};
$params_ref->{pk} = $pk if ( $pk );
my $current_ticket = $c->model('DB::Ticket')->find_or_new( $params_ref );

Another approach could have been to call separately find or new with a
couple of conditionals:

my $model = $c->model('DB::Ticket');
my $current_ticket = ( $pk ? $model->find ({pk => $pk}) : $model->new() );

Thanks,
Luca

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...