Re: [HACKERS] Updated version of pg_receivexlog
On Tue, Aug 16, 2011 at 9:32 AM, Magnus Hagander mag...@hagander.net 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?) 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 haven't read all the code in the detail but seems fine to me in other things: do we need to include src/bin/pg_basebackup/.gitignore in the patch? -- 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] synchronized snapshots
Hi Joachim, On 14/09/2011 05:37, Joachim Wieland wrote: Here is a new version of this patch 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. I can make a patched backend die with an assertion failure by trying to export a snapshot after rolling back a transaction which exported a snapshot. Seems like no cleanup is done at transaction abort. I think that trying to import a snapshot that doesn't exist deserves a better error message. There's currently no way for the user to know that the snapshot didn't exist, other than looking at the SQLSTATE (22023), and even that doesn't tell me a whole lot without looking at the manual. Finally, the comment in ImportSnapshot() still mentions the old syntax. Other than these four problems, the patch looks good. -- 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] Updated version of pg_receivexlog
On Wed, Sep 28, 2011 at 1:38 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Tue, Aug 16, 2011 at 9:32 AM, Magnus Hagander mag...@hagander.net 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
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
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
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?
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.
Background
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.
Question
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 wrote:
Tom Lane wrote:
Bruce Momjian br...@momjian.us 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 br...@momjian.ushttp://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
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 br...@momjian.ushttp://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
Hi Marko, On Wed, Sep 28, 2011 at 2:29 AM, Marko Tiikkaja marko.tiikk...@2ndquadrant.com 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
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 pete...@gmx.net 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
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
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Feature proposal: www_fdw
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 da...@fetter.org 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
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
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
On Wed, Sep 28, 2011 at 7:17 PM, David Fetter da...@fetter.org 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 da...@fetter.org 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
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
inline On Wed, Sep 28, 2011 at 7:21 PM, Andrew Dunstan and...@dunslane.net 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
On 09/28/2011 11:41 AM, Alexander Soudakov wrote: On Wed, Sep 28, 2011 at 7:17 PM, David Fetterda...@fetter.org 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 http://pgxn.org/. 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
Andrew Dunstan and...@dunslane.net 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 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 br...@momjian.ushttp://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.
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.
On Tue, Sep 27, 2011 at 7:57 PM, Will Leinweber w...@heroku.com 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.
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.
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
Gurjeet Singh singh.gurj...@gmail.com wrote: Will Leinweber w...@heroku.com 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
On Wed, Sep 28, 2011 at 08:38, Jaime Casanova ja...@2ndquadrant.com wrote: On Tue, Aug 16, 2011 at 9:32 AM, Magnus Hagander mag...@hagander.net 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.
Alvaro Herrera alvhe...@commandprompt.com 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
On Wed, Sep 28, 2011 at 09:30, Jaime Casanova ja...@2ndquadrant.com wrote: On Wed, Sep 28, 2011 at 1:38 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Tue, Aug 16, 2011 at 9:32 AM, Magnus Hagander mag...@hagander.net 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. !ENTITY pgCtl SYSTEM pg_ctl-ref.sgml !ENTITY pgDump SYSTEM pg_dump.sgml !ENTITY pgDumpall SYSTEM pg_dumpall.sgml +!ENTITY pgReceivexlog SYSTEM pg_receivexlog.sgml !ENTITY pgResetxlogSYSTEM pg_resetxlog.sgml !ENTITY pgRestore SYSTEM pg_restore.sgml !ENTITY postgres SYSTEM postgres-ref.sgml 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.
On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com 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.
On Wed, Sep 28, 2011 at 02:25:44PM -0400, Gurjeet Singh wrote: On Wed, Sep 28, 2011 at 1:51 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com 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 da...@fetter.org 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.
* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote: Alvaro Herrera alvhe...@commandprompt.com 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.
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review of VS 2010 support patches
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 productnameWindows/productname. The simplest way to build with Microsoft tools is to install a supported version of the ! productnameMicrosoft Platform SDK/productname and use the included compiler. It is also possible to build with the full ! productnameMicrosoft Visual C++ 2005 or 2008/productname. In some cases ! that requires the installation of the productnamePlatform SDK/productname in addition to the compiler. /para --- 20,29 There are several different ways of building PostgreSQL on productnameWindows/productname. The simplest way to build with Microsoft tools is to install a supported version of the ! productnameMicrosoft Windows SDK/productname and use the included compiler. It is also possible to build with the full ! productnameMicrosoft Visual C++ 2005, 2008 or 2010/productname. In some cases ! that requires the installation of the productnameWindows SDK/productname in addition to the compiler. /para *** *** 69,100 sect1 id=install-windows-full titleBuilding with productnameVisual C++/productname or the ! productnamePlatform SDK/productname/title para PostgreSQL can be built using the Visual C++ compiler suite from Microsoft. These compilers can be either from productnameVisual Studio/productname, productnameVisual Studio Express/productname or some versions of the ! productnamePlatform SDK/productname. If you do not already have a productnameVisual Studio/productname environment set up, the easiest ! way us to use the compilers in the productnamePlatform SDK/productname, which is a free download from Microsoft. /para para ! PostgreSQL supports the compilers from ! productnameVisual Studio 2005/productname and ! productnameVisual Studio 2008/productname. When using the Platform SDK ! only, or when building for 64-bit Windows, only ! productnameVisual Studio 2008/productname is supported. ! productnameVisual Studio 2010/productname is not yet supported. ! /para ! ! para ! When building using the productnamePlatform SDK/productname, 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 productnameVisual Studio 2010/productname. /para para --- 69,94 sect1 id=install-windows-full titleBuilding with productnameVisual C++/productname or the ! productnameMicrosoft Windows SDK/productname/title para PostgreSQL can be built using the Visual C++ compiler suite from Microsoft. These compilers can be either from productnameVisual Studio/productname, productnameVisual Studio Express/productname or some versions of the ! productnameMicrosoft Windows SDK/productname. If you do not already have a productnameVisual Studio/productname environment set up, the easiest ! way is to use the compilers in the productnameWindows SDK/productname, which is a free download from Microsoft. /para para ! PostgreSQL is known to support compilation using the compilers shipped with ! productnameVisual Studio 2005/productname to ! productnameVisual Studio 2010/productname (including Express editions), ! as well as standalone Windows SDK releases 6.0 to 7.1. ! 64-bit PostgreSQL builds are only supported with ! productnameMicrosoft Windows SDK/productname version 6.0a and above or ! productnameVisual Studio 2008/productname and above. /para para *** *** 104,114 productnameCygwin/productname present in your system PATH. Also, make sure you have all the required Visual C++ tools available in the PATH. In productnameVisual Studio/productname, start the ! applicationVisual Studio Command Prompt/application. In the ! productnamePlatform SDK/productname, start the ! applicationCMD shell/application 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 filenamesrc\tools\msvc/filename directory. /para --- 98,110 productnameCygwin/productname present in your system PATH. Also, make sure
Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
Alvaro Herrera alvhe...@commandprompt.com 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?
2011/9/28 MauMau maumau...@gmail.com:
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.
Background
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.
Question
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
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 and...@dunslane.net 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
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
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
Florian Pflug f...@phlo.org 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=enie=UTF8hq=hnear=Madison,+Dane,+Wisconsinll=43.074684,-89.38188spn=0.003006,0.00383t=hz=18layer=ccbll=43.07468,-89.381742panoid=LhJ-PFHVzxRguJ6h616mmQcbp=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.
On Wed, Sep 28, 2011 at 1:02 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com 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
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 21 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 br...@momjian.ushttp://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
On Wed, Sep 28, 2011 at 12:50 PM, Magnus Hagander mag...@hagander.net 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
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] feature request: auto savepoint for interactive psql when in transaction.
On Wed, Sep 28, 2011 at 10:39 AM, Marko Tiikkaja marko.tiikk...@2ndquadrant.com 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 wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us 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 br...@momjian.ushttp://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
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 pete...@gmx.net 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 br...@momjian.ushttp://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
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 br...@momjian.ushttp://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
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
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
Gurjeet Singh singh.gurj...@gmail.com 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
On Thu, Sep 29, 2011 at 1:11 AM, Tom Lane t...@sss.pgh.pa.us wrote: Gurjeet Singh singh.gurj...@gmail.com 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
On Thu, Sep 29, 2011 at 10:52 AM, Gurjeet Singh singh.gurj...@gmail.com wrote: On Thu, Sep 29, 2011 at 1:11 AM, Tom Lane t...@sss.pgh.pa.us wrote: Gurjeet Singh singh.gurj...@gmail.com 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
