Re: [HACKERS] Disaster!
-On [20040125 03:52], Tom Lane ([EMAIL PROTECTED]) wrote: Hm, okay, I'm pretty sure that that combination wouldn't report ENOSPC at close(). From Tru64's write(2): [ENOSPC] [XSH4.2] No free space is left on the file system containing the file. [Tru64 UNIX] An attempt was made to write past the early warning EOT while this indicator was enabled. [Tru64 UNIX] An attempt was made to write at or beyond the end of a partition. From close(2): [Tru64 UNIX] A close() function on an NFS file system waits for all outstanding I/O to complete. If any operation completes with an error, the error will be returned by close(). The possible errors depend on the NFS server implementation, but the most likely errors are: [snip...] [ENOSPC] Attempted to write on a full file system. We need to fix the code to check close's return value, probably, but it seems we still lack a clear explanation of what happened to your database. You always need to check the return codes of calls like that, what if you received EBADF or EINTR for whatever reason? -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai / kita no mono PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://diary.in-nomine.org/ From the pine tree, learn of the pine tree. And from the bamboo, of the bamboo... ---(end of broadcast)--- TIP 8: explain analyze is your friend
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(at)wxs.nl / 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] constraint modification on todo list
-On [20030911 15:43], Tom Lane ([EMAIL PROTECTED]) wrote: We can't ALTER a table that's already in use when the first ALTER starts, either --- its attempt to exclusive-lock the table will fail. But once you get the exclusive lock, you can (in Postgres) perform a series of operations without fear that subsequently-started transactions will be able to see the incompletely changed state of the table. Evidently Oracle can't handle that. That's why they need to invent combination operations like MODIFY CONSTRAINT. As my colleague says: it is indeed a lazy choice, but super safe and that's the goal. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / 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/ Man inagines that it is death he fears; but what he fears is the unforeseen, the explosion. What man fears is himself... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraint modification on todo list
-On [20030909 00:42], Tom Lane ([EMAIL PROTECTED]) wrote: IIRC, Oracle does not have rollback-able DDL. That might imply that the reason they have MODIFY CONSTRAINT is that in Oracle you can't use the above way to eliminate the window. Can you put ALTERs inside transactions at all in Oracle? As one of the Oracle gurus at work told me: DDL does an implicit commit, so no rollback possible. It also shouldn't be necessary, because you cannot change a table which is in use. It attempts to do a table lock and it fails. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ We should take care not to make the intellect our god; it has, of course, powerful muscles, but no personality... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Maximum table size
-On [20030909 20:32], Bruce Momjian ([EMAIL PROTECTED]) wrote: I know Tom is concerned because we haven't tested it, but I don't think anyone has tested 16TB either, nor our 1600-column limit. If I had the space free on my SAN right now I'd try it. The 1600 column limit should be easy to test on every system with some scripts, no? Also, I think people look at these numbers to determine if PostgreSQL can handle their data needs 5-10 years down the road. At work right now I have a bunch of 2-3 TB databases using Oracle 8. We're expected to be using 60 TB in total storage about 2 years down the road (right now we're using about 20). I guess GIS databases and image databases might be the ones who would be more concerned about these sort of limits in the near term future? -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ From morning to night I stayed out of sight / Didn't recognise I'd become No more than alive I'd barely survive / In a word, overrun... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Maximum table size
-On [20030909 23:02], Andrew Dunstan ([EMAIL PROTECTED]) wrote: They must be very big images or there must be an awful lot of them :-) *grin* I was more thinking of organizations such as NASA and commercial entities storing satellite images in databases. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ I dream of gardens in the desert sand... ---(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] FreeBSD/i386 thread test
-On [20030908 18:52], Bruce Momjian ([EMAIL PROTECTED]) wrote: So you don't have all the *_r functions, and your non-*_r functions aren't thread-safe. Should we disable theading on FreeBSD? Seems so. Exactly. Most other threading works though. :) -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ The only source of knowledge is experience... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] constraint modification on todo list
Hi people, can someone add: Add an ALTER TABLE MODIFY CONSTRAINT item to the todo list? I am even willing to pick this one up in a while, after I finish some other outstanding tasks. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Pull me down again and guide me into pain... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] constraint modification on todo list
-On [20030908 20:52], Rod Taylor ([EMAIL PROTECTED]) wrote: This could be rather time consuming to actually write but having the ability to change foreign key on update / on delete modes without rechecking all of the data would be very useful. I was more interested in this feature for CHECK constraints. :) I think this is a more consistent syntax: ALTER TABLE .. ADD OR REPLACE table constraint I was following Oracle's example here for sake of some consistency between some RDBMSes. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Gravitation can not be held responsible for people falling in love... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] constraint modification on todo list
-On [20030908 22:42], Bruce Momjian ([EMAIL PROTECTED]) wrote: I assume MODIFY would allow you to alter the constraint without re-checking all the rows, as would be required by DROP/ADD. However, if you are modifying the constraint, wouldn't we have to recheck all the rows anyway. Of course, one idea would be to allow MODIFY to make changes that _don't_ require rechecking, but I have no idea what such changes would be. I might have misread/misremembered something: Constraint State Modification You can use the MODIFY CONSTRAINT clause of the ALTER TABLE statement to change the following constraint states: * DEFERRABLE or NOT DEFERRABLE * INITIALLY DEFERRED or INITIALLY IMMEDIATE * RELY or NORELY * USING INDEX ... * ENABLE or DISABLE * VALIDATE or NOVALIDATE * EXCEPTIONS INTO ... http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2103836 I wonder if it is possible to have ALTER TABLE ... MODIFY CONSTRAINT ... CHECK ... Because what I can imagine, and please correct me if I miss something in my thought pattern, you have a small gap between dropping a constraint and adding the new one allowing the possibility of missing checks. Or would this be solved by adding a second constraint under another constraint name with the new constraint values and then dropping the older one? If so, then ALTER TABLE ... RENAME CONSTRAINT would come in handy. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Things should be made as simple as possible, but not any simpler... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] FreeBSD/i386 thread test
-On [20030908 23:52], Peter Eisentraut ([EMAIL PROTECTED]) wrote: Why would FreeBSD have a library of thread-safe libc functions (libc_r) if the functions weren't thread-safe? I think the test is faulty. Having libc_r is not a guarantee that all functions of libc are represented in that library as thread-safe functions. gethostbyname_r() is a notable reentrant function which is absent in FreeBSD. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Character is what you are in the dark... ---(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] 64-bit pgsql
-On [20030905 16:42], Rod Taylor ([EMAIL PROTECTED]) wrote: Download 7.4 beta 2 and run regression tests on those platforms. Report back any issues or successes. 7.4 Release candidates will come with a call for reports on platforms that pass the regression tests which are used to make up the supported platform list. Well, on Itanium2 on FreeBSD 5.1 it compiles. I just need to get the semaphores to a higher value in order to actually do an initdb. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ In the world, there is nothing more submissive and weak than water. Yet for attacking that which is hard and strong, nothing can surpass it... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 64-bit pgsql
-On [20030905 17:22], Jeroen Ruigrok/asmodai ([EMAIL PROTECTED]) wrote: Well, on Itanium2 on FreeBSD 5.1 it compiles. I just need to get the semaphores to a higher value in order to actually do an initdb. Though, did 7.4 raise the bar on SysV IPC? On my other two boxes I haven't tweaked SysV IPC at all (semmni is at 10) and I get initdb. On this Itanium box when I want to initdb I get: creating template1 database in /p/scratch/asmodai/postgresql-7.4beta2/src/test/r egress/./tmp_check/data/base/1... FATAL: could not create semaphores: No space left on device DETAIL: Failed syscall was semget(4, 17, 03600). HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exc eeded. You need to raise the respective kernel parameter. Alternatively, reduc e PostgreSQL's consumption of semaphores by reducing its max_connections paramet er (currently 100). -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ He who has a why to live for can bear almost any how... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 64-bit pgsql
-On [20030905 17:42], Rod Taylor ([EMAIL PROTECTED]) wrote: Is this beta 1 or beta 2? Beta 1 has a bug which may require more shared resources than what is available. Sorry, beta 2. Should've made that clear. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Nothing is more honorable than enlightenment, nothing is more beautiful than virtue... ---(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] 64-bit pgsql
-On [20030905 17:52], Tom Lane ([EMAIL PROTECTED]) wrote: Actually the bug is in beta2, not beta1. I'd suggest grabbing the current nightly snapshot (see /dev on the ftp servers) in preference to beta2, if you are on a machine with small SysV IPC limits. Using a snapshot of September the 4th: creating template1 database in /p/scratch/asmodai/postgresql-snapshot/src/test/r egress/./tmp_check/data/base/1... FATAL: could not create semaphores: No space left on device DETAIL: Failed syscall was semget(4, 17, 03600). HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exc eeded. You need to raise the respective kernel parameter. Alternatively, reduc e PostgreSQL's consumption of semaphores by reducing its max_connections paramet er (currently 10). I mean, I just want to know if you guys still want to support the low SysV settings. If not I just need to ask the admins of the system in question to bump the values a bit. Otherwise I'll keep pestering. :) -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ In my mind nothing makes sense... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 64-bit pgsql
-On [20030905 18:32], Tom Lane ([EMAIL PROTECTED]) wrote: If it dies even at max_connections 10, that is a *lower* setting than we ever supported before (the pre-7.4 default was 32, and you need 20 or more to run the parallel regression test). I suspect that you actually don't have SysV semaphores enabled at all on that machine. There would be no point in trying any smaller values. The SysV semaphores are enabled all right. As comparison: Itanium box running 5.1 | Athlon box running 4-STABLE kern.ipc.msgmax: 16384 | kern.ipc.msgmax: 16384 kern.ipc.msgmni: 40 | kern.ipc.msgmni: 40 kern.ipc.msgmnb: 2048 | kern.ipc.msgmnb: 2048 kern.ipc.msgtql: 40 | kern.ipc.msgtql: 40 kern.ipc.msgssz: 8 | kern.ipc.msgssz: 8 kern.ipc.msgseg: 2048 | kern.ipc.msgseg: 2048 kern.ipc.semmap: 30 | kern.ipc.semmap: 30 kern.ipc.semmni: 10 | kern.ipc.semmni: 10 kern.ipc.semmns: 60 | kern.ipc.semmns: 60 kern.ipc.semmnu: 30 | kern.ipc.semmnu: 30 kern.ipc.semmsl: 60 | kern.ipc.semmsl: 60 kern.ipc.semopm: 100| kern.ipc.semopm: 100 kern.ipc.semume: 10 | kern.ipc.semume: 10 kern.ipc.semusz: 104| kern.ipc.semusz: 92 kern.ipc.semvmx: 32767 | kern.ipc.semvmx: 32767 kern.ipc.semaem: 16384 | kern.ipc.semaem: 16384 kern.ipc.shmmax: 67108864 | kern.ipc.shmmax: 33554432 kern.ipc.shmmin: 1 | kern.ipc.shmmin: 1 kern.ipc.shmmni: 192| kern.ipc.shmmni: 192 kern.ipc.shmseg: 128| kern.ipc.shmseg: 128 kern.ipc.shmall: 8192 | kern.ipc.shmall: 8192 kern.ipc.shm_use_phys: 0| kern.ipc.shm_use_phys: 0 The same settings, except for kern.ipc.semusz and kern.ipc.shmmax, which are higher than on my box running 7.3.4. So it does seem 7.4 places higher demand than 7.3.4 on the SysV IPC, since initdb also dies on my Athlon box which normally runs a PostgreSQL 7.3.4 database. Like I said before, I am not certain if that was intentional, but if it is a side effect of some change than the docs should emphasize that for this release the SysV IPC dependencies might be a bit heavier than 7.3.4. -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Believe those who are seeking the truth; doubt those who find it... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 64-bit pgsql
-On [20030905 20:52], Tom Lane ([EMAIL PROTECTED]) wrote:
Alternatively, find out what symbols your compiler predeclares.
If my theory is right then your pg_config_os.h file is failing to
define HAS_TEST_AND_SET; why?
Indeed, pg_config_os.h does not set anything for __ia64__.
When I added definitions for Itanium and Opteron to the
src/include/port/freebsd.h (attached) I get the following:
Opteron:
gmake[4]: Entering directory
`/h/scratch/asmodai/postgresql-snapshot/src/backend/access/transam'
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o clog.o clog.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o transam.o transam.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o varsup.o varsup.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o xact.o xact.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o xlog.o xlog.c
{standard input}: Assembler messages:
{standard input}:7886: Error: Incorrect register `%eax' used with `b' suffix
gmake[4]: *** [xlog.o] Error 1
Itanium:
gmake[4]: Entering directory
`/q/scratch/asmodai/postgresql-snapshot/src/backend/access/transam'
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o clog.o clog.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o transam.o transam.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o varsup.o varsup.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o xact.o xact.c
gcc -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/include
-c -o xlog.o xlog.c
../../../../src/include/storage/s_lock.h: In function `tas':
../../../../src/include/storage/s_lock.h:125: error: inconsistent operand constraints
in an `asm'
gmake[4]: *** [xlog.o] Error 1
On the Alpha box I get 10 out of 92 regression tests failed, see
http://www.in-nomine.org/~asmodai/regressions.diff
I see some are due to locks failed, but it had as much semaphores
available as the other boxes (or as little) and could go through the
entire initdb routine.
--
Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
The wisdom of the wise, and the experience of ages, may be preserved by
quotations...
--- freebsd.h.orig Fri Sep 5 21:38:06 2003
+++ freebsd.h Fri Sep 5 21:41:38 2003
@@ -44,5 +44,14 @@
#if defined(__powerpc__)
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
+#endif
+#if defined(__ia64__)
+#define HAS_TEST_AND_SET
+typedef unsigned int slock_t;
+#endif
+
+#if defined(__x64_64__)
+#define HAS_TEST_AND_SET
+typedef unsigned int slock_t;
#endif
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 64-bit pgsql
-On [20030905 19:12], Tom Lane ([EMAIL PROTECTED]) wrote: It should not; there is something wrong here, not merely a documentation problem. I am wondering whether your 7.4 build fails to select a TAS() implementation --- if so, it would fall back to implementing spinlocks as semaphores, which would translate to a huge increase in the number of semaphores requested, which would likely result in this symptom. Can you strace the postmaster launch and see how many semget()s it does before dying? I need to correct one statement Tom, the Athlon box had 3 semaphores in use by another application. Which I killed after that. ipcs then showed it was not using anything and initdb ran ok. The Itanium box was also not using any semaphores and failed. I also did the same on an 5.1-CURRENT Alpha box and it also worked. So I guess the problem lies in the Itanium port. Or could I miss something subtle here? -- Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/ Distance lends enhancement to the view... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Potential bug in ALTER TABLE?
Hi,
just want to verify first with you guys before dumping it on the bugs
list. Most likely I am just being silly here or something.
Take this:
create table blah (name TEXT CHECK (name IN ('blah', 'bleh')));
test=# \d blah
Table public.blah
Column | Type | Modifiers
+--+---
name | text |
Check constraints: blah_name ((name = 'blah'::text) OR (name = 'bleh'::text))
As we would expect PostgreSQL to do. The constraint has an
automatically assigned name.
Now, to continue:
ALTER TABLE blah DROP CONSTRAINT blah_name;
ALTER TABLE blah ADD CHECK (name IN ('blah', 'bleh'));
test=# \d blah
Table public.blah
Column | Type | Modifiers
+--+---
name | text |
Check constraints: $1 ((name = 'blah'::text) OR (name = 'bleh'::text))
And this time around PostgreSQL doesn't assign an automatic name.
Well, it depends on what you call a name, but $1, $2, and so on isn't
quite descriptive. Is this an oversight or am I missing some subtle
thing here?
--
Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
Happiness is the absence of the striving for happiness...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Weird constraint output
[Please hold me on the to:/cc: list since I am not subscribed]
After talking this over with some of the great guys on IRC it was
suggested I ask here.
I am currently working on a document about how to convert from MySQL to
PostgreSQL (Sybase, Oracle, DB2, MS SQL Server are also going to be
done). I am now messing with CONSTRAINT CHECK and am encountering
something I don't see the logic of.
I decided to make sure Bugzilla works on pgsql 7.3.x and started to
convert the MySQL database schema to a pgsql one. One of the parts has
a layout like:
CREATE TABLE bugs (
-- ...skipping to relevant point
bug_severity character varying(50) -- in MySQL this is enum()
);
Next I did an:
ALTER TABLE ONLY bugs ADD CONSTRAINT bugs_severity_cstr CHECK
(bug_severity IN ('blocker', 'critical', 'major'));
Now, when I do a \d bugs I get:
Check constraints: bugs_severity_cstr (((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying)) OR (bug_severity = 'major'::character varying))
I would've expected:
Check constraints: bugs_severity_cstr ((bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying) OR (bug_severity = 'major'::character varying))
If you have even more choices there (as Bugzilla does) you even get:
CONSTRAINT bugs_severity_cstr CHECK bug_severity =
'blocker'::character varying) OR (bug_severity = 'critical'::character
varying)) OR (bug_severity = 'major'::character varying)) OR
(bug_severity = 'normal'::character varying)) OR (bug_severity =
'minor'::character varying)) OR (bug_severity = 'trivial'::character
varying)) OR (bug_severity = 'enhancement'::character varying)))
);
But there is no logic to have all those parens plus it makes pg_dump -s
and \d tablename a whole lot messier to read.
Can anyone clarify why we have it like this? Or whether or not it is a
bug perhaps? I could understand micro optimizations, but in this case?
Thanks,
--
Jeroen Ruigrok van der Werven asmodai(at)wxs.nl / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
One moon shows in every pool; in every pool, the one moon...
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
