Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-13 Thread Jeff Davis
On Tue, 2012-11-27 at 14:24 -0800, Jeff Davis wrote: > On Tue, 2012-11-27 at 15:41 -0500, Robert Haas wrote: > > I can't quite see how a non-overloaded flag would work, unless we get > > rid of schemas. > > It may work to pick the first schema in the search path that has any > functions by that na

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-13 Thread Jeff Davis
On Tue, 2012-11-06 at 10:57 -0500, Robert Haas wrote: > I did some experimentation with this. It seems that what Tom proposed > here is a lot cleaner than what I proposed previously, while still > increasing usability in many real-world cases. For example, in > unpatched master: It looks like we

Re: [HACKERS] Multiple --table options for other commands

2012-12-13 Thread Josh Kupershmidt
On Thu, Dec 13, 2012 at 9:03 PM, Karl O. Pinc wrote: > My brain seems to have turned itself on. I went and (re)read > the docbook manual and was able to come up with this, > which works: > > > > >--table >-t > > table > > Yay! (indentation et-al aside) That

Re: [HACKERS] Doc patch, index search_path where it's used to secure functions

2012-12-13 Thread Peter Eisentraut
On Wed, 2012-11-14 at 00:21 -0600, Karl O. Pinc wrote: > I'm going to send this in as a single patch > that fixes all the search path related > index entries: > > search_path-index.patch > > (replaces search_path-normalize.patch > and search_path-securing_v2.patch) The other configuration p

Re: [HACKERS] Multiple --table options for other commands

2012-12-13 Thread Karl O. Pinc
On 12/13/2012 09:24:24 PM, Josh Kupershmidt wrote: > On Thu, Dec 13, 2012 at 7:24 PM, Karl O. Pinc wrote: > > As a fallback I'd do to the clusterdb, reindexdb, and vacuumdb > > syntax summaries what was done to the pg_dump and pg_restore > > syntax summaries. Remove all the detail. This is suck

Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2012-12-13 Thread 李海龙
hi, The problem is not always appear on our system, we can't find a way to reproduce it. After rebuild the index with btree, the problem is disappear at 2012-12-14 00:16, Tom Lane wrote: > =?utf-8?B?5p2O5rW36b6Z?= writes: >> We have lots data to insert in that table which have the spgist ind

Re: [HACKERS] logical decoding - GetOldestXmin

2012-12-13 Thread Michael Paquier
On Fri, Dec 14, 2012 at 2:29 AM, Robert Haas wrote: > On Thu, Dec 13, 2012 at 3:03 PM, Andres Freund > wrote: > > It moves a computation of the sort of: > > > > result -= vacuum_defer_cleanup_age; > > if (!TransactionIdIsNormal(result)) > >result = FirstNormalTransactionId; > > > > inside Pr

Re: [HACKERS] Multiple --table options for other commands

2012-12-13 Thread Josh Kupershmidt
On Thu, Dec 13, 2012 at 7:24 PM, Karl O. Pinc wrote: > The problem is that the pg man pages would then have a > syntax different from all the other man pages in the system, > which all have ... outside of square braces. > See "man cat", e.g. FWIW, `man cat` on my OS X machine has synopsis:

Re: [HACKERS] Multiple --table options for other commands

2012-12-13 Thread Karl O. Pinc
On 12/13/2012 07:37:27 PM, Josh Kupershmidt wrote: > On Thu, Dec 13, 2012 at 6:05 AM, Karl O. Pinc wrote: > > On 12/13/2012 12:35:14 AM, Karl O. Pinc wrote: > > > >> On 12/12/2012 11:04:53 PM, Josh Kupershmidt wrote: > >> > On Wed, Dec 12, 2012 at 8:14 AM, Karl O. Pinc > wrote: > >> > > On 12/11/

Re: [HACKERS] Multiple --table options for other commands

2012-12-13 Thread Josh Kupershmidt
On Thu, Dec 13, 2012 at 6:05 AM, Karl O. Pinc wrote: > On 12/13/2012 12:35:14 AM, Karl O. Pinc wrote: > >> On 12/12/2012 11:04:53 PM, Josh Kupershmidt wrote: >> > On Wed, Dec 12, 2012 at 8:14 AM, Karl O. Pinc wrote: >> > > On 12/11/2012 10:25:43 PM, Josh Kupershmidt wrote: >> > >> On Tue, Dec 11,

Re: [HACKERS] logical decoding - GetOldestXmin

