core dump? OID/database corruption?
An obscure series of events seems to cause a core dump and OID
corruption:
-- tolower function for varchar
create function varchar_lower(varchar) returns varchar
as '/usr/local/lib/pgcontains.so', 'pglower'
language 'c';
create index ztables_title_ndx on ztitles ( varchar_lower (title) ) ;
vacuum analyze ;
{ leave }
at some point come back
drop function varchar_lower (varchar) ;
create function varchar_lower(varchar) returns varchar
as '/usr/local/lib/pgcontains.so', 'pglower'
language 'c';
and strange things start to happen.
I realize that (and only belatedly) once I drop the function the index
is corrupt, but it seems there are invalid oids when I try to dump the
database, and dumping some tables caused a core dump.
I didn't save the data, I was in live service panic mode.
I have a shared library of functions I use in Postgres and I do a drop /
create for an install script. I realize this is a little indiscriminate,
and at least unwise, but I think postgres should be able to handle this.
Given the name of a table, I need to find all foreign keys in that table
and the table/column that they refer to, along with the action to be
performed on update/delete. The following query works, but only when
there is 1 foreign key in the table, when there is more than 2 it grows
exponentially -- which means I am missing a join. However, given my
limitied knowledge about the layouts of the postgres system tables, and
the pg_trigger not being documented on the web site, I have been unable to
get the correct query. Is this possible, and if so, what join(s) am I
missing?
SELECT pt.tgargs,
pt.tgnargs,
pt.tgdeferrable,
pt.tginitdeferred,
pg_proc.proname,
pg_proc_1.proname
FROM pg_class pc,
pg_proc pg_proc,
pg_proc pg_proc_1,
pg_trigger pg_trigger,
pg_trigger pg_trigger_1,
pg_proc pp,
pg_trigger pt
WHERE pt.tgrelid = pc.oid
AND pp.oid = pt.tgfoid
AND pg_trigger.tgconstrrelid = pc.oid
AND pg_proc.oid = pg_trigger.tgfoid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pc.oid
AND ((pc.relname='tblmidterm')
AND (pp.proname LIKE '%ins')
AND (pg_proc.proname LIKE '%upd')
AND (pg_proc_1.proname LIKE '%del'))
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
mlw <markw@mohawksoft.com> writes:
[ drop function on which a functional index is based ]
and strange things start to happen.
All I get is messages like
ERROR: fmgr_info: function 402432: cache lookup failed
which is about what I'd expect. If you've seen a coredump in
this situation, let's hear a more specific bug report.
regards, tom lane
There ya go, I figured it out :) Given the name a table, this query will
return all foreign keys in that table, the table the primary key is in,
the name of the primary key, if the are deferrable, if the are initially
deffered, and the action to be performed (RESTRICT, SET NULL, etc.). To
get the foreign keys and primary keys and tables, you must parse the
null-terminated pg.tgargs.
When I get the equivalent query working for primary keys I will send it
your way -- or if you beat me to it, send it my way (I am working on some
missing functionality from the ODBC driver)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
SELECT pt.tgargs,
pt.tgnargs,
pt.tgdeferrable,
pt.tginitdeferred,
pg_proc.proname,
pg_proc_1.proname
FROM pg_class pc,
pg_proc pg_proc,
pg_proc pg_proc_1,
pg_trigger pg_trigger,
pg_trigger pg_trigger_1,
pg_proc pp,
pg_trigger pt
WHERE pt.tgrelid = pc.oid
AND pp.oid = pt.tgfoid
AND pg_trigger.tgconstrrelid = pc.oid
AND pg_proc.oid = pg_trigger.tgfoid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pc.oid
AND ((pc.relname='<<FOREIGN TABLE>>')
AND (pp.proname LIKE '%%ins')
AND (pg_proc.proname LIKE '%%upd')
AND (pg_proc_1.proname LIKE '%%del')
AND (pg_trigger.tgrelid=pt.tgconstrrelid)
AND (pg_trigger_1.tgrelid = pt.tgconstrrelid))
On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote:
Show quoted text
Hi Michael,
I am on the phpPgAdmin development team, and I have been wanting to add this
functionality to phpPgAdmin. I will start working with your query as soon
as possible, and I will use phpPgAdmin as a testbed for the functionality.I really appreciate having your query as a working basis, because it's
really hard trying to figure out the system tables!Chris
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Michael Fork
Sent: Sunday, December 03, 2000 12:23 PM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc.Given the name of a table, I need to find all foreign keys in that table
and the table/column that they refer to, along with the action to be
performed on update/delete. The following query works, but only when
there is 1 foreign key in the table, when there is more than 2 it grows
exponentially -- which means I am missing a join. However, given my
limitied knowledge about the layouts of the postgres system tables, and
the pg_trigger not being documented on the web site, I have been unable to
get the correct query. Is this possible, and if so, what join(s) am I
missing?SELECT pt.tgargs,
pt.tgnargs,
pt.tgdeferrable,
pt.tginitdeferred,
pg_proc.proname,
pg_proc_1.proname
FROM pg_class pc,
pg_proc pg_proc,
pg_proc pg_proc_1,
pg_trigger pg_trigger,
pg_trigger pg_trigger_1,
pg_proc pp,
pg_trigger pt
WHERE pt.tgrelid = pc.oid
AND pp.oid = pt.tgfoid
AND pg_trigger.tgconstrrelid = pc.oid
AND pg_proc.oid = pg_trigger.tgfoid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pc.oid
AND ((pc.relname='tblmidterm')
AND (pp.proname LIKE '%ins')
AND (pg_proc.proname LIKE '%upd')
AND (pg_proc_1.proname LIKE '%del'))Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
Import Notes
Resolved by subject fallback
Here's the query that, given the primary key table, lists all foreign
keys, their tables, the RI type, and defereability.
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
SELECT pg_trigger.tgargs,
pg_trigger.tgnargs,
pg_trigger.tgdeferrable,
pg_trigger.tginitdeferred,
pg_proc.proname,
pg_proc_1.proname
FROM pg_class pg_class,
pg_class pg_class_1,
pg_class pg_class_2,
pg_proc pg_proc,
pg_proc pg_proc_1,
pg_trigger pg_trigger,
pg_trigger pg_trigger_1,
pg_trigger pg_trigger_2
WHERE pg_trigger.tgconstrrelid = pg_class.oid
AND pg_trigger.tgrelid = pg_class_1.oid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pg_class_1.oid
AND pg_trigger_2.tgconstrrelid = pg_class_2.oid
AND pg_trigger_2.tgfoid = pg_proc.oid
AND pg_class_2.oid = pg_trigger.tgrelid
AND ((pg_class.relname='<<PRIMARY KEY TABLE>>')
AND (pg_proc.proname Like '%upd')
AND (pg_proc_1.proname Like '%del')
AND (pg_trigger_1.tgrelid=pg_trigger.tgconstrrelid)
AND (pg_trigger_2.tgrelid = pg_trigger.tgconstrrelid))
On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote:
Show quoted text
Thanks mike - chances are it will be committed to phpPgAdmin by the end of
the week!BTW, you may wish to make sure that your email as cc'd to the hacker's list
as well.Regards,
Chris
--
Christopher Kings-Lynne
Family Health Network (ACN 089 639 243)-----Original Message-----
From: Michael Fork [mailto:mfork@toledolink.com]
Sent: Tuesday, December 05, 2000 12:25 PM
To: Christopher Kings-Lynne
Subject: RE: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc.There ya go, I figured it out :) Given the name a table, this query will
return all foreign keys in that table, the table the primary key is in,
the name of the primary key, if the are deferrable, if the are initially
deffered, and the action to be performed (RESTRICT, SET NULL, etc.). To
get the foreign keys and primary keys and tables, you must parse the
null-terminated pg.tgargs.When I get the equivalent query working for primary keys I will send it
your way -- or if you beat me to it, send it my way (I am working on some
missing functionality from the ODBC driver)Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo OhioSELECT pt.tgargs,
pt.tgnargs,
pt.tgdeferrable,
pt.tginitdeferred,
pg_proc.proname,
pg_proc_1.proname
FROM pg_class pc,
pg_proc pg_proc,
pg_proc pg_proc_1,
pg_trigger pg_trigger,
pg_trigger pg_trigger_1,
pg_proc pp,
pg_trigger pt
WHERE pt.tgrelid = pc.oid
AND pp.oid = pt.tgfoid
AND pg_trigger.tgconstrrelid = pc.oid
AND pg_proc.oid = pg_trigger.tgfoid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pc.oid
AND ((pc.relname='<<FOREIGN TABLE>>')
AND (pp.proname LIKE '%%ins')
AND (pg_proc.proname LIKE '%%upd')
AND (pg_proc_1.proname LIKE '%%del')
AND (pg_trigger.tgrelid=pt.tgconstrrelid)
AND (pg_trigger_1.tgrelid = pt.tgconstrrelid))On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote:
Hi Michael,
I am on the phpPgAdmin development team, and I have been
wanting to add this
functionality to phpPgAdmin. I will start working with your
query as soon
as possible, and I will use phpPgAdmin as a testbed for the
functionality.
I really appreciate having your query as a working basis, because it's
really hard trying to figure out the system tables!Chris
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Michael Fork
Sent: Sunday, December 03, 2000 12:23 PM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc.Given the name of a table, I need to find all foreign keys in
that table
and the table/column that they refer to, along with the action to be
performed on update/delete. The following query works, but only when
there is 1 foreign key in the table, when there is more than2 it grows
exponentially -- which means I am missing a join. However, given my
limitied knowledge about the layouts of the postgres systemtables, and
the pg_trigger not being documented on the web site, I have
been unable to
get the correct query. Is this possible, and if so, what join(s) am I
missing?SELECT pt.tgargs,
pt.tgnargs,
pt.tgdeferrable,
pt.tginitdeferred,
pg_proc.proname,
pg_proc_1.proname
FROM pg_class pc,
pg_proc pg_proc,
pg_proc pg_proc_1,
pg_trigger pg_trigger,
pg_trigger pg_trigger_1,
pg_proc pp,
pg_trigger pt
WHERE pt.tgrelid = pc.oid
AND pp.oid = pt.tgfoid
AND pg_trigger.tgconstrrelid = pc.oid
AND pg_proc.oid = pg_trigger.tgfoid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pc.oid
AND ((pc.relname='tblmidterm')
AND (pp.proname LIKE '%ins')
AND (pg_proc.proname LIKE '%upd')
AND (pg_proc_1.proname LIKE '%del'))Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
Import Notes
Resolved by subject fallback