[HACKERS] Renaming '2010-Next' to '2010-6' in the commitfest app
Hi Robert, Can we get that commitfest renamed? And if I should know how to do that, can you inform me how? Thanks! -selene -- http://chesnok.com/daily - me -- 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] Synchronous replication patch built on SR
On Wed, May 19, 2010 at 9:58 PM, Boszormenyi Zoltan wrote: >> In the patch, PQputCopyData() checks the newly-introduced pg_conn field >> "duplexCopy". Instead, how about checking the existing field "replication"? > > I didn't see there was such a new field. (looking...) I can see now, > it was added in the middle of the structure. Ok, we can then use it > to allow duplex COPY instead of my new field. I suppose it's non-NULL > if replication is on, right? Then the extra call is not needed then. Right. Usually the first byte of the pg_conn field seems to be also checked as follows, but I'm not sure if that is valuable for this case. if (conn->replication && conn->replication[0]) >> Or we can just allow PQputCopyData() to go even in COPY OUT state. > > I think this may not be too useful for SQL clients, but who knows? :-) > Use cases, anyone? It's for only replication. >> Hmm... when min_sync_replication_clients = 2 and there are three >> "synchronous" standbys, the master waits for only two standbys? >> > > Yes. This is the idea, "partially synchronous replication". > I heard anecdotes about replication solutions where say > ensuring that (say) if at least 50% of the machines across the > whole cluster report back synchronously then the transaction > is considered replicated "good enough". Oh, I got. I heard such a use case for the first time. We seem to have many ideas about the knobs to control synchronization levels, and would need to clarify which ones to be implemented for 9.1. >> I'd like to just know the use case of min_sync_replication_clients. >> Sorry, I've not understood yet how useful this option is. >> > > I hope I answered it. :-) Yep. Thanks! Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_transaction patch
Hajimemashite Takahiro, Thanks for your feedback. I applied all the changes on 9.0beta manually and then it compiled without any assertion failures. I also changed the oids to a different unused range, since the ones I used before had been taken in 9.0beta1. There are still some problems though. I get 0 back from the functions supposed to return the number of inserts/updates for the current transaction. I suspect it is because get_tabstat_entry for some reason returns NULL, in for example pg_stat_get_transaction_tuples_inserted(PG_FUNCTION_ARGS). Does the function look valid? If you can find the error in it, the other functions probably have the same problem. It is strange though the function "pg_stat_get_transaction_numscans" works fine, and it looks like it works the same way. I added run.out843 and run.out90b1, showing the output from both patched versions. run.out843 is the intended output, while run.out90b1 gives 0 on the columns n_tup_ins and n_tup_upd (and probably n_tup_del etc also). I hope someone can help locating the problem. Thanks. Best regards, Joel 2010/5/7 Takahiro Itagaki > > Joel Jacobson wrote: > > > I propose a set of new statistics functions and system views. > > > > I need these functions in order to do automated testing of our system, > > consisting of hundreds of stored procedures in plpgsql. > > My plan is to develop some additional functions to pgTAP, benefiting from > > the new system tables I've added. > > I ported your patch into 9.0beta, but it doesn't work well. > I had two assertion failures from the run.sql: > > TRAP: FailedAssertion("!(entry->trans == ((void *)0))", File: "pgstat.c", > Line: 715) > TRAP: FailedAssertion("!(tabstat->trans == trans)", File: "pgstat.c", Line: > 1756) > > Also, pg_stat_transaction_functions returned no rows from the test case > even > after I removed those assertions. There are no rows in your test/run.out, > too. > > I like your idea itself, but more works are required for the > implementation. > > Regards, > --- > Takahiro Itagaki > NTT Open Source Software Center > > -- Best regards, Joel Jacobson Glue Finance E: j...@gluefinance.com T: +46 70 360 38 01 Postal address: Glue Finance AB Box 549 114 11 Stockholm Sweden Visiting address: Glue Finance AB Birger Jarlsgatan 14 114 34 Stockholm Sweden pg_stat_transaction-1.31.tar.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] Stefan's bug (was: max_standby_delay considered harmful)
On Wed, May 19, 2010 at 10:03 PM, Robert Haas wrote: > On Wed, May 19, 2010 at 8:49 AM, Simon Riggs wrote: >> On Wed, 2010-05-19 at 08:21 -0400, Tom Lane wrote: >>> Robert Haas writes: >>> > On Wed, May 19, 2010 at 1:47 AM, Fujii Masao >>> > wrote: >>> >> Yes, but I prefer XLogCtl->SharedRecoveryInProgress, which is the almost >>> >> same indicator as the boolean you suggested. Thought? >>> >>> > It feels cleaner and simpler to me to use the information that the >>> > postmaster already collects rather than having it take locks and check >>> > shared memory, but I might be wrong. Why do you prefer doing it that >>> > way? >>> >>> The postmaster must absolutely not take locks (once there are competing >>> processes). This is non negotiable from a system robustness standpoint. >> >> Masao has not proposed this, in fact his proposal was to deliberately >> avoid do so. >> >> I proposed using the state recorded in xlog.c rather than attempting to >> duplicate that with a second boolean in postmaster because that seems >> likely to be more buggy. > > Well then how are we reading XLogCtl? In my patch, XLogCtl is directly read in xlog.c without any lock since there should be no other processes running when CancelBackup() is called. *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *** *** 8975,8980 CancelBackup(void) --- 8975,8987 { struct stat stat_buf; + /* +* During recovery, we don't rename the "backup_label" file since +* it might be required for subsequent recovery. +*/ + if (XLogCtl->SharedRecoveryInProgress) + return; + /* if the file is not there, return */ if (stat(BACKUP_LABEL_FILE, &stat_buf) < 0) return; Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade docs
Andres Freund wrote: > On Wednesday 19 May 2010 22:39:32 Bruce Momjian wrote: > > There are some limitations when migrating from 8.3 to 8.4, but not when > > migrating from 8.3 to 9.0, because we added a feature to 9.0. Can you > > give a specific example? > Didnt the 'name' alignment change? Uh, the heading above that item is: Limitations in migrating from PostgreSQL 8.3 What is unclear there? It covers going to 8.4 and 9.0. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade docs
On Wednesday 19 May 2010 22:39:32 Bruce Momjian wrote: > There are some limitations when migrating from 8.3 to 8.4, but not when > migrating from 8.3 to 9.0, because we added a feature to 9.0. Can you > give a specific example? Didnt the 'name' alignment change? Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade docs
Alvaro Herrera wrote: > Excerpts from Stefan Kaltenbrunner's message of mi?? may 19 15:53:18 -0400 > 2010: > > While looking at the docs for pg_upgrade I noticed some stuff that the > > following patch attempts to at least partly address. > > Surely this para can be removed? > > > If you are using tablespaces and migrating to 8.4 or earlier, there must > - be sufficient directory permissions to allow pg_upgrade to rename each > + be sufficient directory permissions to allow pg_upgrade > to rename each > tablespace directory to add a ".old" suffix. > Ah, yes, removed. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade docs
Stefan Kaltenbrunner wrote: > While looking at the docs for pg_upgrade I noticed some stuff that the > following patch attempts to at least partly address. > There is quite some confusion going on between using "Postgres" and > PostgreSQL, I changed that to the later because that is how we spell the > productname in all the other parts of the docs, also added some further > markups and crossreferences to other docs. Applied. > Stuff that seems to need further work is more or less the "limitations" > section, I don't think there are only issues when upgrade from 8.3 but > also from 8.4 (though not as much iirc) there is also the rather bold There are some limitations when migrating from 8.3 to 8.4, but not when migrating from 8.3 to 9.0, because we added a feature to 9.0. Can you give a specific example? > "we will support upgrades from every snapshot and alpha release" which > seems very optimistic... Well, I didn't say "every". -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade docs
Excerpts from Stefan Kaltenbrunner's message of mié may 19 15:53:18 -0400 2010: > While looking at the docs for pg_upgrade I noticed some stuff that the > following patch attempts to at least partly address. Surely this para can be removed? If you are using tablespaces and migrating to 8.4 or earlier, there must - be sufficient directory permissions to allow pg_upgrade to rename each + be sufficient directory permissions to allow pg_upgrade to rename each tablespace directory to add a ".old" suffix. -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade docs
While looking at the docs for pg_upgrade I noticed some stuff that the following patch attempts to at least partly address. There is quite some confusion going on between using "Postgres" and PostgreSQL, I changed that to the later because that is how we spell the productname in all the other parts of the docs, also added some further markups and crossreferences to other docs. Stuff that seems to need further work is more or less the "limitations" section, I don't think there are only issues when upgrade from 8.3 but also from 8.4 (though not as much iirc) there is also the rather bold "we will support upgrades from every snapshot and alpha release" which seems very optimistic... Stefan Index: doc/src/sgml/pgupgrade.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/pgupgrade.sgml,v retrieving revision 1.5 diff -u -r1.5 pgupgrade.sgml --- doc/src/sgml/pgupgrade.sgml 18 May 2010 15:41:36 - 1.5 +++ doc/src/sgml/pgupgrade.sgml 19 May 2010 19:33:07 - @@ -9,10 +9,10 @@ pg_upgrade (formerly called pg_migrator) allows data - stored in Postgres data files to be migrated to a later Postgres + stored in PostgreSQL data files to be migrated to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release - of Postgres. It is not required for minor version upgrades, e.g. + of PostgreSQL. It is not required for minor version upgrades, e.g 9.0.1 -> 9.0.4. @@ -21,7 +21,7 @@ pg_upgrade supports upgrades from 8.3.X and later to the current - major release of Postgres, including snapshot and alpha releases. + major release of PostgreSQL, including snapshot and alpha releases. @@ -37,17 +37,17 @@ - If you are using a version-specific PostgreSQL install directory, e.g. + If you are using a version-specific installation directory, e.g. /opt/PostgreSQL/8.4, you do not need to move the old cluster. The one-click installers all use version-specific install directories. - If your PostgreSQL install directory is not version-specific, e.g. - /usr/local/pgsql, it is necessary to move the current Postgres install - directory so it does not interfere with the new Postgres installation. - Once the current Postgres server is shut down, it is safe to rename the - Postgres install directory; assuming the old directory is + If your installation directory is not version-specific, e.g. + /usr/local/pgsql, it is necessary to move the current PostgreSQL install + directory so it does not interfere with the new PostgreSQL installation. + Once the current PostgreSQL server is shut down, it is safe to rename the + PostgreSQL install directory; assuming the old directory is /usr/local/pgsql, you can do: @@ -58,26 +58,26 @@ If you are using tablespaces and migrating to 8.4 or earlier, there must - be sufficient directory permissions to allow pg_upgrade to rename each + be sufficient directory permissions to allow pg_upgrade to rename each tablespace directory to add a ".old" suffix. - For PostgreSQL source installs, build the new PostgreSQL version + For source installs, build the new version - Build the new Postgres source with configure flags that are compatible - with the old cluster. pg_upgrade will check pg_controldata to make + Build the new PostgreSQL source with configure flags that are compatible + with the old cluster. pg_upgrade will check pg_controldata to make sure all settings are compatible before starting the upgrade. - Install the new Postgres binaries + Install the new PostgreSQL binaries @@ -109,8 +109,10 @@ - Initialize the new cluster using initdb. Again, use compatible initdb - flags that match the old cluster (pg_upgrade will check that too.) Many + Initialize the new cluster ,initdb. + Again, use compatible initdb + flags that match the old cluster. Many prebuilt installers do this step automatically. There is no need to start the new cluster. @@ -139,8 +141,8 @@ pg_upgrade will connect to the old and new servers several times, so you might want to set authentication to trust in pg_hba.conf, or if using md5 authentication, - use a pgpass file to avoid being prompted repeatedly - for a password. + use a ~/.pgpass file (see ) + to avoid being prompted repeatedly for a password. @@ -167,20 +169,20 @@ or -NET STOP pgsql-8.3 (different service name) +NET STOP pgsql-8.3 (PostgreSQL 8.3 and older used a different service name) - Run pg_upgrade + Run pg_upgrade - Always run the pg_upgrade binary in the ne
[HACKERS] tsvector pg_stats seems quite a bit off.
Hi. I am working on getting full-text-search to work and have come across something I think look a bit strange. The document base is arount 350.000 documents and I have set the statistics target on the tsvector column to 1000 since the 100 seems way of. # ANALYZE verbose reference (document_tsvector); INFO: analyzing "reference" INFO: "reference": scanned 14486 of 14486 pages, containing 350174 live rows and 6027 dead rows; 30 rows in sample, 350174 estimated total rows ANALYZE Ok, so analyze allmost examined all rows. Looking into "most_common_freqs" I find # select count(unnest) from (select unnest(most_common_freqs) from pg_stats where attname = 'document_tsvector') as foo; count --- 2810 (1 row) But the distribution is very "flat" at the end, the last 128 values are excactly 1.00189e-05 which means that any term sitting outside the array would get an estimate of 1.00189e-05 * 350174 / 2 = 1.75 ~ 2 rows So far I have no idea if this is bad or good, so a couple of sample runs of stuff that is sitting outside the "most_common_vals" array: # explain analyze select id from efam.reference where document_tsvector @@ to_tsquery('searchterm') order by id limit 2000; QUERY PLAN -- Limit (cost=35.99..35.99 rows=2 width=4) (actual time=20.717..28.135 rows=1612 loops=1) -> Sort (cost=35.99..35.99 rows=2 width=4) (actual time=20.709..23.190 rows=1612 loops=1) Sort Key: id Sort Method: quicksort Memory: 124kB -> Bitmap Heap Scan on reference (cost=28.02..35.98 rows=2 width=4) (actual time=3.522..17.238 rows=1612 loops=1) Recheck Cond: (document_tsvector @@ to_tsquery('searchterm'::text)) -> Bitmap Index Scan on reference_fts_idx (cost=0.00..28.02 rows=2 width=0) (actual time=3.378..3.378 rows=1613 loops=1) Index Cond: (document_tsvector @@ to_tsquery('searchterm'::text)) Total runtime: 30.743 ms (9 rows) Ok, the query-planner estimates that there are 2 rows .. excactly as predicted, works as expected but in fact there are 1612 rows that matches. So, analyze has sampled 6 of 7 rows in the table and this term exists in 1612/350174 rows ~ freq: 0.0046 which is way higher than the lower bound of 1.00189e-05 .. or it should have been sitting around the center of the 2810 values of the histogram collected. So the "most_common_vals" seems to contain a lot of values that should never have been kept in favor of other values that are more common. In practice, just cranking the statistics estimate up high enough seems to solve the problem, but doesn't there seem to be something wrong in how the statistics are collected? # select version(); version --- PostgreSQL 9.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.2.real (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4), 64-bit Jesper -- Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade - link mode and transaction-wraparound data loss
Jesper Krogh wrote: > On 2010-05-18 18:57, Bruce Momjian wrote: > > jes...@krogh.cc wrote: > > > >> Hi > >> > >> I tried running pg_upgrade from the current snapshot of postgresql and > >> upgrading from 8.4.4 to the snapshot version. Everything seem to look fine > >> in the process and all that came out was only "ok's" but when I tried a > >> simple query on the databse it keeps throwing these message out of the back > >> side. > >> > >> DETAIL: You might have already suffered transaction-wraparound data loss. > >> WARNING: some databases have not been vacuumed in over 2 billion > >> transactions > >> > >> > >> The database was around 600GB and it took a couple of minutes to run > >> pg_upgrade after I had all the binaries in the correct place. > >> > >> It is not really an easy task to throw around 600GB of data, so I cannot > >> gaurantee that the above is reproducible, but I'll see if I can get time > >> and try to reproduce it. > >> > > This certainly should never have happened, so I am guessing it is a bug. > > pg_upgrade tries hard to make sure all your datfrozenxid and > > relfrozenxid are properly migrated from the old server, and the > > transaction id is set properly. Unfortunately this is the first time I > > have heard of such a problem, so I am unclear on its cause. > > > > Other people are typically way faster than I am looking into it. > Depesz has produced a full trace to reproduce the problem here: > http://www.depesz.com/index.php/2010/05/19/waiting-for-9-0-pg_upgrade/ Thanks. I have commented on the blog to mention that we have fixed the bug reported there. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade - link mode and transaction-wraparound data loss
Bruce Momjian wrote: > > This is the "production system". I have absolutely no indications that > > anything should be wrong in there. It has run rock-solid since it got > > migrated (dump/restore) to 8.4 for about 7 months now. So I am a bit > > scared about you telling that it seems wrong. (but that cannot be > > attributed to pg_upgrade) > > I am on chat with Alvaro now and it seems we do somehow connect to > template0 for transaction id wraparound. I think Alvaro will post > shortly on this. > > > > OK, thanks. This does seem odd. Frankly, having template0's > > > datfrozenxid be wrong would not cause any kind of instability because > > > template0 is used only by pg_dump, so I am wondering if something else > > > is seriously wrong. > > > > > I also think that something was seriously wrong with the pg_upgrade'd > > version. I'll try to reproduce and be a bit more carefull in tracking > > the steps > > this time. > > Thanks, but I think the entire problem might be this template0 xid issue > that Alvaro and I are researching. I can now see how invalid template0 > xids could cause the instability you saw in the new database. Odd no > one has seen this bug before. OK, after talking to Alvaro and Heikki, the problem is that while you cannot connect to template0, it is accessed by autovacuum for vacuum freeze, even if autovacuum is turned off. I think the reason you are seeing this bug is that your xid counter is near 2 billion (50% to wraparound) and the original template0 xids are the maximum distance from your counter. I am attaching the newest patch which fixes this issue. I did modify this code yesterday with another patch, and I am unclear exactly if you need that patch as well. CVS now has all these changes. If you could test with this and the earlier patch, I think it will now work fine. Thanks for the valuable testing, and quick feedback. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com Index: contrib/pg_upgrade/pg_upgrade.c === RCS file: /cvsroot/pgsql/contrib/pg_upgrade/pg_upgrade.c,v retrieving revision 1.3 diff -c -c -r1.3 pg_upgrade.c *** contrib/pg_upgrade/pg_upgrade.c 18 May 2010 18:40:51 - 1.3 --- contrib/pg_upgrade/pg_upgrade.c 19 May 2010 18:20:03 - *** *** 164,170 check_ok(ctx); /* ! * We do freeze after analyze so pg_statistic is also frozen */ prep_status(ctx, "Freezing all rows on the new cluster"); exec_prog(ctx, true, --- 164,173 check_ok(ctx); /* ! * We do freeze after analyze so pg_statistic is also frozen. ! * template0 is not frozen here, but data rows were frozen by initdb, ! * and we set its datfrozenxid and relfrozenxids later to match the ! * new xid counter later. */ prep_status(ctx, "Freezing all rows on the new cluster"); exec_prog(ctx, true, *** *** 292,339 set_frozenxids(migratorContext *ctx) { int dbnum; ! PGconn *conn; PGresult *dbres; int ntups; prep_status(ctx, "Setting frozenxid counters in new cluster"); ! conn = connectToServer(ctx, "template1", CLUSTER_NEW); /* set pg_database.datfrozenxid */ ! PQclear(executeQueryOrDie(ctx, conn, "UPDATE pg_catalog.pg_database " ! "SET datfrozenxid = '%u' " ! "WHERE datallowconn = true", ctx->old.controldata.chkpnt_nxtxid)); /* get database names */ ! dbres = executeQueryOrDie(ctx, conn, ! "SELECT datname " ! "FROM pg_catalog.pg_database " ! "WHERE datallowconn = true"); ! /* free dbres below */ ! PQfinish(conn); ntups = PQntuples(dbres); for (dbnum = 0; dbnum < ntups; dbnum++) { ! conn = connectToServer(ctx, PQgetvalue(dbres, dbnum, 0), CLUSTER_NEW); /* set pg_class.relfrozenxid */ PQclear(executeQueryOrDie(ctx, conn, "UPDATE pg_catalog.pg_class " "SET relfrozenxid = '%u' " /* only heap and TOAST are vacuumed */ ! "WHERE relkind = 'r' OR " ! " relkind = 't'", ctx->old.controldata.chkpnt_nxtxid)); PQfinish(conn); } PQclear(dbres); check_ok(ctx); } --- 295,366 set_frozenxids(migratorContext *ctx) { int dbnum; ! PGconn *conn, *conn_template1; PGresult *dbres; int ntups; + int i_datname; + int i_datallowconn; prep_status(ctx, "Setting frozenxid counters in new cluster"); ! conn_template1 = connectToServer(ctx, "template1", CLUSTER_NEW); /* set pg_database.datfrozenxid */ ! PQclear(executeQueryOrDie(ctx, conn_template1, "UPDATE pg_catalog.pg_database " ! "SET datfrozenxid = '%u'", ctx->old.controldata.chkpnt_nxtxid)); /* get database names */ ! dbres = executeQueryOrDie(ctx, conn_template1, ! "SELECT datname, datallowconn " ! "FROM pg_catalog.pg_database"); !
Re: [HACKERS] C function argument types
Bogdan Vlad writes: > How can I determine the pg_class oid of the argument type in a > polymorphic C function when it's called with a table row? You're confusing pg_class oid with pg_type oid. The type oid of the function argument is necessarily going to be a *type* oid. You can look at the pg_type row to find the associated pg_class oid, if it's a composite type (it might not be!) 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] Building the 64-bit Postgres
"BRUSSER Michael" writes: > I looked at the release notes but could not figure out at which point > Postgres gave the option of building the 64-bit binaries. Quite a long time ago. Any reasonably current release should be OK. > One feature that we'll be missing terribly is the client TCL api, if I'm > correct it was dropped back in v 7.4 It's not part of the core distribution anymore, but you can still get it from pgfoundry. 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] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 1:31 pm, Tom Lane wrote: > BTW, standard_conforming_strings is really a different case because of > the SQL-injection security hazards with non-scs-aware client code. > I don't see any comparable risk for bytea format. > Yeah, and the impact of this will be much more limited. I'd want quite a bit of convincing to agree that we shouldn't turn it on right away. 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
[HACKERS] C function argument types
Hello How can I determine the pg_class oid of the argument type in a polymorphic C function when it's called with a table row? PG_FUNCTION_INFO_V1(myfunc); Datum myfunc(PG_FUNCTION_ARGS) { Oid arg_type = get_fn_expr_argtype(fcinfo->flinfo, 0); elog(ERROR, "arg_type %d", arg_type); PG_RETURN_NULL(); } select myfunc(mytable) from mytable; arg_type 65754 select 'mytable'::regclass::oid 65752 I expected them to be equal. What gives? For other table the results are 65783 vs 65785. I'm running v 8.4.3 Thanks, Bogdan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Building the 64-bit Postgres
We adding support for Solaris x86 and this pushes us to upgrade the from the old-old version of Postgres we've been using for years. (The requirement is to have the 64-bit exec and libs) I looked at the release notes but could not figure out at which point Postgres gave the option of building the 64-bit binaries. Notes for Rel. 8.2 read "Add support for Solaris x86_64 using the Solaris compiler" Is this only about being able to compile on this platform, or actually build the 64-bit app? Release notes for 8.4 suggest that it can be the 64-bit build: "Make version() return information about whether the server is a 32- or 64-bit binary" It may sound stupid, but there's a number of factors here, and we may not be able to upgrade to the very latest version, hence the question... One feature that we'll be missing terribly is the client TCL api, if I'm correct it was dropped back in v 7.4 Did anyone have a good experience with either pgtcl or pgtcl-ng from pgFoundry? Which one would you recommend? We are building on Solaris, Sol-x86 and Linux, if this matters. Thank you in advance, Michael. This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged. If you are not one of the named recipients or have received this email in error, (i) you should not read, disclose, or copy it, (ii) please notify sender of your receipt by reply email and delete this email and all attachments, (iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.For other languages, go to http://www.3ds.com/terms/email-disclaimer.
Re: [HACKERS] BYTEA / DBD::Pg change in 9.0 beta
Magnus Hagander writes: > On Wed, May 19, 2010 at 11:11 AM, Robert Haas wrote: >> Yeah, that's what I'm worried about. I remember going through this >> with E'' quoting. It wasn't fun. > Right. So do we know what the policy is? As long as DBD::Pg is > released before pg 9.0 we'd be fine, *provided* that they > (redhat/novell/debian/whatever) actually pull in the latest version at > that point... Well, as far as Red Hat goes, I'll make a point of not shipping 9.0 before DBD::Pg is updated. I'm not sure how tense we need to be about this, though, considering that users can easily turn off the option if they need to run clients with old drivers. BTW, standard_conforming_strings is really a different case because of the SQL-injection security hazards with non-scs-aware client code. I don't see any comparable risk for bytea format. 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] pg_upgrade - link mode and transaction-wraparound data loss
On 2010-05-18 18:57, Bruce Momjian wrote: jes...@krogh.cc wrote: Hi I tried running pg_upgrade from the current snapshot of postgresql and upgrading from 8.4.4 to the snapshot version. Everything seem to look fine in the process and all that came out was only "ok's" but when I tried a simple query on the databse it keeps throwing these message out of the back side. DETAIL: You might have already suffered transaction-wraparound data loss. WARNING: some databases have not been vacuumed in over 2 billion transactions The database was around 600GB and it took a couple of minutes to run pg_upgrade after I had all the binaries in the correct place. It is not really an easy task to throw around 600GB of data, so I cannot gaurantee that the above is reproducible, but I'll see if I can get time and try to reproduce it. This certainly should never have happened, so I am guessing it is a bug. pg_upgrade tries hard to make sure all your datfrozenxid and relfrozenxid are properly migrated from the old server, and the transaction id is set properly. Unfortunately this is the first time I have heard of such a problem, so I am unclear on its cause. Other people are typically way faster than I am looking into it. Depesz has produced a full trace to reproduce the problem here: http://www.depesz.com/index.php/2010/05/19/waiting-for-9-0-pg_upgrade/ Jesper -- Jesper -- 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] BYTEA / DBD::Pg change in 9.0 beta
On May 19, 2010, at 18:32 , Robert Haas wrote: > On Wed, May 19, 2010 at 12:16 PM, Stefan Kaltenbrunner > wrote: >>> I think it just depends on whether we're likely to get releases from >>> Linux vendors that include PG 9.0 but not the updated drivers. I'm >>> not sure their schedule will be affected by whether we call it 8.5 or >>> 9.0. >> >> that's a fair point (like I expect debian to provide 9.0 as a backport) >> though the packages could just change the default for that backport. >> The precedence for that is standard_conforming_strings which we now have >> for a while(since 8.2 iirc) - though I don't think we have a firm plan >> on when we are actually going to turn it on... >> Not sure if we really need to wait 4 major releases to allow driver >> developers to adapt... >> So one idea would be to turn it off for 9.1 and enable that and scs for >> 9.1 and try to get driver developers attention early in the release cycle. > > I think we previously discussed flipping standard_conforming_strings > at the beginning of the 9.1 cycle, and I'm still OK with that. Not > sure it bears on the present issue, though. Well, since both issues are related in that they deal with data representation and force driver upgrades and/or reviewing and testing of application code to ensure correct encoding and decoding, flipping both defaults simultaneously might reduce the overall effort required. If 9.0 ships with the new bytea encoding enabled by default, people will need to adapt applications for 9.0 to deal with bytea issues and then again for 9.1 to deal with string encoding issues. Since updated drivers can choose to override the default on a per-connection basis if they're ready to deal with the new representation, flipping the default doesn't have much of a performance advantage either. So +1 for flipping both with the release of 9.1, and warning people well ahead of time. Maybe there could even be a warning in the 9.0 release notes about the scheduled change? best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BYTEA / DBD::Pg change in 9.0 beta
On 05/19/2010 12:32 PM, Robert Haas wrote: > On Wed, May 19, 2010 at 12:16 PM, Stefan Kaltenbrunner > wrote: >>> I think it just depends on whether we're likely to get releases from >>> Linux vendors that include PG 9.0 but not the updated drivers. I'm >>> not sure their schedule will be affected by whether we call it 8.5 or >>> 9.0. >> >> that's a fair point (like I expect debian to provide 9.0 as a backport) >> though the packages could just change the default for that backport. >> The precedence for that is standard_conforming_strings which we now have >> for a while(since 8.2 iirc) - though I don't think we have a firm plan >> on when we are actually going to turn it on... >> Not sure if we really need to wait 4 major releases to allow driver >> developers to adapt... >> So one idea would be to turn it off for 9.1 and enable that and scs for >> 9.1 and try to get driver developers attention early in the release cycle. > > I think we previously discussed flipping standard_conforming_strings > at the beginning of the 9.1 cycle, and I'm still OK with that. Not > sure it bears on the present issue, though. well we might want to get a bit more formal with deprecating things now that we have an official EOL policy for the server. Maybe we should consider adding stuff like scs, bytea output format,add_missing_from there as well with a depcreation & removal/cnage notice. On the other side we tend to break drivers with other stuff in almost every release in some way or another anyway (iirc we broke JDBC in 9.0 already) so maybe that is a mood point. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 12:16 PM, Stefan Kaltenbrunner wrote: >> I think it just depends on whether we're likely to get releases from >> Linux vendors that include PG 9.0 but not the updated drivers. I'm >> not sure their schedule will be affected by whether we call it 8.5 or >> 9.0. > > that's a fair point (like I expect debian to provide 9.0 as a backport) > though the packages could just change the default for that backport. > The precedence for that is standard_conforming_strings which we now have > for a while(since 8.2 iirc) - though I don't think we have a firm plan > on when we are actually going to turn it on... > Not sure if we really need to wait 4 major releases to allow driver > developers to adapt... > So one idea would be to turn it off for 9.1 and enable that and scs for > 9.1 and try to get driver developers attention early in the release cycle. I think we previously discussed flipping standard_conforming_strings at the beginning of the 9.1 cycle, and I'm still OK with that. Not sure it bears on the present issue, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] BYTEA / DBD::Pg change in 9.0 beta
On 05/19/2010 11:19 AM, Magnus Hagander wrote: > On Wed, May 19, 2010 at 11:11 AM, Robert Haas wrote: >> On Wed, May 19, 2010 at 11:07 AM, Magnus Hagander >> wrote: >>> On Wed, May 19, 2010 at 11:06 AM, Greg Sabino Mullane >>> wrote: >> given how much faster the new format is (or rather how slow the old one >> was) and the number of people I have seen complaining "why is bytea so >> slow) I would like to see it staying turned on by default. However this >> also depends on how quickly database driver developers can adapt. DBD::Pg is already patched, and will very likely be released before 9.0 >>> >>> How do the distros generaly deal with that? E.g. do we have to wait >>> for RHEL7 for it to actually show up in redhat? >> >> Yeah, that's what I'm worried about. I remember going through this >> with E'' quoting. It wasn't fun. > > Right. So do we know what the policy is? As long as DBD::Pg is > released before pg 9.0 we'd be fine, *provided* that they > (redhat/novell/debian/whatever) actually pull in the latest version at > that point... well the next debian release (squeeze) is likely to end up with 8.4 anyway, same for RHEL6 I believe. so I don't think we really have a "problem" there. It might actually be not to bad a time to break compatibility because there is a long time for distros to catch up after their next releases. For debian 9.0 will likely show up in backports but i would expect them to provide a backport of the relevant drivers as well (or change the default for the backport). Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BYTEA / DBD::Pg change in 9.0 beta
On 05/19/2010 11:45 AM, Robert Haas wrote: > On Wed, May 19, 2010 at 11:31 AM, Alex Hunsaker wrote: >> On Wed, May 19, 2010 at 09:05, Robert Haas wrote: >>> On Wed, May 19, 2010 at 11:00 AM, Kenneth Marshall wrote: Changing something like that within the minor release arc is not a good idea. It would be better to have it on by default and if the driver developers are not up to use it, they can have that as a setting that they will need to change when going to 9.0. I would be very upset to have a minor upgrade break my database. At least the major upgrades have more testing. >>> >>> I meant, wait for the next MAJOR release to turn it on by default. >>> Changing it in a minor release is clearly a bad idea. >> >> I think with this release already being clearly marked as a bit more >> than the usual major release (9.0 vs 8.5), we can get away with it >> leaving the default the way it is. > > I think it just depends on whether we're likely to get releases from > Linux vendors that include PG 9.0 but not the updated drivers. I'm > not sure their schedule will be affected by whether we call it 8.5 or > 9.0. that's a fair point (like I expect debian to provide 9.0 as a backport) though the packages could just change the default for that backport. The precedence for that is standard_conforming_strings which we now have for a while(since 8.2 iirc) - though I don't think we have a firm plan on when we are actually going to turn it on... Not sure if we really need to wait 4 major releases to allow driver developers to adapt... So one idea would be to turn it off for 9.1 and enable that and scs for 9.1 and try to get driver developers attention early in the release cycle. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 11:31 AM, Alex Hunsaker wrote: > On Wed, May 19, 2010 at 09:05, Robert Haas wrote: >> On Wed, May 19, 2010 at 11:00 AM, Kenneth Marshall wrote: >>> Changing something like that within the minor release arc is >>> not a good idea. It would be better to have it on by default and >>> if the driver developers are not up to use it, they can have that >>> as a setting that they will need to change when going to 9.0. I >>> would be very upset to have a minor upgrade break my database. At >>> least the major upgrades have more testing. >> >> I meant, wait for the next MAJOR release to turn it on by default. >> Changing it in a minor release is clearly a bad idea. > > I think with this release already being clearly marked as a bit more > than the usual major release (9.0 vs 8.5), we can get away with it > leaving the default the way it is. I think it just depends on whether we're likely to get releases from Linux vendors that include PG 9.0 but not the updated drivers. I'm not sure their schedule will be affected by whether we call it 8.5 or 9.0. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 09:05, Robert Haas wrote: > On Wed, May 19, 2010 at 11:00 AM, Kenneth Marshall wrote: >> Changing something like that within the minor release arc is >> not a good idea. It would be better to have it on by default and >> if the driver developers are not up to use it, they can have that >> as a setting that they will need to change when going to 9.0. I >> would be very upset to have a minor upgrade break my database. At >> least the major upgrades have more testing. > > I meant, wait for the next MAJOR release to turn it on by default. > Changing it in a minor release is clearly a bad idea. I think with this release already being clearly marked as a bit more than the usual major release (9.0 vs 8.5), we can get away with it leaving the default the way it is. -- 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] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 11:11 AM, Robert Haas wrote: > On Wed, May 19, 2010 at 11:07 AM, Magnus Hagander wrote: >> On Wed, May 19, 2010 at 11:06 AM, Greg Sabino Mullane >> wrote: >>> > given how much faster the new format is (or rather how slow the old one > was) and the number of people I have seen complaining "why is bytea so > slow) I would like to see it staying turned on by default. However this > also depends on how quickly database driver developers can adapt. >>> >>> DBD::Pg is already patched, and will very likely be released before 9.0 >> >> How do the distros generaly deal with that? E.g. do we have to wait >> for RHEL7 for it to actually show up in redhat? > > Yeah, that's what I'm worried about. I remember going through this > with E'' quoting. It wasn't fun. Right. So do we know what the policy is? As long as DBD::Pg is released before pg 9.0 we'd be fine, *provided* that they (redhat/novell/debian/whatever) actually pull in the latest version at that point... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BYTEA / DBD::Pg change in 9.0 beta
* Magnus Hagander [100519 11:08]: > How do the distros generaly deal with that? E.g. do we have to wait > for RHEL7 for it to actually show up in redhat? Don't worry, 9.0 won't show up in redhat for a while yet either... ;-) -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 11:07 AM, Magnus Hagander wrote: > On Wed, May 19, 2010 at 11:06 AM, Greg Sabino Mullane > wrote: >> given how much faster the new format is (or rather how slow the old one was) and the number of people I have seen complaining "why is bytea so slow) I would like to see it staying turned on by default. However this also depends on how quickly database driver developers can adapt. >> >> DBD::Pg is already patched, and will very likely be released before 9.0 > > How do the distros generaly deal with that? E.g. do we have to wait > for RHEL7 for it to actually show up in redhat? Yeah, that's what I'm worried about. I remember going through this with E'' quoting. It wasn't fun. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 11:06 AM, Greg Sabino Mullane wrote: > >>> given how much faster the new format is (or rather how slow the old one >>> was) and the number of people I have seen complaining "why is bytea so >>> slow) I would like to see it staying turned on by default. However this >>> also depends on how quickly database driver developers can adapt. > > DBD::Pg is already patched, and will very likely be released before 9.0 How do the distros generaly deal with that? E.g. do we have to wait for RHEL7 for it to actually show up in redhat? -- 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] BYTEA / DBD::Pg change in 9.0 beta
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >> given how much faster the new format is (or rather how slow the old one >> was) and the number of people I have seen complaining "why is bytea so >> slow) I would like to see it staying turned on by default. However this >> also depends on how quickly database driver developers can adapt. DBD::Pg is already patched, and will very likely be released before 9.0 - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005191105 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkvz/mgACgkQvJuQZxSWSsiQ+ACg5B61+bJ4fNaJI8kTNIjyV2lS Y0IAnR9tB86upmY5JufsVvcithHOUtjP =rgH4 -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] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 11:00 AM, Kenneth Marshall wrote: > On Wed, May 19, 2010 at 10:54:01AM -0400, Robert Haas wrote: >> On Wed, May 19, 2010 at 10:17 AM, Stefan Kaltenbrunner >> wrote: >> > On 05/19/2010 08:13 AM, Tom Lane wrote: >> >> Bernd Helmle writes: >> >>> --On 18. Mai 2010 23:20:26 +0200 Jesper Krogh wrote: >> May I ask whats the reason is for "breaking" the compatibillity? >> >> >> >>> "Efficency", if i am allowed to call it this way. The new hex >> >>> representation should be more efficient to retrieve and to handle than >> >>> the >> >>> old one. I think bytea_output was set to hex for testing purposes on the >> >>> first hand, but not sure wether there was a consensus to leave it there >> >>> finally later. >> >> >> >> Yeah, we intentionally set it that way initially to help find stuff that >> >> needs to be updated (as DBD::Pg evidently does). ?It's still TBD whether >> >> 9.0.0 will ship with that default or not. >> > >> > given how much faster the new format is (or rather how slow the old one >> > was) and the number of people I have seen complaining "why is bytea so >> > slow) I would like to see it staying turned on by default. However this >> > also depends on how quickly database driver developers can adapt. >> >> I would favor waiting a release to turn it on by default, precisely to >> give driver developers time to adapt. >> > Changing something like that within the minor release arc is > not a good idea. It would be better to have it on by default and > if the driver developers are not up to use it, they can have that > as a setting that they will need to change when going to 9.0. I > would be very upset to have a minor upgrade break my database. At > least the major upgrades have more testing. I meant, wait for the next MAJOR release to turn it on by default. Changing it in a minor release is clearly a bad idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 10:54:01AM -0400, Robert Haas wrote: > On Wed, May 19, 2010 at 10:17 AM, Stefan Kaltenbrunner > wrote: > > On 05/19/2010 08:13 AM, Tom Lane wrote: > >> Bernd Helmle writes: > >>> --On 18. Mai 2010 23:20:26 +0200 Jesper Krogh wrote: > May I ask whats the reason is for "breaking" the compatibillity? > >> > >>> "Efficency", if i am allowed to call it this way. The new hex > >>> representation should be more efficient to retrieve and to handle than the > >>> old one. I think bytea_output was set to hex for testing purposes on the > >>> first hand, but not sure wether there was a consensus to leave it there > >>> finally later. > >> > >> Yeah, we intentionally set it that way initially to help find stuff that > >> needs to be updated (as DBD::Pg evidently does). ?It's still TBD whether > >> 9.0.0 will ship with that default or not. > > > > given how much faster the new format is (or rather how slow the old one > > was) and the number of people I have seen complaining "why is bytea so > > slow) I would like to see it staying turned on by default. However this > > also depends on how quickly database driver developers can adapt. > > I would favor waiting a release to turn it on by default, precisely to > give driver developers time to adapt. > Changing something like that within the minor release arc is not a good idea. It would be better to have it on by default and if the driver developers are not up to use it, they can have that as a setting that they will need to change when going to 9.0. I would be very upset to have a minor upgrade break my database. At least the major upgrades have more testing. Regards, Ken -- 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] BYTEA / DBD::Pg change in 9.0 beta
On Wed, May 19, 2010 at 10:17 AM, Stefan Kaltenbrunner wrote: > On 05/19/2010 08:13 AM, Tom Lane wrote: >> Bernd Helmle writes: >>> --On 18. Mai 2010 23:20:26 +0200 Jesper Krogh wrote: May I ask whats the reason is for "breaking" the compatibillity? >> >>> "Efficency", if i am allowed to call it this way. The new hex >>> representation should be more efficient to retrieve and to handle than the >>> old one. I think bytea_output was set to hex for testing purposes on the >>> first hand, but not sure wether there was a consensus to leave it there >>> finally later. >> >> Yeah, we intentionally set it that way initially to help find stuff that >> needs to be updated (as DBD::Pg evidently does). It's still TBD whether >> 9.0.0 will ship with that default or not. > > given how much faster the new format is (or rather how slow the old one > was) and the number of people I have seen complaining "why is bytea so > slow) I would like to see it staying turned on by default. However this > also depends on how quickly database driver developers can adapt. I would favor waiting a release to turn it on by default, precisely to give driver developers time to adapt. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] BYTEA / DBD::Pg change in 9.0 beta
On 05/19/2010 08:13 AM, Tom Lane wrote: > Bernd Helmle writes: >> --On 18. Mai 2010 23:20:26 +0200 Jesper Krogh wrote: >>> May I ask whats the reason is for "breaking" the compatibillity? > >> "Efficency", if i am allowed to call it this way. The new hex >> representation should be more efficient to retrieve and to handle than the >> old one. I think bytea_output was set to hex for testing purposes on the >> first hand, but not sure wether there was a consensus to leave it there >> finally later. > > Yeah, we intentionally set it that way initially to help find stuff that > needs to be updated (as DBD::Pg evidently does). It's still TBD whether > 9.0.0 will ship with that default or not. given how much faster the new format is (or rather how slow the old one was) and the number of people I have seen complaining "why is bytea so slow) I would like to see it staying turned on by default. However this also depends on how quickly database driver developers can adapt. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Stefan's bug (was: max_standby_delay considered harmful)
On Wed, May 19, 2010 at 8:49 AM, Simon Riggs wrote: > On Wed, 2010-05-19 at 08:21 -0400, Tom Lane wrote: >> Robert Haas writes: >> > On Wed, May 19, 2010 at 1:47 AM, Fujii Masao wrote: >> >> Yes, but I prefer XLogCtl->SharedRecoveryInProgress, which is the almost >> >> same indicator as the boolean you suggested. Thought? >> >> > It feels cleaner and simpler to me to use the information that the >> > postmaster already collects rather than having it take locks and check >> > shared memory, but I might be wrong. Why do you prefer doing it that >> > way? >> >> The postmaster must absolutely not take locks (once there are competing >> processes). This is non negotiable from a system robustness standpoint. > > Masao has not proposed this, in fact his proposal was to deliberately > avoid do so. > > I proposed using the state recorded in xlog.c rather than attempting to > duplicate that with a second boolean in postmaster because that seems > likely to be more buggy. Well then how are we reading XLogCtl? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Synchronous replication patch built on SR
Fujii Masao írta: > On Wed, May 19, 2010 at 5:41 PM, Boszormenyi Zoltan wrote: > >>> Isn't reading the same WAL twice (by walreceiver and startup process) >>> inefficient? >>> >> Yes, and I didn't implement that because it's inefficient. >> > > So I'd like to propose to use LSN instead of XID since LSN can > be easily handled by both walreceiver and startup process. > OK, I will look into it replacing XIDs with LSNs. >>> Currently >>> PQputCopyData() cannot be executed in COPY OUT, but we can relax >>> that. >>> >>> >> And I implemented just that, in a way that upon walreceiver startup >> it sends a new protocol message to the walsender by calling >> PQsetDuplexCopy() (see my patch) and the walsender response is ACK. >> This protocol message is intentionally not handled by the normal >> backend, so plain libpq clients cannot mess up their COPY streams. >> > > The newly-introduced message type "Set Duplex Copy" is really required? > I think that the standby can send its replication mode to the master > via Query or CopyData message, which are already used in SR. For example, > how about including the mode in the handshake message "START_REPLICATION"? > If we do that, we would not need to introduce new libpq function > PQsetDuplexCopy(). BTW, I often got the complaints about adding > new libpq function when I implemented SR ;) > :-) > In the patch, PQputCopyData() checks the newly-introduced pg_conn field > "duplexCopy". Instead, how about checking the existing field "replication"? > I didn't see there was such a new field. (looking...) I can see now, it was added in the middle of the structure. Ok, we can then use it to allow duplex COPY instead of my new field. I suppose it's non-NULL if replication is on, right? Then the extra call is not needed then. > Or we can just allow PQputCopyData() to go even in COPY OUT state. > I think this may not be too useful for SQL clients, but who knows? :-) Use cases, anyone? >> We can change the walreceiver so it sends similarly encapsulated >> messages as the walsender does. In our patch, the walreceiver >> currently sends the raw XIDs. If we add a minimal protocol >> encapsulation, we can distinguish between the XIDs (or later LSNs) >> and the "mark me synchronous from now on" message. >> >> The only problem is: what should be the point when such a client >> becomes synchronous from the master's POV, so the XID/LSN reports >> will count and transactions are made to wait for this client? >> > > One idea is to switch to "sync" when the gap of LSN becomes less > than or equal to XLOG_SEG_SIZE (currently 8MB). That is, walsender > calculates the gap from the current write WAL location on the master > and the last receive/flush/replay location on the standby. And if > the gap <= XLOG_SEG_SIZE, it instructs backends to wait for > replication from then on. > This is a sensible idea. >> As a side note, the async walreceivers' behaviour should be kept >> so they don't send anything back and the message that >> PQsetDuplexCopy() sends to the master would then only >> prepare the walsender that its client will become synchronous >> in the near future. >> > > I agree that walreceiver should send no replication ack if "async" > mode is chosen. OTOH, in "sync" case, walreceiver should always > send ack even if the gap is large and the master doesn't wait for > replication yet. As mentioned above, walsender needs to calculate > the gap from the ack. > Agreed. >>> Seems s/min_sync_replication_clients/max_sync_replication_clients >>> >>> >> No, "min" is indicating the minimum number of walreceiver reports >> needed before a transaction can be released from under the waiting. >> The other reports coming from walreceivers are ignored. >> > > Hmm... when min_sync_replication_clients = 2 and there are three > "synchronous" standbys, the master waits for only two standbys? > Yes. This is the idea, "partially synchronous replication". I heard anecdotes about replication solutions where say ensuring that (say) if at least 50% of the machines across the whole cluster report back synchronously then the transaction is considered replicated "good enough". > The standby which the master ignores is fixed? or dynamically (or > randomly) changed? > It may be randomly changed, depending on who send the reports first. The replication servers themselves may get very busy with large queries or they may be loaded by some other ways and be somewhat late in processing the WAL stream. The less loaded servers answer first, and the transaction is considered properly replicated. >>> min_sync_replication_clients is required to prevent outside attacker >>> from connecting to the master as "synchronous" standby, and degrading >>> the performance on the master? >>> >> ??? >> >> Properly configured pg_hba.conf prevents outside attackers >> to connect as replication clients, no? >> > > Yes :) > > I'd like to just kn
[HACKERS] Adding XML Schema validation (XMLVALIDATE)
Hi, I'm going to start work on another XML todo item: "Add XML Schema validation and xmlvalidate function (SQL:2008)" The standard identifies XMLVALIDATE as: ::= XMLVALIDATE [ ] so I've got something quite clear to work too. libxml has the required support for schema validation so I'll just be wrapping it's functionality much like I did for xpath_exists(). Anyone got any thoughts before I get busy? Thanks, -- Mike Fowler Registered Linux user: 379787 -- 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] Stefan's bug (was: max_standby_delay considered harmful)
On Wed, 2010-05-19 at 08:21 -0400, Tom Lane wrote: > Robert Haas writes: > > On Wed, May 19, 2010 at 1:47 AM, Fujii Masao wrote: > >> Yes, but I prefer XLogCtl->SharedRecoveryInProgress, which is the almost > >> same indicator as the boolean you suggested. Thought? > > > It feels cleaner and simpler to me to use the information that the > > postmaster already collects rather than having it take locks and check > > shared memory, but I might be wrong. Why do you prefer doing it that > > way? > > The postmaster must absolutely not take locks (once there are competing > processes). This is non negotiable from a system robustness standpoint. Masao has not proposed this, in fact his proposal was to deliberately avoid do so. I proposed using the state recorded in xlog.c rather than attempting to duplicate that with a second boolean in postmaster because that seems likely to be more buggy. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Stefan's bug (was: max_standby_delay considered harmful)
Robert Haas writes: > On Wed, May 19, 2010 at 1:47 AM, Fujii Masao wrote: >> Yes, but I prefer XLogCtl->SharedRecoveryInProgress, which is the almost >> same indicator as the boolean you suggested. Thought? > It feels cleaner and simpler to me to use the information that the > postmaster already collects rather than having it take locks and check > shared memory, but I might be wrong. Why do you prefer doing it that > way? The postmaster must absolutely not take locks (once there are competing processes). This is non negotiable from a system robustness standpoint. 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] BYTEA / DBD::Pg change in 9.0 beta
Bernd Helmle writes: > --On 18. Mai 2010 23:20:26 +0200 Jesper Krogh wrote: >> May I ask whats the reason is for "breaking" the compatibillity? > "Efficency", if i am allowed to call it this way. The new hex > representation should be more efficient to retrieve and to handle than the > old one. I think bytea_output was set to hex for testing purposes on the > first hand, but not sure wether there was a consensus to leave it there > finally later. Yeah, we intentionally set it that way initially to help find stuff that needs to be updated (as DBD::Pg evidently does). It's still TBD whether 9.0.0 will ship with that default or not. 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] Stefan's bug (was: max_standby_delay considered harmful)
On Wed, May 19, 2010 at 1:47 AM, Fujii Masao wrote: > On Wed, May 19, 2010 at 12:59 PM, Robert Haas wrote: >> In terms of removing the backup label file, can we simply have an >> additional boolean in the postmaster that indicates whether we've ever >> reached PM_RUN, and only consider removing the backup file if so? > > Yes, but I prefer XLogCtl->SharedRecoveryInProgress, which is the almost > same indicator as the boolean you suggested. Thought? It feels cleaner and simpler to me to use the information that the postmaster already collects rather than having it take locks and check shared memory, but I might be wrong. Why do you prefer doing it that way? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Synchronous replication patch built on SR
On Wed, May 19, 2010 at 5:41 PM, Boszormenyi Zoltan wrote: >> Isn't reading the same WAL twice (by walreceiver and startup process) >> inefficient? > > Yes, and I didn't implement that because it's inefficient. So I'd like to propose to use LSN instead of XID since LSN can be easily handled by both walreceiver and startup process. >> Currently >> PQputCopyData() cannot be executed in COPY OUT, but we can relax >> that. >> > > And I implemented just that, in a way that upon walreceiver startup > it sends a new protocol message to the walsender by calling > PQsetDuplexCopy() (see my patch) and the walsender response is ACK. > This protocol message is intentionally not handled by the normal > backend, so plain libpq clients cannot mess up their COPY streams. The newly-introduced message type "Set Duplex Copy" is really required? I think that the standby can send its replication mode to the master via Query or CopyData message, which are already used in SR. For example, how about including the mode in the handshake message "START_REPLICATION"? If we do that, we would not need to introduce new libpq function PQsetDuplexCopy(). BTW, I often got the complaints about adding new libpq function when I implemented SR ;) In the patch, PQputCopyData() checks the newly-introduced pg_conn field "duplexCopy". Instead, how about checking the existing field "replication"? Or we can just allow PQputCopyData() to go even in COPY OUT state. > We can change the walreceiver so it sends similarly encapsulated > messages as the walsender does. In our patch, the walreceiver > currently sends the raw XIDs. If we add a minimal protocol > encapsulation, we can distinguish between the XIDs (or later LSNs) > and the "mark me synchronous from now on" message. > > The only problem is: what should be the point when such a client > becomes synchronous from the master's POV, so the XID/LSN reports > will count and transactions are made to wait for this client? One idea is to switch to "sync" when the gap of LSN becomes less than or equal to XLOG_SEG_SIZE (currently 8MB). That is, walsender calculates the gap from the current write WAL location on the master and the last receive/flush/replay location on the standby. And if the gap <= XLOG_SEG_SIZE, it instructs backends to wait for replication from then on. > As a side note, the async walreceivers' behaviour should be kept > so they don't send anything back and the message that > PQsetDuplexCopy() sends to the master would then only > prepare the walsender that its client will become synchronous > in the near future. I agree that walreceiver should send no replication ack if "async" mode is chosen. OTOH, in "sync" case, walreceiver should always send ack even if the gap is large and the master doesn't wait for replication yet. As mentioned above, walsender needs to calculate the gap from the ack. >> Seems s/min_sync_replication_clients/max_sync_replication_clients >> > > No, "min" is indicating the minimum number of walreceiver reports > needed before a transaction can be released from under the waiting. > The other reports coming from walreceivers are ignored. Hmm... when min_sync_replication_clients = 2 and there are three "synchronous" standbys, the master waits for only two standbys? The standby which the master ignores is fixed? or dynamically (or randomly) changed? >> min_sync_replication_clients is required to prevent outside attacker >> from connecting to the master as "synchronous" standby, and degrading >> the performance on the master? > > ??? > > Properly configured pg_hba.conf prevents outside attackers > to connect as replication clients, no? Yes :) I'd like to just know the use case of min_sync_replication_clients. Sorry, I've not understood yet how useful this option is. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ecmascript 5 DATESTYLE
2010/5/19 Mike Fowler : > Pavel Stehule wrote: >> >> 2010/5/19 Mike Fowler : >> >>> >>> Pavel Stehule wrote: >>> see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel >>> >>> OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From >>> the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT >>> to >>> an arbitrary string which is then used for the attack, To me this is easy >>> to >>> code against, simply lock the date format right down and ensure that it >>> is >>> always controlled. IMHO I don't see an Oracle specific attack as a reason >>> why we can't have a generic format. Surely we can learn from this known >>> vulnerability and get another one up on Oracle? >>> >> >> I am not a security expert - you can simply don't allow apostrophe, >> double quotes - but I am not sure, if this can be safe - simply - I am >> abe to write this patch, but I am not able to ensure security. >> >> Regards >> Pavel >> > > Well you've rightly identified a potential security hole, so my > recommendation would be to put the patch together bearing in mind the Oracle > vulnerability. Once you've submitted the patch it can be reviewed and we can > ensure that you've managed to steer clear of introducing the same/similar > vulnerability into postgres. > > Am I right in thinking that you're now proposing to do the generic patch > that Robert Haas and I prefer? I'll look on code and I'll see Pavel > > Thanks, > > -- > Mike Fowler > Registered Linux user: 379787 > > -- 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] ecmascript 5 DATESTYLE
Pavel Stehule wrote: 2010/5/19 Mike Fowler : Pavel Stehule wrote: see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to an arbitrary string which is then used for the attack, To me this is easy to code against, simply lock the date format right down and ensure that it is always controlled. IMHO I don't see an Oracle specific attack as a reason why we can't have a generic format. Surely we can learn from this known vulnerability and get another one up on Oracle? I am not a security expert - you can simply don't allow apostrophe, double quotes - but I am not sure, if this can be safe - simply - I am abe to write this patch, but I am not able to ensure security. Regards Pavel Well you've rightly identified a potential security hole, so my recommendation would be to put the patch together bearing in mind the Oracle vulnerability. Once you've submitted the patch it can be reviewed and we can ensure that you've managed to steer clear of introducing the same/similar vulnerability into postgres. Am I right in thinking that you're now proposing to do the generic patch that Robert Haas and I prefer? Thanks, -- Mike Fowler Registered Linux user: 379787 -- 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] ecmascript 5 DATESTYLE
2010/5/19 Mike Fowler : > Pavel Stehule wrote: >> >> see google: lateral sql injection oracle NLS_DATE_FORMAT >> >> I would to like this functionality too - and technically I don't see a >> problem - It's less than 100 lines, but I don't need a new security >> problem. So my proposal is change nothing on this integrated >> functionality and add new custom date type - like cdate that can be >> customized via GUC. >> >> Regards >> Pavel > > OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From > the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to > an arbitrary string which is then used for the attack, To me this is easy to > code against, simply lock the date format right down and ensure that it is > always controlled. IMHO I don't see an Oracle specific attack as a reason > why we can't have a generic format. Surely we can learn from this known > vulnerability and get another one up on Oracle? I am not a security expert - you can simply don't allow apostrophe, double quotes - but I am not sure, if this can be safe - simply - I am abe to write this patch, but I am not able to ensure security. Regards Pavel > > Thanks, > > -- > Mike Fowler > Registered Linux user: 379787 > > "I could be a genius if I just put my mind to it, and I, > I could do anything, if only I could get 'round to it" > -PULP 'Glory Days' > > -- 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] ecmascript 5 DATESTYLE
Pavel Stehule wrote: see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to an arbitrary string which is then used for the attack, To me this is easy to code against, simply lock the date format right down and ensure that it is always controlled. IMHO I don't see an Oracle specific attack as a reason why we can't have a generic format. Surely we can learn from this known vulnerability and get another one up on Oracle? Thanks, -- Mike Fowler Registered Linux user: 379787 "I could be a genius if I just put my mind to it, and I, I could do anything, if only I could get 'round to it" -PULP 'Glory Days' -- 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] ecmascript 5 DATESTYLE
2010/5/19 Mike Fowler : > Pavel Stehule wrote: >> >> 2010/5/19 Peter Eisentraut : >> >>> >>> On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote: >>> ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. >>> >>> Note that we haven't got any other datestyles that are intended to >>> support interoperability with some language. It is usually the job of >>> the client driver to convert PostgreSQL data (plural of datum) to the >>> appropriate type and format for the client environment or language. Is >>> there any reason why JavaScript would be different? >>> > > I wouldn't be keen to see dedicated language specific handling of > date/datetime formats. It would lead to an explosion of functions with new > languages needing adding as and when their users jumped up and down on us. > However a generic format could be very useful and would give the opportunity > for people who need a language specific short cut the opportunity to do a > CREATE FUNCTION wrapping the generic one with a hard coded format specifier. > > Other platforms have generic support for this kind of task, for example > SQLServer: http://msdn.microsoft.com/en-us/library/ms187928.aspx. I wouldn't > recommend the SQLServer way, I think numeric format specifiers are clumsy. > Perhaps a mechanism like Java which is nicely summarized here: > http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html > > Pavel: Why do you believe a generic format function would lead to SQL > injections attacks? see google: lateral sql injection oracle NLS_DATE_FORMAT I would to like this functionality too - and technically I don't see a problem - It's less than 100 lines, but I don't need a new security problem. So my proposal is change nothing on this integrated functionality and add new custom date type - like cdate that can be customized via GUC. Regards Pavel > >> JavaScript isn't special language, but JSON is wide used format for >> interoperability. And same is true for XML datestyle format. >> >> Regards >> Pavel >> > > I think that the postgres handling of those data types should handle the > date encoding themselves. For example, a XMLELEMENT call that was passed a > date would format the date string to the xs:date format (e.g. 2010-05-19) > and when passed a timestamp format to xs:datetime (e.g. > 2010-05-19T09:29:52+01:00). I would see the JSON handling as being no > different. > > Thanks, > > -- > Mike Fowler > Registered Linux user: 379787 > > "I could be a genius if I just put my mind to it, and I, > I could do anything, if only I could get 'round to it" > -PULP 'Glory Days' > > -- 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] Synchronous replication patch built on SR
Fujii Masao írta: > Thanks for your reply! > > On Fri, May 14, 2010 at 10:33 PM, Boszormenyi Zoltan wrote: > >>> In your design, the transaction commit on the master waits for its XID >>> to be read from the XLOG_XACT_COMMIT record and replied by the standby. >>> Right? This design seems not to be extensible to #2 and #3 since >>> walreceiver cannot read XID from the XLOG_XACT_COMMIT record. >>> >> Yes, this was my problem, too. I would have had to >> implement a custom interpreter into walreceiver to >> process the WAL records and extract the XIDs. >> > > Isn't reading the same WAL twice (by walreceiver and startup process) > inefficient? Yes, and I didn't implement that because it's inefficient. I implemented a minimal communication between StartupXLOG() and the walreceiver. > In synchronous replication, the overhead of walreceiver > directly affects the performance of the master. We should not assign > such a hard work to walreceiver, I think. > Exactly. >> But at least the supporting details, i.e. not opening another >> connection, instead being able to do duplex COPY operations in >> a server-acknowledged way is acceptable, no? :-) >> > > Though I might not understand your point (sorry), it's OK for the standby > to send the reply to the master by using CopyData message. I thought about the same. > Currently > PQputCopyData() cannot be executed in COPY OUT, but we can relax > that. > And I implemented just that, in a way that upon walreceiver startup it sends a new protocol message to the walsender by calling PQsetDuplexCopy() (see my patch) and the walsender response is ACK. This protocol message is intentionally not handled by the normal backend, so plain libpq clients cannot mess up their COPY streams. >>> How about >>> using LSN instead of XID? That is, the transaction commit waits until >>> the standby has reached its LSN. LSN is more easy-used for walreceiver >>> and startup process, I think. >>> >>> >> Indeed, using the LSN seems to be more appropriate for >> the walreceiver, but how would you extract the information >> that a certain LSN means a COMMITted transaction? Or >> we could release a locked transaction in case the master receives >> an LSN greater than or equal to the transaction's own LSN? >> > > Yep, we can ensure that the transaction has been replicated by > comparing its own LSN with the smallest LSN in the latest LSNs > of each connected "synchronous" standby. > > >> Sending back all the LSNs in case of long transactions would >> increase the network traffic compared to sending back only the >> XIDs, but the amount is not clear for me. What I am more >> worried about is the contention on the ProcArrayLock. >> XIDs are rarer then LSNs, no? >> > > No. For example, when WAL data sent by walsender at a time > has two XLOG_XACT_COMMIT records, in XID approach, walreceiver > would need to send two replies. OTOH, in LSN approach, only > one reply which indicates the last received location would > need to be sent. > I see. >>> What if the "synchronous" standby starts up from the very old backup? >>> The transaction on the master needs to wait until a large amount of >>> outstanding WAL has been applied? I think that synchronous replication >>> should start with *asynchronous* replication, and should switch to the >>> sync level after the gap between servers has become enough small. >>> What's your opinion? >>> >>> >> It's certainly one option, which I think partly addressed >> with the "strict_sync_replication" knob below. >> If strict_sync_replication = off, then the master doesn't make >> its transactions wait for the synchronous reports, and the client(s) >> can work through their WALs. IIRC, the walreceiver connects >> to the master only very late in the recovery process, no? >> > > No, the master might have a large number of WAL files which > the standby doesn't have. > We can change the walreceiver so it sends similarly encapsulated messages as the walsender does. In our patch, the walreceiver currently sends the raw XIDs. If we add a minimal protocol encapsulation, we can distinguish between the XIDs (or later LSNs) and the "mark me synchronous from now on" message. The only problem is: what should be the point when such a client becomes synchronous from the master's POV, so the XID/LSN reports will count and transactions are made to wait for this client? As a side note, the async walreceivers' behaviour should be kept so they don't send anything back and the message that PQsetDuplexCopy() sends to the master would then only prepare the walsender that its client will become synchronous in the near future. I have added 3 new options, two GUCs in postgresql.conf and one setting in recovery.conf. These options are: 1. min_sync_replication_clients = N where N is the number of reports for a given transaction before it's released as committed synchronously. 0 means comple
Re: [HACKERS] ecmascript 5 DATESTYLE
Pavel Stehule wrote: 2010/5/19 Peter Eisentraut : On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote: ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. Note that we haven't got any other datestyles that are intended to support interoperability with some language. It is usually the job of the client driver to convert PostgreSQL data (plural of datum) to the appropriate type and format for the client environment or language. Is there any reason why JavaScript would be different? I wouldn't be keen to see dedicated language specific handling of date/datetime formats. It would lead to an explosion of functions with new languages needing adding as and when their users jumped up and down on us. However a generic format could be very useful and would give the opportunity for people who need a language specific short cut the opportunity to do a CREATE FUNCTION wrapping the generic one with a hard coded format specifier. Other platforms have generic support for this kind of task, for example SQLServer: http://msdn.microsoft.com/en-us/library/ms187928.aspx. I wouldn't recommend the SQLServer way, I think numeric format specifiers are clumsy. Perhaps a mechanism like Java which is nicely summarized here: http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html Pavel: Why do you believe a generic format function would lead to SQL injections attacks? JavaScript isn't special language, but JSON is wide used format for interoperability. And same is true for XML datestyle format. Regards Pavel I think that the postgres handling of those data types should handle the date encoding themselves. For example, a XMLELEMENT call that was passed a date would format the date string to the xs:date format (e.g. 2010-05-19) and when passed a timestamp format to xs:datetime (e.g. 2010-05-19T09:29:52+01:00). I would see the JSON handling as being no different. Thanks, -- Mike Fowler Registered Linux user: 379787 "I could be a genius if I just put my mind to it, and I, I could do anything, if only I could get 'round to it" -PULP 'Glory Days' -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers