deleting a foreign key that has no references

On 3/19/07, Glen W. Mabey <Glen.Mabey@swri.org> wrote:

But, really, I just want to be able to test to see how many references
there are to a key. Is there some way to do that?

write a triggers which do that.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

On Mon, Mar 19, 2007 at 04:51:57PM +0100, hubert depesz lubaczewski wrote:

On 3/19/07, Glen W. Mabey <Glen.Mabey@swri.org> wrote:

I'm using 8.1.8, and I have a situation where a record in one table
is
only meaningful when it is referenced via foreign key by one or more
records in any one of several tables.

So, really what I want is when one of the referring records is
deleted,
to have a trigger check to see if it was the last one to use that
foreign key, and if so, to delete that other record, too.

My first implementation of this functionality was to write a trigger
function that executed a COUNT(*) on all of the tables that could
have a
reference in them. That became way too slow for the number of
records
in these tables.

Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the
foreign
key constraint, and then trying to catch the exception thrown when a
deletion attempt is made on the record. However, it seems that this
PL/pgsql snippet fails to catch such an error:

BEGIN EXCEPTION
WHEN RAISE_EXCEPTION THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;

But, really, I just want to be able to test to see how many
references there are to a key. Is there
some way to do that?

write a triggers which do that.

I understand that a trigger should be written, and I have already
implemented two such triggers, as described above.

What I'm hoping to find out is whether there is some way to directly
find out how many (using a SELECT query) references there are to a key.

Glen

On Mon, 19 Mar 2007, Glen W. Mabey wrote:

Hello,

I'm using 8.1.8, and I have a situation where a record in one table is
only meaningful when it is referenced via foreign key by one or more
records in any one of several tables.

So, really what I want is when one of the referring records is deleted,
to have a trigger check to see if it was the last one to use that
foreign key, and if so, to delete that other record, too.

My first implementation of this functionality was to write a trigger
function that executed a COUNT(*) on all of the tables that could have a
reference in them. That became way too slow for the number of records
in these tables.

Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the foreign
key constraint, and then trying to catch the exception thrown when a
deletion attempt is made on the record. However, it seems that this
PL/pgsql snippet fails to catch such an error:

BEGIN EXCEPTION
WHEN RAISE_EXCEPTION THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;

Was that the actual function you used or just a shortened version? A
function like that with a delete of the referenced table in the body for
the appropriate key appeared to have reasonable behavior on my 8.2 system
with an immediate constraint, but I didn't do very much testing. One issue
is that to test the insert of a row into the referenced table you'd
probably need to defer a check that the row is referenced in order to have
time to insert referencing rows.

But, really, I just want to be able to test to see how many references
there are to a key. Is there some way to do that?

Currently, not apart from selecting on the referencing table.

"Glen W. Mabey" <Glen.Mabey@swri.org> writes:

What I'm hoping to find out is whether there is some way to directly
find out how many (using a SELECT query) references there are to a key.

There's no hidden shortcut for that, no.

regards, tom lane

Glen W. Mabey wrote:

On Mon, Mar 19, 2007 at 04:51:57PM +0100, hubert depesz lubaczewski wrote:

On 3/19/07, Glen W. Mabey <Glen.Mabey@swri.org> wrote:

I'm using 8.1.8, and I have a situation where a record in one table
is
only meaningful when it is referenced via foreign key by one or more
records in any one of several tables.

So, really what I want is when one of the referring records is
deleted,
to have a trigger check to see if it was the last one to use that
foreign key, and if so, to delete that other record, too.

My first implementation of this functionality was to write a trigger
function that executed a COUNT(*) on all of the tables that could
have a
reference in them. That became way too slow for the number of
records
in these tables.

Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the
foreign
key constraint, and then trying to catch the exception thrown when a
deletion attempt is made on the record. However, it seems that this
PL/pgsql snippet fails to catch such an error:

BEGIN EXCEPTION
WHEN RAISE_EXCEPTION THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;

But, really, I just want to be able to test to see how many
references there are to a key. Is there
some way to do that?

write a triggers which do that.

I understand that a trigger should be written, and I have already
implemented two such triggers, as described above.

