Re: [HACKERS] [PL/PgSQL] EXECUTE...USING enhancement proposal

[Vincenzo Romano]

2010/1/14 Pavel Stehule :
> Hello
>
> I thing, so this is bad idea.
>
> a) this behave depends on DDL implementation, not plpgsql implementation
>
> b) proposed implementation needs some escape magic. This was first
> implementation of USING clause and it was rejected. Some composite and
> nested values are significant break.
>
> see in archive 
> http://archives.postgresql.org/pgsql-patches/2006-08/msg00267.php
>
> Regards
> Pavel Stehule
>
>
> 2010/1/14 Vincenzo Romano :
>> Hi all.
>> There's currently a limitation in the v8.4.2 implementation of the
>> EXECUTE...USING predicate in PL/PgSQL which prevents you from
>> exploiting the USING-supplied value list with DDL commands.
>> For example:
>>
>> CREATE TABLE test ( i int );
>> ...
>> EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;
>>
>> complains with:
>>
>> ERROR:  there is no parameter $1
>> CONTEXT:  SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"
>>
>> while:
>>
>> EXECUTE 'SELECT $1' USING 42;
>>
>> works.
>> In both cases the $1 variable/placeholder refers to a constant value.
>> And actually, even if the "thing" defined after the USING lexeme was a
>> variable, that should be evaluated and substituted *before* executing
>> the command.
>>
>> The current documentation
>> (http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)
>> doesn't say so and clearly describes how this feature is meant to
>> work.
>> Quoting:
>> 
>> The command string can use parameter values, which are referenced in
>> the command as $1, $2,
>> etc. These symbols refer to values supplied in the USING clause. This
>> method is often preferable to
>> inserting data values into the command string as text: it avoids
>> run-time overhead of converting the
>> values to text and back, and it is much less prone to SQL-injection
>> attacks since there is no need for
>> quoting or escaping. An example is:
>> 
>> (38.5.4. Executing Dynamic Commands)
>>
>> It talks about "values", that is typed constants.
>> Please, refer also to the following discussion on pgsql-general mailing list:
>> http://archives.postgresql.org/pgsql-general/2010-01/msg00522.php
>>
>> My proposal is to relax that (clearly running but undocumented)
>> constraint and allow any SQL command in the EXECUTE...USING predicate.
>> I would leave the responsibility to the programmer to ensure whether
>> the dynamic command makes any syntactic and semantic sense.
>>
>> --
>> Vincenzo Romano
>> NotOrAnd Information Technologies
>> NON QVIETIS MARIBVS NAVTA PERITVS

Well, the very basic proposal could then be changed into:

Allow some kind of syntactic replacement of the placeholders found
into the command string with values taken from the USING clause
evaluated straight into the PLPGSQL function body environment. The
model could be the C language sprintf().

Maybe you can think about using different placeholders for "static"
(or "local") evaluation, like #1, #2 ... #n.

For example, you could do something like this:

EXECUTE 'ALTER TABLE '||tablename||' ADD CHECK col1=#1 and col2>=#2
and col2<#3" USING currval1, currval2, currval3.

The execution, within the PLPGSQL interpreter, would proceed like this:

0. Concatenate the sub-strings to just 1.
1. Evaluate the variable list after the USING clause (currval1,
currval2, currval3) to their current values.
2. Replace the placeholders with the natural ordered references within
the command string
3. Send the final string to the execution.

This makes a lot of sense (in my opinion) for higher level functions
(functions which create functions which execute dynamic commands).
It's more like a string substitution but with knowledge of the syntac
of the expressions following the USING clause.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PL/PgSQL] EXECUTE...USING enhancement proposal

[Pavel Stehule]

2010/1/15 Vincenzo Romano :
> 2010/1/14 Pavel Stehule :
>> Hello
>>
>> I thing, so this is bad idea.
>>
>> a) this behave depends on DDL implementation, not plpgsql implementation
>>
>> b) proposed implementation needs some escape magic. This was first
>> implementation of USING clause and it was rejected. Some composite and
>> nested values are significant break.
>>
>> see in archive 
>> http://archives.postgresql.org/pgsql-patches/2006-08/msg00267.php
>>
>> Regards
>> Pavel Stehule
>>
>>
>> 2010/1/14 Vincenzo Romano :
>>> Hi all.
>>> There's currently a limitation in the v8.4.2 implementation of the
>>> EXECUTE...USING predicate in PL/PgSQL which prevents you from
>>> exploiting the USING-supplied value list with DDL commands.
>>> For example:
>>>
>>> CREATE TABLE test ( i int );
>>> ...
>>> EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;
>>>
>>> complains with:
>>>
>>> ERROR:  there is no parameter $1
>>> CONTEXT:  SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"
>>>
>>> while:
>>>
>>> EXECUTE 'SELECT $1' USING 42;
>>>
>>> works.
>>> In both cases the $1 variable/placeholder refers to a constant value.
>>> And actually, even if the "thing" defined after the USING lexeme was a
>>> variable, that should be evaluated and substituted *before* executing
>>> the command.
>>>
>>> The current documentation
>>> (http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)
>>> doesn't say so and clearly describes how this feature is meant to
>>> work.
>>> Quoting:
>>> 
>>> The command string can use parameter values, which are referenced in
>>> the command as $1, $2,
>>> etc. These symbols refer to values supplied in the USING clause. This
>>> method is often preferable to
>>> inserting data values into the command string as text: it avoids
>>> run-time overhead of converting the
>>> values to text and back, and it is much less prone to SQL-injection
>>> attacks since there is no need for
>>> quoting or escaping. An example is:
>>> 
>>> (38.5.4. Executing Dynamic Commands)
>>>
>>> It talks about "values", that is typed constants.
>>> Please, refer also to the following discussion on pgsql-general mailing 
>>> list:
>>> http://archives.postgresql.org/pgsql-general/2010-01/msg00522.php
>>>
>>> My proposal is to relax that (clearly running but undocumented)
>>> constraint and allow any SQL command in the EXECUTE...USING predicate.
>>> I would leave the responsibility to the programmer to ensure whether
>>> the dynamic command makes any syntactic and semantic sense.
>>>
>>> --
>>> Vincenzo Romano
>>> NotOrAnd Information Technologies
>>> NON QVIETIS MARIBVS NAVTA PERITVS
>
> Well, the very basic proposal could then be changed into:
>
> Allow some kind of syntactic replacement of the placeholders found
> into the command string with values taken from the USING clause
> evaluated straight into the PLPGSQL function body environment. The
> model could be the C language sprintf().
>
> Maybe you can think about using different placeholders for "static"
> (or "local") evaluation, like #1, #2 ... #n.
>

I disagree with this functionality for USING clause. Main parser
doesn't support some enhanced syntax. But we can discus about some
function 'printf' or 'format' that can help with similar task.

some like

EXECUTE format('ALTER TABLE % ADD CHECK (col1=% AND col2=%)',
  quote_ident(tablename), cval1, cval2, cval3);


there was two proposals:
a) based on syntax our RAISE statements
b) based on printf syntax

Personally prefer a) - it is simpler and enough

Pavel

http://wiki.postgresql.org/wiki/Sprintf
http://archives.postgresql.org/pgsql-hackers/2009-09/msg00482.php
http://archives.postgresql.org/pgsql-hackers/2009-09/msg00563.php






> For example, you could do something like this:
>
> EXECUTE 'ALTER TABLE '||tablename||' ADD CHECK col1=#1 and col2>=#2
> and col2<#3" USING currval1, currval2, currval3.
>
> The execution, within the PLPGSQL interpreter, would proceed like this:
>
> 0. Concatenate the sub-strings to just 1.
> 1. Evaluate the variable list after the USING clause (currval1,
> currval2, currval3) to their current values.
> 2. Replace the placeholders with the natural ordered references within
> the command string
> 3. Send the final string to the execution.
>
> This makes a lot of sense (in my opinion) for higher level functions
> (functions which create functions which execute dynamic commands).
> It's more like a string substitution but with knowledge of the syntac
> of the expressions following the USING clause.
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PL/PgSQL] EXECUTE...USING enhancement proposal

[Vincenzo Romano]

2010/1/15 Pavel Stehule :
> I disagree with this functionality for USING clause. Main parser
> doesn't support some enhanced syntax. But we can discus about some
> function 'printf' or 'format' that can help with similar task.
>
> some like
>
> EXECUTE format('ALTER TABLE % ADD CHECK (col1=% AND col2=%)',
>                              quote_ident(tablename), cval1, cval2, cval3);
>
>
> there was two proposals:
> a) based on syntax our RAISE statements
> b) based on printf syntax
>
> Personally prefer a) - it is simpler and enough
>
> Pavel



I do like the printf-like approach more than my proposal!
Do you think about a built-in implementation rather than the on in PLGSQL?

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PL/PgSQL] EXECUTE...USING enhancement proposal

[Pavel Stehule]

2010/1/15 Vincenzo Romano :
> 2010/1/15 Pavel Stehule :
>> I disagree with this functionality for USING clause. Main parser
>> doesn't support some enhanced syntax. But we can discus about some
>> function 'printf' or 'format' that can help with similar task.
>>
>> some like
>>
>> EXECUTE format('ALTER TABLE % ADD CHECK (col1=% AND col2=%)',
>>                              quote_ident(tablename), cval1, cval2, cval3);
>>
>>
>> there was two proposals:
>> a) based on syntax our RAISE statements
>> b) based on printf syntax
>>
>> Personally prefer a) - it is simpler and enough
>>
>> Pavel
>
>
>
> I do like the printf-like approach more than my proposal!
> Do you think about a built-in implementation rather than the on in PLGSQL?
>

sure.

the plpgsql isn't problem in 8.5, but integrated version can be little
bit smarter.

Pavel


> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Streaming replication, loose ends

[Heikki Linnakangas]

I've now committed streaming replication. I moved the files around a
bit, and put the walreceiver/walsender stuff in a new
src/backend/replication subdirectory. There's enough stuff there already
to deserve a new subdirectory, and if we add the capability for
streaming base backups etc. that has been discussed, we will have more
code in there.

But it's not time to party yet. There's still a few loose ends we need
to address:

Documentation. The patch originally moved around some sections, but I
didn't include that in the committed version, to make it clear in the
diff what exactly was added/changed. But I do agree with the original
thought of adding a new "Replication" chapter, and moving all the
replication and standby related stuff there from the "Backup and
Restore" chapter, so let's start working on that.

And of course the documentation needs to be improved and expanded in
general.

We talked about changing the retry-logic yesterday, so that the standby
could fall back to restoring WAL files from archive even after it has
already connected to the primary, if it e.g falls behind too much. It
looks like that involves some heavy refactoring around
ReadRecord/FetchRecord, which makes me a bit nervous given how critical
ReadRecord() is and how old and well-tested the current code is. So
let's tackle that as a follow-on patch.

Then there's the issue of what privileges to require for a replication
connection. I kept the superuser() check for now, so you currently need
to be superuser, but as I opined earlier I don't think that's good for
overall security. Perhaps we should add a new "replication" privilege
besides the login privilege. To connect for replication, replication
privilege would be checked instead of login privilege. That would make
it quite simple to create a user or users for replication purposes, with
no other access to the system.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Bruce Momjian]

Heikki Linnakangas wrote:
> I've now committed streaming replication. I moved the files around a
> bit, and put the walreceiver/walsender stuff in a new
> src/backend/replication subdirectory. There's enough stuff there already
> to deserve a new subdirectory, and if we add the capability for
> streaming base backups etc. that has been discussed, we will have more
> code in there.
> 
> But it's not time to party yet. There's still a few loose ends we need
> to address:
> 
> Documentation. The patch originally moved around some sections, but I
> didn't include that in the committed version, to make it clear in the
> diff what exactly was added/changed. But I do agree with the original
> thought of adding a new "Replication" chapter, and moving all the
> replication and standby related stuff there from the "Backup and
> Restore" chapter, so let's start working on that.

Uh, do we really want to call this "replication" rather than archive log
streaming or something.  It seems "replication" is a generic term and
will confuse people who are using other replication solutions like
Slony.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Pavel Stehule]

2010/1/15 Bruce Momjian :
> Heikki Linnakangas wrote:
>> I've now committed streaming replication. I moved the files around a
>> bit, and put the walreceiver/walsender stuff in a new
>> src/backend/replication subdirectory. There's enough stuff there already
>> to deserve a new subdirectory, and if we add the capability for
>> streaming base backups etc. that has been discussed, we will have more
>> code in there.
>>
>> But it's not time to party yet. There's still a few loose ends we need
>> to address:
>>
>> Documentation. The patch originally moved around some sections, but I
>> didn't include that in the committed version, to make it clear in the
>> diff what exactly was added/changed. But I do agree with the original
>> thought of adding a new "Replication" chapter, and moving all the
>> replication and standby related stuff there from the "Backup and
>> Restore" chapter, so let's start working on that.
>
> Uh, do we really want to call this "replication" rather than archive log
> streaming or something.  It seems "replication" is a generic term and
> will confuse people who are using other replication solutions like
> Slony.

+1

Pavel
>
> --
>  Bruce Momjian          http://momjian.us
>  EnterpriseDB                             http://enterprisedb.com
>
>  + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
Sent 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, loose ends

[Boszormenyi Zoltan]

Hi,

Heikki Linnakangas írta:
> I've now committed streaming replication. I moved the files around a
> bit, and put the walreceiver/walsender stuff in a new
> src/backend/replication subdirectory. [snip]

there's one loose end indeed.
"make maintainer-clean" doesn't delete these:

src/backend/replication/walreceiver/walreceiver.o
src/backend/replication/walreceiver/walreceiver.so
src/backend/replication/walreceiver/.deps/

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/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] missing data in information_schema grant_* tables?

[Fabien COELHO]

Hello pgdevs,

I'm trying to use the information_schema, and I'm looking at the grant
tables. ISTM that some views do not show all expected permissions.

 psql> CREATE TABLE foo();
 psql> CREATE USER calvin NOLOGIN;
 psql> GRANT SELECT ON TABLE foo TO calvin;
 psql> GRANT INSERT ON TABLE foo TO PUBLIC; -- not really a good idea

 psql> \dp
Access privileges
 Schema | Name | Type  |   Access privileges   | Column access privileges
+--+---+---+--
 public | foo  | table | fabien=arwdDxt/fabien |
   : calvin=r/fabien
   : =a/fabien

INSERT to PUBLIC is shown on the last line of the access privileges 
column. However, when looking at the information_schema:


 psql> SELECT grantor, grantee, privilege_type
   FROM information_schema.role_table_grants
   WHERE table_name = 'foo';
 grantor | grantee | privilege_type
-+-+
 fabien  | fabien  | SELECT
 fabien  | fabien  | INSERT
 fabien  | fabien  | UPDATE
 fabien  | fabien  | DELETE
 fabien  | fabien  | TRUNCATE
 fabien  | fabien  | REFERENCES
 fabien  | fabien  | TRIGGER
 fabien  | calvin  | SELECT
(8 rows)

My point is that the grant to "PUBLIC" does not show in the information 
schema. However, it appears in the table_privileges view:


 psql> SELECT grantor, grantee, privilege_type FROM 
information_schema.table_privileges WHERE table_name='foo';

 grantor | grantee | privilege_type
-+-+
 ... same as previous query ...
 fabien  | PUBLIC  | INSERT

(1) Would you agree that it is a "bug"? That is, if the grantee is PUBLIC, 
it is an enabled role for the current user, so it should appear in the 
role_table_grants view...


(2) If yes is the answer to the previous question, and in order to fix it, 
would it be acceptable to drop the view definitions of role_table_grants 
based on the pg_catalog and rely on the table_privileges view instead, if 
possible (it looks so, but there may be some issues)? Or should the 
current view definition be simply reworked?


--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New XLOG record indicating WAL-skipping

[Heikki Linnakangas]

Fujii Masao wrote:
> On Wed, Dec 9, 2009 at 6:25 PM, Fujii Masao  wrote:
>> Here is the patch:
>>
>> - Write an XLOG UNLOGGED record in WAL if WAL-logging is skipped for only
>>  the reason that WAL archiving is not enabled and such record has not been
>>  written yet.
>>
>> - Cause archive recovery to end if an XLOG UNLOGGED record is found during
>>  it.
> 
> Here's an updated version of my "New XLOG record indicating WAL-skipping" 
> patch.
> http://archives.postgresql.org/pgsql-hackers/2009-12/msg00788.php

Thanks!

