Thread: steps to ensure referential integrity

I have inherited a database that was originally created in postgres version 6.? There were no steps taken at that time to implement constraints to ensure referential integrity. There are no FK constraints, PK constraints, etc..
Most of what currently exists is done in the existing code. I would like to change this and am interested in getting suggestions. I expect that PK constraints on each table as well as all FK constraints should be in place. Anything else that I should consider?
I have indexes currently set up on a variety of fields, including PK fields. Do the constraints automatically generate indexes on these fields such that I should remove the ones created at the end of my script?
Thanks in advance for the suggestions!
Jodi

I have written a variation of one of the libpq C functions provided with
postgres.  It compiles just fine, but when I try to execute the program
it reports the following error:

./a.out: error while loading shared libraries: libpq.so.3: cannot open
shared object file: No such file or directory

I have checked my system and the libpq.so.3 exists and all of its
dependancies are linked.  Just curious if anyone has any ideas.  Thanks

Kris

Did you set LD_LIBRARY_PATH to locate directory where libpq.so.3 can be
found ?
(Something like LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH)

Luc Jouneau

----- Original Message -----
From: "Kris Kiger" <kris@musicrebellion.com>
To: "Postgres Admin List" <pgsql-admin@postgresql.org>
Sent: Monday, September 22, 2003 6:14 PM
Subject: [ADMIN] libpq problem


> I have written a variation of one of the libpq C functions provided with
> postgres.  It compiles just fine, but when I try to execute the program
> it reports the following error:
>
> ./a.out: error while loading shared libraries: libpq.so.3: cannot open
> shared object file: No such file or directory
>
> I have checked my system and the libpq.so.3 exists and all of its
> dependancies are linked.  Just curious if anyone has any ideas.  Thanks
>
> Kris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

I ended up setting the path in the ld.so.conf file and it seems to work
just fine, thanks for all of your help!

Kris

Kris,

There are a variety of answers for a variety of operating systems.

On Linux add the PostgreSQL lib directory to /etc/ld.so.conf and run
"ldconfig" as root or add the lib directory to your LD_LIBRARY_PATH
environment variable.  I prefer the 1st fix.

On Solaris 8 or later run as root "crle -u -l /path/to/pgsql/lib" which
is the same as the ldconfig option in Linux.  Alternatively, use the
same LD_LIBRARY_PATH solution as above which, by the way, this will
work in HP-UX and AIX though using different environment variables
LIBPATH and SHLIB_PATH, respectively.

Greg


Kris Kiger wrote:
> I have written a variation of one of the libpq C functions provided with
> postgres.  It compiles just fine, but when I try to execute the program
> it reports the following error:
>
> ./a.out: error while loading shared libraries: libpq.so.3: cannot open
> shared object file: No such file or directory
>
> I have checked my system and the libpq.so.3 exists and all of its
> dependancies are linked.  Just curious if anyone has any ideas.  Thanks
>
> Kris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly


--
Greg Spiegelberg
  Sr. Product Development Engineer
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@Cranel.com
Cranel. Technology. Integrity. Focus.

On Mon, 22 Sep 2003, Jodi Kanter wrote:

> I have inherited a database that was originally created in postgres
> version 6.? There were no steps taken at that time to implement
> constraints to ensure referential integrity. There are no FK
> constraints, PK constraints, etc..
> Most of what currently exists is done in the existing code. I would like
> to change this and am interested in getting suggestions. I expect that
> PK constraints on each table as well as all FK constraints should be in
> place. Anything else that I should consider?
> I have indexes currently set up on a variety of fields, including PK
> fields. Do the constraints automatically generate indexes on these
> fields such that I should remove the ones created at the end of my script?
> Thanks in advance for the suggestions!

I would first upgrade to a reasonably new version of Postgesql 6.5.x is
pretty old.  7.3.4 or so should be a good choice.  You'll find that you
can add the pk/fk constraints in the conversion process, and still have a
faster database with fk/pk under 7.3 than you'd have with no fk/pk
constraints in 6.5.3

________________________________
Hi Jodie,

    This snippet is generated code from my client side data dictionary for
postgresql.
    The sequence described is used with nextval() to obtain autoincrement
key values
    for the primary key. Thus primary key definition does create an index as
do foreign key
    declarations.  I use the views to mimic conditional indices. Each view
has a companion
    index generated if one suitable is not already in existance.

    HTH
    Mark Carew
    Brisbane Australia
    07 35117911


      /*  client side     */

    IF ascan(aTokens,"AUTOINC") > 0
      *
      ::AutoInc  := TRUE
      *
      IF cColumnType == "CS"
        *
        #IFDEF SQLFILES
          *
          ::SqlDefault := ;
            "SELECT nextVal('" + ;
            lower(::oTable:filename) + ;
            "_seq') ;"
          *
        #ENDIF
        *
      ENDIF
      *
    ENDIF

  /*  server side */




    DROP SEQUENCE invhdr_seq ;
    CREATE SEQUENCE invhdr_seq START 322 ;

DROP TABLE invhdr ;