What I'm hoping to find out is whether there is some way to directly
find out how many (using a SELECT query) references there are to a key.

This query will return the list of foreign keys which refer to primary keys:

SELECT
g as "DB",n.nspname as "PK_schema",pc.relname as
"PK_table",pa.attname as "PK_column",
n.nspname as "FK_schema",c.relname as "FK_table",a.attname as
"FK_column",b.n as "FK_column_number", f.conname as "FK_name",
pr.conname as "PK_name"
FROM
current_database()g,pg_catalog.pg_attribute a,pg_catalog.pg_attribute
pa,pg_catalog.pg_class c,pg_catalog.pg_class pc,pg_catalog.pg_namespace n,
pg_catalog.pg_namespace pn,pg_catalog.pg_constraint f left join
pg_catalog.pg_constraint pr on(f.conrelid=pr.conrelid and pr.contype='p'),
(SELECT * FROM
generate_series(1,current_setting('max_index_keys')::int,1))b(n)
WHERE
n.oid=c.relnamespace AND pn.oid=pc.relnamespace AND pc.oid=f.confrelid
AND c.oid=f.conrelid AND pa.attrelid=f.confrelid AND a.attrelid=f.conrelid
AND pa.attnum=f.confkey[b.n]AND a.attnum=f.conkey[b.n]AND
f.contype='f'AND f.conkey[b.n]<>0 AND has_schema_privilege(n.oid,
'USAGE'::text);

Add conditions to the pr.conname and you will get what you need

On 3/19/07, Glen W. Mabey <Glen.Mabey@swri.org> wrote:

write a triggers which do that.

I understand that a trigger should be written, and I have already
implemented two such triggers, as described above.

no, i think i didn't make myself clear.
let's use this situation:
we have tables:
create table x (id serial primary key, some_text text);
create table y (id serial primary key, x_id int4 not null references x
(id), some_field text);
where table x is your table in which you want to make some deletes,
and table y is some table that has foreign key to it.
now, you add to table x a field:
alter table x add column refcount int4 not null default 0;

and then we add a trigger:
CREATE OR REPLACE FUNCTION some_trg() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE x SET refcount = refcount + 1 WHERE id = NEW.x_id;
ELSIF TG_OP = 'UPDATE' THEN
IF NEW.x_id <> OLD.x_id THEN
UPDATE x SET refcount = refcount + 1 WHERE id = NEW.x_id;
UPDATE x SET refcount = refcount - 1 WHERE id = OLD.x_id;
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE x SET refcount = refcount - 1 WHERE id = OLD.x_id;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER some_trg AFTER INSERT OR UPDATE OR DELETE ON y FOR EACH
ROW EXECUTE PROCEDURE some_trg();

then - you have to populate the refcount field with current value, but
this is easily doable, and as far as i know you already are doing it
in your code.

so - the trigger keeps the refcount up to date. it is quite
lightweight, so shouldn't be a problem. and what's more important -
size of the table trigger is on doesn't matter.

simple, and working.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

On Mon, Mar 19, 2007 at 09:46:22AM -0700, Stephan Szabo wrote:

On Mon, 19 Mar 2007, Glen W. Mabey wrote:

Hello,

I'm using 8.1.8, and I have a situation where a record in one table is
only meaningful when it is referenced via foreign key by one or more
records in any one of several tables.

So, really what I want is when one of the referring records is deleted,
to have a trigger check to see if it was the last one to use that
foreign key, and if so, to delete that other record, too.

My first implementation of this functionality was to write a trigger
function that executed a COUNT(*) on all of the tables that could have a
reference in them. That became way too slow for the number of records
in these tables.

Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the foreign
key constraint, and then trying to catch the exception thrown when a
deletion attempt is made on the record. However, it seems that this
PL/pgsql snippet fails to catch such an error:

BEGIN EXCEPTION
WHEN RAISE_EXCEPTION THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;

Was that the actual function you used or just a shortened version? A
function like that with a delete of the referenced table in the body for
the appropriate key appeared to have reasonable behavior on my 8.2 system
with an immediate constraint, but I didn't do very much testing. One issue
is that to test the insert of a row into the referenced table you'd
probably need to defer a check that the row is referenced in order to have
time to insert referencing rows.