2012-12-13 Thread Simon Riggs
On 13 December 2012 22:37, Andres Freund wrote: > On 2012-12-13 17:29:06 -0500, Robert Haas wrote: >> On Thu, Dec 13, 2012 at 3:03 PM, Andres Freund >> wrote: >> > It moves a computation of the sort of: >> > >> > result -= vacuum_defer_cleanup_age; >> > if (!TransactionIdIsNormal(result)) >> >

Re: [HACKERS] Use of systable_beginscan_ordered in event trigger patch

2012-12-13 Thread Tom Lane
Robert Haas writes: > On Wed, Dec 12, 2012 at 3:51 PM, Dimitri Fontaine > wrote: >> Robert, does that ring a bell to you? I'm going to crawl the archives >> tomorrow if not… > Yeah, I'm pretty sure you can't set event triggers of any kind on > event triggers. You proposed to allow some stuff th

Re: [HACKERS] Use of systable_beginscan_ordered in event trigger patch

2012-12-13 Thread Robert Haas
On Wed, Dec 12, 2012 at 3:51 PM, Dimitri Fontaine wrote: > Tom Lane writes: >>> Maybe we could just append to it how to remove such an event trigger, >>> which is easy to do because you can't target an event trigger related >>> command with event triggers, so the following is not disabled: >>>

Re: [HACKERS] Logical decoding & exported base snapshot

2012-12-13 Thread Robert Haas
On Thu, Dec 13, 2012 at 5:55 PM, Tom Lane wrote: > Robert Haas writes: >> On Thu, Dec 13, 2012 at 5:25 PM, Andres Freund >> wrote: >>> That would solve the consistency problem, yes. Would we need a special >>> kind of permission for this? I would say superuser/database owner only? > >> Yeah, I

Re: [HACKERS] Logical decoding & exported base snapshot

2012-12-13 Thread Tom Lane
Robert Haas writes: > On Thu, Dec 13, 2012 at 5:25 PM, Andres Freund wrote: >> That would solve the consistency problem, yes. Would we need a special >> kind of permission for this? I would say superuser/database owner only? > Yeah, I doubt we would need a whole new permission for it, but it > w

Re: [HACKERS] Logical decoding & exported base snapshot

2012-12-13 Thread Robert Haas
On Thu, Dec 13, 2012 at 5:25 PM, Andres Freund wrote: > That would solve the consistency problem, yes. Would we need a special > kind of permission for this? I would say superuser/database owner only? Yeah, I doubt we would need a whole new permission for it, but it would certainly have to be dis

Re: [HACKERS] logical decoding - GetOldestXmin

2012-12-13 Thread Andres Freund
On 2012-12-13 17:29:06 -0500, Robert Haas wrote: > On Thu, Dec 13, 2012 at 3:03 PM, Andres Freund wrote: > > It moves a computation of the sort of: > > > > result -= vacuum_defer_cleanup_age; > > if (!TransactionIdIsNormal(result)) > >result = FirstNormalTransactionId; > > > > inside ProcArray

Re: [HACKERS] logical decoding - GetOldestXmin

2012-12-13 Thread Robert Haas
On Thu, Dec 13, 2012 at 3:03 PM, Andres Freund wrote: > It moves a computation of the sort of: > > result -= vacuum_defer_cleanup_age; > if (!TransactionIdIsNormal(result)) >result = FirstNormalTransactionId; > > inside ProcArrayLock. But I can't really imagine that to be relevant... I can.

Re: [HACKERS] Logical decoding & exported base snapshot

2012-12-13 Thread Andres Freund
On 2012-12-13 17:11:31 -0500, Robert Haas wrote: > On Tue, Dec 11, 2012 at 10:20 PM, Tom Lane wrote: > >> This is why the pg_dump master process executes a > >> lock table in access share mode > >> for every table, so your commands would all block. > > > > A lock doesn't protect against schema ch

Re: [HACKERS] Logical decoding & exported base snapshot

2012-12-13 Thread Robert Haas
On Tue, Dec 11, 2012 at 10:20 PM, Tom Lane wrote: >> This is why the pg_dump master process executes a >> lock table in access share mode >> for every table, so your commands would all block. > > A lock doesn't protect against schema changes made before the lock was > taken. The reason that the

Re: [HACKERS] WIP: index support for regexp search

2012-12-13 Thread Alexander Korotkov
On Mon, Dec 3, 2012 at 4:31 PM, Alexander Korotkov wrote: > Actually, I generally dislike path matrix for same reasons. But: > 1) Output graphs could contain trigrams which are completely useless for > search. For example, for regex /(abcdefgh)*ijk/ we need only "ijk" trigram > while graph would c

Re: [HACKERS] SP-GiST for ranges based on 2d-mapping and quad-tree