I don't like special-casing UNLOGGED records in XLogInsert and
ReadRecord(). Those functions are complicated enough already. The
special handling from XLogInsert() (and a few other places) is only
required because the UNLOGGED records carry no payload. That's easy to
avoid, just add some payload to them, doesn't matter what it is. And I
don't think ReadRecord() is the right place to emit the errors/warnings,
that belongs naturally in xlog_redo().

It might be useful to add some information in the records telling why
WAL-logging was skipped. It might turn out to be useful in debugging.
That also conveniently adds payload to the records, to avoid the
special-casing in XLogInsert() :-).

I think it's a premature optimization to skip writing the records if
we've written in the same session already. Especially with the 'reason'
information added to the records, it's nice to have a record of each
such operation. All operations that skip WAL-logging are heavy enough
that an additional WAL record will make no difference. I can see that it
was required to avoid the flooding from heap_insert(), but we can move
the XLogSkipLogging() call from heap_insert() to heap_sync().

Attached is an updated patch, doing the above. Am I missing anything?

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
? GNUmakefile
? b
? config.log
? config.status
? config.status.lineno
? configure.lineno
? gin-splay-1.patch
? gin-splay-2.patch
? gin-splay-3.patch
? md-1.c
? md-1.patch
? temp-file-resowner-2.patch
? contrib/pgbench/fsynctest
? contrib/pgbench/fsynctest.c
? contrib/pgbench/fsynctestfile
? contrib/spi/.deps
? doc/src/sgml/HTML.index
? doc/src/sgml/bookindex.sgml
? doc/src/sgml/features-supported.sgml
? doc/src/sgml/features-unsupported.sgml
? doc/src/sgml/version.sgml
? src/Makefile.global
? src/backend/aaa.patch
? src/backend/postgres
? src/backend/access/common/.deps
? src/backend/access/gin/.deps
? src/backend/access/gist/.deps
? src/backend/access/hash/.deps
? src/backend/access/heap/.deps
? src/backend/access/index/.deps
? src/backend/access/nbtree/.deps
? src/backend/access/transam/.deps
? src/backend/bootstrap/.deps
? src/backend/catalog/.deps
? src/backend/commands/.deps
? src/backend/executor/.deps
? src/backend/foreign/.deps
? src/backend/foreign/dummy/.deps
? src/backend/foreign/postgresql/.deps
? src/backend/lib/.deps
? src/backend/libpq/.deps
? src/backend/main/.deps
? src/backend/nodes/.deps
? src/backend/optimizer/geqo/.deps
? src/backend/optimizer/path/.deps
? src/backend/optimizer/plan/.deps
? src/backend/optimizer/prep/.deps
? src/backend/optimizer/util/.deps
? src/backend/parser/.deps
? src/backend/po/af.mo
? src/backend/po/cs.mo
? src/backend/po/de.mo
? src/backend/po/es.mo
? src/backend/po/fr.mo
? src/backend/po/hr.mo
? src/backend/po/hu.mo
? src/backend/po/it.mo
? src/backend/po/ja.mo
? src/backend/po/ko.mo
? src/backend/po/nb.mo
? src/backend/po/nl.mo
? src/backend/po/pl.mo
? src/backend/po/pt_BR.mo
? src/backend/po/ro.mo
? src/backend/po/ru.mo
? src/backend/po/sk.mo
? src/backend/po/sl.mo
? src/backend/po/sv.mo
? src/backend/po/tr.mo
? src/backend/po/zh_CN.mo
? src/backend/po/zh_TW.mo
? src/backend/port/.deps
? src/backend/postmaster/.deps
? src/backend/regex/.deps
? src/backend/replication/.deps
? src/backend/replication/walreceiver/.deps
? src/backend/rewrite/.deps
? src/backend/snowball/.deps
? src/backend/snowball/snowball_create.sql
? src/backend/storage/buffer/.deps
? src/backend/storage/file/.deps
? src/backend/storage/freespace/.deps
? src/backend/storage/ipc/.deps
? src/backend/storage/large_object/.deps
? src/backend/storage/lmgr/.deps
? src/backend/storage/page/.deps
? src/backend/storage/smgr/.deps
? src/backend/tcop/.deps
? src/backend/tsearch/.deps
? src/backend/utils/.deps
? src/backend/utils/probes.h
? src/backend/utils/adt/.deps
? src/backend/utils/cache/.deps
? src/backend/utils/error/.deps
? src/backend/utils/fmgr/.deps
? src/backend/utils/hash/.deps
? src/backend/utils/init/.deps
? src/backend/utils/mb/.deps
? src/backend/utils/mb/Unicode/BIG5.TXT
? src/backend/utils/mb/Unicode/CP950.TXT
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc2004_sjis2004/.deps
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_jis_2004_and_shi

[HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Leonardo F]

Hi,

I read the thread "Our CLUSTER implementation is pessimal" 
http://archives.postgresql.org/pgsql-hackers/2008-08/msg01371.php .

I would like to try/integrate that patch as we use CLUSTER a lot on our system.

I was going to try to add the proper cost_index/cost_sort calls to decide which 
"path" should be executed, as in:

http://archives.postgresql.org/pgsql-hackers/2008-09/msg00517.php

I don't think it will be easy without help... I'll ask here a lot I'm afraid...

About that patch:

1) would it be possible to use the tuplesort_*tupleslot set of functions 
instead of writing new ones for HeapTuple? That is: is it that 
difficult/impossible/nonsense to construct TupleTableSlot from HeapTuple and 
use those?

2) The patch doesn't check "HeapTupleSatisfiesVacuum" before passing it to 
tuplesort_putrawtuple: would it be reasonable to check the "isdead" flag before 
calling tuplesort_putrawtuple for each tuple?


Sorry if I talked nonsense...



Leonardo




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Heikki Linnakangas]

Leonardo F wrote:
> I read the thread "Our CLUSTER implementation is pessimal" 
> http://archives.postgresql.org/pgsql-hackers/2008-08/msg01371.php .
> 
> I would like to try/integrate that patch as we use CLUSTER a lot on our 
> system.

Great!

> About that patch:
> 
> 1) would it be possible to use the tuplesort_*tupleslot set of functions 
> instead of writing new ones for HeapTuple? That is: is it that 
> difficult/impossible/nonsense to construct TupleTableSlot from HeapTuple and 
> use those?

Yeah, I think you could do that, I agree it feels better that way.
You'll still need new copytup and comparetup functions, though, to deal
with HeapTupleHeaders instead of MinimalTuples, or modify the existing
ones to handle both. And some way to indicate that you want to preserve
the visibility information when you create the tuplesort, maybe a new
parameter to tuplesort_begin_heap().

> 2) The patch doesn't check "HeapTupleSatisfiesVacuum" before passing it to 
> tuplesort_putrawtuple: would it be reasonable to check the "isdead" flag 
> before calling tuplesort_putrawtuple for each tuple?

Yeah, seems reasonable, to avoid sorting dead tuples unnecessarily.

-- 
  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] ECPG DESCRIBE [OUTPUT] support

[Boszormenyi Zoltan]

Hi,

I just saw that you committed the DESCRIBE patch.

Please, also add this small change that adds ecpg_raise()
calls to ECPGdescribe() to return the proper sqlca error
in error paths for:
- unsupported call for DESCRIBE INPUT
- no such connection name
- no such prepared statement

Thanks and 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/

*** /home/zozo/cvs/pgsql/pgsql/src/interfaces/ecpg/ecpglib/descriptor.c	2010-01-15 12:55:24.0 +0100
--- pgsql.3/src/interfaces/ecpg/ecpglib/descriptor.c	2010-01-15 13:14:35.0 +0100
***
*** 739,752 
  
  	/* DESCRIBE INPUT is not yet supported */
  	if (input)
  		return ret;
  
  	con = ecpg_get_connection(connection_name);
  	if (!con)
! 		return false;
  	prep = ecpg_find_prepared_statement(stmt_name, con, NULL);
  	if (!prep)
  		return ret;
  
  	va_start(args, stmt_name);
  
--- 739,762 
  
  	/* DESCRIBE INPUT is not yet supported */
  	if (input)
+ 	{
+ 		ecpg_raise(line, ECPG_UNSUPPORTED, ECPG_SQLSTATE_ECPG_INTERNAL_ERROR, "DESCRIBE INPUT");
  		return ret;
+ 	}
  
  	con = ecpg_get_connection(connection_name);
  	if (!con)
! 	{
! 		ecpg_raise(line, ECPG_NO_CONN, ECPG_SQLSTATE_CONNECTION_DOES_NOT_EXIST,
! connection_name ? connection_name : ecpg_gettext("NULL"));
! 		return ret;
! 	}
  	prep = ecpg_find_prepared_statement(stmt_name, con, NULL);
  	if (!prep)
+ 	{
+ 		ecpg_raise(line, ECPG_INVALID_STMT, ECPG_SQLSTATE_INVALID_SQL_STATEMENT_NAME, stmt_name);
  		return ret;
+ 	}
  
  	va_start(args, stmt_name);
  

-- 
Sent 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 data in information_schema grant_* tables?

[Peter Eisentraut]

On fre, 2010-01-15 at 12:00 +0100, Fabien COELHO wrote:
> INSERT to PUBLIC is shown on the last line of the access privileges 
> column. However, when looking at the information_schema:
> 
>   psql> SELECT grantor, grantee, privilege_type
> FROM information_schema.role_table_grants
> WHERE table_name = 'foo';
>   grantor | grantee | privilege_type
> -+-+
>   fabien  | fabien  | SELECT
>   fabien  | fabien  | INSERT
>   fabien  | fabien  | UPDATE
>   fabien  | fabien  | DELETE
>   fabien  | fabien  | TRUNCATE
>   fabien  | fabien  | REFERENCES
>   fabien  | fabien  | TRIGGER
>   fabien  | calvin  | SELECT
> (8 rows)
> 
> My point is that the grant to "PUBLIC" does not show in the
> information 
> schema. However, it appears in the table_privileges view:
> 
>   psql> SELECT grantor, grantee, privilege_type FROM 
> information_schema.table_privileges WHERE table_name='foo';
>   grantor | grantee | privilege_type
> -+-+
>   ... same as previous query ...
>   fabien  | PUBLIC  | INSERT
> 
> (1) Would you agree that it is a "bug"? That is, if the grantee is
> PUBLIC, 
> it is an enabled role for the current user, so it should appear in
> the 
> role_table_grants view...

The whole point of role_table_grants is that it shows everything that
table_privileges shows except privileges granted to public.  So the
behavior you observe is correct.


-- 
Sent 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, loose ends

[Heikki Linnakangas]

Boszormenyi Zoltan wrote:
> Heikki Linnakangas írta:
>> I've now committed streaming replication. I moved the files around a
>> bit, and put the walreceiver/walsender stuff in a new
>> src/backend/replication subdirectory. [snip]
> 
> there's one loose end indeed.
> "make maintainer-clean" doesn't delete these:
> 
> src/backend/replication/walreceiver/walreceiver.o
> src/backend/replication/walreceiver/walreceiver.so
> src/backend/replication/walreceiver/.deps/

Hmm, I think I'm going to need some help with the Makefiles. Clearly the
way I hooked that directory to the build system was wrong. I think the
attached patch will fix that, but I wonder if there's a trick I'm missing.

Do the MSVC scripts need adjusting? 'red_bat' compiled fine, so I guess not.

Also, I'm seeing a failure in buildfarm member 'colugos':

/opt/local/bin/ccache /Developer/usr/bin/llvm-gcc-4.2 -no-cpp-precomp
-I/opt/local/include -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
-fwrapv -g  -bundle -multiply_defined suppress  walreceiver.o
-bundle_loader ../../../../src/backend/postgres
-L../../../../src/interfaces/libpq -L../../../../src/port
-L/opt/local/lib -lpq  -o walreceiver.so
ld: library not found for -lpq
collect2: ld returned 1 exit status
make[2]: *** [walreceiver.so] Error 1
make[2]: *** Waiting for unfinished jobs

I suspect that's because libpq isn't built yet. I have this:

> all: submake-libpq all-shared-lib

in src/backend/replication/walreceiver/Makefile, but is that not enough?

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
? GNUmakefile
? b
? config.log
? config.status
? config.status.lineno
? configure.lineno
? gin-splay-1.patch
? gin-splay-2.patch
? gin-splay-3.patch
? md-1.c
? md-1.patch
? temp-file-resowner-2.patch
? contrib/pgbench/fsynctest
? contrib/pgbench/fsynctest.c
? contrib/pgbench/fsynctestfile
? contrib/spi/.deps
? src/Makefile.global
? src/backend/aaa.patch
? src/backend/postgres
? src/backend/access/common/.deps
? src/backend/access/gin/.deps
? src/backend/access/gist/.deps
? src/backend/access/hash/.deps
? src/backend/access/heap/.deps
? src/backend/access/index/.deps
? src/backend/access/nbtree/.deps
? src/backend/access/transam/.deps
? src/backend/bootstrap/.deps
? src/backend/catalog/.deps
? src/backend/commands/.deps
? src/backend/executor/.deps
? src/backend/foreign/.deps
? src/backend/foreign/dummy/.deps
? src/backend/foreign/postgresql/.deps
? src/backend/lib/.deps
? src/backend/libpq/.deps
? src/backend/main/.deps
? src/backend/nodes/.deps
? src/backend/optimizer/geqo/.deps
? src/backend/optimizer/path/.deps
? src/backend/optimizer/plan/.deps
? src/backend/optimizer/prep/.deps
? src/backend/optimizer/util/.deps
? src/backend/parser/.deps
? src/backend/po/af.mo
? src/backend/po/cs.mo
? src/backend/po/hr.mo
? src/backend/po/hu.mo
? src/backend/po/it.mo
? src/backend/po/ko.mo
? src/backend/po/nb.mo
? src/backend/po/nl.mo
? src/backend/po/pl.mo
? src/backend/po/ro.mo
? src/backend/po/ru.mo
? src/backend/po/sk.mo
? src/backend/po/sl.mo
? src/backend/po/sv.mo
? src/backend/po/zh_CN.mo
? src/backend/po/zh_TW.mo
? src/backend/port/.deps
? src/backend/postmaster/.deps
? src/backend/regex/.deps
? src/backend/replication/.deps
? src/backend/replication/walreceiver/.deps
? src/backend/rewrite/.deps
? src/backend/snowball/.deps
? src/backend/snowball/snowball_create.sql
? src/backend/storage/buffer/.deps
? src/backend/storage/file/.deps
? src/backend/storage/freespace/.deps
? src/backend/storage/ipc/.deps
? src/backend/storage/large_object/.deps
? src/backend/storage/lmgr/.deps
? src/backend/storage/page/.deps
? src/backend/storage/smgr/.deps
? src/backend/tcop/.deps
? src/backend/tsearch/.deps
? src/backend/utils/.deps
? src/backend/utils/probes.h
? src/backend/utils/adt/.deps
? src/backend/utils/cache/.deps
? src/backend/utils/error/.deps
? src/backend/utils/fmgr/.deps
? src/backend/utils/hash/.deps
? src/backend/utils/init/.deps
? src/backend/utils/mb/.deps
? src/backend/utils/mb/Unicode/BIG5.TXT
? src/backend/utils/mb/Unicode/CP950.TXT
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc2004_sjis2004/.deps
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_jis_2004_and_shift_jis_2004/.deps
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
? src/backend

Re: [HACKERS] per-user pg_service.conf

[Christoph Berg]

> > I was surprised/annoyed to find out that there is no way to have
> > per-user pg_service.conf, something like ~/.pg_service.conf (well,
> > except by export PGSYSCONFDIR).  That would be easy to add.
> > Comments?
> 
> Here's a patch.  Perhaps those who had said they would like that can
> validate the behavior.

Hi,

I just tried the ~/.pg_service.conf patch and it does everything I'd
expect from it. It even improves the documentation to include a
services file example for which I had been looking several times
earlier.

There's not much I have to add, maybe the documentation could add a
pointer to what keywords are recognized:

| The file uses an "INI file" format where the section name is the
| service name and the parameters are connection parameters.

... (see Section 30.1 for a list).

Independently for what this patch changes, error reporting could be
more detailed, currently "syntax error in service file \"%s\", line
%d" is reported for "no = in line" and "keyword X is unknown". The
latter case deserves a different message, maybe like "keyword \"%s\"
is invalid in service file \"%s\", line %d".

Even without the proposed changed, I'd very much appreciate the patch
getting included.

Christoph
-- 
c...@df7cb.de | http://www.df7cb.de/


signature.asc
Description: Digital signature

Re: [HACKERS] ECPG DESCRIBE [OUTPUT] support

[Michael Meskes]

On Fri, Jan 15, 2010 at 01:16:18PM +0100, Boszormenyi Zoltan wrote:
> Please, also add this small change that adds ecpg_raise()
> calls to ECPGdescribe() to return the proper sqlca error
> in error paths for:
> ...

