[HACKERS] Postgres 9.1 - Release Theme
Following a great deal of discussion, I'm pleased to announce that the PostgreSQL Core team has decided that the major theme for the 9.1 release, due in 2011, will be 'NoSQL'. There is a growing trend towards NoSQL databases, with major sites like Twitter and Facebook utilising them extensively. NoSQL databases often include multi-master replication, clustering and failover features that have long been requested in PostgresSQL, but have been extremely difficult to implement with SQL which has prevented us from advancing Postgree in the way that we'd like. To address this, the intention is to remove SQL support from Postgres, and replace it with a language called 'QUEL'. This will provide us with the flexibility we need to implement the features of modern NoSQL databases. With no SQL support there will obviously be some differences in the query syntax that must be used to access your data. For example, the query: select (e.salary/ (e.age - 18)) as comp from employee as e where e.name = Jones would be rewritten as: range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where e.name = Jones Aggregate syntax in QUEL is particularly powerful. For example, the query: select dept, avg(salary) as avg_salary, sum(salary) as tot_salary from employees group by dept may be written as: range of e is employee retrieve (e.dept, avg_salary = avg(e.salary by e.dept), tot_salary = sum(e.salary by e.dept) ) Note that the grouped column can be specified for each individual aggregate. We will be producing a comprehensive guide to the QUEL syntax to aid with application migration. We appreciate the difficulty that this change may cause some users, but feel we must embrace the NoSQL philosophy in order to remain The world's most advanced Open Source database There's no question that, at 21 years old, the SQL standard is past its prime, said core developer and standards expert Peter Eisentraut. It's time for us to switch to something fresher. I personally would have preferred XSLT, but QUEL is almost as good. Project committer Heikki Linnakangas added: By replacing SQL with QUEL not only will will be able to add new features to Postgres that were previously too difficult, but we'll also increase user loyalty as it'll be much harder for them to change to a different, SQL-based database. That'll be pretty cool. You may also notice that without SQL, the project name is somewhat misleading. To address that, the project name will be changed to 'PostgreQUEL' with the 9.1 release. We expect this will also put an end to the periodic debates on changing the project name. Dave Page On behalf of the PostgreSQL Core Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ECPG check variables hidden by locals v2
Hi,
here's a little beautified patch:
- more logical parameter order in ECPGdump_a_type()
- use mm_strdup() instead of strdup() (I notoriously forget this)
- actually bail out with ET_FATAL if the local variable is
of a different type than the global variable that was used in
the DECLARE in the global scope
Although with this patch we can only detect variables under
DECLARE SECTIONs, so we can't detect the scenario
in the attached test case. Should we? This test code would be
a good candidate for the Underhanded C Contest. :-)
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
diff -dcrp pgsql.orig/src/interfaces/ecpg/preproc/descriptor.c pgsql/src/interfaces/ecpg/preproc/descriptor.c
*** pgsql.orig/src/interfaces/ecpg/preproc/descriptor.c 2010-03-10 14:31:49.0 +0100
--- pgsql/src/interfaces/ecpg/preproc/descriptor.c 2010-04-01 09:48:48.0 +0200
*** output_get_descr(char *desc_name, char *
*** 188,194
break;
}
fprintf(yyout, %s,, get_dtype(results-value));
! ECPGdump_a_type(yyout, v-name, v-type, NULL, NULL, NULL, NULL, make_str(0), NULL, NULL);
}
drop_assignments();
fputs(ECPGd_EODT);\n, yyout);
--- 188,194
break;
}
fprintf(yyout, %s,, get_dtype(results-value));
! ECPGdump_a_type(yyout, v-name, v-type, v-brace_level, NULL, NULL, -1, NULL, NULL, make_str(0), NULL, NULL);
}
drop_assignments();
fputs(ECPGd_EODT);\n, yyout);
*** output_set_descr(char *desc_name, char *
*** 293,299
case ECPGd_length:
case ECPGd_type:
fprintf(yyout, %s,, get_dtype(results-value));
! ECPGdump_a_type(yyout, v-name, v-type, NULL, NULL, NULL, NULL, make_str(0), NULL, NULL);
break;
default:
--- 293,299
case ECPGd_length:
case ECPGd_type:
fprintf(yyout, %s,, get_dtype(results-value));
! ECPGdump_a_type(yyout, v-name, v-type, v-brace_level, NULL, NULL, -1, NULL, NULL, make_str(0), NULL, NULL);
break;
default:
diff -dcrp pgsql.orig/src/interfaces/ecpg/preproc/type.c pgsql/src/interfaces/ecpg/preproc/type.c
*** pgsql.orig/src/interfaces/ecpg/preproc/type.c 2010-03-31 12:44:07.0 +0200
--- pgsql/src/interfaces/ecpg/preproc/type.c 2010-04-01 10:03:00.0 +0200
*** ECPGstruct_member_dup(struct ECPGstruct_
*** 54,60
* if this array does contain a struct again, we have to
* create the struct too
*/
! if (rm-type-u.element-type == ECPGt_struct)
type = ECPGmake_struct_type(rm-type-u.element-u.members, rm-type-u.element-type, rm-type-u.element-type_name, rm-type-u.element-struct_sizeof);
else
type = ECPGmake_array_type(ECPGmake_simple_type(rm-type-u.element-type, rm-type-u.element-size, rm-type-u.element-counter), rm-type-size);
--- 54,60
* if this array does contain a struct again, we have to
* create the struct too
*/
! if (rm-type-u.element-type == ECPGt_struct || rm-type-u.element-type == ECPGt_union)
type = ECPGmake_struct_type(rm-type-u.element-u.members, rm-type-u.element-type, rm-type-u.element-type_name, rm-type-u.element-struct_sizeof);
else
type = ECPGmake_array_type(ECPGmake_simple_type(rm-type-u.element-type, rm-type-u.element-size, rm-type-u.element-counter), rm-type-size);
*** static void ECPGdump_a_struct(FILE *o, c
*** 236,247
struct ECPGtype * type, struct ECPGtype * ind_type, const char *prefix, const char *ind_prefix);
void
! ECPGdump_a_type(FILE *o, const char *name, struct ECPGtype * type,
! const char *ind_name, struct ECPGtype * ind_type,
const char *prefix, const char *ind_prefix,
char *arr_str_siz, const char *struct_sizeof,
const char *ind_struct_sizeof)
{
switch (type-type)
{
case ECPGt_array:
--- 236,282
struct ECPGtype * type, struct ECPGtype * ind_type, const char *prefix, const char *ind_prefix);
void
! ECPGdump_a_type(FILE *o, const char *name, struct ECPGtype * type, const int brace_level,
! const char *ind_name, struct ECPGtype * ind_type, const int ind_brace_level,
const char *prefix, const char *ind_prefix,
char *arr_str_siz, const char *struct_sizeof,
const char *ind_struct_sizeof)
{
+ struct variable *var;
+
+ if (type-type != ECPGt_descriptor type-type != ECPGt_sqlda
+ type-type != ECPGt_char_variable
+ brace_level = 0)
+ {
+ char *str;
+
+ str = mm_strdup(name);
+ var = find_variable(str);
+ free(str);
+
+ if ((var-type-type != type-type) ||
+ (var-type-type_name !type-type_name) ||
+ (!var-type-type_name
Re: [HACKERS] Postgres 9.1 - Release Theme
On 1 April 2010 09:13, Dave Page dp...@postgresql.org wrote: Following a great deal of discussion, I'm pleased to announce that the PostgreSQL Core team has decided that the major theme for the 9.1 release, due in 2011, will be 'NoSQL'. There is a growing trend towards NoSQL databases, with major sites like Twitter and Facebook utilising them extensively. NoSQL databases often include multi-master replication, clustering and failover features that have long been requested in PostgresSQL, but have been extremely difficult to implement with SQL which has prevented us from advancing Postgree in the way that we'd like. To address this, the intention is to remove SQL support from Postgres, and replace it with a language called 'QUEL'. This will provide us with the flexibility we need to implement the features of modern NoSQL databases. With no SQL support there will obviously be some differences in the query syntax that must be used to access your data. For example, the query: select (e.salary/ (e.age - 18)) as comp from employee as e where e.name = Jones would be rewritten as: range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where e.name = Jones Aggregate syntax in QUEL is particularly powerful. For example, the query: select dept, avg(salary) as avg_salary, sum(salary) as tot_salary from employees group by dept may be written as: range of e is employee retrieve (e.dept, avg_salary = avg(e.salary by e.dept), tot_salary = sum(e.salary by e.dept) ) Note that the grouped column can be specified for each individual aggregate. We will be producing a comprehensive guide to the QUEL syntax to aid with application migration. We appreciate the difficulty that this change may cause some users, but feel we must embrace the NoSQL philosophy in order to remain The world's most advanced Open Source database There's no question that, at 21 years old, the SQL standard is past its prime, said core developer and standards expert Peter Eisentraut. It's time for us to switch to something fresher. I personally would have preferred XSLT, but QUEL is almost as good. Project committer Heikki Linnakangas added: By replacing SQL with QUEL not only will will be able to add new features to Postgres that were previously too difficult, but we'll also increase user loyalty as it'll be much harder for them to change to a different, SQL-based database. That'll be pretty cool. You may also notice that without SQL, the project name is somewhat misleading. To address that, the project name will be changed to 'PostgreQUEL' with the 9.1 release. We expect this will also put an end to the periodic debates on changing the project name. Dave Page On behalf of the PostgreSQL Core Team I prefer to dump all my data in a big text file and grep it for the information I need. Thom
Re: [HACKERS] Postgres 9.1 - Release Theme
On Thu, Apr 1, 2010 at 9:30 AM, Thom Brown thombr...@gmail.com wrote: I prefer to dump all my data in a big text file and grep it for the information I need. There's no need to start showing off and get all technical y'know. -- Dave Page EnterpriseDB UK: 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] Problems with variable cursorname in ecpg
On Wed, Mar 31, 2010 at 01:15:20PM +0200, Boszormenyi Zoltan wrote: I was thinking about something like the attached patch. It passes all the regression tests. Thanks. Applied. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, 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: pending patch: Re: [HACKERS] HS/SR and smart shutdown
On Thu, Apr 1, 2010 at 12:16 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 31, 2010 at 5:02 AM, Simon Riggs si...@2ndquadrant.com wrote: From what I have seen, the comment about PM_WAIT_BACKENDS is incorrect. backends might be waiting for the WAL record that conflicts with their queries to be replayed. Recovery sometimes waits for backends, but backends never wait for recovery. Really? As Heikki explained before, backends might wait for the lock taken by the startup process. http://archives.postgresql.org/pgsql-hackers/2010-01/msg02984.php Backends wait for locks, yes, but they could be waiting for user locks also. That is not waiting for the WAL record, that concept does not exist. Hmm... this is a good point, on two levels. First, the comment is not as well-phrased as it could be. Second, I wonder why we can't kill the startup process and WAL receiver right away, and then wait for the backends to die off afterwards. I tested whether killing the startup process and walreceiver releases the lock which the backends are waiting for. Unfortunately it doesn't, and the backends have gotten stuck in my box. The behavior which the startup process shuts down without releasing the lock is a bug? BTW, I tested that by compiling postgres with the attached patch and doing the following operations. 1. Make the SR environment 2. Issue some SQLs to the primary psql -h primary server =# CREATE TABLE t(i int); =# BEGIN; =# DROP TABLE t; =# SELECT pg_switch_xlog(); (keep this session alive) 3. Issue some SQLs to the standby psql -h standby server =# BEGIN; =# SELECT * FROM t; -- waiting 4. Perform smart shutdown on the standby Then the startup process and walreceiver shut down, but the session created in #3 is still waiting. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center shutdown_test.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] Postgres 9.1 - Release Theme
Ciao Dave, You may also notice that without SQL, the project name is somewhat misleading. To address that, the project name will be changed to 'PostgreQUEL' with the 9.1 release. We expect this will also put an end to the periodic debates on changing the project name. Ahahahah ... nice fish! ;) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgres 9.1 - Release Theme
Nice to hear and thumbs up! I've just start planning to migrate one of my telco 3Tb database running blunt oracle to coachDb but now of course postgres looks better. Hopefully stupid transactions will be abrogated to wbr Ilya On Thu, Apr 1, 2010 at 12:33 PM, Dave Page dp...@postgresql.org wrote: On Thu, Apr 1, 2010 at 9:30 AM, Thom Brown thombr...@gmail.com wrote: I prefer to dump all my data in a big text file and grep it for the information I need. There's no need to start showing off and get all technical y'know. -- Dave Page EnterpriseDB UK: 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature request - function-based deferrable uniques.
On 31 March 2010 06:58, Dmitry Fefelov fo...@ac-sw.com wrote:
For now Postgres able to create deferrable uniques with following syntax:
...
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [,
... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON
UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
So, deferrable uniques now can be based on column/columns list only. It will
be very useful if there will be possibility to specify functions in this list.
Is it possible?
It's not currently possible using the unique constraint syntax, but I
think that it would be nice to extend this syntax to support this. I
don't think the SQL spec says anything about this, but I think it
would be a useful extension.
Note, however, that there is a workaround to achieve this, which is to
use exclusion constraints. For example:
create table foo(a text, constraint c exclude ((lower(a)) with =)
deferrable initially deferred);
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sorry, too many standbys already vs. MaxWalSenders vs. max_wal_senders
On 1 April 2010 01:51, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 31, 2010 at 11:24 AM, Thom Brown thombr...@gmail.com wrote: [patch] As a general rule, I really appreciate people being willing to take the time to put proposed changes into patch form, even if they're small, but this three-line patch contains two bugs. :-( Thanks for your many typo corrections, though! ...Robert Or my changes were so good, the world wasn't ready for it? Yes, I think that was it.
Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme
2010/4/1 Thom Brown thombr...@gmail.com: On 1 April 2010 09:13, Dave Page dp...@postgresql.org wrote: Following a great deal of discussion, I'm pleased to announce that the PostgreSQL Core team has decided that the major theme for the 9.1 release, due in 2011, will be 'NoSQL'. There is a growing trend towards NoSQL databases, with major sites like Twitter and Facebook utilising them extensively. NoSQL databases often include multi-master replication, clustering and failover features that have long been requested in PostgresSQL, but have been extremely difficult to implement with SQL which has prevented us from advancing Postgree in the way that we'd like. To address this, the intention is to remove SQL support from Postgres, and replace it with a language called 'QUEL'. This will provide us with the flexibility we need to implement the features of modern NoSQL databases. With no SQL support there will obviously be some differences in the query syntax that must be used to access your data. For example, the query: select (e.salary/ (e.age - 18)) as comp from employee as e where e.name = Jones would be rewritten as: range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where e.name = Jones Aggregate syntax in QUEL is particularly powerful. For example, the query: select dept, avg(salary) as avg_salary, sum(salary) as tot_salary from employees group by dept may be written as: range of e is employee retrieve (e.dept, avg_salary = avg(e.salary by e.dept), tot_salary = sum(e.salary by e.dept) ) Note that the grouped column can be specified for each individual aggregate. We will be producing a comprehensive guide to the QUEL syntax to aid with application migration. We appreciate the difficulty that this change may cause some users, but feel we must embrace the NoSQL philosophy in order to remain The world's most advanced Open Source database There's no question that, at 21 years old, the SQL standard is past its prime, said core developer and standards expert Peter Eisentraut. It's time for us to switch to something fresher. I personally would have preferred XSLT, but QUEL is almost as good. Project committer Heikki Linnakangas added: By replacing SQL with QUEL not only will will be able to add new features to Postgres that were previously too difficult, but we'll also increase user loyalty as it'll be much harder for them to change to a different, SQL-based database. That'll be pretty cool. You may also notice that without SQL, the project name is somewhat misleading. To address that, the project name will be changed to 'PostgreQUEL' with the 9.1 release. We expect this will also put an end to the periodic debates on changing the project name. Dave Page On behalf of the PostgreSQL Core Team I prefer to dump all my data in a big text file and grep it for the information I need. As long as you implement your own grep, that sounds about on par with the current trends! Go for it! -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pending patch: Re: [HACKERS] HS/SR and smart shutdown
On Thu, Apr 1, 2010 at 4:42 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Apr 1, 2010 at 12:16 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 31, 2010 at 5:02 AM, Simon Riggs si...@2ndquadrant.com wrote: From what I have seen, the comment about PM_WAIT_BACKENDS is incorrect. backends might be waiting for the WAL record that conflicts with their queries to be replayed. Recovery sometimes waits for backends, but backends never wait for recovery. Really? As Heikki explained before, backends might wait for the lock taken by the startup process. http://archives.postgresql.org/pgsql-hackers/2010-01/msg02984.php Backends wait for locks, yes, but they could be waiting for user locks also. That is not waiting for the WAL record, that concept does not exist. Hmm... this is a good point, on two levels. First, the comment is not as well-phrased as it could be. Second, I wonder why we can't kill the startup process and WAL receiver right away, and then wait for the backends to die off afterwards. I tested whether killing the startup process and walreceiver releases the lock which the backends are waiting for. Unfortunately it doesn't, and the backends have gotten stuck in my box. The behavior which the startup process shuts down without releasing the lock is a bug? I think that what this shows is that the original design of Hot Standby didn't contemplate ever having Hot Standby up without the startup process running. In retrospect, maybe we want to allow that, because a smart shutdown would be more likely to complete in a timely fashion if we stopped replication first and then waited for the backends to die rather than waiting for the backends to die first and then stopping replication. That's because, for so long as replication continues, it may take new locks as well as releasing old ones, to say nothing of using other system resources like CPU and I/O bandwidth. But, for 9.0, I'm not sure we have any real choice, unless making the startup process release locks when it goes away is a very simple change. Assuming that's not the case, I think we should apply this patch with some updates to the comments, document how it works and that it may change in a future release, and add a TODO for 9.1. Thoughts? ...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: pending patch: Re: [HACKERS] HS/SR and smart shutdown
On Thu, 2010-04-01 at 06:48 -0400, Robert Haas wrote: On Thu, Apr 1, 2010 at 4:42 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Apr 1, 2010 at 12:16 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 31, 2010 at 5:02 AM, Simon Riggs si...@2ndquadrant.com wrote: From what I have seen, the comment about PM_WAIT_BACKENDS is incorrect. backends might be waiting for the WAL record that conflicts with their queries to be replayed. Recovery sometimes waits for backends, but backends never wait for recovery. Really? As Heikki explained before, backends might wait for the lock taken by the startup process. http://archives.postgresql.org/pgsql-hackers/2010-01/msg02984.php Backends wait for locks, yes, but they could be waiting for user locks also. That is not waiting for the WAL record, that concept does not exist. Hmm... this is a good point, on two levels. First, the comment is not as well-phrased as it could be. Second, I wonder why we can't kill the startup process and WAL receiver right away, and then wait for the backends to die off afterwards. I tested whether killing the startup process and walreceiver releases the lock which the backends are waiting for. Unfortunately it doesn't, and the backends have gotten stuck in my box. The behavior which the startup process shuts down without releasing the lock is a bug? I think that what this shows is that the original design of Hot Standby didn't contemplate ever having Hot Standby up without the startup process running. In retrospect, maybe we want to allow that, because a smart shutdown would be more likely to complete in a timely fashion if we stopped replication first and then waited for the backends to die rather than waiting for the backends to die first and then stopping replication. That's because, for so long as replication continues, it may take new locks as well as releasing old ones, to say nothing of using other system resources like CPU and I/O bandwidth. But, for 9.0, I'm not sure we have any real choice, unless making the startup process release locks when it goes away is a very simple change. Assuming that's not the case, I think we should apply this patch with some updates to the comments, document how it works and that it may change in a future release, and add a TODO for 9.1. I'm not willing to investigate this further myself at this stage. This looks like risk for little benefit. -- 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: pending patch: Re: [HACKERS] HS/SR and smart shutdown
On Thu, Apr 1, 2010 at 7:18 AM, Simon Riggs si...@2ndquadrant.com wrote: I'm not willing to investigate this further myself at this stage. This looks like risk for little benefit. That's kind of what I figured. I'll see about fixing up Fujii-san's patch and documenting the behavior; but it won't happen before the weekend because I'm going to be out of town. ...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 and PARAM_EXEC
Tom Lane wrote:
In principle it could look something like
(SubPlan N ($0 := b.oid))
but with a few parameters and a bunch of other stuff on the same line
that would get out of hand.
The patch I submitted to implement this, hits bogus varno:65001 in get_variable
when I explain the following query from the aggregates.sql regression
test.
select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) =
b.four);
The explain without the subplan argument-patch shows
regression=# explain verbose select ten, sum(distinct four) from
onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) =
b.four);
QUERY
PLAN
--
GroupAggregate (cost=197.83..216.52 rows=10 width=8)
Output: a.ten, sum(DISTINCT a.four)
Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
- Sort (cost=197.83..200.33 rows=1000 width=8)
Output: a.ten, a.four
Sort Key: a.ten
- Seq Scan on public.onek a (cost=0.00..148.00 rows=1000
width=8)
Output: a.ten, a.four
SubPlan 1
- Seq Scan on public.onek b (cost=0.00..150.50 rows=250
width=0)
Filter: ($0 = b.four)
SubPlan 2
- Seq Scan on public.onek b (cost=0.00..148.00 rows=1000
width=4)
Output: b.four
(14 rows)
The subplan argument list contains a aggref node, with a var argument
that has the 65001 varno. Apparently the OUTER varno is set by
search_indexed_tlist_for_var and it seems like correct behaviour, see
stack trace below.
I'm unsure what is a good approach to solve this problem: let
get_variable not give an error in this case?
regards,
Yeb Havinga
#0 search_indexed_tlist_for_var (var=0xe32dc0, itlist=0xe67290,
newvarno=65001, rtoffset=0)
at setrefs.c:1568
#1 0x00697581 in fix_upper_expr_mutator (node=0xe32dc0,
context=0x7fffabcde850)
at setrefs.c:1853
#2 0x00697529 in fix_upper_expr (glob=0xe77178, node=0xe32dc0,
subplan_itlist=0xe67290,
rtoffset=0) at setrefs.c:1839
#3 0x00696b6e in set_upper_references (glob=0xe77178,
plan=0xe66f80, rtoffset=0)
postgres=#
explain select oid::int + 1,(select oid from pg_class a where a.oid =
b.relfilenode and a.relnamespace = b.relnamespace) from pg_class b;
QUERY
PLAN
-
Seq Scan on pg_class b (cost=0.00..2459.64 rows=296 width=12)
SubPlan 1 ($0 := b.relfilenode, $1 := b.relnamespace)
- Index Scan using pg_class_oid_index on pg_class a
(cost=0.00..8.27 rows=1 width=4)
Index Cond: (oid = $0)
Filter: (relnamespace = $1)
(5 rows)
index 56d9c5b..454d59b 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1686,20 +1686,47 @@ static void
ExplainSubPlans(List *plans, const char *relationship, ExplainState
*es)
{
ListCell *lst;
foreach(lst, plans)
{
SubPlanState *sps = (SubPlanState *) lfirst(lst);
- SubPlan *sp = (SubPlan *) sps-xprstate.expr;
+ SubPlan *sp = (SubPlan *) sps-xprstate.expr;
+ StringInfo signature = makeStringInfo();
+ int i = 0;
+ List *context;
+ bool useprefix;
+ ListCell *c;
+
+ context = deparse_context_for_plan((Node
*)exec_subplan_get_plan(es-pstmt, sp),
+
NULL,
+
es-rtable,
+
es-pstmt-subplans);
+ useprefix = list_length(es-rtable) 1;
+
+ appendStringInfoString(signature, sp-plan_name);
+
+ foreach(c, sp-args)
+ {
+ Node *n = lfirst(c);
+ appendStringInfo(signature, "%s$%d := %s",
+ (i == 0) ? "
(" : ", ",
+ i,
+
deparse_expression(n, context, useprefix, true));
+ i++;
+ }
+
+ if (i 0)
+ appendStringInfoString(signature, ")");
ExplainNode(exec_subplan_get_plan(es-pstmt, sp),
sps-planstate,
NULL,
- relationship, sp-plan_name,
+ relationship,
+ signature-data,
es);
}
}
/*
* Explain a property, such as sort keys or targets, that takes the
form of
* a list of unlabeled items. "data" is a list of C strings.
Re: [HACKERS] [DOCS] Streaming replication document improvements
Fujii Masao wrote: On Thu, Apr 1, 2010 at 11:00 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 31, 2010 at 9:58 PM, Fujii Masao masao.fu...@gmail.com wrote: You mean that we should change replication connection not to consume superuser_reserved_connections slots in 9.0? Yes. I think it's good that walsenders can use the superuser reserved slots, that way a client that opens max_connections connections can't block out standby servers from connecting. Preventing superuser connections from consuming superuser_reserved_connections slots seems strange for me. So I'm leaning toward just removing superuser privilege from replication connection again. Thought? That would be good, but I fear it's a bigger change than we should be doing at this point. How about we adjust the backends math a bit: Currently: ReservedBackends = superuser_reserved_connections MaxBackends = max_connections + autovacuum_max_workers + 1; Proposal: ReservedBackends = superuser_reserved_connections + max_wal_senders MaxBackends = max_connections + autovacuum_max_workers + max_wal_senders + 1 So we implicitly reserve a slot and a superuser reserved slot for each walsender. Walsenders use the slots reserved for superusers, but if you set superuser_reserved_connections=3, there's still always at least three slots available for superuser to log in with psql, even if the maximum number of walsenders are connected. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] patch: preload dictionary new version
Hello this version has enhanced AllocSet allocator - it can use a mmap API. Regards Pavel Stehule preload.diff 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] Alpha release this week?
On Thu, Apr 1, 2010 at 5:24 AM, Robert Haas robertmh...@gmail.com wrote: This stuff is now also at: ftp://developer.postgresql.org/pub/source/9.0alpha5/ Thanks Robert. We're working on this, but it seems that changes in the PG build have broken the debugger again. Hopefully we can get it sorted before the holidays start tomorrow. -- Dave Page EnterpriseDB UK: 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] Parallel pg_dump for 9.1
On Mar 30, 2010, at 8:15 AM, Stefan Kaltenbrunner wrote: Peter Eisentraut wrote: On tis, 2010-03-30 at 08:39 +0200, Stefan Kaltenbrunner wrote: on fast systems pg_dump is completely CPU bottlenecked Might be useful to profile why that is. I don't think pg_dump has historically been developed with CPU efficiency in mind. It's not pg_dump that is the problem - it is COPY that is the limit. In my specific case als the fact that a lot of the columns are bytea adds to the horrible CPU overhead (fixed in 9.0). Still our bulk load unload performance is still way slower on a per core comparision than a lot of other databases :( Don't forget the zlib compression used in -Fc (unless you use -Z0) takes a fair amount of cpu too. I did some tests and it turned out that -Z0 actually took longer than - Z1 simply because there was a lot more data to write out, thus I became IO bound not CPU bound. There's a thing called pigz around that is a parallel gzip implementation - wonder how much of that could be adapted to pg_dumps use as compression does use a considerable amount of time (even at - Z1). The biggest problem I can immediately see is that it uses threads. -- Jeff Trout j...@jefftrout.com http://www.stuarthamm.net/ http://www.dellsmartexitin.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] missing schema qualifications in psql
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 While psql is careful to schema-qualify all references to built-in objects (pg_catalog.*), it completely neglects to do this for built-in operators, which can lead to surprising misbehaviors when users have created custom operators. Here is a patch to address that. It will need a bit of testing. Er...wouldn't this only be a problem if someone creates custom operators *and* forces pg_catalog to the end of the search_path? Wouldn't an easier solution be to prepend pg_catalog to the search_path when doing backslash commands? - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201004010941 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAku0oqQACgkQvJuQZxSWSsiJmACghUKR/i+uyJ2n+beuAid4w432 s0oAnArOf1npuMF/7QJ87ZVmSYMlJTRp =/+yd -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] Parallel pg_dump for 9.1
Jeff wrote: On Mar 30, 2010, at 8:15 AM, Stefan Kaltenbrunner wrote: Peter Eisentraut wrote: On tis, 2010-03-30 at 08:39 +0200, Stefan Kaltenbrunner wrote: on fast systems pg_dump is completely CPU bottlenecked Might be useful to profile why that is. I don't think pg_dump has historically been developed with CPU efficiency in mind. It's not pg_dump that is the problem - it is COPY that is the limit. In my specific case als the fact that a lot of the columns are bytea adds to the horrible CPU overhead (fixed in 9.0). Still our bulk load unload performance is still way slower on a per core comparision than a lot of other databases :( Don't forget the zlib compression used in -Fc (unless you use -Z0) takes a fair amount of cpu too. I did some tests and it turned out that -Z0 actually took longer than -Z1 simply because there was a lot more data to write out, thus I became IO bound not CPU bound. There's a thing called pigz around that is a parallel gzip implementation - wonder how much of that could be adapted to pg_dumps use as compression does use a considerable amount of time (even at -Z1). The biggest problem I can immediately see is that it uses threads. all my numbers are with -Z0 and it is the backend (COPY and/or index creation) that is the limit. If you start using compression you are shifting the load to pg_dump. 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] missing schema qualifications in psql
Peter Eisentraut pete...@gmx.net writes: While psql is careful to schema-qualify all references to built-in objects (pg_catalog.*), it completely neglects to do this for built-in operators, That's intentional because of the utter lack of readability that results if you try to use OPERATOR() everywhere ... 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: [SPAM]Re: [HACKERS] Questions about 9.0 release note
Bruce Momjian br...@momjian.us writes: + Exclusion constraints ensure that if any two rows are compared on + the specified columns or expressions using the specified operators, + at least one of these operator comparisons will be false. The syntax is: Isn't that phrasing outright incorrect? Consider nulls. 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] [DOCS] Streaming replication document improvements
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Fujii Masao wrote: Preventing superuser connections from consuming superuser_reserved_connections slots seems strange for me. So I'm leaning toward just removing superuser privilege from replication connection again. Thought? That would be good, but I fear it's a bigger change than we should be doing at this point. Exactly. Despite Robert's unhappiness, NONE of this should get changed right now. When and if we create a separate replication privilege, it'll be time enough to revisit the connection limit arithmetic. 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] Feature request - function-based deferrable uniques.
Dmitry Fefelov wrote: So, deferrable uniques now can be based on column/columns list only. It will be very useful if there will be possibility to specify functions in this list. Is it possible? Sure -- use CREATE UNIQUE INDEX. alvherre=# create function singleton(int) returns int immutable language sql as $$ select 1 $$; CREATE FUNCTION alvherre=# create table singleton (a int); CREATE TABLE alvherre=# create unique index only_one on singleton (singleton(a)); CREATE INDEX alvherre=# insert into singleton values (3); INSERT 0 1 alvherre=# insert into singleton values (6); ERROR: llave duplicada viola restricción de unicidad «only_one» The reason it's not supported in table_constraint, IIUC, is that the standard doesn't allow it, and that syntax is standards-mandated. CREATE INDEX, however, is not. -- 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] Alias to rollback keyword
On Wed, Mar 31, 2010 at 10:34:41PM -0400, Bruce Momjian wrote: Matthew Altus wrote: Hey, After dealing with a production fault and having to rollback all the time, I kept typing a different word instead of rollback. So I created a patch to accept this word as an alias for rollback. Obviously it's not part of the sql standard, but could be a nice extension for postgresql. See the patch for more details. Applied. Hmm, a careful review of the patch leads me to believe that this is one of the few times that a keyword might benefit from localization. Seems l10n efforts in this area have mostly focused on the filtering case, but I'm sure we can repurose such lists. Licensing might be an issue. Clearly this needs to be controlled by the client locale, not the server. Any need for a guc? Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature request - function-based deferrable uniques.
Alvaro Herrera alvhe...@commandprompt.com writes: The reason it's not supported in table_constraint, IIUC, is that the standard doesn't allow it, and that syntax is standards-mandated. The real problem is not so much extending the syntax as that there'd be no way to represent the constraint in information_schema. Exclusion constraints are outside the standard already, so omitting them from the information_schema views is less bad than missing unique constraints would be. 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] missing schema qualifications in psql
On Thu, Apr 1, 2010 at 9:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: While psql is careful to schema-qualify all references to built-in objects (pg_catalog.*), it completely neglects to do this for built-in operators, That's intentional because of the utter lack of readability that results if you try to use OPERATOR() everywhere ... I was mulling over in my head the possibility that the date on which this patch was posted was deliberate... ...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] [GENERAL] Postgres 9.1 - Release Theme
Following a great deal of discussion, I'm pleased to announce that the PostgreSQL Core team has decided that the major theme for the 9.1 release, due in 2011, will be 'NoSQL'. Please, provide me your address so I can forward you the health care bills I had to pay due to the heart attack I suffered this morning (when reading your post). BTW PostgreSQL core team is not alone realizing how obsolete relational databases are: http://thedailywtf.com/Articles/Announcing-APDB-The-Worlds-Fastest-Database.aspx Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme
On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote: I prefer to dump all my data in a big text file and grep it for the information I need. As long as you implement your own grep, that sounds about on par with the current trends! Go for it! Well, first you have to implement your own compiler. Also a lexer and a parser. 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] I am interested in the MERGE command implementation as my gSoC project
On Tue, Mar 30, 2010 at 3:26 AM, Zhai Boxuan bxz...@gmail.com wrote: To whom may concern, projects provided by postgres in google Summer Code of this year. I think the MERGE command in TO DO list is a suitable topic for me. I have read some infor about the MERGE command, which has not been implemented yet in Postgres 8. I considered the problem and have a brief plan for the jobs. 1 We need to update the backend/parser/gram.y for adding the SQL style MERGE command in the parser. I can do this, since it is similar to what I was doing in China. One new “MergeStmt” structure should be designed to hold the transformed command information. The structure definitely need: one SelectStmt to hold the subquery of the source table, a list of expressions for the MATCH condition. Yet some other expression lists are needed for specifying the additional match and/or not match conditions. It is relatively easy to implement since we can reuse many components of the SELECT command. 2. In the Analyze.c file we need to add a function to transform this MergeStmt into a Query node. It is necessary to add a new command type for MERGE, which is a plannable command. We need to check the semantics correctness of the statement. What I am thinking about is to combine the target table and the source table as a whole SELECT query. If there is no NOT MATCH option, we can generate a normal query node of something like “SELECT * FROM target, source WHERE match-condition;” or , We have to do a cross join if we want to handle some NOT MATCH actions, which will do a query like “SELECT * FROM target, source;” The benefit is that we can almost fully reuse the rewriter and planner to transform this generated query as an executor-accepting structure. 3. A plan is need for the query. The planner should accept this new plannable command. However, as motioned above, the real work will be: do a traditional query plan on the formatted select query based on the target and source table. Then pack this plan with a outer planner node, which is designed for MERGE command specifically. 4. How to execute the query? I am still not very clear. The basic idea is for each returned tuple of the select query we generated above (the tuple contains all the attributes in both source and target table) we can test it with MATCH and/or NOT MATCH conditions, and do corresponding actions base the testing result. I believe there are some problems will encounter especially for the transaction things. And I am also not sure about whether the UPDATE, INSERT and DELETE operations for previous output tuple will affect the remaining join processing. Hope you can help me on improving this rough idea. Or, if you are not convenient, please kindly forward this letter to who may concern it. This is a big project. If you're not already familiar with the internals of the planner and executor, you're not going to be able to finish this in one summer, or even come close. I would expect a patch like this to take a year or more to get merged, even with someone very knowledgeable on it. You might want to set your sights on something a little less ambitious. See also: http://archives.postgresql.org/pgsql-hackers/2010-03/msg01034.php http://archives.postgresql.org/pgsql-hackers/2010-03/msg01116.php ...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 and PARAM_EXEC
Yeb Havinga yebhavi...@gmail.com writes: !DOCTYPE html PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN html head meta content=text/html;charset=ISO-8859-1 http-equiv=Content-Type /head body bgcolor=#ff text=#00 Tom Lane wrote: br blockquote cite=mid:4ba361f2.5010...@gmail.com type=cite blockquote type=citeIn principle it could look something like [ etc etc ] Please do not send HTML-only email to the lists. It's a PITA to work with. Anyway, I hadn't looked at your patch before, but now that I have, it's not even approximately what I was suggesting. What I thought you should do was change ruleutils.c to print the parameter expressions at the call site, ie in the T_SubPlan and T_AlternativeSubPlan cases in get_rule_expr. 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] [GENERAL] Postgres 9.1 - Release Theme
2010/4/1 t...@fuzzy.cz: Following a great deal of discussion, I'm pleased to announce that the PostgreSQL Core team has decided that the major theme for the 9.1 release, due in 2011, will be 'NoSQL'. Please, provide me your address so I can forward you the health care bills I had to pay due to the heart attack I suffered this morning (when reading your post). :-p BTW PostgreSQL core team is not alone realizing how obsolete relational databases are: http://thedailywtf.com/Articles/Announcing-APDB-The-Worlds-Fastest-Database.aspx Yeah, I read that earlier in my daily lunchtime jaunt over to the Daily WTF. Alex clearly spent more time on his text than I did! -- Dave Page EnterpriseDB UK: 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: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme
On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler da...@kineticode.com wrote: On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote: I prefer to dump all my data in a big text file and grep it for the information I need. As long as you implement your own grep, that sounds about on par with the current trends! Go for it! Well, first you have to implement your own compiler. Also a lexer and a parser. All that will be for naught unless you hand wire your own logic boards. I mean really, come on. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme
On Thu, 2010-04-01 at 10:54 -0600, Scott Marlowe wrote: On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler da...@kineticode.com wrote: On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote: I prefer to dump all my data in a big text file and grep it for the information I need. As long as you implement your own grep, that sounds about on par with the current trends! Go for it! Well, first you have to implement your own compiler. Also a lexer and a parser. All that will be for naught unless you hand wire your own logic boards. I mean really, come on. I hate April 1st. -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] mremap and bus error
On Wed, Mar 31, 2010 at 11:05 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Can somebody help me? I think it's pretty hopeless to try to debug this without the complete source code... ...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] [DOCS] Streaming replication document improvements
On Thu, Apr 1, 2010 at 9:09 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Fujii Masao wrote: On Thu, Apr 1, 2010 at 11:00 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Mar 31, 2010 at 9:58 PM, Fujii Masao masao.fu...@gmail.com wrote: You mean that we should change replication connection not to consume superuser_reserved_connections slots in 9.0? Yes. I think it's good that walsenders can use the superuser reserved slots, that way a client that opens max_connections connections can't block out standby servers from connecting. Preventing superuser connections from consuming superuser_reserved_connections slots seems strange for me. So I'm leaning toward just removing superuser privilege from replication connection again. Thought? That would be good, but I fear it's a bigger change than we should be doing at this point. How about we adjust the backends math a bit: Currently: ReservedBackends = superuser_reserved_connections MaxBackends = max_connections + autovacuum_max_workers + 1; Proposal: ReservedBackends = superuser_reserved_connections + max_wal_senders MaxBackends = max_connections + autovacuum_max_workers + max_wal_senders + 1 So we implicitly reserve a slot and a superuser reserved slot for each walsender. Walsenders use the slots reserved for superusers, but if you set superuser_reserved_connections=3, there's still always at least three slots available for superuser to log in with psql, even if the maximum number of walsenders are connected. That seems pretty reasonable to me. I haven't checked how much code impact there is. I know Tom doesn't think we should change it at all, but surely pre-beta is the time to fix nasty corner cases that were added by recently committed patches? ...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: pending patch: Re: [HACKERS] Streaming replication and pg_xlogfile_name()
On Tue, Mar 30, 2010 at 5:14 AM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Mar 3, 2010 at 11:03 PM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Mar 2, 2010 at 10:52 PM, Fujii Masao masao.fu...@gmail.com wrote: It's not clear what it should return, a TLI corresponding the filename of the WAL segment the record was replayed from, so that you can use pg_xlogfile_name() to find out the filename of the WAL segment being replayed, or the accurate TLI of the record being replayed. I'm leaning towards the latter, it feels more correct and accurate, but you could argue for the former too. In any case, it needs to be well-defined. I agree with you that the latter is more correct and accurate. The simple fix is updating the lastPageTLI with the CheckPoint-ThisTimeLineID when replaying the shutdown checkpoint record. Though we might need to use new variable to keep the last applied timeline instead of the lastPageTLI. Here is the revised patch. I used new local variable instead of lastPageTLI to track the tli of last applied record. It is updated with the tli of the log page header when reading the page, and with the tli of the checkpoint record when replaying the checkpoint shutdown record that changes the tli. So pg_last_xlog_replay_location() can return the accurate tli of the last applied record. I rebased the patch to HEAD. Should I think that the patch has been rejected because it has remained pending for about one month? Can someone explain to me in plain language what problem this is trying to fix? I'm having trouble figuring it out. ...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: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme
On 04/01/2010 09:54 AM, Scott Marlowe wrote: On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheelerda...@kineticode.com wrote: On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote: I prefer to dump all my data in a big text file and grep it for the information I need. As long as you implement your own grep, that sounds about on par with the current trends! Go for it! Well, first you have to implement your own compiler. Also a lexer and a parser. All that will be for naught unless you hand wire your own logic boards. I mean really, come on. Actually I think this calls for quantum computing (http://en.wikipedia.org/wiki/Quantum_computer). The intersection of NoSQL and Quantum decoherence is almost to good to be true. -- Adrian Klaver adrian.kla...@gmail.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] mremap and bus error
2010/4/1 Robert Haas robertmh...@gmail.com: On Wed, Mar 31, 2010 at 11:05 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Can somebody help me? I think it's pretty hopeless to try to debug this without the complete source code... I am sorry. I am able to write test case, but I leaved it. Simply, I don't use a function mremap. I searched about it on net, and I think it is known issue on Linux :(. Regards Pavel Stehule ...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] [DOCS] Streaming replication document improvements
Robert Haas robertmh...@gmail.com writes: That seems pretty reasonable to me. I haven't checked how much code impact there is. I know Tom doesn't think we should change it at all, but surely pre-beta is the time to fix nasty corner cases that were added by recently committed patches? What nasty corner case? Having replication connections use superuser reserved slots seems exactly the behavior I'd expect given that they are running as superuser. I agree it would be good to decouple that later, but we already decided we are not going to try to separate replication privilege from superuser in 9.0. (Also, autovacuum workers are a quite separate concept since the DBA doesn't set them up or deal with them directly. So I'm unimpressed by pointing to the treatment of autovacuum_max_workers as a precedent.) 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] [DOCS] Streaming replication document improvements
Tom Lane t...@sss.pgh.pa.us wrote: Having replication connections use superuser reserved slots seems exactly the behavior I'd expect given that they are running as superuser. It seems within the realm of possibility that not all users would think to boost superuser_reserved_connections by the number of replication connections, and be surprised when they are unable to connect in an emergency. -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] [DOCS] Streaming replication document improvements
On 4/1/10 10:44 AM, Kevin Grittner wrote: It seems within the realm of possibility that not all users would think to boost superuser_reserved_connections by the number of replication connections, and be surprised when they are unable to connect in an emergency. Well, that's easy to add to the documentation. -- -- 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] [DOCS] Streaming replication document improvements
On Thu, Apr 1, 2010 at 1:46 PM, Josh Berkus j...@agliodbs.com wrote: On 4/1/10 10:44 AM, Kevin Grittner wrote: It seems within the realm of possibility that not all users would think to boost superuser_reserved_connections by the number of replication connections, and be surprised when they are unable to connect in an emergency. Well, that's easy to add to the documentation. It's probably also easy to fix so that it doesn't NEED to be documented. The thing is, when dealing with new features, we reduce our overall maintenance burden if we get it right the first time. Obviously it's too late for major changes, but minor adjustments to maintain the POLA seem like exactly what we SHOULD be doing right now. ...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] [DOCS] Streaming replication document improvements
The thing is, when dealing with new features, we reduce our overall maintenance burden if we get it right the first time. Obviously it's too late for major changes, but minor adjustments to maintain the POLA seem like exactly what we SHOULD be doing right now. Oh, I agree. Since we have a separate WALSender limit, it seems counter-intuitive and difficult-to-admin to have the WALSenders also limited by superuser_connections. They should be their own separate connection pool, just like the other background processes. However, if this was somehow infeasible, it wouldn't be hard to document. That's all. -- -- 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] [DOCS] Streaming replication document improvements
Josh Berkus j...@agliodbs.com writes: Oh, I agree. Since we have a separate WALSender limit, it seems counter-intuitive and difficult-to-admin to have the WALSenders also limited by superuser_connections. They should be their own separate connection pool, just like the other background processes. +1 Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha release this week?
On Thu, Apr 1, 2010 at 2:31 PM, Dave Page dp...@pgadmin.org wrote: On Thu, Apr 1, 2010 at 5:24 AM, Robert Haas robertmh...@gmail.com wrote: This stuff is now also at: ftp://developer.postgresql.org/pub/source/9.0alpha5/ Thanks Robert. We're working on this, but it seems that changes in the PG build have broken the debugger again. Hopefully we can get it sorted before the holidays start tomorrow. OK, there are builds at http://developer.pgadmin.org/~dpage/ Note that these are from an entirely new build machine for 9.0. There are new build OS's, new compilers, updated dependencies... in other words, expect something to go wrong. I did briefly test the Windows version - the server installed and ran OK, but pgAdmin 1.8 doesn't like PG 9.0 -- Dave Page EnterpriseDB UK: 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] [DOCS] Streaming replication document improvements
On Thu, 2010-04-01 at 11:49 -0700, Josh Berkus wrote: The thing is, when dealing with new features, we reduce our overall maintenance burden if we get it right the first time. Obviously it's too late for major changes, but minor adjustments to maintain the POLA seem like exactly what we SHOULD be doing right now. Oh, I agree. Since we have a separate WALSender limit, it seems counter-intuitive and difficult-to-admin to have the WALSenders also limited by superuser_connections. They should be their own separate connection pool, just like the other background processes. However, if this was somehow infeasible, it wouldn't be hard to document. That's all. +1 -- 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] master in standby mode croaks
On Tue, 2010-03-30 at 22:40 -0400, Robert Haas wrote: I discovered tonight that if you shut down a server, create recovery.conf with standby_mode = 'on', and start it back up again, you get this: LOG: database system was shut down at 2010-03-30 22:34:09 EDT LOG: entering standby mode FATAL: recovery connections cannot start because the recovery_connections parameter is disabled on the WAL source server LOG: startup process (PID 22980) exited with exit code 1 LOG: aborting startup due to startup process failure Now, you might certainly argue that this is a stupid thing to do (my motivation was to test some stuff) but certainly it's fair to say that error message is darn misleading, since in fact recovery_connections was NOT disabled. I believe this is the same start up from a shut down checkpoint problem that's been discussed previously so I won't belabor the point other than to say that I don't think it is the same thing at all. This is a separate error and should be rejected as such. I still think we need to fix this. Agreed, as a separate issue. -- 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
[HACKERS] Problem of Magic Block in Postgres 8.2
Hi,
I have some old code for extension functions in Postgres 8.0. And, I
am trying to make it work with Postgres 8.2.
One problem is about the Magic Block.
The extension functions was developed by C++ mixed with C.
The code is like:
extern C Datum spgistinsert(PG_FUNCTION_ARGS)
{
...
}
I have added enough PG_MODULE_MAGIC; to the source code. (Also,
include 'fmgr.h' and 'postgres.h')
But, when I create the function inside Postgres, it still complains as
following:
ERROR: incompatible library
/home/hepei/bin/Chameleon/lib/libspgist_trie.so: missing magic block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
STATEMENT: /* Load the trie extension */
/* */
load '/home/hepei/bin/Chameleon/lib/libspgist_trie.so';
ERROR: incompatible library
/home/hepei/bin/Chameleon/lib/libspgist_trie.so: missing magic block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
I am capable to define a simple function in a single file by C, and
make it work with postgres 8.2. However, my old code is more complex,
which involves more source code, library, and it is also using both
C++ and C. So, I am not sure where the problem comes from, and how to
solve it.
Thanks
Look forward your reply
--
Pei
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: pending patch: Re: [HACKERS] Streaming replication and pg_xlogfile_name()
On Fri, Apr 2, 2010 at 2:22 AM, Robert Haas robertmh...@gmail.com wrote: Can someone explain to me in plain language what problem this is trying to fix? I'm having trouble figuring it out. The problem is that pg_xlogfile_name(pg_last_xlog_receive_location()) and pg_xlogfile_name(pg_last_xlog_replay_location()) might report an inaccurate WAL file name because currently pg_xlogfile_name() always uses the current timeline to calculate the WAL file name. For example, even though the last applied WAL file is 00010002, the standby wrongly reports that 0002 has been applied last. postgres=# SELECT l lsn, pg_xlogfile_name(l) filename FROM pg_last_xlog_replay_location() AS l; lsn| filename ---+-- 0/200FF70 | 0002 (1 row) $ ls sby/pg_xlog/ 00010002 00010003 archive_status 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] Feature request - function-based deferrable uniques.
Sure -- use CREATE UNIQUE INDEX. alvherre=# create function singleton(int) returns int immutable language sql as $$ select 1 $$; CREATE FUNCTION alvherre=# create table singleton (a int); CREATE TABLE alvherre=# create unique index only_one on singleton (singleton(a)); CREATE INDEX alvherre=# insert into singleton values (3); INSERT 0 1 alvherre=# insert into singleton values (6); ERROR: llave duplicada viola restricción de unicidad «only_one» And it's possible to make this check deferrable? Uniqueness will be validated on commit or SET ALL CONSTRAINTS IMMEDIATE command? The reason it's not supported in table_constraint, IIUC, is that the standard doesn't allow it, and that syntax is standards-mandated. CREATE INDEX, however, is not. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha release this week?
Robert, Dave, Thanks so much for building these. Hopefully we'll get a good turnout and get a lot of things tested. -- -- 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] Proposal: Add JSON support
Dne 1.4.2010 5:39, Joseph Adams napsal(a): I ended up reinventing the wheel and writing another JSON library: http://constellationmedia.com/~funsite/static/json-0.0.1.tar.bz2 This is a first release, and it doesn't really have a name besides json. It's very similar to cJSON, except it is (sans unknown bugs) more reliable, more correct, and cleaner (unless you hate gotos ;-) ). It has a simple test suite. It is not prone to stack overflows, as it doesn't recurse. It is strict, requires input to be UTF-8 (it validates it first) and only outputs UTF-8. Other than treating numbers liberally, my implementation only accepts valid JSON code (it doesn't try to correct anything, even Unicode problems). It is under the MIT license. I did some testing on my own, it passed everything I have thrown at it so far. I also did tests using MSVC for both 32bit and 64bit targets and it worked fine too (except for missing stdbool.h in msvc which is no big deal). The coding style compared to cJSON (or other libs I've seen) seems closer to the style of PostgreSQL, it would however still require pgindent run and maybe some minor adjustments. -- Regards Petr Jelinek (PJMODOS) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Compile fail, alpha5 gcc 4.3.3 in elog.c
Guys, Hmmm. I appear to have had a compile error with that alpha5 tarball, in elog.c. No special options on compile, except an alternate directory and port. Ubunutu 9.10 server GCC 4.3.3 Tries both: ./configure --with-pgport=5490 --prefix=/usr/local/pgsql/9.0/ and: ./configure --with-pgport=5490 --prefix=/usr/local/pgsql/9.0/ --enable-debug -enable-cassert make[4]: Entering directory `/usr/local/src/postgresql-9.0alpha5/src/backend/utils/error' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -Werror -I../../../../src/include -D_GNU_SOURCE -c -o assert.o assert.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -Werror -I../../../../src/include -D_GNU_SOURCE -c -o elog.o elog.c cc1: warnings being treated as errors elog.c: In function ‘write_console’: elog.c:1698: error: ignoring return value of ‘write’, declared with attribute warn_unused_result elog.c: In function ‘write_pipe_chunks’: elog.c:2390: error: ignoring return value of ‘write’, declared with attribute warn_unused_result elog.c:2399: error: ignoring return value of ‘write’, declared with attribute warn_unused_result make[4]: *** [elog.o] Error 1 -- -- 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] Compile fail, alpha5 gcc 4.3.3 in elog.c
Josh Berkus j...@agliodbs.com writes: Hmmm. I appear to have had a compile error with that alpha5 tarball, in elog.c. No special options on compile, except an alternate directory and port. No, you stuck in -Werror. Don't do that on bleeding-edge gcc (or bleeding-edge anything). 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] Compile fail, alpha5 gcc 4.3.3 in elog.c
On 4/1/10 9:57 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Hmmm. I appear to have had a compile error with that alpha5 tarball, in elog.c. No special options on compile, except an alternate directory and port. No, you stuck in -Werror. Don't do that on bleeding-edge gcc (or bleeding-edge anything). I didn't actually. Must be set by default on Ubuntu's gcc? (goes looking for a way to disable it ...) -- -- 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] Compile fail, alpha5 gcc 4.3.3 in elog.c
No, you stuck in -Werror. Don't do that on bleeding-edge gcc (or bleeding-edge anything). Found it ... Robert, you stuck a -Werror in the gzip file you uploaded (but not, for some reason, the bzip). -- -- 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] Problem of Magic Block in Postgres 8.2
Pei He hepeim...@gmail.com wrote:
The extension functions was developed by C++ mixed with C.
ERROR: incompatible library
/home/hepei/bin/Chameleon/lib/libspgist_trie.so: missing magic block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
You can use extern C blocks for PG_MODULE_MAGIC, PG_FUNCTION_INFO_V1,
and function declarations:
extern C
{
#include postgres.h
#include fmgr.h
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(your_function);
extern Datum your_function(PG_FUNCTION_ARGS);
}
However, you should very carefully use C++ exceptions and destructors
in your module because PostgreSQL uses siglongjmp; C++ unwind semantics
don't work on postgres' errors. You cannot use those C++ features and
postgres' APIs together.
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
