Re: [HACKERS] threads stuff/UnixWare
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Larry Rosenman wrote: I agree. the only issue is how to set up our makefiles to only do the -Kpthread/-pthreads(gcc) flags on the client code, and not do it for the backend itself. I think mixing a pgport that has thread flags with a backend that does not is more risky than just compiling everything with the same thread flags. Can we get this straight: is -Kpthread a compile flag or just a link flag? If the latter then it should not be needed in building the libpgport files. If the former then adding it to LDFLAGS is the wrong thing. -Kpthread is needed for both compile and link, and the PTHREAD_CFLAGS is used for both compile and link phases. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Subtle pg_dump problem...
On Wed, 12 May 2004, Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: 2. When an object foo is called and needs to refer to another object bar, it should assume that bar exists in the same schema as foo, and NOT in the current search_path. That would be great if a C function could find out what schema it had been declared in, but I don't think it can readily do so. TODO candidate ? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] threads stuff/UnixWare
--On Thursday, May 13, 2004 10:05:22 -0400 Bruce Momjian [EMAIL PROTECTED] wrote: Basically, as things set right now in CVS, Unixware is ready to go because it thread for everything. We don't have per-template thread settings anymore because we test all of it in configure. Was a change made to link initdb et al with -Kpthread? It doesn't seem to be in AnonCVS yet. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] threads stuff/UnixWare
Larry Rosenman wrote:
-- Start of PGP signed section.
--On Thursday, May 13, 2004 10:05:22 -0400 Bruce Momjian
[EMAIL PROTECTED] wrote:
Basically, as things set right now in CVS, Unixware is ready to go
because it thread for everything. We don't have per-template thread
settings anymore because we test all of it in configure.
Was a change made to link initdb et al with -Kpthread? It doesn't seem to
be in AnonCVS yet.
Really? You are configuring with --enable-thread-safety? I just
updated your template in CVS, and it is attached. However, any old CVS
should work fine.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
if test $GCC != yes; then
then
# The -Kno_host is for a bug in the compiler. See -hackers
# discussion on 7-8/Aug/2003.
cat conftest.c __EOF__
extern char *strcpy(char *, const char *);
static void f(char *p, int n){
strcpy(p+n,);
}
void g(void){
f(0, 0);
}
__EOF__
if $CC -c -O -Kinline conftest.c conftest.err 21; then
CFLAGS=-O -Kinline
else
CFLAGS=-O -Kinline,no_host
fi
rm -f conftest.*
PTHREAD_CFLAGS=-Kpthread
fi
---(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] threads stuff/UnixWare
--On Thursday, May 13, 2004 11:44:59 -0400 Bruce Momjian [EMAIL PROTECTED] wrote: Larry Rosenman wrote: -- Start of PGP signed section. --On Thursday, May 13, 2004 10:05:22 -0400 Bruce Momjian [EMAIL PROTECTED] wrote: Basically, as things set right now in CVS, Unixware is ready to go because it thread for everything. We don't have per-template thread settings anymore because we test all of it in configure. Was a change made to link initdb et al with -Kpthread? It doesn't seem to be in AnonCVS yet. Really? You are configuring with --enable-thread-safety? I just updated your template in CVS, and it is attached. However, any old CVS should work fine. Nope, initdb is where we still die: cc -O -Kinline initdb.o exec.o -L../../../src/interfaces/libpq -lpq -L../../../src/port -L/usr/local/lib -Wl,-R/usr/local/pgsql/lib -lz -lreadline -ltermcap -lresolv -lgen -lld -lsocket -lnsl -ldl -lm -lpgport -o initdb Undefined first referenced symbol in file pthread_mutex_unlocklibpq.so pthread_getspecific libpq.so pthread_mutex_lock libpq.so pthread_key_create libpq.so pthread_oncelibpq.so pthread_setspecific libpq.so UX:ld: ERROR: Symbol referencing errors. No output written to initdb gmake[3]: *** [initdb] Error 1 gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql-server/src/bin/initdb' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/home/ler/pg-dev/pgsql-server/src/bin' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/home/ler/pg-dev/pgsql-server/src' gmake: *** [all] Error 2 $ -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] threads stuff/UnixWare
Larry Rosenman wrote: Really? You are configuring with --enable-thread-safety? I just updated your template in CVS, and it is attached. However, any old CVS should work fine. Nope, initdb is where we still die: OH! I remember now. What we have to do for this platform only is to pass the thread flags into all compile/links. I forgot that step. Let me work on that and send you a patch. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] threads stuff/UnixWare
I know, this sucks, but, I don't see any other way, other than linking *ALL* libpq-using programs (including initdb and friends) with -K pthread. How about making a libpq.so (without pthread) and a thread safe libpq_r.so ? Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Probably security hole in postgresql-7.4.1
Ken Ashcraft [EMAIL PROTECTED] writes: I work at Coverity where we use static analysis to find bugs in software. I ran a security checker over postgresql-7.4.1 and I think I found a security hole. In the code below, fld_size gets copied in from a user specified file. It is passed as the 'needed' parameter to enlargeStringInfo(). If needed is a very large positive value, the addition 'needed += str-len + 1;' could cause an overflow, making needed a negative number. I've applied a patch that fixes this issue, as well as the related one that enlargeStringInfo could go into an infinite loop. Although the path of control you identify doesn't seem very threatening (since one must already be superuser to execute COPY from a file), the same sort of problem could be triggered by sending a malformed data packet, thus opening up the problem to anyone who can get past the initial postmaster authentication check. So this is more severe than we first thought. Great. Thanks for the feedback. If it is serious, is an advisory in order? Ken ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Rough draft for Unicode-aware UPPER()/LOWER()/INITCAP()
Le jeudi 13 Mai 2004 04:42, Tom Lane a crit : I got tired of reading complaints about how upper/lower don't work with Unicode, so I went and prototyped a solution. The attached code uses the C99-standard functions mbstowcs and wcstombs to convert to and from a wchar_t[] representation that can be fed to the also-C99 functions towupper, towlower, etc. These are really good news, thanks. Jean-Michel Pour ---(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] threads stuff/UnixWare
--On Thursday, May 13, 2004 09:54:02 +0200 Zeugswetter Andreas SB SD [EMAIL PROTECTED] wrote: I know, this sucks, but, I don't see any other way, other than linking *ALL* libpq-using programs (including initdb and friends) with -K pthread. How about making a libpq.so (without pthread) and a thread safe libpq_r.so ? That's against the way this platform does things. I.E. the only library is libc. All the other system supplied libs do the right thing in the presence or absence of libthread in the image. I did get a note from my SCO contacts that they are looking into how to make it easier for stuff to be threads ready, but I don't expect that to be ready for 7.5 release. The -Kpthread on all libpq using programs is the easiest way FOR NOW. Thanks, LER Andreas -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
[HACKERS] dead index nodes...
Hi Everyone, Is there a way to find out how many dead index nodes are in a btree based index? I'm attempting to track down how much of my index is bloated because of dead nodes... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL pre-fork speedup
I ran the new Pgpool-1.2.2 and it was a bit faster on the TCP but still slower than on UNIX socket. I used the same script as before. TCP Socket (Pgpool 1.2.0) -- 2.39 sec TCP Socket (Pgpool 1.2.2) -- 0.80 sec 0.80 sec 0.79 sec UNIX Socket (Pgpool 1.2.2) --- 0.026 sec 0.027 sec 0.027 sec Direct TCP connection (no pgpool) - 0.16 sec 0.15 sec 0.16 sec PgPool on TCP is still slower than direct connection but much faster than v1.2. Any other areas that can be improved? This is strange. Using pgbench(pgbench -S -C -t 1000 -h localhost), TCP socket with pgpool 1.2.2 runs about x2 faster than direct connection. Direct connection: 60TPS With pgpool: 122TPS Here is the set up: Direct connection: pgbench --TCP--PG With pgpool: pgbench --TCP--pgpool--UNIX--PG Note: I use PostgreSQL 7.4.2. This means that pgpool forces pgbench to fallback to V2 protocol (remember that pgpool does not support V3 yet), and the start up packet flys on the wire twice at the each connection statge. This actually makes the benchmark worse, still pgpool is better than direct connection however. -- Tatsuo Ishii ---(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] Probably security hole in postgresql-7.4.1
Ken Ashcraft [EMAIL PROTECTED] writes: ... thus opening up the problem to anyone who can get past the initial postmaster authentication check. So this is more severe than we first thought. Great. Thanks for the feedback. If it is serious, is an advisory in order? No, we'll just push out the fix as part of the next update version (though that may happen a little sooner than it would have otherwise). Sensible people don't give direct database connections to untrustworthy users in the first place, since there are so many ways you can cause problems if you can issue random SQL commands ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] threads stuff/UnixWare
Larry Rosenman [EMAIL PROTECTED] writes: I did get a note from my SCO contacts that they are looking into how to make it easier for stuff to be threads ready, but I don't expect that to be ready for 7.5 release. The -Kpthread on all libpq using programs is the easiest way FOR NOW. Hmm. If there is work happening at the platform level to improve matters, then I'd definitely vote for taking the simplest solution (ie -Kpthread everywhere) for now. We can always do all that other work later if nothing gets fixed; but why invest a lot of work for what might be only a short-term problem? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] relcache refcount
Alvaro Herrera [EMAIL PROTECTED] writes: I'm stuck trying to figure out how to decrease reference counting for relcache entries at subtransaction abort. Initially I thought I could just drop them all to zero, Nope, you can't. An active query plan will surely have open relations. Incidentally, I assume that LWLocks are not going to be needed across subtransaction boundaries -- I release them all on abort, just as it's done on main transaction abort. Same for catcache entries. Does anyone think this is incorrect? Sounds like a very unsafe assumption to me. The reason we can get away with force-to-zero logic for these things now is that we know we are reverting to an idle condition. The general solution would require reverting to the state prevailing at subtrans entry. If you want to avoid implementing the general solution for any particular backend module, you'd better be able to prove that it will be in an idle state at every subtrans entry. It's barely possible that that's true for LWLocks but I've got real serious doubts about catcache. As an example: mightn't the call handler for a procedural language hold onto a reference to the proc's pg_proc row throughout execution? Even if it chances not to do that today, somebody could easily want to do it tomorrow, so I think an assumption that it's not needed would be too fragile. BTW, what are your plans for state saving/reversion for the lock manager and buffer manager? The lock state, in particular, makes these other problems look trivial by comparison. Glad to see you are starting to realize why nested transactions haven't been done already ;-) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] threads stuff/UnixWare
--On Thursday, May 13, 2004 09:18:21 -0400 Tom Lane [EMAIL PROTECTED] wrote: Larry Rosenman [EMAIL PROTECTED] writes: I did get a note from my SCO contacts that they are looking into how to make it easier for stuff to be threads ready, but I don't expect that to be ready for 7.5 release. The -Kpthread on all libpq using programs is the easiest way FOR NOW. Hmm. If there is work happening at the platform level to improve matters, then I'd definitely vote for taking the simplest solution (ie -Kpthread everywhere) for now. We can always do all that other work later if nothing gets fixed; but why invest a lot of work for what might be only a short-term problem? I agree. the only issue is how to set up our makefiles to only do the -Kpthread/-pthreads(gcc) flags on the client code, and not do it for the backend itself. regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] threads stuff/UnixWare
Larry Rosenman wrote: -- Start of PGP signed section. --On Thursday, May 13, 2004 09:18:21 -0400 Tom Lane [EMAIL PROTECTED] wrote: Larry Rosenman [EMAIL PROTECTED] writes: I did get a note from my SCO contacts that they are looking into how to make it easier for stuff to be threads ready, but I don't expect that to be ready for 7.5 release. The -Kpthread on all libpq using programs is the easiest way FOR NOW. Hmm. If there is work happening at the platform level to improve matters, then I'd definitely vote for taking the simplest solution (ie -Kpthread everywhere) for now. We can always do all that other work later if nothing gets fixed; but why invest a lot of work for what might be only a short-term problem? [ added blank line here ] I agree. the only issue is how to set up our makefiles to only do the -Kpthread/-pthreads(gcc) flags on the client code, and not do it for the backend itself. I think mixing a pgport that has thread flags with a backend that does not is more risky than just compiling everything with the same thread flags. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] threads stuff/UnixWare
Bruce Momjian [EMAIL PROTECTED] writes: Larry Rosenman wrote: I agree. the only issue is how to set up our makefiles to only do the -Kpthread/-pthreads(gcc) flags on the client code, and not do it for the backend itself. I think mixing a pgport that has thread flags with a backend that does not is more risky than just compiling everything with the same thread flags. Can we get this straight: is -Kpthread a compile flag or just a link flag? If the latter then it should not be needed in building the libpgport files. If the former then adding it to LDFLAGS is the wrong thing. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] pg_begintypend
Hi all, loading a function written in C I get this error: #create function email_in( cstring ) returns email AS 'emaildt.so', 'email_in' LANGUAGE C STRICT; NOTICE: type email is not yet defined DETAIL: Creating a shell type definition. ERROR: could not load library emaildt.so: emaildt.so: undefined symbol: pg_begintypsend I'm compiling my files in this way: gcc -c -g -fPIC -o email.o -I /usr/include/pgsql/server email.c gcc -g -shared -fPIC -o emaildt.so email.o which library I have to link in order to have the symbol pg_begintypsend ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] negative pid?
I see this code in pg_ctl: PID=`sed -n 1p $PIDFILE` if [ $PID -lt 0 ];then PID=`expr 0 - $PID` Wnen it is possible (and why) to have a negative number in postmaster.pid? Thanks, Mike ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_autovacuum Win32 service patch #2
Dave Page wrote: Any comments/criticisms/gasps of horror at all the win32 code? :-) Sorry for not jumping in sooner but I have been offline for several days. Anyway, not having looked at this at all, how will this be effected when pg_autovacuum is integrated into the backend. I assume that the postmaster can be run as a win32 service, and if it launches and kills pg_autovacuum automatically, do you need to do anything for pg_autovacuum? Matthew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposed Query Planner TODO items
On 9 Feb, Tom Lane wrote: [EMAIL PROTECTED] writes: I'll see what I can do about the explain and explain analyze results. I remember in the past that someone said it would be most interesting to execute the latter while the test while running, as opposed to before or after a test. Should I do that here too? If possible, but I'd settle for a standalone result, so long as it's executed against the correct database contents (including pg_statistic settings). Ok, I've found that the kit does capture explain results and I've added a Query Plans links under the query time charts on each of the pages. Um, but I did notice a couple of problems. It looks liks one of the 22 queries is missing and they're not labeled. I'll see about getting that fixed. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] negative pid?
Sorry, I should have checked the source first. Just found this in miscinit.c : * By convention, the owning process' PID is negated if it is a standalone * backend rather than a postmaster. This is just for informational purposes. -Original Message- I see this code in pg_ctl: PID=`sed -n 1p $PIDFILE` if [ $PID -lt 0 ];then PID=`expr 0 - $PID` Wnen it is possible (and why) to have a negative number in postmaster.pid? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Bogus permissions display in 7.4
Deepak Bhole of Red Hat asked me about the following situation:
regression=# create table test (f1 int);
CREATE TABLE
regression=# revoke insert,update,delete,references on test from postgres;
REVOKE
regression=# \z test
Access privileges for database regression
Schema | Name | Type | Access privileges
+--+---+
public | test | table | {postgres=*r***R**t*/postgres}
(1 row)
It seems unreasonably hard to interpret what those stars mean, don't you
think? Certainly you can't tell which star is which without hardwired
knowledge about the order in which the bits will be printed.
The problem here is that we allow the owner to revoke his own ordinary
privileges but not his grant options; so we end up with a permissions
configuration that was not considered in the design of the external
representation for ACL lists. (Per spec it is not possible to hold a
grant option for a privilege without holding the privilege itself too,
and I expect that this printout format was designed assuming that
restriction.)
I think the printout format is fine and the silent non-removal of grant
options was a bad idea, particularly since it doesn't seem to be saving
any code (GRANT/REVOKE check ownerness anyway). I propose that we take
out the special cases in merge_acl_with_grant that prohibit revoking an
owner's grant options, and instead adjust the grant statement code to
act as if those options are always present. Instead of the existing
if (stmt-is_grant
!pg_class_ownercheck(relOid, GetUserId())
pg_class_aclcheck(relOid, GetUserId(),
ACL_GRANT_OPTION_FOR(privileges)) != ACLCHECK_OK)
aclcheck_error(ACLCHECK_NO_PRIV, ACL_KIND_CLASS, relvar-relname);
it'd be something like
if (pg_class_ownercheck(relOid, GetUserId())
{ okay, assume we have all grant options }
else if (pg_class_aclcheck(relOid, GetUserId(), ...) != ACLCHECK_OK)
{ error }
else
{ determine actual grant options for non-owner }
Thus the effective behavior of grant/revoke would remain the same as
before, but we wouldn't have the contrary-to-spec cases in the visible
contents of the ACL list.
I am in the middle of fixing GRANT/REVOKE to conform to spec as
discussed in the bug #1150 thread, and will make this change too
if I don't hear any objections.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] database errors
Our customer has problems with Pg 7.3.2 on Solaris. There are numerous errors in the app. server log and in the database log, including these: LOG: open of /mnt_c1t2d0s0/some-path/postgresql/pg_xlog/0001 (log file 0, segment 1) failed: No such file or directory LOG: invalid primary checkpoint record LOG: open of /mnt_c1t2d0s0/some-path/postgresql/pg_xlog/0001 (log file 0, segment 1) failed: No such file or directory LOG: invalid secondary checkpoint record PANIC: unable to locate a valid checkpoint record LOG: startup process (pid 16527) was terminated by signal 6 LOG: aborting startup due to startup process failure ... ERROR: Cannot insert a duplicate key into unique index cr_pk PANIC: RecordTransactionAbort: xact 55143 already committed LOG: server process (pid 22185) was terminated by signal 6 LOG: terminating any other active server processes WARNING: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query. LOG: all server processes terminated; reinitializing shared memory and semaphores LOG: database system was interrupted at 2004-05-10 10:51:01 CDT LOG: checkpoint record is at 0/30005E0 LOG: redo record is at 0/30005E0; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 53340; next oid: 57982 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/3000620 LOG: ReadRecord: record with zero length at 0/3000930 LOG: redo done at 0/3000908 WARNING: XLogFlush: request 0/A970F68 is not satisfied --- flushed only to 0/3000930 WARNING: XLogFlush: request 0/A970FA8 is not satisfied --- flushed only to 0/3000930 WARNING: XLogFlush: request 0/A970E00 is not satisfied --- flushed only to 0/3000930 WARNING: XLogFlush: request 0/A970E40 is not satisfied --- flushed only to 0/3000930 FATAL: The database system is starting up ... -- We've had Cannot insert a duplicate key into unique index in the past. We ran pg_resetxlog and reloaded the database - this helped. I wonder if message open of /mnt_c1t2d0s0/... (log file 0, segment 1) failed: No such file or directory may indicate some kind of NFS problem. Anything else I need to look at? Thanks in advance, Mike. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Bogus permissions display in 7.4
Tom Lane wrote: I think the printout format is fine and the silent non-removal of grant options was a bad idea, particularly since it doesn't seem to be saving any code (GRANT/REVOKE check ownerness anyway). I propose that we take out the special cases in merge_acl_with_grant that prohibit revoking an owner's grant options, and instead adjust the grant statement code to act as if those options are always present. Sounds good. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
Jan Wieck wrote:
This is the reason why the setting has to be at least per database and
cannot be changed after DB creation.
I think there's overwhelming consensus that db creation time is the
latest you can specify the canonical name setting for it. There's
probably a good case to be made for it to be when you initdb, so that it
is set as expected for shared tables.
(Is anyone actually doing anything on this?)
cheers
andrew
What has to change is the behaviour of the name type operators, which
will automatically change the uniqueness behaviour of the catalog
indexes.
In an UPPERCASE database
foo/Foo/FOO false = FOO true
In a lowercase database
foo/Foo/FOO false = foo true
In both of them
foo/Foo/FOO false Foo true
foo/Foo/FOO false = foo/Foo/FOO false
Jan
Dennis Bjorklund wrote:
On Sun, 25 Apr 2004, Andrew Dunstan wrote:
Why do you want two names? Just keep the original casing, and a
boolean
saying if it's quoted or not.
Sorry - brain malfunction - yes, original casing plus boolean would
work. In effect you could derive the canonical form from those two.
Say that you have this in the table with the identifier
name quoted
--
Foo False
Now you want to add the name FOO
FOO True
should you be allowed or is it a clash with the above?
What if you also add foo
foo True
One of these two should be forbidden. And what about a quoted FOO:
FOO False
FOO True
This case says it is not enough with an expressional unique index on
(upper(name), quoted). It would be easier to enforce uniqueness if one
store both the converted name and the original name:
name orig_name
-
FOO NULL -- quoted one
FOO FOO -- unquoted one
and the first case
FOO Foo -- unquoted
FOO NULL -- clashes with the first, good
foo NULL -- no clash, works fine
With this one can always use upper case translation as per sql spec and
psql can optionally show all unquoted identifiers as upper, lower or
mixed
case.
Then we also have the INFORMATION_SCHEMA that should show the names in
UPPER CASE when not quoted, this since applications that are written for
the standard might depend on that (probably no application do today
but it
would be a valid case of use of the information schema).
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] database errors
Michael Brusser [EMAIL PROTECTED] writes: I wonder if message open of /mnt_c1t2d0s0/... (log file 0, segment 1) failed: No such file or directory may indicate some kind of NFS problem. Running a database over NFS is widely considered a horrid idea --- the NFS protocol is simply too prone to data loss. I think you may have a sterling example here of why not to do it :-( The messages you quote certainly read like a badly corrupted database to me. In the case of a local filesystem I'd be counseling you to start running memory and disk diagnostics. That may still be appropriate here, but you had better also reconsider the decision to use NFS. If you're absolutely set on using NFS, one possibly useful tip is to make sure it's a hard mount not a soft mount. If your systems support NFS-over-TCP instead of UDP, that might be worth trying too. Also I would strongly advise an update to PG 7.3.6. 7.3.2 has serious known bugs. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Dead horse?
On Thu, 5 Feb 2004, Rod Taylor wrote: Don't know. But apparently different users will have different demands From a database. Of course, but I would argue that my claim that PostgreSQL is reliable is backed up by the lack of people posting messages like 'we had a powercut and now my DB is hosed'. One thing we could use (and I have no idea how to do it) is a This hardware is not appropriate for a database test kit. Something to detect lying disks, battery backed write cache that isn't so battery backed, etc. but I'm not sure you can test that without power off tests... so, it would have to be a test that kinda started up then told you to pull the plug on the box. Even a kernel panic wouldn't detect it because the drive would still be powered up. Or, you could have a test that checked what kind of drive it was (IDE versus SCSI) and maybe had a table of drives that are known to lie, possibly even by version, should drives of the same model stop lying half way through production due to fixes in their firmware. I'd guess it the table would still have to be built the old fashioned way, by doing power off tests. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bogus permissions display in 7.4
Peter Eisentraut [EMAIL PROTECTED] writes:
Tom Lane wrote:
I think the printout format is fine and the silent non-removal of
grant options was a bad idea, particularly since it doesn't seem to
be saving any code (GRANT/REVOKE check ownerness anyway). I propose
that we take out the special cases in merge_acl_with_grant that
prohibit revoking an owner's grant options, and instead adjust the
grant statement code to act as if those options are always present.
Sounds good.
If you like that, I have a further suggestion, which is to not include
the owner's grant options in the default ACL, either. This would not
affect the behavior given the above changes; what it would do is reduce
clutter in the ACL display. Right now, if user miriam does
create table mytable(f int);
grant select on mytable to public;
\z mytable
she'll see
Schema | Name | Type |Access privileges
+-+---+--
public | mytable | table | {miriam=a*r*w*d*R*x*t*/miriam,=r/miriam}
Changing the default ACL would take this down to
public | mytable | table | {miriam=arwdRxt/miriam,=r/miriam}
which seems usefully more readable to me. Comments?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] database errors
It looks that No such file or directory followed by the abort signal resulted from manually removing logs. pg_resetxlog took care of this, but other problems persisted. I got a copy of the database and installed it on the local partition. It does seem badly corrupted, these are some hard errors. pg_dump fails and dumps the core: pg_dump: ERROR: XLogFlush: request 0/A971020 is not satisfied --- flushed only to 0/550 ... lost synchronization with server, resetting connection looking at the core file: (dbx) where 15 =[1] _libc_kill(0x0, 0x6, 0x0, 0x, 0x2eaf00, 0xff135888), at 0xff19f938 [2] abort(0xff1bc004, 0xff1c3a4c, 0x0, 0x7efefeff, 0x21c08, 0x2404c4), at 0xff13596c [3] elog(0x14, 0x267818, 0x0, 0xa971020, 0x0, 0x5006260), at 0x2407dc [4] XLogFlush(0xffbee908, 0xffbee908, 0x827e0, 0x0, 0x0, 0x0), at 0x78530 [5] BufferSync(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0x18df2c [6] FlushBufferPool(0x2, 0x1e554, 0x0, 0x3, 0x0, 0xffbeea79), at 0x18e5c4 [7] CreateCheckPoint(0x0, 0x0, 0x82c00, 0xff1bc004, 0x2212c, 0x83534), at 0x7d93c [8] BootstrapMain(0x5, 0xffbeec50, 0x10, 0xffbeec50, 0xffbeebc8, 0xffbeebc8), at 0x836bc [9] SSDataBase(0x3, 0x40a24a8a, 0x2e3800, 0x4, 0x2212c, 0x16f504), at 0x172590 [10] ServerLoop(0x5091, 0x2e398c, 0x2e3800, 0xff1c2940, 0xff1bc004, 0xff1c2940), at 0x16f3a0 [11] PostmasterMain(0x1, 0x323ad0, 0x2af000, 0x0, 0x6572, 0x6572), at 0x16ef88 [12] main(0x1, 0xffbef68c, 0xffbef694, 0x2eaf08, 0x0, 0x0), at 0x12864c == (I don't have the debug build at the moment to get more details) this query fails: LOG: query: select count (1) from note_links_aux; ERROR: XLogFlush: request 0/A971020 is not satisfied --- flushed only to 0/5006260 drop table fails: drop table note_links_aux; ERROR: getObjectDescription: Rule 17019 does not exist Are there any pointers as to why this could happen, aside of potential memory and disk problems? As for NFS... I know how strong the Postgresql community is advising against it, but we have to face it: our customers ARE running on NFS and they WILL be running on NFS. Is there such a thing as better and worse NFS versions? (I made a note of what was said about hard mount vs. soft mount, etc) Tom, you recommended upgrade from 7.3.2 to 7.3.6 Out next release is using v 7.3.4. (maybe it's not too late to upgrade) Would v. 7.3.6 provide more protection against problems like this? Thank you, Mike -Original Message- ... ... The messages you quote certainly read like a badly corrupted database to me. In the case of a local filesystem I'd be counseling you to start running memory and disk diagnostics. That may still be appropriate here, but you had better also reconsider the decision to use NFS. If you're absolutely set on using NFS, one possibly useful tip is to make sure it's a hard mount not a soft mount. If your systems support NFS-over-TCP instead of UDP, that might be worth trying too. Also I would strongly advise an update to PG 7.3.6. 7.3.2 has serious known bugs. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_begintypend
Gaetano Mendola [EMAIL PROTECTED] writes: which library I have to link in order to have the symbol pg_begintypsend ? I think you want pq_begintypsend. Consider paying more attention to unknown-function warnings from your compiler. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] database errors
Michael Brusser [EMAIL PROTECTED] writes: It looks that No such file or directory followed by the abort signal resulted from manually removing logs. pg_resetxlog took care of this, but other problems persisted. pg_dump: ERROR: XLogFlush: request 0/A971020 is not satisfied --- flushed only to 0/550 ... lost synchronization with server, resetting connection Okay, you have a page with an LSN of A971020 which is past end of XLOG (550). You may have created this problem for yourself by doing pg_resetxlog with poorly chosen parameters. You could try redoing it with an XLOG start address larger than that (I'd suggest quite a bit larger, since there's no reason to believe that this is the latest-modified page in the whole DB). Theory B is that this particular page is corrupted and the LSN is just trash. But that seems less likely, since 7.3.4 has checks that test the other page header fields fairly well. Usually all the header fields are garbage if any are. drop table fails: drop table note_links_aux; ERROR: getObjectDescription: Rule 17019 does not exist This looks like plain old corruption ... Out next release is using v 7.3.4. (maybe it's not too late to upgrade) Would v. 7.3.6 provide more protection against problems like this? Read the release notes. But I can't think of any reason to take the time to update and not go all the way to the latest dot-release in your branch. It's not going to be any harder, and it will get you more bug fixes. 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] Proposed Query Planner TODO items
Mark, Ok, I've found that the kit does capture explain results and I've added a Query Plans links under the query time charts on each of the pages. Um, but I did notice a couple of problems. It looks liks one of the 22 queries is missing and they're not labeled. I'll see about getting that fixed. If #19 is missing it's because Oleg I could not get it to complete. That was also the query which we are most interested in testing. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bogus permissions display in 7.4
Schema | Name | Type |Access privileges
+-+---+--
public | mytable | table | {miriam=a*r*w*d*R*x*t*/miriam,=r/miriam}
Changing the default ACL would take this down to
public | mytable | table | {miriam=arwdRxt/miriam,=r/miriam}
which seems usefully more readable to me. Comments?
Guess this means I have to tweak my ACL parser in phpPgAdmin. If you
could do something to make that less of a NIGHTMARE, i'd be all ears :P
ie.
1. Make it easy to convert an array to a rowset
2. Fabien's accessor functions? Would they help?
3. At least the quoting has been fixed in 7.4 though
4. Maybe even a function that takes an aclitem and returnssomething...
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Bogus permissions display in 7.4
I wrote: If you like that, I have a further suggestion, which is to not include the owner's grant options in the default ACL, either. I've been thinking more about this, and realizing that there are more implications than I first thought. Specifically, we have to consider how any hacking we do here will affect recursive_revoke(). The different options I've suggested would have different side effects, and I'm having a hard time deciding which is better. In the existing 7.4 code, you can revoke the owner's privileges but not his grant options. This confuses the ACL display code (and possibly clients that try to interpret ACL displays), and it means that you can't use recursive_revoke to get rid of everyone but the owner's privileges. In my proposal of earlier today, you can revoke the owner's grant options, which will force recursive revocation of everyone else's privileges (since these all flow ultimately from the owner's grants). This implies that it is not possible for the owner to have less privileges than anyone else. Perhaps that is not bad, but up to now it was possible to configure a table that way. Another problem is that because GRANT still acts as though the owner has grant options, he can then go and re-grant privs to other people (or the superuser can do it). Now you have an ACL in which privileges appear to flow from the owner despite having no grant options. That will confuse matters --- for example, if the owner does REVOKE GRANT OPTION FROM himself a second time, this time it will *not* recursively kill everyone else's privileges, because recursive_revoke will not see any need to recurse. If we remove the owner's grant options from the default ACL then revoking the owner's grant options won't ever recurse (unless he first grants them to himself explicitly and then revokes them). Perhaps that's good? I'm not sure. We could patch around some of these problems if recursive_revoke knew who the owner was (and could thereby take into account the implicit owner grant options that I still think we want to have). But it does not know that, and some of its callers do not either. Messier and messier. I'm beginning to see why the SQL spec wants to introduce a _SYSTEM authid to be the original source of rights. It could be that the only good solution is to introduce knowledge of the owner directly into the ACL representation. You could see the spec's approach as doing that: he who gets his rights directly from _SYSTEM is the owner. Another perhaps more compact way is to make a separate ACL_IDTYPE to represent owner (we are using only 3 of the 4 possible bitpatterns so this would be easy). Comments? 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] [pgsql-hackers-win32] Sync vs. fsync during checkpoint
Greg Stark wrote: Jan Wieck [EMAIL PROTECTED] writes: The whole sync() vs. fsync() discussion is in my opinion nonsense at this point. Without the ability to limit the amount of files to a reasonable number, by employing tablespaces in the form of larger container files, the risk of forcing excessive head movement is simply too high. I don't think there was any suggestion of conflating tablespaces with implementing a filesystem in postgres. Tablespaces are just a database entity that database stored objects like tables and indexes are associated to. They group database stored objects and control the storage method and location. The existing storage mechanism, namely a directory with a file for each database object, is perfectly adequate and doesn't have to be replaced to implement tablespaces. All that's needed is that the location of the directory be associated with the tablespace of the object rather than be a global constant. Implementing an Oracle-style filesystem is just one more temptation to reimplement OS services in the database. Personally I think it's an awful idea. But even if postgres did it as an option, it wouldn't necessarily have anything to do with tablespaces. Doing this is not just what you call it. In a system with let's say 500 active backends on a database with let's say 1000 things that are represented as a file, you'll need half a million virtual file descriptors. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PITR Dead horse?
scott.marlowe [EMAIL PROTECTED] writes: but I'm not sure you can test that without power off tests... Well the approach that's been taken manually on the list is to look at the timing results and conclude they're just physically impossible. Doing this automatically could be interesting. If the tool were given a partition to act on directly it would be able to intentionally write to blocks in reverse order doing an fsync between each block and testing whether the bandwidth is low enough to conclude a full rotation between each write had been completed. Doing the same on the filesystem would be less reliable but might also be an interesting test since the OS might make fsync lie directly, or might have some additional intelligence in the filesystem that forces the drive to sync to the platters before fsync returns. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
