Re: [HACKERS] PL contribution guidelines?
On Sunday 28 September 2003 11:53, mlg7 wrote: > >On Saturday 27 September 2003 19:46, Peter Eisentraut wrote: > >> mlg7 writes: > >> > Is there a centralized list of pgsql PL's ? > >> > >> I'm not aware of one. > > > >http://techdocs.postgresql.org/guides/PLLanguages > > > >Josh posted it on advocacy few days back. > > > > Shridhar > > That does not work: > : Proxy Error > : The proxy server received an invalid response from an upstream server. > : > : The proxy server could not handle the request GET /guides/PLLanguages. > : > : Reason: Could not connect to remote machine: Operation timed out Try google cache http://www.google.co.in/search?q=cache:iDjd8nA-l2IJ:techdocs.postgresql.org/guides/PLLanguages+list+of+postgresql+procedural+languages&hl=mr&ie=UTF-8 The google search term I used was "list of postgresql procedural languages". Look for first techdocs link. Yesterday when I got same error, I thought there was problem with international link again...:-) Hard to tell where exactly problem is.. Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] _GNU_SOURCE
Bruce Momjian <[EMAIL PROTECTED]> writes: >> _GNU_SOURCE All of the above, plus GNU extensions. >> >> Which means it enables all this: >> >> __STRICT_ANSI__, _ISOC99_SOURCE, _POSIX_SOURCE, _POSIX_C_SOURCE, >> _XOPEN_SOURCE, _XOPEN_SOURCE_EXTENDED, _LARGEFILE_SOURCE, >> _LARGEFILE64_SOURCE, _FILE_OFFSET_BITS=N, _BSD_SOURCE, _SVID_SOURCE Hm. So is crypt_r() a GNU extension? I would've thought it was specified by some standard or other. Perhaps the real issue here is that /usr/include/crypt.h is using the wrong control symbol. At least in RHL 8.0, it definitely uses __USE_GNU to hide crypt_r and the associated struct type. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [HACKERS] plpgsql doesn't coerce boolean expressions to
Jan Wieck <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> if count(*) = 0 from Room where roomno = new.roomno then >> raise exception ''Room % does not exist'', new.roomno; >> end if; >> >> Is this really intended to be a feature? > I have to admit it was less an intention than more a side effect of the > actual implementation. It was so easy to simply stick "SELECT " in front > of "everything between IF and THEN" and expect the result to be a boolean. Sure, it was easy given a certain implementation technique. Question is, do we want to consider it a supported feature even if it makes the implementation hard? > In the same way you can do > varname := count(*) from Room where roomno = new.roomno; This actually doesn't bother me; I see it as simply a variant syntax for SELECT INTO. (Perhaps it should be documented that way.) If we want to preserve this behavior for IF et al, I don't think there is any practical way to apply SQL-level type coercion as I had wanted. We could instead make the code act like it's assigning to a plpgsql boolean variable --- but it will apply plpgsql's textual conversion methods, not SQL type coercion. regards, tom lane ---(end of broadcast)--- TIP 3: 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] _GNU_SOURCE
-On [20030928 17:52], Tom Lane ([EMAIL PROTECTED]) wrote: >Hm. So is crypt_r() a GNU extension? I would've thought it was >specified by some standard or other. Perhaps the real issue here >is that /usr/include/crypt.h is using the wrong control symbol. >At least in RHL 8.0, it definitely uses __USE_GNU to hide crypt_r >and the associated struct type. crypt() is a 4.3 BSD, SVID 3, Unix 95, Unix 98. crypt_r() though, is a GNU extension: http://lists.debian.org/lsb-discuss/2001/lsb-discuss-200103/msg00026.html and from: http://docs.mandragor.org/files/Programming_languages/C/glibc-2.2.3/libc_32.html#SEC661 "The crypt_r function is a GNU extension." -- Jeroen Ruigrok van der Werven / asmodai / kita no mono PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Though this be madness, yet there is a method in it... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] _GNU_SOURCE
Jeroen Ruigrok/asmodai wrote: > -On [20030928 17:52], Tom Lane ([EMAIL PROTECTED]) wrote: > >Hm. So is crypt_r() a GNU extension? I would've thought it was > >specified by some standard or other. Perhaps the real issue here > >is that /usr/include/crypt.h is using the wrong control symbol. > >At least in RHL 8.0, it definitely uses __USE_GNU to hide crypt_r > >and the associated struct type. > > crypt() is a 4.3 BSD, SVID 3, Unix 95, Unix 98. > > crypt_r() though, is a GNU extension: > http://lists.debian.org/lsb-discuss/2001/lsb-discuss-200103/msg00026.html > and from: > http://docs.mandragor.org/files/Programming_languages/C/glibc-2.2.3/libc_32.html#SEC661 > > "The crypt_r function is a GNU extension." BSD/OS doesn't have crypt_r(), and crypt() manual page says: The crypt() function may not be safely called concurrently from multiple threads, e.g., the interfaces described by pthreads(3). -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Improving REINDEX for system indexes (long)
On Sat, Sep 27, 2003 at 08:18:07PM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > > What about creating a separate filenode anyway and renaming the files > > > afterwards? It would not be an atomic operation anyway, but it would be > > > better than the current setup IMHO. > > > > I think it would be difficult to persuade the buffer manager and storage > > manager to work with this; from their point of view you'd be moving a > > relation underneath them. I doubt it's really worth the trouble; how > > often do you need to reindex a shared catalog? > > The point I missed originally is that he is talking about shared > catalogs, not system catalogs, which work fine in CVS. Well, my idea was to reduce the window of time during which the index would be corrupt, i.e. not completely rebuilt. This is only an issue with shared indexes, because other system indexes do use the changing relfilenode thingie already. However, the main reason for reindexing is a corrupt index, so if for some reason the new index is also corrupt (e.g. the machine crashes midway) there's no point in having a separate index filenode anyway. -- Alvaro Herrera () "The eagle never lost so much time, as when he submitted to learn of the crow." (William Blake) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Sat, 27 Sep 2003, Tom Lane wrote: >> I thought of what seems to be a better design for the check query: use >> a LEFT JOIN and check for NULL in the righthand joined column. > Hmm, my initial testing showed that it really was a little slower > than a more complicated one with NOT EXISTS so I'd abandoned it. How does > it fare for you compared to: > select f1, f2 from fk where not exists (select 1 from pk where pk.f1=fk.f1 > and pk.f2=pk.f2) where fk.f1 is not null and fk.f2 is not null; Were you testing against 7.3 or 7.4? On what kinds of tables? In 7.4 I think that the JOIN would yield as good or better a plan. The best possible plan for the NOT EXISTS query is effectively a nestloop with inner indexscan, which is great if the FK table is small and the PK table is large, but it sucks otherwise. The planner should choose a plan of this form for the LEFT JOIN given that combination of table sizes, and so there shouldn't be any great difference in runtime in that case. But in other combinations, such as large FK and small PK, other plan types will beat the pants off nestloop. > I've actually got code (that no longer cleanly applies, but...) that uses > the single query version with NOT EXISTS (which could be easily changed to > either of the other forms) and was planning to put it together for a patch > when 7.5 devel started because I figured it wasn't precisely a bug and > wouldn't get accepted for 7.4. Well, Bruce has this on his open-items list, so I figure we have a green light to do something for 7.4 if we can work out what to do. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 2-phase commit
Bruce Momjian wrote: > Kevin Brown wrote: > > Actually, all that's really necessary is the ability to call a stored > > procedure when some event occurs. The stored procedure can take it from > > there, and since it can be written in C it can do anything the postgres > > user can do (for good or for ill, of course). > > But the postmaster doesn't connect to any database, and in a serious > failure, might not be able to start one. Ah, true. But I figured that in the context of 2PC and replication that most of the associated failures were likely to occur in an active backend or something equivalent, where a stored procedure was likely to be accessible. But yes, you certainly want to account for failures where the database itself is unavailable. So I guess my original comment isn't strictly true. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
On Sun, 28 Sep 2003, Bruce Momjian wrote: > Stephan Szabo wrote: > > Hmm, my initial testing showed that it really was a little slower > > than a more complicated one with NOT EXISTS so I'd abandoned it. How does > > it fare for you compared to: > > select f1, f2 from fk where not exists (select 1 from pk where pk.f1=fk.f1 > > and pk.f2=pk.f2) where fk.f1 is not null and fk.f2 is not null; > > > > I believe the above is the appropriate not exists form for match > > unspecified. > > > > I've actually got code (that no longer cleanly applies, but...) that uses > > the single query version with NOT EXISTS (which could be easily changed to > > either of the other forms) and was planning to put it together for a patch > > when 7.5 devel started because I figured it wasn't precisely a bug and > > wouldn't get accepted for 7.4. > > I am a little lost on this point myself --- are we talking 7.4 or 7.5 > for this change? I'd thought 7.5, but I now see that it's on the 7.4 open items list. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Well, we haven't even *got* a proposed patch yet, but yeah we should >> tread carefully. > OK. What releases had this slow restore problem? We introduced it in 7.3 --- before that, FKs were simply dumped as "create trigger" commands, and there was no check overhead. So arguably it is a bug; a performance bug maybe, but that's still a bug. No one has yet gone through a dump/reload cycle in which they had to face this penalty. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 2-phase commit
> > Actually, all that's really necessary is the ability to call a stored > > procedure when some event occurs. The stored procedure can take it from > > there, and since it can be written in C it can do anything the postgres > > user can do (for good or for ill, of course). > > But the postmaster doesn't connect to any database, and in a serious > failure, might not be able to start one. In the event of a catastrophic, the 'nothing is running' scenario is one standard monitoring software should pick up on that easily enough. One that PostgreSQL cannot help with anyway (normally this is admin error). Something simple much like pg_locks with transaction state (idle, waiting on local lock, waiting on 3rd party, etc.), time transaction started, time of last status change would be plenty. The monitor software folks (Big Brother, etc. etc.) can write jobs to query those elements and create the appropriate SNMP events when say waiting on 3rd party for > N minutes (log at 1, trouble ticket at 2, SysAdmin page at 5, escalate to VP Pager at 20 minutes or whatever corporate policy is). An alternative is to package an SNMP daemon (much like the stats daemon) into the backend to generate SNMP events -- but I think this is overkill if views are available. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] more i18n/l10n issues
On Sun, Sep 28, 2003 at 03:36:50PM -0400, Alvaro Herrera wrote:
> Now for something completely different:
Oh, there's another thing about the --help-config option. This option
includes an, er, option to display the items that belong to a given
group. So you could say
/tmp/pgsql-es/bin/postgres --help-config -g 'Security'
and the list of parameters that belong to a group that has the word
Security on it would be displayed.
However, the -l option lists the groups with the translated names, and
of course those names do not match on -g specification.
This little patch allows both versions to match, translated and
untranslated.
--
Alvaro Herrera ()
"Crear es tan difícil como ser libre" (Elsa Triolet)
Index: src/backend/utils/misc/help_config.c
===
RCS file: /home/alvherre/cvs/pgsql-server/src/backend/utils/misc/help_config.c,v
retrieving revision 1.7
diff -c -r1.7 help_config.c
*** src/backend/utils/misc/help_config.c27 Sep 2003 09:29:31 - 1.7
--- src/backend/utils/misc/help_config.c28 Sep 2003 20:20:33 -
***
*** 283,289
if (groupString != NULL)
{
! if (strstr(config_group_names[structToTest->generic.group],
groupString) != NULL)
{
if (nameString != NULL)
matches = (matches && true);
--- 283,290
if (groupString != NULL)
{
! if (strstr(config_group_names[structToTest->generic.group],
groupString) != NULL
! ||
strstr(gettext(config_group_names[structToTest->generic.group]), groupString) != NULL)
{
if (nameString != NULL)
matches = (matches && true);
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] _GNU_SOURCE
Bruce Momjian <[EMAIL PROTECTED]> writes: > Jeroen Ruigrok/asmodai wrote: >> "The crypt_r function is a GNU extension." > BSD/OS doesn't have crypt_r(), and crypt() manual page says: > The crypt() function may not be safely called concurrently from multiple > threads, e.g., the interfaces described by pthreads(3). Right. But whether crypt is re-entrant or not isn't really the issue here. The problem is that the standard RHL 8 version of Perl is installed in such a way that fails to compile unless crypt_r's struct typedef is visible. I have not looked, but I surmise this must mean that they configured Perl with _GNU_SOURCE defined. Maybe it was done so Perl could get at crypt_r, but more likely it was for some other reasons altogether ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] more i18n/l10n issues
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Now for something completely different: > The postmaster executable shows --help display perfectly localized. > However I just noted that postgres --help output (the standalone > backend) does not; is it not i18n'ed, or is some sort of missetup? postgres.c's usage() isn't localized. Dunno whether it should be or not, given that we don't normally expect users to invoke it. > However, if I try postgres --help-config the localized display is shown, > but postmaster --help-config says > $ LC_MESSAGES=C /tmp/pgsql-es/bin/postmaster --help-config > FATAL: --help-config requires a value --help-config is only a postgres option. We could possibly make it a postmaster option too, but I'm not sure there's any need for it. regards, tom lane ---(end of broadcast)--- TIP 3: 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: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Bruce Momjian <[EMAIL PROTECTED]> writes: > Let's have multiple people eyeball the patch and give it an OK and we > can add it for 7.4 if people want it. Well, we haven't even *got* a proposed patch yet, but yeah we should tread carefully. I do think it'd be okay to apply a patch if we can come up with one that Stephan and Jan and I all like. As you say, dump/reload speed normally doesn't get thought about except at this stage of the release cycle, so ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Let's have multiple people eyeball the patch and give it an OK and we > > can add it for 7.4 if people want it. > > Well, we haven't even *got* a proposed patch yet, but yeah we should > tread carefully. I do think it'd be okay to apply a patch if we can > come up with one that Stephan and Jan and I all like. As you say, > dump/reload speed normally doesn't get thought about except at this > stage of the release cycle, so ... OK. What releases had this slow restore problem? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Well, we haven't even *got* a proposed patch yet, but yeah we should > >> tread carefully. > > > OK. What releases had this slow restore problem? > > We introduced it in 7.3 --- before that, FKs were simply dumped as > "create trigger" commands, and there was no check overhead. So arguably > it is a bug; a performance bug maybe, but that's still a bug. No one > has yet gone through a dump/reload cycle in which they had to face this > penalty. Now that is a strong argument. I knew you would find one. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] _GNU_SOURCE
Here is an email from the DBD:pg guys describing what _GNU_SOURCE does. --- Jeroen Ruigrok/asmodai wrote: > It's a glibc thing. > > Look at glibc's include/features.h: > > _GNU_SOURCE All of the above, plus GNU extensions. > > Which means it enables all this: > > __STRICT_ANSI__, _ISOC99_SOURCE, _POSIX_SOURCE, _POSIX_C_SOURCE, > _XOPEN_SOURCE, _XOPEN_SOURCE_EXTENDED, _LARGEFILE_SOURCE, > _LARGEFILE64_SOURCE, _FILE_OFFSET_BITS=N, _BSD_SOURCE, _SVID_SOURCE > > -- > Jeroen Ruigrok van der Werven / asmodai / kita no mono > PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B > http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ > There is no greater sorrow than to recall, in misery, the time when we > were happy... > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Stephan Szabo wrote: > Hmm, my initial testing showed that it really was a little slower > than a more complicated one with NOT EXISTS so I'd abandoned it. How does > it fare for you compared to: > select f1, f2 from fk where not exists (select 1 from pk where pk.f1=fk.f1 > and pk.f2=pk.f2) where fk.f1 is not null and fk.f2 is not null; > > I believe the above is the appropriate not exists form for match > unspecified. > > I've actually got code (that no longer cleanly applies, but...) that uses > the single query version with NOT EXISTS (which could be easily changed to > either of the other forms) and was planning to put it together for a patch > when 7.5 devel started because I figured it wasn't precisely a bug and > wouldn't get accepted for 7.4. I am a little lost on this point myself --- are we talking 7.4 or 7.5 for this change? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump doesn't dump binary compatible casts
Joshua D. Drake wrote: Hello, Don't know if my vote counts here, but ANYTHING that makes pg_dump more correct should be backpatched. It is one thing to have index bloat, it is entirely another to not be able to correctly backup and restore. Patch applied to REL7_3_STABLE. Jan Tom Lane wrote: No objection here. -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] more i18n/l10n issues
Some more comments: #: utils/misc/guc.c:647 msgid "collect statistics about executing commands" Is this really "statistics" about the executing commands? #: utils/misc/guc.c:892 msgid "" "The number must be a positive integer. If 0 is specified then effort * " "log2(poolsize) is used" Is it missing the final dot? #: commands/tablecmds.c:2029 msgid "column \"%s\" is in a primary key" So, is it in A primary key, or THE primary key? #: commands/tablecmds.c:4093 msgid "relation \"%s\" already has a TOAST table" This is the one place where I found it could be table instead of relation, because it's checked explicitly in the code. I haven't looked very hard though, there may be others, but I've seen the last message cleanups have changed some of them. Now for something completely different: The postmaster executable shows --help display perfectly localized. However I just noted that postgres --help output (the standalone backend) does not; is it not i18n'ed, or is some sort of missetup? However, if I try postgres --help-config the localized display is shown, but postmaster --help-config says $ LC_MESSAGES=C /tmp/pgsql-es/bin/postmaster --help-config FATAL: --help-config requires a value So, for some things postmaster is localized, but others are not supported; and for some things postgres is localized, but for others it's not. Is this some sort of planned behavior, inconsistency, or plain oversight? Thanks, -- Alvaro Herrera () "Endurecerse, pero jamás perder la ternura" (E. Guevara) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Tom Lane wrote: > > I've actually got code (that no longer cleanly applies, but...) that uses > > the single query version with NOT EXISTS (which could be easily changed to > > either of the other forms) and was planning to put it together for a patch > > when 7.5 devel started because I figured it wasn't precisely a bug and > > wouldn't get accepted for 7.4. > > Well, Bruce has this on his open-items list, so I figure we have a green > light to do something for 7.4 if we can work out what to do. I put it on because I wasn't clear exactly what was happening in the discussion. There also was discussion that we want to improve this now because everyone will be using for upgrading to 7.4, and with a ~50% db reload speed improvement, it is hard to ignore. I am not against the idea of adding it to 7.4 if we can do it cleanly, and in fact we are sort of waiting for more serious bug reports at this time, so doing something else to improve the code isn't out of the question if we can do it without stumbling --- seems dump/reload gets full attention only during beta, which makes sense. However, I think we have to be honest that this is a performance _improvement_, not a fix. Yea, you can say it was a bug that we did it the way we did in the past, but you have to look real hard to see it that way. :-) Let's have multiple people eyeball the patch and give it an OK and we can add it for 7.4 if people want it. If you look really hard, you can say it is a fix for a missing pg_upgrade! -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
On Sun, 28 Sep 2003, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Sat, 27 Sep 2003, Tom Lane wrote: > >> I thought of what seems to be a better design for the check query: use > >> a LEFT JOIN and check for NULL in the righthand joined column. > > > Hmm, my initial testing showed that it really was a little slower > > than a more complicated one with NOT EXISTS so I'd abandoned it. How does > > it fare for you compared to: > > select f1, f2 from fk where not exists (select 1 from pk where pk.f1=fk.f1 > > and pk.f2=pk.f2) where fk.f1 is not null and fk.f2 is not null; > > Were you testing against 7.3 or 7.4? On what kinds of tables? 7.4 with tables of 1-2 integer key columns with 10k-1m pk rows of sequential data (in the 2 key case it was value and #rows-value iirc) and 1m-20m fk rows of randomly generated valid data. But it wasn't any sort of amazingly detailed test and those aren't huge tables, but I don't exactly have a huge machine. I can go back through, do more tests and report back. > In 7.4 I think that the JOIN would yield as good or better a plan. The > best possible plan for the NOT EXISTS query is effectively a nestloop > with inner indexscan, which is great if the FK table is small and the > PK table is large, but it sucks otherwise. The planner should choose a > plan of this form for the LEFT JOIN given that combination of table > sizes, and so there shouldn't be any great difference in runtime in that > case. But in other combinations, such as large FK and small PK, other > plan types will beat the pants off nestloop. That's what I was expecting too. I expected it to basically go, NOT IN, LEFT JOIN, NOT EXISTS in speed (at least when the hashing stuff happened for in given the not in enhancements), but didn't actually see that. > > I've actually got code (that no longer cleanly applies, but...) that uses > > the single query version with NOT EXISTS (which could be easily changed to > > either of the other forms) and was planning to put it together for a patch > > when 7.5 devel started because I figured it wasn't precisely a bug and > > wouldn't get accepted for 7.4. > > Well, Bruce has this on his open-items list, so I figure we have a green > light to do something for 7.4 if we can work out what to do. I must have missed that. I'd have mentioned it earlier then. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
On Sat, 27 Sep 2003, Tom Lane wrote: > [ continuing a discussion from mid-August ] > > Stephan Szabo <[EMAIL PROTECTED]> writes: > >> I assume what you have in mind is to replace > >> validateForeignKeyConstraint() with something that does a join of the > >> two tables via an SPI command. > > > It'd probably be: > > MATCH unspecified: > > SELECT keycolumns FROM referencing_table WHERE > > (keycolumns) NOT IN (SELECT refcols FROM referenced_table > > WHERE refcol1 IS NOT NULL AND ... ) > > AND keycolumn1 IS NOT NULL AND ...; > > > MATCH FULL: (something like, I haven't tried it) > > SELECT keycolumns FROM referencing_table WHERE > > ((keycolumns) NOT IN (SELECT refcols FROM referenced_table > >WHERE refcol1 IS NOT NULL AND ...) > > AND > >(keycolumn1 IS NOT NULL AND ...) > > ) > > OR ((keycolumn1 IS NOT NULL)!=(keycolumn2 IS NOT NULL) OR ...) > > I thought of what seems to be a better design for the check query: use > a LEFT JOIN and check for NULL in the righthand joined column. For > example, I think a MATCH UNSPECIFIED on two columns could be tested like > this: > > select f1,f2 > from fk left join pk on (fk.f1=pk.f1 and fk.f2=pk.f2) > where pk.f1 is null and (fk.f1 is not null and fk.f2 is not null); > > and MATCH FULL is the same except > > where pk.f1 is null and (fk.f1 is not null OR fk.f2 is not null); > > MATCH PARTIAL would be harder; I think you'd need to generate a separate > query for each subset of the columns, in which you would probe for > unmatched rows having exactly that subset non-null. But it could be > done. > > Do you see any logical error here? > > In some preliminary tests, the planner seems to be able to choose > reasonable plans for this type of query even without pg_statistic data, > as long as it knows the table sizes (which it would do after CREATE INDEX). > So it would work reasonably well during a pg_dump script, I think. Hmm, my initial testing showed that it really was a little slower than a more complicated one with NOT EXISTS so I'd abandoned it. How does it fare for you compared to: select f1, f2 from fk where not exists (select 1 from pk where pk.f1=fk.f1 and pk.f2=pk.f2) where fk.f1 is not null and fk.f2 is not null; I believe the above is the appropriate not exists form for match unspecified. I've actually got code (that no longer cleanly applies, but...) that uses the single query version with NOT EXISTS (which could be easily changed to either of the other forms) and was planning to put it together for a patch when 7.5 devel started because I figured it wasn't precisely a bug and wouldn't get accepted for 7.4. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] more i18n/l10n issues
Alvaro Herrera writes: > Oh, there's another thing about the --help-config option. This option > includes an, er, option to display the items that belong to a given > group. So you could say > > /tmp/pgsql-es/bin/postgres --help-config -g 'Security' > > and the list of parameters that belong to a group that has the word > Security on it would be displayed. > > However, the -l option lists the groups with the translated names, and > of course those names do not match on -g specification. I'm quite unhappy about the --help-config option. It was developed without discussion, it was installed hastily, we don't have any information about that interactive configuration application it's supposed to target, it's not documented, it's full of unfinished business, it certainly doesn't make the code easier to maintain because all the documenation is duplicated, but not one-to-one. At this point, I wouldn't spend a lot of time trying to make sense of it. We can revisit it again in the next release and investigate how we can eliminate the duplication of effort between the documentation and the code. > This little patch allows both versions to match, translated and > untranslated. That doesn't make much sense to me. It might be that a translated word is equal to an English word but has a different meaning. It's also weird that you cannot search for a complete word, e.g. "syslog", it's going to print out all the parameters that contain "syslog". Again, I don't think we should worry about that now or we'll end up spending too much time on this. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Possible locale issue with 7.4
In 7.4 I am finding that '(' (and some other punctuation) is not a member of
[:print:]. It is in 7.3. It is a member of [:graph:] in 7.4 (which is
supposed to be [:print:] - [:space:]).
The following is my 7.4 config:
./configure --prefix=/usr/local/pgsql --enable-integer-datetimes --with-pgport=5433
For 7.3 I used:
./configure --prefix=/usr/lib/pgsql --exec-prefix=/usr --with-perl --with-openssl
--mandir=/usr/man --docdir=/usr/doc --enable-integer-datetimes
The following is an example of the problem:
area=> select version();
version
PostgreSQL 7.4beta3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
area=> select '(' ~ '[[:print:]]';
?column?
--
f
(1 row)
area=> select '(' ~ '[[:graph:]]';
?column?
--
t
(1 row)
area=> select '0' ~ '[[:print:]]';
?column?
--
t
(1 row)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] more i18n/l10n issues
On Mon, Sep 29, 2003 at 12:27:01AM +0200, Peter Eisentraut wrote: > Alvaro Herrera writes: > > [fixes for --help-config] > I'm quite unhappy about the --help-config option. It was developed > without discussion, it was installed hastily, we don't have any > information about that interactive configuration application it's supposed > to target, it's not documented, it's full of unfinished business, it > certainly doesn't make the code easier to maintain because all the > documenation is duplicated, but not one-to-one. At this point, I wouldn't > spend a lot of time trying to make sense of it. If you put it that way :-) I'll leave it alone. I hope it can be enhanced in the next release. I'm not sure of it usefulness anyway; the documentation seems good enough. > > This little patch allows both versions to match, translated and > > untranslated. > > That doesn't make much sense to me. It might be that a translated word is > equal to an English word but has a different meaning. You are probably right. In this case may I suggest sticking the gettext() on top of the first parameter to strstr(), so only translated versions are found? This makes for at least moderately sane behavior. -- Alvaro Herrera () Y una voz del caos me habló y me dijo "Sonríe y sé feliz, podría ser peor". Y sonreí. Y fui feliz. Y fue peor. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Possible locale issue with 7.4
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> In 7.4 I am finding that '(' (and some other punctuation) is not a member of
> [:print:]. It is in 7.3. It is a member of [:graph:] in 7.4 (which is
> supposed to be [:print:] - [:space:]).
This is not a locale problem, because I see it in C locale too.
[digs] Apparently this is an oversight in the new regex code we
lifted from Tcl 8.4.1:
switch ((enum classes) index)
{
case CC_PRINT:
case CC_ALNUM:
cv = getcvec(v, UCHAR_MAX, 1, 0);
if (cv)
{
for (i = 0; i <= UCHAR_MAX; i++)
{
if (pg_isalpha((chr) i))
addchr(cv, (chr) i);
}
addrange(cv, (chr) '0', (chr) '9');
}
break;
in other words, :print: is the same as :alnum:. This is obviously
a bug, will fix ... wonder if Henry Spencer knows about it?
regards, tom lane
---(end of broadcast)---
TIP 3: 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] Problem with function permission test in a view
Bruce Momjian wrote: I like your text much better --- added. I will throw this email in the 7.5 queue and we can decide if it is a bug then. If is a bug is better have a patch for the 7.4 May be is only a missing feature. For sure for us was and is actually a nightmare imagine : V1 -> V2 -> F / V3 -> V4 This mean that if a group A can use the view V1 and a group B can use a view V3 whe shall back trace that the function F must be executable by group A due V1 and by group B due V3. Fortunatelly Postgres now take trace of dependencies so we can ( not easily ) analyze each function and see if is used inside a view or not, and choose the right permission. We have ~ 100 View and ~ 100 functions :-( Regards Gaetano Mendola PS: I vote that is a bug :-) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Possible locale issue with 7.4
On Sun, Sep 28, 2003 at 20:09:31 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > > in other words, :print: is the same as :alnum:. This is obviously > a bug, will fix ... wonder if Henry Spencer knows about it? The really cute thing is I only found it because I made a mistake. I didn't want to include spaces in what I was using it for and really should have been using [:graph:] instead. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Possible locale issue with 7.4
On Sun, Sep 28, 2003 at 08:09:31PM -0400, Tom Lane wrote:
> Bruno Wolff III <[EMAIL PROTECTED]> writes:
> > In 7.4 I am finding that '(' (and some other punctuation) is not a member of
> > [:print:]. It is in 7.3. It is a member of [:graph:] in 7.4 (which is
> > supposed to be [:print:] - [:space:]).
>
> This is not a locale problem, because I see it in C locale too.
> [digs] Apparently this is an oversight in the new regex code we
> lifted from Tcl 8.4.1:
Here
http://cvs.sourceforge.net/viewcvs.py/tcl/tcl/generic/regc_locale.c?rev=1.10&view=auto
is the Tcl version. Is looks very similar (meaning, :print: is the
same as :alnum:). Note that the code hasn't changed since
Mon Jul 29 12:27:51 2002 UTC
but is marked with tags to version 8.4.4.
Maybe not too much people uses :print: ?
--
Alvaro Herrera ()
"A wizard is never late, Frodo Baggins, nor is he early.
He arrives precisely when he means to." (Gandalf, en LoTR FoTR)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] more i18n/l10n issues
Alvaro Herrera <[EMAIL PROTECTED]> writes: > If you put it that way :-) I'll leave it alone. I hope it can be > enhanced in the next release. I'm not sure of it usefulness anyway; > the documentation seems good enough. Some guys at Red Hat wanted it to support an admin tool that should see the light of day Real Soon Now. Peter's right that it could be improved though; in particular I would not care to defend its i18n behavior. I've left it undocumented partly because I figure we'll be changing it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_dump bug in 7.4
If you have a check constraint that tests if a boolean column is not false by just using the column name, pg_dump doesn't include parens around the check constraint which causes a syntax error when reloading the database. Using the following to create a table: create table test (col1 boolean constraint test check (col1)); pg_dump -c produced the following: -- -- PostgreSQL database dump -- SET SESSION AUTHORIZATION 'postgres'; SET SESSION AUTHORIZATION 'bruno'; SET search_path = public, pg_catalog; DROP TABLE public.test; SET SESSION AUTHORIZATION 'postgres'; -- -- TOC entry 3 (OID 2200) -- Name: public; Type: ACL; Schema: -; Owner: postgres -- REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO PUBLIC; SET SESSION AUTHORIZATION 'bruno'; -- -- TOC entry 4 (OID 605016) -- Name: test; Type: TABLE; Schema: public; Owner: bruno -- CREATE TABLE test ( col1 boolean, CONSTRAINT test CHECK col1 ); -- -- Data for TOC entry 5 (OID 605016) -- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno -- COPY test (col1) FROM stdin; \. SET SESSION AUTHORIZATION 'postgres'; -- -- TOC entry 2 (OID 2200) -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres -- COMMENT ON SCHEMA public IS 'Standard public schema'; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump bug in 7.4
I have a fix for this in the patch queue and it will be applied in 24 hours. If you want to try it, it is at: http://momjian.postgresql.org/cgi-bin/pgpatches --- Bruno Wolff III wrote: > If you have a check constraint that tests if a boolean column is not > false by just using the column name, pg_dump doesn't include parens > around the check constraint which causes a syntax error when reloading > the database. > > Using the following to create a table: > create table test (col1 boolean constraint test check (col1)); > > pg_dump -c produced the following: > -- > -- PostgreSQL database dump > -- > > SET SESSION AUTHORIZATION 'postgres'; > > SET SESSION AUTHORIZATION 'bruno'; > > SET search_path = public, pg_catalog; > > DROP TABLE public.test; > SET SESSION AUTHORIZATION 'postgres'; > > -- > -- TOC entry 3 (OID 2200) > -- Name: public; Type: ACL; Schema: -; Owner: postgres > -- > > REVOKE ALL ON SCHEMA public FROM PUBLIC; > GRANT ALL ON SCHEMA public TO PUBLIC; > > > SET SESSION AUTHORIZATION 'bruno'; > > -- > -- TOC entry 4 (OID 605016) > -- Name: test; Type: TABLE; Schema: public; Owner: bruno > -- > > CREATE TABLE test ( > col1 boolean, > CONSTRAINT test CHECK col1 > ); > > > -- > -- Data for TOC entry 5 (OID 605016) > -- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno > -- > > COPY test (col1) FROM stdin; > \. > > > SET SESSION AUTHORIZATION 'postgres'; > > -- > -- TOC entry 2 (OID 2200) > -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres > -- > > COMMENT ON SCHEMA public IS 'Standard public schema'; > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump bug in 7.4
If you are referring to my patch, Bruce - that does not fix it. Mine only addresses psql. I don't think that pg_dump uses pg_get_constraintdef(). It's probably a side effect of switching from using consrc to conbin. Chris Bruce Momjian wrote: I have a fix for this in the patch queue and it will be applied in 24 hours. If you want to try it, it is at: http://momjian.postgresql.org/cgi-bin/pgpatches --- Bruno Wolff III wrote: If you have a check constraint that tests if a boolean column is not false by just using the column name, pg_dump doesn't include parens around the check constraint which causes a syntax error when reloading the database. Using the following to create a table: create table test (col1 boolean constraint test check (col1)); pg_dump -c produced the following: -- -- PostgreSQL database dump -- SET SESSION AUTHORIZATION 'postgres'; SET SESSION AUTHORIZATION 'bruno'; SET search_path = public, pg_catalog; DROP TABLE public.test; SET SESSION AUTHORIZATION 'postgres'; -- -- TOC entry 3 (OID 2200) -- Name: public; Type: ACL; Schema: -; Owner: postgres -- REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO PUBLIC; SET SESSION AUTHORIZATION 'bruno'; -- -- TOC entry 4 (OID 605016) -- Name: test; Type: TABLE; Schema: public; Owner: bruno -- CREATE TABLE test ( col1 boolean, CONSTRAINT test CHECK col1 ); -- -- Data for TOC entry 5 (OID 605016) -- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno -- COPY test (col1) FROM stdin; \. SET SESSION AUTHORIZATION 'postgres'; -- -- TOC entry 2 (OID 2200) -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres -- COMMENT ON SCHEMA public IS 'Standard public schema'; ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 2-phase commit
Hiroshi Inoue wrote: (B> (B> > -Original Message- (B> > From: Tom Lane (B> > (B> > Bruce Momjian <[EMAIL PROTECTED]> writes: (B> > > Tom Lane wrote: (B> > >> You're not considering the possibility of a transient communication (B> > >> failure. (B> > (B> > > Can't the master re-send the request after a timeout? (B> > (B> > Not "it can", but "it has to". (B> (B> Why ?$B!!(BMainly the coordinator(slave) not the participant(master) (B> has the resposibilty to resolve the in-doubt transaction. (B (BAs far as I see, it's the above point which prevents the (Badvance of this topic and the issue must be solved ASAP. (B (BAs opposed to your answer (B Not "it can", but "it has to", (Bmy answer is (B Yes "it can", but "it doesn't have to". (B (BThe simplest senario(though there could be varations) is (B (B[At participant(master)'s side] (B Because the commit operations is done, does nothing. (B (B[At coordinator(slave)' side] (B 1) After a while (B 2) re-establish the communication path between the (B partcipant(master)'s TM. (B 3) resend the "commit requeset" to the participant's TM. (B 1)2)3) would be repeated until the coordinator receives (B the "commit ok" message from the partcipant. (B (BIf there's no objection from you, I would assume I'm right. (BPlease don't dodge my question this time. (B (Bregards, (BHiroshi Inoue (Bhttp://www.geocities.jp/inocchichichi/psqlodbc/ (B (B---(end of broadcast)--- (BTIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump bug in 7.4
Christopher Kings-Lynne wrote: > If you are referring to my patch, Bruce - that does not fix it. Mine > only addresses psql. > > I don't think that pg_dump uses pg_get_constraintdef(). It's probably a > side effect of switching from using consrc to conbin. Oh, yea. If forgot the pretty printing only happens in psql. Can someone generate a reproducable failure? --- > > Chris > > > Bruce Momjian wrote: > > I have a fix for this in the patch queue and it will be applied in 24 > > hours. If you want to try it, it is at: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches > > > > > > > > --- > > > > Bruno Wolff III wrote: > > > >>If you have a check constraint that tests if a boolean column is not > >>false by just using the column name, pg_dump doesn't include parens > >>around the check constraint which causes a syntax error when reloading > >>the database. > >> > >>Using the following to create a table: > >>create table test (col1 boolean constraint test check (col1)); > >> > >>pg_dump -c produced the following: > >>-- > >>-- PostgreSQL database dump > >>-- > >> > >>SET SESSION AUTHORIZATION 'postgres'; > >> > >>SET SESSION AUTHORIZATION 'bruno'; > >> > >>SET search_path = public, pg_catalog; > >> > >>DROP TABLE public.test; > >>SET SESSION AUTHORIZATION 'postgres'; > >> > >>-- > >>-- TOC entry 3 (OID 2200) > >>-- Name: public; Type: ACL; Schema: -; Owner: postgres > >>-- > >> > >>REVOKE ALL ON SCHEMA public FROM PUBLIC; > >>GRANT ALL ON SCHEMA public TO PUBLIC; > >> > >> > >>SET SESSION AUTHORIZATION 'bruno'; > >> > >>-- > >>-- TOC entry 4 (OID 605016) > >>-- Name: test; Type: TABLE; Schema: public; Owner: bruno > >>-- > >> > >>CREATE TABLE test ( > >>col1 boolean, > >>CONSTRAINT test CHECK col1 > >>); > >> > >> > >>-- > >>-- Data for TOC entry 5 (OID 605016) > >>-- Name: test; Type: TABLE DATA; Schema: public; Owner: bruno > >>-- > >> > >>COPY test (col1) FROM stdin; > >>\. > >> > >> > >>SET SESSION AUTHORIZATION 'postgres'; > >> > >>-- > >>-- TOC entry 2 (OID 2200) > >>-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres > >>-- > >> > >>COMMENT ON SCHEMA public IS 'Standard public schema'; > >> > >> > >> > >>---(end of broadcast)--- > >>TIP 8: explain analyze is your friend > >> > > > > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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
[HACKERS] Alter Table Column Datatype
I have a few questions (below).
Mechanism:
1) Rename the old column to ...pg.dropped... to get it out of
the way of step 2.
2) Create a new column with the wanted type and appropriate
constraints. Only not null is supported at the moment.
3) Alter in the corrected default (wrapped in cast). It isn't
done with column definition since that would replace NULLS with
the default.
4) Copy data from old column to new column with cast. As you
can see below it works with domains with constraints (most
complex case I could come up with. If data cannot be cast, the
transaction is aborted. This is done with a relfileswap method
so vacuum isn't necessary after this operation -- it also cleans
out dropped column data at the same time.
5) Drop the old (original) column that had earlier been renamed.
Questions:
Is this syntax ok?
ALTER TABLE ALTER COLUMN TYPE
COERCE_ASSIGNMENT is the appropriate coercion technique, I
assume? I don't like EXPLICIT as it would allow data to be
munged without telling the user about it.
In order to correct the Var nodes in Check constraints, views,
etc. I need to change the varattno and type information, then
wrap it in a cast to bring it back to the old data type?
Is there any way of expiring a function cache or, for that
matter, telling which functions use the column in question
internally?
-- Example of work completed to date
rbt=# create domain bdom as integer check(value < 3);
CREATE DOMAIN
rbt=# create sequence bseq;
CREATE SEQUENCE
rbt=# create table btab (col bigint default nextval('bseq'));
CREATE TABLE
rbt=# \d btab
Table "public.btab"
Column | Type | Modifiers
--+--+--
col| bigint | default nextval('bseq'::text)
rbt=# insert into btab default values;
INSERT 2509216 1
rbt=# insert into btab default values;
INSERT 2509217 1
rbt=# insert into btab default values;
INSERT 2509218 1
rbt=# insert into btab default values;
INSERT 2509219 1
rbt=# select * from btab;
col
--
1
2
3
4
(4 rows)
rbt=# alter table btab alter column col type bdom;
ERROR: value for domain bdom violates CHECK constraint "$1"
rbt=# delete from btab where col >= 3;
DELETE 2
rbt=# alter table btab alter column col type bdom;
ALTER TABLE
rbt=# \d btab
Table "public.btab"
Column | Type |Modifiers
--+--+--
col| bdom | default ((nextval('bseq'::text))::integer)::bdom
rbt=# select * from btab;
col
--
1
2
(2 rows)
rbt=# insert into btab default values;
ERROR: value for domain bdom violates CHECK constraint "$1"
rbt=# alter sequence bseq restart with 1;
ALTER SEQUENCE
rbt=# insert into btab default values;
INSERT 2509230 1
rbt=# select * from btab;
col
--
1
2
1
(3 rows)
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] [COMMITTERS] pgsql-server/src/backend catalog/index.c comma ...
"Marc G. Fournier" wrote: (B> (B> On Mon, 29 Sep 2003, Hiroshi Inoue wrote: (B> (B> > He also ignored my question about "2 phase commit" in pgsql-hackers, for (B> > example. (B> (B> Actually, I've been following that thread pretty closely, and I believe I (B> missed your question :( (B (BOK I would explain it again. (B (BBruced asked. (B (B> Bruce Momjian <[EMAIL PROTECTED]> writes: (B>> Tom Lane wrote: (B>>> You're not considering the possibility of a transient communication (B>>> failure. (B (B>> Can't the master re-send the request after a timeout? (B (BTom's answer was. (B (B> Not "it can", but "it has to". (B (BIMHO the partcipants(masters) don't have to bear such (Bheavy responsibility. My answer is (B (B Yes "it can", but "it doesn't have to". (B (BOf cource, the cooridnater(slave) has the responsibility (Bto retry the commit operation for the in-doubt transaction. (B (Bregards, (BHiroshi Inoue (B (B---(end of broadcast)--- (BTIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [COMMITTERS] pgsql-server/src/backend catalog/index.c comma ...
On Mon, 29 Sep 2003, Hiroshi Inoue wrote: > "Marc G. Fournier" wrote: > > > > On Mon, 29 Sep 2003, Hiroshi Inoue wrote: > > > > > He also ignored my question about "2 phase commit" in pgsql-hackers, for > > > example. > > > > Actually, I've been following that thread pretty closely, and I believe I > > missed your question :( > > OK I would explain it again. > > Bruced asked. > > > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> Tom Lane wrote: > >>> You're not considering the possibility of a transient communication > >>> failure. > > >> Can't the master re-send the request after a timeout? > > Tom's answer was. > > > Not "it can", but "it has to". > > IMHO the partcipants(masters) don't have to bear such > heavy responsibility. My answer is > > Yes "it can", but "it doesn't have to". > > Of cource, the cooridnater(slave) has the responsibility > to retry the commit operation for the in-doubt transaction. 'k, a statement, not a question, which is why it didn't stick with me as being unanswered :( ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
You could just as easily argue that the lack of integrity testing at data load time was equally a bug. I think we need someway of telling postgres to suppress a foreign key check. The main problem is that the foreign key column is often not indexed. Chris Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: Tom Lane wrote: Well, we haven't even *got* a proposed patch yet, but yeah we should tread carefully. OK. What releases had this slow restore problem? We introduced it in 7.3 --- before that, FKs were simply dumped as "create trigger" commands, and there was no check overhead. So arguably it is a bug; a performance bug maybe, but that's still a bug. No one has yet gone through a dump/reload cycle in which they had to face this penalty. Now that is a strong argument. I knew you would find one. :-) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 2-phase commit
On Mon, 29 Sep 2003, Hiroshi Inoue wrote: > The simplest senario(though there could be varations) is > > [At participant(master)'s side] > Because the commit operations is done, does nothing. > > [At coordinator(slave)' side] >1) After a while >2) re-establish the communication path between the > partcipant(master)'s TM. >3) resend the "commit requeset" to the participant's TM. > 1)2)3) would be repeated until the coordinator receives > the "commit ok" message from the partcipant. > > If there's no objection from you, I would assume I'm right. 'K, but what happens if the slave never gets a 'commit ok'? Does the slave keep trying ad nausem? ---(end of broadcast)--- TIP 3: 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: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Christopher Kings-Lynne wrote: > You could just as easily argue that the lack of integrity testing at > data load time was equally a bug. > > I think we need someway of telling postgres to suppress a foreign key check. > > The main problem is that the foreign key column is often not indexed. As I remember, the new code is showing full table checks of a few seconds, rather than minutes, but I agree we do need a way to turn off checks some times. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 2-phase commit
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > The simplest senario(though there could be varations) is > [At participant(master)'s side] > Because the commit operations is done, does nothing. > [At coordinator(slave)' side] >1) After a while >2) re-establish the communication path between the > partcipant(master)'s TM. >3) resend the "commit requeset" to the participant's TM. > 1)2)3) would be repeated until the coordinator receives > the "commit ok" message from the partcipant. [ scratches head ] I think you are using the terms "master" and "slave" oppositely than I would. But in any case, this is not an answer to the concern I had. You're assuming that the "coordinator(slave)" side is willing to resend a request indefinitely, and also that the "participant(master)" side is willing to retain per-transaction commit state indefinitely so that it can correctly answer belated questions from the other side. What I was complaining about was that I don't think either side can afford to remember per-transaction state indefinitely. 2PC in the abstract is a useless academic abstraction --- where the rubber meets the road is defining how you cope with failures in the commit protocol. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > I think we need someway of telling postgres to suppress a foreign key check. Well, the subtext argument here is "do we fix it by providing a way to suppress the check, or do we fix it by making the check fast enough to be tolerable?" I think the advantages of choice (b) are obvious --- it doesn't allow bogus data to be loaded accidentally, and it doesn't create a problem with loading existing 7.3 dump files that don't know how to suppress the check. If we find there is no way to do (b) acceptably well, then and only then would I want to consider (a). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pg_get_ruledef and extra line breaks
Hi, I notice that the pretty printing version of pg_get_ruledef inserts extra newlines whereas all the others pretty functions (except view defs) do not. In fact, Andreas argued against a version of pg_get_triggerdef() that added extra newlines. eg, non-pretty: test=# select pg_get_ruledef(oid) from pg_rewrite order by oid desc limit 8;; pg_get_ruledef - CREATE RULE r5 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE r4 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE r3 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE r2 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE r1 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE "_RETURN" AS ON SELECT TO v DO INSTEAD SELECT 1; CREATE RULE asdfx AS ON INSERT TO test WHERE 1 = 1) OR (1 = 2)) OR (1 = 3)) OR (1 = 5)) DO INSTEAD NOTHING; CREATE RULE asdf AS ON INSERT TO test WHERE 1 = 1) OR (1 = 2)) OR (1 = 3)) OR (1 = 5)) DO INSTEAD NOTHING; (8 rows) eg, pretty: test=# select pg_get_ruledef(oid, true) from pg_rewrite order by oid desc limit 8;; pg_get_ruledef -- CREATE RULE r5 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE r4 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE r3 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE r2 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE r1 AS ON INSERT TO v DO INSTEAD NOTHING; CREATE RULE "_RETURN" AS ON SELECT TO v DO INSTEAD SELECT 1; CREATE RULE asdfx AS ON INSERT TO test WHERE 1 = 1 OR 1 = 2 OR 1 = 3 OR 1 = 5 DO INSTEAD NOTHING; CREATE RULE asdf AS ON INSERT TO test WHERE 1 = 1 OR 1 = 2 OR 1 = 3 OR 1 = 5 DO INSTEAD NOTHING; (8 rows) Are those newlines really necessary? They seem a bit out of place? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 2-phase commit
Tom Lane wrote: (B> (B> Hiroshi Inoue <[EMAIL PROTECTED]> writes: (B> > The simplest senario(though there could be varations) is (B> (B> > [At participant(master)'s side] (B> > Because the commit operations is done, does nothing. (B> (B> > [At coordinator(slave)' side] (B> >1) After a while (B> >2) re-establish the communication path between the (B> > partcipant(master)'s TM. (B> >3) resend the "commit requeset" to the participant's TM. (B> > 1)2)3) would be repeated until the coordinator receives (B> > the "commit ok" message from the partcipant. (B> (B> [ scratches head ] I think you are using the terms "master" and "slave" (B> oppositely than I would. (B (BOops my mistake, sorry. (BBut is it 2-phase commit protocol in the first place ? (B (Bregards, (BHiroshi Inoue (Bhttp://www.geocities.jp/inocchichichi/psqlodbc/ (B (B---(end of broadcast)--- (BTIP 9: the planner will ignore your desire to choose an index scan if your (B joining column's datatypes do not match
[HACKERS] pg_dump no longer honors --no-reconnect
>From CVS logs I see: pg_dump/pg_restore now always use SET SESSION AUTHORIZATION, not \connect, to control object ownership. The use-set-session-authorization and no-reconnect switches are obsolete (still accepted on the command line, but they don't do anything). This is a precursor to fixing handling of CREATE SCHEMA, which will be a separate commit. This was to fix a problem with restoring schemas. However, the commit says that --use-set-session-authorization and --no-reconnect are no longer honored. Seems we can silently ignore a --use-set-session-authorization flag because that is now the default, but I don't see how we can ignore a --no-reconnect flag --- we should throw an error. The pg_dump manual says: -R --no-reconnect This option is obsolete but still accepted for backwards compatibility. Also, the 7.3 manual mentions that only the super-user can restore using --use-set-session-authorization. This is now the only way to create dumps. Seems this is a new limitation to pg_dump that we didn't discuss. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 2-phase commit
(B (BHiroshi Inoue wrote: (B> (B> Tom Lane wrote: (B> > (B> > Hiroshi Inoue <[EMAIL PROTECTED]> writes: (B> > > The simplest senario(though there could be varations) is (B> > (B> > > [At participant(master)'s side] (B> > > Because the commit operations is done, does nothing. (B> > (B> > > [At coordinator(slave)' side] (B> > >1) After a while (B> > >2) re-establish the communication path between the (B> > > partcipant(master)'s TM. (B> > >3) resend the "commit requeset" to the participant's TM. (B> > > 1)2)3) would be repeated until the coordinator receives (B> > > the "commit ok" message from the partcipant. (B> > (B> > [ scratches head ] I think you are using the terms "master" and "slave" (B> > oppositely than I would. (B> (B> Oops my mistake, sorry. (B> But is it 2-phase commit protocol in the first place ? (B (BThat is, in your exmaple below (B (B Example: (B (BMaster Slave (B-- - (Bcommit ready--> (B<--OK (Bcommit done->XX (B (Bis the "commit done" message needed ? (B (Bregards, (BHiroshi Inoue (Bhttp://www.geocities.jp/inocchichichi/psqlodbc/ (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (B http://www.postgresql.org/docs/faqs/FAQ.html
Re: ADD FOREIGN KEY (was Re: [HACKERS] [GENERAL] 7.4Beta)
I think the advantages of choice (b) are obvious --- it doesn't allow bogus data to be loaded accidentally, and it doesn't create a problem with loading existing 7.3 dump files that don't know how to suppress the check. OK, I didn't realise there was a (b). I volunteer to do speed tests on data reloading on real data for our site. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 2-phase commit
Tom Lane wrote: (B> (B> Hiroshi Inoue <[EMAIL PROTECTED]> writes: (B> > The simplest senario(though there could be varations) is (B> (B> > [At participant(master)'s side] (B> > Because the commit operations is done, does nothing. (B> (B> > [At coordinator(slave)' side] (B> >1) After a while (B> >2) re-establish the communication path between the (B> > partcipant(master)'s TM. (B> >3) resend the "commit requeset" to the participant's TM. (B> > 1)2)3) would be repeated until the coordinator receives (B> > the "commit ok" message from the partcipant. (B> (B> [ scratches head ] I think you are using the terms "master" and "slave" (B> oppositely than I would. But in any case, this is not an answer to the (B> concern I had. You're assuming that the "coordinator(slave)" side is (B> willing to resend a request indefinitely, and also that the (B> "participant(master)" side is willing to retain per-transaction commit (B> state indefinitely so that it can correctly answer belated questions (B> from the other side. What I was complaining about was that I don't (B> think either side can afford to remember per-transaction state (B> indefinitely. (B (BOK maybe I understand your complaint. (BBasically such situation can occur when either side (Bis down. Especially when the coodinator(master) is down, (Bthe particicipants are troubled. In such cases, e.g. XA (Binterface allows heuristic-commit on the participants. (B (BIn case one or more paricipants are down, the coordinator (Bmay have to remember per-transaction state indefinitely. (BIs it a big problem ? (B (Bregards, (BHiroshi Inoue (Bhttp://www.geocities.jp/inocchichichi/psqlodbc/ (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