Okay, it turns out that I only had not implemented the exception catch
appropriately. Here's what worked:

BEGIN
DELETE FROM "Cuts" WHERE "Cuts".id = OLD.cut_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
RETURN NULL;

andyk: Thank you for the SELECT string you contributed. Unfortunately,
I could not understand what it was doing -- it was way over my head WRT
psql proficiency. So, I don't know whether it would have worked.

At any rate, thank you all for your suggestions. Testing for an error
seems to be the simplest and easiest way to accomplish what I need to
do, and it seems to be fairly fast, too.

Best Regards,
Glen Mabey

On Mon, 2007-03-19 at 11:12 -0500, Glen W. Mabey wrote:

What I'm hoping to find out is whether there is some way to directly
find out how many (using a SELECT query) references there are to a
key.

In the easy case when your schema doesn't change often, you can just
hard code a query of the FK tables and add up the row counts. I bet
something like 'select count(*) from (select * from FKtable1 UNION ALL
select * from FKtable2 ... )' will work (and I'm guessing that the UNION
ALL will optimize well). Obviously, you want indexes on the FKs.

The harder and more general case is to build such a query dynamically
from pg_depends. A good start would be to write a function that returns
an sql query like the above to count the referents of PKtable(PKcolumn).
If you can declare this function stable or immutable (I'm not sure of
this), then it might not be too painful to generate the query within the
trigger itself. Otherwise, you might have to store/update these queries
in a separate table after every DDL change.

See the pg_depends documentation at
http://www.postgresql.org/docs/8.2/interactive/catalog-pg-depend.html

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.

On Mon, 2007-03-19 at 13:03 -0700, Reece Hart wrote:

The harder and more general case is to build such a query dynamically
from pg_depends

...

See the pg_depends documentation at

Apologies. I intended to write pg_constraint and the documentation at
http://www.postgresql.org/docs/8.2/interactive/catalog-pg-constraint.html

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.

Hi all,

I am inserting 783159 records but the insert is failing after 634314
records. I am getting "ERROR: could not open relation with OID
3221204992" message. I am using

1- PostgreSQL: 8.2.3
2- OS: Red Hat Enterprise Linux AS release 3.
3- Logfile output:
ERROR: XX000: could not open relation with OID 3221204992
LOCATION: relation_open, heapam.c:700
STATEMENT: INSERT INTO parcel (OBJECTID, shape) VALUES ( $1, $2 )

This is happening only on Linux , on Windows (pg 8.2.1), I can insert
all data.

I have gone through all the hints on the list for this thread and tried
most of them, like
- reindex pg_class
- set enable_indexscan = off;
- increase shared_buffers /temp_buffers
- SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE oid <=
3221204992 ORDER BY oid DESC LIMIT 6;

There is no temporary table and no table is being dropped / created.

No record is returned for
select oid, * from pg_class where oid = 3221204992;

Wondering if somebody have some other hints to resolve this problem?

Thanks.
Ale Raza.

araza@esri.com writes:

I am inserting 783159 records but the insert is failing after 634314
records. I am getting "ERROR: could not open relation with OID
3221204992" message. I am using

1- PostgreSQL: 8.2.3
2- OS: Red Hat Enterprise Linux AS release 3.
3- Logfile output:
ERROR: XX000: could not open relation with OID 3221204992
LOCATION: relation_open, heapam.c:700
STATEMENT: INSERT INTO parcel (OBJECTID, shape) VALUES ( $1, $2 )

Please provide more detail, like what the table schema is, what indexes
and foreign keys it has, and exactly what the insert process is
(eg do you have all these inserts wrapped in a BEGIN?). Also what is
the client-side software?

Is the OID mentioned in the complaint the same every time you try it?

regards, tom lane

Tom,

It's a C API client and inserts are not wrapped into a BEGIN/END.

Here is the schema info:

sde93=# \d parcel
Table "sde.parcel"
Column | Type | Modifiers
----------+-------------+-----------
objectid | integer | not null
shape | st_geometry |

st_geometry is our own implementation for geometry type. In a loading
mode it has no index. Initially, when table is created, a GiST index is
created for shape and a btree for objectid but both are dropped for
loading.

