Re: [HACKERS] Declarative partitioning
Hello Amit, In the example >create table part201606week4 partition of parted >for values start (2016, 6, 2) end (2016, 6, 29); seems to be a typo regards Sameer -- View this message in context: http://postgresql.nabble.com/Declarative-partitioning-tp5862462p5903204.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] Support for N synchronous standby servers - take 2
Hello,
Continuing testing:
For pg_syncinfo.conf below an error is thrown.
{
"sync_info":
{
"quorum": 3,
"nodes":
[
{"priority":1,"group":"cluster1"},
"A"
]
},
"groups":
{
"cluster1":["B","C"]
}
}
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
TRAP: FailedAssertion("!(n < list->length)", File: "list.c", Line: 392)
LOG: server process (PID 17764) was terminated by signal 6: Aborted
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2015-09-15 17:15:35
IST
In the scenario here the quorum specified is 3 but there are just 2 nodes,
what should the expected behaviour be?
I feel the json parsing should throw an appropriate error with explanation
as the sync rule does not make sense. The behaviour that the master keeps
waiting for the non existent 3rd quorum node will not be helpful anyway.
regards
Sameer
--
View this message in context:
http://postgresql.nabble.com/Support-for-N-synchronous-standby-servers-take-2-tp5849384p5865954.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] Support for N synchronous standby servers - take 2
Hello, I did apply the patch to HEAD and tried to setup basic async replication.But i got an error. Turned on logging for details below. Unpatched Primary Log LOG: database system was shut down at 2015-09-12 13:41:40 IST LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started Unpatched Standby log LOG: entering standby mode LOG: redo starts at 0/228 LOG: invalid record length at 0/2D0 LOG: started streaming WAL from primary at 0/200 on timeline 1 LOG: consistent recovery state reached at 0/2F8 LOG: database system is ready to accept read only connections Patched Primary log LOG: database system was shut down at 2015-09-12 13:50:17 IST LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started LOG: server process (PID 17317) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2015-09-12 13:50:18 IST FATAL: the database system is in recovery mode LOG: database system was not properly shut down; automatic recovery in progress LOG: invalid record length at 0/398 LOG: redo is not required LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started LOG: server process (PID 17343) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes Patched Standby log LOG: database system was interrupted; last known up at 2015-09-12 13:50:16 IST FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up LOG: entering standby mode LOG: redo starts at 0/228 LOG: invalid record length at 0/2D0 LOG: started streaming WAL from primary at 0/200 on timeline 1 FATAL: could not receive data from WAL stream: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. FATAL: could not connect to the primary server: FATAL: the database system is in recovery mode Not sure if there is something i am missing which causes this. regards Sameer -- View this message in context: http://postgresql.nabble.com/Support-for-N-synchronous-standby-servers-take-2-tp5849384p5865685.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] [PROPOSAL] VACUUM Progress Checker.
Hello, Your current design completely misses the time taken to scan indexes, which is significant. I tried to address this issue in the attached patch. The patch calculates index scan progress by measuring against scanned pages in LVRelStats. It checks for a change current page being scanned and increments the progress counter. When counter reaches scanned pages number in LVRelStats, progress is 100% complete. For now the progress is emitted as a warning (so no config changes needed to see progress) Thoughts? regards Sameer IndexScanProgress.patch http://postgresql.nabble.com/file/n5858109/IndexScanProgress.patch -- View this message in context: http://postgresql.nabble.com/PROPOSAL-VACUUM-Progress-Checker-tp5855849p5858109.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] [PROPOSAL] VACUUM Progress Checker.
Hello, Thank you for suggestion. The design with hooks and a separate view was mainly to keep most of the code outside core as the feature proposed is specific to VACUUM command. Also, having a separate view can give more flexibility in terms of displaying various progress parameters. FWIW ,there was resistance to include columns in pg_stat_activity earlier in the following thread, http://www.postgresql.org/message-id/AANLkTi=TcuMA38oGUKX9p5WVPpY+M3L0XUp7=PLT+LCT@... Perhaps as suggested in the link, the progress could be made available via a function call which does progress calculation on demand. Then we do not need a separate view, or clutter pg_stat_activity, and also has benefit of calculating progress just when it's needed. -- View this message in context: http://postgresql.nabble.com/PROPOSAL-VACUUM-Progress-Checker-tp5855849p5856192.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] [PATCH] two-arg current_setting() with fallback
Hello, Well, speaking of the two-arg form vs alternate name, here's a version of the patch which includes the new behavior Thought i will attempt a review. The patch applies cleanly to latest HEAD. patch -p1 /home/Sameer/Downloads/0001-Add-two-arg-form-of-current_setting-to-optionally-su.patch patching file doc/src/sgml/func.sgml Hunk #1 succeeded at 16216 (offset 1 line). Hunk #2 succeeded at 16255 (offset 1 line). patching file src/backend/utils/misc/guc.c Hunk #1 succeeded at 7693 (offset -3 lines). Hunk #2 succeeded at 8012 (offset -3 lines). patching file src/include/catalog/pg_proc.h Hunk #1 succeeded at 3044 (offset 4 lines). patching file src/include/utils/builtins.h patching file src/include/utils/guc.h patching file src/test/regress/expected/guc.out patching file src/test/regress/sql/guc.sql But i do get error at make make -C catalog schemapg.h make[3]: Entering directory `/home/Sameer/git/latest_postgres/postgres/src/backend/catalog' cd ../../../src/include/catalog '/usr/bin/perl' ./duplicate_oids 3280 make[3]: *** [postgres.bki] Error 1 make[3]: Leaving directory `/home/Sameer/git/latest_postgres/postgres/src/backend/catalog' make[2]: *** [submake-schemapg] Error 2 make[2]: Leaving directory `/home/Sameer/git/latest_postgres/postgres/src/backend' make[1]: *** [all-backend-recurse] Error 2 make[1]: Leaving directory `/home/Sameer/git/latest_postgres/postgres/src' make: *** [all-src-recurse] Error 2 regards Sameer -- View this message in context: http://postgresql.nabble.com/PATCH-two-arg-current-setting-with-fallback-tp5842654p5847904.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] Priority table or Cache table
Hello, I applied the patch to current HEAD. There was one failure (attached), freelist.rej http://postgresql.1045698.n5.nabble.com/file/n5804200/freelist.rej Compiled the provided pgbench.c and added following in .conf shared_buffers = 128MB # min 128kB Shared_buffers=64MB Priority_buffers=128MB I was planning to performance test later hence different values. But while executing pgbench the following assertion occurs LOG: database system is ready to accept connections LOG: autovacuum launcher started TRAP: FailedAssertion(!(strategy_delta = 0), File: bufmgr.c, Line: 1435) LOG: background writer process (PID 10274) was terminated by signal 6: Aborted LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. Is there a way to avoid it? Am i making some mistake? regards Sameer -- View this message in context: http://postgresql.1045698.n5.nabble.com/Priority-table-or-Cache-table-tp5792831p5804200.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] Compression of full-page-writes
Hello, What kind of error did you get at the server crash? Assertion error? If yes, it might be because of the conflict with 4a170ee9e0ebd7021cb1190fabd5b0cbe2effb8e. This commit forbids palloc from being called within a critical section, but the patch does that and then the assertion error happens. That's a bug of the patch. seems to be that STATEMENT: create table test (id integer); TRAP: FailedAssertion(!(CritSectionCount == 0 || (CurrentMemoryContext) == ErrorContext || (MyAuxProcType == CheckpointerProcess)), File: mcxt.c, Line: 670) LOG: server process (PID 29721) was terminated by signal 6: Aborted DETAIL: Failed process was running: drop table test; LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. How do i resolve this? Thank you, Sameer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compression of full-page-writes
Hello, Done. Attached is the updated version of the patch. I was trying to check WAL reduction using this patch on latest available git version of Postgres using JDBC runner with tpcc benchmark. patching_problems.txt http://postgresql.1045698.n5.nabble.com/file/n5803482/patching_problems.txt I did resolve the patching conflicts and then compiled the source, removing couple of compiler errors in process. But the server crashes in the compress mode i.e. the moment any WAL is generated. Works fine in 'on' and 'off' mode. Clearly i must be resolving patch conflicts incorrectly as this patch applied cleanly earlier. Is there a version of the source where i could apply it the patch cleanly? Thank you, Sameer -- View this message in context: http://postgresql.1045698.n5.nabble.com/Compression-of-full-page-writes-tp5769039p5803482.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] Problem with displaying wide tables in psql
On Wed, Dec 11, 2013 at 11:13 PM, Sergey Muraviov sergey.k.murav...@gmail.com wrote: Hi. I've improved the patch. It works in expanded mode when either format option is set to wrapped (\pset format wrapped), or we have no pager, or pager doesn't chop long lines (so you can still use the trick). Target output width is taken from either columns option (\pset columns 70), or environment variable $COLUMNS, or terminal size. And it's also compatible with any border style (\pset border 0|1|2). Here are some examples: postgres=# \x 1 postgres=# \pset format wrapped postgres=# \pset border 0 postgres=# select * from wide_table; * Record 1 value afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df sadfsadfa sd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf * Record 2 value afadsafasd fasdf asdfasd postgres=# \pset border 1 postgres=# \pset columns 70 postgres=# select * from wide_table; -[ RECORD 1 ]- value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa | df sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f | sadf sad fadsf -[ RECORD 2 ]- value | afadsafasd fasdf asdfasd postgres=# \pset border 2 postgres=# \pset columns 60 postgres=# select * from wide_table; +-[ RECORD 1 ]-+ | value | afadsafasd fasdf asdfasd fsad fas df sadf sad f | | | sadf sadf sa df sadfsadfasd fsad fsa df sadf as | | | d fa sfd sadfsadf asdf sad f sadf sad fadsf | +-[ RECORD 2 ]-+ | value | afadsafasd fasdf asdfasd | +---+--+ Regards, Sergey The patch applies and compile cleanly. I tried the following \pset format wrapped \pset columns 70. Not in expanded mode select * from wide_table works fine. select * from pg_stats has problems in viewing. Is it that pg_stats can be viewed easily only in expanded mode i.e. if columns displayed are wrapped then there is no way to view results in non expanded mode? regards Sameer -- Sent 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_stat_statements: calls under-estimation propagation
I've cleaned this up - revision attached - and marked it ready for committer. Thank you for this. I did the basic hygiene test. The patch applies correctly and compiles with no warnings. Did not find anything broken in basic functionality. In the documentation i have a minor suggestion of replacing phrase might judge to be a non-distinct with - may judge to be non- distinct. regards Sameer -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5781577.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
Hello, Please find v10 of patch attached. This patch addresses following review comments 1. Removed errcode and used elogs for error pg_stat_statements schema is not supported by its binary 2. Removed comments and other code formatting not directly relevant to patch functionality 3. changed position of query_id in view to userid,dbid,query_id.. 4 cleaned the patch some more to avoid unnecessary whitespaces, newlines. I assume the usage of PGSS_TUP_LATEST after explanation given. Also the mixing of PG_VERSION_NUM with query_id is ok after after explanation given. regards Sameer pg_stat_statements-identification-v10.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extra functionality to createuser
Hello,
Tried to test this patch. Did the following
1. cloned from https://github.com/samthakur74/postgres
2. Applied patch and make install
3. created rolesapp_readonly_role,app2_writer_role
4. Tried createuser -D -S -l -g app_readonly_role,app2_writer_role
test_user got error: createuser: invalid option -- 'g'
5. Tried createuser -D -S -l --roles
app_readonly_role,app2_writer_role test_user. This does not give
error.
6. Confirmed that test_user is created using \du and it has
postgres=# \du
List of roles
Role name | Attributes |
Member of
---++---
---
Sameer| Superuser, Create role, Create DB, Replication | {}
app2_writer_role | Cannot login | {}
app_readonly_role | Cannot login | {}
my_new_user || {app_reado
nly_role,app2_writer_role}
test_user || {app_reado
nly_role,app2_writer_role}
7. createuser --help does show -g, --roles roles to
associate with this new role
So i think -g option is failing
regards
Sameer
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extra functionality to createuser
1. cloned from https://github.com/samthakur74/postgres Sorry. cloned from https://github.com/postgres/postgres regards Sameer -- Sent 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_stat_statements: calls under-estimation propagation
I took a quick look. Observations:
+ /* Making query ID dependent on PG version */
+ query-queryId |= PG_VERSION_NUM 16;
If you want to do something like this, make the value of
PGSS_FILE_HEADER incorporate (PG_VERSION_NUM / 100) or something.
Why are you doing this?
The thought was queryid should have a different value for the same
query across PG versions, to ensure that clients using
the view,do not assume otherwise.
@@ -128,6 +146,7 @@ typedef struct pgssEntry
pgssHashKey key; /* hash key of entry - MUST BE FIRST */
Counters counters; /* the statistics for this query */
int query_len; /* # of valid bytes in query string */
+ uint32 query_id; /* jumble value for this entry */
query_id is already in key.
Not sure I like the idea of the new enum at all, but in any case you
shouldn't have a PGSS_TUP_LATEST constant - should someone go update
all usage of that constant only when your version isn't the latest?
Like here:
+ if (detected_version = PGSS_TUP_LATEST)
There is #define PGSS_TUP_LATEST PGSS_TUP_V1_2
So if an update has to be done, this is the one place to do it.
I forget why Daniel originally altered the min value of
pg_stat_statements.max to 1 (I just remember that he did), but I don't
think it holds that you should keep it there. Have you considered the
failure modes when it is actually set to 1?
Will set it back to the original value and also test for max value = 1
This is what I call a can't happen error, or a defensive one:
+ else
+ {
+ /*
+ * Couldn't identify the tuple format. Raise error.
+ *
+ * This is an exceptional case that may only happen in bizarre
+ * situations, since it is thought that every released version
+ * of pg_stat_statements has a matching schema.
+ */
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg(pg_stat_statements schema is not supported
+ by its installed binary)));
+ }
I'll generally make these simple elogs(), which are more terse. No one
is going to find all that dressing useful.
Will convert to using elogs
Please take a look at this, for future reference:
https://wiki.postgresql.org/wiki/Creating_Clean_Patches
The whitespace changes are distracting.
Thanks! Still learning the art of clean patch submission.
It probably isn't useful to comment random, unaffected code that isn't
affected by your patch - I don't find this new refactoring useful, and
am surprised to see it in your patch:
+ /* Check header existence and magic number match. */
if (fread(header, sizeof(uint32), 1, file) != 1 ||
- header != PGSS_FILE_HEADER ||
- fread(num, sizeof(int32), 1, file) != 1)
+ header != PGSS_FILE_HEADER)
+ goto error;
+
+ /* Read how many table entries there are. */
+ if (fread(num, sizeof(int32), 1, file) != 1)
goto error;
Did you mean to add all this, or is it left over from Daniel's patch?
I think its a carry over from Daniel's code. I understand the thought.
Will keep patch strictly restricted to functionality implemented
@@ -43,6 +43,7 @@
*/
#include postgres.h
+#include time.h
#include unistd.h
#include access/hash.h
@@ -59,15 +60,18 @@
#include storage/spin.h
#include tcop/utility.h
#include utils/builtins.h
+#include utils/timestamp.h
Final thought: I think the order in the pg_stat_statements view is
wrong. It ought to be like a composite primary key - (userid, dbid,
query_id).
Will make the change.
--
Peter Geoghegan
Thank you for the review
Sameer
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5778472.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
Hello, Please find attached pg_stat_statements-identification-v9.patch. I have tried to address the following review comments 1. Use version PGSS_TUP_V1_2 2.Fixed total time being zero 3. Remove 'session_start' from the view and use point release number to generate queryid 4. Hide only queryid and query text and not all fields from unauthorized user 5. Removed introduced field from view and code as statistics session concept is not being used 6. Removed struct Instrumentation usage 7. Updated sgml to reflect changes made. Removed all references to statistics session, and introduced fields. regards Sameer pg_stat_statements-identification-v9.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
This paragraph reads a bit strange to me: + A statistics session is the time period when statistics are gathered by statistics collector + without being reset. So a statistics session continues across normal shutdowns, + but whenever statistics are reset, like during a crash or upgrade, a new time period + of statistics collection commences i.e. a new statistics session. + The query_id value generation is linked to statistics session to emphasize the fact + that whenever statistics are reset,the query_id for the same queries will also change. time period when? Shouldn't that be time period during which. Also, doesn't a new statistics session start when a stats reset is invoked by the user? The bit after commences appears correct (to me, not a native by any means) but seems also a bit strange. I have tried to rephrase this. Hopefully less confusing A statistics session refers to the time period when statement statistics are gathered by statistics collector. A statistics session persists across normal shutdowns. Whenever statistics are reset like during a crash or upgrade, a new statistics session starts. The query_id value generation is linked to statistics session to emphasize that whenever statistics are reset,the query_id for the same queries will also change. regards Sameer -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5774365.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
Please find patch attached which adds documentation for session_start and introduced fields and corrects documentation for queryid to be query_id. session_start remains in the view as agreed. regards Sameer pg_stat_statements-identification-v8.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
Please find the patch attached Thanks for the patch! Here are the review comments: +OUT session_start timestamptz, +OUT introduced timestamptz, The patch exposes these columns in pg_stat_statements view. These should be documented. Yes, will add to documentation. I don't think that session_start should be exposed in every rows in pg_stat_statements because it's updated only when all statistics are reset, i.e., session_start of all entries in pg_stat_statements indicate the same. I understand. Will remove session_start from view and expose it via a function pg_stat_statements_current_session_start() ? +OUT query_id int8, query_id or queryid? I like the latter. Also the document uses the latter. Will change to queryid Thank you Sameer -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5773448.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
On Sat, Oct 5, 2013 at 1:38 PM, Daniel Farina dan...@heroku.com wrote: On Fri, Oct 4, 2013 at 7:22 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Oct 3, 2013 at 5:11 PM, Sameer Thakur samthaku...@gmail.com wrote: On Wed, Oct 2, 2013 at 6:40 PM, Sameer Thakur samthaku...@gmail.com wrote: Looks pretty good. Do you want to package up the patch with your change and do the honors and re-submit it? Thanks for helping out so much! Sure, will do. Need to add a bit of documentation explaining statistics session as well. I did some more basic testing around pg_stat_statements.max, now that we have clarity from Peter about its value being legitimate below 100. Seems to work fine, with pg_stat_statements =4 the max unique queries in the view are 4. On the 5th query the view holds just the latest unique query discarding the previous 4. Fujii had reported a segmentation fault in this scenario. Thank you for the patch Please find the patch attached Thanks for the patch! Here are the review comments: +OUT session_start timestamptz, +OUT introduced timestamptz, The patch exposes these columns in pg_stat_statements view. These should be documented. I don't think that session_start should be exposed in every rows in pg_stat_statements because it's updated only when all statistics are reset, i.e., session_start of all entries in pg_stat_statements indicate the same. Dunno. I agree it'd be less query traffic and noise. Maybe hidden behind a UDF? I thought stats_reset on pg_database may be prior art, but realized that the statistics there differ depending on stats resets per database (maybe a name change of 'session' to 'stats_reset' would be useful to avoid too much in-cohesion, though). I didn't want to bloat the taxonomy of exposed API/symbols too much for pg_stat_statements, but perhaps in this instance it is reasonable. Also, isn't the interlock with the result set is perhaps more precise/fine-grained with the current solution? Yet, that's awfully corner-casey. I'm on the fence because the simplicity and precision of the current regime for aggregation tools is nice, but avoiding the noise for inspecting humans in the common case is also nice. I don't see a reason right now to go strongly either way, so if you feel moderately strongly that the repetitive column should be stripped then I am happy to relent there and help out. Let me know of your detailed thoughts (or modify the patch) with your idea. Thinking a bit more, if its just a question of a repeating value we have the same situation for userid and dbid. They would be the same for a user across multiple queries in same statistics session. So userid,dbid and session_start do repeat across rows. Not sure why treatment for session_start be different. I also checked pg_stat_plans @ https://github.com/2ndQuadrant/pg_stat_plans and did not see any special treatment given for a particular field in terms of access i.e. the granularity of api wrt pg_stat_statements has been maintained. regards Sameer -- Sent 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_stat_statements: calls under-estimation propagation
On Wed, Oct 2, 2013 at 6:40 PM, Sameer Thakur samthaku...@gmail.com wrote: Looks pretty good. Do you want to package up the patch with your change and do the honors and re-submit it? Thanks for helping out so much! Sure, will do. Need to add a bit of documentation explaining statistics session as well. I did some more basic testing around pg_stat_statements.max, now that we have clarity from Peter about its value being legitimate below 100. Seems to work fine, with pg_stat_statements =4 the max unique queries in the view are 4. On the 5th query the view holds just the latest unique query discarding the previous 4. Fujii had reported a segmentation fault in this scenario. Thank you for the patch Please find the patch attached regards Sameer pg_stat_statements-identification-v7.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
Looks pretty good. Do you want to package up the patch with your change and do the honors and re-submit it? Thanks for helping out so much! Sure, will do. Need to add a bit of documentation explaining statistics session as well. I did some more basic testing around pg_stat_statements.max, now that we have clarity from Peter about its value being legitimate below 100. Seems to work fine, with pg_stat_statements =4 the max unique queries in the view are 4. On the 5th query the view holds just the latest unique query discarding the previous 4. Fujii had reported a segmentation fault in this scenario. Thank you for the patch regards Sameer -- Sent 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_stat_statements: calls under-estimation propagation
On Tue, Oct 1, 2013 at 12:48 AM, Daniel Farina-5 [via PostgreSQL] ml-node+s1045698n5772887...@n5.nabble.com wrote: On Sep 30, 2013 4:39 AM, Sameer Thakur [hidden email] wrote: Also, for onlookers, I have changed this patch around to do the date-oriented stuff but want to look it over before stapling it up and sending it. If one cannot wait, one can look at https://github.com/fdr/postgres/tree/queryid. The squashed-version of that history contains a reasonable patch I think, but a re-read often finds something for me and I've only just completed it yesterday. I did the following 1. Forked from fdr/postgres 2. cloned branch queryid 3. squashed 22899c802571a57cfaf0df38e6c5c366b5430c74 d813096e29049667151a49fc5e5cf3d6bbe55702 picked be2671a4a6aa355c5e8ae646210e6c8e0b84ecb5 4. usual make/make install/create extension pg_stat_statements. (pg_stat_statements.max=100). 5. select * from pg_stat_statements_reset(), select * from pgbench_tellers. result below: userid | dbid | session_start |introduced | query | query_id | calls | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | t emp_blks_read | temp_blks_written | blk_read_time | blk_write_time +---+--+---+---+-+---++ --+-+--+-+-++-+++-- --+---+---+ 10 | 12900 | 2013-09-30 16:55:22.285113+05:30 | 1970-01-01 05:30:00+05:30 | select * from pg_stat_statements_reset(); | 2531907647060518039 | 1 | 0 | 1 | 0 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 10 | 12900 | 2013-09-30 16:55:22.285113+05:30 | 1970-01-01 05:30:00+05:30 | select * from pgbench_tellers ; | 7580333025384382649 | 1 | 0 | 10 | 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (2 rows) I understand session_start and verified that it changes with each database restart to reflect current time. It should only restart when the statistics file cannot be loaded. I am not sure why introduced keeps showing the same 1970-01-01 05:30:00+05:30 value. I thought it reflected the (most recent) time query statements statistics is added to hashtable. Is this a bug? Will continue to test and try and understand the code. Yes, a bug. There are a few calls to pgss store and I must be submitting a zero value for the introduction time in one of those cases. Heh, I thought that was fixed, but maybe I broke something. Like I said; preliminary. At the earliest I can look at this Wednesday, but feel free to amend and resubmit including my changes if you feel inclined and get to it first. In pg_stat_statements.c line 1440 changed if (instr == NULL) to if (instr == NULL || INSTR_TIME_IS_ZERO(instr-starttime)) This seemed to do the trick. I will continue to test some more. regards Sameer -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5772930.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
On Tue, Oct 1, 2013 at 12:48 AM, Daniel Farina-5 [via PostgreSQL] ml-node+s1045698n5772887...@n5.nabble.com wrote: On Sep 30, 2013 4:39 AM, Sameer Thakur [hidden email] wrote: Also, for onlookers, I have changed this patch around to do the date-oriented stuff but want to look it over before stapling it up and sending it. If one cannot wait, one can look at https://github.com/fdr/postgres/tree/queryid. The squashed-version of that history contains a reasonable patch I think, but a re-read often finds something for me and I've only just completed it yesterday. I did the following 1. Forked from fdr/postgres 2. cloned branch queryid 3. squashed 22899c802571a57cfaf0df38e6c5c366b5430c74 d813096e29049667151a49fc5e5cf3d6bbe55702 picked be2671a4a6aa355c5e8ae646210e6c8e0b84ecb5 4. usual make/make install/create extension pg_stat_statements. (pg_stat_statements.max=100). 5. select * from pg_stat_statements_reset(), select * from pgbench_tellers. result below: userid | dbid | session_start |introduced | query | query_id | calls | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | t emp_blks_read | temp_blks_written | blk_read_time | blk_write_time +---+--+---+---+-+---++ --+-+--+-+-++-+++-- --+---+---+ 10 | 12900 | 2013-09-30 16:55:22.285113+05:30 | 1970-01-01 05:30:00+05:30 | select * from pg_stat_statements_reset(); | 2531907647060518039 | 1 | 0 | 1 | 0 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 10 | 12900 | 2013-09-30 16:55:22.285113+05:30 | 1970-01-01 05:30:00+05:30 | select * from pgbench_tellers ; | 7580333025384382649 | 1 | 0 | 10 | 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (2 rows) I understand session_start and verified that it changes with each database restart to reflect current time. It should only restart when the statistics file cannot be loaded. This seems to work fine. 1. Started the instance 2. Executed pg_stat_statements_reset(), select * from pgbench_history,select* from pgbench_tellers. Got the following in pg_stat_statements view userid | dbid | session_start | introduced| query | query_id | calls | tota l_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_wri tten | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time +---+--+--+---+--+---+- ---+--+-+--+-+-++-++--- -++---+---+ 10 | 12900 | 2013-10-01 17:43:26.667074+05:30 | 2013-10-01 17:43:43.724301+05:30 | select * from pgbench_history;| -165801328395488047 | 1 | 0 |0 | 0 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 10 | 12900 | 2013-10-01 17:43:26.667074+05:30 | 2013-10-01 17:43:37.379785+05:30 | select * from pgbench_tellers;| 8376871363863945311 | 1 | 0 | 10 | 0 |1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 10 | 12900 | 2013-10-01 17:43:26.667074+05:30 | 2013-10-01 17:43:26.667178+05:30 | select * from pg_stat_statements_reset(); | -1061018443194138344 | 1 | 0 |1 | 0 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (3 rows) Then restarted the server and saw pg_stat_statements view again. userid | dbid | session_start
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
Also, for onlookers, I have changed this patch around to do the date-oriented stuff but want to look it over before stapling it up and sending it. If one cannot wait, one can look at https://github.com/fdr/postgres/tree/queryid. The squashed-version of that history contains a reasonable patch I think, but a re-read often finds something for me and I've only just completed it yesterday. I did the following 1. Forked from fdr/postgres 2. cloned branch queryid 3. squashed 22899c802571a57cfaf0df38e6c5c366b5430c74 d813096e29049667151a49fc5e5cf3d6bbe55702 picked be2671a4a6aa355c5e8ae646210e6c8e0b84ecb5 4. usual make/make install/create extension pg_stat_statements. (pg_stat_statements.max=100). 5. select * from pg_stat_statements_reset(), select * from pgbench_tellers. result below: userid | dbid | session_start |introduced | query | query_id | calls | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | t emp_blks_read | temp_blks_written | blk_read_time | blk_write_time +---+--+---+---+-+---++ --+-+--+-+-++-+++-- --+---+---+ 10 | 12900 | 2013-09-30 16:55:22.285113+05:30 | 1970-01-01 05:30:00+05:30 | select * from pg_stat_statements_reset(); | 2531907647060518039 | 1 | 0 | 1 | 0 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 10 | 12900 | 2013-09-30 16:55:22.285113+05:30 | 1970-01-01 05:30:00+05:30 | select * from pgbench_tellers ; | 7580333025384382649 | 1 | 0 | 10 | 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (2 rows) I understand session_start and verified that it changes with each database restart to reflect current time. I am not sure why introduced keeps showing the same 1970-01-01 05:30:00+05:30 value. I thought it reflected the (most recent) time query statements statistics is added to hashtable. Is this a bug? Will continue to test and try and understand the code. regards Sameer -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5772841.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
On Mon, Sep 23, 2013 at 1:26 PM, samthakur74 samthaku...@gmail.com wrote: I forgot about removal of the relevant SGML, amended here in v6. Thank you for this! i did a quick test with following steps: 1. Applied v6 patch 2. make and make install on pg_stat_statements; 3. Restarted Postgres with pg_stat_statements loaded with pg_stat_statements.max = 4 4. Dropped and created extension pg_stat_statements. Executed following: select * from pg_stat_statements_reset(); select * from pgbench_branches ; select * from pgbench_history ; select * from pgbench_tellers ; select * from pgbench_accounts; I expected 4 rows in pg_stat_statements view for each of 4 queries above. But i saw just 2 rows. select * from pg_stat_statements; userid | dbid | stat_session_id | query | quer y_id | calls | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | l ocal_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | bl k_read_time | blk_write_time +---+-+-+--- ---+---+++-+--+- +-++-+-- --+++---+--- + 10 | 12900 |21595345 | select * from pgbench_accounts; | -803800319 3522943111 | 1 |108.176 | 10 |1640 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 10 | 12900 |21595345 | select * from pgbench_tellers ; | -149722997 7134331757 | 1 | 0.227 | 10 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (2 rows) Am i doing something wrong? Yes i was. Just saw a warning when pg_stat_statements is loaded that valid values for pg_stat_statements.max is between 100 and 2147483647. Not sure why though. regards Sameer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for fail-back without fresh backup
Attached patch combines documentation patch and source-code patch. I have had a stab at reviewing the documentation. Have a look. --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1749,6 +1749,50 @@ include 'filename' /listitem /varlistentry + varlistentry id=guc-synchronous-transfer xreflabel=synchronous_transfer + termvarnamesynchronous_transfer/varname (typeenum/type)/term + indexterm + primaryvarnamesynchronous_transfer/ configuration parameter/primary + /indexterm + listitem + para +This parameter controls the synchronous nature of WAL transfer and +maintains file system level consistency between master server and +standby server. It specifies whether master server will wait for file +system level change (for example : modifying data page) before +the corresponding WAL records are replicated to the standby server. + /para + para +Valid values are literalcommit/, literaldata_flush/ and +literalall/. The default value is literalcommit/, meaning +that master will only wait for transaction commits, this is equivalent +to turning off literalsynchronous_transfer/ parameter and standby +server will behave as a quotesynchronous standby / in +Streaming Replication. For value literaldata_flush/, master will +wait only for data page modifications but not for transaction +commits, hence the standby server will act as quoteasynchronous +failback safe standby/. For value literal all/, master will wait +for data page modifications as well as for transaction commits and +resultant standby server will act as quotesynchronous failback safe +standby/.The wait is on background activities and hence will not create performance overhead. + To configure synchronous failback safe standby +xref linkend=guc-synchronous-standby-names should be set. + /para + /listitem + /varlistentry @@ -2258,14 +2302,25 @@ include 'filename'/indexterm listitem para -Specifies a comma-separated list of standby names that can support -firsttermsynchronous replication/, as described in -xref linkend=synchronous-replication. -At any one time there will be at most one active synchronous standby; -transactions waiting for commit will be allowed to proceed after -this standby server confirms receipt of their data. -The synchronous standby will be the first standby named in this list -that is both currently connected and streaming data in real-time +Specifies a comma-separated list of standby names. If this parameter +is set then standby will behave as synchronous standby in replication, +as described in xref linkend=synchronous-replication or synchronous +failback safe standby, as described in xref linkend=failback-safe. +At any time there will be at most one active standby; when standby is +synchronous standby in replication, transactions waiting for commit +will be allowed to proceed after this standby server confirms receipt +of their data. But when standby is synchronous failback safe standby +data page modifications as well as transaction commits will be allowed +to proceed only after this standby server confirms receipt of their data. +If this parameter is set to empty value and +xref linkend=guc-synchronous-transfer is set to literaldata_flush/ +then standby is called as asynchronous failback safe standby and only +data page modifications will wait before corresponding WAL record is +replicated to standby. + /para + para +Synchronous standby in replication will be the first standby named in +this list that is both currently connected and streaming data in real-time (as shown by a state of literalstreaming/literal in the link linkend=monitoring-stats-views-table literalpg_stat_replication//link view). --- a/doc/src/sgml/high-availability.sgml +++ b/doc/src/sgml/high-availability.sgml + + sect2 id=failback-safe + titleSetting up failback safe standby/title + + indexterm zone=high-availability + primarySetting up failback safe standby/primary + /indexterm + + para + PostgreSQL streaming replication offers durability, but if the master crashes and +a particular WAL record is unable to reach to standby server, then that +WAL record is present on master server but not on standby server. +In such a case master is ahead of standby server in term of WAL records and data in database. +This leads to file-system level inconsistency between master and standby server. +For example a heap page update on the master might not have been reflected on standby when
Re: [HACKERS] pg_stat_statements: calls under-estimation propagation
You seem to have forgotten to include the pg_stat_statements--1.2.sql and pg_stat_statements--1.1--1.2.sql in the patch. Sorry again. Please find updated patch attached. I did not add pg_stat_statements--1.2.sql. I have added that now and updated the patch again. The patch attached should contain following file changes patching file contrib/pg_stat_statements/Makefile patching file contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql patching file contrib/pg_stat_statements/pg_stat_statements--1.2.sql patching file contrib/pg_stat_statements/pg_stat_statements.c patching file contrib/pg_stat_statements/pg_stat_statements.control patching file doc/src/sgml/pgstatstatements.sgml regards Sameer pg_stat_statements-identification-v4.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Extending pg_stat_statements to expose queryid
Hello All, I am trying to revive the discussion about exposing queryid in pg_stat_statements. I did find the same request posted on hackers @ http://www.postgresql.org/message-id/CABUevExLnb6xJwS=8rTaLOfDOS-tFm09==z2m_vz5hhfkgw...@mail.gmail.com and http://www.postgresql.org/message-id/cacn56+nlmtwhg8eqqqnyzqe2q0negjokmgfiusk_aohw627...@mail.gmail.com From the discussions I concluded 1. The main use case for exposing queryid, is it being a better substitute to hashing the query text of a pg_stat_statements snapshot, to make a candidate key. Problems occur when hash value should be different even if query text is same. For example when a table referred in a query is dropped and recreated or when the query text is same on different schemas and schema name is not included in query text. 2. Exposing queryid was proposed earlier but was not accepted. The main reason was that queryid could be unstable as well. Since queryid was derived from hashing query tree and query tree could undergo changes between minor PostgreSQL releases, meant the queryid for same query could be different between releases, resulting in incorrect statement statistics collection. 3. Another problem is to distinguish between queries whose statistics are continuously maintained and queries which are intermittent, whose statistics might be silent reset, without the reporting tool being wiser. 4. A solution to avoid misrepresentation of intermittent queries as consistent queries would be to assign a unique number to each new row and once that row is discarded, the unique number cannot be reused. The drawbacks here is possible collision of unique values generated. 5. A patch implementing solution for identifying intermittent query is @ https://github.com/fdr/postgres/branches/error-prop-pg_stat_statements-v2. The solution avoids using a counter, and achieves the same result by the property that intermittent queries accumulate errors due to eviction from hashtable while consistent queries do not. Error accumulation would be the parameter by which a reporting tool can figure out if there was eviction of queries between snapshots. 6. To address the problem of unstable queryid generated from query tree, it was proposed to eliminate any possible misunderstanding that queryid will remain the same between releases, by xoring the hash from query tree with statistics sessionid. This also helps in all cases where the statistics file is reset like crash recovery,recovery mode, ensuring a new hash value for reset statistics. To avoid increasing the chance of collision, a longer session key and padding the queryid can be done to complete the XOR. Implementation of this is @ https://github.com/fdr/postgres/branches/pg_stat_statements-identification-v3 7. The patch pg_stat_statements-identification-v3 was returned with feedback for more documentation in commitfest 2013-01. Questions: 1. Is there a plan to re-introduce this patch? The code seems to be documented. 2. There was mention of further testing of error propagation using hooks. Could this be elaborated? 3. There was a use case that exposing queryid could be used to aggregate statistics across WAL based replication clusters. But now that queryid is derived from statistics session id, which is randomly generated, this use case is still not addressed. Is this correct? Regards Sameer
Re: [HACKERS] Detach/attach table and index data files from one cluster to another
On Fri, Apr 12, 2013 at 9:52 PM, Andres Freund and...@2ndquadrant.comwrote: On 2013-04-12 12:14:24 -0400, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 04/12/2013 10:15 AM, Tom Lane wrote: There's 0 chance of making that work, because the two databases wouldn't have the same notions of committed XIDs. Yeah. Trying to think way outside the box, could we invent some sort of fixup mechanism that could be applied to adopted files? Well, it wouldn't be that hard to replace XIDs with FrozenXID or InvalidXID as appropriate, if you had access to the source database's clog while you did the copying. It just wouldn't be very fast. I think if one goes over the heap and hint bits everything (so the item pointers don't have to be immediately rewritten), freeze everything and such it should be doable at about disk speed unless you have a really fast disk subsystem. But it still is fairly complicated and I doubt its really necessary. I suppose it would still be faster than a COPY transfer, but I'm not sure it'd be enough faster to justify the work and the additional portability hits you'd be taking. Using binary copy might already give quite a speedup, Sameer, did you try that? No we have not so far, was soliciting feedback first from the hackers and possibly implement as a contrib module. Also i did misread the earlier post on the subject. Also, do you really need parts of a cluster or would a base backup of the whole cluster do the trick? We were looking at parts of cluster as an faster alternative to pg_dump and restore -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
[HACKERS] Detach/attach table and index data files from one cluster to another
Hello, The current process of transferring data files from one cluster to another by using pg_dump and pg_restore is time consuming. The proposed tool tries to make migration faster for tables and indices only by copying their binary data files. This is like pg_upgrade but used for migration of table and indices * *The discussion here @ http://www.postgresql.org/message-id/caa-alv5cqf09zvfrcb1xxuqlsp-oux0s_hq6ryscd6ctami...@mail.gmail.com speaks of possibility detaching/attaching databases as an alternative to dump/restore. But the process of freezing XID’s and zeroing out LSN’s make the solution equally time consuming if not more. But if we consider just tables and indexes to be detached/reattached, would this be a viable alternative to dump and restore of tables? The same discussion indicates it could be done but is more complicated as one has to deal with system catalogs of the newly mounted table and map old OID’s to new ones. This is required to ensure consistency in roles, and objects owned by those roles. We would also need to ensure LSN values of the reattached pages are less than the current WAL endpoint in receiver. Are there any more issues we need to be aware of? regards Sameer
Re: [HACKERS] Inconsistent DB data in Streaming Replication
Hello, The only potential use case for this that I can see, would be for system maintenance and a controlled failover. I agree: that's a major PITA when doing DR testing, but I personally don't think this is the way to fix that particular edge case. This is the use case we are trying to address (I work with Samrat). We were wondering why this may not be a good fix? regards Sameer On Thu, Apr 11, 2013 at 3:52 PM, Ants Aasma a...@cybertec.at wrote: On Thu, Apr 11, 2013 at 10:09 AM, Amit Kapila amit.kap...@huawei.com wrote: Consider the case old-master crashed during flushing the data page, now you would need full page image from new-master. It might so happen that in new-master Checkpoint would have purged (reused) the log file's from that time line, in that case it will be difficult to get the full page image, user can refer WAL archive for that, but I think it will not be straight forward. Using a WAL archive is standard procedure when you do not wish to take new base backups all the time. This already works now, when archive recovery is set up the WAL will be automatically fetched from the archive. One more point, what will be the new behavior when there are 2 transactions one has synchronous_commit =off and other with on? Exactly the same as now - waiting for commit record replication when reporting commit success to the client and waiting for WAL replication before writing a data page are orthogonal features. Regards, Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
