Обсуждение: Extensions vs PGXS' MODULE_PATHNAME handling
I've run into a small infelicity that was introduced by our recent round
of redesign of the extensions feature. Specifically, if we have an
installation script that is named like hstore-1.0.sql.in, then what
pgxs.mk will substitute for MODULE_PATHNAME in it is
"$libdir/hstore-1.0" ... not exactly what's wanted. This is because the
transformation rule depends on $*, ie the base name of the input file.
There are a number of things we could do about this, each with some
upsides and downsides:
1. Forget about using MODULE_PATHNAME, and just start hardwiring
"$libdir/shlib-name" into install scripts. A small upside is we'd not
need the .sql.in-to-.sql build step anymore. The downside is that it's
kind of nice that the sql scripts don't need to know the shlib name ---
that certainly simplifies copying-and-pasting example functions.
2. Change the pgxs.mk rule to use $(MODULE_big)$(MODULES) instead of $*
(as I suspect it originally did, given the conditional around it).
This would work for makefiles that use $(MODULE_big) or use $(MODULES)
to build just a single shlib. In those that build multiple shlibs
(currently only contrib/spi), we'd still have to fall back to hardwiring
"$libdir/shlib-name" into the install scripts. Upside: works without
changes in simple cases. Downside: breaks for multiple output modules,
and ugly as sin anyway.
3. Change the pgxs.mk rule to strip $* down to whatever's before the
first dash. The downside of this is that we'd have to restrict
extensions to not have names including dash, a restriction not being
made presently. On the other hand, we may well have to enforce such a
restriction anyway in order to get pg_available_extensions to make sense
of the directory contents. Another point is that changing the rule
would potentially break old-style non-extension modules that use dashes
in their names. We could work around that by making the rule behavior
conditional on whether EXTENSION is defined, which is kinda ugly but
probably worth doing for backwards compatibility's sake.
On balance #3 seems the least bad, but I wonder if anyone sees this
choice differently or has another solution that I didn't think of.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > pgxs.mk will substitute for MODULE_PATHNAME in it is > "$libdir/hstore-1.0" ... not exactly what's wanted. This is because the > transformation rule depends on $*, ie the base name of the input file. [...] > On balance #3 seems the least bad, but I wonder if anyone sees this > choice differently or has another solution that I didn't think of. A though that is occurring to me here would be to add a shlib property in the control file and have the SQL script use $libdir/$shlib, or even $shlib maybe. That would only work for extensions scripts, and even only for those containing a single .so. But the only counter example I know of is PGQ, and its install script is ran by its command line tools. So PGQ would now ship 2 or 3 extensions with some dependencies, each with its own .so. Seems cleaner for me anyway. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> pgxs.mk will substitute for MODULE_PATHNAME in it is
>> "$libdir/hstore-1.0" ... not exactly what's wanted. This is because the
>> transformation rule depends on $*, ie the base name of the input file.
> A though that is occurring to me here would be to add a shlib property
> in the control file and have the SQL script use $libdir/$shlib, or even
> $shlib maybe. That would only work for extensions scripts, and even
> only for those containing a single .so.
Right, the basic difficulty here is exactly that in a Makefile that's
building multiple shlibs, there is no easy way to decide which shlibs go
with which sql scripts. The existing implementation essentially relies
on the base name of the sql script matching the base name of the shlib.
Adding a single-valued shlib property wouldn't improve matters at all.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Right, the basic difficulty here is exactly that in a Makefile that's > building multiple shlibs, there is no easy way to decide which shlibs go > with which sql scripts. The existing implementation essentially relies > on the base name of the sql script matching the base name of the shlib. > Adding a single-valued shlib property wouldn't improve matters at all. My take here is to way that in this case, the current (9.1) way to deal with the situation is to have multiple extensions when you have multiple shlibs. After all we know that multiple extensions from the same Makefile works, thanks to contrib/spi (I mean extension/spi). And we even have inter-extensions dependencies in 9.1, so that's friendly enough I think. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Right, the basic difficulty here is exactly that in a Makefile that's
>> building multiple shlibs, there is no easy way to decide which shlibs go
>> with which sql scripts. The existing implementation essentially relies
>> on the base name of the sql script matching the base name of the shlib.
>> Adding a single-valued shlib property wouldn't improve matters at all.
> My take here is to way that in this case, the current (9.1) way to deal
> with the situation is to have multiple extensions when you have multiple
> shlibs. After all we know that multiple extensions from the same
> Makefile works, thanks to contrib/spi (I mean extension/spi).
But contrib/spi is exactly the case where it *won't* work. We need to
somehow figure out that $libdir/autoinc is what to substitute in
autoinc-1.0.sql, $libdir/insert_username in insert_username-1.0.sql,
etc.
Also, I've been looking at the pg_available_extensions issue a bit.
I don't yet have a proposal for exactly how we ought to redefine it,
but I did notice that the existing code is terribly confused by
secondary control files: it doesn't realize that they're not primary
control files, so you get e.g. hstore and hstore-1.0 as separate
listings.
We could possibly work around that by giving secondary control files a
different extension, but I'm becoming more and more convinced that it's
just a bad idea to have a file naming rule in which it's ambiguous where
the extension name stops and the version name starts.
I did think of another idea besides forbidding dash in extension names:
what if we use double dash as the name/version separator, ie the naming
conventions are likeextension--version.controlextension--version.sqlextension--oldversion-newversion.sql
Then we'd only have to forbid double dash in extension names, which
seems unlikely to be a problem for anybody. (I think we might also have
to forbid empty version names to make this bulletproof, but that doesn't
bother me much either.)
Comments?
regards, tom lane
On Feb 12, 2011, at 2:29 PM, Tom Lane wrote:
> I did think of another idea besides forbidding dash in extension names:
> what if we use double dash as the name/version separator, ie the naming
> conventions are like
> extension--version.control
> extension--version.sql
> extension--oldversion-newversion.sql
> Then we'd only have to forbid double dash in extension names, which
> seems unlikely to be a problem for anybody. (I think we might also have
> to forbid empty version names to make this bulletproof, but that doesn't
> bother me much either.)
+1 You might even consider mandating a double-dash between versions, so that they could have dashes:
extension--oldversion--newversion.sql
We don't have to worry about the length of the file name, do we?
Best,
David
"David E. Wheeler" <david@kineticode.com> writes:
> On Feb 12, 2011, at 2:29 PM, Tom Lane wrote:
>> I did think of another idea besides forbidding dash in extension names:
>> what if we use double dash as the name/version separator,
> +1 You might even consider mandating a double-dash between versions, so that they could have dashes:
> extension--oldversion--newversion.sql
Hm. I think we'd still have to disallow dash as the first or last
character in a version name to make that unambiguous. Not sure it's
worth the trouble.
regards, tom lane
On Feb 12, 2011, at 3:12 PM, Tom Lane wrote: > "David E. Wheeler" <david@kineticode.com> writes: >> On Feb 12, 2011, at 2:29 PM, Tom Lane wrote: >>> I did think of another idea besides forbidding dash in extension names: >>> what if we use double dash as the name/version separator, > >> +1 You might even consider mandating a double-dash between versions, so that they could have dashes: >> extension--oldversion--newversion.sql > > Hm. I think we'd still have to disallow dash as the first or last > character in a version name to make that unambiguous. Not sure it's > worth the trouble. How likely is *that*? David
"David E. Wheeler" <david@kineticode.com> writes:
> On Feb 12, 2011, at 3:12 PM, Tom Lane wrote:
>> Hm. I think we'd still have to disallow dash as the first or last
>> character in a version name to make that unambiguous. Not sure it's
>> worth the trouble.
> How likely is *that*?
Not very, but the rules are getting a bit complicated ...
regards, tom lane
On Feb 12, 2011, at 3:37 PM, Tom Lane wrote: >> How likely is *that*? > > Not very, but the rules are getting a bit complicated ... Doesn't seem complicated to me: 1. Use -- to separate extension name, old version, new version 2. Don't use - at the beginning or end of name or version number 3. Profit How hard is that? David
Tom Lane <tgl@sss.pgh.pa.us> writes: >> My take here is to way that in this case, the current (9.1) way to deal >> with the situation is to have multiple extensions when you have multiple >> shlibs. After all we know that multiple extensions from the same >> Makefile works, thanks to contrib/spi (I mean extension/spi). > > But contrib/spi is exactly the case where it *won't* work. We need to > somehow figure out that $libdir/autoinc is what to substitute in > autoinc-1.0.sql, $libdir/insert_username in insert_username-1.0.sql, > etc. Indeed. That's why I'm proposing to have that setup in the control file, which is per extension, rather than in the common Makefile. > Also, I've been looking at the pg_available_extensions issue a bit. > I don't yet have a proposal for exactly how we ought to redefine it, > but I did notice that the existing code is terribly confused by > secondary control files: it doesn't realize that they're not primary > control files, so you get e.g. hstore and hstore-1.0 as separate > listings. I'd think that's it's a good idea if dealt with "correctly" because now that ALTER EXTENSION UPDATE can deal with more than one target VERSION I expect the view to show each available update here. If possible adding the "update chain sequence" information as computed in the code would be great. Because we can't ask people to figure that out all by themselves, the best way to check your upgrading setup is fine would be to run SELECT * FROM pg_available_extensions; and read the result. > We could possibly work around that by giving secondary control files a > different extension, but I'm becoming more and more convinced that it's > just a bad idea to have a file naming rule in which it's ambiguous where > the extension name stops and the version name starts. Agreed. > I did think of another idea besides forbidding dash in extension names: > what if we use double dash as the name/version separator, ie the naming > conventions are like > extension--version.control > extension--version.sql > extension--oldversion-newversion.sql Yeah, something like that would work, so would maybe using ':' and forbidding one-letter extension names, but I'm not in a position to check that this won't confuse the windows we support too much. I see about no downside to the double dash proposal, that said. > Then we'd only have to forbid double dash in extension names, which > seems unlikely to be a problem for anybody. (I think we might also have > to forbid empty version names to make this bulletproof, but that doesn't > bother me much either.) Those look like sanity checks more than anything else, I'd welcome us having them. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> But contrib/spi is exactly the case where it *won't* work. We need to
>> somehow figure out that $libdir/autoinc is what to substitute in
>> autoinc-1.0.sql, $libdir/insert_username in insert_username-1.0.sql,
>> etc.
> Indeed. That's why I'm proposing to have that setup in the control
> file, which is per extension, rather than in the common Makefile.
How's that help? In a makefile building more than one extension,
you'd still need a way to decide which extension the current script
file is associated with.
Or are you suggesting substituting for MODULE_PATHNAME during CREATE
EXTENSION, and not during "make" at all? That would work I guess.
I'm hesitant to have any substitutions that happen unconditionally,
but we could add a control parameter likemodule_pathname = '$libdir/hstore'
and then things would be pretty clean.
I think we should still change the file naming conventions to use double
dashes, though, since there's more than one reason to want that. Will
work on that next.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Or are you suggesting substituting for MODULE_PATHNAME during CREATE > EXTENSION, and not during "make" at all? That would work I guess. That's my idea, sorry not having made it clear enough. We have $libdir which is expanded server-side AFAIUI, I though we would have $shlib expanded the same way and taken from some backend variable like with creating_extension. > I'm hesitant to have any substitutions that happen unconditionally, > but we could add a control parameter like > module_pathname = '$libdir/hstore' > and then things would be pretty clean. Ok. Maybe the simpler would be to make the current control variable a static backend variable so that EXT_CONTROL(module_pathname) is easy to find out from anywhere (I see you got rid of some direct usage of static variables with recordDependencyOnCurrentExtension() already). > I think we should still change the file naming conventions to use double > dashes, though, since there's more than one reason to want that. Will > work on that next. Great! Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> I'm hesitant to have any substitutions that happen unconditionally,
>> but we could add a control parameter like
>> module_pathname = '$libdir/hstore'
>> and then things would be pretty clean.
> Ok. Maybe the simpler would be to make the current control variable a
> static backend variable so that EXT_CONTROL(module_pathname) is easy to
> find out from anywhere (I see you got rid of some direct usage of static
> variables with recordDependencyOnCurrentExtension() already).
I think it's better to keep it working as a textual substitution.
That poses the least risk of breaking scripts that work today ---
who's to say that somebody might not be relying on the substitution
happening someplace else than CREATE FUNCTION's shlib string?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I think it's better to keep it working as a textual substitution. > That poses the least risk of breaking scripts that work today --- > who's to say that somebody might not be relying on the substitution > happening someplace else than CREATE FUNCTION's shlib string? Fair enough, I suppose. So +1 from me, FWIW. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> I think it's better to keep it working as a textual substitution.
>> That poses the least risk of breaking scripts that work today ---
>> who's to say that somebody might not be relying on the substitution
>> happening someplace else than CREATE FUNCTION's shlib string?
> Fair enough, I suppose. So +1 from me, FWIW.
OK, so with that, attached is an example of the complete conversion diff
for a contrib module (hstore in particular). Although "git status"
reports hstore--1.0.sql as being a rename of hstore.sql.in, "git diff"
doesn't seem to be exceedingly bright about presenting it that way :-(.
But actually the change in that script other than renaming is just
removing the "set search_path" command and adjusting the header comment.
I've checked that regression tests pass and "create extension hstore
from unpackaged" successfully upgrades from a 9.0 dump. I don't have
the ability to check that it works on Windows too, but since we're not
hacking pgxs.mk I doubt that there's anything to do to the Windows build
process.
Barring objections, I'll press on with fixing the rest of them.
regards, tom lane
diff --git a/contrib/hstore/.gitignore b/contrib/hstore/.gitignore
index d7af95330c380d468c35f781f34de30ea05709a5..19b6c5ba425ca92d1bb371bf43d9cdae372f8c1a 100644
*** a/contrib/hstore/.gitignore
--- b/contrib/hstore/.gitignore
***************
*** 1,3 ****
- /hstore.sql
# Generated subdirectories
/results/
--- 1,2 ----
diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile
index 1d533fdd60280b1e62610dd7b98cdfb4151de1b4..5badbdb714b60cd786cffa86526a405bccfd1ea0 100644
*** a/contrib/hstore/Makefile
--- b/contrib/hstore/Makefile
*************** MODULE_big = hstore
*** 4,11 ****
OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \
crc32.o
! DATA_built = hstore.sql
! DATA = uninstall_hstore.sql
REGRESS = hstore
ifdef USE_PGXS
--- 4,11 ----
OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \
crc32.o
! EXTENSION = hstore
! DATA = hstore--1.0.sql hstore--unpackaged--1.0.sql
REGRESS = hstore
ifdef USE_PGXS
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index 354fff20fe2b24127ac9ec1ae9a20f72d628e256..083faf8d9c433ba9f34a95f65fed64c0079a6561 100644
*** a/contrib/hstore/expected/hstore.out
--- b/contrib/hstore/expected/hstore.out
***************
*** 1,12 ****
! --
! -- first, define the datatype. Turn off echoing so that expected file
! -- does not depend on contents of hstore.sql.
! --
! SET client_min_messages = warning;
! \set ECHO none
! psql:hstore.sql:228: WARNING: => is deprecated as an operator name
DETAIL: This name may be disallowed altogether in future versions of PostgreSQL.
- RESET client_min_messages;
set escape_string_warning=off;
--hstore;
select ''::hstore;
--- 1,6 ----
! CREATE EXTENSION hstore;
! WARNING: => is deprecated as an operator name
DETAIL: This name may be disallowed altogether in future versions of PostgreSQL.
set escape_string_warning=off;
--hstore;
select ''::hstore;
diff --git a/contrib/hstore/hstore--1.0.sql b/contrib/hstore/hstore--1.0.sql
index ...d77b14286bdce8af49bdad9620e00c5c4ce827fe .
*** a/contrib/hstore/hstore--1.0.sql
--- b/contrib/hstore/hstore--1.0.sql
***************
*** 0 ****
--- 1,527 ----
+ /* contrib/hstore/hstore--1.0.sql */
+
+ CREATE TYPE hstore;
+
+ CREATE OR REPLACE FUNCTION hstore_in(cstring)
+ RETURNS hstore
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_out(hstore)
+ RETURNS cstring
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_recv(internal)
+ RETURNS hstore
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_send(hstore)
+ RETURNS bytea
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE TYPE hstore (
+ INTERNALLENGTH = -1,
+ INPUT = hstore_in,
+ OUTPUT = hstore_out,
+ RECEIVE = hstore_recv,
+ SEND = hstore_send,
+ STORAGE = extended
+ );
+
+ CREATE OR REPLACE FUNCTION hstore_version_diag(hstore)
+ RETURNS integer
+ AS 'MODULE_PATHNAME','hstore_version_diag'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION fetchval(hstore,text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','hstore_fetchval'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR -> (
+ LEFTARG = hstore,
+ RIGHTARG = text,
+ PROCEDURE = fetchval
+ );
+
+ CREATE OR REPLACE FUNCTION slice_array(hstore,text[])
+ RETURNS text[]
+ AS 'MODULE_PATHNAME','hstore_slice_to_array'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR -> (
+ LEFTARG = hstore,
+ RIGHTARG = text[],
+ PROCEDURE = slice_array
+ );
+
+ CREATE OR REPLACE FUNCTION slice(hstore,text[])
+ RETURNS hstore
+ AS 'MODULE_PATHNAME','hstore_slice_to_hstore'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION isexists(hstore,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME','hstore_exists'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION exist(hstore,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME','hstore_exists'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR ? (
+ LEFTARG = hstore,
+ RIGHTARG = text,
+ PROCEDURE = exist,
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE OR REPLACE FUNCTION exists_any(hstore,text[])
+ RETURNS bool
+ AS 'MODULE_PATHNAME','hstore_exists_any'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR ?| (
+ LEFTARG = hstore,
+ RIGHTARG = text[],
+ PROCEDURE = exists_any,
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE OR REPLACE FUNCTION exists_all(hstore,text[])
+ RETURNS bool
+ AS 'MODULE_PATHNAME','hstore_exists_all'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR ?& (
+ LEFTARG = hstore,
+ RIGHTARG = text[],
+ PROCEDURE = exists_all,
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE OR REPLACE FUNCTION isdefined(hstore,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME','hstore_defined'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION defined(hstore,text)
+ RETURNS bool
+ AS 'MODULE_PATHNAME','hstore_defined'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION delete(hstore,text)
+ RETURNS hstore
+ AS 'MODULE_PATHNAME','hstore_delete'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION delete(hstore,text[])
+ RETURNS hstore
+ AS 'MODULE_PATHNAME','hstore_delete_array'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION delete(hstore,hstore)
+ RETURNS hstore
+ AS 'MODULE_PATHNAME','hstore_delete_hstore'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR - (
+ LEFTARG = hstore,
+ RIGHTARG = text,
+ PROCEDURE = delete
+ );
+
+ CREATE OPERATOR - (
+ LEFTARG = hstore,
+ RIGHTARG = text[],
+ PROCEDURE = delete
+ );
+
+ CREATE OPERATOR - (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = delete
+ );
+
+ CREATE OR REPLACE FUNCTION hs_concat(hstore,hstore)
+ RETURNS hstore
+ AS 'MODULE_PATHNAME','hstore_concat'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR || (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hs_concat
+ );
+
+ CREATE OR REPLACE FUNCTION hs_contains(hstore,hstore)
+ RETURNS bool
+ AS 'MODULE_PATHNAME','hstore_contains'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hs_contained(hstore,hstore)
+ RETURNS bool
+ AS 'MODULE_PATHNAME','hstore_contained'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR @> (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hs_contains,
+ COMMUTATOR = '<@',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE OPERATOR <@ (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hs_contained,
+ COMMUTATOR = '@>',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ -- obsolete:
+ CREATE OPERATOR @ (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hs_contains,
+ COMMUTATOR = '~',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE OPERATOR ~ (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hs_contained,
+ COMMUTATOR = '@',
+ RESTRICT = contsel,
+ JOIN = contjoinsel
+ );
+
+ CREATE OR REPLACE FUNCTION tconvert(text,text)
+ RETURNS hstore
+ AS 'MODULE_PATHNAME','hstore_from_text'
+ LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL)
+
+ CREATE OR REPLACE FUNCTION hstore(text,text)
+ RETURNS hstore
+ AS 'MODULE_PATHNAME','hstore_from_text'
+ LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL)
+
+ CREATE OPERATOR => (
+ LEFTARG = text,
+ RIGHTARG = text,
+ PROCEDURE = hstore
+ );
+
+ CREATE OR REPLACE FUNCTION hstore(text[],text[])
+ RETURNS hstore
+ AS 'MODULE_PATHNAME', 'hstore_from_arrays'
+ LANGUAGE C IMMUTABLE; -- not STRICT; allows (keys,null)
+
+ CREATE FUNCTION hstore(text[])
+ RETURNS hstore
+ AS 'MODULE_PATHNAME', 'hstore_from_array'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE CAST (text[] AS hstore)
+ WITH FUNCTION hstore(text[]);
+
+ CREATE OR REPLACE FUNCTION hstore(record)
+ RETURNS hstore
+ AS 'MODULE_PATHNAME', 'hstore_from_record'
+ LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::recordtype)
+
+ CREATE OR REPLACE FUNCTION hstore_to_array(hstore)
+ RETURNS text[]
+ AS 'MODULE_PATHNAME','hstore_to_array'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR %% (
+ RIGHTARG = hstore,
+ PROCEDURE = hstore_to_array
+ );
+
+ CREATE OR REPLACE FUNCTION hstore_to_matrix(hstore)
+ RETURNS text[]
+ AS 'MODULE_PATHNAME','hstore_to_matrix'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR %# (
+ RIGHTARG = hstore,
+ PROCEDURE = hstore_to_matrix
+ );
+
+ CREATE OR REPLACE FUNCTION akeys(hstore)
+ RETURNS text[]
+ AS 'MODULE_PATHNAME','hstore_akeys'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION avals(hstore)
+ RETURNS text[]
+ AS 'MODULE_PATHNAME','hstore_avals'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION skeys(hstore)
+ RETURNS setof text
+ AS 'MODULE_PATHNAME','hstore_skeys'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION svals(hstore)
+ RETURNS setof text
+ AS 'MODULE_PATHNAME','hstore_svals'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION each(IN hs hstore,
+ OUT key text,
+ OUT value text)
+ RETURNS SETOF record
+ AS 'MODULE_PATHNAME','hstore_each'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION populate_record(anyelement,hstore)
+ RETURNS anyelement
+ AS 'MODULE_PATHNAME', 'hstore_populate_record'
+ LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::rectype,hstore)
+
+ CREATE OPERATOR #= (
+ LEFTARG = anyelement,
+ RIGHTARG = hstore,
+ PROCEDURE = populate_record
+ );
+
+ -- btree support
+
+ CREATE OR REPLACE FUNCTION hstore_eq(hstore,hstore)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME','hstore_eq'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_ne(hstore,hstore)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME','hstore_ne'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_gt(hstore,hstore)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME','hstore_gt'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_ge(hstore,hstore)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME','hstore_ge'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_lt(hstore,hstore)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME','hstore_lt'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_le(hstore,hstore)
+ RETURNS boolean
+ AS 'MODULE_PATHNAME','hstore_le'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION hstore_cmp(hstore,hstore)
+ RETURNS integer
+ AS 'MODULE_PATHNAME','hstore_cmp'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR = (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hstore_eq,
+ COMMUTATOR = =,
+ NEGATOR = <>,
+ RESTRICT = eqsel,
+ JOIN = eqjoinsel,
+ MERGES,
+ HASHES
+ );
+ CREATE OPERATOR <> (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hstore_ne,
+ COMMUTATOR = <>,
+ NEGATOR = =,
+ RESTRICT = neqsel,
+ JOIN = neqjoinsel
+ );
+
+ -- the comparison operators have funky names (and are undocumented)
+ -- in an attempt to discourage anyone from actually using them. they
+ -- only exist to support the btree opclass
+
+ CREATE OPERATOR #<# (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hstore_lt,
+ COMMUTATOR = #>#,
+ NEGATOR = #>=#,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+ );
+ CREATE OPERATOR #<=# (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hstore_le,
+ COMMUTATOR = #>=#,
+ NEGATOR = #>#,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+ );
+ CREATE OPERATOR #># (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hstore_gt,
+ COMMUTATOR = #<#,
+ NEGATOR = #<=#,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+ );
+ CREATE OPERATOR #>=# (
+ LEFTARG = hstore,
+ RIGHTARG = hstore,
+ PROCEDURE = hstore_ge,
+ COMMUTATOR = #<=#,
+ NEGATOR = #<#,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+ );
+
+ CREATE OPERATOR CLASS btree_hstore_ops
+ DEFAULT FOR TYPE hstore USING btree
+ AS
+ OPERATOR 1 #<# ,
+ OPERATOR 2 #<=# ,
+ OPERATOR 3 = ,
+ OPERATOR 4 #>=# ,
+ OPERATOR 5 #># ,
+ FUNCTION 1 hstore_cmp(hstore,hstore);
+
+ -- hash support
+
+ CREATE OR REPLACE FUNCTION hstore_hash(hstore)
+ RETURNS integer
+ AS 'MODULE_PATHNAME','hstore_hash'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OPERATOR CLASS hash_hstore_ops
+ DEFAULT FOR TYPE hstore USING hash
+ AS
+ OPERATOR 1 = ,
+ FUNCTION 1 hstore_hash(hstore);
+
+ -- GiST support
+
+ CREATE TYPE ghstore;
+
+ CREATE OR REPLACE FUNCTION ghstore_in(cstring)
+ RETURNS ghstore
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE OR REPLACE FUNCTION ghstore_out(ghstore)
+ RETURNS cstring
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C STRICT IMMUTABLE;
+
+ CREATE TYPE ghstore (
+ INTERNALLENGTH = -1,
+ INPUT = ghstore_in,
+ OUTPUT = ghstore_out
+ );
+
+ CREATE OR REPLACE FUNCTION ghstore_compress(internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OR REPLACE FUNCTION ghstore_decompress(internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OR REPLACE FUNCTION ghstore_penalty(internal,internal,internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OR REPLACE FUNCTION ghstore_picksplit(internal, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OR REPLACE FUNCTION ghstore_union(internal, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OR REPLACE FUNCTION ghstore_same(internal, internal, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OR REPLACE FUNCTION ghstore_consistent(internal,internal,int,oid,internal)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OPERATOR CLASS gist_hstore_ops
+ DEFAULT FOR TYPE hstore USING gist
+ AS
+ OPERATOR 7 @> ,
+ OPERATOR 9 ?(hstore,text) ,
+ OPERATOR 10 ?|(hstore,text[]) ,
+ OPERATOR 11 ?&(hstore,text[]) ,
+ --OPERATOR 8 <@ ,
+ OPERATOR 13 @ ,
+ --OPERATOR 14 ~ ,
+ FUNCTION 1 ghstore_consistent (internal, internal, int, oid, internal),
+ FUNCTION 2 ghstore_union (internal, internal),
+ FUNCTION 3 ghstore_compress (internal),
+ FUNCTION 4 ghstore_decompress (internal),
+ FUNCTION 5 ghstore_penalty (internal, internal, internal),
+ FUNCTION 6 ghstore_picksplit (internal, internal),
+ FUNCTION 7 ghstore_same (internal, internal, internal),
+ STORAGE ghstore;
+
+ -- GIN support
+
+ CREATE OR REPLACE FUNCTION gin_extract_hstore(internal, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OR REPLACE FUNCTION gin_extract_hstore_query(internal, internal, int2, internal, internal)
+ RETURNS internal
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OR REPLACE FUNCTION gin_consistent_hstore(internal, int2, internal, int4, internal, internal)
+ RETURNS bool
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+
+ CREATE OPERATOR CLASS gin_hstore_ops
+ DEFAULT FOR TYPE hstore USING gin
+ AS
+ OPERATOR 7 @>,
+ OPERATOR 9 ?(hstore,text),
+ OPERATOR 10 ?|(hstore,text[]),
+ OPERATOR 11 ?&(hstore,text[]),
+ FUNCTION 1 bttextcmp(text,text),
+ FUNCTION 2 gin_extract_hstore(internal, internal),
+ FUNCTION 3 gin_extract_hstore_query(internal, internal, int2, internal, internal),
+ FUNCTION 4 gin_consistent_hstore(internal, int2, internal, int4, internal, internal),
+ STORAGE text;
diff --git a/contrib/hstore/hstore--unpackaged--1.0.sql b/contrib/hstore/hstore--unpackaged--1.0.sql
index ...3236543fe463af1ec2caf3d67bb8ed03e7802cf4 .
*** a/contrib/hstore/hstore--unpackaged--1.0.sql
--- b/contrib/hstore/hstore--unpackaged--1.0.sql
***************
*** 0 ****
--- 1,89 ----
+ /* contrib/hstore/hstore--unpackaged--1.0.sql */
+
+ ALTER EXTENSION hstore ADD cast (text[] as hstore);
+ ALTER EXTENSION hstore ADD function akeys(hstore);
+ ALTER EXTENSION hstore ADD function avals(hstore);
+ ALTER EXTENSION hstore ADD function defined(hstore,text);
+ ALTER EXTENSION hstore ADD function delete(hstore,hstore);
+ ALTER EXTENSION hstore ADD function delete(hstore,text);
+ ALTER EXTENSION hstore ADD function delete(hstore,text[]);
+ ALTER EXTENSION hstore ADD function each(hstore);
+ ALTER EXTENSION hstore ADD function exist(hstore,text);
+ ALTER EXTENSION hstore ADD function exists_all(hstore,text[]);
+ ALTER EXTENSION hstore ADD function exists_any(hstore,text[]);
+ ALTER EXTENSION hstore ADD function fetchval(hstore,text);
+ ALTER EXTENSION hstore ADD function ghstore_compress(internal);
+ ALTER EXTENSION hstore ADD function ghstore_consistent(internal,internal,integer,oid,internal);
+ ALTER EXTENSION hstore ADD function ghstore_decompress(internal);
+ ALTER EXTENSION hstore ADD function ghstore_in(cstring);
+ ALTER EXTENSION hstore ADD function ghstore_out(ghstore);
+ ALTER EXTENSION hstore ADD function ghstore_penalty(internal,internal,internal);
+ ALTER EXTENSION hstore ADD function ghstore_picksplit(internal,internal);
+ ALTER EXTENSION hstore ADD function ghstore_same(internal,internal,internal);
+ ALTER EXTENSION hstore ADD function ghstore_union(internal,internal);
+ ALTER EXTENSION hstore ADD function gin_consistent_hstore(internal,smallint,internal,integer,internal,internal);
+ ALTER EXTENSION hstore ADD function gin_extract_hstore(internal,internal);
+ ALTER EXTENSION hstore ADD function gin_extract_hstore_query(internal,internal,smallint,internal,internal);
+ ALTER EXTENSION hstore ADD function hs_concat(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hs_contained(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hs_contains(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hstore(record);
+ ALTER EXTENSION hstore ADD function hstore(text,text);
+ ALTER EXTENSION hstore ADD function hstore(text[]);
+ ALTER EXTENSION hstore ADD function hstore(text[],text[]);
+ ALTER EXTENSION hstore ADD function hstore_cmp(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hstore_eq(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hstore_ge(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hstore_gt(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hstore_hash(hstore);
+ ALTER EXTENSION hstore ADD function hstore_in(cstring);
+ ALTER EXTENSION hstore ADD function hstore_le(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hstore_lt(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hstore_ne(hstore,hstore);
+ ALTER EXTENSION hstore ADD function hstore_out(hstore);
+ ALTER EXTENSION hstore ADD function hstore_recv(internal);
+ ALTER EXTENSION hstore ADD function hstore_send(hstore);
+ ALTER EXTENSION hstore ADD function hstore_to_array(hstore);
+ ALTER EXTENSION hstore ADD function hstore_to_matrix(hstore);
+ ALTER EXTENSION hstore ADD function hstore_version_diag(hstore);
+ ALTER EXTENSION hstore ADD function isdefined(hstore,text);
+ ALTER EXTENSION hstore ADD function isexists(hstore,text);
+ ALTER EXTENSION hstore ADD function populate_record(anyelement,hstore);
+ ALTER EXTENSION hstore ADD function skeys(hstore);
+ ALTER EXTENSION hstore ADD function slice(hstore,text[]);
+ ALTER EXTENSION hstore ADD function slice_array(hstore,text[]);
+ ALTER EXTENSION hstore ADD function svals(hstore);
+ ALTER EXTENSION hstore ADD function tconvert(text,text);
+ ALTER EXTENSION hstore ADD operator #<#(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator #<=#(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator #=(anyelement,hstore);
+ ALTER EXTENSION hstore ADD operator #>#(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator #>=#(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator %#(NONE,hstore);
+ ALTER EXTENSION hstore ADD operator %%(NONE,hstore);
+ ALTER EXTENSION hstore ADD operator -(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator -(hstore,text);
+ ALTER EXTENSION hstore ADD operator -(hstore,text[]);
+ ALTER EXTENSION hstore ADD operator ->(hstore,text);
+ ALTER EXTENSION hstore ADD operator ->(hstore,text[]);
+ ALTER EXTENSION hstore ADD operator <>(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator <@(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator =(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator =>(text,text);
+ ALTER EXTENSION hstore ADD operator ?&(hstore,text[]);
+ ALTER EXTENSION hstore ADD operator ?(hstore,text);
+ ALTER EXTENSION hstore ADD operator ?|(hstore,text[]);
+ ALTER EXTENSION hstore ADD operator @(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator @>(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator class btree_hstore_ops using btree;
+ ALTER EXTENSION hstore ADD operator class gin_hstore_ops using gin;
+ ALTER EXTENSION hstore ADD operator class gist_hstore_ops using gist;
+ ALTER EXTENSION hstore ADD operator class hash_hstore_ops using hash;
+ ALTER EXTENSION hstore ADD operator family btree_hstore_ops using btree;
+ ALTER EXTENSION hstore ADD operator family gin_hstore_ops using gin;
+ ALTER EXTENSION hstore ADD operator family gist_hstore_ops using gist;
+ ALTER EXTENSION hstore ADD operator family hash_hstore_ops using hash;
+ ALTER EXTENSION hstore ADD operator ||(hstore,hstore);
+ ALTER EXTENSION hstore ADD operator ~(hstore,hstore);
+ ALTER EXTENSION hstore ADD type ghstore;
+ ALTER EXTENSION hstore ADD type hstore;
diff --git a/contrib/hstore/hstore.control b/contrib/hstore/hstore.control
index ...0a57b3487b4392ba73884979d665a6a892b38ebf .
*** a/contrib/hstore/hstore.control
--- b/contrib/hstore/hstore.control
***************
*** 0 ****
--- 1,5 ----
+ # hstore extension
+ comment = 'store sets of (key, value) pairs'
+ default_version = '1.0'
+ module_pathname = '$libdir/hstore'
+ relocatable = true
diff --git a/contrib/hstore/hstore.sql.in b/contrib/hstore/hstore.sql.in
index 5b39c189e112e57b245b878f64567af6fbd7347c..e69de29bb2d1d6434b8b29ae775ad8c2e48c5391 100644
*** a/contrib/hstore/hstore.sql.in
--- b/contrib/hstore/hstore.sql.in
***************
*** 1,530 ****
- /* contrib/hstore/hstore.sql.in */
-
- -- Adjust this setting to control where the objects get created.
- SET search_path = public;
-
- CREATE TYPE hstore;
-
- CREATE OR REPLACE FUNCTION hstore_in(cstring)
- RETURNS hstore
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_out(hstore)
- RETURNS cstring
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_recv(internal)
- RETURNS hstore
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_send(hstore)
- RETURNS bytea
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE TYPE hstore (
- INTERNALLENGTH = -1,
- INPUT = hstore_in,
- OUTPUT = hstore_out,
- RECEIVE = hstore_recv,
- SEND = hstore_send,
- STORAGE = extended
- );
-
- CREATE OR REPLACE FUNCTION hstore_version_diag(hstore)
- RETURNS integer
- AS 'MODULE_PATHNAME','hstore_version_diag'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION fetchval(hstore,text)
- RETURNS text
- AS 'MODULE_PATHNAME','hstore_fetchval'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR -> (
- LEFTARG = hstore,
- RIGHTARG = text,
- PROCEDURE = fetchval
- );
-
- CREATE OR REPLACE FUNCTION slice_array(hstore,text[])
- RETURNS text[]
- AS 'MODULE_PATHNAME','hstore_slice_to_array'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR -> (
- LEFTARG = hstore,
- RIGHTARG = text[],
- PROCEDURE = slice_array
- );
-
- CREATE OR REPLACE FUNCTION slice(hstore,text[])
- RETURNS hstore
- AS 'MODULE_PATHNAME','hstore_slice_to_hstore'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION isexists(hstore,text)
- RETURNS bool
- AS 'MODULE_PATHNAME','hstore_exists'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION exist(hstore,text)
- RETURNS bool
- AS 'MODULE_PATHNAME','hstore_exists'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR ? (
- LEFTARG = hstore,
- RIGHTARG = text,
- PROCEDURE = exist,
- RESTRICT = contsel,
- JOIN = contjoinsel
- );
-
- CREATE OR REPLACE FUNCTION exists_any(hstore,text[])
- RETURNS bool
- AS 'MODULE_PATHNAME','hstore_exists_any'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR ?| (
- LEFTARG = hstore,
- RIGHTARG = text[],
- PROCEDURE = exists_any,
- RESTRICT = contsel,
- JOIN = contjoinsel
- );
-
- CREATE OR REPLACE FUNCTION exists_all(hstore,text[])
- RETURNS bool
- AS 'MODULE_PATHNAME','hstore_exists_all'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR ?& (
- LEFTARG = hstore,
- RIGHTARG = text[],
- PROCEDURE = exists_all,
- RESTRICT = contsel,
- JOIN = contjoinsel
- );
-
- CREATE OR REPLACE FUNCTION isdefined(hstore,text)
- RETURNS bool
- AS 'MODULE_PATHNAME','hstore_defined'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION defined(hstore,text)
- RETURNS bool
- AS 'MODULE_PATHNAME','hstore_defined'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION delete(hstore,text)
- RETURNS hstore
- AS 'MODULE_PATHNAME','hstore_delete'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION delete(hstore,text[])
- RETURNS hstore
- AS 'MODULE_PATHNAME','hstore_delete_array'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION delete(hstore,hstore)
- RETURNS hstore
- AS 'MODULE_PATHNAME','hstore_delete_hstore'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR - (
- LEFTARG = hstore,
- RIGHTARG = text,
- PROCEDURE = delete
- );
-
- CREATE OPERATOR - (
- LEFTARG = hstore,
- RIGHTARG = text[],
- PROCEDURE = delete
- );
-
- CREATE OPERATOR - (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = delete
- );
-
- CREATE OR REPLACE FUNCTION hs_concat(hstore,hstore)
- RETURNS hstore
- AS 'MODULE_PATHNAME','hstore_concat'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR || (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hs_concat
- );
-
- CREATE OR REPLACE FUNCTION hs_contains(hstore,hstore)
- RETURNS bool
- AS 'MODULE_PATHNAME','hstore_contains'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hs_contained(hstore,hstore)
- RETURNS bool
- AS 'MODULE_PATHNAME','hstore_contained'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR @> (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hs_contains,
- COMMUTATOR = '<@',
- RESTRICT = contsel,
- JOIN = contjoinsel
- );
-
- CREATE OPERATOR <@ (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hs_contained,
- COMMUTATOR = '@>',
- RESTRICT = contsel,
- JOIN = contjoinsel
- );
-
- -- obsolete:
- CREATE OPERATOR @ (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hs_contains,
- COMMUTATOR = '~',
- RESTRICT = contsel,
- JOIN = contjoinsel
- );
-
- CREATE OPERATOR ~ (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hs_contained,
- COMMUTATOR = '@',
- RESTRICT = contsel,
- JOIN = contjoinsel
- );
-
- CREATE OR REPLACE FUNCTION tconvert(text,text)
- RETURNS hstore
- AS 'MODULE_PATHNAME','hstore_from_text'
- LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL)
-
- CREATE OR REPLACE FUNCTION hstore(text,text)
- RETURNS hstore
- AS 'MODULE_PATHNAME','hstore_from_text'
- LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL)
-
- CREATE OPERATOR => (
- LEFTARG = text,
- RIGHTARG = text,
- PROCEDURE = hstore
- );
-
- CREATE OR REPLACE FUNCTION hstore(text[],text[])
- RETURNS hstore
- AS 'MODULE_PATHNAME', 'hstore_from_arrays'
- LANGUAGE C IMMUTABLE; -- not STRICT; allows (keys,null)
-
- CREATE FUNCTION hstore(text[])
- RETURNS hstore
- AS 'MODULE_PATHNAME', 'hstore_from_array'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE CAST (text[] AS hstore)
- WITH FUNCTION hstore(text[]);
-
- CREATE OR REPLACE FUNCTION hstore(record)
- RETURNS hstore
- AS 'MODULE_PATHNAME', 'hstore_from_record'
- LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::recordtype)
-
- CREATE OR REPLACE FUNCTION hstore_to_array(hstore)
- RETURNS text[]
- AS 'MODULE_PATHNAME','hstore_to_array'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR %% (
- RIGHTARG = hstore,
- PROCEDURE = hstore_to_array
- );
-
- CREATE OR REPLACE FUNCTION hstore_to_matrix(hstore)
- RETURNS text[]
- AS 'MODULE_PATHNAME','hstore_to_matrix'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR %# (
- RIGHTARG = hstore,
- PROCEDURE = hstore_to_matrix
- );
-
- CREATE OR REPLACE FUNCTION akeys(hstore)
- RETURNS text[]
- AS 'MODULE_PATHNAME','hstore_akeys'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION avals(hstore)
- RETURNS text[]
- AS 'MODULE_PATHNAME','hstore_avals'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION skeys(hstore)
- RETURNS setof text
- AS 'MODULE_PATHNAME','hstore_skeys'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION svals(hstore)
- RETURNS setof text
- AS 'MODULE_PATHNAME','hstore_svals'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION each(IN hs hstore,
- OUT key text,
- OUT value text)
- RETURNS SETOF record
- AS 'MODULE_PATHNAME','hstore_each'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION populate_record(anyelement,hstore)
- RETURNS anyelement
- AS 'MODULE_PATHNAME', 'hstore_populate_record'
- LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::rectype,hstore)
-
- CREATE OPERATOR #= (
- LEFTARG = anyelement,
- RIGHTARG = hstore,
- PROCEDURE = populate_record
- );
-
- -- btree support
-
- CREATE OR REPLACE FUNCTION hstore_eq(hstore,hstore)
- RETURNS boolean
- AS 'MODULE_PATHNAME','hstore_eq'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_ne(hstore,hstore)
- RETURNS boolean
- AS 'MODULE_PATHNAME','hstore_ne'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_gt(hstore,hstore)
- RETURNS boolean
- AS 'MODULE_PATHNAME','hstore_gt'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_ge(hstore,hstore)
- RETURNS boolean
- AS 'MODULE_PATHNAME','hstore_ge'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_lt(hstore,hstore)
- RETURNS boolean
- AS 'MODULE_PATHNAME','hstore_lt'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_le(hstore,hstore)
- RETURNS boolean
- AS 'MODULE_PATHNAME','hstore_le'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION hstore_cmp(hstore,hstore)
- RETURNS integer
- AS 'MODULE_PATHNAME','hstore_cmp'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR = (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hstore_eq,
- COMMUTATOR = =,
- NEGATOR = <>,
- RESTRICT = eqsel,
- JOIN = eqjoinsel,
- MERGES,
- HASHES
- );
- CREATE OPERATOR <> (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hstore_ne,
- COMMUTATOR = <>,
- NEGATOR = =,
- RESTRICT = neqsel,
- JOIN = neqjoinsel
- );
-
- -- the comparison operators have funky names (and are undocumented)
- -- in an attempt to discourage anyone from actually using them. they
- -- only exist to support the btree opclass
-
- CREATE OPERATOR #<# (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hstore_lt,
- COMMUTATOR = #>#,
- NEGATOR = #>=#,
- RESTRICT = scalarltsel,
- JOIN = scalarltjoinsel
- );
- CREATE OPERATOR #<=# (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hstore_le,
- COMMUTATOR = #>=#,
- NEGATOR = #>#,
- RESTRICT = scalarltsel,
- JOIN = scalarltjoinsel
- );
- CREATE OPERATOR #># (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hstore_gt,
- COMMUTATOR = #<#,
- NEGATOR = #<=#,
- RESTRICT = scalargtsel,
- JOIN = scalargtjoinsel
- );
- CREATE OPERATOR #>=# (
- LEFTARG = hstore,
- RIGHTARG = hstore,
- PROCEDURE = hstore_ge,
- COMMUTATOR = #<=#,
- NEGATOR = #<#,
- RESTRICT = scalargtsel,
- JOIN = scalargtjoinsel
- );
-
- CREATE OPERATOR CLASS btree_hstore_ops
- DEFAULT FOR TYPE hstore USING btree
- AS
- OPERATOR 1 #<# ,
- OPERATOR 2 #<=# ,
- OPERATOR 3 = ,
- OPERATOR 4 #>=# ,
- OPERATOR 5 #># ,
- FUNCTION 1 hstore_cmp(hstore,hstore);
-
- -- hash support
-
- CREATE OR REPLACE FUNCTION hstore_hash(hstore)
- RETURNS integer
- AS 'MODULE_PATHNAME','hstore_hash'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OPERATOR CLASS hash_hstore_ops
- DEFAULT FOR TYPE hstore USING hash
- AS
- OPERATOR 1 = ,
- FUNCTION 1 hstore_hash(hstore);
-
- -- GiST support
-
- CREATE TYPE ghstore;
-
- CREATE OR REPLACE FUNCTION ghstore_in(cstring)
- RETURNS ghstore
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE OR REPLACE FUNCTION ghstore_out(ghstore)
- RETURNS cstring
- AS 'MODULE_PATHNAME'
- LANGUAGE C STRICT IMMUTABLE;
-
- CREATE TYPE ghstore (
- INTERNALLENGTH = -1,
- INPUT = ghstore_in,
- OUTPUT = ghstore_out
- );
-
- CREATE OR REPLACE FUNCTION ghstore_compress(internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OR REPLACE FUNCTION ghstore_decompress(internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OR REPLACE FUNCTION ghstore_penalty(internal,internal,internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OR REPLACE FUNCTION ghstore_picksplit(internal, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OR REPLACE FUNCTION ghstore_union(internal, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OR REPLACE FUNCTION ghstore_same(internal, internal, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OR REPLACE FUNCTION ghstore_consistent(internal,internal,int,oid,internal)
- RETURNS bool
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OPERATOR CLASS gist_hstore_ops
- DEFAULT FOR TYPE hstore USING gist
- AS
- OPERATOR 7 @> ,
- OPERATOR 9 ?(hstore,text) ,
- OPERATOR 10 ?|(hstore,text[]) ,
- OPERATOR 11 ?&(hstore,text[]) ,
- --OPERATOR 8 <@ ,
- OPERATOR 13 @ ,
- --OPERATOR 14 ~ ,
- FUNCTION 1 ghstore_consistent (internal, internal, int, oid, internal),
- FUNCTION 2 ghstore_union (internal, internal),
- FUNCTION 3 ghstore_compress (internal),
- FUNCTION 4 ghstore_decompress (internal),
- FUNCTION 5 ghstore_penalty (internal, internal, internal),
- FUNCTION 6 ghstore_picksplit (internal, internal),
- FUNCTION 7 ghstore_same (internal, internal, internal),
- STORAGE ghstore;
-
- -- GIN support
-
- CREATE OR REPLACE FUNCTION gin_extract_hstore(internal, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OR REPLACE FUNCTION gin_extract_hstore_query(internal, internal, int2, internal, internal)
- RETURNS internal
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OR REPLACE FUNCTION gin_consistent_hstore(internal, int2, internal, int4, internal, internal)
- RETURNS bool
- AS 'MODULE_PATHNAME'
- LANGUAGE C IMMUTABLE STRICT;
-
- CREATE OPERATOR CLASS gin_hstore_ops
- DEFAULT FOR TYPE hstore USING gin
- AS
- OPERATOR 7 @>,
- OPERATOR 9 ?(hstore,text),
- OPERATOR 10 ?|(hstore,text[]),
- OPERATOR 11 ?&(hstore,text[]),
- FUNCTION 1 bttextcmp(text,text),
- FUNCTION 2 gin_extract_hstore(internal, internal),
- FUNCTION 3 gin_extract_hstore_query(internal, internal, int2, internal, internal),
- FUNCTION 4 gin_consistent_hstore(internal, int2, internal, int4, internal, internal),
- STORAGE text;
--- 0 ----
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index 58a79675267aecc63ff8f50fe8bdd33ec44c567b..fb6bb59f8a0045b778d6a9bf2e72f7a036752383 100644
*** a/contrib/hstore/sql/hstore.sql
--- b/contrib/hstore/sql/hstore.sql
***************
*** 1,12 ****
! --
! -- first, define the datatype. Turn off echoing so that expected file
! -- does not depend on contents of hstore.sql.
! --
! SET client_min_messages = warning;
! \set ECHO none
! \i hstore.sql
! \set ECHO all
! RESET client_min_messages;
set escape_string_warning=off;
--- 1,4 ----
! CREATE EXTENSION hstore;
set escape_string_warning=off;
diff --git a/contrib/hstore/uninstall_hstore.sql b/contrib/hstore/uninstall_hstore.sql
index a03e43164f8b315b2fccb533adc3034c0124bb07..e69de29bb2d1d6434b8b29ae775ad8c2e48c5391 100644
*** a/contrib/hstore/uninstall_hstore.sql
--- b/contrib/hstore/uninstall_hstore.sql
***************
*** 1,86 ****
- /* contrib/hstore/uninstall_hstore.sql */
-
- -- Adjust this setting to control where the objects get dropped.
- SET search_path = public;
-
- DROP OPERATOR CLASS gist_hstore_ops USING gist CASCADE;
- DROP OPERATOR CLASS gin_hstore_ops USING gin CASCADE;
- DROP OPERATOR CLASS hash_hstore_ops USING hash CASCADE;
- DROP OPERATOR CLASS btree_hstore_ops USING btree CASCADE;
-
- DROP OPERATOR - ( hstore, text );
- DROP OPERATOR - ( hstore, text[] );
- DROP OPERATOR - ( hstore, hstore );
- DROP OPERATOR ? ( hstore, text );
- DROP OPERATOR ?& ( hstore, text[] );
- DROP OPERATOR ?| ( hstore, text[] );
- DROP OPERATOR -> ( hstore, text );
- DROP OPERATOR -> ( hstore, text[] );
- DROP OPERATOR || ( hstore, hstore );
- DROP OPERATOR @> ( hstore, hstore );
- DROP OPERATOR <@ ( hstore, hstore );
- DROP OPERATOR @ ( hstore, hstore );
- DROP OPERATOR ~ ( hstore, hstore );
- DROP OPERATOR => ( text, text );
- DROP OPERATOR #= ( anyelement, hstore );
- DROP OPERATOR %% ( NONE, hstore );
- DROP OPERATOR %# ( NONE, hstore );
- DROP OPERATOR = ( hstore, hstore );
- DROP OPERATOR <> ( hstore, hstore );
- DROP OPERATOR #<# ( hstore, hstore );
- DROP OPERATOR #<=# ( hstore, hstore );
- DROP OPERATOR #># ( hstore, hstore );
- DROP OPERATOR #>=# ( hstore, hstore );
-
- DROP CAST (text[] AS hstore);
-
- DROP FUNCTION hstore_eq(hstore,hstore);
- DROP FUNCTION hstore_ne(hstore,hstore);
- DROP FUNCTION hstore_gt(hstore,hstore);
- DROP FUNCTION hstore_ge(hstore,hstore);
- DROP FUNCTION hstore_lt(hstore,hstore);
- DROP FUNCTION hstore_le(hstore,hstore);
- DROP FUNCTION hstore_cmp(hstore,hstore);
- DROP FUNCTION hstore_hash(hstore);
- DROP FUNCTION slice_array(hstore,text[]);
- DROP FUNCTION slice(hstore,text[]);
- DROP FUNCTION fetchval(hstore,text);
- DROP FUNCTION isexists(hstore,text);
- DROP FUNCTION exist(hstore,text);
- DROP FUNCTION exists_any(hstore,text[]);
- DROP FUNCTION exists_all(hstore,text[]);
- DROP FUNCTION isdefined(hstore,text);
- DROP FUNCTION defined(hstore,text);
- DROP FUNCTION delete(hstore,text);
- DROP FUNCTION delete(hstore,text[]);
- DROP FUNCTION delete(hstore,hstore);
- DROP FUNCTION hs_concat(hstore,hstore);
- DROP FUNCTION hs_contains(hstore,hstore);
- DROP FUNCTION hs_contained(hstore,hstore);
- DROP FUNCTION tconvert(text,text);
- DROP FUNCTION hstore(text,text);
- DROP FUNCTION hstore(text[],text[]);
- DROP FUNCTION hstore_to_array(hstore);
- DROP FUNCTION hstore_to_matrix(hstore);
- DROP FUNCTION hstore(record);
- DROP FUNCTION hstore(text[]);
- DROP FUNCTION akeys(hstore);
- DROP FUNCTION avals(hstore);
- DROP FUNCTION skeys(hstore);
- DROP FUNCTION svals(hstore);
- DROP FUNCTION each(hstore);
- DROP FUNCTION populate_record(anyelement,hstore);
- DROP FUNCTION ghstore_compress(internal);
- DROP FUNCTION ghstore_decompress(internal);
- DROP FUNCTION ghstore_penalty(internal,internal,internal);
- DROP FUNCTION ghstore_picksplit(internal, internal);
- DROP FUNCTION ghstore_union(internal, internal);
- DROP FUNCTION ghstore_same(internal, internal, internal);
- DROP FUNCTION ghstore_consistent(internal,internal,int,oid,internal);
- DROP FUNCTION gin_consistent_hstore(internal, int2, internal, int4, internal, internal);
- DROP FUNCTION gin_extract_hstore(internal, internal);
- DROP FUNCTION gin_extract_hstore_query(internal, internal, smallint, internal, internal);
- DROP FUNCTION hstore_version_diag(hstore);
-
- DROP TYPE hstore CASCADE;
- DROP TYPE ghstore CASCADE;
--- 0 ----
Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> I think it's better to keep it working as a textual substitution.
Thinking about this some more, it has the advantage that the effects of
the control file settings are kept within the script file processing and
pg_extension catalog. The only backend impact is the dependency
tracking.
> OK, so with that, attached is an example of the complete conversion diff
> for a contrib module (hstore in particular). Although "git status"
I see you're not using the @extschema@ placeholder in the upgrade
script. It is intentional? It's been common wisdom and practice to
edit the SQL file of any contrib or third party module to have it
installed in your preferred schema…
> reports hstore--1.0.sql as being a rename of hstore.sql.in, "git diff"
> doesn't seem to be exceedingly bright about presenting it that way :-(.
> But actually the change in that script other than renaming is just
> removing the "set search_path" command and adjusting the header comment.
And we don't have to rely on hstore.sql.in file anymore as the change is
done by the backend side of things. That's a very good point for the
windows build system I think.
> Barring objections, I'll press on with fixing the rest of them.
I think you'd be interested into this reworked SQL query. It should be
providing exactly the script file you need as an upgrade from unpackaged.
I took the time to finish this query (filter out array types, some
replacement in operator classes and families descriptions) because I
think it would be nice to offer it in the docs. It could even be
proposed as a function :)
I hope you'll find it useful, but it could well be you finished the
search&replace of all contribs already (ah, emacs keyboard macros).
CREATE EXTENSION hstore;
CREATE SCHEMA empty_place; SET search_path TO empty_place;
WITH objs AS ( SELECT classid, 'ALTER EXTENSION ' || E.extname || ' ADD ' ||
replace(pg_describe_object(classid,objid, 0), N.nspname, '@extschema@') || ';' as sql FROM
pg_dependD JOIN pg_extension E ON D.refobjid = E.oid AND D.refclassid = E.tableoid
JOIN pg_namespace N ON E.extnamespace = N.oid WHERE CASE WHEN classid = 'pg_catalog.pg_type'::regclass
THEN (SELECT typarray FROM pg_type WHERE oid=objid) != 0 ELSE true END AND deptype =
'e'AND E.extname = 'hstore' ) SELECT CASE WHEN classid IN ('pg_catalog.pg_opclass'::regclass,
'pg_catalog.pg_opfamily'::regclass) THEN replace(sql, 'for access method', 'using') ELSE sql END FROM
objs;
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> OK, so with that, attached is an example of the complete conversion diff
>> for a contrib module (hstore in particular). Although "git status"
> I see you're not using the @extschema@ placeholder in the upgrade
> script. It is intentional?
Yes, it should be unnecessary given the search_path setup done by
execute_extension_script(). Also, I think that a relocatable
extension's script should not be subject to @extschema@ substitution,
no matter what.
> I think you'd be interested into this reworked SQL query. It should be
> providing exactly the script file you need as an upgrade from unpackaged.
This seems overly complicated. I have a version of it that I'll publish
as soon as I've tested it on all the contrib modules ...
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Yes, it should be unnecessary given the search_path setup done by > execute_extension_script(). Also, I think that a relocatable > extension's script should not be subject to @extschema@ substitution, > no matter what. Oh I'm just realizing that my reasoning predates the search_path strong guarantees at CREATE EXTENSION time. >> I think you'd be interested into this reworked SQL query. It should be >> providing exactly the script file you need as an upgrade from unpackaged. > > This seems overly complicated. I have a version of it that I'll publish > as soon as I've tested it on all the contrib modules ... Nice. I confess I worked out mine from my last patch where I still have the INTERNAL dependencies setup etc, so maybe that makes it more complex that it needs to be. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Feb 13, 2011, at 11:34 AM, Tom Lane wrote: > OK, so with that, attached is an example of the complete conversion diff > for a contrib module (hstore in particular). Although "git status" > reports hstore--1.0.sql as being a rename of hstore.sql.in, "git diff" > doesn't seem to be exceedingly bright about presenting it that way :-(. > But actually the change in that script other than renaming is just > removing the "set search_path" command and adjusting the header comment. I sure would like it if the install script with no version in it corresponded to the latest version. Otherwise, one mustrename the file every time one does a release. And as you're noting, you lose Git history that way. Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> I sure would like it if the install script with no version in it corresponded to the latest version. Otherwise, one
mustrename the file every time one does a release. And as you're noting, you lose Git history that way.
(1) git does know it's a rename, it's just not default for git diff to
show it that way.
(2) I think that the normal use-case would not involve removing the old
file, so this is moot anyhow.
regards, tom lane
On Feb 13, 2011, at 4:46 PM, Tom Lane wrote: >> I sure would like it if the install script with no version in it corresponded to the latest version. Otherwise, one mustrename the file every time one does a release. And as you're noting, you lose Git history that way. > > (1) git does know it's a rename, it's just not default for git diff to > show it that way. I see, looks like one can `git diff --follow` to see it that way: http://stackoverflow.com/questions/2314652/ > (2) I think that the normal use-case would not involve removing the old > file, so this is moot anyhow. Oh. So one normally will ship, for an extension "foo", only "foo.sql" and any necssary upgrade scripts? Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Feb 13, 2011, at 4:46 PM, Tom Lane wrote:
>> (2) I think that the normal use-case would not involve removing the old
>> file, so this is moot anyhow.
> Oh. So one normally will ship, for an extension "foo", only "foo.sql" and any necssary upgrade scripts?
I think after a couple of releases you'd be shipping something like
foo--1.0.sqlfoo--1.1.sqlfoo--1.0--1.1.sqlfoo--2.0.sqlfoo--1.1--2.0.sql
and it'll soon get to be a mess if your SCM doesn't clearly distinguish
which is which.
Also, as I mentioned before, once you've branched off foo--1.1.sql
it's probably a mistake to be changing foo--1.0.sql anymore anyway.
I suppose if you really wanted foo.sql to always be the head version,
you could do something like "cp foo.sql foo--$VERSION.sql" as part of
the build process in the Makefile.
regards, tom lane
On Feb 13, 2011, at 4:59 PM, Tom Lane wrote: > I think after a couple of releases you'd be shipping something like > > foo--1.0.sql > foo--1.1.sql > foo--1.0--1.1.sql > foo--2.0.sql > foo--1.1--2.0.sql > > and it'll soon get to be a mess if your SCM doesn't clearly distinguish > which is which. > > Also, as I mentioned before, once you've branched off foo--1.1.sql > it's probably a mistake to be changing foo--1.0.sql anymore anyway. > > I suppose if you really wanted foo.sql to always be the head version, > you could do something like "cp foo.sql foo--$VERSION.sql" as part of > the build process in the Makefile. That would be okay. Is $EXTVERSION still defined in the Makefile? ($VERSION is the PostgreSQL version, of course). Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Feb 13, 2011, at 4:59 PM, Tom Lane wrote:
>> I suppose if you really wanted foo.sql to always be the head version,
>> you could do something like "cp foo.sql foo--$VERSION.sql" as part of
>> the build process in the Makefile.
> That would be okay. Is $EXTVERSION still defined in the Makefile? ($VERSION is the PostgreSQL version, of course).
I haven't set the contrib makefiles up that way, but of course you could
do it if you wanted to in your own makefiles.
regards, tom lane
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Also, I've been looking at the pg_available_extensions issue a bit.
>> I don't yet have a proposal for exactly how we ought to redefine it,
>> but I did notice that the existing code is terribly confused by
>> secondary control files: it doesn't realize that they're not primary
>> control files, so you get e.g. hstore and hstore-1.0 as separate
>> listings.
> I'd think that's it's a good idea if dealt with "correctly" because now
> that ALTER EXTENSION UPDATE can deal with more than one target VERSION
> I expect the view to show each available update here.
Thinking about this some more ... it seems like we now need two separate
views, because there is some information that could change per-version,
and some that really only makes sense at the per-extension level.
For instance, we could have pg_available_extensions that produces a row
per primary control file, with columns
name (view's effective primary key)default_versioninstalled_version (NULL if not installed)comment
(if one is present in primary control file)
and pg_available_extension_versions that produces a row per install
script, with columns
nameversion ((name, version) is primary key)commentrequiresrelocatableschema
where the last four columns can vary across versions due to secondary
control files.
Or we could combine these into just one view with pkey (name, version),
but then the default_version and installed_version columns would be the
same across all rows with the same extension name, which seems confusing
and unnormalized.
> If possible adding the "update chain sequence" information as computed
> in the code would be great. Because we can't ask people to figure that
> out all by themselves, the best way to check your upgrading setup is
> fine would be to run SELECT * FROM pg_available_extensions; and read the
> result.
I think this is probably a good thing to provide but it shouldn't go in
either of the above views, on two grounds: (1) it's going to be
relatively expensive to compute, and most people won't need it; (2)
the views could only sensibly cover paths from current version to listed
version, which isn't good enough. What an extension author actually
wants to know is "have I introduced any undesirable update paths
anywhere?" I suggest instead that we invent a SRF, say
pg_extension_update_paths(extension_name text) returns setof record,
that returns a row for each pair of distinct version names found in
the extension's install and update scripts, with columns
source version nametarget other version namepath update path from source to target, or NULL if
none
The output might look like this:
1.0 1.1 1.0--1.11.1 1.2 1.1--1.2unpackaged 1.0 unpackaged--1.01.0 1.2
1.0--1.1--1.21.0 unpackaged1.1 1.01.1 unpackaged1.2 1.11.2 1.01.2
unpackagedunpackaged 1.1 unpackaged--1.0--1.1unpackaged 1.2 unpackaged--1.0--1.1--1.2
where the first three rows correspond to available update scripts and
the rest are synthesized.
(Looking at this, it looks like it could get pretty bulky pretty
quickly. Maybe we should eliminate all rows in which the path would be
NULL? Or just eliminate rows in which the target doesn't have an
install script, which would remove the three rows with target =
unpackaged in the above example?)
Thoughts?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Thinking about this some more ... it seems like we now need two separate > views, because there is some information that could change per-version, > and some that really only makes sense at the per-extension level. Makes sense. > For instance, we could have pg_available_extensions that produces a row > per primary control file, with columns > > name (view's effective primary key) > default_version > installed_version (NULL if not installed) > comment (if one is present in primary control file) Check. > and pg_available_extension_versions that produces a row per install > script, with columns > > name > version ((name, version) is primary key) > comment > requires > relocatable > schema > > where the last four columns can vary across versions due to secondary > control files. I like this primary key because that's also the one for debian stable distributions :) Joking apart, aren't we missing the encoding somewhere? > Or we could combine these into just one view with pkey (name, version), > but then the default_version and installed_version columns would be the > same across all rows with the same extension name, which seems confusing > and unnormalized. Let's go with two views. Once we have that it's easy enough to LEFT JOIN if we want a summarized view. Maybe we could even revive \dX. Without pattern it would show the short form (pg_available_extension) and given a pattern pg_available_extension_versions. > I suggest instead that we invent a SRF, say > pg_extension_update_paths(extension_name text) returns setof record, > that returns a row for each pair of distinct version names found in > the extension's install and update scripts, with columns Agreed. > source version name > target other version name > path update path from source to target, or NULL if none > > The output might look like this: > > 1.0 1.1 1.0--1.1 > 1.1 1.2 1.1--1.2 > unpackaged 1.0 unpackaged--1.0 > 1.0 1.2 1.0--1.1--1.2 > 1.0 unpackaged > 1.1 1.0 > 1.1 unpackaged > 1.2 1.1 > 1.2 1.0 > 1.2 unpackaged > unpackaged 1.1 unpackaged--1.0--1.1 > unpackaged 1.2 unpackaged--1.0--1.1--1.2 What about having this chain column be an array of version strings? If you want to see it this way, use array_to_string(path, '--')… > where the first three rows correspond to available update scripts and > the rest are synthesized. The ordering is not clearly apparent, but I don't think it matters. > (Looking at this, it looks like it could get pretty bulky pretty > quickly. Maybe we should eliminate all rows in which the path would be > NULL? Or just eliminate rows in which the target doesn't have an > install script, which would remove the three rows with target = > unpackaged in the above example?) Removing NULL path rows seems the best option to me. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> and pg_available_extension_versions that produces a row per install
>> script, with columns
>>
>> name
>> version ((name, version) is primary key)
>> comment
>> requires
>> relocatable
>> schema
>>
>> where the last four columns can vary across versions due to secondary
>> control files.
> I like this primary key because that's also the one for debian stable
> distributions :) Joking apart, aren't we missing the encoding somewhere?
I intentionally left out columns that seem like extension implementation
details rather than things users of the extension need to know. Hence,
no directory, encoding, or module_pathname. There's no fundamental
reason not to include these, I guess, although maybe there could be some
security objection to showing directory. But do we need 'em?
>> The output might look like this:
>>
>> 1.0 1.1 1.0--1.1
>> 1.1 1.2 1.1--1.2
>> unpackaged 1.0 unpackaged--1.0
>> 1.0 1.2 1.0--1.1--1.2
>> 1.0 unpackaged
>> 1.1 1.0
>> 1.1 unpackaged
>> 1.2 1.1
>> 1.2 1.0
>> 1.2 unpackaged
>> unpackaged 1.1 unpackaged--1.0--1.1
>> unpackaged 1.2 unpackaged--1.0--1.1--1.2
> What about having this chain column be an array of version strings? If
> you want to see it this way, use array_to_string(path, '--')…
I was thinking the other way --- you can split it with
regexp_split_to_array (or regexp_split_to_table) if you want to, but
having a compact human-readable form is probably the most important
case. It's not a big deal either way though. Anyone else want to
vote?
>> where the first three rows correspond to available update scripts and
>> the rest are synthesized.
> The ordering is not clearly apparent, but I don't think it matters.
Sorry, I only meant that in this example I put the rows coming from
single scripts first. I didn't mean to suggest that the function would
guarantee any particular output ordering.
>> (Looking at this, it looks like it could get pretty bulky pretty
>> quickly. Maybe we should eliminate all rows in which the path would be
>> NULL? Or just eliminate rows in which the target doesn't have an
>> install script, which would remove the three rows with target =
>> unpackaged in the above example?)
> Removing NULL path rows seems the best option to me.
Yeah, possibly. I'm a bit concerned about cases where the author meant
to provide an update path and forgot: it would be fairly obvious in this
representation but maybe you could keep making the same oversight if the
row's not there at all. Also, it's easy enough to write "where path is
not null" if you want to filter the rows that way.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I intentionally left out columns that seem like extension implementation > details rather than things users of the extension need to know. Hence, > no directory, encoding, or module_pathname. There's no fundamental > reason not to include these, I guess, although maybe there could be some > security objection to showing directory. But do we need 'em? I share your view on the directory and module_pathname, but though that maybe encoding could be the source of subtle errors and that users would be happy to know what PostgreSQL is using. But well, that's not holding enough water now that I think some more about it. > I was thinking the other way --- you can split it with > regexp_split_to_array (or regexp_split_to_table) if you want to, but > having a compact human-readable form is probably the most important > case. It's not a big deal either way though. Anyone else want to > vote? I'm not set one way or the other and won't share another opinion on that :) > Sorry, I only meant that in this example I put the rows coming from > single scripts first. I didn't mean to suggest that the function would > guarantee any particular output ordering. Ok. > Yeah, possibly. I'm a bit concerned about cases where the author meant > to provide an update path and forgot: it would be fairly obvious in this > representation but maybe you could keep making the same oversight if the > row's not there at all. Also, it's easy enough to write "where path is > not null" if you want to filter the rows that way. I would expect the author to check with something like WHERE installed = '1.0' and available = '1.2' But again, the preference here is about either "cluttering" the default output more than necessary or having to type a WHERE clause to double check your setup. No strong opinion here, just a preference… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> [ about omitting rows for which there is no update path ]
>> Yeah, possibly. I'm a bit concerned about cases where the author meant
>> to provide an update path and forgot: it would be fairly obvious in this
>> representation but maybe you could keep making the same oversight if the
>> row's not there at all. Also, it's easy enough to write "where path is
>> not null" if you want to filter the rows that way.
> I would expect the author to check with something like
> WHERE installed = '1.0' and available = '1.2'
I don't really think that's a behavior we want to encourage. ISTM the
cases that are going to be trouble are paths you failed to think about,
and therefore what you want to do is look over the whole output set to
see if there are any surprising paths...
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I don't really think that's a behavior we want to encourage. ISTM the > cases that are going to be trouble are paths you failed to think about, > and therefore what you want to do is look over the whole output set to > see if there are any surprising paths... Mmm, yes. Ok. -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
I wrote:
> Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
>> I think you'd be interested into this reworked SQL query. It should be
>> providing exactly the script file you need as an upgrade from unpackaged.
> This seems overly complicated. I have a version of it that I'll publish
> as soon as I've tested it on all the contrib modules ...
Just for the archives' sake: the '@extschema@' business did turn out to
be important, at least for tsearch2 where it's necessary to distinguish
the objects it's dealing with from similarly-named objects in
pg_catalog. So this is what I used to generate the "unpackaged"
scripts. Some of them needed manual adjustment later to cover cases
where 9.1 had diverged from 9.0, but the script could hardly be expected
to know about that.
#! /bin/sh
MOD="$1"
psql -d testdb -c "create extension $MOD"
(
echo "/* contrib/$MOD/$MOD--unpackaged--1.0.sql */"
echo
psql -A -t -d testdb -c " SELECT 'ALTER EXTENSION ' || E.extname || ' ADD ' || replace(pg_describe_object(classid,
objid,0), N.nspname, '@extschema@') || ';' FROM pg_depend D JOIN pg_extension E ON
D.refobjid= E.oid AND D.refclassid = E.tableoid JOIN pg_namespace N ON E.extnamespace
=N.oid WHERE deptype = 'e' AND E.extname = '$MOD' ORDER BY D.objid
" | sed -e 's/ADD cast from \(.*\) to \(.*\);/ADD cast (\1 as \2);/' \-e 's/ for access method / using /'
) > contrib/$MOD/$MOD--unpackaged--1.0.sql
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Just for the archives' sake: the '@extschema@' business did turn out to
> be important, at least for tsearch2 where it's necessary to distinguish
> the objects it's dealing with from similarly-named objects in
> pg_catalog. So this is what I used to generate the "unpackaged"
> scripts. Some of them needed manual adjustment later to cover cases
> where 9.1 had diverged from 9.0, but the script could hardly be expected
> to know about that.
Good to know that even contrib needs that!
> #! /bin/sh
>
> MOD="$1"
>
> psql -d testdb -c "create extension $MOD"
>
> (
> echo "/* contrib/$MOD/$MOD--unpackaged--1.0.sql */"
> echo
>
> psql -A -t -d testdb -c "
> SELECT 'ALTER EXTENSION ' || E.extname || ' ADD '
> || replace(pg_describe_object(classid, objid, 0),
> N.nspname, '@extschema@')
> || ';'
> FROM pg_depend D
> JOIN pg_extension E ON D.refobjid = E.oid
> AND D.refclassid = E.tableoid
> JOIN pg_namespace N ON E.extnamespace = N.oid
> WHERE deptype = 'e' AND E.extname = '$MOD'
> ORDER BY D.objid
> " | sed -e 's/ADD cast from \(.*\) to \(.*\);/ADD cast (\1 as \2);/' \
> -e 's/ for access method / using /'
> ) > contrib/$MOD/$MOD--unpackaged--1.0.sql
Ah well sed makes it simpler to read, but it won't be usable in windows.
I now realize also that the second version of this query did some
useless array type filtering. Adding a replace() step in the query
would not be that ugly I guess, if we wanted to make it so.
Do we want to add such a query in the docs to help pgfoundry authors to
write their own 'from unpackaged' scripts?
CREATE OR REPLACE FUNCTION extension_unpackaged_upgrade_script(text) RETURNS SETOF text LANGUAGE SQL
AS $$
WITH objs AS ( SELECT 'ALTER EXTENSION ' || E.extname || ' ADD ' || replace(pg_describe_object(classid, objid, 0),
N.nspname, '@extschema@') || ';' AS d FROM pg_depend D JOIN pg_extension E ON D.refobjid =
E.oid AND D.refclassid = E.tableoid JOIN pg_namespace N ON E.extnamespace = N.oid WHERE
deptype= 'e' AND E.extname = $1 ORDER BY D.objid
)
SELECT regexp_replace(replace(d, ' for access method ', ' using '), 'ADD cast from (.*) to (.*);',
E'ADD cast (\\1 as \\2);') FROM objs
$$;
dim=# select * from extension_unpackaged_upgrade_script('lo'); extension_unpackaged_upgrade_script
---------------------------------------------------------------------ALTER EXTENSION lo ADD type @extschema@.lo;ALTER
EXTENSIONlo ADD function @extschema@.lo_oid(@extschema@.lo);ALTER EXTENSION lo ADD function @extschema@.lo_manage();
(3 rows)
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 02/15/2011 04:49 PM, Dimitri Fontaine wrote: > > Ah well sed makes it simpler to read, but it won't be usable in windows. You can make perl do the same stuff (and perl has psed anyway), and perl is required for MSVC builds. cheers andrew
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Do we want to add such a query in the docs to help pgfoundry authors to
> write their own 'from unpackaged' scripts?
[ scratches head ... ] Why is your version generating so many
unnecessary @extschema@ uses?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
> [ scratches head ... ] Why is your version generating so many
> unnecessary @extschema@ uses?
I just ran create table tomlist as select your query and create table
dimlist as select my query, then:
dim=# select * from tomlist except select * from dimlist; desc
--------------------------------------------------------------------------------------------------ALTER EXTENSION
hstoreADD operator family @extschema@.gin_hstore_ops for access method gin;ALTER EXTENSION hstore ADD operator class
@extschema@.btree_hstore_opsfor access method btree;ALTER EXTENSION hstore ADD operator family
@extschema@.hash_hstore_opsfor access method hash;ALTER EXTENSION hstore ADD operator class @extschema@.gist_hstore_ops
foraccess method gist;ALTER EXTENSION hstore ADD operator family @extschema@.gist_hstore_ops for access method
gist;ALTEREXTENSION hstore ADD cast from text[] to @extschema@.hstore;ALTER EXTENSION hstore ADD operator class
@extschema@.gin_hstore_opsfor access method gin;ALTER EXTENSION hstore ADD operator family @extschema@.btree_hstore_ops
foraccess method btree;ALTER EXTENSION hstore ADD operator class @extschema@.hash_hstore_ops for access method hash;
(9 rows)
No difference on @extschema@ use here.
dim=# select t.desc, d.desc from tomlist t natural join dimlist d limit 1;
-[ RECORD 1 ]---------------------------------------------
desc | ALTER EXTENSION hstore ADD type @extschema@.hstore;
desc | ALTER EXTENSION hstore ADD type @extschema@.hstore;
dim=# select t.desc, d.desc from tomlist t natural join dimlist d limit 1 offset 10;
-[ RECORD 1 ]---------------------------------------------------------------------------
desc | ALTER EXTENSION hstore ADD function @extschema@.slice(@extschema@.hstore,text[]);
desc | ALTER EXTENSION hstore ADD function @extschema@.slice(@extschema@.hstore,text[]);
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> [ scratches head ... ] Why is your version generating so many
>> unnecessary @extschema@ uses?
> I just ran create table tomlist as select your query and create table
> dimlist as select my query, then:
> ...
> No difference on @extschema@ use here.
Well, when I did it I only got @extschema@ uses in tsearch2 (see the
committed update scripts), so there's *something* different about what
you're doing. I'm unsure what.
regards, tom lane