Done.

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! Forca Barca! 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: [HACKERS] Streaming replication status

[Kevin Grittner]

Greg Smith  wrote:
 
> to make it easier to monitor for out of disk errors that might
> prove catastrophic to replication.
 
We handle that with the fsutil functions (in pgfoundry).  This can
actually measure free space on each volume.  These weren't portable
enough to include in core, but maybe they could be made more
portable?
 
-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] Streaming replication, loose ends

[Heikki Linnakangas]

Heikki Linnakangas wrote:
> Also, I'm seeing a failure in buildfarm member 'colugos':
> 
> /opt/local/bin/ccache /Developer/usr/bin/llvm-gcc-4.2 -no-cpp-precomp
> -I/opt/local/include -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
> -fwrapv -g  -bundle -multiply_defined suppress  walreceiver.o
> -bundle_loader ../../../../src/backend/postgres
> -L../../../../src/interfaces/libpq -L../../../../src/port
> -L/opt/local/lib -lpq  -o walreceiver.so
> ld: library not found for -lpq
> collect2: ld returned 1 exit status
> make[2]: *** [walreceiver.so] Error 1
> make[2]: *** Waiting for unfinished jobs
> 
> I suspect that's because libpq isn't built yet. I have this:
> 
>> all: submake-libpq all-shared-lib
> 
> in src/backend/replication/walreceiver/Makefile, but is that not enough?

Yep. What's happening is that "make -j" starts building libpq and
walreceiver.so simultaneously, because of the above line in the
Makefile. We actually have the same problem in src/bin/*/Makefile, but
we don't notice it there because src/interfaces is listed before src/bin
in src/Makefile, so when you do "make -j" at the top-level, libpq is
built first. You get the same error if you do "make clean" at the
top-level, and then e.g "cd src/bin/scripts/; make -j"

So the simple fix would be to reorder the lines in src/Makefile, so that
src/interfaces is built before src/backend. Alternatively we could do this:

*** src/backend/replication/walreceiver/Makefile15 Jan 2010 09:19:03
-   1.1
--- src/backend/replication/walreceiver/Makefile15 Jan 2010 13:57:24 
-
***
*** 18,24 
  SHLIB_LINK = $(libpq)
  NAME = walreceiver

! all: submake-libpq all-shared-lib

  include $(top_srcdir)/src/Makefile.shlib

--- 18,28 
  SHLIB_LINK = $(libpq)
  NAME = walreceiver

! all: all-shared-lib
!
! # Compiling walreceiver.o doesn't really need libpq library,
! # only linking it does. But there's no easy way to specify that.
! walreceiver.o: submake-libpq

  include $(top_srcdir)/src/Makefile.shlib

And I guess all the other uses of submake-libpq should be changed similarly.

Am I missing a trick?

-- 
  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] missing data in information_schema grant_* tables?

[Fabien COELHO]

Dear Peter,

(1) Would you agree that it is a "bug"? That is, if the grantee is 
PUBLIC, it is an enabled role for the current user, so it should appear 
in the role_table_grants view...


The whole point of role_table_grants is that it shows everything that
table_privileges shows except privileges granted to public.  So the
behavior you observe is correct.


This is not my understanding of ISO/IEC 9075-11:2003(E), page 57 :

"5.39 ROLE_TABLE_GRANTS view

Function

Identifies the privileges on tables defined in this catalog that are 
available or granted by the currently applicable roles."


From the definition above, ISTM that a privilege granted to PUBLIC should 

also appear, both because it is granted by me and available to me.

Moreover, if I execute the SELECT of the view definition provided in the 
standard (a little bit simplified, and executed on the information schema 
instead of the "definition schema"), the PUBLIC stuff is displayed :


  psql> SELECT grantor, grantee, table_name
FROM information_schema.table_privileges
WHERE grantee IN (SELECT role_name FROM 
information_schema.enabled_roles)
   OR grantor IN (SELECT role_name FROM 
information_schema.enabled_roles);

   ...
   fabien   | calvin   | foo
   fabien   | PUBLIC   | foo

I think that the view definition in postgresql could simply reuse the view 
defined in the standard.


--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Testing with concurrent sessions

[Kevin Grittner]

Markus Wanner  wrote:
 
> I must admit that I haven't ever tested on python 2.6 before. I'll
> try that (especially as it's the staircase to 3.0, IIUC).
 
I don't use python much, so I can't comment on that.  I do see that
my system has these two versions on it, with a symlink that makes
2.6 the default.

Python 2.6.2 (release26-maint, Apr 19 2009, 01:56:41)
[GCC 4.3.3] on linux2
 
Python 3.0.1+ (r301:69556, Apr 15 2009, 15:59:22)
[GCC 4.3.3] on linux2
 
I haven't quite gotten it to work yet; I'll start over with 3.0 and
see how it goes.  I'll also attach the results of the 2.6 attempt.
 
> Try a CursesReporter() instead, it gives much nicer output!
 
Thanks, I'll try it.
 
A few other issues in testing so far:
 
(1)  I see that a 'make dcheck' does a 'make install'.  That's not
right.  For one thing I usually install in a location where I need
to sudo to install; but more importantly, I want to do all checks
*before* I install.  It's easy enough to work around that for now,
but I don't think it's acceptable long-term.
 
(2)  After a 'make dcheck' failure, the cluster created for the
testing is left running.
 
(3)  If the install could check dependencies, report problems, and
refuse to install without required packages, that would be less
confusing for python novices (like me).
 
Perhaps some of these problems will go away with python 3.0, but I
figured I should pass the info along.
 
Thanks again for this.  It should help me a lot.
 
-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] Testing with concurrent sessions

[Kevin Grittner]

I wrote:
 
> I'll also attach the results of the 2.6 attempt.
 
Let's try that again.
 
-Kevin
kgri...@kgrittn-desktop:~/git/postgresql/kgrittn$ make dcheck
make -C src/test dcheck  
make[1]: Entering directory `/home/kgrittn/git/postgresql/kgrittn/src/test'
make -C regress dcheck 
make[2]: Entering directory 
`/home/kgrittn/git/postgresql/kgrittn/src/test/regress'
./pg_dtester.py --temp-install --top-builddir=../../.. \
   
--multibyte=SQL_ASCII   
   
Postgres dtester suiteCopyright (c) 2004-2010, by Markus Wanner 
   

temp_install: creating temporary installation
initdb-0: initializing database system 0 
pg-0: starting database system 0 
testdb: creating database testdb at server 0 
conn-0A: connecting to database testdb at server 0
conn-0B: connecting to database testdb at server 0
conn-0C: connecting to database testdb at server 0
test-conn-0A: test started
test-conn-0B: test started
test-conn-0C: test started
startup of test test-conn-0A failed, skipping.

Traceback (most recent call last):
  File "/usr/lib/python2.6/dist-packages/twisted/internet/base.py", line 757, 
in runUntilCurrent
call.func(*call.args, **call.kw)

  File "/usr/local/lib/python2.6/dist-packages/dtester/runner.py", line 62, in 
checkTimeout 
TimeoutError("TIMEOUT: %s!" % self.msg)))   

  File "/usr/lib/python2.6/dist-packages/twisted/internet/defer.py", line 269, 
in errback   
self._startRunCallbacks(fail)   

  File "/usr/lib/python2.6/dist-packages/twisted/internet/defer.py", line 312, 
in _startRunCallbacks
self._runCallbacks()

---  --- 

  File "/usr/lib/python2.6/dist-packages/twisted/internet/defer.py", line 328, 
in _runCallbacks 
self.result = callback(self.result, *args, **kw)

  File "/usr/local/lib/python2.6/dist-packages/dtester/runner.py", line 186, in 
cbTestFailed
self.reporter.stopTest(tname, test, False, failure) 

  File "/usr/local/lib/python2.6/dist-packages/dtester/reporter.py", line 92, 
in stopTest   
row = tb.pop()  

exceptions.IndexError: pop from empty list  

startup of test test-conn-0B failed, skipping.  


Traceback (most recent call last):
  File "/usr/lib/python2.6/dist-packages/twisted/internet/base.py", line 757, 
in runUntilCurrent
call.func(*call.args, **call.kw)

  File "/usr/local/lib/python2.6/dist-packages/dtester/runner.py", line 62, in 
checkTimeout 
TimeoutError("TIMEOUT: %s!" % self.msg)))   

  File "/usr/lib/python2.6/dist-packages/twisted/internet/defer.py", line 269, 
in errback   
self._startRunCallbacks(fail)   

  File "/usr/lib/python2.6/dist-packages/twisted/internet/defer.py", line 312, 
in _startRunCallbacks
self._runCallbacks()

---  --- 

  File "/usr/lib/python2.6/dist-packages/twisted/internet/defer.py", line 328, 
in _runCallbacks 
self.result = callback(self.result, *args, **kw)

  File "/usr/local/lib/python2.6/dist-packages/dtester/runner.py", line 186, in 
cbTestFailed
self.reporter.stopTest(tname, test, False, failure) 

  File "/usr/local/lib/python2.6/dist-packages/dtester/reporter.py", line 92, 
in stopTest   
row = tb.pop()  

exceptions.IndexError: pop from empty list  

startup of test test-conn-0C failed, skipping.  


Traceback (most recent call last):
  File "/usr/lib/python2.6/dist-packages/twisted/internet/base.py", line 757, 
in runUntilCurrent
call.func(*call.args, **call.kw)
  File "/usr/local/lib/python2.6/dist-pac

Re: [HACKERS] Testing with concurrent sessions

[Markus Wanner]

Hi,

Quoting "Kevin Grittner" :

I haven't quite gotten it to work yet; I'll start over with 3.0 and
see how it goes.


Let's stick to 2.x versions, first...


I'll also attach the results of the 2.6 attempt.


Thanks, that looks already pretty promising. ;-)


A few other issues in testing so far:

(1)  I see that a 'make dcheck' does a 'make install'.  That's not
right.  For one thing I usually install in a location where I need
to sudo to install; but more importantly, I want to do all checks
*before* I install.  It's easy enough to work around that for now,
but I don't think it's acceptable long-term.


It does: "temp_install: creating temporary installation" means it's  
running make install in the background.



(2)  After a 'make dcheck' failure, the cluster created for the
testing is left running.


That counts as a bug. I also get that from time to time (and with  
Postgres-R testing on 3+ instances, it's even more annoying).


Note that the error just before that is, that a psql process it starts  
cannot connect to its postmaster ("startup of test test-conn-0A  
failed, skipping.") Please check the log  
(src/test/regress/dtester.log) for why that failed in the first place.  
Can you connect manually to the database (that's still running after a  
make dcheck)?



(3)  If the install could check dependencies, report problems, and
refuse to install without required packages, that would be less
confusing for python novices (like me).


I'm not exactly a distutils hacker... Anybody else got any clue here?


Perhaps some of these problems will go away with python 3.0, but I
figured I should pass the info along.


I'd rather suspect that more of them will arise.

Regards

Markus


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Testing with concurrent sessions

[Kevin Grittner]

"Markus Wanner"  wrote:
> Quoting "Kevin Grittner" :
>> I haven't quite gotten it to work yet; I'll start over with 3.0
>> and see how it goes.
>
> Let's stick to 2.x versions, first...
 
OK
 
> It does: "temp_install: creating temporary installation" means
> it's running make install in the background.
 
OK, sorry for misreading that.
 
>> (2)  After a 'make dcheck' failure, the cluster created for the
>> testing is left running.
>
> That counts as a bug. I also get that from time to time (and with
> Postgres-R testing on 3+ instances, it's even more annoying).
>
> Note that the error just before that is, that a psql process it
> starts cannot connect to its postmaster ("startup of test
> test-conn-0A failed, skipping.") Please check the log
> (src/test/regress/dtester.log) for why that failed in the first
> place.
 
Not sure what's relevant there.  Entire file tarball attached.
 
> Can you connect manually to the database (that's still running
> after a make dcheck)?
 
Yes I can.  Any queries you'd like me to run in there?
 
-Kevin


dtester.log.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

Re: [HACKERS] Testing with concurrent sessions

[Kevin Grittner]

Markus Wanner  wrote:
 
> Second: at the very end of pg_dtester.py, you find the line:
>   reporter = StreamReporter()
> 
> Try a CursesReporter() instead, it gives much nicer output!
 
When I try to do that, Kate complains (I'm even copying their typo):
 
You are trying to save a python file as non ASCII, without
specifiying a correct source encoding line for encoding "utf-8"
 
It offers these options:
 
Insert: # -*- coding: utf-8 -*-
Save Nevertheless
Cancel

Should that coding line be in there?
 
-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] About "Our CLUSTER implementation is pessimal" patch

[Leonardo F]

> Yeah, I think you could do that, I agree it feels better that way.
> You'll still need new copytup and comparetup functions, though, to deal
> with HeapTupleHeaders instead of MinimalTuples, or modify the existing
> ones to handle both. 

You meant HeapTuple, not HeapTupleHeaders, right?

Mmh, didn't think of those two functions; I might as well start with Gregory
Stark's patch (that is: using HeapTuple)

> And some way to indicate that you want to preserve
> the visibility information when you create the tuplesort, maybe a new
> parameter to tuplesort_begin_heap().

I guess that using Gregory Stark's patch there's no need for it, since it uses
HeapTuples, right?

A patch that:

1) uses always the old CLUSTER method for non-btree indexes and for
expression indexes
2) add a whole set of new functions to tuplesort (as in Gregory Stark's patch)

would be rejected "for sure"? Or can be thought as a "better than nothing,
works in 90% cases" patch?


Leonardo




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New XLOG record indicating WAL-skipping

[Greg Stark]

On Fri, Jan 15, 2010 at 11:28 AM, Heikki Linnakangas
 wrote:
> I can see that it
> was required to avoid the flooding from heap_insert(), but we can move
> the XLogSkipLogging() call from heap_insert() to heap_sync().
>
> Attached is an updated patch, doing the above. Am I missing anything?

Hm, perhaps the timing is actually important? What if someone takes a
hot backup while an unlogged operation is in progress. The checkpoint
can occur and finish and the backup finish all while the unlogged
operation is happening. Then the replica can start restoring archived
logs from that point forward. In the original coding it sounds like
the replica would never notice the unlogged operation which might not
have been synced before the start of the initial hot backup.  If the
record occurs when the sync begins then the replica would be in
trouble if the checkpoint begins before the operation completed but
finished after the sync began and the record was emitted.

It seems like it's important that the record occur only after the sync
*completes* to be sure that if the replica doesn't see the record then
it knows the sync was done before its initial backup image was taken.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Tom Lane]

Heikki Linnakangas  writes:
> Yep. What's happening is that "make -j" starts building libpq and
> walreceiver.so simultaneously, because of the above line in the
> Makefile. We actually have the same problem in src/bin/*/Makefile, but
> we don't notice it there because src/interfaces is listed before src/bin
> in src/Makefile, so when you do "make -j" at the top-level, libpq is
> built first.

I'm actually fairly uncomfortable with the notion that something buried
deep within the src/backend tree is going to reach over and cause libpq
to get built.  Maybe the real answer is that you put walreceiver in the
wrong place, and it ought to be under src/bin/.

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] Testing with concurrent sessions

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE- 
Hash: RIPEMD160


> Dead or not, it still works, even against 8.4. I have many programs
> that use it. It's simply a wrapper around the libpq interface and as
> long as the libpq interface remains stable (which we go to great pains
> to do), so will this module.

Well, I stand corrected. Good to know.

> Given the talk of importing some perl module into the postgresql tree
> it just seemed more logical to me to take something that was close to
> libpq and had no external dependancies than taking a module with an
> external dependancy (namely DBI).

Yes, I could see that. Actually, I just came across another one
by Hiroyuki OYAMA and Aaron Crane. This was last updated January 10, 2010! :

http://search.cpan.org/~arc/DBD-PgPP-0.08/

Still requires DBI of course, but no Perl library or compiling required
as DBD::Pg does. So we've not got three valid options. :)

- --
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201001151129
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAktQmI0ACgkQvJuQZxSWSsgNugCgjwkT9QwGpvhcIXCNYhRcTwSW
JZcAnjvrsjwpO/QvJ1LzU+cUZ4UqajxV
=bu4q
-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] New XLOG record indicating WAL-skipping

[Heikki Linnakangas]

Greg Stark wrote:
> What if someone takes a hot backup while an unlogged operation is in progress.

Can't do that, pg_start_backup() throws an error if archive_mode=off.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Heikki Linnakangas]

Tom Lane wrote:
> Heikki Linnakangas  writes:
>> Yep. What's happening is that "make -j" starts building libpq and
>> walreceiver.so simultaneously, because of the above line in the
>> Makefile. We actually have the same problem in src/bin/*/Makefile, but
>> we don't notice it there because src/interfaces is listed before src/bin
>> in src/Makefile, so when you do "make -j" at the top-level, libpq is
>> built first.
> 
> I'm actually fairly uncomfortable with the notion that something buried
> deep within the src/backend tree is going to reach over and cause libpq
> to get built.  Maybe the real answer is that you put walreceiver in the
> wrong place, and it ought to be under src/bin/.

That feels even more wrong to me. Walreceiver is a postmaster
subprocess, tightly integrated with the rest of the backend.

One can argue that it shouldn't be, and walreceiver process should call
libpq through some new API, and the builtin implementation of that API
which uses libpq would be a loadable module that could be in src/bin/ or
contrib. Greg Stark requested that earlier. But I don't want to start
designing such an API at this point.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication status

[Greg Smith]

Stefan Kaltenbrunner wrote:

Greg Smith wrote:


The other popular request that keeps popping up here is  providing an 
easy way to see how backlogged the archive_command is, to make it 
easier to monitor for out of disk errors that might prove 
catastrophic to replication.


I tend to disagree - in any reasonable production setup basic stulff 
like disk space usage is monitored by non-application specific matters.
While monitoring backlog might be interesting for other reasons, 
citing disk space usage/exhaustions seems just wrong.


I was just mentioning that one use of the data, but there are others.  
Let's say that your archive_command works by copying things over to a 
NFS mount, and the mount goes down.  It could be a long time before you 
noticed this via disk space monitoring.  But if you were monitoring "how 
long has it been since the last time pg_last_archived_xlogfile() 
changed?", this would jump right out at you.


Another popular question is "how far behind real-time is the archiver 
process?"  You can do this right now by duplicating the same xlog file 
name scanning and sorting that the archiver does in your own code, 
looking for .ready files.  It would be simpler if you could call 
pg_last_archived_xlogfile() and then just grab that file's timestamp.


I think it's also important to consider the fact that diagnostic 
internals exposed via the database are far more useful to some people 
than things you have to setup outside of it.  You talk about reasonable 
configurations above, but some production setups are not so reasonable.  
In many of the more secure environments I've worked in (finance, 
defense), there is *no* access to the database server beyond what comes 
out of port 5432 without getting a whole separate team of people 
involved.  If the DBA can write a simple monitoring program themselves 
that presents data via the one port that is exposed, that makes life 
easier for them.  This same issue pops up sometimes when we consider the 
shared hosting case too, where the user may not have the option of 
running a full-fledged monitoring script.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication status

[Kevin Grittner]

Greg Smith  wrote:
 
> In many of the more secure environments I've worked in (finance, 
> defense), there is *no* access to the database server beyond what
> comes out of port 5432 without getting a whole separate team of
> people involved.  If the DBA can write a simple monitoring program
> themselves that presents data via the one port that is exposed,
> that makes life easier for them.
 
Right, we don't want to give the monitoring software an OS login for
the database servers, for security reasons.
 
-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] Streaming replication, loose ends

[Robert Haas]

On Fri, Jan 15, 2010 at 11:47 AM, Heikki Linnakangas
 wrote:
> Tom Lane wrote:
>> Heikki Linnakangas  writes:
>>> Yep. What's happening is that "make -j" starts building libpq and
>>> walreceiver.so simultaneously, because of the above line in the
>>> Makefile. We actually have the same problem in src/bin/*/Makefile, but
>>> we don't notice it there because src/interfaces is listed before src/bin
>>> in src/Makefile, so when you do "make -j" at the top-level, libpq is
>>> built first.
>>
>> I'm actually fairly uncomfortable with the notion that something buried
>> deep within the src/backend tree is going to reach over and cause libpq
>> to get built.  Maybe the real answer is that you put walreceiver in the
>> wrong place, and it ought to be under src/bin/.
>
> That feels even more wrong to me. Walreceiver is a postmaster
> subprocess, tightly integrated with the rest of the backend.

The major problem with having one part of the tree depend on a
completely different part of the tree is that it's easy for the
dependencies to be wrong.  If the backend depends on libpq, then it
depends implicitly on all the things on which libpq depends.  If
something that libpq depends on, but that the backend does not depend
on directly, gets updated, does the backend get rebuilt?  It's easy to
get this wrong.  On the other hand, it's also possible to get it
right.  If we can decide what we want to happen, I'm willing to take a
crack at it, though if you or Tom or Peter prefer to do it that is
certainly OK with me too.

...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] Streaming replication, loose ends

[Tom Lane]

Heikki Linnakangas  writes:
> Tom Lane wrote:
>> I'm actually fairly uncomfortable with the notion that something buried
>> deep within the src/backend tree is going to reach over and cause libpq
>> to get built.  Maybe the real answer is that you put walreceiver in the
>> wrong place, and it ought to be under src/bin/.

> That feels even more wrong to me. Walreceiver is a postmaster
> subprocess, tightly integrated with the rest of the backend.

[ shrug... ]  pg_dump, to take one example, is considerably more
"tightly integrated" with the backend than walreceiver is.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Heikki Linnakangas]

