Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is cycle references?

Is cycle references?

From
Date:
The db schema we have need to do a cycle references.  I.e.,
an attribute in table_1 references the key in table_2 and
an attribute in table_2 references the key in table_1.  However,
PostgreSQL does not allow us to make reference to an "non-existing" table!
If we create a fake table_2 first, then create table_1, drop table_2,
create the "real" table_2, we will lost the reference constraint during
droping the "fake" table_2.

Is there any work-around on this issue?

We appreicate your hints.

Limin
pumpkin networks

DBI -> remote host?

From
"Steve Wolfe"
Date:
  I apologize upfront for this message.  I'm under the gun, and things
aren't looking good.

  If anyone has experience using DBI to connect to a PostgreSQL database
that is on a different machine, I could use some help.  One of our packages
(purchased by management...) needs to be migrated ASAP.  I looked in the
code to find where it connects, and changed it to:

DBI->connect("dbi:Pg:dbname=hyperseek;host=10.0.1.100;port=5432")

  It does connect - but does not retrieve any data.  It doesn't seem to be a
permissions issue, the database is owned by the user that is connecting.
Any ideas?

steve

Re: DBI -> remote host?

From
"Steve Wolfe"
Date:
> Be sure to set DBI->trace(2) on.  It helps greatly in tracking errors. I
show it
> below on the database object, but you should be able to set it as a class
> method, I believe. I have done this in the past, but don't see it in any
of my
> code now.

> I realize that this is not exactly what you asked for, but I don't really
> understand what you failure is.  Can you connect via 'psql -h 10.0.1.100
> hyperseek' ?

  Thanks for the letter.  I should have given more detail.

psql -h 10.0.1.100 -d hyperseek -c "\d"

 works just hunky-dorey.  I did a DBI_TRACE, and while I won't pretend to
really understand what's going on, there are no messages indicating errors
or warnings.  (I can provide it if you'd like).  And, there *is* data in the
database.  I checked that very quickly. : )

 the DBI trace shows it connecting as the owner of the table, and shows it
returning the hash when it connects.  When a query is executed, it appears
to function correctly - but no data is returned.  The kicker is that if I
query the tables from PSQL, the data shows up.  Any ideas?  I'm certainly
baffled.

steve

Re: Is cycle references?

From
Stephan Szabo
Date:
On Wed, 1 Nov 2000 limin@www.pumpkinnet.com wrote:

>
> The db schema we have need to do a cycle references.  I.e.,
> an attribute in table_1 references the key in table_2 and
> an attribute in table_2 references the key in table_1.  However,
> PostgreSQL does not allow us to make reference to an "non-existing" table!
> If we create a fake table_2 first, then create table_1, drop table_2,
> create the "real" table_2, we will lost the reference constraint during
> droping the "fake" table_2.
>
> Is there any work-around on this issue?
Use ALTER TABLE ADD CONSTRAINT to add the constraint after
the second table is made.

Re: Is cycle references?

From
Tod McQuillin
Date:
On Wed, 1 Nov 2000 limin@www.pumpkinnet.com wrote:

> The db schema we have need to do a cycle references.  I.e.,
> an attribute in table_1 references the key in table_2 and
> an attribute in table_2 references the key in table_1.  However,
> PostgreSQL does not allow us to make reference to an "non-existing" table!
>
> Is there any work-around on this issue?

I had the same problem.  You can play around with creating the tables
without the RI checks and adding the constraints later, but I found this
clumsy.

The solution I used was to have a third table with attributes referencing
keys from table_1 and table_2.  This avoids the problem of inserting
references to keys which don't exist yet, and you can use joins from
table_[12] to table_3 to do the same queries you would have done with the
cross-referential tables.
--
Tod McQuillin

Re: Is cycle references?

From
Jan Wieck
Date:
Tod McQuillin wrote:
> On Wed, 1 Nov 2000 limin@www.pumpkinnet.com wrote:
>
> > The db schema we have need to do a cycle references.  I.e.,
> > an attribute in table_1 references the key in table_2 and
> > an attribute in table_2 references the key in table_1.  However,
> > PostgreSQL does not allow us to make reference to an "non-existing" table!
> >
> > Is there any work-around on this issue?
>
> I had the same problem.  You can play around with creating the tables
> without the RI checks and adding the constraints later, but I found this
> clumsy.
>
> The solution I used was to have a third table with attributes referencing
> keys from table_1 and table_2.  This avoids the problem of inserting
> references to keys which don't exist yet, and you can use joins from
> table_[12] to table_3 to do the same queries you would have done with the
> cross-referential tables.

    One of the reasons to setup referential integrity constraints
    is to avoid inserting references to keys that don't exist, so
    getting  around it with your 3-table setup looks a little odd
    to me.

    You'd better think about declaring your constraints INITIALLY
    DEFERRED  and  cover  the  key-  and  reference-insertions by
    proper transaction blocks.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #