Re: [HACKERS] Updated version of pg_receivexlog

[Jaime Casanova]

On Wed, Sep 28, 2011 at 1:38 AM, Jaime Casanova  wrote:
> On Tue, Aug 16, 2011 at 9:32 AM, Magnus Hagander  wrote:
>> Here's an updated version of pg_receivexlog, that should now actually
>> work (it previously failed miserably when a replication record crossed
>> a WAL file boundary - something which I at the time could not properly
>> reproduce, but when I restarted my work on it now could easily
>> reproduce every time :D).
>>
>> It also contains an update to pg_basebackup that allows it to stream
>> the transaction log in the background while the backup is running,
>> thus reducing the need for wal_keep_segments (if the client can keep
>> up, it should eliminate the need completely).
>>
>
> reviewing this...
>

btw, executing 'make world' with this patch gives me this error (seems
like an entry is missing in doc/src/sgml/ref/allfiles.sgml):

jade:reference.sgml:223:4:E: general entity "pgReceivexlog" not
defined and no default entity

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

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

Re: [HACKERS] pg_upgrade - add config directory setting

[Peter Eisentraut]

On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
> It would perhaps be useful to add optional --old-confdir and 
> --new-confdir parameters to pg_upgrade. If these parameters are absent
> then pg_upgrade would work as it does now and assume that the config 
> files are in the datadir. 

It should work the same way the postmaster itself works: If the given
directory is not a data directory, look for the postgresql.conf file and
look there for the location of the data directory.



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

