Re: [HACKERS] PATCH to fix two little typo in charset.sgml
Dickson S. Guedes wrote: Hi all, Attached is a patch to fix a command line example in charset.sgml. No, the options really are called LC_COLLATE and LC_CTYPE now. They were renamed on 6th of April, just before beta1 -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] how to insure libpq(dll/so) for thread-safety?
wjzeng zwjing_...@hotmail.com wrote: In pgsql/src/interfaces/libpq/fe-exec.c, there are two variables: - static int static_client_encoding = PG_SQL_ASCII; static bool static_std_strings = false; If enable_thread_safety is no, how to insure libpq(dll/so) for thread-safety? Use PQescape[String|Bytea]Conn() instead of PQescape[String|Bytea](). The static variables are used only in those deprecated functions. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] SE-PostgreSQL for v8.5 development (r1891)
The SE-PostgreSQL patches (for v8.5) are updated:
http://sepgsql.googlecode.com/files/sepgsql-01-sysatt-8.4beta1-r1891.patch
http://sepgsql.googlecode.com/files/sepgsql-02-core-8.4beta1-r1891.patch
http://sepgsql.googlecode.com/files/sepgsql-03-writable-8.4beta1-r1891.patch
http://sepgsql.googlecode.com/files/sepgsql-04-rowlevel-8.4beta1-r1891.patch
http://sepgsql.googlecode.com/files/sepgsql-05-perms-8.4beta1-r1891.patch
http://sepgsql.googlecode.com/files/sepgsql-06-utils-8.4beta1-r1891.patch
http://sepgsql.googlecode.com/files/sepgsql-07-tests-8.4beta1-r1891.patch
http://sepgsql.googlecode.com/files/sepgsql-08-docs-8.4beta1-r1891.patch
List of updates:
* The base version was upgraded to the latest CVS HEAD.
* db_schema, db_sequence object classes were added.
These classes provide analogy of ACL_USAGE and so on.
* db_table/db_column:{reference} permission was added.
* Some of bug were fixed.
BTW, Bruce,
In the v8.4, it seems to me more appropriate that the Appendix G. External
Projects points to a few external (but not mainlined yet) projects such
as
SE-PostgreSQL.
Ah, very good idea.
I made a wiki entry at:
http://wiki.postgresql.org/wiki/SEPostgreSQLv8.4
It notes the step to apply the SE-PostgreSQL patches to v8.4 series, and
will introduce the list of patches for each v8.4 series.
(I'll start to provide them from v8.4beta2.)
Is it similar to what you imagined?
If OK, I'll send a documentation patch for the appendix.
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.com
--
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] New trigger option of pg_standby
Hi, Sorry for the delay. On Thu, May 14, 2009 at 6:04 AM, Simon Riggs si...@2ndquadrant.com wrote: but before we go to DB_IN_PRODUCTION? I think it can be either, so I'll go with your proposal. I also think so. (I'm aware Fujii-san is asleep right now, so we should expect another viewpoint before tomorrow). I'd like to avoid adding new parameter for warm-standby if possible because currently the setup of it is already complicated. But, I don't have another good idea yet other than the already proposed. Sorry. Personally, I'd rather make pg_standby delete a trigger file when the timeline history file is requested even if this would break the current behavior, than the setup of warm-standby becomes more complicated. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] Problem with estimating pages for a table
Hi, Cristina M cristina.ma...@yahoo.com writes: For each varchar column - I add an extra 4 bytes For each numeric column - I add an extra 8 bytes Add a 28 bytes row overhead. For example if i have a table with col1: integer, col2: varchar, col3 varchar, I will get: pages = (col1width + col2width + 4 + col3width + 4 + 28)* no. of rows / block size You may find Greg Startk's presentation on the topic helpful: http://wiki.postgresql.org/wiki/Image:How_Long_Is_a_String.pdf Regards, -- dim -- 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] Synchronous replication: Promotion of Standby to Primary
Hi, On Mon, May 4, 2009 at 11:07 PM, K, Niranjan (NSN - IN/Bangalore) niranja...@nsn.com wrote: Hi, Re-opening the discussion related to triggers to promote standby server. In the earlier dicussion, there were 2 proposals, Trigger based on file and trigger based on signals. I think there was no conclusion on this. http://archives.postgresql.org/pgsql-hackers/2008-12/msg01231.php According to the proposal, it seems to be better if signals are used as we can avoid checking the file existance in the loop. Or the other approach is to use inotify (http://en.wikipedia.org/wiki/Inotify). But portability to other unix like OS is an issue to look for. Could you please get back which is being implemented/ considered for promotion of standby? I had to choose the trigger file approach last time since the patch was also using warm-standby in part. But, in 8.5, synch-rep is going to work without warm-standby. So, we can choose also the trigger based on signals. In this case, Since using kill(1) command directly for the trigger signal is troublesome, we should also provide the wrapper command such as pg_ctl trigger/promote. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] Synchronous replication: Promotion of Standby to Primary
Hi, On Thu, May 14, 2009 at 8:16 PM, Fujii Masao masao.fu...@gmail.com wrote: Hi, On Mon, May 4, 2009 at 11:07 PM, K, Niranjan (NSN - IN/Bangalore) niranja...@nsn.com wrote: Hi, Re-opening the discussion related to triggers to promote standby server. In the earlier dicussion, there were 2 proposals, Trigger based on file and trigger based on signals. I think there was no conclusion on this. http://archives.postgresql.org/pgsql-hackers/2008-12/msg01231.php According to the proposal, it seems to be better if signals are used as we can avoid checking the file existance in the loop. Or the other approach is to use inotify (http://en.wikipedia.org/wiki/Inotify). But portability to other unix like OS is an issue to look for. Could you please get back which is being implemented/ considered for promotion of standby? I had to choose the trigger file approach last time since the patch was also using warm-standby in part. But, in 8.5, synch-rep is going to work without warm-standby. So, we can choose also the trigger based on signals. In this case, In this case, I'm not sure which signal can be used for the trigger. SIGINT/QUIT/TERM/USR1 are already used in postmaster. SIGUSR2 is already reserved for children according to the source comment. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] PATCH to fix two little typo in charset.sgml
Em Qui, 2009-05-14 às 09:35 +0300, Heikki Linnakangas escreveu: Dickson S. Guedes wrote: Hi all, Attached is a patch to fix a command line example in charset.sgml. No, the options really are called LC_COLLATE and LC_CTYPE now. They were renamed on 6th of April, just before beta1 Oops! Sorry, my fault, forget it. :/ I was missed that and my local version of repository wasn't updating rightly :( I did a cvs update from scratch now and it is working as expected. Thanks... -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br signature.asc Description: Esta é uma parte de mensagem assinada digitalmente
Re: [HACKERS] New trigger option of pg_standby
On Wed, 2009-05-13 at 21:43 +0100, Simon Riggs wrote:
On Wed, 2009-05-13 at 21:26 +0300, Heikki Linnakangas wrote:
This whole thing can be considered to be a new feature.
recovery.conf will contain a new optional parameter:
recovery_end_command (string)
Implemented.
Some possibility of re-factoring in calc of %r, though that has not been
done to ensure code clarity and avoid need for retesting other aspects
of recovery at this stage of beta.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Index: doc/src/sgml/backup.sgml
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.125
diff -c -r2.125 backup.sgml
*** doc/src/sgml/backup.sgml 27 Apr 2009 16:27:35 - 2.125
--- doc/src/sgml/backup.sgml 14 May 2009 15:14:35 -
***
*** 1126,1131
--- 1126,1154
/listitem
/varlistentry
+ varlistentry id=recovery-end-command xreflabel=recovery_end_command
+ termvarnamerecovery_end_command/varname (typestring/type)/term
+ listitem
+para
+ This parameter specifies a shell command that will be executed once only
+ at the end of recovery. This parameter is optional. The purpose of the
+ recovery_end_command is to provide a mechanism for cleanup following
+ replication or recovery.
+ Any literal%r/ is replaced by the name of the file
+ containing the last valid restart point. That is the earliest file that
+ must be kept to allow a restore to be restartable, so this information
+ can be used to truncate the archive to just the minimum required to
+ support restart of the current restore. literal%r/ would only be
+ used in a warm-standby configuration (see xref linkend=warm-standby).
+ Write literal%%/ to embed an actual literal%/ character
+ in the command.
+ If the command returns a non-zero exit status then a WARNING log
+ message will be written, unless signalled in which case we return
+ a FATAL error.
+/para
+ /listitem
+ /varlistentry
+
varlistentry id=recovery-target-time xreflabel=recovery_target_time
termvarnamerecovery_target_time/varname
(typetimestamp/type)
Index: doc/src/sgml/pgstandby.sgml
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/pgstandby.sgml,v
retrieving revision 2.7
diff -c -r2.7 pgstandby.sgml
*** doc/src/sgml/pgstandby.sgml 27 Feb 2009 09:30:21 - 2.7
--- doc/src/sgml/pgstandby.sgml 14 May 2009 15:33:18 -
***
*** 210,215
--- 210,216
archive_command = 'cp %p .../archive/%f'
restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5442 .../archive %f %p %r 2standby.log'
+ recovery_end_command = 'rm /tmp/pgsql.trigger.5442'
/programlisting
para
where the archive directory is physically located on the standby server,
***
*** 241,246
--- 242,252
/listitem
listitem
para
+ remove the trigger file when recovery ends
+ /para
+/listitem
+listitem
+ para
remove no-longer-needed files from the archive directory
/para
/listitem
Index: src/backend/access/transam/xlog.c
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.337
diff -c -r1.337 xlog.c
*** src/backend/access/transam/xlog.c 7 May 2009 11:25:25 - 1.337
--- src/backend/access/transam/xlog.c 14 May 2009 15:19:41 -
***
*** 147,152
--- 147,153
/* options taken from recovery.conf */
static char *recoveryRestoreCommand = NULL;
+ static char *recoveryEndCommand = NULL;
static bool recoveryTarget = false;
static bool recoveryTargetExact = false;
static bool recoveryTargetInclusive = true;
***
*** 463,468
--- 464,470
static void XLogFileClose(void);
static bool RestoreArchivedFile(char *path, const char *xlogfname,
const char *recovername, off_t expectedSize);
+ static void ExecuteRecoveryEndCommand(void);
static void PreallocXlogFiles(XLogRecPtr endptr);
static void RemoveOldXlogFiles(uint32 log, uint32 seg, XLogRecPtr endptr);
static void ValidateXLOGDirectoryStructure(void);
***
*** 2850,2855
--- 2852,2965
}
/*
+ * Attempt to execute the recovery_end_command.
+ */
+ static void
+ ExecuteRecoveryEndCommand(void)
+ {
+ char xlogRecoveryEndCmd[MAXPGPATH];
+ char lastRestartPointFname[MAXPGPATH];
+ char *dp;
+ char *endp;
+ const char *sp;
+ int rc;
+ bool signaled;
+ uint32 restartLog;
+ uint32 restartSeg;
+
+ Assert(recoveryEndCommand);
+
+ /*
+ * Calculate the archive file cutoff point for use during log shipping
+ * replication. All files earlier than this point can be deleted
+
[HACKERS] Optimizing Read-Only Scalability
In a thread on -perform it has been observed that our Read-Only scalability is not as good as it could be. One problem being that we need to scan the whole of the ProcArray to derive a snapshot, which becomes the dominant task with many users. If we think about a situation where write transactions happen infrequently, then the likelihood is that we end up with xmin==xmax most of the time. If our last snapshot had xmin=xmax and the xmax hasn't changed since our last snapshot then we don't need to scan the procarray at all, just look at the header. So we can optimize away the scan through the procarray by doing two if tests, one outside of the lock, one inside. In normal running, both will be optimized away, though in read-only periods we would avoid much work. We don't need to change the API to GetSnapshotData since the snapshot is statically allocated and unless newly created will contain the last snapshot's data. Interesting? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Optimizing Read-Only Scalability
Simon Riggs si...@2ndquadrant.com writes: In a thread on -perform it has been observed that our Read-Only scalability is not as good as it could be. One problem being that we need to scan the whole of the ProcArray to derive a snapshot, which becomes the dominant task with many users. GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. regards, tom lane -- 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] Optimizing Read-Only Scalability
On Thu, May 14, 2009 at 1:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: In a thread on -perform it has been observed that our Read-Only scalability is not as good as it could be. One problem being that we need to scan the whole of the ProcArray to derive a snapshot, which becomes the dominant task with many users. GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. I think Simon's point was that it is O(n) rather than O(1), not that it took an exclusive lock. ...Robert -- 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] Optimizing Read-Only Scalability
Robert Haas robertmh...@gmail.com writes: On Thu, May 14, 2009 at 1:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. I think Simon's point was that it is O(n) rather than O(1), not that it took an exclusive lock. I think my point was that there's no evidence that GetSnapshotData is where the scalability issue is. Without some evidence there's no point in kluging it up. regards, tom lane -- 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] Optimizing Read-Only Scalability
On Thu, 2009-05-14 at 13:28 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: In a thread on -perform it has been observed that our Read-Only scalability is not as good as it could be. One problem being that we need to scan the whole of the ProcArray to derive a snapshot, which becomes the dominant task with many users. GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. I agree completely; I didn't mention the ProcArrayLock at all... I did mention scanning the procarray itself, which is likely too big to fit in on-chip cache and so must be re-read from main RAM each time. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Optimizing Read-Only Scalability
On Thu, May 14, 2009 at 1:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, May 14, 2009 at 1:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: GetSnapshotData doesn't take an exclusive lock. Neither does start or end of a read-only transaction. AFAIK there is no reason, and certainly no shred of experimental evidence, to think that ProcArrayLock contention is the bottleneck for read-only scenarios. I think Simon's point was that it is O(n) rather than O(1), not that it took an exclusive lock. I think my point was that there's no evidence that GetSnapshotData is where the scalability issue is. Without some evidence there's no point in kluging it up. Sure. I don't think anyone was proposing to commit something without first testing it. Supposing that the patch can be shown to improve performance for all-read-only workloads, and supposing further that the patch can be shown to have no material negative impact on write-heavy workloads, it would also be interesting to throw in a bit of scattered write traffic and see whether that completely negates the benefit or not. ...Robert -- 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] Optimizing Read-Only Scalability
On Thu, 2009-05-14 at 14:06 -0400, Robert Haas wrote: Supposing that the patch can be shown to improve performance for all-read-only workloads, and supposing further that the patch can be shown to have no material negative impact on write-heavy workloads, it would also be interesting to throw in a bit of scattered write traffic and see whether that completely negates the benefit or not. If you have a workload consisting of high volume single/few row lookups (OLRP), then ISTM that the majority of data cache line accesses will be on the procarray, especially so when we have many sessions. More to the point, MySQL would not need to access an equivalent data structure and so Postgres would access much more memory. The way I understand it, typically 4 CPUs at a time will be able to access that memory at the same time. If they can skip that part entirely, then we will get better scalability. Anyway, I'd be indebted to anyone that can shed more light on the hardware technical details in my above paragraphs. We'll learn something either way. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] New trigger option of pg_standby
Hi, On Fri, May 15, 2009 at 12:36 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-05-13 at 21:43 +0100, Simon Riggs wrote: On Wed, 2009-05-13 at 21:26 +0300, Heikki Linnakangas wrote: This whole thing can be considered to be a new feature. recovery.conf will contain a new optional parameter: recovery_end_command (string) Implemented. + ereport(signaled ? FATAL : WARNING, + (errmsg(recovery_end_command \%s\: return code %d, + xlogRecoveryEndCmd, rc))); In fast failover case, pg_standby has to delete the trigger file immediately if it's found. Otherwise, recovery may go wrong as I already described. http://archives.postgresql.org/pgsql-hackers/2009-04/msg01139.php So, in fast mode, recovery_end_command would always fail to delete the trigger file, and cause warning. This is odd behavior, I think. We should change WARNING to DEBUG2 like RestoreArchivedFile() in the above code? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] New trigger option of pg_standby
Fujii Masao wrote: On Fri, May 15, 2009 at 12:36 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-05-13 at 21:43 +0100, Simon Riggs wrote: On Wed, 2009-05-13 at 21:26 +0300, Heikki Linnakangas wrote: This whole thing can be considered to be a new feature. recovery.conf will contain a new optional parameter: recovery_end_command (string) Implemented. + ereport(signaled ? FATAL : WARNING, + (errmsg(recovery_end_command \%s\: return code %d, + xlogRecoveryEndCmd, rc))); In fast failover case, pg_standby has to delete the trigger file immediately if it's found. Otherwise, recovery may go wrong as I already described. http://archives.postgresql.org/pgsql-hackers/2009-04/msg01139.php And if you delete it immediately, you risk getting stuck if there's extra WAL files in pg_xlog. So still need the change in the backend to not call restore_command again for a WAL segment equal to or later than one that it already failed to restore. Or, we can truncate the trigger file to make it behave like a smart failover for the subsequent pg_standby calls. So, in fast mode, recovery_end_command would always fail to delete the trigger file, and cause warning. This is odd behavior, I think. We should change WARNING to DEBUG2 like RestoreArchivedFile() in the above code? I think we should just change the pg_standby example to use rm -f rather than rm. It seems useful to give a warning if the command fails. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] New trigger option of pg_standby
On Fri, 2009-05-15 at 03:49 +0900, Fujii Masao wrote: Hi, On Fri, May 15, 2009 at 12:36 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-05-13 at 21:43 +0100, Simon Riggs wrote: On Wed, 2009-05-13 at 21:26 +0300, Heikki Linnakangas wrote: This whole thing can be considered to be a new feature. recovery.conf will contain a new optional parameter: recovery_end_command (string) Implemented. + ereport(signaled ? FATAL : WARNING, + (errmsg(recovery_end_command \%s\: return code %d, + xlogRecoveryEndCmd, rc))); In fast failover case, pg_standby has to delete the trigger file immediately if it's found. Otherwise, recovery may go wrong as I already described. http://archives.postgresql.org/pgsql-hackers/2009-04/msg01139.php So, in fast mode, recovery_end_command would always fail to delete the trigger file, and cause warning. This is odd behavior, I think. We should change WARNING to DEBUG2 like RestoreArchivedFile() in the above code? Using rm -f would avoid the WARNING. I'd rather keep it at WARNING, since not sure what command I'll be running and what a non-zero rc means. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] pg_views definition format
On May 13, 5:37 pm, gsm...@gregsmith.com (Greg Smith) wrote:
On Wed, 13 May 2009, Kevin Field wrote:
Or would the only way to do this be to actually create a view and then
call pg_get_viewdef() and then delete the view?
Just make it a temporary view and then it drops when the session ends.
Here's a working shell example that transforms a view into the parsed form
and returns it:
$ v=select * from pg_views
$ p=`psql -Atc create temporary view x as ${v}; select
pg_get_viewdef('x'::regclass);`
$ echo $p
SELECT pg_views.schemaname, pg_views.viewname, pg_views.viewowner,
pg_views.definition FROM pg_views;
Thanks. This works more quickly than I thought it might, which is
good.
Something I ran into though when trying to extend this logic to rules:
for some reason rule definitions are compiled with create rule x as
in front of them, unlike views, which just have everything after the
as. I can keep the two parts separate and test accordingly, but it
seems a bit inconsistent.
--
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] pg_rules definition format
On May 14, 2:22 pm, Kevin Field kevinjamesfi...@gmail.com wrote: Something I ran into though when trying to extend this logic to rules: for some reason rule definitions are compiled with create rule x as in front of them, unlike views, which just have everything after the as. I can keep the two parts separate and test accordingly, but it seems a bit inconsistent. The fix isn't actually this clean in the end, since the 'fake' rule to be returned will have a different 'definition' (because its name is different) than the one we'd actually use to insert. So either we do some regexing or we have to back up the old rule's definition, drop the rule, insert it, get the new definiton, compare, and then if they're different, drop it again and put the old one back. -- 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] pg_rules definition format
On May 14, 2:22 pm, Kevin Field kevinjamesfi...@gmail.com wrote: Something I ran into though when trying to extend this logic to rules: for some reason rule definitions are compiled with create rule x as in front of them, unlike views, which just have everything after the as. I can keep the two parts separate and test accordingly, but it seems a bit inconsistent. The fix isn't actually this clean in the end, since the 'fake' rule to be returned will have a different 'definition' (because its name is different) than the one we'd actually use to insert. So either we do some regexing or we have to back up the old rule's definition, drop the rule, insert it, get the new definiton, compare, and then if they're different, drop it again and put the old one back. -- 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] New trigger option of pg_standby
I've finally committed Simon's recovery_end_command patch, as well as the changes to pg_standby. There's now smart and fast failover modes, chosen by the content of the trigger file, smart mode is the default. A fast trigger file is truncated, turning it into a smart trigger for subsequent pg_standby invocations. I believe this is now safe in all the combinations discussed, in both fast and smart mode, with or without extra WAL files copied to pg_xlog, and also if the last archived WAL file is incomplete. You now need to set up recovery_end_command to clean up the trigger file; pg_standby no longer does that automatically. Simon Riggs wrote: On Fri, 2009-05-15 at 03:49 +0900, Fujii Masao wrote: Hi, On Fri, May 15, 2009 at 12:36 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-05-13 at 21:43 +0100, Simon Riggs wrote: On Wed, 2009-05-13 at 21:26 +0300, Heikki Linnakangas wrote: This whole thing can be considered to be a new feature. recovery.conf will contain a new optional parameter: recovery_end_command (string) Implemented. + ereport(signaled ? FATAL : WARNING, + (errmsg(recovery_end_command \%s\: return code %d, + xlogRecoveryEndCmd, rc))); In fast failover case, pg_standby has to delete the trigger file immediately if it's found. Otherwise, recovery may go wrong as I already described. http://archives.postgresql.org/pgsql-hackers/2009-04/msg01139.php So, in fast mode, recovery_end_command would always fail to delete the trigger file, and cause warning. This is odd behavior, I think. We should change WARNING to DEBUG2 like RestoreArchivedFile() in the above code? Using rm -f would avoid the WARNING. I'd rather keep it at WARNING, since not sure what command I'll be running and what a non-zero rc means. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] [COMMITTERS] pgsql: Add recovery_end_command option to recovery.conf.
Alvaro Herrera alvhe...@commandprompt.com writes: Heikki Linnakangas wrote: Log Message: --- Add recovery_end_command option to recovery.conf. recovery_end_command is run at the end of archive recovery, providing a chance to do external cleanup. Modify pg_standby so that it no longer removes the trigger file, that is to be done using the recovery_end_command now. I think it's worth documenting this stuff in the release notes so that it shows up for the next beta ... Yeah, on it now. Good work getting the patch in! regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] valgrind error in tsvectorin
Was just running the regression tests under valgrind and aside from the usual false positives caused by structure padding I noticed this: ==19366== Source and destination overlap in memcpy(0x4BB7FC0, 0x4BB7FC0, 12) ==19366==at 0x4026B12: memcpy (mc_replace_strmem.c:402) ==19366==by 0x8389750: uniqueentry (tsvector.c:128) ==19366==by 0x8389C63: tsvectorin (tsvector.c:265) ==19366==by 0x83B1888: InputFunctionCall (fmgr.c:1878) ==19366==by 0x83B1B46: OidInputFunctionCall (fmgr.c:2009) ==19366==by 0x8171651: stringTypeDatum (parse_type.c:497) ==19366==by 0x8171CAC: coerce_type (parse_coerce.c:239) ==19366==by 0x8171A72: coerce_to_target_type (parse_coerce.c:86) ==19366==by 0x8166DB5: transformTypeCast (parse_expr.c:2016) ==19366==by 0x8162FA8: transformExpr (parse_expr.c:181) ==19366==by 0x8174990: transformTargetEntry (parse_target.c:75) ==19366==by 0x8174B01: transformTargetList (parse_target.c:145) After a quick glance at the code I suspect res and ptr end up pointing to the same object, perhaps the loop condition has a fencepost error. But I don't really understand what it's trying to do at all. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- 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] [COMMITTERS] pgsql: Translation updates
Alvaro Herrera wrote: Log Message: --- Translation updates I just now remembered that we have a new rule about including only 80% files, so some of these files may need to be removed. Sorry for the mess :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] valgrind errors
And lest anyone think Teodor and Oleg are the only ones that have that kind of problem, here are two in resolve_polymorphic_tupdesc that fire several times in the regression tests: ==20391== Source and destination overlap in strncpy(0x4BD91DA, 0x4BD91DA, 64) ==20391==at 0x4026CC4: strncpy (mc_replace_strmem.c:291) ==20391==by 0x83224CF: namestrcpy (name.c:217) ==20391==by 0x809635F: TupleDescInitEntry (tupdesc.c:463) ==20391==by 0x83B2BA1: resolve_polymorphic_tupdesc (funcapi.c:500) ==20391==by 0x83B272C: internal_get_result_type (funcapi.c:323) ==20391==by 0x83B256B: get_expr_result_type (funcapi.c:234) ==20391==by 0x816DE41: addRangeTableEntryForFunction (parse_relation.c:1138) ==20391==by 0x816023E: transformRangeFunction (parse_clause.c:594) ==20391==by 0x816059A: transformFromClauseItem (parse_clause.c:709) ==20391==by 0x815F686: transformFromClause (parse_clause.c:123) ==20391==by 0x813ADE3: transformSelectStmt (analyze.c:800) ==20391==by 0x8139B83: transformStmt (analyze.c:183) ==20372== Source and destination overlap in strncpy(0x4BD887D, 0x4BD887D, 64) ==20372==at 0x4026CC4: strncpy (mc_replace_strmem.c:291) ==20372==by 0x83224CF: namestrcpy (name.c:217) ==20372==by 0x809635F: TupleDescInitEntry (tupdesc.c:463) ==20372==by 0x83B2BEA: resolve_polymorphic_tupdesc (funcapi.c:507) ==20372==by 0x83B272C: internal_get_result_type (funcapi.c:323) ==20372==by 0x83B256B: get_expr_result_type (funcapi.c:234) ==20372==by 0x816DE41: addRangeTableEntryForFunction (parse_relation.c:1138) ==20372==by 0x816023E: transformRangeFunction (parse_clause.c:594) ==20372==by 0x816059A: transformFromClauseItem (parse_clause.c:709) ==20372==by 0x815F686: transformFromClause (parse_clause.c:123) ==20372==by 0x813ADE3: transformSelectStmt (analyze.c:800) ==20372==by 0x8139B83: transformStmt (analyze.c:183) I'm not sure if there are any realistic platforms where strcpy with source and destination exactly equal is really going to cause an actual problem, but... And another one in tsearch: ==20349== Source and destination overlap in memcpy(0x71ADAE4, 0x71ADAE4, 8) ==20349==at 0x4026B12: memcpy (mc_replace_strmem.c:402) ==20349==by 0x82DC762: dispell_lexize (dict_ispell.c:141) ==20349==by 0x83B0A66: FunctionCall4 (fmgr.c:1360) ==20349==by 0x82D315F: LexizeExec (ts_parse.c:208) ==20349==by 0x82D3776: parsetext (ts_parse.c:399) ==20349==by 0x82E1EBC: to_tsvector_byid (to_tsany.c:226) ==20349==by 0x81E9016: ExecMakeFunctionResult (execQual.c:1665) ==20349==by 0x81E9932: ExecEvalFunc (execQual.c:2097) ==20349==by 0x81ED49F: ExecEvalExprSwitchContext (execQual.c:4076) ==20349==by 0x8276D89: evaluate_expr (clauses.c:3841) ==20349==by 0x82763C0: evaluate_function (clauses.c:3448) ==20349==by 0x8275EA1: simplify_function (clauses.c:3252) And i think this is just uninitialized data at the end of the bitfield that is being masked off anyways: ==20253== Conditional jump or move depends on uninitialised value(s) ==20253==at 0x82F2C72: array_bitmap_copy (arrayfuncs.c:3854) ==20253==by 0x82EF4A3: array_set_slice (arrayfuncs.c:2538) ==20253==by 0x81E6B8C: ExecEvalArrayRef (execQual.c:397) ==20253==by 0x81EF8D7: ExecTargetList (execQual.c:4988) ==20253==by 0x81EFDA2: ExecProject (execQual.c:5203) ==20253==by 0x81EFF1F: ExecScan (execScan.c:143) ==20253==by 0x825: ExecSeqScan (nodeSeqscan.c:131) ==20253==by 0x81E5F3C: ExecProcNode (execProcnode.c:363) ==20253==by 0x81E3597: ExecutePlan (execMain.c:1504) ==20253==by 0x81E16F3: standard_ExecutorRun (execMain.c:309) ==20253==by 0x81E15A4: ExecutorRun (execMain.c:258) ==20253==by 0x82CD9E5: ProcessQuery (pquery.c:196) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- 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] [COMMITTERS] pgsql: Add recovery_end_command option to recovery.conf.
Jaime Casanova jcasa...@systemguards.com.ec writes: maybe we want to update share/recovery.conf.sample too? Good catch, done. regards, tom lane -- 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] valgrind error in tsvectorin
Gregory Stark st...@enterprisedb.com writes: Was just running the regression tests under valgrind and aside from the usual false positives caused by structure padding I noticed this: ==19366== Source and destination overlap in memcpy(0x4BB7FC0, 0x4BB7FC0, 12) ==19366==at 0x4026B12: memcpy (mc_replace_strmem.c:402) ==19366==by 0x8389750: uniqueentry (tsvector.c:128) ==19366==by 0x8389C63: tsvectorin (tsvector.c:265) ==19366==by 0x83B1888: InputFunctionCall (fmgr.c:1878) ==19366==by 0x83B1B46: OidInputFunctionCall (fmgr.c:2009) ==19366==by 0x8171651: stringTypeDatum (parse_type.c:497) ==19366==by 0x8171CAC: coerce_type (parse_coerce.c:239) ==19366==by 0x8171A72: coerce_to_target_type (parse_coerce.c:86) ==19366==by 0x8166DB5: transformTypeCast (parse_expr.c:2016) ==19366==by 0x8162FA8: transformExpr (parse_expr.c:181) ==19366==by 0x8174990: transformTargetEntry (parse_target.c:75) ==19366==by 0x8174B01: transformTargetList (parse_target.c:145) After a quick glance at the code I suspect res and ptr end up pointing to the same object, perhaps the loop condition has a fencepost error. But I don't really understand what it's trying to do at all. Yeah, it looks like the memcpy is sometimes unnecessary because res and ptr point to the same place. It might be worth cleaning up just to avoid the valgrind warning, but I doubt it would save any noticeable number of cycles. regards, tom lane -- 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] valgrind error in tsvectorin
Yeah, it looks like the memcpy is sometimes unnecessary because res and ptr point to the same place. It might be worth cleaning up just to avoid the valgrind warning, but I doubt it would save any noticeable number of cycles. I assume valgrind is warning about it because memcpy's behaviour is undefined if the blocks overlap. I'm having trouble imagining an implementation that would fail if they're precisely the same pointer though. -- greg -- 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] [COMMITTERS] pgsql: Translation updates
Alvaro Herrera wrote: Alvaro Herrera wrote: Log Message: --- Translation updates I just now remembered that we have a new rule about including only 80% files, so some of these files may need to be removed. Sorry for the mess :-( This is the list of files that would be removed in the backend if we enforced this policy: af.po: 91 (2%) remove cs.po: 564 (18%) remove de.po: 2808 (91%) es.po: 3075 (99%) fr.po: 3057 (99%) hr.po: 1682 (54%) remove hu.po: 477 (15%) remove it.po: 471 (15%) remove ja.po: 2832 (91%) ko.po: 2179 (70%) remove nb.po: 200 (6%) remove nl.po: 409 (13%) remove pl.po: 919 (29%) remove pt_BR.po: 2829 (91%) ro.po: 422 (13%) remove ru.po: 1272 (41%) remove sk.po: 2073 (67%) remove sl.po: 374 (12%) remove sv.po: 1856 (60%) remove tr.po: 2571 (83%) zh_CN.po: 1878 (60%) remove zh_TW.po: 1128 (36%) remove It is a long list of files to remove so I'm not sure about removing them hastily for today's beta. We can further discuss this before the 8.4 release. These numbers come from this snippet: for i in *po; do all=$(LC_ALL=C msgfmt -c -v $i 21) translated=$(echo $all | sed -e 's/\([0-9]*\) translated messages.*/\1/') percent=$(echo $translated 100 * $total / p | dc) echo -n $i: $translated ($percent%) if [ $percent -lt 80 ]; then echo remove else echo fi done $total has been previously set manually (to 3083). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] valgrind error in tsvectorin
Greg Stark st...@enterprisedb.com writes: Yeah, it looks like the memcpy is sometimes unnecessary because res and ptr point to the same place. It might be worth cleaning up just to avoid the valgrind warning, but I doubt it would save any noticeable number of cycles. I assume valgrind is warning about it because memcpy's behaviour is undefined if the blocks overlap. I'm having trouble imagining an implementation that would fail if they're precisely the same pointer though. Me either. A counterexample is that compilers typically implement structure assignment via memcpy, and the behavior of *d = *s is not undefined merely because d and s point to the same place. In this particular example it looks like res and ptr might be the same often enough that adding an if (res != ptr) test would save enough cycles to be worth its cost ... but it's pretty marginal. regards, tom lane -- 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] [COMMITTERS] pgsql: Translation updates
Alvaro Herrera alvhe...@commandprompt.com writes: I just now remembered that we have a new rule about including only 80% files, so some of these files may need to be removed. Sorry for the mess :-( It is a long list of files to remove so I'm not sure about removing them hastily for today's beta. We can further discuss this before the 8.4 release. I don't see any reason not to include partial translations in the beta --- maybe it would help motivate some people to contribute to the translations. Whether the 80% rule is appropriate for full releases is a different question. regards, tom lane -- 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] Optimizing Read-Only Scalability
Simon, So we can optimize away the scan through the procarray by doing two if tests, one outside of the lock, one inside. In normal running, both will be optimized away, though in read-only periods we would avoid much work. How much work would it be to work up a test patch? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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] New trigger option of pg_standby
Hi, On Fri, May 15, 2009 at 5:31 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I've finally committed Simon's recovery_end_command patch, as well as the changes to pg_standby. There's now smart and fast failover modes, chosen by the content of the trigger file, smart mode is the default. A fast trigger file is truncated, turning it into a smart trigger for subsequent pg_standby invocations. I believe this is now safe in all the combinations discussed, in both fast and smart mode, with or without extra WAL files copied to pg_xlog, and also if the last archived WAL file is incomplete. Thanks for revising my patch and committing it! This seems to work fine in all the case which I described. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Testing of parallel restore with current snapshot
Andrew, Tom, Just wanted to remark on my tests of this feature since the last set of patches. First of all, no locking. Yay. Andrew's latest algorithm tends to result in building indexes on the same table at the same time. This is excellent for most users; I'm on a client's site which is I/O bound and that approach is speeding up parallel load about 20% compared to the beta1 version. In other words, don't mess with it now. I think it's perfect. ;-) -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
