Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7784312

Browse files
committed
Ok, attached is the lo type, which goes some way with Large Object
Orphaning that occurs with JDBC & ODBC.Contents:contrib/lo/Makefile contrib/lo/README contrib/lo/lo.c contrib/lo/lo.sql.inThese are just test stuff - not essentialcontrib/lo/test.sql contrib/lo/drop.sqlPeter Mount
1 parentd6e0ee6 commit7784312

File tree

6 files changed

+460
-0
lines changed

6 files changed

+460
-0
lines changed

‎contrib/lo/Makefile

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
#
2+
#PostgreSQL lo type
3+
#
4+
#Makefile pinched from the ip-mac contrib package
5+
#
6+
#$Id: Makefile,v 1.1 1998/06/16 07:07:11 momjian Exp $
7+
8+
SRCDIR= ../../src
9+
10+
include$(SRCDIR)/Makefile.global
11+
12+
CONTRIBDIR=$(LIBDIR)/modules
13+
14+
CFLAGS+=$(CFLAGS_SL) -I$(SRCDIR)/include
15+
16+
ifdefREFINT_VERBOSE
17+
CFLAGS+= -DREFINT_VERBOSE
18+
endif
19+
20+
TARGETS= lo$(DLSUFFIX) lo.sql
21+
22+
CLEANFILES+=$(TARGETS)
23+
24+
all::$(TARGETS)
25+
26+
install:: all$(CONTRIBDIR)
27+
forfin*$(DLSUFFIX);do$(INSTALL) -c$$f$(CONTRIBDIR)/$$f;done
28+
29+
$(CONTRIBDIR):
30+
mkdir -p$(CONTRIBDIR)
31+
32+
%.sql:%.sql.in
33+
rm -f$@;\
34+
C=`pwd`;\
35+
sed -e"s:_OBJWD_:$(CONTRIBDIR):g"\
36+
-e"s:_DLSUFFIX_:$(DLSUFFIX):g"<$<>$@
37+
38+
clean:
39+
rm -f$(TARGETS)*.o

