[HACKERS] Renaming '2010-Next' to '2010-6' in the commitfest app

2010-05-19 Thread Selena Deckelmann
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

2010-05-19 Thread Fujii Masao
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

2010-05-19 Thread Joel Jacobson
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)

2010-05-19 Thread Fujii Masao
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

2010-05-19 Thread Bruce Momjian
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

2010-05-19 Thread Andres Freund
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

2010-05-19 Thread Bruce Momjian
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

2010-05-19 Thread Bruce Momjian
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

2010-05-19 Thread Alvaro Herrera
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

2010-05-19 Thread Stefan Kaltenbrunner
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.

2010-05-19 Thread Jesper Krogh

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

2010-05-19 Thread Bruce Momjian
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

2010-05-19 Thread Bruce Momjian
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

2010-05-19 Thread Tom Lane
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

2010-05-19 Thread Tom Lane
"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

2010-05-19 Thread Andrew Dunstan
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

2010-05-19 Thread Bogdan Vlad
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

2010-05-19 Thread BRUSSER Michael
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

2010-05-19 Thread Tom Lane
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

2010-05-19 Thread Jesper Krogh

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

2010-05-19 Thread Florian Pflug
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

2010-05-19 Thread Stefan Kaltenbrunner
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

2010-05-19 Thread Robert Haas
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

2010-05-19 Thread Stefan Kaltenbrunner
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

2010-05-19 Thread Stefan Kaltenbrunner
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

2010-05-19 Thread Robert Haas
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

2010-05-19 Thread Alex Hunsaker
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

2010-05-19 Thread Magnus Hagander
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

2010-05-19 Thread Aidan Van Dyk
* 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

2010-05-19 Thread Robert Haas
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

2010-05-19 Thread Magnus Hagander
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

2010-05-19 Thread Greg Sabino Mullane

-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

2010-05-19 Thread Robert Haas
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

2010-05-19 Thread Kenneth Marshall
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

2010-05-19 Thread Robert Haas
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

2010-05-19 Thread Stefan Kaltenbrunner
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)

2010-05-19 Thread Robert Haas
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

2010-05-19 Thread Boszormenyi Zoltan
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)

2010-05-19 Thread Mike Fowler

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)

2010-05-19 Thread Simon Riggs
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)

2010-05-19 Thread Tom Lane
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

2010-05-19 Thread Tom Lane
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)

2010-05-19 Thread Robert Haas
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

2010-05-19 Thread Fujii Masao
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-05-19 Thread Pavel Stehule
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

2010-05-19 Thread 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?


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-05-19 Thread Pavel Stehule
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

2010-05-19 Thread 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?


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-05-19 Thread Pavel Stehule
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

2010-05-19 Thread Boszormenyi Zoltan
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

2010-05-19 Thread 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?



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