Re: [PATCHES] [BUGS] BUG #4007: chr(0) doesn't work anymore
Tom Lane wrote: Steve Clark [EMAIL PROTECTED] writes: I'm not sure I understand what you mean about TEXT being null-safe. What are the issues, and why was it supported for years and now abruptly changed. It never was supported, we are simply plugging a hole that let you create a text value that would be likely to malfunction in subsequent use. Seems we never documented that chr(0) is not supported. I have applied the following doc patch to CVS HEAD and 8.3.X. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.420 diff -c -c -r1.420 func.sgml *** doc/src/sgml/func.sgml 26 Feb 2008 15:32:30 - 1.420 --- doc/src/sgml/func.sgml 3 Mar 2008 17:07:22 - *** *** 1340,1346 Character with the given code. For acronymUTF8/acronym the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate a strictly ! acronymASCII/acronym character. /entry entryliteralchr(65)/literal/entry entryliteralA/literal/entry --- 1340,1347 Character with the given code. For acronymUTF8/acronym the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate a strictly ! acronymASCII/acronym character. The NULL (0) character is not ! allowed because text data types cannot reliably store such bytes. /entry entryliteralchr(65)/literal/entry entryliteralA/literal/entry -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] CopyReadAttributesCSV optimization
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Heikki Linnakangas wrote: Here's a patch to speed up CopyReadAttributesCSV. On the test case I've been playing with, loading the TPC-H partsupp table, about 20% CopyReadAttributesCSV (inlined into DoCopy, DoCopy itself is insignificant): samples %image name symbol name 8136 25.8360 postgres CopyReadLine 6350 20.1645 postgres DoCopy 2181 6.9258 postgres pg_verify_mbstr_len 2157 6.8496 reiserfs (no symbols) 1668 5.2968 libc-2.7.so memcpy 1142 3.6264 libc-2.7.so strtod_l_internal 951 3.0199 postgres heap_formtuple 904 2.8707 libc-2.7.so strtol_l_internal 619 1.9656 libc-2.7.so memset 442 1.4036 libc-2.7.so strlen 341 1.0828 postgres hash_any 329 1.0447 postgres pg_atoi 300 0.9527 postgres AllocSetAlloc With this patch, the usage of that function goes down to ~13% samples %image name symbol name 7191 28.7778 postgres CopyReadLine 3257 13.0343 postgres DoCopy 2127 8.5121 reiserfs (no symbols) 1914 7.6597 postgres pg_verify_mbstr_len 1413 5.6547 libc-2.7.so memcpy 920 3.6818 libc-2.7.so strtod_l_internal 784 3.1375 libc-2.7.so strtol_l_internal 745 2.9814 postgres heap_formtuple 508 2.0330 libc-2.7.so memset 398 1.5928 libc-2.7.so strlen 315 1.2606 postgres hash_any 255 1.0205 postgres AllocSetAlloc The trick is to split the loop in CopyReadAttributesCSV into two parts, inside quotes, and outside quotes, saving some instructions in both parts. Your mileage may vary, but I'm quite happy with this. I haven't tested it much yet, but I wouldn't expect it to be a loss in any interesting scenario. The code also doesn't look much worse after the patch, perhaps even better. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] Remove FATAL from pg_lzdecompress
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Zdenek Kotala wrote: I attach patch which adds boundaries check and memory overwriting protection when compressed data are corrupted. Current behavior let code overwrite a memory and after that check if unpacked size is same as expected value. In this case elog execution fails (at least on Solaris - malloc has corrupted structures) and no message appears in a log file. I did not add any extra information into the message. Reasonable solution seems to be use errcontext how was recommended by Alvaro. But I 'm not sure if printtup is good place for it, because pg_detoast is called from many places. However, is can be solved in separate patch. I'm also think that this modification should be backported to other version too. Thanks Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] sinval.c / sinvaladt.c restructuring
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Alvaro Herrera wrote: I just modified the interactions in sinval.c and sinvaladt.c per http://thread.gmane.org/gmane.comp.db.postgresql.devel.patches/18820/focus=18822 It looks a lot saner this way: the code that actually deals with the queue, including locking etc, is all in sinvaladt.c. This means that the struct definition of the queue, and the queue pointer, are now internal implementation details inside sinvaladt.c. One side effect of this change is that the call to SendPostmasterSignal now occurs after the lock has been released. ISTM this is a good idea on general principles (no syscalls in lwlocked code), but I'm wondering if I created a thundering hoard problem that did not exist before. All tests pass. As a test of Review Board, I uploaded the patch to it: http://reviewdemo.postgresql.org/r/19/ -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. [ Attachment, skipping... ] ---(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 -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] [BUGS] BUG #4007: chr(0) doesn't work anymore
Bruce Momjian wrote: Tom Lane wrote: Steve Clark [EMAIL PROTECTED] writes: I'm not sure I understand what you mean about TEXT being null-safe. What are the issues, and why was it supported for years and now abruptly changed. It never was supported, we are simply plugging a hole that let you create a text value that would be likely to malfunction in subsequent use. Seems we never documented that chr(0) is not supported. I have applied the following doc patch to CVS HEAD and 8.3.X. The NULL (0) character is not allowed because text data types cannot reliably store such bytes. Reliably is arguably misleading here. cheers andrew -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] [BUGS] BUG #4007: chr(0) doesn't work anymore
BAndrew Dunstan wrote: Bruce Momjian wrote: Tom Lane wrote: Steve Clark [EMAIL PROTECTED] writes: I'm not sure I understand what you mean about TEXT being null-safe. What are the issues, and why was it supported for years and now abruptly changed. It never was supported, we are simply plugging a hole that let you create a text value that would be likely to malfunction in subsequent use. Seems we never documented that chr(0) is not supported. I have applied the following doc patch to CVS HEAD and 8.3.X. The NULL (0) character is not allowed because text data types cannot reliably store such bytes. Reliably is arguably misleading here. Agreed. Reliably word removed. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] Logging conflicted queries on deadlocks
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- ITAGAKI Takahiro wrote: Here is a patch to log conflicted queries on deadlocks. Queries are dumped at CONTEXT in the same sorting order as DETAIL messages. Those queries are picked from pg_stat_get_backend_activity, as same as pg_stat_activity, so that users cannot see other user's queries. (It might be better to log all queries in the server log and mask them in the client response, but I'm not sure how to do it...) | ERROR: deadlock detected | DETAIL: Process 3088 waits for ShareLock on transaction 608; blocked by process 2928. | Process 2928 waits for ShareLock on transaction 609; blocked by process 2824. | Process 2824 waits for ShareLock on transaction 610; blocked by process 3088. | CONTEXT: Process 3088: UPDATE test SET i = i WHERE i = 1; | Process 2928: insufficient privilege | Process 2824: UPDATE test SET i = i WHERE i = 3; | STATEMENT: UPDATE test SET i = i WHERE i = 1; Alvaro Herrera [EMAIL PROTECTED] wrote: Perhaps it could be shown in CONTEXT, like so: I think it's useful to show the PID of each statement, for the case where there are more than two processes deadlocked. Thanks for response. I bought your suggestion :-) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] [BUGS] BUG #3973: pg_dump using inherited tables do not always restore
On Wed, Feb 20, 2008 at 3:55 PM, Tom Lane [EMAIL PROTECTED] wrote: Alex Hunsaker [EMAIL PROTECTED] writes: create table junk (val integer not null, val2 integer); create table junk_child () inherits (junk_1); alter table junk_child alter column val drop not null; insert into junk_child (val2) values (1); pg_dump -t junk -t junk_child pg_restore/psql will fail because junk_child.val now has a not null constraint Actually the bug is that ALTER TABLE allows you to do that. It should not be possible to drop an inherited constraint, but right now there's not enough information in the system catalogs to detect the situation. Fixing this has been on the TODO list for awhile: o %Prevent child tables from altering or dropping constraints like CHECK that were inherited from the parent table regards, tom lane Hrm how about something like the attached patch? It only handles set not null/drop not null. And I thought about making it so set default behaved the same way, but i can see how that can be useful in the real world. Thoughts? Arguably pg_dump should just do something similar to what it does for set default (because that dumps correctly)... I only say that because there specific regressions test for the behavior I outlined above. Which is now broken with my patch. Be gentle... its my first dive into postgresql guts... inhertied_null.patch Description: Binary data -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] [HACKERS] new warning message
Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: Would it be reasonable to throw a warning if you revoke a privilege from some role, and that role inherits the privilege from some other role (or PUBLIC)? This has been suggested and rejected before --- the consensus is it'd be too noisy. Possibly the REVOKE manual page could be modified to throw more stress on the point. Agreed, patch attached and applied. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/revoke.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v retrieving revision 1.46 diff -c -c -r1.46 revoke.sgml *** doc/src/sgml/ref/revoke.sgml 30 Oct 2007 19:43:30 - 1.46 --- doc/src/sgml/ref/revoke.sgml 3 Mar 2008 19:16:38 - *** *** 92,98 literalPUBLIC/literal. Thus, for example, revoking literalSELECT/ privilege from literalPUBLIC/literal does not necessarily mean that all roles have lost literalSELECT/ privilege on the object: those who have it granted !directly or via another role will still have it. /para para --- 92,101 literalPUBLIC/literal. Thus, for example, revoking literalSELECT/ privilege from literalPUBLIC/literal does not necessarily mean that all roles have lost literalSELECT/ privilege on the object: those who have it granted !directly or via another role will still have it. Similarly, revoking !literalSELECT/ from a user might not prevent that user from using !literalSELECT/ if literalPUBLIC/literal or another membership !role still has literalSELECT/ rights. /para para -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] CopyReadLineText optimization
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Heikki Linnakangas wrote: Heikki Linnakangas wrote: Attached is a patch that modifies CopyReadLineText so that it uses memchr to speed up the scan. The nice thing about memchr is that we can take advantage of any clever optimizations that might be in libc or compiler. Here's an updated version of the patch. The principle is the same, but the same optimization is now used for CSV input as well, and there's more comments. I still need to do more benchmarking. I mentioned a ~5% speedup on the test I ran earlier, which was a load of the lineitem table from TPC-H. It looks like with cheaper data types the gain can be much bigger; here's an oprofile from loading the TPC-H partsupp table, Before: samples %image name symbol name 5146 25.7635 postgres CopyReadLine 4089 20.4716 postgres DoCopy 1449 7.2544 reiserfs (no symbols) 1369 6.8539 postgres pg_verify_mbstr_len 1013 5.0716 libc-2.7.so memcpy 749 3.7499 libc-2.7.so strtod_l_internal 598 2.9939 postgres heap_formtuple 548 2.7436 libc-2.7.so strtol_l_internal 403 2.0176 libc-2.7.so memset 309 1.5470 libc-2.7.so strlen 208 1.0414 postgres AllocSetAlloc ... After: samples %image name symbol name 4165 25.7879 postgres DoCopy 1574 9.7455 postgres pg_verify_mbstr_len 1520 9.4112 reiserfs (no symbols) 1005 6.2225 libc-2.7.so memchr 986 6.1049 libc-2.7.so memcpy 632 3.9131 libc-2.7.so strtod_l_internal 589 3.6468 postgres heap_formtuple 546 3.3806 libc-2.7.so strtol_l_internal 386 2.3899 libc-2.7.so memset 366 2.2661 postgres CopyReadLine 287 1.7770 libc-2.7.so strlen 215 1.3312 postgres LWLockAcquire 208 1.2878 postgres hash_any 176 1.0897 postgres LWLockRelease 161 0.9968 postgres InputFunctionCall 157 0.9721 postgres AllocSetAlloc ... Profile shows that with the patch, ~8.5% of the CPU time is spent in CopyReadLine+memchr, vs. 25.5% before. That's a quite significant speedup. I still need to test the worst-case performance, with input that has a lot of escapes. It would be interesting to hear reports with this patch from people on different platforms. These results are from my laptop with 32-bit Intel CPU, running Linux. There could be big differences in the memchr implementations. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] Bulk Insert tuning
Added to TODO: o Consider using a ring buffer for COPY FROM http://archives.postgresql.org/pgsql-patches/2008-02/msg00140.php --- Simon Riggs wrote: On Tue, 2008-02-26 at 15:12 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Following patch implements a simple mechanism to keep a buffer pinned while we are bulk loading. This will fail to clean up nicely after a subtransaction abort, no? Yes, will fix. (For that matter I don't think it's right even for a top-level abort.) And I'm pretty sure it will trash your table entirely if someone inserts into another relation while a bulk insert is happening. (Not at all impossible, think of triggers for instance.) The pinned buffer is separate from the preferred block for each relation; BulkInsertBuffer isn't used for determining the block to insert into. If you try to insert into a block that differs from the pinned one it unpins it and re-pins the new one. So it is always safe with respect to the data in the table. It can run into recursive bulk insert ops but that just destroys the performance advantage, its not actually dangerous. From a code structural point of view, we are already well past the number of distinct options that heap_insert ought to have. I was thinking the other day that bulk inserts ought to use a ring-buffer strategy to avoid having COPY IN trash the whole buffer arena, just as we've taught COPY OUT not to. So maybe a better idea is to generalize BufferAccessStrategy to be able to handle write as well as read concerns; or have two versions of it, one for writing and one for reading. In any case the point being to encapsulate all these random little options in a struct, which could also carry along state that needs to be saved across a series of inserts, such as the last pinned buffer. That was actually my first thought when I realised recursive ops were possible. I don't think its necessary from a code correctness perspective but it might be an appropriate re-factoring considering those little bool-s seem to be breeding. I think we need two Strategy types since CTAS would need one of each. But then VACUUM is mid-way on that. Hmmm. Will consider. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] Reference by output in : \d table_name
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- kenneth d'souza wrote: With reference to the post http://archives.postgresql.org/pgsql-patches/2008-02/msg00104.phpand as stated by -hackers and -patchers, I am submitting the diff -c output as an attachment. Thanks, Kenneth _ Tried the new MSN Messenger? It?s cool! Download now. http://messenger.msn.com/Download/Default.aspx?mkt=en-in [ Attachment, skipping... ] -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] [BUGS] Incomplete docs for restore_command for hot standby
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
Markus Bertheau wrote:
2008/2/22, Simon Riggs [EMAIL PROTECTED]:
On Thu, 2008-02-21 at 08:01 +0600, Markus Bertheau wrote:
Section 24.3.3.1 states about restore_command:
The command will be asked for file names that are not present in the
archive; it must return nonzero when so asked.
Section 24.4.1 further states:
The magic that makes the two loosely coupled servers work together is
simply a restore_command used on the standby that waits for the next
WAL file to become available from the primary.
It is not clear from the first paragraph, whether the non-existing
file that restore_command is being asked for is a not-yet-generated
WAL file or something different. If it was a not-yet-generated WAL
file, restore_command for replication would have to wait for it to
appear. If it was something different, restore_command for replication
would have to return an error right away. (Because else it would hang
indefinitely, waiting for a file that is not going to appear). Yet I
couldn't find hints in the documentation as to how these two cases can
be detected by restore_command, i.e. how restore_command should tell a
request for a WAL file from a request for a non-WAL file.
The two sentences aren't mutually exclusive, especially when you
consider they are discussing two different use cases. Why not read up on
pg_standby anyway?
I read about pg_standby, but this is not about solving a particular problem
but
about missing information in the docs.
Practice (http://archives.postgresql.org/sydpug/2006-10/msg1.php)
shows that this is a problem, and people use unproved heuristics
('history' substring in the requested file name).
Old email written during beta. Read at your own peril.
The email may be old, but the problem at hand is still relevant.
Additionally, 24.3.3 contains slightly misleading information:
It is important that the command return nonzero exit status on
failure. The command will be asked for log files that are not present
in the archive; it must return nonzero when so asked. This is not an
error condition.
This suggests that all non-existing files that restore_command will be
asked for are log files. One could therefore reasonably assume that
restore_command for replication should wait on all non-existing files.
24.3.3.1 later corrects this by stating that not only log files may be
requested, but nevertheless.
If you have some suggested changes, I'd be happy to hear them.
Probably additions are better than just changes though.
What about this:
*** a/doc/src/sgml/backup.sgml
--- b/doc/src/sgml/backup.sgml
***
*** 1001,1011 restore_command = 'cp /mnt/server/archivedir/%f %p'
para
It is important that the command return nonzero exit status on failure.
! The command emphasiswill/ be asked for log files that are not
present
! in the archive; it must return nonzero when so asked. This is not an
! error condition. Be aware also that the base name of the literal%p/
! path will be different from literal%f/; do not expect them to be
! interchangeable.
/para
para
--- 1001,1011
para
It is important that the command return nonzero exit status on failure.
! The command emphasiswill/ be asked for log and other files that are
! not present in the archive; it must return nonzero when so asked. This
is
! not an error condition. Be aware also that the base name of the
! literal%p/ path will be different from literal%f/; do not expect
! them to be interchangeable.
/para
para
***
*** 1576,1594 archive_command = 'local_backup_script.sh'
para
The magic that makes the two loosely coupled servers work together is
! simply a varnamerestore_command/ used on the standby that waits
! for the next WAL file to become available from the primary. The
! varnamerestore_command/ is specified in the
filenamerecovery.conf/ file on the standby server. Normal recovery
processing would request a file from the WAL archive, reporting failure
if the file was unavailable. For standby processing it is normal for
! the next file to be unavailable, so we must be patient and wait for
! it to appear. A waiting varnamerestore_command/ can be written as
! a custom script that loops after polling for the existence of the next
! WAL file. There must also be some way to trigger failover, which should
! interrupt the
Re: [PATCHES] [BUGS] BUG #3973: pg_dump using inherited tables do not always restore
Alex Hunsaker [EMAIL PROTECTED] writes: On Wed, Feb 20, 2008 at 3:55 PM, Tom Lane [EMAIL PROTECTED] wrote: Actually the bug is that ALTER TABLE allows you to do that. It should not be possible to drop an inherited constraint, but right now there's not enough information in the system catalogs to detect the situation. Fixing this has been on the TODO list for awhile: Hrm how about something like the attached patch? It seems much more restrictive than necessary, plus it does nothing for the check-constraint case. My recollection of the previous discussion about how to fix this was that we needed to add an inhcount column to pg_constraint, and add entries for not-null constraints (at least inherited ones) to pg_constraint so that they'd be able to have inhcount fields. The latter would also allow us to attach names to not-null constraints, which I think is required by spec but we've never supported. regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] [GENERAL] Empty arrays with ARRAY[]
A quick recap: I submitted a patch for empty ARRAY[] syntax back in November, and as far as I can see it never made it to the patches list. Gregory suggested a different way of approaching the problem (quoted below), but nobody commented further about how it might be made to work. I'd like to RFC again on Gregory's idea, and if that doesn't bear any fruit I'd like to submit the patch as-is for review. Regards, BJ On 01/12/2007, Brendan Jurd [EMAIL PROTECTED] wrote: On Nov 30, 2007 9:09 PM, Gregory Stark [EMAIL PROTECTED] wrote: I'm sorry to suggest anything at this point, but... would it be less invasive if instead of requiring the immediate cast you created a special case in the array code to allow a placeholder object for empty array of unknown type. The only operation which would be allowed on it would be to cast it to some specific array type. That way things like UPDATE foo SET col = array[]; INSERT INTO foo (col) VALUES (array[]); could be allowed if they could be contrived to introduce an assignment cast. Not sure it would be less invasive, but I do like the outcome of being able to create an empty array pending assignment. In addition to your examples, it might also make it possible to do things like this in plpgsql DECLARE a text[] := array[]; Whereas my patch requires you to write a text[]: =array[]::text[]; ... which seems pretty stupid. ... Any suggestions about how you would enforce the only allow casts to array types restriction on the empty array? -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] [GENERAL] ts_headline
I have applied the attached documentation patch to show ts_headline() using a configuration name. --- Oleg Bartunov wrote: On Sat, 23 Feb 2008, Stephen Davies wrote: As it turns out, all I needed was in the doco but the key element - the first config arg to ts_headline - was not in any of the examples so I missed it. aha, Original one were based on default configuration, but then concept was changed, but the examples were not modified. Would it be possible for ts_headline to work with the pre-parsed ts_vector? it's impossible, Richard already explained you the reasons. I see references to future plans for phrase searching in ts. Is there a date for this? Not yet. The problem mostly algebraical :) Simple 'exact search' is doable, but we need something more, since we support boolean operators, pluggable dictionaries (which could produce several lexemes, for example), and document structure (lexem weights). So, we need to define consistent algebra for text, to have predictable results. This is quite a complex task, which require a lot of dedicated time, which we don't have. Cheers and thanks, Stephen Davies On Friday 22 February 2008 22:54, Oleg Bartunov wrote: On Fri, 22 Feb 2008, Stephen Davies wrote: H! I think I now understand the ts position better, thank you. Part of my problem has been that I am used to the functionality of Open Text's LCS (aka BASIS) product which handles text differently. It includes the position (and context) information in the index and does remember how the text was parsed so does not need to reparse to insert hit navigation tags nor need pointers as to how to parse queries. (It also supports phrase searching.) Now that I have a better understanding of ts, I think I will be able to make it do at least most of what I hoped for. I'm wondering if it was not described in the text search documentation :) Thank you again for your help with this. Cheers, Stephen Davies On Friday 22 February 2008 20:45, Richard Huxton wrote: Stephen Davies wrote: Unfortunately, my link to the box with the test database is down due to lack of maintenance by our local telco (Telstra) but I think that I also missed the optional config arg to ts_headline. The lack of link also means that I cannot confirm your findings but your logic looks good. Looks like ALTER DATABASE SET default_text_config='english' is what you need. It begs the question, however, as to why ts-headline needs to reparse the raw text. It needs to line up tsvector lexemes with actual characters in the text. The tsvector is missing punctuation, any stopwords (the, it, a) as well as being stemmed (if your dictionary does that). Also, it's looking for a short span of words that provide the best match. That might not be a complete match of course, and is different to how you'd normally look to use a tsvector. At least in my case, I am using a trigger to parse the combination of Title and Abstract to a ts_vector field in the table row (as suggested in 12.2.2 and 12.4.3 in the doco) so that the ts_vector is already available to ts_headline. If ts_headline had the ability to use that pre-parsed ts_vector, my problem would never have arisen - and the performance of ts_headline would be improved. Maybe. It would still have to parse the text to some degree though, just to get the original words punctuation into the headline. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/textsearch.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/textsearch.sgml,v retrieving revision 1.40 diff -c -c -r1.40 textsearch.sgml *** doc/src/sgml/textsearch.sgml 13 Dec 2007 06:32:47 - 1.40 --- doc/src/sgml/textsearch.sgml 4 Mar 2008 02:55:17 - *** *** 1102,1108 For example:
[PATCHES] Re: [BUGS] Incomplete docs for restore_command for hot standby
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
Markus Bertheau wrote:
2008/2/22, Simon Riggs [EMAIL PROTECTED]:
On Thu, 2008-02-21 at 08:01 +0600, Markus Bertheau wrote:
Section 24.3.3.1 states about restore_command:
The command will be asked for file names that are not present in the
archive; it must return nonzero when so asked.
Section 24.4.1 further states:
The magic that makes the two loosely coupled servers work together is
simply a restore_command used on the standby that waits for the next
WAL file to become available from the primary.
It is not clear from the first paragraph, whether the non-existing
file that restore_command is being asked for is a not-yet-generated
WAL file or something different. If it was a not-yet-generated WAL
file, restore_command for replication would have to wait for it to
appear. If it was something different, restore_command for replication
would have to return an error right away. (Because else it would hang
indefinitely, waiting for a file that is not going to appear). Yet I
couldn't find hints in the documentation as to how these two cases can
be detected by restore_command, i.e. how restore_command should tell a
request for a WAL file from a request for a non-WAL file.
The two sentences aren't mutually exclusive, especially when you
consider they are discussing two different use cases. Why not read up on
pg_standby anyway?
I read about pg_standby, but this is not about solving a particular problem
but
about missing information in the docs.
Practice (http://archives.postgresql.org/sydpug/2006-10/msg1.php)
shows that this is a problem, and people use unproved heuristics
('history' substring in the requested file name).
Old email written during beta. Read at your own peril.
The email may be old, but the problem at hand is still relevant.
Additionally, 24.3.3 contains slightly misleading information:
It is important that the command return nonzero exit status on
failure. The command will be asked for log files that are not present
in the archive; it must return nonzero when so asked. This is not an
error condition.
This suggests that all non-existing files that restore_command will be
asked for are log files. One could therefore reasonably assume that
restore_command for replication should wait on all non-existing files.
24.3.3.1 later corrects this by stating that not only log files may be
requested, but nevertheless.
If you have some suggested changes, I'd be happy to hear them.
Probably additions are better than just changes though.
What about this:
*** a/doc/src/sgml/backup.sgml
--- b/doc/src/sgml/backup.sgml
***
*** 1001,1011 restore_command = 'cp /mnt/server/archivedir/%f %p'
para
It is important that the command return nonzero exit status on failure.
! The command emphasiswill/ be asked for log files that are not
present
! in the archive; it must return nonzero when so asked. This is not an
! error condition. Be aware also that the base name of the literal%p/
! path will be different from literal%f/; do not expect them to be
! interchangeable.
/para
para
--- 1001,1011
para
It is important that the command return nonzero exit status on failure.
! The command emphasiswill/ be asked for log and other files that are
! not present in the archive; it must return nonzero when so asked. This
is
! not an error condition. Be aware also that the base name of the
! literal%p/ path will be different from literal%f/; do not expect
! them to be interchangeable.
/para
para
***
*** 1576,1594 archive_command = 'local_backup_script.sh'
para
The magic that makes the two loosely coupled servers work together is
! simply a varnamerestore_command/ used on the standby that waits
! for the next WAL file to become available from the primary. The
! varnamerestore_command/ is specified in the
filenamerecovery.conf/ file on the standby server. Normal recovery
processing would request a file from the WAL archive, reporting failure
if the file was unavailable. For standby processing it is normal for
! the next file to be unavailable, so we must be patient and wait for
! it to appear. A waiting varnamerestore_command/ can be written as
! a custom script that loops after polling for the existence of the next
! WAL file. There must also be some way to trigger failover, which should
! interrupt the