CREATE TABLE invhdr  (
    vinno  int4,
    vindate  date
        NOT NULL,
    dateent  date,
    clientno  char(12)
        NOT NULL,
    total  numeric(10,2),
    balance  numeric(10,2),
    allocamt  numeric(10,2),
    gst  numeric(9,2),
    cordno  char(14),
    printed  char(1)
        ,
    origin  char(1)
        ,

    PRIMARY KEY ( vinno )

    ,FOREIGN KEY (clientno )
        REFERENCES client
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
    );

    CREATE INDEX invhdr_dateclt ON invhdr (vindate, clientno);

    CREATE INDEX invhdr_vindate ON invhdr (vindate);

    CREATE INDEX invhdr_dateent ON invhdr (dateent);

    DROP VIEW invhdr_unprint_view ;

    CREATE VIEW invhdr_unprint_view AS
     SELECT * FROM invhdr
      WHERE PRINTED = 'N'
       ORDER BY vinno ;

    CREATE INDEX invhdr_clntactv ON invhdr (clientno, vindate);

    DROP VIEW invhdr_clntactv_view ;

    CREATE VIEW invhdr_clntactv_view AS
     SELECT * FROM invhdr
      WHERE BALANCE  <> cast( 0.00 as numeric )
       ORDER BY clientno, vindate ;

    CREATE INDEX invhdr_cordno ON invhdr (cordno);

    DROP VIEW invhdr_cordno_view ;

    CREATE VIEW invhdr_cordno_view AS
     SELECT * FROM invhdr
      WHERE CORDNO <> ' '
       ORDER BY cordno ;

On Tue, Sep 23, 2003 at 10:52:27AM -0600, scott.marlowe wrote:
> On Mon, 22 Sep 2003, Jodi Kanter wrote:
>
> > I have inherited a database that was originally created in postgres
> > version 6.? There were no steps taken at that time to implement
> > constraints to ensure referential integrity. There are no FK
> > constraints, PK constraints, etc..
> > Most of what currently exists is done in the existing code. I would like
> > to change this and am interested in getting suggestions. I expect that
> > PK constraints on each table as well as all FK constraints should be in
> > place. Anything else that I should consider?
> > I have indexes currently set up on a variety of fields, including PK
> > fields. Do the constraints automatically generate indexes on these
> > fields such that I should remove the ones created at the end of my script?
> > Thanks in advance for the suggestions!
>
> I would first upgrade to a reasonably new version of Postgesql 6.5.x is
> pretty old.  7.3.4 or so should be a good choice.  You'll find that you
> can add the pk/fk constraints in the conversion process, and still have a
> faster database with fk/pk under 7.3 than you'd have with no fk/pk
> constraints in 6.5.3
>

Creating a primary key also creates a unique index on that field(s).  Depending on how normalised the data schema is,
youmay also have to create stored procedures to gaurentee data integrity.  If the data is not currently good, you may
haveto remove bad data to get the constraints to check.  I'm no guru, but I do something like this to at least check: 

select count(distinct(id)) from table;
vs
select count(id) from table;

if the numbers are different, you have dup ids.

To get a list:
select id,count(*) from table group by (id);

I hope someone can figure out how how to put a where on that so it only returns those above 1, but I personaly don't
knowhow, so I'll use my friend the shell: 

psql database -Uuser -c 'select id,count(*) from table gropu by id' | grep -v '1$';

Alex Turner

On Tue, 2003-09-23 at 13:41, aturner@neteconomist.com wrote:
> On Tue, Sep 23, 2003 at 10:52:27AM -0600, scott.marlowe wrote:
> > On Mon, 22 Sep 2003, Jodi Kanter wrote:
> >
> > > I have inherited a database that was originally created in postgres
> > > version 6.? There were no steps taken at that time to implement
> > > constraints to ensure referential integrity. There are no FK
> > > constraints, PK constraints, etc..
> > > Most of what currently exists is done in the existing code. I would like
> > > to change this and am interested in getting suggestions. I expect that
> > > PK constraints on each table as well as all FK constraints should be in
> > > place. Anything else that I should consider?
> > > I have indexes currently set up on a variety of fields, including PK
> > > fields. Do the constraints automatically generate indexes on these
> > > fields such that I should remove the ones created at the end of my script?
> > > Thanks in advance for the suggestions!
> >
> > I would first upgrade to a reasonably new version of Postgesql 6.5.x is
> > pretty old.  7.3.4 or so should be a good choice.  You'll find that you
> > can add the pk/fk constraints in the conversion process, and still have a
> > faster database with fk/pk under 7.3 than you'd have with no fk/pk
> > constraints in 6.5.3
> >
>
> Creating a primary key also creates a unique index on that field(s).  Depending on how normalised the data schema is,
youmay also have to create stored procedures to gaurentee data integrity.  If the data is not currently good, you may
haveto remove bad data to get the constraints to check.  I'm no guru, but I do something like this to at least check: 
>
> select count(distinct(id)) from table;
> vs
> select count(id) from table;
>

select count(distinct(id)), count(id), count(*) from table


> if the numbers are different, you have dup ids.
>
> To get a list:
> select id,count(*) from table group by (id);
>
> I hope someone can figure out how how to put a where on that so it only returns those above 1, but I personaly don't
knowhow, so I'll use my friend the shell: 
>

select id,count(*) from table group by id having count(*) > 1;

and actually just running that would allow you to skip the check from
above.

HTH

Robert Treat

> psql database -Uuser -c 'select id,count(*) from table gropu by id' | grep -v '1$';
>
> Alex Turner

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL