[HACKERS] Patch for checking file parameters to psql before password prompt
Patch for the changes discussed in http://archives.postgresql.org/pgsql-hackers/2010-10/msg00919.php attached (eventually ...) In summary: If the input file (-f) doesn't exist or the ouput or log files (-o and -l) can't be created psql exits before prompting for a password. Regards, Alastair. psql_check_file_params_before_login.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Patch to fix a crash of psql
I confirmed the problem. Also I confirmed your patch fixes the problem. In addition to this, all the tests in test/mb and test/regress are passed. Fix committed as you proposed(without any message I proposed). Thanks. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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: separate databases for contrib module testing
Andrew Dunstan and...@dunslane.net writes: I'd like to change the way we set the CONTRIB_TESTDB name for contrib modules. so that each module doesn't wipe out the previous module's test db. Personally I always thought that was a feature not a bug. If we give each one its own DB, there will be a couple of dozen databases cluttering the installation at the end of make installcheck, and no convenient way to get rid of them. Moreover, what I think you've got in mind doesn't work in the make check case anyway --- you'd have little alternative but to test upgrading each one separately. 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] autovacuum truncate exclusive lock round two
Attached is a new patch that addresses most of the points raised in
discussion before.
1) Most of the configuration variables are derived from deadlock_timeout
now. The check for conflicting lock request interval is
deadlock_timeout/10, clamped to 10ms. The try to acquire exclusive
lock interval is deadlock_timeout/20, also clamped to 10ms. The only
GUC variable remaining is autovacuum_truncate_lock_try=2000ms with a
range from 0 (just try once) to 2ms.
I'd like to point out that this is a significant change in functionality
as without the config option for the check interval, there is no longer
any possibility to disable the call to LockHasWaiters() and return to
the original (deadlock code kills autovacuum) behavior.
2) The partition lock in LockHasWaiters() was lowered to LW_SHARED. The
LW_EXCLUSIVE was indeed a copy/paste result.
3) The instr_time handling was simplified as suggested.
4) Lower case TRUE/FALSE.
I did not touch the part about suppressing the stats and the ANALYZE
step of auto vacuum+analyze. The situation is no different today. When
the deadlock code kills autovacuum, stats aren't updated either. And
this patch is meant to cause autovacuum to finish the truncate in a few
minutes or hours, so that the situation fixes itself.
Jan
--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index c9253a9..e8f88ad 100644
*** a/src/backend/commands/vacuumlazy.c
--- b/src/backend/commands/vacuumlazy.c
***
*** 52,62
--- 52,64
#include storage/bufmgr.h
#include storage/freespace.h
#include storage/lmgr.h
+ #include storage/proc.h
#include utils/lsyscache.h
#include utils/memutils.h
#include utils/pg_rusage.h
#include utils/timestamp.h
#include utils/tqual.h
+ #include portability/instr_time.h
/*
*** typedef struct LVRelStats
*** 103,108
--- 105,111
ItemPointer dead_tuples; /* array of ItemPointerData */
int num_index_scans;
TransactionId latestRemovedXid;
+ bool lock_waiter_detected;
} LVRelStats;
*** static TransactionId FreezeLimit;
*** 114,119
--- 117,126
static BufferAccessStrategy vac_strategy;
+ static int autovacuum_truncate_lock_check;
+ static int autovacuum_truncate_lock_retry;
+ static int autovacuum_truncate_lock_wait;
+
/* non-export function prototypes */
static void lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
*** lazy_vacuum_rel(Relation onerel, VacuumS
*** 193,198
--- 200,207
vacrelstats-old_rel_pages = onerel-rd_rel-relpages;
vacrelstats-old_rel_tuples = onerel-rd_rel-reltuples;
vacrelstats-num_index_scans = 0;
+ vacrelstats-pages_removed = 0;
+ vacrelstats-lock_waiter_detected = false;
/* Open all indexes of the relation */
vac_open_indexes(onerel, RowExclusiveLock, nindexes, Irel);
*** lazy_vacuum_rel(Relation onerel, VacuumS
*** 259,268
vacrelstats-hasindex,
new_frozen_xid);
! /* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel),
onerel-rd_rel-relisshared,
new_rel_tuples);
/* and log the action if appropriate */
if (IsAutoVacuumWorkerProcess() Log_autovacuum_min_duration = 0)
--- 268,285
vacrelstats-hasindex,
new_frozen_xid);
! /*
! * report results to the stats collector, too.
! * An early terminated lazy_truncate_heap attempt
! * suppresses the message and also cancels the
! * execution of ANALYZE, if that was ordered.
! */
! if (!vacrelstats-lock_waiter_detected)
! pgstat_report_vacuum(RelationGetRelid(onerel),
onerel-rd_rel-relisshared,
new_rel_tuples);
+ else
+ vacstmt-options = ~VACOPT_ANALYZE;
/* and log the action if appropriate */
if (IsAutoVacuumWorkerProcess() Log_autovacuum_min_duration = 0)
*** lazy_truncate_heap(Relation onerel, LVRe
*** 1255,1334
BlockNumber old_rel_pages = vacrelstats-rel_pages;
BlockNumber new_rel_pages;
PGRUsage ru0;
pg_rusage_init(ru0);
/*
! * We need full exclusive lock on the relation in order to do truncation.
! * If we can't get it, give up rather than waiting --- we don't want to
! * block other backends, and we don't want to deadlock (which is quite
! * possible considering we already hold a lower-grade lock).
*/
! if (!ConditionalLockRelation(onerel, AccessExclusiveLock))
! return;
/*
! * Now that we have exclusive lock, look to see if the rel has grown
! * whilst we were vacuuming with non-exclusive lock. If so, give up; the
! * newly added pages presumably contain non-deletable tuples.
*/
! new_rel_pages = RelationGetNumberOfBlocks(onerel);
! if (new_rel_pages != old_rel_pages)
{
/*
! * Note: we intentionally don't update
Re: [HACKERS] proposal: separate databases for contrib module testing
On 12/02/2012 10:05 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: I'd like to change the way we set the CONTRIB_TESTDB name for contrib modules. so that each module doesn't wipe out the previous module's test db. Personally I always thought that was a feature not a bug. If we give each one its own DB, there will be a couple of dozen databases cluttering the installation at the end of make installcheck, and no convenient way to get rid of them. Moreover, what I think you've got in mind doesn't work in the make check case anyway --- you'd have little alternative but to test upgrading each one separately. The last point at least doesn't seem relevant. The test script we currently use for pg_upgrade uses make installcheck and the new cross-version upgrade testing I'm working on relies on having the items to be upgraded established via make installcheck. How about if we have a make target to clean these databases out, installcheck-clean, maybe? Alternatively, or in addition, how about if we have a separate make target to do things the way I'm suggesting, assuming I can make that work? Testing upgrading each contrib module separately is really very sub-optimal. 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] proposal: separate databases for contrib module testing
Andrew Dunstan and...@dunslane.net writes: On 12/02/2012 10:05 AM, Tom Lane wrote: Personally I always thought that was a feature not a bug. If we give each one its own DB, there will be a couple of dozen databases cluttering the installation at the end of make installcheck, and no convenient way to get rid of them. How about if we have a make target to clean these databases out, installcheck-clean, maybe? Alternatively, or in addition, how about if we have a separate make target to do things the way I'm suggesting, assuming I can make that work? Either of those would satisfy my concern. The latter might be a bit easier, not sure. 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] proposal: separate databases for contrib module testing
On 12/02/2012 11:29 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 12/02/2012 10:05 AM, Tom Lane wrote: Personally I always thought that was a feature not a bug. If we give each one its own DB, there will be a couple of dozen databases cluttering the installation at the end of make installcheck, and no convenient way to get rid of them. How about if we have a make target to clean these databases out, installcheck-clean, maybe? Alternatively, or in addition, how about if we have a separate make target to do things the way I'm suggesting, assuming I can make that work? Either of those would satisfy my concern. The latter might be a bit easier, not sure. Yeah. This lets me get what I want, via make USE_MODULE_DB=1 installcheck, don't even need a new target. There's probably a case for doing the same sort of thing for the pl_* makefiles on the basis of consistency, not sure if it's worth it though. cheers andrew *** a/contrib/dblink/Makefile --- b/contrib/dblink/Makefile *** *** 11,16 DATA = dblink--1.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql --- 11,19 REGRESS = dblink + # the db name is hard-coded in the tests + override undefine USE_MODULE_DB + ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) *** a/src/Makefile.global.in --- b/src/Makefile.global.in *** *** 428,433 submake-libpgport: --- 428,442 PL_TESTDB = pl_regression CONTRIB_TESTDB = contrib_regression + ifneq ($(MODULE_big),) + CONTRIB_TESTDB_MODULE = contrib_regression_$(MODULE_big) + else + ifneq ($(MODULES),) + CONTRIB_TESTDB_MODULE = contrib_regression_$(MODULES) + else + CONTRIB_TESTDB_MODULE = contrib_regression + endif + endif ifdef NO_LOCALE NOLOCALE += --no-locale *** a/src/makefiles/pgxs.mk --- b/src/makefiles/pgxs.mk *** *** 240,246 distclean maintainer-clean: clean ifdef REGRESS # Select database to use for running the tests ! REGRESS_OPTS += --dbname=$(CONTRIB_TESTDB) # where to find psql for running the tests PSQLDIR = $(bindir) --- 240,250 ifdef REGRESS # Select database to use for running the tests ! ifdef USE_MODULE_DB ! REGRESS_OPTS += --dbname=$(CONTRIB_TESTDB_MODULE) ! else ! REGRESS_OPTS += --dbname=$(CONTRIB_TESTDB) ! endif # where to find psql for running the tests PSQLDIR = $(bindir) -- 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] WIP: index support for regexp search
On Fri, Nov 30, 2012 at 6:23 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 30.11.2012 13:20, Alexander Korotkov wrote: On Thu, Nov 29, 2012 at 5:25 PM, Heikki Linnakangashlinnakangas@** vmware.com hlinnakan...@vmware.com wrote: Would it be safe to simply stop short the depth-first search on overflow, and proceed with the graph that was constructed up to that point? For depth-first it's not. But your proposal naturally makes sense. I've changed it to breadth-first search. And then it's safe to mark all unprocessed states as final when overflow. It means that we assume every unprocessed branch to immediately finish with matching (this can give us more false positives but no false negatives). For overflow of matrix collection, it's safe to do just OR between all the trigrams. New version of patch is attached. Thanks, sounds good. I've spent quite a long time trying to understand the transformation the getState/addKeys/addAcrs functions do to the original CNFA graph. I think that still needs more comments to explain the steps involved in it. One thing that occurs to me is that it might be better to delay expanding colors to characters. You could build and transform the graph, and even create the paths, all while operating on colors. You would end up with lists of color trigrams, consisting of sequences of three colors that must appear in the source string. Only at the last step you would expand the color trigrams to real character trigrams. I think that would save a lot of processing while building the graph, if you have colors that contain many characters. It would allow us to do better than the fixed small MAX_COLOR_CHARS limit. For example with MAX_COLOR_CHARS = 4 in the current patch, it's a shame that we can't do anything with a fairly simple regexp like ^a[b-g]h$ Nice idea to delay expanding colors to characters! Obviously, we should delay expanding inly alphanumerical characters. Because non-alphanumberical characters influence graph structure. Trying to implement... -- With best regards, Alexander Korotkov.
Re: [HACKERS] WIP: index support for regexp search
On Sat, Dec 1, 2012 at 3:22 PM, Erik Rijkers e...@xs4all.nl wrote: On Fri, November 30, 2012 12:22, Alexander Korotkov wrote: Hi! On Thu, Nov 29, 2012 at 12:58 PM, er e...@xs4all.nl wrote: On Mon, November 26, 2012 20:49, Alexander Korotkov wrote: I ran the simple-minded tests against generated data (similar to the ones I did in January 2012). The problems of that older version seem pretty much all removed. (although I didn't do much work on it -- just reran these tests). Thanks a lot for testing! Could you repeat for 0.7 version of patch which has new overflow handling? I've attached a similar test re-run that compares HEAD with patch versions 0.6, and 0.7. Thanks! Did you write scripts for automated testing? I would be nice if you share them. -- With best regards, Alexander Korotkov.
Re: [HACKERS] WIP: index support for regexp search
Alexander Korotkov aekorot...@gmail.com writes: Nice idea to delay expanding colors to characters! Obviously, we should delay expanding inly alphanumerical characters. Because non-alphanumberical characters influence graph structure. Trying to implement... Uh, why would that be? Colors are colors. The regexp machinery doesn't care whether they represent alphanumerics or not. (Or to be more precise, if there is a situation where it makes a difference, separate colors will have been created for each set of characters that need to be distinguished.) 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] [PATCH 08/14] Store the number of subtransactions in xl_running_xacts separately from toplevel xids
On 15 November 2012 12:07, Simon Riggs si...@2ndquadrant.com wrote: On 14 November 2012 22:17, Andres Freund and...@2ndquadrant.com wrote: To avoid complicating logic we store both, the toplevel and the subxids, in -xip, first -xcnt toplevel ones, and then -subxcnt subxids. That looks good, not much change. Will apply in next few days. Please add me as committer and mark ready. I tried improving this, but couldn't. So I've committed it as is. -- Simon Riggs http://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] Materialized views WIP patch
Hi Kevin, On Mon, 26 Nov 2012 22:24:33 +0100, Kevin Grittner kgri...@mail.com wrote: Marko Tiikkaja wrote: T2 sees an empty table As far as I know you are the first to notice this behavior. Thanks for pointing it out. I will take a look at the issue; I don't know whether it's something small I can address in this CF or whether it will need to be in the next CF, but I will fix it. Any news on this front? I'll get back when I manage to get a better grasp of the code. The code looks relatively straightforward and good to my eyes. It passes my testing and looks to be changing all the necessary parts of the code. Keep in mind that the current behavior of behaving like a regular view when the contents are invalid is not what I had in mind, that was an accidental effect of commenting out the body of the ExecCheckRelationsValid() function right before posting the patch because I noticed a regression. When I noticed current behavior, it struck me that someone might prefer it to the intended behavior of showing an error like this: ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg(materialized view \%s\ has not been populated, get_rel_name(rte-relid)), errhint(Use the LOAD MATERIALIZED VIEW command.))); I mention it in case someone wants to argue for silently behaving as a regular view when the MV is not populated. FWIW, I'd prefer an error in this case, but I don't feel strongly about it. Regards, Marko Tiikkaja -- 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] Tablespaces in the data directory
On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Someone just reported a problem when they had created a new tablespace inside the old data directory. I'm sure there can be other issues caused by this as well, but this is mainly a confusing scenario for people now. As there isn't (as far as I know at least) any actual *point* in creating a tablespace inside the main data directory, should we perhaps disallow this in CREATE TABLESPACE? Or at least throw a WARNING if one does it? It could be pretty hard to detect that in general (think symlinks and such). I guess if we're just trying to print a helpful warning, we don't have to worry about extreme corner cases. But what exactly do you have in mind --- complain about any relative path? Complain about absolute paths that have a prefix matching the DataDir? Oh, I hadn't thought quite so far as the implementation :) Was looking to see if there were going to be some major objections before I even started thinking about that. But for the implementation, I'd say any absolute path that have a prefix matching DataDir. Tablespaces cannot be created using relative paths, so we don't have to deal with that. -- 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] Tablespaces in the data directory
On 12/02/2012 07:50 PM, Magnus Hagander wrote: On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Someone just reported a problem when they had created a new tablespace inside the old data directory. I'm sure there can be other issues caused by this as well, but this is mainly a confusing scenario for people now. As there isn't (as far as I know at least) any actual *point* in creating a tablespace inside the main data directory, should we perhaps disallow this in CREATE TABLESPACE? Or at least throw a WARNING if one does it? It could be pretty hard to detect that in general (think symlinks and such). I guess if we're just trying to print a helpful warning, we don't have to worry about extreme corner cases. But what exactly do you have in mind --- complain about any relative path? Complain about absolute paths that have a prefix matching the DataDir? Oh, I hadn't thought quite so far as the implementation :) Was looking to see if there were going to be some major objections before I even started thinking about that. But for the implementation, I'd say any absolute path that have a prefix matching DataDir. Tablespaces cannot be created using relative paths, so we don't have to deal with that. I have been known to symlink a tablespace on a replica back to a directory in the datadir, while on the primary it points elsewhere. What exactly is the problem? 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] autovacuum stress-testing our system
On 21.11.2012 19:02, Robert Haas wrote: On Sun, Nov 18, 2012 at 5:49 PM, Tomas Vondra t...@fuzzy.cz wrote: The two main changes are these: (1) The stats file is split into a common db file, containing all the DB Entries, and per-database files with tables/functions. The common file is still called pgstat.stat, the per-db files have the database OID appended, so for example pgstat.stat.12345 etc. This was a trivial hack pgstat_read_statsfile/pgstat_write_statsfile functions, introducing two new functions: pgstat_read_db_statsfile pgstat_write_db_statsfile that do the trick of reading/writing stat file for one database. (2) The pgstat_read_statsfile has an additional parameter onlydbs that says that you don't need table/func stats - just the list of db entries. This is used for autovacuum launcher, which does not need to read the table/stats (if I'm reading the code in autovacuum.c correctly - it seems to be working as expected). I'm not an expert on the stats system, but this seems like a promising approach to me. (a) It does not solve the many-schema scenario at all - that'll need a completely new approach I guess :-( We don't need to solve every problem in the first patch. I've got no problem kicking this one down the road. (b) It does not solve the writing part at all - the current code uses a single timestamp (last_statwrite) to decide if a new file needs to be written. That clearly is not enough for multiple files - there should be one timestamp for each database/file. I'm thinking about how to solve this and how to integrate it with pgstat_send_inquiry etc. Presumably you need a last_statwrite for each file, in a hash table or something, and requests need to specify which file is needed. And yet another one I'm thinking about is using a fixed-length array of timestamps (e.g. 256), indexed by mod(dboid,256). That would mean stats for all databases with the same mod(oid,256) would be written at the same time. Seems like an over-engineering though. That seems like an unnecessary kludge. (c) I'm a bit worried about the number of files - right now there's one for each database and I'm thinking about splitting them by type (one for tables, one for functions) which might make it even faster for some apps with a lot of stored procedures etc. But is the large number of files actually a problem? After all, we're using one file per relation fork in the base directory, so this seems like a minor issue. I don't see why one file per database would be a problem. After all, we already have on directory per database inside base/. If the user has so many databases that dirent lookups in a directory of that size are a problem, they're already hosed, and this will probably still work out to a net win. Attached is a v2 of the patch, fixing some of the issues and unclear points from the initial version. The main improvement is that it implements writing only stats for the requested database (set when sending inquiry). There's a dynamic array of request - for each DB only the last request is kept. I've done a number of changes - most importantly: - added a stats_timestamp field to PgStat_StatDBEntry, keeping the last write of the database (i.e. a per-database last_statwrite), which is used to decide whether the file is stale or not - handling of the 'permanent' flag correctly (used when starting or stopping the cluster) for per-db files - added a very simple header to the per-db files (basically just a format ID and a timestamp) - this is needed for checking of the timestamp of the last write from workers (although maybe we could just read the pgstat.stat, which is now rather small) - a 'force' parameter (true - write all databases, even if they weren't specifically requested) So with the exception of 'multi-schema' case (which was not the aim of this effort), it should solve all the issues of the initial version. There are two blocks of code dealing with clock glitches. I haven't fixed those yet, but that can wait I guess. I've also left there some logging I've used during development (printing inquiries and which file is written and when). The main unsolved problem I'm struggling with is what to do when a database is dropped? Right now, the statfile remains in pg_stat_tmp forewer (or until the restart) - is there a good way to remove the file? I'm thinking about adding a message to be sent to the collector from the code that handles DROP TABLE. I've done some very simple performance testing - I've created 1000 databases with 1000 tables each, done ANALYZE on all of them. With only autovacum running, I've seen this: Without the patch - %CPU %MEM TIME+ COMMAND 183.00:10.10 postgres: autovacuum launcher process 172.60:11.44 postgres: stats collector process The I/O
Re: [HACKERS] Tablespaces in the data directory
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 As there isn't (as far as I know at least) any actual *point* in creating a tablespace inside the main data directory, should we perhaps disallow this in CREATE TABLESPACE? Or at least throw a WARNING if one does it? Sure there is a point - emulating some other system. Could be replication, QA box, disaster recovery, etc. I'd be cool with a warning, but do not think we should disallow it. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201212022133 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlC8D7kACgkQvJuQZxSWSsj+5gCgsmi6NXue+Hp0gycVOL/JEGUT anYAoIqwo24JeLfliRHLvwPbdK4F4TXa =EwgC -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH 11/14] Introduce wal decoding via catalog timetravel
On 12-11-14 08:17 PM, Andres Freund wrote: I am getting errors like the following when I try to use either your test_decoding plugin or my own (which does even less than yours) LOG: database system is ready to accept connections LOG: autovacuum launcher started WARNING: connecting to WARNING: Initiating logical rep LOG: computed new xmin: 773 LOG: start reading from 0/17F5D58, scrolled back to 0/17F4000 LOG: got new xmin 773 at 25124280 LOG: found initial snapshot (via running xacts). Done: 1 WARNING: reached consistent point, stopping! WARNING: Starting logical replication LOG: start reading from 0/17F5D58, scrolled back to 0/17F4000 LOG: found initial snapshot (via running xacts). Done: 1 FATAL: cannot read pg_class without having selected a database TRAP: FailedAssertion(!(SHMQueueEmpty((MyProc-myProcLocks[i]))), File: proc.c, Line: 759) This seems to be happening under the calls at reorderbuffer.c:832if (!SnapBuildHasCatalogChanges(NULL, xid, change-relnode)) The sequence of events I do is: 1. start pg_receivellog 2. run a checkpoint 3. Attach to the walsender process with gdb 4. Start a new client connection with psql and do 'INSERT INTO a values (1)' twice. (skipping step 3 doesn't make a difference) I This introduces several things: * 'reorderbuffer' module which reassembles transactions from a stream of interspersed changes * 'snapbuilder' which builds catalog snapshots so that tuples from wal can be understood * logging more data into wal to facilitate logical decoding * wal decoding into an reorderbuffer * shared library output plugins with 5 callbacks * init * begin * change * commit * walsender infrastructur to stream out changes and to keep the global xmin low enough * INIT_LOGICAL_REPLICATION $plugin; waits till a consistent snapshot is built and returns * initial LSN * replication slot identifier * id of a pg_export() style snapshot * START_LOGICAL_REPLICATION $id $lsn; streams out changes * uses named output plugins for output specification Todo: * testing infrastructure (isolationtester) * persistence/spilling to disk of built snapshots, longrunning transactions * user docs * more frequent lowering of xmins * more docs about the internals * support for user declared catalog tables * actual exporting of initial pg_export snapshots after INIT_LOGICAL_REPLICATION * own shared memory segment instead of piggybacking on walsender's * nicer interface between snapbuild.c, reorderbuffer.c, decode.c and the outside. * more frequent xl_running_xid's so xmin can be upped more frequently * add STOP_LOGICAL_REPLICATION $id --- src/backend/access/heap/heapam.c| 280 +- src/backend/access/transam/xlog.c |1 + src/backend/catalog/index.c | 74 ++ src/backend/replication/Makefile|2 + src/backend/replication/logical/Makefile| 19 + src/backend/replication/logical/decode.c| 496 ++ src/backend/replication/logical/logicalfuncs.c | 247 + src/backend/replication/logical/reorderbuffer.c | 1156 +++ src/backend/replication/logical/snapbuild.c | 1144 ++ src/backend/replication/repl_gram.y | 32 +- src/backend/replication/repl_scanner.l |2 + src/backend/replication/walsender.c | 566 ++- src/backend/storage/ipc/procarray.c | 23 + src/backend/storage/ipc/standby.c |8 +- src/backend/utils/cache/inval.c |2 +- src/backend/utils/cache/relcache.c |3 +- src/backend/utils/misc/guc.c| 11 + src/backend/utils/time/tqual.c | 249 + src/bin/pg_controldata/pg_controldata.c |2 + src/include/access/heapam_xlog.h| 23 + src/include/access/transam.h|5 + src/include/access/xlog.h |3 +- src/include/catalog/index.h |4 + src/include/nodes/nodes.h |2 + src/include/nodes/replnodes.h | 22 + src/include/replication/decode.h| 21 + src/include/replication/logicalfuncs.h | 44 + src/include/replication/output_plugin.h | 76 ++ src/include/replication/reorderbuffer.h | 284 ++ src/include/replication/snapbuild.h | 128 +++ src/include/replication/walsender.h |1 + src/include/replication/walsender_private.h | 34 +- src/include/storage/itemptr.h |3 + src/include/storage/sinval.h|2 + src/include/utils/tqual.h | 31 +- 35 files changed, 4966 insertions(+), 34 deletions(-) create mode 100644 src/backend/replication/logical/Makefile create mode 100644 src/backend/replication/logical/decode.c
[HACKERS] [PATCH] Patch to fix libecpg.so for isinf missing
hi
isinf() is not build to libecpg.so if build and install postgresql by
source on solaris9.
(isinf() is not contained within solaris9 system.)
bash-2.05$ nm /usr/local/pgsql/lib/libecpg.so | grep isinf
[215] | 0| 0|FUNC |GLOB |0|UNDEF |isinf
It(isinf missing) will causes ecpg program compile error such as follows.
bash-2.05$ ecpg test.pgc
bash-2.05$ cc -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lecpg
test.c -o test
Undefined first referenced
symbol in file
isinf /usr/local/pgsql/lib/libecpg.so
ld: fatal: Symbol referencing errors. No output written to test
I modify src/interfaces/ecpg/ecpglib/Makefile to resolve this problem.
The diff file refer to the attachment ecpglib-Makefile.patch.
Regards,
Jiang Guiqing
int main(void)
{
EXEC SQL CONNECT TO tcp:postgresql://localhost:5432/postgres USER
postgres;
EXEC SQL CREATE TABLE testecpg (id int, data text);
EXEC SQL DISCONNECT;
return 0;
}
diff --git a/src/interfaces/ecpg/ecpglib/Makefile
b/src/interfaces/ecpg/ecpglib/Makefile
index 565df3c..daac615 100644
--- a/src/interfaces/ecpg/ecpglib/Makefile
+++ b/src/interfaces/ecpg/ecpglib/Makefile
@@ -26,7 +26,7 @@ LIBS := $(filter-out -lpgport, $(LIBS))
OBJS= execute.o typename.o descriptor.o sqlda.o data.o error.o prepare.o
memory.o \
connect.o misc.o path.o pgstrcasecmp.o \
- $(filter snprintf.o strlcpy.o win32setlocale.o, $(LIBOBJS))
+ $(filter snprintf.o strlcpy.o win32setlocale.o isinf.o, $(LIBOBJS))
# thread.c is needed only for non-WIN32 implementation of path.c
ifneq ($(PORTNAME), win32)
@@ -57,7 +57,7 @@ include $(top_srcdir)/src/Makefile.shlib
# necessarily use the same object files as the backend uses. Instead,
# symlink the source files in here and build our own object file.
-path.c pgstrcasecmp.c snprintf.c strlcpy.c thread.c win32setlocale.c: % :
$(top_srcdir)/src/port/%
+path.c pgstrcasecmp.c snprintf.c strlcpy.c thread.c win32setlocale.c isinf.c:
% : $(top_srcdir)/src/port/%
rm -f $@ $(LN_S) $ .
misc.o: misc.c $(top_builddir)/src/port/pg_config_paths.h
--
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 removng unused targets
Sorry for the delay. I've reviewed the patch. It was applied successfully, and it worked well for tests I did including the example you showed. I think it's worth the work, but I'm not sure you go about it in the right way. (I feel the patch decreases code readability more than it gives an advantage.) If you move forward in this way, I think the following need to be considered at least: * The following functions need to be changed to have the resorderbyonly flag: _equalTargetEntry() _readTargetEntry() _outTargetEntry() * Can we remove the attributes in the coded way safely? /* * Plan come out in the right order, we can remove attributes which * are used only for ORDER BY clause because there is no need to * calculate them. */ The implicit relationship between the TargetEntry's resno and the list size (the resno is not larger than the list size if I understand it aright) might break. Is that OK? (I would like to think a more simple approach to this optimization.) Thanks, Best regards, Etsuro Fujita From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Alexander Korotkov Sent: Tuesday, October 02, 2012 4:46 PM To: pgsql-hackers; Tom Lane Subject: [HACKERS] Patch for removng unused targets Hi! Attached patch removes unused targets which are used only for order by when data already comes in right order. It introduces resorderbyonly flag of TargetEntry which indicated that entry is used only for ORDER BY clause. If data comes in right order then such entries are removed in grouping_planner function. This is my first patch on planner. Probably, I did it in wrong way. But I think it is worthwhile optimization and you could give me direction to rework patch. Actually we meet need of this optimization when ranking full-text search in GIN index (it isn't published yet, will post prototype soon). But there is some synthetic example illustrating benefit from patch. CREATE OR REPLACE FUNCTION slow_func(x float8, y float8) RETURNS float8 AS $$ BEGIN PERFORM pg_sleep(0.01); RETURN x + y; END; $$ IMMUTABLE LANGUAGE plpgsql; CREATE TABLE test AS (SELECT random() AS x, random() AS y FROM generate_series(1,1000)); CREATE INDEX test_idx ON test(slow_func(x,y)); Without patch: test=# EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM test ORDER BY slow_func(x,y) LIMIT 10; QUERY PLAN -- Limit (cost=0.00..3.09 rows=10 width=16) (actual time=11.344..103.443 rows=10 loops=1) Output: x, y, (slow_func(x, y)) - Index Scan using test_idx on public.test (cost=0.00..309.25 rows=1000 width=16) (actual time=11.341..103.422 rows=10 loops=1) Output: x, y, slow_func(x, y) Total runtime: 103.524 ms (5 rows) With patch: test=# EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM test ORDER BY slow_func(x,y) LIMIT 10; QUERY PLAN --- Limit (cost=0.00..3.09 rows=10 width=16) (actual time=0.062..0.093 rows=10 loops=1) Output: x, y - Index Scan using test_idx on public.test (cost=0.00..309.25 rows=1000 width=16) (actual time=0.058..0.085 rows=10 loops=1) Output: x, y Total runtime: 0.164 ms (5 rows) -- With best regards, Alexander Korotkov.
Re: [HACKERS] WIP: index support for regexp search
On Sun, December 2, 2012 19:07, Alexander Korotkov wrote: I've attached a similar test re-run that compares HEAD with patch versions 0.6, and 0.7. Thanks! Did you write scripts for automated testing? I would be nice if you share them. Sure, here they are. The perl program does depend a bit on my particular setup (it reads the port from the postgresql.conf of each instance, and the script knows the data_dir locations), but I suppose it's easy enough to remove that. (Just hardcode the %instances hash, instead of calling instances().) If you need help to get them to run I can 'generalise' them, but for now I'll send them as they are. Erik Rijkers create_data.sh Description: application/shellscript trgm_regex_test.pl Description: Perl program -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
