http://wiki.postgresql.org/index.php?title=Special:Contributions&feed=atom&target=ZigaPostgreSQL wiki - User contributions [en]2013-05-22T12:16:22ZFrom PostgreSQL wikiMediaWiki 1.15.5-2squeeze5http://wiki.postgresql.org/wiki/Talk:PGXNTalk:PGXN2010-05-27T18:24:05Z<p>Ziga: </p>
<hr />
<div>== Possible FAQ Addition ==<br />
<br />
How to pronounce PGAN? (P-G-An, P-Gan?)<br />
<br />
== PgFoundry considered useful ==<br />
<br />
Why not use PgFoundry as a place for uploading extensions?<br />
<br />
It already provides many facilities needed for development<br />
and is being quite used. Developers can already neatly package<br />
and upload their stuff. It already supports versioned file distribution.<br />
It is just not well enough integrated with actual running database one<br />
is using in practice.<br />
<br />
PgDEX could periodically (say once a day) download all changed<br />
published .tar.gz files (but not stuff in SCM) and compile<br />
them into installable packages. These would be installed by<br />
special command line utility, as suggested.<br />
<br />
What seems to be needed with this is a way to specify meta-information<br />
in packages; this could be in a form of a required file (say control,<br />
control.xml or pg_package.xml), which would contain the required data.<br />
Perhaps XML and/or RDF would be a good choice for this?<br />
<br />
At the very least, it would seem sensible to have some kind of correspondence<br />
between extension names and PgFoundry projects.<br />
<br />
--[[User:Ziga|Ziga]] 18:11, 27 May 2010 (UTC)</div>Zigahttp://wiki.postgresql.org/wiki/Talk:PGXNTalk:PGXN2010-05-27T18:11:42Z<p>Ziga: </p>
<hr />
<div>== Possible FAQ Addition ==<br />
<br />
How to pronounce PGAN? (P-G-An, P-Gan?)<br />
<br />
== PgFoundry considered useful ==<br />
<br />
Why not use PgFoundry as a place for uploading extensions?<br />
<br />
It already provides many facilities needed for development<br />
and is being quite used. It already supports versioned file distribution.<br />
It is just not well enough integrated with actual running database one<br />
is using in practice.<br />
<br />
PgDEX could periodically (say once a day) download all changed<br />
published .tar.gz files (but not stuff in SCM) and compile<br />
them into installable packages. These would be installed by<br />
special command line utility, as suggested.<br />
<br />
What seems to be needed with this is a way to specify meta-information<br />
in packages; this could be in a form of a required file (say control,<br />
control.xml or pg_package.xml), which would contain the required data.<br />
Perhaps XML and/or RDF would be a good choice for this?<br />
<br />
--[[User:Ziga|Ziga]] 18:11, 27 May 2010 (UTC)</div>Zigahttp://wiki.postgresql.org/wiki/Talk:PGXNTalk:PGXN2010-05-27T18:11:05Z<p>Ziga: </p>
<hr />
<div>== Possible FAQ Addition ==<br />
<br />
How to pronounce PGAN? (P-G-An, P-Gan?)<br />
<br />
Why not use PgFoundry as a place for uploading extensions?<br />
<br />
It already provides many facilities needed for development<br />
and is being quite used. It already supports versioned file distribution.<br />
It is just not well enough integrated with actual running database one<br />
is using in practice.<br />
<br />
PgDEX could periodically (say once a day) download all changed<br />
published .tar.gz files (but not stuff in SCM) and compile<br />
them into installable packages. These would be installed by<br />
special command line utility, as suggested.<br />
<br />
What seems to be needed with this is a way to specify meta-information<br />
in packages; this could be in a form of a required file (say control,<br />
control.xml or pg_package.xml), which would contain the required data.<br />
Perhaps XML and/or RDF would be a good choice for this?<br />
<br />
--[[User:Ziga|Ziga]] 18:11, 27 May 2010 (UTC)</div>Zigahttp://wiki.postgresql.org/wiki/User:ZigaUser:Ziga2010-04-20T18:30:29Z<p>Ziga: </p>
<hr />
<div>Žiga Kranjec<br />
<br />
software developer<br />
<br />
Mail: ziga ⓐ ljudmila.org<br />
<br />
===areas of interest===<br />
<br />
* User interfaces & User experience<br />
* Perl<br />
* Web development<br />
* Semantic Web<br />
<br />
===PostgreSQL areas of interest===<br />
* [[DDL Functions]] - SQL-callable functions capable of generating DDL scripts for objects within the database<br />
* [[Module Manager]] - automating installation of PostgreSQL extensions<br />
** [[User:Ziga/xpg_package|xpg_package]] - easily manage PostgreSQL extensions<br />
* [[User:Ziga/xpg_diff|xpg_diff]] - schema comparator/updater<br />
* [[DATALINK]] - SQL/MED DATALINK type</div>Zigahttp://wiki.postgresql.org/wiki/Free_disk_spaceFree disk space2010-04-20T18:24:49Z<p>Ziga: /* Example */</p>
<hr />
<div>{{SnippetInfo|Free disk space|lang=plperlu|version=8.3|depends=Linux|category=Administrative}}<br />
Author: [[User:Ziga|Žiga Kranjec]]<br />
<br />
This function returns file system information provided by unix utilities '''df(1)''' and '''mount(8)'''<br />
<br />
You can use it to get this information in SQL instead of shell, which can sometimes be inconvenient.<br />
<br />
It tested on Linux only; it might work on some other unices but not on Windows.<br />
<br />
<source lang="perl"><br />
CREATE OR REPLACE FUNCTION disk_free(<br />
OUT dev integer, OUT filesystem text, OUT fstype text, OUT mountpoint text, <br />
OUT size numeric, OUT used numeric, OUT avail numeric, <br />
OUT use double precision, OUT options text)<br />
RETURNS SETOF record AS<br />
$BODY$<br />
# Get UNIX file system information<br />
# this uses UNIX df(1) and mount(1) commands<br />
<br />
my %d=();<br />
<br />
# reported by df(1)<br />
my $df=`df -B1 -a -P`; my @df=split(/[\n\r]+/,$df); shift @df;<br />
for my $l (@df) { <br />
my @a=split(/\s+/,$l); my $i=$a[0];<br />
$d{$i}={<br />
'filesystem'=>$i,'size'=>$a[1],'used'=>$a[2],<br />
'avail'=>$a[3],'use'=>$a[4],'mountpoint'=>$a[5]<br />
};<br />
if( $d{$i}{'use'}eq'-') { $d{$i}{'use'}=undef; } <br />
else { $d{$i}{'use'}=~s/%//; }<br />
my @stat=stat($d{$i}{'mountpoint'});<br />
$d{$i}{'dev'}=$stat[0];<br />
}<br />
<br />
# reported by mount(8)<br />
my $mt=`mount`; my @mt=split(/[\n\r]+/,$mt);<br />
for my $l (@mt) {<br />
my @a=split(/\s+/,$l);<br />
$d{$a[0]}{'fstype'}=$a[4];<br />
$d{$a[0]}{'options'}=$a[5];<br />
}<br />
<br />
for my $i (keys(%d)) { return_next($d{$i}); }<br />
<br />
return undef;<br />
$BODY$<br />
LANGUAGE 'plperlu' VOLATILE<br />
COST 1000<br />
ROWS 10;<br />
ALTER FUNCTION disk_free() OWNER TO postgres;<br />
COMMENT ON FUNCTION disk_free() IS 'Get UNIX file system information';<br />
</source><br />
<br />
==Example==<br />
<source lang='SQL'><br />
select * from disk_free();<br />
</source><br />
<pre><br />
dev | filesystem | fstype | mountpoint | size | used | avail | use | options <br />
------+-------------+-------------+--------------------------+---------------+--------------+---------------+------+-----------------------------------<br />
2054 | /dev/sda6 | ext3 | /tmp | 1476407296 | 53538816 | 1347870720 | 4 | (rw)<br />
16 | tmpfs | tmpfs | /dev/shm | 4254875648 | 0 | 4254875648 | 0 | (rw,nosuid,nodev)<br />
2051 | /dev/sda3 | ext3 | /var | 78742790144 | 16139456512 | 58603335680 | 22 | (rw)<br />
2055 | /dev/sda7 | ext3 | /home | 2837008560128 | 207753547776 | 2485143212032 | 8 | (rw)<br />
10 | devpts | devpts | /dev/pts | 0 | 0 | 0 | | (rw,noexec,nosuid,gid=5,mode=620)<br />
2050 | /dev/sda2 | ext3 | / | 2952847360 | 599957504 | 2202890240 | 22 | (rw,errors=remount-ro)<br />
2052 | /dev/sda4 | ext3 | /usr | 29529182208 | 2355245056 | 25673940992 | 9 | (rw)<br />
2049 | /dev/sda1 | ext3 | /boot | 484179968 | 121042944 | 338137088 | 27 | (rw)<br />
3 | proc | proc | /proc | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
17 | binfmt_misc | binfmt_misc | /proc/sys/fs/binfmt_misc | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
0 | sysfs | sysfs | /sys | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
13 | udev | tmpfs | /dev | 10485760 | 159744 | 10326016 | 2 | (rw,mode=0755)<br />
(12 rows)<br />
</pre></div>Zigahttp://wiki.postgresql.org/wiki/Free_disk_spaceFree disk space2010-04-20T18:23:22Z<p>Ziga: </p>
<hr />
<div>{{SnippetInfo|Free disk space|lang=plperlu|version=8.3|depends=Linux|category=Administrative}}<br />
Author: [[User:Ziga|Žiga Kranjec]]<br />
<br />
This function returns file system information provided by unix utilities '''df(1)''' and '''mount(8)'''<br />
<br />
You can use it to get this information in SQL instead of shell, which can sometimes be inconvenient.<br />
<br />
It tested on Linux only; it might work on some other unices but not on Windows.<br />
<br />
<source lang="perl"><br />
CREATE OR REPLACE FUNCTION disk_free(<br />
OUT dev integer, OUT filesystem text, OUT fstype text, OUT mountpoint text, <br />
OUT size numeric, OUT used numeric, OUT avail numeric, <br />
OUT use double precision, OUT options text)<br />
RETURNS SETOF record AS<br />
$BODY$<br />
# Get UNIX file system information<br />
# this uses UNIX df(1) and mount(1) commands<br />
<br />
my %d=();<br />
<br />
# reported by df(1)<br />
my $df=`df -B1 -a -P`; my @df=split(/[\n\r]+/,$df); shift @df;<br />
for my $l (@df) { <br />
my @a=split(/\s+/,$l); my $i=$a[0];<br />
$d{$i}={<br />
'filesystem'=>$i,'size'=>$a[1],'used'=>$a[2],<br />
'avail'=>$a[3],'use'=>$a[4],'mountpoint'=>$a[5]<br />
};<br />
if( $d{$i}{'use'}eq'-') { $d{$i}{'use'}=undef; } <br />
else { $d{$i}{'use'}=~s/%//; }<br />
my @stat=stat($d{$i}{'mountpoint'});<br />
$d{$i}{'dev'}=$stat[0];<br />
}<br />
<br />
# reported by mount(8)<br />
my $mt=`mount`; my @mt=split(/[\n\r]+/,$mt);<br />
for my $l (@mt) {<br />
my @a=split(/\s+/,$l);<br />
$d{$a[0]}{'fstype'}=$a[4];<br />
$d{$a[0]}{'options'}=$a[5];<br />
}<br />
<br />
for my $i (keys(%d)) { return_next($d{$i}); }<br />
<br />
return undef;<br />
$BODY$<br />
LANGUAGE 'plperlu' VOLATILE<br />
COST 1000<br />
ROWS 10;<br />
ALTER FUNCTION disk_free() OWNER TO postgres;<br />
COMMENT ON FUNCTION disk_free() IS 'Get UNIX file system information';<br />
</source><br />
<br />
==Example==<br />
<source lang='SQL'><br />
select * from disk_free();<br />
</source><br />
<pre><br />
dev | filesystem | fstype | mountpoint | size | used | avail | %use | options <br />
------+-------------+-------------+--------------------------+---------------+--------------+---------------+------+-----------------------------------<br />
2054 | /dev/sda6 | ext3 | /tmp | 1476407296 | 53538816 | 1347870720 | 4 | (rw)<br />
16 | tmpfs | tmpfs | /dev/shm | 4254875648 | 0 | 4254875648 | 0 | (rw,nosuid,nodev)<br />
2051 | /dev/sda3 | ext3 | /var | 78742790144 | 16139456512 | 58603335680 | 22 | (rw)<br />
2055 | /dev/sda7 | ext3 | /home | 2837008560128 | 207753547776 | 2485143212032 | 8 | (rw)<br />
10 | devpts | devpts | /dev/pts | 0 | 0 | 0 | | (rw,noexec,nosuid,gid=5,mode=620)<br />
2050 | /dev/sda2 | ext3 | / | 2952847360 | 599957504 | 2202890240 | 22 | (rw,errors=remount-ro)<br />
2052 | /dev/sda4 | ext3 | /usr | 29529182208 | 2355245056 | 25673940992 | 9 | (rw)<br />
2049 | /dev/sda1 | ext3 | /boot | 484179968 | 121042944 | 338137088 | 27 | (rw)<br />
3 | proc | proc | /proc | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
17 | binfmt_misc | binfmt_misc | /proc/sys/fs/binfmt_misc | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
0 | sysfs | sysfs | /sys | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
13 | udev | tmpfs | /dev | 10485760 | 159744 | 10326016 | 2 | (rw,mode=0755)<br />
(12 rows)<br />
</pre></div>Zigahttp://wiki.postgresql.org/wiki/Free_disk_spaceFree disk space2010-04-20T18:22:55Z<p>Ziga: </p>
<hr />
<div>{{SnippetInfo|Free disk space|lang=plperlu|version=8.3|depends=Linux|category=Administrative}}<br />
Author: [[User:Ziga|Žiga Kranjec]]<br />
<br />
This function returns file system information provided by unix utilities '''df(1)''' and '''mount(8)'''<br />
<br />
You can use it to get this information in SQL instead of shell, which can sometimes be inconvenient.<br />
<br />
It tested on Linux only; it might work on some other unices but not on Windows.<br />
<br />
<source lang="perl"><br />
CREATE OR REPLACE FUNCTION disk_free(<br />
OUT dev integer, OUT filesystem text, OUT fstype text, OUT mountpoint text, <br />
OUT size numeric, OUT used numeric, OUT avail numeric, <br />
OUT use double precision, OUT options text)<br />
RETURNS SETOF record AS<br />
$BODY$<br />
# Get UNIX file system information<br />
# this uses UNIX df(1) and mount(1) commands<br />
<br />
my %d=();<br />
<br />
# reported by df(1)<br />
my $df=`df -B1 -a -P`; my @df=split(/[\n\r]+/,$df); shift @df;<br />
for my $l (@df) { <br />
my @a=split(/\s+/,$l); my $i=$a[0];<br />
$d{$i}={<br />
'filesystem'=>$i,'size'=>$a[1],'used'=>$a[2],<br />
'avail'=>$a[3],'use'=>$a[4],'mountpoint'=>$a[5]<br />
};<br />
if( $d{$i}{'use'}eq'-') { $d{$i}{'use'}=undef; } <br />
else { $d{$i}{'use'}=~s/%//; }<br />
my @stat=stat($d{$i}{'mountpoint'});<br />
$d{$i}{'dev'}=$stat[0];<br />
}<br />
<br />
# reported by mount(8)<br />
my $mt=`mount`; my @mt=split(/[\n\r]+/,$mt);<br />
for my $l (@mt) {<br />
my @a=split(/\s+/,$l);<br />
$d{$a[0]}{'fstype'}=$a[4];<br />
$d{$a[0]}{'options'}=$a[5];<br />
}<br />
<br />
for my $i (keys(%d)) { return_next($d{$i}); }<br />
<br />
return undef;<br />
$BODY$<br />
LANGUAGE 'plperlu' VOLATILE<br />
COST 1000<br />
ROWS 10;<br />
ALTER FUNCTION os.disk_free() OWNER TO postgres;<br />
COMMENT ON FUNCTION disk_free() IS 'Get UNIX file system information';<br />
</source><br />
<br />
==Example==<br />
<source lang='SQL'><br />
select * from disk_free();<br />
</source><br />
<pre><br />
dev | filesystem | fstype | mountpoint | size | used | avail | %use | options <br />
------+-------------+-------------+--------------------------+---------------+--------------+---------------+------+-----------------------------------<br />
2054 | /dev/sda6 | ext3 | /tmp | 1476407296 | 53538816 | 1347870720 | 4 | (rw)<br />
16 | tmpfs | tmpfs | /dev/shm | 4254875648 | 0 | 4254875648 | 0 | (rw,nosuid,nodev)<br />
2051 | /dev/sda3 | ext3 | /var | 78742790144 | 16139456512 | 58603335680 | 22 | (rw)<br />
2055 | /dev/sda7 | ext3 | /home | 2837008560128 | 207753547776 | 2485143212032 | 8 | (rw)<br />
10 | devpts | devpts | /dev/pts | 0 | 0 | 0 | | (rw,noexec,nosuid,gid=5,mode=620)<br />
2050 | /dev/sda2 | ext3 | / | 2952847360 | 599957504 | 2202890240 | 22 | (rw,errors=remount-ro)<br />
2052 | /dev/sda4 | ext3 | /usr | 29529182208 | 2355245056 | 25673940992 | 9 | (rw)<br />
2049 | /dev/sda1 | ext3 | /boot | 484179968 | 121042944 | 338137088 | 27 | (rw)<br />
3 | proc | proc | /proc | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
17 | binfmt_misc | binfmt_misc | /proc/sys/fs/binfmt_misc | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
0 | sysfs | sysfs | /sys | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
13 | udev | tmpfs | /dev | 10485760 | 159744 | 10326016 | 2 | (rw,mode=0755)<br />
(12 rows)<br />
</pre></div>Zigahttp://wiki.postgresql.org/wiki/Free_disk_spaceFree disk space2010-04-20T18:22:13Z<p>Ziga: New page: {{SnippetInfo|Free disk space|lang=plperlu|version=8.3|depends=Linux|category=Administrative}} Author: Žiga Kranjec This function returns file system information provided b...</p>
<hr />
<div>{{SnippetInfo|Free disk space|lang=plperlu|version=8.3|depends=Linux|category=Administrative}}<br />
Author: [[User:Ziga|Žiga Kranjec]]<br />
<br />
This function returns file system information provided by unix utilities '''df(1)''' and '''mount(8)'''<br />
<br />
You can use it to get this information in SQL instead of shell, which can sometimes be inconvenient.<br />
<br />
It tested on Linux only; it might work on some other unices but not on Windows.<br />
<br />
<source lang="perl"><br />
CREATE OR REPLACE FUNCTION disk_free(<br />
OUT dev integer, OUT filesystem text, OUT fstype text, OUT mountpoint text, <br />
OUT size numeric, OUT used numeric, OUT avail numeric, <br />
OUT use double precision, OUT options text)<br />
RETURNS SETOF record AS<br />
$BODY$<br />
# Get UNIX file system information<br />
# this uses UNIX df(1) and mount(1) commands<br />
<br />
my %d=();<br />
<br />
# reported by df(1)<br />
my $df=`df -B1 -a -P`;my @df=split(/[\n\r]+/,$df); shift @df;<br />
for my $l (@df) { <br />
my @a=split(/\s+/,$l); my $i=$a[0];<br />
$d{$i}={<br />
'filesystem'=>$i,'size'=>$a[1],'used'=>$a[2],<br />
'avail'=>$a[3],'use'=>$a[4],'mountpoint'=>$a[5]<br />
};<br />
if( $d{$i}{'use'}eq'-') { $d{$i}{'use'}=undef; } <br />
else { $d{$i}{'use'}=~s/%//; }<br />
my @stat=stat($d{$i}{'mountpoint'});<br />
$d{$i}{'dev'}=$stat[0];<br />
}<br />
<br />
# reported by mount(1)<br />
my $mt=`mount`; my @mt=split(/[\n\r]+/,$mt);<br />
for my $l (@mt) {<br />
my @a=split(/\s+/,$l);<br />
$d{$a[0]}{'fstype'}=$a[4];<br />
$d{$a[0]}{'options'}=$a[5];<br />
}<br />
<br />
for my $i (keys(%d)) { return_next($d{$i}); }<br />
<br />
return undef;<br />
$BODY$<br />
LANGUAGE 'plperlu' VOLATILE<br />
COST 1000<br />
ROWS 10;<br />
ALTER FUNCTION os.disk_free() OWNER TO postgres;<br />
COMMENT ON FUNCTION disk_free() IS 'Get UNIX file system information';<br />
</source><br />
<br />
==Example==<br />
<source lang='SQL'><br />
select * from disk_free();<br />
</source><br />
<pre><br />
dev | filesystem | fstype | mountpoint | size | used | avail | %use | options <br />
------+-------------+-------------+--------------------------+---------------+--------------+---------------+------+-----------------------------------<br />
2054 | /dev/sda6 | ext3 | /tmp | 1476407296 | 53538816 | 1347870720 | 4 | (rw)<br />
16 | tmpfs | tmpfs | /dev/shm | 4254875648 | 0 | 4254875648 | 0 | (rw,nosuid,nodev)<br />
2051 | /dev/sda3 | ext3 | /var | 78742790144 | 16139456512 | 58603335680 | 22 | (rw)<br />
2055 | /dev/sda7 | ext3 | /home | 2837008560128 | 207753547776 | 2485143212032 | 8 | (rw)<br />
10 | devpts | devpts | /dev/pts | 0 | 0 | 0 | | (rw,noexec,nosuid,gid=5,mode=620)<br />
2050 | /dev/sda2 | ext3 | / | 2952847360 | 599957504 | 2202890240 | 22 | (rw,errors=remount-ro)<br />
2052 | /dev/sda4 | ext3 | /usr | 29529182208 | 2355245056 | 25673940992 | 9 | (rw)<br />
2049 | /dev/sda1 | ext3 | /boot | 484179968 | 121042944 | 338137088 | 27 | (rw)<br />
3 | proc | proc | /proc | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
17 | binfmt_misc | binfmt_misc | /proc/sys/fs/binfmt_misc | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
0 | sysfs | sysfs | /sys | 0 | 0 | 0 | | (rw,noexec,nosuid,nodev)<br />
13 | udev | tmpfs | /dev | 10485760 | 159744 | 10326016 | 2 | (rw,mode=0755)<br />
(12 rows)<br />
</pre></div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-16T14:16:03Z<p>Ziga: /* Implementation */</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
Note that this is distinct from and complements efforts described [[SQL/MED|here]].<br />
<br />
Datalink is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 does. A lot of it is defined by the SQL standard to be "implementation specific".<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
However, there is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
A '''column''' containing a value of type DATALINK can optionally have some special semantics:<br />
# referential integrity - file pointed to by a datalink cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice (and probably needed to implement datalink):<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) → datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) → datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) → datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) → url<br />
* DLURLCOMPLETEONLY(datalink) → url<br />
* DLURLCOMPLETEWRITE(datalink) → url<br />
* DLURLPATH(datalink) → file path<br />
* DLURLPATHONLY(datalink) → file path<br />
* DLURLPATHWRITE(datalink) → file path<br />
<br />
* DLURLSCHEME(datalink) → URL scheme ('HTTP' or 'FILE')<br />
* DLURLSERVER(datalink) → server address<br />
<br />
* DLREPLACECONTENT(url, url, comment) → datalink (new in SQL 2008)<br />
<br />
Additional functions in another well known RDBMS<br />
<br />
* DLVALUE(address,linktype [,comment]) → datalink<br />
* DLCOMMENT(datalink) → text<br />
* DLLINKTYPE(datalink) → link type ('FILE' or 'URL')<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec. They are insanely verbose as text, but can be stored in a typmod. They are specified '''per column''' and thus apply to all URLs stored.<br />
<br />
'''LINK CONTROL'''<br />
* NO<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL - linked files cannot be deleted or renamed<br />
* SELECTIVE - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED<br />
** linked files cannot be modified<br />
* FS<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES<br />
* NO<br />
<br />
'''ON UNLINK'''<br />
* DELETE<br />
** file is deleted when unlinked<br />
* RESTORE<br />
** original properties (ownership, permissions) restored as well<br />
* NONE<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably even impossible to do properly on plain unix. Many parts, however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
Some sort of inter-server communication is needed (to support remote files). Accessing datalinker api in a remote postgresql instance over libpq (say dblink) could maybe work.<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say, even for root user. <br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-16T14:13:29Z<p>Ziga: /* Datalink support functions */</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
Note that this is distinct from and complements efforts described [[SQL/MED|here]].<br />
<br />
Datalink is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 does. A lot of it is defined by the SQL standard to be "implementation specific".<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
However, there is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
A '''column''' containing a value of type DATALINK can optionally have some special semantics:<br />
# referential integrity - file pointed to by a datalink cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice (and probably needed to implement datalink):<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) → datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) → datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) → datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) → url<br />
* DLURLCOMPLETEONLY(datalink) → url<br />
* DLURLCOMPLETEWRITE(datalink) → url<br />
* DLURLPATH(datalink) → file path<br />
* DLURLPATHONLY(datalink) → file path<br />
* DLURLPATHWRITE(datalink) → file path<br />
<br />
* DLURLSCHEME(datalink) → URL scheme ('HTTP' or 'FILE')<br />
* DLURLSERVER(datalink) → server address<br />
<br />
* DLREPLACECONTENT(url, url, comment) → datalink (new in SQL 2008)<br />
<br />
Additional functions in another well known RDBMS<br />
<br />
* DLVALUE(address,linktype [,comment]) → datalink<br />
* DLCOMMENT(datalink) → text<br />
* DLLINKTYPE(datalink) → link type ('FILE' or 'URL')<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec. They are insanely verbose as text, but can be stored in a typmod. They are specified '''per column''' and thus apply to all URLs stored.<br />
<br />
'''LINK CONTROL'''<br />
* NO<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL - linked files cannot be deleted or renamed<br />
* SELECTIVE - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED<br />
** linked files cannot be modified<br />
* FS<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES<br />
* NO<br />
<br />
'''ON UNLINK'''<br />
* DELETE<br />
** file is deleted when unlinked<br />
* RESTORE<br />
** original properties (ownership, permissions) restored as well<br />
* NONE<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts, however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
Some sort of inter-server communication is needed (to support remote files). Accessing datalinker api in a remote postgresql instance over libpq (say dblink) could maybe work.<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say.<br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-08T18:29:51Z<p>Ziga: /* Introduction */</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
Note that this is distinct from and complements efforts described [[SQL/MED|here]].<br />
<br />
Datalink is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 does. A lot of it is defined by the SQL standard to be "implementation specific".<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
However, there is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
A '''column''' containing a value of type DATALINK can optionally have some special semantics:<br />
# referential integrity - file pointed to by a datalink cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice (and probably needed to implement datalink):<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) -> datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) -> datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) -> datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) -> url<br />
* DLURLCOMPLETEONLY(datalink) -> url<br />
* DLURLCOMPLETEWRITE(datalink) -> url<br />
* DLURLPATH(datalink) -> fpath<br />
* DLURLPATHONLY(datalink) -> fpath<br />
* DLURLPATHWRITE(datalink) -> fpath<br />
<br />
* DLURLSCHEME(datalink) -> URL scheme (HTTP or FILE)<br />
* DLURLSERVER(datalink) -> server address<br />
<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec. They are insanely verbose as text, but can be stored in a typmod. They are specified '''per column''' and thus apply to all URLs stored.<br />
<br />
'''LINK CONTROL'''<br />
* NO<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL - linked files cannot be deleted or renamed<br />
* SELECTIVE - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED<br />
** linked files cannot be modified<br />
* FS<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES<br />
* NO<br />
<br />
'''ON UNLINK'''<br />
* DELETE<br />
** file is deleted when unlinked<br />
* RESTORE<br />
** original properties (ownership, permissions) restored as well<br />
* NONE<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts, however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
Some sort of inter-server communication is needed (to support remote files). Accessing datalinker api in a remote postgresql instance over libpq (say dblink) could maybe work.<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say.<br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:49:48Z<p>Ziga: /* Implementation */</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
A '''column''' containing a value of type DATALINK can optionally have some special semantics:<br />
# referential integrity - file pointed to by a datalink cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice (and probably needed to implement datalink):<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) -> datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) -> datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) -> datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) -> url<br />
* DLURLCOMPLETEONLY(datalink) -> url<br />
* DLURLCOMPLETEWRITE(datalink) -> url<br />
* DLURLPATH(datalink) -> fpath<br />
* DLURLPATHONLY(datalink) -> fpath<br />
* DLURLPATHWRITE(datalink) -> fpath<br />
<br />
* DLURLSCHEME(datalink) -> URL scheme (HTTP or FILE)<br />
* DLURLSERVER(datalink) -> server address<br />
<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec. They are insanely verbose as text, but can be stored in a typmod. They are specified '''per column''' and thus apply to all URLs stored.<br />
<br />
'''LINK CONTROL'''<br />
* NO<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL - linked files cannot be deleted or renamed<br />
* SELECTIVE - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED<br />
** linked files cannot be modified<br />
* FS<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES<br />
* NO<br />
<br />
'''ON UNLINK'''<br />
* DELETE<br />
** file is deleted when unlinked<br />
* RESTORE<br />
** original properties (ownership, permissions) restored as well<br />
* NONE<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts, however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
Some sort of inter-server communication is needed (to support remote files). Accessing datalinker api in a remote postgresql instance over libpq (say dblink) could maybe work.<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say.<br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:46:15Z<p>Ziga: /* Datalink attributes per SQL spec */</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
A '''column''' containing a value of type DATALINK can optionally have some special semantics:<br />
# referential integrity - file pointed to by a datalink cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice (and probably needed to implement datalink):<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) -> datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) -> datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) -> datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) -> url<br />
* DLURLCOMPLETEONLY(datalink) -> url<br />
* DLURLCOMPLETEWRITE(datalink) -> url<br />
* DLURLPATH(datalink) -> fpath<br />
* DLURLPATHONLY(datalink) -> fpath<br />
* DLURLPATHWRITE(datalink) -> fpath<br />
<br />
* DLURLSCHEME(datalink) -> URL scheme (HTTP or FILE)<br />
* DLURLSERVER(datalink) -> server address<br />
<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec. They are insanely verbose as text, but can be stored in a typmod. They are specified '''per column''' and thus apply to all URLs stored.<br />
<br />
'''LINK CONTROL'''<br />
* NO<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL - linked files cannot be deleted or renamed<br />
* SELECTIVE - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED<br />
** linked files cannot be modified<br />
* FS<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES<br />
* NO<br />
<br />
'''ON UNLINK'''<br />
* DELETE<br />
** file is deleted when unlinked<br />
* RESTORE<br />
** original properties (ownership, permissions) restored as well<br />
* NONE<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
+ some sort of inter-server communication<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say.<br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:45:48Z<p>Ziga: /* Datalink attributes per SQL spec */</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
A '''column''' containing a value of type DATALINK can optionally have some special semantics:<br />
# referential integrity - file pointed to by a datalink cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice (and probably needed to implement datalink):<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) -> datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) -> datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) -> datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) -> url<br />
* DLURLCOMPLETEONLY(datalink) -> url<br />
* DLURLCOMPLETEWRITE(datalink) -> url<br />
* DLURLPATH(datalink) -> fpath<br />
* DLURLPATHONLY(datalink) -> fpath<br />
* DLURLPATHWRITE(datalink) -> fpath<br />
<br />
* DLURLSCHEME(datalink) -> URL scheme (HTTP or FILE)<br />
* DLURLSERVER(datalink) -> server address<br />
<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec. They are insanely verbose as text, but can be stored in a typmod. They are specified '''per column''' and thus apply to all URLs stored.<br />
<br />
'''LINK CONTROL'''<br />
* NO<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL - linked files cannot be deleted or renamed<br />
* SELECTIVE - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED<br />
** linked files cannot be modified<br />
* FS<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES<br />
* NO<br />
<br />
'''UNLINK'''<br />
* DELETE<br />
** file is deleted when unlinked<br />
* RESTORE<br />
** original properties (ownership, permissions) restored as well<br />
* NONE<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
+ some sort of inter-server communication<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say.<br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:41:41Z<p>Ziga: </p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
A '''column''' containing a value of type DATALINK can optionally have some special semantics:<br />
# referential integrity - file pointed to by a datalink cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice (and probably needed to implement datalink):<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) -> datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) -> datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) -> datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) -> url<br />
* DLURLCOMPLETEONLY(datalink) -> url<br />
* DLURLCOMPLETEWRITE(datalink) -> url<br />
* DLURLPATH(datalink) -> fpath<br />
* DLURLPATHONLY(datalink) -> fpath<br />
* DLURLPATHWRITE(datalink) -> fpath<br />
<br />
* DLURLSCHEME(datalink) -> URL scheme (HTTP or FILE)<br />
* DLURLSERVER(datalink) -> server address<br />
<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec<br />
<br />
'''LINK CONTROL'''<br />
* NO (0/n)<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE (f)<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL (a) - linked files cannot be deleted or renamed<br />
* SELECTIVE (c/s) - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE (0/n) - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB (d)<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS (f)<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE (e/t)<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE (a)<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED (b)<br />
** linked files cannot be modified<br />
* FS (f)<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES (1/y)<br />
* NO (0/n)<br />
<br />
'''UNLINK'''<br />
* DELETE (d)<br />
** file is deleted when unlinked<br />
* RESTORE (1/r)<br />
** original properties (ownership, permissions) restored as well<br />
* NONE (0/n)<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
+ some sort of inter-server communication<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say.<br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:39:21Z<p>Ziga: /* Features */</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
A '''column''' containing a value of type DATALINK can optionally have some special semantics:<br />
# referential integrity - file pointed to by a datalink cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice:<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) -> datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) -> datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) -> datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) -> url<br />
* DLURLCOMPLETEONLY(datalink) -> url<br />
* DLURLCOMPLETEWRITE(datalink) -> url<br />
* DLURLPATH(datalink) -> fpath<br />
* DLURLPATHONLY(datalink) -> fpath<br />
* DLURLPATHWRITE(datalink) -> fpath<br />
<br />
* DLURLSCHEME(datalink) -> URL scheme (HTTP or FILE)<br />
* DLURLSERVER(datalink) -> server address<br />
<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec<br />
<br />
'''LINK CONTROL'''<br />
* NO (0/n)<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE (f)<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL (a) - linked files cannot be deleted or renamed<br />
* SELECTIVE (c/s) - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE (0/n) - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB (d)<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS (f)<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE (e/t)<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE (a)<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED (b)<br />
** linked files cannot be modified<br />
* FS (f)<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES (1/y)<br />
* NO (0/n)<br />
<br />
'''UNLINK'''<br />
* DELETE (d)<br />
** file is deleted when unlinked<br />
* RESTORE (1/r)<br />
** original properties (ownership, permissions) restored as well<br />
* NONE (0/n)<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
+ some sort of inter-server communication<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say.<br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:37:10Z<p>Ziga: </p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
The DATALINK type can have some special semantics:<br />
# referential integrity - file pointed to by a datalink column cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified by SQL/MED, but is often needed in practice:<br />
* compact URL storage for lots and lots of URLs (say int instead of string; sort-of like enum, but for URLs)<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks should enable you to:<br />
* efficiently store URLs (datalinks are stored as integers, not strings)<br />
* conveniently map between URLs and file system paths<br />
* safely read and write files from SQL (SQL/MED does not specify functions for this)<br />
* keep backups of changed files<br />
* manage files on different servers<br />
* manage datalink metadata (unified interface to extended filesystem attributes)<br />
<br />
==Specification==<br />
===Synopsys===<br />
<br />
create table my_files (<br />
...,<br />
link datalink,<br />
....<br />
)<br />
<br />
insert into my_files (link) select dlvalue('file:///etc/passwd');<br />
<br />
select into url dlurlcomplete(link) from my_files;<br />
<br />
update my_files set link=dlnewcopy(url,0);<br />
<br />
update my_files set link=dlpreviouscopy(url,0);<br />
<br />
=== Datalink support functions ===<br />
<br />
Constructors for values of type datalink:<br />
<br />
* DLVALUE(url) -> datalink (INSERT only)<br />
* DLNEWCOPY(url,tokenp) -> datalink (UPDATE only)<br />
* DLPREVIOUSCOPY(url,tokenp) -> datalink (UPDATE only)<br />
<br />
Functions for extracting information from datalink type:<br />
<br />
* DLURLCOMPLETE(datalink) -> url<br />
* DLURLCOMPLETEONLY(datalink) -> url<br />
* DLURLCOMPLETEWRITE(datalink) -> url<br />
* DLURLPATH(datalink) -> fpath<br />
* DLURLPATHONLY(datalink) -> fpath<br />
* DLURLPATHWRITE(datalink) -> fpath<br />
<br />
* DLURLSCHEME(datalink) -> URL scheme (HTTP or FILE)<br />
* DLURLSERVER(datalink) -> server address<br />
<br />
<br />
=== Datalink attributes per SQL spec ===<br />
<br />
These are refered to as ''link control options'' by SQL spec<br />
<br />
'''LINK CONTROL'''<br />
* NO (0/n)<br />
** just store the datalink<br />
** file is not "linked", no further control<br />
* FILE (f)<br />
** file is "linked"<br />
** file has to exist<br />
** level of control can be specified with further options<br />
<br />
'''INTEGRITY'''<br />
* ALL (a) - linked files cannot be deleted or renamed<br />
* SELECTIVE (c/s) - linked files can only be deleted or modified using file manager operations, if no datalinker is installed<br />
* NONE (0/n) - referenced files can be deleted or modified using file manager operations, not compatible with FILE LINK CONTROL<br />
<br />
'''READ ACCESS'''<br />
* DB (d)<br />
** read access is controlled by SQL server, based on access privileges to the datalink value<br />
** involves read access tokens<br />
*** encoded into the URL by the SQL server<br />
*** verified by external file manager/data linker<br />
* FS (f)<br />
** read access is determined by file manager<br />
<br />
'''WRITE ACCESS'''<br />
* ADMIN REQUIRING TOKEN FOR UPDATE (e/t)<br />
** write access governed by SQL server (and datalinker)<br />
** involves write access token for modifying file content<br />
* ADMIN NOT REQUIRING TOKEN FOR UPDATE (a)<br />
** write access governed by SQL server (and datalinker)<br />
* BLOCKED (b)<br />
** linked files cannot be modified<br />
* FS (f)<br />
** write access controlled by file manager<br />
<br />
'''RECOVERY'''<br />
* YES (1/y)<br />
* NO (0/n)<br />
<br />
'''UNLINK'''<br />
* DELETE (d)<br />
** file is deleted when unlinked<br />
* RESTORE (1/r)<br />
** original properties (ownership, permissions) restored as well<br />
* NONE (0/n)<br />
** ownership and permissions are not restored<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts however seem to be implementable.<br />
<br />
System should support at least http: and file: URL schemes. These two require quite different implementations. Perhaps a support for pluggable URL schemes could be useful - consider for example RDF namespaces.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs, provided for the use case mentioned. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. Master URL table is stored here.<br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics.<br />
<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
===Parts===<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
+ some sort of inter-server communication<br />
<br />
===Misc notes===<br />
<br />
On ext3 filesystem, files can be locked with 'chattr +i' command, but only by root. This prevents file from beeing changed, no matter what unix file permissions say.<br />
<br />
By changing file owner/group to postgres and setting unix file permissions, file can be made effectively owned by postgresql server (which can than use normal unix functions to use them).<br />
<br />
PL/PerlU seems the right choice for this, with Perl for external datalinker. Proper security and safety will be hard to get right!<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:09:56Z<p>Ziga: /* Features */</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
The DATALINK type can have some special semantics:<br />
# referential integrity - file pointed to by a datalink column cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access control through database<br />
* write access control through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified in SQL/MED, but is often needed in practice<br />
* compact URL storage for lots and lots of URLs<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts however seem to be implementable.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. URL dictionary <br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics on file modifications.<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
Parts:<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
+ some sort of inter-server communication<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:08:40Z<p>Ziga: </p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
===Features===<br />
The DATALINK type can have some special semantics:<br />
# referential integrity - file pointed to by a datalink column cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access through database<br />
* write access through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
The following is not specified in SQL/MED, but is often needed in practice<br />
* compact URL storage for lots and lots of URLs<br />
* URL to filesystem mapping, access control to file system hierarchies<br />
* local server file access (read, write)<br />
* remote server file access (get, put)<br />
* extended file system attributes (get,set)<br />
* file meta data (list,get,set)<br />
<br />
===Use cases===<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts however seem to be implementable.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. URL dictionary <br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics on file modifications.<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
Parts:<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
+ some sort of inter-server communication<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/DATALINKDATALINK2010-04-06T05:04:24Z<p>Ziga: New page: ==Introduction== SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification. It is a special SQL type intended to store URLs in database columns, and a number of functions, wh...</p>
<hr />
<div>==Introduction==<br />
<br />
SQL Datalinks are a part of SQL/MED ISO/IEC 9075-9:2003 specification.<br />
<br />
It is a special SQL type intended to store URLs in database columns,<br />
and a number of functions, which can be used in SQL queries.<br />
<br />
There aren't many RDBMSs which implement SQL/MED datalinks. <br />
IBM DB2 implements it. A lot of it is defined to be "implementation specific" <br />
by the SQL standard.<br />
<br />
Values of DATALINK type are opaque, constructed by using function DLVALUE.<br />
There are several functions available for converting DATALINK back to text, <br />
for example DLURLCOMPLETE.<br />
<br />
Only references to external files are stored in the database, not the<br />
content of the files themselves.<br />
<br />
The DATALINK type can have some special semantics:<br />
# referential integrity - file pointed to by a datalink column cannot be renamed or deleted<br />
# access control - either SQL or file system mediated<br />
# point in time recovery of file changes<br />
<br />
There is not much software, which demands SQL/MED datalinks.<br />
There is functionality concerning files, URLs and web, which is often <br />
needed in modern applications. Often, it is awkward to use these together <br />
with RDBMS data. SQL/MED standardizes some of this.<br />
<br />
Common use cases include:<br />
* website content management - ensuring that linked-to URLs exist<br />
* workflow management - providing for locking and versioning of files<br />
* configuration - managing configuration files, ie have a change in database recreate text configuration file. This file should be locked and writeable only from database.<br />
* web server log storage (many referer URLs)<br />
* RDF databases (many URLs)<br />
<br />
Datalinks as defined by SQL/MED should provide:<br />
* transactional semantics<br />
* checking if file exists<br />
* protection of linked file against renaming or deletion<br />
* read access through database<br />
* write access through database<br />
* point-in-time recovery<br />
* deletion of files no longer referenced<br />
* access to files on different servers<br />
<br />
In addition, we would also like<br />
* compact URL storage for lots and lots of URLs<br />
* URL : filesystem mapping<br />
* remote server access<br />
* extended file system attributes<br />
* file meta data<br />
<br />
<br />
<br />
==Implementation==<br />
<br />
Implementation of SQL/MED requires a lot of hocus-pocus and is probably event impossible to do properly on plain unix. Many parts however seem to be implementable.<br />
<br />
When storing many URLs, it should be possible to greatly reduce space requirements by storing URLs only once in a master URL table and using an int4 or int8 as an actual DATALINK type. This greatly reduces storage requirements needed for many URLs. It should be possible to partition the master URL table according to several criteria.<br />
<br />
Datalink software is divided in several parts:<br />
* datalink database schema. URL dictionary <br />
* external process ''datalinker''. It uses datalink schema. It manages files on behalf of postgresql server. It uses postgresql for implementing transactional semantics on file modifications.<br />
QUESTION: what to do for multiple databases on same server?<br />
<br />
Parts:<br />
* DL - DATALINK SQL/MED + support functions<br />
* FILEIO - local file access functions<br />
* URL - parsing and manipulation - one often needs to get and set various parts of URLs and filenames<br />
<br />
+ some sort of inter-server communication<br />
<br />
[[Category:SQL/MED]]</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T20:30:57Z<p>Ziga: /* As a database user */</p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome. <br />
It usually requires command-line interface and/or superuser access.<br />
It would be great, if modules could be used through SQL alone.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Import desired module with '''contrib.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select contrib.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
INSTALL hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
<br />
When user calls '''import()''' for the first time, module is installed into current schema.<br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''contrib'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=contrib; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
contrib | pg_module | table | pg_modules | modules, which can imported or deported<br />
contrib | pg_module_contents | table | pg_modules | module contents<br />
contrib | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
==Elaboration==<br />
<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
<br />
✓ = done,<br />
❤ = work in progress<br />
<br />
====✓ enable regular users to install modules====<br />
Done through use of SECURITY DEFINER functions. <br />
Basically, module import/deport functions run as superuser and can do anything.<br />
USAGE privilege on module manager schema must be granted to roles to enable them to use modules.<br />
<br />
====install modules by name (and optionally version)====<br />
====❤ command line interface, in spirit of apt-get or CPAN====<br />
====✓ transactional installation (module either gets installed ok or not at all)====<br />
====✓ you should be able to tell what is installed====<br />
====✓ should manage current contrib stuff too====<br />
====support multiple versions of databases and extensions====<br />
====create and drop (and alter) extensions in user-specified databases/schemas====<br />
====auto-get and compile and install extensions from sources from internet====<br />
====module sources: contrib, pgfoundry, user====<br />
====dependencies management (install dependencies as well)====<br />
====uninstall should use dependancies, not hand-written SQL scripts====<br />
====support for/integration with pg_dump/pg_restore; should dump import() statements insted of objects====<br />
<br />
==Construction==<br />
<br />
===Current status===<br />
There is a prototype implementation of user level module manager in PL/PgSQL in form of SQL schema.<br />
The name of the schema defaults to '''modules''', but can be configured at module manager installation time.<br />
<br />
There is a prototype implementation of command-line tool in Perl, which help with module creation.<br />
This is not needed by normal SQL users.<br />
<br />
===Concepts===<br />
A ''module'' is a collection of SQL objects, <br />
which can be created or dropped at once.<br />
<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management<br />
<br />
There are two aspect to module manager:<br />
# user level access for normal database users: functions '''import(modulename)''', '''install(modulename)''' and '''uninstall(modulename)'''<br />
# admin level access for database administrators: functions '''create_module()''', '''drop_module()''' and '''analyze_module()'''<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T20:28:58Z<p>Ziga: </p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome. <br />
It usually requires command-line interface and/or superuser access.<br />
It would be great, if modules could be used through SQL alone.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Import desired module with '''contrib.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select contrib.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''contrib'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=contrib; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
contrib | pg_module | table | pg_modules | modules, which can imported or deported<br />
contrib | pg_module_contents | table | pg_modules | module contents<br />
contrib | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
==Elaboration==<br />
<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
<br />
✓ = done,<br />
❤ = work in progress<br />
<br />
====✓ enable regular users to install modules====<br />
Done through use of SECURITY DEFINER functions. <br />
Basically, module import/deport functions run as superuser and can do anything.<br />
USAGE privilege on module manager schema must be granted to roles to enable them to use modules.<br />
<br />
====install modules by name (and optionally version)====<br />
====❤ command line interface, in spirit of apt-get or CPAN====<br />
====✓ transactional installation (module either gets installed ok or not at all)====<br />
====✓ you should be able to tell what is installed====<br />
====✓ should manage current contrib stuff too====<br />
====support multiple versions of databases and extensions====<br />
====create and drop (and alter) extensions in user-specified databases/schemas====<br />
====auto-get and compile and install extensions from sources from internet====<br />
====module sources: contrib, pgfoundry, user====<br />
====dependencies management (install dependencies as well)====<br />
====uninstall should use dependancies, not hand-written SQL scripts====<br />
====support for/integration with pg_dump/pg_restore; should dump import() statements insted of objects====<br />
<br />
==Construction==<br />
<br />
===Current status===<br />
There is a prototype implementation of user level module manager in PL/PgSQL in form of SQL schema.<br />
The name of the schema defaults to '''modules''', but can be configured at module manager installation time.<br />
<br />
There is a prototype implementation of command-line tool in Perl, which help with module creation.<br />
This is not needed by normal SQL users.<br />
<br />
===Concepts===<br />
A ''module'' is a collection of SQL objects, <br />
which can be created or dropped at once.<br />
<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management<br />
<br />
There are two aspect to module manager:<br />
# user level access for normal database users: functions '''import(modulename)''', '''install(modulename)''' and '''uninstall(modulename)'''<br />
# admin level access for database administrators: functions '''create_module()''', '''drop_module()''' and '''analyze_module()'''<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T20:28:02Z<p>Ziga: /* As a database user */</p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome. <br />
It usually requires command-line interface and/or superuser access.<br />
It would be great, if modules could be used through SQL alone.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Import desired module with '''contrib.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select contrib.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''modules'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=modules; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
modules | pg_module | table | pg_modules | modules, which can imported or deported<br />
modules | pg_module_contents | table | pg_modules | module contents<br />
modules | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
==Elaboration==<br />
<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
<br />
✓ = done,<br />
❤ = work in progress<br />
<br />
====✓ enable regular users to install modules====<br />
Done through use of SECURITY DEFINER functions. <br />
Basically, module import/deport functions run as superuser and can do anything.<br />
USAGE privilege on module manager schema must be granted to roles to enable them to use modules.<br />
<br />
====install modules by name (and optionally version)====<br />
====❤ command line interface, in spirit of apt-get or CPAN====<br />
====✓ transactional installation (module either gets installed ok or not at all)====<br />
====✓ you should be able to tell what is installed====<br />
====✓ should manage current contrib stuff too====<br />
====support multiple versions of databases and extensions====<br />
====create and drop (and alter) extensions in user-specified databases/schemas====<br />
====auto-get and compile and install extensions from sources from internet====<br />
====module sources: contrib, pgfoundry, user====<br />
====dependencies management (install dependencies as well)====<br />
====uninstall should use dependancies, not hand-written SQL scripts====<br />
====support for/integration with pg_dump/pg_restore; should dump import() statements insted of objects====<br />
<br />
==Construction==<br />
<br />
===Current status===<br />
There is a prototype implementation of user level module manager in PL/PgSQL in form of SQL schema.<br />
The name of the schema defaults to '''modules''', but can be configured at module manager installation time.<br />
<br />
There is a prototype implementation of command-line tool in Perl, which help with module creation.<br />
This is not needed by normal SQL users.<br />
<br />
===Concepts===<br />
A ''module'' is a collection of SQL objects, <br />
which can be created or dropped at once.<br />
<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management<br />
<br />
There are two aspect to module manager:<br />
# user level access for normal database users: functions '''import(modulename)''', '''install(modulename)''' and '''uninstall(modulename)'''<br />
# admin level access for database administrators: functions '''create_module()''', '''drop_module()''' and '''analyze_module()'''<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T20:27:07Z<p>Ziga: /* Concepts */</p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome. <br />
It usually requires command-line interface and/or superuser access.<br />
It would be great, if modules could be used through SQL alone.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Simply import desired module with '''modules.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select modules.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
If module is already imported, IMPORT is returned instead of IMPORT DEFINE.<br />
<br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''modules'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=modules; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
modules | pg_module | table | pg_modules | modules, which can imported or deported<br />
modules | pg_module_contents | table | pg_modules | module contents<br />
modules | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
==Elaboration==<br />
<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
<br />
✓ = done,<br />
❤ = work in progress<br />
<br />
====✓ enable regular users to install modules====<br />
Done through use of SECURITY DEFINER functions. <br />
Basically, module import/deport functions run as superuser and can do anything.<br />
USAGE privilege on module manager schema must be granted to roles to enable them to use modules.<br />
<br />
====install modules by name (and optionally version)====<br />
====❤ command line interface, in spirit of apt-get or CPAN====<br />
====✓ transactional installation (module either gets installed ok or not at all)====<br />
====✓ you should be able to tell what is installed====<br />
====✓ should manage current contrib stuff too====<br />
====support multiple versions of databases and extensions====<br />
====create and drop (and alter) extensions in user-specified databases/schemas====<br />
====auto-get and compile and install extensions from sources from internet====<br />
====module sources: contrib, pgfoundry, user====<br />
====dependencies management (install dependencies as well)====<br />
====uninstall should use dependancies, not hand-written SQL scripts====<br />
====support for/integration with pg_dump/pg_restore; should dump import() statements insted of objects====<br />
<br />
==Construction==<br />
<br />
===Current status===<br />
There is a prototype implementation of user level module manager in PL/PgSQL in form of SQL schema.<br />
The name of the schema defaults to '''modules''', but can be configured at module manager installation time.<br />
<br />
There is a prototype implementation of command-line tool in Perl, which help with module creation.<br />
This is not needed by normal SQL users.<br />
<br />
===Concepts===<br />
A ''module'' is a collection of SQL objects, <br />
which can be created or dropped at once.<br />
<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management<br />
<br />
There are two aspect to module manager:<br />
# user level access for normal database users: functions '''import(modulename)''', '''install(modulename)''' and '''uninstall(modulename)'''<br />
# admin level access for database administrators: functions '''create_module()''', '''drop_module()''' and '''analyze_module()'''<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T14:36:32Z<p>Ziga: /* Wishes/Requirements */</p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome. <br />
It usually requires command-line interface and/or superuser access.<br />
It would be great, if modules could be used through SQL alone.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Simply import desired module with '''modules.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select modules.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
If module is already imported, IMPORT is returned instead of IMPORT DEFINE.<br />
<br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''modules'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=modules; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
modules | pg_module | table | pg_modules | modules, which can imported or deported<br />
modules | pg_module_contents | table | pg_modules | module contents<br />
modules | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
==Elaboration==<br />
<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
<br />
✓ = done,<br />
❤ = work in progress<br />
<br />
====✓ enable regular users to install modules====<br />
Done through use of SECURITY DEFINER functions. <br />
Basically, module import/deport functions run as superuser and can do anything.<br />
USAGE privilege on module manager schema must be granted to roles to enable them to use modules.<br />
<br />
====install modules by name (and optionally version)====<br />
====❤ command line interface, in spirit of apt-get or CPAN====<br />
====✓ transactional installation (module either gets installed ok or not at all)====<br />
====✓ you should be able to tell what is installed====<br />
====✓ should manage current contrib stuff too====<br />
====support multiple versions of databases and extensions====<br />
====create and drop (and alter) extensions in user-specified databases/schemas====<br />
====auto-get and compile and install extensions from sources from internet====<br />
====module sources: contrib, pgfoundry, user====<br />
====dependencies management (install dependencies as well)====<br />
====uninstall should use dependancies, not hand-written SQL scripts====<br />
====support for/integration with pg_dump/pg_restore; should dump import() statements insted of objects====<br />
<br />
==Construction==<br />
<br />
===Current status===<br />
There is a prototype implementation of user level module manager in PL/PgSQL in form of SQL schema.<br />
The name of the schema defaults to '''modules''', but can be configured at module manager installation time.<br />
<br />
There is a prototype implementation of command-line tool in Perl, which help with module creation.<br />
This is not needed by normal SQL users.<br />
<br />
===Concepts===<br />
A ''module'' is a collection of SQL objects, <br />
which can be created or droped at once.<br />
<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
There are two aspect to module manager:<br />
# user level access for normal database users: functions '''import(modulename)''' and '''deport(modulename)'''<br />
# admin level access for database administrators: functions '''create_module()''', '''drop_module()''' and '''analyze_module()'''<br />
<br />
Mo<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T14:31:28Z<p>Ziga: </p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome. <br />
It usually requires command-line interface and/or superuser access.<br />
It would be great, if modules could be used through SQL alone.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Simply import desired module with '''modules.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select modules.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
If module is already imported, IMPORT is returned instead of IMPORT DEFINE.<br />
<br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''modules'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=modules; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
modules | pg_module | table | pg_modules | modules, which can imported or deported<br />
modules | pg_module_contents | table | pg_modules | module contents<br />
modules | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
==Elaboration==<br />
<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
* ✓ enable regular users to install modules<br />
* install modules by name (and optionally version)<br />
* ❤ command line interface, in spirit of apt-get or CPAN<br />
* ✓ transactional installation (module either gets installed ok or not at all)<br />
* ✓ you should be able to tell what is installed<br />
* ✓ should manage current contrib stuff too <br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
✓ = done,<br />
❤ = work in progress<br />
<br />
==Construction==<br />
<br />
===Current status===<br />
There is a prototype implementation of user level module manager in PL/PgSQL in form of SQL schema.<br />
The name of the schema defaults to '''modules''', but can be configured at module manager installation time.<br />
<br />
There is a prototype implementation of command-line tool in Perl, which help with module creation.<br />
This is not needed by normal SQL users.<br />
<br />
===Concepts===<br />
A ''module'' is a collection of SQL objects, <br />
which can be created or droped at once.<br />
<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
There are two aspect to module manager:<br />
# user level access for normal database users: functions '''import(modulename)''' and '''deport(modulename)'''<br />
# admin level access for database administrators: functions '''create_module()''', '''drop_module()''' and '''analyze_module()'''<br />
<br />
Mo<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T14:13:01Z<p>Ziga: </p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome. <br />
It usually requires command-line interface and/or superuser access.<br />
It would be great, if modules could be used through SQL alone.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
A ''module'' is a collection of SQL objects.<br />
<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Simply import desired module with '''modules.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select modules.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
If module is already imported, IMPORT is returned instead of IMPORT DEFINE.<br />
<br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''modules'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=modules; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
modules | pg_module | table | pg_modules | modules, which can imported or deported<br />
modules | pg_module_contents | table | pg_modules | module contents<br />
modules | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
<br />
==Elaboration==<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
* ✓ enable regular users to install modules<br />
* install modules by name (and optionally version)<br />
* ❤ command line interface, in spirit of apt-get or CPAN<br />
* ✓ transactional installation (module either gets installed ok or not at all)<br />
* ✓ you should be able to tell what is installed<br />
* ✓ should manage current contrib stuff too <br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
✓ = done,<br />
❤ = work in progress<br />
<br />
==Construction==<br />
<br />
===Concepts===<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T14:03:46Z<p>Ziga: /* Wishes/Requirements */</p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Simply import desired module with '''modules.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select modules.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
If module is already imported, IMPORT is returned instead of IMPORT DEFINE.<br />
<br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''modules'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=modules; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
modules | pg_module | table | pg_modules | modules, which can imported or deported<br />
modules | pg_module_contents | table | pg_modules | module contents<br />
modules | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
<br />
==Elaboration==<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
* ✓ enable regular users to install modules<br />
* install modules by name (and optionally version)<br />
* ❤ command line interface, in spirit of apt-get or CPAN<br />
* ✓ transactional installation (module either gets installed ok or not at all)<br />
* ✓ you should be able to tell what is installed<br />
* ✓ should manage current contrib stuff too <br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
✓ = done,<br />
❤ = work in progress<br />
<br />
==Construction==<br />
<br />
===Concepts===<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T03:26:39Z<p>Ziga: </p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As a database user===<br />
<br />
====Using modules====<br />
Simply import desired module with '''modules.import()''' function.<br />
<br />
<pre><br />
ljudmila=> select modules.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
</pre><br />
<br />
If module is already imported, IMPORT is returned instead of IMPORT DEFINE.<br />
<br />
<br />
===As a database administrator===<br />
<br />
====Install the module manager and contrib modules====<br />
<br />
By default, this goes into schema '''modules'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=modules; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
modules | pg_module | table | pg_modules | modules, which can imported or deported<br />
modules | pg_module_contents | table | pg_modules | module contents<br />
modules | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
<br />
</pre><br />
<br />
====List module contents====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';<br />
modname | sql_type | sql_identifier | objid | objsubid <br />
-----------+----------+--------------------------------------------------------+---------+----------<br />
tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0<br />
tablefunc | FUNCTION | crosstab(text) | 6542868 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0<br />
tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0<br />
tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0<br />
tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0<br />
tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0<br />
tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0<br />
tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0<br />
tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0<br />
(14 rows)<br />
<br />
</pre><br />
<br />
====Show module usage by schema====<br />
<pre><br />
ljudmila=# select * from modules.pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
<br />
</pre><br />
<br />
====Grant module usage privilege to user====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
<br />
==Elaboration==<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
* simple apt-get or CPAN like interface, install by name<br />
* transactional installation (module either gets installed ok or not at all)<br />
* you should be able to tell what is installed<br />
* should manage current contrib stuff too<br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
==Construction==<br />
<br />
===Concepts===<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-17T03:08:11Z<p>Ziga: </p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Synopsis==<br />
<br />
===As database administrator===<br />
<br />
====Install the module manager and contrib modules:====<br />
<br />
By default, this goes into schema '''modules'''.<br />
<br />
<pre><br />
postgres@mama:$ pg_module all >pg_module.sql<br />
postgres@mama:$ psql<br />
ljudmila=# \i pg_module.sql<br />
BEGIN<br />
... stuff ...<br />
COMMIT<br />
ljudmila=# set search_path=modules; \d+<br />
SET<br />
List of relations<br />
Schema | Name | Type | Owner | Description <br />
---------+--------------------+-------+------------+-----------------------------------------<br />
modules | pg_module | table | pg_modules | modules, which can imported or deported<br />
modules | pg_module_contents | table | pg_modules | module contents<br />
modules | pg_module_usage | view | pg_modules | modules imported into schemas<br />
(3 rows)<br />
</pre><br />
<br />
====List available modules====<br />
<pre><br />
ljudmila=# select * from modules.pg_module;<br />
modname | modimport | moddeport <br />
---------------------+---------------------------------------+---------------------------------<br />
pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()<br />
pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()<br />
tsearch2 | module_tsearch2_import() | module_tsearch2_deport()<br />
moddatetime | module_moddatetime_import() | <br />
pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()<br />
lo | module_lo_import() | module_lo_deport()<br />
seg | module_seg_import() | module_seg_deport()<br />
pgxml | module_pgxml_import() | module_pgxml_deport()<br />
test_parser | module_test_parser_import() | module_test_parser_deport()<br />
tablefunc | module_tablefunc_import() | module_tablefunc_deport()<br />
cube | module_cube_import() | module_cube_deport()<br />
timetravel | module_timetravel_import() | <br />
chkpass | module_chkpass_import() | module_chkpass_deport()<br />
_int | module__int_import() | module__int_deport()<br />
refint | module_refint_import() | <br />
int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()<br />
pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()<br />
fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()<br />
pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()<br />
dblink | module_dblink_import() | module_dblink_deport()<br />
isn | module_isn_import() | module_isn_deport()<br />
pageinspect | module_pageinspect_import() | module_pageinspect_deport()<br />
insert_username | module_insert_username_import() | <br />
adminpack | module_adminpack_import() | module_adminpack_deport()<br />
ltree | module_ltree_import() | module_ltree_deport()<br />
dict_int | module_dict_int_import() | module_dict_int_deport()<br />
autoinc | module_autoinc_import() | <br />
btree_gist | module_btree_gist_import() | module_btree_gist_deport()<br />
dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()<br />
pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()<br />
hstore | module_hstore_import() | module_hstore_deport()<br />
earthdistance | module_earthdistance_import() | module_earthdistance_deport()<br />
sslinfo | module_sslinfo_import() | module_sslinfo_deport()<br />
(35 rows)<br />
</pre><br />
<br />
====Show module usage by schema:====<br />
<pre><br />
ljudmila=# select * from pg_module_usage;<br />
schema_name | module_name | module_order <br />
-------------+-------------+--------------<br />
(0 rows)<br />
</pre><br />
<br />
====Grant module usage privilege to user:====<br />
<pre><br />
ljudmila=# grant usage on schema modules to atom;<br />
GRANT<br />
</pre><br />
<br />
<br />
===As database user, simply import desired module:===<br />
<br />
<pre><br />
ljudmila=> select modules.import('hstore');<br />
... stuff ...<br />
import <br />
---------------<br />
IMPORT DEFINE hstore<br />
(1 row)<br />
<br />
Time: 86.935 ms<br />
ljudmila=> select 'a=>3'::hstore;<br />
hstore <br />
----------<br />
"a"=>"3"<br />
(1 row)<br />
<br />
Time: 0.759 ms<br />
</pre><br />
<br />
If module is already imported, IMPORT is returned instead of IMPORT DEFINE.<br />
<br />
<br />
==Elaboration==<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
* simple apt-get or CPAN like interface, install by name<br />
* transactional installation (module either gets installed ok or not at all)<br />
* you should be able to tell what is installed<br />
* should manage current contrib stuff too<br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
==Construction==<br />
<br />
===Concepts===<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-10-16T17:09:11Z<p>Ziga: </p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27ecd2@mail.gmail.com Database owner installable modules patch by Tom Dunstan]<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-07/msg01098.php PostgreSQL extensions packaging proposal by Dimitri Fontaine]<br />
<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Elaboration==<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's own stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
* simple apt-get or CPAN like interface, install by name<br />
* transactional installation (module either gets installed ok or not at all)<br />
* you should be able to tell what is installed<br />
* should manage current contrib stuff too<br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
==Construction==<br />
<br />
===Concepts===<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/User:ZigaUser:Ziga2008-04-14T21:28:20Z<p>Ziga: </p>
<hr />
<div>Žiga Kranjec<br />
<br />
software developer<br />
<br />
Mail: ziga (a) ljudmila.org<br />
<br />
===areas of interest===<br />
<br />
* User interfaces & User experience<br />
* Perl<br />
* web development<br />
* semantic web<br />
<br />
===PostgreSQL areas of interest===<br />
* [[DDL Functions]] - SQL-callable functions capable of generating DDL scripts for objects within the database<br />
* [[Module Manager]] - automating installation of PostgreSQL extensions<br />
** [[User:Ziga/xpg_package|xpg_package]] - easily manage PostgreSQL extensions<br />
* [[User:Ziga/xpg_diff|xpg_diff]] - schema comparator/updater<br />
* [[Datalink]] - SQL/MED DATALINK type</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-04-14T21:26:56Z<p>Ziga: </p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Elaboration==<br />
===Use Cases===<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
===Wishes/Requirements===<br />
* simple apt-get or CPAN like interface, install by name<br />
* transactional installation (module either gets installed ok or not at all)<br />
* you should be able to tell what is installed<br />
* should manage current contrib stuff too<br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
==Construction==<br />
<br />
===Concepts===<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
===See Also===<br />
* [[User:Ziga/xpg_package]] - somewhat older document of a similar nature</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-04-14T21:23:07Z<p>Ziga: Extension Manager moved to Module Manager: term Module seems to be agreed upon</p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Use Cases==<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
==Wishes/Requirements==<br />
* simple apt-get or CPAN like interface, install by name<br />
* transactional installation (module either gets installed ok or not at all)<br />
* you should be able to tell what is installed<br />
* should manage current contrib stuff too<br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
==Concepts==<br />
<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
See Also: [[User:Ziga/xpg_package]]</div>Zigahttp://wiki.postgresql.org/wiki/Extension_ManagerExtension Manager2008-04-14T21:23:07Z<p>Ziga: Extension Manager moved to Module Manager: term Module seems to be agreed upon</p>
<hr />
<div>#REDIRECT [[Module Manager]]</div>Zigahttp://wiki.postgresql.org/wiki/Module_ManagerModule Manager2008-04-14T21:22:32Z<p>Ziga: </p>
<hr />
<div>There has been some interest PostgreSQL extension/package/module manager.<br />
<br />
We would like some software to make it easier for users and developers to install and manage<br />
various add-on features available for PostgreSQL.<br />
<br />
Currently the process of installing add-ons is rather manual and cumbersome.<br />
<br />
Recently, this topic has reappeared on pgsql-hackers mailing list:<br />
* [http://archives.postgresql.org/pgsql-hackers/2008-04/msg00132.php 2008-04 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-hackers/2007-10/msg01281.php 2007-10 pgsql-hackers thread]<br />
* [http://archives.postgresql.org/pgsql-patches/2006-05/msg00327.php 2006-05 pgsql-hackers thread]<br />
<br />
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?<br />
Here the term ''module'' is used, due to popular demand.<br />
<br />
==Use Cases==<br />
* Installing stuff from ''contrib''<br />
* Installing stuff from [http://pgfoundry.org/ PgFoundry]<br />
* Installing user's stuff<br />
* Convincing ISPs that modules are cool and can be easily installed and used safely<br />
<br />
==Wishes/Requirements==<br />
* simple apt-get or CPAN like interface, install by name<br />
* transactional installation (module either gets installed ok or not at all)<br />
* you should be able to tell what is installed<br />
* should manage current contrib stuff too<br />
* support multiple versions of databases and extensions<br />
* create and drop (and alter) extensions in user-specified databases/schemas<br />
* auto-get and compile and install extensions from sources from internet<br />
* module sources: contrib, pgfoundry, user<br />
* dependencies management (install dependencies as well)<br />
* uninstall should use dependancies, not hand-written SQL scripts<br />
* support for/integration with pg_dump/pg_restore<br />
<br />
==Concepts==<br />
<br />
''Module manager'' is a tool, which is specific to PostgreSQL, <br />
not a specific operating system. It should handle all tasks<br />
related to module management.<br />
<br />
Modules are installed on local system (eg Debian Linux), per<br />
specific PostgreSQL version.<br />
<br />
Modules are installed each in it's own directory, like:<br />
<br />
/usr/share/posteresql/8.3/modules/tablefunc-8.3.0/<br />
<br />
path structure:<br />
<br />
$base/postgresql/<i>pg_version</i>/modules/<i>modulename</i>-<i>moduleversion</i>/<br />
<br />
where:<br />
* pg_version - postgresql version number (so multiple installations are supported)<br />
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)<br />
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.<br />
<br />
module directory should contain at least:<br />
* SQL install instructions (file install.sql)<br />
* SQL uninstall instructions (file uninstall.sql)<br />
* Module information (file module.xml) - contents of this should be elaborated<br />
<br />
This would make it easier for utilities to enumerate installed modules.<br />
<br />
Once installed, extensions can be:<br />
* ''loaded'' (installed in particular database/schema)<br />
* ''unloaded'' (removed from particular database/schema)<br />
* ''upgraded'' (to a new version)<br />
<br />
Perhaps words CREATE, DROP and ALTER should be used?<br />
<br />
===Current ''contrib'' problems===<br />
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available<br />
* no module versions specified (or any metadata for that matter)<br />
* many files explicitly set schema to public:<br />
** this is the default anyway<br />
** it requires user to edit SQL file to change this<br />
** this should be set by module manager, if it is to support module installation in any schema<br />
<br />
Recommendations: <br />
* Move everything from contrib to appropriate dirs/files in modules directory<br />
* Add missing module.xml files<br />
* Make symlinks from contrib<br />
<br />
See Also: [[User:Ziga/xpg_package]]</div>Zigahttp://wiki.postgresql.org/wiki/User:ZigaUser:Ziga2008-04-14T20:35:26Z<p>Ziga: </p>
<hr />
<div>Žiga Kranjec<br />
<br />
software developer<br />
<br />
Mail: ziga (a) ljudmila.org<br />
<br />
===areas of interest===<br />
<br />
* user interfaces & user experience<br />
* perl<br />
* website development<br />
<br />
===PostgreSQL areas of interest===<br />
* [[DDL Functions]] - SQL-callable functions capable of generating DDL scripts for objects within the database<br />
* [[Module Manager]] - automating installation of PostgreSQL extensions<br />
** [[User:Ziga/xpg_package|xpg_package]] - easily manage PostgreSQL extensions<br />
* [[User:Ziga/xpg_diff|xpg_diff]] - schema comparator/updater<br />
* [[Datalink]] - SQL/MED DATALINK type</div>Zigahttp://wiki.postgresql.org/wiki/User:ZigaUser:Ziga2008-04-14T20:33:35Z<p>Ziga: New page: Žiga Kranjec software developer Mail: ziga (a) ljudmila.org ===areas of interest=== * user interfaces & user experience * perl * website development ===PostgreSQL areas of interest==...</p>
<hr />
<div>Žiga Kranjec<br />
<br />
software developer<br />
<br />
Mail: ziga (a) ljudmila.org<br />
<br />
===areas of interest===<br />
<br />
* user interfaces & user experience<br />
* perl<br />
* website development<br />
<br />
===PostgreSQL areas of interest===<br />
* [[DDL Functions]] - SQL-callable functions capable of generating DDL scripts for objects within the database<br />
* [[Package Manager]] - automating installation of PostgreSQL extensions<br />
** [[User:Ziga/xpg_package|xpg_package]] - easily manage PostgreSQL extensions<br />
* [[User:Ziga/xpg_diff|xpg_diff]] - schema comparator/updater</div>Ziga