Re: [HACKERS] Re: Changing the default value of an inherited column
At 01:36 31/03/01 -0500, Tom Lane wrote: > >which is OK as far as the field set goes, but it loses the additional >DEFAULT and NOT NULL information for the child table. Any thoughts on >the best way to fix this? > Can pg_dump easily detect overridden attrs? If so, we just treat them as table attrs and let the backend do it's stuff. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(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] Re: Changing the default value of an inherited column
Philip Warner <[EMAIL PROTECTED]> writes:
> Not a squawk as such, but does this have implications for pg_dump?
Good point. With recently-committed changes, try:
regression=# create table p1 (f1 int default 42 not null, f2 int);
CREATE
regression=# create table c1 (f1 int, f2 int default 7) inherits (p1);
NOTICE: CREATE TABLE: merging attribute "f1" with inherited definition
NOTICE: CREATE TABLE: merging attribute "f2" with inherited definition
CREATE
regression=# create table c2 (f1 int default 43, f2 int not null) inherits (p1);
NOTICE: CREATE TABLE: merging attribute "f1" with inherited definition
NOTICE: CREATE TABLE: merging attribute "f2" with inherited definition
CREATE
pg_dump dumps both c1 and c2 like this:
CREATE TABLE "c2" (
)
inherits ("p1");
which is OK as far as the field set goes, but it loses the additional
DEFAULT and NOT NULL information for the child table. Any thoughts on
the best way to fix this?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: 7.1 pg_dump fails for user-defined types (release stopper?)
At 15:49 31/03/01 +1000, Philip Warner wrote: > >(TOC2 > TOC1) >iff (Max(TOC2.OID, TOC2.DEPS) > Max(TOC1.OID, TOC1.DEPS)) > OR (Max(TOC2.OID, TOC2.DEPS) = Max(TOC1.OID, TOC1.DEPS) > And TOC1.OID = Max(TOC2.DEPS) > ) > >Where DEPS is a list of OIDs the TOC entry depends on. > >(I *think* that's right...). > This will of course not handle multi-level dependencies. But for the simple ordering we are talking about, I think it will work. It can be extended later when we want to walk a complete dependency tree. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: 7.1 pg_dump fails for user-defined types (release stopper?)
At 14:55 30/03/01 -0500, Tom Lane wrote: > >A more promising idea >is to hack function creation so that the OID assigned to the function >is lower than the OIDs assigned to any shell types created when the >function is defined. This seems hard; would it be better to have the CREATE TYPE use a new OID, and fixup the refs? >Or we could try to hack pg_dump to fix this, >but that doesn't seem appetizing. This *may* not be all that hard; there is a currently unused (always NULL) parameter on the pg_dump ArchiveEntry calls intended for extra dependencies. For UDTs, we could set the this to be the max OID that references the type (or a list of OIDs, if we had to), then modify the pg_restore sort code to check these values if not NULL. ie. (TOC2 > TOC1) iff (Max(TOC2.OID, TOC2.DEPS) > Max(TOC1.OID, TOC1.DEPS)) OR (Max(TOC2.OID, TOC2.DEPS) = Max(TOC1.OID, TOC1.DEPS) And TOC1.OID = Max(TOC2.DEPS) ) Where DEPS is a list of OIDs the TOC entry depends on. (I *think* that's right...). Since this will only be used when the args is non-null, this code would only be activated in the current broken case. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(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] Re: Changing the default value of an inherited column
At 12:10 30/03/01 -0500, Tom Lane wrote: > >Comments? I'm going to implement and commit this today unless I hear >loud squawks ... > Not a squawk as such, but does this have implications for pg_dump? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: MacOS X OK, was: Call for platforms
> Well, once I figured out a few things about how to use the Unix part > of MacOS X it seemed to work without a hitch in the regression > testing. I need to figure out how to make the system start it up > automatically at boot time still, but then I need to figure out how > to do double sided printing on an HP 5si and several other things too. OK, that confirms that MacOS X is supported. > BTW the offer of testing on NetBSD/mac68k stands. 250MB is plenty if > you don't use X, and I have some extra disks anyway. I'll probably > try NetBSD/macppc sometime in the next week or so. I'm always tempted to snare another platform "win", but will draw the line at dragging my own machines out of the closet ;) If there is no potential user who cares about it, we should let it die a graceful death. For NetBSD/ppc (and any other platform not on the list) the sooner the better; if you can bang it out at the beginning of the week we should be able to get the info into the release. - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Third call for platform testing (linux 2.4.x)
> I still don't see an entry for Linux 2.4.x My (uncommitted) updates to the real list show 2.4.2 in the comments section. I may remove all mention of versions, since it seems that most released versions of x86 Linux run PostgreSQL successfully. Comments? - Thomas > > Unreported or problem platforms: > > > > Linux 2.0.x MIPS 7.0 2000-04-13 (Tatsuo has lost machine) > > mklinux PPC750 7.0 2000-04-13, Tatsuo Ishii > > NetBSD m68k7.0 2000-04-10 (Henry has lost machine) > > NetBSD Sparc 7.0 2000-04-13, Tom I. Helbekkmo > > QNX 4.25 x86 7.0 2000-04-01, Dr. Andreas Kardos > > Ultrix MIPS7.1 2001-??-??, Alexander Klimov > > > > mklinux has failed Tatsuo's testing afaicr. Demote to unsupported? > > > > Any NetBSD partisans who can do testing or solicit testing from the > > NetBSD crowd? Same for OpenBSD? > > > > QNX is known to have problems with 7.1. Any hope of fixing for 7.1.1? Is > > there anyone able to work on it? If not, I'll move to the unsupported > > list. > > > > And here are the up-to-date platforms; thanks for the reports: > > > > AIX 4.3.3 RS6000 7.1 2001-03-21, Gilles Darold > > BeOS 5.0.3 x86 7.1 2000-12-18, Cyril Velter > > BSDI 4.01 x86 7.1 2001-03-19, Bruce Momjian > > Compaq Tru64 4.0g Alpha 7.1 2001-03-19, Brent Verner > > FreeBSD 4.3 x867.1 2001-03-19, Vince Vielhaber > > HPUX PA-RISC 7.1 2001-03-19, 10.20 Tom Lane, 11.00 Giles Lean > > IRIX 6.5.11 MIPS 7.1 2001-03-22, Robert Bruccoleri > > Linux 2.2.x Alpha 7.1 2001-01-23, Ryan Kirkpatrick > > Linux 2.2.x armv4l 7.1 2001-03-22, Mark Knox > > Linux 2.2.18 PPC750 7.1 2001-03-19, Tom Lane > > Linux 2.2.x S/390 7.1 2000-11-17, Neale Ferguson > > Linux 2.2.15 Sparc 7.1 2001-01-30, Ryan Kirkpatrick > > Linux 2.2.16 x86 7.1 2001-03-19, Thomas Lockhart > > MacOS X Darwin PPC 7.1 2000-12-11, Peter Bierman > > NetBSD 1.5 alpha 7.1 2001-03-22, Giles Lean > > NetBSD 1.5E arm32 7.1 2001-03-21, Patrick Welche > > NetBSD 1.5S x867.1 2001-03-21, Patrick Welche > > OpenBSD 2.8 x867.1 2001-03-22, Brandon Palmer > > SCO OpenServer 5 x86 7.1 2001-03-13, Billy Allie > > SCO UnixWare 7.1.1 x86 7.1 2001-03-19, Larry Rosenman > > Solaris 2.7 Sparc 7.1 2001-03-22, Marc Fournier > > Solaris x867.1 2001-03-27, Mathijs Brands > > SunOS 4.1.4 Sparc 7.1 2001-03-23, Tatsuo Ishii > > Windows/Win32 x86 7.1 2001-03-26, Magnus Hagander (clients only) > > WinNT/Cygwin x86 7.1 2001-03-16, Jason Tishler > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!!HELP
> Hi
>
> Regarding my previous post, I just successfully created a unique index on
> pg_shadow. DON'T DO THIS!!!
> ---
> CREATE UNIQUE INDEX shadow_index ON pg_shadow (usename)
> ---
> I couldn't create at pg_shadow_index as the pg prefix is reserved for
> system tables.
>
> This BROKE the database. At least I can't connect anymore with a:
> ---
> template1=# \c statements
> FATAL 1: Index 'pg_shadow_name_index' does not exist
> Previous connection kept
> template1=#
> ---
> If I look at the error log I get :
> ---
> ERROR: Illegal class name 'pg_shadow_index'
> The 'pg_' name prefix is reserved for system catalogs
> ERROR: Index 'pg_shadow_name_index' does not exist
> ERROR: SearchSysCache: recursive use of cache 23
> ERROR: SearchSysCache: recursive use of cache 23
> ERROR: SearchSysCache: recursive use of cache 23
> ERROR: SearchSysCache: recursive use of cache 23 <-- quite psql here
> FATAL 1: Index 'pg_shadow_name_index' does not exist <-- restarted again
> FATAL 1: Index 'pg_shadow_name_index' does not exist
> FATAL 1: Index 'pg_shadow_name_index' does not exist
> ---
>
> What can I do??? I've got a non-trivial amount of data that I cannot afford
> to lose!! HELP!..
First, here is a patch which will prevent this from happening in the
future. Do people want this held for 7.2 or applied now? It disables
the creation of user indexes on system tables.
The user-defined indexes on system columns can not be made to work
easily. Tom Lane pointed out to me in a phone call that code like:
CatalogIndexInsert(irelations, Num_pg_class_indices, relrelation, reltup);
assumes it knows the number of indexes on each system table, and a
user-defined one would not be updated by any system catalog change that
did not go through the executor.
As far as recovery, I am not sure. One issue is that pg_shadow is a
global table, not local to the database. My guess is that the global
table is still fine, but the index is in the database where you created
the index. You can't remove the file because pg_index thinks the index
is proper and exists.
I am kind of stumped.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Index: src/backend/catalog/index.c
===
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.144
diff -c -r1.144 index.c
*** src/backend/catalog/index.c 2001/03/22 06:16:10 1.144
--- src/backend/catalog/index.c 2001/03/30 22:55:54
***
*** 864,869
--- 864,876
indexInfo->ii_NumKeyAttrs < 1)
elog(ERROR, "must index at least one attribute");
+ if (heapRelationName && !allow_system_table_mods &&
+ IsSystemRelationName(heapRelationName) && IsNormalProcessingMode())
+ {
+ elog(ERROR, "You can not create indexes on system tables: '%s'",
+heapRelationName);
+ }
+
/*
* get heap relation oid and open the heap relation
*/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: AW: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> -Original Message- > From: Mikheev, Vadim [mailto:[EMAIL PROTECTED]] > > > > It is intuitive. The bug was iirc, that you saw 2 versions > > > of the same row in the second select statement (= 2 rows > > > returned by second select). > > > > I think we should be extremely wary of assuming that we have a clear > > characterization of "what the bug is", let alone "how to fix it". > > The real issue here is that SELECT has different MVCC visibility rules > > from UPDATE and SELECT FOR UPDATE. I suspect that that *must* be so > > This is not correct - SELECT has same rules. Are you able to reproduce > this bad behaviour without running queries in functions? I assume > the answer is NO. I just overlooked function case two years ago. > But SELECT/UPDATE visibility rules are same! Yes, there seems to be a confusion about visibility. Each query in SERIALIZABLE isolation level uses a common snapshot for a TX. Each query in READ COMMITTED isolation level uses its own snapshot. It seems the only difference between SERIALZABLE and READ COMMITTED. But there's a sort of SERIALIZABLE world inside functions even under READ COMMITTED mode. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Updated TODO.detail
I never liked the fact that the TODO web page pulls up a flat file copy of the TODO.detail mailbox file. I hacked together a little script that automatically MHonarc's the TODO.detail file and displays it to the user. You can see it if you go to main TODO web page: http://www.postgresql.org/docs/todo.html Click on any item in red. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Third call for platform testing (linux 2.4.x)
On Sat, Mar 31, 2001 at 12:02:35PM +1200, Franck Martin allegedly wrote: > I still don't see an entry for Linux 2.4.x > > Cheers. This should fix that: == All 76 tests passed. == rm regress.o make[2]: Leaving directory `/usr/exp/tmp/postgresql-7.1RC1/src/test/regress' make[1]: Leaving directory `/usr/exp/tmp/postgresql-7.1RC1/src/test' pc11j:/usr/exp/tmp/postgresql-7.1RC1$ uname -a Linux pc11j 2.4.2 #8 Fri Mar 2 16:40:13 CET 2001 i686 unknown Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pg_hba.conf "password" authentication broken?
if i use type "crypt", the backend assumes that the client is
handing it an already encrypted passwd, and then compares it to an encrypted
version of pg_shadow->passwd.
and if i use type "password filename", the backend assumes a clear text
password from the client, and then compares an encrypted version of that
to the normal contents of the second field of "filename".
however, if i use type "password", it just does a clear text comparison
of the password from the client and the password in pg_shadow.
attached are patches which allow for a special case type "password pg_shadow",
which similar to supplying a filename, actually encrypts the cleartext
password from the client, and compares it to the normal contents of pg_shadow.
this allows the storage of encrypted passwords in pg_shadow.
i was unable to determine any other way of not storing clear text passwords
in pg_shadow.
i implemented this in such a way that it will not impact existing
installations.
--
[ Jim Mercer [EMAIL PROTECTED] ]
[ Reptilian Research -- Longer Life through Colder Blood ]
[ aka[EMAIL PROTECTED] +1 416 410-5633 ]
*** auth.c.orig Fri Mar 30 19:37:08 2001
--- auth.c Fri Mar 30 19:28:20 2001
***
*** 695,701
static int
checkPassword(Port *port, char *user, char *password)
{
! if (port->auth_method == uaPassword && port->auth_arg[0] != '\0')
return verify_password(port->auth_arg, user, password);
return crypt_verify(port, user, password);
--- 695,702
static int
checkPassword(Port *port, char *user, char *password)
{
! if (port->auth_method == uaPassword && port->auth_arg[0] != '\0'
! && strcmp(port->auth_arg, "pg_shadow") != 0)
return verify_password(port->auth_arg, user, password);
return crypt_verify(port, user, password);
*** crypt.c.origFri Mar 30 19:38:26 2001
--- crypt.c Fri Mar 30 19:39:07 2001
***
*** 280,287
* authentication method being used for this connection.
*/
! crypt_pwd =
! (port->auth_method == uaCrypt ? crypt(passwd, port->salt) : passwd);
if (!strcmp(pgpass, crypt_pwd))
{
--- 280,294
* authentication method being used for this connection.
*/
! if (port->auth_method == uaCrypt)
! crypt_pwd = crypt(passwd, port->salt);
! else
! {
! /* if port->auth_arg, encrypt password from client before compare */
! if (port->auth_arg[0] != 0)
! pgpass = crypt(pgpass, passwd);
! crypt_pwd = passwd;
! }
if (!strcmp(pgpass, crypt_pwd))
{
---(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] Third call for platform testing (linux 2.4.x)
I still don't see an entry for Linux 2.4.x Cheers. Thomas Lockhart wrote: > Unreported or problem platforms: > > Linux 2.0.x MIPS 7.0 2000-04-13 (Tatsuo has lost machine) > mklinux PPC750 7.0 2000-04-13, Tatsuo Ishii > NetBSD m68k7.0 2000-04-10 (Henry has lost machine) > NetBSD Sparc 7.0 2000-04-13, Tom I. Helbekkmo > QNX 4.25 x86 7.0 2000-04-01, Dr. Andreas Kardos > Ultrix MIPS7.1 2001-??-??, Alexander Klimov > > mklinux has failed Tatsuo's testing afaicr. Demote to unsupported? > > Any NetBSD partisans who can do testing or solicit testing from the > NetBSD crowd? Same for OpenBSD? > > QNX is known to have problems with 7.1. Any hope of fixing for 7.1.1? Is > there anyone able to work on it? If not, I'll move to the unsupported > list. > > And here are the up-to-date platforms; thanks for the reports: > > AIX 4.3.3 RS6000 7.1 2001-03-21, Gilles Darold > BeOS 5.0.3 x86 7.1 2000-12-18, Cyril Velter > BSDI 4.01 x86 7.1 2001-03-19, Bruce Momjian > Compaq Tru64 4.0g Alpha 7.1 2001-03-19, Brent Verner > FreeBSD 4.3 x867.1 2001-03-19, Vince Vielhaber > HPUX PA-RISC 7.1 2001-03-19, 10.20 Tom Lane, 11.00 Giles Lean > IRIX 6.5.11 MIPS 7.1 2001-03-22, Robert Bruccoleri > Linux 2.2.x Alpha 7.1 2001-01-23, Ryan Kirkpatrick > Linux 2.2.x armv4l 7.1 2001-03-22, Mark Knox > Linux 2.2.18 PPC750 7.1 2001-03-19, Tom Lane > Linux 2.2.x S/390 7.1 2000-11-17, Neale Ferguson > Linux 2.2.15 Sparc 7.1 2001-01-30, Ryan Kirkpatrick > Linux 2.2.16 x86 7.1 2001-03-19, Thomas Lockhart > MacOS X Darwin PPC 7.1 2000-12-11, Peter Bierman > NetBSD 1.5 alpha 7.1 2001-03-22, Giles Lean > NetBSD 1.5E arm32 7.1 2001-03-21, Patrick Welche > NetBSD 1.5S x867.1 2001-03-21, Patrick Welche > OpenBSD 2.8 x867.1 2001-03-22, Brandon Palmer > SCO OpenServer 5 x86 7.1 2001-03-13, Billy Allie > SCO UnixWare 7.1.1 x86 7.1 2001-03-19, Larry Rosenman > Solaris 2.7 Sparc 7.1 2001-03-22, Marc Fournier > Solaris x867.1 2001-03-27, Mathijs Brands > SunOS 4.1.4 Sparc 7.1 2001-03-23, Tatsuo Ishii > Windows/Win32 x86 7.1 2001-03-26, Magnus Hagander (clients only) > WinNT/Cygwin x86 7.1 2001-03-16, Jason Tishler > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: third call for platforms...
> I have a Cobalt 2.0.x MIPS box that is currently compiling the latest CVS > of PostgreSQL ... I'll let you know in a few hours how it went. Great! - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Changing the default value of an inherited column
On Fri, Mar 30, 2001 at 11:05:53PM +0200, Peter Eisentraut wrote: > Tom Lane writes: > > > 3. The new column will have a default value if any of the combined > > column specifications have one. The last-specified default (the one > > in the explicitly given column list, or the rightmost parent table > > that gives a default) will be used. > > This seems pretty random. It would be more reasonable if multiple > (default) inheritance weren't allowed unless you explicitly specify a new > default for the new column, but we don't have a syntax for this. I agree, but I thought the original issue was that PG _does_ now have syntax for it. Any conflict in default values should result in either a failure, or "no default". Choosing a default randomly, or according to an arbitrary and complicated rule (same thing), is a source of bugs. > > 4. All relevant constraints from all the column specifications will > > be applied. In particular, if any of the specifications includes NOT > > NULL, the resulting column will be NOT NULL. (But the current > > implementation does not support inheritance of UNIQUE or PRIMARY KEY > > constraints, and I do not have time to add that now.) > > This is definitely a violation of that Liskov Substitution. If a context > expects a certain table and gets a more restricted table, it will > certainly notice. Not so. The rule is that the base-table code only has to understand the derived table. The derived table need not be able to represent all values possible in the base table. Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Changing the default value of an inherited column
On Fri, Mar 30, 2001 at 12:10:59PM -0500, Tom Lane wrote: > [EMAIL PROTECTED] (Nathan Myers) writes: > > The O-O principle involved here is Liskov Substitution: if the derived > > table is used in the context of code that thinks it's looking at the > > base table, does anything break? > > I propose the following behavior: > > 1. A table can have only one column of a given name. If the same > column name occurs in multiple parent tables and/or in the explicitly > specified column list, these column specifications are combined to > produce a single column specification. A NOTICE will be emitted to > warn the user that this has happened. The ordinal position of the > resulting column is determined by its first appearance. Treatment of like-named members of multiple base types is not done consistently in the various O-O languages. It's really a snakepit, and anything you do automatically will cause terrible problems for somebody. Nonetheless, for any given circumstances some possible approaches are clearly better than others. In C++, as in most O-O languages, the like-named members are kept distinct. When referred to in the context of a base type, the member chosen is the "right one". Used in the context of the multiply-derived type, the compiler reports an ambiguity, and you are obliged to qualify the name explicitly to identify which among the like-named inherited members you meant. You can declare which one is "really inherited". Some other languages presume to choose automatically which one they think you meant. The real danger is from members inherited from way back up the trees, which you might not know one are there. Of course PG is different from any O-O language. I don't know if PG has an equivalent to the "base-class context". I suppose PG has a long history of merging like-named members, and that the issue is just of the details of how the merge happens. > 4. All relevant constraints from all the column specifications will > be applied. In particular, if any of the specifications includes NOT > NULL, the resulting column will be NOT NULL. (But the current > implementation does not support inheritance of UNIQUE or PRIMARY KEY > constraints, and I do not have time to add that now.) Sounds like a TODO item... Do all the triggers of the base tables get applied, to be run one after another? -- Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Changing the default value of an inherited column
Peter Eisentraut <[EMAIL PROTECTED]> writes: >> 4. All relevant constraints from all the column specifications will >> be applied. In particular, if any of the specifications includes NOT >> NULL, the resulting column will be NOT NULL. (But the current >> implementation does not support inheritance of UNIQUE or PRIMARY KEY >> constraints, and I do not have time to add that now.) > This is definitely a violation of that Liskov Substitution. If a context > expects a certain table and gets a more restricted table, it will > certainly notice. Au contraire --- I'd say that if the child table fails to adhere to the constraints set for the parent table, *that* is a violation of inheritance. In particular, a table containing NULLs that is a child of a table in which the same column is marked NOT NULL is likely to blow up an application that is not expecting to get any nulls back. In any case, we have already been inheriting general constraints from parent tables. Relaxing that would be a change of behavior. The failure to inherit NOT NULL constraints some of the time (in some cases they were inherited, in some cases not) cannot be construed as anything but a bug. > If we're going to make changes to the inheritance logic, we could > certainly use some more thought than a few hours. The primary issue here is to revert the 7.0 behavior to what it had been for many years before that, and secondarily to make NOT NULL inheritance behave consistently with itself and with other constraints. It doesn't take hours of thought to justify either. I will agree that left-to-right vs. right-to-left precedence of inherited default values is pretty much a random choice, but it's doubtful that anyone is really depending on that. The existing behavior was not self-consistent anyway, since it was actually not "the first specified default" but "the default or lack of same attached to the first parent containing such a field". For example, if we do not change this behavior then create table p1 (f1 int); create table p2 (f1 int default 1) inherits(p1); results in p2.f1 having a default, while create table p1 (f1 int); create table p2 (f1 int default 1, f2 int); create table p3 (f3 int) inherits(p1, p2); results in p3.f1 not having a default. I don't think that can be argued to be anything but a bug either (consider what happens if p2 also says NOT NULL for f1). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: Changing the default value of an inherited column
Tom Lane writes: > 3. The new column will have a default value if any of the combined > column specifications have one. The last-specified default (the one > in the explicitly given column list, or the rightmost parent table > that gives a default) will be used. This seems pretty random. It would be more reasonable if multiple (default) inheritance weren't allowed unless you explicitly specify a new default for the new column, but we don't have a syntax for this. > 4. All relevant constraints from all the column specifications will > be applied. In particular, if any of the specifications includes NOT > NULL, the resulting column will be NOT NULL. (But the current > implementation does not support inheritance of UNIQUE or PRIMARY KEY > constraints, and I do not have time to add that now.) This is definitely a violation of that Liskov Substitution. If a context expects a certain table and gets a more restricted table, it will certainly notice. > Comments? I'm going to implement and commit this today unless I hear > loud squawks ... If we're going to make changes to the inheritance logic, we could certainly use some more thought than a few hours. If you want to revert the patch that was installed in 7.0 then ok, but the rest is not appropriate right now, IMHO. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [HACKERS] 7.1 pg_dump fails for user-defined types (release stopper?)
Anything dependent upon the original function in your example is busted anyhow, regardless of a dump/reload: CREATE function test() returns int4 AS 'SELECT 1' LANGUAGE 'SQL'; CREATE SELECT test() test -- 1 (1 row) CREATE VIEW test_view AS SELECT test(); CREATE SELECT * FROM test_view; test -- 1 (1 row) DROP FUNCTION test(); DROP CREATE function test() returns int4 AS 'SELECT 1' LANGUAGE 'SQL'; CREATE SELECT * FROM test_view; ERROR: Function OID 387520 does not exist So dumping OID order is least of the users' problems. Its hard to come up with examples where an object dependent upon another in a *working* database has a lesser OID. So the regression suite really did its job in this case. Mike Mascari [EMAIL PROTECTED] -Original Message- From: Darren King [SMTP:[EMAIL PROTECTED]] Sent: Friday, March 30, 2001 3:31 PM To: Pgsql-Hackers@Postgresql. Org Subject:RE: [HACKERS] 7.1 pg_dump fails for user-defined types (release stopper?) > A more promising idea is to hack function creation > so that the OID assigned to the function is lower > than the OIDs assigned to any shell types created > when the function is defined. Or we could try to > hack pg_dump to fix this, but that doesn't seem > appetizing. Requiring OID ordering would open up a new can of worms. What happens if the user does a drop/create on the function after creating it? The function could potentially be recreated with a higher OID and then the user would be in the same situation. If the system requires (or works around) creation ordering when creating functions and types, ISTM that pg_dump should have to do the same. darrenk ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] 7.1 pg_dump fails for user-defined types (release stopper?)
> A more promising idea is to hack function creation > so that the OID assigned to the function is lower > than the OIDs assigned to any shell types created > when the function is defined. Or we could try to > hack pg_dump to fix this, but that doesn't seem > appetizing. Requiring OID ordering would open up a new can of worms. What happens if the user does a drop/create on the function after creating it? The function could potentially be recreated with a higher OID and then the user would be in the same situation. If the system requires (or works around) creation ordering when creating functions and types, ISTM that pg_dump should have to do the same. darrenk ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] 7.1 pg_dump fails for user-defined types (release stopper?)
> I can think of a couple of ways to deal with this, the simplest being > to say "don't do that" --- ie, define widget_in with result type > "opaque" rather than "widget". That's pretty ugly and will likely Why is it ugly? Why not update RETURNS type for XXX_in function when creating type? > break people's 7.0 dump scripts all by itself. A more promising idea Is 7.1 pg_dump able to dump 7.0 database?.. > is to hack function creation so that the OID assigned to the function > is lower than the OIDs assigned to any shell types created when the > function is defined. How much lower? How to guarantee that OID of XXX_out created sometime after XXX_in will be lower than XXX' OID? > Or we could try to hack pg_dump to fix this, > but that doesn't seem appetizing. It looks like also right way to follow - pg_dump should care about system dependancies. > There may be similar problems with other shell-catalog-entry cases; > haven't looked yet. > > Is this a release stopper? I'm inclined to think it is. Yes, looks like that one -:( Vadim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: AW: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7 .1
> > This will take thought, research and discussion. A quick fix is the > > last thing that should be on our minds. > > From my latest tests( see following post), I tend to agree, > that this is extremely sensitive :-( > I do however think that Vadim's patch description was the > correct thing to do. To avoid double tuple versions return - maybe. To get same results from SELECT and SELECT FOR UPDATE in functions - no time for 7.1. > The problem case seems to be when the function is not > executed inside a txn. Any query is executed inside TX. All queries of a function are executed in the same TX. Vadim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: third call for platforms...
On Fri, 30 Mar 2001, Dominic J. Eidson wrote: > I have a Cobalt 2.0.x MIPS box that is currently compiling the latest CVS > of PostgreSQL ... I'll let you know in a few hours how it went. Compiled fine, and passed all but the geometry regression test: [root@web-cache regress]# more regression.out test boolean ... ok test char ... ok test name ... ok test varchar ... ok test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timestamp... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet ... ok test comments ... ok test oidjoins ... ok test type_sanity ... ok test opr_sanity ... ok test geometry ... FAILED test horology ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2... ok test copy ... ok test constraints ... ok test triggers ... ok test create_misc ... ok test create_aggregate ... ok test create_operator ... ok test create_index ... ok test inherit ... ok test create_view ... ok test sanity_check ... ok test errors ... ok test select ... ok test select_into ... ok test select_distinct ... ok test select_distinct_on ... ok test select_implicit ... ok test select_having... ok test subselect... ok test union... ok test case ... ok test join ... ok test aggregates ... ok test transactions ... ok test random ... ok test portals ... ok test arrays ... ok test btree_index ... ok test hash_index ... ok test misc ... ok test select_views ... ok test alter_table ... ok test portals_p2 ... ok test rules... ok test foreign_key ... ok test limit... ok test plpgsql ... ok test temp ... ok Attached, find the regression.diffs file. -- Dominic J. Eidson "Baruk Khazad! Khazad ai-menu!" - Gimli --- http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/ *** ./expected/geometry.out Tue Sep 12 16:07:16 2000 --- ./results/geometry.out Fri Mar 30 13:08:07 2001 *** *** 150,160 six |box -+ | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) ! | (71.7106781186548,72.7106781186548),(-69.7106781186548,-68.7106781186548) ! | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932738) ! | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559643) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) ! | (170.710678118655,70.7106781186548),(29.2893218813452,-70.7106781186548) (6 rows) -- translation --- 150,160 six |box -+ | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) ! | (71.7106781186547,72.7106781186547),(-69.7106781186547,-68.7106781186547) ! | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932737) ! | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559642) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) ! | (170.710678118655,70.7106781186547),(29.2893218813453,-70.7106781186547) (6 rows) -- translation *** *** 443,454 FROM CIRCLE_TBL; six | polygon
[HACKERS] 7.1 pg_dump fails for user-defined types (release stopper?)
Tricia Holben of Great Bridge just pointed out to me a rather nasty problem that's exposed by trying to pg_dump and reload the regression test database. The regression tests include CREATE FUNCTION widget_in(opaque) RETURNS widget AS '/home/postgres/pgsql/src/test/regress/regress.sl' LANGUAGE 'c'; NOTICE: ProcedureCreate: type 'widget' is not yet defined CREATE FUNCTION widget_out(opaque) RETURNS opaque AS '/home/postgres/pgsql/src/test/regress/regress.sl' LANGUAGE 'c'; CREATE TYPE widget ( internallength = 24, input = widget_in, output = widget_out, alignment = double ); which is considered a correct approach to defining I/O procedures for user-defined types; notice that the code goes out of its way to allow type "widget" to be referenced before it is defined. Unfortunately, since the shell pg_type entry for type widget is created before the pg_proc entry for widget_in is, the OID assignment sequence is: widget, widget_in, widget_out. When pg_dump dumps these objects in OID order, it dumps the CREATE TYPE command first --- an ordering that will fail upon reload. 7.0.* and before do not have this problem because they dump type definitions after function definitions, regardless of OIDs. I can think of a couple of ways to deal with this, the simplest being to say "don't do that" --- ie, define widget_in with result type "opaque" rather than "widget". That's pretty ugly and will likely break people's 7.0 dump scripts all by itself. A more promising idea is to hack function creation so that the OID assigned to the function is lower than the OIDs assigned to any shell types created when the function is defined. Or we could try to hack pg_dump to fix this, but that doesn't seem appetizing. There may be similar problems with other shell-catalog-entry cases; haven't looked yet. Is this a release stopper? I'm inclined to think it is. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: AW: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> > It is intuitive. The bug was iirc, that you saw 2 versions > > of the same row in the second select statement (= 2 rows > > returned by second select). > > I think we should be extremely wary of assuming that we have a clear > characterization of "what the bug is", let alone "how to fix it". > The real issue here is that SELECT has different MVCC visibility rules > from UPDATE and SELECT FOR UPDATE. I suspect that that *must* be so This is not correct - SELECT has same rules. Are you able to reproduce this bad behaviour without running queries in functions? I assume the answer is NO. I just overlooked function case two years ago. But SELECT/UPDATE visibility rules are same! Ever wonder why in SERIALIZABLE mode UPDATE/SELECT_FOR_UPDATE cause rollback in the event of concurrent modification? Because of concurrent modifications make visibility of SELECT and UPDATE different and this means *unconsistent* view of database for applications. In READ COMMITTED mode a query must see changes made by previous queries - the only one rule we have to follow to provide consistent result for applications. > in any mode that allows more concurrency than full serializable mode. > Thus, the question we are really facing is how we might alter the > visibility rules in a way that will make the results more intuitive > and/or useful while still allowing concurrency. > > This will take thought, research and discussion. A quick fix is the > last thing that should be on our minds. I agreed to leave it as Known Bug for 7.1. > A first question: where did the MVCC rules come from > originally, anyway? >From the fact that I've used Oracle before Postgres'95, liked it and had time to read its documentation -:) > Is there any academic research to look at? There is academic Theorem of Serializability but it's different from SERIALIZABLE mode definitions in standard. Probably, this difference was caused by lobbying from Oracle... Vadim ---(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] Re: [ADMIN] User administration tool
> > Wow, I see. I never suspected it did that too. :-) Seems I don't need > > to write anything, except perhaps add group capabilities to pgaccess. > > Isn't phpPgAdmin yet another tool of this type? I haven't tried it myself, > (no need, myself being the only user...) but the web page > (http://www.greatbridge.org/project/phppgadmin/projdisplay.php) says: Yes, it is. Some people prefer a non-web interaface. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> > > I doubt if it's a bug of SELECT. Well what > > > 'concurrent UPDATE then SELECT FOR UPDATE + > > > SELECT' return ? > > > > I'm going to add additional check to heapgettup and > > heap_fetch: > > SELECT seems to be able to return a different result > from that of preceding SELECT FOR UPDATE even after > applying your change. Oh, you're right. Well, if we really want that SELECT returns the same result as SELECT FOR UPDATE *in functions* (out of functions results are already same) then we have to add some modifications to fix proposed: 1. If newer version of visible tuple T is marked for update by us *before* query began then do not return T. 2. If tuple T1 is *not visible* because of it was inserted by concurrent committed TX then check if it's marked for update by current TX *before* query began and return *this* tuple version if yes. This will be in accordance with standard which requires us return committed (whenever) rows in READ COMMITTED mode. In fact, in this mode our SELECTs provide higher isolation than required by standard returning rows committed *before* query began. Why? Because of SELECT doesn't lock rows and the same row may be visited by SELECT in join queries many times - so we have to be protected against concurrent updates. SELECT FOR UPDATE protects us BUT if query itself calls some functions which updates queried table then currently we may lose information that tuple was marked for update before query began - so updating tuple inserted by concurrent committed TX and marked for update by us we would have to save its t_cmax in t_cmin (and either add new flag to t_infomask or don't turn OFF HEAP_MARKED_FOR_UPDATE in this case). This is not what I would like to do in 7.1 > SELECT doesn't seem guilty but the result is far > from intuitive. I think that SELECT is guilty. At least returning two versions of the same row! (One that could be fixed easy). Vadim ---(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] RC2 schedualed for Tomorrow evening ...
> The Hermit Hacker writes: > > > Just a heads up for anyone that might have something outstanding ... I'm > > going to package her early evening (~18:30AST) and announce it to both > > pgsql-hackers and pgsql-announce when done ... > > > > Once RC2 goes out, its meant to be a "this is what we'd release if docs > > were completely ready to go" ... > > AFAICT, we need to close out the platform list, re-generate INSTALL, and > that's it. Bruce mentioned something about additional changes in HISTORY, > though. I just need to grovel through the CVS logs again to update them. I will do that today. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RC2 schedualed for Tomorrow evening ...
On Fri, 30 Mar 2001, Peter Eisentraut wrote: > The Hermit Hacker writes: > > > Just a heads up for anyone that might have something outstanding ... I'm > > going to package her early evening (~18:30AST) and announce it to both > > pgsql-hackers and pgsql-announce when done ... > > > > Once RC2 goes out, its meant to be a "this is what we'd release if docs > > were completely ready to go" ... > > AFAICT, we need to close out the platform list, re-generate INSTALL, and > that's it. Bruce mentioned something about additional changes in HISTORY, > though. right, that falls under the "this is what we'd release if docs were completely ready to go" :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Third call for platform testing
On Fri, 30 Mar 2001, Mathijs Brands wrote: > On Fri, Mar 30, 2001 at 03:17:06PM +, Thomas Lockhart allegedly wrote: > > And here are the up-to-date platforms; thanks for the reports: > > > > > Solaris 2.7 Sparc 7.1 2001-03-22, Marc Fournier > > Marc, was this done without unix sockets? nope, purely default ... it was only the x86 platform that I had a bugger with getting a clean regress working on ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: third call for platforms...
( I deleted the email accidentially) I have a Cobalt 2.0.x MIPS box that is currently compiling the latest CVS of PostgreSQL ... I'll let you know in a few hours how it went. # uname -a Linux web-cache 2.0.34C52_SK #1 Tue Nov 30 18:14:40 PST 1999 mips unknown -- Dominic J. Eidson "Baruk Khazad! Khazad ai-menu!" - Gimli --- http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Third call for platform testing
On Fri, Mar 30, 2001 at 03:17:06PM +, Thomas Lockhart allegedly wrote: > And here are the up-to-date platforms; thanks for the reports: > Solaris 2.7 Sparc 7.1 2001-03-22, Marc Fournier Marc, was this done without unix sockets? Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: Changing the default value of an inherited column
[EMAIL PROTECTED] (Nathan Myers) writes: > The O-O principle involved here is Liskov Substitution: if the derived > table is used in the context of code that thinks it's looking at the > base table, does anything break? Good point. That answers my concern about how to handle typmod: an application *could* be broken by a change in typmod (eg, suppose it's allocated a buffer just big enough for a char(N) attribute, using the N of the parent table). Therefore we must disallow changes in typmod in child tables. Further study of creatinh.c shows that we have inconsistent behavior at the moment, as it will allow columns of the same name to be inherited from multiple parents and (silently) combined --- how is that really different from combining with an explicit specification? I propose the following behavior: 1. A table can have only one column of a given name. If the same column name occurs in multiple parent tables and/or in the explicitly specified column list, these column specifications are combined to produce a single column specification. A NOTICE will be emitted to warn the user that this has happened. The ordinal position of the resulting column is determined by its first appearance. 2. An error will be reported if columns to be combined do not all have the same datatype and typmod value. 3. The new column will have a default value if any of the combined column specifications have one. The last-specified default (the one in the explicitly given column list, or the rightmost parent table that gives a default) will be used. 4. All relevant constraints from all the column specifications will be applied. In particular, if any of the specifications includes NOT NULL, the resulting column will be NOT NULL. (But the current implementation does not support inheritance of UNIQUE or PRIMARY KEY constraints, and I do not have time to add that now.) This behavior differs from prior versions as follows: 1. We return to the pre-7.0 behavior of allowing an explicit specification of a column name that is also inherited (7.0 rejects this, thereby preventing the default from being changed in the child). However, we will now issue a warning NOTICE, to answer the concern that prompted this change of behavior. 2. We will now enforce uniformity of typmod as well as type OID when combining columns. 3. In both 7.0 and prior versions, if a column appeared in multiple parents but not in the explicit column list, the first parent's default value (if any) and NOT NULL state would be used, ignoring those of later parents. Failing to "or" together the NOT NULL flags is clearly wrong, and I believe it's inconsistent to use an earlier rather than later parent's default value when we want an explicitly-specified default to win out over all of them. The explicit column specifications are treated as coming after the last parent for other purposes, so we should define the default to use as the last one reading left-to-right. Comments? I'm going to implement and commit this today unless I hear loud squawks ... regards, tom lane ---(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] RC2 schedualed for Tomorrow evening ...
The Hermit Hacker writes: > Just a heads up for anyone that might have something outstanding ... I'm > going to package her early evening (~18:30AST) and announce it to both > pgsql-hackers and pgsql-announce when done ... > > Once RC2 goes out, its meant to be a "this is what we'd release if docs > were completely ready to go" ... AFAICT, we need to close out the platform list, re-generate INSTALL, and that's it. Bruce mentioned something about additional changes in HISTORY, though. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: [SQL] Re: pg_dump potential bug -UNIQUE INDEX on PG_SHADOW Dont!!HELP
I can confirm with current sources: test=> CREATE UNIQUE INDEX shadow_index ON pg_shadow (usename); CREATE test=> select * from pg_shadow; ERROR: Index 'pg_shadow_sysid_index' does not exist test=> \q $ psql test psql: FATAL 1: Index 'pg_shadow_name_index' does not exist $ gdb shows that the check in heap_create() is working because the index name does not begin with pg_, just the base table: Breakpoint 1, heap_create (relname=0x838d1d0 "shadow_index", tupDesc=0x83915e4, istemp=0 '\000', storage_create=0 '\000', allow_system_table_mods=0) at heap.c:183 183 boolnailme = false; First, should we allow user-specified indexes on system tables, and if so, why does this error happen? Notice the user wanted an index named shadow_index, but the error mentioned is pg_shadow_name_index. > Hi > > Regarding my previous post, I just successfully created a unique index on > pg_shadow. DON'T DO THIS!!! > --- > CREATE UNIQUE INDEX shadow_index ON pg_shadow (usename) > --- > I couldn't create at pg_shadow_index as the pg prefix is reserved for > system tables. > > This BROKE the database. At least I can't connect anymore with a: > --- > template1=# \c statements > FATAL 1: Index 'pg_shadow_name_index' does not exist > Previous connection kept > template1=# > --- > If I look at the error log I get : > --- > ERROR: Illegal class name 'pg_shadow_index' > The 'pg_' name prefix is reserved for system catalogs > ERROR: Index 'pg_shadow_name_index' does not exist > ERROR: SearchSysCache: recursive use of cache 23 > ERROR: SearchSysCache: recursive use of cache 23 > ERROR: SearchSysCache: recursive use of cache 23 > ERROR: SearchSysCache: recursive use of cache 23 <-- quite psql here > FATAL 1: Index 'pg_shadow_name_index' does not exist <-- restarted again > FATAL 1: Index 'pg_shadow_name_index' does not exist > FATAL 1: Index 'pg_shadow_name_index' does not exist > --- > > What can I do??? I've got a non-trivial amount of data that I cannot afford > to lose!! HELP!.. > > Regards > MArCin - Thanks > > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] HUGE BUG - Please fix!!!
This is fixed in current 7.1RC1 sources. > Hi guys, > > I don't want to do a patch for a one character error. Yet, that's a HUGE > error and it really needs to be fixed. > > Once you have a pg_database file which is over 1 page, it CRASHES. That's > what it did on me. Yes! I have many databases or rather, I often destroy > my databases to regenerate them from scratch (that's for me the easiest > way to do it). Because of that, the pg_database is now two pages. > > The utils/misc/database.c has a function called GetRawDatabaseInfo() > which reads that file "on its own". There is a HUGE bug in there, and > it may not always crash a system, but it really needs to be fixed. > > At line #183, you have a for() loop which looks like this (since at > least V6.5.0 and still present in V7.0.3): > > for (i = 0; i <= max; i++) > > All the other such loops start with an index of 1, not zero. And > therefore you want the <=. In this special case loop (or are all > the others special cases?!?) you need to use the following: > > for(i = 0; i < max; i++) > > Please, I know it's easier when you get a patch, but FIX IT. It's > not fun to try to access your database and have the backend crash > because of such a tiny bug! > > Thank you for all your work. > > > > Alexis Wilke > Director > Made to Order Software, Ltd > > e-mail: [EMAIL PROTECTED] > > Web Page: http://www.m2osw.com > Company e-mail: [EMAIL PROTECTED] > Phone: 020 8748 9898 +(44) 20 8748 9898 > Fax:020 8748 4250 +(44) 20 8748 4250 > Address:Britannia House > 1-11 Glenthorne Road > Hammersmith > London W6 0LF > United Kingdom > > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/?.refer=text > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
AW: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> If I remember correctly, UPDATE establishes a lock on the affected rows,
> which will block another UPDATE on the same rows for the duration of the
> transaction. If that's true, shouldn't I be able to achieve my desired
> behavior by removing the initial as follows:
>
> create function nextid( varchar(32)) returns int8 as '
> update idseq set id = id + 1 where name = $1::text;
> select id from idseq where name = $1::text;
> ' language 'sql';
Yes, better, but be sure, to only use this function from inside a transaction.
If you use it in autocommit mode (no begin work) you might in theory read a row,
that another session modified between the two lines.
> Or, would I still have to add FOR UPDATE to that final SELECT?
Now, this certainly looks very funny. You actually get reasonable results only
if you do include the "for update" with RC1 sources .
To the rest on the list:
Try the above example by adding a lock between the two lines:
create function nextid( varchar(32)) returns int8 as '
update idseq set id = id + 1 where name = $1::text;
select * from lock1;
select id from idseq where name = $1::text for update;
' language 'sql';
session1:
begin work;
lock table lock1 in access
exclusive mode;
session 2:
not in txn: select nextid('one'); // this blocks
select nextid('one');
commit work;
And stare at the results you get with and without for update :-(
Something is definitely fishy with the visibility of SELECT here.
Andreas
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: AW: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
Zeugswetter Andreas SB <[EMAIL PROTECTED]> writes: > It is intuitive. The bug was iirc, that you saw 2 versions of the same row > in the second select statement (= 2 rows returned by second select). I think we should be extremely wary of assuming that we have a clear characterization of "what the bug is", let alone "how to fix it". The real issue here is that SELECT has different MVCC visibility rules from UPDATE and SELECT FOR UPDATE. I suspect that that *must* be so in any mode that allows more concurrency than full serializable mode. Thus, the question we are really facing is how we might alter the visibility rules in a way that will make the results more intuitive and/or useful while still allowing concurrency. This will take thought, research and discussion. A quick fix is the last thing that should be on our minds. A first question: where did the MVCC rules come from originally, anyway? Is there any academic research to look at? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: [ADMIN] User administration tool
> Bruce Momjian writes: > > > I have started coding a user/group administration tool that allows you > > to add/modify/delete users and groups. I should have something working > > in a week. I will look similar to my pgmonitor tool. > > Pgaccess already does part of this. If you're going to write it in Tcl/Tk > anyway, I think you might as well integrate it there. Wow, I see. I never suspected it did that too. :-) Seems I don't need to write anything, except perhaps add group capabilities to pgaccess. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
AW: AW: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> > It is intuitive. The bug was iirc, that you saw 2 versions of the same row > > in the second select statement (= 2 rows returned by second select). > > I think we should be extremely wary of assuming that we have a clear > characterization of "what the bug is", let alone "how to fix it". > The real issue here is that SELECT has different MVCC visibility rules > from UPDATE and SELECT FOR UPDATE. I suspect that that *must* be so > in any mode that allows more concurrency than full serializable mode. Yes, definitely. > Thus, the question we are really facing is how we might alter the > visibility rules in a way that will make the results more intuitive > and/or useful while still allowing concurrency. > > This will take thought, research and discussion. A quick fix is the > last thing that should be on our minds. >From my latest tests( see following post), I tend to agree, that this is extremely sensitive :-( I do however think that Vadim's patch description was the correct thing to do. The problem case seems to be when the function is not executed inside a txn. I was not able to reproduce any failure, when inside txns, since the first update or select for update blocks the rest. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: Call for platforms
> Yep. We have many other MIPS (ONYX Crimson, , ONYX, Challenge, Indy w/ IRIX > 6.2, 6.5, etc.), Alpha and Sparc platforms if there are some others that need > testing (How about NetBSD on NeXT?). All of these are interesting to help others decide whether their particular machine is supported. For my narrow purposes of documenting which kinds of platforms are supported for the upcoming release, I'm focused on processor/OS combinations. So the following already seem to be covered: MIPS/IRIX (32 bit compilation only- try 64 bit compilation?) Alpha/Linux Alpha/Tru64 Sparc/Solaris Sparc/Linux x86/NetBSD (need all other NetBSD architectures!) x86/OpenBSD (need all other archs!) If you have other combinations (I've forgotten what NeXT is; we need 68k and 88k architectures tested; our NetBSD/68k guy no longer has that machine) they would be particularly helpful. TIA - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: [ADMIN] User administration tool
Bruce Momjian writes: > I have started coding a user/group administration tool that allows you > to add/modify/delete users and groups. I should have something working > in a week. I will look similar to my pgmonitor tool. Pgaccess already does part of this. If you're going to write it in Tcl/Tk anyway, I think you might as well integrate it there. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Third call for platform testing
Unreported or problem platforms: Linux 2.0.x MIPS 7.0 2000-04-13 (Tatsuo has lost machine) mklinux PPC750 7.0 2000-04-13, Tatsuo Ishii NetBSD m68k7.0 2000-04-10 (Henry has lost machine) NetBSD Sparc 7.0 2000-04-13, Tom I. Helbekkmo QNX 4.25 x86 7.0 2000-04-01, Dr. Andreas Kardos Ultrix MIPS7.1 2001-??-??, Alexander Klimov mklinux has failed Tatsuo's testing afaicr. Demote to unsupported? Any NetBSD partisans who can do testing or solicit testing from the NetBSD crowd? Same for OpenBSD? QNX is known to have problems with 7.1. Any hope of fixing for 7.1.1? Is there anyone able to work on it? If not, I'll move to the unsupported list. And here are the up-to-date platforms; thanks for the reports: AIX 4.3.3 RS6000 7.1 2001-03-21, Gilles Darold BeOS 5.0.3 x86 7.1 2000-12-18, Cyril Velter BSDI 4.01 x86 7.1 2001-03-19, Bruce Momjian Compaq Tru64 4.0g Alpha 7.1 2001-03-19, Brent Verner FreeBSD 4.3 x867.1 2001-03-19, Vince Vielhaber HPUX PA-RISC 7.1 2001-03-19, 10.20 Tom Lane, 11.00 Giles Lean IRIX 6.5.11 MIPS 7.1 2001-03-22, Robert Bruccoleri Linux 2.2.x Alpha 7.1 2001-01-23, Ryan Kirkpatrick Linux 2.2.x armv4l 7.1 2001-03-22, Mark Knox Linux 2.2.18 PPC750 7.1 2001-03-19, Tom Lane Linux 2.2.x S/390 7.1 2000-11-17, Neale Ferguson Linux 2.2.15 Sparc 7.1 2001-01-30, Ryan Kirkpatrick Linux 2.2.16 x86 7.1 2001-03-19, Thomas Lockhart MacOS X Darwin PPC 7.1 2000-12-11, Peter Bierman NetBSD 1.5 alpha 7.1 2001-03-22, Giles Lean NetBSD 1.5E arm32 7.1 2001-03-21, Patrick Welche NetBSD 1.5S x867.1 2001-03-21, Patrick Welche OpenBSD 2.8 x867.1 2001-03-22, Brandon Palmer SCO OpenServer 5 x86 7.1 2001-03-13, Billy Allie SCO UnixWare 7.1.1 x86 7.1 2001-03-19, Larry Rosenman Solaris 2.7 Sparc 7.1 2001-03-22, Marc Fournier Solaris x867.1 2001-03-27, Mathijs Brands SunOS 4.1.4 Sparc 7.1 2001-03-23, Tatsuo Ishii Windows/Win32 x86 7.1 2001-03-26, Magnus Hagander (clients only) WinNT/Cygwin x86 7.1 2001-03-16, Jason Tishler ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
AW: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> To the rest on the list:
> Try the above example by adding a lock between the two lines:
>
> create function nextid( varchar(32)) returns int8 as '
> update idseq set id = id + 1 where name = $1::text;
> select * from lock1;
> select id from idseq where name = $1::text for update;
> ' language 'sql';
>
> session1:
> begin work;
> lock table lock1 in access
>exclusive mode;
> session 2:
> not in txn: select nextid('one'); // this blocks
> select nextid('one');
> commit work;
>
> And stare at the results you get with and without for update :-(
> Something is definitely fishy with the visibility of SELECT here.
Without "for update" I see a tuple in session2 from before session1 began.
After both complete, the net result is correct (id is incremented by 2).
This is very interesting, unfortunately I must leave Internet access until
monday since my daughter called me home, and mail is so dead slow,
that I did not even receive my last mails yet :-(
Andreas
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] testing last sanpshot in QNX platform
Maurizio writes: > configure:6840: checking test program > configure:6849: gcc -o conftest.map conftest.c -lz -lunix -lresolv -lPW -lgen >-lBSD -lcompat -lld -ldld -llc -lIPC -lipc -lnsl -lsocket -ldl -lm -lbsd -lsfio >-lunix 1>&5 > cc warning: cc: cannot find library 'resolv' > cc warning: cc: cannot find library 'PW' [etc] This means that earlier in configure it was determined that these libraries existed (see "checking for main in -lxxx") but now it doesn't work anymore. Not sure why this could happen, given that people have used QNX previously. (At least they got past this point.) What's curious here is that it wants to name the output program "conftest.map", which looks like it detected ".map" as the executable extension (ordinarily only used for ".exe" on Windows). What's also curious is that the error message doesn't look like anything "gcc" would produce. I think this might be a case of a messed up compiler installation and/or a case of a user cheating with configure to cover up for that fact. ;-) Some more information about your compiler setup and a peek into config.log near the compiler detection tests could shed some light onto the problem. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(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 group by in conjuction with Views
This seems to work for me. I used the snapshot from 3/28 on Solaris 8 SELECT service, count(*) AS GebruikersAantal FROM tbtrouble GROUP BY service; service | gebruikersaantal ---+-- Service 1 |2 Service 3 |2 Service 4 |1 (3 rows) SELECT service, count(*) AS GebruikersAantal FROM vwtrouble GROUP BY service; service | gebruikersaantal ---+-- Service 1 |2 Service 3 |2 Service 4 |1 (3 rows) > This message is in MIME format. Since your mail reader does not understand > this format, some or all of this message may not be legible. > > > Hi there, > > I have found a small but annoying bug. I have created a view. The > SQL-statement in the view contains a GROUP BY statement. Then I compose a > SQL-statement using this view and another GROUP BY statement and a COUNT(*) > statement. The count(*) statement doesn't count the amount of grouped > record's of the view, but it count's the amount of grouped records of the > GROUP BY in the view and of the GROUP BY in the select statement. It counts > all the records grouped instead of only the records grouped from the view. > This is wrong (IMHO). When I use a temporary table instead of a view all > things work OK. IMHO views shouldn't differ from temporary tables. > > To make things a bit more clear I have add an SQL-attachment. You can run > the attachment in an empty database form psql to have a look at the problem. > > I sometimes get another <> error too while executing these > group statements: 'My bit's blew right of the end of the world'. (This is > when i am using an ODBC link to my db.) > > Mighty thanks in advance, > > Jeroen Eitjes > j.eitjes@chem.leidenuniv.nl > eitjes@walras.nl > > ---(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] User administration tool
Matthew writes: > semi related to this, I have always thought that the way postgresql > handles the deletion of users and groups to be flawed. If I create a user, > grant permissions on a table and then drop the user, permissions now exist > on that table for a user that does not exist. Unfortunately it is not possible to prevent this with anything approaching ease, in the same way that userdel on Unix can't scan all file systems for some to-be-stale files before removing users. > I see this as a possible security flaw since a new user can then be > created with the user id of the ID user and have all the permissions > that might have ever been assigned to that old user. This will be fixed in 7.2 when Oids will be used as user ids. Of course Oids can wrap, but that's another days project... -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] client notification of AbortTransaction()
Hi, I've written an extension to PGSQL that in some cases has to abort the current transaction. It calls AbortCurrentTransaction() in that case, but the problem is that the client doesn't get notified. Is there a way to detect such an abort, or do I have to make a modification the the client libs ?? Regards, Igmar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: Call for platforms
At 11:06 PM 3/28/01 -0500, Tom Lane wrote: >Mark Knox <[EMAIL PROTECTED]> writes: >> I don't think this solution would be valid on many other platforms. > >Au contraire --- the ARM is the first platform I've heard of that does >not think sizeof(ItemPointerData) is 6. Else we'd have seen this >regress test fail before. I meant I don't think *my* solution (ie packing the struct) would be valid anywhere else. It seems to be an arm-specific problem so maybe it needs an arm-specific patch? I've had to do this type of thing many times to get packages working properly in arm linux. It's a quirky platform. >> Well, this patch seems to produce attlens of 6 as desired, but it >> causes many (13) of the regression tests to fail. Do you want to see >> the regression.diffs? > >Please. See attached. regression.diffs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] testing last sanpshot in QNX platform
hi, I come back in office after a long period out for work. Yesterday I have downloaded the last snapshot. When I execute configure I have an error compiling conftest. The last version I have checked was 7.1 b3 and all works fine. Attached is the config.log file. Has someone any suggestion ? Thanks Maurizio CauciDREAMTECH di Cauci MaurizioVia Ronchetti, 2 - 21013 Gallarate (VA)www.dreamtech-it.com Config.log ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] HUGE BUG - Please fix!!!
Hi guys, I don't want to do a patch for a one character error. Yet, that's a HUGE error and it really needs to be fixed. Once you have a pg_database file which is over 1 page, it CRASHES. That's what it did on me. Yes! I have many databases or rather, I often destroy my databases to regenerate them from scratch (that's for me the easiest way to do it). Because of that, the pg_database is now two pages. The utils/misc/database.c has a function called GetRawDatabaseInfo() which reads that file "on its own". There is a HUGE bug in there, and it may not always crash a system, but it really needs to be fixed. At line #183, you have a for() loop which looks like this (since at least V6.5.0 and still present in V7.0.3): for (i = 0; i <= max; i++) All the other such loops start with an index of 1, not zero. And therefore you want the <=. In this special case loop (or are all the others special cases?!?) you need to use the following: for(i = 0; i < max; i++) Please, I know it's easier when you get a patch, but FIX IT. It's not fun to try to access your database and have the backend crash because of such a tiny bug! Thank you for all your work. Alexis Wilke Director Made to Order Software, Ltd e-mail: [EMAIL PROTECTED] Web Page: http://www.m2osw.com Company e-mail: [EMAIL PROTECTED] Phone: 020 8748 9898 +(44) 20 8748 9898 Fax:020 8748 4250 +(44) 20 8748 4250 Address:Britannia House 1-11 Glenthorne Road Hammersmith London W6 0LF United Kingdom __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/?.refer=text ---(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] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
On Tuesday 27 March 2001 15:14, Tom Lane wrote: > Forest Wilkinson <[EMAIL PROTECTED]> writes: > > session1<< create function nextid( varchar(32)) returns int8 as ' > > session1<< select * from idseq where name = $1::text for update; > > session1<< update idseq set id = id + 1 where name = $1::text; > > session1<< select id from idseq where name = $1::text; > > session1<< ' language 'sql'; > > [ doesn't work as expected in parallel transactions ] [snip] > The workaround for Forest is to make the final SELECT be a SELECT FOR > UPDATE, so that it's playing by the same rules as the earlier commands. > But I wonder whether we ought to rethink the MVCC rules so that that's > not necessary. I have no idea how we might change the rules though. > If nothing else, we should document this issue better: SELECT and SELECT > FOR UPDATE have different visibility rules, so you probably don't want > to intermix them. My, that's ugly. (But thanks for the workaround.) If I remember correctly, UPDATE establishes a lock on the affected rows, which will block another UPDATE on the same rows for the duration of the transaction. If that's true, shouldn't I be able to achieve my desired behavior by removing the initial as follows: create function nextid( varchar(32)) returns int8 as ' update idseq set id = id + 1 where name = $1::text; select id from idseq where name = $1::text; ' language 'sql'; Or, would I still have to add FOR UPDATE to that final SELECT? Forest ---(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
AW: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
> > > I doubt if it's a bug of SELECT. Well what > > > 'concurrent UPDATE then SELECT FOR UPDATE + > > > SELECT' return ? > > > > I'm going to add additional check to heapgettup and > > heap_fetch: > > > > SELECT seems to be able to return a different result > from that of preceding SELECT FOR UPDATE even after > applying your change. Only if you left this cursor position without doing an actual update (i.e. after fetch next). The select for update is only supposed to guard the current cursor position. Once you leave without modification another session can be allowed to update. This is how it is supposed to react in read committed mode. If you don't like this you need repeatable read. The example given is of questionable value, since a select for update without a cursor in read committed mode does not need to behave any different than a simple select without for update. > SELECT doesn't seem guilty but the result is far > from intuitive. It is intuitive. The bug was iirc, that you saw 2 versions of the same row in the second select statement (= 2 rows returned by second select). Vadim's patch will let you see only the newer row. > It seems impossoble for all queires inside such > a function to use a common snapshot. In read committed they are not required to ! It looks like a lot of people on the list are absolute fans of repeatable read isolation :-) Not me, I know a lot of applications where committed read, or even read uncommitted makes a lot more sense. Andreas ---(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: Solaris 7 SPARC passes tests (was Re: [HACKERS] Re: [BUGS] Tests randomly failed)
> On Tue, Mar 27, 2001 at 08:08:47PM -0500, Tom Lane wrote: > Mathijs Brands <[EMAIL PROTECTED]> writes: > > No luck :( Tests still randomly crash. (This is an Ultra 10 machine.) > > How about if you change the pg_regress script to use TCP connections? > (Look for the bit that forces unix_sockets=no for certain OSes, and > add solaris) > > regards, tom lane Someone ran into this again yesterday with Solaris x86. The unix socket problem is probably the same for both architectures, so why not change pg_regress.sh to include *solaris* as part of the same case statement that excludes QNX and BeOS for unix sockets? It is safe to say that Solaris does have this problem. The postmaster startup test could say something a bit more useful this way too, as a standard "make check" does not report which type of sockets are being used (but it does when --temp-install=""). Some folks may want that to be recorded in the output consistently. A very small patch to do both of those things is attached. Cheers, -Rick *** pg_regress.sh Wed Mar 28 02:46:50 2001 --- pg_regress.sh~ Wed Mar 28 02:46:38 2001 *** *** 156,166 # -- ! # When on QNX or BeOS, don't use Unix sockets. # -- case $host_platform in ! *-*-qnx* | *beos*) unix_sockets=no;; *) unix_sockets=yes;; --- 156,166 # -- ! # When on QNX, BeOS, or Solaris, don't use Unix sockets. # -- case $host_platform in ! *-*-qnx* | *beos* | *solaris* ) unix_sockets=no;; *) unix_sockets=yes;; *** *** 354,359 --- 354,364 if kill -0 $postmaster_pid >/dev/null 2>&1 then echo "running on port $PGPORT with pid $postmaster_pid" + if [ -n "$PGHOST" ]; then + echo "(using postmaster on Inet socket)" + else + echo "(using postmaster on Unix socket)" + fi else echo echo "$me: postmaster did not start" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] drivers for postgresql
Hi I am new to PostgreSQL, and I would like to install it on a Linux box. I wish to create and edit databases programmatically using Java. My problem is that I don't know where to get the drivers for PostgreSQL. I will be glad if you could refer me to a few sites that you know of. regards gilmour ps: please send your response to [EMAIL PROTECTED], since I will be leaving my current job at the end of this week.
[HACKERS] A few notes on timezones
Dear all,
please excuse me for posting out of the blue (I am no longer
subscribed) but I have been asked by my colleagues to send a message
since I've pretty much been hacking at this problem all day.
To summarise the issue briefly we were very confused regarding the SET
TIMEZONE command which behaved differently on Linux and Tru64 Unix
4.0F. We immediately blamed Postgres, as one normally does, and then
decided that since RC1 is out it would be better if we actually worked
out what the really issue was.
As usual a good RTFM session provided the answer. The idea of this
message is to provide a sort of "tutorial" on how it apparently
timezone changes are handled according to POSIX.1 and XPG.4.
SET TIMEZONE is dealt with in src/backend/commands/variable.c:357 and
the following short program[1]:
/* tzset-test.c */
#include
#include
#include
main(int argc, char **argv)
{
char *tzone="TZ=GMT0";
extern long timezone;
if ( argc > 1)
putenv(argv[1]);
else
putenv(tzone);
tzset();
printf("daylight=%d\ntimezone=%ld\ntzname[0]=%s\ntzname[1]=%s\n",
daylight, timezone,
(tzname[0] ? tzname[0] : "NULL"),
(tzname[1] ? tzname[1] : "NULL"));
exit(0);
}
simulates the procedure used to change the timezone in
parse_timezone(). In a few words what needs to be done is that the
environment variable TZ is set to the required value and this is
"imported" back into the program by using tzset().
Now, the simple issue we were facing was that setting the timezone to
GMT worked under Linux but not under Tru64 Unix. In particular
someone on this mailing list replied something along the lines of
"well, you need to set it to something which the OS recognises". It
turned out that the statement is true but in a different sense than
what we had expected. We were of the mistaken belief that the
timezone had to be set to something known in /etc/zoneinfo (Tru64 Unix
notation), i.e. one of:
Australia GMTGMT+7 GMT-6 GMT4 Japan Singapore
BelfastGMT+0 GMT+8 GMT-7 GMT5 Libya SystemV
Brazil GMT+1 GMT+9 GMT-8 GMT6 London Turkey
CETGMT+10 GMT-0 GMT-9 GMT7 METUCT
Canada GMT+11 GMT-1 GMT0 GMT8 Mexico US
Chile GMT+12 GMT-10 GMT1 GMT9 NZ UTC
Cuba GMT+13 GMT-11 GMT10 Greenwich NZ-CHATUniversal
Dublin GMT+2 GMT-12 GMT11 Hongkong Navajo W-SU
EETGMT+3 GMT-2 GMT12 IcelandPRCWET
Egypt GMT+4 GMT-3 GMT13 Iran Poland Zulu
FactoryGMT+5 GMT-4 GMT2 Israel ROClocaltime
GB-EireGMT+6 GMT-5 GMT3 JamaicaROKsources
It actually turns out that this is not the case. The _correct_ value,
i.e. the one mandated by the tzset(3) man page and, according to
Mr. Digital,
``
Interfaces documented on this reference page conform to industry standards
as follows:
tzset(): POSIX.1, XPG4, XPG4-UNIX
''
is in fact not "GMT" or "Iceland" but a string of the form:
``
When TZ appears in the environment and its value is not a null string, the
value has one of three formats:
:
:pathname
stdoffset[dst[offset] [,start[/time],end[/time]]]
''
where ':' means UTC, ':pathname' sends you to the zoneinfo file and
the last one is the string which should be used. In particular, where
Linux accepts GMT and reads it to be GMT0, under Tru64 Unix the
correct behaviour _requires_ the use of GMT0.
Examples of this behaviour are (local timezone EET DST, GMT+3):
[Tru64 Unix 4.0F (and 4.0G)]
./tzset-test TZ=GMT
daylight=1
timezone=-7200
tzname[0]=EET
tzname[1]=EET DST
[Debian GNU/Linux 2.2 (Kernel 2.2.18, glibc 2.1.3)]
./tzset-test TZ=GMT
daylight=0
timezone=0
tzname[0]=GMT
tzname[1]=GMT
whereas the POSIXly "correct" (the use of quotes will become apparent
later) setting of TZ=GMT0 gives the "expected" result:
[Tru64 Unix 4.0F (and 4.0G)]
./tzset-test TZ=GMT0
daylight=0
timezone=0
tzname[0]=GMT
tzname[1]=
[Debian GNU/Linux 2.2 (Kernel 2.2.18, glibc 2.1.3)]
./tzset-test TZ=GMT0
daylight=0
timezone=0
tzname[0]=GMT
tzname[1]=GMT
As you can imagine the above discrepancy, seen from within Postgres
but not tested separately, had driven us to despair for our application
which "localised" times depending on the remote user location.
Now, this might be all closed but as a matter of fact we went a little
further and discovered that, as long as you use the POSIXly defined
format you can specify the timezones to be anything you want! For
example[2]:
./tzset-test TZ=PIPPO0PLUTO-2
daylight=1
timezone=0
tzname[0]=PIPPO
tzname[1]=PLUTO
is perfectly valid. Not only, unless you specify also the "change
dates" for DST, as specified earlier, you run the risk of wrong
conversions. Any timezone used at the moment will a
[HACKERS] Error in the date field (with NULL value...).Thanks!
Subject: Importing data from Informix to PostgreSQL. Error in the date field (WITH NULL value) Hello! I'll try to explain my little problem. Well, I have this table create table mytable ( codice char(16) not null, dt_inizio date, dt_finedate, tipo_operazione char(1), causa_operazione integer ); ok! I find out that pgsql: In my example '' is the NULL value exported from Informix... ! (an ASCII file) INFORMIX PostgreSQL char(16) '' --> blank string (I think it's ok! ) char(1)'' --> blank string (I think it's ok) integer'' --> 0 (is it an error? ) date ''--> ERROR! Bad date external representation '' >> select * from mytable ; codice | dt_inizio | dt_fine | tipo_operazione | causa_operazione ABCEDEF | 2001-03-28 | | |0 XXXYYYAAA23C957Y | 2001-03-28 | | |0 clinica=# insert into mytable values ( '','03/28/2001', '' , '' , '' ); ERROR: Bad date external representation '' ^^^ PostgreSQL doesn't want '' as an input of a date with NULL value: it's necessary to use this kind of insert: >> insert into mytable values ( '','03/28/2001',null,'',''); ^^ Now there is a new line in the table: | 2001-03-28 | | |0 My question: How can I resolv my problem? I have a big data file to import where in the 2nd date field there is '' instead of null How can I "binds" PostgreSQL to consider '' as null ? Many thanks for any suggestions! CIAO! MAURIZIO *** ** Happy surfing on THE NET !! ** ** Ciao by ** ** C R I X 98 ** *** AntiSpam: rimuovere il trattino basso dall'indirizzo per scrivermi... (delete the underscore from the e-mail address to reply) ---(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] Re: Call for platforms
On Tue, 27 Mar 2001 09:57:45 -0500 (EST), Bruce Momjian alluded: > > We just fixed that yesterday. Can you grab the most recent CVS and give > it a try? Yep. We have many other MIPS (ONYX Crimson, , ONYX, Challenge, Indy w/ IRIX 6.2, 6.5, etc.), Alpha and Sparc platforms if there are some others that need testing (How about NetBSD on NeXT?). Jeff -- Jeff Duffy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [SQL] possible row locking bug in 7.0.3 & 7.1
"Mikheev, Vadim" wrote: > > > > >> I assume this is not possible in 7.1? > > > > > > > >Just looked in heapam.c - I can fix it in two hours. > > > >The question is - should we do this now? > > > >Comments? > > > > > > It's a bug; how confident are you of the fix? > > 95% -:) > > > I doubt if it's a bug of SELECT. Well what > > 'concurrent UPDATE then SELECT FOR UPDATE + > > SELECT' return ? > > I'm going to add additional check to heapgettup and > heap_fetch: > SELECT seems to be able to return a different result from that of preceding SELECT FOR UPDATE even after applying your change. SELECT doesn't seem guilty but the result is far from intuitive. It seems impossoble for all queires inside such a function to use a common snapshot. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: Re: [PORTS] pgmonitor and Solaris
On Fri, Mar 30, 2001 at 11:07:25AM +0100, Pete Forman allegedly wrote: > I've done a quick survey of the Suns available to me to see whether > sendmail updates the ps display. The summary is: > >1) Require "/usr/ucb/ps w" at least. /usr/bin/ps has no options > that I can find to display the status of sendmail. > >2) Older versions of sendmail do not update the status. 8.6 does > not update, 8.8.8 does. > >3) Solaris 2.5 and 2.5.1 have sendmail 8.6, 7 has 8.9.1, 8 has > 8.9.3. Some 2.6 have 8.6, others have 8.8.8. Presumably > patches have beed applied. > > On other OSs, AIX 4.1, 4.2, 4.3 have sendmail 8.8.4, 8.8.6, 8.8.8 or > 8.9.3. They all display the status in both SysV and BSD modes. > On AIX there is one ps command which handles both styles. > > IRIX 6.2 and 6.5.4m through 6.5.10m have sendmail 8.8.8, 8.9.1, 8.9.3. > No status is available. There do not appear to be any BSD-ish ps > options. The way /usr/ucb/ps in Solaris extracts the status for a process requires root rights and is (in my opinion) pretty gross. What it does is read the pseudo-file /proc//psinfo, which contains the real parameters supplied to the program and not the clobbered version. Regards, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: Re: [PORTS] pgmonitor and Solaris
Bruce Momjian writes: > > Tom Lane writes: > > The consequence should be: > > > > 1. check if sendmail works with /usr/bin/ps > > > > 2. a) if yes, figure out what got lost in PostgreSQL > > > > 2. b) if no, make the Solaris case in ps_status.c use the BSD > > approach > > [ I had tried to move this discussion to ports, but hackers is OK > too.] > > Well, this is very interesting. I am glad to hear you based the > current ps_status code on sendmail, which I think is the perfect > way to go. > > I seem to remember the Solaris manual page stating it doesn't > update the ps display, but I may be mixing that up with something > else. I agree /usr/bin/ps is the better option, if we can get it > working. Most people will not remember to use /usr/ucb/ps. I've done a quick survey of the Suns available to me to see whether sendmail updates the ps display. The summary is: 1) Require "/usr/ucb/ps w" at least. /usr/bin/ps has no options that I can find to display the status of sendmail. 2) Older versions of sendmail do not update the status. 8.6 does not update, 8.8.8 does. 3) Solaris 2.5 and 2.5.1 have sendmail 8.6, 7 has 8.9.1, 8 has 8.9.3. Some 2.6 have 8.6, others have 8.8.8. Presumably patches have beed applied. On other OSs, AIX 4.1, 4.2, 4.3 have sendmail 8.8.4, 8.8.6, 8.8.8 or 8.9.3. They all display the status in both SysV and BSD modes. On AIX there is one ps command which handles both styles. IRIX 6.2 and 6.5.4m through 6.5.10m have sendmail 8.8.8, 8.9.1, 8.9.3. No status is available. There do not appear to be any BSD-ish ps options. -- Pete Forman -./\.- Disclaimer: This post is originated WesternGeco -./\.- by myself and does not represent [EMAIL PROTECTED] -./\.- opinion of Schlumberger, Baker http://www.crosswinds.net/~petef -./\.- Hughes or their divisions. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] testing last sanpshot in QNX platform
hi,
Sorry if you receive this message again . I just sent it yesterday
with attached the config file but probably was too large.
I come back in office after a long period out for
work.
Yesterday I have downloaded the last
snapshot.
When I execute configure I have an error compiling
conftest.
Seems that non found some librarys but in the
previus lines (when execute the check write yes)
The last version I have checked was 7.1 b3 and all
works fine.
This is only the last few lines of the
config.log file.
-
configure:6840: checking test
programconfigure:6849: gcc -o conftest.map conftest.c -lz
-lunix -lresolv -lPW -lgen -lBSD -lcompat -lld -ldld -llc -lIPC -lipc -lnsl
-lsocket -ldl -lm -lbsd -lsfio -lunix 1>&5cc warning: cc: cannot find
library 'resolv'cc warning: cc: cannot find library 'PW'cc warning: cc:
cannot find library 'gen'cc warning: cc: cannot find library 'BSD'cc
warning: cc: cannot find library 'compat'cc warning: cc: cannot find library
'ld'cc warning: cc: cannot find library 'dld'cc warning: cc: cannot find
library 'lc'cc warning: cc: cannot find library 'IPC'cc warning: cc:
cannot find library 'ipc'cc warning: cc: cannot find library 'nsl'cc
warning: cc: cannot find library 'dl'cc warning: cc: cannot find library
'bsd'cc warning: cc: cannot find library 'sfio'cc warning: cc: cannot
find library 'resolv'cc warning: cc: cannot find library 'PW'cc warning:
cc: cannot find library 'gen'cc warning: cc: cannot find library 'BSD'cc
warning: cc: cannot find library 'compat'cc warning: cc: cannot find library
'ld'cc warning: cc: cannot find library 'dld'cc warning: cc: cannot find
library 'lc'cc warning: cc: cannot find library 'IPC'cc warning: cc:
cannot find library 'ipc'cc warning: cc: cannot find library 'nsl'cc
warning: cc: cannot find library 'dl'cc warning: cc: cannot find library
'bsd'cc warning: cc: cannot find library 'sfio'configure: failed program
was:#line 6845 "configure"#include "confdefs.h"int main() { return
0; }
-
Has someone any suggestion ?
Thanks
Maurizio CauciDREAMTECH di Cauci
MaurizioVia Ronchetti, 2 - 21013 Gallarate (VA)www.dreamtech-it.com
