[HACKERS] order of operations for pg_restore
I'm working on a tool that runs pg_restore with -j 4. I notice that after COPYing in the data, pg_restore does two indexes and a cluster command in parallel. The first CREATE INDEX is running, the CLUSTER command is waiting on it and the second CREATE INDEX is waiting on the CLUSTER. This seems sub-optimal. Would it make sense to run the CLUSTER command first? I'm pretty sure I can replicate the behavior if necessary. Running 9.1.2. Andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] index sizes: single table vs partitioned
For a large table, should there be a difference in index sizes between a single table representation and representation based on multiple partitions with identical indexes? A
[HACKERS] Query to generate CREATE INDEX statement from
I'm building some partitioning support functions. I'm working on writing one called clone_indexes_to_partition right now. The idea is to take all the indexes applied to the parent and create a matching index on the child. Is there existing code that generates a CREATE INDEX statement given an indexrelid? Andrew
Re: [HACKERS] mysql2pgsql.perl update
On Mon, Feb 28, 2011 at 11:26 AM, Josh Berkus j...@agliodbs.com wrote: does foundry support git or should I just link to a repo on github? If you prefer using git, the latter. Ok, will do. Assign the project and I'll update stuff. Andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] mysql2pgsql.perl update
Hi all. I spent some time updating mysql2pgsql.perl. Changes were driven by an attempt to migrate a redmine database. Original code was failing for a number of reasons (regex recursion explosion, . I was wondering it there's a more formal / appropriate place to put this. I'd also appreciate a separate set of eyes on my changes. I grabbed an initial copy from http://pgfoundry.org/projects/mysql2pgsql/ Git repo of the changes at https://github.com/SmartReceipt/mysql2pgsql/commits/master/ Commit history follows. commit 3cbe2cfa8782d250e5c4ee814c4585c96105fb45 (HEAD, sr/master, origin/master, master) Author: Andrew Hammond andrew.george.hamm...@gmail.com Date: Sat Feb 26 12:36:36 2011 -0800 simplify handling of mysql autoincrement to use serial8 datatype commit 5c559b7073e6f6e72ce11f0f45be4d13cc30fd9a Author: Andrew Hammond andrew.george.hamm...@gmail.com Date: Sat Feb 26 12:26:46 2011 -0800 multi-value inserts are supported in pgsql as of 8.2 and way faster commit f5798a7911e5114378fd4764a62288e1826f6b56 Author: Andrew Hammond andrew.george.hamm...@gmail.com Date: Sat Feb 26 12:22:32 2011 -0800 replace \\ with commit d730da318da301157783bf15c6add3e58e32b57e Author: Andrew Hammond andrew.george.hamm...@gmail.com Date: Sat Feb 26 12:11:49 2011 -0800 best practices for handling quoted strings and use lookahead rather than unnecessary match and replace commit 20610fcb50d082e2c46bf27190e4d30dba966c27 Author: Andrew Hammond andrew.george.hamm...@gmail.com Date: Sat Feb 26 12:08:40 2011 -0800 whitespace indenting normalization commit 4281e1c314501f4209245ac55d31f1e43b4ddc21 Author: Andrew Hammond andrew.george.hamm...@gmail.com Date: Sat Feb 26 11:58:52 2011 -0800 pgfoundry version 2007-12-04 17:29 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] mysql2pgsql.perl update
On Mon, Feb 28, 2011 at 10:49 AM, Josh Berkus j...@agliodbs.com wrote: Andrew, I spent some time updating mysql2pgsql.perl. Changes were driven by an attempt to migrate a redmine database. Original code was failing for a number of reasons (regex recursion explosion, . I was wondering it there's a more formal / appropriate place to put this. I'd also appreciate a separate set of eyes on my changes. I grabbed an initial copy from http://pgfoundry.org/projects/mysql2pgsql/ Git repo of the changes at https://github.com/SmartReceipt/mysql2pgsql/commits/master/ I can give you ownership of the pgFoundry project. What's your pgfoundry ID? pgfoundry id is ahammond. does foundry support git or should I just link to a repo on github? A -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
On Thu, Jul 3, 2008 at 10:57 PM, Andrew Hammond [EMAIL PROTECTED] wrote: On Thu, Jul 3, 2008 at 3:47 PM, Tom Lane [EMAIL PROTECTED] wrote: Have you looked into the machine's kernel log to see if there is any evidence of low-level distress (hardware or filesystem level)? I'm wondering if ENOSPC is being reported because it is the closest available errno code, but the real problem is something different than the error message text suggests. Other than the errno the symptoms all look quite a bit like a bad-sector problem ... da1 is the storage device where the PGDATA lives. Jun 19 03:06:14 db1 kernel: mpt1: request 0x929ba560:6810 timed out for ccb 0xffe2 (req-ccb 0xffe2) Jun 19 03:06:14 db1 kernel: mpt1: request 0x929b90c0:6811 timed out for ccb 0xff0001081000 (req-ccb 0xff0001081000) Jun 19 03:06:14 db1 kernel: mpt1: request 0x929b9f88:6812 timed out for ccb 0xffd93800 (req-ccb 0xffd93800) Jun 19 03:06:14 db1 kernel: mpt1: attempting to abort req 0x929ba560:6810 function 0 Jun 19 03:06:14 db1 kernel: mpt1: request 0x929bcc90:6813 timed out for ccb 0xff03e132dc00 (req-ccb 0xff03e132dc00) Jun 19 03:06:14 db1 kernel: mpt1: completing timedout/aborted req 0x929ba560:6810 Jun 19 03:06:14 db1 kernel: mpt1: abort of req 0x929ba560:0 completed Jun 19 03:06:14 db1 kernel: mpt1: attempting to abort req 0x929b90c0:6811 function 0 Jun 19 03:06:14 db1 kernel: mpt1: completing timedout/aborted req 0x929b90c0:6811 Jun 19 03:06:14 db1 kernel: mpt1: abort of req 0x929b90c0:0 completed Jun 19 03:06:14 db1 kernel: mpt1: attempting to abort req 0x929b9f88:6812 function 0 Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): WRITE(16). CDB: 8a 0 0 0 0 1 6c 99 9 c0 0 0 0 20 0 0 Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): CAM Status: SCSI Status Error Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): SCSI Status: Check Condition Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): UNIT ATTENTION asc:29,0 Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): Power on, reset, or bus device reset occurred Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): Retrying Command (per Sense Data) Jun 19 03:06:14 db1 kernel: mpt1: completing timedout/aborted req 0x929b9f88:6812 Jun 19 03:06:14 db1 kernel: mpt1: abort of req 0x929b9f88:0 completed Jun 19 03:06:14 db1 kernel: mpt1: attempting to abort req 0x929bcc90:6813 function 0 Jun 19 03:06:14 db1 kernel: mpt1: completing timedout/aborted req 0x929bcc90:6813 Jun 19 03:06:14 db1 kernel: mpt1: abort of req 0x929bcc90:0 completed Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): WRITE(16). CDB: 8a 0 0 0 0 1 65 1b 71 a0 0 0 0 20 0 0 Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): CAM Status: SCSI Status Error Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): SCSI Status: Check Condition Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): UNIT ATTENTION asc:29,0 Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): Power on, reset, or bus device reset occurred Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): Retrying Command (per Sense Data) Jun 19 03:18:16 db1 kernel: mpt3: request 0x929d5900:56299 timed out for ccb 0xff03df7f5000 (req-ccb 0xff03df7f5000) I think this is a smoking gun. Andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
On Mon, Jul 7, 2008 at 12:33 PM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Hammond [EMAIL PROTECTED] writes: On Thu, Jul 3, 2008 at 3:47 PM, Tom Lane [EMAIL PROTECTED] wrote: Have you looked into the machine's kernel log to see if there is any evidence of low-level distress (hardware or filesystem level)? Jun 19 03:06:14 db1 kernel: mpt1: attempting to abort req 0x929b9f88:6812 function 0 Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): WRITE(16). CDB: 8a 0 0 0 0 1 6c 99 9 c0 0 0 0 20 0 0 Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): CAM Status: SCSI Status Error Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): SCSI Status: Check Condition Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): UNIT ATTENTION asc:29,0 Jun 19 03:06:14 db1 kernel: (da1:mpt1:0:0:0): Power on, reset, or bus device reset occurred [etc] I think this is a smoking gun. Yeah, sure looks like one. Time to replace that disk drive? Well, that's an Apple XRaid device on the other side of an LSI FiberChannel HBA. I'll see if it has any error messages in it's logs. Also, I suggest filing a bug with your kernel distributor --- ENOSPC was a totally misleading error code here. Seems like EIO would be more appropriate. They'll probably want to see the kernel log. It's FreeBSD 6.2 (yes, I know it's EoL). I'll submit a bug including this email. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
On Mon, Jul 7, 2008 at 2:08 PM, Andrew Hammond [EMAIL PROTECTED] wrote: On Mon, Jul 7, 2008 at 12:33 PM, Tom Lane [EMAIL PROTECTED] wrote: Also, I suggest filing a bug with your kernel distributor --- ENOSPC was a totally misleading error code here. Seems like EIO would be more appropriate. They'll probably want to see the kernel log. It's FreeBSD 6.2 (yes, I know it's EoL). I'll submit a bug including this email. http://www.freebsd.org/cgi/query-pr.cgi?pr=125382 Andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
Does anyone else have any suggestions about what I can do to diagnose this? Do I need to re-initdb or can I reasonably keep running with the existing db? A On Mon, Jun 30, 2008 at 7:20 PM, Andrew Hammond [EMAIL PROTECTED] wrote: On Fri, Jun 27, 2008 at 8:14 PM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Hammond [EMAIL PROTECTED] writes: (I thought this line was interesting) Jun 27 15:54:31 qadb2 postgres[92519]: [44-1] PANIC: could not open relation 1663/16386/679439393: No such file or directory I googled to find out what the numbers 1663/16386/679439393 from the PANIC message mean, but no luck. tablespaceOID/databaseOID/relfilenode. Looks like just some random user table. Not clear why this would be a crash, *especially* since WAL recovery is generally willing to create nonexistent files. Is this reproducible? Yes, both when I just tried to restart the recovery: Jun 30 16:04:43 qadb2 postgres[20797]: [27-1] DEBUG: invoking IpcMemoryCreate(size=92938240) Jun 30 16:04:43 qadb2 postgres[20797]: [28-1] DEBUG: max_safe_fds = 983, usable_fds = 1000, already_open = 7 Jun 30 16:04:43 qadb2 postgres[20798]: [29-1] LOG: database system was interrupted while in recovery at 2008-06-27 15:54:31 PDT Jun 30 16:04:43 qadb2 postgres[20798]: [29-2] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. Jun 30 16:04:43 qadb2 postgres[20798]: [30-1] LOG: starting archive recovery Jun 30 16:04:43 qadb2 postgres[20798]: [31-1] LOG: restore_command = cp -p /usr/tmp/2008-06-25_wals/%f %p Jun 30 16:04:43 qadb2 postgres[20798]: [32-1] DEBUG: executing restore command cp -p /usr/tmp/2008-06-25_wals/0001.history pg_xlog/RECOVERYHISTORY Jun 30 16:04:43 qadb2 postgres[20798]: [33-1] DEBUG: could not restore file 0001.history from archive: return code 256 Jun 30 16:04:43 qadb2 postgres[20798]: [34-1] DEBUG: executing restore command cp -p /usr/tmp/2008-06-25_wals/000101D60078.0055F0B8.backup Jun 30 16:04:43 qadb2 postgres[20798]: [34-2] pg_xlog/RECOVERYHISTORY Jun 30 16:04:43 qadb2 postgres[20798]: [35-1] LOG: restored log file 000101D60078.0055F0B8.backup from archive Jun 30 16:04:43 qadb2 postgres[20798]: [36-1] DEBUG: executing restore command cp -p /usr/tmp/2008-06-25_wals/000101D60078 pg_xlog/RECOVERYXLOG Jun 30 16:04:44 qadb2 postgres[20797]: [29-1] DEBUG: forked new backend, pid=20805 socket=8 Jun 30 16:04:44 qadb2 postgres[20805]: [29-1] LOG: connection received: host=[local] Jun 30 16:04:44 qadb2 postgres[20805]: [30-1] FATAL: the database system is starting up Jun 30 16:04:44 qadb2 postgres[20805]: [31-1] DEBUG: proc_exit(0) Jun 30 16:04:44 qadb2 postgres[20805]: [32-1] DEBUG: shmem_exit(0) Jun 30 16:04:44 qadb2 postgres[20805]: [33-1] DEBUG: exit(0) Jun 30 16:04:44 qadb2 postgres[20797]: [30-1] DEBUG: reaping dead processes Jun 30 16:04:44 qadb2 postgres[20797]: [31-1] DEBUG: server process (PID 20805) exited with exit code 0 Jun 30 16:04:44 qadb2 postgres[20798]: [37-1] LOG: restored log file 000101D60078 from archive Jun 30 16:04:44 qadb2 postgres[20798]: [38-1] LOG: checkpoint record is at 1D6/7855F0B8 Jun 30 16:04:44 qadb2 postgres[20798]: [39-1] LOG: redo record is at 1D6/7855F0B8; undo record is at 0/0; shutdown FALSE Jun 30 16:04:44 qadb2 postgres[20798]: [40-1] LOG: next transaction ID: 397171279; next OID: 679516596 Jun 30 16:04:44 qadb2 postgres[20798]: [41-1] LOG: next MultiXactId: 857318; next MultiXactOffset: 1718141 Jun 30 16:04:44 qadb2 postgres[20798]: [42-1] LOG: automatic recovery in progress Jun 30 16:04:44 qadb2 postgres[20798]: [43-1] LOG: redo starts at 1D6/7855F108 Jun 30 16:04:45 qadb2 postgres[20798]: [44-1] PANIC: could not open relation 1663/16386/679439393: No such file or directory Jun 30 16:04:45 qadb2 postgres[20797]: [32-1] DEBUG: reaping dead processes Jun 30 16:04:45 qadb2 postgres[20797]: [33-1] LOG: startup process (PID 20798) was terminated by signal 6 Jun 30 16:04:45 qadb2 postgres[20797]: [34-1] LOG: aborting startup due to startup process failure Jun 30 16:04:45 qadb2 postgres[20797]: [35-1] DEBUG: proc_exit(1) Jun 30 16:04:45 qadb2 postgres[20797]: [36-1] DEBUG: shmem_exit(1) Jun 30 16:04:45 qadb2 postgres[20797]: [37-1] DEBUG: exit(1) And also when I tried to wipe the slate clean and recover it freshly. Jun 30 19:11:59 qadb2 postgres[23091]: [1-1] DEBUG: postmaster: PostmasterMain: initial environ dump: Jun 30 19:11:59 qadb2 postgres[23091]: [2-1] DEBUG: - Jun 30 19:11:59 qadb2 postgres[23091]: [3-1] DEBUG: USER=pgsql Jun 30 19:11:59 qadb2 postgres[23091]: [4-1] DEBUG: MAIL=/var/mail/pgsql Jun 30 19:11:59 qadb2 postgres[23091]: [5-1] DEBUG: LD_LIBRARY_PATH=:/usr/local/adecn/lib Jun 30 19:11:59 qadb2 postgres[23091]: [6-1] DEBUG: HOME=/usr/local/pgsql Jun 30 19:11:59 qadb2 postgres[23091
Re: [HACKERS] the un-vacuumable table
On Thu, Jul 3, 2008 at 2:35 PM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Hammond [EMAIL PROTECTED] writes: Does anyone else have any suggestions about what I can do to diagnose this? The whole thing is pretty mystifying, especially the ENOSPC write failure on what seems like it couldn't have been a full disk. Yes, I've passed along the task of explaining why PG thought the disk was full to the sysadmin responsible for the box. I'll post the answer here, when and if we have one. Jun 27 15:54:31 qadb2 postgres[92519]: [44-1] PANIC: could not open relation 1663/16386/679439393: No such file or directory I don't think anyone asked before --- after the restore fails with the above, does the directory $PGDATA/base/16386/ exist? Although WAL recovery should attempt to create missing files, I think it won't try to create missing directories. The directory exists (and the 679439393 file does not). Andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
On Thu, Jul 3, 2008 at 3:47 PM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Hammond [EMAIL PROTECTED] writes: On Thu, Jul 3, 2008 at 2:35 PM, Tom Lane [EMAIL PROTECTED] wrote: The whole thing is pretty mystifying, especially the ENOSPC write failure on what seems like it couldn't have been a full disk. Yes, I've passed along the task of explaining why PG thought the disk was full to the sysadmin responsible for the box. I'll post the answer here, when and if we have one. I just noticed something even more mystifying: you said that the ENOSPC error occurred once a day during vacuuming. Actually, the ENOSPC happened once. After that first error, we got vacuumdb: vacuuming of database adecndb failed: ERROR: failed to re-find parent key in ledgerdetail_2008_03_idx2 for deletion target page 64767 repeatedly. That doesn't make any sense, because a write error would leave the shared buffer still marked dirty, and so the next checkpoint would try to write it again. If there's a persistent write error on a particular block, you should see it being complained of at least once per checkpoint interval. If you didn't see that, it suggests that the ENOSPC was transient, which isn't unreasonable --- but why would it recur for the exact same block each night? Have you looked into the machine's kernel log to see if there is any evidence of low-level distress (hardware or filesystem level)? I'm wondering if ENOSPC is being reported because it is the closest available errno code, but the real problem is something different than the error message text suggests. Other than the errno the symptoms all look quite a bit like a bad-sector problem ... I will pass this along to the sysadmin in charge of this box. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the un-vacuumable table
On Fri, Jun 27, 2008 at 8:14 PM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Hammond [EMAIL PROTECTED] writes: (I thought this line was interesting) Jun 27 15:54:31 qadb2 postgres[92519]: [44-1] PANIC: could not open relation 1663/16386/679439393: No such file or directory I googled to find out what the numbers 1663/16386/679439393 from the PANIC message mean, but no luck. tablespaceOID/databaseOID/relfilenode. Looks like just some random user table. Not clear why this would be a crash, *especially* since WAL recovery is generally willing to create nonexistent files. Is this reproducible? Yes, both when I just tried to restart the recovery: Jun 30 16:04:43 qadb2 postgres[20797]: [27-1] DEBUG: invoking IpcMemoryCreate(size=92938240) Jun 30 16:04:43 qadb2 postgres[20797]: [28-1] DEBUG: max_safe_fds = 983, usable_fds = 1000, already_open = 7 Jun 30 16:04:43 qadb2 postgres[20798]: [29-1] LOG: database system was interrupted while in recovery at 2008-06-27 15:54:31 PDT Jun 30 16:04:43 qadb2 postgres[20798]: [29-2] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. Jun 30 16:04:43 qadb2 postgres[20798]: [30-1] LOG: starting archive recovery Jun 30 16:04:43 qadb2 postgres[20798]: [31-1] LOG: restore_command = cp -p /usr/tmp/2008-06-25_wals/%f %p Jun 30 16:04:43 qadb2 postgres[20798]: [32-1] DEBUG: executing restore command cp -p /usr/tmp/2008-06-25_wals/0001.history pg_xlog/RECOVERYHISTORY Jun 30 16:04:43 qadb2 postgres[20798]: [33-1] DEBUG: could not restore file 0001.history from archive: return code 256 Jun 30 16:04:43 qadb2 postgres[20798]: [34-1] DEBUG: executing restore command cp -p /usr/tmp/2008-06-25_wals/000101D60078.0055F0B8.backup Jun 30 16:04:43 qadb2 postgres[20798]: [34-2] pg_xlog/RECOVERYHISTORY Jun 30 16:04:43 qadb2 postgres[20798]: [35-1] LOG: restored log file 000101D60078.0055F0B8.backup from archive Jun 30 16:04:43 qadb2 postgres[20798]: [36-1] DEBUG: executing restore command cp -p /usr/tmp/2008-06-25_wals/000101D60078 pg_xlog/RECOVERYXLOG Jun 30 16:04:44 qadb2 postgres[20797]: [29-1] DEBUG: forked new backend, pid=20805 socket=8 Jun 30 16:04:44 qadb2 postgres[20805]: [29-1] LOG: connection received: host=[local] Jun 30 16:04:44 qadb2 postgres[20805]: [30-1] FATAL: the database system is starting up Jun 30 16:04:44 qadb2 postgres[20805]: [31-1] DEBUG: proc_exit(0) Jun 30 16:04:44 qadb2 postgres[20805]: [32-1] DEBUG: shmem_exit(0) Jun 30 16:04:44 qadb2 postgres[20805]: [33-1] DEBUG: exit(0) Jun 30 16:04:44 qadb2 postgres[20797]: [30-1] DEBUG: reaping dead processes Jun 30 16:04:44 qadb2 postgres[20797]: [31-1] DEBUG: server process (PID 20805) exited with exit code 0 Jun 30 16:04:44 qadb2 postgres[20798]: [37-1] LOG: restored log file 000101D60078 from archive Jun 30 16:04:44 qadb2 postgres[20798]: [38-1] LOG: checkpoint record is at 1D6/7855F0B8 Jun 30 16:04:44 qadb2 postgres[20798]: [39-1] LOG: redo record is at 1D6/7855F0B8; undo record is at 0/0; shutdown FALSE Jun 30 16:04:44 qadb2 postgres[20798]: [40-1] LOG: next transaction ID: 397171279; next OID: 679516596 Jun 30 16:04:44 qadb2 postgres[20798]: [41-1] LOG: next MultiXactId: 857318; next MultiXactOffset: 1718141 Jun 30 16:04:44 qadb2 postgres[20798]: [42-1] LOG: automatic recovery in progress Jun 30 16:04:44 qadb2 postgres[20798]: [43-1] LOG: redo starts at 1D6/7855F108 Jun 30 16:04:45 qadb2 postgres[20798]: [44-1] PANIC: could not open relation 1663/16386/679439393: No such file or directory Jun 30 16:04:45 qadb2 postgres[20797]: [32-1] DEBUG: reaping dead processes Jun 30 16:04:45 qadb2 postgres[20797]: [33-1] LOG: startup process (PID 20798) was terminated by signal 6 Jun 30 16:04:45 qadb2 postgres[20797]: [34-1] LOG: aborting startup due to startup process failure Jun 30 16:04:45 qadb2 postgres[20797]: [35-1] DEBUG: proc_exit(1) Jun 30 16:04:45 qadb2 postgres[20797]: [36-1] DEBUG: shmem_exit(1) Jun 30 16:04:45 qadb2 postgres[20797]: [37-1] DEBUG: exit(1) And also when I tried to wipe the slate clean and recover it freshly. Jun 30 19:11:59 qadb2 postgres[23091]: [1-1] DEBUG: postmaster: PostmasterMain: initial environ dump: Jun 30 19:11:59 qadb2 postgres[23091]: [2-1] DEBUG: - Jun 30 19:11:59 qadb2 postgres[23091]: [3-1] DEBUG: USER=pgsql Jun 30 19:11:59 qadb2 postgres[23091]: [4-1] DEBUG: MAIL=/var/mail/pgsql Jun 30 19:11:59 qadb2 postgres[23091]: [5-1] DEBUG: LD_LIBRARY_PATH=:/usr/local/adecn/lib Jun 30 19:11:59 qadb2 postgres[23091]: [6-1] DEBUG: HOME=/usr/local/pgsql Jun 30 19:11:59 qadb2 postgres[23091]: [7-1] DEBUG: PGLIB=/usr/local/lib Jun 30 19:11:59 qadb2 postgres[23091]: [8-1] DEBUG: PS1=[QA2] [EMAIL PROTECTED]:\w\$ Jun 30 19:11:59 qadb2 postgres[23091]: [9-1] DEBUG: BLOCKSIZE=K Jun 30 19:11:59 qadb2 postgres[23091]: [10-1] DEBUG:TERM=xterm Jun 30 19:11:59 qadb2 postgres[23091]: [11-1
Re: [HACKERS] the un-vacuumable table
On Wed, Jun 25, 2008 at 9:57 AM, Andrew Hammond [EMAIL PROTECTED] wrote: On Wed, Jun 25, 2008 at 2:58 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Andrew Hammond wrote: I found this error message in my log files repeatedly: Error: failed to re-find parent key in ledgerdetail_2008_03_idx2 for deletion target page 64767 I though hmm, that index looks broken. I'd better re-create it. So, I dropped the index and then tried to create a new one to replace it. Which completely locked up the backend that was running the CREATE TABLE. I ran truss against the backend in question and it didn't register anything (except signals 2 and 15 when I tried to cancel the query and kill the backend respectively). I eventually had to restart the database to get the CREATE INDEX process to go away (well, to release that big nasty lock). What kind of an index is it? Does SELECT COUNT(*) from table work? After the restart I did a count(*) and it worked. A little under 13m rows. So, sequential scans seem to work. posting here in case there's interest in gathering some forensic data or a clever suggetion about how I can recover this situation or even some ideas about what's causing it. Anyway, the current plan is to drop the table and reload it from backup. I'm Yes, please take a filesystem-level backup right away to retain the evidence. Well, I've already burned our downtime allowance for this month, but we do a regular PITR type backup which hopefully will be sufficient to replicate the problem. Could you connect to the hung backend with gdb and get a stacktrace? The backend is no longer hung (two restarts later). I'll try to reproduce this problem on my workstation (same binary, same OS, libraries etc) using the PITR dump. Andrew I tried to restore the PITR backup and it failed. Jun 27 15:54:30 qadb2 postgres[92517]: [1-1] DEBUG: postmaster: PostmasterMain: initial environ dump: Jun 27 15:54:30 qadb2 postgres[92517]: [2-1] DEBUG: - Jun 27 15:54:30 qadb2 postgres[92517]: [3-1] DEBUG: USER=pgsql Jun 27 15:54:30 qadb2 postgres[92517]: [4-1] DEBUG: MAIL=/var/mail/pgsql Jun 27 15:54:30 qadb2 postgres[92517]: [5-1] DEBUG: LD_LIBRARY_PATH=:/usr/local/adecn/lib Jun 27 15:54:30 qadb2 postgres[92517]: [6-1] DEBUG: HOME=/usr/local/pgsql Jun 27 15:54:30 qadb2 postgres[92517]: [7-1] DEBUG: PGLIB=/usr/local/lib Jun 27 15:54:30 qadb2 postgres[92517]: [8-1] DEBUG: PS1=[QA2] [EMAIL PROTECTED]:\w\$ Jun 27 15:54:30 qadb2 postgres[92517]: [9-1] DEBUG: BLOCKSIZE=K Jun 27 15:54:30 qadb2 postgres[92517]: [10-1] DEBUG:TERM=xterm Jun 27 15:54:30 qadb2 postgres[92517]: [11-1] DEBUG: PGSYSCONFDIR=/usr/local/etc/postgresql Jun 27 15:54:30 qadb2 postgres[92517]: [12-1] DEBUG: PGLOCALEDIR=/usr/local/share/locale Jun 27 15:54:30 qadb2 postgres[92517]: [13-1] DEBUG: Jun 27 15:54:30 qadb2 postgres[92517]: [13-2] PATH=/usr/local/adecn/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/usr/X11R6/bin Jun 27 15:54:30 qadb2 postgres[92517]: [13-3] :/usr/local/pgsql/bin Jun 27 15:54:30 qadb2 postgres[92517]: [14-1] DEBUG: ADECN_HOME=/usr/local/adecn Jun 27 15:54:30 qadb2 postgres[92517]: [15-1] DEBUG:SHELL=/bin/sh Jun 27 15:54:30 qadb2 postgres[92517]: [16-1] DEBUG: Jun 27 15:54:30 qadb2 postgres[92517]: [16-2] CLASSPATH= (deleted a bunch of lines) Jun 27 15:54:30 qadb2 postgres[92517]: [17-1] DEBUG: PYTHONPATH=/usr/local/adecn/python:/usr/local/adecn/lib/python:/usr/local/adecn/api/client/python Jun 27 15:54:30 qadb2 postgres[92517]: [18-1] DEBUG:FTP_PASSIVE_MODE=YES Jun 27 15:54:30 qadb2 postgres[92517]: [19-1] DEBUG: PGDATA=/var/db/adecn/adecndb Jun 27 15:54:30 qadb2 postgres[92517]: [20-1] DEBUG:LC_COLLATE=C Jun 27 15:54:30 qadb2 postgres[92517]: [21-1] DEBUG:LC_CTYPE=C Jun 27 15:54:30 qadb2 postgres[92517]: [22-1] DEBUG:LC_MESSAGES=C Jun 27 15:54:30 qadb2 postgres[92517]: [23-1] DEBUG:LC_MONETARY=C Jun 27 15:54:30 qadb2 postgres[92517]: [24-1] DEBUG:LC_NUMERIC=C Jun 27 15:54:30 qadb2 postgres[92517]: [25-1] DEBUG:LC_TIME=C Jun 27 15:54:30 qadb2 postgres[92517]: [26-1] DEBUG: - Jun 27 15:54:30 qadb2 postgres[92518]: [27-1] DEBUG: invoking IpcMemoryCreate(size=92938240) Jun 27 15:54:30 qadb2 postgres[92518]: [28-1] DEBUG: max_safe_fds = 983, usable_fds = 1000, already_open = 7 Jun 27 15:54:30 qadb2 postgres[92519]: [29-1] LOG: database system was interrupted at 2008-06-25 03:01:02 PDT Jun 27 15:54:30 qadb2 postgres[92519]: [30-1] LOG: starting archive recovery Jun 27 15:54:30 qadb2 postgres[92519]: [31-1] LOG: restore_command = cp -p /usr/tmp/2008-06-25_wals/%f %p Jun 27 15:54:30 qadb2 postgres[92519]: [32-1] DEBUG: executing restore command cp -p /usr/tmp/2008-06-25_wals/0001.history pg_xlog/RECOVERYHISTORY Jun 27 15:54:30 qadb2 postgres[92519]: [33-1] DEBUG: could not restore file 0001.history from archive
[HACKERS] the un-vacuumable table
I found this error message in my log files repeatedly: Error: failed to re-find parent key in ledgerdetail_2008_03_idx2 for deletion target page 64767 I though hmm, that index looks broken. I'd better re-create it. So, I dropped the index and then tried to create a new one to replace it. Which completely locked up the backend that was running the CREATE TABLE. I ran truss against the backend in question and it didn't register anything (except signals 2 and 15 when I tried to cancel the query and kill the backend respectively). I eventually had to restart the database to get the CREATE INDEX process to go away (well, to release that big nasty lock). I then tried to do a VACUUM FULL, but it didn't complete after more than 2 hours. I cancelled that and tried a CLUSTER in the hopes that it might work a little faster. It did the exact same thing as the create index command: completely locked up the backend. So, I'm wondering what if anything I can do to get that table cleaned up. Running 8.1.11 on FreeBSD 6.2. Anyway, the current plan is to drop the table and reload it from backup. I'm posting here in case there's interest in gathering some forensic data or a clever suggetion about how I can recover this situation or even some ideas about what's causing it. Andrew
Re: [HACKERS] the un-vacuumable table
On Wed, Jun 25, 2008 at 2:58 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Andrew Hammond wrote: I found this error message in my log files repeatedly: Error: failed to re-find parent key in ledgerdetail_2008_03_idx2 for deletion target page 64767 I though hmm, that index looks broken. I'd better re-create it. So, I dropped the index and then tried to create a new one to replace it. Which completely locked up the backend that was running the CREATE TABLE. I ran truss against the backend in question and it didn't register anything (except signals 2 and 15 when I tried to cancel the query and kill the backend respectively). I eventually had to restart the database to get the CREATE INDEX process to go away (well, to release that big nasty lock). What kind of an index is it? Does SELECT COUNT(*) from table work? After the restart I did a count(*) and it worked. A little under 13m rows. So, sequential scans seem to work. posting here in case there's interest in gathering some forensic data or a clever suggetion about how I can recover this situation or even some ideas about what's causing it. Anyway, the current plan is to drop the table and reload it from backup. I'm Yes, please take a filesystem-level backup right away to retain the evidence. Well, I've already burned our downtime allowance for this month, but we do a regular PITR type backup which hopefully will be sufficient to replicate the problem. Could you connect to the hung backend with gdb and get a stacktrace? The backend is no longer hung (two restarts later). I'll try to reproduce this problem on my workstation (same binary, same OS, libraries etc) using the PITR dump. Andrew
[HACKERS] WAL file naming sequence definition
I'd confirmation on how WAL files are named. I'm trying to write a tool
which can tell me when we are missing a WAL file from the sequence. I
initially thought that the file names were monotonically incrementing
hexadecimal numbers. This doesn't appear to be the case.
000101B700FD
000101B700FE
(there seem to be a whole bunch of missing filenames in the sequence here)
000101B8
000101B80001
This pattern repeats. I hunted through the code and discovered the following
in src/include/access/xlog_internal.h.
#define XLogFilePath(path, tli, log, seg) \
snprintf(path, MAXPGPATH, XLOGDIR /%08X%08X%08X, tli, log, seg)
So, the names are not a single hexadecimal number, but instead three of them
concatenated together. This macro is used eight times in
src/backend/access/xlog.c. It seems clear that the first number, tli, is a
TimeLineID. I wasn't completely clear on the behavior of log and seg until I
found the following, also in xlog_internal.h.
#define NextLogSeg(logId, logSeg) \
do { \
if ((logSeg) = XLogSegsPerFile-1) \
{ \
(logId)++; \
(logSeg) = 0; \
} \
else \
(logSeg)++; \
} while (0)
So, clearly log simply increments and seg increments until it gets up to
XLogSegsPerFile. Again, xlog_internal.h knows what that is.
/*
* We break each logical log file (xlogid value) into segment files of the
* size indicated by XLOG_SEG_SIZE. One possible segment at the end of each
* log file is wasted, to ensure that we don't have problems representing
* last-byte-position-plus-1.
*/
#define XLogSegSize ((uint32) XLOG_SEG_SIZE)
#define XLogSegsPerFile (((uint32) 0x) / XLogSegSize)
In src/include/pg_config.h.in, I see
/* XLOG_SEG_SIZE is the size of a single WAL file. This must be a power of 2
and larger than XLOG_BLCKSZ (preferably, a great deal larger than
XLOG_BLCKSZ). Changing XLOG_SEG_SIZE requires an initdb. */
#undef XLOG_SEG_SIZE
Then configure tells me the following
# Check whether --with-wal-segsize was given.
if test ${with_wal_segsize+set} = set; then
withval=$with_wal_segsize;
case $withval in
yes)
{ { echo $as_me:$LINENO: error: argument required for
--with-wal-segsize
echo $as_me: error: argument required for --with-wal-segsize option 2;}
{ (exit 1); exit 1; }; }
;;
no)
{ { echo $as_me:$LINENO: error: argument required for
--with-wal-segsize
echo $as_me: error: argument required for --with-wal-segsize option 2;}
{ (exit 1); exit 1; }; }
;;
*)
wal_segsize=$withval
;;
esac
else
wal_segsize=16
fi
case ${wal_segsize} in
1) ;;
2) ;;
4) ;;
8) ;;
16) ;;
32) ;;
64) ;;
*) { { echo $as_me:$LINENO: error: Invalid WAL segment size. Allowed
values a
echo $as_me: error: Invalid WAL segment size. Allowed values are
1,2,4,8,16,32,
{ (exit 1); exit 1; }; }
esac
{ echo $as_me:$LINENO: result: ${wal_segsize}MB 5
echo ${ECHO_T}${wal_segsize}MB 6; }
cat confdefs.h _ACEOF
#define XLOG_SEG_SIZE (${wal_segsize} * 1024 * 1024)
_ACEOF
Since I didn't specify a wal_segsize at compile time, it seems that my
XLogSegsPerFile should be
0x / (16 * 1024 * 1024) = 255
Which matches nicely with what I'm observing.
So, and this is where I want the double-check, a tool which verifies there
are no missing WAL files (based on names alone) in a series of WAL files
needs to know the following.
1) Timeline history (although perhaps not, it could simply verify all
existing timelines)
2) What, if any, wal_segsize was specified for the database which is
generating the WAL files
Am I missing anything? The format of .backup files seem pretty simple to me.
So I intend to do the following.
1) find the most recent .backup file
2) verify that all the files required for that .backup exist
3) see if there are any newer files, and
4) if there are newer files, warn if any are missing from the sequence
Would this be reasonable and is there any community interest in
open-sourcing the tool that I'm building?
Andrew
Re: [HACKERS] test
On Dec 12, 2007 11:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: test Does anybody see any value in having [EMAIL PROTECTED] be an alias for pgsql-hackers? No, but I see some mild irritation in having to modify my rules to tag a second address with the pgsql-hackers label. Andrew
Re: [HACKERS] PG 7.3 is five years old today
On Nov 29, 2007 11:11 AM, Ron Mayer [EMAIL PROTECTED] wrote: Robert Treat wrote: On Tuesday 27 November 2007 15:07, Simon Riggs wrote: On Tue, 2007-11-27 at 14:02 -0500, Tom Lane wrote: There has been some discussion of making a project policy of dropping support for old releases after five years. Should we consider formally instituting that? ... Perhaps we should ask for volunteers to maintain that branch? ... +1 to see if anyone else wants to take over management of the branch. I also think we should be a bit more generous on the EOL notice. One thing that could soften the blow is if the EOL notice mentions which commercial organizations will provide paid support for longer than the community does. I assume that's one of the benefits of going with the commercial support organizations? I bet there's plenty. Perhaps calling it an EOL is a mistake since the concept does not perfectly map between OSS and commercial software. I doubt there are any plans to trim the 7.3 branch from CVS and I imagine that the community will be happy to work with anyone who wishes to back-port patches, up to and perhaps including rolling their patch into CVS. This is very different from a traditional EOL. Perhaps Switching over to passive / user driven support is a better way to phrase this? We can of course emphasize the availability of commercial organizations that are willing to take over active support for anyone willing to pay for it. Do we have any numbers on the downloads of 7.3.x for the last few values of x? That might be a good indicator of how many people are actually following the upgrade path. Andrew
Re: [HACKERS] Release notes introductory text
On 10/11/07, Bruce Momjian [EMAIL PROTECTED] wrote: Kevin Grittner wrote: On Thu, Oct 11, 2007 at 3:04 PM, in message [EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] wrote: This release represents a major leap forward by adding significant new functionality and performance enhancements to productnamePostgreSQL/. Many complex ideas that normally take years to implement were added rapidly to this release by our development team. You do realize that this will make many managers very reluctant to adopt it before it has settled in for many months, right? If the goal is to provide fair warning of a high-than-usual-risk release, you've got it covered. No, that was not the intent. The indent was to say we got a lot done in one year. You have a suggestion? Well, a number of these were bumped from 8.2, so it might be a good idea to go with something like complex improvements long under development have come to fruition. For the reason suggested above, I don't think it's a great idea to try to emphasize the impressive speed with which some of these features have actually been implemented. I don't know that there's any credible way to tell people that although these things were done quickly they were also done with the exceptional care and attention to detail for which the PostgreSQL development community is famous. I really like your wording about how we're going beyond feature parity. That's exactly the kind of stance for which the World's Most Advanced Open Source Database ought to be aiming. As long as we can avoid the negative connotations associated with experimental. Andrew
Re: [HACKERS] database diagram
Wrong list. Try asking on the pgAdmin3 list, or maybe pgsql-general. Andrew On 9/16/07, Eretna Evrengizid [EMAIL PROTECTED] wrote: hi, I use postgresql 8.2 and I cant see database diagram on pgAdmin III, I think it can be . it is there , isnt it ? -- Be a better Heartthrob. Get better relationship answers http://us.rd.yahoo.com/evt=48255/*http://answers.yahoo.com/dir/_ylc=X3oDMTI5MGx2aThyBF9TAzIxMTU1MDAzNTIEX3MDMzk2NTQ1MTAzBHNlYwNCQUJwaWxsYXJfTklfMzYwBHNsawNQcm9kdWN0X3F1ZXN0aW9uX3BhZ2U-?link=listsid=396545433from someone who knows. Yahoo! Answers - Check it out.
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
On 9/13/07, Bruce Momjian [EMAIL PROTECTED] wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Is this item closed? No, it isn't. Please add a TODO item about it: * Prevent long-lived temp tables from causing frozen-Xid advancement starvation Sorry, I don't understand this. Can you give me more text? Thanks. s/long-lived/orphaned/ ? And possibly this means better orphan detection and removal. Andrew
Re: [HACKERS] More logging for autovacuum
On 8/7/07, Tom Lane [EMAIL PROTECTED] wrote: Peter Eisentraut [EMAIL PROTECTED] writes: But INFO is not shown by default. INFO is mostly a hack to try to emulate VACUUM VERBOSE's ancient behavior before we redesigned the elog levels. It's intended for controlling messages that should go to a client because the client asked for them, and usually should NOT go to the log. I think it's 100% inappropriate to use it for messages from a background process that has no client. Traditional log-level semantics aren't very rich. I think that's the real source of this problem: How do I get the info I need to tune auto-vacuum without swamping my system with log IO. While the following isn't production ready, it seems to have some really good ideas. http://log4c.sourceforge.net/ log4c is inspired by log4j, which seems to have become the defacto standard tool for logging in the enterprise java world. Andrew
Re: [HACKERS] Bugtraq: Having Fun With PostgreSQL
On 6/25/07, Tom Lane [EMAIL PROTECTED] wrote: Andrew Hammond [EMAIL PROTECTED] writes: On 6/25/07, Tom Lane [EMAIL PROTECTED] wrote: The normal build process for any open-source package is ./configure make sudo make install ... now what? OK, time to read the manual ... Since they presumably don't know about initdb yet, yeah, I figure they'll be reading the manual. We already talk about how to initdb. It seems reasonable to have the manual talk about how to initially connect to your secure by default database and create a non-superuser working user. +1 on having such a discussion in the manual (someone else suggested that already IIRC). But I'm not seeing what a configure flag brings to the party. Like Andrew Sullivan said above, if we want to achieve the dubious goal of being secure by default this seems like the least invasive way to change the process so that we can be buzzword compliant. Andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Bugtraq: Having Fun With PostgreSQL
On 6/25/07, Tom Lane [EMAIL PROTECTED] wrote: Andrew Sullivan [EMAIL PROTECTED] writes: On Mon, Jun 25, 2007 at 01:31:52PM -0400, Tom Lane wrote: Why is that better than the initdb-time option we already have? Locking down options earlier rather than later is usually not a win. Like I said, I don't actually think it _is_ better. But it would solve the problem that some people think it's a bad thing that you run superuser-type commands without reading the manual, and then get a badly-secured system. (The idea here, incidentally, is not to replace the initdb-time option, but to set the default of the initdb command.) But, per previous discussion, the people that would be affected are only the ones building from source. If they didn't read the manual for initdb (nor notice the warning it puts out about trust auth), they *certainly* didn't look for any nonstandard configure options. The normal build process for any open-source package is ./configure make sudo make install ... now what? OK, time to read the manual ... Since they presumably don't know about initdb yet, yeah, I figure they'll be reading the manual. We already talk about how to initdb. It seems reasonable to have the manual talk about how to initially connect to your secure by default database and create a non-superuser working user. I like the idea of it being a configure flag, it seems the least invasive way to do it. Andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] one click install?
The problem here is that there aren't really very many defined defaults, or that these defaults vary (sometimes greatly) between the different flavors of UNIX. For example, please tell me: 1) Where should PGDATA default to? 2) How do you want to handle logging output from the postmaster? There are plenty of options... 3) Where should those log files get written? 4) For 1 and 3, will that support multiple major versions of PostgreSQL? (ie, can I have 8.2.latest and 8.1.latest installed at the same time) 5) How about multiple postmasters (on different ports)? I think that the community would be well served by standardizing on these things, at least for basic installations. On 6/11/07, Timasmith [EMAIL PROTECTED] wrote: Hi, I can probably figure it out on linux but I would like to do a one click install based upon defined defaults for the Postgresql database (creating it as a service and load my sql file which creates the database) - has anyone written such a how to? thanks Tim ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-www] [HACKERS] Avoiding legal email signatures
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 6/12/07, Tom Lane wrote: A more serious objection is that any automated tool would probably get it wrong sometimes, and strip important text. I vote 'lets not bother' Right. I agree with Josh's idea about mentioning list policies in the subscription confirmation message, though. Why? If the legal mumbo-jumbo has already got some precedence as being un-enforcable (even if it's only in a handful of jurisdictions), why give it even a patina of credibility by addressing it in a policy? Saying that it's not applicable here implies that is is applicable elsewhere. To quote Ghandi first they laugh at you, then they ignore you, then they fight you, then you win. I say we stick with the laughing. To that end, I propose should have a policy about being pelted with scathing sarcasm when the signal to boilerplate ratio drops below 10:1. Andrew -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iD8DBQFGbxln+zlEYLc6JJgRAuaNAJsECSRrgIqR1f5c15P7OszVa34lVgCghWSb io55WHyChKGQVHCQ9R+z2ec= =KNyQ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] one click install?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 6/12/07, Andrej Ricnik-Bay wrote: On 6/13/07, Andrew Hammond wrote: The problem here is that there aren't really very many defined defaults, or that these defaults vary (sometimes greatly) between the different flavors of UNIX. For example, please tell me: 1) Where should PGDATA default to? 2) How do you want to handle logging output from the postmaster? There are plenty of options... 3) Where should those log files get written? 4) For 1 and 3, will that support multiple major versions of PostgreSQL? (ie, can I have 8.2.latest and 8.1.latest installed at the same time) 5) How about multiple postmasters (on different ports)? Exactly :} ... all very good points... and then there's still the ownerships of processes and directories/files, and their perms. And integration with the init-scripts. And how e.g. the environment variables for users should be handled. I think that the community would be well served by standardizing on these things, at least for basic installations. But whose decision should that be? The postgres' developers? I think that the defaults that the configure script suggests are quite sane, and happily use them in my Slackware installations. They're reasonable for a system which only wants a single version of the binaries installed at any given time. Generally I want to have at least two binaries on a production server at any given time: the one I'm running and either the one I'm upgrading to or the one I just upgraded from. Adding slony into the mix makes things even more complicated along those lines. Linux File system Hierarchy standards? Which major distro(s)? And what about the BSDs (or the commercial Unices supported)? I think a cage match would be a good way to settle this, and we could use money collected by selling the even to pay-per-view to fund development of Optimizer Hints. Seriously though, just having some suggestions about where these things belong in the docs wouldn't hurt and might actually lead to some convergence. And while at it: who would define what a basic installation is? :) I'd be willing to take a stab at that one (since it's pretty easy). A basic installation is any install where the person or software doing the install doesn't care about anything more than getting postgres running (NB: no mention of version numbers, performance requirements, or... well... anything a serious use would care about). Andrew -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iD8DBQFGbxz3+zlEYLc6JJgRAnEQAJ9o24X5zzn6CK05G3DpBF2j5ckQiwCginrc SjIbaI5I80rzWpicvtQR4Yo= =R4hG -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Selecting a constant question: A summary
On 6/12/07, Josh Berkus [EMAIL PROTECTED] wrote: Tom, What's the point? You keep reminding us that your code is middleware that can't assume anything much about the queries you're dealing with. Therefore, I see no real value in fixing up one corner case. Your argument about space allocation falls to the ground unless we can provide a guaranteed, and usefully tight, upper bound on the column width in *every* situation. If we cannot (which we can't), you're still going to need those client-side kluges. Hmmm? I thought that Dann was just talking about constants, and not column results. Am I confused? BTW, the reason I'm resistant to even thinking about this is that Postgres is designed as an extensible system. Trying to do what you want is not a matter of fixing literal constants and concatenation and one or two other places --- it's a matter of imposing a new and potentially hard-to-meet requirement on every datatype under the sun, including a lot of user-written code that we don't control and would break by adding such a requirement. So it's not even likely that we'd think very hard about making this work, let alone actually do it. I'd think it would be possible to do this in an abstract way ... having a DisplayLength() call for each data type and value. That would require casting the constant, though, or computing all uncast constants as text. The simplest formulation of this problem appears to be that constant strings that are uncast are treated as type unknown. The connx guys seem to think that they should be implicitly cast to char(n) where n is the length of the string. Is that a reasonable description, or are you guys looking for something more general? If you're just talking about the strings, then here are the thoughts I've gleaned from the preceding thread. - This makes possible some performance tweaks for drivers - It achieves spec compliance (albeit for a stupid part of the spec) - Implicit casting of unknown to char(n) or anything else seems rather sketchy to me, but I can't see any specific objection, except that... - I don't know when the right time to do the cast is. And doing it too early seems obviously wrong. - This only helps in corner case of string constants that are 1. not already cast and 2. not manipulated in any way And that seems like a very small corner case with little or no practical use. I guess if you have some code that turns query output into some flavor of pretty-print, it'd make sense to have a constant column as output of a CASE statement or something. - The corner case must already be correctly handled by the general case for arbitrary sized text, or alternatively phrased: there is no way to conform to the standard while supporting arbitrary sized text. Unless you're willing to pay the cost of scanning twice, or maintaining biggest entry data for each variable length column. - I don't know how much effort it would require to implement this, nor how much complexity it would add to the code base. Clearly both of these would be non-zero values. Given the above, I agree with Tom: this seems like corner case where the returns are marginal at best, compared to the cost to implement and maintain. Is there something I'm getting wrong in this summary? Andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
On 6/7/07, Jim C. Nasby [EMAIL PROTECTED] wrote: On Mon, Jun 04, 2007 at 11:04:26AM -0400, Alvaro Herrera wrote: The launcher is set up to wake up in autovacuum_naptime seconds at most. So if the user configures a ridiculuos time (for example 86400 seconds, which I've seen) then the launcher would not detect the postmaster death Is there some threshold after which we should have PostgreSQL emit a warning to the effect of autovacuum_naptime is very large. Are you sure you know what you're doing? Yeah, I've seen people set that up with the intention of now autovacuum will only run during our slow time!. I'm thinking it'd be worth mentioning in the docs that this won't work, and instead suggesting that they run vacuumdb -a or equivalent at that time instead. Thoughts? Hmmm... it seems to me that points new users towards not using autovacuum, which doesn't seem like the best idea. I think it'd be better to say that setting the naptime really high is a Bad Idea. Instead, if they want to shift maintenances to off hours they should consider using a cron job that bonks around the pg_autovacuum.vac_base_thresh or vac_scale_factor values for tables they don't want vacuumed during operational hours (set them really high at the start of operational hours, then to normal during off hours). Tweaking the enable column would work too, but they presumably don't want to disable ANALYZE, although it's entirely likely that new users don't know what ANALYZE does, in which case they _really_ don't want to disable it. This should probably be very close to a section that says something about how insufficient maintenance can be expected to lead to greater performance issues than using autovacuum with default settings. Assuming we believe that to be the case, which I think is reasonable given that we are now defaulting to having autovacuum enabled. Andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Best Practice for running vacuums during off hours WAS Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 6/7/07, Matthew T. O'Connor wrote: Tom Lane wrote: Andrew Hammond writes: Hmmm... it seems to me that points new users towards not using autovacuum, which doesn't seem like the best idea. I think it'd be better to say that setting the naptime really high is a Bad Idea. It seems like we should have an upper limit on the GUC variable that's less than INT_MAX ;-). Would an hour be sane? 10 minutes? This is independent of the problem at hand, though, which is that we probably want the launcher to notice postmaster death in less time than autovacuum_naptime, for reasonable values of same. Do we need a configurable autovacuum naptime at all? I know I put it in the original contrib autovacuum because I had no idea what knobs might be needed. I can't see a good reason to ever have a naptime longer than the default 60 seconds, but I suppose one might want a smaller naptime for a very active system? That's a good question. I can't see any reason for a naptime longer than 60 seconds either. I think very large naptime settings are a symptom of another issue: what's the Right Way to defer vacuums until off hours? Is that even a desirable thing anymore? I don't think it is in the majority of cases. I originally thought that this was more of a Best Practices issue (ie, fix in the docs, not the code), but now I'm wondering if there's much call for supporting the idea of being more aggressive with vacuums at different times of the day / week / month. Anyone? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iD8DBQFGaIdN+zlEYLc6JJgRAiNFAJ49CQwiTVxWhXNeOzIBABLN5LZY3wCfUj/W ZLakjPyRVwOijaB6keS3ld8= =Hg/X -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Command tags in create/drop scripts
On Jun 5, 9:19 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Zdenek Kotala wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? I don't think so; there is no demand from anybody but Zdenek to remove those programs. Has it ever even come up before? Personally I found really strange to have createuser and createdb shipped by Postgres when I started using it. I just didn't complain. +1. Given the prevalence of the pg_foo convention, those names are clunky. So is initdb. I'm less creative than Zdenek, so I'd suggest simply renaming to pg_createuser and friends with the same command line options as the originals. Have the binaries check $0 and emit a warning about using the deprecated name to STDERR if called by a name that doesn't have the pg_ prefix. Default to symlinking the old names for backwards compatibility until 9.0. Andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] interval / interval - double operator
On 5/18/07, Andrew Hammond [EMAIL PROTECTED] wrote:
On 5/17/07, Tom Lane [EMAIL PROTECTED] wrote:
Andrew Hammond [EMAIL PROTECTED] writes:
On 5/17/07, Tom Lane [EMAIL PROTECTED] wrote:
What are the grounds for defining it that way rather than some other
way?
The only alternative that came to mind when I wrote it was using a
numeric
instead of float.
No, I'm wondering what's the justification for smashing it to a single
number at all, when the inputs are three-field values. Interval divided
by float doesn't produce just a float, for example.
I think I see what you're getting at here. '1 month' / '1 day' could
return a number of reasonable values depending on how many days are in the
month (28 to 31) and on how many hours are in a day (generally 24, but can
be 23 or 25 for DST adjustments). The definition above simply assumes that
EXTRACT(epoch...) does the Right Thing. Hmmm. I'm at a loss for the right
way to solve this. It seems very reasonable to want to divide intervals by
intervals (how many nanocenturies in a fortnight?), but I'm at a loss for
how to do that correctly. I'll read the code from EXTRACT(epoch...) and see
what happening there.
Ok, I've been hunting through src/backend to try and find the code for
EXTRACT(epoch ...). I found EXTRACT in src/backend/parser/gram.y, which
seems like a reasonable place to start.
| EXTRACT '(' extract_list ')'
{
FuncCall *n = makeNode(FuncCall);
n-funcname = SystemFuncName(date_part);
n-args = $3;
n-agg_star = FALSE;
n-agg_distinct = FALSE;
n-location = @1;
$$ = (Node *)n;
}
Which got me looking for date_part. But that only seems to be in the
gram.y file, include/catalog/pg_proc.h and the test suite. The pg_proc.h
stuff looks pretty interesting, but to decipher it, I figured I need to read
up on SystemFuncName(). So I grepped for SystemFuncName(). It turns out to
be a wrapper around list_make2(), which is part of the linked list package.
Then I checked out makeNode(), which is a wrapper around newNode(), which in
turn is memory allocation stuff. At this point I'm kind of lost. I'm pretty
sure that the next thing I need to hunt up is in the parser, but I don't
know where to look.
Can anyone please tell me what is the right way to chase down the actual
code that implements EXTRACT(epoch ...)? (please note that I'm not asking
where that code is, but how to find it.) Or even better, is there a web page
or other document someone can give me a pointer to?
Andrew
Re: [HACKERS] interval / interval - double operator
On 5/17/07, Tom Lane [EMAIL PROTECTED] wrote: Andrew Hammond [EMAIL PROTECTED] writes: On 5/17/07, Tom Lane [EMAIL PROTECTED] wrote: What are the grounds for defining it that way rather than some other way? The only alternative that came to mind when I wrote it was using a numeric instead of float. No, I'm wondering what's the justification for smashing it to a single number at all, when the inputs are three-field values. Interval divided by float doesn't produce just a float, for example. I think I see what you're getting at here. '1 month' / '1 day' could return a number of reasonable values depending on how many days are in the month (28 to 31) and on how many hours are in a day (generally 24, but can be 23 or 25 for DST adjustments). The definition above simply assumes that EXTRACT(epoch...) does the Right Thing. Hmmm. I'm at a loss for the right way to solve this. It seems very reasonable to want to divide intervals by intervals (how many nanocenturies in a fortnight?), but I'm at a loss for how to do that correctly. I'll read the code from EXTRACT(epoch...) and see what happening there. Andrew
[HACKERS] interval / interval - double operator
Yet another potential addition to the family of operators. Some guy was asking for it on IRC so... CREATE OR REPLACE FUNCTION interval_over_interval(interval, interval) RETURNS float STRICT IMMUTABLE LANGUAGE sql AS $$ SELECT extract(epoch from $1)::float / extract(epoch from $2); $$; CREATE OPERATOR / ( leftarg = interval , rightarg = interval , procedure = interval_over_interval ); ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] interval / interval - double operator
On 5/17/07, Tom Lane [EMAIL PROTECTED] wrote: Andrew Hammond [EMAIL PROTECTED] writes: Yet another potential addition to the family of operators. Some guy was asking for it on IRC so... CREATE OR REPLACE FUNCTION interval_over_interval(interval, interval) RETURNS float STRICT IMMUTABLE LANGUAGE sql AS $$ SELECT extract(epoch from $1)::float / extract(epoch from $2); $$; What are the grounds for defining it that way rather than some other way? The only alternative that came to mind when I wrote it was using a numeric instead of float. I couldn't see why a numeric with some arbitrary precision / scale was particularly better than just using a double precision. There's already an interval_div function in the catalog which take an interval and a double precision and returns an interval, so using floating point math already has precedent. I figured that if I went with numeric, I'd also have to have a pretty good reason to change the existing operator or it'd inconsistent. Since float (without parameters) is both shorter to type and appears to be the same as double precision (at least according to the docs), my innate lazy streak went that way. Am I missing something obvious? Andrew
Re: [HACKERS] pg_comparator table diff/sync
On May 11, 1:16 pm, Erik 2.0 [EMAIL PROTECTED] wrote: Is pg_comparator the only project out there that does what it does? I tried patching it, and it seems OK, but I'm not terribly confident in my patch. I'm hoping someone will tell me there's a great table- driven rsync out there that everyone uses and I just don't know about. Slony? But perhaps I'm not understanding what pg_comparator does. Andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Feature lists for 8.3 and 8.4
On May 8, 7:16 am, [EMAIL PROTECTED] (Abraham, Danny) wrote: Hi, I am migrating from Sybase to Postgres. 3. Who is the guy to ask about a feature like startup migrate in ORACLE? You could check out EnterpriseDB, which is based on Postgres and provides an Oracle compatibility layer. That gives you room to breath while you migrate to a PostgreSQL native implementation. I've never used it, but I hear good things. Andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] TODO idea - implicit constraints across child tables with a common column as primary key (but obviously not a shared index)
If you have a table with a bunch of children, and these children all have a primary key which is generated from the same sequence, assuming that you're partitioning based on date (ie, this is a transaction record table), it would be nice if the planner could spot that all tables have a primary key on a column used as a join condition, check the min / max to see if there is overlap between tables, then apply CBE as if constraints existed. This strikes me as a pretty common situation, certainly we're seeing it here. Andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] temporal variants of generate_series()
I've written the following function definitions to extend generate_series to support some temporal types (timestamptz, date and time). Please include them if there's sufficient perceived need or value. -- timestamptz version CREATE OR REPLACE FUNCTION generate_series ( start_ts timestamptz , end_ts timestamptz , step interval ) RETURNS SETOF timestamptz AS $$ DECLARE current_ts timestamptz := start_ts; BEGIN IF start_ts end_ts AND step INTERVAL '0 seconds' THEN LOOP IF current_ts end_ts THEN RETURN; END IF; RETURN NEXT current_ts; current_ts := current_ts + step; END LOOP; ELSIF end_ts start_ts AND step INTERVAL '0 seconds' THEN LOOP IF current_ts end_ts THEN RETURN; END IF; RETURN NEXT current_ts; current_ts := current_ts + step; END LOOP; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; -- date version CREATE OR REPLACE FUNCTION generate_series ( start_ts date , end_ts date , step interval ) RETURNS SETOF date AS $$ DECLARE current_ts date := start_ts; BEGIN IF start_ts end_ts AND step INTERVAL '0 seconds' THEN LOOP IF current_ts end_ts THEN RETURN; END IF; RETURN NEXT current_ts; current_ts := current_ts + step; END LOOP; ELSIF end_ts start_ts AND step INTERVAL '0 seconds' THEN LOOP IF current_ts end_ts THEN RETURN; END IF; RETURN NEXT current_ts; current_ts := current_ts + step; END LOOP; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; -- time version CREATE OR REPLACE FUNCTION generate_series ( start_ts time , end_ts time , step interval ) RETURNS SETOF time AS $$ DECLARE current_ts time := start_ts; BEGIN IF step INTERVAL '0 seconds' THEN LOOP-- handle wraparound first IF current_ts end_ts THEN EXIT; END IF; RETURN NEXT current_ts; current_ts := current_ts + step; END LOOP; LOOP IF current_ts end_ts THEN RETURN; END IF; RETURN NEXT current_ts; current_ts := current_ts + step; END LOOP; ELSIF step INTERVAL '0 seconds' THEN LOOP-- handle wraparound first IF current_ts end_ts THEN EXIT; END IF; RETURN NEXT current_ts; current_ts := current_ts + step; END LOOP; LOOP IF current_ts end_ts THEN RETURN; END IF; RETURN NEXT current_ts; current_ts := current_ts + step; END LOOP; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Finding Queries that have been done on a DB
On Apr 3, 5:37 pm, [EMAIL PROTECTED] (4wheels) wrote: Hello all! This is my first post! I am interested in finding out what queries have been made against a particular database in postgres. The version of Postgres is 8.0 running on Mandrake 10. The queries are made by client computers over the network. What steps must I take to accomplish such a task? Is this even at all a feasible goal? It's trivial, as you would know if you had read the manual. Of course it appears that not only were you too lazy to read the manual, you were too lazy to read the charter of this newsgroup before posting to it. I have copied it below for your convenience. The PostgreSQL developers team lives here. Discussion of current development issues, problems and bugs, and proposed new features. If your question cannot be answered by people in the other lists, and it is likely that only a developer will know the answer, you may re-post your question in this list. You must try elsewhere first! Google groups doesn't find your question on any of the other pgsql-* lists. Perhaps you should try pgsql-novice or -general? Andrew ---(end of broadcast)--- TIP 1: 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] [pgsql-www] Help: reading the source
1) Wrong list, you want -hackers. 2) Did you have a specific question based on what you have read or are you asking for suggested reading? On 3/29/07, 李宁 [EMAIL PROTECTED] wrote: Dear everyone, I'm a college student,now I am doing the topic about the postgresql storage management as my thesis. I wish I can get help here about where should I begin. I am reading the book Database System Implementation and reading the source code,mainly in storage/smgr,I hope someone can give me some suggestion or instruction. Thank you! Sincerely yours ninglee ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] My honours project - databases using dynamically attached entity-properties
On Mar 11, 12:47 pm, [EMAIL PROTECTED] (Josh Berkus) wrote: No matter how much Heikki hates them, I think he'd agree that EAV tables are better than having the application execute DDL at runtime. EAV moves the structure that is typically in the design of the tables into the contents of the tables. With an EAV database you have effectively destroyed the semantic difference between DML and DDL. I'm willing to concede that there may be situations where EAV is actually the right answer to a design problem. I have yet to encounter one, but Josh has more experience, and more varied experience than I do. To me, EAV is a perfect example of ignoring the YAGNI principal. http://c2.com/xp/YouArentGonnaNeedIt.html What I'd basically like to know is a) Is this problem worth solving? I think you're solving a symptom, not the root cause of the problem. Clarify the root cause, and then you have an interesting project. Andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] My honours project - databases using dynamically attached entity-properties
On 3/12/07, Richard Huxton dev@archonet.com wrote: Josh Berkus wrote: I really don't see any way you could implement UDFs other than EAV that wouldn't be immensely awkward, or result in executing DDL at runtime. What's so horrible about DDL at runtime? Obviously, you're only going to allow specific additions to specific schemas/tables, but why not? More to the point, since EAV is effectively smearing the semantics of DDL with DML, what, if any of the arguments against doing DDL at runtime don't apply equally to EAV? Well, aside from being able to say hey, I'm not executing DDL at runtime. :) I see the issue as one of cost: it's substantially harder to implement DDL at runtime than to work around the problem using EAV. If that analysis is reasonable, then it would be a very interesting research project to see how to cut down that cost of implementation. Andrew ---(end of broadcast)--- TIP 1: 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] help required regarding queryin postgis database from google maps
On Feb 25, 9:34 am, [EMAIL PROTECTED] (Andrew Dunstan) wrote: Phani Kishore wrote: hi ! i think u people could probably help me i how to query the pgsql/postgis from google maps api to display the markers on the google maps which are stored in the postgis database. Phani Kishore Tata Consultancy Services Mailto: [EMAIL PROTECTED] Website:http://www.tcs.com This list is not about how to use postgres. Please ask in the correct forum. pgsql-general, would be a good place to start with. Andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SCMS question
On Feb 22, 9:49 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Andrew Dunstan wrote: It's also fair to say that this is a subject about which we usually get much more noise from partisans of other SCM systems than from the relatively small number of people who actually have to maintain the postgresql code. (As Tom has pointed out, our biggest pain point is the occasional wish to move things across directories.) While annoying, this is something that really only a problem for the CVS maintainer (and anyone who's stuck waiting for the maintainer to shuffle stuff). I suggest that while it would be nice to solve this problem, it's more of a bonus side-effect rather than a significant benefit to changing SCMs. For example, currently if I have a patch and somebody reviews it and opines that I have to change foo to bar; then I resubmit the patch. How do they find out whether I actually changed foo to bar? Currently there are two alternatives: 1. trust that I did it 2. review the whole patch again With a distributed SCM, I could just patch the code and commit a new revision in my branch to just change foo to bar, and then the reviewer can check that I truly did what he wanted. Another easy thing to do is to track the current HEAD in a branch of mine. Keeping patches up to date in parallel with other developments is easier. Alvaro's arguments above suggest a significant, ongoing pay-off for everyone who writes patches, everyone who reviews patches and everyone who has to maintain separate patches. I won't attempt to quantify this pay-off, but it looks pretty significant to me. Andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Plan invalidation design
On Feb 18, 9:35 am, [EMAIL PROTECTED] (Tom Lane) wrote: Russell Smith [EMAIL PROTECTED] writes: If you replan and immutable function, aren't you possibly messing up a functional index that is using the old function. Hey, if you change an immutable function that has an index, you are in trouble already. True. While I agree that if you change an immutable function used by an index, your index will break, I do not understand how re-planning it will cause problems. Is the worry that the index will not pick up on the new plan? Andrew ---(end of broadcast)--- TIP 1: 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] Missing directory when building 8.2.3-base
On Feb 12, 5:16 pm, [EMAIL PROTECTED] (Joshua D. Drake) wrote: Peter Eisentraut wrote: Jeroen T. Vermeulen wrote: Is this a known problem? Is there any test procedure that builds the base distribution before release? Most of the core team is convinced that the postgresql-foo tarballs are useless, but Marc insists on keeping them. But since they are nearly useless, no one tests them, so it is not surprising that they don't work. Why do we keep them again? I can't recall at any point in the life of CMD us ever using the -foo tarballs. Not to mention they just take up space. Let's dump them. The FreeBSD database/postgres* ports depend on them. Which is probably why Marc insists on keeping them. Andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Missing directory when building 8.2.3-base
The FreeBSD database/postgres* ports depend on them. Which is probably why Marc insists on keeping them. Well, I think that's a horrid dependency to have. Other packaging systems (e.g. the RPM builds) seem quite able to split up a single unified build into multiple packages - what can't FBSD? What would we do if some other packaging system wanted to ask us for a different split? I am not particularly impressed with the FreeBSD database/postgres* ports. The emphasis on splitting postgres into -server -client and - contrib packages, while in keeping with the rest of the ports collection seems misplaced when you consider that they offer no mechanism (at least of which I am aware) to support multiple versions of the binary. I can't imagine a situation where I would care about having separate packages, aside from being annoyed that some of the more valuable stuff in contrib is not built / installed. Does anyone operate a production environment without at least pgstattuple? On the other hand, every production server I've worked on has had at least 2 binary packages installed and ready for use at all times (the current build and the last production build in case we're forced to roll back). In many cases servers I've worked on have had multiple back-ends running, often with different binaries. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Missing directory when building 8.2.3-base
On 2/13/07, Peter Eisentraut [EMAIL PROTECTED] wrote: Andrew Hammond wrote: The FreeBSD database/postgres* ports depend on them. Which is probably why Marc insists on keeping them. I hesitate to believe that seeing that they don't actually work, whereas we have heard no complaints that the FreeBSD ports don't work. I am not convinced anyone who is serious about postgresql uses the ports for reasons outlined in a prior post. However, they certainly are used in the ports (FreeBSD 6.2, ports cvsup'd about 2 mins ago): Script started on Tue Feb 13 19:25:28 2007 [EMAIL PROTECTED] /usr/ports/databases/postgresql82-server]# make === BACKUP YOUR DATA! = As always, backup your data before upgrading. If the upgrade leads to a higher minor revision (e.g. 7.3.x - 7.4), a dump and restore of all databases is required. This is *NOT* done by the port! Press ctrl-C *now* if you need to pg_dump. === === Found saved configuration for postgresql-server-8.2.3 = postgresql-base-8.2.3.tar.bz2 doesn't seem to exist in /usr/ports/distfiles/postgresql. = Attempting to fetch from ftp://ftp8.us.postgresql.org/postgresql/source/v8.2.3/. postgresql-base-8.2.3.tar.bz2 100% of 8301 kB 619 kBps 00m00s = postgresql-opt-8.2.3.tar.bz2 doesn't seem to exist in /usr/ports/distfiles/postgresql. = Attempting to fetch from ftp://ftp8.us.postgresql.org/postgresql/source/v8.2.3/. postgresql-opt-8.2.3.tar.bz2 100% of 163 kB 171 kBps = postgresql-test-8.2.3.tar.bz2 doesn't seem to exist in /usr/ports/distfiles/postgresql. = Attempting to fetch from ftp://ftp8.us.postgresql.org/postgresql/source/v8.2.3/. postgresql-test-8.2.3.tar.bz2 100% of 962 kB 254 kBps === Extracting for postgresql-server-8.2.3 = MD5 Checksum OK for postgresql/postgresql-base-8.2.3.tar.bz2. = SHA256 Checksum OK for postgresql/postgresql-base-8.2.3.tar.bz2. = MD5 Checksum OK for postgresql/postgresql-opt-8.2.3.tar.bz2. = SHA256 Checksum OK for postgresql/postgresql-opt-8.2.3.tar.bz2. = MD5 Checksum OK for postgresql/postgresql-test-8.2.3.tar.bz2. = SHA256 Checksum OK for postgresql/postgresql-test-8.2.3.tar.bz2. -- snip -- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: Commit timestamp
On Feb 7, 8:12 pm, [EMAIL PROTECTED] (Bruce Momjian) wrote: Jan Wieck wrote: On 2/7/2007 10:35 PM, Bruce Momjian wrote: I find the term logical proof of it's correctness too restrictive. It sounds like some formal academic process that really doesn't work well for us. Thank you. My intuition is that it might be possible to prove that _nothing_ can provide guaranteed ordering when there is disconnected operation. However, I think that the clock based ordering Jan has described could provide _probable_ ordering under disconnected operation. I can see three variables in the equation that would determine the probability of correctness for the ordering. 1) clock drift rate between disconnected clusters 2) disconnection time 3) transaction rate on the tables, or even rows involved There are probably more. I think that if Jan implements what he's described then a very interesting follow-up would be to do the statistical analysis necessary to quantify the risk of incorrect ordering while disconnected. (I've got x ms/ms relative clock drift, and y tps. How long can I run disconnected before falling under 99.999% probability of correctly ordered transactions?) No, I _now_ understand the use case, but when the patch was posted, the use case was missing. I would like to see a repost with the patch, and a description of its use so we can all move forward on that. An additional use case for an on-commit timestamp is in the analysis of billing transactions in highly concurrent systems. For example, imagine your billing period is monthly and you have transactions which start before and end after the end-of-month. Having the on-commit timestamp for these transactions may help when attempting to reconcile between transactions and account activities. Andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] BuildFarm: Do we need another FreeBSD/amd64 member?
On Jan 19 2006, 9:36 pm, [EMAIL PROTECTED] (Larry Rosenman) wrote: Tom Lane wrote: Larry Rosenman [EMAIL PROTECTED] writes: I've got a fastFreeBSD/amd64 server available to run Buildfarm on. However, I see we already have a couple of others running it. My questions are: 1) do we need another one? 2) if yes, what options need coverage? Looks like we're fairly well covered onfreebsdalready. Are you willing to consider running some less-popular OS on it? Not particularly, as I want it to do other things. However, I might be willing To get VMWare running on it, and run something else in a VM. What were you thinking? FreeBSD 6.2 was just released. I don't see any coverage for it yet. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] FreeBSD 6.2 ./configure plays oddly when building 8.2.2 from tarball
I'm trying to build PostgreSQL 8.2.2 outside the ports system on a FreeBSD 6.2 amd64 box. The databases/postgresql81-server port builds 8.1.7 just fine on the same box. My configure fails. I'm new to FreeBSD so I expect I'm missing something pretty obvious. config.log follows. Line 2212 is very odd since it appears to be giving gcc the library prefix as a file name. This file contains any messages produced by compilers while running configure, to aid debugging if configure makes a mistake. It was created by PostgreSQL configure 8.2.2, which was generated by GNU Autoconf 2.59. Invocation command line was $ ./configure --prefix=/usr/local/pgsql822_sl126_amd64_FreeBSD62 --with-includes=/usr/local/include --with-libs=/usr/local/lib --enable-thread-saftey --with-openssl --enable-debug CC=gcc CFLAGS=-O3 -funroll-loops -m64 ## - ## ## Platform. ## ## - ## hostname = ahammond.ecoffice.experclick.com uname -m = amd64 uname -r = 6.2-RELEASE uname -s = FreeBSD uname -v = FreeBSD 6.2-RELEASE #0: Fri Jan 12 08:43:30 UTC 2007 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SMP /usr/bin/uname -p = amd64 /bin/uname -X = unknown /bin/arch = unknown /usr/bin/arch -k = unknown /usr/convex/getsysinfo = unknown hostinfo = unknown /bin/machine = unknown /usr/bin/oslevel = unknown /bin/universe = unknown PATH: /sbin PATH: /bin PATH: /usr/sbin PATH: /usr/bin PATH: /usr/games PATH: /usr/local/sbin PATH: /usr/local/bin PATH: /usr/X11R6/bin PATH: /home/ahammond/bin ## --- ## ## Core tests. ## ## --- ## configure:1408: checking build system type configure:1426: result: x86_64-unknown-freebsd6.2 configure:1434: checking host system type configure:1448: result: x86_64-unknown-freebsd6.2 configure:1458: checking which template to use configure:1558: result: freebsd configure:1700: checking whether to build with 64-bit integer date/time support configure:1732: result: no configure:1739: checking whether NLS is wanted configure:1774: result: no configure:1782: checking for default port number configure:1812: result: 5432 configure:2170: checking for C compiler version configure:2173: gcc --version /dev/null 5 gcc (GCC) 3.4.6 [FreeBSD] 20060305 Copyright (C) 2006 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. configure:2176: $? = 0 configure:2178: gcc -v /dev/null 5 Using built-in specs. Configured with: FreeBSD/amd64 system compiler Thread model: posix gcc version 3.4.6 [FreeBSD] 20060305 configure:2181: $? = 0 configure:2183: gcc -V /dev/null 5 gcc: `-V' option must have argument configure:2186: $? = 1 configure:2209: checking for C compiler default output file name configure:2212: gcc -O3 -funroll-loops -m64 conftest.c /usr/local/lib 5 /usr/local/lib: file not recognized: File format not recognized configure:2215: $? = 1 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] FreeBSD 6.2 ./configure plays oddly when building 8.2.2 from tarball
On Feb 8, 11:28 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Tom Lane wrote: I thought it might be coming from your --with-libs switch somehow, but when I add that to my configure command it does not change this output at all. Is it possible you've got environment variables (like CFLAGS) that configure might be picking up? A stout I'll bet it's $LIBS Bingo. I'll pay up next time I see you guys. Or you're welcome to drop by Santa Barbara area to collect. Andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 10 weeks to feature freeze (Pending Work)
On 2/6/07, Jim Nasby [EMAIL PROTECTED] wrote: On Feb 5, 2007, at 12:53 PM, Andrew Hammond wrote: On Jan 26, 2:38 pm, [EMAIL PROTECTED] (Tom Lane) wrote: Rick Gigger [EMAIL PROTECTED] writes: I thought that the following todo item just barely missed 8.2: Allow a warm standby system to also allow read-only statements [pitr] No, it's a someday-wishlist item; the work involved is not small. Slony1 has supported log-shipping replication for about a year now. It provides similar functionality. Not really 1) It's not possible for a PITR 'slave' to fall behind to a state where it will never catch up, unless it's just on inadequate hardware. Same isn't true with slony. I imagine that there are ways to screw up WAL shipping too, but there are plenty more ways to mess up slony. 2) PITR handles DDL seamlessly 3) PITR is *much* simpler to configure and maintain 4) You need 3 databases to do log shipping using slony1. An origin, a subscriber which generates the logs and obviously the log-replica. All of which is why I qualified my statement with similar. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 10 weeks to feature freeze (Pending Work)
On Jan 26, 2:38 pm, [EMAIL PROTECTED] (Tom Lane) wrote: Rick Gigger [EMAIL PROTECTED] writes: I thought that the following todo item just barely missed 8.2: Allow a warm standby system to also allow read-only statements [pitr] No, it's a someday-wishlist item; the work involved is not small. Slony1 has supported log-shipping replication for about a year now. It provides similar functionality. Andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] effective_cache_size vs units
Benny Amorsen wrote: TL == Tom Lane [EMAIL PROTECTED] writes: TL Anyone against making it case-insensitive, speak now or hold your TL peace. SI-units are inherently case-sensitive. The obvious example is that now you will allow people to specify an amount in millibytes, while interpreting it in megabytes. And as Peter points out, there may be a desire to support SI-type units in other parts of the database at some time in the future. It seems like a questionable idea to break with convention just for ease of use. You are trying to interpret valid input as misspellings based on context, and then you silently guess at what the user really meant. That's MySQL behaviour! I agree. But perhaps the solution instead of failing is to throw a warning to the effect of Not to be pedantic, but you said mb and millibits as a unit doesn't make sense in this context. Assuming you meant MB (MegaBits). and then start up. Generally I'm against guessing what the user really wants, but in this case, it seems pretty difficult to guess wrong. But either way I'm always dead set against _silently_ guessing. Andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Clarification needed
Indira Muthuswamy wrote: Can anyone of you help me in finding the datatype of a particular column in a table in Postgres? Thanks and Regards, M.Indira You're almost in the right place, but you'd be better off asking this question in the pgsql-general or perhaps pgsql-novice. This mailing list is for talking about modifications to the actual database engine. Drew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] would like to stop it auto-starting on boot on HP-UX IPF
1) This has nothing to do with hacking the internals of Postgres, which means you are asking on the wrong list. 2) This has little to do with the Postgres scripts, but instead has to do with the bootup scripts for the OS and or possibly some HP-UX package manager. It is unlikely that the Postgres community is the right one to ask about this, although there may be some HP-UX users around here. Have you looked for an HP-UX community? Drew Lee wrote: I have installed postgres. I would like to stop it auto-starting on boot but I am not sure where HP UX postgres keeps these auto start settings/scripts. I have searched everywhere. Can someone give me some guidence please. This is what keeps starting # ps -ef|grep postg sfmdb 2881 1594 0 19:20:02 ? 0:00 postgres: sfmdb LOGDB [local] id le sfmdb 1618 1617 0 19:18:09 ? 0:00 postgres: stats collector proces s sfmdb 1617 1594 0 19:18:09 ? 0:00 postgres: stats buffer process sfmdb 2329 1594 0 19:19:00 ? 0:00 postgres: sfmdb LOGDB [local] id le root 20809 978 1 09:47:17 pts/ta0:00 grep postg # Thanks Lee ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] BugTracker (Was: Re: 8.2 features status)
Kenneth Marshall wrote: RT is easy to setup/configure/use and works well with PostgreSQL as the backend. RT works with Postgres, but I wouldn't say well. All queries in RT are generated by a query generator due to a naive obsession with database independance. They've achieved database independance at the cost of all the queries being brain-dead. Fixing the query generator would be a pretty big job. Drew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] perl namespace for postgres specific modules?
I need to write a perl module which will parse a .pgpass file into a reasonable data-structure in memory. I may extend it later to go in the other direction (given a populated datastructure, write a .pgpass). The first question that came to mind is what namespace should I put this under? Is there any precedent for perl modules intended to support postgresql administration? If not, I suggest PostgreSQL::pgpass Thoughts? Drew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 8.2 features status
I am actually hoping that jabber.postgresql.org would help that in the long run. Jabber's ok, but why not go with SILC instead? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Replication Documentation
Markus Schiltknecht wrote: Hi, Andrew Hammond wrote: I can see value in documenting what replication systems are known to work (for some definition of work) with a given release in the documentation for that release. Five years down the road when I'm trying to implement replication for a client who's somehow locked into postgres 8.2 (for whatever reason), it would be very helpful to know that slony1.2 is an option. I don't know if this is sufficient justification. Please keep in mind, that most replication solutions (that I know of) are quite independent from the PostgreSQL version used. Thus, documenting which version of PostgreSQL can be used with which version of a replication system should better be covered in the documentation of the replication system. I would agree to this with the caveat that there needs to be something in the postgres documentation that points people to the various replication systems available. Otherwise you would have to update the PostgreSQL documentation for new releases of your favorite replication system - which seems to lead to confusion. Yeah, updating the docs based on other software releases would suck. How about what works with a given release at the time of the release? Perhaps this could be limited to a pointer to the docs for such replication systems, and maybe a very brief description (based on Chris' taxonomy)? Including a separate page on the history of postgres replication to date also makes some sense, at least to me. It should be relatively easy to maintain. I agree that having such a 'replication guide for users of PostgreSQL' is a good thing to have. But I think not much of that should be part of the official PostgreSQL documentation - mainly because the replication solutions are not part of PostgreSQL. Arguably, neither are most of the procedural languages in the Server Programming section of the documentation, and yet they're included. I agree that it's improtant to keep the documentation from getting cluttered up with stuff that's not part of PostgreSQL. However, I think the very fact so many people assume that there's no replication for PostgreSQL simply because it's not mentioned in the documentation shows that for many people replication is precieved as part of the dbms. Even a single page in the documentation wich consists of something along the lines of the following would help these folks find what they're looking for. There are a number of different approaches to solving the problem of replication, each with strengths and weaknesses. As a result, there are a number of different replication solutions available for PostgreSQL. To find out more, please refer to the website. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Replication Documentation
There are a number of different approaches to solving the problem of replication, each with strengths and weaknesses. As a result, there are a number of different replication solutions available for PostgreSQL. To find out more, please refer to the website. Well, that's what I've been talking about all along, and it has also been the resolution at the Toronto meeting. Great. Is the above text sufficient for the documentation then, or does anyone have a suggestion on how to say this better? Drew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Replication Documentation
Peter Eisentraut wrote: Alvaro Herrera wrote: I don't think this sort of material belongs directly into the PostgreSQL documentation. Why not? PostgreSQL documentation (or any product documentation) should be factual: describe what the software does and give advice on its use. This should be mostly independent of the external circumstances, because people will still read that documentation three or four years from now. The proposed text is, at least partially, journalistic: it evaluates competing ideas, gives historical and anecdotal information, reports on current events, and makes speculations about the future. That is the sort of material that is published in periodicals or other volatile media. I can see value in documenting what replication systems are known to work (for some definition of work) with a given release in the documentation for that release. Five years down the road when I'm trying to implement replication for a client who's somehow locked into postgres 8.2 (for whatever reason), it would be very helpful to know that slony1.2 is an option. I don't know if this is sufficient justification. Including a separate page on the history of postgres replication to date also makes some sense, at least to me. It should be relatively easy to maintain. If we do talk about replicatoin, then including a probably separate and presumably quite static page on the taxonomy of replication seems necessary. As Chris notes, the term replication by it'self is can mean quite a number of things. At the summit, we resolved, for precisely these reasons, to keep the journalistic parts on the web site, for clear separation from the shipped product and for easier updates (and for easier reference as well, because the PostgreSQL documentation is not the single obvious place to look for it) and refer to it from the documentation. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] column-level privilege
I am a PostgreSQL lover at China, I'd like to know when the column-level privilege can be added to a release version of PostgreSQL? and is there someone who is working on the problem? You can often achieve similar effects with VIEWs and RULEs / TRIGGERs. Drew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Progress bar updates
Neil Conway wrote: I would suggest starting with utility functions like index builds or COPY which would have to be specially handled anyways. Handling all optimizable queries in a single generic implementation seems like something to tackle only once the basic infrastructure is there and working for simple cases. Of course the estimates would be not much better than guesses. Estimating query progress for DDL should be reasonably doable, but I think it would require some hard thought to get even somewhat accurate estimates for SELECT queries -- and I'm not sure there's much point doing this if we don't at least have an idea how we might implement reasonably accurate progress reporting for every kind of query. We already have EXPLAIN ANALYZE. Perhaps the right way to do this is something that provides similar output. I could see something that looks like EXPLAIN for the parts that have not yet executed, something reasonable to show progress of the currently active part of the plan (current time, rows, loops), and EXPLAIN ANALYZE output for the parts which have been completed. I can see how this might lead to dynamically re-planning queries. Going backwards, perhaps there's something related to progress monitoring that could be taken from the TelegraphCQ work? Drew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] constraints, inheritance and _RETURN ON SELECT rules
I posted about this a couple of days ago, but the post was not
complete. Trying again:
-- suppose the following table exists
CREATE TABLE many_tables (
table_id text, -- defines which virtual table encoded
att0 text,
att1 text,
att2 text,
att3 text
);
-- with some example data
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('cat', '1', 'fudge', '0.08', null);
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('cat', '2', 'pepper', '0.44', null);
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('dog', '1', 'morgan', 'Golden Retriever', '7');
INSERT INTO many_tables (table_id, att0, att1, att2, att3)
VALUES ('bird', '1', 'boo', 'a grumpy parrot', 'Grey');
-- Goal:
-- 1) Allow incremental application migration to a relational schema
design.
-- 2) Improve performance, even for existing applications.
--
-- Method:
-- 1) Migrate the data out of many_tables into relational tables which
have
--appropriate data-types, constraints etc.
-- 2) Place rules on many_tables to support DML.
-- 3) Use inheritance + constraint to create separate child tables.
-- 4) Define _RETURN ON SELECT rule to viewify each child table with
data
--from the appropriate relational table.
CREATE TABLE cat (
cat_id INTEGER PRIMARY KEY -- was att0
, cat_name TEXT NOT NULL -- was att1
, aloofness NUMERIC(4,3) -- was att2
DEFAULT 1.0
CHECK (0.0 = aloofness AND aloofness = 1.0)
);
BEGIN;
INSERT INTO cat (cat_id, cat_name, aloofness)
SELECT CAST(att0 AS integer), att1, CAST(att2 AS numeric(4,3))
FROM many_tables WHERE table_id = 'cat';
DELETE FROM many_tables WHERE table_id = 'cat';
CREATE OR REPLACE RULE many_tables_cat_insert AS
ON INSERT TO many_tables
WHERE NEW.table_id = 'cat'
DO INSTEAD
INSERT INTO cat (cat_id, cat_name, aloofness)
VALUES ( CAST(NEW.att0 AS integer)
, NEW.att1
, CAST(NEW.att2 AS numeric(1,3))
-- gleefully ignore the other attributes
);
CREATE OR REPLACE RULE many_tables_cat_update AS
ON UPDATE TO many_tables
WHERE OLD.table_id = 'cat' AND NEW.table_id = 'cat'
DO INSTEAD
UPDATE cat
SET cat_id = CAST(NEW.att0 AS integer)
, cat_name = NEW.att1
, aloofness = CAST(NEW.att2 AS numeric(1,3))
WHERE cat_id = CAST(OLD.att0 AS integer);
CREATE OR REPLACE RULE many_tables_cat_delete AS
ON DELETE TO many_tables
WHERE OLD.table_id = 'cat'
DO INSTEAD
DELETE FROM cat
WHERE cat_id = CAST(OLD.att0 AS integer);
CREATE TABLE many_tables_cat (CHECK (table_id = 'cat')) INHERITS
(many_tables);
CREATE OR REPLACE RULE _RETURN AS
ON SELECT TO many_tables_cat DO INSTEAD
SELECT CAST('cat' AS text) AS table_id
, CAST(cat_id AS text)AS att0
, cat_nameAS att1
, CAST(aloofness AS text) AS att2
, CAST(null AS text) AS att3
FROM cat;
COMMIT;
-- test
SELECT * FROM cat; --ok
SELECT * FROM many_tables; -- oops!
ahammond=# CREATE TABLE many_tables_cat (CHECK (table_id = 'cat'))
INHERITS (many_tables);
CREATE TABLE
ahammond=# \d many_tables_cat
Table public.many_tables_cat
Column | Type | Modifiers
--+--+---
table_id | text |
att0 | text |
att1 | text |
att2 | text |
att3 | text |
Check constraints:
many_tables_cat_table_id_check CHECK (table_id = 'cat'::text)
Inherits: many_tables
ahammond=# CREATE OR REPLACE RULE _RETURN AS
ahammond-# ON SELECT TO many_tables_cat DO INSTEAD
ahammond-# SELECT CAST('cat' AS text) AS table_id
ahammond-# , CAST(cat_id AS text)AS att0
ahammond-# , cat_nameAS att1
ahammond-# , CAST(aloofness AS text) AS att2
ahammond-# , CAST(null AS text) AS att3
ahammond-# FROM cat;
CREATE RULE
ahammond=# \d many_tables_cat
View public.many_tables_cat
Column | Type | Modifiers
--+--+---
table_id | text |
att0 | text |
att1 | text |
att2 | text |
att3 | text |
View definition:
SELECT 'cat'::text AS table_id, cat.cat_id::text AS att0, cat.cat_name
AS att1, cat.aloofness::text AS att2, NULL::text AS att3
FROM cat;
ahammond=# SELECT * FROM many_tables;
ERROR: could not open relation 1663/16385/209728: No such file or
directory
Ideally, I think this should work as expected. I don't know for sure
how the constraint should fit into things, but I suspect that it should
remain and become more of an assertion. You're kind of stuck trusting
the DBA if the constraint refers to a column which doesn't even exist
in the source of the view.
Either that, or viewifying inherited tables should fail. Which is
probably the correct behaviour if the above can't reasonably be
supported.
Drew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
I have a client with the following EAV inspired schema. CREATE TABLE many_tables ( table_id text primary key,-- defines which virtual table is encoded attribute1 text, attribute2 text, attribute3 text, attribute4 text, ... ); I'd like to use a mix of constraint based paritioning, rules _and_views_ to implement a real schema underneath this mess, like the following. CREATE TABLE cat ( cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL, aloofness NUMERIC(1,3) DEFAULT 1.0 CHECK (0.0 = aloofness AND aloofness = 1.0) ); CREATE RULE many_tables_cat_insert AS ON INSERT TO many_tables WHERE table_id = 'cat' DO INSTEAD INSERT INTO cat (cat_id, cat_name, aloofness) VALUES ( CAST(attribute1 AS integer), attribute2, CAST(attribute3 AS numeric(1,3)) -- gleefully ignore the other attributes ); -- etc for UPDATE, and DELETE rules -- This part doesn't work CREATE VIEW many_tables_a (CHECK (table_id = 'cat')) INHERITS (many_tables) AS SELECT 'cat' AS table_id, CAST(cat_id AS text) AS attribute1, cat_name AS attribute2, CAST(aloofness AS text) AS attribute3, null AS attribute4, ... FROM cat; So, I guess I'm stuck doing the UNION ALL approach in this instance. This won't get me the partitioning win, nor the flexibility and cleanliness of design that I'd get with inheritance. As far as I can see, supporting the above would it mean adding inheritance and constraint support to views. Does anyone have a better approach? Drew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] password is no required, authentication is overridden
Thomas Bley wrote: I type: pg_dump -h localhost -U postgres database_name and there is no question for the password. Have you created a .pgpass (or whatever the equivilant in the Windows world is)? That could be supplying the password. I haven't made changes to pg_hba.conf. I'm logged in as user admin, not postgres, the password is not empty. my pg_hba.conf: # ... # TYPE DATABASE USER CIDR-ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 md5 without the -h localhost, I get the same (complete) dump I'm not sure what the default behaviour for local socket connections is. Perhaps you should add a line to your pg_hba.conf to define a rule for local socket connections. For example local all all md5 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] password is no required, authentication is overridden
There is a pgpass file and it contians the password: D:\Documents and Settings\admin\Application Data\postgresql\pgpass.conf My installation is on: D:\Program Files\PostgreSQL\8.1\ Maybe it got automatically created by pgadmin3 ? I'll bet you're right. Looking into the documentation delivered with pg (PostgreSQL and pgAdmin Help) there is a page about the pgpass file, but there is not hint to environment variable pgpassword or the pgpass-file on the page of pg_dump, maybe you can add it ? In the documentation page of psql there is a hint to the pgpass file. That's a good idea. What do you think would be a good way to say it? Drew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)
On 7/18/06, Aaron Bono [EMAIL PROTECTED] wrote: On 18 Jul 2006 09:07:08 -0700, Andrew Hammond [EMAIL PROTECTED] wrote: I have a client with the following EAV inspired schema.CREATE TABLE many_tables (table_id text primary key,-- defines which virtual table isencodedattribute1 text,attribute2 text, attribute3 text,attribute4 text,...);I'd like to use a mix of constraint based paritioning, rules_and_views_ to implement a real schema underneath this mess, like thefollowing. CREATE TABLE cat ( cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL, aloofness NUMERIC(1,3) DEFAULT 1.0 CHECK (0.0 = aloofness ANDaloofness = 1.0));CREATE RULE many_tables_cat_insert AS ON INSERT TO many_tables WHERE table_id = 'cat' DO INSTEADINSERT INTO cat (cat_id, cat_name, aloofness) VALUES ( CAST(attribute1 AS integer), attribute2, CAST(attribute3 AS numeric(1,3)) -- gleefully ignore the other attributes);-- etc for UPDATE, and DELETE rules-- This part doesn't workCREATE VIEW many_tables_a (CHECK (table_id = 'cat')) INHERITS(many_tables) ASSELECT 'cat' AS table_id, CAST(cat_id AS text) AS attribute1,cat_name AS attribute2,CAST(aloofness AS text) AS attribute3,null AS attribute4, ...FROM cat;So, I guess I'm stuck doing the UNION ALL approach in this instance. This won't get me the partitioning win, nor the flexibility andcleanliness of design that I'd get with inheritance.As far as I can see, supporting the above would it mean addinginheritance and constraint support to views. Does anyone have a better approach?If you don't mind having redundant data, you could change the ON INSERT trigger to copy the data into cat, add an ON UPDATE trigger (you should do this anyway) and revoke direct insert/update to cat. Then you don't need many_tables_a or a UNION. There's already a performance problem, I suspect that would just exacerbate it. Since I want to encourage developers to use the relational tables instead of the many_tables table, refusing DML wouldn't be a good idea. Of course I don't know if this achieves your intended result or not. What is your business requirement for this? The current virtual table design has performance (as well as maitenance) issues. Performance tuning is problematic. A major re-design of the application is not something that can be done until the next major release. However, if I can slide a proper relational schema underneath this hodge-podge table while retaining compatability for legacy apps then it becomes possible to fix parts of the application to use the relational tables incrementally on an as-needed basis. If I could get constraint based exclusioning to work with the partitioning, then I would be able to realize performance improvements immediately (which is always good for a consultant). Unfortunately I don't see any way to do this. Inheritance seems to fit backwards from what I'm actually trying to do. I've seen a few EAV designs in practice. They've all been problematic. I'd like to have a better way of dealing with them. Which is why I'm tentatively suggesting support for inheritance and constraints in views. If there's some other way to achieve constraint based exclusion across a UNION of heterogenous tables, I'd love to hear it. Drew
Re: [HACKERS] using constraint based paritioning to fix EAV
Hannu Krosing wrote: Ühel kenal päeval, T, 2006-07-18 kell 16:44, kirjutas Andrew Hammond: On 7/18/06, Aaron Bono [EMAIL PROTECTED] wrote: On 18 Jul 2006 09:07:08 -0700, Andrew Hammond [EMAIL PROTECTED] wrote: I have a client with the following EAV inspired schema. CREATE TABLE many_tables ( table_id text primary key,-- defines which virtual table is encoded attribute1 text, attribute2 text, attribute3 text, attribute4 text, ... ); Maybe you can approach the problem from another end, and make the many_tables table the virtual one and all the others into real tables ? The many_tables table already exists. It works about as poorly as you'd expect. My goal is to migrate away from this attrocious design. My question is how best to do that. I'd like to take advantage of table partitioning and constraint based exclusion, but I can't figure out how to do it since the underlying tables are heterogenous in nature. Perhaps I'm going about this the wrong way though. I think I'll partition based on table_id and leave the native storage in the crappy text fields. Then create views of what should be the real, relational tables. That lets the developers migrate and (I hope) eases the performance burden somewhat. Once the code migration is complete I can finalize the data move. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] postgresql.conf basic analysis tool
Is there any interest in a basic perl script that would read through a postgresql.conf file and calculate approximate memory (and shared memory) usage? Also, are there any other (simple for now) things I should look at in the process? Asking because I'm getting annoyed with doing this by hand so... Drew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] further meta-data in pg_stat_activity?
How much work would it be to implement and how valuable would people find the following additions to pg_stat_activity? 1) # of transactions committed on this connection since start 2) # of transactions rolled back 3) milliseconds used processing requests 4) milliseconds idle in transaction 5) milliseconds idle 6) this is the n'th backend spawned since the postmaster started 7) this is the n'th backend for the given client_addr 8) this is the n'th backend for the given user 9) timestamp for start of the current transaction (null if idle?) Drew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Idea about fixing the lockfile issues in postgresql.init
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This seems very complicated. Why not simply delete the lock files as an earlier part of the boot process? Also, I've done a bunch of work on the init script we use here in production to run multiple backends. It supports the concept of having multiple versions of postgres installed as well as slony, and pg_autovacuum. I've found them helpful. Tf there's any interest, I could post them to the lists. Drew Tom Lane wrote: | I've been getting some more flak lately about fixing the RPM-supplied | init scripts to avoid the problem where the postmaster fails to start | because there's a stale lockfile in the data directory. See for | instance https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=134090 | but it's hardly the first time the question has been brought up. | | I've always resisted the standard solution of having the init script | itself remove the lock file, because I think that is a great way to | shoot yourself in the foot. But I finally had an idea that would make | things better with no increased risk. | | The postmaster does not abort simply because the lockfile is there. | It checks whether the PID mentioned in the lockfile exists, and belongs | to a postgres-owned process (the latter by seeing if kill(pid, 0) | succeeds), and is not the postmaster itself. If any of these tests fail | then it knows the lockfile is stale. So the scenario in which | it gets fooled is where the reboot has used just one or two more | processes than were used in the last boot cycle, so that the PID that | belonged to the postmaster in the last cycle now belongs to either | pg_ctl or the postgres-owned shell launched by su. | | So what occurred to me is that we could eliminate this scenario if we | could get rid of those two processes. The init script is running as | root, and so if its PID is the one mentioned in the old lockfile, | the kill() test will fail and the postmaster will go on its merry way. | It's only the other processes launched by su that could fool the | kill() test. | | After some experimentation, I have found that what will actually work | requires two changes: | | 1. In the init script, do not use pg_ctl to launch the postmaster, but | instead invoke it directly, ie something like | su - postgres -c /usr/bin/postmaster ...args... | pg_ctl is not really buying us any functionality or notational advantage | here, so this seems like no loss. This brings us down to one extra | postgres-owned process, namely the shell that su launches which in | turn launches the postmaster. (Depending on timing, the shell might or | might not still be around when the postmaster probes.) | | 2. In the postmaster, reject as bogus matches not only our own PID, | but our parent's PID from getppid(). This is perfectly safe since | whatever launched the postmaster is certainly not a competing | postmaster. Now we cannot be fooled by the parent shell either. | | AFAICS this is a bulletproof solution for typical users who only launch | one postmaster during system boot. If you launch two or more postmasters | then it's still possible for the first-launched one to have the same PID | that belonged to the second one during the previous boot cycle. But the | odds of this seem pretty low, since it would imply a much greater change | in the usage of PIDs during the boot cycle. The known failure cases | involve a change of just one or two PIDs, whereas postmasters launched | by different init scripts will surely be separated by dozens if not | hundreds of PIDs. (Besides, if you are truly paranoid you'd be running | separate postmasters under separate user IDs, which'd eliminate the | problem again.) | | I'd also want to remove the existing code in the init scripts that zaps | the socket lockfile, since it would be unnecessary. I've forebore to | remove it so far because it doesn't introduce a serious risk of data | corruption the way zapping the datadir lockfile would, but it is | definitely risky especially in multi-postmaster scenarios. | | Anyone see any downsides to these changes? | | regards, tom lane | | ---(end of broadcast)--- | TIP 5: Have you checked our extensive FAQ? | |http://www.postgresql.org/docs/faqs/FAQ.html - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFBYbGhgfzn5SevSpoRArnhAJ95jRZrSHcp/HJM2f39akDgpOu1VwCbBPWQ IkvGJ6L0QdyAQbK0yTnOVzA= =tJQE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Hacking MySQL Database
Christopher Browne wrote: After takin a swig o' Arrakan spice grog, Anonymous [EMAIL PROTECTED] belched out: Does anybody here know how to hack into a mysql database? You might want to visit a mailing list devoted to MySQL; they might know... And possibly even care, too. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is there any method to keep table in memory at startup
Vinay Jain wrote: Hi thanx and sorry that I asked such a simple question in postgres-hackers list but the complexity which i feel on that basis please allow me to explain my problem further. As i am working on sorting order , length and substring functions for Hindi text(Indian Language)... Here is the problem which i found in postgresql... after setting collating sequence in proper way(i.e. C) the order was on basis of unicode values...but in Hindi Language some of combined unicode values makes a single character similarly length is not appropriate for these reasons hence substring operations so i designed a customized data type called IndCharand operations on it in order by statement the only function called is indchar_lt(defined for operator).. Is your indchar_lt function declared IMMUTABLE? That would allow it's results to be cached instead of re-calculated every time. Now please guide me where is starting(where i can open connection to database) and ending of my programI feel only in indchar_lt function which will be called many times in order by statement causing performance degradation.. Have you created an index on that column? That would be a usual way to speed up an ORDER BY. NB, the function involved must be IMMUTABLE to be used in an index. as i am not much experienced this assumption may be wrong... My professor at University used to always say measure measure measure. Postgres makes it easy to measure. Try putting EXPLAIN ANALYZE before your SELECT statement. Run the SELECT a couple of times first so that the OS can get stuff cached, then: do an EXPLAIN ANALYZE on the query, save the results then ANALYZE the tables involved and to another EXPLAIN ANALYZE on the query, save the results add the necessary index, ANALYZE then EXPLAIN ANALYZE. so my question remains as it is that is there any such thing which can be called at startup of psql.to make connection to database I'm really not sure what you mean by this. psql connects to the database on startup. regards Vinay Andrew Hammond wrote: Vinay Jain wrote: Hi thank you for such a useful information... but actually in my case if i keep table in disk it significantly degrades performance and even for a table of 10 rows it takes 1-2 minutes I think u r not beliving it ! am i right for example I create a table in which i use my customized data type say student create table student (Name INDCHAR //INDCHAR is customized data type age integer); now i give query like this select * from student order by name; it will search for it's comparator operator () and related function... in that function there is one lookup table if that table is in memory no problem! (oh but it can't be) if it is in disk my program makes connection to database and execute query which is just a select statement on a simple where condition of equality. then closes connection There's your problem. Creating database connections is an expensive operation. They are not intended to be opened and closed often or quickly. Open your database connection at the beginning of your program, and close it at the end. You could also throw an index on the column you're using in your order by clause, but that won't make a difference until your table get a little bigger. Please take further questions of this nature to the pgsql-novice list. so every time less than operator() is called it does the same task.. what i feel in table of 10 rows how many times the operator will be called(NO idea but must be 10 times) is there any solution.. thanks in advance regards vinay ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is there any method to keep table in memory at startup
Vinay Jain wrote: Hi thank you for such a useful information... but actually in my case if i keep table in disk it significantly degrades performance and even for a table of 10 rows it takes 1-2 minutes I think u r not beliving it ! am i right for example I create a table in which i use my customized data type say student create table student (Name INDCHAR //INDCHAR is customized data type age integer); now i give query like this select * from student order by name; it will search for it's comparator operator () and related function... in that function there is one lookup table if that table is in memory no problem! (oh but it can't be) if it is in disk my program makes connection to database and execute query which is just a select statement on a simple where condition of equality. then closes connection There's your problem. Creating database connections is an expensive operation. They are not intended to be opened and closed often or quickly. Open your database connection at the beginning of your program, and close it at the end. You could also throw an index on the column you're using in your order by clause, but that won't make a difference until your table get a little bigger. Please take further questions of this nature to the pgsql-novice list. so every time less than operator() is called it does the same task.. what i feel in table of 10 rows how many times the operator will be called(NO idea but must be 10 times) is there any solution.. thanks in advance regards vinay ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PostgreSQL configuration
Mark Kirkwood wrote: Tom Lane wrote: I think if you spelled the subdir name config rather than etc, it would be more obvious what's what. How about 'conf' - (familiar to anyone who has used apache or tomcat ) How about 'etc' - (familiar ot anyone who has used unix) -- Andrew Hammond ---(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] rotatelogs integration in pg_ctl
Tom Lane wrote: Andrew Hammond [EMAIL PROTECTED] writes: I've attached a patch for pg_ctl which integrates the Apache project's rotatelogs for logging. Why bother? You just pipe pg_ctl's output to rotatelogs and you're done. It's not difficult to do, once you know how and once you know that there aren't any gotchas. However, the question comes up often enough it's clear that not everybody knows how. This provides a simple, clean, standardized way of using rotatelog. The patch is simple, low risk, and limited impact. So, why not? Drew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] rotatelogs integration in pg_ctl
I've attached a patch for pg_ctl which integrates the Apache project's
rotatelogs for logging. Is there any interested in the community for
such a thing? I have not yet added the appropriate stuff to autoconf to
completely integrate this.
I would appreciate any suggestions for improvement.
Drew
--- pg_ctl 2004-03-19 14:01:40.0 -0500
+++ pg_ctl_new 2004-03-24 15:30:29.0 -0500
@@ -39,6 +39,9 @@
Options for start or restart:
-l FILENAME write (or append) server log to FILENAME. The
use of this option is highly recommended.
+ -r ROTATIONTIME use apache's rotatelogs instead of writing
+ dirrectly tto FILENAME. Rotate the logs every
+ ROTATIONTIME seconds (see man for rotatelogs)
-o OPTIONS command line options to pass to the postmaster
(PostgreSQL server executable)
-p PATH-TO-POSTMASTER normally not necessary
@@ -62,6 +65,10 @@
VERSION='7.4.2'
DEF_PGPORT='5432'
+# have autoconf detect this?
+apache_bindir='/opt/OXRS/apache/bin'
+rotatelogs_path=$apache_bindir/rotatelogs
+
# protect the log file
umask 077
@@ -114,6 +121,7 @@
wait=
wait_seconds=60
logfile=
+rotation_time=
silence_echo=
shutdown_mode=smart
PGDATAOPTS=
@@ -143,6 +151,12 @@
-l*)
logfile=`echo $1 | sed 's/^-l//'`
;;
+ -r)
+ rotation_time=$2
+ shift;;
+ -r*)
+ rotation_time=`echo $1 | sed 's/^-m//'`
+ ;;
-m)
shutdown_mode=$2
shift;;
@@ -207,6 +221,18 @@
exit 1
fi
+if [ ! x$rotation_time = x ]; then
+if [ ! -x $rotatelogs_path ]; then
+echo $CMDNAME: log rotation specified but can't find $rotatelogs_path 12
+exit 1
+fi
+
+if [ x$logfile = x ]; then
+echo $CMDNAME: log rotation specified but no logfile given. Try the -l option. 12
+exit 1
+fi
+fi
+
if [ -z $wait ]; then
case $op in
start) wait=no;;
@@ -338,7 +364,11 @@
fi
if [ -n $logfile ]; then
+if [ -n $rotation_time ]; then # use rotatelogs for logging
+ $po_path ${1+$@} ${PGDATAOPTS+$PGDATAOPTS} /dev/null 21 | $rotatelogs_path $logfile $rotation_time
+ else
$po_path ${1+$@} ${PGDATAOPTS+$PGDATAOPTS} /dev/null $logfile 21
+ fi
else
# when starting without log file, redirect stderr to stdout, so
# pg_ctl can be invoked with $logfile and still have pg_ctl's
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
