Re: force defaults - Mailing list pgsql-general

From Sim Zacks
Subject Re: force defaults
Date
Msg-id 50504DF5.1070203@compulab.co.il
Whole thread Raw
In response to force defaults  (Willy-Bas Loos <willybas@gmail.com>)
List pgsql-general
<meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
    body p { margin-bottom: 0cm; margin-top: 0pt; }

  <body style="direction: ltr;"
    bidimailui-detected-decoding-type="latin-charset" text="#000000"
    bgcolor="#FFFFFF">
    I haven't checked more recent versions,
      but in 8.2 using
          case when new.val is null then 'U' else new.val end
      worked a hell of a lot faster then coalesce.
      However, just going into the trigger is significant overhead.

      Alban's suggestion of using a rule would work as well.
      If you don't need the value in real time, you can either have a
      cron job update all the wrong values on a regular basis or using
      listen/notify you can get it close to real time.

      Sim


      On 09/12/2012 09:49 AM, Willy-Bas Loos wrote:

    <blockquote
cite="mid:CAHnozTjpO3Yn9VfcAdZYj6A1tPkAOC26HFnsvL2mosHi6189sw@mail.gmail.com"
      type="cite">Hi,

      I want to force deafults, and wonder about the performance.
      The trigger i use (below) makes the query (also below) take 45%
      more time.
      The result is the same now, but i do have a use for using the
      trigger (see "background info").

      Isn't there a more efficient way to force the defaults (in the
      database) when the application layer explicitly inserts a NULL?

      Cheers,

      WBL


      <backgorund info>
       I'm building an application where users can upload data to a
      database, with a website as a gui.
       
       In the legacy database that we're "pimping", some attributes are
      optional, but there are also codes that explicitly mark the
      attribute as 'Unknown' in the same field.
       That would mean that there are 2 things that mean the same thing:
      NULL and 'U' for unknown.
       I don't want to bother our customer with the NULL issues in
      queries, so i would like to make those fields NOT NULL.

       The users will use an Excel or CSV form to upload the data and
      they can just leave a blank for the optional fields if they like.
       We'll use php to insert the data in a table, from which we'll
      check if the input satisfies our demands before inserting into the
      actual tables that matter.

       When the users leave a blank, php is bound to insert a NULL (or
      even an empty string) into the upload table.
       I want to use a default, even if php explicitly inserts a NULL.
      </backgorund info>


      --the TRIGGER
      create or replace function force_defaults () returns trigger as $$
      begin
          new.val:=coalesce(new.val, 'U');
      return new;
      end;
      $$ language plpgsql;

      --the QUERIES (on my laptop, no postgres config, pg 9.1):
      create table accounts (like pgbench_accounts including all);
      --(1)
      alter table accounts add column val text default 'U';
      insert into accounts(aid, bid, abalance, filler) select * from
      pgbench_accounts;
       INSERT 0 50000000
       Time: 538760.542 ms

      --(2)
      alter table accounts alter column val set default null;
      create trigger bla before insert or update on accounts for each
      row ...etc
      vacuum accounts;
      insert into accounts(aid, bid, abalance, filler) select * from
      pgbench_accounts;
       INSERT 0 50000000
       Time: 780421.041 ms

        <img
            moz-do-not-send="true" class="ajT"
            src="https://mail.google.com/mail/u/0/images/cleardot.gif">



      --
      "Quality comes from focus and clarity of purpose" -- Mark
      Shuttleworth

pgsql-general by date:

Previous
From: "Albe Laurenz"
Date:
Subject: Re: Postgresql replication assistance
Next
From: "Albe Laurenz"
Date:
Subject: Re: CentOS initd Script