Re: [HACKERS] updateMinRecoveryPoint bug?
Simon Riggs wrote:
On Mon, 2009-12-28 at 14:40 +0200, Heikki Linnakangas wrote:
Fujii Masao wrote:
How about always resetting ControlFile-minRecoveryPoint to {0, 0} at the
beginning of a crash recovery, to fix the bug?
Yeah, that would work. I think it would be better to clear it in
CreateCheckPoint(), though, when we set the pointer to the new
checkpoint. That includes the shutdown checkpoint created at the end of
archive recovery. minRecoveryPoint should never be set during normal
operation, after all.
Sounds better.
Committed.
--
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
[HACKERS] solution to make static changes in pg_hba.conf file?
hi, i assigned super user privelledge to a user by specifing entries in pg_hba.conf file as hostallnewuser127.1.1.1 md5 and the default postgres user is made access to only the default postgres databse and is no more the super user. the problem is that this conf file is available to all and can be changed again. can someone assist me with a solution to make this changes static. thanks happy new year:-) Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL) accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL.
Re: [HACKERS] Cancelling idle in transaction state
On Tue, 29 Dec 2009, Simon Riggs wrote: The proposal is to send an additional NOTICE to the client and abort all open transactions and subtransactions (this is what I got from the previous discussion). Would this work with JDBC driver and/or general protocol clients? A Notice would be easy to overlook. The JDBC driver wraps that as a SQLWarning which callers need to explicitly check for (and rarely do in my experience). So when they run their next statement they'll get an error saying that the current transaction is aborted, but they'll have no idea why as the warning was silently eaten. I'd prefer the transaction cancellation to come as an Error because that's what it really is. The only downside I can see is that a client would get confused if: 1) Transaction starts. 2) Idle transaction is killed and error message is given. 3) Client issues rollback 4) Client gets error message from saying the transaction was cancelled. Kris Jurka -- 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] Cancelling idle in transaction state
On Wed, Dec 30, 2009 at 12:28 AM, Simon Riggs si...@2ndquadrant.com wrote: I had to write an additional function AbortAnyTransaction() which aborts all transactions and subtransactions and leaves the transaction in the aborted state, is there an existing function to do this? AbortOutOfAnyTransaction() But this would clean up completely and not leave the transaction in the aborted state. Subsequent commands will be executed just fine instead of being refused with the error message that the transaction is already aborted... Right? Joachim -- 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] Can we hide data from the superadmin
hi, i assigned super user privelledge to a user by specifing entries in pg_hba.conf file as hostallnewuser127.1.1.1 md5 and the default postgres user is made access to only the default postgres databse and is no more the super user. the problem is that this conf file is available to all and can be changed again. can someone assist me with a solution to make this changes static. thanks happy new year:-) - Original Message - From: Tarun Sharma To: pgsql-hackers@postgresql.org Sent: Tuesday, December 29, 2009 3:56 PM Subject: Can we hide data from the superadmin hi when a superuser is logged in to the postgres server ,he has priveledge to access all the databases. can we hide the structure of some of the databases from the superuser? like if he gives command to open such a database the postgres may ask for access password again. as in Oracle Database Vault, organizations can pro-actively safeguard application data stored in the Oracle database from being accessed by privileged database users kindly assist me if there exists any solution for this problem. thanks, tarun sharma Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the original intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL) accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL.
Re: [HACKERS] Hot Standy introduced problem with query cancel behavior
On Tue, Dec 29, 2009 at 4:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: This seems like a fairly bad idea. One of the intended use-cases is to be able to manually kill -INT a misbehaving backend. Assuming that there will be valid info about the signal in shared memory will break that. I never intended to change the current behavior. Actually I wanted to even enhance it by allowing to also cancel an idle transaction via SIGINT. We are free to define what should happen if there is no internal reason available because the signal has been sent manually. We can also use SIGUSR1 of course but then you cannot cancel an idle transaction just from the commandline. Not sure if this is necessary though but I would have liked it in the past already (I once used a version of slony that left transactions idle from time to time...) Joachim -- 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] Cancelling idle in transaction state
On Wed, 2009-12-30 at 05:02 -0500, Kris Jurka wrote: On Tue, 29 Dec 2009, Simon Riggs wrote: The proposal is to send an additional NOTICE to the client and abort all open transactions and subtransactions (this is what I got from the previous discussion). Would this work with JDBC driver and/or general protocol clients? A Notice would be easy to overlook. The JDBC driver wraps that as a SQLWarning which callers need to explicitly check for (and rarely do in my experience). So when they run their next statement they'll get an error saying that the current transaction is aborted, but they'll have no idea why as the warning was silently eaten. I'd prefer the transaction cancellation to come as an Error because that's what it really is. I'm not certain of all of these points, but here goes: AFAIK, NOTICE was suggested because it can be sent at any time, whereas ERRORs are only associated with statements. http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-ASYNC It is possible for NoticeResponse messages to be generated due to outside activity; for example, if the database administrator commands a fast database shutdown, the backend will send a NoticeResponse indicating this fact before closing the connection. Accordingly, frontends should always be prepared to accept and display NoticeResponse messages, even when the connection is nominally idle. Can JDBC accept a NOTICE, yet throw an error? NOTICEs have a SQLState field just like ERRORs do, so you should be able to special case that. I understand that this will mean that we are enhancing the protocol for this release, but I don't have a better suggestion. The only downside I can see is that a client would get confused if: 1) Transaction starts. 2) Idle transaction is killed and error message is given. 3) Client issues rollback 4) Client gets error message from saying the transaction was cancelled. Are you saying that the client should send rollback and that it should generate no message? -- Simon Riggs www.2ndQuadrant.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] Hot Standy introduced problem with query cancel behavior
On Wed, 2009-12-30 at 12:05 +0100, Joachim Wieland wrote: On Tue, Dec 29, 2009 at 4:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: This seems like a fairly bad idea. One of the intended use-cases is to be able to manually kill -INT a misbehaving backend. Assuming that there will be valid info about the signal in shared memory will break that. I never intended to change the current behavior. Actually I wanted to even enhance it by allowing to also cancel an idle transaction via SIGINT. We are free to define what should happen if there is no internal reason available because the signal has been sent manually. We can also use SIGUSR1 of course but then you cannot cancel an idle transaction just from the commandline. Not sure if this is necessary though but I would have liked it in the past already (I once used a version of slony that left transactions idle from time to time...) Andres mentioned this in relation to Startup process sending signals to backends. Startup needs to in-some cases issue a FATAL error also, which is a separate issue from the discusion around SIGINT. I will rework the FATAL case and continue to support you in finding a solution to the cancel-while-idle case. -- Simon Riggs www.2ndQuadrant.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] Cancelling idle in transaction state
On Wed, 2009-12-30 at 11:43 +0100, Joachim Wieland wrote: On Wed, Dec 30, 2009 at 12:28 AM, Simon Riggs si...@2ndquadrant.com wrote: I had to write an additional function AbortAnyTransaction() which aborts all transactions and subtransactions and leaves the transaction in the aborted state, is there an existing function to do this? AbortOutOfAnyTransaction() But this would clean up completely and not leave the transaction in the aborted state. True Subsequent commands will be executed just fine instead of being refused with the error message that the transaction is already aborted... True, but it is a subsequent transaction, not the same one. (I've checked). -- Simon Riggs www.2ndQuadrant.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] Cancelling idle in transaction state
Simon Riggs wrote: On Wed, 2009-12-30 at 05:02 -0500, Kris Jurka wrote: On Tue, 29 Dec 2009, Simon Riggs wrote: The proposal is to send an additional NOTICE to the client and abort all open transactions and subtransactions (this is what I got from the previous discussion). Would this work with JDBC driver and/or general protocol clients? A Notice would be easy to overlook. The JDBC driver wraps that as a SQLWarning which callers need to explicitly check for (and rarely do in my experience). So when they run their next statement they'll get an error saying that the current transaction is aborted, but they'll have no idea why as the warning was silently eaten. I'd prefer the transaction cancellation to come as an Error because that's what it really is. I'm not certain of all of these points, but here goes: AFAIK, NOTICE was suggested because it can be sent at any time, whereas ERRORs are only associated with statements. http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-ASYNC It is possible for NoticeResponse messages to be generated due to outside activity; for example, if the database administrator commands a fast database shutdown, the backend will send a NoticeResponse indicating this fact before closing the connection. Accordingly, frontends should always be prepared to accept and display NoticeResponse messages, even when the connection is nominally idle. Could we send an asynchronous notification immediately when the transaction is cancelled, but also change the error message you get in the subsequent commands. Clients that ignore the async notification would still see a proper error message at the ERROR. Something like: ERROR: current transaction was aborted because of conflict with recovery, commands ignored until end of transaction block instead of the usual current transaction is aborted, commands ignored until end of transaction block message. -- 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] solution to make static changes in pg_hba.conf file?
2009/12/30 Tarun Sharma tarun.sha...@newgen.co.in: hi, i assigned super user privelledge to a user by specifing entries in pg_hba.conf file as host all newuser 127.1.1.1 md5 and the default postgres user is made access to only the default postgres databse and is no more the super user. the problem is that this conf file is available to all and can be changed again. can someone assist me with a solution to make this changes static. Only root, or someone who at least has full permissions on the PostgreSQL datafiles, can do this by default. If you have granted other users access to do it, you sohuld revoke those. One way can be to move the pg_hba.conf file to a different directory and lock down the permissions there. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Cancelling idle in transaction state
On Wed, 30 Dec 2009, Simon Riggs wrote: http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-ASYNC It is possible for NoticeResponse messages to be generated due to outside activity; for example, if the database administrator commands a fast database shutdown, the backend will send a NoticeResponse indicating this fact before closing the connection. Accordingly, frontends should always be prepared to accept and display NoticeResponse messages, even when the connection is nominally idle. The problem is that frontends won't check the backend connection until they've already been given the next command to execute at which point it's too late. I think a lot of the discussion on this thread is wishful thinking about when a frontend will see the message and what they'll do with it. You would either need a multithreaded frontend that had some type of callback mechanism for these notices, or you'd need to poll the socket every so often to see if you'd received a notice. I don't think that describes most applications or client libraries. Can JDBC accept a NOTICE, yet throw an error? NOTICEs have a SQLState field just like ERRORs do, so you should be able to special case that. Yes, that's possible. The only downside I can see is that a client would get confused if: 1) Transaction starts. 2) Idle transaction is killed and error message is given. 3) Client issues rollback 4) Client gets error message from saying the transaction was cancelled. Are you saying that the client should send rollback and that it should generate no message? No, I'm saying if for some business logic reason the client decided it needed to rollback as it hadn't seen the error message yet. Kris Jurka -- 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] msvc build fails in Japanese environment
2009/12/30 Hiroshi Inoue in...@tpf.co.jp: Hi, I try to build msvc version of postgres using the current cvs and get the following error. Unable to determine vcbuild version from first line of output! at src/tools/msvc/Solution.pm line 70. The error comes from the following output of vcbuild /? in my environment. Microsoft(R) Visual C++ Project Builder - コマンド ライン バージョン 8.00.50727 The Command Line Version part is localized. In addtion there's no space between Mircrosoft and (R). How typical of Microsoft to localize different strings in different languages :S Since this is not the first time we've run into issues specifically with the Japanese version of Windows, perhaps we should try to get a japanese buildfarm member running? Is this something you could help with? Or somebody else from our Japanese community? The attahced patch fixes the error in my environment. Applied, thanks. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Cancelling idle in transaction state
On Wed, 30 Dec 2009, Heikki Linnakangas wrote: Could we send an asynchronous notification immediately when the transaction is cancelled, but also change the error message you get in the subsequent commands. Clients that ignore the async notification would still see a proper error message at the ERROR. +1 Kris Jurka -- 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] Cancelling idle in transaction state
On 30/12/2009 7:37 PM, Simon Riggs wrote: Can JDBC accept a NOTICE, yet throw an error? NOTICEs have a SQLState field just like ERRORs do, so you should be able to special case that. The JDBC driver would have to throw when the app code next interacted with the connection object anyway. It can't asynchronously throw an exception. Since the next interaction that can throw SQLException() is likely to be setup for or execution of a query, I'm not sure it makes any difference to the JDBC user whether query cancellation is reported as a NOTICE or an ERROR behind the scenes. Since the proposed patch leaves cancelled transactions in the error state, rather than closing them and leaving the connection clean and idle, it doesn't matter much if a client doesn't understand or check for the NOTICE. The app code will try to do work on the connection and that work will fail because the transaction is aborted, resulting in a normal SQLException reporting that the current transaction is aborted JDBC-using code has to be prepared to handle exceptions at any point of interaction with the JDBC driver anyway, and any code that isn't is buggy. Consequently there's LOTS of buggy JDBC code out there :-( as people often ignore exceptions thrown during operations they think can't fail. However, such buggy code is already broken by pg_cancel_backend() and pg_terminate_backend(), and won't be broken any more or differently by the proposed change, so I don't see a problem with it. Personally, I'd be happy to leave the JDBC driver as it was. It might be kind of handy if I could getWarnings() on the connection object without blocking so I could call it before I executed a statement on the connection ... but that'd always introduce a race between transaction cancellation/timeout and statement execution, so code must always be prepared to handle timeout/cancellation related failure anyway. As you say, the driver can special-case connection cancelled NOTICE mesages as errors and throw them at next user interaction it wants. But I'm not sure that's anything more than a kind of nice-to-have cosmetic feature. If the JDBC driver handled the NOTICE and threw a more informative SQLException to tell the app why the transaction was dead, that'd be nice, but hardly vital. It'd want to preserve the notice as an SQLWarning as well. I understand that this will mean that we are enhancing the protocol for this release, but I don't have a better suggestion. Only in an extremely backward compatible way - and it's more of a behavior change for the backend than a protocol change. Pg's backends change behaviour a whole lot more than that in a typical release... The only downside I can see is that a client would get confused if: 1) Transaction starts. 2) Idle transaction is killed and error message is given. 3) Client issues rollback 4) Client gets error message from saying the transaction was cancelled. For JDBC users, there is no transaction in progress is only reported as a SQLWarning via getWarnings(), so I'd be surprised if anything used it for more than logging or debugging purposes. Are you saying that the client should send rollback and that it should generate no message? -- Craig Ringer -- 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] exec_execute_message crash
While inspecting a complain from a pgpool user, I found that
PostgreSQL crushes with following statck trace:
#0 0x0826436a in list_length (l=0xaabe4e28)
at ../../../src/include/nodes/pg_list.h:94
#1 0x08262168 in IsTransactionStmtList (parseTrees=0xaabe4e28)
at postgres.c:2429
#2 0x0826132e in exec_execute_message (portal_name=0x857bab0 , max_rows=0)
at postgres.c:1824
#3 0x08263b2a in PostgresMain (argc=4, argv=0x84f6c28,
username=0x84f6b08 t-ishii) at postgres.c:3671
#4 0x0823299e in BackendRun (port=0x8511e68) at postmaster.c:3449
#5 0x08231f78 in BackendStartup (port=0x8511e68) at postmaster.c:3063
#6 0x0822f90a in ServerLoop () at postmaster.c:1387
#7 0x0822f131 in PostmasterMain (argc=3, argv=0x84f4bf8) at postmaster.c:1040
#8 0x081c6217 in main (argc=3, argv=0x84f4bf8) at main.c:188
Ok, I think I understand what's going on.
parse
bind
describe
execute
This sequence of commands create cached plan in unnamed portal.
$5 = {name = 0x8574de4 , prepStmtName = 0x0, heap = 0x8598400,
resowner = 0x8598488, cleanup = 0x81632ca PortalCleanup, createSubid = 1,
sourceText = 0x85ab818 SELECT omitted...,
commandTag = 0x84682ca SELECT, stmts = 0xaabf43b0, cplan = 0xaabf4950,
portalParams = 0x0, strategy = PORTAL_ONE_SELECT, cursorOptions = 4,
status = PORTAL_READY, queryDesc = 0x85abc20, tupDesc = 0x85ddcb0,
formats = 0x85abc68, holdStore = 0x0, holdContext = 0x0, atStart = 1 '\001',
atEnd = 1 '\001', posOverflow = 0 '\0', portalPos = 0,
creation_time = 315487957498169, visible = 1 '\001'}
The cached plan(portal-cplan) and statements(portal-stmts) are
created by exec_bind_message():
/*
* Revalidate the cached plan; this may result in replanning.
Any
* cruft will be generated in MessageContext. The plan
refcount will
* be assigned to the Portal, so it will be released at portal
* destruction.
*/
cplan = RevalidateCachedPlan(psrc, false);
plan_list = cplan-stmt_list;
Please note that cplan and stmts belong to the same memory context.
Then following commands are coming:
parse invalid SQL thus abort a transaction
bind (error)
describe (error)
execute (crash)
parse causes transaction to abort, which causes call to
AbortCurrentTransaction-AbortTransaction-AtAbort_portals-ReleaseCachedPlan.
It
calls ReleaseCachePlan(portal-cplan). ReleaseCachePlan calls
MemoryContextDelete(plan-context) which destroys both portal-cplan
and portal-stmts.
That was the reason why I had segfault by accessing portal-stmts.
To fix this I think exec_execute_message should throw an error if
portal-cleanup is NULL, since portal-cleanup is NULLed by
AtAbort_Portals at transaction abort (or portal is dropped).
Here is a suggested fix:
diff -c postgres.c~ postgres.c
*** postgres.c~ 2009-06-18 19:08:08.0 +0900
--- postgres.c 2009-12-30 21:34:49.0 +0900
***
*** 1804,1810
dest = DestRemoteExecute;
portal = GetPortalByName(portal_name);
! if (!PortalIsValid(portal))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_CURSOR),
errmsg(portal \%s\ does not exist,
portal_name)));
--- 1804,1810
dest = DestRemoteExecute;
portal = GetPortalByName(portal_name);
! if (!PortalIsValid(portal) || (PortalIsValid(portal) portal-cleanup
== NULL))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_CURSOR),
errmsg(portal \%s\ does not exist,
portal_name)));
--
Tatsuo Ishii
SRA OSS, Inc. Japan
--
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] exec_execute_message crash
Tatsuo Ishii wrote: ! if (!PortalIsValid(portal) || (PortalIsValid(portal) portal-cleanup == NULL)) Surely the second call to PortalIsValid() is redundant. if (( !PortalIsValid(portal)) || portal-cleanup == NULL) should do it, no? cheers 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] Backup history file should be replicated in Streaming Replication?
Fujii Masao wrote:
On Thu, Dec 24, 2009 at 1:39 PM, Fujii Masao masao.fu...@gmail.com wrote:
On Wed, Dec 23, 2009 at 7:50 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Ok. How about writing the history file in pg_stop_backup() for
informational purposes only. Ie. never read it, but rely on the WAL
records instead.
Sounds good. I'll make such change as a self-contained patch.
Done. Please see the attached patch.
Design:
* pg_stop_backup writes the backup-end xlog record which contains
the backup starting point.
* In archive recovery, the startup process doesn't mark the database
as consistent until it has read the backup-end record.
* A backup history file is still created as in the past, but is never
used.
As the patch stands, reachedBackupEnd is never set to true if starting
from a restore point after the end-of-backup. We'll need to store the
information that we've reached end-of-backup somewhere on disk.
Here's is modified patch that adds a new backupStartPoint field to
pg_control for that + some other minor editorialization.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 783725c..63884eb 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -515,8 +515,7 @@ static void xlog_outrec(StringInfo buf, XLogRecord *record);
#endif
static void issue_xlog_fsync(void);
static void pg_start_backup_callback(int code, Datum arg);
-static bool read_backup_label(XLogRecPtr *checkPointLoc,
- XLogRecPtr *minRecoveryLoc);
+static bool read_backup_label(XLogRecPtr *checkPointLoc);
static void rm_redo_error_callback(void *arg);
static int get_sync_bit(int method);
@@ -5355,7 +5354,6 @@ StartupXLOG(void)
bool haveBackupLabel = false;
XLogRecPtr RecPtr,
checkPointLoc,
-backupStopLoc,
EndOfLog;
uint32 endLogId;
uint32 endLogSeg;
@@ -5454,7 +5452,7 @@ StartupXLOG(void)
recoveryTargetTLI,
ControlFile-checkPointCopy.ThisTimeLineID)));
- if (read_backup_label(checkPointLoc, backupStopLoc))
+ if (read_backup_label(checkPointLoc))
{
/*
* When a backup_label file is present, we want to roll forward from
@@ -5597,11 +5595,23 @@ StartupXLOG(void)
ControlFile-prevCheckPoint = ControlFile-checkPoint;
ControlFile-checkPoint = checkPointLoc;
ControlFile-checkPointCopy = checkPoint;
- if (backupStopLoc.xlogid != 0 || backupStopLoc.xrecoff != 0)
+ if (InArchiveRecovery)
+ {
+ /* initialize minRecoveryPoint if not set yet */
+ if (XLByteLT(ControlFile-minRecoveryPoint, checkPoint.redo))
+ControlFile-minRecoveryPoint = checkPoint.redo;
+ }
+ else
{
- if (XLByteLT(ControlFile-minRecoveryPoint, backupStopLoc))
-ControlFile-minRecoveryPoint = backupStopLoc;
+ XLogRecPtr InvalidXLogRecPtr = {0, 0};
+ ControlFile-minRecoveryPoint = InvalidXLogRecPtr;
}
+ /*
+ * set backupStartupPoint if we're starting archive recovery from a
+ * base backup.
+ */
+ if (haveBackupLabel)
+ ControlFile-backupStartPoint = checkPoint.redo;
ControlFile-time = (pg_time_t) time(NULL);
/* No need to hold ControlFileLock yet, we aren't up far enough */
UpdateControlFile();
@@ -5703,15 +5713,9 @@ StartupXLOG(void)
InRedo = true;
- if (minRecoveryPoint.xlogid == 0 minRecoveryPoint.xrecoff == 0)
-ereport(LOG,
- (errmsg(redo starts at %X/%X,
-ReadRecPtr.xlogid, ReadRecPtr.xrecoff)));
- else
-ereport(LOG,
- (errmsg(redo starts at %X/%X, consistency will be reached at %X/%X,
-ReadRecPtr.xlogid, ReadRecPtr.xrecoff,
- minRecoveryPoint.xlogid, minRecoveryPoint.xrecoff)));
+ ereport(LOG,
+ (errmsg(redo starts at %X/%X,
+ ReadRecPtr.xlogid, ReadRecPtr.xrecoff)));
/*
* Let postmaster know we've started redo now, so that it can
@@ -5771,7 +5775,8 @@ StartupXLOG(void)
* Have we passed our safe starting point?
*/
if (!reachedMinRecoveryPoint
- XLByteLE(minRecoveryPoint, EndRecPtr))
+ XLByteLE(minRecoveryPoint, EndRecPtr)
+ XLogRecPtrIsInvalid(ControlFile-backupStartPoint))
{
reachedMinRecoveryPoint = true;
ereport(LOG,
@@ -5877,7 +5882,9 @@ StartupXLOG(void)
* be further ahead --- ControlFile-minRecoveryPoint cannot have been
* advanced beyond the WAL we processed.
*/
- if (InRecovery XLByteLT(EndOfLog, minRecoveryPoint))
+ if (InArchiveRecovery
+ (XLByteLT(EndOfLog, minRecoveryPoint) ||
+ !XLogRecPtrIsInvalid(ControlFile-backupStartPoint)))
{
if (reachedStopPoint) /* stopped because of stop request */
ereport(FATAL,
@@ -7310,6 +7317,32 @@ xlog_redo(XLogRecPtr lsn, XLogRecord *record)
{
/* nothing to do here */
}
+ else if (info == XLOG_BACKUP_END)
+ {
+ XLogRecPtr startpoint;
+ memcpy(startpoint, XLogRecGetData(record), sizeof(startpoint));
+
+ if (XLByteEQ(ControlFile-backupStartPoint, startpoint))
+ {
+ /*
+ *
Re: [HACKERS] Backup history file should be replicated in Streaming Replication?
Simon Riggs wrote: On Wed, 2009-12-23 at 12:50 +0200, Heikki Linnakangas wrote: I just realized that the current history file fails to recognize this scenario: 1. pg_start_backup() 2. cp -a $PGDATA data-backup 3. create data-backup/recovery.conf 4. postmaster -D data-backup That is, starting postmaster on a data directory, without ever calling pg_stop_backup(). Because pg_stop_backup() was not called, the history file is not there, and recovery won't complain about not reaching the safe starting point. That is of course a case of don't do that!, but perhaps we should refuse to start up if the backup history file is not found? At least in the WAL-based approach, I think we should refuse to start up if we don't see the pg_stop_backup WAL record. The code has always been capable of starting without this, which was considered a feature to be able start from a hot copy. Why is that desirable? The system is in an inconsistent state. To force it, you can always use pg_resetxlog. -- 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] exec_execute_message crash
Tatsuo Ishii wrote: ! if (!PortalIsValid(portal) || (PortalIsValid(portal) portal-cleanup == NULL)) Surely the second call to PortalIsValid() is redundant. if (( !PortalIsValid(portal)) || portal-cleanup == NULL) should do it, no? Oops. You are right. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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] KNNGiST for knn-search (WIP)
Robert, On Wed, 30 Dec 2009, Robert Haas wrote: Based on the feedback provided on this patch so far, it looks like some changes are probably needed, but it's not entirely clear whether the feedback provided is sufficient to provide guidance on what changes should be made. It does also need to be updated to CVS HEAD, as it no longer applies cleanly. this is not a problem. I tend to feel that we should probably target this for 8.6 rather than 8.5. We are down to the last CommitFest, and while we don't have a nailed-down criterion for what is too big for the last CommitFest of a given release cycle, this is definitely a big, invasive patch. This patch weights in at over 2400 adds/removes, and it's not boilerplate stuff like updates to pg_proc entries, but real, significant changes. I'm worried that applying something like this late in the release cycle is just not a good idea, especially given the fact that it probably still needs significant revising. However, I'm fairly conservative by nature, so perhaps someone else will have a different opinion, or maybe there is a way to restructure it so that the needed changes are less invasive. the patch adds new strategy of gist tree traverse and doesn't change old one, so there is no risk to ruin old code. I'm all for good conservatism, but this is not the case, else we wouldn't have GiST at all. We are very interested in the KNN to be in the 8.5 and we're ready to fix any issues. From metodological point of view I don't quite understand how to measure the value of development, I mean what'is a big patch, invasive patch. Should we prefer cosmetic pathces, spelling fixes, etc ? Of course, they are easy for refering, but people are waiting from us not just fixes, but new features. For example, KNN-GiST is a big improvement for PostGIS community, which is a big part of postgres users. Actually, it's PostGIS community, which supported our work. Now, what we should say them ? The patch was too big and invasive, so, sorry, wait one year more ? I think it's not good. Robert, I'm not against you, it's your right to have your opinion. I address this to other developers. It's important for us, since we have several other patches ready, for example, long awaited phrase search (http://www.sai.msu.su/~megera/wiki/2009-08-12). We postponed it, since it was supposed that EDB will support it, but, hey, it wont. We did it for our own. Teodor insist to submit it for 8.5, but I'm now begin to hesitate, what if this patch will be also too big. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Cancelling idle in transaction state
Craig Ringer cr...@postnewspapers.com.au wrote: It might be kind of handy if I could getWarnings() on the connection object without blocking so I could call it before I executed a statement on the connection ... but that'd always introduce a race between transaction cancellation/timeout and statement execution, so code must always be prepared to handle timeout/cancellation related failure anyway. +1 (I think) If I'm understanding this, it sounds to me like it would be most appropriate for the NOTICE to generate a warning at the connection level and for the next request to throw an exception in the format suggested by Heikki -- which I think is what Craig is suggesting. -Kevin -- 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] test/example does not support win32.
Hiroshi Saito wrote: Hi Tom-san. Um, How do you consider sample which cannot build? I think testlibpq2.c is missing a couple of system includes, sys/types.h and unistd.h (or alternatively select.h); and testlibpq3.c is missing stdint.h. Or so say my (POSIX) manpages anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Can we hide data from the superadmin
Tarun Sharma wrote: hi, i assigned super user privelledge to a user by specifing entries in pg_hba.conf file as hostallnewuser127.1.1.1 md5 and the default postgres user is made access to only the default postgres databse and is no more the super user. the problem is that this conf file is available to all and can be changed again. It is only available to all if all is somebody who has superuser access to the operating system, which is something you should avoid. If you don't trust your OS superuser, there's nothing you can do. -- 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] point_ops for GiST
Sync with current CVS -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ point_ops-0.5.gz Description: Unix tar archive -- 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] Stats for inheritance trees
Robert Haas robertmh...@gmail.com writes: If you'd be willing to look over the latest version of my per-tablespace random_page_cost/seq_page_cost patch, which I posted to -hackers some time in the last few days, I can get that committed and then start working on this, if you'd like. I think Alvaro would actually be the right person to review that, since the reloptions code is almost entirely his work. 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] Add subdirectory support for DATA/DOCS with PGXS
Alvaro Herrera wrote: The attached patch is a prototype which allows the user to specify a new MODULEDIR variable in a module makefile which, if specified, will install DATA and DOCS items in contrib/$(MODULEDIR) rather than just contrib. If MODULEDIR is left unspecified, the files will simply be stored directly in contrib/ as before. As a proof of its usefulness, you could remove DATA_TSEARCH and replace it with usage of MODULEDIR, right? Not in its current form because PGXS always places files underneath a contrib/ subdirectory within datadir. However, if people are happier with this approach then it shouldn't be too hard to alter things so that my PGXS Makefile would look like this: MODULE_big=postgis-1.5 MODULEDIR=contrib/$(MODULE_big) Once in this form it should then be possible to use this code to replace the DATA_TSEARCH variable that is currently in place. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs -- 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] Add subdirectory support for DATA/DOCS with PGXS
Mark Cave-Ayland mark.cave-ayl...@siriusit.co.uk writes: Alvaro Herrera wrote: As a proof of its usefulness, you could remove DATA_TSEARCH and replace it with usage of MODULEDIR, right? Not in its current form because PGXS always places files underneath a contrib/ subdirectory within datadir. However, if people are happier with this approach then it shouldn't be too hard to alter things so that my PGXS Makefile would look like this: MODULE_big=postgis-1.5 MODULEDIR=contrib/$(MODULE_big) If you can set it up in such a way that the default behavior doesn't change, this would be workable. I don't think we want people to suddenly find their stuff installing in the wrong place. It probably wouldn't be that hard, something along the lines of ifndef MODULEDIR MODULEDIR=contrib endif ought to do it no? 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] test/example does not support win32.
Hi Alvaro-san. Yes, I thinks that it is an exact idea. However, this example was not helped. fd_set complains Thanks! It seems that pg_bench takes the thing same again into consideration. Anyway, If it is called example of end-user code, what is the evasion method of fd_set? Regards, Hiroshi Saito - Original Message - From: Alvaro Herrera alvhe...@commandprompt.com Hiroshi Saito wrote: Hi Tom-san. Um, How do you consider sample which cannot build? I think testlibpq2.c is missing a couple of system includes, sys/types.h and unistd.h (or alternatively select.h); and testlibpq3.c is missing stdint.h. Or so say my (POSIX) manpages anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Stats for inheritance trees
Tom Lane escribió: Robert Haas robertmh...@gmail.com writes: If you'd be willing to look over the latest version of my per-tablespace random_page_cost/seq_page_cost patch, which I posted to -hackers some time in the last few days, I can get that committed and then start working on this, if you'd like. I think Alvaro would actually be the right person to review that, since the reloptions code is almost entirely his work. I can't promise anything right now though, as my wife could get with labour very soon ... -- 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] krb_server_keyfile setting doesn't work on Windows
Hi,
As far as I tested, the krb_server_keyfile setting
in postgres.conf doesn't work on Windows.
Because gssapi32.dll(krb5_32.dll) seems to call
getenv(KRB5_KTNAME) in msvcr71, postgres.exe
should call putenv(KRB5_KTNAME=...) in msvcr71.
The attached patch fixes the problem in my test
case.
regards,
Hiroshi Inoue
Index: auth.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/libpq/auth.c,v
retrieving revision 1.188
diff -c -r1.188 auth.c
*** auth.c 12 Dec 2009 21:35:21 - 1.188
--- auth.c 30 Dec 2009 15:03:51 -
***
*** 877,882
--- 877,905
errdetail(%s: %s, msg_major, msg_minor)));
}
+ #ifdefWIN32
+ static void
+ msvc_krb5_ktname(const char *kt_path)
+ {
+ typedef int (_cdecl * PUTENVPROC) (const char *);
+ const char *msvcrdll = msvcr71;
+ static boolinitialized = false;
+ HMODULE hmodule;
+ static PUTENVPROC putenvFunc = NULL;
+
+ if (initialized)
+ {
+ if (!putenvFunc)
+ return;
+ }
+ else if (hmodule = GetModuleHandle(msvcrdll), hmodule != NULL)
+ putenvFunc = (PUTENVPROC) GetProcAddress(hmodule, _putenv);
+ initialized = true;
+ if (putenvFunc != NULL)
+ putenvFunc(kt_path);
+ }
+ #endif /* WIN32 */
+
static int
pg_GSS_recvauth(Port *port)
{
***
*** 923,928
--- 946,954
return STATUS_ERROR;
}
snprintf(kt_path, kt_len, KRB5_KTNAME=%s,
pg_krb_server_keyfile);
+ #ifdefWIN32
+ msvc_krb5_ktname(kt_path);
+ #endif /* WIN32 */
putenv(kt_path);
}
}
--
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] test/example does not support win32.
Hiroshi Saito z-sa...@guitar.ocn.ne.jp writes: Yes, I thinks that it is an exact idea. However, this example was not helped. fd_set complains Thanks! It seems that pg_bench takes the thing same again into consideration. Anyway, If it is called example of end-user code, what is the evasion method of fd_set? On reflection I think it's just wrong to expect that the examples will compile out-of-the-box on every platform. The only way that that can possibly happen is if they depend on our configuration infrastructure, which is exactly what I feel they should not depend on. Any client program that has ambitions of portability is going to have its own autoconf stuff, so injecting ours into a piece of sample code is just going to result in headaches. Even including only pg_config.h would be a serious invasion of application namespace. Looking at pgbench, or any other one of our client-side programs, is not relevant to the point here. Those programs *are* supposed to rely on the PG autoconf environment. We can certainly add some more standard #includes to the examples if they're obviously missing some. But that isn't going to get us to a point where they'll compile everywhere without change. 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] test/example does not support win32.
Tom Lane wrote: Hiroshi Saito z-sa...@guitar.ocn.ne.jp writes: Yes, I thinks that it is an exact idea. However, this example was not helped. fd_set complains Thanks! It seems that pg_bench takes the thing same again into consideration. Anyway, If it is called example of end-user code, what is the evasion method of fd_set? On reflection I think it's just wrong to expect that the examples will compile out-of-the-box on every platform. The only way that that can possibly happen is if they depend on our configuration infrastructure, which is exactly what I feel they should not depend on. Any client program that has ambitions of portability is going to have its own autoconf stuff, so injecting ours into a piece of sample code is just going to result in headaches. Even including only pg_config.h would be a serious invasion of application namespace. Looking at pgbench, or any other one of our client-side programs, is not relevant to the point here. Those programs *are* supposed to rely on the PG autoconf environment. We can certainly add some more standard #includes to the examples if they're obviously missing some. But that isn't going to get us to a point where they'll compile everywhere without change. Well, those example programs are pretty clean libpq apps so I don't see why they should using platform-specific stuff. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] test/example does not support win32.
Bruce Momjian br...@momjian.us writes: Well, those example programs are pretty clean libpq apps so I don't see why they should using platform-specific stuff. Example #2 depends on select(), which depends on fd_set, so you're already into territory where there are issues. 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] test/example does not support win32.
Tom Lane wrote: Hiroshi Saito z-sa...@guitar.ocn.ne.jp writes: Yes, I thinks that it is an exact idea. However, this example was not helped. fd_set complains Thanks! It seems that pg_bench takes the thing same again into consideration. Anyway, If it is called example of end-user code, what is the evasion method of fd_set? On reflection I think it's just wrong to expect that the examples will compile out-of-the-box on every platform. The only way that that can possibly happen is if they depend on our configuration infrastructure, which is exactly what I feel they should not depend on. Any client program that has ambitions of portability is going to have its own autoconf stuff, so injecting ours into a piece of sample code is just going to result in headaches. Even including only pg_config.h would be a serious invasion of application namespace. Looking at pgbench, or any other one of our client-side programs, is not relevant to the point here. Those programs *are* supposed to rely on the PG autoconf environment. We can certainly add some more standard #includes to the examples if they're obviously missing some. But that isn't going to get us to a point where they'll compile everywhere without change. That would be all good and well if we didn't already rely on the configure setup. But we do - the Makefile includes src/Makefile.global, which is built by configure. Anyway, let's see how far we can get with including some standard header files. cheers 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] test/example does not support win32.
Hi Andrew-san. Although this is a standard in windows. *** testlibpq2.c.orig Wed Dec 30 13:19:03 2009 --- testlibpq2.cThu Dec 31 00:52:52 2009 *** *** 24,34 --- 24,39 * * INSERT INTO TBL1 VALUES (10); */ + + #ifdef WIN32 + #include windows.h + #endif #include stdio.h #include stdlib.h #include string.h #include errno.h #include sys/time.h + #include sys/types.h #include libpq-fe.h static void Does this become the standard which you consider? or #IFDEF Isn't it allowed? Regards, Hiroshi Saito - Original Message - From: Andrew Dunstan and...@dunslane.net To: Tom Lane t...@sss.pgh.pa.us Cc: Hiroshi Saito z-sa...@guitar.ocn.ne.jp; Alvaro Herrera alvhe...@commandprompt.com; pgsql-hackers pgsql-hackers@postgresql.org; Bruce Momjian br...@momjian.us Sent: Thursday, December 31, 2009 12:45 AM Subject: Re: [HACKERS] test/example does not support win32. Tom Lane wrote: Hiroshi Saito z-sa...@guitar.ocn.ne.jp writes: Yes, I thinks that it is an exact idea. However, this example was not helped. fd_set complains Thanks! It seems that pg_bench takes the thing same again into consideration. Anyway, If it is called example of end-user code, what is the evasion method of fd_set? On reflection I think it's just wrong to expect that the examples will compile out-of-the-box on every platform. The only way that that can possibly happen is if they depend on our configuration infrastructure, which is exactly what I feel they should not depend on. Any client program that has ambitions of portability is going to have its own autoconf stuff, so injecting ours into a piece of sample code is just going to result in headaches. Even including only pg_config.h would be a serious invasion of application namespace. Looking at pgbench, or any other one of our client-side programs, is not relevant to the point here. Those programs *are* supposed to rely on the PG autoconf environment. We can certainly add some more standard #includes to the examples if they're obviously missing some. But that isn't going to get us to a point where they'll compile everywhere without change. That would be all good and well if we didn't already rely on the configure setup. But we do - the Makefile includes src/Makefile.global, which is built by configure. Anyway, let's see how far we can get with including some standard header files. cheers 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] Thoughts on statistics for continuously advancing columns
Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: My thoughts on dealing with this intelligently without a major change to statstics gathering went along these lines: 1. add columns to pg_statistic to hold estimates of upper and lower bounds growth between analyzes. This seems like a fundamentally broken approach I don't have a better idea at the moment :-( It's been a while since I've been bitten by this issue -- the last time was under Sybase. The Sybase suggestion was to either add dummy rows [YUCK!] to set the extreme bounds or to lie to the optimizer by fudging the statistics after each generation. Perhaps we could do better by adding columns for high and low bounds to pg_statistic. These would not be set by ANALYZE, but user-modifiable to cover exactly this problem? NULL would mean current behavior? -Kevin -- 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] test/example does not support win32.
Hiroshi Saito wrote: Hi Andrew-san. Although this is a standard in windows. *** testlibpq2.c.orig Wed Dec 30 13:19:03 2009 --- testlibpq2.cThu Dec 31 00:52:52 2009 *** *** 24,34 --- 24,39 * * INSERT INTO TBL1 VALUES (10); */ + + #ifdef WIN32 + #include windows.h + #endif #include stdio.h #include stdlib.h #include string.h #include errno.h #include sys/time.h + #include sys/types.h #include libpq-fe.h static void Does this become the standard which you consider? or #IFDEF Isn't it allowed? I certainly think we can use ifdefs. This addition seems OK to me at first glance. Does it solve the problem you encountered? cheers 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] test/example does not support win32.
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: On reflection I think it's just wrong to expect that the examples will compile out-of-the-box on every platform. That would be all good and well if we didn't already rely on the configure setup. But we do - the Makefile includes src/Makefile.global, which is built by configure. That makefile is not part of the examples. It wouldn't get copied and pasted into someone's source code. Anyway, let's see how far we can get with including some standard header files. Sure, no objection to that. It's when somebody starts wanting to use HAVE_FOO symbols that I get unhappy. 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] krb_server_keyfile setting doesn't work on Windows
Hiroshi Inoue in...@tpf.co.jp writes: Because gssapi32.dll(krb5_32.dll) seems to call getenv(KRB5_KTNAME) in msvcr71, postgres.exe should call putenv(KRB5_KTNAME=...) in msvcr71. The attached patch fixes the problem in my test case. Don't we already have something like that in our src/port putenv substitute? 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] Thoughts on statistics for continuously advancing columns
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: I don't have a better idea at the moment :-( It's been a while since I've been bitten by this issue -- the last time was under Sybase. The Sybase suggestion was to either add dummy rows [YUCK!] to set the extreme bounds or to lie to the optimizer by fudging the statistics after each generation. Perhaps we could do better by adding columns for high and low bounds to pg_statistic. These would not be set by ANALYZE, but user-modifiable to cover exactly this problem? NULL would mean current behavior? Well, the problem Josh has got is exactly that a constant high bound doesn't work. What I'm wondering about is why he finds that re-running ANALYZE isn't an acceptable solution. It's supposed to be a reasonably cheap thing to do. I think the cleanest solution to this would be to make ANALYZE cheaper, perhaps by finding some way for it to work incrementally. 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] krb_server_keyfile setting doesn't work on Windows
2009/12/30 Hiroshi Inoue in...@tpf.co.jp: Hi, As far as I tested, the krb_server_keyfile setting in postgres.conf doesn't work on Windows. Because gssapi32.dll(krb5_32.dll) seems to call getenv(KRB5_KTNAME) in msvcr71, postgres.exe should call putenv(KRB5_KTNAME=...) in msvcr71. The attached patch fixes the problem in my test case. Isn't the main backend linked with msvcr71.dll anyway? Then the regular putenv should put it in th eenv of msvcr71.dll, and the stuff that's wrapped through src/port/win32env.c will put it in the regular msvcr file. I wonder if you're possibly being hit with the bug I patched the other day, but didn't backpatch. (http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=f8bcd7220b1166f7c037ceaf0a53958cbc6a7630). Can you see if that fix solves your problem as well? (Either directly or by testing HEAD) If not, the fix should still go in win32env.c, not directly in auth.c -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Stats for inheritance trees
On Wed, Dec 30, 2009 at 10:24 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Tom Lane escribió: Robert Haas robertmh...@gmail.com writes: If you'd be willing to look over the latest version of my per-tablespace random_page_cost/seq_page_cost patch, which I posted to -hackers some time in the last few days, I can get that committed and then start working on this, if you'd like. I think Alvaro would actually be the right person to review that, since the reloptions code is almost entirely his work. I can't promise anything right now though, as my wife could get with labour very soon ... In terms of reasons for not being able to guarantee anything, I'd have to say that's one of the best I've heard. In all honesty, I'm not very worried about the reloptions stuff proper. I have copied the existing coding pattern so closely that it's a little hard to imagine that I've broken anything too badly. My main concerns are: 1. Am I leaking memory anywhere? and 2. Can anything bad happen as a result of invalidation events and/or concurrent updates to pg_tablespace? If anyone feels qualified to check my work on those two points, that would be great. In reality, even if I've done something relatively stupid, it isn't likely to have much practical impact since pg_tablespace updates figure to be infrequent and many people won't use this feature at all. But I'd still rather not do something stupid. Thanks, ...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] test/example does not support win32.
Hi Andrew-san. This saves a windows users. I appreciate your suggestion. Thanks! P.S) I often use by the test by nmake at the time of independent creation of libpq. Regards, Hiroshi Saito - Original Message - From: Andrew Dunstan and...@dunslane.net Hiroshi Saito wrote: Hi Andrew-san. Although this is a standard in windows. *** testlibpq2.c.orig Wed Dec 30 13:19:03 2009 --- testlibpq2.cThu Dec 31 00:52:52 2009 *** *** 24,34 --- 24,39 * * INSERT INTO TBL1 VALUES (10); */ + + #ifdef WIN32 + #include windows.h + #endif #include stdio.h #include stdlib.h #include string.h #include errno.h #include sys/time.h + #include sys/types.h #include libpq-fe.h static void Does this become the standard which you consider? or #IFDEF Isn't it allowed? I certainly think we can use ifdefs. This addition seems OK to me at first glance. Does it solve the problem you encountered? cheers andrew examples_win32_patch2 Description: Binary data -- 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] test/example does not support win32.
2009/12/30 Hiroshi Saito z-sa...@guitar.ocn.ne.jp: Hi Andrew-san. This saves a windows users. I appreciate your suggestion. Thanks! This one looks much better. +1 for this version :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Thoughts on statistics for continuously advancing columns
On Wed, 30 Dec 2009 11:16:45 -0500, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: I don't have a better idea at the moment :-( It's been a while since I've been bitten by this issue -- the last time was under Sybase. The Sybase suggestion was to either add dummy rows [YUCK!] to set the extreme bounds or to lie to the optimizer by fudging the statistics after each generation. Perhaps we could do better by adding columns for high and low bounds to pg_statistic. These would not be set by ANALYZE, but user-modifiable to cover exactly this problem? NULL would mean current behavior? Well, the problem Josh has got is exactly that a constant high bound doesn't work. What I'm wondering about is why he finds that re-running ANALYZE isn't an acceptable solution. It's supposed to be a reasonably cheap thing to do. What makes ANALYZE cheap is that two things: 1. It uses read only bandwidth (for the most part), which is the most bandwidth we have 2. It doesn't take a lock that bothers anything On the other hand ANALYZE also: 1. Uses lots of memory 2. Lots of processor 3. Can take a long time We normally don't notice because most sets won't incur a penalty. We got a customer who has a single table that is over 1TB in size... We notice. Granted that is the extreme but it would only take a quarter of that size (which is common) to start seeing issues. I think the cleanest solution to this would be to make ANALYZE cheaper, perhaps by finding some way for it to work incrementally. That could be interesting. What about a running statistics set that has some kind of threshold? What I mean is, we run our normal analyze but we can mark a table HOT (yeah bad term). If we mark the table HOT statistics are generated on the fly for the planner and updated every X interval. Perhaps then written out at a checkpoint? This is just off the top of my head. JD regards, tom lane -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Thoughts on statistics for continuously advancing columns
Tom Lane t...@sss.pgh.pa.us wrote: Well, the problem Josh has got is exactly that a constant high bound doesn't work. I thought the problem was that the high bound in the statistics fell too far below the actual high end in the data. This tends (in my experience) to be much more painful than an artificially extended high end in the statistics. (YMMV, of course.) What I'm wondering about is why he finds that re-running ANALYZE isn't an acceptable solution. It's supposed to be a reasonably cheap thing to do. Good point. We haven't hit this problem in PostgreSQL precisely because we can run ANALYZE often enough to prevent the skew from becoming pathological. I think the cleanest solution to this would be to make ANALYZE cheaper, perhaps by finding some way for it to work incrementally. Yeah, though as you say above, it'd be good to know why frequent ANALYZE is a problem as it stands. -Kevin -- 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] test/example does not support win32.
Hiroshi Saito z-sa...@guitar.ocn.ne.jp writes: [ examples_win32_patch2 ] Is the addition of -DFRONTEND actually needed, and if so why? We shouldn't be depending on that in any user-exposed code, I would think. Otherwise I don't have any objection to this version. 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] test/example does not support win32.
Hi Tom-san. Ahh.. It was correction of the test of often... again, the pursued relation was seen, I think that it is good now. Thanks!! Regards, Hiroshi Saito - Original Message - From: Tom Lane t...@sss.pgh.pa.us Hiroshi Saito z-sa...@guitar.ocn.ne.jp writes: [ examples_win32_patch2 ] Is the addition of -DFRONTEND actually needed, and if so why? We shouldn't be depending on that in any user-exposed code, I would think. Otherwise I don't have any objection to this version. 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 examples_win32_patch3 Description: Binary data -- 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] Thoughts on statistics for continuously advancing columns
Joshua D. Drake wrote: We normally don't notice because most sets won't incur a penalty. We got a customer who has a single table that is over 1TB in size... We notice. Granted that is the extreme but it would only take a quarter of that size (which is common) to start seeing issues. Right, and the only thing that makes this case less painful is that you don't really need the stats to be updated quite as often in situations with that much data. If, say, your stats say there's 2B rows in the table but there's actually 2.5B, that's a big error, but unlikely to change the types of plans you get. Once there's millions of distinct values it's takes a big change for plans to shift, etc. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] exec_execute_message crash
Tatsuo Ishii is...@postgresql.org writes: parse causes transaction to abort, which causes call to AbortCurrentTransaction-AbortTransaction-AtAbort_portals-ReleaseCachedPlan. It calls ReleaseCachePlan(portal-cplan). ReleaseCachePlan calls MemoryContextDelete(plan-context) which destroys both portal-cplan and portal-stmts. That was the reason why I had segfault by accessing portal-stmts. To fix this I think exec_execute_message should throw an error if portal-cleanup is NULL, since portal-cleanup is NULLed by AtAbort_Portals at transaction abort (or portal is dropped). This is just a kluge, and a rather bad one I think. The real problem here is that AtAbort_Portals destroys the portal contents and doesn't do anything to record the fact. It should probably be putting the portal into PORTAL_FAILED state, and what exec_execute_message ought to be doing is checking for that. It might be a good idea to explicitly zero out the now-dangling pointers in the Portal struct, too. It'd be nice to have a test case for this, hint hint ... 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] KNNGiST for knn-search (WIP)
On Wed, Dec 30, 2009 at 9:20 AM, Oleg Bartunov o...@sai.msu.su wrote: From metodological point of view I don't quite understand how to measure the value of development, I mean what'is a big patch, invasive patch. I want to speak specifically to this question because I think it's a good one. Of course, I also want to make clear that I have nothing against you or your patch and that it sounds like a really nice feature. From my point of view, what makes a patch invasive is the likelihood that it might break something other than itself. For example, your patch touches the core planner code and the core GIST code, so it seems possible that adding support for this feature might break something else in one of those areas. All things being equal, we would prefer to take that risk at the beginning of a development cycle rather than the end. If your patch was the same size, but consisted mostly of new code with very few changes to what is there now, it might still be difficult to properly review and verify - but any bugs we missed would likely affect only the NEW functionality, not any EXISTING functionality. Please understand that the previous paragraph is intended to be a general statement about software development in general more than a specific commentary on your particular patch. Whether applying your patch in particular will break anything is, of course, something that's difficult to know until we do it and see what happens, and at this point I haven't even reviewed it. It's also possible that I'm doing a poor job estimating the risk of breakage, and I certainly welcome other opinions from other people in a position to make a technical judgement on that point. I might also have a different opinion myself after I review the patch in more detail, so please do post an updated version. Should we prefer cosmetic pathces, spelling fixes, etc ? Of course, they are easy for refering, but people are waiting from us not just fixes, but new features. For example, KNN-GiST is a big improvement for PostGIS community, which is a big part of postgres users. Actually, it's PostGIS community, which supported our work. Now, what we should say them ? The patch was too big and invasive, so, sorry, wait one year more ? I think it's not good. Well, I understand your point, but there is obviously some deadline for patches to be submitted for any particular release. Clearly, after the last CommitFest is over, that deadline is past. However, we have previously discussed having a policy that no new large patches will be accepted for the last CommitFest that were not also submitted for the second-to-last CommitFest. Hopefully it's obvious that I have no desire to keep cool new features away from the PostGIS community, the PostgreSQL community, or anyone else, but we have to weigh that against the desire to have a stable and bug-free release, and applying big patches at the last minute makes that less likely. As an example, the change to run the background writer during recovery and the changes in semi/anti join planning for 8.4 have both resulted in multiple bug reports. The former was half the footprint of your patch and applied at the very end of the release cycle; the latter was slightly larger and applied in August 2008, so considerably earlier in the cycle than this one could possibly be - and there were still things we did not catch before release. ...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] Thoughts on statistics for continuously advancing columns
Greg Smith g...@2ndquadrant.com writes: Right, and the only thing that makes this case less painful is that you don't really need the stats to be updated quite as often in situations with that much data. If, say, your stats say there's 2B rows in the table but there's actually 2.5B, that's a big error, but unlikely to change the types of plans you get. Once there's millions of distinct values it's takes a big change for plans to shift, etc. Normally, yeah. I think Josh's problem is that he's got performance-critical queries that are touching the moving edge of the data set, and so the part of the stats that are relevant to them is changing fast, even though in an overall sense the table contents might not be changing much. 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] Thoughts on statistics for continuously advancing columns
Greg Smith g...@2ndquadrant.com wrote: If, say, your stats say there's 2B rows in the table but there's actually 2.5B, that's a big error, but unlikely to change the types of plans you get. Once there's millions of distinct values it's takes a big change for plans to shift, etc. Well, the exception to that is if the stats say that your highest value is x, and there are actually 500 million rows with values greater than x, you can get some very bad plans for queries requiring a range of values above x. -Kevin -- 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] KNNGiST for knn-search (WIP)
Teodor Sigaev escribió: Actually, it's easy to split patch to several ones: - contrib/pg_trgm - contrib/btree_gist - knngist itself - planner changes +1 on the split patches. I wonder about the opr_sanity test change ... why is it necessary? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Thoughts on statistics for continuously advancing columns
Tom Lane escribió: Greg Smith g...@2ndquadrant.com writes: Right, and the only thing that makes this case less painful is that you don't really need the stats to be updated quite as often in situations with that much data. If, say, your stats say there's 2B rows in the table but there's actually 2.5B, that's a big error, but unlikely to change the types of plans you get. Once there's millions of distinct values it's takes a big change for plans to shift, etc. Normally, yeah. I think Josh's problem is that he's got performance-critical queries that are touching the moving edge of the data set, and so the part of the stats that are relevant to them is changing fast, even though in an overall sense the table contents might not be changing much. Maybe only tangentially related: if this was a setup partitioned by a timestamp, it would be very useful to be able to analyze only the current partition and have updated stats for the parent relation as well. However AFAICT with your proposed changes in this area this would not work, right? You'd need an analyze on the parent relation, which is painful. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] KNNGiST for knn-search (WIP)
2009/12/30 Teodor Sigaev teo...@sigaev.ru: changes should be made. It does also need to be updated to CVS HEAD, as it no longer applies cleanly. The reason was a point_ops patch, some OIDs become duplicated. Both attached patches are synced with current CVS. Thanks! I will take a look. I tend to feel that we should probably target this for 8.6 rather than 8.5. We are down to the last CommitFest, and while we don't have a nailed-down criterion for what is too big for the last CommitFest of a given release cycle, this is definitely a big, invasive patch. This Is we really have rule to accept only small patches at last CommitFest? May be, FixFest name is better for it? :) See here and following for some of the previous discussion - which was not unanimous on all points: http://archives.postgresql.org/pgsql-hackers/2009-09/msg00139.php I think the intention is not to accept only bug fixes, but to limit large features to those that have already been through a CommitFest or two. Actually, it's easy to split patch to several ones: - contrib/pg_trgm - contrib/btree_gist - knngist itself - planner changes And knngist depends on rbtree and point_ops patch, in summary 6 dependent patches. Is it more comfortable? I'm not sure. One of the problems with separating out contrib module changes is that it tends to obscure the point of the changes to the core code. On the other hand if some of the core code changes can be split out into an infrastructure patch that is of some independent usefulness, that can certainly be worthwhile. It's not obvious to me without looking at this more than I have whether there is a possble split that makes sense here; I will read your updated patch. ...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] Backup history file should be replicated in Streaming Replication?
On Wed, 2009-12-30 at 15:31 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: The code has always been capable of starting without this, which was considered a feature to be able start from a hot copy. Why is that desirable? The system is in an inconsistent state. To force it, you can always use pg_resetxlog. It's not desirable, for me, but its been there since 8.0 and I have no info on whether its used. If you have a workaround that allows us to continue to support it, I suggest you document it and then plug the gap as originally suggested by you. -- Simon Riggs www.2ndQuadrant.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] PATCH: Add hstore_to_json()
On Dec 29, 2009, at 6:14 PM, Robert Haas wrote: I've been mulling this over and I think this is a pretty good idea. If we could get it done in time for 8.5, we could actually change the output type of EXPLAIN (FORMAT JSON) to the new type. If not, I'm inclined to say that we should postpone adding any more functions that generate json output until such time as we have a real type for it. I wouldn't feel too bad about changing the output type of EXPLAIN (FORMAT JSON) from text to json in 8.6, because it's relatively difficult to be depending on that for anything very important. It's much easier to be depending on something like this, and changing it later could easily break working applications. +1 Anyone have an interest in taking a crack at this? There are a bunch of C libraries listed on http://www.json.org/. Perhaps one has a suitable license and clean enough implementation to be used? Best, David -- 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: Add hstore_to_json()
On Wed, Dec 30, 2009 at 12:38 PM, David E. Wheeler da...@kineticode.com wrote: On Dec 29, 2009, at 6:14 PM, Robert Haas wrote: I've been mulling this over and I think this is a pretty good idea. If we could get it done in time for 8.5, we could actually change the output type of EXPLAIN (FORMAT JSON) to the new type. If not, I'm inclined to say that we should postpone adding any more functions that generate json output until such time as we have a real type for it. I wouldn't feel too bad about changing the output type of EXPLAIN (FORMAT JSON) from text to json in 8.6, because it's relatively difficult to be depending on that for anything very important. It's much easier to be depending on something like this, and changing it later could easily break working applications. +1 Anyone have an interest in taking a crack at this? There are a bunch of C libraries listed on http://www.json.org/. Perhaps one has a suitable license and clean enough implementation to be used? It looks like they are all very permissive, though I wonder what the legal effect of a license clause that the software be used for Good and not Evil might be. I guess the question is whether we would slurp one of these into our code base, or whether we would add an analog of --with-libxml and provide only a stub implementation when the library is not present. Any opinions? Does anyone know whether any of these implementations are commonly packaged already? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Thread safety and libxml2
We got an interesting report on the testers list today: http://archives.postgresql.org/pgsql-testers/2009-12/msg0.php Basically, configure failed on their OpenBSD system because thread safety is on but the libxml2 wasn't compiled with threaded support: http://xmlsoft.org/threads.html Disabling either feature (no --with-libxml or --disable-thread-safety) gives a working build. I wonder if it's worthwhile to document this coupling between thread safety and libxml2 in either http://developer.postgresql.org/pgdocs/postgres/install-procedure.html or even the release notes. It seems quite likely to bite someone else again the future. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] PATCH: Add hstore_to_json()
On Dec 30, 2009, at 9:53 AM, Robert Haas wrote: It looks like they are all very permissive, though I wonder what the legal effect of a license clause that the software be used for Good and not Evil might be. Yeah, that might be too restrictive, given that PostgreSQL is used by government agencies and porn sites. Not that a given gov or porn site is inherently evil, mind, but some are. ;-P I guess the question is whether we would slurp one of these into our code base, or whether we would add an analog of --with-libxml and provide only a stub implementation when the library is not present. Any opinions? Does anyone know whether any of these implementations are commonly packaged already? I doubt that they have similar interfaces, so we'd probably have to rely on one. I'd probably favor embedding, personally, it's less work for admins. Best, David -- 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: Add hstore_to_json()
David E. Wheeler wrote: I guess the question is whether we would slurp one of these into our code base, or whether we would add an analog of --with-libxml and provide only a stub implementation when the library is not present. Any opinions? Does anyone know whether any of these implementations are commonly packaged already? I doubt that they have similar interfaces, so we'd probably have to rely on one. I'd probably favor embedding, personally, it's less work for admins. I think we are getting the cart way before the horse. I'd like to see at least the outline of an API before we go any further. JSON is, shall we say, lightly specified, and doesn't appear to have any equivalent to XPath and friends, for example. How will we extract values from a JSON object? How will we be able to set values inside them? In ECMAScript it's not a problem, because the objects returned are just like any other objects, but that's not the case here. These are the sorts of questions we need to answer before we look at any implementation details, I think. cheers 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] Thoughts on statistics for continuously advancing columns
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribió: Normally, yeah. I think Josh's problem is that he's got performance-critical queries that are touching the moving edge of the data set, and so the part of the stats that are relevant to them is changing fast, even though in an overall sense the table contents might not be changing much. Maybe only tangentially related: if this was a setup partitioned by a timestamp, it would be very useful to be able to analyze only the current partition and have updated stats for the parent relation as well. However AFAICT with your proposed changes in this area this would not work, right? You'd need an analyze on the parent relation, which is painful. Yeah, I was just thinking about that myself. The parent-level ANALYZE would approximately double the work involved, assuming that your total data set is large enough to max out the number of blocks sampled. So it'd be painful but not catastrophic. Maybe the way to think about the incremental update problem is to find a way to let ANALYZE calculate parent-relation stats from the stats of the individual partitions. Not that I know how to do that either, but at least it's a clearly stated task. 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] Thoughts on statistics for continuously advancing columns
On Wed, Dec 30, 2009 at 4:31 PM, Joshua D. Drake j...@commandprompt.com wrote: On the other hand ANALYZE also: 1. Uses lots of memory 2. Lots of processor 3. Can take a long time We normally don't notice because most sets won't incur a penalty. We got a customer who has a single table that is over 1TB in size... We notice. Granted that is the extreme but it would only take a quarter of that size (which is common) to start seeing issues. I'm a bit puzzled by people's repeated suggestion here that large tables take a long time to analyze. The sample analyze takes to generate statistics is not heavily influenced by the size of the table. Your 1TB table should take basically the same amount of time as a 1GB table or a 1MB table (if it wasn't already in cache). Unless the reason why it's 1TB is that the columns are extremely wide rather than that it has a lot of rows? Or unless you've raised the statistics target in (a misguided*) belief that larger tables require larger statistics targets to achieve the same level of accuracy. Or unless when you say ANALYZE you're really running VACUUM ANALYZE. [*] except for ndistinct estimates :( -- 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] Cancelling idle in transaction state
On Wed, 2009-12-30 at 14:15 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: AFAIK, NOTICE was suggested because it can be sent at any time, whereas ERRORs are only associated with statements. http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-ASYNC It is possible for NoticeResponse messages to be generated due to outside activity; for example, if the database administrator commands a fast database shutdown, the backend will send a NoticeResponse indicating this fact before closing the connection. Accordingly, frontends should always be prepared to accept and display NoticeResponse messages, even when the connection is nominally idle. Could we send an asynchronous notification immediately when the transaction is cancelled, but also change the error message you get in the subsequent commands. Clients that ignore the async notification would still see a proper error message at the ERROR. Something like: ERROR: current transaction was aborted because of conflict with recovery, commands ignored until end of transaction block instead of the usual current transaction is aborted, commands ignored until end of transaction block message. This is possible, yes. I have an added complication, hinted at by Joachim, currently investigating. -- Simon Riggs www.2ndQuadrant.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] Hot Standy introduced problem with query cancel behavior
On Wednesday 30 December 2009 01:13:01 Simon Riggs wrote: On Tue, 2009-12-29 at 11:13 -0500, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Tuesday 29 December 2009 16:22:54 Tom Lane wrote: This seems like a fairly bad idea. One of the intended use-cases is to be able to manually kill -INT a misbehaving backend. Assuming that there will be valid info about the signal in shared memory will break that. Well. That already is the case now. MyProc-recoveryConflictMode is checked to recognize what kind of conflict is being resolved... In that case, HS has already broken it, and we need to fix it not make it worse. My humble opinion is that SIGINT should not be overloaded with multiple meanings. We already have a multiplexed signal mechanism, which is what should be used for any additional signal reasons HS may need to introduce. It's a revelation to me, but yes, I see it now and agree. I'm looking at Fujii-san's multiplexing patch from Jul 31 to rewrite this code using that mechanism. It sounds like it's a neat fit and it should get around the bug report from Kris also if it all works. Hm. I just read a bit of that multiplexing facility (out of a different reason) and I have some doubt about it being used unmodified for canceling backends: procsignal.c: /* * Note: Since there's no locking, it's possible that the target * process detaches from shared memory and exits right after this * test, before we set the flag and send signal. And the signal slot * might even be recycled by a new process, so it's remotely possible * that we set a flag for a wrong process. That's OK, all the signals * are such that no harm is done if they're mistakenly fired. */ procsignal.h: ... * Also, because of race conditions, it's important that all the signals be * defined so that no harm is done if a process mistakenly receives one. */ When cancelling a backend that behaviour could be a bit annoying ;-) I guess locking procarray during sending the signal should be enough? Andres -- 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] Thoughts on statistics for continuously advancing columns
On Wed, 30 Dec 2009 18:42:38 +, Greg Stark gsst...@mit.edu wrote:
I'm a bit puzzled by people's repeated suggestion here that large
tables take a long time to analyze. The sample analyze takes to
generate statistics is not heavily influenced by the size of the
table. Your 1TB table should take basically the same amount of time as
a 1GB table or a 1MB table (if it wasn't already in cache).
No.
postgres=# analyze verbose test_one_million;
INFO: analyzing public.test_one_million
INFO: test_one_million: scanned 3000 of 4425 pages, containing 677950
live rows and 0 dead rows; 3000 rows in sample, 76 estimated total rows
ANALYZE
Time: 168.009 ms
postgres=# analyze verbose test_one_million;
INFO: analyzing public.test_one_million
INFO: test_one_million: scanned 3000 of 4425 pages, containing 677950
live rows and 0 dead rows; 3000 rows in sample, 76 estimated total rows
ANALYZE
Time: 104.006 ms
postgres=# analyze verbose test_ten_million;
INFO: analyzing public.test_ten_million
INFO: test_ten_million: scanned 3000 of 44248 pages, containing 678000
live rows and 0 dead rows; 3000 rows in sample, 1048 estimated total
rows
ANALYZE
Time: 20145.148 ms
postgres=# analyze verbose test_ten_million;
INFO: analyzing public.test_ten_million
INFO: test_ten_million: scanned 3000 of 44248 pages, containing 678000
live rows and 0 dead rows; 3000 rows in sample, 1048 estimated total
rows
ANALYZE
Time: 18481.053 ms
postgres=# analyze verbose test_ten_million;
INFO: analyzing public.test_ten_million
INFO: test_ten_million: scanned 3000 of 44248 pages, containing 678000
live rows and 0 dead rows; 3000 rows in sample, 1048 estimated total
rows
ANALYZE
Time: 17653.006 ms
The test_one_million when in cache and out is very quick. I don't think
the ten million is actually able to get into cache (small box) but either
way
if you look at the on disk number for the one million 168ms versus the on
disk number for the ten million, they are vastly different.
postgres=# select
pg_size_pretty(pg_total_relation_size('test_one_million'));
pg_size_pretty
35 MB
(1 row)
Time: 108.006 ms
postgres=# select
pg_size_pretty(pg_total_relation_size('test_ten_million'));
pg_size_pretty
346 MB
(1 row)
Unless the reason why it's 1TB is that the columns are extremely wide
rather than that it has a lot of rows?
I should have qualified, yes they are very wide.
JD
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
--
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] Thoughts on statistics for continuously advancing columns
On tis, 2009-12-29 at 22:08 -0500, Tom Lane wrote: This seems like a fundamentally broken approach, first because time between analyzes is not even approximately a constant, and second because it assumes that we have a distance metric for all datatypes. Maybe you could compute a correlation between the column values and the transaction numbers to recognize a continuously advancing column. It wouldn't tell you much about how fast they are advancing, but at least the typical use cases of serial and current timestamp columns should clearly stick out. And then instead of assuming that a value beyond the histogram bound doesn't exist, you assume for example the average frequency, which should be pretty good for the serial and timestamp cases. (Next step: Fourier analysis ;-) ) -- 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: Add hstore_to_json()
On Wed, Dec 30, 2009 at 1:23 PM, Andrew Dunstan and...@dunslane.net wrote: I think we are getting the cart way before the horse. I'd like to see at least the outline of an API before we go any further. JSON is, shall we say, lightly specified, and doesn't appear to have any equivalent to XPath and friends, for example. How will we extract values from a JSON object? How will we be able to set values inside them? In ECMAScript it's not a problem, because the objects returned are just like any other objects, but that's not the case here. These are the sorts of questions we need to answer before we look at any implementation details, I think. I think the idea that Peter was proposing was to start by creating a type that doesn't necessarily have a lot of operators or functions associated with it, with the thought of adding those later. It would still need to validate the input, of course. Anyhow, that might be a bad way to approach the problem, but I think that's how we got here. ...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] Hot Standy introduced problem with query cancel behavior
On Wed, Dec 30, 2009 at 2:06 PM, Andres Freund and...@anarazel.de wrote: On Wednesday 30 December 2009 01:13:01 Simon Riggs wrote: On Tue, 2009-12-29 at 11:13 -0500, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Tuesday 29 December 2009 16:22:54 Tom Lane wrote: This seems like a fairly bad idea. One of the intended use-cases is to be able to manually kill -INT a misbehaving backend. Assuming that there will be valid info about the signal in shared memory will break that. Well. That already is the case now. MyProc-recoveryConflictMode is checked to recognize what kind of conflict is being resolved... In that case, HS has already broken it, and we need to fix it not make it worse. My humble opinion is that SIGINT should not be overloaded with multiple meanings. We already have a multiplexed signal mechanism, which is what should be used for any additional signal reasons HS may need to introduce. It's a revelation to me, but yes, I see it now and agree. I'm looking at Fujii-san's multiplexing patch from Jul 31 to rewrite this code using that mechanism. It sounds like it's a neat fit and it should get around the bug report from Kris also if it all works. Hm. I just read a bit of that multiplexing facility (out of a different reason) and I have some doubt about it being used unmodified for canceling backends: procsignal.c: /* * Note: Since there's no locking, it's possible that the target * process detaches from shared memory and exits right after this * test, before we set the flag and send signal. And the signal slot * might even be recycled by a new process, so it's remotely possible * that we set a flag for a wrong process. That's OK, all the signals * are such that no harm is done if they're mistakenly fired. */ procsignal.h: ... * Also, because of race conditions, it's important that all the signals be * defined so that no harm is done if a process mistakenly receives one. */ When cancelling a backend that behaviour could be a bit annoying ;-) I guess locking procarray during sending the signal should be enough? I think the idea is that you define the behavior of the signal to be look at this other piece of state to see whether you should cancel yourself rather than just cancel yourself. Then if a signal is delivered by mistake, it's no big deal - you just look at the other piece of state and decide that you don't need to do anything. ...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] PATCH: Add hstore_to_json()
Robert Haas wrote: On Wed, Dec 30, 2009 at 1:23 PM, Andrew Dunstan and...@dunslane.net wrote: I think we are getting the cart way before the horse. I'd like to see at least the outline of an API before we go any further. JSON is, shall we say, lightly specified, and doesn't appear to have any equivalent to XPath and friends, for example. How will we extract values from a JSON object? How will we be able to set values inside them? In ECMAScript it's not a problem, because the objects returned are just like any other objects, but that's not the case here. These are the sorts of questions we need to answer before we look at any implementation details, I think. I think the idea that Peter was proposing was to start by creating a type that doesn't necessarily have a lot of operators or functions associated with it, with the thought of adding those later. It would still need to validate the input, of course. Anyhow, that might be a bad way to approach the problem, but I think that's how we got here. That does not at all seem like a good way to go. Until we know what operations we want to support we have no idea which library to use. We can not assume that they will all support what we want to do. cheers 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] PATCH: Add hstore_to_json()
On Wed, Dec 30, 2009 at 2:26 PM, Andrew Dunstan and...@dunslane.net wrote: Robert Haas wrote: On Wed, Dec 30, 2009 at 1:23 PM, Andrew Dunstan and...@dunslane.net wrote: I think we are getting the cart way before the horse. I'd like to see at least the outline of an API before we go any further. JSON is, shall we say, lightly specified, and doesn't appear to have any equivalent to XPath and friends, for example. How will we extract values from a JSON object? How will we be able to set values inside them? In ECMAScript it's not a problem, because the objects returned are just like any other objects, but that's not the case here. These are the sorts of questions we need to answer before we look at any implementation details, I think. I think the idea that Peter was proposing was to start by creating a type that doesn't necessarily have a lot of operators or functions associated with it, with the thought of adding those later. It would still need to validate the input, of course. Anyhow, that might be a bad way to approach the problem, but I think that's how we got here. That does not at all seem like a good way to go. Until we know what operations we want to support we have no idea which library to use. We can not assume that they will all support what we want to do. Well that is a bit of a problem, yes... Doesn't seem insurmountable, though, just one more thing to think about as we're having this conversation. Someone else will need to weigh in on this point though, as I don't use JSON in a way that would make anything beyond validation particularly relevant. ...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] update_process_title=off and logger, wal, ... processes
* Rod Taylor (rod.tay...@gmail.com) wrote: I (stupidly?) installed PostgreSQL into a hostile environment which didn't like this and decided to kill the processes as a result. Unfortunately, I cannot change the environment. That's not hostile, that's broken. Stephen signature.asc Description: Digital signature
Re: [HACKERS] KNNGiST for knn-search (WIP)
On Wed, Dec 30, 2009 at 12:16 PM, Robert Haas robertmh...@gmail.com wrote: 2009/12/30 Teodor Sigaev teo...@sigaev.ru: changes should be made. It does also need to be updated to CVS HEAD, as it no longer applies cleanly. The reason was a point_ops patch, some OIDs become duplicated. Both attached patches are synced with current CVS. Thanks! I will take a look. OK, I'm confused. First, there are two versions of the patch here, so I'm not sure which one I'm supposed to be looking at. Second, when I attempt to apply either one, I get: $ patch -p0 ~/Download/builtin_knngist-0.5 patching file src/backend/access/gist/gistget.c patching file src/backend/access/gist/gistproc.c Reversed (or previously applied) patch detected! Assume -R? [n] ...regardless of how I answer that question, it then goes on to apply most of the rest of the patch successfully. Help? ...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] Thoughts on statistics for continuously advancing columns
Peter Eisentraut pete...@gmx.net writes: On tis, 2009-12-29 at 22:08 -0500, Tom Lane wrote: This seems like a fundamentally broken approach, first because time between analyzes is not even approximately a constant, and second because it assumes that we have a distance metric for all datatypes. Maybe you could compute a correlation between the column values and the transaction numbers to recognize a continuously advancing column. It wouldn't tell you much about how fast they are advancing, but at least the typical use cases of serial and current timestamp columns should clearly stick out. And then instead of assuming that a value beyond the histogram bound doesn't exist, you assume for example the average frequency, which should be pretty good for the serial and timestamp cases. (Next step: Fourier analysis ;-) ) Actually, the histogram hasn't got much of anything to do with estimates of the number of occurrences of a single value. Josh hasn't shown us his specific problem query, but I would bet that it's roughly like WHERE update_time now() - interval 'something', that is, the estimate that's problematic is an inequality not an equality. When the range being asked for is outside the histogram bounds, it really is rather difficult to come up with a reasonable estimate --- you'd need a specific idea of how far outside the upper bound it is, how fast the upper bound has been advancing, and how long it's been since the last analyze. (I find the last bit particularly nasty, because it will mean that plans change even when nothing is changing in the database.) [ thinks for awhile ... ] Actually, in the problematic cases, it's interesting to consider the following strategy: when scalarineqsel notices that it's being asked for a range estimate that's outside the current histogram bounds, first try to obtain the actual current max() or min() of the column value --- this is something we can get fairly cheaply if there's a btree index on the column. If we can get it, plug it into the histogram, replacing the high or low bin boundary. Then estimate as we currently do. This would work reasonably well as long as re-analyzes happen at a time scale such that the histogram doesn't move much overall, ie, the number of insertions between analyzes isn't a lot compared to the number of rows per bin. We'd have some linear-in-the-bin-size estimation error because the modified last or first bin actually contains more rows than other bins, but it would certainly work a lot better than it does now. 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] pg_read_file() and non-ascii input file
On Mon, Nov 30, 2009 at 4:36 AM, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: If we want to keep backward compatibility, the issue can be fixed by adding pg_verifymbstr() to the function. We can also have the binary version in another name, like pg_read_binary_file(). I don't feel good about changing the return type of an existing function, so I guess +1 from me on the approach quoted above. ...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] quoting psql varible as identifier
On Tue, Dec 29, 2009 at 3:19 PM, Pavel Stehule pavel.steh...@gmail.com wrote: here is patch The error handling in quote_literal() doesn't look right to me. The documentation for PQescapeStringConn says that it stores an error message in the conn object, but your code ignores that and prints out a generic message instead. That doesn't seem right: but then it further goes on to call exit(1), which seems like a considerable overreaction to an encoding violation, which is apparently the only class of error PQescapeStringConn() is documented to throw. ...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] Thoughts on statistics for continuously advancing columns
j...@commandprompt.com (Joshua D. Drake) writes: On the other hand ANALYZE also: 1. Uses lots of memory 2. Lots of processor 3. Can take a long time We normally don't notice because most sets won't incur a penalty. We got a customer who has a single table that is over 1TB in size... We notice. Granted that is the extreme but it would only take a quarter of that size (which is common) to start seeing issues. I find it curious that ANALYZE *would* take a long time to run. After all, its sampling strategy means that, barring having SET STATISTICS to some ghastly high number, it shouldn't need to do materially more work to analyze a 1TB table than is required to analyze a 1GB table. With the out-of-the-box (which may have changed without my notice ;-)) default of 10 bars in the histogram, it should search for 30K rows, which, while not free, doesn't get enormously more expensive as tables grow. -- cbbrowne,@,gmail.com http://linuxfinances.info/info/linuxdistributions.html Rules of the Evil Overlord #179. I will not outsource core functions. http://www.eviloverlord.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] Thoughts on statistics for continuously advancing columns
well that's interesting because they claim to be doing exactly the same amount of I/O in terms of pages. In the first case it's reading 3/4 of the table so it's effectively doing a sequential scan. In the second case it's only scanning 7.5% so you would expect it to be slower but not that much slower. If as you say the rows are very wide then the other part of the equation will be TOAST table I/O though. I'm not sure what it would look like but I bet analyze isn't optimized to handle well -- not much of postgres really knows about TOAST. It'll be accessing the same number of TOAST records but out of a much bigger TOAST table. -- 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] Thoughts on statistics for continuously advancing columns
Chris Browne cbbro...@acm.org writes: I find it curious that ANALYZE *would* take a long time to run. After all, its sampling strategy means that, barring having SET STATISTICS to some ghastly high number, it shouldn't need to do materially more work to analyze a 1TB table than is required to analyze a 1GB table. Right. The example JD quotes in this thread compares a 35MB table to a 350MB one, and the difference is all about having crossed the threshold of what would fit in his available RAM. There isn't going to be much difference in the ANALYZE time for big versus very big tables. (There might, however, be a difference in the quality of the resulting stats :-() 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] krb_server_keyfile setting doesn't work on Windows
Tom Lane wrote: Hiroshi Inoue in...@tpf.co.jp writes: Because gssapi32.dll(krb5_32.dll) seems to call getenv(KRB5_KTNAME) in msvcr71, postgres.exe should call putenv(KRB5_KTNAME=...) in msvcr71. The attached patch fixes the problem in my test case. Don't we already have something like that in our src/port putenv substitute? Yes, pgwin32_putenv() calls putenv() in msvcrt which libintl or other MINGW dlls use. I'm not sure if it's appropriate to add msvcr71's putenv call to pgwin32_putenv() (src/port/win32env.c). regards, Hiroshi Inoue -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Status of plperl inter-sp calling
While waiting for feedback on my earlier plperl refactor and feature
patches I'm working on a further patch that adds, among other things,
fast inter-plperl-sp calling.
I want to outline what I've got and get some feedback on open issues.
To make a call to a stored procedure from plperl you just call the
function name prefixed by SP::. For example:
create function poly() returns text language plperl
as $$ return poly0 $$;
create function poly(text) returns text language plperl
as $$ return poly1 $$
create function poly(text, text) returns text language plperl
as $$ return poly2 $$
create function foo() returns text language plperl as $$
SP::poly();
SP::poly(1);
SP::poly(1,2);
return undef;
$$
That handles the arity of the calls and invokes the right SP, bypassing
SQL if the SP is already loaded.
That much works currently. Behind the scenes, when a stored procedure is
loaded into plperl the code ref for the perl sub is stored in a cache.
Effectively just
$cache{$name}[$nargs] = $coderef;
An SP::AUTOLOAD sub intercepts any SP::* call and effectively does
lookup_sp($name, \...@_)-(@_);
For SPs that are already loaded lookup_sp returns $cache{$name}[$nargs]
so the overhead of the call is very small.
For SPs that are not cached, lookup_sp returns a code ref of a closure
that will invoke $name with the args in @_ via
spi_exec_query(select * from $name($encoded_args));
The fallback-to-SQL behaviour neatly handles non-cached SPs (forcing
them to be loaded and thus cached), and inter-language calling (both
plperl-plperl and other PLs).
Limitations:
* It's not meant to handle type polymorphism, only the number of args.
* When invoked via SQL, because the SP isn't cached, all non-ref args
are all expressed as strings via quote_nullable(). Any array refs
are encoded as ARRAY[...] via encode_array_constructor().
I don't see either of those as significant issues: If you need more
control for a particular SP then don't use SP::* to call that SP.
Open issues:
* What should SP::foo(...) return? The plain as-if-called-by-perl
return value, or something closer to what spi_exec_query() returns?
* If the called SP::foo(...) calls return_next those rows are returned
directly to the client. That can be construed as a feature.
* Cache invalidation. How can I hook into an SP being dropped so I can
pro-actively invalidate the cache?
* Probably many other things I've not thought of.
This is all a little rough and exploratory at the moment.
I'm very keen to get any feedback you might have.
Tim.
p.s. Happy New Year! (I may be off-line for a few days.)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] A third lock method
I've been reviewing code to get a better handle on the scope of changes to support serializable transactions, in preparation for next month's meetings with our CIO. My posts should start getting progressively less hand-wavy. :-) I've come to a few conclusions: (1) The notions of having multiple serializable implementations (SSI, S2PL, OCC) which can be mapped as a configuration option is really not worth it. The cases where S2PL or OCC beat SSI are too narrow to be worth the effort, and the pluggable approach seems like it would be much more invasive and destabilizing than just picking one and doing it more directly. (2) If we're going with SSI, it appears that it would be a very good idea to define a third lock method (SIREAD_LOCKMETHOD perhaps) for the SIREAD locks. For one thing, that could keep them out of the way of normal conflict detection (they don't conflict with anything, per se) and out of the way of deadlock detection, including rearrangement of waiting transactions. For another, they have a different life-cycle -- they must stick around (along with some minimal transaction information) until all transactions with a snapshot prior to their commit have completed. That seems cleaner and easier with a separate lock method. Thoughts? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PostgreSQL RPM sets for 8.5 Alpha 3 released.
Hi, PostgreSQL RPM Building Project released RPM sets for 3rd Alpha of the upcoming 8.5 release. Please note that these packages are **not** production ready. They are for Fedora 7,8,9,11,12 and RHEL/CentOS 4,5. These packages *do* require a dump/reload, even from the second alpha packages, because of catversion updates. We need more people to discover any bugs and test new features in 8.5 development version. Here is the list of the new features in 8.5 development version: http://developer.postgresql.org/pgdocs/postgres/release-8-5.html This RPM set has new experimental feature that has been requested for a long time: Multiple version installation for RPMs. We also need volunteers to test this new RPM feature. As usual, please find detailed info about RPMs from: http://yum.pgsqlrpms.org/howtuyum.php A mini howto about 8.5 alpha release + RPMs are here: http://yum.pgsqlrpms.org/news-8.5alpha-packages-ready-for-testing.php Please report any packaging related errors to me. If you find any PostgreSQL 8.5 bugs, please post them to pgsql-b...@postgresql.org or fill out this form: http://www.postgresql.org/support/submitbug Sincerely, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[HACKERS] Allowing x IS NOT NULL as a btree search condition
Back when we put in the ability to use x IS NULL as a btree search condition, we intentionally left out x IS NOT NULL, on the grounds that it is comparable to x something which is not btree-searchable either. However, it occurs to me that we missed a bet here. The NOT NULL condition could instead be treated like x is less than NULL (in a NULLS LAST index) or x is greater than NULL (in a NULLS FIRST index), which would make it work like a searchable inequality. It's still true that except in the case of a mostly-null column, it would seldom be worth doing such an indexscan. However, I can see an application where an index search condition like this would be highly worthwhile: namely, trying to extract a column min or max. Right now, if there are a fair number of nulls at the end of the index you're interested in, you have to stupidly scan through them --- but if the btree code knew about doing this, it could descend the tree intelligently and land right on the first or last non-null. We have already seen field complaints about the performance of index-optimized MAX in cases with many nulls, eg http://archives.postgresql.org/pgsql-performance/2006-12/msg00099.php which fixing this would take care of. This would also affect the usefulness of the idea I proposed earlier today about automatically updating the histogram bin boundaries when trying to estimate inequality selectivity for comparison values near the ends of the range --- if we can't rely on the index lookup for max or min to be cheap, doing that stops looking quite so attractive. While I haven't tried to code this yet, I'm guessing that it's just a very small addition to the logic we already have. Any objections to fixing this 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] A third lock method
Kevin Grittner wrote: I've been reviewing code to get a better handle on the scope of changes to support serializable transactions, in preparation for next month's meetings with our CIO. My posts should start getting progressively less hand-wavy. :-) I've come to a few conclusions: (1) The notions of having multiple serializable implementations (SSI, S2PL, OCC) which can be mapped as a configuration option is really not worth it. The cases where S2PL or OCC beat SSI are too narrow to be worth the effort, and the pluggable approach seems like it would be much more invasive and destabilizing than just picking one and doing it more directly. Agreed. (2) If we're going with SSI, it appears that it would be a very good idea to define a third lock method (SIREAD_LOCKMETHOD perhaps) for the SIREAD locks. For one thing, that could keep them out of the way of normal conflict detection (they don't conflict with anything, per se) and out of the way of deadlock detection, including rearrangement of waiting transactions. For another, they have a different life-cycle -- they must stick around (along with some minimal transaction information) until all transactions with a snapshot prior to their commit have completed. That seems cleaner and easier with a separate lock method. I must be missing something but I thought the only problem with our existing snapshot system was that you could see a row updated after your snapshot was created, and that the solution to that was to abort the transaction that would see the new row. Can you tell me what I am missing? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Allowing x IS NOT NULL as a btree search condition
Tom Lane wrote: Back when we put in the ability to use x IS NULL as a btree search condition, we intentionally left out x IS NOT NULL, on the grounds that it is comparable to x something which is not btree-searchable either. However, it occurs to me that we missed a bet here. The NOT NULL condition could instead be treated like x is less than NULL (in a NULLS LAST index) or x is greater than NULL (in a NULLS FIRST index), which would make it work like a searchable inequality. It's still true that except in the case of a mostly-null column, it would seldom be worth doing such an indexscan. However, I can see an application where an index search condition like this would be highly worthwhile: namely, trying to extract a column min or max. Right now, if there are a fair number of nulls at the end of the index you're interested in, you have to stupidly scan through them --- but if the btree code knew about doing this, it could descend the tree intelligently and land right on the first or last non-null. We have already seen field complaints about the performance of index-optimized MAX in cases with many nulls, eg http://archives.postgresql.org/pgsql-performance/2006-12/msg00099.php which fixing this would take care of. This would also affect the usefulness of the idea I proposed earlier today about automatically updating the histogram bin boundaries when trying to estimate inequality selectivity for comparison values near the ends of the range --- if we can't rely on the index lookup for max or min to be cheap, doing that stops looking quite so attractive. While I haven't tried to code this yet, I'm guessing that it's just a very small addition to the logic we already have. Any objections to fixing this up? Sounds good to me. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Windows x64 [repost]
2009/12/4 Tsutomu Yamada tsut...@sraoss.co.jp: Thanks to suggestion. I send pathces again by another mailer for the archive. Sorry to waste resources, below is same content that I send before. I have a couple of comments about the first patch (I'll get to the others later): config.win32.h has: + #ifdef _MSC_VER + /* #undef HAVE_STDINT_H */ + #else #define HAVE_STDINT_H 1 + #endif Is that really necessary? config.h.win32 is only used on MSVC builds, no? A bit further down, it has: + /* The size of `void *', as computed by sizeof. */ + #define SIZEOF_VOID_P 4 + shouldn't that be 8 for win64 platforms? This patch also needs autoconf support for other platforms, but I've bugged Bruce about that and have some code to get that done. Just wanted these questions settled before I move on. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Hot Standy introduced problem with query cancel behavior
- Ursprüngliche Mitteilung - On Wed, Dec 30, 2009 at 2:06 PM, Andres Freund and...@anarazel.de wrote: On Wednesday 30 December 2009 01:13:01 Simon Riggs wrote: On Tue, 2009-12-29 at 11:13 -0500, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Tuesday 29 December 2009 16:22:54 Tom Lane wrote: This seems like a fairly bad idea. One of the intended use-cases is to be able to manually kill -INT a misbehaving backend. Assuming that there will be valid info about the signal in shared memory will break that. Well. That already is the case now. MyProc-recoveryConflictMode is checked to recognize what kind of conflict is being resolved... In that case, HS has already broken it, and we need to fix it not make it worse. My humble opinion is that SIGINT should not be overloaded with multiple meanings. We already have a multiplexed signal mechanism, which is what should be used for any additional signal reasons HS may need to introduce. It's a revelation to me, but yes, I see it now and agree. I'm looking at Fujii-san's multiplexing patch from Jul 31 to rewrite this code using that mechanism. It sounds like it's a neat fit and it should get around the bug report from Kris also if it all works. Hm. I just read a bit of that multiplexing facility (out of a different reason) and I have some doubt about it being used unmodified for canceling backends: procsignal.c: /* * Note: Since there's no locking, it's possible that the target * process detaches from shared memory and exits right after this * test, before we set the flag and send signal. And the signal slot * might even be recycled by a new process, so it's remotely possible * that we set a flag for a wrong process. That's OK, all the signals * are such that no harm is done if they're mistakenly fired. */ procsignal.h: ... * Also, because of race conditions, it's important that all the signals be * defined so that no harm is done if a process mistakenly receives one. */ When cancelling a backend that behaviour could be a bit annoying ;-) I guess locking procarray during sending the signal should be enough? I think the idea is that you define the behavior of the signal to be look at this other piece of state to see whether you should cancel yourself rather than just cancel yourself. Then if a signal is delivered by mistake, it's no big deal - you just look at the other piece of state and decide that you don't need to do anything. I dont see an easy way to pass enough information right now. You cant regenerate enough of it in the to be killed backend as most of the relevant information is only available in the startup process. Inventing yet another segment in shm just for this seems overcomplicated to me. Thats why I suggested locking the procarray for this - without having looked at the code that should prevent a backend slot from beimg reused. Andres -- 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] krb_server_keyfile setting doesn't work on Windows
Magnus Hagander wrote: 2009/12/30 Hiroshi Inoue in...@tpf.co.jp: Hi, As far as I tested, the krb_server_keyfile setting in postgres.conf doesn't work on Windows. Because gssapi32.dll(krb5_32.dll) seems to call getenv(KRB5_KTNAME) in msvcr71, postgres.exe should call putenv(KRB5_KTNAME=...) in msvcr71. The attached patch fixes the problem in my test case. Isn't the main backend linked with msvcr71.dll anyway? What main backend directly links is msvcr80 or msvcr90 according to the msvc build environment. Then the regular putenv should put it in th eenv of msvcr71.dll, and the stuff that's wrapped through src/port/win32env.c will put it in the regular msvcr file. I wonder if you're possibly being hit with the bug I patched the other day, but didn't backpatch. (http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=f8bcd7220b1166f7c037ceaf0a53958cbc6a7630). Can you see if that fix solves your problem as well? (Either directly or by testing HEAD) I'm testing using the current cvs. If not, the fix should still go in win32env.c, not directly in auth.c I don't object to it. Possibly we would have to add msvcr80 or msvcr90 as well in the future. regards, Hiroshi Inoue -- 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] krb_server_keyfile setting doesn't work on Windows
2009/12/31 Hiroshi Inoue in...@tpf.co.jp: Magnus Hagander wrote: 2009/12/30 Hiroshi Inoue in...@tpf.co.jp: Hi, As far as I tested, the krb_server_keyfile setting in postgres.conf doesn't work on Windows. Because gssapi32.dll(krb5_32.dll) seems to call getenv(KRB5_KTNAME) in msvcr71, postgres.exe should call putenv(KRB5_KTNAME=...) in msvcr71. The attached patch fixes the problem in my test case. Isn't the main backend linked with msvcr71.dll anyway? What main backend directly links is msvcr80 or msvcr90 according to the msvc build environment. Arrgh. My bad, I thought msvcr71 was vs2005. Now that you put it like this, I know it's vs2003. Then the regular putenv should put it in th eenv of msvcr71.dll, and the stuff that's wrapped through src/port/win32env.c will put it in the regular msvcr file. I wonder if you're possibly being hit with the bug I patched the other day, but didn't backpatch. (http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=f8bcd7220b1166f7c037ceaf0a53958cbc6a7630). Can you see if that fix solves your problem as well? (Either directly or by testing HEAD) I'm testing using the current cvs. If not, the fix should still go in win32env.c, not directly in auth.c I don't object to it. Possibly we would have to add msvcr80 or msvcr90 as well in the future. To be safe, yes, we should have that. Do you want to work on such a complete solution, or should I look at it? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Hot Standy introduced problem with query cancel behavior
On Wed, Dec 30, 2009 at 6:43 PM, Andres Freund and...@anarazel.de wrote: - Ursprüngliche Mitteilung - On Wed, Dec 30, 2009 at 2:06 PM, Andres Freund and...@anarazel.de wrote: On Wednesday 30 December 2009 01:13:01 Simon Riggs wrote: On Tue, 2009-12-29 at 11:13 -0500, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Tuesday 29 December 2009 16:22:54 Tom Lane wrote: This seems like a fairly bad idea. One of the intended use-cases is to be able to manually kill -INT a misbehaving backend. Assuming that there will be valid info about the signal in shared memory will break that. Well. That already is the case now. MyProc-recoveryConflictMode is checked to recognize what kind of conflict is being resolved... In that case, HS has already broken it, and we need to fix it not make it worse. My humble opinion is that SIGINT should not be overloaded with multiple meanings. We already have a multiplexed signal mechanism, which is what should be used for any additional signal reasons HS may need to introduce. It's a revelation to me, but yes, I see it now and agree. I'm looking at Fujii-san's multiplexing patch from Jul 31 to rewrite this code using that mechanism. It sounds like it's a neat fit and it should get around the bug report from Kris also if it all works. Hm. I just read a bit of that multiplexing facility (out of a different reason) and I have some doubt about it being used unmodified for canceling backends: procsignal.c: /* * Note: Since there's no locking, it's possible that the target * process detaches from shared memory and exits right after this * test, before we set the flag and send signal. And the signal slot * might even be recycled by a new process, so it's remotely possible * that we set a flag for a wrong process. That's OK, all the signals * are such that no harm is done if they're mistakenly fired. */ procsignal.h: ... * Also, because of race conditions, it's important that all the signals be * defined so that no harm is done if a process mistakenly receives one. */ When cancelling a backend that behaviour could be a bit annoying ;-) I guess locking procarray during sending the signal should be enough? I think the idea is that you define the behavior of the signal to be look at this other piece of state to see whether you should cancel yourself rather than just cancel yourself. Then if a signal is delivered by mistake, it's no big deal - you just look at the other piece of state and decide that you don't need to do anything. I dont see an easy way to pass enough information right now. You cant regenerate enough of it in the to be killed backend as most of the relevant information is only available in the startup process. Inventing yet another segment in shm just for this seems overcomplicated to me. Thats why I suggested locking the procarray for this - without having looked at the code that should prevent a backend slot from beimg reused. Yeah, I understand, but I have a feeling that the code doesn't do it that way right now for a reason. Someone who understands this better than I should comment, but I'm thinking you would likely need to lock the ProcArray in CheckProcSignal as well, and I'm thinking that can't be safely done from within a signal handler. ...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] Status of plperl inter-sp calling
On Wed, Dec 30, 2009 at 5:54 PM, Tim Bunce tim.bu...@pobox.com wrote:
That much works currently. Behind the scenes, when a stored procedure is
loaded into plperl the code ref for the perl sub is stored in a cache.
Effectively just
$cache{$name}[$nargs] = $coderef;
That doesn't seem like enough to guarantee that you've got the right
function. What if you have two functions with the same number of
arguments but different argument types? And what about optional
arguments, variable arguments, etc.?
...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] Status of plperl inter-sp calling
On Dec 30, 2009, at 4:17 PM, Robert Haas wrote:
That much works currently. Behind the scenes, when a stored procedure is
loaded into plperl the code ref for the perl sub is stored in a cache.
Effectively just
$cache{$name}[$nargs] = $coderef;
That doesn't seem like enough to guarantee that you've got the right
function. What if you have two functions with the same number of
arguments but different argument types? And what about optional
arguments, variable arguments, etc.?
As Tim said elsewhere:
I don't see either of those as significant issues: If you need more
control for a particular SP then don't use SP::* to call that SP.
Best,
Davdi
--
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] Thoughts on statistics for continuously advancing columns
Joshua D. Drake wrote: postgres=# analyze verbose test_ten_million; INFO: analyzing public.test_ten_million INFO: test_ten_million: scanned 3000 of 44248 pages, containing 678000 live rows and 0 dead rows; 3000 rows in sample, 1048 estimated total rows ANALYZE Time: 20145.148 ms At an ever larger table sizes, this would turn into 3000 random seeks all over the drive, one at a time because there's no async I/O here to queue requests better than that for this access pattern. Let's say they take 10ms each, not an unrealistic amount of time on current hardware. That's 30 seconds, best case, which is similar to what JD's example is showing even on a pretty small data set. Under load it could easily take over a minute, hammering the disks the whole time, and in a TOAST situation you're doing even more work. It's not outrageous and it doesn't scale linearly with table size, but it's not something you want to happen any more than you have to either--consider the poor client who is trying to get their work done while that is going on. On smaller tables, you're both more likely to grab a useful next page via readahead, and to just have the data you need cached in RAM already. There's a couple of shelves in the response time to finish ANALYZE as you exceed L1/L2 CPU cache size and RAM size, then it trails downward as the seeks get longer and longer once the data you need is spread further across the disk(s). That the logical beginning of a drive is much faster than the logical end doesn't help either. I should generate that graph again one day somewhere I can release it at... -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] exec_execute_message crash
This is just a kluge, and a rather bad one I think. The real problem
here is that AtAbort_Portals destroys the portal contents and doesn't
do anything to record the fact. It should probably be putting the
portal into PORTAL_FAILED state, and what exec_execute_message ought
to be doing is checking for that.
Yeah I thought about that too. in AtAbort_Portals:
--
/*
* Abort processing for portals.
*
* At this point we reset active status and run the cleanup hook if
* present, but we can't release the portal's memory until the cleanup call.
*
* The reason we need to reset active is so that we can replace the unnamed
* portal, else we'll fail to execute ROLLBACK when it arrives.
*/
void
AtAbort_Portals(void)
{
HASH_SEQ_STATUS status;
PortalHashEnt *hentry;
hash_seq_init(status, PortalHashTable);
while ((hentry = (PortalHashEnt *) hash_seq_search(status)) != NULL)
{
Portal portal = hentry-portal;
if (portal-status == PORTAL_ACTIVE)
portal-status = PORTAL_FAILED;
--
Should I change the last if clause to?
if (portal-status == PORTAL_ACTIVE || portal-status ==
PORTAL_READY)
portal-status = PORTAL_FAILED;
zero out the now-dangling pointers in the Portal struct, too.
portal-cplan is already zero out by PortalReleaseCachedPlan. Problem
is, portal-stmts may belong to PortalContext or others (in this
particluar case). So if we want to zero out portal-stmts, we need to
memorize the memory context which it belongs to and we need add a new
struct member to portal. I'm afraid this is an overkill...
It'd be nice to have a test case for this, hint hint ...
Still working on...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
--
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] Hot Standy introduced problem with query cancel behavior
On Thursday 31 December 2009 01:09:57 Robert Haas wrote: On Wed, Dec 30, 2009 at 6:43 PM, Andres Freund and...@anarazel.de wrote: On Wed, Dec 30, 2009 at 2:06 PM, Andres Freund and...@anarazel.de wrote: On Wednesday 30 December 2009 01:13:01 Simon Riggs wrote: On Tue, 2009-12-29 at 11:13 -0500, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Tuesday 29 December 2009 16:22:54 Tom Lane wrote: This seems like a fairly bad idea. One of the intended use-cases is to be able to manually kill -INT a misbehaving backend. Assuming that there will be valid info about the signal in shared memory will break that. Well. That already is the case now. MyProc-recoveryConflictMode is checked to recognize what kind of conflict is being resolved... In that case, HS has already broken it, and we need to fix it not make it worse. My humble opinion is that SIGINT should not be overloaded with multiple meanings. We already have a multiplexed signal mechanism, which is what should be used for any additional signal reasons HS may need to introduce. It's a revelation to me, but yes, I see it now and agree. I'm looking at Fujii-san's multiplexing patch from Jul 31 to rewrite this code using that mechanism. It sounds like it's a neat fit and it should get around the bug report from Kris also if it all works. Hm. I just read a bit of that multiplexing facility (out of a different reason) and I have some doubt about it being used unmodified for canceling backends: procsignal.c: /* * Note: Since there's no locking, it's possible that the target * process detaches from shared memory and exits right after this * test, before we set the flag and send signal. And the signal slot * might even be recycled by a new process, so it's remotely possible * that we set a flag for a wrong process. That's OK, all the signals * are such that no harm is done if they're mistakenly fired. */ procsignal.h: ... * Also, because of race conditions, it's important that all the signals be * defined so that no harm is done if a process mistakenly receives one. */ When cancelling a backend that behaviour could be a bit annoying ;-) I guess locking procarray during sending the signal should be enough? I think the idea is that you define the behavior of the signal to be look at this other piece of state to see whether you should cancel yourself rather than just cancel yourself. Then if a signal is delivered by mistake, it's no big deal - you just look at the other piece of state and decide that you don't need to do anything. I dont see an easy way to pass enough information right now. You cant regenerate enough of it in the to be killed backend as most of the relevant information is only available in the startup process. Inventing yet another segment in shm just for this seems overcomplicated to me. Thats why I suggested locking the procarray for this - without having looked at the code that should prevent a backend slot from beimg reused. Yeah, I understand, but I have a feeling that the code doesn't do it that way right now for a reason. Someone who understands this better than I should comment, but I'm thinking you would likely need to lock the ProcArray in CheckProcSignal as well, and I'm thinking that can't be safely done from within a signal handler. I don't think we would need to lock in the signal handler. The situation is that two different flags (at this point at least) are needed. FATAL which aborts the session and ERROR which aborts the transaction. Consider the following scenario: - both flag are set while holding a lock on the procarray - starting a new backend requires a lock on the procarray - backend startup cleans up both flags in its ProcSignalSlot (only that specific one as its the only one manipulated under a lock, mind you) - transaction startup clears the ERROR flag under locking - after the new backend started no signal handler targeted for the old backend can get triggered (new pid, if we consider direct reuse of the same pid we have much bigger problems anyway), thus no flag targeted for the old backend can get set That would require a nice comment explaining that but it should work. Another possibility would be to make the whole signal delivery mechanism safe - that should be possible if we had a atomically settable backend id... Unfortunately that would limit the max lifetime for a backend a bit - as sig_atomic_t is 4byte on most platforms. So no backend would be allowed to live after creation of the 2**32-1th backend after it. I don't immediately see a way circumvent that 32bit barrier without using assembler or locks. Andres PS: Hm. For my former message beeing written on a mobile phone it looked surprisingly clean... -- Sent via pgsql-hackers mailing list
Re: [HACKERS] A third lock method
Bruce Momjian wrote: I must be missing something but I thought the only problem with our existing snapshot system was that you could see a row updated after your snapshot was created, and that the solution to that was to abort the transaction that would see the new row. Can you tell me what I am missing? Well, that's roughly on target as a 30,000 foot overview, although I think you're also working in an issue from the read committed implementation. The issue with read committed is that you start with one snapshot but may wind up combining views of data from that initial snapshot with one or more updated versions of rows from subsequent commits -- if you blocked on a conflicting write which was subsequently committed. I recently started a thread which drifted into a discussion of that issue, but it's almost completely orthogonal to the issue of implementing truly serializable transactions -- the overlap is that a fix for the read committed anomalies might provide groundwork for an optimization to the serializable implementation. After re-reading the thread on the read committed issue and pondering it a bit more, I'm inclined to think that we should worry about that if and when serializable is working and we actually see problems that the aforementioned optimization might fix. (Unless someone cares enough about the read committed anomalies to want to champion that as a separate issue.) What I'm trying to stay focused on is the serializable implementation. I'd settle for the traditional Strict 2 Phase Locking (S2PL) approach, but since there is a new technique which appears to perform much better than that for most workloads, and has most of the benefits of snapshot isolation (reads don't block writes and vice versa), I figure why not go for the gold. You are right in that the new technique will basically work like snapshot isolation but will roll back a transaction when there is one transaction which reads data which is modified by a concurrent transaction and writes data which is read by a concurrent transaction (assuming all are serializable). Years of research by Dr Alan Fekete et al has established that only when there is a cycle of such read-write dependencies among transactions, the graph contains a pivot transaction which has dependencies in both directions (to the same or different transactions), and a transaction which is on the write end of such an edge commits first, can snapshot isolation mode show behavior inconsistent with fully serializable behavior. The SSI technique doesn't attempt to build and analyze a complete graph of such dependencies because the overhead would be prohibitive; rather, it looks for the pivot which must be present in such a graph. The SSI technique requires that serializable transactions (but not transactions in other isolation levels) use a non-blocking SIREAD lock to track reads, so that the read-write dependencies among concurrent serializable transactions can be identified. The hard part is that this does require some implementation of predicate locking to prevent problems with phantom rows. Honestly, that is probably going to be over half the battle. I had been nibbling around the edges of the serializable issue trying to address optimizations which should help performance of serializable transactions by reducing the rollback rate; but came to the conclusion that that was all premature optimization and a distraction. I think the thing to do is start with the hard part, so I've been reading up on practical techniques for implementing predicate locking, and reading through the PostgreSQL locking code, trying to get my head around this. Once this hardest part is solved, I really think that a workable serializable mode is not too far past it, and *then* would be the time to look at optimizations. It's just a little bit of a stretch to call SILOCKs locks, because they don't actually block anything. They are used at various points to see where a transaction is reading data which has been modified by another transaction or vice versa. And they do need to be kept until all concurrent transactions have completed. Other than those quirks, they behave pretty much like read locks, though, so it seems to make sense to use the locking system for them. The differences are such that I thought a new lock method might be appropriate. This thread is to try to solicit opinions on whether that makes sense to anyone but me. :-) Once I sort out the subject issue, I'm about ready to try to start generating a very rough prototype of predicate locking. I don't want to start a discussion of those details on this thread, because it seems to me that a decision on the subject issue affects significant details about how I go about that. Consider this the 5,000 foot overview. Previous thread contain much more, and references to more authoritative documents. This is not a small effort. I'll be surprised if it comes in at less than 2,000 lines of
Re: [HACKERS] A third lock method
Kevin Grittner wrote: Once I sort out the subject issue, I'm about ready to try to start generating a very rough prototype of predicate locking. I don't want to start a discussion of those details on this thread, because it seems to me that a decision on the subject issue affects significant details about how I go about that. Consider this the 5,000 foot overview. Previous thread contain much more, and references to more authoritative documents. This is not a small effort. I'll be surprised if it comes in at less than 2,000 lines of code, plus documentation and tests. I'm not really expecting it to make the next release after 8.5. With some luck, maybe the next release after that. And that's all conditional on our CIO approving programming time for the effort. I'm currently trying to dig in far enough to provide more accurate estimates and time lines to facilitate approval. Hopefully I haven't just scared you off of your offer to help. ;-) Thanks, I do understand it better now. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