‎contrib/lo/README

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
PostgreSQL type extension for managing Large Objects
2+
----------------------------------------------------
3+
4+
$Id: README,v 1.1 1998/06/16 07:07:11 momjian Exp $
5+
6+
Overview
7+
8+
One of the problems with the JDBC driver (and this affects the ODBC driver
9+
also), is that the specification assumes that references to BLOBS (Binary
10+
Large OBjectS) are stored within a table, and if that entry is changed, the
11+
associated BLOB is deleted from the database.
12+
13+
As PostgreSQL stands, this doesn't occur. It allocates an OID for each object,
14+
and it is up to the application to store, and ultimately delete the objects.
15+
16+
Now this is fine for new postgresql specific applications, but existing ones
17+
using JDBC or ODBC wont delete the objects, arising to orphaning - objects
18+
that are not referenced by anything, and simply occupy disk space.
19+
20+
The Fix
21+
22+
I've fixed this by creating a new data type 'lo', some support functions, and
23+
a Trigger which handles the orphaning problem.
24+
25+
The 'lo' type was created because we needed to differenciate between normal
26+
Oid's and Large Objects. Currently the JDBC driver handles this dilema easily,
27+
but (after talking to Byron), the ODBC driver needed a unique type. They had created an 'lo' type, but not the solution to orphaning.
28+
29+
Install
30+
31+
Ok, first build the shared library, and install. Typing 'make install' in the
32+
contrib/lo directory should do it.
33+
34+
Then, as the postgres super user, run the lo.sql script. This will install the
35+
type, and define the support functions.
36+
37+
How to Use
38+
39+
The easiest way is by an example:
40+
41+
> create table image (title text,raster lo);
42+
> create trigger t_image before update or delete on image for each row execute procedure lo_manage(raster);
43+
44+
Here, a trigger is created for each column that contains a lo type.
45+
46+
Issues
47+
48+
* dropping a table will still orphan any objects it contains, as the trigger
49+
is not actioned.
50+
51+
For now, precede the 'drop table' with 'delete from {table}'. However, this
52+
could be fixed by having 'drop table' perform an additional
53+
54+
'select lo_unlink({colname}::oid) from {tablename}'
55+
56+
for each column, before actually dropping the table.
57+
58+
* Some frontends may create their own tables, and will not create the
59+
associated trigger(s). Also, users may not remember (or know) to create
60+
the triggers.
61+
62+
This can be solved, but would involve changes to the parser.
63+
64+
As the ODBC driver needs a permanent lo type (& JDBC could be optimised to
65+
use it if it's Oid is fixed), and as the above issues can only be fixed by
66+
some internal changes, I feel it should become a permanent built-in type.
67+
68+
I'm releasing this into contrib, just to get it out, and tested.
69+
70+
Peter Mount <peter@retep.org.uk> June 13 1998
71+

‎contrib/lo/drop.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
--
2+
-- This removes the type (and a test table)
3+
-- It's used just for development
4+
--
5+
6+
-- remove our test table
7+
droptable a;
8+
9+
-- now drop any sql based functions associated with the lo type
10+
dropfunctionoid(lo);
11+
12+
-- now drop the type
13+
droptype lo;
14+
15+
-- as the type is gone, remove the C based functions
16+
dropfunction lo_in(opaque);
17+
dropfunction lo_out(opaque);
18+
dropfunction lo(oid);
19+
dropfunction lo_manage();
20+
21+
-- the lo stuff is now removed from the system

‎contrib/lo/lo.c

Lines changed: 213 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,213 @@
1+
/*
2+
*PostgreSQL type definitions for managed LargeObjects.
3+
*
4+
*$Id: lo.c,v 1.1 1998/06/16 07:07:11 momjian Exp $
5+
*
6+
*/
7+
8+
#include<stdio.h>
9+
10+
#include<postgres.h>
11+
#include<utils/palloc.h>
12+
13+
/* Required for largeobjects */
14+
#include<libpq/libpq-fs.h>
15+
#include<libpq/be-fsstubs.h>
16+
17+
/* Required for SPI */
18+
#include<executor/spi.h>
19+
20+
/* Required for triggers */
21+
#include<commands/trigger.h>
22+
23+
/* required for tolower() */
24+
25+
/*
26+
*This is the internal storage format for managed large objects
27+
*
28+
*/
29+
30+
typedefOidBlob;
31+
32+
/*
33+
*Various forward declarations:
34+
*/
35+
36+
Blob*lo_in(char*str);/* Create from String*/
37+
char*lo_out(Blob*addr);/* Output oid as String*/
38+
Oidlo_oid(Blob*addr);/* Return oid as an oid*/
39+
Blob*lo(Oidoid);/* Return Blob based on oid*/
40+
HeapTuplelo_manage(void);/* Trigger handler*/
41+
42+
/*
43+
* This creates a large object, and set's its OID to the value in the
44+
* supplied string.
45+
*
46+
* If the string is empty, then a new LargeObject is created, and its oid
47+
* is placed in the resulting lo.
48+
*/
49+
Blob*
50+
lo_in(char*str)
51+
{
52+
Blob*result;
53+
Oidoid;
54+
intcount;
55+
56+
if (strlen(str)>0)
57+
{
58+
59+
count=sscanf(str,"%d",&oid);
60+
61+
if (count<1)
62+
{
63+
elog(ERROR,"lo_in: error in parsing \"%s\"",str);
64+
return (NULL);
65+
}
66+
67+
if(oid<0)
68+
{
69+
elog(ERROR,"lo_in: illegal oid \"%s\"",str);
70+
return (NULL);
71+
}
72+
}
73+
else
74+
{
75+
/*
76+
* There is no Oid passed, so create a new one
77+
*/
78+
oid=lo_creat(INV_READ|INV_WRITE);
79+
if(oid==InvalidOid)
80+
{
81+
elog(ERROR,"lo_in: InvalidOid returned from lo_creat");
82+
return (NULL);
83+
}
84+
}
85+
86+
result= (Blob*)palloc(sizeof(Blob));
87+
88+
*result=oid;
89+
90+
return (result);
91+
}
92+
93+
/*
94+
* This simply outputs the Oid of the Blob as a string.
95+
*/
96+
char*
97+
lo_out(Blob*addr)
98+
{
99+
char*result;
100+
101+
if (addr==NULL)
102+
return (NULL);
103+
104+
result= (char*)palloc(32);
105+
sprintf(result,"%d",*addr);
106+
return (result);
107+
}
108+
109+
/*
110+
* This function converts Blob to oid.
111+
*
112+
* eg: select lo_export(raster::oid,'/path/file') from table;
113+
*
114+
*/
115+
Oid
116+
lo_oid(Blob*addr)
117+
{
118+
if(addr==NULL)
119+
returnInvalidOid;
120+
return (Oid)(*addr);
121+
}
122+
123+
/*
124+
* This function is used so we can convert oid's to lo's
125+
*
126+
* ie: insert into table values(lo_import('/path/file')::lo);
127+
*
128+
*/
129+
Blob*
130+
lo(Oidoid)
131+
{
132+
Blob*result= (Blob*)palloc(sizeof(Blob));
133+
*result=oid;
134+
return (result);
135+
}
136+
137+
/*
138+
* This handles the trigger that protects us from orphaned large objects
139+
*/
140+
HeapTuple
141+
lo_manage(void)
142+
{
143+
intattnum;/* attribute number to monitor*/
144+
char**args;/* Args containing attr name*/
145+
TupleDesctupdesc;/* Tuple Descriptor*/
146+
HeapTuplerettuple;/* Tuple to be returned*/
147+
boolisdelete;/* are we deleting?*/
148+
HeapTuplenewtuple=NULL;/* The new value for tuple*/
149+
HeapTupletrigtuple;/* The original value of tuple*/
150+
151+
if (!CurrentTriggerData)
152+
elog(ERROR,"lo: triggers are not initialized");
153+
154+
/*
155+
* Fetch some values from CurrentTriggerData
156+
*/
157+
newtuple=CurrentTriggerData->tg_newtuple;
158+
trigtuple=CurrentTriggerData->tg_trigtuple;
159+
tupdesc=CurrentTriggerData->tg_relation->rd_att;
160+
args=CurrentTriggerData->tg_trigger->tgargs;
161+
162+
/* tuple to return to Executor */
163+
if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
164+
rettuple=newtuple;
165+
else
166+
rettuple=trigtuple;
167+
168+
/* Are we deleting the row? */
169+
isdelete=TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event);
170+
171+
/* Were done with it */
172+
CurrentTriggerData=NULL;
173+
174+
/* Get the column were interested in */
175+
attnum=SPI_fnumber(tupdesc,args[0]);
176+
177+
/*
178+
* Handle updates
179+
*
180+
* Here, if the value of the monitored attribute changes, then the
181+
* large object associated with the original value is unlinked.
182+
*/
183+
if(newtuple!=NULL) {
184+
char*orig=SPI_getvalue(trigtuple,tupdesc,attnum);
185+
char*newv=SPI_getvalue(newtuple,tupdesc,attnum);
186+
187+
if((orig!=newv&& (orig==NULL||newv==NULL))|| (orig!=NULL&&newv!=NULL&&strcmp(orig,newv)))
188+
lo_unlink(atoi(orig));
189+
190+
if(newv)
191+
pfree(newv);
192+
if(orig)
193+
pfree(orig);
194+
}
195+
196+
/*
197+
* Handle deleting of rows
198+
*
199+
* Here, we unlink the large object associated with the managed attribute
200+
*
201+
*/
202+
if(isdelete) {
203+
char*orig=SPI_getvalue(trigtuple,tupdesc,attnum);
204+
205+
if(orig!=NULL) {
206+
lo_unlink(atoi(orig));
207+
208+
pfree(orig);
209+
}
210+
}
211+
212+
return (rettuple);
213+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp