Re: [HACKERS] Pl/Python -- current maintainer?
James Robinson schrieb: I see neilc has hacked on it very recently to reduce memory leaks. I take that as both good and bad signs. We're a [ small ] python shop, and would be most interested in being able to simplify our life through doing some things in plpython instead of pl/pgsql where appropriate. Keeping our constants and so forth in the appropriate python module would make things ever so much simpler here and there at the very least. But we've never hacked on the backend, nor at the C python API level. But I see no reason why not to start now -- lurked here for many a year. For example, I see that plpython functions cannot be declared to return void. That can't be too tough to remedy. Implementing the DBI 2.0 API interface to SPI can wait another day. Also have a look at: http://python.projects.postgresql.org/ it needs some more love too but has high potential. Maybe it can become next generation pl/pythonu? Would be nice. And with even more love the restricted python from zope could be ported so there could be a pl/python again :-) Ok, just haluzinating ;) Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Pl/Python -- current maintainer?
Ühel kenal päeval, L, 2006-02-25 kell 10:09, kirjutas Tino Wildenhain: James Robinson schrieb: I see neilc has hacked on it very recently to reduce memory leaks. I take that as both good and bad signs. We're a [ small ] python shop, and would be most interested in being able to simplify our life through doing some things in plpython instead of pl/pgsql where appropriate. Keeping our constants and so forth in the appropriate python module would make things ever so much simpler here and there at the very least. But we've never hacked on the backend, nor at the C python API level. But I see no reason why not to start now -- lurked here for many a year. For example, I see that plpython functions cannot be declared to return void. That can't be too tough to remedy. Implementing the DBI 2.0 API interface to SPI can wait another day. Also have a look at: http://python.projects.postgresql.org/ it needs some more love too but has high potential. Yes, this one seems to be the pl/python done right :) But it also suffers a little from trying to do too much at one time, and so moves a little slow :( Maybe it can become next generation pl/pythonu? Would be nice. I guess that it would need to be at least somewhat backwards compatible to replace current pl/pythonu. And with even more love the restricted python from zope could be ported so there could be a pl/python again :-) That would be nice, but actually not very high on my list of wishes, as I mostly want to use plpythony as a replacement for writing C funtions, and we probably will never have restricted C -- Hannu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] What's with this lib suffix?
I have a PL/Java bug entry from Peter E. that reads It is customary in PostgreSQL land and elsewhere, that dynamically loadable modules do not have a lib prefix (compare plpgsql.so, pltcl.so, etc.). So I suggest that the shared object installed by PL/Java also be called exactly pljava.so.. I'd like to follow customary practices but as it turns out, I'm not the one adding the 'lib' prefix. It's done by the Makefile.shlib that comes bundled with pgxs. Here you can read things like: # Default shlib naming convention used by the majority of platforms shlib = lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION) shlib_major= lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION) shlib_bare = lib$(NAME)$(DLSUFFIX) and sure enough, that's what gets used too. So what goes? Personally, I'd prefer to keep the 'lib' prefix since it really *is* the default naming convention on all *n[iu]x platforms I've been in contact with. Not so on Windows though so perhaps that should change in Makefile.shlib? I'm confused. Kind Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Adding an ignore list to pg_restore, prototype patch #1
Hi again,
Martin Pitt [2006-02-19 14:39 +0100]:
Since this changes the behaviour of pg_restore, this should probably
become an option, e. g. -D / --ignore-existing-table-data. I'll do
this if you agree to the principle of the current patch.
I improved the patch now to only ignore TABLE DATA for existing tables
if '-X ignore-existing-tables' is specified. I also updated the
documentation.
Since this doesn't change the default behaviour now any more, I would
like to put this patch into the Debian packages to provide automatic
upgrades for PostGIS-enabled databases (see [1]). Does anyone object
to this?
Do you consider to adopt this upstream?
Thanks in advance, and have a nice weekend!
Martin
[1] http://bugs.debian.org/351571
--
Martin Pitthttp://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org
In a world without walls and fences, who needs Windows and Gates?
--- postgresql-8.1.3/doc/src/sgml/ref/pg_restore.sgml
+++ postgresql-8.1.3/doc/src/sgml/ref/pg_restore.sgml
@@ -395,6 +395,19 @@
/listitem
/varlistentry
+ varlistentry
+ termoption-X ignore-existing-tables//term
+ listitem
+ para
+ By default, table data objects are restored even if the
+ associated table already exists. With this option, such table
+ data is silently ignored. This is useful for dumping and
+ restoring databases with tables which contain auxiliary data
+ for PostgreSQL extensions (e. g. PostGIS).
+ /para
+ /listitem
+ /varlistentry
+
/variablelist
/para
--- postgresql-8.1.3/src/bin/pg_dump/pg_backup_archiver.c
+++ postgresql-8.1.3/src/bin/pg_dump/pg_backup_archiver.c
@@ -268,6 +268,21 @@
_printTocEntry(AH, te, ropt, false, false);
defnDumped = true;
+ /* If we could not create a table, ignore the
respective TABLE DATA if
+* -X ignore-existing-tables is given */
+ if (ropt-ignoreExistingTables AH-lastErrorTE == te
strcmp (te-desc, TABLE) == 0) {
+ TocEntry *tes;
+
+ ahlog (AH, 1, table %s could not be created,
will not restore its data\n, te-tag);
+
+ for (tes = te-next; tes != AH-toc; tes =
tes-next) {
+ if (strcmp (tes-desc, TABLE DATA) ==
0 strcmp (tes-tag, te-tag) == 0) {
+strcpy (tes-desc, IGNOREDATA);
+break;
+ }
+ }
+ }
+
/* If we created a DB, connect to it... */
if (strcmp(te-desc, DATABASE) == 0)
{
@@ -1876,6 +1891,10 @@
if (strcmp(te-desc, ENCODING) == 0)
return 0;
+ /* IGNOREDATA is a TABLE DATA which should not be restored */
+ if (strcmp (te-desc, IGNOREDATA) == 0)
+ return 0;
+
/* If it's an ACL, maybe ignore it */
if ((!include_acls || ropt-aclsSkip) strcmp(te-desc, ACL) == 0)
return 0;
--- postgresql-8.1.3/src/bin/pg_dump/pg_backup.h
+++ postgresql-8.1.3/src/bin/pg_dump/pg_backup.h
@@ -106,6 +106,7 @@
char *pghost;
char *username;
int ignoreVersion;
+ int ignoreExistingTables;
int requirePassword;
int exit_on_error;
--- postgresql-8.1.3/src/bin/pg_dump/pg_restore.c
+++ postgresql-8.1.3/src/bin/pg_dump/pg_restore.c
@@ -254,6 +254,8 @@
use_setsessauth = 1;
else if (strcmp(optarg, disable-triggers) ==
0)
disable_triggers = 1;
+ else if (strcmp(optarg,
ignore-existing-tables) == 0)
+ opts-ignoreExistingTables = 1;
else
{
fprintf(stderr,
@@ -394,6 +396,8 @@
printf(_( -X use-set-session-authorization,
--use-set-session-authorization\n
use SESSION AUTHORIZATION
commands instead of\n
OWNER TO commands\n));
+ printf(_( -X ignore-existing-tables\n
+ skip restoration of data for
already existing tables\n));
printf(_(\nConnection options:\n));
printf(_( -h, --host=HOSTNAME database server host or socket
directory\n));
signature.asc
Description: Digital signature
Re: [HACKERS] What's with this lib suffix?
Thomas Hallgren wrote: # Default shlib naming convention used by the majority of platforms shlib = lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION) shlib_major = lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION) shlib_bare = lib$(NAME)$(DLSUFFIX) and sure enough, that's what gets used too. So what goes? You are confusing the naming convention for shared libraries that are intended to be linked into programs (or other libraries) at build time, which normally have to be named libsomething.so because that is what the compiler/linker flag -lsomething resolves to, with the naming convention for shared libraries that are intended to be loaded at run-time (sometimes called plug-ins), which require no particular naming. Examples: $ ls -1 /usr/lib/postgresql/8.1/lib/ ascii_and_mic.so cyrillic_and_mic.so euc_cn_and_mic.so euc_jp_and_sjis.so euc_kr_and_mic.so euc_tw_and_big5.so latin2_and_win1250.so latin_and_mic.so pljava.so plpgsql.so ... $ ls -1 /usr/lib/samba/vfs/ audit.so cap.so default_quota.so expand_msdfs.so extd_audit.so fake_perms.so full_audit.so netatalk.so readonly.so recycle.so shadow_copy.so $ ls -1 /lib/security/ pam_access.so pam_debug.so pam_deny.so pam_env.so pam_filter.so pam_ftp.so ... $ ls -1 /usr/lib/apache2/modules/ httpd.exp mod_actions.so mod_asis.so mod_auth_anon.so mod_auth_dbm.so mod_auth_digest.so mod_auth_ldap.so mod_cache.so ... $ ls -1 /usr/lib/valgrind/x86-linux/ ... vgpreload_core.so vgpreload_helgrind.so vgpreload_massif.so vgpreload_memcheck.so -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] What's with this lib suffix?
Peter Eisentraut wrote: Thomas Hallgren wrote: # Default shlib naming convention used by the majority of platforms shlib = lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION) shlib_major = lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION) shlib_bare= lib$(NAME)$(DLSUFFIX) and sure enough, that's what gets used too. So what goes? You are confusing the naming convention for shared libraries that are intended to be linked into programs (or other libraries) at build time, which normally have to be named libsomething.so because that is what the compiler/linker flag -lsomething resolves to, with the naming convention for shared libraries that are intended to be loaded at run-time (sometimes called plug-ins), which require no particular naming. In that case, I'd appreciate some advice on how to use the pgxs package to compile a 'plug-in'. Looks to me it's only designed to compile 'shared libraries'. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Resurrecting some old patches
On Fri, Feb 24, 2006 at 10:15:39PM -0500, Bruce Momjian wrote: Aren't they in the 8.2 hold queue that I have not processed yet: http://momjian.postgresql.org/cgi-bin/pgpatches_hold Hmm yeah. Didn't know about that page. The first is ok but the second no longer applies cleanly to HEAD so I posted an update. There's no particular point reviewing the old patch since psql changed quite a bit between now and then. If you somehow mark the held queue so people look at that email and the new patch rather than the old one, that'd be great. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] What's with this lib suffix?
Thomas Hallgren wrote: In that case, I'd appreciate some advice on how to use the pgxs package to compile a 'plug-in'. Looks to me it's only designed to compile 'shared libraries'. You can just use the Makefile.shlib rules normally and then rename the file during installation. PL/pgSQL does things this way: # In order to use Makefile.shlib, we allow it to build a static # library libplpgsql.a, which we just ignore, as well as a shared # library that it will insist on naming $(shlib). We don't want to # call it that when installed, however, so we ignore the install-shlib # rule and do this instead: install: installdirs all ifeq ($(enable_shared), yes) $(INSTALL_SHLIB) $(shlib) $(DESTDIR)$(pkglibdir)/plpgsql$(DLSUFFIX) else @echo *; \ echo * PL/pgSQL was not installed due to lack of shared library support.; \ echo * endif -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] What's with this lib suffix?
Thomas Hallgren wrote: Peter Eisentraut wrote: Thomas Hallgren wrote: # Default shlib naming convention used by the majority of platforms shlib= lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION) shlib_major= lib$(NAME)$(DLSUFFIX).$(SO_MAJOR_VERSION) shlib_bare= lib$(NAME)$(DLSUFFIX) and sure enough, that's what gets used too. So what goes? You are confusing the naming convention for shared libraries that are intended to be linked into programs (or other libraries) at build time, which normally have to be named libsomething.so because that is what the compiler/linker flag -lsomething resolves to, with the naming convention for shared libraries that are intended to be loaded at run-time (sometimes called plug-ins), which require no particular naming. In that case, I'd appreciate some advice on how to use the pgxs package to compile a 'plug-in'. Looks to me it's only designed to compile 'shared libraries'. Enumkit's makefile uses pgxs happily to make foo.so without the lib prefix. The relevant portion reads like this: MODULES = $(TYPENAME) DATA_built = $(TYPENAME)-install.sql ENUMS = junk SRCS += $(TYPENAME).c OBJS = $(SRCS:.c=.o) PGXS := $(shell pg_config --pgxs) include $(PGXS) With this, make TYPENAME=foo ENUMS='foo,bar' produces foo.so. HTH cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Resurrecting some old patches
OK. --- Martijn van Oosterhout wrote: -- Start of PGP signed section. On Fri, Feb 24, 2006 at 10:15:39PM -0500, Bruce Momjian wrote: Aren't they in the 8.2 hold queue that I have not processed yet: http://momjian.postgresql.org/cgi-bin/pgpatches_hold Hmm yeah. Didn't know about that page. The first is ok but the second no longer applies cleanly to HEAD so I posted an update. There's no particular point reviewing the old patch since psql changed quite a bit between now and then. If you somehow mark the held queue so people look at that email and the new patch rather than the old one, that'd be great. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. -- End of PGP section, PGP failed! -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Adding an ignore list to pg_restore, prototype p.tch #1
Martin Pitt wrote: Hi again, Martin Pitt [2006-02-19 14:39 +0100]: Since this changes the behaviour of pg_restore, this should probably become an option, e. g. -D / --ignore-existing-table-data. I'll do this if you agree to the principle of the current patch. I improved the patch now to only ignore TABLE DATA for existing tables if '-X ignore-existing-tables' is specified. I also updated the documentation. Is this really an appropiate description for the behavior? What happens if the table is not created for some other reason? Consider for example a table using a datatype that couldn't be created. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Adding an ignore list to pg_restore, prototype patch #1
Martin Pitt [EMAIL PROTECTED] writes: Martin Pitt [2006-02-19 14:39 +0100]: Since this changes the behaviour of pg_restore, this should probably become an option, e. g. -D / --ignore-existing-table-data. I'll do this if you agree to the principle of the current patch. I improved the patch now to only ignore TABLE DATA for existing tables if '-X ignore-existing-tables' is specified. I also updated the documentation. This patch is unbelievably ugly and probably vulnerable to coredumps. Please use a cleaner way of disabling the subsequent load than tromping all over the TOC datastructure, ie, not this: +strcpy (tes-desc, IGNOREDATA); BTW, I'm pretty sure it fails for tables with same names in different schemas, too. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] textToQualifiedNameList second parameter
On 2/24/06, Rafael Martinez Guerrero [EMAIL PROTECTED] wrote: Hello In version 7.4.x, the function textToQualifiedNameList was defined with two parameters. Some months ago, the second parameter was removed [1] so I had to modify my program to work after this change. Now with 8.0.7, the second parameter is back again and I get this error when I try to compile: so, your progam rely on internal functions from someone else's software? bad idea Is the second parameter back again? [1]: http://archives.postgresql.org/pgsql-patches/2005-05/msg00307.php -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
On Fri, Feb 24, 2006 at 19:40:33 -0500, Clark C. Evans [EMAIL PROTECTED] wrote: While the textual description of this view Identify domain constraints in this catalog accessable to a given user. has not changed between SQL-1992 and SQL-2003, the actual critera specified is quite different: In SQL 1992, it seems to show only domains that are in schemas owned by the current user. In SQL 2003, it seems to be more intelligent: showing all constraints that are visible to the current user. I'm curious which rule PostgreSQL's information_schema is using? I think the SQL-2003 rules more properly follow the textual description and are more useful; the SQL-1999 rules are effectively useless in all but trivial cases. This has been discussed previously in a couple of threads. I believe the desire is to make it work as specified in SQL-2003, but I do not remember whether or not anyone volunteered to do the work to make it happen. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] constraints and sql92 information_schema compliance
Bruno, This has been discussed previously in a couple of threads. I believe the desire is to make it work as specified in SQL-2003, but I do not remember whether or not anyone volunteered to do the work to make it happen. I believe that the newsysviews follow the SQL03 permissions structure. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: | This has been discussed previously in a couple of threads. I believe the | desire is to make it work as specified in SQL-2003, but I do not remember | whether or not anyone volunteered to do the work to make it happen. | | I believe that the newsysviews follow the SQL03 permissions structure. Fantastic! The SQL92 permission structure was braindead. After some time working with the information schema, I have three suggestions: * for foreign-key and check constraints, the default names are $1, $2, etc.; it would be great if they were upgraded to use the default names given by primary and unique key constraints: table_uk_1stcol, table_pk - the problem with $1 is that they arn't unique across tables, and hence won't work /w information_schema nicely unless you manually name the constraints * when creating a foreign key constraint on two columns, say from A (x, y) to B (x, y), if the unique index on B is (x,y) you can make a foreign key from A-B using (y,x) - this might seem correct, but it makes it impossible to deterine from the information schema which columns to join on -- and you might infer the wrong relation ie, FROM A JOIN B ON (b.x = a.y and b.y = a.x) * it would be great to add a warning if a constraint is not unique within its schema (obviously, making it an error is a bad idea) I think with these changes no updates to the views are necessary; and compliance with the information_schema is more or less automatic unless someone is ignoring the warnings. Kind Regards, Clark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Pl/Python -- current maintainer?
On Sat, Feb 25, 2006 at 10:09:52AM +0100, Tino Wildenhain wrote: And with even more love the restricted python from zope could be ported so there could be a pl/python again :-) Ok, just haluzinating ;) Not necessarily. ;) From what I have seen of zope's restricted python, it does, or can, force its restrictions by checking bytecode. I imagine a simple PL sitting on top of the untrusted varient that merely implements a custom validator that checks the bytecode produced by the untrusted PL's validator. The language handler would remain the same: Create untrusted plpy... CREATE FUNCTION zope_restrict(oid) RETURNS VOID LANGUAGE python AS $$ getcode = Postgres.CachedQuery(SELECT probin FROM pg_proc WHERE oid = $1) getlangval = Postgres.CachedQuery( SELECT lanvalidator FROM pg_language WHERE oid = (SELECT prolang FROM pg_proc WHERE oid = $1) ) langvaloid = getlangval(self.function.procid) langval = Postgres.Function(langvaloid) # produce the function's bytecode for checking langval(args[0]) # get the function's newly created bytecode code = getcode(args[0]).next()[0] # # XXX: do the zope restriction stuff, raise exception if necessary # Postgres.NOTICE(repr(code)) $$; CREATE TRUSTED LANGUAGE plpyr HANDLER python.handler VALIDATOR zope_restrict; [This almost works in plpy head, but I think I just found a bug ;] -- Regards, James William Pye ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, 25 Feb 2006, Clark C. Evans wrote: On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: | This has been discussed previously in a couple of threads. I believe the | desire is to make it work as specified in SQL-2003, but I do not remember | whether or not anyone volunteered to do the work to make it happen. | | I believe that the newsysviews follow the SQL03 permissions structure. Fantastic! The SQL92 permission structure was braindead. After some time working with the information schema, I have three suggestions: * for foreign-key and check constraints, the default names are $1, $2, etc.; it would be great if they were upgraded to use the default names given by primary and unique key constraints: table_uk_1stcol, table_pk Err... what version are you using? I get constraint names like tt_a_fkey from devel, and I thought at least 8.1 does the same. * when creating a foreign key constraint on two columns, say from A (x, y) to B (x, y), if the unique index on B is (x,y) you can make a foreign key from A-B using (y,x) I don't understand which particular case you're complaining about, but as far as I can see, we have to allow that case by the rest of the spec. If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) and B(y), all of A(x,y)-B(x,y), A(y,x)-B(x,y), A(x,y)-B(y,x) and A(y,x)-B(y,x) seem to be allowed by the definition in the constraint section (as only the sets must be equal, with no mention of ordering). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, 25 Feb 2006, Stephan Szabo wrote: On Sat, 25 Feb 2006, Clark C. Evans wrote: On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: | This has been discussed previously in a couple of threads. I believe the | desire is to make it work as specified in SQL-2003, but I do not remember | whether or not anyone volunteered to do the work to make it happen. | | I believe that the newsysviews follow the SQL03 permissions structure. Fantastic! The SQL92 permission structure was braindead. After some time working with the information schema, I have three suggestions: * for foreign-key and check constraints, the default names are $1, $2, etc.; it would be great if they were upgraded to use the default names given by primary and unique key constraints: table_uk_1stcol, table_pk Err... what version are you using? I get constraint names like tt_a_fkey from devel, and I thought at least 8.1 does the same. * when creating a foreign key constraint on two columns, say from A (x, y) to B (x, y), if the unique index on B is (x,y) you can make a foreign key from A-B using (y,x) I don't understand which particular case you're complaining about, but as far as I can see, we have to allow that case by the rest of the spec. If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) and B(y), all of A(x,y)-B(x,y), A(y,x)-B(x,y), A(x,y)-B(y,x) and A(y,x)-B(y,x) seem to be allowed by the definition in the constraint section (as only the sets must be equal, with no mention of ordering). The sets in this case being the referenced columns and the unique columns in the unique constraint. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Pl/Python -- current maintainer?
James William Pye wrote: On Sat, Feb 25, 2006 at 10:09:52AM +0100, Tino Wildenhain wrote: And with even more love the restricted python from zope could be ported so there could be a pl/python again :-) Ok, just haluzinating ;) Not necessarily. ;) From what I have seen of zope's restricted python, it does, or can, force its restrictions by checking bytecode. I imagine a simple PL sitting on top of the untrusted varient that merely implements a custom validator that checks the bytecode produced by the untrusted PL's validator. I'm not sure it's an issue now that we have pg_pltemplate, but in older versions it's possible to create a language without setting a validator. This would make the validator an unsuitable place for checking the restrictions. But the call handler can access the bytecode just the same, so it's just a matter of moving the checks there, just before the execution. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote: | * for foreign-key and check constraints, the default names | are $1, $2, etc.; it would be great if they were upgraded | to use the default names given by primary and unique key | constraints: table_uk_1stcol, table_pk | | Err... what version are you using? I get constraint names like tt_a_fkey | from devel, and I thought at least 8.1 does the same. 7.4.8, so it's a bit old -- glad to hear this made it! | * when creating a foreign key constraint on two columns, say | from A (x, y) to B (x, y), if the unique index on B is (x,y) | you can make a foreign key from A-B using (y,x) | | I don't understand which particular case you're complaining about, but as | far as I can see, we have to allow that case by the rest of the spec. To be clear, I'm talking about... CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); CREATE TABLE a (b text, c text); ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); For this case, the information schema details: 1. the foreign key constraint as a reference to the primary key constraint and lists the tuple (b,c) 2. the primary key constraint lists the keys (y,z) In particular, the column ordering (z, y) in the reference clause is *lost*. Hence, if you were to blindly reconstruct a join critiera from the information schema, you'd wrongly assume that useful join critiera is: ON (a.b == x.y AND a.c == x.z) when the correct join critiera should be: ON (a.b == x.z AND a.c == x.y) I assert the problem here is that the FOREIGN KEY constraint construction should have *failed* since the *tuple* (z,y) does not infact match any unique key in table x. | If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) | and B(y), all of A(x,y)-B(x,y), A(y,x)-B(x,y), A(x,y)-B(y,x) and | A(y,x)-B(y,x) seem to be allowed by the definition in the constraint | section (as only the sets must be equal, with no mention of ordering). Ordering of tuples (fields within a row object) are significant in SQL; and hence the two above are not comparable. | The sets in this case being the referenced columns and the unique | columns in the unique constraint. Not sure I get this; sorry about being so obscure in my first email. I hope this one clarifies the problem. Cheers, Clark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, 2006-02-25 at 16:35 -0500, Clark C. Evans wrote:
On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote:
| * for foreign-key and check constraints, the default names
| are $1, $2, etc.; it would be great if they were upgraded
| to use the default names given by primary and unique key
| constraints: table_uk_1stcol, table_pk
|
| Err... what version are you using? I get constraint names like tt_a_fkey
| from devel, and I thought at least 8.1 does the same.
7.4.8, so it's a bit old -- glad to hear this made it!
| * when creating a foreign key constraint on two columns, say
| from A (x, y) to B (x, y), if the unique index on B is (x,y)
| you can make a foreign key from A-B using (y,x)
|
| I don't understand which particular case you're complaining about, but as
| far as I can see, we have to allow that case by the rest of the spec.
To be clear, I'm talking about...
CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
CREATE TABLE a (b text, c text);
ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
For this case, the information schema details:
1. the foreign key constraint as a reference to the
primary key constraint and lists the tuple (b,c)
2. the primary key constraint lists the keys (y,z)
I'm afraid I don't follow what the issue is.
Can out point it out in the below psql output?
k=# CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index x_pkey
for table x
CREATE TABLE
k=# CREATE TABLE a (b text, c text);
CREATE TABLE
k=# ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
ALTER TABLE
k=# \d x
Table public.x
Column | Type | Modifiers
+--+---
y | text | not null
z | text | not null
Indexes:
x_pkey PRIMARY KEY, btree (y, z)
k=# \d a
Table public.a
Column | Type | Modifiers
+--+---
b | text |
c | text |
Foreign-key constraints:
a_b_fkey FOREIGN KEY (b, c) REFERENCES x(z, y)
k=# insert into x values ('foo', 'bar');
INSERT 0 1
k=# insert into a values ('foo', 'bar');
ERROR: insert or update on table a violates foreign key constraint
a_b_fkey
DETAIL: Key (b,c)=(foo,bar) is not present in table x.
k=# insert into a values ('bar', 'foo');
INSERT 0 1
--
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, 25 Feb 2006, Clark C. Evans wrote: On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote: | * for foreign-key and check constraints, the default names | are $1, $2, etc.; it would be great if they were upgraded | to use the default names given by primary and unique key | constraints: table_uk_1stcol, table_pk | | Err... what version are you using? I get constraint names like tt_a_fkey | from devel, and I thought at least 8.1 does the same. 7.4.8, so it's a bit old -- glad to hear this made it! | * when creating a foreign key constraint on two columns, say | from A (x, y) to B (x, y), if the unique index on B is (x,y) | you can make a foreign key from A-B using (y,x) | | I don't understand which particular case you're complaining about, but as | far as I can see, we have to allow that case by the rest of the spec. To be clear, I'm talking about... CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); CREATE TABLE a (b text, c text); ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); For this case, the information schema details: 1. the foreign key constraint as a reference to the primary key constraint and lists the tuple (b,c) 2. the primary key constraint lists the keys (y,z) In particular, the column ordering (z, y) in the reference clause is *lost*. Hence, if you were to blindly reconstruct a join critiera from the information schema, you'd wrongly assume that useful join critiera is: ON (a.b == x.y AND a.c == x.z) when the correct join critiera should be: ON (a.b == x.z AND a.c == x.y) I assert the problem here is that the FOREIGN KEY constraint construction should have *failed* since the *tuple* (z,y) does not infact match any unique key in table x. I disagree because the spec doesn't say that the columns must be equal or the list of columns must be the equal but that the set of columns must be equal. And in the definitions section, set is defined as an unordered collection of distinct objects. | If A(x) is comparable to B(x) and B(y) and A(y) is comparable to B(x) | and B(y), all of A(x,y)-B(x,y), A(y,x)-B(x,y), A(x,y)-B(y,x) and | A(y,x)-B(y,x) seem to be allowed by the definition in the constraint | section (as only the sets must be equal, with no mention of ordering). Ordering of tuples (fields within a row object) are significant in SQL; and hence the two above are not comparable. You misunderstand what comparable means in the above. Comparable is the constraint on the column types (for example numeric types are comparable to other numeric types). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, 25 Feb 2006, Stephan Szabo wrote: On Sat, 25 Feb 2006, Clark C. Evans wrote: On Sat, Feb 25, 2006 at 12:51:51PM -0800, Stephan Szabo wrote: | * for foreign-key and check constraints, the default names | are $1, $2, etc.; it would be great if they were upgraded | to use the default names given by primary and unique key | constraints: table_uk_1stcol, table_pk | | Err... what version are you using? I get constraint names like tt_a_fkey | from devel, and I thought at least 8.1 does the same. 7.4.8, so it's a bit old -- glad to hear this made it! | * when creating a foreign key constraint on two columns, say | from A (x, y) to B (x, y), if the unique index on B is (x,y) | you can make a foreign key from A-B using (y,x) | | I don't understand which particular case you're complaining about, but as | far as I can see, we have to allow that case by the rest of the spec. To be clear, I'm talking about... CREATE TABLE x (y text, z text, PRIMARY KEY(y,z)); CREATE TABLE a (b text, c text); ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y); For this case, the information schema details: 1. the foreign key constraint as a reference to the primary key constraint and lists the tuple (b,c) 2. the primary key constraint lists the keys (y,z) In particular, the column ordering (z, y) in the reference clause is *lost*. Hence, if you were to blindly reconstruct a join critiera from the information schema, you'd wrongly assume that useful join critiera is: ON (a.b == x.y AND a.c == x.z) when the correct join critiera should be: ON (a.b == x.z AND a.c == x.y) I assert the problem here is that the FOREIGN KEY constraint construction should have *failed* since the *tuple* (z,y) does not infact match any unique key in table x. I disagree because the spec doesn't say that the columns must be equal or the list of columns must be the equal but that the set of columns must be equal. And in the definitions section, set is defined as an unordered collection of distinct objects. Okay, I'll take that back for SQL2003. They must have realized that this was broken with information schema and changed it. That's an interesting incompatibility with old versions, but it's easy to implement. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, Feb 25, 2006 at 11:51:55AM -0800, Josh Berkus wrote: Bruno, This has been discussed previously in a couple of threads. I believe the desire is to make it work as specified in SQL-2003, but I do not remember whether or not anyone volunteered to do the work to make it happen. I believe that the newsysviews follow the SQL03 permissions structure. Does SQL03 specify a different name for info schema? Should we morph newsysviews to that? Of course this still leaves the issue of how to deal with PostgreSQL-specific stuff that isn't in infoschema, since we probably don't want to be adding extra stuff there. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraints and sql92 information_schema compliance
Guys, So, based on this discussion, I'd like to consider taking a second stab at newsysviews: 1) Implement SQL03 changes into the information_schema, using some of the code from newsysviews; 2) Modify the newsysviews to be extensions of the information_schema views: e.g. information_schema.tables would have the SQL03 information, and information_schema.tables_pg would have pg-specific stuff like table size and last analyzed date. I think that this should answer the objections expressed to the newsysviews and provide a path towards integrating them into the main code. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Pl/Python -- current maintainer?
On Sat, Feb 25, 2006 at 06:36:19PM -0300, Alvaro Herrera wrote: I'm not sure it's an issue now that we have pg_pltemplate, but in older versions it's possible to create a language without setting a validator. This would make the validator an unsuitable place for checking the restrictions. Hrm. I think this would only be an issue in PL/Py is if the user had the ability to alter probin. The handler will never directly execute code in prosrc; it relies on a validator to fill in probin. Whether a regular user could take advantage of this or not, I'm not sure as I have yet to test it or to give it much thought. -- Regards, James William Pye ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Pl/Python -- current maintainer?
On Sat, Feb 25, 2006 at 01:21:34PM -0700, I wrote: From what I have seen of zope's restricted python, it does, or can, force its restrictions by checking bytecode. I imagine a simple PL sitting on top of the untrusted varient that merely implements a custom validator that checks the bytecode produced by the untrusted PL's validator. The language handler would remain the same: [ugh, Correcting my assumptions...] Zope's RestrictedPython is a custom bytecode generator that compiles Python code specially, as opposed to a bytecode processor that validates against some rule set as I had thought for some (wishful? ;) reason. The bytecode then needs to be executed in an special environment that then imposes some specified restrictions at runtime(I'm not really clear on all the details here as I am having a very difficult time finding documentation). This doesn't mean that it couldn't be used. However, it does mean that some munging of the handler would be required(Something that I desired to avoid). -- Regards, James William Pye ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] TOAST compression
toast_compress_datum() considers compression to be successful if the compressed version of the datum is smaller than the uncompressed version. I think this is overly generous: if compression reduces the size of the datum by, say, 0.01%, it is likely a net loss to use the compressed version of the datum since we'll need to pay for LZ decompression every time that we de-TOAST it. This situation can occur frequently when storing mostly-uncompressible data (compressed images, encrypted data, etc.) -- some parts of the data will compress well (e.g. metadata), but the vast majority will not. It's true that LZ decompression is fast, so we should probably use the compressed version of the datum unless the reduction in size is very small. I'm not sure precisely what that threshold should be, however. Comments? -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TOAST compression
Neil Conway wrote: toast_compress_datum() considers compression to be successful if the compressed version of the datum is smaller than the uncompressed version. I think this is overly generous: if compression reduces the size of the datum by, say, 0.01%, it is likely a net loss to use the compressed version of the datum since we'll need to pay for LZ decompression every time that we de-TOAST it. This situation can occur frequently when storing mostly-uncompressible data (compressed images, encrypted data, etc.) -- some parts of the data will compress well (e.g. metadata), but the vast majority will not. It's true that LZ decompression is fast, so we should probably use the compressed version of the datum unless the reduction in size is very small. I'm not sure precisely what that threshold should be, however. Comments? 20%? 25% -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] User privileges-verification required
In my opinion we should cater for such a situation, and two possible solutions come to my mind for this: I've done exactly this before, and had to use single user mode to recover. Annoying. 1. Place a restriction that there should be more than one superuser before you can issue a NOCREATEUSER command. I agree :) Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sat, Feb 25, 2006 at 04:50:41PM -0500, Rod Taylor wrote:
| Can out point it out in the below psql output?
|
| k=# CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index x_pkey
| for table x
| CREATE TABLE
| k=# CREATE TABLE a (b text, c text);
| CREATE TABLE
| k=# ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
| ALTER TABLE
- this alter _should_ fail, since there isn't a canidate key
on the table x matching (z, y)
| x_pkey PRIMARY KEY, btree (y, z)
| a_b_fkey FOREIGN KEY (b, c) REFERENCES x(z, y)
The problem is that PostgreSQL is maintaining information that is/should
not be available to an SQL processor: the ordering of the _referenced_
columns. That a_b_fkey happens to reference (z, y) is not available in
the SQL INFORMATION_SCHEMA, and thus should not be used to interpret
standard SQL statements affected by the foreign key constraint.
| k=# insert into x values ('foo', 'bar');
| INSERT 0 1
| k=# insert into a values ('foo', 'bar');
| ERROR: insert or update on table a violates foreign key constraint
| a_b_fkey
| DETAIL: Key (b,c)=(foo,bar) is not present in table x.
Assuming that you _could_ create the FOREIGN KEY reference above, if you
are strictly using the meta-data available in the information_schema,
this insert should succeed
| k=# insert into a values ('bar', 'foo');
| INSERT 0 1
and this insert should fail. The opposite happens beacuse PostgreSQL
is storing _more_ information than what is specified and has over
interpreted the meaning of the reference clause.
On Sat, Feb 25, 2006 at 02:01:00PM -0800, Stephan Szabo wrote:
| On Sat, 25 Feb 2006, Clark C. Evans wrote:
|
| CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
| CREATE TABLE a (b text, c text);
| ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
|
| I assert the problem here is that the FOREIGN KEY constraint
| construction should have *failed* since the *tuple* (z,y)
| does not infact match any unique key in table x.
|
| I disagree because the spec doesn't say that the columns must be equal
| or the list of columns must be the equal but that the set of columns must
| be equal. And in the definitions section, set is defined as an unordered
| collection of distinct objects.
Let's use the example Rod gave us above. If the comparison for foreign
key constraints should be done as an unorderd set, then why does the
following fail?
| k=# insert into x values ('foo', 'bar');
| INSERT 0 1
| k=# insert into a values ('foo', 'bar');
| ERROR: insert or update on table a violates foreign key constraint
| a_b_fkey
| DETAIL: Key (b,c)=(foo,bar) is not present in table x.
While the SQL1992 specification may be horribly incorrect; the current
behavior is not compliant with it... so this isn't a great defense. If
PostgreSQL didn't store the order of the columns referenced, it couldn't
provide the error above (which makes sense, given the extension).
| Okay, I'll take that back for SQL2003. They must have realized that this
| was broken with information schema and changed it.
Ok.
| That's an interesting incompatibility with old versions,
| but it's easy to implement.
This would be great; it would reduce the chances of an external program
generating SQL from making incorrect joins and causing a very strange
behavior and incorrect results.
Cheers,
Clark
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] constraints and sql92 information_schema compliance
Stephen, So, a quick re-cap of the questions/concerns I had: * Making the default constraint names include the table - This was implemented in 8.x, thank you! * Forbidding the creation of a foreign key constraint where the column list for the referenced table doesn't *exactly* match a canidate key on that table. - I think you've agreed to something like this, or am I mis-understanding? * Issue a warning when creating a constraint who's name is not unique within its (the constraint's) schema. - This request seems to have gotten lost in the vigorous discussion ;) Kind Regards, Clark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sun, 26 Feb 2006, Clark C. Evans wrote: Stephen, So, a quick re-cap of the questions/concerns I had: * Making the default constraint names include the table - This was implemented in 8.x, thank you! * Forbidding the creation of a foreign key constraint where the column list for the referenced table doesn't *exactly* match a canidate key on that table. - I think you've agreed to something like this, or am I mis-understanding? Well, SQL03 requires it to match exactly (I haven't checked 99). SQL92 explicitly requires us to support not matching exactly and we can't really remove it for some amount of time due to compatibility. About the best we're likely to be able to do is change pg_dump to dump it in the 03 order and possibly give an optional way to turn on an exact check (default off) for the next version, probably changing the default 1 or 2 versions after that. Personally, I disagree with the 03 requirement and think that it's more an example of them misdesigning the information schema, but we should probably move in that direction for compatibility with more recent versions of spec. * Issue a warning when creating a constraint who's name is not unique within its (the constraint's) schema. - This request seems to have gotten lost in the vigorous discussion ;) I don't have a problem with it (once, I argued for following the spec constraint on this way back when), however I think this was proposed and rejected before as excess noise. You might want to look back through the archives. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] constraints and sql92 information_schema compliance
On Sun, 26 Feb 2006, Clark C. Evans wrote:
On Sat, Feb 25, 2006 at 04:50:41PM -0500, Rod Taylor wrote:
On Sat, Feb 25, 2006 at 02:01:00PM -0800, Stephan Szabo wrote:
| On Sat, 25 Feb 2006, Clark C. Evans wrote:
|
| CREATE TABLE x (y text, z text, PRIMARY KEY(y,z));
| CREATE TABLE a (b text, c text);
| ALTER TABLE a ADD FOREIGN KEY (b, c) REFERENCES x(z, y);
|
| I assert the problem here is that the FOREIGN KEY constraint
| construction should have *failed* since the *tuple* (z,y)
| does not infact match any unique key in table x.
|
| I disagree because the spec doesn't say that the columns must be equal
| or the list of columns must be the equal but that the set of columns must
| be equal. And in the definitions section, set is defined as an unordered
| collection of distinct objects.
Let's use the example Rod gave us above. If the comparison for foreign
key constraints should be done as an unorderd set, then why does the
following fail?
| k=# insert into x values ('foo', 'bar');
| INSERT 0 1
| k=# insert into a values ('foo', 'bar');
| ERROR: insert or update on table a violates foreign key constraint
| a_b_fkey
| DETAIL: Key (b,c)=(foo,bar) is not present in table x.
While the SQL1992 specification may be horribly incorrect; the current
behavior is not compliant with it... so this isn't a great defense. If
PostgreSQL didn't store the order of the columns referenced, it couldn't
provide the error above (which makes sense, given the extension).
No, because you're misunderstanding what the SQL92 spec says. The spec
says that the comparison between the (z,y) in the references and the key
definition (y,z) is unordered, not that the comparisons between (b,c) and
(z,y) are unordered.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
