Re: [HACKERS] streaming replication breaks horribly if master crashes
On 17/06/10 02:40, Greg Stark wrote: On Thu, Jun 17, 2010 at 12:16 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Greg Starkgsst...@mit.edu wrote: TCP keepalives are for detecting broken network connections Yeah. That seems like what we have here. If you shoot the OS in the head, the network connection is broken rather abruptly, without the normal packets exchanged to close the TCP connection. It sounds like it behaves just fine except for not detecting a broken connection. So I think there are two things happening here. If you shut down the master and don't replace it then you'll get no network errors until TCP gives up entirely. Similarly if you pull the network cable or your switch powers off or your routing table becomes messed up, or anything else occurs which prevents packets from getting through then you'll see similar breakage. You wouldn't want your database to suddenly come up as master in such circumstances though when you'll have to fix the problem anyways, doing so won't solve any problems it would just create a second problem. We're not talking about a timeout for promoting standby to master. The problem is that the standby doesn't notice that from the master's point of view, the connection has been broken. Whether it's because of a network error or because the master server crashed doesn't matter, the standby should reconnect in any case. TCP keepalives are a perfect fit, as long as you can tune the keepalive time short enough. Where Short enough is up to the admin to decide depending on the application. Having said that, it would probably make life easier if we implemented an application level heartbeat anyway. Not all OS's allow tuning keepalives. But there's a second case. The Postgres master just stops responding -- perhaps it starts seeing disk errors and becomes stuck in disk-wait or the machine just becomes very heaviliy loaded and Postgres can't get any cycles, or someone attaches to it with gdb, or one of any number of things happen which cause it to stop sending data. In that case replication will not see any data from the master but TCP will never time out because the network is just fine. That's why there needs to be an application level health check if you want to have timeouts. You can't depend on the network layer to detect problems between the application. If the PostgreSQL master stops responding, it's OK for the slave to sit and wait for the master to recover. Reconnecting wouldn't help. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] debug log in pg_archivecleanup
On Wed, Jun 16, 2010 at 12:24 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: Fujii Masao masao.fu...@gmail.com wrote: This is because pg_archivecleanup puts the line break \n in the head of debug message. Why should we do so? --- if (debug) fprintf(stderr, \n%s: removing \%s\, progname, WALFilePath); --- We also need \n at line 308. L.125: fprintf(stderr, \n%s: removing \%s\, progname, WALFilePath); L.308: fprintf(stderr, %s: keep WAL file %s and later, progname, exclusiveCleanupFileName); Yes. What about the attached patch? Note that we don't need a line break at Line 130 because strerror() fills the last %s. L.130: fprintf(stderr, \n%s: ERROR failed to remove \%s\: %s, Right. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center archivecleanup_line_break_v1.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
[HACKERS] Should the JSON datatype be a specialization of text?
Currently, the JSON datatype (repository:
http://git.postgresql.org/gitweb?p=json-datatype.git;a=summary ) is
implemented somewhat like a specialization of TEXT, like XML is. I'm
beginning to question if this is the right way to go. This doesn't
concern whether the JSON datatype should retain the given content
verbatim (e.g. ' string '::JSON doesn't strip spaces) or whether
it should be internally stored using varlena (the same way TEXT is
stored). What I'm talking about revolves around two fundamental
approaches to the API design:
A. JSON as a specialization of TEXT. json('string')::TEXT =
'string'. To unwrap it, you need a special function:
from_json('string')::TEXT .
B. JSON as a naked type. json('string')::TEXT = 'string'. To
unwrap it, simply cast to the desired type.
Early in the discussion of the JSON datatype proposal, we leaned in
favor of approach A (see
http://archives.postgresql.org/pgsql-hackers/2010-04/msg00263.php ).
However, based on input I've received (mainly questions about why
from_json and to_json exist), I'm beginning to think that while
approach A makes more sense from an implementor's perspective,
approach B makes a heck of a lot more sense to users.
Although my code currently implements approach A, I am in favor of approach B.
Arguments I can think of in favor of approach A (keeping JSON as a
specialization of TEXT):
* No surprises when casting between JSON and TEXT. If approach B is
used, 'string'::json would be 'string', but 'string'::json::text
would be 'string'.
* 'null'::json and NULL are distinct. 'null'::json is just a string
containing 'null' and won't ever become NULL unless you explicitly
pass it through from_json. Also, if I'm not mistaken, input functions
can't yield null when given non-null input (see the above link).
* For users who just want to store some JSON-encoded text in a
database for a while, approach A probably makes more sense.
* Is consistent with the XML datatype.
Arguments in favor of approach B (making JSON a naked data type):
* Makes data more accessible. Just cast to the type you need, just
like any other data type. No need to remember to_json and from_json
(though these function names might be used for functions to convert
JSON-formatted TEXT to/from the JSON datatype).
* Is consistent with other programming languages. When you
json_decode something in PHP, you get an object or an array. When you
paste a JSON literal into JavaScript code, you end up with a native
type, not some object you have to convert down to a native type.
Notice how in these programming languages, you typically carry
verbatim JSON texts around as strings, not a special string type that
performs validation.
* JSON was meant to be a format representing types in a programming
language. It has arrays, objects, strings, true, false and null
because JavaScript and many other popular scripting languages have
those.
* Users tend to care more about the underlying data in JSON values
than the notion of JSON-formatted text (though users care about that
too). If a user really wants to treat JSON like text, why not just
use TEXT along with CHECK (json_validate(content)) ? Granted, it's
not as fun :-)
One workaround to the null problem of approach B might be to throw an
error when 'null' is passed to the input function (as in, don't allow
the JSON type to even hold 'null' (though null can be nested within an
array/object)), and have a function for converting text to JSON that
returns NULL if 'null' is given. Note that I am strongly against only
allowing the JSON type to hold objects and arrays, in particular
because it would break being able to extract non-compound values from
JSON trees (e.g. json_get('[0,1,2,3]', '[2]') ). By the way, how hard
would it be to get 'null'::JSON to yield NULL?
Keep in mind there's a chance someone will standardize JSON/SQL in the
future, so more may be at stake here than just PostgreSQL's codebase
and users.
Although text versus naked is a fundamental design aspect of the JSON
datatype, it shouldn't be a blocker for me moving forward with the
project. Most of the code in place and in the works shouldn't be
affected by a transition from approach A to B.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Wed, 16 Jun 2010, Josh Berkus wrote: Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Yes. Currently the only moderators for -announce are Marc and Greg S-M. This means that you can get your announce through quickly if you follow up a posting to that list with a private e-mail to one of them; otherwise, stuff tends to lag for several days. Or there are a couple of pass-throughs, for release announcements and PWN, which are not moderated. I've asked several times that we add additional moderators for -announce. Anyone volunteering ... ? Adding is simple enough ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- 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] ANNOUNCE list
On Thu, 17 Jun 2010, Tatsuo Ishii wrote: On Wed, 2010-06-16 at 10:34 -0700, Josh Berkus wrote: Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Yes. Currently the only moderators for -announce are Marc and Greg S-M. And me, and devrim and a number of others. I think adding new moderators who are regualy reading emails and living in different time zones is an idea. If nobody in +0900 tinme zone(Japan), I'd like to be an additional moderator. Sounds great to me ... please confirm what email address you wish to use for this and I'll get you added ... Thank you ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] trace_recovery_messages
Hi, We should make trace_recovery_messages available only when the WAL_DEBUG macro was defined? Currently it's always available, so the standby seems to call elog() too frequently. 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] ANNOUNCE list
On Wed, 2010-06-16 at 10:34 -0700, Josh Berkus wrote: Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Yes. Currently the only moderators for -announce are Marc and Greg S-M. And me, and devrim and a number of others. I think adding new moderators who are regualy reading emails and living in different time zones is an idea. If nobody in +0900 tinme zone(Japan), I'd like to be an additional moderator. Sounds great to me ... please confirm what email address you wish to use for this and I'll get you added ... Thanks. is...@postgresql.org please. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Partitioning syntax
On Mon, Jan 18, 2010 at 3:55 AM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: Robert Haas robertmh...@gmail.com wrote: A couple of preliminary comments on this: Thanks. The attached is rebased on HEAD, with additional documentation. This one, doesn't apply to head anymore... please update -- Jaime Casanova www.2ndQuadrant.com Soporte y capacitación de PostgreSQL -- 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 breaks horribly if master crashes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Heikki Linnakangas wrote: We're not talking about a timeout for promoting standby to master. The problem is that the standby doesn't notice that from the master's point of view, the connection has been broken. Whether it's because of a network error or because the master server crashed doesn't matter, the standby should reconnect in any case. TCP keepalives are a perfect fit, as long as you can tune the keepalive time short enough. Where Short enough is up to the admin to decide depending on the application. I tested this yesterday and I could not get any reaction from the wal receiver even after using minimal values compared to the default values . The default values in linux for tcp_keepalive_time, tcp_keepalive_intvl and tcp_keepalive_probes are 7200, 75 and 9. I reduced these values to 60, 3, 3 and nothing happened, it continuous with status ESTABLISHED after 60+3*3 seconds. I did not restart the network after I changed these values on the fly via /proc. I wonder if this is the reason the connection didn't die neither with the new keppalive values after the connection was broken. I will check this later today. regards, - -- Rafael Martinez, r.m.guerr...@usit.uio.no Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) iEYEARECAAYFAkwZyJ4ACgkQBhuKQurGihT3kgCgn4iQkZ8YKr/nAk5/QqpwYfnc 4lsAn2CKvgeeIOon+lWRHe908hbJ+zK6 =VymH -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Debug message in RemoveOldXlogFiles
Hi, In the following debug message in RemoveOldXlogFiles(), the variables log and seg don't indicate LSN, so we should use %u instead of %X? elog(DEBUG2, removing WAL segments older than %X/%X, log, seg); I attached the patch to do so. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center remove_old_wal_debug_v1.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 breaks horribly if master crashes
On Thu, Jun 17, 2010 at 4:02 PM, Rafael Martinez r.m.guerr...@usit.uio.no wrote: I tested this yesterday and I could not get any reaction from the wal receiver even after using minimal values compared to the default values . The default values in linux for tcp_keepalive_time, tcp_keepalive_intvl and tcp_keepalive_probes are 7200, 75 and 9. I reduced these values to 60, 3, 3 and nothing happened, it continuous with status ESTABLISHED after 60+3*3 seconds. I did not restart the network after I changed these values on the fly via /proc. I wonder if this is the reason the connection didn't die neither with the new keppalive values after the connection was broken. I will check this later today. Walreceiver uses libpq to communicate with the master. But keepalive is not enabled in libpq currently. That is libpq code doesn't call something like setsockopt(SOL_SOCKET, SO_KEEPALIVE). So even if you change the kernel options for keepalive, it has no effect on walreceiver. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] streaming replication breaks horribly if master crashes
On Thu, Jun 17, 2010 at 09:20, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Jun 17, 2010 at 4:02 PM, Rafael Martinez r.m.guerr...@usit.uio.no wrote: I tested this yesterday and I could not get any reaction from the wal receiver even after using minimal values compared to the default values . The default values in linux for tcp_keepalive_time, tcp_keepalive_intvl and tcp_keepalive_probes are 7200, 75 and 9. I reduced these values to 60, 3, 3 and nothing happened, it continuous with status ESTABLISHED after 60+3*3 seconds. I did not restart the network after I changed these values on the fly via /proc. I wonder if this is the reason the connection didn't die neither with the new keppalive values after the connection was broken. I will check this later today. Walreceiver uses libpq to communicate with the master. But keepalive is not enabled in libpq currently. That is libpq code doesn't call something like setsockopt(SOL_SOCKET, SO_KEEPALIVE). So even if you change the kernel options for keepalive, it has no effect on walreceiver. Yeah, there was a patch submitted for this - I think it's on the CF page for 9.1... I guess if we really need it walreceiver could enable it - just get the socket with PQsocket(). -- 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: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Thu, Jun 17, 2010 at 08:29, Marc G. Fournier scra...@hub.org wrote: On Wed, 16 Jun 2010, Josh Berkus wrote: Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Yes. Currently the only moderators for -announce are Marc and Greg S-M. This means that you can get your announce through quickly if you follow up a posting to that list with a private e-mail to one of them; otherwise, stuff tends to lag for several days. Or there are a couple of pass-throughs, for release announcements and PWN, which are not moderated. I've asked several times that we add additional moderators for -announce. Anyone volunteering ... ? Adding is simple enough ... In principle I would, and I have before, but the way that we do moderation really doesn't scale to multiple moderators, and I'm not willing to expend my time for pointless work. What I'm referring to? The fact that at least last time I was looking at this, most (all other?) moderators *only* approve things. And never reject them, instead letting the timeout take care of things thatn shouldn't be posted. That means that if there are 10 moderators, every one of them needs to look at all the mails and ignore them. In cases of other lists where I moderate, people reject spam when they see it, which means that once I go in there I only see stuff that nobody else has already processed. Which makes for less double (or ten-double) work... (I still do moderate some lists, but they are the smaller and more specific ones for pgeu and such, where there is only one or in worst case two moderators) -- 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] DB crash SOS
Hi all, I use 8.2 on a windows server 2008. Suddenly postgres crashed and I can not do anything. Strange things happend to postgres in the last few weeks. Once, there was so many postgres process, that I could not connect to it with pgAdmin3. It said that too many connections and I had to restart postgres. Than, after a while the postgres service stopped but some process ran and it was still accsessable so the application which used it ran without any problem and I could simply start postgres again. Now something is wrong, I can not make backup and the vacuum stops too. The message is pg_dump: Error message from server: ERROR: could not access status of transaction 3974799 DETAIL: Could not read from file pg_clog/0003 at offset 204800: No error. pg_dump: The command was: COPY public.active_sessions_split (ct_sid, ct_name, ct_pos, ct_val, ct_changed) TO stdout; pg_dump: *** aborted because of error The only one thing I found to correct this is to create a file filled with binary 0 and replace clog/0003. Both files size was the same but I get that errormessage again. I tried even with a bigger empty clog/0003 file but than I get that: pg_dump: Error message from server: ERROR: xlog flush request 0/A19F5BF8 is not satisfied --- flushed only to 0/A02A1AC8 CONTEXT: writing block 1149 of relation 1663/4192208/4192508 pg_dump: The command was: COPY public.history (historyid, adatkod, elemid, userid, ido, actionid, targyid, szuloid, opvalue, longfield, longtext) TO stdout; pg_dump: *** aborted because of error I tried to drop the last few transaction with pg_resetxlog and hoped to save some data but there is again the original error message. I checked the permissions of the whole data dir. The owner of it is postgres and has full permission. Can anyone suggest something? Many data would be lost if I can not repaire that so please! Thanks, fenor -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_dump does not honor namespaces when functions are used in index
Dear hackers,
I have a pretty nasty problem to submit to your careful scrutiny.
Please consider the following piece of SQL code:
CREATE SCHEMA bar;
SET search_path = bar;
CREATE FUNCTION bar() RETURNS text AS $$
BEGIN
RETURN 'foobar';
END
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE SCHEMA foo;
SET search_path = foo;
CREATE FUNCTION foo() RETURNS text AS $$
BEGIN
RETURN bar();
END
$$ LANGUAGE plpgsql IMMUTABLE;
SET search_path = public;
CREATE TABLE foobar (d text);
insert into foobar (d) values ('foobar');
set search_path = public, foo, bar;
CREATE INDEX foobar_d on foobar using btree(foo());
Run this on a newly created database, and dump it with pg_dump. You'll
notice that the dump is unusable. Creating a new database from this
dump will trigger the following error:
ERROR: function bar() does not exist
LINE 1: SELECT bar()
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT bar()
CONTEXT: PL/pgSQL function foo line 2 at RETURN
How can we fix this?
--
Jean-Baptiste Quenot
--
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] Should the JSON datatype be a specialization of text?
On Thu, Jun 17, 2010 at 2:29 AM, Joseph Adams
joeyadams3.14...@gmail.com wrote:
* No surprises when casting between JSON and TEXT. If approach B is
used, 'string'::json would be 'string', but 'string'::json::text
would be 'string'.
As far as I'm concerned, that's a non-starter. It should be legal to
cast text to json, but what it should do is validate that the string
is already legal JSON, not quote it as a string. I think you'll find
that things get pretty horribly messy pretty fast if you do it any
other way. What happens if the user has {1,2,3} in a text column
someplace and wants to tread this as a JSON object?
* 'null'::json and NULL are distinct. 'null'::json is just a string
containing 'null' and won't ever become NULL unless you explicitly
pass it through from_json. Also, if I'm not mistaken, input functions
can't yield null when given non-null input (see the above link).
I believe that keeping a JSON NULL separate from an SQL NULL is
absolutely essential.
By the way, how about posting your code and adding it to the
CommitFest page for others to review? Early feedback is usually good,
where these things are concerned.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
--
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 does not honor namespaces when functions are used in index
On 17 June 2010 12:31, Jean-Baptiste Quenot j...@caraldi.com wrote:
Dear hackers,
I have a pretty nasty problem to submit to your careful scrutiny.
Please consider the following piece of SQL code:
CREATE SCHEMA bar;
SET search_path = bar;
CREATE FUNCTION bar() RETURNS text AS $$
BEGIN
RETURN 'foobar';
END
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE SCHEMA foo;
SET search_path = foo;
CREATE FUNCTION foo() RETURNS text AS $$
BEGIN
RETURN bar();
END
$$ LANGUAGE plpgsql IMMUTABLE;
SET search_path = public;
CREATE TABLE foobar (d text);
insert into foobar (d) values ('foobar');
set search_path = public, foo, bar;
CREATE INDEX foobar_d on foobar using btree(foo());
Run this on a newly created database, and dump it with pg_dump. You'll
notice that the dump is unusable. Creating a new database from this
dump will trigger the following error:
ERROR: function bar() does not exist
LINE 1: SELECT bar()
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT bar()
CONTEXT: PL/pgSQL function foo line 2 at RETURN
How can we fix this?
--
Jean-Baptiste Quenot
--
I think Postgres doesn't check to see whether bar() exists in the current
search path when you create the foo() function, and since it isn't in the
foo() function's search path value, it fails to find the function when you
try to use it. It can probably be fixed (this specific case, not generally)
with:
ALTER FUNCTION foo.foo() SET search_path=foo, bar;
Thom
Re: [HACKERS] DB crash SOS
On Thu, Jun 17, 2010 at 4:39 AM, Felde Norbert feno...@gmail.com wrote: I use 8.2 on a windows server 2008. Suddenly postgres crashed and I can not do anything. Strange things happend to postgres in the last few weeks. Once, there was so many postgres process, that I could not connect to it with pgAdmin3. It said that too many connections and I had to restart postgres. Than, after a while the postgres service stopped but some process ran and it was still accsessable so the application which used it ran without any problem and I could simply start postgres again. Now something is wrong, I can not make backup and the vacuum stops too. The message is pg_dump: Error message from server: ERROR: could not access status of transaction 3974799 DETAIL: Could not read from file pg_clog/0003 at offset 204800: No error. pg_dump: The command was: COPY public.active_sessions_split (ct_sid, ct_name, ct_pos, ct_val, ct_changed) TO stdout; pg_dump: *** aborted because of error The only one thing I found to correct this is to create a file filled with binary 0 and replace clog/0003. Both files size was the same but I get that errormessage again. I tried even with a bigger empty clog/0003 file but than I get that: pg_dump: Error message from server: ERROR: xlog flush request 0/A19F5BF8 is not satisfied --- flushed only to 0/A02A1AC8 CONTEXT: writing block 1149 of relation 1663/4192208/4192508 pg_dump: The command was: COPY public.history (historyid, adatkod, elemid, userid, ido, actionid, targyid, szuloid, opvalue, longfield, longtext) TO stdout; pg_dump: *** aborted because of error I tried to drop the last few transaction with pg_resetxlog and hoped to save some data but there is again the original error message. I checked the permissions of the whole data dir. The owner of it is postgres and has full permission. Can anyone suggest something? Many data would be lost if I can not repaire that so please! First, I'd suggest you make a copy of the database before you do anything else. Second, is it possible your disk filled up at some point during all of this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] modular se-pgsql as proof-of-concept
2010/6/17 KaiGai Kohei kai...@ak.jp.nec.com: I tried to implement a modular se-pgsql as proof-of-concept, using the DML permission check hook which was proposed by Robert Haas. At first, please build and install the latest PostgreSQL with this patch to add a hook on DML permission checks. http://archives.postgresql.org/pgsql-hackers/2010-05/msg01095.php Then, check out the modular se-pgsql, as follows: % svn co http://sepgsql.googlecode.com/svn/trunk/ sepgsql This is a good start - I think with some cleanup this could be committable, though probably it makes sense to wait until after we get the security label infrastructure in. I suspect some code cleanup will be needed; one thing I noticed off the top of my head was that you didn't follow the usual style for installing hook functions in a way that can accomodate multiple hooks. See contrib/auto_explain for an example. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 does not honor namespaces when functions are used in index
On Thu, Jun 17, 2010 at 8:13 AM, Thom Brown thombr...@gmail.com wrote:
On 17 June 2010 12:31, Jean-Baptiste Quenot j...@caraldi.com wrote:
Dear hackers,
I have a pretty nasty problem to submit to your careful scrutiny.
Please consider the following piece of SQL code:
CREATE SCHEMA bar;
SET search_path = bar;
CREATE FUNCTION bar() RETURNS text AS $$
BEGIN
RETURN 'foobar';
END
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE SCHEMA foo;
SET search_path = foo;
CREATE FUNCTION foo() RETURNS text AS $$
BEGIN
RETURN bar();
END
$$ LANGUAGE plpgsql IMMUTABLE;
SET search_path = public;
CREATE TABLE foobar (d text);
insert into foobar (d) values ('foobar');
set search_path = public, foo, bar;
CREATE INDEX foobar_d on foobar using btree(foo());
Run this on a newly created database, and dump it with pg_dump. You'll
notice that the dump is unusable. Creating a new database from this
dump will trigger the following error:
ERROR: function bar() does not exist
LINE 1: SELECT bar()
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT bar()
CONTEXT: PL/pgSQL function foo line 2 at RETURN
How can we fix this?
--
Jean-Baptiste Quenot
--
I think Postgres doesn't check to see whether bar() exists in the current
search path when you create the foo() function, and since it isn't in the
foo() function's search path value, it fails to find the function when you
try to use it. It can probably be fixed (this specific case, not generally)
with:
ALTER FUNCTION foo.foo() SET search_path=foo, bar;
I suppose that the root of the problem here is that foo() is not
really immutable - it gives different results depending on the search
path. It seems like that could bite you in a number of different
ways.
I actually wonder if we shouldn't automatically tag plpgsql functions
with the search_path in effect at the time of their creation (as if
the user had done ALTER FUNCTION ... SET search_path=...whatever the
current search path is...). I suppose the current behavior could
sometimes be useful but on the whole it seems more like a giant
foot-gun which the user oughtn't to get unless they explicitly ask for
it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 What I'm referring to? The fact that at least last time I was looking at this, most (all other?) moderators *only* approve things. And never reject them, instead letting the timeout take care of things thatn shouldn't be posted. Certainly not all, becuase I don't do things that way. And certainly not -announce, because I don't think Marc works that way either. As far as the original complaint, if one suspects something is stuck in the queue, a message to #postgresql might be the quickest way to get someone's attention, followed by an email to -www. But my all means, let's add more mods, especially to -announce as Marc, Rob, and I are all in the same time zone. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201006170920 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkwaIV0ACgkQvJuQZxSWSsiGegCff6KbUMe1QdynDY/PPwd+OYUl mDwAn3FXrbDP9Toa/pOOubMB97WC2YDe =ONPP -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump does not honor namespaces when functions are used in index
On 17 June 2010 14:20, Robert Haas robertmh...@gmail.com wrote:
On Thu, Jun 17, 2010 at 8:13 AM, Thom Brown thombr...@gmail.com wrote:
On 17 June 2010 12:31, Jean-Baptiste Quenot j...@caraldi.com wrote:
Dear hackers,
I have a pretty nasty problem to submit to your careful scrutiny.
Please consider the following piece of SQL code:
CREATE SCHEMA bar;
SET search_path = bar;
CREATE FUNCTION bar() RETURNS text AS $$
BEGIN
RETURN 'foobar';
END
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE SCHEMA foo;
SET search_path = foo;
CREATE FUNCTION foo() RETURNS text AS $$
BEGIN
RETURN bar();
END
$$ LANGUAGE plpgsql IMMUTABLE;
SET search_path = public;
CREATE TABLE foobar (d text);
insert into foobar (d) values ('foobar');
set search_path = public, foo, bar;
CREATE INDEX foobar_d on foobar using btree(foo());
Run this on a newly created database, and dump it with pg_dump. You'll
notice that the dump is unusable. Creating a new database from this
dump will trigger the following error:
ERROR: function bar() does not exist
LINE 1: SELECT bar()
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY: SELECT bar()
CONTEXT: PL/pgSQL function foo line 2 at RETURN
How can we fix this?
--
Jean-Baptiste Quenot
--
I think Postgres doesn't check to see whether bar() exists in the current
search path when you create the foo() function, and since it isn't in the
foo() function's search path value, it fails to find the function when
you
try to use it. It can probably be fixed (this specific case, not
generally)
with:
ALTER FUNCTION foo.foo() SET search_path=foo, bar;
I suppose that the root of the problem here is that foo() is not
really immutable - it gives different results depending on the search
path. It seems like that could bite you in a number of different
ways.
I actually wonder if we shouldn't automatically tag plpgsql functions
with the search_path in effect at the time of their creation (as if
the user had done ALTER FUNCTION ... SET search_path=...whatever the
current search path is...). I suppose the current behavior could
sometimes be useful but on the whole it seems more like a giant
foot-gun which the user oughtn't to get unless they explicitly ask for
it.
That wouldn't solve the problem in the above case since the search path at
the time of declaring the function was incorrect anyway as it didn't cover
the bar schema. It would fix cases where search paths are correctly set
before functions are created though. Unless there's a language-specific
parser to validate the content of functions, typos in function names will
cause the restoration of backups to fail.
Thom
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Thu, 17 Jun 2010, Magnus Hagander wrote: What I'm referring to? The fact that at least last time I was looking at this, most (all other?) moderators *only* approve things. And never reject them, instead letting the timeout take care of things thatn shouldn't be posted. That means that if there are 10 moderators, every one of them needs to look at all the mails and ignore them. In cases of other lists where I moderate, people reject spam when they see it, which means that once I go in there I only see stuff that nobody else has already processed. Which makes for less double (or ten-double) work... I sooo agree here ... and to make matters worse, when I go through all of the groups once a week, I find a half dozen or more postings that 'slipped through the cracks' that should have been approved, but weren't ... but to get there, I have to weed through *hundreds* of postings to find them ... But, I think you and I are exceptions here, in that we use the web interface for moderation, and not just email ... although I'm not sure why its so far to do a 'Reply' and type 'Reject' since ppl have to have already checked the body of the message to now it shouldn't be approved ... most of the work is already done by that point ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- 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 does not honor namespaces when functions are used in index
Robert Haas robertmh...@gmail.com writes: I suppose that the root of the problem here is that foo() is not really immutable - it gives different results depending on the search path. Yeah. The declaration of the function is broken --- it's not pg_dump's fault that the function misbehaves. I actually wonder if we shouldn't automatically tag plpgsql functions with the search_path in effect at the time of their creation (as if the user had done ALTER FUNCTION ... SET search_path=...whatever the current search path is...). That would be extremely expensive and not very backwards-compatible. In the case at hand, just writing RETURN bar.bar(); would be the best-performing solution. 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] Should the JSON datatype be a specialization of text?
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 17, 2010 at 2:29 AM, Joseph Adams joeyadams3.14...@gmail.com wrote: * No surprises when casting between JSON and TEXT. If approach B is used, 'string'::json would be 'string', but 'string'::json::text would be 'string'. As far as I'm concerned, that's a non-starter. It should be legal to cast text to json, but what it should do is validate that the string is already legal JSON, not quote it as a string. I'm not really convinced about that. It seems clear to me that there are two behaviors that we'd like: 1. Take a string that is legal JSON, and make it into a JSON object. 2. Take an arbitrary string (or a number, a bool, etc) and make it a literal value within a JSON object. We can make one of these behaviors be invoked by a cast, and the other by an explicit function call --- the question is which is which. I'm inclined to think that associating #2 with casts might be better, because clearly casting numerics or bools to JSON ought to act like #2. If we do it as you suggest then casting text to JSON behaves differently from casting anything else to JSON. 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] Re: pg_dump does not honor namespaces when functions are used in index
On Thu, Jun 17, 2010 at 4:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: I actually wonder if we shouldn't automatically tag plpgsql functions with the search_path in effect at the time of their creation (as if the user had done ALTER FUNCTION ... SET search_path=...whatever the current search path is...). That would be extremely expensive and not very backwards-compatible. In the case at hand, just writing RETURN bar.bar(); would be the best-performing solution. I wonder if we should have a mode for plpgsql functions where all name lookups are done at definition time So the bar() function would be resolved to bar.bar() and stored that way permanently so that pg_dump dumped the definition as bar.bar(). That would be probably just as good as setting the search path on the function for most users and better for some. It would have the same problem with dynamic sql that a lot of things have though. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [pgsql-www] ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Thu, 2010-06-17 at 13:22 +, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 What I'm referring to? The fact that at least last time I was looking at this, most (all other?) moderators *only* approve things. And never reject them, instead letting the timeout take care of things thatn shouldn't be posted. Certainly not all, becuase I don't do things that way. And certainly not -announce, because I don't think Marc works that way either. Uhh the complaint above is bogus. I know I don't just approve things either. As far as the original complaint, if one suspects something is stuck in the queue, a message to #postgresql might be the quickest way to get someone's attention, followed by an email to -www. Agreed. But my all means, let's add more mods, especially to -announce as Marc, Rob, and I are all in the same time zone. Agreed. Joshua D. Drake -- 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] DB crash SOS
On Jun 17, 2010, at 10:39 , Felde Norbert wrote: I tried even with a bigger empty clog/0003 file but than I get that: pg_dump: Error message from server: ERROR: xlog flush request 0/A19F5BF8 is not satisfied --- flushed only to 0/A02A1AC8 CONTEXT: writing block 1149 of relation 1663/4192208/4192508 pg_dump: The command was: COPY public.history (historyid, adatkod, elemid, userid, ido, actionid, targyid, szuloid, opvalue, longfield, longtext) TO stdout; pg_dump: *** aborted because of error Hm, you could try to make the clog/0003 file 256kB. Thats the maximum size of clog segments. best regards, Florian Pflug -- 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] Should the JSON datatype be a specialization of text?
On Thu, Jun 17, 2010 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 17, 2010 at 2:29 AM, Joseph Adams joeyadams3.14...@gmail.com wrote: * No surprises when casting between JSON and TEXT. If approach B is used, 'string'::json would be 'string', but 'string'::json::text would be 'string'. As far as I'm concerned, that's a non-starter. It should be legal to cast text to json, but what it should do is validate that the string is already legal JSON, not quote it as a string. I'm not really convinced about that. It seems clear to me that there are two behaviors that we'd like: 1. Take a string that is legal JSON, and make it into a JSON object. 2. Take an arbitrary string (or a number, a bool, etc) and make it a literal value within a JSON object. We can make one of these behaviors be invoked by a cast, and the other by an explicit function call --- the question is which is which. Up to this point I agree. I'm inclined to think that associating #2 with casts might be better, because clearly casting numerics or bools to JSON ought to act like #2. If we do it as you suggest then casting text to JSON behaves differently from casting anything else to JSON. I think this is going to turn into a thicket of semantic ambiguity. There are also two things you might want on output - (1) take a JSON object and export it as a string; (2) take a JSON object and extract from it some natively typed thing. So what happens, for example, when someone writes: json 'true' Do they get a JSON boolean or a JSON text? i.e. true or 'true'? Joseph's proposal also involved foo::text::json::text foo::text, which seems pretty ugly to me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 breaks horribly if master crashes
Fujii Masao masao.fu...@gmail.com writes: On Thu, Jun 17, 2010 at 5:26 AM, Robert Haas robertmh...@gmail.com wrote: The real problem here is that we're sending records to the slave which might cease to exist on the master if it unexpectedly reboots. I believe that what we need to do is make sure that the master only sends WAL it has already fsync'd (Tom suggested on another thread that this might be necessary, and I think it's now clear that it is 100% necessary). The attached patch changes walsender so that it always sends WAL up to LogwrtResult.Flush instead of LogwrtResult.Write. Applied, along with some minor comment improvements of my own. 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] hstore == and deprecate =
On Jun 16, 2010, at 4:58 PM, Tom Lane wrote: hstore = text[] is new in 9.0. Wup, sorry, I read this as being the other operator. Nevermind ... (FWIW, I share your dislike of for this operator. I just haven't got a better idea.) There aren't any very good choices. Possible correlates: text[] key_slice := my_hstore - ARRAY['foo', 'bar']; bool has_keys := my_hstore ? ARRAY['foo', 'bar']; text[] keyvals := %% my_hstore; text[] keyvals := %# my_hstore; Frankly, %% and %# are closest, in a sense. But instead of an array, we want to get back an hstore. - and ? are correlates in that their RHSs are arrays. Possible operators to get a slice of the hstore: hstore slice := my_hstore + ARRAY['foo', 'bar']; hstore slice := my_hstore # ARRAY['foo', 'bar']; hstore slice := my_hstore ARRAY['foo', 'bar']; hstore slice := my_hstore ! ARRAY['foo', 'bar']; hstore slice := my_hstore * ARRAY['foo', 'bar']; hstore slice := my_hstore % ARRAY['foo', 'bar']; hstore slice := my_hstore @ ARRAY['foo', 'bar']; hstore slice := my_hstore % ARRAY['foo', 'bar']; hstore slice := my_hstore # ARRAY['foo', 'bar']; hstore slice := my_hstore ARRAY['foo', 'bar']; hstore slice := my_hstore @# ARRAY['foo', 'bar']; Maybe % is good, in that it combines %% and -, in a sense. Or #, which kind of goes along with #=, which also returns an hstore. Anyway, the more I look at it the less I care, as long as *something* works. 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] Keepalive for max_standby_delay
Robert Haas robertmh...@gmail.com writes: On Wed, Jun 9, 2010 at 8:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yes, I'll get with it ... Any update on this? Sorry, I've been a bit distracted by other responsibilities (libtiff security issues for Red Hat, if you must know). I'll get on it shortly. 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] Keepalive for max_standby_delay
On Wed, Jun 16, 2010 at 9:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Jun 9, 2010 at 8:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yes, I'll get with it ... Any update on this? Sorry, I've been a bit distracted by other responsibilities (libtiff security issues for Red Hat, if you must know). I'll get on it shortly. What? You have other things to do besides hack on PostgreSQL? Shocking! :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Should the JSON datatype be a specialization of text?
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 17, 2010 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to think that associating #2 with casts might be better, because clearly casting numerics or bools to JSON ought to act like #2. If we do it as you suggest then casting text to JSON behaves differently from casting anything else to JSON. I think this is going to turn into a thicket of semantic ambiguity. True. Maybe it would be better to *not have* casts as such between JSON and non-text data types, but make you write something like json_literal(numeric) to get a JSON literal representing a value. Then json_literal(text) would do an unsurprising thing (analogous to quote_literal), and we could use the casts between text and json for the behavior where the text is interpreted as a valid JSON object. Joseph's proposal also involved foo::text::json::text foo::text, which seems pretty ugly to me. Agreed, that's not too nice. 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] ANNOUNCE list (was Re: New PGXN Extension site)
Marc Fournier wrote: But, I think you and I are exceptions here, in that we use the web interface for moderation, and not just email ... Is it possible that the ones that use email for moderating the lists have aggressive spam filters? Then they might not receive most of the list postings that should be rejected… (I don't really know how the list-moderating system works, but this occurred to me as a possibility.) -- 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] debug log in pg_archivecleanup
Fujii Masao masao.fu...@gmail.com writes: On Wed, Jun 16, 2010 at 12:24 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: This is because pg_archivecleanup puts the line break \n in the head of debug message. Why should we do so? Yes. What about the attached patch? Applied along with a bit of further editorialization. Note that we don't need a line break at Line 130 because strerror() fills the last %s. L.130: fprintf(stderr, \n%s: ERROR failed to remove \%s\: %s, Right. Huh? strerror() doesn't include a newline. 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] Debug message in RemoveOldXlogFiles
Fujii Masao masao.fu...@gmail.com writes: In the following debug message in RemoveOldXlogFiles(), the variables log and seg don't indicate LSN, so we should use %u instead of %X? elog(DEBUG2, removing WAL segments older than %X/%X, log, seg); I attached the patch to do so. Applied, thanks. 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: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
Excerpts from Marc G. Fournier's message of jue jun 17 10:47:41 -0400 2010: I sooo agree here ... and to make matters worse, when I go through all of the groups once a week, I find a half dozen or more postings that 'slipped through the cracks' that should have been approved, but weren't ... but to get there, I have to weed through *hundreds* of postings to find them ... But, I think you and I are exceptions here, in that we use the web interface for moderation, and not just email ... although I'm not sure why its so far to do a 'Reply' and type 'Reject' since ppl have to have already checked the body of the message to now it shouldn't be approved ... most of the work is already done by that point ... hey, count me as an exception as well. I do reject all spam that gets to me (-hackers and -committers these days only, plus -es-ayuda). I moderate *everything* I get by email -- but I never visit the website. If some stuff is still queued after I go through routine moderation, it's only because I didn't get it (remember there's a setting that says only send to this many moderators, so no one should be swamped). I shared a recipe that allows Mutt to do one-keystroke moderation (saves a lot of work), and I recently wrote another one for Sup-mail which is what I'm currently using -- if anyone is interested in that one, let me know. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] trace_recovery_messages
Fujii Masao masao.fu...@gmail.com writes: We should make trace_recovery_messages available only when the WAL_DEBUG macro was defined? No, because it's used in a lot of other contexts besides that. Currently it's always available, so the standby seems to call elog() too frequently. Where? I don't see very many messages that would actually get emitted at the default setting of the parameter. 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] to enable O_DIRECT within postgresql
Daniel Ng wrote: I am trying to enable the direct IO for the disk-resident hash partitions of hashjoin in postgresql. As Tom already mentioned this isn't working because of alignment issues. I'm not sure what you expect to achieve though. You should be warned that other than the WAL, every experiment I've ever seen that tries to add more direct I/O to the database has failed to improve anything; the result is neither barely noticeable, or a major performance drop. This is particularly futile if you're doing your research on Linux/ext3, where even if your code works delivers a speed up no one will trust it enough to ever merge and deploy it, due to the generally poor quality of that area of the kernel so far. This particular area is magnetic for drawing developer attention as it seems like there's a big win just under the surface if things were improved a bit. There isn't. On operating systems like Solaris where it's possible to prototype here by use mounting options to silently covert parts of the database to direct I/O, experiments in that area have all been disappointing. One of the presentations from Jignesh Shah at Sun covered his experiments in this area, can't seem to find it at the moment but I remember the results were not positive in any way. -- 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] Explicit psqlrc
Excerpts from Mark Wong's message of mié jun 16 23:54:52 -0400 2010: ==Usability review== Read what the patch is supposed to do, and consider: Does the patch actually implement that? How does it play with ON_ERROR_STOP/ROLLBACK? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. 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] Should the JSON datatype be a specialization of text?
On Thu, Jun 17, 2010 at 12:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 17, 2010 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to think that associating #2 with casts might be better, because clearly casting numerics or bools to JSON ought to act like #2. If we do it as you suggest then casting text to JSON behaves differently from casting anything else to JSON. I think this is going to turn into a thicket of semantic ambiguity. True. Maybe it would be better to *not have* casts as such between JSON and non-text data types, but make you write something like json_literal(numeric) to get a JSON literal representing a value. Then json_literal(text) would do an unsurprising thing (analogous to quote_literal), and we could use the casts between text and json for the behavior where the text is interpreted as a valid JSON object. Yep, I agree. Except you need a way to generate not only JSON objects that are quoted strings, but also hashes, arrays, booleans, numbers, and nulls... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
Marc G. Fournier wrote: Anyone volunteering ... ? Adding is simple enough ... I can help with moderating announce, having now gotten used to doing the similar chore for things submitted to the web site for a few months. -- 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] hstore == and deprecate =
On Jun 17, 2010, at 2:56 , David E. Wheeler wrote: On Jun 16, 2010, at 4:58 PM, Tom Lane wrote: hstore = text[] is new in 9.0. Wup, sorry, I read this as being the other operator. Nevermind ... (FWIW, I share your dislike of for this operator. I just haven't got a better idea.) There aren't any very good choices. Since there seems to be no consensus on this, maybe thats a sign that there shouldn't be an operator for this at all. I suggested due due the similarities to ?, but I can see why people object to that - mainly because it looks like an predicate, not like an operation on hstores. How about turning it into a function hstore hstore(hstore, text[]) instead? Could also be hstore_restrict if people think naming it just hstore is ambiguous. best regards, Florian Pflug -- 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] hstore == and deprecate =
Since there are no other votes for that option (or, indeed, any other option), I'm going to go with my original instinct and change hstore = text[] to hstore text[]. Patch to do that is attached. If what that operator is doing is appending an array of text to an Hstore, shouldn't we use || instead? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On Thu, Jun 17, 2010 at 4:39 PM, Josh Berkus j...@agliodbs.com wrote: Since there are no other votes for that option (or, indeed, any other option), I'm going to go with my original instinct and change hstore = text[] to hstore text[]. Patch to do that is attached. If what that operator is doing is appending an array of text to an Hstore, shouldn't we use || instead? It isn't. || already does what you're saying. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] hstore == and deprecate =
On 6/17/10 1:40 PM, Robert Haas wrote: On Thu, Jun 17, 2010 at 4:39 PM, Josh Berkus j...@agliodbs.com wrote: Since there are no other votes for that option (or, indeed, any other option), I'm going to go with my original instinct and change hstore = text[] to hstore text[]. Patch to do that is attached. If what that operator is doing is appending an array of text to an Hstore, shouldn't we use || instead? It isn't. || already does what you're saying. So what *does* it do? OK, so after a brief poll on IRC, one reason you're not getting coherent feedback on this is that few people understand the operators which hstore 9.0 already uses and which are new for 9.0, let alone what new operators are proposed for each thing. I know I've completely lost track, particularly since doc patches haven't kept up with the code changes. I've reread most of this thread and it doesn't help me. On the other hand, maybe less feedback is less bikeshedding. You decide. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On Jun 17, 2010, at 1:50 PM, Josh Berkus wrote: It isn't. || already does what you're saying. So what *does* it do? It returns an hstore that's effectively a slice of another hstore. From the docs (http://developer.postgresql.org/pgdocs/postgres/hstore.html): 'a=1,b=2,c=3'::hstore = ARRAY['b','c','x'] Result is: 'b=2, c=3'::hstore 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] hstore == and deprecate =
On Thu, Jun 17, 2010 at 4:50 PM, Josh Berkus j...@agliodbs.com wrote: On 6/17/10 1:40 PM, Robert Haas wrote: On Thu, Jun 17, 2010 at 4:39 PM, Josh Berkus j...@agliodbs.com wrote: Since there are no other votes for that option (or, indeed, any other option), I'm going to go with my original instinct and change hstore = text[] to hstore text[]. Patch to do that is attached. If what that operator is doing is appending an array of text to an Hstore, shouldn't we use || instead? It isn't. || already does what you're saying. So what *does* it do? OK, so after a brief poll on IRC, one reason you're not getting coherent feedback on this is that few people understand the operators which hstore 9.0 already uses and which are new for 9.0, let alone what new operators are proposed for each thing. I know I've completely lost track, particularly since doc patches haven't kept up with the code changes. I've reread most of this thread and it doesn't help me. On the other hand, maybe less feedback is less bikeshedding. You decide. Well, they are documented, so you can read up on them... http://developer.postgresql.org/pgdocs/postgres/hstore.html This isn't a critical issue in desperate need of community input; we just need to resolve it one way or the other so we can move on to the next thing. I'm still inclined to go ahead and apply the patch I attached upthread, because that is less work for me than doing anything else... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Why aren't master and slave DBs binary identical?
Hackers, We've noticed that checksums and file sizes for the master database, and slave database, even after all transactions have been cleared, are not identical. Why is that? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On 6/17/10 2:03 PM, David E. Wheeler wrote: On Jun 17, 2010, at 1:50 PM, Josh Berkus wrote: It isn't. || already does what you're saying. So what *does* it do? It returns an hstore that's effectively a slice of another hstore. From the docs (http://developer.postgresql.org/pgdocs/postgres/hstore.html): OK, hammered this out on IRC with several Hstore users, and I think the best answer here is consistency. Both with the other hstore operators and with other set types, such as intarray and ltree. Currently for hstore, %% returns a flattened array and %# returns a two-dimensional array. That means that it makes sense that the operator which returns an hstore subset should be something based on %, either %, %% or just %. I vote for % . Stuff we discussed and discarded includes: for two reasons: (a) it looks like a predicate and (b) it's used as intersect for intarray and ltree, and we might want to implement intersect for hstore someday. # because it's used as index for intarray, and thus should more properly be a synonym for - in hstore + because it looks like it ought to be some kind of special incrementor. Using % would also mean that sometime in the future we can implement !% as elements NOT in this list (i.e. ' a = 1, b = 2, c = 5' !% 'a, b' == 'c = 5' ) -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why aren't master and slave DBs binary identical?
Hi, On Friday 18 June 2010 00:22:00 Josh Berkus wrote: We've noticed that checksums and file sizes for the master database, and slave database, even after all transactions have been cleared, are not identical. Why is that? Non Wal-Logged action like visibility bits. 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] Why aren't master and slave DBs binary identical?
Andres Freund and...@anarazel.de writes: On Friday 18 June 2010 00:22:00 Josh Berkus wrote: We've noticed that checksums and file sizes for the master database, and slave database, even after all transactions have been cleared, are not identical. Why is that? Non Wal-Logged action like visibility bits. That wouldn't affect file sizes though. Could we have some specifics? 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] Why aren't master and slave DBs binary identical?
Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Friday 18 June 2010 00:22:00 Josh Berkus wrote: We've noticed that checksums and file sizes for the master database, and slave database, even after all transactions have been cleared, are not identical. Why is that? Non Wal-Logged action like visibility bits. That wouldn't affect file sizes though. Could we have some specifics? I have not noticed different file sizes, only checksums. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
Josh Berkus j...@agliodbs.com writes: Currently for hstore, %% returns a flattened array and %# returns a two-dimensional array. That means that it makes sense that the operator which returns an hstore subset should be something based on %, either %, %% or just %. But %% and %# are prefix operators. Extrapolating from those to an infix operator seems a bit thin. Nonetheless, something using % seems better than something using , for the other reasons you mention. I vote for % . I'd vote for %, out of those. Reason: the operator isn't commutative, in fact left and right inputs aren't even the same datatype, so a glyph that looks asymmetric seems more natural. Using % would also mean that sometime in the future we can implement !% as elements NOT in this list (i.e. ' a = 1, b = 2, c = 5' !% 'a, b' == 'c = 5' ) You can prepend ! to any operator name at all, so that's not much of a differentiator. 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] hstore == and deprecate =
On Jun 17, 2010, at 4:15 PM, Tom Lane wrote: Using % would also mean that sometime in the future we can implement !% as elements NOT in this list (i.e. ' a = 1, b = 2, c = 5' !% 'a, b' == 'c = 5' ) You can prepend ! to any operator name at all, so that's not much of a differentiator. %! then. :-) 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] modular se-pgsql as proof-of-concept
(2010/06/17 21:59), Robert Haas wrote: 2010/6/17 KaiGai Koheikai...@ak.jp.nec.com: I tried to implement a modular se-pgsql as proof-of-concept, using the DML permission check hook which was proposed by Robert Haas. At first, please build and install the latest PostgreSQL with this patch to add a hook on DML permission checks. http://archives.postgresql.org/pgsql-hackers/2010-05/msg01095.php Then, check out the modular se-pgsql, as follows: % svn co http://sepgsql.googlecode.com/svn/trunk/ sepgsql This is a good start - I think with some cleanup this could be committable, though probably it makes sense to wait until after we get the security label infrastructure in. I suspect some code cleanup will be needed; one thing I noticed off the top of my head was that you didn't follow the usual style for installing hook functions in a way that can accomodate multiple hooks. See contrib/auto_explain for an example. Thanks for your comments. I'll fix it later. BTW, I have a question which community (PostgreSQL or SELinux) shall eventually maintain the module, although PostgreSQL provides a set of interfaces for access control modules? I thought SELinux side (mainly I and NEC) will maintain the sepgsql module being suitable for the interfaces. If we need another proof-of-concept module independent from selinux for regression test, at least, it is not a tough work. Thanks, -- KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] trace_recovery_messages
On Fri, Jun 18, 2010 at 2:48 AM, Tom Lane t...@sss.pgh.pa.us wrote: Fujii Masao masao.fu...@gmail.com writes: We should make trace_recovery_messages available only when the WAL_DEBUG macro was defined? No, because it's used in a lot of other contexts besides that. Currently it's always available, so the standby seems to call elog() too frequently. Where? I don't see very many messages that would actually get emitted at the default setting of the parameter. Yes. I was just concerned that frequent calls themselves may increase the overhead. 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] Partitioning syntax
Jaime Casanova ja...@2ndquadrant.com wrote: This one, doesn't apply to head anymore... please update Thank you for reviewing my patch! I attached an updated patch set for partitioning syntax. The latest codes are available at: http://repo.or.cz/w/pgsql-fdw.git (I'm recycling FDW repo for the feature.) * master branch is a copy of postgres' HEAD. * 'partition' branch contains codes for partitioning. The details and discussion for partitioning are in the wiki page: http://wiki.postgresql.org/wiki/Table_partitioning Regards, --- Takahiro Itagaki NTT Open Source Software Center partition_20100618.tar.gz 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
[HACKERS] system views for walsender activity
Hi, We don't have any statistic views for walsenders in SR's master server in 9.0, but such views would be useful to monitor and manage standby servers from the master server. I have two ideas for the solution - adding a new system view or recycling pg_stat_activity: 1. Add another system view for walsenders, ex. pg_stat_replication. It would show pid, remote host, and sent location for each walsender. 2. Make pg_stat_activity to show walsenders. We could use current_query to display walsender-specific information, like: =# SELECT * FROM my_stat_activity ; -[ RECORD 1 ]+- datid| 16384 snip current_query| SELECT * FROM my_stat_activity ; -[ RECORD 2 ]+- datid| 0 datname | procpid | 4300 usesysid | 10 usename | itagaki application_name | client_addr | ::1 client_port | 37710 backend_start| 2010-06-16 16:47:35.646486+09 xact_start | query_start | waiting | f current_query| walsender: sent=0/701AAA8 The attached patch is a WIP codes for the case 2, but it might be better to design management policy via SQL in 9.1 before such detailed implementation. Comments welcome. Regards, --- Takahiro Itagaki NTT Open Source Software Center walsender_activity-20100618.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] hstore == and deprecate =
Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Currently for hstore, %% returns a flattened array and %# returns a two-dimensional array. That means that it makes sense that the operator which returns an hstore subset should be something based on %, either %, %% or just %. But %% and %# are prefix operators. Extrapolating from those to an infix operator seems a bit thin. Nonetheless, something using % seems better than something using , for the other reasons you mention. I vote for % . I'd vote for %, out of those. Reason: the operator isn't commutative, in fact left and right inputs aren't even the same datatype, so a glyph that looks asymmetric seems more natural. I think this bikeshed is going to be more paint than shed. However, I just wondered about | as the operator. Think of the right hand operand as a filter on the hstore, and a pipe seems to work. Lots of operators aren't commutative. Arithmetic % for example ;-) But honestly, I can live with just about anything. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] modular se-pgsql as proof-of-concept
2010/6/17 KaiGai Kohei kai...@ak.jp.nec.com: (2010/06/17 21:59), Robert Haas wrote: 2010/6/17 KaiGai Koheikai...@ak.jp.nec.com: I tried to implement a modular se-pgsql as proof-of-concept, using the DML permission check hook which was proposed by Robert Haas. At first, please build and install the latest PostgreSQL with this patch to add a hook on DML permission checks. http://archives.postgresql.org/pgsql-hackers/2010-05/msg01095.php Then, check out the modular se-pgsql, as follows: % svn co http://sepgsql.googlecode.com/svn/trunk/ sepgsql This is a good start - I think with some cleanup this could be committable, though probably it makes sense to wait until after we get the security label infrastructure in. I suspect some code cleanup will be needed; one thing I noticed off the top of my head was that you didn't follow the usual style for installing hook functions in a way that can accomodate multiple hooks. See contrib/auto_explain for an example. Thanks for your comments. I'll fix it later. BTW, I have a question which community (PostgreSQL or SELinux) shall eventually maintain the module, although PostgreSQL provides a set of interfaces for access control modules? I thought SELinux side (mainly I and NEC) will maintain the sepgsql module being suitable for the interfaces. If we need another proof-of-concept module independent from selinux for regression test, at least, it is not a tough work. I had thought perhaps it would end up as a contrib module, but there are other options. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] hstore == and deprecate =
On Thu, Jun 17, 2010 at 11:04 PM, Andrew Dunstan and...@dunslane.net wrote: I vote for % . I'd vote for %, out of those. Reason: the operator isn't commutative, in fact left and right inputs aren't even the same datatype, so a glyph that looks asymmetric seems more natural. Lots of operators aren't commutative. Arithmetic % for example ;-) I've committed this as % -- if anyone cares about it enough to keep arguing, we can change it again. I think this bikeshed is going to be more paint than shed. You said it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