Client is reading data from ESRI personal GeoDatabase and inserting into
parcel table.

The OID is same every time.

Thanks.
Ale.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, March 21, 2007 4:15 PM
To: Ale Raza
Cc: postgresql-general
Subject: Re: [GENERAL] Insert fail: could not open relation with OID
3221204992

araza@esri.com writes:

I am inserting 783159 records but the insert is failing after 634314
records. I am getting "ERROR: could not open relation with OID
3221204992" message. I am using

1- PostgreSQL: 8.2.3
2- OS: Red Hat Enterprise Linux AS release 3.
3- Logfile output:
ERROR: XX000: could not open relation with OID 3221204992
LOCATION: relation_open, heapam.c:700
STATEMENT: INSERT INTO parcel (OBJECTID, shape) VALUES ( $1, $2 )

Please provide more detail, like what the table schema is, what indexes
and foreign keys it has, and exactly what the insert process is
(eg do you have all these inserts wrapped in a BEGIN?). Also what is
the client-side software?

Is the OID mentioned in the complaint the same every time you try it?

regards, tom lane

araza@esri.com writes:

Here is the schema info:
...
shape | st_geometry |

st_geometry is our own implementation for geometry type.

To be blunt, I'd suggest looking there first. Can you duplicate the
failure when loading into a table with no custom datatype?

regards, tom lane

Tom,

No problem without shape column. I can load all data.

# SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE oid <=
3221204992 ORDER BY oid DESC LIMIT 6;
oid | relname | relkind
-------+----------------------+---------
78036 | pg_toast_78032_index | i
78034 | pg_toast_78032 | t
78032 | parcel | r
78031 | pg_toast_78027_index | i
78029 | pg_toast_78027 | t
78027 | parcel_t | r
(6 rows)

Table parcel_t: without shape.
Table parcel: with shape.

My concern is:
- Why it's an issue on Linux not on windows? In both cases it's a window
client.
- What shape column making insert fail?

Ale.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 22, 2007 8:02 AM
To: Ale Raza
Cc: postgresql-general
Subject: Re: [GENERAL] Insert fail: could not open relation with OID
3221204992

araza@esri.com writes:

Here is the schema info:
...
shape | st_geometry |

st_geometry is our own implementation for geometry type.

To be blunt, I'd suggest looking there first. Can you duplicate the
failure when loading into a table with no custom datatype?

regards, tom lane

"Ale Raza" <araza@esri.com> writes:

No problem without shape column. I can load all data.