Robert Haas wrote:
> On Fri, Jan 15, 2010 at 11:47 AM, Heikki Linnakangas
>  wrote:
>> Tom Lane wrote:
>>> Heikki Linnakangas  writes:
 Yep. What's happening is that "make -j" starts building libpq and
 walreceiver.so simultaneously, because of the above line in the
 Makefile. We actually have the same problem in src/bin/*/Makefile, but
 we don't notice it there because src/interfaces is listed before src/bin
 in src/Makefile, so when you do "make -j" at the top-level, libpq is
 built first.
>>> I'm actually fairly uncomfortable with the notion that something buried
>>> deep within the src/backend tree is going to reach over and cause libpq
>>> to get built.  Maybe the real answer is that you put walreceiver in the
>>> wrong place, and it ought to be under src/bin/.
>> That feels even more wrong to me. Walreceiver is a postmaster
>> subprocess, tightly integrated with the rest of the backend.
> 
> The major problem with having one part of the tree depend on a
> completely different part of the tree is that it's easy for the
> dependencies to be wrong.  If the backend depends on libpq, then it
> depends implicitly on all the things on which libpq depends.  If
> something that libpq depends on, but that the backend does not depend
> on directly, gets updated, does the backend get rebuilt?  

The backend doesn't get rebuilt, and it doesn't need to be. The fact
that walreceiver is a dynamically loaded module should isolate changes
in libpq or its dependencies from affecting the rest of the backend.

I moved the line for src/backend/replication/walreceiver in src/Makefile
further down, after src/interfaces. That should fix the build failures
for now, but I'm all ears if there's better suggestions.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Tom Lane]

Heikki Linnakangas  writes:
> I moved the line for src/backend/replication/walreceiver in src/Makefile
> further down, after src/interfaces. That should fix the build failures
> for now, but I'm all ears if there's better suggestions.

Yeah, I saw.  Seems like a reasonable solution for now.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Joshua D. Drake]

On Fri, 2010-01-15 at 05:36 -0500, Bruce Momjian wrote:
> Heikki Linnakangas wrote:
> > I've now committed streaming replication. I moved the files around a
> > bit, and put the walreceiver/walsender stuff in a new
> > src/backend/replication subdirectory. There's enough stuff there already
> > to deserve a new subdirectory, and if we add the capability for
> > streaming base backups etc. that has been discussed, we will have more
> > code in there.
> > 
> > But it's not time to party yet. There's still a few loose ends we need
> > to address:
> > 
> > Documentation. The patch originally moved around some sections, but I
> > didn't include that in the committed version, to make it clear in the
> > diff what exactly was added/changed. But I do agree with the original
> > thought of adding a new "Replication" chapter, and moving all the
> > replication and standby related stuff there from the "Backup and
> > Restore" chapter, so let's start working on that.
> 
> Uh, do we really want to call this "replication" rather than archive log
> streaming or something.  It seems "replication" is a generic term and
> will confuse people who are using other replication solutions like
> Slony.

+1, it is not replication. I would call it something like continuous
archiving or streaming pitr

Joshua D. Drake


> 
> -- 
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
> 
>   + If your life is a hard drive, Christ can be your backup. +
> 


-- 
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] Streaming replication, loose ends

[Robert Haas]

On Fri, Jan 15, 2010 at 12:17 PM, Joshua D. Drake  
wrote:
>> Uh, do we really want to call this "replication" rather than archive log
>> streaming or something.  It seems "replication" is a generic term and
>> will confuse people who are using other replication solutions like
>> Slony.
>
> +1, it is not replication. I would call it something like continuous
> archiving or streaming pitr

Of course "PITR" does stand for "point-in-time replication"...

...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] Streaming replication status

[Stefan Kaltenbrunner]

Greg Smith wrote:

Stefan Kaltenbrunner wrote:

Greg Smith wrote:


The other popular request that keeps popping up here is  providing an 
easy way to see how backlogged the archive_command is, to make it 
easier to monitor for out of disk errors that might prove 
catastrophic to replication.


I tend to disagree - in any reasonable production setup basic stulff 
like disk space usage is monitored by non-application specific matters.
While monitoring backlog might be interesting for other reasons, 
citing disk space usage/exhaustions seems just wrong.


I was just mentioning that one use of the data, but there are others.  
Let's say that your archive_command works by copying things over to a 
NFS mount, and the mount goes down.  It could be a long time before you 
noticed this via disk space monitoring.  But if you were monitoring "how 
long has it been since the last time pg_last_archived_xlogfile() 
changed?", this would jump right out at you.


well from an syadmin perspective you have to monitor the NFS mount 
anyway - so why do you need the database to do too(and not in a sane way 
because there is no way the database can even figure out what the real 
problem is and if there is one)?




Another popular question is "how far behind real-time is the archiver 
process?"  You can do this right now by duplicating the same xlog file 
name scanning and sorting that the archiver does in your own code, 
looking for .ready files.  It would be simpler if you could call 
pg_last_archived_xlogfile() and then just grab that file's timestamp.


well that one seems a more reasonable reasoning to me however I'm not so 
sure that the proposed implementation feels right - though can't come up 
with a better suggestion for now.




I think it's also important to consider the fact that diagnostic 
internals exposed via the database are far more useful to some people 
than things you have to setup outside of it.  You talk about reasonable 
configurations above, but some production setups are not so reasonable.  
In many of the more secure environments I've worked in (finance, 
defense), there is *no* access to the database server beyond what comes 
out of port 5432 without getting a whole separate team of people 
involved.  If the DBA can write a simple monitoring program themselves 
that presents data via the one port that is exposed, that makes life 
easier for them.  This same issue pops up sometimes when we consider the 
shared hosting case too, where the user may not have the option of 
running a full-fledged monitoring script.


well again I consider stuff like "available diskspace" or "NFS mount 
available" completely in the realm of the OS level management. The 
database side should focus on the stuff that concerns the internal state 
and operation of the database app itself.
If you continue your line of thought you will have to add all kind of 
stuff to the database, like CPU usage tracking, getting information 
about running processes, storage health.
As soon as you are done you have reimplemented nagios-plugins over SQL 
on port 5432 instead of NRPE(or SNMP or whatnot).
Again I fully understand and know that there are environments where the 
DBA does not have OS level (be it root or no shell at all) access has to 
the OS but even if you had that "archiving is hanging" function you 
would still have to go back to that "completely different group" and 
have them diagnose again.
So my point is - that even if you have disparate groups of people being 
responsible for different parts of a system solution you can't really 
work around incompetency(or slownest or whatever) of the group 
responsible for the lower layer by adding partial and inexact 
functionality at the upper part that can only guess what the real issue is.



Stefan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Heikki Linnakangas]

Bruce Momjian wrote:
> Heikki Linnakangas wrote:
>> I've now committed streaming replication. I moved the files around a
>> bit, and put the walreceiver/walsender stuff in a new
>> src/backend/replication subdirectory. There's enough stuff there already
>> to deserve a new subdirectory, and if we add the capability for
>> streaming base backups etc. that has been discussed, we will have more
>> code in there.
>>
>> But it's not time to party yet. There's still a few loose ends we need
>> to address:
>>
>> Documentation. The patch originally moved around some sections, but I
>> didn't include that in the committed version, to make it clear in the
>> diff what exactly was added/changed. But I do agree with the original
>> thought of adding a new "Replication" chapter, and moving all the
>> replication and standby related stuff there from the "Backup and
>> Restore" chapter, so let's start working on that.
> 
> Uh, do we really want to call this "replication" rather than archive log
> streaming or something.  It seems "replication" is a generic term and
> will confuse people who are using other replication solutions like
> Slony.

Good question. OTOH, if we move the sections about setting up a
file-shipping based standby with pg_standby, that's not streaming.

What we have now is:

Server Administration
  ...
  Backup and Restore
SQL Dump
File System Level Backup
Continuous Archiving and Point-In-Time Recovery (PITR)
Warm Standby Servers for High Availability
  Planning
  Implementation
  Failover
  Record-based Log Shipping
  Streaming Replication
  Incrementally Updated Backups
Hot Standby
  User's Overview
  Handling query conflicts
  Administrator's Overview
  Hot Standby Parameter Reference
  Caveats
  High Availability, Load Balancing, and Replication

I propose:

Server Administration
  Backup and Restore
SQL Dump
File System Level Backup
Continuous Archiving and Point-In-Time Recovery (PITR)
  High Availability, Load Balancing, and Replication
Introduction
  Comparison of different solutions (*)
File-based Log Shipping
  Planning
  Implementation
Streaming Replication
  Setting up
Failover
Hot Standby
  User's Overview
  Handling query conflicts
  Administrator's Overview
  Hot Standby Parameter Reference
  Caveats
Incrementally Updated Backups

(*) Current content of "High Availability, Load Balancing, and
Replication" chapter goes here

Note that I propose to remove "Record-based Log Shipping" section
altogether. We can briefly mention that method under Streaming
Replication, but I consider that obsolete with streaming replication.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Heikki Linnakangas]

Robert Haas wrote:
> On Fri, Jan 15, 2010 at 12:17 PM, Joshua D. Drake  
> wrote:
>>> Uh, do we really want to call this "replication" rather than archive log
>>> streaming or something.  It seems "replication" is a generic term and
>>> will confuse people who are using other replication solutions like
>>> Slony.
>> +1, it is not replication. I would call it something like continuous
>> archiving or streaming pitr
> 
> Of course "PITR" does stand for "point-in-time replication"...

I'm not sure if you're joking, but PITR actually stands for
Point-In-Time *Recovery*.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication status

[Stefan Kaltenbrunner]

Kevin Grittner wrote:

Greg Smith  wrote:
 
In many of the more secure environments I've worked in (finance, 
defense), there is *no* access to the database server beyond what

comes out of port 5432 without getting a whole separate team of
people involved.  If the DBA can write a simple monitoring program
themselves that presents data via the one port that is exposed,
that makes life easier for them.
 
Right, we don't want to give the monitoring software an OS login for

the database servers, for security reasons.


depending on what you exactly mean by that I do have to wonder how you 
monitor more complex stuff (or stuff that require elevated privs) - say 
raid health, multipath configuration, status of OS level updates, "are 
certain processes running or not" as well as basic parameters like CPU 
or IO load. as in stuff you cannot know usless you have it exported 
through "some" port.



Stefan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Joshua D. Drake]

On Fri, 2010-01-15 at 19:30 +0200, Heikki Linnakangas wrote:
> Robert Haas wrote:
> > On Fri, Jan 15, 2010 at 12:17 PM, Joshua D. Drake  
> > wrote:
> >>> Uh, do we really want to call this "replication" rather than archive log
> >>> streaming or something.  It seems "replication" is a generic term and
> >>> will confuse people who are using other replication solutions like
> >>> Slony.
> >> +1, it is not replication. I would call it something like continuous
> >> archiving or streaming pitr
> > 
> > Of course "PITR" does stand for "point-in-time replication"...
> 
> I'm not sure if you're joking, but PITR actually stands for
> Point-In-Time *Recovery*.

Right.

Joshua D. Drake


> 


-- 
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] Streaming replication, loose ends

[Robert Haas]

On Fri, Jan 15, 2010 at 12:30 PM, Heikki Linnakangas
 wrote:
> Robert Haas wrote:
>> On Fri, Jan 15, 2010 at 12:17 PM, Joshua D. Drake  
>> wrote:
 Uh, do we really want to call this "replication" rather than archive log
 streaming or something.  It seems "replication" is a generic term and
 will confuse people who are using other replication solutions like
 Slony.
>>> +1, it is not replication. I would call it something like continuous
>>> archiving or streaming pitr
>>
>> Of course "PITR" does stand for "point-in-time replication"...
>
> I'm not sure if you're joking, but PITR actually stands for
> Point-In-Time *Recovery*.

Oops.  No, not joking, just wrong.

But I'm still wondering why this isn't replication.

...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] Streaming replication, loose ends

[David E. Wheeler]

On Jan 15, 2010, at 9:37 AM, Robert Haas wrote:

> But I'm still wondering why this isn't replication.

I was wondering the same thing. ISTM that the docs could reference third-party 
replication solutions, too (or a wiki page listing them, since they'll change 
often).

Anyway, I think Heikki's proposed chapter name covers it:

> High Availability, Load Balancing, and Replication

Works for me.

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] Streaming replication, loose ends

[David Fetter]

On Fri, Jan 15, 2010 at 12:11:01PM -0500, Tom Lane wrote:
> Heikki Linnakangas  writes:
> > I moved the line for src/backend/replication/walreceiver in
> > src/Makefile further down, after src/interfaces. That should fix
> > the build failures for now, but I'm all ears if there's better
> > suggestions.
> 
> Yeah, I saw.  Seems like a reasonable solution for now.

We can always cvs mv...oh, wait! ;)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Application name patch - v3

[Guillaume Lelarge]

Le 08/01/2010 23:22, Guillaume Lelarge a écrit :
> Le 07/01/2010 19:13, Robert Haas a écrit :
>> On Thu, Jan 7, 2010 at 10:33 AM, Guillaume Lelarge
>>  wrote:
>>> Le 04/01/2010 22:36, Guillaume Lelarge a écrit :
 Le 29/12/2009 14:12, Guillaume Lelarge a écrit :
> Le 29/12/2009 00:03, Guillaume Lelarge a écrit :
>> Le 28/12/2009 22:59, Tom Lane a écrit :
>>> Guillaume Lelarge  writes:
 Le 28/12/2009 17:06, Tom Lane a écrit :
> I think we were stalled on the question of whether to use one array
> or two parallel arrays.  Do you want to try coding up a sample usage
> of each possibility so we can see which one seems more useful?
>>>
 I'm interested in working on this. But I don't find the thread that 
 talk
 about this.
>>>
>>> Try here
>>> http://archives.postgresql.org/message-id/4aae8ccf.9070...@esilo.com
>>>
>>
>> Thanks. I've read all the "new version of PQconnectdb" and "Determining
>> client_encoding from client locale" threads. I think I understand the
>> goal. Still need to re-read this one
>> (http://archives.postgresql.org/message-id/6222.1253734...@sss.pgh.pa.us)
>>  and
>> completely understand it (will probably need to look at the code, at
>> least the PQconnectdb one). But I'm definitely working on this.
>>
>
> If I try to sum up my readings so far, this is what we still have to do:
>
> 1. try the one-array approach
>PGconn *PQconnectParams(const char **params)
>
> 2. try the two-arrays approach
>PGconn *PQconnectParams(const char **keywords, const char **values)
>
> Instead of doing a wrapper around PQconnectdb, we need to refactor the
> whole function, so that we can get rid of the parsing of the conninfo
> string (which is quite complicated).
>
> Using psql as an example would be a good idea, AFAICT.
>
> Am I right? did I misunderstand or forget something?
>

 I supposed I was right since noone yell at me :)

 I worked on this tonight. You'll find two patches attached, one for the
 one-array approach, one for the two-arrays approach. I know some more
 factoring can be done (at least, the "get the fallback resources..."
 part). I'm OK to do them. I just need to know if I'm on the right track.

>>>
>>> Hmmm... sorry but... can i have some comments on these two patches, please?
>>
>> I would suggest adding your patch(es) to:
>>
>> https://commitfest.postgresql.org/action/commitfest_view/open
>>
>> Probably just one entry for the two of them would be most appropriate.
>>
> 
> Done. Thanks.
> 

New patches because the old ones didn't apply anymore, due to recent CVS
commits.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com
Index: src/bin/psql/startup.c
===
RCS file: /opt/cvsroot_postgresql/pgsql/src/bin/psql/startup.c,v
retrieving revision 1.158
diff -c -p -c -r1.158 startup.c
*** src/bin/psql/startup.c	2 Jan 2010 16:57:59 -	1.158
--- src/bin/psql/startup.c	4 Jan 2010 21:04:13 -
*** main(int argc, char *argv[])
*** 171,181 
  	/* loop until we have a password if requested by backend */
  	do
  	{
! 		new_pass = false;
! 		pset.db = PQsetdbLogin(options.host, options.port, NULL, NULL,
! 	options.action == ACT_LIST_DB && options.dbname == NULL ?
! 			   "postgres" : options.dbname,
! 			   options.username, password);
  
  		if (PQstatus(pset.db) == CONNECTION_BAD &&
  			PQconnectionNeedsPassword(pset.db) &&
--- 171,190 
  	/* loop until we have a password if requested by backend */
  	do
  	{
! const char *params[] = {
!   "host", options.host,
!   "port", options.port,
!   "dbname", (options.action == ACT_LIST_DB && 
!options.dbname == NULL) ? "postgres" : options.dbname,
!   "user", options.username,
!   "password", password,
!   "application_name", pset.progname,
!   NULL, NULL
!   };
! 
! new_pass = false;
! 
! pset.db = PQconnectdbParams(params);
  
  		if (PQstatus(pset.db) == CONNECTION_BAD &&
  			PQconnectionNeedsPassword(pset.db) &&
Index: src/interfaces/libpq/exports.txt
===
RCS file: /opt/cvsroot_postgresql/pgsql/src/interfaces/libpq/exports.txt,v
retrieving revision 1.23
diff -c -p -c -r1.23 exports.txt
*** src/interfaces/libpq/exports.txt	31 Mar 2009 01:41:27 -	1.23
--- src/interfaces/libpq/exports.txt	4 Jan 2010 20:51:13 -
*** PQresultSetInstanceData   150
*** 153,155 
--- 153,157 
  PQfireResultCreateEvents  151
  PQconninfoParse   152
  PQinitOpenSSL 153
+ PQconnectdbParams 154
+ PQconnectStartParams  155
Index: src/interfac

Re: [HACKERS] Streaming replication, loose ends

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


 +1, it is not replication. I would call it something like continuous
 archiving or streaming pitr

I agree we should consider a different name.

> But I'm still wondering why this isn't replication.

Because replication is an ambiguous and overloaded term.

On the other hand, it's a great buzzword, so we should use
the phrase as much as possible in the press releases. :)

- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201001151257
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAktQrNYACgkQvJuQZxSWSsj8NACgwIQVq/GGQoY/4U6pAYyR5IeS
EsoAoLjoYlLErv4g3Vy65rbA9u9W0vww
=u2Hk
-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] Streaming replication status

[Kevin Grittner]

Stefan Kaltenbrunner  wrote:
> Kevin Grittner wrote:
 
>> Right, we don't want to give the monitoring software an OS login
>> for the database servers, for security reasons.
> 
> depending on what you exactly mean by that I do have to wonder how
> you monitor more complex stuff (or stuff that require elevated
> privs) - say raid health, multipath configuration, status of OS
> level updates, "are certain processes running or not" as well as
> basic parameters like CPU or IO load. as in stuff you cannot know
> usless you have it exported through "some" port.
 
Many of those are monitored on the server one way or another,
through a hardware card accessible only to the DBAs.  The card sends
an email to the DBAs for any sort of distress, including impending
or actual drive failure, ambient temperature out of bounds, internal
or external power out of bounds, etc.  OS updates are managed by the
DBAs through scripts.  Ideally we would tie these in to our opcenter
software, which displays status through hundreds of "LED" boxes on
big plasma displays in our support areas (and can send emails and
jabber messages when things get to a bad state), but since the
messages are getting to the right people in a timely manner, this is
a low priority as far as monitoring enhancement requests go.
 
Only the DBAs have OS logins to database servers.  Monitoring
software must deal with application ports (which have to be open
anyway, so that doesn't add any security risk).  Since the hardware
monitoring doesn't know about file systems, and the disk space on
database servers is primarily an issue for the database, it made
sense to us to add the ability to check the space available to the
database through a database connection.  Hence, fsutil.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] ECPG documentation patch

[Boszormenyi Zoltan]

Hi,

here's the documentation patch for the new ECPG features.

- I changed the order of sections "Using Descriptor Areas" and
  "Informix compatibility mode"
- split the "Using Descriptor Areas", so it now have two subsections:
  "Named SQL Descriptor Areas" and "SQLDA Descriptor Areas".
  The second one talks about the native mode SQLDA only.
- Documented DESCRIBE and the USING/INTO quirks.
- Documented the "string" pseudo-type in compat mode
- Modified the section name "Additional embedded SQL statements",
  it now reads "Additional/missing embedded SQL statements" and
  documented the lack of "FREE cursor_name" statement and
  the behaviour of "FREE statement_name" statement.
- Documented the Informix-compatible SQLDA under the
  "Informix compatibility mode" section.

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/

*** pgsql.orig/doc/src/sgml/ecpg.sgml	2009-12-08 09:23:19.0 +0100
--- pgsql.doc/doc/src/sgml/ecpg.sgml	2010-01-15 19:07:01.0 +0100
***
*** 2376,2381 
--- 2376,2853 

   
  
+  
+   Using Descriptor Areas
+ 
+   
+An SQL descriptor area is a more sophisticated method for processing
+the result of a SELECT, FETCH or
+a DESCRIBE statement. An SQL descriptor area groups
+the data of one row of data together with metadata items into one
+data structure.  The metadata is particularly useful when executing
+dynamic SQL statements, where the nature of the result columns might
+not be known ahead of time. PostgreSQL provides two ways to use
+Descriptor Areas: the named SQL Descriptor Areas and the C-structure
+SQLDAs.
+   
+ 
+   
+Named SQL Descriptor Areas
+ 
+
+ A named SQL descriptor area consists of a header, which contains
+ information concerning the entire descriptor, and one or more item
+ descriptor areas, which basically each describe one column in the
+ result row.
+
+ 
+
+ Before you can use an SQL descriptor area, you need to allocate one:
+ 
+ EXEC SQL ALLOCATE DESCRIPTOR identifier;
+ 
+ The identifier serves as the variable name of the
+ descriptor area.  The scope of the allocated descriptor is WHAT?.
+ When you don't need the descriptor anymore, you should deallocate
+ it:
+ 
+ EXEC SQL DEALLOCATE DESCRIPTOR identifier;
+ 
+
+ 
+
+ To use a descriptor area, specify it as the storage target in an
+ INTO clause, instead of listing host variables:
+ 
+ EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc;
+ 
+ If the resultset is empty, the Descriptor Area will still contain
+ the metadata from the query, i.e. the field names.
+
+ 
+
+ For not yet executed prepared queries, the DESCRIBE
+ statement can be used to get the metadata of the resultset:
+  
+ EXEC SQL BEGIN DECLARE SECTION;
+ char *sql_stmt = "SELECT * FROM table1";
+ EXEC SQL END DECLARE SECTION;
+ 
+ EXEC SQL PREPARE stmt1 FROM :sql_stmt;
+ EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;
+ 
+
+ 
+
+ Before PostgreSQL 8.5, the SQL keyword was optional,
+ so using DESCRIPTOR and SQL DESCRIPTOR
+ produced named SQL Descriptor Areas. Now it is mandatory, omitting
+ the SQL keyword produces SQLDA Descriptor Areas,
+ see . 
+
+ 
+
+ In DESCRIBE and FETCH statements,
+ the INTO and USING keywords can be
+ used to similarly: they produce the resultset and the metadata in a
+ Descriptor Area.
+
+ 
+
+ Now how do you get the data out of the descriptor area?  You can
+ think of the descriptor area as a structure with named fields.  To
+ retrieve the value of a field from the header and store it into a
+ host variable, use the following command:
+ 
+ EXEC SQL GET DESCRIPTOR name :hostvar = field;
+ 
+ Currently, there is only one header field defined:
+ COUNT, which tells how many item
+ descriptor areas exist (that is, how many columns are contained in
+ the result).  The host variable needs to be of an integer type.  To
+ get a field from the item descriptor area, use the following
+ command:
+ 
+ EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field;
+ 
+ num can be a literal integer or a host
+ variable containing an integer. Possible fields are:
+ 
+ 
+  
+   CARDINALITY (integer)
+   
+
+ number of rows in the result set
+
+   
+  
+ 
+  
+   DATA
+   
+
+ actual data item (therefore, the data type of this field
+ depends on the query)
+
+   
+  
+ 
+  
+   DATETIME_INTERVAL_CODE

Re: [HACKERS] Streaming replication, retrying from archive

[Heikki Linnakangas]

Dimitri Fontaine wrote:
> But how we handle failures when transitioning from one state to the
> other should be a lot easier to discuss and decide as soon as we have
> the possible states and the transitions we want to allow and support. I
> think.
>
> My guess is that those states and transitions are in the code, but not
> explicit, so that each time we talk about how to handle the error cases
> we have to be extra verbose and we risk not talking about exactly the
> same thing. Naming the states should make those arrangements easier, I
> should think. Not sure if it would help follow the time constraint now
> though.

I agree, a state machine is a useful way of thinking about this. I
recall that mail of yours from last summer :-).

The states we have at the moment in standby are:

1. Archive recovery. Standby fetches WAL files from archive using
restore_command. When a file is not found in archive, we switch to state 2

2. Streaming replication. Standby connects (and reconnects if the
connection is lost for any reason) to the primary, starts streaming, and
applies WAL as it arrives. We stay in this state until trigger file is
found or server is shut down.

The states with my suggested ReadRecord/FetchRecord refactoring, the
code I have in the replication-xlogrefactor branch in my git repo, are:

1. Initial archive recovery. Standby fetches WAL files from archive
using restore_command. When a file is not found in archive, we start
walreceiver and switch to state 2

2. Retrying to restore from archive. When the connection to primary is
established and replication is started, we switch to state 3

3. Streaming replication. Connection to primary is established, and WAL
is applied as it arrives. When the connection is dropped, we go back to
state 2

Although the the state transitions between 2 and 3 are a bit fuzzy in
that version; walreceiver runs concurrently, trying to reconnect, while
startup process retries restoring from archive. Fujii-san's suggestion
to have walreceiver stop while startup process retries restoring from
archive (or have walreceiver run restore_command in approach #2) would
make that clearer.

-- 
  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] Hot Standby and handling max_standby_delay

[Simon Riggs]

We need to calculate a more accurate time since WAL arrived to make
max_standby_delay sensible in all cases. Difficult to know exactly when
to record new timestamps for received WAL. So, proposal is...

if (Base time is earlier than WAL record time)
standby_delay = WAL record time - Base time
else
standby_delay = now() - Base time

When standby_mode = off we record new base time when a new WAL file
arrives.

When standby_mode = on we record new base time each time we do
XLogWalRcvFlush(). We also record a new base time on first entry to the
main for loop in XLogRecv(), i.e. each time we start writing a new burst
of streamed WAL data.

So in either case, when we are waiting for new input we reset the timer
as soon as new WAL is received. The resolution/accuracy of standby_delay
will be no more than the time taken to replay a single file. This
shouldn't matter, since sane settings of max_standby_delay are either 0
or a number like 5-20 (seconds).

Which means if we are busy we don't record many new times, whereas if we
are working in sporadic bursts we keep up with the latest time of
receipt. This also works when we are performing an archive_recovery for
an old backup.

Startup process will access base time each time it begins to wait and
calculate current standby_delay before comparing against
max_standby_delay.

Comments?

-- 
 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] Testing with concurrent sessions

[Markus Wanner]

Hi,

Kevin Grittner wrote:

Not sure what's relevant there.  Entire file tarball attached.


Due to reasons mentioned in this thread as well, I've decided to use 
psql to connect to the database. dtester is parsing its output and 
checks that against expectations. Hawever, that has its own pitfalls, so 
in the end I'm almost about to change back to using libpq or 
implementing the bare minimum protocol (that might have its own merits 
within the twisted world, if implemented in the required async fashion).


Strangely, your log has escape codes in it, which I'm assuming makes the 
 parsing choke. Is that something special to your installation? My psql 
never colors its outputs...


However, the quickest way forward probably is to filter out escape 
sequences. Turning off tty is not really an option, because dtester 
doesn't have a chance to capture all necessary events, in that mode.



Yes I can.  Any queries you'd like me to run in there?


It looks like psql can connect, too. It's just the parsing of outputs 
which fails.


Regards

Markus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Heikki Linnakangas]