2012-12-13 Thread Alexander Korotkov
Hi! On Sun, Nov 4, 2012 at 11:41 PM, Jeff Davis wrote: > On Fri, 2012-11-02 at 12:47 +0400, Alexander Korotkov wrote: > > > Right version of patch is attached. > > > * In bounds_adjacent, there's no reason to flip the labels back. > Fixed. > * Comment should indicate more explicitly that boun

Re: [HACKERS] gistchoose vs. bloat

2012-12-13 Thread Alexander Korotkov
Hi! On Sat, Dec 8, 2012 at 7:05 PM, Andres Freund wrote: > I notice there's no documentation about the new reloption at all? > Thanks for notice! I've added small description to docs in the attached patch. -- With best regards, Alexander Korotkov. gist_choose_bloat-0.5.patch Description:

Re: [HACKERS] Statistics and selectivity estimation for ranges

2012-12-13 Thread Alexander Korotkov
Hi, Jeff! Thanks a lot for review! On Mon, Dec 10, 2012 at 11:21 PM, Jeff Davis wrote: > It looks like there are still some problems with this patch. > > CREATE TABLE foo(ir int4range); > insert into foo select 'empty' from generate_series(1,1); > insert into foo select int4range(NULL

Re: [HACKERS] Logical decoding & exported base snapshot

2012-12-13 Thread Andres Freund
On 2012-12-13 11:02:06 -0500, Steve Singer wrote: > On 12-12-12 06:20 AM, Andres Freund wrote: > >>Possible solutions: > >>1) INIT_LOGICAL_REPLICATION waits for an answer from the client that > >>confirms that logical replication initialization is finished. Before > >>that the walsender connection

Re: [HACKERS] logical decoding - GetOldestXmin

2012-12-13 Thread Andres Freund
On 2012-12-13 18:29:00 +0100, Andres Freund wrote: > On 2012-12-13 00:05:41 +, Peter Geoghegan wrote: > > Initialisation means finding a free “logical slot” from shared memory, > > then looping until the new magic xmin horizon for logical walsenders > > (stored in their “slot”) is that of the w

Re: [HACKERS] logical changeset generation v3 - git repository

2012-12-13 Thread Andres Freund
Hi Peter! Thanks for the review, you raise many noteworthy points. This is going to be a long mail... On 2012-12-13 00:05:41 +, Peter Geoghegan wrote: > I'm very glad that you followed my earlier recommendation of splitting > your demo logical changeset consumer into a contrib module, in the

Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?

2012-12-13 Thread Tom Lane
=?utf-8?B?5p2O5rW36b6Z?= writes: > We have lots data to insert in that table which have the spgist index, > may be the spgist index have a bug on a heavy write condition? Perhaps, but you certainly haven't provided any information that would help anyone to fix the bug. Can you create a self-cont

Re: [HACKERS] Logical decoding & exported base snapshot

2012-12-13 Thread Steve Singer
On 12-12-12 06:20 AM, Andres Freund wrote: Possible solutions: 1) INIT_LOGICAL_REPLICATION waits for an answer from the client that confirms that logical replication initialization is finished. Before that the walsender connection cannot be used for anything else. 2) we remove the snapshot as so

[HACKERS] I s this a bug of spgist index in a heavy write condition?

2012-12-13 Thread 李海龙
Hi,pgsql-hackers, I'm not sure whether it is a bug of using spgist index or not . OS Version: CentOS release 6.2 (Final) PostgreSQL Version: postgres=# select version(); version -- Pos

Re: [HACKERS] Re: [PATCH 02/14] Add support for a generic wal reading facility dubbed XLogReader

2012-12-13 Thread Alvaro Herrera
Heikki Linnakangas wrote: > On 11.12.2012 21:11, Andres Freund wrote: > >Now that I have read some of that code, I am currently unsure how the > >current implementation of this can cooperate with translation, even when > >used from the backend? > > Hmm, there was a gettext() call missing from repo

Re: [HACKERS] [PERFORM] encouraging index-only scans

2012-12-13 Thread Peter Geoghegan
On 13 December 2012 03:51, Tom Lane wrote: > ANALYZE does not set that value, and is not going to start doing so, > because it doesn't scan enough of the table to derive a trustworthy > value. I'm slightly surprised by your remarks here, because the commit message where the relallvisible column w

Re: [HACKERS] WIP patch for hint bit i/o mitigation

2012-12-13 Thread Merlin Moncure
On Thu, Dec 13, 2012 at 7:06 AM, Hari Babu wrote: > Please find the review of the patch. Thanks for detailed review! > Basic stuff: > > - Patch applies with offsets. > - Compiles cleanly with no warnings > - Regression Test pass. > > Code Review: > - > 1. Better

