Re: [HACKERS] Permissions within a function
Thomas Hallgren wrote: Is there a way to bypass security checks that retains the SQL parser? I'd like my C-code to do something like: impersonate pgadmin SELECT image from class_table revert to self You can use GetUserId() and SetUserId() to flip the current user identity around as you like. For such a simple query, however, it might seem better to bypass SPI altogether and do a straight table lookup through lower-level functions. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] double error msg [ 8.0 rc1 ]
ERROR: column b does not exist ERROR: column b does not exist 2 ERROR msg's. Cause I didn't seen a previous discussion about this, I guess that this could be a 'particularly only me' problem. The point cames more strange if I grant that on previous releases (7.4.x) the error msg didn't came to me twice a time. A direction for this one would be apreciated, a where to look at. Sure you're not running psql output and postgres output on the same terminal? Chris ---(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] Permissions within a function
Tom Lane wrote: AFAICS you are choosing to do things in the hardest possible way, on the basis of completely unfounded suppositions about performance gains. I recommend the KISS principle. Leave the jar files as jars and let the Java runtime system manage them. If that was an option, believe me, I would. The current implementation was not designed and implemented due to my lack of understanding of the loader mechanisms already present in the runtime. The Java runtime system just does'nt provide a ClassLoader that can be made to follow the semantics stipulated by the SQL 2003 Java mapping. That was the major reason. I have a well functioning solution. The only lacking part is how to prevent arbitrary user access to the underlying table. I'd really like your advice on how to do that. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Permissions within a function
Peter Eisentraut wrote: Thomas Hallgren wrote: Is there a way to bypass security checks that retains the SQL parser? I'd like my C-code to do something like: impersonate pgadmin SELECT image from class_table revert to self You can use GetUserId() and SetUserId() to flip the current user identity around as you like. For such a simple query, however, it might seem better to bypass SPI altogether and do a straight table lookup through lower-level functions. Brilliant! I had no idea it was that simple. SetUserId seems to be extremely lightweight and just what I need. By using it, I can let my ClassLoader execute with other restrictions than the function caller (bypassing SPI is not so good for me since the loader is fairly complex and will access more than one table). Thanks for the advice, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for port reports
Prototype is #include signal.h int sigwait (sigset_t *set); but fe_secure.c calls sigwait(sigpipe_sigset, signo); so there's effectively one argument too much! reards On Fri, 17 Dec 2004, Tom Lane wrote: Date: Fri, 17 Dec 2004 12:49:59 -0500 From: Tom Lane [EMAIL PROTECTED] To: Bruce Momjian [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Call for port reports Bruce Momjian [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: UX:acomp: ERREUR: fe-secure.c, ligne 1316 : prototype mismatch: 2 args passed, 1 expected: sigwait() What is your prototype for sigwait()? Whatever it is, it doesn't agree with the Single Unix Spec: http://www.opengroup.org/onlinepubs/007908799/xsh/sigwait.html On HPUX 10.20 I see #include pthread.h int sigwait(sigset_t *set); and indeed --enable-thread-safety doesn't work on this platform, although we fail the thread safety configure test so there are other problems beside sigwait(). My feeling about this is too bad, we are not supporting threading on platforms whose pthread.h doesn't follow SUS. regards, tom lane -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Dump from cygwin directly to windows
Mike G [EMAIL PROTECTED] writes: It looks like it started off as a permissions problem. I added the users to the database before trying again and this time it worked fine. I have attached the log from the original attempt if you wish to have a look. As best I can tell, you ran the restore script as a non-superuser, which probably wasn't a good thing to do. 8.0 pg_dump generates scripts that should cope with this situation a bit more reasonably, but it would still end up as a do-over in most cases because none of the object ownerships would come out right. regards, tom lane ---(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] Permissions within a function
Thomas Hallgren [EMAIL PROTECTED] writes: The Java runtime system just does'nt provide a ClassLoader that can be made to follow the semantics stipulated by the SQL 2003 Java mapping. [ raised eyebrow... ] Can the spec really be that broken? They don't write these things in a total vacuum. Besides, I'd think Sun would get after them for specifying nonstandard semantics for ClassLoader. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] double error msg [ 8.0 rc1 ]
G u i d o B a r o s i o [EMAIL PROTECTED] writes: guido=# insert into test1 (b) values (b); ERROR: column b does not exist ERROR: column b does not exist 2 ERROR msg's. The postmaster's stderr is pointed at your terminal, so you're getting the postmaster log output in addition to what psql prints. regards, tom lane ---(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] Permissions within a function
Thomas Hallgren [EMAIL PROTECTED] writes: Peter Eisentraut wrote: You can use GetUserId() and SetUserId() to flip the current user identity around as you like. For such a simple query, however, it might seem better to bypass SPI altogether and do a straight table lookup through lower-level functions. Brilliant! I had no idea it was that simple. Well, it's not. Exactly what are you going to flip it *to*? You can't hardwire a particular userid and expect to have a robust solution. I'd recommend the lower-level approach myself. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Permissions within a function
Tom Lane wrote: Thomas Hallgren [EMAIL PROTECTED] writes: The Java runtime system just does'nt provide a ClassLoader that can be made to follow the semantics stipulated by the SQL 2003 Java mapping. [ raised eyebrow... ] Can the spec really be that broken? They don't write these things in a total vacuum. Besides, I'd think Sun would get after them for specifying nonstandard semantics for ClassLoader. You miss the point. There is a standard that ClassLoaders must follow but that does not mean that the Java runtime comes will all possible implementations of that standard. My ClassLoader follows both the Java standard and the SQL spec. So neither the SQL spec. nor my implementation of it is not broken. Regards, Thomas Hallgren ---(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] Permissions within a function
Tom Lane wrote: Well, it's not. Exactly what are you going to flip it *to*? You can't hardwire a particular userid and expect to have a robust solution. I'd recommend the lower-level approach myself. How about flipping to the owner of the table, (or perhaps schema since all pljava specific stuff resides in the sqlj schema) that I want to access? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [Testperf-general] BufferSync and bgwriter
On Thu, 2004-12-16 at 17:54, Richard Huxton wrote: Josh Berkus wrote: Clearly, OSDL-DBT2 is not a real world test! That is its benefit, since it is heavily instrumented and we are able to re-run it many times without different parameter settings. The application is well known and doesn't suffer that badly from factors that would allow certain effects to be swamped. If it had too much randomness or variation, it would be difficult to interpret. I don't think you followed me. The issue is that for parameters designed to smooth out spikes like bgwriter and vacuum delay, it helps to have really bad spikes to begin with. There's a possibility that the parameters (and calculations) that work well for for a steady-state OLTP application are actually bad for an application with much more erratic usage, just as a high sort_mem is good for DSS and bad for OLTP. I'm a little concerned that in an erratic, or even just a changing environment there isn't going to be any set of values that are correct. I think this expresses my own thoughts most clearly, however: There have been many good ideas expressed on this thread, though none of them, including my own, are IMHO suitable for inclusion in 8.0, given the stage of the release process we are now at. *** Please give your support now to the addition of Neil's recent bgwriter patch to the 8.0 release. It simplifies tuning, is proven to remove a clear performance blockage, yet does so without changing the underlying algorithm used by the bgwriter - so there is no case to answer along the lines that this might not apply in some situations. Neil's bgwriter does the same thing, just avoids holding a critical lock for longer than it needs to. *** I will happily discuss further ideas for 8.1 at a later stage. I'll be around tomorrow for further discussion and better replies to different individual points. Please excuse my slow answers during this debate. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Port report: NetBSD 2.0 mac68k
Rémi Zara wrote: Le 16 d?c. 04, ? 22:48, Bruce Momjian a ?crit : I am confused by the threading failure. I don't see any free() call in thread_test.c. Would you go to the tools/thread directory and run the program manually and use a debugger to see the failure line? Is there some threading flag NetBSD requires for compiles or linking? Ok. I must have made an error reporting the output of the thread safety test. Here is the output, for ./configure --enable-thread-safety: configure:18831: ./conftest Your errno is thread-safe. Your system uses strerror() which is not thread-safe. ** Your system uses getpwuid() which is not thread-safe. ** Your system has getaddrinfo(); it does not need gethostbyname() or gethostbyname_r(). ** YOUR PLATFORM IS NOT THREAD-SAFE. ** OK, that is the right output, but I thought NetBSD 2.0 had native threading. -- 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] Port report: NetBSD 2.0 mac68k
OK, I modified the m64k spinlock patch to more cleanly merge into our
code, attached. Applied.
---
Rémi Zara wrote:
Hi,
Here is a port report for NetBSD 2.0 mac68k, with sources of
postgresql8.0.0rc1.
Here is the configure line used :
./configure --prefix=/data/postgresql/pgsql-8.0.0rc1 --with-openssl
--with-python --with-perl --with-tcl --with-krb5 --with-pam
But some tweaking was necessary to make it work:
* krb5.h is in /usr/include/krb5 on netbsd (set via CPPFLAGS)
* krb5_encrypt is to be found in -lkrb5 -ldes -lasn1 -lroken -lcrypto
--enable-thread-safety does not work because the thread safety test
fails (src/tools/thread/thread_test)
configure:18831: ./conftest
conftest in free(): error: freelist is destroyed.
[1] Abort trap (core dumped) ./conftest${ac_e...
Then the tas code in src/backend/storage/lmgr/s_lock.c cannot be
compiled and linked on this system without modification:
the '_' in front of the tas symbol should be removes, and '%' added in
front of register names. I've attached a diff that makes these
modifications only for NetBSD mac68k ELF.
With these modifications, make and make install are OK !
template1=# SELECT version();
version
-
PostgreSQL 8.0.0rc1 on m68k-unknown-netbsdelf2.0, compiled by GCC gcc
(GCC) 3.3.3 (NetBSD nb3 20040520)
(1 row)
in make check, two tests fail: float8 and misc.
I've attached the regression.diffs file.
Regards,
R?mi Zara
--
R?mi Zara
http://www.remi-zara.net/
[ Attachment, skipping... ]
[ Attachment, skipping... ]
--
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
Index: src/backend/storage/lmgr/s_lock.c
===
RCS file: /cvsroot/pgsql/src/backend/storage/lmgr/s_lock.c,v
retrieving revision 1.32
diff -c -c -r1.32 s_lock.c
*** src/backend/storage/lmgr/s_lock.c 30 Aug 2004 23:47:20 - 1.32
--- src/backend/storage/lmgr/s_lock.c 18 Dec 2004 20:19:35 -
***
*** 136,147
#if defined(__m68k__)
static void
! tas_dummy() /* really means: extern
int tas(slock_t
!* **lock); */
{
__asm__ __volatile__(
!
\
.global _tas\n\
_tas: \n\
movel sp@(0x4),a0 \n\
--- 136,161
#if defined(__m68k__)
+ /* really means: extern int tas(slock_t* **lock); */
static void
! tas_dummy()
{
__asm__ __volatile__(
! #if defined(__NetBSD__) defined(__ELF__)
! /* no underscore for label and % for registers */
!
\
! .global tas \n\
! tas: \n\
! movel %sp@(0x4),%a0 \n\
! tas %a0@\n\
! beq _success\n\
! moveq #-128,%d0 \n\
! rts \n\
! _success: \n\
! moveq #0,%d0 \n\
! rts \n
! #else
!
\
.global _tas\n\
_tas: \n\
movel sp@(0x4),a0 \n\
***
*** 151,158
rts \n\
_success: \n\
moveq #0,d0 \n\
! rts \n\
! );
}
#endif /* __m68k__ */
--- 165,173
rts \n\
_success: \n\
moveq #0,d0 \n\
! rts \n
! #endif /* __NetBSD__ __ELF__ */
! );
}
#endif /* __m68k__ */
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] production server down
Michael Fuhr wrote: On Wed, Dec 15, 2004 at 11:41:02AM -0800, Joe Conway wrote: Just wanted to close the loop for the sake of the list archives. With Tom's xlog dump tool I was able (with a bunch of his help off-list) to identify the needed parameters for pg_resetxlog. Running pg_resetxlog got us back a running database. We're now involved in checking the data. Any chance you could write up a summary of the thread: what caused the problem, how you diagnosed it, how you fixed it, and how to avoid it? Might make a useful lessons learned document. Sorry for the delay -- been a busy week. See a summary below. Hope someone finds this useful. Warning -- this is a bit long... --- Background: --- The server in question is an IBM x445, 8-way Xeon, 8 GB RAM. We're running SuSE 9 with the postgresql-7.4.5-36.4 RPM. The database is just over 400GB in size at this point, and resides on a dedicated NFS mounted Network Appliance volume (~6 TB). The server has 2 network interfaces, both gigabit ethernet. One interface (eth0) is dedicated to the NFS mounted storage. It is on a private storage subsystem vlan, running with jumbo frames (9K): # ifconfig eth0 eth0 Link encap:Ethernet HWaddr 00:09:6B:E6:33:B7 [...] UP BROADCAST RUNNING MULTICAST MTU:9000 Metric:1 The data volume is mounted thus: # cat /etc/fstab csddata7-vlan35:/vol/vol0/replica /replica nfs proto=tcp,suid,rw,vers=3,proto=tcp,timeo=600,retrans=2,hard,fg,rsize=8192,wsize=8192 0 0 We have been continuously and aggressively bulk loading parametric data collected from our company's equipment in the field (i.e. in use at our customers) for the past several weeks. This is part of a push to get caught up with available data, after which we expect bulk loading to take a few hours each evening. The server had been up since November 2, 2004. On December 13 the server experienced a complete hang, requiring our unix admin to go into the datacenter and physically cycle power. We don't know the exact cause of that hang, but we have recently experienced a similar hang on two similar servers (both IBM x445, both running SuSE 8.x, one running Oracle 9i, the other an application server). aside In both of those cases we were advised to increase the size of our swap partition -- for some reason either SuSE's installation defaults, or the admin doing the installation (not sure which), had decided that 1GB swap was sufficient for machines with 8GB of RAM. Novell's support advised to increase swap to = physical RAM. We had made that change on the two other servers, but not on the Postgres server because it was busy loading data at the time. We've now made that change, applied all IBM firmware updates, and are waiting to see if the problem repeats. /aside Upon server restart, there was instability noted with the network interfaces, and so they were reset (not exactly sure how the unix admin determined that, but that is what I was told). Unfortunately, the init.d script was in the process of starting postgresql while the eth0 interface (and thus the NFS mount and PGDATA) was unstable. The PostgreSQL Specific Problem: We decided to wait until the 14th before resuming data loading, because the developer who wrote the loading scripts was due back in the office that day after taking vacation. On the 14th, he noted that Postgres was not running, and further, would not start. Here is the snippet from the logs: 2004-12-13 15:05:52 LOG: recycled transaction log file 0165004C 2004-12-13 15:26:01 LOG: recycled transaction log file 0165004D 2004-12-13 16:39:55 LOG: database system was shut down at 2004-11-02 17:05:33 PST 2004-12-13 16:39:55 LOG: checkpoint record is at 0/9B0B8C 2004-12-13 16:39:55 LOG: redo record is at 0/9B0B8C; undo record is at 0/0; shutdown TRUE 2004-12-13 16:39:55 LOG: next transaction ID: 536; next OID: 17142 2004-12-13 16:39:55 LOG: database system is ready 2004-12-14 15:36:20 FATAL: IDENT authentication failed for user colprod 2004-12-14 15:36:58 FATAL: IDENT authentication failed for user colprod 2004-12-14 15:39:26 LOG: received smart shutdown request 2004-12-14 15:39:26 LOG: shutting down 2004-12-14 15:39:28 PANIC: could not open file /replica/pgdata/pg_xlog/ (log file 0, segment 0): No such file or directory 2004-12-14 15:39:28 LOG: shutdown process (PID 23202) was terminated by signal 6 2004-12-14 15:39:39 LOG: database system shutdown was interrupted at 2004-12-14 15:39:26 PST 2004-12-14 15:39:39 LOG: could not open file /replica/pgdata/pg_xlog/ (log file 0, segment 0): No such file or directory 2004-12-14 15:39:39 LOG: invalid primary checkpoint record 2004-12-14 15:39:39 LOG: could not open file /replica/pgdata/pg_xlog/ (log file 0, segment 0): No such file or directory 2004-12-14 15:39:39 LOG:
Re: [HACKERS] production server down
Joe Conway wrote: We then spent most of the next 24 hours reviewing the recovered database. The bulk data loading process was well instrumented, so we knew exactly which data should have been committed prior to the server hang, and which files were inprocess (we had been doing 10 loads in parallel) at the time of the hang. The results of the investigation indicated complete recovery, with no missing or unwanted (i.e. uncommitted records looking committed) data. Good analysis. Seems you were lucky in that only WAL was corrupt and not the heap or index files. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Call for port reports
Has anybody tried Solaris8 or 9/ADM64(SUN Fire v40 for example) combo? I personally don't have access to this platform, but am interested in someone else has already tried. -- Tatsuo Ishii I have started filling in the supported platform list for the 8.0.0 release with the information from the build farm: http://developer.postgresql.org/docs/postgres/supported-platforms.html It's now time to fill the holes. Briefly, I'm looking for exit status 0 on ./configure --prefix=SOMEWHERE --enable-thread-safety --with-tcl \ --with-perl --with-python --with-krb5 --with-pam -with-openssl make make install make check with PostgreSQL 8.0.0rc1 or later. If you know what you're doing, you can also try other options, but please tell what you did. (I would like the build farm members for Windows and Cygwin to use more feature-enabling options, because in those cases we really need the information about which extra features compile and work.) If your system provides multiple compilers (for example, a vendor compiler and GCC), test with all of them. Call configure as follows: ./configure CC=/foo/cc --prefix=... If your system has multiple compilation modes, such as 32 bit and 64 bit, it may be worth trying both. Report the output of SELECT version(); as well as a common name of the operating system under which it can be listed (e.g., the distributor, in case of a Linux-based system). -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] production server down
On Sat, Dec 18, 2004 at 02:28:51PM -0800, Joe Conway wrote: Hi, Apparently, either because of the server hang, or because of the flakey eth0 interface on reboot, pg_control had become corrupt. However, it was not corrupt in the sense that it contained impossibly invalid data. In fact, as pointed out by Alvaro, it had values that all look close to those one would find in a recently initdb'd pg_control file, except the last modified date: I can't help remembering the fact that the init script executes an initdb automatically if it finds an empty data directory (the ones I know of at least -- does the one you are running?). Maybe what happened was that it found the empty mount point, executed an initdb, and then the NFS drive came online. Later, the pg_control file was sync'ed to the empty database settings. It'd be interesting to know if the mount point does have some files on it. These values (from the corrupt pg_control file) are strange: pg_control last modified: Tue Dec 14 15:39:26 2004 Time of latest checkpoint:Tue Nov 2 17:05:32 2004 Maybe the latest checkpoint date has some interesting bit pattern that could explain it somehow. -- Alvaro Herrera ([EMAIL PROTECTED]) El sentido de las cosas no viene de las cosas, sino de las inteligencias que las aplican a sus problemas diarios en busca del progreso. (Ernesto Hernández-Novich) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] production server down
Alvaro Herrera wrote: I can't help remembering the fact that the init script executes an initdb automatically if it finds an empty data directory (the ones I know of at least -- does the one you are running?). Maybe what happened was that it found the empty mount point, executed an initdb, and then the NFS drive came online. Later, the pg_control file was sync'ed to the empty database settings. It'd be interesting to know if the mount point does have some files on it. Good point! I'll take a look at the first opportunity. These values (from the corrupt pg_control file) are strange: pg_control last modified: Tue Dec 14 15:39:26 2004 Time of latest checkpoint:Tue Nov 2 17:05:32 2004 Maybe the latest checkpoint date has some interesting bit pattern that could explain it somehow. The last modified corresponds to just prior to the PANIC. See the logs: 2004-12-14 15:39:26 LOG: received smart shutdown request 2004-12-14 15:39:26 LOG: shutting down 2004-12-14 15:39:28 PANIC: could not open file /replica/pgdata/pg_xlog/ (log file 0, segment 0): No such file or directory The Tue Nov 2 17:05:32 2004 seems to be related to the *previous* restart; from /var/log/messages: 8-- ... Nov 2 17:04:20 csdfds1 syslogd 1.4.1: restart. ... Nov 2 17:05:22 csdfds1 su: pam_unix2: session started for user postgres, service su ... Nov 2 17:05:33 csdfds1 su: (to postgres) root on /dev/pts/5 Nov 2 17:05:33 csdfds1 su: pam_unix2: session started for user postgres, service su Nov 2 17:05:33 csdfds1 su: pam_unix2: session finished for user postgres, service su ... 8-- Can you make any sense out of that? Joe ---(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] production server down
Alvaro Herrera [EMAIL PROTECTED] writes: These values (from the corrupt pg_control file) are strange: pg_control last modified: Tue Dec 14 15:39:26 2004 Time of latest checkpoint:Tue Nov 2 17:05:32 2004 The last modified date doesn't prove a lot because it would have been updated when we set the state to shutting down, just before the panic occurred when we noticed there wasn't any WAL segment file where pg_control said there should be one. The latest checkpoint is mighty interesting though. I think Alvaro's idea that this copy of pg_control got created when the NFS mount was offline is a real good theory. However, it would seem that that was quite some time ago (Nov 2 if not earlier), which would suggest that the mount instability problem has been around longer than Joe realizes :-( If the bogus copy is indeed hiding underneath the mount point, then the sequence of events last week is easy to explain: * system boots * NFS mount takes awhile to come online * Postgres starts and reads the bogus pg_control into memory; then it just sits there since they didn't try to start any data loading tasks right away * eventually NFS mount comes online * next day, admin decides to shut down Postgres * Postgres changes last-mod date and state in its in-memory pg_control, and writes it out, overwriting the good copy on the NFS server * Postgres then panics because there's no WAL file where pg_control indicates the shutdown checkpoint WAL record should go * and now we're in the state Joe documented So one thing I'd strongly suggest is stopping Postgres and dismounting the NFS server to see what's under there. If there is a valid-looking PGDATA directory under there, you definitely want to get rid of it to reduce the risk of this happening again. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] production server down
Joe Conway [EMAIL PROTECTED] writes: The Tue Nov 2 17:05:32 2004 seems to be related to the *previous* restart; from /var/log/messages: Nov 2 17:04:20 csdfds1 syslogd 1.4.1: restart. ... Nov 2 17:05:22 csdfds1 su: pam_unix2: session started for user postgres, service su ... Nov 2 17:05:33 csdfds1 su: (to postgres) root on /dev/pts/5 Nov 2 17:05:33 csdfds1 su: pam_unix2: session started for user postgres, service su Nov 2 17:05:33 csdfds1 su: pam_unix2: session finished for user postgres, service su I'm betting that the su at :33 is the invocation of the postmaster. The fact that it took the script 11 seconds to get to that step is suggestive to say the least. Are you using one of the scripts that does an auto initdb if it doesn't see a valid PGDATA? 11 seconds might be about right for that. One problem with this theory is how come you didn't get screwed during *that* boot cycle. It seems to require assuming that the NFS mount came online just after the initdb finished (else initdb would have overwritten the on-NFS pg_control) but before the regular postmaster started (else this same scenario would have played out then). That's not a very wide window. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
pg_resetxlog for 8.0 (was Re: [HACKERS] production server down)
Joe Conway [EMAIL PROTECTED] writes: The manpage for pg_resetxlog gives some general idea how it is used, and a way to estimate the next transaction id and wal segment. I had forgotten that that text was in there. It needs to be updated for 8.0 because WAL segment file names are now three-parters. I think there's also a MUST FIX FOR 8.0 to-do item here: there needs to be a way to specify the timeline ID to use. In prior versions we just allowed pg_resetxlog to set startup ID to 1 all the time, because it wasn't really being used for anything. But as of 8.0 that's a significant value and so you'd better be able to set it. What I'm inclined to do is just widen the -l option to take three numbers instead of 2. That will keep it in sync with what people will see when they look at WAL file names. Any objections? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] buildfarm improvements
Andrew Dunstan schrieb: I have implemented several requested improvements, which I hope will prove useful. Since this whole piece of work exists for the benefit of the pg developers, I'm posting some info here. The latest version includes these features: . the log page shows the system type near the top OS/Compiler/Architecture . the log page shows the script configuration data (other than the password) and including the script version number . the changed files list(s) on the log page include CVS revision numbers. An example showing all these can be seen at http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=dogdt=2004-12-17%2021:06:01 Constructive comments welcome as always. Good. What I also miss is the successful output of the make test step. Something like the Log in Details, just behind an additional request. Config = Log Link to Details Without those details one doesn't trust the presented result. He might think that only the build was successful, and not the make test step also. People I redirect to this page from other projects, not reading the status pages everyday. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] production server down
Tom Lane wrote:
I think Alvaro's idea that this copy of pg_control got created when the
NFS mount was offline is a real good theory. However, it would seem
that that was quite some time ago (Nov 2 if not earlier), which would
suggest that the mount instability problem has been around longer than
Joe realizes :-(
I'm starting to wonder if this has somehow happened once or even twice
before, each time the server was restarted. The timing of services
starting on boot might have been biting us all along.
If the bogus copy is indeed hiding underneath the mount point, then the
sequence of events last week is easy to explain:
* system boots
* NFS mount takes awhile to come online
* Postgres starts and reads the bogus pg_control into memory;
then it just sits there since they didn't try to start any
data loading tasks right away
* eventually NFS mount comes online
* next day, admin decides to shut down Postgres
* Postgres changes last-mod date and state in its in-memory
pg_control, and writes it out, overwriting the good copy
on the NFS server
* Postgres then panics because there's no WAL file where
pg_control indicates the shutdown checkpoint WAL record
should go
* and now we're in the state Joe documented
So one thing I'd strongly suggest is stopping Postgres and dismounting
the NFS server to see what's under there. If there is a valid-looking
PGDATA directory under there, you definitely want to get rid of it to
reduce the risk of this happening again.
Perhaps we should purposefully place a root owned placeholder file there
-- that way Postgres would refuse to start at all in this scenario.
BTW, the init script is indeed the one which automatically does initdb:
[...]
case $1 in
start)
touch $LOGFILE
chown postgres:postgres $LOGFILE
chmod 0600 $LOGFILE
if [ ! -f $DATADIR/PG_VERSION ]; then
echo -n Initializing the PostgreSQL database at location
${DATADIR}
LANG_SYSCONFIG=/etc/sysconfig/language
test -f $LANG_SYSCONFIG . $LANG_SYSCONFIG
LANG=${POSTGRES_LANG:-$RC_LANG}
install -d -o postgres -g daemon -m 700 ${DATADIR}
su - postgres -c env -i LANG=$LANG initdb $DATADIR
initlog || rc_failed
[...]
Joe
---(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] production server down
Joe Conway wrote: So one thing I'd strongly suggest is stopping Postgres and dismounting the NFS server to see what's under there. If there is a valid-looking PGDATA directory under there, you definitely want to get rid of it to reduce the risk of this happening again. Perhaps we should purposefully place a root owned placeholder file there -- that way Postgres would refuse to start at all in this scenario. BTW, the init script is indeed the one which automatically does initdb: ISTM that this should ideally be a sysconfig setting that is picked up by the init script. In the absence of that, in your case, certainly the root-owned placeholder is a good idea - it seems nicer than disabling on-boot startup altogether if you can avoid that. cheers andrew ---(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] buildfarm improvements
Reini Urban wrote: What I also miss is the successful output of the make test step. Something like the Log in Details, just behind an additional request. Config = Log Link to Details Without those details one doesn't trust the presented result. He might think that only the build was successful, and not the make test step also. People I redirect to this page from other projects, not reading the status pages everyday. That would actually be a substantial change in the way it works. Basically, it sends the log of the step that failed. That preserves bandwidth and doesn't clog the database with success cases. These logs are not inconsiderable - I just checked on the canonical system and for the last successful run they were 640Kb. I was originally given this (virtual) server on the basis of my assurance that the bandwidth and database requirements would be very modest, so I'm inclined to keep to that. Regarding your last sentence - the intended prime users are the postgresql hackers. If it had a vastly more general audience I would have produced something a good less spartan in style. I'm not quite sure why you're redirecting people to the status pages from other projects. This is not the official list of supported platforms, and is not intended as a substitute for it. Perhaps we could put a statement at the top of the details page saying what steps have succeeded (which we could infer from the result). Of course, if people don't want to believe it then they won't - having logs should not make believing it any easier - faking the logs would be quite trivial. FYI here's what happens during a run - a status of OK means that *all* of this has run successfully: [EMAIL PROTECTED] buildfarm]$ ./run_build.pl --verbose checking out source ... checking if build run needed ... copying source to pgsql.3034 ... running configure ... running make ... running make check ... running make contrib ... running make install ... setting up db cluster ... starting db ... running make installcheck ... restarting db ... running make contrib install ... running make contrib installcheck ... stopping db ... OK All the buildfarm members are known, by the way, and every status report is signed with a SHA1 signature. We don't just accept anonymous reports. In many cases I know these people from previous electronic interaction, via email and/or IRC. That, more than the presence of success logs, should give you some confidence in the results, I hope. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] production server down
Andrew Dunstan wrote: In the absence of that, in your case, certainly the root-owned placeholder is a good idea - it seems nicer than disabling on-boot startup altogether if you can avoid that. I'm pretty well convinced at this point that a start on boot init script is inappropriate when working with NFS attached storage. We really do need to be sure the mount is OK before starting the database. And we have the watchdogs in place, and people on call at all times, specifically to deal with service down events. And while we could always modify the init script to rip out the helpful initdb part, I think the under-the-mount safety file is a must, unless of course someone thinks of an alternate scenario where it will do more harm than good ;-). Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Shared row locking
BTom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: You mean all empty/zero rows can be removed? Can we guarantee that on commit we can clean up the bitmap? If not the idea doesn't work. For whatever data structure we use, we may reset the structure to empty during backend-crash recovery. So your objection boils down to what if a backend exits normally but forgets to clean up its locks? Assuming that doesn't happen isn't any worse than assuming a backend will clean up its shared memory state on non-crash exit, so I don't think it's a serious concern. That brings another thought: really what this is all about is working around the fact that the standard lock manager can only cope with a finite number of coexisting locks, because it's working in a fixed-size shared memory arena. Maybe we should instead think about ways to allow the existing lock table to spill to disk when it gets too big. That would eliminate max_locks_per_transaction as a source of hard failures, which would be a nice benefit. Agreed. Once concern I have about allowing the lock table to spill to disk is that a large number of FOR UPDATE locks could push out lock entries used by other backends, causing very poor performance. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Identifying time of last stat reset via sql
This relates to an earlier request from someone to allow reporting of the server start time. It seems both stats start/reset time and server start time are related. Is this something for the TODO list? I can't remember why we didn't want to report server start time, at least for super-users. --- Ed Loehr wrote: I asked this on general, but didn't receive any responses. Is it possible via SQL to identify the time of the last stat reset (or pg_stat_reset() call)? This is what I'm lacking to be able to measure query activity volume over time via SQL, i.e., maybe a function similar to the fictitious pg_stat_get_last_reset_time() below: select sum(n_tup_ins + n_tup_upd + n_tup_del) / (now() - pg_stat_get_last_reset_time()) as write_qps from pg_stat_all_tables If not, would this be considered worthy of a TODO item? Or is there another approached designed for determining QPS, etc? Ed ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- 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]
[HACKERS] stable contrib cleanup
Is there any chance we could do a little low-risk housekeeping on contrib in stable branches? Specific low hanging fruit I'd like to see for the 7.4 branch is adding cube/expected/cube_1.out to the branch, and backporting the contrib/Makefile changes Tom put in the other day. (There might be others to follow, but those would be a good start). I realise these are not high priority items, but they would help us get more clean buildfarm runs. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
