Re: [HACKERS] psycopg2 license changed
Federico Di Gregorio wrote: Even if tests and examples code aren't almost never distributed except in the psycopg2 source package? A couple of other people contributed to the tests: if you really feel like it is so important I'll contact them and ask their permission to use the LGPL3 + exception (the contribution was without the exception) or remove the code (we won't lose much.) I understand that from a technical perspective these are all different bits. But the sort of people who get stressed about licenses might not, and that's why it's always better to have a simple, standard, unified license that covers the entire chunk of software you're packaging. If the examples show up in the source package, that means the source package has two licenses instead of one, and that's a bad thing. It's not a huge issue, I'm just afraid that if you don't get this nailed down now there's just going to another round of this tedious license investigation in the future one day. I'd think it's better for you and everyone else in the long run to just completely unify the license. And if takes another release for the examples to get that license change, I think that's OK. I wouldn't hold up the big work here--getting your next release out with the big LGPL3 switch for the main code--over this bit of trivia. I just think it's a potential future headache you should try to remove when you can. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
Jakub Ouhrabka wrote: I've found similar reports but with older versions of postgres: http://old.nabble.com/100--of-CPU-utilization-postgres-process-tt27302021.html Those all looked like a FreeBSD issue, doubt it's related to yours. The pgstat.stat is ~20MB. There are 650 databases, 140GB total. default_statistics_target = 1000 The system is running Proxmox linux distribution. PostgreSQL is in OpenVZ container. With this many databases and this high of a statistics target, running in a VM, suspecting autovacuum seems reasonable. You might want to try setting log_autovacuum_min_duration=0 in the postgresql.conf, restarting or signalling (pg_ctl reload) the server, and watching just what it's doing. You might need to reduce how aggressively that runs, or limit the higher target to only the tables that need it, to get this under control. You're really pushing what you can do in a VM with this many databases of this size. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Problem with 8.4 stats collector high load
Hi, sorry for repost but previous message didn't get through. So I'm trying another list and sending without attachment which I can send privately upon request (strace output mentioned below). We've migrated some of our databases to 8.4 cluster (from 8.2 and older versions). These databases are archive databases, so there is no user activity - no connected users. But the stats collector generates load - 20-40% of modern 2.8GHz core all the time. I've found similar reports but with older versions of postgres: http://old.nabble.com/100--of-CPU-utilization-postgres-process-tt27302021.html Any clues what does it cause and how to investigate it? I'm attaching my findings below - I suspect autovacuum but don't know where the problem is exactly. Thanks, Kuba Detailed report: PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit The pgstat.stat is ~20MB. There are 650 databases, 140GB total. Attached is strace output of stats collector running for 5s. Restarting postgresql and/or pg_stat_reset() doesn't help. When I do select * from pg_stat_activity, there is every 3rd try row like this: template1# select * from pg_stat_activity; datname - some database in the cluster procpid - changing number usename - postgres current_query - xact_start - null query_start - null backend_start - few milliseconds ago ps shows autovacuum worker: 21323 0:04 /opt/pg/bin/postmaster -D /var/lib/postgresql/8.4/data 21325 0:00 postgres: writer process 21326 0:00 postgres: wal writer process 21327 3:01 postgres: autovacuum launcher process 21328 22:30 postgres: stats collector process 21355 0:00 postgres: autovacuum worker process "name of db" There are only minor modifications to postgresql.conf: shared_buffers = 512MB temp_buffers = 2MB work_mem = 32MB maintenance_work_mem = 128MB max_stack_depth = 1MB fsync = off wal_buffers = 1MB checkpoint_segments = 100 effective_cache_size = 2GB default_statistics_target = 1000 The system is running Proxmox linux distribution. PostgreSQL is in OpenVZ container. The kernel is 2.6.18-2-pve. PostgreSQL data files are on local xfs filesystem. We don't have much experience with this setup yet. But we have a smaller cluster with 8.4 running without this problem on other machine. And we have a big 8.2 cluster on this setup without this problem. -- 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] psql tab-completion for new syntax
Here is a patch to support new syntax in psql tab completion
and fix bugs to complete after an open parenthesis.
Supported additonal syntax are:
- ALTER TABLE/INDEX/TABLESPACE SET/RESET with options
- ALTER TABLE ALTER COLUMN SET/RESET with options
- ALTER TABLE ALTER COLUMN SET STORAGE
- CREATE TRIGGER with events
- CREATE INDEX CONCURRENTLY
- CREATE INDEX ON (without name)
- CREATE INDEX ... USING with pg_am.amname instead of hard-corded names.
Fixes bugs are:
Bug 1: Double parenthesis
=# INSERT INTO pgbench_history VALUES (
=# INSERT INTO pgbench_history VALUES (( <= wrong
Bug 2: We cannot complete words if no whitespaces around a parenthesis.
=# CREATE INDEX idx ON pgbench_history(
^ no whitespace here
Bug 3: should be completed with "(" before columns.
=# CREATE INDEX foo ON pgbench_accounts USING BTREE
abalance aid bid filler<= wrong, should be "("
I adjusted previous_word() to split words not only with spaces but also
with non-alphabets, and removed a hack with find_open_parenthesis().
Comments?
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
psql-tab-completion_20100216.patch
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] LISTEN/NOTIFY and notification timing guarantees
I wrote: > ... > 3. It is possible for a backend's own self-notifies to not be delivered > immediately after commit, if they are queued behind some other > uncommitted transaction's messages. That wasn't possible before either. > ... We could fix > #3 by re-instituting the special code path that previously existed for > self-notifies, ie send them to the client directly from AtCommit_Notify > and ignore self-notifies coming back from the queue. This would mean > that a backend might see its own self-notifies in a different order > relative to other backends' messages than other backends do --- but that > was the case in the old coding as well. I think preserving the > property that self-notifies are delivered immediately upon commit might > be more important than that. I modified the patch to do that, but after awhile realized that there are more worms in this can than I'd thought. What I had done was to add the NotifyMyFrontEnd() calls to the post-commit cleanup function for async.c. However, that is a horribly bad place to put it because of the non-negligible probability of a failure. An encoding conversion failure, for example, becomes a "PANIC: cannot abort transaction NNN, it was already committed". The reason we have not seen any such behavior in the field is that in the historical coding, self-notifies are actually sent *pre commit*. So if they do happen to fail you get a transaction rollback and no backend crash. Of course, if some notifies went out before we got to the one that failed, the app might have taken action based on a notify for some event that now didn't happen; so that's not exactly ideal either. So right now I'm not sure what to do. We could adopt the historical policy of sending self-notifies pre-commit, but that doesn't seem tremendously appetizing from the standpoint of transactional integrity. Or we could do it the way Joachim's submitted patch does, but I'm quite sure somebody will complain about the delay involved. Another possibility is to force a ProcessIncomingNotifies scan to occur before we reach ReadyForQuery if we sent any notifies in the just-finished transaction --- but that won't help if there are uncommitted messages in front of ours. So it would only really improve matters if we forced queuing order to match commit order, as I was speculating about earlier. Thoughts? 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] Re: [BUGS] BUG #4566: pg_stop_backup() reports incorrect STOP WAL LOCATION
On Fri, Feb 5, 2010 at 9:08 AM, Takahiro Itagaki
wrote:
>
> Fujii Masao wrote:
>
>> On Fri, Dec 5, 2008 at 11:41 PM, Randy Isbell wrote:
>> > An inconsistency exists between the segment name reported by
>> > pg_stop_backup() and the actual WAL file name.
>> >
>> > START WAL LOCATION: 10/FE1E2BAC (file 0002001000FE)
>> > STOP WAL LOCATION: 10/FF00 (file 0002001000FF)
>
>> But it was rejected because its change might break the existing app.
>
> It might break existing applications if it returns "FE" instead of "FF",
> but never-used filename surprises users. (IMO, the existing apps probably
> crash if "FF" returned, i.e, 1/256 of the time.)
>
> Should it return the *next* reasonable log filename instead of "FF"?
> For example, 00020020 for the above case.
Here is the patch that avoids a nonexistent file name, according to
Itagaki-san's suggestion. If we are crossing a logid boundary, the
next reasonable file name is used instead of a nonexistent one.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***
*** 8057,8063 pg_stop_backup(PG_FUNCTION_ARGS)
*/
RequestXLogSwitch();
! XLByteToSeg(stoppoint, _logId, _logSeg);
XLogFileName(stopxlogfilename, ThisTimeLineID, _logId, _logSeg);
/* Use the log timezone here, not the session timezone */
--- 8057,8078
*/
RequestXLogSwitch();
! if (stoppoint.xrecoff >= XLogSegSize)
! {
! XLogRecPtr recptr = stoppoint;
!
! /*
! * Since xlog segment file name is calculated by using XLByteToSeg,
! * it might indicate a nonexistent file (i.e., which ends in "FF")
! * when we are crossing a logid boundary. In this case, we use the
! * next reasonable file name instead of nonexistent one.
! */
! recptr.xlogid += 1;
! recptr.xrecoff = XLOG_BLCKSZ;
! XLByteToSeg(recptr, _logId, _logSeg);
! }
! else
! XLByteToSeg(stoppoint, _logId, _logSeg);
XLogFileName(stopxlogfilename, ThisTimeLineID, _logId, _logSeg);
/* Use the log timezone here, not the session timezone */
--
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] CommitFest Status Summary - 2010-02-14
(2010/02/14 13:34), Robert Haas wrote: > * Fix large object support in pg_dump. I think this is just waiting > for a second opinion on whether the approach is correct. I've been > meaning to look at it, but haven't gotten enough round tuits; maybe > someone else would like to take a look? This is an open item, so we > should really try to deal with it. Do I have anything I can work on this right now? Because I'll be unavailable at the next week, I'd like to fix up it within this week, if possible. -- OSS Platform Development Division, NEC KaiGai Kohei -- 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 PGresult object and encoding
libpq has a PQclientEncoding() function that takes a connection object. However, the client encoding is, in some cases, a property of the result object. For instance, if your client_encoding changes, but you keep the result object around, you have no way to determine later what encoding the results are in. The result object already saves the client_encoding. Should an accessor be provided? Regards, Jeff Davis -- 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] Confusion over Python drivers
On Sun, 2010-02-14 at 20:43 +0100, Florian Weimer wrote: > The downside is that passing strings up to the application may have > distinctly worse performance characteristics than passing a number. Yes, that is a good point. I tried to clarify this in the doc. I think this would fall under the optional type conversion convenience functions. As long as it's explicit that the conversion is happening, I think it's OK. Regards, Jeff Davis -- 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] Error when building postgresql with contrib functions
M Z wrote: > Looks like the contrib functions have not been added in. The contrib features are not built or installed by default -- they are optional modules. This should point you in the right direction: http://www.postgresql.org/docs/8.3/interactive/contrib.html -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] Explain buffers display units.
On Mon, Feb 15, 2010 at 6:44 PM, Greg Stark wrote: > I did respond to it. The whole point is that the text output is for a > human to read. It should be printed in human-readable units. Not some > arbitrary internal unit of accounting that they then have to do > arithmetic on to make sense of. Well, I disagree with your statement the previous output was not printed in human-readable units: it was printed in blocks, which I find to be a perfectly good unit. It's true that the basic unit of blocks can be converted into kilobytes, but so what? We aren't really measuring kilobytes; we're measuring blocks. We could equally well convert the sort and hash output from kilobytes into blocks, but it would be equally wrong: the sort and hash statistics are measuring memory usage by adding up actual memory allocations. The buffer statistics are simply counting the number of blocks that are read or written. Multiplying by the block size makes it sound as if all the memory was read or used, which is simply not the case - especially for things like buffer hits, which don't actually read or allocate any memory at all. > We do *not* display raw block numbers anywhere else. Generally I think > we should have a policy of outputing human-readable standard units of > memory whenever displaying a memory quantity. Actually I thought we > already had that policy, hence things like: > > postgres=# show shared_buffers; > shared_buffers > > 28MB > (1 row) > > postgres=# show checkpoint_timeout; > checkpoint_timeout > > 5min > (1 row) We certainly do that for GUCs, and in that context it seems to me to make sense. If you set your shared buffers to a gigabyte, PG will use an additional GB of memory. But if you hit a "gigabyte" of shared buffers, you may be examining anywhere from one 8K block over and over again all the way up to a full GB of memory. Block hits and reads just don't add in the same way that actual memory allocations do. And at any rate, what we DON'T do for GUCs is produce differing output format for the same parameter based on the magnitude of the output value, as you've done here. We accept input in several different formats, but there is only one canonical output formal for any particular GUC, which is furthermore always chosen in such a way that the exact value of the setting is preserved (again, unlike what you've done here). > The other examples you name are all internal or machine-readable > fomats which have to be formatted somehow using sql queries or tools > if you want to inspect the values directly. The user is free to format > the output of the pg_stat* functions using pg_size_pretty() though > it's annoying that it's not in the same base unit that > pg_relation_size() outputs but these are the only interface to these > internal counters so there's no way to know if they're being used for > human-readable output or for gathering raw data for statistics or > other purposes. So, you're saying we shouldn't look at the way that the pg_stat functions format the output because somebody might write a view over it that formats it in some different way that may or may not match what you've done for the EXPLAIN output? What makes you think that people don't just look at the raw numbers? I certainly have, and there's no suggestion in the documentation that users should do anything else. pg_stat_statements doesn't do what you're suggesting either; it, too, presents raw numbers, and lets the user make of it what they will. They might, for example, want to compute a hit ratio, as in the example provided in the docs. In the case of EXPLAIN of an index scan, they might want to estimate the number of seeks, on the theory that an inner-indexscan is going to be all random IO. >> I think this is a really terrible idea. You've got a lot of very >> specific formatting code in explain.c which anyone who wants to use >> the JSON and XML output will very possibly need to reimplement. I >> have worked really hard to keep the text format in sync with all the >> others, and up until now they have been. > > You're assuming the JSON and XML program is planning to display the > measurements? They might not be. They might be gathering them for > charting or for alerts or all kinds of other things. Even if they do > plan to output them they'll want to format it in way that makes sense > for the context it's used in which might include more or fewer digits > or plug into some widget which requires raw values and does the > formatting automatically. Yes, they might want to write their own formatting code, but they also might not. They might want to calculate hit ratios, or they might want to alter the number of decimal places, or they might just want to output the exact same information as the text format, but in a GUI format rather than using ASCII art. > Whereas the human-readable format should display values in a form > humans can parse, the machine-readable output sho
Re: [HACKERS] Streaming Replication on win32
On Tue, Feb 16, 2010 at 1:33 AM, Magnus Hagander wrote: > 2010/2/15 Tom Lane : >> Magnus Hagander writes: >>> I changed your patch to this, because I find it a lot simpler. The >>> change is in the checking in pgwin32_recv - there is no need to ever >>> call waitforsinglesocket, we can just exit out early. Thanks a lot, Magnus! >>> Do you see any issue with that? >> >> This definitely looks cleaner, but is there a reason not to use bool >> instead of int here? > > No. Can include/port/win32.h refer to bool type? 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] TCP keepalive support for libpq
On Tue, Feb 16, 2010 at 1:18 AM, Robert Haas wrote: >> I'm all for this as a 9.1 submission, but let's not commit to trying to >> debug it now. I would like a green buildfarm for awhile before we wrap >> alpha4, and this sort of untested "it can't hurt" patch is exactly what >> is likely to make things not green. > > Mmm. OK, fair enough. Okay. I added the patch to the first CF for v9.1. Let's discuss about it later. 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] ToDo: preload for fulltext dictionary
Pavel Stehule wrote: > The dictionary data could be shared or minimally dictionary could be > preloaded like some PL language. > > What do you think about this? Surely preloading is the most realistic approach, but I hope we would support dynamic allocation of shared memory, and load dictionaries in the area and share it with backends. We should avoid additonal calls of shmget() or mmap() in the additional shared memory allocation, but we can shrink shared buffers and reuse the area for general purposes. We often have serveral GB of shared buffers nowadays, so dividing some MB of buffers will not be problem. Regards, --- Takahiro Itagaki 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] Explain buffers display units.
Greg Stark wrote: We do *not* display raw block numbers anywhere else. Generally I think we should have a policy of outputing human-readable standard units of memory whenever displaying a memory quantity. Actually I thought we already had that policy, hence things like... The first counter example I thought of is log_checkpoints which looks like this: LOG: checkpoint complete: wrote 133795 buffers (25.5%); 0 transaction log file(s) added, 0 removed, 98 recycled; write=112.281 s, sync=108.809 s, total=221.166 s Probably the XML schema should include the units as an attribute for each tag so tools don't have to hard-code knowledge about what unit each tag is in. I don't know if it's practical at this point, but it might be helpful for the truly machine-targeted output formats to include specifically BLCKSZ somewhere in their header--just so there's a universal way to interpret the output even if the user tuned that. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [HACKERS] Explain buffers display units.
On Mon, Feb 15, 2010 at 7:58 PM, Robert Haas wrote: >>> To me, buffers seem like discrete (and unitless) >>> entities, and we handle them that way elsewhere in the system (see, >>> e.g. pg_stat_database, pg_statio_all_tables). I don't know that it's >>> a good idea to display that same information here in a different >>> format. > > This seems like an important point that you need to respond to. Why > should we print out this information in kB here when we display it as > raw numbers elsewhere? I can't see any reason at all. I did respond to it. The whole point is that the text output is for a human to read. It should be printed in human-readable units. Not some arbitrary internal unit of accounting that they then have to do arithmetic on to make sense of. We do *not* display raw block numbers anywhere else. Generally I think we should have a policy of outputing human-readable standard units of memory whenever displaying a memory quantity. Actually I thought we already had that policy, hence things like: postgres=# show shared_buffers; shared_buffers 28MB (1 row) postgres=# show checkpoint_timeout; checkpoint_timeout 5min (1 row) The other examples you name are all internal or machine-readable fomats which have to be formatted somehow using sql queries or tools if you want to inspect the values directly. The user is free to format the output of the pg_stat* functions using pg_size_pretty() though it's annoying that it's not in the same base unit that pg_relation_size() outputs. but these are the only interface to these internal counters so there's no way to know if they're being used for human-readable output or for gathering raw data for statistics or other purposes. >>> I definitely do not want to do anything that loses accuracy. This is >>> probably accurate enough for most uses, but it's still not as accurate >>> as just printing the raw numbers. >> >> I left the XML/JSON output in terms of blocks on the theory that tools >> reading this data can look up the block size and convert all it wants. > > I think this is a really terrible idea. You've got a lot of very > specific formatting code in explain.c which anyone who wants to use > the JSON and XML output will very possibly need to reimplement. I > have worked really hard to keep the text format in sync with all the > others, and up until now they have been. You're assuming the JSON and XML program is planning to display the measurements? They might not be. They might be gathering them for charting or for alerts or all kinds of other things. Even if they do plan to output them they'll want to format it in way that makes sense for the context it's used in which might include more or fewer digits or plug into some widget which requires raw values and does the formatting automatically. Whereas the human-readable format should display values in a form humans can parse, the machine-readable output should include the raw measurements with enough information for the tool to make sense of it. Probably the XML schema should include the units as an attribute for each tag so tools don't have to hard-code knowledge about what unit each tag is in. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [GENERAL] libecpg versions and libecpg_compat
Hi Hackers, I posted this to the GENERAL list a while back, but got no repies. Perhaps someone here can help... I've been building ECPG (embedded SQL/C) programs on a system with Pg version 8.0 installed. When I tried to run them recently on version 8.4 I found that there was a libecpg library incompatibility: v8.0 uses libecpg.so.5, whereas 8.4 uses libecpg.so.6. Then I noticed libecpg_compat in the lib area. What is this used for? "compat" suggests compatibility between different versions? But Pg 8.0 has libecpg_compat.so.2, whereas Pg 8.4 has libecpg_compat.so.3. Is there some way of building with Pg v8.0 ECPG lib and running on a system with Pg v8.4 ECPG lib? or vice versa? and is libecpg_compat intended for that purpose? Thanks, Rob -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Error when building postgresql with contrib functions
Hi
I am trying to build postgresql with contrib functions from source code
checked out from cvs version 8.3.8 but getting error:
==
conifer=# SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int',
'true') as t(id int4);
ERROR: function xpath_table(unknown, unknown, unknown, unknown, unknown)
does not exist
LINE 1: SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int...
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
==
Looks like the contrib functions have not been added in.
OS Ubuntu Karmic.
I checked out source code from branch 8.3.8. Before building postgresql,
$ sudo apt-get install libreadline6-dev zlib1g-dev libxml2-dev libxslt1-dev
bison flex
libreadline6-dev needs libncurses5-dev as dependency so libncurses5-dev was
also installed.
The installation step I performed:
$ ./configure --with-libxml --with-libxslt
$ make
$ make check
$ sudo make install
$ export PGDATA=/data/pgsql/data
$ initdb
$ createdb conifer
$ pg_ctl start
$ psql
everything looks fine but I got error by doing:
conifer=# CREATE TABLE xpath_test (id integer NOT NULL, t xml);
CREATE TABLE
conifer=# INSERT INTO xpath_test VALUES (1, '1');
INSERT 0 1
conifer=# SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int',
'true') as t(id int4);
ERROR: function xpath_table(unknown, unknown, unknown, unknown, unknown)
does not exist
LINE 1: SELECT * FROM xpath_table('id', 't', 'xpath_test', '/doc/int...
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
Thank you very much for your help.
Best,
M Z
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Feb 15, 2010, at 2:42 PM, Tim Bunce wrote:
> I've not really looked the the DBD::Pg code much so this seemed like a
> good excuse... It looks like the default is to call PQprepare() with
> paramTypes Oid values of 0.
Yes, IIRC, 0 == unknown as far as the server is concerned. It just tells the
server to resolve it when it can.
> http://developer.postgresql.org/pgdocs/postgres/libpq-exec.html says
> "If paramTypes is NULL, or any particular element in the array is zero,
> the server assigns a data type to the parameter symbol in the same way
> it would do for an untyped literal string."
Right, exactly.
> But I don't know if that means it has the same semantics as using
> 'unknown' as a type to PL/Perl's spi_prepare(). The docs for
> spi_prepare() don't mention if type parameters are optional or what
> happens if they're omitted.
> http://developer.postgresql.org/pgdocs/postgres/plperl-builtins.html#PLPERL-DATABASE
Same as in SQL PREPARE, I'm sure. Ultimately that's what's doing the work, IIUC.
> Looking at the code I see spi_prepare() maps the provided arg type names
> to oids then calls SPI_prepare(). The docs for SPI_prepare() also don't
> mention if the type parameters are optional or what happens if they're
> omitted.
> The docs for the int nargs parameter say "number of input *parameters*"
> not "number of parameters that Oid *argtypes describes"
> http://developer.postgresql.org/pgdocs/postgres/spi-spi-prepare.html
>
> Guess I need to go and check the current behaviour... see below.
And like maybe a doc patch might be useful.
> I'm currently using:
>
>my $placeholders = join ",", map { '$'.$_ } 1..$arity;
>my $plan = spi_prepare("select * from $spname($placeholders)",
> @$arg_types) };
Ah, yeah, that's better, but I do think you should use quote_ident() on the
function name.
> and it turns out that spi_prepare is happy to prepare a statement with
> more placeholders than there are types provided.
Types or args?
> I'm a little nervous of relying on that undocumented behaviour.
> Hopefully someone can clarify if that's expected behaviour.
It's what I would expect, but I'm not an authority on this stuff.
> So, anyway, I've now extended the code so the parenthesis and types
> aren't needed. Thanks for prompting the investigation :)
Yay!
>> I don't think it's necessary. I mean, if you're passed an array, you
>> should of course pass it to PostgreSQL, but it can be anyarray.
>
> Sure, you can pass an array in encoded string form, no problem.
> But specifying in the signature a type that includes [] enables
> you to use a perl array _reference_ and let call() look after
> encoding it for you.
>
> I did it that way round, rather than checking all the args for refs on
> every call, as it felt safer, more efficient, and more extensible.
IIRC (again, sorry), that's what DBD::Pg does: It checks all the args and turns
an array into an SQL array, without regard to specified types.
>> No, but the latter is more Perlish.
>
> True. You can't specify a schema though, and the 'SP' is somewhat
> artificial. Still, I'm coming round to the idea :)
What about `SP->schema::function_name()`? Agreed that SP is artificial, but
there needs to be some kind of handle for AUTOLOAD to wrap itself around. Maybe
a singleton object instead? (I was kind of thinking of SP as that, anyway:
use constant SP => 'PostgreSQL::PLPerl';
)
>> Yeah yeah. I could even put one on CPAN. ;-P
>
> I think it only needs this (untested):
>
>package SP;
>sub AUTOLOAD { our $AUTOLOAD =~ s/^SP:://; shift; call($AUTOLOAD, @_); }
Yep. Might be nice sugar to just throw in your module anyway.
> I could either add an extra module (PostgreSQL::PLPerl::Call::SP)
> or add a fancy import hook like:
>
>use PostgreSQL::PLPerl::Call qw(:AUTOLOAD => 'SP');
The latter is nice, as then the DBA can specify the name of package/global
object.
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] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Mon, Feb 15, 2010 at 11:52:01AM -0800, David E. Wheeler wrote:
> On Feb 15, 2010, at 2:51 AM, Tim Bunce wrote:
>
> > The signature doesn't just qualify the selection of the function,
> > it also ensures appropriate interpretation of the arguments.
> >
> > I could allow call('foo', @args), which could be written call(foo => @args),
> > but what should that mean in terms of the underlying behaviour?
> >
> > I think there are three practical options:
> > a) treat it the same as call('foo(unknown...)', @args)
>
> I believe that's basically what psql does. It's certainly what DBD::Pg does.
I've not really looked the the DBD::Pg code much so this seemed like a
good excuse... It looks like the default is to call PQprepare() with
paramTypes Oid values of 0.
http://developer.postgresql.org/pgdocs/postgres/libpq-exec.html says
"If paramTypes is NULL, or any particular element in the array is zero,
the server assigns a data type to the parameter symbol in the same way
it would do for an untyped literal string."
But I don't know if that means it has the same semantics as using
'unknown' as a type to PL/Perl's spi_prepare(). The docs for
spi_prepare() don't mention if type parameters are optional or what
happens if they're omitted.
http://developer.postgresql.org/pgdocs/postgres/plperl-builtins.html#PLPERL-DATABASE
Looking at the code I see spi_prepare() maps the provided arg type names
to oids then calls SPI_prepare(). The docs for SPI_prepare() also don't
mention if the type parameters are optional or what happens if they're omitted.
The docs for the int nargs parameter say "number of input *parameters*"
not "number of parameters that Oid *argtypes describes"
http://developer.postgresql.org/pgdocs/postgres/spi-spi-prepare.html
Guess I need to go and check the current behaviour... see below.
> > c) instead of using a cached prepared query, build an SQL statement
> > for every execution, which would naturally have to quote all values:
> >my $args = join ",", map { ::quote_nullable($_) } @_;
> >return ::spi_exec_query("select * from $spname($args)");
> >
> > I suspect there are subtle issues (that I'm unfamilar with) lurking here.
> > I'd appreciate someone with greater understanding spelling out the issues
> > and trade-offs in those options.
>
> I'm pretty sure the implementation doesn't have to declare the types of
> anything:
>
> sub AUTOLOAD {
> my $self = shift;
> our $AUTOLOAD;
> (my $fn = $AUTOLOAD) =~ s/.*://;
> my $prepared = spi_prepare(
> 'EXECUTE ' . quote_ident($fn) . '('
> . join(', ', ('?') x @_)
> . ')';
> # Cache it and call it.
> }
I'm currently using:
my $placeholders = join ",", map { '$'.$_ } 1..$arity;
my $plan = spi_prepare("select * from $spname($placeholders)", @$arg_types)
};
and it turns out that spi_prepare is happy to prepare a statement with
more placeholders than there are types provided.
I'm a little nervous of relying on that undocumented behaviour.
Hopefully someone can clarify if that's expected behaviour.
So, anyway, I've now extended the code so the parenthesis and types
aren't needed. Thanks for prompting the investigation :)
> > Umm,
> >tl_activity_stats_sql => [qw(text[] int)]
> >
> > seems to me longer and rather less visually appealing than
> >
> >'tl_activity_stats_sql(text[], int)'
>
> That would work, too. But either way, having to specify the signature
> would be the exception rather than the rule. You'd only need to do it
> when calling a polymorphic function with the same number of arguments
> as another polymorphic function.
[Tick]
> >> and only provide the signature when I need to disambiguate between
> >> polymorphic variants.
> >
> > Or need to qualify the type of the argument for some other reason, like
> > passing an array reference.
>
> I don't think it's necessary. I mean, if you're passed an array, you
> should of course pass it to PostgreSQL, but it can be anyarray.
Sure, you can pass an array in encoded string form, no problem.
But specifying in the signature a type that includes [] enables
you to use a perl array _reference_ and let call() look after
encoding it for you.
I did it that way round, rather than checking all the args for refs on
every call, as it felt safer, more efficient, and more extensible.
> > But perhaps we can agree on one of the options a/b/c above and then
> > this issue will be less relevant. It's not like you'd be saving much
> > typing:
> >
> >call('tl_activity_stats_sql', @args)
> >call(tl_activity_stats_sql => @args)
> >SP->tl_activity_stats_sql(@args)
>
> No, but the latter is more Perlish.
True. You can't specify a schema though, and the 'SP' is somewhat
artificial. Still, I'm coming round to the idea :)
> > You could always add a trivial SP::AUTOLOAD wrapper function to your
> > plperl.on_init code :)
>
> Yeah yeah. I could even put one on CPAN. ;-P
I think it
[HACKERS] one more index for pg_tablespace?
hello ... i have come an interesting corner case this morning and i am not sure if it is worth treating this as a bug or as just "bad luck". imagine creating a directory along with a tablespace ... hans-jurgen-schonigs-macbook:html hs$ mkdir /tmp/x hans-jurgen-schonigs-macbook:html hs$ psql test psql (8.4.1) Type "help" for help. test=# create tablespace x location '/tmp/x'; CREATE TABLESPACE test=# create tablespace x2 location '/tmp/x'; ERROR: directory "/tmp/x" is not empty test=# \q postgres errors our here correctly because it sees that the tablespace is not empty. this is perfect ... hans-jurgen-schonigs-macbook:html hs$ cd /tmp/x hans-jurgen-schonigs-macbook:x hs$ ls PG_VERSION hans-jurgen-schonigs-macbook:x hs$ rm PG_VERSION now, after killing the PG_VERSION file, i am able to create a tablespace pointing to the same directoy. this should be prevented by one more unique index on the directory. hans-jurgen-schonigs-macbook:x hs$ psql test psql (8.4.1) Type "help" for help. test=# create tablespace x2 location '/tmp/x'; CREATE TABLESPACE test=# \d pg_tablespace Table "pg_catalog.pg_tablespace" Column| Type| Modifiers -+---+--- spcname | name | not null spcowner| oid | not null spclocation | text | spcacl | aclitem[] | Indexes: "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global" "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global" Tablespace: "pg_global" test=# SELECT * FROM pg_tablespace; spcname | spcowner | spclocation | spcacl +--+---+ pg_default | 10 | | pg_global | 10 | | x | 10 | /tmp/x| x2 | 10 | /tmp/x| (6 rows) now, killing PG_VERSION manually is not what people do but what can happen is that, say, an NFS connection is gone or that somehow the directory is empty because of some other network filesystem doing some funny thing. it is quite realistic that this can happen. how about one more unique index here? pg_tablespace does not look too good with a duplicate entry ... many thanks, hans -- Cybertec Schönig & Schönig GmbH Reyergasse 9 / 2 A-2700 Wiener Neustadt Web: www.postgresql-support.de -- 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] LISTEN/NOTIFY versus encoding conversion
On Mon, 2010-02-15 at 13:53 -0500, Tom Lane wrote: > You're assuming that the LISTEN was transmitted across the connection, > and not for example executed by a pre-existing function. Ok, good point. > In practice, since encoding conversion failures could interfere with the > results of almost any operation, it's not apparent to me why we should > single out NOTIFY as being so fragile it has to have an ASCII-only > restriction. Ok, it sounds reasonable to lift the restriction. Regards, Jeff Davis -- 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] Listen / Notify - what to do when the queue is full
On Sun, 2010-02-14 at 22:44 +, Simon Riggs wrote: > * We also discussed the idea of having a NOTIFY command that would work > from Primary to Standby. All this would need is some code to WAL log the > NOTIFY if not in Hot Standby and for some recovery code to send the > NOTIFY to any listeners on the standby. I would suggest that would be an > option on NOTIFY to WAL log the notification: > e.g. NOTIFY me 'with_payload' FOR STANDBY ALSO; My first reaction is that it should not be optional. If we allow a slave system to LISTEN on a condition, what's the point if it doesn't receive the notifications from the master? Cache invalidation seems to be the driving use case for LISTEN/NOTIFY. Only the master can invalidate the cache (as Tom points out downthread); and users on the slave system want to know about that invalidation if they are explicitly listening for it. Regards, Jeff Davis -- 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] LISTEN/NOTIFY versus encoding conversion
On Sun, Feb 14, 2010 at 03:15:30PM -0500, Tom Lane wrote: > So the currently submitted patch is logically inconsistent. If we > enforce a character set restriction on the payload for fear of > being unable to convert it to the destination client_encoding, then > we should logically do the same for the condition name. But then > why not also restrict a lot of other things to pure ASCII? AFAICS this essentially goes for "payload is a text string" and for people who want "payload as binary" will have to do hex encoding or some such. At least, I thought one of the reasons why it got limited was because we couldn't decide. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] psycopg2 license changed
On 15/02/2010 20:12, Greg Smith wrote: > Federico Di Gregorio wrote: >> I just wanted all interested people know that psycopg2 2.0.14 to be >> released in the next few days will be under the LGPL3 + OpenSSL >> exception (example code and tests under the LGPL3 alone because they are >> never linked to OpenSSL). > > Great news and I look forward to the release. One small thing to > consider: having more than one license can turn into a cost to users of > your software who are required to have each license reviewed for legal > issues, and I'd think that maintaining two has some cost for you too. > If it's possible for you to fold all these into a single license, that > would really be a lot nicer. Being able to say "psycopg2 is LGPL3 + > OpenSSL exception", period, is much easier for people to deal with than > having two licenses and needing to include the description you gave > above for explanation. Having to educate a lawyer on how linking works, > so they understand the subtle distinction for why the two licenses > exist, is no fun at all. Even if tests and examples code aren't almost never distributed except in the psycopg2 source package? A couple of other people contributed to the tests: if you really feel like it is so important I'll contact them and ask their permission to use the LGPL3 + exception (the contribution was without the exception) or remove the code (we won't lose much.) federico -- Federico Di Gregorio federico.digrego...@dndg.it Studio Associato Di Nunzio e Di Gregorio http://dndg.it God is in the rain... -- Evey Hammond signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Avoiding bad prepared-statement plans.
Robert Haas wrote: > > 7. Why is there no option to do parameterized-queries which replan every > > time? > > > > This just seems like an area that has been neglected, or maybe I am > > missing something and our current setup is acceptable. > > No, our current setup is not acceptable, and your questions are all > right on target. I have been hoping that someone would take an > interest in this problem for years. An option to replan on every > execution would be a very, very fine thing. IMHO, there should also > be a way to signal to PL/pgsql that you want this behavior for a > particular query, short of wrapping it using EXECUTE, which is clunky > and also forces a re-parse on every execution. I was hoping I was wrong. :-( -- Bruce Momjian http://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] Avoiding bad prepared-statement plans.
On Mon, Feb 15, 2010 at 2:11 PM, Bruce Momjian wrote: > Pavel Stehule wrote: >> > The problem that we face is that we don't have any very good way to tell >> > whether a fresh planning attempt is likely to yield a plan significantly >> > better than the generic plan. ?I can think of some heuristics --- for >> > example if the query contains LIKE with a parameterized pattern or a >> > partitioned table --- but that doesn't seem like a particularly nice >> > road to travel. >> > >> > A possible scheme is to try it and keep track of whether we ever >> > actually do get a better plan. ?If, after N attempts, none of the custom >> > plans were ever more than X% cheaper than the generic one, then give up >> > and stop attempting to produce custom plans. ?Tuning the variables might >> > be challenging though. >> >> I afraid so every heuristic is bad. Problem is identification of bad >> generic plan. And nobody ensure, so non generic plan will be better >> than generic. Still I thing we need some way for lazy prepared >> statements - plan is generated everytime with known parameters. > > Yea, this opens a whole host of questions for me: > > 1. Why do we only do bind-level planning for anonymous wire-level queries? > > 2. I realize we did anonymous-only because that was the only way we had > in the protocol to _signal_ bind-time planning, but didn't we think of > this when we were implementing the wire-level protocol? > > 3. Do we have no place to add this cleanly without a protocol version > bump? > > 4. Why don't we just always do planning at first bind time? When is > that worse than using generic values? > > 5. Why have we not added an option for SQL-level prepare to do this? > > 6. When do our generic columns costs significantly worse than having > specific constants? I assume unique columns are fine with generic > constants. > > 7. Why is there no option to do parameterized-queries which replan every > time? > > This just seems like an area that has been neglected, or maybe I am > missing something and our current setup is acceptable. No, our current setup is not acceptable, and your questions are all right on target. I have been hoping that someone would take an interest in this problem for years. An option to replan on every execution would be a very, very fine thing. IMHO, there should also be a way to signal to PL/pgsql that you want this behavior for a particular query, short of wrapping it using EXECUTE, which is clunky and also forces a re-parse on every execution. ...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] Listen / Notify - what to do when the queue is full
Joachim Wieland writes: > We could probably fake this on the Hot Standby in the following way: > We introduce a commit record for every notifying transaction and write > it into the queue itself. So right before writing anything else, we > write an entry which informs readers that the following records are > not yet committed. Then we write the actual notifications and commit. > In post-commit we return back to the commit record and flip its > status. This doesn't seem likely to work --- it essentially makes commit non atomic. There has to be one and only one authoritative reference as to whether transaction X committed. I think that having HS slave sessions issue notifies is a fairly silly idea anyway. They can't write the database, so exactly what condition are they going to be notifying others about? What *would* be useful is for HS slaves to be able to listen for notify messages issued by writing sessions on the master. This patch gets rid of the need for LISTEN to change on-disk state, so in principle we can do it. The only bit we seem to lack is WAL transmission of the messages (plus of course synchronization in case a slave session is too slow about picking up messages). Definitely a 9.1 project at this point though. 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] Explain buffers display units.
On Mon, Feb 15, 2010 at 1:29 PM, Greg Stark wrote: > On Mon, Feb 15, 2010 at 6:05 PM, Robert Haas wrote: >>> Well there was a 30+ message thread almost a week ago where there >>> seemed to be some contention over the issue of whether the numbers >>> should be averages or totals. But were there was no dispute over the >>> idea of printing in memory units instead of blocks. >> >> Hmm yeah, I guess it wasn't discussed. I'm still not sure it's an >> improvement. If a query hit one buffer, is that really the same as >> saying it hit 8kB? > > Well you can always convert between them. The only time it would make > a difference is if you're sure it's random i/o and you're concerned > with the number of iops. However it's impossible to tell from this > output how many of these buffers are read sequentially and how many > randomly. Even if it's sequential you don't know how much it read > between interruptions to handle the inner side of a join or whether > the cached blocks were interspersed throughout the file or were all at > the beginning or end. All true, although "you can always converted between them" assumes you know the block size. I don't imagine many people change that, but... > I think we should provide better tools to measure these things > directly rather than force users to make deductions from buffer > counts. I'm still excited about using dtrace to get real counts of > iops, seeks, etc. Sure. >> To me, buffers seem like discrete (and unitless) >> entities, and we handle them that way elsewhere in the system (see, >> e.g. pg_stat_database, pg_statio_all_tables). I don't know that it's >> a good idea to display that same information here in a different >> format. This seems like an important point that you need to respond to. Why should we print out this information in kB here when we display it as raw numbers elsewhere? I can't see any reason at all. >> I definitely do not want to do anything that loses accuracy. This is >> probably accurate enough for most uses, but it's still not as accurate >> as just printing the raw numbers. > > I left the XML/JSON output in terms of blocks on the theory that tools > reading this data can look up the block size and convert all it wants. I think this is a really terrible idea. You've got a lot of very specific formatting code in explain.c which anyone who wants to use the JSON and XML output will very possibly need to reimplement. I have worked really hard to keep the text format in sync with all the others, and up until now they have been. > Incidentally looking at the pg_size_pretty() functions reminds me that > these counters are all 32-bit. That means they'll do funny things if > you have a query which accesses over 16TB of data... I suspect this > should probably be changed though I'm feeling lazy about it unless > someone else wants to push me to do it now. Well that will require fixing a whole lot of bits in the stats infrastructure that are only minimally related to this patch. That is certainly 9.1 material. Basically, I think this whole change is a bad idea and should be reverted. You've made the text format EXPLAIN inconsistent with both the non-text formats and with the rest of the buffer statistics stuff for absolutely no benefit that I can see. ...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] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Feb 15, 2010, at 2:51 AM, Tim Bunce wrote:
> The signature doesn't just qualify the selection of the function,
> it also ensures appropriate interpretation of the arguments.
>
> I could allow call('foo', @args), which could be written call(foo => @args),
> but what should that mean in terms of the underlying behaviour?
>
> I think there are three practical options:
> a) treat it the same as call('foo(unknown...)', @args)
I believe that's basically what psql does. It's certainly what DBD::Pg does.
> b) treat it the same as call('foo(text...)', @args)
Probably not a great idea.
> c) instead of using a cached prepared query, build an SQL statement
> for every execution, which would naturally have to quote all values:
>my $args = join ",", map { ::quote_nullable($_) } @_;
>return ::spi_exec_query("select * from $spname($args)");
>
> I suspect there are subtle issues (that I'm unfamilar with) lurking here.
> I'd appreciate someone with greater understanding spelling out the issues
> and trade-offs in those options.
I'm pretty sure the implementation doesn't have to declare the types of
anything:
sub AUTOLOAD {
my $self = shift;
our $AUTOLOAD;
(my $fn = $AUTOLOAD) =~ s/.*://;
my $prepared = spi_prepare(
'EXECUTE ' . quote_ident($fn) . '('
. join(', ', ('?') x @_)
. ')';
# Cache it and call it.
}
> Umm,
>tl_activity_stats_sql => [qw(text[] int)]
>
> seems to me longer and rather less visually appealing than
>
>'tl_activity_stats_sql(text[], int)'
That would work, too. But either way, having to specify the signature would be
the exception rather than the rule. You'd only need to do it when calling a
polymorphic function with the same number of arguments as another polymorphic
function.
>> and only provide the signature when I need to disambiguate between
>> polymorphic variants.
>
> Or need to qualify the type of the argument for some other reason, like
> passing an array reference.
I don't think it's necessary. I mean, if you're passed an array, you should of
course pass it to PostgreSQL, but it can be anyarray.
> But perhaps we can agree on one of the options a/b/c above and then
> this issue will be less relevant. It's not like you'd be saving much
> typing:
>
>call('tl_activity_stats_sql', @args)
>call(tl_activity_stats_sql => @args)
>SP->tl_activity_stats_sql(@args)
No, but the latter is more Perlish.
> You could always add a trivial SP::AUTOLOAD wrapper function to your
> plperl.on_init code :)
Yeah yeah. I could even put one on CPAN. ;-P But where are you caching planned
functions?
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] Avoiding bad prepared-statement plans.
Tom Lane wrote: Well, no, consider the situation where planning takes 50 ms, the generic plan costs 100ms to execute, but a parameter-specific plan would take 1ms to execute. Planning is very expensive compared to execution but it's still a win to do it. I think that's a fun and worthwhile problem. But my limited personal aim right now is a stopgap for pathological cases. I'd like to pick the low-hanging fruit; actually squeezing the fat out of prepared statements is a job I wouldn't get around to completing. Sorry for mixing metaphors. Here's what I like about the really slow plans. (Now why does that sound so strange?) We don't know if re-planning will help, but we do know that (1) it won't hurt much relative to execution time, so we really don't _care_; and (2) there is lots of potential for improvement, so catching just one execution that can be much faster might pay for all the extra time spent re-planning. Where do we draw the line between costly and pathological? I still like Bart's idea of a fixed ratio to planning time, because it reflects a self-tuning sense of proportion. Sure, planning time can vary a lot but we're talking about an order-of-magnitude difference, not an exact 19:21 optimum. We can be sloppy and still expect to win. AFAIC a statement could go to "re-planning mode" if the shortest execution time for the generic plan takes at least 10x longer than the longest planning time. That gives us a decent shot at finding statements where re-planning is a safe bet. A parameter that we or the user would have to tweak would just be a fragile approximation of that. A possible scheme is to try it and keep track of whether we ever actually do get a better plan. If, after N attempts, none of the custom plans were ever more than X% cheaper than the generic one, then give up and stop attempting to produce custom plans. Tuning the variables might be challenging though. A simple stopgap implementation may also be a useful experimentation platform for refinements. It shouldn't be too complex to rip out when something better comes along. Jeroen -- 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] Listen / Notify - what to do when the queue is full
On Mon, Feb 15, 2010 at 1:48 PM, Simon Riggs wrote: > On Mon, 2010-02-15 at 12:59 +0100, Joachim Wieland wrote: >> I have tested it already. The point where it currently fails is the >> following line: >> >> qe->xid = GetCurrentTransactionId(); > > That's a shame. So it will never work in Hot Standby mode unless you can > think of a different way. We could probably fake this on the Hot Standby in the following way: We introduce a commit record for every notifying transaction and write it into the queue itself. So right before writing anything else, we write an entry which informs readers that the following records are not yet committed. Then we write the actual notifications and commit. In post-commit we return back to the commit record and flip its status. Reading backends would stop at the commit record and we'd signal them so that they can continue. This actually plays nicely with Tom's intent to not have interleaved notifications in the queue (makes things a bit easier but would probably work either way)... However we'd need to make sure that we clean up that commit record even if something weird happens (similar to TransactionIdDidAbort() returning true) in order to allow the readers to proceed. Comments? 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] psycopg2 license changed
Federico Di Gregorio wrote: I just wanted all interested people know that psycopg2 2.0.14 to be released in the next few days will be under the LGPL3 + OpenSSL exception (example code and tests under the LGPL3 alone because they are never linked to OpenSSL). Great news and I look forward to the release. One small thing to consider: having more than one license can turn into a cost to users of your software who are required to have each license reviewed for legal issues, and I'd think that maintaining two has some cost for you too. If it's possible for you to fold all these into a single license, that would really be a lot nicer. Being able to say "psycopg2 is LGPL3 + OpenSSL exception", period, is much easier for people to deal with than having two licenses and needing to include the description you gave above for explanation. Having to educate a lawyer on how linking works, so they understand the subtle distinction for why the two licenses exist, is no fun at all. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Avoiding bad prepared-statement plans.
Pavel Stehule wrote: > > The problem that we face is that we don't have any very good way to tell > > whether a fresh planning attempt is likely to yield a plan significantly > > better than the generic plan. ?I can think of some heuristics --- for > > example if the query contains LIKE with a parameterized pattern or a > > partitioned table --- but that doesn't seem like a particularly nice > > road to travel. > > > > A possible scheme is to try it and keep track of whether we ever > > actually do get a better plan. ?If, after N attempts, none of the custom > > plans were ever more than X% cheaper than the generic one, then give up > > and stop attempting to produce custom plans. ?Tuning the variables might > > be challenging though. > > I afraid so every heuristic is bad. Problem is identification of bad > generic plan. And nobody ensure, so non generic plan will be better > than generic. Still I thing we need some way for lazy prepared > statements - plan is generated everytime with known parameters. Yea, this opens a whole host of questions for me: 1. Why do we only do bind-level planning for anonymous wire-level queries? 2. I realize we did anonymous-only because that was the only way we had in the protocol to _signal_ bind-time planning, but didn't we think of this when we were implementing the wire-level protocol? 3. Do we have no place to add this cleanly without a protocol version bump? 4. Why don't we just always do planning at first bind time? When is that worse than using generic values? 5. Why have we not added an option for SQL-level prepare to do this? 6. When do our generic columns costs significantly worse than having specific constants? I assume unique columns are fine with generic constants. 7. Why is there no option to do parameterized-queries which replan every time? This just seems like an area that has been neglected, or maybe I am missing something and our current setup is acceptable. We have done a lot of work to generate acceptable optimizer statistics, but we are not using them for a significant part of our user base, particularly JDBC. We do have a TODO item, but it has gotten little attention: Allow finer control over the caching of prepared query plans Currently anonymous (un-named) queries prepared via the libpq API are planned at bind time using the supplied parameters --- allow SQL PREPARE to do the same. Also, allow control over replanning prepared queries either manually or automatically when statistics for execute parameters differ dramatically from those used during planning. -- Bruce Momjian http://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] LISTEN/NOTIFY versus encoding conversion
Jeff Davis writes: > On Sun, 2010-02-14 at 15:15 -0500, Tom Lane wrote: >> Most obviously, we could also get an encoding >> conversion failure on the notify condition name --- but we've never >> enforced a character set restriction on that, and nobody's ever >> complained about it AFAIR. > If the client successfully executed the LISTEN, then it could convert > all of the characters in one direction. You're assuming that the LISTEN was transmitted across the connection, and not for example executed by a pre-existing function. > The case of a condition name conversion error seems less problematic to > me anyway, because it would happen every time; so there's no danger of > making it through testing and then failing in production. mmm ... that's assuming that condition names are constants, which isn't necessarily the case either (I seem to recall generating condition names even back in 1997). > Ok. I'd feel a little better if I understood what would actually happen > in the case of an error with NOTIFY. When does the client receive the > error? Might the client code confuse it with an error for something > synchronous, like a command execution? Yeah, that's possible, but avoiding encoding conversion failures doesn't eliminate that little hole in the protocol :-(. There are other ways for the send attempt to fail. Admittedly, many of them involve a connection drop, but not all. In practice, since encoding conversion failures could interfere with the results of almost any operation, it's not apparent to me why we should single out NOTIFY as being so fragile it has to have an ASCII-only restriction. 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] LISTEN/NOTIFY versus encoding conversion
On Sun, 2010-02-14 at 15:15 -0500, Tom Lane wrote: > Most obviously, we could also get an encoding > conversion failure on the notify condition name --- but we've never > enforced a character set restriction on that, and nobody's ever > complained about it AFAIR. If the client successfully executed the LISTEN, then it could convert all of the characters in one direction. I suppose some incomplete conversion routine might not be able to convert the same characters in the other direction -- is that what you're referring to? The case of a condition name conversion error seems less problematic to me anyway, because it would happen every time; so there's no danger of making it through testing and then failing in production. > I'm now thinking that we should just drop that restriction. Ok. I'd feel a little better if I understood what would actually happen in the case of an error with NOTIFY. When does the client receive the error? Might the client code confuse it with an error for something synchronous, like a command execution? Regards, Jeff Davis -- 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] Explain buffers display units.
On Mon, Feb 15, 2010 at 6:05 PM, Robert Haas wrote: >> Well there was a 30+ message thread almost a week ago where there >> seemed to be some contention over the issue of whether the numbers >> should be averages or totals. But were there was no dispute over the >> idea of printing in memory units instead of blocks. > > Hmm yeah, I guess it wasn't discussed. I'm still not sure it's an > improvement. If a query hit one buffer, is that really the same as > saying it hit 8kB? Well you can always convert between them. The only time it would make a difference is if you're sure it's random i/o and you're concerned with the number of iops. However it's impossible to tell from this output how many of these buffers are read sequentially and how many randomly. Even if it's sequential you don't know how much it read between interruptions to handle the inner side of a join or whether the cached blocks were interspersed throughout the file or were all at the beginning or end. I think we should provide better tools to measure these things directly rather than force users to make deductions from buffer counts. I'm still excited about using dtrace to get real counts of iops, seeks, etc. > To me, buffers seem like discrete (and unitless) > entities, and we handle them that way elsewhere in the system (see, > e.g. pg_stat_database, pg_statio_all_tables). I don't know that it's > a good idea to display that same information here in a different > format. >... > I definitely do not want to do anything that loses accuracy. This is > probably accurate enough for most uses, but it's still not as accurate > as just printing the raw numbers. I left the XML/JSON output in terms of blocks on the theory that tools reading this data can look up the block size and convert all it wants. Likewise the pg_stat* functions are for extracting raw data. Any tool or query that extracts this data can present it in any friendly form it wants. Incidentally looking at the pg_size_pretty() functions reminds me that these counters are all 32-bit. That means they'll do funny things if you have a query which accesses over 16TB of data... I suspect this should probably be changed though I'm feeling lazy about it unless someone else wants to push me to do it now. -- 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] psycopg2 license changed
Ciao Federico, Federico Di Gregorio ha scritto: I just wanted all interested people know that psycopg2 2.0.14 to be released in the next few days will be under the LGPL3 + OpenSSL exception (example code and tests under the LGPL3 alone because they are never linked to OpenSSL). Thank you so much for your contribution! I hope this makes everybody happy, have fun, This is great news. I also want to point out that our valuable ITPUG member Daniele Varrazzo has started to write some documentation about PsycoPG2, which can be found here: http://initd.org/psycopg/docs/ It would be good if we could update our wiki as well in order to include this resource too. Ciao, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it -- 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] Explain buffers display units.
On Mon, Feb 15, 2010 at 9:55 AM, Greg Stark wrote: > On Mon, Feb 15, 2010 at 2:22 PM, Robert Haas wrote: >>> a) Changed the line description to "Total Buffer Usage" which at least >>> hints that it's something more akin to the "Total runtime" listed at >>> the bottom than the "actual time". >>> >>> b) Used units of memory -- I formatted them with 3 significant digits >>> (unless the unit is bytes or kB where that would be silly). It's just >>> what looked best to my eye. >> >> I wasn't aware we had consensus on making this change, which I see you >> committed less than an hour after posting this. > > Well there was a 30+ message thread almost a week ago where there > seemed to be some contention over the issue of whether the numbers > should be averages or totals. But were there was no dispute over the > idea of printing in memory units instead of blocks. Hmm yeah, I guess it wasn't discussed. I'm still not sure it's an improvement. If a query hit one buffer, is that really the same as saying it hit 8kB? To me, buffers seem like discrete (and unitless) entities, and we handle them that way elsewhere in the system (see, e.g. pg_stat_database, pg_statio_all_tables). I don't know that it's a good idea to display that same information here in a different format. > We can always continue tweak the details of the format such as adding > spaces before the units to make it similar to the pg_size_pretty(). > I'm not sure I like the idea of making it exactly equivalent because > pg_size_pretty() doesn't print any decimals so it's pretty imprecise > for smaller values. I definitely do not want to do anything that loses accuracy. This is probably accurate enough for most uses, but it's still not as accurate as just printing the raw numbers. ...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] Explain buffers display units.
Greg Stark wrote: We can always continue tweak the details of the format such as adding spaces before the units to make it similar to the pg_size_pretty(). I'm not sure I like the idea of making it exactly equivalent because pg_size_pretty() doesn't print any decimals so it's pretty imprecise for smaller values. That's a reasonable position; I'd be fine with upgrading the requirements for a text scraping app to handle either "8 kB" or "1.356 kB" if it wanted to share some code to consume either type of info, if all you did was throw a space in there. I'd suggest either removing the PB units support from your implementation, or adding it to pg_size_pretty, just to keep those two routines more like one another in terms of what they might produce as output given the same scale of input. Also, a quick comment in the new code explaining what you just said above might be helpful, just to preempt a similar "how is this different from pg_size_pretty?" question from popping up again one day. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] LISTEN/NOTIFY and notification timing guarantees
Joachim Wieland writes: > One question regarding #2: Is a client application able to tell > whether or not it has received all notifications from one batch? i.e. > does PQnotifies() return NULL only when the backend has sent over the > complete batch of notifications or could it also return NULL while a > batch is still being transmitted but the client-side buffer just > happens to be empty? That's true, it's difficult for the client to be sure whether it's gotten all the available notifications. It could wait a little bit to see if more arrive but there's no sure upper bound for how long is enough. If you really need it, though, you could send a query (perhaps just a dummy empty-string query). In the old implementation, the query response would mark a point of guaranteed consistency in the notification responses: you would have gotten all or none of the messages from any particular sending transaction, and furthermore there could not be any missing messages from transactions that committed before one that you saw a message from. The latter property is probably the bigger issue really, and I'm afraid that even with contiguous queuing we'd not be able to guarantee it, so maybe we have a problem even with my proposed #2 fix. Maybe we should go back to the existing scheme whereby a writer takes a lock it holds through commit, so that entries in the queue are guaranteed to be in commit order. It wouldn't lock out readers just other writers. 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] RADIUS secret in file
* Magnus Hagander (mag...@hagander.net) wrote: > IIRC Stephen had some other reason, but I'll leave it to him to > fill that in :-) I was really looking for multi-server support as well, and support for a config-file format that's commonly used for RADIUS. I'll take a whack at doing that this evening. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] RADIUS secret in file
Magnus Hagander wrote: Attached is the "last step" of the RADIUS authenticaiton as I promised Stephen - which allows the reading of the RAIDUS secret from a file instead of hardcoded in pg_hba.conf. One reason being you don't want the secret in your config file that may be in a shared repository or such. IIRC Stephen had some other reason, but I'll leave it to him to fill that in :-) Do we really need this in addition to the existing pg_hba.conf @-include functionality? Stefan -- 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 on win32
2010/2/15 Tom Lane : > Magnus Hagander writes: >> I changed your patch to this, because I find it a lot simpler. The >> change is in the checking in pgwin32_recv - there is no need to ever >> call waitforsinglesocket, we can just exit out early. > >> Do you see any issue with that? > > This definitely looks cleaner, but is there a reason not to use bool > instead of int here? No. -- 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
[HACKERS] RADIUS secret in file
Attached is the "last step" of the RADIUS authenticaiton as I promised Stephen - which allows the reading of the RAIDUS secret from a file instead of hardcoded in pg_hba.conf. One reason being you don't want the secret in your config file that may be in a shared repository or such. IIRC Stephen had some other reason, but I'll leave it to him to fill that in :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ radius_file.patch 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] Streaming Replication on win32
Magnus Hagander writes: > I changed your patch to this, because I find it a lot simpler. The > change is in the checking in pgwin32_recv - there is no need to ever > call waitforsinglesocket, we can just exit out early. > Do you see any issue with that? This definitely looks cleaner, but is there a reason not to use bool instead of int here? 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] TCP keepalive support for libpq
On Mon, Feb 15, 2010 at 11:15 AM, Tom Lane wrote: > Robert Haas writes: >> On Mon, Feb 15, 2010 at 11:00 AM, Tom Lane wrote: >>> If this were actually a low-risk patch I might think it was okay to try >>> to shoehorn it in now; but IME nothing involving making new use of >>> system-dependent APIs is ever low-risk. Look at Greg's current >>> embarrassment over fsync, a syscall I'm sure he thought he knew all >>> about. > >> That's why I think we shouldn't change the default behavior, but >> exposing a new option that people can use or not as works for them >> seems OK. > > That's assuming they get as far as having a working libpq to try it > with. I'm worried about the possibility of inducing compile or link > failures. "It works in the backend" doesn't give me that much confidence > about it working in libpq. > > I'm all for this as a 9.1 submission, but let's not commit to trying to > debug it now. I would like a green buildfarm for awhile before we wrap > alpha4, and this sort of untested "it can't hurt" patch is exactly what > is likely to make things not green. Mmm. OK, fair enough. ...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] TCP keepalive support for libpq
Robert Haas writes: > On Mon, Feb 15, 2010 at 11:00 AM, Tom Lane wrote: >> If this were actually a low-risk patch I might think it was okay to try >> to shoehorn it in now; but IME nothing involving making new use of >> system-dependent APIs is ever low-risk. Look at Greg's current >> embarrassment over fsync, a syscall I'm sure he thought he knew all >> about. > That's why I think we shouldn't change the default behavior, but > exposing a new option that people can use or not as works for them > seems OK. That's assuming they get as far as having a working libpq to try it with. I'm worried about the possibility of inducing compile or link failures. "It works in the backend" doesn't give me that much confidence about it working in libpq. I'm all for this as a 9.1 submission, but let's not commit to trying to debug it now. I would like a green buildfarm for awhile before we wrap alpha4, and this sort of untested "it can't hurt" patch is exactly what is likely to make things not green. 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] TCP keepalive support for libpq
2010/2/15 Robert Haas : > On Mon, Feb 15, 2010 at 11:00 AM, Tom Lane wrote: >> Euler Taveira de Oliveira writes: >>> Magnus Hagander escreveu: If we want to do this, I'd be inclined to say we sneak this into 9.0.. It's small enough ;) >>> I'm afraid Robert will say a big NO. ;) I'm not against your idea; so if >>> nobody objects go for it *now*. >> >> If Robert doesn't I will. This was submitted *way* past the appropriate >> deadline; and if it were so critical as all that, why'd we never hear >> any complaints before? > > Agreed. > >> If this were actually a low-risk patch I might think it was okay to try >> to shoehorn it in now; but IME nothing involving making new use of >> system-dependent APIs is ever low-risk. Look at Greg's current >> embarrassment over fsync, a syscall I'm sure he thought he knew all >> about. > > That's why I think we shouldn't change the default behavior, but > exposing a new option that people can use or not as works for them > seems OK. Well, not changing the default will have us with a behaviour that's half-way between what we have now and what we have on the server side. That just seems ugly. Let's just punt the whole thing to 9.1 instead and do it properly 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] TCP keepalive support for libpq
On Mon, Feb 15, 2010 at 11:00 AM, Tom Lane wrote: > Euler Taveira de Oliveira writes: >> Magnus Hagander escreveu: >>> If we want to do this, I'd be inclined to say we sneak this into 9.0.. >>> It's small enough ;) >>> >> I'm afraid Robert will say a big NO. ;) I'm not against your idea; so if >> nobody objects go for it *now*. > > If Robert doesn't I will. This was submitted *way* past the appropriate > deadline; and if it were so critical as all that, why'd we never hear > any complaints before? Agreed. > If this were actually a low-risk patch I might think it was okay to try > to shoehorn it in now; but IME nothing involving making new use of > system-dependent APIs is ever low-risk. Look at Greg's current > embarrassment over fsync, a syscall I'm sure he thought he knew all > about. That's why I think we shouldn't change the default behavior, but exposing a new option that people can use or not as works for them seems OK. ...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] plperl message style on newly added messages
Robert Haas writes:
> On Mon, Feb 15, 2010 at 10:02 AM, Alvaro Herrera
> wrote:
>> I notice that the new plperl error messages are somewhat out of line of
>> our usual style:
>>
>> ereport(ERROR,
>> (errcode(ERRCODE_INTERNAL_ERROR),
>> errmsg("while executing utf8fix"),
>> errdetail("%s", strip_trailing_ws(SvPV_nolen(ERRSV)))
>> ));
>>
>> I think the errdetail field should really be errmsg, and the errdetail
>> should be errcontext.
>>
>> There are several messages like this.
>>
>> Should this be fixed?
> Yes.
Yes. Message style policing is exactly the kind of thing we should be
doing now. While you're at it, get rid of the
errcode(ERRCODE_INTERNAL_ERROR) if you can't find a better errcode ---
that's the default anyway.
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] TCP keepalive support for libpq
Euler Taveira de Oliveira writes: > Magnus Hagander escreveu: >> If we want to do this, I'd be inclined to say we sneak this into 9.0.. >> It's small enough ;) >> > I'm afraid Robert will say a big NO. ;) I'm not against your idea; so if > nobody objects go for it *now*. If Robert doesn't I will. This was submitted *way* past the appropriate deadline; and if it were so critical as all that, why'd we never hear any complaints before? If this were actually a low-risk patch I might think it was okay to try to shoehorn it in now; but IME nothing involving making new use of system-dependent APIs is ever low-risk. Look at Greg's current embarrassment over fsync, a syscall I'm sure he thought he knew all about. 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] Streaming Replication on win32
2010/2/15 Fujii Masao : > On Sun, Feb 14, 2010 at 11:52 PM, Magnus Hagander wrote: >> Remember that the win32 code *always* puts the socket in non-blocking >> mode. So we can't just "teach the layer about it". We need some way to >> pass the information down that this is actually something we want to >> be non-blocking, and it can't be the normal flag on the socket. I >> don't really have an idea of where else we'd put it though :( It's in >> the port structure, but not beyond it. > > Right. > > BTW, pq_getbyte_if_available() always changes the socket to non-blocking > and blocking mode before and after calling secure_read(), respectively. > This code seems wrong on win32. Because, as you said, the socket is always > in non-blocking mode on win32. We should change pq_getbyte_if_available() > so as not to change the socket mode only in win32? Yes. >> What we could do, is have an ugly global flag specifically for the >> use-case we have here. Assuming we do create a plataform specific >> pq_getbyte_if_available(), the code-path that would have trouble now >> would be when we call pq_getbyte_if_available(), and it in turns asks >> the socket if there is data, there is, but we end up calling back into >> the SSL code to fetch the data, and it gets an incomplete packet. >> Correct? So the path is basically: >> >> pq_getbyte_if_available() -> secure_read() -> SSL_read() -> >> my_sock_read() -> pgwin32_recv() >> >> Given that we know we are working on a single socket here, we could >> use a global flag to tell pgwin32_recv() to become nonblocking. We >> could set this flag directly in the win32-specific version of >> pq_getbyte_if_available(), and make sure it's cleared as soon as we >> exit. >> >> It will obviously fail if we do anything on a *different* socket >> during this time, so it has to be set for a very short time. But that >> seems doable. And we don't call any socket stuff from signal handlers >> so that shouldn't cause issues. > > Agreed. Here is the patch which does that (including the above-mentioned > change). I haven't tested it yet because I failed in creating the build > environment for the MSVC :( I'll try to create that again, and test it. > Though I'm not sure how long it takes. I changed your patch to this, because I find it a lot simpler. The change is in the checking in pgwin32_recv - there is no need to ever call waitforsinglesocket, we can just exit out early. Do you see any issue with that? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ pq_getbyte_if_available_on_win32_magnus.patch 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] alpha4 timing (was: Speed up CREATE DATABASE)
Robert Haas writes: > On Mon, Feb 15, 2010 at 6:19 AM, Greg Stark wrote: >> When do we cut the alpha? If I look at it at about 10-11pm EST is that too >> late? > It looks like it's going to take Andrew until tomorrow to commit the > last perl patch, so I think we should plan to cut the alpha on > Wednesday if nothing comes up. Wednesday would be about the earliest possible point --- probably end of the week would be safer. > On a related note, should we go ahead and do the 8.5 -> 9.0 > renumbering at this point? Is someone already working on this? I was planning to do it once the other dust settles. 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] getting version 7.2.8
hudson...@aol.com writes: > I need to get a copy of PostGres source for version 7.2.8. In particular I > need to build PostGres for now unsupported BeOS. I don't see V 7.2.8 on the > download mirror. Can someone please provide info on accessing an older > version? AFAICT from the CVS history, BeOS support was removed in 8.2, so you could use anything up to 8.1.x. Do you really need to use something as obsolete and known-full-of-bugs as 7.2? It *will* eat your data someday. 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] Regression failure on pika caused by CLUSTER rewrite
Greg Stark writes: > In looking through the build farm wreckage caused by fsyncing > directories I noticed this interesting failure on pika: > + ERROR: role "clstr_user" cannot be dropped because some objects depend on > it > + DETAIL: owner of table pg_temp_9.clstr_temp That was fixed ages ago, but pika hasn't rebuilt since then :-( 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] Streaming replication on win32, still broken
With the libpq fixes, I get further (more on that fix later, btw), but now I get stuck in this. When I do something on the master that generates WAL, such as insert a record, and then try to query this on the slave, the walreceiver process crashes with: PANIC: XX000: could not write to log file 0, segment 9 at offset 0, length 160: Invalid argument LOCATION: XLogWalRcvWrite, .\src\backend\replication\walreceiver.c:487 I'll keep digging at the details, but if somebody has a good idea here.. ;) -- 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] TCP keepalive support for libpq
Magnus Hagander escreveu: > If we want to do this, I'd be inclined to say we sneak this into 9.0.. > It's small enough ;) > I'm afraid Robert will say a big NO. ;) I'm not against your idea; so if nobody objects go for it *now*. -- 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] pg_dump sort order for functions
Peter Eisentraut writes: > Since we ran out of time/ideas on this, I would propose just committing > the part that breaks ties based on the number of arguments, which > already solves a large part of the problem (at least in a pre-default > values world) and would very likely be a part of any possible future > utterly complete solution. Seems safe enough. 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] plperl message style on newly added messages
On Mon, Feb 15, 2010 at 10:02 AM, Alvaro Herrera
wrote:
> I notice that the new plperl error messages are somewhat out of line of
> our usual style:
>
> ereport(ERROR,
> (errcode(ERRCODE_INTERNAL_ERROR),
> errmsg("while executing utf8fix"),
> errdetail("%s", strip_trailing_ws(SvPV_nolen(ERRSV)))
> ));
>
> I think the errdetail field should really be errmsg, and the errdetail
> should be errcontext.
>
> There are several messages like this.
>
> Should this be fixed?
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] TCP keepalive support for libpq
On Mon, Feb 15, 2010 at 9:52 AM, Magnus Hagander wrote: > 2010/2/15 Euler Taveira de Oliveira : >> Fujii Masao escreveu: >>> Here is the patch which provides those three parameters as conninfo >>> options. Should this patch be added into the first CommitFest for v9.1? >>> >> Go ahead. > > If we want to do this, I'd be inclined to say we sneak this into 9.0.. > It's small enough ;) I think that's reasonable, provided that we don't change the default behavior. I think it's too late to change the default behavior of much of anything for 9.0, and libpq seems like a particularly delicate place to be changing things. I also think adding three new environment variables for this is likely overkill. I'd rip that part out. Just to be clear, I don't intend to work on this myself. But I am in favor of YOU working on it. :-) ...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] plperl message style on newly added messages
Hi,
I notice that the new plperl error messages are somewhat out of line of
our usual style:
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
errmsg("while executing utf8fix"),
errdetail("%s", strip_trailing_ws(SvPV_nolen(ERRSV)))
));
I think the errdetail field should really be errmsg, and the errdetail
should be errcontext.
There are several messages like this.
Should this be fixed?
--
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] Explain buffers display units.
On Mon, Feb 15, 2010 at 2:22 PM, Robert Haas wrote: >> a) Changed the line description to "Total Buffer Usage" which at least >> hints that it's something more akin to the "Total runtime" listed at >> the bottom than the "actual time". >> >> b) Used units of memory -- I formatted them with 3 significant digits >> (unless the unit is bytes or kB where that would be silly). It's just >> what looked best to my eye. > > I wasn't aware we had consensus on making this change, which I see you > committed less than an hour after posting this. Well there was a 30+ message thread almost a week ago where there seemed to be some contention over the issue of whether the numbers should be averages or totals. But were there was no dispute over the idea of printing in memory units instead of blocks. Given the controversy over whether to display averages or totals and given the issues raised towards the end of the thread that there are no comparable estimated values printed so there's no particular need to average them I opted for the minimal change of just labelling it "Total Buffer Usage". It didn't seem there was consensus to change it to averages per loop or to change the whole plan output to display totals. And I didn't see anyone argue that saying calling out that it was a total was a bad idea. We can always continue tweak the details of the format such as adding spaces before the units to make it similar to the pg_size_pretty(). I'm not sure I like the idea of making it exactly equivalent because pg_size_pretty() doesn't print any decimals so it's pretty imprecise for smaller values. -- 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] TCP keepalive support for libpq
2010/2/15 Euler Taveira de Oliveira : > Fujii Masao escreveu: >> Here is the patch which provides those three parameters as conninfo >> options. Should this patch be added into the first CommitFest for v9.1? >> > Go ahead. If we want to do this, I'd be inclined to say we sneak this into 9.0.. It's small enough ;) -- 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] alpha4 timing (was: Speed up CREATE DATABASE)
Robert Haas escribió: > On Mon, Feb 15, 2010 at 6:19 AM, Greg Stark wrote: > > When do we cut the alpha? If I look at it at about 10-11pm EST is that too > > late? > > It looks like it's going to take Andrew until tomorrow to commit the > last perl patch, so I think we should plan to cut the alpha on > Wednesday if nothing comes up. Hmm, we need a translation update! -- 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] pg_dump sort order for functions
On tis, 2010-01-12 at 16:35 +0200, Peter Eisentraut wrote: > Um, that tag is the "name", and if you change that, the name in CREATE > FUNCTION also changes. I was initially thinking in that direction, but > it seems it won't be feasible without significant refactoring. > > In the mean time, hacking it into the sort function itself as a special > case works out fine, per attached patch. One might frown upon such an > exception, but then again, function overloading is an exception to the > one-name-per-object rule all over the place anyway. ;-) Since we ran out of time/ideas on this, I would propose just committing the part that breaks ties based on the number of arguments, which already solves a large part of the problem (at least in a pre-default values world) and would very likely be a part of any possible future utterly complete solution. -- 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] TCP keepalive support for libpq
Fujii Masao escreveu: > Here is the patch which provides those three parameters as conninfo > options. Should this patch be added into the first CommitFest for v9.1? > Go ahead. -- 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
[HACKERS] ToDo: preload for fulltext dictionary
Hello, Czech users reports a slow first fulltext queries. It is based on using ispell dictionary. The dictionary data could be shared or minimally dictionary could be preloaded like some PL language. What do you think about this? Regards Pavel Stehule -- 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] Output configuration status after ./configure run.
On ons, 2010-02-10 at 18:25 -0200, Euler Taveira de Oliveira wrote: > Alvaro Herrera escreveu: > > The general idea seems sensible to me. I can't comment on the > > specifics. > > > +1. A lot of other programs have this summary at the end of configure > execution. The problem is that PostgreSQL has too many options. Do we want to > list all of them? The reason that a lot of other packages have this sort of display is probably because they use an opportunistic configuration approach, meaning they configure the packages with whatever libraries they happen to find installed at the time. So you don't actually know what you are getting until the end of the configure run. (Clearly, however, a package autobuilder doesn't read that output, so the concept is broken.) PostgreSQL doesn't work that way (for the most part). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] alpha4 timing (was: Speed up CREATE DATABASE)
On Mon, Feb 15, 2010 at 6:19 AM, Greg Stark wrote: > When do we cut the alpha? If I look at it at about 10-11pm EST is that too > late? It looks like it's going to take Andrew until tomorrow to commit the last perl patch, so I think we should plan to cut the alpha on Wednesday if nothing comes up. On a related note, should we go ahead and do the 8.5 -> 9.0 renumbering at this point? Is someone already working on this? ...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] Explain buffers display units.
On Sun, Feb 14, 2010 at 8:25 PM, Greg Stark wrote: > So this is what I did about my two complaints earlier about the > explain buffer patch. > > a) Changed the line description to "Total Buffer Usage" which at least > hints that it's something more akin to the "Total runtime" listed at > the bottom than the "actual time". > > b) Used units of memory -- I formatted them with 3 significant digits > (unless the unit is bytes or kB where that would be silly). It's just > what looked best to my eye. I wasn't aware we had consensus on making this change, which I see you committed less than an hour after posting this. > I'm finding "hit" and "read" kind of confusing myself but don't really > have any better idea. It's not entirely clear whether read is the > total accesses out of which some are cache hits or if they're two > disjoint sets. Keep in mind these terms are taken from other parts of the system where they existed prior to this patch. We probably want to stick with them at this point for consistency, but in any case it's certainly a separate discussion. ...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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
2010/2/15 Greg Stark : > On Mon, Feb 15, 2010 at 11:34 AM, marcin mank wrote: >> LOG: could not link file "pg_xlog/xlogtemp.2367" to >> "pg_xlog/0001" (initialization of log file 0, >> > > This is not related -- it seems your filesystem doesn't support hard > links. I thought we used "junctions" on versions of Windows that > support them which I would have expected would include XP but my > knowledge of Windows is thin and obsolete. Junctions are for symbolic links, and only valid for directories. NTFS has "real" hardlinks though CreateLink(). No idea if that works on remote filesystems though. But AFAIK, we don't use that on Windows. But the rest of the thread has indicated why this shows up anyway :) -- 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] Documentation build issues on Debian/Ubuntu
Greg Smith wrote: > I can't seem to build the PDF version of the documentation on any of > my Ubuntu 9.04 systems, and wonder if there's anything that > can/should should get done about it. Yeah, I'm seeing the same problem here. The strange thing is that 8.4 docs seem to build just fine. -- 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Monday 15 February 2010 14:50:03 marcin mank wrote: > Yes, the issue with initdb failing is unrelated (and I have no problem > about the fs being unsupported). But fsync still DOES fail on > directories from the mount. > > >> But I would not be that sure that eg. NFS or something like that won`t > >> complain. > > > > It does not. > > What if someone mounts a NFS share from a system that does not support > directory fsync (per buildfarm: unixware, AIX) on Linux? I agree that > this is asking for trouble, but... Then nothing. The fsync via nfs or such is a local operation. There is nothing like a "fsync" command transported - i.e. the fsync controls the local cache not the remote one... 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
Yes, the issue with initdb failing is unrelated (and I have no problem about the fs being unsupported). But fsync still DOES fail on directories from the mount. >> But I would not be that sure that eg. NFS or something like that won`t >> complain. > It does not. > What if someone mounts a NFS share from a system that does not support directory fsync (per buildfarm: unixware, AIX) on Linux? I agree that this is asking for trouble, but... Greetings Marcin Mańk -- 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] getting version 7.2.8
On Sun, 2010-02-14 at 23:09 -0500, hudson...@aol.com wrote: > I need to get a copy of PostGres source for version 7.2.8. ftp://ftp-archives.postgresql.org/pub/source/v7.2.8/ Regards, -- Devrim GÜNDÜZ, RHCE PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer 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] getting version 7.2.8
Dear Folks, I need to get a copy of PostGres source for version 7.2.8. In particular I need to build PostGres for now unsupported BeOS. I don't see V 7.2.8 on the download mirror. Can someone please provide info on accessing an older version? Many thanks, Andrew
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Mon, Feb 15, 2010 at 10:51:14AM +, Tim Bunce wrote: > On Sat, Feb 13, 2010 at 02:25:48PM -0800, David E. Wheeler wrote: > > On Feb 12, 2010, at 3:10 PM, Tim Bunce wrote: > > > > > I've appended the POD documentation and attached the (rough but working) > > > test script. > > > > > > I plan to release the module to CPAN in the next week or so. > > > > > > I'd greatly appreciate any feedback. > > > > I like the idea overall, and anything that can simplify the interface is > > more than welcome. However: > > > > * I'd rather not have to specify a signature for a non-polymorphic function. > > The signature doesn't just qualify the selection of the function, > it also ensures appropriate interpretation of the arguments. Just to clarify that... I mean appropriate interpretation not only by PostgreSQL but also by the call() code knowing which arguments may need array encoding (without having to check them all on every call). The signature also makes it easy to refer to functions in other schemas. Something that a SP->func_name(...) style syntax wouldn't allow. Tim. -- 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] Listen / Notify - what to do when the queue is full
On Mon, 2010-02-15 at 12:59 +0100, Joachim Wieland wrote: > On Sun, Feb 14, 2010 at 11:44 PM, Simon Riggs wrote: > > Next set of questions > > > > * Will this work during Hot Standby now? The barrier was that it wrote > > to a table and so we could not allow that. ISTM this new version can and > > should work with Hot Standby. Can you test that and if so, remove the > > explicit barrier code and change tests and docs to enable it? > > I have tested it already. The point where it currently fails is the > following line: > > qe->xid = GetCurrentTransactionId(); > > We record the TransactionId (of the notifying transaction) in the > notification in order to later check if this transaction has committed > successfully or not. If you tell me how we can find this out in HS, we > might be done... > > The reason why we are doing all this is because we fear that we can > not write the notifications to disk once we have committed to clog... > So we write them to disk before committing to clog and therefore need > to record the TransactionId. That's a shame. So it will never work in Hot Standby mode unless you can think of a different way. > > * We also discussed the idea of having a NOTIFY command that would work > > from Primary to Standby. All this would need is some code to WAL log the > > NOTIFY if not in Hot Standby and for some recovery code to send the > > NOTIFY to any listeners on the standby. I would suggest that would be an > > option on NOTIFY to WAL log the notification: > > e.g. NOTIFY me 'with_payload' FOR STANDBY ALSO; > > What should happen if you wanted to replay a NOTIFY WAL record in the > standby but cannot write to the pg_notify/ directory? Same thing that happens to any action that cannot be replayed. Why should that be a problem? -- 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] Listen / Notify - what to do when the queue is full
On Mon, 2010-02-15 at 12:59 +0100, Joachim Wieland wrote: > > * I think it's confusing that pg_notify is both a data structure and > a > > function. Suggest changing one of those to avoid issues in > > understanding. "Use pg_notify" might be confused by a DBA. > > You are talking about the libpq datastructure PGnotify I suppose... I > don't see it overly confusing but I wouldn't object changing it. There > was a previous discussion about the name, see the last paragraph of > http://archives.postgresql.org/message-id/dc7b844e1002021510i4aaa879fy8bbdd003729d2...@mail.gmail.com No, which illustrates the confusion nicely! Function and datastructure. -- 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] Regression failure on pika caused by CLUSTER rewrite
Le 15 févr. 2010 à 12:52, Greg Stark a écrit : In looking through the build farm wreckage caused by fsyncing directories I noticed this interesting failure on pika: == pgsql.13659/src/test/regress/regression.diffs === *** /home/pgbuildfarm/workdir/HEAD/pgsql.13659/src/test/regress/ expected/cluster.outWed Feb 3 00:16:38 2010 --- /home/pgbuildfarm/workdir/HEAD/pgsql.13659/src/test/regress/ results/cluster.outWed Feb 3 19:19:06 2010 *** *** 453,455 --- 453,457 DROP TABLE clstr_2; DROP TABLE clstr_3; DROP USER clstr_user; + ERROR: role "clstr_user" cannot be dropped because some objects depend on it + DETAIL: owner of table pg_temp_9.clstr_temp Hi I think that was fixed some time ago (see "Fix timing-sensitive regression test result..." commit by Tom on 02/03). But pika suffered some connectivity issues then and is only now building current HEAD again. Regards, Rémi Zara -- 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] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Mon, Feb 15, 2010 at 10:42:15AM +, Richard Huxton wrote:
> On 15/02/10 10:32, Tim Bunce wrote:
> >On Mon, Feb 15, 2010 at 07:31:14AM +, Richard Huxton wrote:
> >>
> >>Is there any value in having a two-stage interface?
> >>
> >>$seq_fn = get_call('nextval(regclass)');
> >>$foo1 = $seq_fn->($seq1);
> >>$foo2 = $seq_fn->($seq2);
> >
> >I don't think there's significant performance value in that.
> >
> >Perhaps it could be useful to be able to pre-curry a call and
> >then pass that code ref around, but you can do that trivially
> >already:
> >
> > $nextval_fn = sub { call('nextval(regclass)', @_) };
> > $val = $nextval_fn->($seq1);
> >or
> > $nextfoo_fn = sub { call('nextval(regclass)', 'foo_seqn') };
> > $val = $nextfoo_fn->();
>
> Fair enough. Just wondered whether it was worth putting that on your
> side of the interface. I'm forced to concede you probably have more
> experience in database-related APIs than me :-)
I've actually very little experience with PostgreSQL! I'm happy to argue
each case on its merits and am certainly open to education and persuasion.
At the moment I don't see enough gain to warrant an additional API.
I am adding the some examples to the docs though. So thanks for that!
Tim.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Monday 15 February 2010 12:55:36 Greg Stark wrote: > On Mon, Feb 15, 2010 at 11:50 AM, Andres Freund wrote: > > If I understood him correctly marcin seems to mount a windows share on > > linux via some vbox-proprietary pseudo filesystem. That wont get > > detected and thus no junctions will be used... (I have doubts you even > > can create them via vboxfs (or even smb)). > > I would consider that a unsupported setup. Agreed? > > I'm not sure which versions of Windows we support in general. But on > further thought I thought we only used hard links for xlog files on > systems where we knew they worked and just did a rename() on systems > without them. So I'm puzzled why we're trying to hard link on this > system. Perhaps we need to make this a run-time check instead of just > making it depend on the system. Well, I guess linux is normally a system where hardlinking is considered safe. And I dont really see a problem with that - for example we require ntfs on windows as well... In the end its only some strange filesystem whats causing the issue here... 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] Listen / Notify - what to do when the queue is full
On Sun, Feb 14, 2010 at 11:44 PM, Simon Riggs wrote: > Next set of questions > > * Will this work during Hot Standby now? The barrier was that it wrote > to a table and so we could not allow that. ISTM this new version can and > should work with Hot Standby. Can you test that and if so, remove the > explicit barrier code and change tests and docs to enable it? I have tested it already. The point where it currently fails is the following line: qe->xid = GetCurrentTransactionId(); We record the TransactionId (of the notifying transaction) in the notification in order to later check if this transaction has committed successfully or not. If you tell me how we can find this out in HS, we might be done... The reason why we are doing all this is because we fear that we can not write the notifications to disk once we have committed to clog... So we write them to disk before committing to clog and therefore need to record the TransactionId. > * We also discussed the idea of having a NOTIFY command that would work > from Primary to Standby. All this would need is some code to WAL log the > NOTIFY if not in Hot Standby and for some recovery code to send the > NOTIFY to any listeners on the standby. I would suggest that would be an > option on NOTIFY to WAL log the notification: > e.g. NOTIFY me 'with_payload' FOR STANDBY ALSO; What should happen if you wanted to replay a NOTIFY WAL record in the standby but cannot write to the pg_notify/ directory? > * Don't really like pg_listening() as a name. Perhaps pg_listening_to() > or pg_listening_on() or pg_listening_for() or pg_listening_channels() or > pg_listen_channels() pg_listen_channels() sounds best to me but I leave this decision to a native speaker. > * I think it's confusing that pg_notify is both a data structure and a > function. Suggest changing one of those to avoid issues in > understanding. "Use pg_notify" might be confused by a DBA. You are talking about the libpq datastructure PGnotify I suppose... I don't see it overly confusing but I wouldn't object changing it. There was a previous discussion about the name, see the last paragraph of http://archives.postgresql.org/message-id/dc7b844e1002021510i4aaa879fy8bbdd003729d2...@mail.gmail.com 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Mon, Feb 15, 2010 at 11:50 AM, Andres Freund wrote: > If I understood him correctly marcin seems to mount a windows share on linux > via some vbox-proprietary pseudo filesystem. That wont get detected and thus > no junctions will be used... (I have doubts you even can create them via > vboxfs (or even smb)). > I would consider that a unsupported setup. Agreed? I'm not sure which versions of Windows we support in general. But on further thought I thought we only used hard links for xlog files on systems where we knew they worked and just did a rename() on systems without them. So I'm puzzled why we're trying to hard link on this system. Perhaps we need to make this a run-time check instead of just making it depend on the system. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Regression failure on pika caused by CLUSTER rewrite
In looking through the build farm wreckage caused by fsyncing directories I noticed this interesting failure on pika: == pgsql.13659/src/test/regress/regression.diffs === *** /home/pgbuildfarm/workdir/HEAD/pgsql.13659/src/test/regress/expected/cluster.out Wed Feb 3 00:16:38 2010 --- /home/pgbuildfarm/workdir/HEAD/pgsql.13659/src/test/regress/results/cluster.out Wed Feb 3 19:19:06 2010 *** *** 453,455 --- 453,457 DROP TABLE clstr_2; DROP TABLE clstr_3; DROP USER clstr_user; + ERROR: role "clstr_user" cannot be dropped because some objects depend on it + DETAIL: owner of table pg_temp_9.clstr_temp -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Monday 15 February 2010 12:45:39 Greg Stark wrote: > On Mon, Feb 15, 2010 at 11:34 AM, marcin mank wrote: > > LOG: could not link file "pg_xlog/xlogtemp.2367" to > > "pg_xlog/0001" (initialization of log file 0, > > This is not related -- it seems your filesystem doesn't support hard > links. I thought we used "junctions" on versions of Windows that > support them which I would have expected would include XP but my > knowledge of Windows is thin and obsolete. If I understood him correctly marcin seems to mount a windows share on linux via some vbox-proprietary pseudo filesystem. That wont get detected and thus no junctions will be used... (I have doubts you even can create them via vboxfs (or even smb)). I would consider that a unsupported setup. Agreed? 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Monday 15 February 2010 12:34:44 marcin mank wrote: > On Mon, Feb 15, 2010 at 11:02 AM, Andres Freund wrote: > > Hi Marcin, > > > > Sounds rather unlikely to me. Its likely handled at an upper layer (vfs > > in linux' case) and only overloaded when an optimized implementation is > > available. Which os do you see implementing that only on a part of the > > filesystems? > > I have a Windows XP dev machine, which runs virtualbox, which runs > ubuntu, which mounts a windows directory through vboxfs > btw: 8.4.2 initdb won`t work there too, So this is not a regression. > The error is: > DEBUG: creating and filling new WAL file > LOG: could not link file "pg_xlog/xlogtemp.2367" to > "pg_xlog/0001" (initialization of log file 0, > segment 0): Operation not permitted > FATAL: could not open file "pg_xlog/0001" (log > file 0, segment 0): No such file or directory That does seem to be a different issue. Currently there are no fsyncs on directories at all, so likely your setup is hosed anyway ;-) > But I would not be that sure that eg. NFS or something like that won`t > complain. It does not. > Ignoring the return code seems the right choice. And the error hiding one as well. With delayed allocation you theoretically could error out on fsync with -ENOSPC ... 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Mon, Feb 15, 2010 at 11:34 AM, marcin mank wrote: > LOG: could not link file "pg_xlog/xlogtemp.2367" to > "pg_xlog/0001" (initialization of log file 0, > This is not related -- it seems your filesystem doesn't support hard links. I thought we used "junctions" on versions of Windows that support them which I would have expected would include XP but my knowledge of Windows is thin and obsolete. -- 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] LISTEN/NOTIFY and notification timing guarantees
On Mon, Feb 15, 2010 at 3:31 AM, Tom Lane wrote: > I'm not sure how probable it is that applications might be coded in a > way that relies on the properties lost according to point #2 or #3. Your observations are all correct as far as I can tell. One question regarding #2: Is a client application able to tell whether or not it has received all notifications from one batch? i.e. does PQnotifies() return NULL only when the backend has sent over the complete batch of notifications or could it also return NULL while a batch is still being transmitted but the client-side buffer just happens to be empty? > We could fix #2 by not releasing AsyncQueueLock between pages when > queuing messages. This has no obvious downsides as far as I can see; > if anything it ought to save some cycles and contention. Currently transactions with a small number of notifications can deliver their notifications and then proceed with their commit while transactions with many notifications need to stay there longer, so the current behavior is fair in this respect. Changing the locking strategy makes the small volume transactions wait for the bigger ones. Also currently readers can already start reading while writers are still writing (until they hit the first uncommitted transaction of their database). > I think preserving the > property that self-notifies are delivered immediately upon commit might > be more important than that. Fine with me, sounds reasonable :-) 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] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Mon, Feb 15, 2010 at 11:02 AM, Andres Freund wrote: > Hi Marcin, > > Sounds rather unlikely to me. Its likely handled at an upper layer (vfs in > linux' case) and only overloaded when an optimized implementation is > available. > Which os do you see implementing that only on a part of the filesystems? > I have a Windows XP dev machine, which runs virtualbox, which runs ubuntu, which mounts a windows directory through vboxfs fsync does error out on directories inside that mount. btw: 8.4.2 initdb won`t work there too, So this is not a regression. The error is: DEBUG: creating and filling new WAL file LOG: could not link file "pg_xlog/xlogtemp.2367" to "pg_xlog/0001" (initialization of log file 0, segment 0): Operation not permitted FATAL: could not open file "pg_xlog/0001" (log file 0, segment 0): No such file or directory But I would not be that sure that eg. NFS or something like that won`t complain. Ignoring the return code seems the right choice. Greetings Marcin Mańk -- 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] buildfarm breakage
Andrew Dunstan píše v po 08. 02. 2010 v 20:07 -0500: > > Our Solaris *moth members seem to have stopped building. Have we lost them? Hi Andrew, The answer is not simple. Yes, we lost Solaris 8 and 9 machines which was reinstalled and now they are used for different purpose. It was planned before the April and I announced it long time ago. It unfortunately happed and timing looks strange. And I did not find replacement. I have replacement for nevada/x86 machine already, but I need to setup it which is one item in my very long TODO list :(. Solaris 10 Sparc/x86 and nevada sparc are covered at this moment. Zdenek -- 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] CommitFest Status Summary - 2010-02-14
On Sun, Feb 14, 2010 at 10:14:28PM -0500, Andrew Dunstan wrote:
>
> Robert Haas wrote:
> >We're down to 5 patches remaining, and 1 day remaining, so it's time
> >to try to wrap things up.
> >
> >* Package namespace and Safe init cleanup for plperl. Andrew Dunstan
> >is taking care of this one, I believe.
>
> I will get this in, with changes as discussed recently.
Here's a small extra patch for your consideration.
It addresses a couple of minor loose-ends in plperl:
- move on_proc_exit() call to after the plperl_*_init() calls
so on_proc_exit will only be called if plperl_*_init() succeeds
(else there's a risk of on_proc_exit consuming all the exit hook slots)
- don't allow use of Safe version 2.21 as that's broken for PL/Perl.
Tim.
commit d8c0d4e63c00606db95f95a9c8f2b7ccf3c819b3
Author: Tim Bunce
Date: Mon Feb 15 11:18:07 2010 +
Move on_proc_exit to after init (that may fail). Avoid Safe 2.21.
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index e950222..16d74a7 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -365,8 +365,6 @@ select_perl_context(bool trusted)
{
/* first actual use of a perl interpreter */
- on_proc_exit(plperl_fini, 0);
-
if (trusted)
{
plperl_trusted_init();
@@ -379,6 +377,10 @@ select_perl_context(bool trusted)
plperl_untrusted_interp = plperl_held_interp;
interp_state = INTERP_UNTRUSTED;
}
+
+ /* successfully initialized, so arrange for cleanup */
+ on_proc_exit(plperl_fini, 0);
+
}
else
{
@@ -685,8 +687,9 @@ plperl_trusted_init(void)
/*
* Reject too-old versions of Safe and some others:
* 2.20: http://rt.perl.org/rt3/Ticket/Display.html?id=72068
+ * 2.21: http://rt.perl.org/rt3/Ticket/Display.html?id=72700
*/
- if (safe_version_x100 < 209 || safe_version_x100 == 220)
+ if (safe_version_x100 < 209 || safe_version_x100 == 220 || safe_version_x100 == 221)
{
/* not safe, so disallow all trusted funcs */
eval_pv(PLC_SAFE_BAD, FALSE);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
On Mon, Feb 15, 2010 at 10:02 AM, Andres Freund wrote: > Hi Marcin, > > Sounds rather unlikely to me. Its likely handled at an upper layer (vfs in > linux' case) and only overloaded when an optimized implementation is > available. > Which os do you see implementing that only on a part of the filesystems? > > A runtime check would be creating, fsyncing and deleting a directory for > every directory youre fsyncing because they could be on a different fs... We could just not check the result code of the fsync. Or print a warning the first time and stop trying subsequently. When do we cut the alpha? If I look at it at about 10-11pm EST is that too late? -- 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] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Sat, Feb 13, 2010 at 02:25:48PM -0800, David E. Wheeler wrote:
> On Feb 12, 2010, at 3:10 PM, Tim Bunce wrote:
>
> > I've appended the POD documentation and attached the (rough but working)
> > test script.
> >
> > I plan to release the module to CPAN in the next week or so.
> >
> > I'd greatly appreciate any feedback.
>
> I like the idea overall, and anything that can simplify the interface is more
> than welcome. However:
>
> * I'd rather not have to specify a signature for a non-polymorphic function.
The signature doesn't just qualify the selection of the function,
it also ensures appropriate interpretation of the arguments.
I could allow call('foo', @args), which could be written call(foo => @args),
but what should that mean in terms of the underlying behaviour?
I think there are three practical options:
a) treat it the same as call('foo(unknown...)', @args)
b) treat it the same as call('foo(text...)', @args)
c) instead of using a cached prepared query, build an SQL statement
for every execution, which would naturally have to quote all values:
my $args = join ",", map { ::quote_nullable($_) } @_;
return ::spi_exec_query("select * from $spname($args)");
I suspect there are subtle issues (that I'm unfamilar with) lurking here.
I'd appreciate someone with greater understanding spelling out the issues
and trade-offs in those options.
> * I'd like to be able to use Perl code to call the functions as discussed
> previously, something like:
>
> my $count_sql = SP->tl_activity_stats_sql(
> [ statistic => $stat, person_id => $pid ],
> $debug
> );
>
> For a Polymorphic function, perhaps it could be something like:
>
> my $count = SP->call(
> tl_activity_stats_sql => [qw(text[] int)],
> [ statistic => $stat, person_id => $pid ],
> $debug
> );
>
> The advantage here is that I'm not writing functions inside strings,
Umm,
tl_activity_stats_sql => [qw(text[] int)]
seems to me longer and rather less visually appealing than
'tl_activity_stats_sql(text[], int)'
> and only provide the signature when I need to disambiguate between
> polymorphic variants.
Or need to qualify the type of the argument for some other reason, like
passing an array reference.
But perhaps we can agree on one of the options a/b/c above and then
this issue will be less relevant. It's not like you'd be saving much
typing:
call('tl_activity_stats_sql', @args)
call(tl_activity_stats_sql => @args)
SP->tl_activity_stats_sql(@args)
You could always add a trivial SP::AUTOLOAD wrapper function to your
plperl.on_init code :)
> Anyway, That's just interface arguing. The overall idea is sound and
> very much appreciated.
Thanks!
Tim.
--
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] idle in txn query cancellation
On Monday 15 February 2010 09:47:09 Simon Riggs wrote: > On Sat, 2010-02-13 at 22:37 +0100, Andres Freund wrote: > > On a related note I would also like to get rid of the restriction that > > a normal query cancellation will only be done if no subtransactions > > are stacked. > > But I guess its too late for that? (I have a patch ready, some cleanup > > would be needed) > > The latter works by: > > - adding a explicit error code (which should be done regardless of > > this > > discussion) > > - avoiding to catch such error at a few places (plperl, plpython) > > - recursively aborting the subtransactions once the mainloop is > > reached > > - relying on the fact that the cancellation signal will get resent > > - possibly escalating to a FATAL if nothing happens after a certain > > number of tries > > Such an action needs to have a good, clear theoretical explanation with > it to show that the interaction with savepoints is a good one. I can provide a bit more explanation. The patch (other thread) already added some more comments but its definitely good to explain/define some more. Will post that to the thread with the patch, ok? > I toyed with the idea of a new level between ERROR and FATAL to allow > ERRORs to be handled by savepoints still in all cases. I have a hard time believing that it will help in that situation. Either you allow cleaning up process local resources in PG_TRY/PG_TRY in which situation you cant abort recursively at all places because the catching code block may very well reference resources associated with that snapshot or you abort the process in a way that there are no process local resources. How would the middleway between those work? 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] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 15/02/10 10:32, Tim Bunce wrote:
On Mon, Feb 15, 2010 at 07:31:14AM +, Richard Huxton wrote:
Is there any value in having a two-stage interface?
$seq_fn = get_call('nextval(regclass)');
$foo1 = $seq_fn->($seq1);
$foo2 = $seq_fn->($seq2);
I don't think there's significant performance value in that.
Perhaps it could be useful to be able to pre-curry a call and
then pass that code ref around, but you can do that trivially
already:
$nextval_fn = sub { call('nextval(regclass)', @_) };
$val = $nextval_fn->($seq1);
or
$nextfoo_fn = sub { call('nextval(regclass)', 'foo_seqn') };
$val = $nextfoo_fn->();
Fair enough. Just wondered whether it was worth putting that on your
side of the interface. I'm forced to concede you probably have more
experience in database-related APIs than me :-)
--
Richard Huxton
Archonet Ltd
--
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] idle in txn query cancellation
On Monday 15 February 2010 09:50:08 Simon Riggs wrote: > On Sat, 2010-02-13 at 22:37 +0100, Andres Freund wrote: > > The first patch adds the capability to add a flag to ereport like: > > ereport(ERROR | LOG_NO_CLIENT) > > Tom earlier suggested using COMERROR but thats just a version of LOG > > which doesnt report to the client. The patch makes that to be a > > synonym of LOG | LOG_NO_CLIENT. > > While its not the most pretty API I dont think its that bad because > > the directionality is somewhat a property of the loglevel. Beside it > > would generate a lot of useless noise and breakage. > > > > The second patch changes the FATAL during cancelling an idle in txn > > query into ERROR | LOG_NO_CLIENT. > > To avoid breaking the known state there also may no "ready for query" > > message get sent. The patch ensures that by setting and checking a > > "silent_error_while_idle" variable. > > > > That way the client will not see that an error occured until the next > > command sent but I dont think there is a solution to that in 9.0 > > timeframe if at all. > > > > The patch only adds that for the recovery conflict path for now. > > > > What do you think? Is it worth applying something like that now? If > > yes I would try to test the patch some more (obviously the patch > > survives the regression tests, but they do not seem to check the > > extended query protocol at all). > > I think that is much better than FATAL. If it works I think we should > apply it for this release. It does work for me at least ;-). I only have marginal testing with the extended query protocol though and I think the error message needs to get improved somewhat. I plan to make testing the extended query protocol easier by making pgbench able to restart after a such an error (thats why I like the seperate error code for such cancellations...) The problem with the error message is, that errdetail_abort() uses MyProc- >recoveryConflictPending which is already unset when the errdetail is used. Unless you beat me I plan to provide a patch here (havent looked at how to do so yet though). 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] Listen / Notify - what to do when the queue is full
On Sun, 2010-02-14 at 17:22 +0100, Joachim Wieland wrote: > New patch attached, thanks for the review. Next set of questions * Will this work during Hot Standby now? The barrier was that it wrote to a table and so we could not allow that. ISTM this new version can and should work with Hot Standby. Can you test that and if so, remove the explicit barrier code and change tests and docs to enable it? * We also discussed the idea of having a NOTIFY command that would work from Primary to Standby. All this would need is some code to WAL log the NOTIFY if not in Hot Standby and for some recovery code to send the NOTIFY to any listeners on the standby. I would suggest that would be an option on NOTIFY to WAL log the notification: e.g. NOTIFY me 'with_payload' FOR STANDBY ALSO; * Don't really like pg_listening() as a name. Perhaps pg_listening_to() or pg_listening_on() or pg_listening_for() or pg_listening_channels() or pg_listen_channels() * I think it's confusing that pg_notify is both a data structure and a function. Suggest changing one of those to avoid issues in understanding. "Use pg_notify" might be confused by a DBA. -- 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] idle in txn query cancellation
On Sat, 2010-02-13 at 22:37 +0100, Andres Freund wrote: > The first patch adds the capability to add a flag to ereport like: > ereport(ERROR | LOG_NO_CLIENT) > Tom earlier suggested using COMERROR but thats just a version of LOG > which doesnt report to the client. The patch makes that to be a > synonym of LOG | LOG_NO_CLIENT. > While its not the most pretty API I dont think its that bad because > the directionality is somewhat a property of the loglevel. Beside it > would generate a lot of useless noise and breakage. > > The second patch changes the FATAL during cancelling an idle in txn > query into ERROR | LOG_NO_CLIENT. > To avoid breaking the known state there also may no "ready for query" > message get sent. The patch ensures that by setting and checking a > "silent_error_while_idle" variable. > > That way the client will not see that an error occured until the next > command sent but I dont think there is a solution to that in 9.0 > timeframe if at all. > > The patch only adds that for the recovery conflict path for now. > > What do you think? Is it worth applying something like that now? If > yes I would try to test the patch some more (obviously the patch > survives the regression tests, but they do not seem to check the > extended query protocol at all). I think that is much better than FATAL. If it works I think we should apply it for this release. -- 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