[HACKERS] libpq with SSL on VC 2010

2012-12-13 Thread Ka55i0peia
Hey libpq-fans, i have question compiling libpq on windows (VS2010 32 and 64bit) with SSL support. I downloaded the latest source of postgres http://www.postgresql.org/ftp/source/v9.2.2/ and also the OpenSSL Win64 v1.0.1c http://slproweb.com/products/Win32OpenSSL.html . I ran nmake in libpq fo

Re: [HACKERS] [PERFORM] encouraging index-only scans

2012-12-13 Thread Bruce Momjian
On Thu, Dec 13, 2012 at 09:40:40AM +, Simon Riggs wrote: > On 13 December 2012 03:51, Tom Lane wrote: > > >> Yes, this does seem like a problem for upgrades from 9.2 to 9.3? We can > >> have pg_dump --binary-upgrade set these, or have ANALYZE set it. I > >> would prefer the later. > > > >

Re: [HACKERS] WIP patch for hint bit i/o mitigation

2012-12-13 Thread Atri Sharma
On Thu, Dec 13, 2012 at 6:36 PM, Hari Babu wrote: > On Thu, Dec 7, 2012 at 7:56 PM, Hari babu > wrote: > > >>On Thu, Dec 6, 2012 at 8:52 PM, Merlin Moncure > wrote: > > >>Thanks for that -- that's fairly comprehensive I'd say. I'm quite > > ** ** > > >>interested in that benchmarki

Re: [HACKERS] WIP patch for hint bit i/o mitigation

2012-12-13 Thread Hari Babu
On Thu, Dec 7, 2012 at 7:56 PM, Hari babu wrote: >>On Thu, Dec 6, 2012 at 8:52 PM, Merlin Moncure wrote: >>Thanks for that -- that's fairly comprehensive I'd say. I'm quite >>interested in that benchmarking framework as well. Do you need help >>setting up the scripts? >Presently I

Re: [HACKERS] Multiple --table options for other commands

2012-12-13 Thread Karl O. Pinc
On 12/13/2012 12:35:14 AM, Karl O. Pinc wrote: > On 12/12/2012 11:04:53 PM, Josh Kupershmidt wrote: > > On Wed, Dec 12, 2012 at 8:14 AM, Karl O. Pinc wrote: > > > On 12/11/2012 10:25:43 PM, Josh Kupershmidt wrote: > > >> On Tue, Dec 11, 2012 at 11:46 AM, Karl O. Pinc > > wrote: > > >> >> I belie

Re: [HACKERS] recursive view syntax

2012-12-13 Thread Abhijit Menon-Sen
At 2012-11-13 23:32:15 -0500, pete...@gmx.net wrote: > > I noticed we don't implement the recursive view syntax, even though > it's part of the standard SQL feature set for recursive queries. > Here is a patch to add that. It basically converts > > CREATE RECURSIVE VIEW name (columns) AS SELECT .

Re: [HACKERS] [PERFORM] encouraging index-only scans

2012-12-13 Thread Simon Riggs
On 13 December 2012 03:51, Tom Lane wrote: >> Yes, this does seem like a problem for upgrades from 9.2 to 9.3? We can >> have pg_dump --binary-upgrade set these, or have ANALYZE set it. I >> would prefer the later. > > ANALYZE does not set that value, and is not going to start doing so, > beca

Re: [HACKERS] PRIVATE columns

2012-12-13 Thread Kohei KaiGai
2012/12/12 Tom Lane : > Simon Riggs writes: >> Currently, ANALYZE collects data on all columns and stores these >> samples in pg_statistic where they can be seen via the view pg_stats. > > Only if you have appropriate privileges. > >> In some cases we have data that is private and we do not wish o

Re: [HACKERS] PRIVATE columns

2012-12-13 Thread Simon Riggs
On 12 December 2012 20:57, Tom Lane wrote: > SET STATISTICS 0 seems like a sufficient solution for people who don't > trust the have_column_privilege() protection in the pg_stats view. The point here is that a user may *have* privilege on the column and have rights to see some, but not all, rows

[HACKERS] Re: [PATCH 02/14] Add support for a generic wal reading facility dubbed XLogReader

2012-12-13 Thread Heikki Linnakangas
On 11.12.2012 21:11, Andres Freund wrote: Now that I have read some of that code, I am currently unsure how the current implementation of this can cooperate with translation, even when used from the backend? Hmm, there was a gettext() call missing from report_invalid_record. That's where the t