Re: [HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1

[Peter Eisentraut]

On tis, 2011-09-27 at 16:19 -0700, Jamie Fox wrote:
> 
> It fails at this stage:
> 
> Restoring user relation files
> linking /data/pgsql/prod-84/base/11564/2613 to
> /data/pgsql/prod-91/base/12698/12570
> linking /data/pgsql/prod-84/base/11564/2683 to
> /data/pgsql/prod-91/base/12698/12572
> Mismatch of relation names: database "prod1", old rel
> pg_toast.pg_toast_54542379, new rel pg_toast.pg_toast_16735
> Failure, exiting 

This issue is known and a fix is currently being discussed.


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

Re: [HACKERS] fix for pg_upgrade

[panam]

Here are all generated log files.

I just removed all other DBs except gnucash (which includes the accounts
table), but the issue also emerges with other DBs.
Upgraded the 9.1 instance to the new build (9.1.1.) as well but this
apparently did not change anything.
PG versions are (including generated logs):
PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
PostgreSQL 9.1.0, compiled by Visual C++ build 1500, 64-bit: 
http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.0.zip
pg_upgrade_9.1.0.zip 
PostgreSQL 9.1.1, compiled by Visual C++ build 1500, 64-bit: 
http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.1.zip
pg_upgrade_9.1.1.zip 
I hope that is what you meant with "pg_upgrade log file".

Regards,
panam

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4848829.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Does RelCache/SysCache shrink except when relations are deleted?

[MauMau]

Hello,

Please let me ask you some questions about RelCache/SysCache/CatCache 
design. I know I should post this to pgsql-general, but I decided to post 
here because the content includes design questions.


<>
My customer is facing a "out of memory" problem during a batch job. I'd like 
to know the cause and solutions. PostgreSQL version is 8.2.7 (32-bit on 
Linux).


The batch job consists of two steps in a single psql session:

1. call some PL/pgSQL function (say "somefunc" here)
2. VACUUM tables (at this time, maintenance_work_mem=256MB)

The step 2 emitted the following messages in syslog.

ERROR:  out of memory
DETAIL:  Failed on request of size 268435452.
STATEMENT:  VACUUM some_table_name

somefunc copies rows from a single table to 100,000 tables (table_1 - 
table_10) as follows:


[somefunc]
FOR id in 1 .. 10 LOOP
   check if the table "table_${ID}" exists by searching pg_class
   if the table exists
   INSERT INTO table_${id} SELECT * FROM some_table
   WHERE pk = id;
   else /* the table does not exist */
   CREATE TABLE table_${id} AS SELECT * FROM some_table
   WHERE pk = id;
END LOOP;

Before starting somefunc, the virtual memory of the backend postgres is 
1.6GB, as reported by top command as "VIRT" column. When somefunc completes, 
it becomes 2.6GB. So, VACUUM cannot allocate 256MB because the virtual 
memory space is full.


This is all the information I have now. I requested the customer to collect 
PostgreSQL server log so that memory context statistics can be obtained when 
"out of memory" occurs. Plus, I asked for the result of "SHOW ALL" and the 
minimal procedure to reproduce the problem. However, I'd like to ask your 
opinions rather than waiting for the problem to happen again.



<>
I'm guessing that CacheMemoryContext might be using much memory, because 
somefunc accesses as many as 100,000 tables. But I don't understand 
RelCache/SysCache implementation yet.


Q1: When are the RelCache/SysCache entries removed from CacheMemoryContext?
Are they removed only when the corresponding relations are deleted? If so, 
"many tables and indexes" is not friendly for the current PostgreSQL?


Q2: somefunc increased 1GB of virtual memory after accessing 100,000 tables. 
This means that one table uses 10KB of local memory.
Is it common that this much memory is used for RelCache/SysCache or other 
control information?

Does the number of attributes in a table affect local memory usage much?

Q3: I think one solution is to run VACUUM in a separate psql session.
Are there any other solutions you can think of?

Q4: The customer says one strange thing. If the 100,000 tables exist before 
somefunc starts (i.e., somefunc just copy records), the virtual memory of 
postgres does not increase.

Is there anything to reason about his comment?

Regards
MauMau


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

Re: [HACKERS] pg_upgrade automatic testing

[Bruce Momjian]

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian  writes:
> > > I propose I just remove the 8.4
> > > test and always allow toast table names not to match --- the oids are
> > > still checked and are preserved.
> > 
> > +1.  You'll still make the check for non-toast tables, of course?
> 
> Yes, only toast tables will skip the check.  Proposed patch attached.

I was wrong. I can check for the version number because the toast file
name is made to match when pg_upgrade completes on the 9.0+ cluster. 
Updated patch attached that adds comments and checks for 9.0 instead of
8.4.

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

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c
new file mode 100644
index e41ab2b..b55bd6d
*** a/contrib/pg_upgrade/info.c
--- b/contrib/pg_upgrade/info.c
*** gen_db_file_maps(DbInfo *old_db, DbInfo 
*** 57,68 
     old_db->db_name, old_rel->reloid, new_rel->reloid);
  
  		/*
! 		 * In pre-8.4, TOAST table names change during CLUSTER;  in >= 8.4
! 		 * TOAST relation names always use heap table oids, hence we cannot
! 		 * check relation names when upgrading from pre-8.4.
  		 */
  		if (strcmp(old_rel->nspname, new_rel->nspname) != 0 ||
! 			((GET_MAJOR_VERSION(old_cluster.major_version) >= 804 ||
  			  strcmp(old_rel->nspname, "pg_toast") != 0) &&
  			 strcmp(old_rel->relname, new_rel->relname) != 0))
  			pg_log(PG_FATAL, "Mismatch of relation names in database \"%s\": "
--- 57,71 
     old_db->db_name, old_rel->reloid, new_rel->reloid);
  
  		/*
! 		 * TOAST table names initially match the heap pg_class oid.
! 		 * In pre-8.4, TOAST table names change during CLUSTER; in pre-9.0,
! 		 * TOAST table names change during ALTER TABLE ALTER COLUMN SET TYPE.
! 		 * In >= 9.0, TOAST relation names always use heap table oids, hence
! 		 * we cannot check relation names when upgrading from pre-9.0.
! 		 * Clusters upgraded to 9.0 will get matching TOAST names.
  		 */
  		if (strcmp(old_rel->nspname, new_rel->nspname) != 0 ||
! 			((GET_MAJOR_VERSION(old_cluster.major_version) >= 900 ||
  			  strcmp(old_rel->nspname, "pg_toast") != 0) &&
  			 strcmp(old_rel->relname, new_rel->relname) != 0))
  			pg_log(PG_FATAL, "Mismatch of relation names in database \"%s\": "

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

Re: [HACKERS] fix for pg_upgrade

[Bruce Momjian]

panam wrote:
> Here are all generated log files.
> 
> I just removed all other DBs except gnucash (which includes the accounts
> table), but the issue also emerges with other DBs.
> Upgraded the 9.1 instance to the new build (9.1.1.) as well but this
> apparently did not change anything.
> PG versions are (including generated logs):
> PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
> PostgreSQL 9.1.0, compiled by Visual C++ build 1500, 64-bit: 
> http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.0.zip
> pg_upgrade_9.1.0.zip 
> PostgreSQL 9.1.1, compiled by Visual C++ build 1500, 64-bit: 
> http://postgresql.1045698.n5.nabble.com/file/n4848829/pg_upgrade_9.1.1.zip
> pg_upgrade_9.1.1.zip 
> I hope that is what you meant with "pg_upgrade log file".

OK, so it fails for all tables and you are using the newest version. 
Thanks for all your work.  I am now guessing that pg_upgrade 9.1.X is
just broken on Windows. 

Perhaps the variables set by pg_upgrade_support.so are not being passed
into the server variables?  I know pg_upgrade 9.0.X worked on Windows
because EnterpriseDB did extensive testing recently on this.   Has
anyone used pg_upgrade 9.1.X on Windows?

As far as a log file, you need you to use '-l log' and email me that
file.

As far as testing, I wonder if we need to load in pg_upgrade_support on
Windows, and rerun some of the pg_dumpall SQL create table statements to
see why the pg_class.oid and others are not getting set.  For example,
this:

-- For binary upgrade, must preserve pg_class oids
SELECT 
binary_upgrade.set_next_heap_pg_class_oid('465783'::pg_catalog.oid);
SELECT 
binary_upgrade.set_next_toast_pg_class_oid('465786'::pg_catalog.oid);
SELECT 
binary_upgrade.set_next_index_pg_class_oid('465788'::pg_catalog.oid);

CREATE TABLE accounts (
guid character varying(32) NOT NULL,
name character varying(2048) NOT NULL,
account_type character varying(2048) NOT NULL,
commodity_guid character varying(32),
commodity_scu integer NOT NULL,
non_std_scu integer NOT NULL,
parent_guid character varying(32),
code character varying(2048),
description character varying(2048),
hidden integer,
placeholder integer
);

should set the accounts pg_class.oid as 465783.  The server will need to
be started with -b and this will disable autovacuum.  Can someone on
Windows try this?

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

  + It's impossible for everything to be true. +

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

Re: [HACKERS] synchronized snapshots

[Joachim Wieland]

Hi Marko,

On Wed, Sep 28, 2011 at 2:29 AM, Marko Tiikkaja
 wrote:
> In a sequence such as this:
>
>  BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>  INSERT INTO foo VALUES (-1);
>  SELECT pg_export_snapshot();
>
> the row added to "foo" is not visible in the exported snapshot.  If that's
> the desired behaviour, I think it should be mentioned in the documentation.

Yes, that's the desired behaviour, the patch add this paragraph to the
documentation already:

"Also note that even after the synchronization both clients still run
their own independent transactions. As a consequence, even though
synchronized with respect to reading pre-existing data, both
transactions won't be able to see each other's uncommitted data."

I'll take a look at the other issues and update the patch either
tonight or tomorrow.


Thank you,
Joachim

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

Re: [HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1

[Jamie Fox]

Thanks, I'm following the thread "pg_upgrade automatic testing" and
will try the patch just detailed there.

Jamie


On Wed, Sep 28, 2011 at 12:50 AM, Peter Eisentraut  wrote:
> On tis, 2011-09-27 at 16:19 -0700, Jamie Fox wrote:
>>
>> It fails at this stage:
>>
>>     Restoring user relation files
>>     linking /data/pgsql/prod-84/base/11564/2613 to
>> /data/pgsql/prod-91/base/12698/12570
>>     linking /data/pgsql/prod-84/base/11564/2683 to
>> /data/pgsql/prod-91/base/12698/12572
>>     Mismatch of relation names: database "prod1", old rel
>> pg_toast.pg_toast_54542379, new rel pg_toast.pg_toast_16735
>>     Failure, exiting
>
> This issue is known and a fix is currently being discussed.
>
>

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

[HACKERS] Feature proposal: www_fdw

[Alexander Soudakov]

Greetings postgres hackers!

Here you can find www_fdw feature documentation:
http://wiki.postgresql.org/wiki/WWW_FDW

Looking forward for your feedback.

-- 
Alexander Soudakov
Developer Programmer
email: cyga...@gmail.com
skype: asudakov

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

Re: [HACKERS] pg_upgrade - add config directory setting

[Alvaro Herrera]

Excerpts from Peter Eisentraut's message of mié sep 28 04:49:43 -0300 2011:
> On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
> > It would perhaps be useful to add optional --old-confdir and 
> > --new-confdir parameters to pg_upgrade. If these parameters are absent
> > then pg_upgrade would work as it does now and assume that the config 
> > files are in the datadir. 
> 
> It should work the same way the postmaster itself works: If the given
> directory is not a data directory, look for the postgresql.conf file and
> look there for the location of the data directory.

So we need a postmaster switch:

postmaster --parse-config-and-report=data_directory

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Feature proposal: www_fdw

[David Fetter]

On Wed, Sep 28, 2011 at 05:32:37PM +0400, Alexander Soudakov wrote:
> Greetings postgres hackers!
> 
> Here you can find www_fdw feature documentation:
> http://wiki.postgresql.org/wiki/WWW_FDW
> 
> Looking forward for your feedback.

Do you have some libraries you plan to base this on, or will you be
hand-tooling it all?

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] pg_upgrade - add config directory setting

[Steve Crawford]

On 09/28/2011 12:49 AM, Peter Eisentraut wrote:

On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:

It would perhaps be useful to add optional --old-confdir and
--new-confdir parameters to pg_upgrade. If these parameters are absent
then pg_upgrade would work as it does now and assume that the config
files are in the datadir.

It should work the same way the postmaster itself works: If the given
directory is not a data directory, look for the postgresql.conf file and
look there for the location of the data directory.




That would make sense to me (I actually tried setting the datadirs based 
on that assumption). It would require adding that feature to pg_upgrade 
and tweaking the docs for --XXX-datadir but would not require any new 
parameters.


Cheers,
Steve

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

Re: [HACKERS] Feature proposal: www_fdw

[Andrew Dunstan]

On 09/28/2011 09:32 AM, Alexander Soudakov wrote:

Greetings postgres hackers!

Here you can find www_fdw feature documentation:
http://wiki.postgresql.org/wiki/WWW_FDW

Looking forward for your feedback.



Why should this be a core feature, as the subject suggests? It could 
just be an extension, like other FDWs, no?


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] Feature proposal: www_fdw

[Alexander Soudakov]

On Wed, Sep 28, 2011 at 7:17 PM, David Fetter  wrote:
> On Wed, Sep 28, 2011 at 05:32:37PM +0400, Alexander Soudakov wrote:
>> Greetings postgres hackers!
>>
>> Here you can find www_fdw feature documentation:
>> http://wiki.postgresql.org/wiki/WWW_FDW
>>
>> Looking forward for your feedback.
>
> Do you have some libraries you plan to base this on, or will you be
> hand-tooling it all?

I was planning to use 3rd party libraries for json/xml/yaml,
interactions with services. The same way as FWD from here
http://wiki.postgresql.org/wiki/Foreign_data_wrappers do.

But now I guess it might be a problem to include it under contrib
directory (same way as file_fdw), right?

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



-- 
Alexander Soudakov
Developer Programmer
email: cyga...@gmail.com
google talk: cyga...@gmail.com
jabber: asuda...@jabber.org
icq uin: 311516511
skype: asudakov

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

Re: [HACKERS] Extension proposal: www_fdw

[Andrew Dunstan]

On 09/28/2011 11:46 AM, Alexander Soudakov wrote:



Why should this be a core feature, as the subject suggests? It could just be
an extension, like other FDWs, no?


Subject?
I didn't mean this, truly speaking I missed this moment.
But now I guess it would be ok it to be extension like other FDWs.
What do I need to change this? Change subject as s/Feature/Extension/ ?



Just do it and publish it. You don't need permission. If you want 
discussion on the functionality, pgsql-general is probably a more 
appropriate forum for a non-core item.


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] Extension proposal: www_fdw

