Thread: Looking for foreign key guinea pigs

 I'm looking to see if anyone has
a) a development/testing server they don't mind
  testing very early pre-development stuff on
b) foreign key systems (using no action) that
  currently have deadlocking or waiting problems
c) can run realistic requests that have deadlocked
  the current triggers
d) willingness to help with debugging

I'm in very preliminary stages of trying out the
dirty read-for update barrier code in a fashion
that appears to work better in regards to locking
than what we currently have, but I only have
simple tests to run from multiple psqls
interactively right now.

I'm not sure that the no action cases (all I've
converted at the moment) are working 100% to
both not lock as badly and still maintain the
correct semantics and looking for some brave
souls to help. :)

In any case, I'm still days (at the 1/2 hour
a day I tend to get to work on it) away from
having a patch form I'm comfortable with.

If anyone's interested in helping reply here
or directly. :)

Hi Stephan,

Sounds good. I might be able to help you out with those tests as I'd
love this problem to be fixed. I actually removed all RI and replaced
them with triggers because of the problems I was having. I might be a
bit slow getting back to you each time though as my plate is pretty
full. You can grab me off-list for more info.

Tom.

On Wed, 2002-10-16 at 12:25, Stephan Szabo wrote:
>  I'm looking to see if anyone has
> a) a development/testing server they don't mind
>   testing very early pre-development stuff on
> b) foreign key systems (using no action) that
>   currently have deadlocking or waiting problems
> c) can run realistic requests that have deadlocked
>   the current triggers
> d) willingness to help with debugging
>
> I'm in very preliminary stages of trying out the
> dirty read-for update barrier code in a fashion
> that appears to work better in regards to locking
> than what we currently have, but I only have
> simple tests to run from multiple psqls
> interactively right now.
>
> I'm not sure that the no action cases (all I've
> converted at the moment) are working 100% to
> both not lock as badly and still maintain the
> correct semantics and looking for some brave
> souls to help. :)
>
> In any case, I'm still days (at the 1/2 hour
> a day I tend to get to work on it) away from
> having a patch form I'm comfortable with.
>
> If anyone's interested in helping reply here
> or directly. :)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Thomas O'Dowd  - A Noop a day, helps you work rest and play!
tom@nooper.com - http://nooper.co.jp

Hi,

Can someone help me with this

During a Vacuum full analyse we are getting

2002-10-16 14:29:08 [18326]  ERROR:  _mdfd_getrelnfd: cannot open relation
pg_temp_15300_53: No such file or directory
2002-10-16 14:29:08 [18326]  DEBUG:  AbortCurrentTransaction
2002-10-16 14:29:08 [18326]  DEBUG:  proc_exit(0)
2002-10-16 14:29:08 [18326]  DEBUG:  shmem_exit(0)
2002-10-16 14:29:08 [18326]  DEBUG:  exit(0)

Thanks

Andrew

Hi,

Can someone help me with this

During a Vacuum full analyse we are getting

2002-10-16 14:29:08 [18326]  ERROR:  _mdfd_getrelnfd: cannot open relation
pg_temp_15300_53: No such file or directory
2002-10-16 14:29:08 [18326]  DEBUG:  AbortCurrentTransaction
2002-10-16 14:29:08 [18326]  DEBUG:  proc_exit(0)
2002-10-16 14:29:08 [18326]  DEBUG:  shmem_exit(0)
2002-10-16 14:29:08 [18326]  DEBUG:  exit(0)

Thanks

Andrew

Hi,

Can someone help me with this

During a Vacuum full analyse we are getting

2002-10-16 14:29:08 [18326]  ERROR:  _mdfd_getrelnfd: cannot open relation
pg_temp_15300_53: No such file or directory
2002-10-16 14:29:08 [18326]  DEBUG:  AbortCurrentTransaction
2002-10-16 14:29:08 [18326]  DEBUG:  proc_exit(0)
2002-10-16 14:29:08 [18326]  DEBUG:  shmem_exit(0)
2002-10-16 14:29:08 [18326]  DEBUG:  exit(0)

Thanks

Andrew

Is there something I can do about this without manually creating the
relfilenode file in /base/?


----- Original Message -----
From: "Andrew Bartley" <abartley@evolvosystems.com>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, October 16, 2002 2:45 PM
Subject: [GENERAL] ERROR: _mdfd_getrelnfd: cannot open relation
pg_temp_15300_53: No such file or directory


> Hi,
>
> Can someone help me with this
>
> During a Vacuum full analyse we are getting
>
> 2002-10-16 14:29:08 [18326]  ERROR:  _mdfd_getrelnfd: cannot open relation
> pg_temp_15300_53: No such file or directory
> 2002-10-16 14:29:08 [18326]  DEBUG:  AbortCurrentTransaction
> 2002-10-16 14:29:08 [18326]  DEBUG:  proc_exit(0)
> 2002-10-16 14:29:08 [18326]  DEBUG:  shmem_exit(0)
> 2002-10-16 14:29:08 [18326]  DEBUG:  exit(0)
>
> Thanks
>
> Andrew
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Hi all,

I still need help with this.

Is there someone who knows how to fix this?

Thanks

Andrew
----- Original Message -----
From: "Andrew Bartley" <abartley@evolvosystems.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, October 18, 2002 1:08 PM
Subject: Re: [GENERAL] ERROR: _mdfd_getrelnfd: cannot open relation
pg_temp_15300_53: No such file or directory


