Re: [HACKERS] [PATCH] Largeobject access controls
2009/9/24 KaiGai Kohei kai...@ak.jp.nec.com: Example) postgres=# SET SESSION AUTHORIZATION ymj; SET postgres= SELECT loread(lo_open(16453, x'4'::int), 20); ERROR: permission denied for largeobject 16453 postgres=# SET largeobject_compat_acl = on; enables compatible mode SET (Only superuser can set it) postgres=# SET SESSION AUTHORIZATION ymj; SET postgres= SELECT loread(lo_open(16453, x'4'::int), 20); NOTICE: permission denied for largeobject 16453 dose not prevent it i'm not really sure the warnings are worth the trouble but if you want to do it then the NOTICE version should use another message... i'm not comfortable with a permission denied that is simply ignored... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Largeobject access controls
Jaime Casanova wrote: 2009/9/23 KaiGai Kohei kai...@ak.jp.nec.com: Jaime, KaiGai Kohei wrote: | ALTER LARGE OBJECT is working, but now that we can change the owner of | a LO we should be able to see who the actual owner is... i mean we | should add an owner column in \dl for psql (maybe \dl+) and maybe an | lo_owner() function. | | I would like to buy your idea at the revised patch. Now we don't have xxx_owner() function for other database objects, such as tables, procedures and so on. good point, but we have has_xx_privileges() family of functions but i think we can add them later if needed... Yes, the has_xx_privileges() family should be added later or soon. Anyway, what I wanted to say is we have no special functions to show owner of the database objects. Jaime Casanova wrote: Do you think the largeobject_compat_acl is a meaningful name, instead? maybe something like largeobject_security_controls? It is important to contain a term of compat which means compatible, because this GUC does not disables all the security checks. The v8.4.x checks superuser privilege on using lo_import()/lo_export(). It is also checked in this patch, even if the GUC is turned on. The purpose of the GUC is to provide compatible behavior, not to provide a stuff to turn on/off all the security features in largeobjects. that's why the section in the postgresql.conf is called VERSION/PLATFORM COMPATIBILITY and the subsection Previous PostgreSQL Versions we have other compatibilty GUC and no ones of those has the term compat in it... Indeed, I put the largeobject_compat_acl in the compatibility section, but no other GUCs have compat prefix/suffix. It seems to me fair enough. So, I still prefer the largeobject_compat_acl. maybe enhanced_largeobjects_checks or enhanced_lo_checks or make the GUC an enum and name it largeobject_control_checks with posible values basic and enhanced But, isn't the enhanced tumid expression? It just applies native database privilege mechanism on largeobjects, as if it does on other objects. An other alternative is largeobject_check_acl. What's your feeling? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Largeobject access controls
Jaime Casanova wrote: 2009/9/24 KaiGai Kohei kai...@ak.jp.nec.com: Example) postgres=# SET SESSION AUTHORIZATION ymj; SET postgres= SELECT loread(lo_open(16453, x'4'::int), 20); ERROR: permission denied for largeobject 16453 postgres=# SET largeobject_compat_acl = on; enables compatible mode SET (Only superuser can set it) postgres=# SET SESSION AUTHORIZATION ymj; SET postgres= SELECT loread(lo_open(16453, x'4'::int), 20); NOTICE: permission denied for largeobject 16453 dose not prevent it i'm not really sure the warnings are worth the trouble but if you want to do it then the NOTICE version should use another message... i'm not comfortable with a permission denied that is simply ignored... It is not a significant issue whether the compatible mode allows users to bypass ACL checks with or without any notifications. Which is the preferable one? 1. It always generates notifications whenever access violations. 2. It generates notifications at the first violation only. 3. It never generates notifications. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication patch for CommitFest 2009-09
Hi, Sorry for the delay. On Mon, Sep 21, 2009 at 4:51 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Having gone through the patch now in more detail, I think it's in pretty good shape. I'm happy with the overall design, except that I haven't been able to make up my mind if walreceiver should indeed be a stand-alone program as discussed, or a postmaster child process as in the patch you submitted. Putting that question aside for a moment, here's some minor things, in no particular order: Thanks for the comments. - The async API in PQgetXLogData is quite different from the other commands. It's close to the API from PQgetCopyData(), but doesn't return a malloc'd buffer like PQgetCopyData does. I presume that's to optimize away the extra memcpy step? Yes. This is for preventing extra memcpy. I don't think that's really necessary, I don't recall any complaints about that in PQgetCopyData(), and if it does become an issue, it could be optimized away by mallocing the buffer first and reading directly to that. OK. I'll change PQgetXLogData() to return a malloc'd buffer, and will remove PQmarkConsumed(). - Can we avoid sprinkling XLogStreamingAllowed() calls to places where we check if WAL-logging is required (nbtsort.c, copy.c etc.). I think we need a new macro to encapsulate (XLogArchivingActive() || XLogStreamingAllowed()). Yes. I'll introduce a new macro XLogIsNeeded() which encapsulates (XLogArchivingActive() || XLogStreamingAllowed()). - Is O_DIRECT ever a good idea in walreceiver? If it's really direct and doesn't get cached, the startup process will need to read from disk. Good point. I agree that O_DIRECT is useless if walreceiver works with the startup process. It might be useful if only stand-alone walreceiver program is executed in the standby. - Can we replace read/write_conninfo with just a long-enough field in shared mem? Would be simpler. (this is moot if we go with the stand-alone walreceiver program and pass it as a command-line argument) Yes, if we can decide the length of conninfo. Since I could not decide that, I used read/write_conninfo to tell walreceiver the conninfo. Is the fixed size 1024B enough for conninfo? - walreceiver shouldn't die on connection error, just to be restarted by startup process. Can we add error handling a la bgwriter and have a retry loop within walreceiver? (again, if we go with a stand-alone walreceiver program, it's probably better to have startup process responsible to restart walreceiver, as it is now) Error handling a la bgwriter? You mean that PG_exception_stack should be set up to handle an ERROR exception? Anyway, I'll change walreceiver to retry connecting to the primary after an error occurs in PQstartXLogStreaming()/PQgetXLogData()/ PQputXLogRecPtr(). Should we set an upper limit of the number of the retries? - pq_wait in backend waits until you can read or write at least 1 byte. There is no guarantee that you can send or read the whole message without blocking. We'd have to put the socket in non-blocking mode for that. I'm not sure what the implications of this are. Umm... AFAIK, poll and select guarantee that at least the subsequent recv will not be blocked. If there is only 1 byte available in the buffer, recv would read that 1 byte and return immediately. I'm not sure if send will get stuck even after poll is passed. In my environment (RHEL5), send seems not to be blocked. - we should include system_identifier somewhere in the replication startup handshake. Otherwise you can connect to server from a different system and have logs shipped, if they happen to be roughly at the same point in WAL. Replay will almost certainly fail, but we should error earlier. Agreed. I'll do that. - I know I said we should have just asynchronous replication at first, but looking ahead, how would you do synchronous? As the previous patch did, I'm going to make walsender read the latest XLOG from wal_buffers, introduce the signaling between a backend and walsender, and keep a backend waiting until the specified XLOG has been written or fsynced in the standby. What kind of signaling is needed between walreceiver and startup process for that? I was thinking that the synchronization mode which a client waits until XLOG has been applied is not necessary right now, so no signaling is also not required between those processes yet. But, HS requires this capability? - 'replication' shouldn't be a real database. Agreed. I'll remove that. I found the paging logic in walsender confusing, and didn't like the idea that walsender needs to set the XLOGSTREAM_END_SEG flag. Surely walreceiver knows how to split the WAL into files without such a flag. I reworked that logic, I think it's easier to understand now. I kept the support for the flag in libpq and the protocol for now, but it should be removed too, or repurposed to indicate that pg_switch_xlog() was done in the master. I've pushed that to
[HACKERS] BEGIN TRANSACTION and START TRANSACTION: different error handling
Hi all, I am using psqlodbc to connect to PostgreSQL8.2.4 server on Linux. The manual says BEGIN TRANSACATION is equlvalent to START TRANSACTION, but it turns out that they throw different error message and have different effect to subsequent queries. I have a table CREATE TEMPORARY TABLE ooid (oid_ INTEGER NOT NULL); The autocommit is set to on. When inserting into ooid with a NULL value within a transaction, I expect the transaction is aborted and rollback is executed automatically. With the transaction started by BEGIN TRANSACTION, PostgreSQL runs as expected. The server log shows error message is ERROR: null value in column oid_ violates not-null constraint, and any following query runs well. - 2009-09-24 13:53:13 JST jmdb postgres STATEMENT: BEGIN TRANSACTION; DELETE FROM pid; INSERT INTO pid VALUES (9765); DELETE FROM jid; INSERT INTO jid VALUES (644); DELETE FROM did; INSERT INTO did VALUES (2029); DELETE FROM ooid; INSERT INTO ooid (SELECT COALESCE( (SELECT Document.oid_FROM Document, JobWHERE Document.DocNum = (SELECT DocNum FROM did)AND Job.jobId = (SELECT jobId FROM jid)AND Document.memberOf_ = Job.oid_AND Job.assignedTo_ = (SELECT PrinterObjId FROM pid)), NULL)); update Document set DocName=NULL where Document.oid_=(SELECT oid_ FROM ooid);update Document set DocLastModified = CURRENT_TIMESTAMP where Document.oid_=(SELECT oid_ FROM ooid); COMMIT; 2009-09-24 13:53:17 JST jmdb postgres ERROR: null value in column oid_ violates not-null constraint - When the transaction is started by START TRANSACTION, the error message is different, and all following query failed with the same error message ERROR: current transaction is aborted, commands ignored until end of transaction block. - 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: START TRANSACTION; DELETE FROM pid; INSERT INTO pid VALUES (9765); DELETE FROM jid; INSERT INTO jid VALUES (779); DELETE FROM did; INSERT INTO did VALUES (2530); DELETE FROM ooid; INSERT INTO ooid (SELECT COALESCE( (SELECT Document.oid_FROM Document, JobWHERE Document.DocNum = (SELECT DocNum FROM did)AND Job.jobId = (SELECT jobId FROM jid)AND Document.memberOf_ = Job.oid_AND Job.assignedTo_ = (SELECT PrinterObjId FROM pid)), NULL)); update Document set DocName=NULL where Document.oid_=(SELECT oid_ FROM ooid);update Document set DocLastModified = CURRENT_TIMESTAMP where Document.oid_=(SELECT oid_ FROM ooid); COMMIT; 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: SELECT oid_,DocName FROM DocView WHERE DocNum=2530 and DocJobId=779 and pid_=0 FOR READ ONLY 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: DELETE FROM Printer WHERE PrinterObjId=0; 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block - I searched archives, but no related comment is found. Should I do some setting on server to make the START TRANSACTION act as BEGIN TRANSACTION? Thank you. Greetings, Ning Xie -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: walreceiver settings Re: [HACKERS] Streaming Replication patch for CommitFest 2009-09
Hi, On Mon, Sep 21, 2009 at 1:55 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The startup process could capture stderr from walreceiver and forward it with elog(LOG). The startup process should obtain also the message level in some way (pipe?), and control the messages according to it. It's confusing that every messages are output with LOG level. The startup process could kill and restart walreceiver to reload. If reloading is really required, that is. I think that it's confusing that SIGHUP kills a process. If walreceiver is being monitored, the monitoring tool might raise a false alert. Which GUC parameters are we concerned about? The ones related to logging you mentioned, but if we handle logging via a pipe to the startup process, that won't be an issue. wal_sync_method and fsync. At least I'd like to use fdatasync instead of fsync for performance improvement. Sounds complicated.. One option that you might well want to change on the fly is the connection info string in recovery.conf. Neither of the above really cater for that, unless we make walreceiver read recovery.conf as well. I think we should keep walreceiver quite dumb. Agreed. 4) Change walreceiver back to a child process of postmaster. Yeah, that's not out of the question either. I like this simplest approach. But, as you pointed out, in the original patch, the way to launch walreceiver is not robust. We need to add some codes using examples from autovacuum launcher and worker. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BEGIN TRANSACTION and START TRANSACTION: different error handling
On Thu, 2009-09-24 at 17:51 +0900, ning wrote: Hi all, I am using psqlodbc to connect to PostgreSQL8.2.4 server on Linux. The manual says BEGIN TRANSACATION is equlvalent to START TRANSACTION, but it turns out that they throw different error message and have different effect to subsequent queries. I have a table CREATE TEMPORARY TABLE ooid (oid_ INTEGER NOT NULL); The autocommit is set to on. When inserting into ooid with a NULL value within a transaction, I expect the transaction is aborted and rollback is executed automatically. With the transaction started by BEGIN TRANSACTION, PostgreSQL runs as expected. The server log shows error message is ERROR: null value in column oid_ violates not-null constraint, and any following query runs well. I expect the transaction is aborted and rollback is executed automatically. - this is not how postgreSQL behaves. PostgreSQL needs an explicit end of transaction from client, either COMMIT; or ROLLBACK; when run from psql, they both act the same, except the string returned hannu=# begin transaction; BEGIN hannu=# select 1/0; ERROR: division by zero hannu=# select 1/0; ERROR: current transaction is aborted, commands ignored until end of transaction block hannu=# abort; ROLLBACK hannu=# start transaction; START TRANSACTION hannu=# select 1/0; ERROR: division by zero hannu=# select 1/0; ERROR: current transaction is aborted, commands ignored until end of transaction block hannu=# abort; ROLLBACK I suspect, that psqlodbc is the one doing the automatic rollback and it seems to rely on reply BEGIN to establish an in-transaction state. so when start transaction; returns START TRANSACTION instead of BEGIN, psqlodbc does not realise that it is in transaction and does not initiate the automatic rollback. - 2009-09-24 13:53:13 JST jmdb postgres STATEMENT: BEGIN TRANSACTION; DELETE FROM pid; INSERT INTO pid VALUES (9765); DELETE FROM jid; INSERT INTO jid VALUES (644); DELETE FROM did; INSERT INTO did VALUES (2029); DELETE FROM ooid; INSERT INTO ooid (SELECT COALESCE( (SELECT Document.oid_FROM Document, JobWHERE Document.DocNum = (SELECT DocNum FROM did)AND Job.jobId = (SELECT jobId FROM jid)AND Document.memberOf_ = Job.oid_AND Job.assignedTo_ = (SELECT PrinterObjId FROM pid)), NULL)); update Document set DocName=NULL where Document.oid_=(SELECT oid_ FROM ooid);update Document set DocLastModified = CURRENT_TIMESTAMP where Document.oid_=(SELECT oid_ FROM ooid); COMMIT; 2009-09-24 13:53:17 JST jmdb postgres ERROR: null value in column oid_ violates not-null constraint - When the transaction is started by START TRANSACTION, the error message is different, and all following query failed with the same error message ERROR: current transaction is aborted, commands ignored until end of transaction block. - 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: START TRANSACTION; DELETE FROM pid; INSERT INTO pid VALUES (9765); DELETE FROM jid; INSERT INTO jid VALUES (779); DELETE FROM did; INSERT INTO did VALUES (2530); DELETE FROM ooid; INSERT INTO ooid (SELECT COALESCE( (SELECT Document.oid_FROM Document, JobWHERE Document.DocNum = (SELECT DocNum FROM did)AND Job.jobId = (SELECT jobId FROM jid)AND Document.memberOf_ = Job.oid_AND Job.assignedTo_ = (SELECT PrinterObjId FROM pid)), NULL)); update Document set DocName=NULL where Document.oid_=(SELECT oid_ FROM ooid);update Document set DocLastModified = CURRENT_TIMESTAMP where Document.oid_=(SELECT oid_ FROM ooid); COMMIT; 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: SELECT oid_,DocName FROM DocView WHERE DocNum=2530 and DocJobId=779 and pid_=0 FOR READ ONLY 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: DELETE FROM Printer WHERE PrinterObjId=0; 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block - I searched archives, but no related comment is found. Should I do some setting on server to make the START TRANSACTION act as BEGIN TRANSACTION? Thank you. Greetings, Ning Xie -- 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] DefaultACLs
Petr Jelinek wrote: I made some more small adjustments - mainly renaming stuff after Tom's comment on anonymous code blocks patch and removed one unused shared dependency. Hi, the patch still has some issues with dependency handling: postgres=# create role test; CREATE ROLE postgres=# create role test2; CREATE ROLE postgres=# create schema s; CREATE SCHEMA postgres=# alter default privileges in schema s for user test2 grant insert on table to test; ALTER DEFAULT PRIVILEGES postgres=# drop role test2; DROP ROLE postgres=# drop schema s; ERROR: could not find tuple for default acls 16387 postgres=# At this moment pg_default_acls is empty and schema s is undroppable... I got an unexpected server exit after that once, when I executed \ds from psql, but unfortunately don't have a backtrace (forgot to ulimit -c unlimited). The next time I tried to provoke that backend crash I failed: after the ERROR: could not find tuple for default acls 16387 I'm only stuck with an undroppable schema, but the rest of the system works normally. Apart from that all my complains from the previous review seem to be addressed, except for the tab completion... Not sure if it's mandatory for commit, but it sure would be useful. Marking as Waiting on Author. Cheers, Jan -- 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] Streaming Replication patch for CommitFest 2009-09
Hi, On Mon, Sep 21, 2009 at 4:51 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I found the paging logic in walsender confusing, and didn't like the idea that walsender needs to set the XLOGSTREAM_END_SEG flag. Surely walreceiver knows how to split the WAL into files without such a flag. I reworked that logic, I think it's easier to understand now. I kept the support for the flag in libpq and the protocol for now, but it should be removed too, or repurposed to indicate that pg_switch_xlog() was done in the master. I've pushed that to 'replication-orig' branch in my git repository, attached is the same as a diff against your SR_0914.patch. In the 'replication-orig' branch, walreceiver fsyncs the previous XLOG file after receiving new XLOG records before writing them. This would increase the backend's waiting time for replication in synchronous case. The walreceiver should fsync the XLOG file after sending ACK (if needed) before receiving the next XLOG records? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby 0.2.1
Heikki Linnakangas wrote: The problem becomes a lot easier if we accept that it's OK to have a lock included in the running-xacts snapshot and also appear in a XLOG_RELATION_LOCK record later. The standby should handle that gracefully already. If we just remove RecoveryInfoLock, that can happen, but it still won't be possible for a lock to be missed out which is what we really care about. I see the problem with that now. Without the lock, it's possible that the XLOG_RELATION_LOCK WAL record is written before the XLOG_RUNNING_XACTS record. If the lock is not included in the snapshot, we want the lock WAL record to be after the snapshot record. So i guess we'll need the RecoveryInfoLock. But we don't need to hold it across the wait. I think it's enough to acquire it just before writing the WAL record in LockAcquire. That will ensure that the WAL record isn't written until the snapshot is completely finished. -- 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] Streaming Replication patch for CommitFest 2009-09
Fujii Masao wrote: In the 'replication-orig' branch, walreceiver fsyncs the previous XLOG file after receiving new XLOG records before writing them. This would increase the backend's waiting time for replication in synchronous case. The walreceiver should fsync the XLOG file after sending ACK (if needed) before receiving the next XLOG records? I don't follow. Wareceiver does fsync the file just after writing it if the fsync_requested flag was set in the message. Surely that would be set in synchronous mode, that's what the flag is for, right? -- 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] Streaming Replication patch for CommitFest 2009-09
Hi, On Thu, Sep 24, 2009 at 7:41 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Fujii Masao wrote: In the 'replication-orig' branch, walreceiver fsyncs the previous XLOG file after receiving new XLOG records before writing them. This would increase the backend's waiting time for replication in synchronous case. The walreceiver should fsync the XLOG file after sending ACK (if needed) before receiving the next XLOG records? I don't follow. Wareceiver does fsync the file just after writing it if the fsync_requested flag was set in the message. Surely that would be set in synchronous mode, that's what the flag is for, right? That's the case where fsync is issued at the end of segment. In this case, since the fsync_requested flag is not set, walreceiver doesn't perform fsync in that loop. After the next XLOG arrives, walreceiver does fsync to the previous file, in XLogWalRcvWrite(). Am I missing something? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication patch for CommitFest 2009-09
Fujii Masao wrote: On Mon, Sep 21, 2009 at 4:51 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: - Can we replace read/write_conninfo with just a long-enough field in shared mem? Would be simpler. (this is moot if we go with the stand-alone walreceiver program and pass it as a command-line argument) Yes, if we can decide the length of conninfo. Since I could not decide that, I used read/write_conninfo to tell walreceiver the conninfo. Is the fixed size 1024B enough for conninfo? Yeah, that should be plenty. - walreceiver shouldn't die on connection error, just to be restarted by startup process. Can we add error handling a la bgwriter and have a retry loop within walreceiver? (again, if we go with a stand-alone walreceiver program, it's probably better to have startup process responsible to restart walreceiver, as it is now) Error handling a la bgwriter? You mean that PG_exception_stack should be set up to handle an ERROR exception? Yep. Anyway, I'll change walreceiver to retry connecting to the primary after an error occurs in PQstartXLogStreaming()/PQgetXLogData()/ PQputXLogRecPtr(). Should we set an upper limit of the number of the retries? I don't think we need an upper limit. - pq_wait in backend waits until you can read or write at least 1 byte. There is no guarantee that you can send or read the whole message without blocking. We'd have to put the socket in non-blocking mode for that. I'm not sure what the implications of this are. Umm... AFAIK, poll and select guarantee that at least the subsequent recv will not be blocked. If there is only 1 byte available in the buffer, recv would read that 1 byte and return immediately. I'm not sure if send will get stuck even after poll is passed. In my environment (RHEL5), send seems not to be blocked. Hmm, I guess you're right. - I know I said we should have just asynchronous replication at first, but looking ahead, how would you do synchronous? As the previous patch did, I'm going to make walsender read the latest XLOG from wal_buffers, introduce the signaling between a backend and walsender, and keep a backend waiting until the specified XLOG has been written or fsynced in the standby. Ok. I don't think walsender needs to access wal_buffers even then, though. Once the backend has written the WAL, walsender can well read it from disk (it will surely be in OS cache still). What kind of signaling is needed between walreceiver and startup process for that? I was thinking that the synchronization mode which a client waits until XLOG has been applied is not necessary right now, so no signaling is also not required between those processes yet. But, HS requires this capability? Yeah, I think it will be important with hot standby. It's a much more useful guarantee that once COMMIT returns, the transactions is visible in the standby, than that it's merely fsync'd to disk in the standby. (don't need to solve it now, let's do just asynchronous mode now, but it's something to keep in mind) -- 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] Streaming Replication patch for CommitFest 2009-09
Fujii Masao wrote: On Thu, Sep 24, 2009 at 7:41 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Fujii Masao wrote: In the 'replication-orig' branch, walreceiver fsyncs the previous XLOG file after receiving new XLOG records before writing them. This would increase the backend's waiting time for replication in synchronous case. The walreceiver should fsync the XLOG file after sending ACK (if needed) before receiving the next XLOG records? I don't follow. Wareceiver does fsync the file just after writing it if the fsync_requested flag was set in the message. Surely that would be set in synchronous mode, that's what the flag is for, right? That's the case where fsync is issued at the end of segment. In this case, since the fsync_requested flag is not set, walreceiver doesn't perform fsync in that loop. After the next XLOG arrives, walreceiver does fsync to the previous file, in XLogWalRcvWrite(). Ok. I don't see anything wrong with that. If the primary didn't set fsync_requested, it's not in a hurry to get an acknowledgment. I guess we could check *after* writing, if we just finished filling the segment. If we did, we could fsync since we're going to fsync anyway as soon as we receive the next message. Not sure if it's worth the trouble. -- 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] [PATCH] DefaultACLs
Jan Urbański napsal(a): Petr Jelinek wrote: I made some more small adjustments - mainly renaming stuff after Tom's comment on anonymous code blocks patch and removed one unused shared dependency. Hi, the patch still has some issues with dependency handling: postgres=# create role test; CREATE ROLE postgres=# create role test2; CREATE ROLE postgres=# create schema s; CREATE SCHEMA postgres=# alter default privileges in schema s for user test2 grant insert on table to test; ALTER DEFAULT PRIVILEGES postgres=# drop role test2; DROP ROLE postgres=# drop schema s; ERROR: could not find tuple for default acls 16387 postgres=# Fixed and added regression test for dependency handling. -- Regards Petr Jelinek (PJMODOS) defacl-2009-09-24.diff.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] [PATCH] DefaultACLs
OK, the previous problem went away, but I can still do something like that:
postgres=# create role test;
CREATE ROLE
postgres=# create role test2;
CREATE ROLE
postgres=# create database db;
CREATE DATABASE
postgres=# \c db
psql (8.5devel)
You are now connected to database db.
db=# alter default privileges for role test2 grant insert on table to test;
ALTER DEFAULT PRIVILEGES
db=# \c postgres
psql (8.5devel)
You are now connected to database postgres.
postgres=# drop role test2;
DROP ROLE
postgres=# \c db
psql (8.5devel)
You are now connected to database db.
db=# select * from pg_default_acls ;
defaclrole | defaclnamespace | defaclobjtype | defacllist
+-+---+--
16385 | 0 | r |
{16385=arwdDxt/16385,test=a/wulczer}
(1 row)
db=#
Dependencies suck, I know..
Jan
--
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] BEGIN TRANSACTION and START TRANSACTION: different error handling
On Thu, Sep 24, 2009 at 12:16:43PM +0300, Hannu Krosing wrote: I expect the transaction is aborted and rollback is executed automatically. - this is not how postgreSQL behaves. PostgreSQL needs an explicit end of transaction from client, either COMMIT; or ROLLBACK; when run from psql, they both act the same, except the string returned hannu=# begin transaction; BEGIN hannu=# select 1/0; ERROR: division by zero hannu=# select 1/0; ERROR: current transaction is aborted, commands ignored until end of transaction block hannu=# abort; ROLLBACK hannu=# start transaction; START TRANSACTION hannu=# select 1/0; ERROR: division by zero hannu=# select 1/0; ERROR: current transaction is aborted, commands ignored until end of transaction block hannu=# abort; ROLLBACK I suspect, that psqlodbc is the one doing the automatic rollback and it seems to rely on reply BEGIN to establish an in-transaction state. so when start transaction; returns START TRANSACTION instead of BEGIN, psqlodbc does not realise that it is in transaction and does not initiate the automatic rollback. Well. I'd always thought BEGIN and START were syntactic Aspartame and had the same underlying implementation. So this is a surprise. Why do they return a different status? -dg ` -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] DefaultACLs
Jan Urbański napsal(a): Dependencies suck, I know.. Cross-database dependencies do. I had to make target role owner of the default acls which adds some side effects like the fact that it blocks DROP ROLE so DROP OWNED BY has to be used. As for REASSIGN OWNED, it does not reassign anything (I don't think it's a good idea to reassign default acls) it just spits warning with hint what to do if user plans to drop the role. -- Regards Petr Jelinek (PJMODOS) defacl-2009-09-24-2.diff.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] BEGIN TRANSACTION and START TRANSACTION: different error handling
On Thu, Sep 24, 2009 at 6:16 PM, Hannu Krosing ha...@krosing.net wrote: On Thu, 2009-09-24 at 17:51 +0900, ning wrote: Hi all, I am using psqlodbc to connect to PostgreSQL8.2.4 server on Linux. The manual says BEGIN TRANSACATION is equlvalent to START TRANSACTION, but it turns out that they throw different error message and have different effect to subsequent queries. I have a table CREATE TEMPORARY TABLE ooid (oid_ INTEGER NOT NULL); The autocommit is set to on. When inserting into ooid with a NULL value within a transaction, I expect the transaction is aborted and rollback is executed automatically. With the transaction started by BEGIN TRANSACTION, PostgreSQL runs as expected. The server log shows error message is ERROR: null value in column oid_ violates not-null constraint, and any following query runs well. I expect the transaction is aborted and rollback is executed automatically. - this is not how postgreSQL behaves. PostgreSQL needs an explicit end of transaction from client, either COMMIT; or ROLLBACK; when run from psql, they both act the same, except the string returned hannu=# begin transaction; BEGIN hannu=# select 1/0; ERROR: division by zero hannu=# select 1/0; ERROR: current transaction is aborted, commands ignored until end of transaction block hannu=# abort; ROLLBACK hannu=# start transaction; START TRANSACTION hannu=# select 1/0; ERROR: division by zero hannu=# select 1/0; ERROR: current transaction is aborted, commands ignored until end of transaction block hannu=# abort; ROLLBACK I suspect, that psqlodbc is the one doing the automatic rollback and it seems to rely on reply BEGIN to establish an in-transaction state. so when start transaction; returns START TRANSACTION instead of BEGIN, psqlodbc does not realise that it is in transaction and does not initiate the automatic rollback. - 2009-09-24 13:53:13 JST jmdb postgres STATEMENT: BEGIN TRANSACTION; DELETE FROM pid; INSERT INTO pid VALUES (9765); DELETE FROM jid; INSERT INTO jid VALUES (644); DELETE FROM did; INSERT INTO did VALUES (2029); DELETE FROM ooid; INSERT INTO ooid (SELECT COALESCE( (SELECT Document.oid_ FROM Document, Job WHERE Document.DocNum = (SELECT DocNum FROM did) AND Job.jobId = (SELECT jobId FROM jid) AND Document.memberOf_ = Job.oid_ AND Job.assignedTo_ = (SELECT PrinterObjId FROM pid)), NULL)); update Document set DocName=NULL where Document.oid_=(SELECT oid_ FROM ooid);update Document set DocLastModified = CURRENT_TIMESTAMP where Document.oid_=(SELECT oid_ FROM ooid); COMMIT; 2009-09-24 13:53:17 JST jmdb postgres ERROR: null value in column oid_ violates not-null constraint - When the transaction is started by START TRANSACTION, the error message is different, and all following query failed with the same error message ERROR: current transaction is aborted, commands ignored until end of transaction block. - 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: START TRANSACTION; DELETE FROM pid; INSERT INTO pid VALUES (9765); DELETE FROM jid; INSERT INTO jid VALUES (779); DELETE FROM did; INSERT INTO did VALUES (2530); DELETE FROM ooid; INSERT INTO ooid (SELECT COALESCE( (SELECT Document.oid_ FROM Document, Job WHERE Document.DocNum = (SELECT DocNum FROM did) AND Job.jobId = (SELECT jobId FROM jid) AND Document.memberOf_ = Job.oid_ AND Job.assignedTo_ = (SELECT PrinterObjId FROM pid)), NULL)); update Document set DocName=NULL where Document.oid_=(SELECT oid_ FROM ooid);update Document set DocLastModified = CURRENT_TIMESTAMP where Document.oid_=(SELECT oid_ FROM ooid); COMMIT; 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: SELECT oid_,DocName FROM DocView WHERE DocNum=2530 and DocJobId=779 and pid_=0 FOR READ ONLY 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block 2009-09-24 13:53:59 JST jmdb postgres STATEMENT: DELETE FROM Printer WHERE PrinterObjId=0; 2009-09-24 13:53:59 JST jmdb postgres ERROR: current transaction is aborted, commands ignored until end of transaction block - I searched archives, but no related comment is found. Should I do some setting on server to make the START TRANSACTION act as BEGIN TRANSACTION? Thank you. Greetings, Ning Xie hank you Hannu. PostgreSQL needs an explicit end of transaction from client, either COMMIT; or ROLLBACK; In psql, after error occurred in transaction an explicit COMMIT leads to ROLLBACK, either for begin transaction or start transaction. Doesn't this imply the PostgreSQL server react a ROLLBACK when receiving a COMMIT according to the server-side state? Or do you mean that it's psql that sends a ROLLBACK to server when receiving a COMMIT according to the client-side state(which is based on the reply of
Re: [HACKERS] [PATCH] DefaultACLs
Petr Jelinek wrote: Jan Urbański napsal(a): Dependencies suck, I know.. Cross-database dependencies do. I had to make target role owner of the default acls which adds some side effects like the fact that it blocks DROP ROLE so DROP OWNED BY has to be used. As for REASSIGN OWNED, it does not reassign anything (I don't think it's a good idea to reassign default acls) it just spits warning with hint what to do if user plans to drop the role. OK, so that addresses my last gripe. It seems that when you try to drop a role to which you have granted some privileges before, you can't, and when you REASSIGN OWNED, it doesn't help. So maybe it's not even necessary to give a warning when REASSIGN OWNED is called on default ACLs. Only loose end is tab completion which can probably be added later on. I'm also not sure if we wouldn't like to have ALTER DEFAULT PRIVILEGES FOR ALL ROLES (or something similar), so you won't have to ALTER DEFAULT PRIVILEGES for each developer you gave a DB login to. Petr told me that was the previous design but has been shot down - I found references to that on the mailing list, but most complains were about tying the syntax to ALTER SCHEMA. Since we now have ALTER DEFAULT PRIVILEGES I think it might make sense to introduce a way to set the default privileges for all roles (and give superusers the right to do it). This can be added later on, but maybe we could make the syntax work so when you do ALTER DEFAULT PRIVILEGES without FOR ROLE you set them for all roles in the current DB and if you want to set them for yourself, you need to specify FOR ROLE yourrole. That'd be a minor change in the patch. Setting to Ready for Committer (and leaving to the committer the decision whether to support FOR ALL ROLES and what to do about the warning). Jan -- 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] Using results from INSERT ... RETURNING
Robert Haas wrote: However, that's not the whole problem, either. To your point about documentation, it seems this path doesn't touch the README at all, and it needs to, because some of the statements in that file would certainly become false were this patch to be applied. So I think we should at a minimum ask the patch author to (1) fix the explain bugs I found and (2) update the README, as well as (3) revert needless whitespace changes - there are a couple in execMain.c, from the looks of it. I overlooked the README completely. I'll see what I can do about these and submit an updated patch in a couple of days. Regards, Marko Tiikkaja -- 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] Unicode Normalization
On Sep 24, 2009, at 6:24 AM, p...@thetdh.com wrote: In a context using normalization, wouldn't you typically want to store a normalized-text type that could perhaps (depending on locale) take advantage of simpler, more-efficient comparison functions? That might be nice, but I'd be wary of a geometric multiplication of text types. We already have TEXT and CITEXT; what if we had your NTEXT (normalized text) but I wanted it to also be case-insensitive? Whether you're doing INSERT/UPDATE, or importing a flat text file, if you canonicalize characters and substrings of identical meaning when trivial distinctions of encoding are irrelevant, you're better off later. User-invocable normalization functions by themselves don't make much sense. Well, they make sense because there's nothing else right now. It's an easy way to get some support in, and besides, it's mandated by the SQL standard. (If Postgres now supports binary- or mixed-binary-and-text flat files, perhaps for restore purposes, the same thing applies.) Don't follow this bit. 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] Unicode Normalization
David E. Wheeler wrote: On Sep 24, 2009, at 6:24 AM, p...@thetdh.com wrote: In a context using normalization, wouldn't you typically want to store a normalized-text type that could perhaps (depending on locale) take advantage of simpler, more-efficient comparison functions? That might be nice, but I'd be wary of a geometric multiplication of text types. We already have TEXT and CITEXT; what if we had your NTEXT (normalized text) but I wanted it to also be case-insensitive? Actually, I don't think it's necessarily a good idea at all. If a user inputs a perfectly valid piece of UTF8 text, we should be able to give it back to them exactly, whether or not it's in normalized form. The normalized forms are useful for certain comparison purposes, but they don't affect the validity of the text. CITEXT doesn't mangle what is stored, just how it's compared. 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] Unicode Normalization
On Sep 24, 2009, at 8:59 AM, Andrew Dunstan wrote: That might be nice, but I'd be wary of a geometric multiplication of text types. We already have TEXT and CITEXT; what if we had your NTEXT (normalized text) but I wanted it to also be case-insensitive? Actually, I don't think it's necessarily a good idea at all. If a user inputs a perfectly valid piece of UTF8 text, we should be able to give it back to them exactly, whether or not it's in normalized form. The normalized forms are useful for certain comparison purposes, but they don't affect the validity of the text. CITEXT doesn't mangle what is stored, just how it's compared. Right, I don't think there's a need for a normalized TEXT type. 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] [rfc] unicode escapes for extended strings
On Wed, 2009-09-23 at 22:46 +0300, Marko Kreen wrote: I looked at your code for U and saw that you allow standalone second half of the surrogate pair there, although you error out on first half. Was that deliberate? No. Perhaps pg_verifymbstr() should be made to check for such values, because even if we fix the escaping code, such data can still be inserted via plain utf8 or \x escapes? Good idea. This could also check for other invalid things like byte-order marks in UTF-8. -- 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] Join optimization for inheritance tables
If it could be done with a emphasissmall/ amount of throwaway code, maybe ... Well, that's a reasonable goal ... -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] libpq port number handling
On Thu, Sep 24, 2009 at 07:57:55PM +0100, Sam Mason wrote:
postg...@sussy:/root createuser -D -p ricky
I don't think you want to be passing -p here; it's saying to use
ricky as the port number, which fails (sounds like a bug if it doesn't
complain about this) giving a port number of zero
Hum, why is PG doing an (unchecked) atoi on the user specified port
rather than leaving it up to getaddrinfo to resolve the port? It would
seem to require changing UNIXSOCK_PATH to accept a string as the port
number, which is probably a bit much of a change.
The included doesn't feel very nice, but is probably more acceptable.
--
Sam http://samason.me.uk/
--- src/interfaces/libpq/fe-connect.c~ 2009-06-11 15:49:13.0 +0100
+++ src/interfaces/libpq/fe-connect.c 2009-09-24 20:48:53.0 +0100
@@ -817,7 +817,16 @@
/* Set up port number as a string */
if (conn-pgport != NULL conn-pgport[0] != '\0')
+ {
portnum = atoi(conn-pgport);
+ if (portnum 1 || portnum 65535)
+ {
+ appendPQExpBuffer(conn-errorMessage,
+ libpq_gettext(invalid port number \%s\ specified\n),
+ conn-pgport);
+ goto connect_errReturn;
+ }
+ }
else
portnum = DEF_PGPORT;
snprintf(portstr, sizeof(portstr), %d, portnum);
--
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] Unicode Normalization
In a context using normalization, wouldn't you typically want to store a normalized-text type that could perhaps (depending on locale) take advantage of simpler, more-efficient comparison functions? Whether you're doing INSERT/UPDATE, or importing a flat text file, if you canonicalize characters and substrings of identical meaning when trivial distinctions of encoding are irrelevant, you're better off later. User-invocable normalization functions by themselves don't make much sense. (If Postgres now supports binary- or mixed-binary-and-text flat files, perhaps for restore purposes, the same thing applies.) David Hudson
[HACKERS] plpgsql function is so slow
Hi,
I recently saw a complaint that a simple PL/PgSQL code is slower than PL/SQL.
I did some benchmark and confirmed it is. I coded the same function
(function2) in C just to compare with something. According to OP [1], the
PL/SQL seems to run more than 15x faster than PL/PgSQL code.
euler=# select function1();
function1
---
1
(1 row)
Time: 62107,607 ms
euler=# select function2();
function2
---
1
(1 row)
Time: 419,673 ms
The PL/PgSQL function is:
CREATE OR REPLACE FUNCTION function1()
RETURNS INTEGER AS
$BODY$
DECLARE
i INTEGER;
s INTEGER := 0;
BEGIN
FOR i IN 1 .. power(10, 8) LOOP
s := s + 1;
END LOOP;
RETURN s;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
The C function is:
#include postgres.h
#include math.h
#include fmgr.h
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(function2);
Datum function2(PG_FUNCTION_ARGS)
{
int i;
int s = 0;
for (i = 1; i = (int) pow(10, 8); i++)
s += 1;
PG_RETURN_INT32(s);
}
PL/PgSQL oprofile is:
samples %symbol name
2263 25.6024 AllocSetReset
1071 12.1168 ExecMakeFunctionResultNoSets
725 8.2023 AllocSetAlloc
664 7.5122 RevalidateCachedPlan
586 6.6297 ExecEvalParam
521 5.8943 AcquireExecutorLocks
463 5.2381 ResourceOwnerForgetPlanCacheRef
359 4.0615 AllocSetFreeIndex
329 3.7221 int4pl
262 2.9641 ExecEvalConst
248 2.8057 check_stack_depth
244 2.7605 MemoryContextReset
234 2.6474 list_head
143 1.6178 ReleaseCachedPlan
130 1.4708 MemoryContextAlloc
121 1.3689 pgstat_end_function_usage
111 1.2558 pgstat_init_function_usage
981.1087 list_head
941.0635 ResourceOwnerEnlargePlanCacheRefs
901.0182 ResourceOwnerRememberPlanCacheRef
440.4978 SPI_push
390.4412 SPI_pop
Any ideas?
[1]
http://listas.postgresql.org.br/pipermail/pgbr-geral/2009-September/017427.html
--
Euler Taveira de Oliveira
http://www.timbira.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] TODO item: Allow more complex user/database default GUC settings
Tom Lane escribió: Gurjeet Singh singh.gurj...@gmail.com writes: ON instead of second ALTER looks better, and IMHO DATABASE dbname should be optional too: ALTER ROLE rolename [ON DATABASE dbname] SET config TO value; IN, not ON. This creates a parser conflict with CREATE ROLE foo IN ROLE bar I think it can be solved by splitting OptRoleElem in a set of productions for ALTER and a superset of that for ALTER. I'll go try that. -- 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] plpgsql function is so slow
Euler Taveira de Oliveira eu...@timbira.com writes: I recently saw a complaint that a simple PL/PgSQL code is slower than PL/SQL. I did some benchmark and confirmed it is. I coded the same function (function2) in C just to compare with something. According to OP [1], the PL/SQL seems to run more than 15x faster than PL/PgSQL code. Does incrementing an integer in a tight loop represent the complainant's typical plpgsql workload? If so, maybe he should go use another PL. plpgsql is intended for work that involves a lot of database access, and so the overhead in operations like this isn't significant. We offer other PLs that make different tradeoffs. FWIW, the high showing of AllocSetReset in your profile suggests to me that you're timing an assert-enabled build, which wouldn't exactly be a fair comparison to an Oracle production build anyhow. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch for information_schema performance
Hi,
the attached patch addresses the performance issues of the
authorization related views from information_schema (BUG #4596). It
implements what Tom suggests in
http://archives.postgresql.org/pgsql-bugs/2008-12/msg00144.php
In the cases that I have tested both the new and the old view return
the same data but I'd appreciate more tests. The patch currently does
not remove the original views but renames them from xyz to old_xyz, so
you can run your own tests of the new view definition vs. the old one.
I also include a psql file that might help you testing.
Regards,
Joachim
diff -cr cvs/src/backend/catalog/information_schema.sql cvs.build/src/backend/catalog/information_schema.sql
*** cvs/src/backend/catalog/information_schema.sql 2009-09-06 08:59:03.0 +0200
--- cvs.build/src/backend/catalog/information_schema.sql 2009-09-25 02:25:14.0 +0200
***
*** 480,485
--- 480,552
CREATE VIEW column_privileges AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+CAST(g_grantee.rolname AS sql_identifier) AS grantee,
+CAST(current_database() AS sql_identifier) AS table_catalog,
+CAST(nc.nspname AS sql_identifier) AS table_schema,
+CAST(relname AS sql_identifier) AS table_name,
+CAST(attname AS sql_identifier) AS column_name,
+CAST(prtxt.ptext AS character_data) AS privilege_type,
+CAST(grantable AS yes_or_no) AS is_grantable
+ FROM (
+ SELECT pr_c.grantor,
+ pr_c.grantee,
+ attname,
+ relname,
+ relnamespace,
+ pr_c.type,
+ CASE WHEN
+ pr_c.is_grantable OR pg_has_role(pr_c.grantee, pr_c.relowner, 'USAGE')
+ THEN 'YES' ELSE 'NO' END as grantable
+ FROM
+ (SELECT oid, relname, relnamespace, relowner, (acls).*
+ FROM (SELECT oid, relname, relnamespace, relowner, aclexplode(relacl) as acls
+ FROM pg_class
+ WHERE relkind IN ('r', 'v')) x
+ ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, type, is_grantable),
+ pg_attribute a
+ WHERE a.attrelid = pr_c.oid
+ AND a.attnum 0
+ AND NOT a.attisdropped
+ UNION
+ SELECT pr_a.grantor,
+ pr_a.grantee,
+ attname,
+ relname,
+ relnamespace,
+ pr_a.type,
+ CASE WHEN
+ pr_a.is_grantable OR pg_has_role(pr_a.grantee, c.relowner, 'USAGE')
+ THEN 'YES' ELSE 'NO' END as grantable
+ FROM
+ (SELECT attrelid, attname, (acls).*
+ FROM (SELECT attrelid, attname, aclexplode(attacl) AS acls
+ FROM pg_attribute
+ WHERE attnum 0
+AND NOT attisdropped) x
+ ) pr_a (attrelid, attname, grantor, grantee, type, is_grantable),
+ pg_class c
+ WHERE pr_a.attrelid = c.oid
+ AND relkind IN ('r','v')
+ ) x,
+ (SELECT oid, rolname FROM pg_authid
+UNION ALL
+SELECT 0::oid, 'PUBLIC'
+ ) AS g_grantee (oid, rolname),
+ pg_authid u_grantor,
+ (VALUES ('a', 'INSERT'),
+ ('r', 'SELECT'),
+ ('w', 'UPDATE'),
+ ('x', 'REFERENCES')) AS prtxt (pchr, ptext),
+ pg_namespace nc
+ WHERE nc.oid = relnamespace
+ AND x.grantee = g_grantee.oid
+ AND x.grantor = u_grantor.oid
+ AND prtxt.pchr = type;
+
+ GRANT SELECT ON column_privileges TO PUBLIC;
+
+ CREATE VIEW old_column_privileges AS
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS table_catalog,
CAST(nc.nspname AS sql_identifier) AS table_schema,
***
*** 523,530
OR pg_has_role(grantee.oid, 'USAGE')
OR grantee.rolname = 'PUBLIC');
- GRANT SELECT ON column_privileges TO PUBLIC;
-
/*
* 5.20
--- 590,595
***
*** 1124,1129
--- 1189,1203
*/
CREATE VIEW role_column_grants AS
+ SELECT column_privileges.*
+ FROM column_privileges
+ JOIN enabled_roles er1 ON grantor = er1.role_name
+ JOIN enabled_roles er2 ON grantee = er2.role_name;
+
+ GRANT SELECT ON role_column_grants TO PUBLIC;
+
+
+ CREATE VIEW old_role_column_grants AS
SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
CAST(g_grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS table_catalog,
***
*** 1163,1177
AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
OR g_grantee.rolname IN (SELECT role_name
Re: [HACKERS] [PATCH] Largeobject access controls
The attached patch is revised from the previous revision at the following points: - The largeobject_compat_acl is renamed to largeobject_check_acl. Its default is on, and turning it off means the largeobject stuff performs in compatible mode for the v8.4.x or prior releases. - Notification messages were eliminated at the compatible mode. It always allows to bypass ACL checks without any warnings. Thanks, $ diffstat sepgsql-02-blob-8.5devel-r2328.patch.gz doc/src/sgml/config.sgml | 28 ++ doc/src/sgml/ref/allfiles.sgml|1 doc/src/sgml/ref/alter_large_object.sgml | 75 + doc/src/sgml/ref/grant.sgml |8 doc/src/sgml/ref/revoke.sgml |6 doc/src/sgml/reference.sgml |1 src/backend/catalog/Makefile |6 src/backend/catalog/aclchk.c | 249 ++ src/backend/catalog/dependency.c | 14 + src/backend/catalog/pg_largeobject.c | 357 +++!! src/backend/catalog/pg_shdepend.c |8 src/backend/commands/alter.c |5 src/backend/commands/comment.c| 11 src/backend/commands/tablecmds.c |1 src/backend/libpq/be-fsstubs.c| 49 +-- src/backend/parser/gram.y | 20 + src/backend/storage/large_object/inv_api.c| 115 ++---!!! src/backend/tcop/utility.c|3 src/backend/utils/adt/acl.c |5 src/backend/utils/cache/syscache.c| 13 src/backend/utils/misc/guc.c | 10 src/backend/utils/misc/postgresql.conf.sample |1 src/bin/psql/large_obj.c | 10 src/include/catalog/dependency.h |1 src/include/catalog/indexing.h|3 src/include/catalog/pg_largeobject_metadata.h | 67 src/include/nodes/parsenodes.h|1 src/include/utils/acl.h |6 src/include/utils/syscache.h |1 src/test/regress/expected/privileges.out | 206 +++ src/test/regress/expected/sanity_check.out|3 src/test/regress/sql/privileges.sql | 85 ++ 32 files changed, 976 insertions(+), 77 deletions(-), 316 modifications(!) KaiGai Kohei wrote: Jaime Casanova wrote: 2009/9/23 KaiGai Kohei kai...@ak.jp.nec.com: Jaime, KaiGai Kohei wrote: | ALTER LARGE OBJECT is working, but now that we can change the owner of | a LO we should be able to see who the actual owner is... i mean we | should add an owner column in \dl for psql (maybe \dl+) and maybe an | lo_owner() function. | | I would like to buy your idea at the revised patch. Now we don't have xxx_owner() function for other database objects, such as tables, procedures and so on. good point, but we have has_xx_privileges() family of functions but i think we can add them later if needed... Yes, the has_xx_privileges() family should be added later or soon. Anyway, what I wanted to say is we have no special functions to show owner of the database objects. Jaime Casanova wrote: Do you think the largeobject_compat_acl is a meaningful name, instead? maybe something like largeobject_security_controls? It is important to contain a term of compat which means compatible, because this GUC does not disables all the security checks. The v8.4.x checks superuser privilege on using lo_import()/lo_export(). It is also checked in this patch, even if the GUC is turned on. The purpose of the GUC is to provide compatible behavior, not to provide a stuff to turn on/off all the security features in largeobjects. that's why the section in the postgresql.conf is called VERSION/PLATFORM COMPATIBILITY and the subsection Previous PostgreSQL Versions we have other compatibilty GUC and no ones of those has the term compat in it... Indeed, I put the largeobject_compat_acl in the compatibility section, but no other GUCs have compat prefix/suffix. It seems to me fair enough. So, I still prefer the largeobject_compat_acl. maybe enhanced_largeobjects_checks or enhanced_lo_checks or make the GUC an enum and name it largeobject_control_checks with posible values basic and enhanced But, isn't the enhanced tumid expression? It just applies native database privilege mechanism on largeobjects, as if it does on other objects. An other alternative is largeobject_check_acl. What's your feeling? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com sepgsql-02-blob-8.5devel-r2328.patch.gz Description: application/gzip -- 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_hba.conf: samehost and samenet [REVIEW]
Magnus Hagander wrote:
On Mon, Sep 21, 2009 at 20:12, Stef Walter stef-l...@memberwebs.com wrote:
This patch does not build on Windows, the error is:
ip.obj : error LNK2019: unresolved external symbol __imp__wsaio...@36
referenced
in function _pg_foreach_ifaddr
ip.obj : error LNK2019: unresolved external symbol __imp__wsasock...@24
referenc
ed in function _pg_foreach_ifaddr
.\Release\libpq\libpq.dll : fatal error LNK1120: 2 unresolved externals
I don't have time to investigate this further right now, so if
somebody else want to dig into why that is happening that would be
helpful :)
Seems there are two windows build systems. Once I discovered the MSVC
one, and got it working, I added the required ws2 library (already used
by other components of postgresql).
Attached patch contains a fix.
Cheers,
Stef
diff --git a/configure.in b/configure.in
index e545a1f..b77ce2b 100644
*** a/configure.in
--- b/configure.in
*** AC_SUBST(OSSP_UUID_LIBS)
*** 969,975
##
dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES
! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h])
# At least on IRIX, cpp test for netinet/tcp.h will fail unless
# netinet/in.h is included first.
--- 969,975
##
dnl sys/socket.h is required by AC_FUNC_ACCEPT_ARGTYPES
! AC_CHECK_HEADERS([crypt.h dld.h fp_class.h getopt.h ieeefp.h langinfo.h poll.h pwd.h sys/ipc.h sys/poll.h sys/pstat.h sys/resource.h sys/select.h sys/sem.h sys/socket.h sys/shm.h sys/tas.h sys/time.h sys/un.h termios.h ucred.h utime.h wchar.h wctype.h kernel/OS.h kernel/image.h SupportDefs.h ifaddrs.h])
# At least on IRIX, cpp test for netinet/tcp.h will fail unless
# netinet/in.h is included first.
*** PGAC_VAR_INT_TIMEZONE
*** 1148,1154
AC_FUNC_ACCEPT_ARGTYPES
PGAC_FUNC_GETTIMEOFDAY_1ARG
! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs])
# posix_fadvise() is a no-op on Solaris, so don't incur function overhead
# by calling it, 2009-04-02
--- 1148,1154
AC_FUNC_ACCEPT_ARGTYPES
PGAC_FUNC_GETTIMEOFDAY_1ARG
! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs getifaddrs])
# posix_fadvise() is a no-op on Solaris, so don't incur function overhead
# by calling it, 2009-04-02
diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml
index ad4d084..e5152f4 100644
*** a/doc/src/sgml/client-auth.sgml
--- b/doc/src/sgml/client-auth.sgml
*** hostnossl replaceabledatabase/replac
*** 244,249
--- 244,255
support for IPv6 addresses.
/para
+ paraInstead of a replaceableCIDR-address/replaceable, you can specify
+literalsamehost/literal to match any of the server's own IP addresses,
+or literalsamenet/literal to match any address in a subnet that the
+server belongs to.
+ /para
+
para
This field only applies to literalhost/literal,
literalhostssl/literal, and literalhostnossl/ records.
diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c
index e6f7db2..702971a 100644
*** a/src/backend/libpq/hba.c
--- b/src/backend/libpq/hba.c
*** check_db(const char *dbname, const char
*** 512,517
--- 512,608
return false;
}
+ /*
+ * Check to see if a connecting IP matches the address and netmask.
+ */
+ static bool
+ check_ip(SockAddr *raddr, struct sockaddr *addr, struct sockaddr *mask)
+ {
+ if (raddr-addr.ss_family == addr-sa_family)
+ {
+ /* Same address family */
+ if (!pg_range_sockaddr(raddr-addr, (struct sockaddr_storage*)addr,
+ (struct sockaddr_storage*)mask))
+ return false;
+ }
+ #ifdef HAVE_IPV6
+ else if (addr-sa_family == AF_INET
+ raddr-addr.ss_family == AF_INET6)
+ {
+ /*
+ * Wrong address family. We allow only one case: if the file
+ * has IPv4 and the port is IPv6, promote the file address to
+ * IPv6 and try to match that way.
+ */
+ struct sockaddr_storage addrcopy,
+ maskcopy;
+
+ memcpy(addrcopy, addr, sizeof(addrcopy));
+ memcpy(maskcopy, mask, sizeof(maskcopy));
+ pg_promote_v4_to_v6_addr(addrcopy);
+ pg_promote_v4_to_v6_mask(maskcopy);
+
+ if (!pg_range_sockaddr(raddr-addr, addrcopy, maskcopy))
+ return false;
+ }
+ #endif /* HAVE_IPV6 */
+ else
+ {
+ /* Wrong address family, no IPV6 */
+ return false;
+ }
+
+ return true;
+ }
+
+ typedef struct CheckNetwork {
+ NetMethod method;
+ SockAddr
Re: [HACKERS] libpq port number handling
Sam Mason s...@samason.me.uk writes: Hum, why is PG doing an (unchecked) atoi on the user specified port rather than leaving it up to getaddrinfo to resolve the port? It would seem to require changing UNIXSOCK_PATH to accept a string as the port number, which is probably a bit much of a change. The included doesn't feel very nice, but is probably more acceptable. I had been thinking about applying strstr to insist that the string contain only digits (and maybe spaces), but the range check you suggest is probably more useful. Anyone have objections? (BTW, are port numbers still limited to 16 bits in IPv6? If not then this won't do.) 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] libpq port number handling
On Thu, Sep 24, 2009 at 8:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, are port numbers still limited to 16 bits in IPv6? Yes. ...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] libpq port number handling
Sam Mason s...@samason.me.uk writes: + if (portnum 1 || portnum 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. This is guaranteed safe on all Unix systems I know of, because privileged ports can only be listened to by root-owned processes and we know the postmaster won't be one. I am not sure whether it would be possible to start the postmaster on a low-numbered port on Windows though. Anyone know? Even if it's possible, do we want to allow it? 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] libpq port number handling
Tom Lane wrote: Sam Mason s...@samason.me.uk writes: +if (portnum 1 || portnum 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. This is guaranteed safe on all Unix systems I know of, because privileged ports can only be listened to by root-owned processes and we know the postmaster won't be one. This is just an aside. The recent Linux system allows to assign a part of root privileges (called as capabilities) on a certain process. Example) # setcap cap_net_bind_service=ep /usr/local/pgsql/bin/postgres -- it allows anyone to launch postmaster with cap_net_bind_service capability. $ pg_ctl -o -i -p 100 start $ psql postgres -p 100 psql (8.5devel) Type help for help. postgres=# Even if it's possible, do we want to allow it? I cannot find any merits. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq port number handling
On Thu, 24 Sep 2009, Tom Lane wrote: Sam Mason s...@samason.me.uk writes: + if (portnum 1 || portnum 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. Restricting the target port seems like a bad idea. What about a firewall (or ssh tunnel) that did port forwarding. What PG binds to and what a client connects to may not be the same thing. 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] libpq port number handling
On Thu, Sep 24, 2009 at 8:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Sam Mason s...@samason.me.uk writes: + if (portnum 1 || portnum 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. This is guaranteed safe on all Unix systems I know of, because privileged ports can only be listened to by root-owned processes and we know the postmaster won't be one. I am not sure whether it would be possible to start the postmaster on a low-numbered port on Windows though. Anyone know? Even if it's possible, do we want to allow it? I don't think we get much benefit out of artificially limiting libpq in this way. In 99.99% of cases it won't matter, and in the other 0.01% it will be a needless annoyance. I think we should restrict ourselves to checking what is legal, not what we think is a good idea. ...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] syslog_line_prefix
On Tue, Sep 15, 2009 at 2:18 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
Magnus Hagander wrote:
I'm not sure I like this as a GUC. We're going to end up with a lot of
different GUCs, and everytime we add a new log destination (admittedly
not often, of course), that increases even further. And GUCs really
don't provide the level of flexibility you'd really like to have. I've
been thinking (long-term) in the direction of a separate config file,
since that could contain an arbitrary number of lines, with rules on
them (somewhat like pg_hba.conf maybe).
I tend to agree with this idea, but I'm not sure about rejecting the
current patch because of it.
I'm picking up this patch to review for this CommitFest. I agree that
the idea of this patch is good. It's pretty silly for us to give
people advice that they should not log time stamps and pids to syslog,
but then provide them no way of actually implementing that behavior
when logging to multiple destinations.
On the other hand, I don't think this is the right way to do it. The
patch proposes the following mapping of logging destinations to GUCs:
stderr - log_line_prefix (same as now)
csvlog - not applicable (same as now)
syslog - syslog_line_prefix
eventlog - syslog_line_prefix
That's not exactly mnemonic; I think we'd want
{stderr,syslog,eventlog}_log_line_prefix if anything. But that seems
like too many GUCs already - for anyone logging to a single
destination (which I would think by far the most common case), it's
just extra work. So I'm inclined to say that we should reject this
patch for now and see what other ideas come down the pipe.
...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] COPY enhancements
On Fri, Sep 18, 2009 at 12:14 AM, Emmanuel Cecchet m...@asterdata.com wrote: Here is a new version of error logging and autopartitioning in COPY based on the latest COPY patch that provides the new syntax for copy options (this patch also includes the COPY option patch). New features compared to previous version: - removed the GUC variables for error logging and use copy options instead (renamed options as suggested by Josh) - added documentation and examples (see copy.sgml) - partitioning also activated by copy option rather than GUC variable (renamed as well) - added a new ERROR_LOGGING_MAX_ERRORS option that allows to abort the COPY operation if a certain number of bad rows has been encountered. - updated unit tests I also tried to update the wiki pages but it's late and the doc is probably better for now. This addresses most of the comments so far except for the format of the error table (lack of natural key) and a possible pg_error_table as a default name. Emmanuel Emmanuel Cecchet wrote: Hi all, Finally the error logging and autopartitioning patches for COPY that I presented at PGCon are here! Error logging is described here: http://wiki.postgresql.org/wiki/Error_logging_in_COPY Autopartitioning is described here: http://wiki.postgresql.org/wiki/Auto-partitioning_in_COPY The attached patch contains both contribs as well as unit tests. I will submit shortly the new patch at commitfest.postgresql.org. Thanks in advance for your feedback, Emmanuel This no longer applies. ...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] COPY enhancements
Yes, I have to update the patch following what Tom already integrated of the COPY patch. I will get a new version posted as soon as I can. Emmanuel Robert Haas wrote: On Fri, Sep 18, 2009 at 12:14 AM, Emmanuel Cecchet m...@asterdata.com wrote: Here is a new version of error logging and autopartitioning in COPY based on the latest COPY patch that provides the new syntax for copy options (this patch also includes the COPY option patch). New features compared to previous version: - removed the GUC variables for error logging and use copy options instead (renamed options as suggested by Josh) - added documentation and examples (see copy.sgml) - partitioning also activated by copy option rather than GUC variable (renamed as well) - added a new ERROR_LOGGING_MAX_ERRORS option that allows to abort the COPY operation if a certain number of bad rows has been encountered. - updated unit tests I also tried to update the wiki pages but it's late and the doc is probably better for now. This addresses most of the comments so far except for the format of the error table (lack of natural key) and a possible pg_error_table as a default name. Emmanuel Emmanuel Cecchet wrote: Hi all, Finally the error logging and autopartitioning patches for COPY that I presented at PGCon are here! Error logging is described here: http://wiki.postgresql.org/wiki/Error_logging_in_COPY Autopartitioning is described here: http://wiki.postgresql.org/wiki/Auto-partitioning_in_COPY The attached patch contains both contribs as well as unit tests. I will submit shortly the new patch at commitfest.postgresql.org. Thanks in advance for your feedback, Emmanuel This no longer applies. ...Robert -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.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] WIP - syslogger infrastructure changes
On Mon, Sep 14, 2009 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: First, the patch removes the logging_collector parameter and basically assumes that logging_collector is always on. I don't find that to be a good idea, and you certainly have not made a case why we should change it. I can't see any reason why pushing functionality out of backends and downstream to the syslogger process is an improvement. What it's more likely to do is create a processing bottleneck and a single point of failure. Hmm. I think the justification was supposed to be this part here: $ With that, it's no longer necessary to restart your server just to $ reconfigure the logging, and it also takes away a confusing parameter $ (really log_destination=stderr, logging_collector=on is *not* a logical $ way to say log to file). Instead, it adds a log_destination of file that $ is the standard log to file. Do we have any positive or negative experience with logging_collector as a performance bottleneck? Are there people running with logging_collector=off to avert disaster? ... Given that the syslogger is now always started, those that actually *want* logging to stderr (which I claim is a low number of people, but that's a different story) will have it go through the syslogger and then to the stderr of syslogger. That design doesn't work because there is then *no* way to recover from a syslogger crash. You no longer have access to the original stderr file once the postmaster has redirected stderr to syslogger. We can live with that so long as syslogger's stderr output isn't very interesting, but not if it's going to be the main log channel. I haven't read the patch yet, but this seems like it might be surmountable by having the postmaster keep both file descriptors around, and letting the children make appropriate calls to dup2() and close() just after forking. ...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] plpgsql function is so slow
Tom Lane escreveu: FWIW, the high showing of AllocSetReset in your profile suggests to me that you're timing an assert-enabled build, which wouldn't exactly be a fair comparison to an Oracle production build anyhow. Ops... forgot to remove it from other test. It seems much better but far from the ideal. :( I've never taken a look at the pl/pgsql code but it could be nice if there would be two path codes: access-data and non-access-data paths. I have no idea if it will be possible (is path type too complex to detect?) but it will certainly improve the non-access-data functions. euler=# select function1(); -- PL/PgSQL function1 --- 1 (1 row) Time: 34455,263 ms euler=# select function3(); -- PL/Perl function3 --- 1 (1 row) Time: 24986,016 ms pl/pgsql oprofile is: samples %symbol name 620 14.4961 ExecMakeFunctionResultNoSets 537 12. AllocSetAlloc 484 11.3163 AllocSetReset 323 7.5520 RevalidateCachedPlan 292 6.8272 ExecEvalParam 222 5.1906 pgstat_init_function_usage 218 5.0970 int4pl 199 4.6528 ResourceOwnerForgetPlanCacheRef 196 4.5827 AcquireExecutorLocks 175 4.0917 ResourceOwnerRememberPlanCacheRef 166 3.8812 ReleaseCachedPlan 155 3.6240 pgstat_end_function_usage 143 3.3435 check_stack_depth 136 3.1798 ExecEvalConst 123 2.8758 ResourceOwnerEnlargePlanCacheRefs 100 2.3381 MemoryContextReset 992.3147 MemoryContextAlloc 481.1223 SPI_pop 410.9586 SPI_push -- Euler Taveira de Oliveira http://www.timbira.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] latest hstore patch
On Sep 23, 2009, at 5:27 PM, Andrew Gierth wrote: I intentionally avoided hstore_to_array because it would be unclear which one it meant (the 1-d or 2-d result). Thanks Andrew. Given these replies, unless anyone else wants to weigh in on the array conversion operator and function names, this patch is ready for committer review (along with my tiny doc patch). I'll update the commitfest site to that effect. Thanks, 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] plpgsql function is so slow
2009/9/24 Euler Taveira de Oliveira eu...@timbira.com:
Hi,
I recently saw a complaint that a simple PL/PgSQL code is slower than PL/SQL.
I did some benchmark and confirmed it is. I coded the same function
(function2) in C just to compare with something. According to OP [1], the
PL/SQL seems to run more than 15x faster than PL/PgSQL code.
sure - PL/SQL is translated to native code, so PL/pgSQL is only
interpret. What more - PL/SQL or C use native integer arithmetic, but
PL/pgSQL use PostgreSQL integer arithmetic.
so if you have to use fast code, use C, plperl, but not use PL/pgSQL -
it is just glue for SQL statements.
regards
Pavel Stehule
euler=# select function1();
function1
---
1
(1 row)
Time: 62107,607 ms
euler=# select function2();
function2
---
1
(1 row)
Time: 419,673 ms
The PL/PgSQL function is:
CREATE OR REPLACE FUNCTION function1()
RETURNS INTEGER AS
$BODY$
DECLARE
i INTEGER;
s INTEGER := 0;
BEGIN
FOR i IN 1 .. power(10, 8) LOOP
s := s + 1;
END LOOP;
RETURN s;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
The C function is:
#include postgres.h
#include math.h
#include fmgr.h
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(function2);
Datum function2(PG_FUNCTION_ARGS)
{
int i;
int s = 0;
for (i = 1; i = (int) pow(10, 8); i++)
s += 1;
PG_RETURN_INT32(s);
}
PL/PgSQL oprofile is:
samples % symbol name
2263 25.6024 AllocSetReset
1071 12.1168 ExecMakeFunctionResultNoSets
725 8.2023 AllocSetAlloc
664 7.5122 RevalidateCachedPlan
586 6.6297 ExecEvalParam
521 5.8943 AcquireExecutorLocks
463 5.2381 ResourceOwnerForgetPlanCacheRef
359 4.0615 AllocSetFreeIndex
329 3.7221 int4pl
262 2.9641 ExecEvalConst
248 2.8057 check_stack_depth
244 2.7605 MemoryContextReset
234 2.6474 list_head
143 1.6178 ReleaseCachedPlan
130 1.4708 MemoryContextAlloc
121 1.3689 pgstat_end_function_usage
111 1.2558 pgstat_init_function_usage
98 1.1087 list_head
94 1.0635 ResourceOwnerEnlargePlanCacheRefs
90 1.0182 ResourceOwnerRememberPlanCacheRef
44 0.4978 SPI_push
39 0.4412 SPI_pop
Any ideas?
[1]
http://listas.postgresql.org.br/pipermail/pgbr-geral/2009-September/017427.html
--
Euler Taveira de Oliveira
http://www.timbira.com/
--
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] plpgsql function is so slow
Euler == Euler Taveira de Oliveira eu...@timbira.com writes: Euler Ops... forgot to remove it from other test. It seems much Euler better but far from the ideal. :( I've never taken a look at Euler the pl/pgsql code but it could be nice if there would be two Euler path codes: access-data and non-access-data paths. I have no Euler idea if it will be possible (is path type too complex to Euler detect?) but it will certainly improve the non-access-data Euler functions. Like Tom said, this benchmark is silly. Some comparisons (note that in all these cases I've replaced the power(10,8) with a constant, because you weren't comparing like with like there): plpgsql 13.3 sec tcl85 29.9 sec perl5.8 7.7 sec python2.6 11.5 sec C0.242 sec What this suggests to me is that plpgsql isn't so far off the norm for interpreted scripting languages; sure it's slower than perl, but then most things are; comparing it with C code is just silly. There is, though, one genuine case that's come up a few times in IRC regarding slowness of procedural code in pg, and that's any time someone tries to implement some array-based algorithm in plpgsql. The fact that a[i] is O(i) not O(1) (unless the array type is fixed length) comes as a nasty shock since iterating over an array becomes O(n^2). This is obviously a consequence of the array storage format; is there any potential for changing that to some format which has, say, an array of element offsets at the start, rather than relying on stepping over length fields? -- Andrew (irc:RhodiumToad) -- 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] [rfc] unicode escapes for extended strings
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, Sep 24, 2009 at 09:42:32PM +0300, Peter Eisentraut wrote: On Wed, 2009-09-23 at 22:46 +0300, Marko Kreen wrote: [...] Good idea. This could also check for other invalid things like byte-order marks in UTF-8. But watch out. Microsoft apps do like to insert a BOM at the beginning of the text. Not that I think it's a good idea, but the Unicode folks seem to think its OK [1] :-( http://unicode.org/faq/utf_bom.html#bom5 Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFKvFtCBcgs9XrR2kYRAsHXAJ9lpaqZ2IFKGwZd+H3Ej6H+m44vpgCeLe7n vc+ciE1N5AqOre3DmvwKaNI= =UTBQ -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