[Alexander Soudakov]

inline

On Wed, Sep 28, 2011 at 7:21 PM, Andrew Dunstan  wrote:
>
>
> On 09/28/2011 09:32 AM, Alexander Soudakov wrote:
>>
>> Greetings postgres hackers!
>>
>> Here you can find www_fdw feature documentation:
>> http://wiki.postgresql.org/wiki/WWW_FDW
>>
>> Looking forward for your feedback.
>>
>
> Why should this be a core feature, as the subject suggests? It could just be
> an extension, like other FDWs, no?


Subject?
I didn't mean this, truly speaking I missed this moment.
But now I guess it would be ok it to be extension like other FDWs.
What do I need to change this? Change subject as s/Feature/Extension/ ?

>
> cheers
>
> andrew
>



-- 
Alexander Soudakov
Developer Programmer
email: cyga...@gmail.com
google talk: cyga...@gmail.com
jabber: asuda...@jabber.org
icq uin: 311516511
skype: asudakov

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

Re: [HACKERS] Feature proposal: www_fdw

[Andrew Dunstan]

On 09/28/2011 11:41 AM, Alexander Soudakov wrote:

On Wed, Sep 28, 2011 at 7:17 PM, David Fetter  wrote:

On Wed, Sep 28, 2011 at 05:32:37PM +0400, Alexander Soudakov wrote:

Greetings postgres hackers!

Here you can find www_fdw feature documentation:
http://wiki.postgresql.org/wiki/WWW_FDW

Looking forward for your feedback.

Do you have some libraries you plan to base this on, or will you be
hand-tooling it all?

I was planning to use 3rd party libraries for json/xml/yaml,
interactions with services. The same way as FWD from here
http://wiki.postgresql.org/wiki/Foreign_data_wrappers do.

But now I guess it might be a problem to include it under contrib
directory (same way as file_fdw), right?



Postgres is designed to be extensible. Many people are creating FDWs 
which are being published elsewhere, like . There is 
no need to put them all in contrib or anywhere else in the core code.


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] Feature proposal: www_fdw

[Tom Lane]

Andrew Dunstan  writes:
> Why should this be a core feature, as the subject suggests? It could 
> just be an extension, like other FDWs, no?

In fact it had *better* be an extension, not core, because anything that
allows the server to go out and touch the web is going to be a security
hazard in some people's usages.  I can see that some people will want
this type of functionality, but others definitely won't.

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] fix for pg_upgrade

[Bruce Momjian]

Bruce Momjian wrote:
> OK, so it fails for all tables and you are using the newest version. 
> Thanks for all your work.  I am now guessing that pg_upgrade 9.1.X is
> just broken on Windows. 
> 
> Perhaps the variables set by pg_upgrade_support.so are not being passed
> into the server variables?  I know pg_upgrade 9.0.X worked on Windows
> because EnterpriseDB did extensive testing recently on this.   Has
> anyone used pg_upgrade 9.1.X on Windows?

OK, I have a new theory.  postmaster.c processes the -b
(binary-upgrade) flag by setting a C variable:

case 'b':
/* Undocumented flag used for binary upgrades */
IsBinaryUpgrade = true;
break;

I am now wondering if this variable is not being passed down to the
sessions during Win32's EXEC_BACKEND.  Looking at the other postmaster
settings, these set GUC variables, which I assume are passed down.  Can
someone confirm this?  How should this be fixed?

FYI, the binary-upgrade set() functions will not operate unless the -b
option is enabled, which explains the failure the reporter is seeing.

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

  + It's impossible for everything to be true. +

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

[HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[Will Leinweber]

I ruined a 5 hour UPDATE by typoing a table name on a SELECT to verify
the update worked. I suppose I have no one else to blame, but it was
really frustrating, to say the least. I assume this has happened to
others as well.

I only later found out about SAVEPOINT, which I immediately ran the
next time I attempted the huge update.

psql console, while in a transaction, and while in interactive mode,
should savepoint for me.

—Will

bqjezaraxa=# select count(*) from transactions where log='';
ERROR:  relation "transactions" does not exist

bqjezaraxa=# select count(*) from transfers where log='';
ERROR:  current transaction is aborted, commands ignored until end of
transaction block

bqjezaraxa=# oh damn it ;
ERROR:  syntax error at or near "oh"
LINE 1: oh damn it;

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

Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[Gurjeet Singh]

On Tue, Sep 27, 2011 at 7:57 PM, Will Leinweber  wrote:

> I ruined a 5 hour UPDATE by typoing a table name on a SELECT to verify
> the update worked. I suppose I have no one else to blame, but it was
> really frustrating, to say the least. I assume this has happened to
> others as well.
>
> I only later found out about SAVEPOINT, which I immediately ran the
> next time I attempted the huge update.
>
> psql console, while in a transaction, and while in interactive mode,
> should savepoint for me.
>
>
I guess it would be a neat feature to have this in Postgres rather than in
psql. That is, if running in an explicit transaction (one started with
BEGIN), issue a savepoint after/before every command and emit the savepoint
name in a NOTICE.

I *think* savepoints are detrimental to performance, maybe under certain
pre-conditions, so it might be desirable to control it using a user-settable
parameter.

If there's no perceivable performance difference in using savepoints even
under large transactions, then we might want to make it all automatic and
transparent. So Postgres issues a savepoint before every command, and if the
command fails, rollback to that savepoint, else release that savepoint.

Regards,
-- 
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[Marko Tiikkaja]

On 28/09/2011 02:57, Will Leinweber wrote:

psql console, while in a transaction, and while in interactive mode,
should savepoint for me.


You are lucky, since that feature has been in psql for some time 
already.  Simply add this to your .psqlrc:


\set ON_ERROR_ROLLBACK on


--
Marko Tiikkajahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[Alvaro Herrera]

Excerpts from Will Leinweber's message of mar sep 27 20:57:52 -0300 2011:
> I ruined a 5 hour UPDATE by typoing a table name on a SELECT to verify
> the update worked. I suppose I have no one else to blame, but it was
> really frustrating, to say the least. I assume this has happened to
> others as well.
> 
> I only later found out about SAVEPOINT, which I immediately ran the
> next time I attempted the huge update.
> 
> psql console, while in a transaction, and while in interactive mode,
> should savepoint for me.

See ON_ERROR_ROLLBACK
http://www.postgresql.org/docs/9.0/static/app-psql.html

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[Kevin Grittner]

Gurjeet Singh  wrote:
> Will Leinweber  wrote:
> 
>> I ruined a 5 hour UPDATE by typoing a table name on a SELECT to
>> verify the update worked.
 
Ouch!  I normally use tab-completion or copy/paste to save myself
from myself in such situations.
 
>> I only later found out about SAVEPOINT, which I immediately ran
>> the next time I attempted the huge update.
 
That could work, too.  Of course you have to remember too execute
the SAVEPOINT statement *after* the big UPDATE, and you could have a
typo in entering the SAVEPOINT command.
 
>> psql console, while in a transaction, and while in interactive
>> mode, should savepoint for me.
 
I could potentially see a psql backslash command for that.  One
concern I would have about it is that sometimes people paste a
series of commands into an interactive psql session as one big
paste.  What happens then?
 
> I guess it would be a neat feature to have this in Postgres rather
> than in psql. That is, if running in an explicit transaction (one
> started with BEGIN), issue a savepoint after/before every command
> and emit the savepoint name in a NOTICE.
 
You certainly wouldn't want to do that all the time, and I'm very
skeptical about the idea of putting it in the backend code.  For one
thing, I don't think it makes sense to do this except in an
interactive session.  (At least, I can't think of a use-case without
a human on the other end of the connection.)
 
> If there's no perceivable performance difference in using
> savepoints even under large transactions, then we might want to
> make it all automatic and transparent. So Postgres issues a
> savepoint before every command, and if the command fails, rollback
> to that savepoint, else release that savepoint.
 
No.  While I haven't seen the "failed transaction" concept in other
databases, I'm inclined to think it's a good idea.  If you have a
series of statements to run, and one of them fails, continuing to
execute later statements could cause data loss.  (Picture CREATE
TABLE AS SELECT followed by DROP TABLE on the original.)
 
-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] Updated version of pg_receivexlog

[Magnus Hagander]

On Wed, Sep 28, 2011 at 08:38, Jaime Casanova  wrote:
> On Tue, Aug 16, 2011 at 9:32 AM, Magnus Hagander  wrote:
>> Here's an updated version of pg_receivexlog, that should now actually
>> work (it previously failed miserably when a replication record crossed
>> a WAL file boundary - something which I at the time could not properly
>> reproduce, but when I restarted my work on it now could easily
>> reproduce every time :D).
>>
>> It also contains an update to pg_basebackup that allows it to stream
>> the transaction log in the background while the backup is running,
>> thus reducing the need for wal_keep_segments (if the client can keep
>> up, it should eliminate the need completely).
>>
>
> reviewing this...
>
> i found useful pg_receivexlog as an independent utility, but i'm not
> so sure about the ability to call it from pg_basebackup via --xlog
> option. this is because pg_receivexlog will continue streaming even
> after pg_basebackup if it's called independently but not in the other
> case so the use case for --xlog seems more narrow and error prone (ie:
> you said that it reduces the need for wal_keep_segments *if the client
> can keep up*... how can we know that before starting pg_basebackup?)

These two are not intended to be used together.

pg_basebackup --xlog=stream is intended for the same use-case as
"pg_basebackup -x" today, which is take a backup of just the parts
that you actually need to clone the database, but to do so without
having to guestimate the value for wal_keep_segments.


> pg_receivexlog worked good in my tests.
>
> pg_basebackup with --xlog=stream gives me an already recycled wal
> segment message (note that the file was in pg_xlog in the standby):
> FATAL:  could not receive data from WAL stream: FATAL:  requested WAL
> segment 0001005C has already been removed

Do you get this reproducibly? Or did you get it just once?

And when you say "in the standby" what are you referring to? There is
no standby server in the case of pg_basebackup --xlog=stream, it's
just backup... But are you saying pg_basebackup had received the file,
yet tried to get it again?


> in other things:
> do we need to include src/bin/pg_basebackup/.gitignore in the patch?

Not sure what you mean? We need to add pg_receivexlog to this file,
yes - in head it just contains pg_basebackup.

-- 
 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] feature request: auto savepoint for interactive psql when in transaction.

[Kevin Grittner]

Alvaro Herrera  wrote:
 
> See ON_ERROR_ROLLBACK
> http://www.postgresql.org/docs/9.0/static/app-psql.html
 
I had missed that.  Dang, this database product is rich with nice
features!  :-)
 
-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] Updated version of pg_receivexlog

[Magnus Hagander]

On Wed, Sep 28, 2011 at 09:30, Jaime Casanova  wrote:
> On Wed, Sep 28, 2011 at 1:38 AM, Jaime Casanova  wrote:
>> On Tue, Aug 16, 2011 at 9:32 AM, Magnus Hagander  wrote:
>>> Here's an updated version of pg_receivexlog, that should now actually
>>> work (it previously failed miserably when a replication record crossed
>>> a WAL file boundary - something which I at the time could not properly
>>> reproduce, but when I restarted my work on it now could easily
>>> reproduce every time :D).
>>>
>>> It also contains an update to pg_basebackup that allows it to stream
>>> the transaction log in the background while the backup is running,
>>> thus reducing the need for wal_keep_segments (if the client can keep
>>> up, it should eliminate the need completely).
>>>
>>
>> reviewing this...
>>
>
> btw, executing 'make world' with this patch gives me this error (seems
> like an entry is missing in doc/src/sgml/ref/allfiles.sgml):
>
> jade:reference.sgml:223:4:E: general entity "pgReceivexlog" not
> defined and no default entity

Ugh, how did I miss that. You need this:

diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 8a8616b..382d297 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -172,6 +172,7 @@ Complete list of usable sgml source files in this directory.
 
 
 
+
 
 
 



I think I broke it in a merge at some point..
-- 
 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] feature request: auto savepoint for interactive psql when in transaction.

[Gurjeet Singh]

On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner  wrote:

> Alvaro Herrera  wrote:
>
> > See ON_ERROR_ROLLBACK
> > http://www.postgresql.org/docs/9.0/static/app-psql.html
>
> I had missed that.  Dang, this database product is rich with nice
> features!  :-)
>

+1

I would like it to be on/interactive by default, though.

-- 
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[David Fetter]

On Wed, Sep 28, 2011 at 02:25:44PM -0400, Gurjeet Singh wrote:
> On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner  > wrote:
> 
> > Alvaro Herrera  wrote:
> >
> > > See ON_ERROR_ROLLBACK
> > > http://www.postgresql.org/docs/9.0/static/app-psql.html
> >
> > I had missed that.  Dang, this database product is rich with nice
> > features!  :-)
> >
> 
> +1
> 
> I would like it to be on/interactive by default, though.