What I thought :-(

My concern is:
- Why it's an issue on Linux not on windows? In both cases it's a window
client.

Platform-dependent bug in your code, likely. Without seeing the code
it's impossible to speculate much further, but I'd look first for places
that scribble on memory not allocated to you (perhaps due to a
miscalculation of the size needed for a dynamically-allocated object).

regards, tom lane

Tom,

The _Recv function is not receiving the data when client is sending
bytea of size 211k.

Here is the stack:

Breakpoint 3, #####_Recv (fcinfo=0xbfffa3a0) at binary.c:138
138 StringInfo src_buf = (StringInfo)
PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
(gdb) p *src_buf
Cannot access memory at address 0x0
(gdb) n
0x0819c4f8 in PostgresMain ()
(gdb) bt
#0 0x0819c4f8 in PostgresMain ()
#1 0x0817a4d9 in BackendRun ()
#2 0x08179e03 in BackendStartup ()
#3 0x0817825f in ServerLoop ()
#4 0x0817785b in PostmasterMain ()
#5 0x0813fd1c in main ()
(gdb) c
Continuing.

Am I missing some parameters or it's a bug?

Thanks.

Ale.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 22, 2007 11:41 AM
To: Ale Raza
Cc: postgresql-general
Subject: Re: [GENERAL] Insert fail: could not open relation with OID
3221204992

"Ale Raza" <araza@esri.com> writes:

No problem without shape column. I can load all data.

What I thought :-(

My concern is:
- Why it's an issue on Linux not on windows? In both cases it's a

window

client.

Platform-dependent bug in your code, likely. Without seeing the code
it's impossible to speculate much further, but I'd look first for places
that scribble on memory not allocated to you (perhaps due to a
miscalculation of the size needed for a dynamically-allocated object).

regards, tom lane

"Ale Raza" <araza@esri.com> writes:

Breakpoint 3, #####_Recv (fcinfo=0xbfffa3a0) at binary.c:138
138 StringInfo src_buf = (StringInfo)
PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
(gdb) p *src_buf
Cannot access memory at address 0x0

At the point where you've stopped, src_buf hasn't been assigned to yet.

regards, tom lane

" ....src_buf hasn't been assigned to yet. ..."

May be copy/paste issue. It has been assigned. Here is the complete stack for bytea size 7480 and 211758.The later fail.

Pass (bytea size = 7480):
Breakpoint 1, ST_Geometry_Recv (fcinfo=0xbfffcb90) at binary.c:138
138 StringInfo src_buf = (StringInfo) PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
(gdb) p *src_buf
$2 = {data = 0xff0c8d8b <Address 0xff0c8d8b out of bounds>, len = -1031143425, maxlen = -13595335, cursor = 158662655}
(gdb) n
139 SE_ST_GEOMETRY *result = NULL;
(gdb) p *src_buf
$3 = {data = 0x84ce6b2 "º\022\b\003", len = 7480, maxlen = 7481, cursor = 0}
(gdb) bt
#0 ST_Geometry_Recv (fcinfo=0xbfffcb90) at binary.c:139
#1 0x08209ebd in ReceiveFunctionCall ()
#2 0x08209fde in OidReceiveFunctionCall ()
#3 0x0819a5a0 in exec_bind_message ()
#4 0x0819c6e6 in PostgresMain ()
#5 0x0817a4d9 in BackendRun ()
#6 0x08179e03 in BackendStartup ()
#7 0x0817825f in ServerLoop ()
#8 0x0817785b in PostmasterMain ()
#9 0x0813fd1c in main ()
(gdb) c
Continuing.

Fail (bytea size = 211758):
Breakpoint 1, ST_Geometry_Recv (fcinfo=0xbfffcb90) at binary.c:138
138 StringInfo src_buf = (StringInfo) PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
(gdb) p *src_buf
$4 = {data = 0xff0c8d8b <Address 0xff0c8d8b out of bounds>, len = -1031143425, maxlen = -13595335, cursor = 158662655}
(gdb) n
0x0819c4f8 in PostgresMain ()
(gdb) p *src_buf
No symbol "src_buf" in current context.
(gdb) bt
#0 0x0819c4f8 in PostgresMain ()
#1 0x0817a4d9 in BackendRun ()
#2 0x08179e03 in BackendStartup ()
#3 0x0817825f in ServerLoop ()
#4 0x0817785b in PostmasterMain ()
#5 0x0813fd1c in main ()
(gdb)

The pointer moves to PostgresMain (), as soon as I move to next.

Ale.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, March 23, 2007 2:20 PM
To: Ale Raza
Cc: postgresql-general
Subject: Re: [GENERAL] Insert fail: could not open relation with OID 3221204992

"Ale Raza" <araza@esri.com> writes:

Breakpoint 3, #####_Recv (fcinfo=0xbfffa3a0) at binary.c:138
138 StringInfo src_buf = (StringInfo)
PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
(gdb) p *src_buf
Cannot access memory at address 0x0

At the point where you've stopped, src_buf hasn't been assigned to yet.

regards, tom lane

"Ale Raza" <araza@esri.com> writes:

Breakpoint 1, ST_Geometry_Recv (fcinfo=0xbfffcb90) at binary.c:138
138 StringInfo src_buf = (StringInfo) PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
(gdb) p *src_buf
$4 = {data = 0xff0c8d8b <Address 0xff0c8d8b out of bounds>, len = -1031143425, maxlen = -13595335, cursor = 158662655}
(gdb) n
0x0819c4f8 in PostgresMain ()
(gdb) p *src_buf
No symbol "src_buf" in current context.

Try recompiling with a lower optimization level (maybe even -O0) so you
can debug. gdb sometimes gets confused by optimized code...

regards, tom lane