> Is there something I can do about this without manually creating the
> relfilenode file in /base/?
>
>
> ----- Original Message -----
> From: "Andrew Bartley" <abartley@evolvosystems.com>
> To: <pgsql-general@postgresql.org>
> Sent: Wednesday, October 16, 2002 2:45 PM
> Subject: [GENERAL] ERROR: _mdfd_getrelnfd: cannot open relation
> pg_temp_15300_53: No such file or directory
>
>
> > Hi,
> >
> > Can someone help me with this
> >
> > During a Vacuum full analyse we are getting
> >
> > 2002-10-16 14:29:08 [18326]  ERROR:  _mdfd_getrelnfd: cannot open
relation
> > pg_temp_15300_53: No such file or directory
> > 2002-10-16 14:29:08 [18326]  DEBUG:  AbortCurrentTransaction
> > 2002-10-16 14:29:08 [18326]  DEBUG:  proc_exit(0)
> > 2002-10-16 14:29:08 [18326]  DEBUG:  shmem_exit(0)
> > 2002-10-16 14:29:08 [18326]  DEBUG:  exit(0)
> >
> > Thanks
> >
> > Andrew
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

"Andrew Bartley" <abartley@evolvosystems.com> writes:
> Is there someone who knows how to fix this?
>
> 2002-10-16 14:29:08 [18326]  ERROR:  _mdfd_getrelnfd: cannot open
> relation
> pg_temp_15300_53: No such file or directory

Depending on which PG version you are running, you may be able to just
drop that temp table.  If that doesn't work, create a dummy file to
match it, and then drop it.  You need a command like

    touch $PGDATA/base/DBOID/FILENODE

where DBOID is the OID of your database as seen in pg_database, and
FILENODE is the pg_class.relfilenode value for the temp table.

            regards, tom lane

Thanks Tom,

I have already done this on our local site.

We have one remote site I do not have access to.

We are able to apply patches to this site only.  Is there an automated way
of doing this.

I have tried to but together a shell script to touch these files.  But it
seems that the table in question has a second pg_toast oid associated with
it.

How do I find this relation in the pg_ tables?

The version of postgres we are running is 7.2

Thanks

Andrew




----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrew Bartley" <abartley@evolvosystems.com>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, October 22, 2002 8:36 AM
Subject: Re: [GENERAL] ERROR: _mdfd_getrelnfd: cannot open relation
pg_temp_15300_53: No such file or directory


> "Andrew Bartley" <abartley@evolvosystems.com> writes:
> > Is there someone who knows how to fix this?
> >
> > 2002-10-16 14:29:08 [18326]  ERROR:  _mdfd_getrelnfd: cannot open
> > relation
> > pg_temp_15300_53: No such file or directory
>
> Depending on which PG version you are running, you may be able to just
> drop that temp table.  If that doesn't work, create a dummy file to
> match it, and then drop it.  You need a command like
>
> touch $PGDATA/base/DBOID/FILENODE
>
> where DBOID is the OID of your database as seen in pg_database, and
> FILENODE is the pg_class.relfilenode value for the temp table.
>
> regards, tom lane
>
>

"Andrew Bartley" <abartley@evolvosystems.com> writes:
> I have tried to but together a shell script to touch these files.  But it
> seems that the table in question has a second pg_toast oid associated with
> it.

That's interesting ... if both those physical files went away while the
pg_class rows remained, it suggests some systematic problem rather than
just a random glitch.  I doubt we'll be able to learn anything about the
cause if we're not allowed into the database machine though :-(

Anyway, the pg_class.reltoastrelid field for the temp table gives you
the OID of the pg_class row for its TOAST table, from which you can get
the relfilenode value to touch for the TOAST table.

Very likely, the TOAST table's index has got the same disease: to find
it, get the reltoastidxid field from the TOAST table's pg_class row, and
again go to that OID in pg_class to see its relfilenode value.

(Actually, in 7.2 you'll almost certainly find that relfilenode of each
of these rows matches its OID, but to be perfectly safe you should look
up and use the relfilenode.)

            regards, tom lane

Thanks again.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrew Bartley" <abartley@evolvosystems.com>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, October 22, 2002 9:30 AM
Subject: Re: [GENERAL] ERROR: _mdfd_getrelnfd: cannot open relation
pg_temp_15300_53: No such file or directory


> "Andrew Bartley" <abartley@evolvosystems.com> writes:
> > I have tried to but together a shell script to touch these files.  But
it
> > seems that the table in question has a second pg_toast oid associated
with
> > it.
>
> That's interesting ... if both those physical files went away while the
> pg_class rows remained, it suggests some systematic problem rather than
> just a random glitch.  I doubt we'll be able to learn anything about the
> cause if we're not allowed into the database machine though :-(
>
> Anyway, the pg_class.reltoastrelid field for the temp table gives you
> the OID of the pg_class row for its TOAST table, from which you can get
> the relfilenode value to touch for the TOAST table.
>
> Very likely, the TOAST table's index has got the same disease: to find
> it, get the reltoastidxid field from the TOAST table's pg_class row, and
> again go to that OID in pg_class to see its relfilenode value.
>
> (Actually, in 7.2 you'll almost certainly find that relfilenode of each
> of these rows matches its OID, but to be perfectly safe you should look
> up and use the relfilenode.)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>