You can have it by putting it in your .psqlrc.

If we were just starting out, I'd be all for changing the defaults,
but we're not.  We'd break things unnecessarily if we changed this
default.

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] feature request: auto savepoint for interactive psql when in transaction.

[Stephen Frost]

* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote:
> Alvaro Herrera  wrote:
> > See ON_ERROR_ROLLBACK
> > http://www.postgresql.org/docs/9.0/static/app-psql.html
>  
> I had missed that.  Dang, this database product is rich with nice
> features!  :-)

Be careful when running scripts, however..  Any invocation of psql will
read you .psqlrc and if you've got ON_ERROR_ROLLBACK set there then
psql -f blah ; will pick up on that and you'll end up running every
command in the script (including the commit at the end...), even if some
of the commands in it failed.

So, if you put it in your .psqlrc, be VERY sure that you UN-set it on
the command-line whenever running SQL scripts which *should* rollback on
failures..

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[Alvaro Herrera]

Excerpts from Stephen Frost's message of mié sep 28 16:22:58 -0300 2011:

> Be careful when running scripts, however..  Any invocation of psql will
> read you .psqlrc and if you've got ON_ERROR_ROLLBACK set there then
> psql -f blah ; will pick up on that and you'll end up running every
> command in the script (including the commit at the end...), even if some
> of the commands in it failed.

So set it to "interactive".

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Review of VS 2010 support patches

[Brar Piening]

Brar Piening wrote:


The attached patch includes documentation changes and excludes my 
versions of pgbison.pl and pgflex.pl which have been replaced by 
Andrews' versions that are already commited.


Building current head today I noticed that the patch doesn't apply 
cleanly anymore.


Attached is a new version.

Regards,

Brar
diff -Napcdr -x .git postgresql/doc/src/sgml/install-windows.sgml 
postgresql_dev/doc/src/sgml/install-windows.sgml
*** postgresql/doc/src/sgml/install-windows.sgmlWed Sep 28 19:23:58 2011
--- postgresql_dev/doc/src/sgml/install-windows.sgmlWed Sep 28 19:32:22 2011
***
*** 20,29 
There are several different ways of building PostgreSQL on
Windows. The simplest way to build with
Microsoft tools is to install a supported version of the
!   Microsoft Platform SDK and use the included
compiler. It is also possible to build with the full
!   Microsoft Visual C++ 2005 or 2008. In some cases
!   that requires the installation of the Platform 
SDK
in addition to the compiler.
   
  
--- 20,29 
There are several different ways of building PostgreSQL on
Windows. The simplest way to build with
Microsoft tools is to install a supported version of the
!   Microsoft Windows SDK and use the included
compiler. It is also possible to build with the full
!   Microsoft Visual C++ 2005, 2008 or 2010. In some 
cases
!   that requires the installation of the Windows SDK
in addition to the compiler.
   
  
***
*** 69,100 
  
   
Building with Visual C++ or the
!   Platform SDK
  
   
PostgreSQL can be built using the Visual C++ compiler suite from Microsoft.
These compilers can be either from Visual Studio,
Visual Studio Express or some versions of the
!   Platform SDK. If you do not already have a
Visual Studio environment set up, the easiest
!   way us to use the compilers in the Platform SDK,
which is a free download from Microsoft.
   
  
   
!   PostgreSQL supports the compilers from
!   Visual Studio 2005 and
!   Visual Studio 2008. When using the Platform SDK
!   only, or when building for 64-bit Windows, only
!   Visual Studio 2008 is supported.
!   Visual Studio 2010 is not yet supported.
!  
! 
!  
!   When building using the Platform SDK, versions
!   6.0 to 7.0 of the SDK are supported. Older or newer versions will not work.
!   In particular, versions from 7.0a and later will not work, since
!   they include compilers from Visual Studio 2010.
   
  
   
--- 69,94 
  
   
Building with Visual C++ or the
!   Microsoft Windows SDK
  
   
PostgreSQL can be built using the Visual C++ compiler suite from Microsoft.
These compilers can be either from Visual Studio,
Visual Studio Express or some versions of the
!   Microsoft Windows SDK. If you do not already 
have a
Visual Studio environment set up, the easiest
!   way is to use the compilers in the Windows SDK,
which is a free download from Microsoft.
   
  
   
!   PostgreSQL is known to support compilation using the compilers shipped with
!   Visual Studio 2005 to
!   Visual Studio 2010 (including Express editions),
!   as well as standalone Windows SDK releases 6.0 to 7.1.
!   64-bit PostgreSQL builds are only supported with
!   Microsoft Windows SDK version 6.0a and above or
!   Visual Studio 2008 and above.
   
  
   
***
*** 104,114 
Cygwin present in your system PATH. Also, make
sure you have all the required Visual C++ tools available in the PATH. In
Visual Studio, start the
!   Visual Studio Command Prompt. In the
!   Platform SDK, start the
!   CMD shell listed under the SDK on the Start Menu.
If you wish to build a 64-bit version, you must use the 64-bit version of
the command, and vice versa.
All commands should be run from the src\tools\msvc
directory.
   
--- 98,110 
Cygwin present in your system PATH. Also, make
sure you have all the required Visual C++ tools available in the PATH. In
Visual Studio, start the
!   Visual Studio Command Prompt.
If you wish to build a 64-bit version, you must use the 64-bit version of
the command, and vice versa.
+   In the Microsoft Windows SDK, start the
+   CMD shell listed under the SDK on the Start Menu.
+   In recent SDK versions you can change the targeted CPU architecture by using
+   the setenv command.
All commands should be run from the src\tools\msvc
directory.
   
*** $ENV{PATH}=$ENV{PATH} . ';c:\some\where\
*** 148,164 
  
 
  
!  Microsoft Platform SDK
   
It is recommended that you upgrade to the latest supported version
!   of the Microsoft Platform SDK (currently
!   version 7.0), available for download from
http://www.microsoft.com/downloads/";>.
   
   
You must always include the
Windows Headers and Libraries part of the 
SDK.
!   If you install the Platform SDK
including the Vi

Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[Kevin Grittner]

Alvaro Herrera  wrote:
 
>> ON_ERROR_ROLLBACK ["on" can be a problem in a script file]
 
> So set it to "interactive".
 
I think we have an opportunity for a documentation enhancement 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] Does RelCache/SysCache shrink except when relations are deleted?

[Merlin Moncure]