Leonardo F wrote:
>> Yeah, I think you could do that, I agree it feels better that way.
>> You'll still need new copytup and comparetup functions, though, to deal
>> with HeapTupleHeaders instead of MinimalTuples, or modify the existing
>> ones to handle both. 
> 
> You meant HeapTuple, not HeapTupleHeaders, right?

No, I did mean HeapTupleHeader. MinimalTuple struct is cut-down version
HeapTupleHeader, while HeapTuple is structure that holds a pointer to
HeapTupleHeader + some extra information. SortTuple takes the role of
HeapTUple in tuplesort.c. A bit confusing, yes.

That said, I didn't really look closely, maybe I'm missing something and
HeapTuple is in fact the right struct to pass around.

>> And some way to indicate that you want to preserve
>> the visibility information when you create the tuplesort, maybe a new
>> parameter to tuplesort_begin_heap().
> 
> I guess that using Gregory Stark's patch there's no need for it, since it uses
> HeapTuples, right?

Hmm, you still need to set different comparison function in
Tuplesortstate->comparetup, so you'll still need a separate begin()
function too, or a flag to the existing one.

> A patch that:
> 
> 1) uses always the old CLUSTER method for non-btree indexes and for
> expression indexes
> 2) add a whole set of new functions to tuplesort (as in Gregory Stark's patch)
> 
> would be rejected "for sure"? Or can be thought as a "better than nothing,
> works in 90% cases" patch?

I'm fine with 1), though I wish we didn't have to add all that
boilerplate code 2). I guess it's not a show-stopper.

-- 
  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] Testing with concurrent sessions

[Markus Wanner]

Hi,

Kevin Grittner wrote:

You are trying to save a python file as non ASCII, without
specifiying a correct source encoding line for encoding "utf-8"


I wasn't aware I had non-ascii characters in there. Inserting an 
encoding line seems fine. I'll fix that for the upcoming version 0.1.


Regards

Markus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Testing with concurrent sessions

[Kevin Grittner]

Markus Wanner  wrote:
 
> I wasn't aware I had non-ascii characters in there. Inserting an 
> encoding line seems fine. I'll fix that for the upcoming version
> 0.1.
 
Yeah, I couldn't find any, either.  I just tried creating a minimal
python file in Kate, and it gave me that even though I *know* it was
all ASCII characters right off my keyboard.  I guess Kate is being
overly picky.  On the other hand, if it silences an annoying message
sometimes, maybe that's reason enough to add it.
 
-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] Testing with concurrent sessions

[Kevin Grittner]

Markus Wanner  wrote:
 
> Strangely, your log has escape codes in it, which I'm assuming
> makes the parsing choke. Is that something special to your
> installation? My psql never colors its outputs...
 
I haven't configured anything like that intentionally.  I don't
*see* any colors when I use psql.  Can you think of anywhere I
should check something which might be causing this?
 
-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] Streaming replication, loose ends

[Simon Riggs]

On Fri, 2010-01-15 at 19:29 +0200, Heikki Linnakangas wrote:

> File-based Log Shipping
>   Planning
>   Implementation
> Streaming Replication
>   Setting up

How about "Log Streaming Replication"?

So its a particular kind of replication, which seems correct to me.

-- 
 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] Streaming replication status

[Greg Smith]

Stefan Kaltenbrunner wrote:


Another popular question is "how far behind real-time is the archiver 
process?"  You can do this right now by duplicating the same xlog 
file name scanning and sorting that the archiver does in your own 
code, looking for .ready files.  It would be simpler if you could 
call pg_last_archived_xlogfile() and then just grab that file's 
timestamp.


well that one seems a more reasonable reasoning to me however I'm not 
so sure that the proposed implementation feels right - though can't 
come up with a better suggestion for now.


That's basically where I'm at, and I was looking more for feedback on 
that topic rather than to get lost defending use-cases here.  There are 
a few of them, and you can debate their individual merits all day.  As a 
general comment to your line of criticism here, I feel the idea that 
"we're monitoring that already via " does not mean that an additional 
check is without value.  The kind of people who like redundancy in their 
database like it in their monitoring, too.  I feel there's at least one 
unique thing exposing this bit buys you, and the fact that it can be a 
useful secondary source of information too for systems monitoring is 
welcome bonus--regardless of whether good practice already supplies a 
primary one.


If you continue your line of thought you will have to add all kind of 
stuff to the database, like CPU usage tracking, getting information 
about running processes, storage health.


I'm looking to expose something that only the database knows for 
sure--"what is the archiver working on?"--via the standard way you ask 
the database questions, a SELECT call.  The database doesn't know 
anything about the CPU, running processes, or storage, so suggesting 
this path leads in that direction doesn't make any sense.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Hot Standby and handling max_standby_delay

[Heikki Linnakangas]

Simon Riggs wrote:
> We need to calculate a more accurate time since WAL arrived to make
> max_standby_delay sensible in all cases. Difficult to know exactly when
> to record new timestamps for received WAL. So, proposal is...
> 
> if (Base time is earlier than WAL record time)
>   standby_delay = WAL record time - Base time
> else
>   standby_delay = now() - Base time
> 
> When standby_mode = off we record new base time when a new WAL file
> arrives.
> 
> When standby_mode = on we record new base time each time we do
> XLogWalRcvFlush(). We also record a new base time on first entry to the
> main for loop in XLogRecv(), i.e. each time we start writing a new burst
> of streamed WAL data.
> 
> So in either case, when we are waiting for new input we reset the timer
> as soon as new WAL is received. The resolution/accuracy of standby_delay
> will be no more than the time taken to replay a single file. This
> shouldn't matter, since sane settings of max_standby_delay are either 0
> or a number like 5-20 (seconds).

That would change the meaning of max_standby_delay. Currently, it's the
delay between *generating* and applying a WAL record, your proposal
would change it to mean delay between receiving and applying it. That
seems a lot less useful to me.

With the current definition, I would feel pretty comfortable setting it
to say 15 minutes, knowing that if the standby falls behind for any
reason, as soon as the connection is re-established or
archiving/restoring fixed, it will catch up quickly, blowing away any
read-only queries if required. With your new definition, the standby
would in the worst case pause for 15 minutes at every WAL file.

-- 
  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] Testing with concurrent sessions

[Markus Wanner]

Hi,

Kevin Grittner wrote:

I haven't configured anything like that intentionally.  I don't
*see* any colors when I use psql.  Can you think of anywhere I
should check something which might be causing this?


No idea ATM.

However, just to make sure that has absolutely nothing to do with the 
curses reporter I've written: is that dtester.log you just sent the log 
from a run with the StreamReporter or the CursesReporter? (Should not 
have any influence for the log, but you never know).


Please recheck with the StreamReporter and try to grep the lines 
starting with "[psql0]", "[psql1]" and "[psql2]". Dtester simply logs 
all and any output of all 3rd party processes started.


Alternatively, you may want to filter out all lines that start with 
"[postmaster0]", that might already reduce what we can consider noise in 
this case.


Regards

Markus Wanner

--
Sent 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, retrying from archive

[Simon Riggs]

On Fri, 2010-01-15 at 20:11 +0200, Heikki Linnakangas wrote:

> The states we have at the moment in standby are:
> 
> 1. Archive recovery. Standby fetches WAL files from archive using
> restore_command. When a file is not found in archive, we switch to state 2
> 
> 2. Streaming replication. Standby connects (and reconnects if the
> connection is lost for any reason) to the primary, starts streaming, and
> applies WAL as it arrives. We stay in this state until trigger file is
> found or server is shut down.

> The states with my suggested ReadRecord/FetchRecord refactoring, the
> code I have in the replication-xlogrefactor branch in my git repo, are:
> 
> 1. Initial archive recovery. Standby fetches WAL files from archive
> using restore_command. When a file is not found in archive, we start
> walreceiver and switch to state 2
> 
> 2. Retrying to restore from archive. When the connection to primary is
> established and replication is started, we switch to state 3
> 
> 3. Streaming replication. Connection to primary is established, and WAL
> is applied as it arrives. When the connection is dropped, we go back to
> state 2
> 
> Although the the state transitions between 2 and 3 are a bit fuzzy in
> that version; walreceiver runs concurrently, trying to reconnect, while
> startup process retries restoring from archive. Fujii-san's suggestion
> to have walreceiver stop while startup process retries restoring from
> archive (or have walreceiver run restore_command in approach #2) would
> make that clearer.

The one-way state transitions between 1->2 in both cases seem to make
this a little more complex, rather than more simple. 

If the connection did drop then WAL will be in the archive, so the path
for data is archive->primary->standby. There already needs to be a
network path between archive and standby, so why not drop back from
state 3 -> 1 rather than from 3 -> 2? That way we could have just 2
states on each side, rather than 3.

-- 
 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] Testing with concurrent sessions

[Kevin Grittner]

Markus Wanner  wrote:
 
> Strangely, your log has escape codes in it, which I'm assuming
> makes the parsing choke. Is that something special to your
> installation?
 
My pager is "less"; could that cause it?  Could the twisted
environment look like one where the pager should kick in?
 
-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] Testing with concurrent sessions

[Markus Wanner]

Hi,

Kevin Grittner wrote:

My pager is "less"; could that cause it?  Could the twisted
environment look like one where the pager should kick in?


Yes, that could be it. At least it fails here, too, if I set PAGER=less. 
Try:


  PAGER=more make dcheck

So, the solution probably lies in adjusting the environment, before 
starting psql. (Maybe even dropping all existing environment variables 
for better control of the situation). Will add that for dtester 0.1.


(Also note that I plan to move most of what's currently in the patch to 
the dtester package itself. However, that requires it to be (even more) 
generic.)


Thank you for testing the tester ;-)

Regards

Markus

--
Sent 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 data in information_schema grant_* tables?

[Peter Eisentraut]

On fre, 2010-01-15 at 15:06 +0100, Fabien COELHO wrote:
> > The whole point of role_table_grants is that it shows everything that
> > table_privileges shows except privileges granted to public.  So the
> > behavior you observe is correct.
> 
> This is not my understanding of ISO/IEC 9075-11:2003(E), page 57 :

You're right, it's a bug, but it's already fixed in 8.5. :-)



-- 
Sent 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 and non-blocking I/O

[Heikki Linnakangas]

Fujii Masao wrote:
> On Wed, Jan 13, 2010 at 3:37 AM, Magnus Hagander  wrote:
>>> This change which moves walreceiver process into a dynamically loaded
>>> module caused the following compile error on my MinGW environment.
>> That sounds strange - it should pick those up from the -lpostgres. Any
>> chance you have an old postgres binary around from a non-syncrep build
>> or something?
> 
> No, there is no old postgres binary.
> 
>> Do you have an environment to try to build it under msvc?
> 
> No, unfortunately.
> 
>> in my
>> experience, that gives you easier-to-understand error messages in a
>> lot of cases like this - it removets the mingw black magic.
> 
> OK. I'll try to build it under msvc.
> 
> But since there seems to be a long way to go before doing that,
> I would appreciate if someone could give me some advice.

It looks like dawn_bat is experiencing the same problem. I don't think
we want to sprinkle all those variables with PGDLLIMPORT, and it didn't
fix the problem for you earlier anyway. Is there some other way to fix this?

Do people still use MinGW for any real work? Could we just drop
walreceiver support from MinGW builds?

Or maybe we should consider splitting walreceiver into two parts after
all. Only the bare minimum that needs to access libpq would go into the
shared object, and the rest would be linked with the backend as usual.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and non-blocking I/O

[Magnus Hagander]

2010/1/15 Heikki Linnakangas :
> Fujii Masao wrote:
>> On Wed, Jan 13, 2010 at 3:37 AM, Magnus Hagander  wrote:
 This change which moves walreceiver process into a dynamically loaded
 module caused the following compile error on my MinGW environment.
>>> That sounds strange - it should pick those up from the -lpostgres. Any
>>> chance you have an old postgres binary around from a non-syncrep build
>>> or something?
>>
>> No, there is no old postgres binary.
>>
>>> Do you have an environment to try to build it under msvc?
>>
>> No, unfortunately.
>>
>>> in my
>>> experience, that gives you easier-to-understand error messages in a
>>> lot of cases like this - it removets the mingw black magic.
>>
>> OK. I'll try to build it under msvc.
>>
>> But since there seems to be a long way to go before doing that,
>> I would appreciate if someone could give me some advice.
>
> It looks like dawn_bat is experiencing the same problem. I don't think
> we want to sprinkle all those variables with PGDLLIMPORT, and it didn't
> fix the problem for you earlier anyway. Is there some other way to fix this?
>
> Do people still use MinGW for any real work? Could we just drop
> walreceiver support from MinGW builds?

We don't know if this works on MSVC, because MSVC doesn't actually try
to build the walreceiver. I'm going to look at that tomorrow.

If we get the same issues there, we a problem in our code. If not, we
need to figure out what's up with mingw.


> Or maybe we should consider splitting walreceiver into two parts after
> all. Only the bare minimum that needs to access libpq would go into the
> shared object, and the rest would be linked with the backend as usual.

That would certainly be one option.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Testing with concurrent sessions

[Andrew Dunstan]

Markus Wanner wrote:

Hi,

Kevin Grittner wrote:

My pager is "less"; could that cause it?  Could the twisted
environment look like one where the pager should kick in?


Yes, that could be it. At least it fails here, too, if I set 
PAGER=less. Try:


  PAGER=more make dcheck



Surely for automated use you want the psql pager off altogether. "psql 
--pset pager=off" or some such invocation should do it.


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] mailing list archiver chewing patches

[Matteo Beccati]

Hi everyone,

Il 14/01/2010 19:36, David Fetter ha scritto:

On Thu, Jan 14, 2010 at 03:08:22PM +0100, Matteo Beccati wrote:

Il 14/01/2010 14:39, Dimitri Fontaine ha scritto:

Matteo Beccati   writes:>

Any improvements to sorting are welcome :)


...
 ARRAY[uid]
...


Thanks David, using an array rather than text concatenation is slightly 
slower and uses a bit more memory, but you've been able to convince me 
that it's The Right Way(TM) ;)


Anyway, I've made further changes and I would say that at this point the 
PoC is feature complete. There surely are still some rough edges and a 
few things to clean up, but I'd like to get your feedback once again:


http://archives.beccati.org

You will find that pgsql-general and -hackers are subscribed and getting 
messages live, wihle -hackers-history and -www have been imported from 
the archives (about 200k and 1.5k messages respectively at 50 messages/s).


Also, I'd need some help with the CTE query that was picking a wrong 
plan and led me to forcibly disable merge joins inside the application 
when executing it. Plans are attached.



Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/
archiveopteryx=# EXPLAIN ANALYZE WITH RECURSIVE t (mailbox, uid, date, subject, 
sender, has_attachments, parent_uid, idx, depth) AS (
  SELECT mailbox, uid, date, subject, sender, has_attachments, parent_uid, 
uid::text, 1
  FROM arc_messages
  WHERE parent_uid IS NULL AND mailbox = 17 AND date >= '2007-11-01' AND date < 
'2007-12-01'
  UNION ALL
  SELECT a.mailbox, a.uid, a.date, a.subject, a.sender, a.has_attachments, 
a.parent_uid, t.idx || '.' || a.uid::text, t.depth + 1
  FROM t JOIN arc_messages a USING (mailbox)
  WHERE t.uid = a.parent_uid
) SELECT * FROM t ORDER BY idx ;

   QUERY PLAN

 Sort  (cost=92761.67..92769.91 rows=1647 width=121) (actual 
time=4183.736..4185.762 rows=1428 loops=1)
   Sort Key: t.idx
   Sort Method:  quicksort  Memory: 366kB
   CTE t
 ->  Recursive Union  (cost=0.00..92579.09 rows=1647 width=130) (actual 
time=0.030..4173.724 rows=1428 loops=1)
   ->  Index Scan using arc_messages_mailbox_parent_id_date_key on 