2011/9/28 MauMau :
> Hello,
>
> Please let me ask you some questions about RelCache/SysCache/CatCache
> design. I know I should post this to pgsql-general, but I decided to post
> here because the content includes design questions.
>
> <>
> My customer is facing a "out of memory" problem during a batch job. I'd like
> to know the cause and solutions. PostgreSQL version is 8.2.7 (32-bit on
> Linux).
>
> The batch job consists of two steps in a single psql session:
>
> 1. call some PL/pgSQL function (say "somefunc" here)
> 2. VACUUM tables (at this time, maintenance_work_mem=256MB)
>
> The step 2 emitted the following messages in syslog.
>
> ERROR:  out of memory
> DETAIL:  Failed on request of size 268435452.
> STATEMENT:  VACUUM some_table_name
>
> somefunc copies rows from a single table to 100,000 tables (table_1 -
> table_10) as follows:
>
> [somefunc]
> FOR id in 1 .. 10 LOOP
>   check if the table "table_${ID}" exists by searching pg_class
>   if the table exists
>       INSERT INTO table_${id} SELECT * FROM some_table
>           WHERE pk = id;
>   else /* the table does not exist */
>       CREATE TABLE table_${id} AS SELECT * FROM some_table
>           WHERE pk = id;
> END LOOP;
>
> Before starting somefunc, the virtual memory of the backend postgres is
> 1.6GB, as reported by top command as "VIRT" column. When somefunc completes,
> it becomes 2.6GB. So, VACUUM cannot allocate 256MB because the virtual
> memory space is full.
>
> This is all the information I have now. I requested the customer to collect
> PostgreSQL server log so that memory context statistics can be obtained when
> "out of memory" occurs. Plus, I asked for the result of "SHOW ALL" and the
> minimal procedure to reproduce the problem. However, I'd like to ask your
> opinions rather than waiting for the problem to happen again.
>
>
> <>
> I'm guessing that CacheMemoryContext might be using much memory, because
> somefunc accesses as many as 100,000 tables. But I don't understand
> RelCache/SysCache implementation yet.
>
> Q1: When are the RelCache/SysCache entries removed from CacheMemoryContext?
> Are they removed only when the corresponding relations are deleted? If so,
> "many tables and indexes" is not friendly for the current PostgreSQL?
>
> Q2: somefunc increased 1GB of virtual memory after accessing 100,000 tables.
> This means that one table uses 10KB of local memory.
> Is it common that this much memory is used for RelCache/SysCache or other
> control information?
> Does the number of attributes in a table affect local memory usage much?
>
> Q3: I think one solution is to run VACUUM in a separate psql session.
> Are there any other solutions you can think of?
>
> Q4: The customer says one strange thing. If the 100,000 tables exist before
> somefunc starts (i.e., somefunc just copy records), the virtual memory of
> postgres does not increase.
> Is there anything to reason about his comment?

can we see all of your memory settings plus physical memory?  the
solution is probably going to be reducing shared buffers an/or adding
physical memory.

merlin

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

Re: [HACKERS] Feature proposal: www_fdw

[Oleg Bartunov]

Guys,

I suggest Alexander to announce his project just to let all us know and
avoid duplicate work. I hope it's a good starter project for Alexander !
I agree with Andrew, it's also should be posted to -general. 
It's clear it should be an extension !


Oleg
On Wed, 28 Sep 2011, Tom Lane wrote:


Andrew Dunstan  writes:

Why should this be a core feature, as the subject suggests? It could
just be an extension, like other FDWs, no?


In fact it had *better* be an extension, not core, because anything that
allows the server to go out and touch the web is going to be a security
hazard in some people's usages.  I can see that some people will want
this type of functionality, but others definitely won't.

regards, tom lane




Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Re: [HACKERS] Feature proposal: www_fdw

[Florian Pflug]

On Sep28, 2011, at 15:32 , Alexander Soudakov wrote:
> Here you can find www_fdw feature documentation:
> http://wiki.postgresql.org/wiki/WWW_FDW

Certainly looks useful (as a third-party extension, as others have already 
pointed out)

What I didn't quite understand is how one would pass (dynamic) parameters for a 
GET request. For example, not too long ago I needed to access the Google Maps 
API from postgres. I ended up using pl/python, and now wonder if your FDW would 
support that use-case.

best regards,
Florian Pflug


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

Re: [HACKERS] fix for pg_upgrade

[panam]

Hi Bruce,

here is the file you asked for: 
http://postgresql.1045698.n5.nabble.com/file/n4850735/pg_upgrade_logfile.txt
pg_upgrade_logfile.txt 

I guess you are not addressing me here, right?
> The server will need to 
> be started with -b and this will disable autovacuum.  Can someone on 
> Windows try this?

Thanks
panam 

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4850735.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Feature proposal: www_fdw

[Kevin Grittner]

Florian Pflug  wrote:
> On Sep28, 2011, at 15:32 , Alexander Soudakov wrote:
>> Here you can find www_fdw feature documentation:
>> http://wiki.postgresql.org/wiki/WWW_FDW
> 
> Certainly looks useful (as a third-party extension, as others have
> already pointed out)
 
Our programmers agree that it is likely to be useful here.  I agree
that it should be an extension.
 
> What I didn't quite understand is how one would pass (dynamic)
> parameters for a GET request. For example, not too long ago I
> needed to access the Google Maps API from postgres. I ended up
> using pl/python, and now wonder if your FDW would support that
> use-case.
 
I would assume that the usual ? to start parameters and & between
parameters would be used.  For example, with Google Maps:
 
http://maps.google.com/maps?hl=en&ie=UTF8&hq=&hnear=Madison,+Dane,+Wisconsin&ll=43.074684,-89.38188&spn=0.003006,0.00383&t=h&z=18&layer=c&cbll=43.07468,-89.381742&panoid=LhJ-PFHVzxRguJ6h616mmQ&cbp=12,355.53,,0,-1.32
 
-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] feature request: auto savepoint for interactive psql when in transaction.

[Daniel Farina]

On Wed, Sep 28, 2011 at 1:02 PM, Kevin Grittner
 wrote:
> Alvaro Herrera  wrote:
>
>>> ON_ERROR_ROLLBACK ["on" can be a problem in a script file]
>
>> So set it to "interactive".
>
> I think we have an opportunity for a documentation enhancement there.

In the same vein, I think there may also be some room for
discoverability enhancements here in psql, too: I've been using psql
for years and just compensated for want of this feature by being very
careful or annoyed (when that didn't work out).  I never knew about this.

Something along the lines of the HINT message emitted from the server,
but I do not know if client-side psql has any prior art for that.

-- 
fdr

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

Re: [HACKERS] fix for pg_upgrade

[Bruce Momjian]

panam wrote:
> Hi Bruce,
> 
> here is the file you asked for: 
> http://postgresql.1045698.n5.nabble.com/file/n4850735/pg_upgrade_logfile.txt
> pg_upgrade_logfile.txt 
> 

OK, I see it using -b to pg_ctl:

""C:\Program Files\PostgreSQL\9.1\bin/pg_ctl" -w -l "nul" -D 
"D:\applications\postgres\9.1" -o "-p 5432 -b" start >> "nul" 2>&1"

What I have to find out is whether this is passed to the individual
session processes.  I guess is no.

> I guess you are not addressing me here, right?
> > The server will need to 
> > be started with -b and this will disable autovacuum.  Can someone on 
> > Windows try this?

No, not really.  I think it is a software bug and I need guidance about
a solution.

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

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Updated version of pg_receivexlog

[Jaime Casanova]

On Wed, Sep 28, 2011 at 12:50 PM, Magnus Hagander  wrote:
>
>> pg_receivexlog worked good in my tests.
>>
>> pg_basebackup with --xlog=stream gives me an already recycled wal
>> segment message (note that the file was in pg_xlog in the standby):
>> FATAL:  could not receive data from WAL stream: FATAL:  requested WAL
>> segment 0001005C has already been removed
>
> Do you get this reproducibly? Or did you get it just once?
>
> And when you say "in the standby" what are you referring to? There is
> no standby server in the case of pg_basebackup --xlog=stream, it's
> just backup... But are you saying pg_basebackup had received the file,
> yet tried to get it again?
>

ok, i was trying to setup a standby server cloning with
pg_basebackup... i can't use it that way?

the docs says:
"""
If this option is specified, it is possible to start a postmaster
directly in the extracted directory without the need to consult the
log archive, thus making this a completely standalone backup.
"""

it doesn't say that is not possible to use this for a standby
server... probably that's why i get the error i put a recovery.conf
after pg_basebackup finished... maybe we can say that  more loudly?

>
>> in other things:
>> do we need to include src/bin/pg_basebackup/.gitignore in the patch?
>
> Not sure what you mean? We need to add pg_receivexlog to this file,
> yes - in head it just contains pg_basebackup.
>

your patch includes a modification in the file
src/bin/pg_basebackup/.gitignore, maybe i'm just being annoying
besides is a simple change... just forget that...

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

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

Re: [HACKERS] fix for pg_upgrade

[Alvaro Herrera]

Excerpts from Bruce Momjian's message of mié sep 28 13:48:28 -0300 2011:
> Bruce Momjian wrote:
> > OK, so it fails for all tables and you are using the newest version. 
> > Thanks for all your work.  I am now guessing that pg_upgrade 9.1.X is
> > just broken on Windows. 
> > 
> > Perhaps the variables set by pg_upgrade_support.so are not being passed
> > into the server variables?  I know pg_upgrade 9.0.X worked on Windows
> > because EnterpriseDB did extensive testing recently on this.   Has
> > anyone used pg_upgrade 9.1.X on Windows?
> 
> OK, I have a new theory.  postmaster.c processes the -b
> (binary-upgrade) flag by setting a C variable:
> 
> case 'b':
> /* Undocumented flag used for binary upgrades */
> IsBinaryUpgrade = true;
> break;
> 
> I am now wondering if this variable is not being passed down to the
> sessions during Win32's EXEC_BACKEND.  Looking at the other postmaster
> settings, these set GUC variables, which I assume are passed down.  Can
> someone confirm this?

Well, you could compile it with -DEXEC_BACKEND to test it for yourself.

>  How should this be fixed?

Maybe it should be part of struct BackendParameters.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.

[Will Leinweber]

On Wed, Sep 28, 2011 at 10:39 AM, Marko Tiikkaja
 wrote:
>  Simply add this to your .psqlrc:
>
> \set ON_ERROR_ROLLBACK on


Thank you Marko and Alvaro for pointing me in the right direction. I
set it to 'interactive', which I think makes the most sense.

I do wish this behavior was a little more discoverable, even though it
is in the manual.

—Will

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

Re: [HACKERS] pg_upgrade automatic testing

[Bruce Momjian]

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Bruce Momjian  writes:
> > > > I propose I just remove the 8.4
> > > > test and always allow toast table names not to match --- the oids are
> > > > still checked and are preserved.
> > > 
> > > +1.  You'll still make the check for non-toast tables, of course?
> > 
> > Yes, only toast tables will skip the check.  Proposed patch attached.
> 
> I was wrong. I can check for the version number because the toast file
> name is made to match when pg_upgrade completes on the 9.0+ cluster. 
> Updated patch attached that adds comments and checks for 9.0 instead of
> 8.4.

Applied to head and 9.1.

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

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1

[Bruce Momjian]

Jamie Fox wrote:
> Thanks, I'm following the thread "pg_upgrade automatic testing" and
> will try the patch just detailed there.

I have applied the patch to head and 9.1.X.  We still have a win32 bug
to fix.  It is a shame I was not able to fix these before 9.1.1 was
released.  :-(

---

> 
> 
> On Wed, Sep 28, 2011 at 12:50 AM, Peter Eisentraut  wrote:
> > On tis, 2011-09-27 at 16:19 -0700, Jamie Fox wrote:
> >>
> >> It fails at this stage:
> >>
> >> ? ? Restoring user relation files
> >> ? ? linking /data/pgsql/prod-84/base/11564/2613 to
> >> /data/pgsql/prod-91/base/12698/12570
> >> ? ? linking /data/pgsql/prod-84/base/11564/2683 to
> >> /data/pgsql/prod-91/base/12698/12572
> >> ? ? Mismatch of relation names: database "prod1", old rel
> >> pg_toast.pg_toast_54542379, new rel pg_toast.pg_toast_16735
> >> ? ? Failure, exiting
> >
> > This issue is known and a fix is currently being discussed.
> >
> >
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

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

  + It's impossible for everything to be true. +

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

Re: [HACKERS] fix for pg_upgrade

[Bruce Momjian]

Alvaro Herrera wrote:
> 
> Excerpts from Bruce Momjian's message of mi?? sep 28 13:48:28 -0300 2011:
> > Bruce Momjian wrote:
> > > OK, so it fails for all tables and you are using the newest version. 
> > > Thanks for all your work.  I am now guessing that pg_upgrade 9.1.X is
> > > just broken on Windows. 
> > > 
> > > Perhaps the variables set by pg_upgrade_support.so are not being passed
> > > into the server variables?  I know pg_upgrade 9.0.X worked on Windows
> > > because EnterpriseDB did extensive testing recently on this.   Has
> > > anyone used pg_upgrade 9.1.X on Windows?
> > 
> > OK, I have a new theory.  postmaster.c processes the -b
> > (binary-upgrade) flag by setting a C variable:
> > 
> > case 'b':
> > /* Undocumented flag used for binary upgrades */
> > IsBinaryUpgrade = true;
> > break;
> > 
> > I am now wondering if this variable is not being passed down to the
> > sessions during Win32's EXEC_BACKEND.  Looking at the other postmaster
> > settings, these set GUC variables, which I assume are passed down.  Can
> > someone confirm this?
> 
> Well, you could compile it with -DEXEC_BACKEND to test it for yourself.
> 
> >  How should this be fixed?
> 
> Maybe it should be part of struct BackendParameters.

Thanks.  That's what I did, and tested the failure with -DEXEC_BACKEND,
and the fix with the patch, which is attached.  I am confident this will
fix Windows as well.

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

  + It's impossible for everything to be true. +
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
new file mode 100644
index 94b57fa..0a84d97
*** a/src/backend/postmaster/postmaster.c
--- b/src/backend/postmaster/postmaster.c
*** typedef struct
*** 433,438 
--- 433,439 
  	TimestampTz PgStartTime;
  	TimestampTz PgReloadTime;
  	bool		redirection_done;
+ 	bool		IsBinaryUpgrade;
  #ifdef WIN32
  	HANDLE		PostmasterHandle;
  	HANDLE		initial_signal_pipe;
*** save_backend_variables(BackendParameters
*** 4653,4658 
--- 4654,4660 
  	param->PgReloadTime = PgReloadTime;
  
  	param->redirection_done = redirection_done;
+ 	param->IsBinaryUpgrade = IsBinaryUpgrade;
  
  #ifdef WIN32
  	param->PostmasterHandle = PostmasterHandle;
*** restore_backend_variables(BackendParamet
*** 4874,4879 
--- 4876,4882 
  	PgReloadTime = param->PgReloadTime;
  
  	redirection_done = param->redirection_done;
+ 	IsBinaryUpgrade = param->IsBinaryUpgrade;
  
  #ifdef WIN32
  	PostmasterHandle = param->PostmasterHandle;

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

[HACKERS] Removing savepointLevel from TransactionState

[Gurjeet Singh]

I noticed that the savepointLevel member of TransactionStateData struct is
initialized to 0 from TopTransactionStateData, and never incremented or
decremented afterwards.

Since this is a file-local struct I think we can simply get rid of all
usages of this without any risk. I visited all the commits where this
variable was introduced/used/changed and could not find any point in history
where it was ever useful. Maybe I missed something, but looks like it is a
leftover from someone's forward-thinking.

Patch attached.

Regards,
-- 
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 3dab45c..cde3772 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -132,7 +132,6 @@ typedef struct TransactionStateData
 	TransactionId transactionId;	/* my XID, or Invalid if none */
 	SubTransactionId subTransactionId;	/* my subxact ID */
 	char	   *name;			/* savepoint name, if any */
-	int			savepointLevel; /* savepoint level */
 	TransState	state;			/* low-level state */
 	TBlockState blockState;		/* high-level state */
 	int			nestingLevel;	/* transaction nesting depth */
@@ -2641,12 +2640,10 @@ CommitTransactionCommand(void)
 		case TBLOCK_SUBRESTART:
 			{
 char	   *name;
-int			savepointLevel;
 
 /* save name and keep Cleanup from freeing it */
 name = s->name;
 s->name = NULL;
-savepointLevel = s->savepointLevel;
 
 AbortSubTransaction();
 CleanupSubTransaction();
@@ -2654,7 +2651,6 @@ CommitTransactionCommand(void)
 DefineSavepoint(NULL);
 s = CurrentTransactionState;	/* changed by push */
 s->name = name;
-s->savepointLevel = savepointLevel;
 
 /* This is the same as TBLOCK_SUBBEGIN case */
 AssertState(s->blockState == TBLOCK_SUBBEGIN);
@@ -2670,19 +2666,16 @@ CommitTransactionCommand(void)
 		case TBLOCK_SUBABORT_RESTART:
 			{
 char	   *name;
-int			savepointLevel;
 
 /* save name and keep Cleanup from freeing it */
 name = s->name;
 s->name = NULL;
-savepointLevel = s->savepointLevel;
 
 CleanupSubTransaction();
 
 DefineSavepoint(NULL);
 s = CurrentTransactionState;	/* changed by push */
 s->name = name;
-s->savepointLevel = savepointLevel;
 
 /* This is the same as TBLOCK_SUBBEGIN case */
 AssertState(s->blockState == TBLOCK_SUBBEGIN);
@@ -3536,12 +3529,6 @@ ReleaseSavepoint(List *options)
 (errcode(ERRCODE_S_E_INVALID_SPECIFICATION),
  errmsg("no such savepoint")));
 
-	/* disallow crossing savepoint level boundaries */
-	if (target->savepointLevel != s->savepointLevel)
-		ereport(ERROR,
-(errcode(ERRCODE_S_E_INVALID_SPECIFICATION),
- errmsg("no such savepoint")));
-
 	/*
 	 * Mark "commit pending" all subtransactions up to the target
 	 * subtransaction.	The actual commits will happen when control gets to
@@ -3635,12 +3622,6 @@ RollbackToSavepoint(List *options)
 (errcode(ERRCODE_S_E_INVALID_SPECIFICATION),
  errmsg("no such savepoint")));
 
-	/* disallow crossing savepoint level boundaries */
-	if (target->savepointLevel != s->savepointLevel)
-		ereport(ERROR,
-(errcode(ERRCODE_S_E_INVALID_SPECIFICATION),
- errmsg("no such savepoint")));
-
 	/*
 	 * Mark "abort pending" all subtransactions up to the target
 	 * subtransaction.	The actual aborts will happen when control gets to
@@ -4287,7 +4268,6 @@ PushTransaction(void)
 	s->parent = p;
 	s->nestingLevel = p->nestingLevel + 1;
 	s->gucNestLevel = NewGUCNestLevel();
-	s->savepointLevel = p->savepointLevel;
 	s->state = TRANS_DEFAULT;
 	s->blockState = TBLOCK_SUBBEGIN;
 	GetUserIdAndSecContext(&s->prevUser, &s->prevSecContext);

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

Re: [HACKERS] pg_upgrade - add config directory setting

[Peter Eisentraut]

On ons, 2011-09-28 at 11:53 -0300, Alvaro Herrera wrote:
> Excerpts from Peter Eisentraut's message of mié sep 28 04:49:43 -0300 2011:
> > On tis, 2011-09-27 at 16:13 -0700, Steve Crawford wrote:
> > > It would perhaps be useful to add optional --old-confdir and 
> > > --new-confdir parameters to pg_upgrade. If these parameters are absent
> > > then pg_upgrade would work as it does now and assume that the config 
> > > files are in the datadir. 
> > 
> > It should work the same way the postmaster itself works: If the given
> > directory is not a data directory, look for the postgresql.conf file and
> > look there for the location of the data directory.
> 
> So we need a postmaster switch:
> 
> postmaster --parse-config-and-report=data_directory

Perhaps.  That might have some use for pg_ctl as well.


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

Re: [HACKERS] Removing savepointLevel from TransactionState

[Tom Lane]

Gurjeet Singh  writes:
> I noticed that the savepointLevel member of TransactionStateData struct is
> initialized to 0 from TopTransactionStateData, and never incremented or
> decremented afterwards.

> Since this is a file-local struct I think we can simply get rid of all
> usages of this without any risk.

ISTM you have detected a bug, not just dead code that should be removed.
Surely those tests that throw error on savepointLevel change were
meant to do something important?

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] Removing savepointLevel from TransactionState

[Gurjeet Singh]

On Thu, Sep 29, 2011 at 1:11 AM, Tom Lane  wrote:

> Gurjeet Singh  writes:
> > I noticed that the savepointLevel member of TransactionStateData struct
> is
> > initialized to 0 from TopTransactionStateData, and never incremented or
> > decremented afterwards.
>
> > Since this is a file-local struct I think we can simply get rid of all
> > usages of this without any risk.
>
> ISTM you have detected a bug, not just dead code that should be removed.
> Surely those tests that throw error on savepointLevel change were
> meant to do something important?
>

That's surprising for dead-code removal!

Not sure which failures you're pointing to. `make check` before and after
the patch on master says 'All 126 tests passed.'

Let me know about the failures, and I'll see if I can find the root cause.

Regards,
-- 
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Re: [HACKERS] Removing savepointLevel from TransactionState

[Pavan Deolasee]

On Thu, Sep 29, 2011 at 10:52 AM, Gurjeet Singh  wrote:
> On Thu, Sep 29, 2011 at 1:11 AM, Tom Lane  wrote:
>>
>> Gurjeet Singh  writes:
>> > I noticed that the savepointLevel member of TransactionStateData struct
>> > is
>> > initialized to 0 from TopTransactionStateData, and never incremented or
>> > decremented afterwards.
>>
>> > Since this is a file-local struct I think we can simply get rid of all
>> > usages of this without any risk.
>>
>> ISTM you have detected a bug, not just dead code that should be removed.
>> Surely those tests that throw error on savepointLevel change were
>> meant to do something important?
>
> That's surprising for dead-code removal!
>
> Not sure which failures you're pointing to. `make check` before and after
> the patch on master says 'All 126 tests passed.'
>

I think Tom is talking about the test in the code and not a regression
test itself. Looking at the archives and git log, it seems though that
we never completed this feature and the code was always dead since its
inception.

http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg48321.html

Thanks,
Pavan
-- 
Pavan Deolasee
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