arc_messages  (cost=0.00..486.42 rows=567 width=94) (actual time=0.025..1.432 
rows=482 loops=1)
 Index Cond: ((mailbox = 17) AND (parent_uid IS NULL) AND (date 
>= '2007-11-01 00:00:00+01'::timestamp with time zone) AND (date < '2007-12-01 
00:00:00+01'::timestamp with time zone))
   ->  Merge Join  (cost=729.68..9208.61 rows=108 width=130) (actual 
time=262.120..277.819 rows=63 loops=15)
 Merge Cond: ((a.mailbox = t.mailbox) AND (a.parent_uid = 
t.uid))
 ->  Index Scan using arc_messages_mailbox_parent_id_key on 
arc_messages a  (cost=0.00..6452.25 rows=193871 width=94) (actual 
time=0.018..147.782 rows=85101 loops=15)
 ->  Sort  (cost=729.68..758.03 rows=5670 width=44) (actual 
time=0.403..0.559 rows=109 loops=15)
   Sort Key: t.mailbox, t.uid
   Sort Method:  quicksort  Memory: 25kB
   ->  WorkTable Scan on t  (cost=0.00..22.68 rows=5670 
width=44) (actual time=0.003..0.145 rows=95 loops=15)
   ->  CTE Scan on t  (cost=0.00..6.59 rows=1647 width=121) (actual 
time=0.035..4179.686 rows=1428 loops=1)
 Total runtime: 4188.187 ms
(16 rows)
archiveopteryx=# SET enable_mergejoin = false;
SET
archiveopteryx=# EXPLAIN ANALYZE WITH RECURSIVE t (mailbox, uid, date, subject, 
sender, has_attachments, parent_uid, idx, depth) AS (
  SELECT mailbox, uid, date, subject, sender, has_attachments, parent_uid, 
uid::text, 1
  FROM arc_messages
  WHERE parent_uid IS NULL AND mailbox = 17 AND date >= '2007-11-01' AND date < 
'2007-12-01'
  UNION ALL
  SELECT a.mailbox, a.uid, a.date, a.subject, a.sender, a.has_attachments, 
a.parent_uid, t.idx || '.' || a.uid::text, t.depth + 1
  FROM t JOIN arc_messages a USING (mailbox)
  WHERE t.uid = a.parent_uid
) SELECT * FROM t ORDER BY idx ;

   QUERY PLAN

 Sort  (cost=104762.75..104770.98 rows=1647 width=121) (actual 
time=34.315..36.331 rows=1428 loops=1)
   Sort Key: t.idx
   Sort Method:  quicksort  Memory: 366kB
   CTE t
 ->  Recursive Union  (cost=0.00..104580.17 rows=1647 width=130) (actual 
time=0.040..24.851 rows=1428 loops=1)
   ->  Index Scan using arc_messages_mailbox_parent_id_date_key on 
arc_messages  (cost=0.00..486.42 rows=567 widt

Re: [HACKERS] Streaming replication and non-blocking I/O

[Andrew Dunstan]

Heikki Linnakangas wrote:

Do people still use MinGW for any real work? Could we just drop
walreceiver support from MinGW builds?

Or maybe we should consider splitting walreceiver into two parts after
all. Only the bare minimum that needs to access libpq would go into the
shared object, and the rest would be linked with the backend as usual.

  


I use MinGW when doing Windows work (e.g. the threading piece in 
parallel pg_restore).  And I think it is generally desirable to be able 
to build on Windows using an open source tool chain. I'd want a damn 
good reason to abandon its use. And I don't like the idea of not 
supporting walreceiver on it either. Please find another solution if 
possible.


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] Streaming replication and non-blocking I/O

[Magnus Hagander]

2010/1/15 Andrew Dunstan :
>
>
> Heikki Linnakangas wrote:
>>
>> Do people still use MinGW for any real work? Could we just drop
>> walreceiver support from MinGW builds?
>>
>> Or maybe we should consider splitting walreceiver into two parts after
>> all. Only the bare minimum that needs to access libpq would go into the
>> shared object, and the rest would be linked with the backend as usual.
>>
>>
>
> I use MinGW when doing Windows work (e.g. the threading piece in parallel 
> pg_restore).  And I think it is generally desirable to be able to build on 
> Windows using an open source tool chain. I'd want a damn good reason to 
> abandon its use. And I don't like the idea of not supporting walreceiver on 
> it either. Please find another solution if possible.
>

Yeah. FWIW, I don't use mingw do do any windows development, but
definitely +1 on working hard to keep support for it if at all
possible.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and non-blocking I/O

[Heikki Linnakangas]

Magnus Hagander wrote:
> 2010/1/15 Andrew Dunstan :
>>
>> Heikki Linnakangas wrote:
>>> Do people still use MinGW for any real work? Could we just drop
>>> walreceiver support from MinGW builds?
>>>
>>> Or maybe we should consider splitting walreceiver into two parts after
>>> all. Only the bare minimum that needs to access libpq would go into the
>>> shared object, and the rest would be linked with the backend as usual.
>>>
>> I use MinGW when doing Windows work (e.g. the threading piece in parallel 
>> pg_restore).  And I think it is generally desirable to be able to build on 
>> Windows using an open source tool chain. I'd want a damn good reason to 
>> abandon its use. And I don't like the idea of not supporting walreceiver on 
>> it either. Please find another solution if possible.
> 
> Yeah. FWIW, I don't use mingw do do any windows development, but
> definitely +1 on working hard to keep support for it if at all
> possible.

Ok. I'll look at splitting walreceiver code between the shared module
and backend binary slightly differently. At first glance, it doesn't
seem that hard after all, and will make the code more modular anyway.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and non-blocking I/O

[Tom Lane]

Heikki Linnakangas  writes:
> Magnus Hagander wrote:
>> Yeah. FWIW, I don't use mingw do do any windows development, but
>> definitely +1 on working hard to keep support for it if at all
>> possible.

> Ok. I'll look at splitting walreceiver code between the shared module
> and backend binary slightly differently. At first glance, it doesn't
> seem that hard after all, and will make the code more modular anyway.

This is probably going in the wrong direction.  There is no good reason
why that module should be failing to link, and I don't think it's going
to be "more modular" if you're forced to avoid any global variable
references at all in some arbitrary portion of the code.

I think it's a tools/build process problem and should be attacked that
way.

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] plpython3

[James William Pye]

On Jan 14, 2010, at 2:03 PM, Joshua D. Drake wrote:
> What I would (as a non hacker) would look for is:
> 
> (1) Generalized benchmarks between plpython(core) and plpython3u
> 
> I know a lot of these are subjective, but it is still good to see if
> there are any curves or points that bring the performance of either to
> light.

I guess I could do some simple function I/O tests to identify invocation 
overhead(take a single parameter and return it). This should give a somewhat 
reasonable view of the trade-offs of "native typing" vs conversion 
performance-wise. One thing to keep in mind is that *three* tests would need to 
be done per parameter set:

 1. plpython's
 2. plpython3's (raw data objects/"native typing")
 3. plpython3's + @pytypes

The third should show degraded performance in comparison to plpythonu's whereas 
the second should show improvement or near equivalence.

@pytypes is actually implemented in pure-Python, so the impact should be quite 
visible.

http://python.projects.postgresql.org/pldocs/plpython3-postgres-pytypes.html


I'm not sure there's anything else worth measuring. SRFs, maybe?


> (2) Example of the traceback facility, I know it is silly but I don't
> have time to actually download head, apply the patch and test this.

Well, if you ever do find some time, the *easiest* way would probably be to 
download a branch snapshot from git.pg.org:

http://git.postgresql.org/gitweb?p=plpython3.git;a=snapshot;h=refs/heads/plpython3;sf=tgz

It requires Python 3.1. 3.0 has been abandoned by python.org.

> This
> type of thing, showing debugging facilities within the function would be
> killer.

The test output has a *lot* of tracebacks, so I'll just copy and paste one here.

This one shows the traceback output of a chained exception.

-- suffocates a pg error, and attempts to enter a protected area
CREATE OR REPLACE FUNCTION pg_failure_suf_IFTE() RETURNS VOID LANGUAGE 
plpython3u AS
$python$
import Postgres

rp = Postgres.Type(Postgres.CONST['REGPROCEDUREOID'])

def main():
try:
fun = rp('nosuchfunc(int17,zzz)')
except:
# Should be valid, but the protection of
# PL_DB_IN_ERROR should keep it from getting called.
rp('pg_x_failure_suf()')
$python$;


SELECT pg_failure_suf_IFTE();
ERROR:  database action attempted while in failed transaction
CONTEXT:  [exception from Python]
Traceback (most recent call last):
   File "public.pg_failure_suf_ifte()", line 8, in main
fun = rp('nosuchfunc(int17,zzz)')
 Postgres.Exception: type "int17" does not exist
CODE: 42704

During handling of the above exception, another exception occurred:

 Traceback (most recent call last):
   File "public.pg_failure_suf_ifte()", line 12, in main
rp('pg_x_failure_suf()')
 Postgres.Exception

[public.pg_failure_suf_ifte()]


> (3) A distinct real world comparison where the core plpython falls down
> (if it does) against the plpython3u implementation

Hrm. Are you looking for something that plpython3 can do that plpython can't? 
Or are you looking for something where plpython makes the user work a lot 
harder?
-- 
Sent 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 and non-blocking I/O

[Aidan Van Dyk]

* Heikki Linnakangas  [100115 15:20]:

> Ok. I'll look at splitting walreceiver code between the shared module
> and backend binary slightly differently. At first glance, it doesn't
> seem that hard after all, and will make the code more modular anyway.

Maybe an insane question, but why can postmaster just not "exec"
walreceiver?  I mean, because of windows, we already have that code
around, and then walreceiver could link directly to libpq and not have
to worry at all about linking all of postmaster backends to libpq...

But I do understand that's a radical change...

a.
-- 
Aidan Van Dyk Create like a god,
ai...@highrise.ca   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] Testing with concurrent sessions

[Kevin Grittner]

Markus Wanner  wrote:
 
> So, the solution probably lies in adjusting the environment,
> before starting psql. (Maybe even dropping all existing
> environment variables for better control of the situation). Will
> add that for dtester 0.1.
 
Based on Andrew's suggestion, I changed line 276 to:
 
args=['psql', '-A', '--pset=pager=off',
 
I now get 5 of 6 tests succeeded (83.3%), processed in 18.5 seconds.
 
I'm not clear on what you want to see from the run or whether it
might be better sent off-list.
 
Also, in looking closer at how you have the tests defined, it
doesn't look to me like you're carefully interleaving specific
sequences of statements on specific connections so much as opening
multiple connections and then for each statement saying "run this on
all connections."  That's certainly a valid test to include, but I
need the more controlled format, too.  It does appear that that's
pretty straightforward to code; you just haven't chosen to do so in
the particular tests here, correct?
 
-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] plpython3

[Joshua D. Drake]

On Fri, 2010-01-15 at 13:26 -0700, James William Pye wrote:
> On Jan 14, 2010, at 2:03 PM, Joshua D. Drake wrote:
> > What I would (as a non hacker) would look for is:
> > 
> > (1) Generalized benchmarks between plpython(core) and plpython3u
> > 
> > I know a lot of these are subjective, but it is still good to see if
> > there are any curves or points that bring the performance of either to
> > light.
> 
> I guess I could do some simple function I/O tests to identify invocation 
> overhead(take a single parameter and return it). This should give a somewhat 
> reasonable view of the trade-offs of "native typing" vs conversion 
> performance-wise. One thing to keep in mind is that *three* tests would need 
> to be done per parameter set:
> 
>  1. plpython's
>  2. plpython3's (raw data objects/"native typing")
>  3. plpython3's + @pytypes
> 
> The third should show degraded performance in comparison to plpythonu's 
> whereas the second should show improvement or near equivalence.
> 
> @pytypes is actually implemented in pure-Python, so the impact should be 
> quite visible.
> 
> http://python.projects.postgresql.org/pldocs/plpython3-postgres-pytypes.html
> 
> 
> I'm not sure there's anything else worth measuring. SRFs, maybe?
> 
> 
> > (2) Example of the traceback facility, I know it is silly but I don't
> > have time to actually download head, apply the patch and test this.
> 
> Well, if you ever do find some time, the *easiest* way would probably be to 
> download a branch snapshot from git.pg.org:
> 
> http://git.postgresql.org/gitweb?p=plpython3.git;a=snapshot;h=refs/heads/plpython3;sf=tgz
> 
> It requires Python 3.1. 3.0 has been abandoned by python.org.
> 
> > This
> > type of thing, showing debugging facilities within the function would be
> > killer.
> 
> The test output has a *lot* of tracebacks, so I'll just copy and paste one 
> here.
> 
> This one shows the traceback output of a chained exception.
> 
> -- suffocates a pg error, and attempts to enter a protected area
> CREATE OR REPLACE FUNCTION pg_failure_suf_IFTE() RETURNS VOID LANGUAGE 
> plpython3u AS
> $python$
> import Postgres
> 
> rp = Postgres.Type(Postgres.CONST['REGPROCEDUREOID'])
> 
> def main():
> try:
> fun = rp('nosuchfunc(int17,zzz)')
> except:
> # Should be valid, but the protection of
> # PL_DB_IN_ERROR should keep it from getting called.
> rp('pg_x_failure_suf()')
> $python$;
> 
> 
> SELECT pg_failure_suf_IFTE();
> ERROR:  database action attempted while in failed transaction
> CONTEXT:  [exception from Python]
> Traceback (most recent call last):
>File "public.pg_failure_suf_ifte()", line 8, in main
> fun = rp('nosuchfunc(int17,zzz)')
>  Postgres.Exception: type "int17" does not exist
> CODE: 42704
> 
> During handling of the above exception, another exception occurred:
> 
>  Traceback (most recent call last):
>File "public.pg_failure_suf_ifte()", line 12, in main
> rp('pg_x_failure_suf()')
>  Postgres.Exception
> 
> [public.pg_failure_suf_ifte()]
> 
> 
> > (3) A distinct real world comparison where the core plpython falls down
> > (if it does) against the plpython3u implementation
> 
> Hrm. Are you looking for something that plpython3 can do that plpython can't? 
> Or are you looking for something where plpython makes the user work a lot 
> harder?

I think both apply.

This is great stuff, thank you for taking the effort.

Joshua D. Drake



-- 
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] Streaming replication and non-blocking I/O

[Tom Lane]

I wrote:
> I think it's a tools/build process problem and should be attacked that
> way.

Specifically, I think you missed out $(BE_DLLLIBS) in SHLIB_LINK.
We'll find out at the next mingw build...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and non-blocking I/O

[Tom Lane]

Aidan Van Dyk  writes:
> Maybe an insane question, but why can postmaster just not "exec"
> walreceiver?

It'd greatly complicate access to shared memory.

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] Testing with concurrent sessions

[Kevin Grittner]

"Kevin Grittner"  wrote:
 
> Also, in looking closer at how you have the tests defined, it
> doesn't look to me like you're carefully interleaving specific
> sequences of statements on specific connections so much as opening
> multiple connections and then for each statement saying "run this
> on all connections."
 
I take it back; you've got both.
 
I do want to expand the tests quite a bit -- do I work them all into
this same file, or how would I proceed?  I think I'll need about 20
more tests, but I don't want to get in the way of your work on the
framework which runs them.
 
-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] Streaming replication and non-blocking I/O

[Heikki Linnakangas]

Tom Lane wrote:
> I wrote:
>> I think it's a tools/build process problem and should be attacked that
>> way.
> 
> Specifically, I think you missed out $(BE_DLLLIBS) in SHLIB_LINK.
> We'll find out at the next mingw build...

Thanks. But what is BE_DLLLIBS? I can't find any description of it.

I suspect the MinGW build will fail because of the missing PGDLLIMPORTs.
Before we sprinkle all the global variables it touches with that, let me
explain what I meant by dividing walreceiver code differently between
dynamically loaded module and backend code. Right now I have to go to
sleep, though, but I'll try to get back to during the weekend.

-- 
  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] Testing with concurrent sessions

[Kevin Grittner]

Markus Wanner  wrote:
 
> Please recheck with the StreamReporter and try to grep the lines 
> starting with "[psql0]", "[psql1]" and "[psql2]". Dtester simply
> logs all and any output of all 3rd party processes started.
 
For me, all psql output seems to be [psql0]; no [psql1] or [psql2].
 
Bug?
 
-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] Streaming replication and non-blocking I/O

[Tom Lane]

Heikki Linnakangas  writes:
> Tom Lane wrote:
>> Specifically, I think you missed out $(BE_DLLLIBS) in SHLIB_LINK.
>> We'll find out at the next mingw build...

> Thanks. But what is BE_DLLLIBS? I can't find any description of it.

It was the wrong theory anyway --- it already is included (in
Makefile.shlib).  But what it does is provide -lpostgres on platforms
where that is needed, such as mingw.

> I suspect the MinGW build will fail because of the missing PGDLLIMPORTs.

Yeah.  On closer investigation the problem seems to be -DBUILDING_DLL,
which flips the meaning of PGDLLIMPORT.  contrib/dblink, which surely
works and has the same linkage requirements as walreceiver, does *not*
use that.  I've committed a patch to change that, we'll soon see if it
works...

> Before we sprinkle all the global variables it touches with that, let me
> explain what I meant by dividing walreceiver code differently between
> dynamically loaded module and backend code. Right now I have to go to
> sleep, though, but I'll try to get back to during the weekend.

Yeah, nothing to be done till we get another buildfarm cycle anyway.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Testing with concurrent sessions

[Kevin Grittner]

Markus Wanner  wrote:
 
> Go try it, read the code and simply ask, if you get stuck. I'll
> try to come up with some more documentation and such...
 
I'm a little unclear about the differences between "uses",
"depends", and "onlyAfter".  Here's what they *sound* like they
mean, to me; although I don't think the code isn't entirely
consistent with this interpretation.
 
"uses" means that the referenced task has complimentary setUp and
tearDown methods, and the dependent task may only run after a
successful invocation of the referenced task's setUp method, and the
referenced task will wait for completion of all dependent tasks
before invoking tearDown.
 
"depends" means that the tearDown method of the referenced task
doesn't undo the work of its setUp, at least for purposes of the
dependent task.  The dependent task can only start after successful
completion of the referenced class's work (*just* setUp, or all the
way to tearDown?), but the referenced task doesn't need to wait for
the dependent task.
 
"onlyAfter" means that the dependent task must wait for completion
of the referenced task, but doesn't care whether or not the
referenced class completed successfully.
 
How close am I?
 
-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] bug in integration SQL parser to plpgsq

[Tom Lane]

Pavel Stehule  writes:
> it doesn't support EXPLAIN as possible begin of SQL statement:

I've applied a fix for that.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Greg Stark]

On Fri, Jan 15, 2010 at 6:39 PM, Simon Riggs  wrote:
> On Fri, 2010-01-15 at 19:29 +0200, Heikki Linnakangas wrote:
> How about "Log Streaming Replication"?
>
> So its a particular kind of replication, which seems correct to me.

I thought the whole point of this effort was to be able to bill it as
a built-in easy replication which was perceived as a major hole in
Postgres's feature set.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Hot Standby and handling max_standby_delay

[Simon Riggs]

On Fri, 2010-01-15 at 20:50 +0200, Heikki Linnakangas wrote:

> > So in either case, when we are waiting for new input we reset the timer
> > as soon as new WAL is received. The resolution/accuracy of standby_delay
> > will be no more than the time taken to replay a single file. This
> > shouldn't matter, since sane settings of max_standby_delay are either 0
> > or a number like 5-20 (seconds).
> 
> That would change the meaning of max_standby_delay. Currently, it's the
> delay between *generating* and applying a WAL record, your proposal
> would change it to mean delay between receiving and applying it. That
> seems a lot less useful to me.

Remember that this proposal is about responding to your comments. You
showed that the time difference between generating and applying a WAL
record lacked useful meaning in cases where the generation was not
smooth and continuous. So, taking your earlier refutation as still
observing a problem, I definitely do redefine the meaning of
max_standby_delay. As you say "standby delay" means the difference
between receive and apply.

The bottom line here is: are you willing to dismiss your earlier
observation of difficulties? I don't think you can...

> With the current definition, I would feel pretty comfortable setting it
> to say 15 minutes, knowing that if the standby falls behind for any
> reason, as soon as the connection is re-established or
> archiving/restoring fixed, it will catch up quickly, blowing away any
> read-only queries if required. With your new definition, the standby
> would in the worst case pause for 15 minutes at every WAL file.

Yes, it does. And I know you're thinking along those lines because we
are concurrently discussing how to handle re-connection after updates.

The alternative is this: after being disconnected for 15 minutes we
reconnect. For the next X minutes the standby will be almost unusable
for queries while we catch up again.

---

So, I'm left with thinking that both of these ways are right, in
different circumstances and with different priorities.

If your priority is High Availability, then you are willing to give up
the capability for long-ish queries when that conflicts with the role of
HA server. (delay = apply - generate). If your priority is a Reporting
Server, then you are willing to give up HA capability in return for
relatively uninterrupted querying (delay = apply - receive).

Do we agree the two goals are mutually exclusive? If so, I think we need
another parameter to express those configuration goals.

Also, I think we need some ways to explicitly block recovery to allow
queries to run, and some ways to explicitly block queries so recovery
can run.

Perhaps we need a way to block new queries on a regular basis, so that
recovery gets a chance to run. Kind of time-slicing algorithm, like OS.
That way we could assign a relative priority to each.

Hmmm.

-- 
 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] review: More frame options in window functions

[Erik Rijkers]

> Thanks for the review. I've found another crash today and attached is
> fixed version. The case is:
>
> SELECT four, sum(ten) over (PARTITION BY four ORDER BY four RANGE 1
> PRECEDING) FROM tenk1 WHERE unique1 < 10;
>

Hi,

The patch (more_frame_options.20100115.patch.gz) applies cleanly, but the 
regression test gives:


***
/var/data1/pg_stuff/pg_sandbox/pgsql.rows_frame_types/src/test/regress/expected/window.out
  2010-01-15
22:36:01.0 +0100
---
/var/data1/pg_stuff/pg_sandbox/pgsql.rows_frame_types/src/test/regress/results/window.out
   2010-01-15
22:37:01.0 +0100
***
*** 934,953 

  SELECT four, ten, sum(ten) over (partition by four order by four range 1 
preceding)
  FROM tenk1 WHERE unique1 < 10;
!  four | ten | sum
! --+-+-
! 0 |   0 |  12
! 0 |   8 |  12
! 0 |   4 |  12
! 1 |   5 |  15
! 1 |   9 |  15
! 1 |   1 |  15
! 2 |   6 |   8
! 2 |   2 |   8
! 3 |   3 |  10
! 3 |   7 |  10
! (10 rows)
!
  CREATE VIEW v_window AS
SELECT i, sum(i) over (order by i rows between 1 preceding and 1 
following) as sum_rows,
sum(i) over (order by i / 3 range between 1 preceding and 1 following) 
as sum_range
--- 934,940 

  SELECT four, ten, sum(ten) over (partition by four order by four range 1 
preceding)
  FROM tenk1 WHERE unique1 < 10;
! ERROR:  cannot extract system attribute from minimal tuple
  CREATE VIEW v_window AS
SELECT i, sum(i) over (order by i rows between 1 preceding and 1 
following) as sum_rows,
sum(i) over (order by i / 3 range between 1 preceding and 1 following) 
as sum_range

==



regards,


Erik Rijkers



-- 
Sent 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, loose ends

[Simon Riggs]

On Fri, 2010-01-15 at 22:38 +, Greg Stark wrote:
> On Fri, Jan 15, 2010 at 6:39 PM, Simon Riggs  wrote:
> > On Fri, 2010-01-15 at 19:29 +0200, Heikki Linnakangas wrote:
> > How about "Log Streaming Replication"?
> >
> > So its a particular kind of replication, which seems correct to me.
> 
> I thought the whole point of this effort was to be able to bill it as
> a built-in easy replication which was perceived as a major hole in
> Postgres's feature set.

How does that affect my proposal?

Heikki called it Streaming Replication; I have just added "Log" to it,
to make clear that it is similar in many ways to File-based Log
Shipping, which was a section heading earlier in docs.

-- 
 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] Mammoth in Core?

[Joshua D. Drake]

Hello,

O.k. I know there is no way we will hit this for 8.5. So this is more of
a future discussion more than anything. We at CMD have been working
diligently on our next version of Mammoth Replicator, 1.9. It is
currently revved at 8.4. I expect that we will be close to done if not
done, by the release of 8.5.

My question is, do we have any interest in working on getting this into
core? To give those that don't have any background with Mammoth here is
the run down:

1. It is a patch to .Org. E.g; it integrates with the backend unlike
Slony or Londiste.

2. 1.9 remove the SPOF problem of the 1.8 series by adding forwarder
capabilities within the postmaster itself. (1.8 used a secondary daemon)

3. It has been developed for years as a proprietary product, but was
released as BSD about a year ago.

It supports the following features:

 * Data replication
 * Partial replication (to multiple different slaves)
 * Large Object replication
 * ACL (GRANT/REVOKE) replication
 * ALTER/CREATE ROLE
 * Promotion (And promote back)
 * Firing triggers on a slave with replicated relations (for reporting,
materialized views etc...)
 * Monitoring

The docs are here:

https://projects.commandprompt.com/public/replicator/wiki/Documentation/current

There are some limitations, which could be addressed. I would have to
talk with Alvaro and Alexey further on them but this is more of a field
test.

If the community is interested in having a full scale replication system
in the backend (HS and SR provide different facilities) then CMD is
interested in making this community ready.

If the community isn't interested, we are likely to start putting our
efforts elsewhere (as opposed to Mammoth Replicator).

Sincerely,

Joshua D. Drake



-- 
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] Streaming replication and non-blocking I/O

[Andrew Dunstan]

Tom Lane wrote:

Before we sprinkle all the global variables it touches with that, let me
explain what I meant by dividing walreceiver code differently between
dynamically loaded module and backend code. Right now I have to go to
sleep, though, but I'll try to get back to during the weekend.



Yeah, nothing to be done till we get another buildfarm cycle anyway.


  


I ran an extra cycle. Still a bit of work to do: 



cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication and non-blocking I/O

[Tom Lane]

Andrew Dunstan  writes:
> I ran an extra cycle. Still a bit of work to do: 
> 

Well, at least now we're down to the variables that haven't got
PGDLLIMPORT, rather than wondering what's wrong with the build ...

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] bug in integration SQL parser to plpgsq

[Pavel Stehule]

2010/1/15 Tom Lane :
> Pavel Stehule  writes:
>> it doesn't support EXPLAIN as possible begin of SQL statement:
>
> I've applied a fix for that.

Thank you

Pavel Stehule
>
>                        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] Mammoth in Core?

[Tom Lane]

"Joshua D. Drake"  writes:
> O.k. I know there is no way we will hit this for 8.5. So this is more of
> a future discussion more than anything.

Well, this is not really the time to be having such a discussion; right
now we need to all have our noses to the grindstone dealing with the
already-submitted 8.5 features.  The start of the next devel cycle would
be a more appropriate time to think about it.

(Personally, I suspect we're going to have our hands full dealing with
HS+SR for quite some time to come, which implies we should not scatter
our energies across multiple replication solutions.  But that will be
clearer in a few months when we see what emerges from beta.)

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Bruce Momjian]

Simon Riggs wrote:
> On Fri, 2010-01-15 at 19:29 +0200, Heikki Linnakangas wrote:
> 
> > File-based Log Shipping
> >   Planning
> >   Implementation
> > Streaming Replication
> >   Setting up
> 
> How about "Log Streaming Replication"?
> 
> So its a particular kind of replication, which seems correct to me.

Yea, I like that.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication, loose ends

[Bruce Momjian]

Heikki Linnakangas wrote:
> > Uh, do we really want to call this "replication" rather than archive log
> > streaming or something.  It seems "replication" is a generic term and
> > will confuse people who are using other replication solutions like
> > Slony.
> 
> Good question. OTOH, if we move the sections about setting up a
> file-shipping based standby with pg_standby, that's not streaming.
> 
> What we have now is:

Frankly, I am concerned we now have a "replication" CVS subdirectory; 
it looks more like a 'wal_streaming' directory to me.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New XLOG record indicating WAL-skipping

[Fujii Masao]

On Fri, Jan 15, 2010 at 8:28 PM, Heikki Linnakangas
 wrote:
> I don't like special-casing UNLOGGED records in XLogInsert and
> ReadRecord(). Those functions are complicated enough already. The
> special handling from XLogInsert() (and a few other places) is only
> required because the UNLOGGED records carry no payload. That's easy to
> avoid, just add some payload to them, doesn't matter what it is. And I
> don't think ReadRecord() is the right place to emit the errors/warnings,
> that belongs naturally in xlog_redo().
>
> It might be useful to add some information in the records telling why
> WAL-logging was skipped. It might turn out to be useful in debugging.
> That also conveniently adds payload to the records, to avoid the
> special-casing in XLogInsert() :-).
>
> I think it's a premature optimization to skip writing the records if
> we've written in the same session already. Especially with the 'reason'
> information added to the records, it's nice to have a record of each
> such operation. All operations that skip WAL-logging are heavy enough
> that an additional WAL record will make no difference. I can see that it
> was required to avoid the flooding from heap_insert(), but we can move
> the XLogSkipLogging() call from heap_insert() to heap_sync().
>
> Attached is an updated patch, doing the above. Am I missing anything?

Thanks a lot! Your change seems to be OK.

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] Testing with concurrent sessions

[Markus Wanner]

Hi,

Kevin Grittner wrote:

I'm a little unclear about the differences between "uses",
"depends", and "onlyAfter".  Here's what they *sound* like they
mean, to me; although I don't think the code isn't entirely
consistent with this interpretation.


Wow, you are way ahead of me. I intended to write some documentation 
about that, but...


I differentiate tests and test suites. Tests mainly have a run method, 
while test suites have setUp and tearDown ones.



"uses" means that the referenced task has complimentary setUp and
tearDown methods, and the dependent task may only run after a
successful invocation of the referenced task's setUp method, and the
referenced task will wait for completion of all dependent tasks
before invoking tearDown.


Absolutely correct (may I just copy that para for documentation)? ;-)

Two additional things: tests and test suites may have requirements (in 
the form of interfaces). The used test suites are passed to the 
dependent task and it may call the referenced tasks's methods, for 
example to get the database directory or to run a certain SQL command.


Second, if the referenced task fails, any running dependent task is 
getting aborted as well. That might be obvious, though.



"depends" means that the tearDown method of the referenced task
doesn't undo the work of its setUp, at least for purposes of the
dependent task.  The dependent task can only start after successful
completion of the referenced class's work (*just* setUp, or all the
way to tearDown?), but the referenced task doesn't need to wait for
the dependent task.


Hm.. no, not quite. The fact that not all suites clean up after them has 
nothing to do with how they are referenced ("uses" or "depends"). So 
far, it's entirely up to the test suite. I dislike that, but it works. 
(I've been thinking about some separate resource allocation handling and 
what not, but..)


The only difference between "depends" and "uses" is the requirements 
fulfilling. "uses" does that, while "depends" only adds the timing and 
functional dependencies, but doesn't pass the referenced task as an 
argument to the dependent task.



"onlyAfter" means that the dependent task must wait for completion
of the referenced task, but doesn't care whether or not the
referenced class completed successfully.


That's how I think it *should* be. ATM "onlyAfter" requires successful 
completion of the dependent task.


I'd like to change that to support "onlyAfter", "onlyAfterSuccessOf" and 
"onlyAfterFailureOf". Plus "onlyBefore" for convenience.


This is all work in progress and I'm open to suggestions and requests.

Thank you for thinking through all of this. I'm sure you understand now, 
why it's not a version 0.1, yet :-)


Regards

Markus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Testing with concurrent sessions

[Markus Wanner]

Hi,

Kevin Grittner wrote:

Based on Andrew's suggestion, I changed line 276 to:
 
args=['psql', '-A', '--pset=pager=off',


That looks like a correct fix for psql, yes. Thanks for pointing that 
out Andrew.


Other processes might be confused by (or at least act differently with) 
a PAGER env variable, so that still needs to be cleared in general.



I now get 5 of 6 tests succeeded (83.3%), processed in 18.5 seconds.


That's perfect. The one test that fails is expected to fail (another 
thing dtester doesn't support, yet). The serialization code you write 
should finally make that test pass ;-)


> I do want to expand the tests quite a bit -- do I work them all into
> this same file, or how would I proceed?  I think I'll need about 20
> more tests, but I don't want to get in the way of your work on the
> framework which runs them.

Well, first of all, another piece of the missing manual: there are 
BaseTest and SyncTest classes. Those based on BaseTest runs within the 
event loop of the twisted framework, thus need to be written in the very 
same asynchronous fashion. Mostly calling async methods that return a 
Deferred object, on which you may addCallback() or addErrback(). See the 
fine twisted documentation, especially the part about "Low-Level 
Networking and Event Loop" here:


http://twistedmatrix.com/documents/current/core/howto/index.html

The SyncTest is based on BaseTest, but a new thread is created to run 
its run method, passing back its results to the main event loop when 
done. That allows you to call blocking methods without having to care 
about blocking the entire event loop.


However, it makes interacting between the two models a bit complicated. 
To call an async function from a SyncTest, you need to call the syncCall 
method. The separate thread then waits for some callback in the main 
event loop.


Both have their own set of caveats, IMO.

I'm not sure about how to organize the tests and ongoing development of 
the framework. I've already broken the Postgres-R tests with dtester-0.0.


Maybe we put up a git branch with the dtester patches included? So 
whenever I want to change the framework, I can check if and how it 
affects your tests.


Regards

Markus


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers