date:20140318

Bruce Momjian  writes:
> Would people accept?

>   for (errno = 0; (dirent = readdir(dir)) != NULL; errno = 0)

It's a bit weird looking, but I agree that there's value in only needing
the errno-zeroing in precisely one place.

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] issue log message to suggest VACUUM FULL if a table is nearly empty

2014-03-18 Thread Wang, Jing

On Friday, 14 March 2014 2:42 PM, Amit Kapila  wrote:
>On Wed, Mar 12, 2014 at 12:22 PM, Haribabu Kommi  
>wrote:
>> On Tue, Mar 11, 2014 at 2:59 PM, Amit Kapila  wrote:
>>
>>> By the way have you checked if FreeSpaceMapVacuum() can serve your 
>>> purpose, because this call already traverses FSM in depth-first 
>>> order to update the freespace. So may be by using this call or 
>>> wrapper on this such that it returns total freespace as well apart 
>>> from updating freespace can serve the need.
>>
>> Thanks for information. we can get the table free space by writing 
>> some wrapper or modify a little bit of FreeSpaceMapVacuum() function.

> I think it might be okay to even change this API to return the FreeSpace, as 
> the other place it is used is for Index Vacuum, so even if we don't have any 
> intention to print such a message for index in this patch, 
> but similar information could be useful there as well to suggest a user that 
> index has lot of free space.

Enclosed please find the new patch which get the FreeSpace for one relation 
from the return of FreeSpaceMapVacuum() function. This function and the 
fsm_vacuum_page() function have been slightly modified to get the FreeSpace and 
no I/O burden increasing. The little side-effect is it will calculate FreeSpace 
for every table even the table is very small.


Kind regards
Jing Wang
Fujitsu Australia


vacuum_v2.patch
Description: vacuum_v2.patch

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] QSoC proposal: Rewrite pg_dump and pg_restore

2014-03-18 Thread Alexandr

Hello!
Here is the text of my proposal which I've applied to GSoC.
(and link
https://docs.google.com/document/d/1s-Q4rzEysPxo-dINsk_eKFJOBoVjNYDrQ-Oh75gtYEM/edit?usp=sharing)

Any suggestions and comments are welcome.
*

PostgreSQL GSoC 2014 proposal

Project name

Rewrite (add) pg_dump and pg_restore utilities as libraries (.so, .dll &
.dylib)

Short description

pg_dump is a utility for backing up a PostgreSQL database. It makes
consistent backups even if the database is being used concurrently.
pg_restore is a utility for restoring a PostgreSQL database from an
archive created by pg_dump in one of the non-plain-text formats. I think
it will be more comforatable to use these tools as libraries.

Name: Alexander Shvidchenko

E-mail: askel...@gmail.com

Location: Rostov-on-Don, Russia (UTC +04.00)

Benefits to the PostgreSQL Community

This feature will expand opportunities to work with backups of
databases. Especially, it’s important for third-party developers. It
will be easier to use the functionality of the tools in applications
when they will be libraries.

Quantifiable results

Static and dynamic libraries with the functionality of pg_dump and
pg_restore tools (.so, .dll and .dylib files)

Project Schedule

until May 31

Make code review and solve architecture questions with help of community

1 June – 30 June

Detailed implementation of libraries.

1 July – 31 July

Finish Implementation of libraires and begin testing.

1 August -15 August

Final refactoring, testing and commit.

Academic experience

I entered the university in 2013. Before entering the university I
finished the college in 2012. My graduate work in the college was the
client-server application. It was a realization of XMPP. The client was
realized in Qt. The client worked with SQLite database and the server
worked with MySQL database.

Why is PostgreSQL?

- I’m intereted in this idea and believe this project would be useful
for the community;

- PostgreSQL is a very respected community. I would be proud to be a
part of it;

- PostgreSQL is one of the best DBMS and I would like to make it better.

Links

1) PostgreSQL 9.3.3 Documentation, pg_dump

http://www.postgresql.org/docs/9.3/static/app-pgdump.html

2) PostgreSQL 9.3.3 Documentation, pg_restore

http://www.postgresql.org/docs/9.3/static/app-pgrestore.html

3) Static library

http://en.wikipedia.org/wiki/Static_library

4) Dynamic library

http://en.wikipedia.org/wiki/Dynamic-link_library

With best wishes,
Alexander S.

Re: [HACKERS] pg_archivecleanup bug

2014-03-18 Thread Bruce Momjian

On Tue, Mar 18, 2014 at 09:13:28PM +0200, Heikki Linnakangas wrote:
> On 03/18/2014 09:04 PM, Simon Riggs wrote:
> >On 18 March 2014 18:55, Alvaro Herrera  wrote:
> >
> >>That said, I don't find comma expression to be particularly "not
> >>simple".
> >
> >Maybe, but we've not used it before anywhere in Postgres, so I don't
> >see a reason to start now. Especially since C is not the native
> >language of many people these days and people just won't understand
> >it.
> 
> Agreed. The psqlODBC code is littered with comma expressions, and
> the first time I saw it, it took me a really long time to figure out
> what "if (foo = malloc(...), foo) { } " meant. And I consider myself
> quite experienced with C.

I can see how the comma syntax would be confusing, though it does the
job well.  Attached is a patch that does the double-errno.  However,
some of these loops are large, and there are 'continue' calls in there,
causing the addition of many new errno locations.  I am not totally
comfortable that this new coding layout will stay unbroken.

Would people accept?

for (errno = 0; (dirent = readdir(dir)) != NULL; errno = 0)

That would keep the errno's together and avoid the 'continue' additions.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/contrib/pg_archivecleanup/pg_archivecleanup.c b/contrib/pg_archivecleanup/pg_archivecleanup.c
new file mode 100644
index 7b5484b..405ec48
*** a/contrib/pg_archivecleanup/pg_archivecleanup.c
--- b/contrib/pg_archivecleanup/pg_archivecleanup.c
*** CleanupPriorWALFiles(void)
*** 106,111 
--- 106,112 
  
  	if ((xldir = opendir(archiveLocation)) != NULL)
  	{
+ 		errno = 0;
  		while ((xlde = readdir(xldir)) != NULL)
  		{
  			strncpy(walfile, xlde->d_name, MAXPGPATH);
*** CleanupPriorWALFiles(void)
*** 148,153 
--- 149,155 
  		fprintf(stderr,
  "%s: file \"%s\" would be removed\n",
  progname, WALFilePath);
+ 	errno = 0;
  	continue;
  }
  
*** CleanupPriorWALFiles(void)
*** 163,170 
  	break;
  }
  			}
  		}
! 		closedir(xldir);
  	}
  	else
  		fprintf(stderr, "%s: could not open archive location \"%s\": %s\n",
--- 165,185 
  	break;
  }
  			}
+ 			errno = 0;
  		}
! 
! #ifdef WIN32
! 		/* Bug in old Mingw dirent.c;  fixed in mingw-runtime-3.2, 2003-10-10 */
! 		if (GetLastError() == ERROR_NO_MORE_FILES)
! 			errno = 0;
! #endif
! 
! 		if (errno)
! 			fprintf(stderr, "%s: could not read archive location \"%s\": %s\n",
! 	progname, archiveLocation, strerror(errno));
! 		if (!closedir(xldir))
! 			fprintf(stderr, "%s: could not close archive location \"%s\": %s\n",
! 	progname, archiveLocation, strerror(errno));
  	}
  	else
  		fprintf(stderr, "%s: could not open archive location \"%s\": %s\n",
diff --git a/contrib/pg_standby/pg_standby.c b/contrib/pg_standby/pg_standby.c
new file mode 100644
index 8ddd486..d4731d7
*** a/contrib/pg_standby/pg_standby.c
--- b/contrib/pg_standby/pg_standby.c
*** CustomizableCleanupPriorWALFiles(void)
*** 245,250 
--- 245,251 
  		 */
  		if ((xldir = opendir(archiveLocation)) != NULL)
  		{
+ 			errno = 0;
  			while ((xlde = readdir(xldir)) != NULL)
  			{
  /*
*** CustomizableCleanupPriorWALFiles(void)
*** 282,288 
--- 283,300 
  		break;
  	}
  }
+ errno = 0;
  			}
+ 
+ #ifdef WIN32
+ 			/* Bug in old Mingw dirent.c;  fixed in mingw-runtime-3.2, 2003-10-10 */
+ 			if (GetLastError() == ERROR_NO_MORE_FILES)
+ errno = 0;
+ #endif
+ 
+ 			if (errno)
+ fprintf(stderr, "%s: could not read archive location \"%s\": %s\n",
+ 		progname, archiveLocation, strerror(errno));
  			if (debug)
  fprintf(stderr, "\n");
  		}
*** CustomizableCleanupPriorWALFiles(void)
*** 290,296 
  			fprintf(stderr, "%s: could not open archive location \"%s\": %s\n",
  	progname, archiveLocation, strerror(errno));
  
! 		closedir(xldir);
  		fflush(stderr);
  	}
  }
--- 302,311 
  			fprintf(stderr, "%s: could not open archive location \"%s\": %s\n",
  	progname, archiveLocation, strerror(errno));
  
! 		if (!closedir(xldir))
! 			fprintf(stderr, "%s: could not close archive location \"%s\": %s\n",
! 	progname, archiveLocation, strerror(errno));
! 		
  		fflush(stderr);
  	}
  }
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
new file mode 100644
index 4dc809d..5158cfe
*** a/src/backend/storage/file/fd.c
--- b/src/backend/storage/file/fd.c
*** ReadDir(DIR *dir, const char *dirname)
*** 1957,1966 
  		return dent;
  
  #ifdef WIN32
! 	/*
! 	 * This fix is in mingw cvs (runtime/mingwex/dirent.c rev 1.4), but not in
! 	 * released version
! 	 */
  	if (GetLastError() == ERROR_NO_MORE_FILES)
  		errno = 0;
  #endif
--- 1957,1963 
  		return dent;
  
  #ifdef WIN32
! 	/* Bug in old Mingw

[HACKERS] GSoC application: MADlib k-medoids clustering

2014-03-18 Thread Maxence Ahlouche

Hi all,

Some of you may remember me from last year: I had applied to implement the
k-medoids clustering method in MADlib, a Postgres and GreenPlum library.
As this project could not be selected last year, I'm trying again now!

You can find my application at this address:
https://github.com/viodlen/gsoc_2014
I've also pasted it at the end of this email.

As for me: I'm Maxence Ahlouche, a French student in computer science. I've
been studying IT for almost 4 years now, the first two of them being in a
technical degree. I'm currently in an engineering school, and am supposed
to graduate next year.


However, there's a problem with my application: the GSoC is supposed to
begin on may 19, but I have lectures and exams until at least June 4, and
the official end of my school year is on June 13. The period between June 4
and June 14 is intended for students to finish their school projects, if
they haven't already.
In the worst case, I won't have time to do anything significant for the
first 4 weeks.

Still, I think I'm able to handle this situation, and this worst case is
quite unlikely. Between May 19 and June 4, I'll have lectures and exams,
but it should still be a quiet time, and I'll have time to work on my GSoC
project the evenings and week-ends.  After June 4, I'll only have my school
projects to finish and present, and I think I'll be able to spend most of
my time on GSoC.
I've taken it into account in my proposal's schedule, that's why the first
part takes almost as long as the second, even though it looks easier to me.

Of course, if the community doesn't want to take this risk, I'll understand.

Regards,
Maxence Ahlouche

My proposal:

> GSoC 2014 proposal: implementing clustering algorithms in MADlib
> 
>
> Synopsis
> 
>
> This project aims to implement some clustering algorithms in MADlib,
> which is a data analytics and machine learning library for PostgreSQL,
> Greenplum and HAWQ.
>
> Benefits to the PostgreSQL community
> 
>
> Currently, only the k-means clustering algorithm is implemented in
> MADlib (see the doc:
> http://doc.madlib.net/latest/group__grp__clustering.html ). The
> k-medoids algorithm, while being computationnally more intensive, is
> much less sensitive to outliers (points that don't belong obviously to
> one cluster or another). This is interesting on noisy datasets, that's
> why I'm planning to implement it during the first part of the GSoC.
>
> Still, these algorithms are based on distance computation, therefore
> they can only find convex clusters. That's why I'm proposing to
> implement the OPTICS (*ordering points to identify the clustering
> structure*, see http://en.wikipedia.org/wiki/OPTICS_algorithm ), which
> addresses this issue, as the second part of this GSoC project.
>
> The PostgreSQL community would benefit from these features, as it
> would make available clustering algorithms more powerful than simple
> k-means.
>
> Project details
> ---
>
> k-medoids
> "
>
> The first goal of this project is to implement the k-medoids
> clustering algorithm. For this, I'll first spend some time studying
> the k-means algorithm, as both will probably be pretty similar. This
> will also allow me to get familiar with the codebase, the conventions,
> the data structures I'll need, etc.
>
> Then I'll implement, test and debug the algorithm. If relevant, I'll
> also provide a "k-medoids++" version, which, similarly to the
> k-means++ function in MADlib, will chose the initial centroids
> depending on the dataset, instead of chosing them randomly. This
> allows to detect small clusters located far from the others (which are
> usually detected as part of an other bigger cluster using the standard
> algorithm).
>
> The final step would be to refactor the code from k-means and
> k-medoids to remove any code duplication introduced in this first
> part.
>
> OPTICS
> ""
>
> The second part of this project would be to implement the
> density-based clustering algorithm OPTICS, which would overcome the
> main problem of both the k-means and k-medoids algorithm: non-convex
> clusters. This algorithm has been preferred over DBSCAN
> (http://en.wikipedia.org/wiki/DBSCAN ) as it is able to detect
> clusters of different densities, and, consequently, overlapping
> clusters.
>
> I'll first take some time to understand full well the algorithm, and
> make a prototype in Python, to be sure I know how it works. Then I'll
> actually implement it, test it, and debug it in MADlib.
>
> If, after that, any time's left, I'll consider implementing some
> of the improvements of k-means and k-medoids that we can find in the
> litterature.
>
> Deliverables
> 
>
> * the k-medoids algorithm in MADlib;
> * the OPTICS algorithm, also in MADlib;
> * optionnally, some improvements on k-means and/or k-medoids.
>
> Project Schedule
> 
>
> #. Implementation of

[HACKERS] logical decoding doc

2014-03-18 Thread Tatsuo Ishii

It seems two exactly same sql sessions are repeated in
logicaldecoding.sgml. Is this intended?

postgres=# -- You can also peek ahead in the change stream without consuming 
changes
postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, 
NULL);
 location  | xid | data
---+-+---
 0/16E09C0 | 690 | BEGIN 690
 0/16E09C0 | 690 | table public.data: INSERT: id[integer]:3 data[text]:'3'
 0/16E0B90 | 690 | COMMIT 690
(3 rows)

postgres=# -- You can also peek ahead in the change stream without consuming 
changes
postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, 
NULL);
 location  | xid | data
---+-+---
 0/16E09C0 | 690 | BEGIN 690
 0/16E09C0 | 690 | table public.data: INSERT: id[integer]:3 data[text]:'3'
 0/16E0B90 | 690 | COMMIT 690
(3 rows)

Maybe this is to demonstrating "peek ahead" does not consume changes,
but IMO this is a little bit confusing for readers and I think there's
a room to enhance the second sql session comment for example:

postgres=# -- Again you can also peek ahead in the change stream without 
consuming changes

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wiki Page Draft for upcoming release

Josh Berkus  writes:
> On 03/17/2014 05:49 PM, Josh Berkus wrote:
>> https://wiki.postgresql.org/wiki/20140320UpdateIssues

> Anyone?  We're going public with this in 36 hours, so I'd love for it to
> actually be correct.

I did a bit more hacking on this page.  Could use another look from Alvaro
and/or Andres, I'm sure.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Minimum supported version of Python?

Peter Eisentraut  writes:
> On 3/17/14, 10:55 PM, Tom Lane wrote:
>> It doesn't pass the regression tests.  Do you need more of a bug report
>> than that?

> It does pass the tests for me and others.  If you are seeing something
> different, then we need to see some details, like what platform, what
> version, what Python version, how installed, what PostgreSQL version,
> how installed, actual diffs, etc.

After further experimentation, I've found that 2.3 does pass the regression
tests if one installs the separately-available cdecimal module.  So my
complaint reduces to the fact that our "Requirements" documentation
doesn't mention the need for this.  Do you have an objection to adding
something there?

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] First draft of update announcement

Josh Berkus  writes:
> Updated per feedback.  CC'd to Advocacy now for additional corrections.

A few thoughts:

> The PostgreSQL Global Development Group has released an update to all
> supported version of the database system, including versions 9.3.4, 9.2.8,
> 9.1.13, 9.0.19, and 8.4.20.

By my count, 9.0.17 and 8.4.21 are the correct minor numbers.

> The data corruption issue in PostgreSQL 9.3 affects binary replication
> standbys, servers being recovered from point-in-time-recovery backup, and
> standalone servers which recover from a system crash. The bug causes rows
> to vanish from indexes during recovery due to timing issues with updating
> locks.

Per earlier discussion, I think "vanish from indexes" is a bad choice of
wording here: it will make people think they can recover by REINDEXing,
which is not the case.  I haven't got a great alternative wording though;
best I can do offhand is "causes table rows to become unreachable by
index scans", which lacks punch.

Also, although this isn't too important to users, the problem isn't a
"timing issue".  How about "... during recovery due to incorrect replay of
tuple locking operations", or some such?

> For this reason, users are encouraged to take a new base backup of each
> of their standby databases after applying the update.

"new base backup for", perhaps?  With "of", this sounds like you're
telling people to make backups from the (corrupted) slave servers.

> * Remove ability to execute OVERLAPs with a single argument

There wasn't ever any actual ability to execute such calls; there was only
some code that tried to support the case and failed miserably.  I'm not
sure this is worth mentioning in the announcement, really --- but if you
do, this is a poor description because it sounds like we removed a usable
feature.

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] First draft of update announcement

All,

Updated per feedback. CC'd to Advocacy now for additional corrections.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
The PostgreSQL Global Development Group has released an update to all supported version of the database system, including versions 9.3.4, 9.2.8, 9.1.13, 9.0.19, and 8.4.20. This minor release fixes a data corruption issue with replication and crash recovery in version 9.3, as well as several other minor issues in all versions. All users of version 9.3 are urged to update their installations at the next possible downtime. Users of older versions should update at their convenience.

The data corruption issue in PostgreSQL 9.3 affects binary replication standbys, servers being recovered from point-in-time-recovery backup, and standalone servers which recover from a system crash. The bug causes rows to vanish from indexes during recovery due to timing issues with updating locks. This can then cause query results to be inconsistent depending on whether or not an index is used, and eventually lead to primary key violations and similar issues. For this reason, users are encouraged to take a new base backup of each of their standby databases after applying the update.

Other PostgreSQL 9.3 only fixes in this update include:

* Make sure that statistics files for dropped databases get deleted
* Allow materialized views to be referenced in UPDATE and DELETE queries
* Add read-only data_checksum parameter
* Prevent erroneous operator push-down in postgres_fdw

This release resolves some other issues in all versions of PostgreSQL, including:

* Fix timing consistency issue with NOTIFY
* Allow regular expression execution to be cancelled
* Remove ability to execute OVERLAPs with a single argument
* Improve performance of index checks for newly added rows
* Prevent premature walsender disconnection
* Prevent memory errors on newer Windows versions
* Update timezone files

Additional changes and details of some of the above issues can be found in the Release Notes. Two of the issues which affect version 9.3 have additional information on the 9.3.4 Update Wiki Page.

Users of version 8.4 should note that it will reach End-of-Life (EOL) three months from now, per our [Versioning Policy](http://www.postgresql.org/support/versioning/). This means that this is likely to be the next-to-last update for version 8.4, and users should be planning to upgrade to a newer version of PostgreSQL.

As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shut down PostgreSQL and update its binaries. Users who have skipped multiple update releases may need to perform additional post-update steps; see the Release Notes for details.

Links:
* [Download](http://postgresql.org/download)
* [Release Notes](http://www.postgresql.org/docs/current/static/release.html)
* [9.3.4 Update Wiki Page](https://wiki.postgresql.org/wiki/20140320UpdateIssues)

--
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] ALTER TABLE lock strength reduction patch is unsafe Reply-To:

2014-03-18 Thread Noah Misch

On Tue, Mar 18, 2014 at 10:39:03AM +, Simon Riggs wrote:
> On 8 March 2014 11:14, Simon Riggs  wrote:
> > Implemented in attached patch, v22

> > I commend this patch to you for final review; I would like to commit
> > this in a few days.
> 
> I'm planning to commit this today at 1500UTC barring objections or
> negative reviews.

Not an objection, but FYI, I'm currently in the midst of reviewing this.

-- 
Noah Misch
EnterpriseDB http://www.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] plpgsql.warn_shadow

2014-03-18 Thread Marko Tiikkaja


Hi Petr,

On 3/18/14, 8:38 PM, I wrote:

I did one small change (that I think was agreed anyway) from Marko's
original patch in that warnings are only emitted during function
creation, no runtime warnings and no warnings for inline (DO) plpgsql
code either as I really don't think these optional warnings/errors
during runtime are a good idea.


Not super excited, but I can live with that.


I'm sorry, that came out wrong.

As far as I'm concerned, I believe we have a consensus that 
*runtime-only* warnings are not a terribly good idea.  The warnings in 
this patch were emitted originally all the time because I wanted to 
maximize their visibility.  But I think that has a bit of the same 
problems as run-time warnings do; who's gonna notice them?


In any case, I think you guys have the situation under control and if 
this patch gets committed like this, it solves my issues.  Thanks for 
your work here.



Regards,
Marko Tiikkaja


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] plpgsql.warn_shadow

On 18 March 2014 20:52, Marti Raudsepp  wrote:
> On Tue, Mar 18, 2014 at 9:29 PM, Petr Jelinek  wrote:
>> Attached V4 uses "shadowed-variables" instead of "shadow".
>
> I think that should be "shadowed_variables" for consistency; GUC
> values usually have underscores, not dashes. (e.g.
> intervalstyle=sql_standard, backslash_quote=safe_encoding,
> synchronous_commit=remote_write etc)

Definitely. Sorry for not noticing that earlier; don't want dashes.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wiki Page Draft for upcoming release

2014-03-18 Thread Andrew Dunstan



On 03/18/2014 04:39 PM, Andres Freund wrote:


Mail that's CC/TOed to me onlist, is automatically marked as read by a
sieve script so I don't have to mark it as read twice. It seems
something went wrong there for a couple of messages...




Why not just turn on eliminatecc on the majordomo server? See 



cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] plpgsql.warn_shadow

2014-03-18 Thread Marti Raudsepp

On Tue, Mar 18, 2014 at 9:29 PM, Petr Jelinek  wrote:
> Attached V4 uses "shadowed-variables" instead of "shadow".

I think that should be "shadowed_variables" for consistency; GUC
values usually have underscores, not dashes. (e.g.
intervalstyle=sql_standard, backslash_quote=safe_encoding,
synchronous_commit=remote_write etc)

Regards,
Marti


-- 
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] Windows build patch

Wim Dumon wrote:
> 9.3.1 is the version that failed for me, MSVS 2012, Windows 7.

It's pretty clear that we will never be able to keep this working unless
somebody sets up a buildfarm animal that tests this stuff directly.
If you're up to the task, please see here:
https://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Failure while inserting parent tuple to B-tree is not fun

Heikki Linnakangas  writes:
> Yeah, it's a bit silly that each resource manager has to do that on 
> their own. It would be useful to have a memory context that was 
> automatically reset between each WAL record. In fact that should 
> probably be the default memory context you run the WAL redo routines in.

+1

> But even if we do that, I'm not in a hurry to remove rm_startup/cleanup. 
> They seem generally useful, even if they're not actually used for much 
> anymore.

Agreed; they aren't hurting us and they could be useful.

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] Wiki Page Draft for upcoming release

On 2014-03-18 17:34:34 -0300, Alvaro Herrera wrote:
> Andres Freund wrote:
> > On 2014-03-18 16:19:01 -0400, Tom Lane wrote:
> > > Andres Freund  writes:
> > > > On 2014-03-18 19:28:53 +, Greg Stark wrote:
> > > >> It would be nice to be able to tell people that if they vacuum, then
> > > >> reindex and check all their foreign key constraints then they should
> > > >> be ok.
> > > 
> > > > I don't think so:
> > > > http://archives.postgresql.org/message-id/20140317233919.GS16438%40awork2.anarazel.de
> > > 
> > > > I still think a rewriting noop ALTER TABLE ... ALTER COLUMN col TYPE
> > > > old_type USING (col); is the only real thing to do.
> > > 
> > > Unfortunately, the operative word in that is "noop".  Didn't you see
> > > http://www.postgresql.org/message-id/11964.1395100...@sss.pgh.pa.us
> > 
> > Huh, that somehow never arrived here. Very odd.
> 
> 2014-03-18 00:01:18 [10067] 1WPhT4-0002cN-F3 <= 
> pgsql-hackers-owner+M246803=andres=anarazel...@postgresql.org H=localhost 
> (postgresql.org) [127.0.0.1] P=smtp S=3502 id=11964.1395100...@sss.pgh.pa.us
> 2014-03-18 00:01:18 [10067] 1WPhT4-0002cN-F3 <= 
> pgsql-hackers-owner+M246803=andres=anarazel...@postgresql.org H=localhost 
> (postgresql.org) [127.0.0.1] P=smtp S=3502 id=11964.1395100...@sss.pgh.pa.us
> 2014-03-18 00:01:19 [10170] 1WPhT4-0002cN-F3 => and...@anarazel.de 
> R=dnslookup T=remote_smtp H=mail.anarazel.de [217.115.131.40] 
> X=TLS1.0:DHE_RSA_AES_128_CBC_SHA1:128 QT=1s DT=1s
> 2014-03-18 00:01:19 [10170] 1WPhT4-0002cN-F3 Completed QT=1s

Mail that's CC/TOed to me onlist, is automatically marked as read by a
sieve script so I don't have to mark it as read twice. It seems
something went wrong there for a couple of messages...

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Failure while inserting parent tuple to B-tree is not fun

2014-03-18 Thread Heikki Linnakangas


On 02/06/2014 01:54 AM, Peter Geoghegan wrote:

On Thu, Jan 23, 2014 at 1:36 PM, Peter Geoghegan  wrote:

So while post-recovery callbacks no longer exist for any
rmgr-managed-resource, 100% of remaining startup and cleanup callbacks
concern the simple management of memory of AM-specific recovery
contexts (for GiST, GiN and SP-GiST). I have to wonder if there isn't
a better abstraction than that, such as a generic recovery memory
context, allowing you to retire all 3 callbacks. I mean, StartupXLOG()
just calls those callbacks for each resource at exactly the same time
anyway, just as it initializes resource managers in precisely the same
manner earlier on. Plus if you look at what those AM-local memory
management routines do, it all seems very simple.


What are your thoughts on this, as someone that has a broader
perspective here? Are you inclined to keep the startup and cleanup
callbacks in anticipation of a day when that degree of generality is
useful? That would be pretty well-precedented of course, but I would
like to hear your opinion.


So, I just removed the rm_safe_restartpoint callback, as that's clearly 
dead now and I would complain loudly if someone tried to add a resource 
manager that would need it again.


Yeah, it's a bit silly that each resource manager has to do that on 
their own. It would be useful to have a memory context that was 
automatically reset between each WAL record. In fact that should 
probably be the default memory context you run the WAL redo routines in.


But even if we do that, I'm not in a hurry to remove rm_startup/cleanup. 
They seem generally useful, even if they're not actually used for much 
anymore.


- Heikki


--
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] Wiki Page Draft for upcoming release

Andres Freund wrote:
> On 2014-03-18 16:19:01 -0400, Tom Lane wrote:
> > Andres Freund  writes:
> > > On 2014-03-18 19:28:53 +, Greg Stark wrote:
> > >> It would be nice to be able to tell people that if they vacuum, then
> > >> reindex and check all their foreign key constraints then they should
> > >> be ok.
> > 
> > > I don't think so:
> > > http://archives.postgresql.org/message-id/20140317233919.GS16438%40awork2.anarazel.de
> > 
> > > I still think a rewriting noop ALTER TABLE ... ALTER COLUMN col TYPE
> > > old_type USING (col); is the only real thing to do.
> > 
> > Unfortunately, the operative word in that is "noop".  Didn't you see
> > http://www.postgresql.org/message-id/11964.1395100...@sss.pgh.pa.us
> 
> Huh, that somehow never arrived here. Very odd.

2014-03-18 00:01:18 [10067] 1WPhT4-0002cN-F3 <= 
pgsql-hackers-owner+M246803=andres=anarazel...@postgresql.org H=localhost 
(postgresql.org) [127.0.0.1] P=smtp S=3502 id=11964.1395100...@sss.pgh.pa.us
2014-03-18 00:01:18 [10067] 1WPhT4-0002cN-F3 <= 
pgsql-hackers-owner+M246803=andres=anarazel...@postgresql.org H=localhost 
(postgresql.org) [127.0.0.1] P=smtp S=3502 id=11964.1395100...@sss.pgh.pa.us
2014-03-18 00:01:19 [10170] 1WPhT4-0002cN-F3 => and...@anarazel.de R=dnslookup 
T=remote_smtp H=mail.anarazel.de [217.115.131.40] 
X=TLS1.0:DHE_RSA_AES_128_CBC_SHA1:128 QT=1s DT=1s
2014-03-18 00:01:19 [10170] 1WPhT4-0002cN-F3 Completed QT=1s

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [WIP] Better partial index-only scans

On Tue, Mar 18, 2014 at 4:18 PM, Joshua Yanovski  wrote:
>> I'm glad you're looking at this, but we're in the final throws of
>> nailing down 9.4 and I don't have anticipate I'll have time to look at
>> it in the near future.  You should add it here so we don't forget
>> about it:
>>
>> https://commitfest.postgresql.org/action/commitfest_view/open
> Yeah, no worries--you guys are busy enough as it is.  As far as adding
> it to the commitfest goes, I did, actually.  Should I add the comment
> with the testcase as well?  I'm investigating further and it's looking
> to me like what I'm really up against is O(n^2) behavior in the
> optimizer for OR clauses, but I'll keep looking--don't want to say
> anything too prematurely.
>>
>> And you might also check this:
>> https://wiki.postgresql.org/wiki/Submitting_a_Patch
>>
> Also read that--did I do something wrong?  I tried to make sure I
> followed its guidelines.  Anyway, thanks for the response :)

No, I think you wrote a nice email, and I didn't think you did
anything wrong.  It was mostly just a form letter to say, hey, this
looks interesting.  Glad you were already aware of the resources.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Wiki Page Draft for upcoming release

On 2014-03-18 16:19:01 -0400, Tom Lane wrote:
> Andres Freund  writes:
> > On 2014-03-18 19:28:53 +, Greg Stark wrote:
> >> It would be nice to be able to tell people that if they vacuum, then
> >> reindex and check all their foreign key constraints then they should
> >> be ok.
> 
> > I don't think so:
> > http://archives.postgresql.org/message-id/20140317233919.GS16438%40awork2.anarazel.de
> 
> > I still think a rewriting noop ALTER TABLE ... ALTER COLUMN col TYPE
> > old_type USING (col); is the only real thing to do.
> 
> Unfortunately, the operative word in that is "noop".  Didn't you see
> http://www.postgresql.org/message-id/11964.1395100...@sss.pgh.pa.us

Huh, that somehow never arrived here. Very odd.

> It would have helped before we put in the transform smarts, but now
> it's too smart.

Gna. Hm, what about: USING (COALESCE(columnname, NULL));

That still seems to work.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wiki Page Draft for upcoming release

Andres Freund  writes:
> On 2014-03-18 19:28:53 +, Greg Stark wrote:
>> It would be nice to be able to tell people that if they vacuum, then
>> reindex and check all their foreign key constraints then they should
>> be ok.

> I don't think so:
> http://archives.postgresql.org/message-id/20140317233919.GS16438%40awork2.anarazel.de

> I still think a rewriting noop ALTER TABLE ... ALTER COLUMN col TYPE
> old_type USING (col); is the only real thing to do.

Unfortunately, the operative word in that is "noop".  Didn't you see
http://www.postgresql.org/message-id/11964.1395100...@sss.pgh.pa.us
It would have helped before we put in the transform smarts, but now
it's too smart.

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] [WIP] Better partial index-only scans

2014-03-18 Thread Joshua Yanovski

> I'm glad you're looking at this, but we're in the final throws of
> nailing down 9.4 and I don't have anticipate I'll have time to look at
> it in the near future.  You should add it here so we don't forget
> about it:
>
> https://commitfest.postgresql.org/action/commitfest_view/open
Yeah, no worries--you guys are busy enough as it is.  As far as adding
it to the commitfest goes, I did, actually.  Should I add the comment
with the testcase as well?  I'm investigating further and it's looking
to me like what I'm really up against is O(n^2) behavior in the
optimizer for OR clauses, but I'll keep looking--don't want to say
anything too prematurely.
>
> And you might also check this:
> https://wiki.postgresql.org/wiki/Submitting_a_Patch
>
Also read that--did I do something wrong?  I tried to make sure I
followed its guidelines.  Anyway, thanks for the response :)

-- 
Josh


-- 
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] Portability issues in shm_mq

Robert Haas  writes:
> On Tue, Mar 18, 2014 at 12:15 PM, Tom Lane  wrote:
>> Meh.  I think you're putting a bit too much faith in your ability to
>> predict the locus of bugs that you think aren't there.

> Well, I'm open to suggestions.

As a suggestion: it'd be worth explicitly testing zero-byte and one-byte
messages, those being obvious edge cases.  Then, say, randomly chosen
lengths in the range 100-1000; this would help ferret out odd-length
issues.  And something with message sizes larger than the queue size.

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] First-draft release notes for next week's releases

Folks:

So another question, which I've already received from the field, is how
can you detect this kind of corruption in the first place, if it's not
causing a user-visible error?

Got that question from someone who failed over between master and
replica on 9.3.2 last weekend.  They're not seeing PK/FK errors, but
that doesn't mean they're clean.  How can they tell?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] Planner hints in Postgresql

2014-03-18 Thread Claudio Freire

On Tue, Mar 18, 2014 at 4:48 PM, Merlin Moncure  wrote:

> > That alone could improve things considerably, and statistical info could
> be
> > propagated along expressions to make it possible to model uncertainty in
> > complex expressions as well.
>
> But how would that work?  I see no solution adumbrated there :-).
>

I would have to tipify the SQL expression grammar for this, but I don't
think it would be impossible. Most non-function expression nodes seem
rather trivial. Even CASE, as long as you have a distribution for the
conditional, you can derive a distribution for the whole. User defined
functions would be another game, though. Correlation would have to be
measured, and that can be troublesome and a weak spot of risk computation
as much as it is of planning, but it could be fuzzed arbitrarily until
properly computed - after all, dependency on correlation or non-correlation
is a known source of risk, and accounting for it in any way is better than
not.

> Let's say you change the rowcount estimate to low/bestguess/high *and*
> you only engage extra searches when there is enough disparity between
> those values you still get exponentially more searches.

I was under the impression the planner already did an exhaustive search for
some queries. So it's just a matter of picking the best plan among those
(ie: estimating cost). The case of GEQO isn't any different, except perhaps
introducing a risk-decreasing transformation would be needed, unless I'm
missing something.

>  (my thinking
> is that if bestguess estimated execution time is some user definable
> amount faster then low/high at any node), a more skeptical plan is
> introduced.   All that could end up being pessimal to the general case
> though.

I think the cost estimate would be replaced by a distribution (simplified
perhaps into an array of moments, or whatever is easily manipulated in the
face of complex expressions). What the user would pick, is a sampling
method of said distribution. Then, plans get measured by the user's stick
(say: arithmetic mean, median, 90th percentile, etc). The arithmetic mean
would I guess be the default, and that ought to be roughly equivalent to
the planner's current behavior.

Re: [HACKERS] [WIP] Better partial index-only scans

On Mon, Mar 17, 2014 at 3:14 AM, Joshua Yanovski  wrote:
> Here's a SQL script that (1) demonstrates the new index only scan
> functionality, and (2) at least on my machine, has a consistently
> higher planning time for the version with my change than without it.

I'm glad you're looking at this, but we're in the final throws of
nailing down 9.4 and I don't have anticipate I'll have time to look at
it in the near future.  You should add it here so we don't forget
about it:

https://commitfest.postgresql.org/action/commitfest_view/open

And you might also check this:
https://wiki.postgresql.org/wiki/Submitting_a_Patch

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Wiki Page Draft for upcoming release

On 03/18/2014 12:55 PM, Andres Freund wrote:
> On 2014-03-18 12:52:49 -0700, Josh Berkus wrote:
>> On 03/18/2014 12:35 PM, Andres Freund wrote:
>>> I still think a rewriting noop ALTER TABLE ... ALTER COLUMN col TYPE
>>> old_type USING (col); is the only real thing to do.
>>
>> Then why wouldn't VACUUM FULL work?
> 
> Please read the referenced message and children. It will use
> rewriteheap.c which tries to maintain the update chains.

Reading ... hmmm.  Well, the ALTER TABLE thing may work, but I'm really
hesitant to recommend it as a general solution for our users; it doesn't
look repliable.  Too bad we don't have a handy corrupted database to
practice on.

I will note that VACUUM FULL doesn't work either.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] Wiki Page Draft for upcoming release

On 2014-03-18 12:52:49 -0700, Josh Berkus wrote:
> On 03/18/2014 12:35 PM, Andres Freund wrote:
> > I still think a rewriting noop ALTER TABLE ... ALTER COLUMN col TYPE
> > old_type USING (col); is the only real thing to do.
> 
> Then why wouldn't VACUUM FULL work?

Please read the referenced message and children. It will use
rewriteheap.c which tries to maintain the update chains.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wiki Page Draft for upcoming release

On 03/18/2014 12:35 PM, Andres Freund wrote:
> I still think a rewriting noop ALTER TABLE ... ALTER COLUMN col TYPE
> old_type USING (col); is the only real thing to do.

Then why wouldn't VACUUM FULL work?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] plpgsql.warn_shadow

2014-03-18 20:49 GMT+01:00 Petr Jelinek :

>
> On 18/03/14 20:45, Pavel Stehule wrote:
>
>
>
> 2014-03-18 20:44 GMT+01:00 Petr Jelinek :
>
>>
>> On 18/03/14 20:36, Pavel Stehule wrote:
>>
>>>
>>>
>>> +To aid the user in finding instances of simple but common problems
>>> before
>>> +they cause harm, PL/PgSQL provides a number of
>>> additional
>>> +checks. When enabled, depending on the
>>> configuration, they
>>> +can be used to emit either a WARNING or an
>>> ERROR
>>> +during the compilation of a function.
>>> +   
>>>
>>> >>>provides a number of additional<<<
>>>
>>> There will be only one check in 9.4, so this sentence is confusing and
>>> should be reformulated.
>>>
>>
>>  Thanks, yeah I left that sentence unchanged from original patch, maybe I
>> should remove the word "number" there as it implies that there are a lot of
>> them, but I don't really want to change everything to singular when the
>> input is specified as list.
>
>
>  What about add sentence: in this moment only "shadowed-variables" is
> available?
>
>
> There is something like that said 2 paragraphs down the line:
>
> +  The default is "none". Currently the list of available checks
> +  includes only one:
> +  
> +   
> +shadowed-variables
>
>
ok


>  --
>  Petr Jelinek  http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

Tom Lane escribió:
> Alvaro Herrera  writes:
> > Please see my reply to Robert.  My proposal (in form of a patch) is
> >   while operating on tuple (0,2) in table "foo": updating tuple
> > Would this work for you?
> 
> It's pretty lousy from a readability standpoint, even in English;
> I shudder to think what it might come out as after translation.

Well, the same thing actually.  I didn't think it was too bad.

> I think the enum idea you floated is probably worth doing.  It's
> certainly no more complex than passing a string, no?

Okay, done that way, attached.  I think this one solves all concerns
there were.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/src/backend/access/heap/heapam.c
--- b/src/backend/access/heap/heapam.c
***
*** 105,115  static void GetMultiXactIdHintBits(MultiXactId multi, uint16 *new_infomask,
  	   uint16 *new_infomask2);
  static TransactionId MultiXactIdGetUpdateXid(TransactionId xmax,
  		uint16 t_infomask);
! static void MultiXactIdWait(MultiXactId multi, MultiXactStatus status,
! int *remaining, uint16 infomask);
! static bool ConditionalMultiXactIdWait(MultiXactId multi,
! 		   MultiXactStatus status, int *remaining,
! 		   uint16 infomask);
  static XLogRecPtr log_heap_new_cid(Relation relation, HeapTuple tup);
  static HeapTuple ExtractReplicaIdentity(Relation rel, HeapTuple tup, bool key_modified,
  		bool *copy);
--- 105,116 
  	   uint16 *new_infomask2);
  static TransactionId MultiXactIdGetUpdateXid(TransactionId xmax,
  		uint16 t_infomask);
! static void MultiXactIdWait(MultiXactId multi, MultiXactStatus status, uint16 infomask,
! Relation rel, ItemPointer ctid, XLTW_Oper oper,
! int *remaining);
! static bool ConditionalMultiXactIdWait(MultiXactId multi, MultiXactStatus status,
! 		   uint16 infomask, Relation rel, ItemPointer ctid,
! 		   XLTW_Oper oper, int *remaining);
  static XLogRecPtr log_heap_new_cid(Relation relation, HeapTuple tup);
  static HeapTuple ExtractReplicaIdentity(Relation rel, HeapTuple tup, bool key_modified,
  		bool *copy);
***
*** 2714,2721  l1:
  		if (infomask & HEAP_XMAX_IS_MULTI)
  		{
  			/* wait for multixact */
! 			MultiXactIdWait((MultiXactId) xwait, MultiXactStatusUpdate,
! 			NULL, infomask);
  			LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  			/*
--- 2715,2723 
  		if (infomask & HEAP_XMAX_IS_MULTI)
  		{
  			/* wait for multixact */
! 			MultiXactIdWait((MultiXactId) xwait, MultiXactStatusUpdate, infomask,
! 			relation, &tp.t_data->t_ctid, XLTW_Delete,
! 			NULL);
  			LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  			/*
***
*** 2741,2747  l1:
  		else
  		{
  			/* wait for regular transaction to end */
! 			XactLockTableWait(xwait);
  			LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  			/*
--- 2743,2749 
  		else
  		{
  			/* wait for regular transaction to end */
! 			XactLockTableWait(xwait, relation, &tp.t_data->t_ctid, XLTW_Delete);
  			LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  			/*
***
*** 3266,3273  l2:
  			int			remain;
  
  			/* wait for multixact */
! 			MultiXactIdWait((MultiXactId) xwait, mxact_status, &remain,
! 			infomask);
  			LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  			/*
--- 3268,3276 
  			int			remain;
  
  			/* wait for multixact */
! 			MultiXactIdWait((MultiXactId) xwait, mxact_status, infomask,
! 			relation, &oldtup.t_data->t_ctid, XLTW_Update,
! 			&remain);
  			LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  			/*
***
*** 3341,3347  l2:
  			else
  			{
  /* wait for regular transaction to end */
! XactLockTableWait(xwait);
  LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  /*
--- 3344,3351 
  			else
  			{
  /* wait for regular transaction to end */
! XactLockTableWait(xwait, relation, &oldtup.t_data->t_ctid,
!   XLTW_Update);
  LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  /*
***
*** 4402,4415  l3:
  if (nowait)
  {
  	if (!ConditionalMultiXactIdWait((MultiXactId) xwait,
! 	status, NULL, infomask))
  		ereport(ERROR,
  (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
   errmsg("could not obtain lock on row in relation \"%s\"",
  		RelationGetRelationName(relation;
  }
  else
! 	MultiXactIdWait((MultiXactId) xwait, status, NULL, infomask);
  
  /* if there are updates, follow the update chain */
  if (follow_updates &&
--- 4406,4423 
  if (nowait)
  {
  	if (!ConditionalMultiXactIdWait((MultiXactId) xwait,
!   status, infomask, relation,
! 	&tuple->t_data->t_ctid,
! 	XLTW_Lock, NULL))
  		ereport(ERROR,
  (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
   errmsg("could not obtain lock on row in relation \"%s\"",
  		Relat

Re: [HACKERS] plpgsql.warn_shadow



On 18/03/14 20:45, Pavel Stehule wrote:



2014-03-18 20:44 GMT+01:00 Petr Jelinek >:



On 18/03/14 20:36, Pavel Stehule wrote:


   
+To aid the user in finding instances of simple but common
problems before
+they cause harm, PL/PgSQL provides a
number of additional
+checks. When enabled, depending on the
configuration, they
+can be used to emit either a WARNING or an
ERROR
+during the compilation of a function.
+   

>>>provides a number of additional<<<

There will be only one check in 9.4, so this sentence is
confusing and should be reformulated.


Thanks, yeah I left that sentence unchanged from original patch,
maybe I should remove the word "number" there as it implies that
there are a lot of them, but I don't really want to change
everything to singular when the input is specified as list.


What about add sentence: in this moment only "shadowed-variables" is 
available?


There is something like that said 2 paragraphs down the line:

+  The default is "none". Currently the list of available checks
+  includes only one:
+  
+   
+shadowed-variables

--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Planner hints in Postgresql

2014-03-18 Thread Merlin Moncure

On Tue, Mar 18, 2014 at 11:53 AM, Claudio Freire  wrote:
>
> On Mon, Mar 17, 2014 at 8:47 PM, Tom Lane  wrote:
>>
>> Claudio Freire  writes:
>> > On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby  wrote:
>> >> Even better would be if the planner could estimate how bad a plan will
>> >> become if we made assumptions that turn out to be wrong.
>>
>> > That's precisely what risk estimation was about.
>>
>> Yeah.  I would like to see the planner's cost estimates extended to
>> include some sort of uncertainty estimate, whereupon risk-averse people
>> could ask it to prefer low-uncertainty plans over high-uncertainty ones
>> (the plans we typically choose for ORDER BY ... LIMIT queries being great
>> examples of the latter).  But it's a long way from wishing that to making
>> it so.  Right now it's not even clear (to me anyway) how we'd measure or
>> model such uncertainty.
>
> Well, currently, selectivity estimates based on MCV should be pretty
> low-uncertainty, whereas certainty of other estimates could be modeled as a
> random variable if ANALYZE gathered a few statistical moments (for variables
> that are prone to that kind of statistical analysis).

Sure, plus as noted you have cases where the planer makes SWAGs.  Each
of those SWAGs can introduce say (in the worst case) an order of
magnitude of error in the row count estimate.

> That alone could improve things considerably, and statistical info could be
> propagated along expressions to make it possible to model uncertainty in
> complex expressions as well.

But how would that work?  I see no solution adumbrated there :-).
Let's say you change the rowcount estimate to low/bestguess/high *and*
you only engage extra searches when there is enough disparity between
those values you still get exponentially more searches.  (my thinking
is that if bestguess estimated execution time is some user definable
amount faster then low/high at any node), a more skeptical plan is
introduced.   All that could end up being pessimal to the general case
though.

merlin

-- 
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] plpgsql.warn_shadow

2014-03-18 20:44 GMT+01:00 Petr Jelinek :

>
> On 18/03/14 20:36, Pavel Stehule wrote:
>
>>
>>
>> +To aid the user in finding instances of simple but common problems
>> before
>> +they cause harm, PL/PgSQL provides a number of
>> additional
>> +checks. When enabled, depending on the
>> configuration, they
>> +can be used to emit either a WARNING or an
>> ERROR
>> +during the compilation of a function.
>> +   
>>
>> >>>provides a number of additional<<<
>>
>> There will be only one check in 9.4, so this sentence is confusing and
>> should be reformulated.
>>
>
> Thanks, yeah I left that sentence unchanged from original patch, maybe I
> should remove the word "number" there as it implies that there are a lot of
> them, but I don't really want to change everything to singular when the
> input is specified as list.


What about add sentence: in this moment only "shadowed-variables" is
available?

Pavel


>
>
> --
>  Petr Jelinek  http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>

Re: [HACKERS] plpgsql.warn_shadow



On 18/03/14 20:36, Pavel Stehule wrote:


   
+To aid the user in finding instances of simple but common 
problems before
+they cause harm, PL/PgSQL provides a number of 
additional
+checks. When enabled, depending on the 
configuration, they
+can be used to emit either a WARNING or an 
ERROR

+during the compilation of a function.
+   

>>>provides a number of additional<<<

There will be only one check in 9.4, so this sentence is confusing and 
should be reformulated.


Thanks, yeah I left that sentence unchanged from original patch, maybe I 
should remove the word "number" there as it implies that there are a lot 
of them, but I don't really want to change everything to singular when 
the input is specified as list.


--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

On Tue, Mar 18, 2014 at 12:53 PM, Tom Lane  wrote:
> Alvaro Herrera  writes:
>> Please see my reply to Robert.  My proposal (in form of a patch) is
>>   while operating on tuple (0,2) in table "foo": updating tuple
>> Would this work for you?
>
> It's pretty lousy from a readability standpoint, even in English;
> I shudder to think what it might come out as after translation.
>
> I think the enum idea you floated is probably worth doing.  It's
> certainly no more complex than passing a string, no?

+1.  We've done similar things in tablecmds.c.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] plpgsql.warn_shadow

2014-03-18 Thread Marko Tiikkaja


On 3/18/14, 7:56 PM, Petr Jelinek wrote:

Ok, so I took the liberty of rewriting the patch so that it uses
plpgsql.extra_warnings and plpgsql.extra_errors configuration variables
with possible values "none", "all" and "shadow" ("none" being the default).
Updated doc and regression tests to reflect the code changes, everything
builds and tests pass just fine.


Cool, thanks!


I did one small change (that I think was agreed anyway) from Marko's
original patch in that warnings are only emitted during function
creation, no runtime warnings and no warnings for inline (DO) plpgsql
code either as I really don't think these optional warnings/errors
during runtime are a good idea.


Not super excited, but I can live with that.


Note that the patch does not really handle the list of values as list,
basically "all" and "shadow" are translated to true and proper handling
of this is left to whoever will want to implement additional checks. I
think this approach is fine as I don't see the need to build frameworks
here and it was same in the original patch.


Yeah, I don't think rushing all that logic into 9.4 would be such a good 
idea.  Especially since it's not necessary at all.



Regards,
Marko Tiikkaja


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] plpgsql.warn_shadow

Hello


Tomorrow I'll do a review

fast check



   
+To aid the user in finding instances of simple but common problems
before
+they cause harm, PL/PgSQL provides a number of
additional
+checks. When enabled, depending on the configuration,
they
+can be used to emit either a WARNING or an
ERROR
+during the compilation of a function.
+   

>>>provides a number of additional<<<

There will be only one check in 9.4, so this sentence is confusing and
should be reformulated.

Regards

Pavel



2014-03-18 20:29 GMT+01:00 Petr Jelinek :

>
> On 18/03/14 20:15, Pavel Stehule wrote:
>
>
> 2014-03-18 20:14 GMT+01:00 Simon Riggs :
>
>> On 18 March 2014 19:04, Pavel Stehule  wrote:
>>
>> > I don't think so only "shadow" is good name for some check. Maybe
>> > "shadow-variables-check"
>>
>>  "shadowed-variables" would be a better name.
>>
>
> +1
>
>
> Attached V4 uses "shadowed-variables" instead of "shadow".
>
> --
>  Petr Jelinek  http://www.2ndQuadrant.com/
>
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>

Re: [HACKERS] Wiki Page Draft for upcoming release

On 2014-03-18 19:28:53 +, Greg Stark wrote:
> It would be nice to be able to tell people that if they vacuum, then
> reindex and check all their foreign key constraints then they should
> be ok.

I don't think so:
http://archives.postgresql.org/message-id/20140317233919.GS16438%40awork2.anarazel.de

I still think a rewriting noop ALTER TABLE ... ALTER COLUMN col TYPE
old_type USING (col); is the only real thing to do.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wiki Page Draft for upcoming release

On Tue, Mar 18, 2014 at 7:05 PM, Greg Stark  wrote:
> I'll do a first pass now.

I fixed the "Causes" section. I haven't touched the other sections
which are pretty reasonable. It would be nice to have more suggestions
for what people should do other than dump/restore.

It would be nice to be able to tell people that if they vacuum, then
reindex and check all their foreign key constraints then they should
be ok. I think that's almost true except I'm not sure how to tell that
they've waited long enough before vacuuming and I'm not 100% sure
it'll fix the problem. (Also they could have lost a row which has
since had all all it's referencing rows deleted. The database won't be
able to help them find that.)

-- 
greg


-- 
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] plpgsql.warn_shadow



On 18/03/14 20:15, Pavel Stehule wrote:


2014-03-18 20:14 GMT+01:00 Simon Riggs >:


On 18 March 2014 19:04, Pavel Stehule mailto:pavel.steh...@gmail.com>> wrote:

> I don't think so only "shadow" is good name for some check. Maybe
> "shadow-variables-check"

"shadowed-variables" would be a better name.


+1


Attached V4 uses "shadowed-variables" instead of "shadow".

--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index bddd458..d6709fd 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -4711,6 +4711,51 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
   
 
   
+  
+   Additional compile-time checks
+
+   
+To aid the user in finding instances of simple but common problems before
+they cause harm, PL/PgSQL provides a number of additional
+checks. When enabled, depending on the configuration, they
+can be used to emit either a WARNING or an ERROR
+during the compilation of a function.
+   
+
+ 
+  These additional checks are enabled through the configuration variables
+  plpgsql.extra_warnings for warnings and 
+  plpgsql.extra_errors for errors. Both can be set either to
+  a comma-separated list of checks, "none" or "all".
+  The default is "none". Currently the list of available checks
+  includes only one:
+  
+   
+shadowed-variables
+
+ 
+  Checks if a declaration shadows a previously defined variable. For
+  example (with plpgsql.extra_warnings set to
+  shadowed-variables):
+
+CREATE FUNCTION foo(f1 int) RETURNS int AS $$
+DECLARE
+f1 int;
+BEGIN
+RETURN f1;
+END
+$$ LANGUAGE plpgsql;
+WARNING:  variable "f1" shadows a previously defined variable
+LINE 3: f1 int;
+^
+CREATE FUNCTION
+
+ 
+
+   
+  
+ 
+ 
  
 
   
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index 5afc2e5..8732efc 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -352,6 +352,9 @@ do_compile(FunctionCallInfo fcinfo,
 	function->out_param_varno = -1;		/* set up for no OUT param */
 	function->resolve_option = plpgsql_variable_conflict;
 	function->print_strict_params = plpgsql_print_strict_params;
+	/* only promote extra warnings and errors at CREATE FUNCTION time */
+	function->extra_warnings = plpgsql_extra_warnings && forValidator;
+	function->extra_errors = plpgsql_extra_errors && forValidator;
 
 	if (is_dml_trigger)
 		function->fn_is_trigger = PLPGSQL_DML_TRIGGER;
@@ -849,6 +852,9 @@ plpgsql_compile_inline(char *proc_source)
 	function->out_param_varno = -1;		/* set up for no OUT param */
 	function->resolve_option = plpgsql_variable_conflict;
 	function->print_strict_params = plpgsql_print_strict_params;
+	/* don't do extra validation for inline code as we don't want to add spam at runtime */
+	function->extra_warnings = false;
+	function->extra_errors = false;
 
 	plpgsql_ns_init();
 	plpgsql_ns_push(func_name);
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index c0cb585..98f7ddd 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -727,6 +727,20 @@ decl_varname	: T_WORD
 			  $1.ident, NULL, NULL,
 			  NULL) != NULL)
 			yyerror("duplicate declaration");
+
+		if (plpgsql_curr_compile->extra_warnings || plpgsql_curr_compile->extra_errors)
+		{
+			PLpgSQL_nsitem *nsi;
+			nsi = plpgsql_ns_lookup(plpgsql_ns_top(), false,
+	$1.ident, NULL, NULL, NULL);
+			if (nsi != NULL)
+ereport(plpgsql_curr_compile->extra_errors ? ERROR : WARNING,
+		(errcode(ERRCODE_DUPLICATE_ALIAS),
+		 errmsg("variable \"%s\" shadows a previously defined variable",
+$1.ident),
+		 parser_errposition(@1)));
+		}
+
 	}
 | unreserved_keyword
 	{
@@ -740,6 +754,20 @@ decl_varname	: T_WORD
 			  $1, NULL, NULL,
 			  NULL) != NULL)
 			yyerror("duplicate declaration");
+
+		if (plpgsql_curr_compile->extra_warnings || plpgsql_curr_compile->extra_errors)
+		{
+			PLpgSQL_nsitem *nsi;
+			nsi = plpgsql_ns_lookup(plpgsql_ns_top(), false,
+	$1, NULL, NULL, NULL);
+			if (nsi != NULL)
+ereport(plpgsql_curr_compile->extra_errors ? ERROR : WARNING,
+		(errcode(ERRCODE_DUPLICATE_ALIAS),
+		 errmsg("variable \"%s\" shadows a previously defined variable",
+$1),
+		 parser_errposition(@1)));
+		}
+
 	}
 ;
 
diff --git a/src/pl/plpgsql/src/pl_handler.c b/src/pl/plpgsql/src/pl_handler.c
index f21393a..a8e9210 100644
--- a/src/pl/plpgsql/src/pl_handler.c
+++ b/src/pl/plpgsql/src/pl_handler.c
@@ -25,6 +25,11 @@
 #include "utils/lsyscache.h"
 #include "utils/syscache.h"
 
+
+static bool plpgsql_extra_checks_check_hook(char **newvalue, void **extra, GucSou

Re: [HACKERS] plpgsql.warn_shadow

2014-03-18 20:14 GMT+01:00 Simon Riggs :

> On 18 March 2014 19:04, Pavel Stehule  wrote:
>
> > I don't think so only "shadow" is good name for some check. Maybe
> > "shadow-variables-check"
>
> "shadowed-variables" would be a better name.
>

+1


>
> --
>  Simon Riggs   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [HACKERS] plpgsql.warn_shadow

On 18 March 2014 19:04, Pavel Stehule  wrote:

> I don't think so only "shadow" is good name for some check. Maybe
> "shadow-variables-check"

"shadowed-variables" would be a better name.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_archivecleanup bug

2014-03-18 Thread Heikki Linnakangas


On 03/18/2014 09:04 PM, Simon Riggs wrote:

On 18 March 2014 18:55, Alvaro Herrera  wrote:


That said, I don't find comma expression to be particularly "not
simple".


Maybe, but we've not used it before anywhere in Postgres, so I don't
see a reason to start now. Especially since C is not the native
language of many people these days and people just won't understand
it.


Agreed. The psqlODBC code is littered with comma expressions, and the 
first time I saw it, it took me a really long time to figure out what 
"if (foo = malloc(...), foo) { } " meant. And I consider myself quite 
experienced with C.


- Heikki


--
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] B-tree descend for insertion locking

2014-03-18 Thread Peter Geoghegan

On Tue, Mar 18, 2014 at 4:12 AM, Heikki Linnakangas
 wrote:
> See attached patch. The new contract of _bt_getroot is a bit weird: it locks
> the returned page in the requested lock-mode, shared or exclusive, if the
> root page was also the leaf page. Otherwise it's locked in shared mode
> regardless off the requested lock mode. But OTOH, the new contract for
> _bt_search() is more clear now: it actually locks the returned page in the
> requested mode, where it used to only use the access parameter to decide
> whether to create a root page if the index was empty.

I had considered this myself, and am under the impression that the
only reason things work this way is because it makes the interface of
_bt_getroot() clearer. I think what you propose is logical, and you
should pursue it, but fwiw I'm not convinced that you've really
simplified _bt_search(). However, you have kind of made _bt_search()
into something that succinctly represents what is useful about Lehman
and Yao as compared to earlier concurrent locking techniques, and
that's a good thing. I would probably have remarked on that in the
comments if I were you. I certainly would not have left out some
reference to L&Y. You've removed an existing one where the lock
escalation occurs, emphasizing that it's safe, and I'd like to see you
at least compensate for that.

-- 
Peter Geoghegan

-- 
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] GSoC proposal. Index-only scans for GIST

On 03/18/2014 12:11 PM, Alexander Korotkov wrote:
> Josh,
> 
> Anastasia has already consulted to me in person. It is not big proposal.
> But for newbie who is not familiar with PostgreSQL code base and especially
> GiST it seems fair enough.
> 

Thanks, that's what I wanted to know.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] GSoC proposal. Index-only scans for GIST

2014-03-18 Thread Alexander Korotkov

Josh,

Anastasia has already consulted to me in person. It is not big proposal.
But for newbie who is not familiar with PostgreSQL code base and especially
GiST it seems fair enough.


With best regards,
Alexander Korotkov.

On Tue, Mar 18, 2014 at 9:16 PM, Josh Berkus  wrote:

> Alexander,
>
> Can you comment on the below proposal?  I'd like your opinion on how
> difficult it will be.
>
> On 03/18/2014 06:12 AM, Anastasia Lubennikova wrote:
> > Hello!
> > Here is the text of my proposal which I've applied to GSoC.
> > (and link
> >
> http://www.google-melange.com/gsoc/proposal/public/google/gsoc2014/lubennikovaav/5629499534213120
> )
> > Any suggestions and comments are welcome.
> >
> > *Project name*
> >
> > Support for index-only scans for GIST index
> >
> >
> >
> > *Brief review*
> >
> > Currently GiST index don't have index-only scan functionality. Index-only
> > scans are a major performance feature added to Postgres 9.2. They allow
> > certain types of queries to be satisfied just by retrieving data from
> > indexes, and not from tables. This feature for B-trees (implemented since
> > PostgreSQL-9.2) allows doing certain types of queries significantly
> faster.
> > This is achieved by reduction in the amount of I/O necessary to satisfy
> > queries. I think it will be useful to implement this feature for user
> > defined data types that use GiST index.
> >
> >
> >
> > *Benefits to the PostgreSQL Community*
> >
> > Faster GiST index search would be actual for many PostgreSQL applications
> > (for example some GIS systems).
> >
> >
> >  *Quantifiable results*
> >
> > Acceleration of GiST index search.
> >
> >
> > *Project details*
> >
> > 1. The GiST is a balanced tree structure like a B-tree, containing  > pointer> pairs. GiST key is a member of a user-defined class, and
> > represents some property that is true of all data items reachable from
> the
> > pointer associated with the key. The GiST provides a possibility to
> create
> > custom data types with indexed access methods and extensible set of
> queries.
> >
> > There are seven methods that an index operator class for GiST must
> provide,
> > and an eighth that is optional.
> >
> > -consistent
> >
> > -union
> >
> > -compress
> >
> > -decompress
> >
> > -penalty
> >
> > -picksplit
> >
> > -equal
> >
> > -distance (optional)
> >
> > I'm going to create new optional method fetch() in addition to existing.
> > Thus user can create a method of retrieving data from the index without
> > loss. It will be used when performing search queries to speed data
> > retrieving.
> >
> >
> >
> > 2. gistget algorithm (several parts omitted):
> >
> > Check the key
> > gistindex_keytest() - does this index tuple satisfy the scan key(s)?
> >
> > Scan all items on the GiST index page and insert them into the queue (or
> > directly to output areas)
> >
> > plain scan
> >
> > Heap tuple TIDs are returned into so->pageData[]
> >
> > ordered scan
> >
> > Heap tuple TIDs are pushed into individual search queue items
> >
> > If the fetch() is specified by the developer, then using it, algorithm
> can
> > retrieve the data directly to output areas at this stage, without
> reference
> > to the heap.
> >
> >
> > 3. Create function gistcanreturn to check whether fetch() is specified by
> > user.
> >
> > Amcanreturn - Function to check whether index supports index-only scans,
> or
> > zero if none
> >
> > There is the question of support index-only scans for multicolumn
> indexes.
> > Probably it would require extend the interface to add separate columns
> > checking.
> >
> > To solve this question I need the community's help.
> >
> >
> > 4. Add details for index only scans into gistcostestimate function
> >
> >
> >
> >  *Links*
> >
> > 1) Hellerstein J. M., Naughton J. F., Pfeffer A. Generalized search
> > trees for database systems. - September, 1995.
> >
> > 2) http://www.sai.msu.su/~megera/postgres/gist/
> >
> > 3) PostgreSQL 9.3.3 Documentation: chapters 11. Indexes, 55. GiST
> > Indexes.
> >
>
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>

Re: [HACKERS] Portability issues in shm_mq

On Tue, Mar 18, 2014 at 12:15 PM, Tom Lane  wrote:
>>  It's tempting to instead add one or more tests that we specifically
>> choose to have values we think are likely to exercise
>> platform-specific differences or otherwise find bugs - e.g. just add a
>> second test where the queue size and message length are both odd.
>
> Meh.  I think you're putting a bit too much faith in your ability to
> predict the locus of bugs that you think aren't there.

Well, I'm open to suggestions.

>> maybe at a test where the queue is smaller than the message size, so
>> that every message wraps (multiple times?).
>
> Does the code support messages larger than the queue size?  If so, yes,
> that case clearly oughta be tested.

Yep.  You should be able to send and receive any message that fits
within MaxAllocSize on even the smallest possible queue. Performance
may suck, but if that's an issue for you then don't use such a blasted
small queue.  The intended use of this is to stream (potentially long)
error messages or (potentially long and numerous) tuples between
cooperating backends without having to preallocate space for all the
data you want to send (which won't be any more feasible in a DSM than
it would be in the main segment).

Actually, you should be able to send or receive arbitrarily large
messages if you change the MemoryContextAlloc call in shm_mq.c to
MemoryContextAllocHuge, but I can't see any compelling reason to do
that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] GSoC proposal. Index-only scans for GIST

2014-03-18 Thread Alexander Korotkov

On Tue, Mar 18, 2014 at 5:12 PM, Anastasia Lubennikova <
lubennikov...@gmail.com> wrote:

> 2. gistget algorithm (several parts omitted):
>
> Check the key
> gistindex_keytest() - does this index tuple satisfy the scan key(s)?
>
> Scan all items on the GiST index page and insert them into the queue (or
> directly to output areas)
>
> plain scan
>
> Heap tuple TIDs are returned into so->pageData[]
>
> ordered scan
>
> Heap tuple TIDs are pushed into individual search queue items
>
> If the fetch() is specified by the developer, then using it, algorithm can
> retrieve the data directly to output areas at this stage, without reference
> to the heap.
>

I think there are following changes to be made to GiST code:
1) When next consistent IndexTuple is found extract original Datums using
"fetch" method.
2) Put those original Datums to queue.
3) When returning next ItemPointer from queue put original Datums to
IndexScanDesc (into xs_itup).

 3. Create function gistcanreturn to check whether fetch() is specified by
> user.
>
> Amcanreturn - Function to check whether index supports index-only scans,
> or zero if none
>
> There is the question of support index-only scans for multicolumn indexes.
> Probably it would require extend the interface to add separate columns
> checking.
>
> To solve this question I need the community's help.
>
>
> 4. Add details for index only scans into gistcostestimate function
>

Also, another part of work to be mentioned is to implement fetch function
for all suitable opclasses.


With best regards,
Alexander Korotkov.

Re: [HACKERS] Wiki Page Draft for upcoming release

On Tue, Mar 18, 2014 at 6:41 PM, Josh Berkus  wrote:
>
> Anyone?  We're going public with this in 36 hours, so I'd love for it to
> actually be correct.

I'll do a first pass now.


-- 
greg


-- 
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] plpgsql.warn_shadow

2014-03-18 19:56 GMT+01:00 Petr Jelinek :

>
> On 18/03/14 13:43, Pavel Stehule wrote:
>
> 2014-03-18 13:23 GMT+01:00 Petr Jelinek 
>
>>
>>  Agree that compile_errors dos not make sense, additional_errors and
>> additional_warnings seems better, maybe plpgsql.extra_warnings and
>> plpgsql.extra_errors?
>>
>
>  extra* sounds better
>
>
> Ok, so I took the liberty of rewriting the patch so that it uses
> plpgsql.extra_warnings and plpgsql.extra_errors configuration variables
> with possible values "none", "all" and "shadow" ("none" being the default).
> Updated doc and regression tests to reflect the code changes, everything
> builds and tests pass just fine.
>

I don't think so only "shadow" is good name for some check. Maybe
"shadow-variables-check"

??


>
> I did one small change (that I think was agreed anyway) from Marko's
> original patch in that warnings are only emitted during function creation,
> no runtime warnings and no warnings for inline (DO) plpgsql code either as
> I really don't think these optional warnings/errors during runtime are a
> good idea.
>
> Note that the patch does not really handle the list of values as list,
> basically "all" and "shadow" are translated to true and proper handling of
> this is left to whoever will want to implement additional checks. I think
> this approach is fine as I don't see the need to build frameworks here and
> it was same in the original patch.
>
> --
>  Petr Jelinek  http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>

Re: [HACKERS] pg_archivecleanup bug

On 18 March 2014 18:55, Alvaro Herrera  wrote:

> That said, I don't find comma expression to be particularly "not
> simple".

Maybe, but we've not used it before anywhere in Postgres, so I don't
see a reason to start now. Especially since C is not the native
language of many people these days and people just won't understand
it.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Failure while inserting parent tuple to B-tree is not fun

2014-03-18 Thread Heikki Linnakangas


On 02/06/2014 06:42 AM, Peter Geoghegan wrote:

I'm not sure about this:


*** _bt_findinsertloc(Relation rel,
*** 675,680 
--- 701,707 
static void
_bt_insertonpg(Relation rel,
   Buffer buf,
+  Buffer cbuf,
   BTStack stack,
   IndexTuple itup,
   OffsetNumber newitemoff,


Wouldn't lcbuf be a better name for the new argument? After all, in
relevant contexts 'buf' is the parent of both of the pages post-split,
but there are two children in play here. You say:


+  *When inserting to a non-leaf page, 'cbuf' is the left-sibling 
of the
+  *page we're inserting the downlink for.  This function will 
clear the
+  *INCOMPLETE_SPLIT flag on it, and release the buffer.


I suggest also noting in comments at this point that cbuf/the
left-sibling is the "old half" from the split.

Even having vented about cbuf's name, I can kind of see why you didn't
call it lcbuf: we already have an "BTPageOpaque lpageop" variable for
the special 'buf' metadata within the _bt_insertonpg() function; so
there might be an ambiguity between the possibly-soon-to-be-left page
(the target page) and the *child* left page that needs to have its
flag cleared. Although I still think you should change the name of
"lpageop" (further details follow).

Consider this:


/* release buffers */
+   if (!P_ISLEAF(lpageop))
+   _bt_relbuf(rel, cbuf);


(Rebased, so this looks a bit different to your original version IIRC).

This is checking that the _bt_insertonpg() target page (whose special
data lpageop points to) is not a leaf page, and releasing the *child*
left page if it isn't. This is pretty confusing. So I suggest naming
"lpageop" "tpageop" ('t' for target, a terminology already used in the
comments above the function).


I don't know, the buf/page/lpageop variable names are used in many 
functions in nbtinsert.c. Perhaps they should all be renamed, but I 
think it's fine as it is, and not this patch's responsibility anyway. 
(The lpageop name makes sense when the page has to be split, and the 
page becomes the left page. Otherwise, admittedly, not so much)



Also, I suggest you change this existing code comment:

  * On entry, we must have the right buffer in which to do the
  * insertion, and the buffer must be pinned and write-locked.  
On return,
  * we will have dropped both the pin and the lock on the buffer.

Change "right" to "correct" here. Minor point, but "right" is a loaded word.


Fixed.


But why are you doing new things while checking P_ISLEAF(lpageop) in
_bt_insertonpg() to begin with? Would you not be better off doing
things when there is a child buffer passed (e.g. "if
(BufferIsValid(cbuf))..."), and only then asserting
!P_ISLEAF(lpageop)? That seems to me to more naturally establish the
context of "_bt_insertonpg() is called to insert downlink after
split". Although maybe that conflates things within "XLOG stuff". Hmm.


Changed that way for the places where we deal with the incomplete-split 
flag.


I committed the patch now. Thanks for the review!

Before committing, I spotted a bug in the way the new page-deletion code 
interacts with this: there was a check that the page that's about to be 
deleted was not marked with the INCOMPLETE_SPLIT flag, it was possible 
that the page was the right half of an incomplete split, and so didn't 
have a downlink. Vacuuming that failed with an "failed to re-find parent 
key" error. I fixed that by checking that the left sibling is also not 
marked with the flag.


It would be fairly easy to delete a page that is the right half of an 
incomplete split. Such a page doesn't have a downlink pointing to it, so 
just skip removing it, and instead clear the INCOMPLETE_SPLIT flag in 
the left sibling. But deleting incompletely split pages isn't important 
from a disk-space point of view, they should be extremely rare, so I 
decided to just punt.


- Heikki


--
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] Portability issues in shm_mq

After the last round of changes, I can confirm that my original test with
UTF8 locale works, and my HPPA box is happy too.  We could still stand
to improve the regression test though.

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] plpgsql.warn_shadow



On 18/03/14 13:43, Pavel Stehule wrote:
2014-03-18 13:23 GMT+01:00 Petr Jelinek >



Agree that compile_errors dos not make sense, additional_errors
and additional_warnings seems better, maybe plpgsql.extra_warnings
and plpgsql.extra_errors?


extra* sounds better


Ok, so I took the liberty of rewriting the patch so that it uses 
plpgsql.extra_warnings and plpgsql.extra_errors configuration variables 
with possible values "none", "all" and "shadow" ("none" being the default).
Updated doc and regression tests to reflect the code changes, everything 
builds and tests pass just fine.


I did one small change (that I think was agreed anyway) from Marko's 
original patch in that warnings are only emitted during function 
creation, no runtime warnings and no warnings for inline (DO) plpgsql 
code either as I really don't think these optional warnings/errors 
during runtime are a good idea.


Note that the patch does not really handle the list of values as list, 
basically "all" and "shadow" are translated to true and proper handling 
of this is left to whoever will want to implement additional checks. I 
think this approach is fine as I don't see the need to build frameworks 
here and it was same in the original patch.


--
 Petr Jelinek  http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index bddd458..56ee2b3 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -4711,6 +4711,51 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
   
 
   
+  
+   Additional compile-time checks
+
+   
+To aid the user in finding instances of simple but common problems before
+they cause harm, PL/PgSQL provides a number of additional
+checks. When enabled, depending on the configuration, they
+can be used to emit a WARNING or an ERROR
+during the compilation of a function.
+   
+
+ 
+  These additional checks are enabled through the configuration variables
+  plpgsql.extra_warnings for warnings and 
+  plpgsql.extra_errors for errors. Both can be set either to
+  a comma-separated list of checks, "none" or "all".
+  The default is "none". Currently the list of available checks
+  includes only one:
+  
+   
+shadow
+
+ 
+  Checks if a declaration shadows a previously defined variable. For
+  example (with plpgsql.extra_warnings set to
+  shadow):
+
+CREATE FUNCTION foo(f1 int) RETURNS int AS $$
+DECLARE
+f1 int;
+BEGIN
+RETURN f1;
+END
+$$ LANGUAGE plpgsql;
+WARNING:  variable "f1" shadows a previously defined variable
+LINE 3: f1 int;
+^
+CREATE FUNCTION
+
+ 
+
+   
+  
+ 
+ 
  
 
   
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index 5afc2e5..8732efc 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -352,6 +352,9 @@ do_compile(FunctionCallInfo fcinfo,
 	function->out_param_varno = -1;		/* set up for no OUT param */
 	function->resolve_option = plpgsql_variable_conflict;
 	function->print_strict_params = plpgsql_print_strict_params;
+	/* only promote extra warnings and errors at CREATE FUNCTION time */
+	function->extra_warnings = plpgsql_extra_warnings && forValidator;
+	function->extra_errors = plpgsql_extra_errors && forValidator;
 
 	if (is_dml_trigger)
 		function->fn_is_trigger = PLPGSQL_DML_TRIGGER;
@@ -849,6 +852,9 @@ plpgsql_compile_inline(char *proc_source)
 	function->out_param_varno = -1;		/* set up for no OUT param */
 	function->resolve_option = plpgsql_variable_conflict;
 	function->print_strict_params = plpgsql_print_strict_params;
+	/* don't do extra validation for inline code as we don't want to add spam at runtime */
+	function->extra_warnings = false;
+	function->extra_errors = false;
 
 	plpgsql_ns_init();
 	plpgsql_ns_push(func_name);
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index c0cb585..98f7ddd 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -727,6 +727,20 @@ decl_varname	: T_WORD
 			  $1.ident, NULL, NULL,
 			  NULL) != NULL)
 			yyerror("duplicate declaration");
+
+		if (plpgsql_curr_compile->extra_warnings || plpgsql_curr_compile->extra_errors)
+		{
+			PLpgSQL_nsitem *nsi;
+			nsi = plpgsql_ns_lookup(plpgsql_ns_top(), false,
+	$1.ident, NULL, NULL, NULL);
+			if (nsi != NULL)
+ereport(plpgsql_curr_compile->extra_errors ? ERROR : WARNING,
+		(errcode(ERRCODE_DUPLICATE_ALIAS),
+		 errmsg("variable \"%s\" shadows a previously defined variable",
+$1.ident),
+		 parser_errposition(@1)));
+		}
+
 	}
 | unreserved_keyword
 	{
@@ -740,6 +754,20 @@ decl_varname	: T_WORD
 			  $1, NULL, NULL,
 			  NULL) != NULL)
 			yyerror("duplicate declaration");
+
+		if (plpgsql_curr_compile->extra_warn

Re: [HACKERS] pg_archivecleanup bug

Simon Riggs escribió:
> On 18 March 2014 18:18, Bruce Momjian  wrote:
> > On Tue, Mar 18, 2014 at 06:11:30PM +, Simon Riggs wrote:

> >> Why make style changes at all? A patch with no style changes would
> >> mean backpatch and HEAD versions would be the same.
> >
> > The old style had errno set in two places in the loop, while the new
> > style has it set in only one place.
> 
> Seems better to leave the previously-good coding in place. ISTM to be
> clearer to use simple C.

If you're saying we should use that style in all readdir loops, with the
errno=0 before the loop and at the bottom of it, I don't disagree.
Let's just make sure they're all safe though (i.e. watch out for
"continue" for instance).

That said, I don't find comma expression to be particularly "not
simple".

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_archivecleanup bug

On 18 March 2014 18:18, Bruce Momjian  wrote:
> On Tue, Mar 18, 2014 at 06:11:30PM +, Simon Riggs wrote:
>> On 18 March 2014 18:01, Bruce Momjian  wrote:
>> > On Tue, Mar 18, 2014 at 04:17:53PM +, Simon Riggs wrote:
>> >> > While I am not a fan of backpatching, the fact we are ignoring errors in
>> >> > some critical cases seems the non-cosmetic parts should be backpatched.
>> >>
>> >> pg_resetxlog was not an offender here; its coding was sound.
>> >>
>> >> We shouldn't be discussing backpatching a patch that contains changes
>> >> to coding style.
>> >
>> > I was going to remove the coding style changes to pg_resetxlog from the
>> > backpatched portion.
>>
>> Why make style changes at all? A patch with no style changes would
>> mean backpatch and HEAD versions would be the same.
>
> The old style had errno set in two places in the loop, while the new
> style has it set in only one place.

Seems better to leave the previously-good coding in place. ISTM to be
clearer to use simple C.

You're doing this anyway for the backpatch, so I'm not causing you any
more work. Better one patch than two.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_archivecleanup bug

Bruce Momjian escribió:
> On Tue, Mar 18, 2014 at 06:11:30PM +, Simon Riggs wrote:
> > On 18 March 2014 18:01, Bruce Momjian  wrote:
> > > On Tue, Mar 18, 2014 at 04:17:53PM +, Simon Riggs wrote:
> > >> > While I am not a fan of backpatching, the fact we are ignoring errors 
> > >> > in
> > >> > some critical cases seems the non-cosmetic parts should be backpatched.
> > >>
> > >> pg_resetxlog was not an offender here; its coding was sound.
> > >>
> > >> We shouldn't be discussing backpatching a patch that contains changes
> > >> to coding style.
> > >
> > > I was going to remove the coding style changes to pg_resetxlog from the
> > > backpatched portion.
> > 
> > Why make style changes at all? A patch with no style changes would
> > mean backpatch and HEAD versions would be the same.
> 
> The old style had errno set in two places in the loop, while the new
> style has it set in only one place.

I think it makes more sense to have all callsites look the same in all
supported branches.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Wiki Page Draft for upcoming release

On 03/17/2014 05:49 PM, Josh Berkus wrote:
> All,
> 
> https://wiki.postgresql.org/wiki/20140320UpdateIssues
> 
> I'm sure my explanation of the data corruption issue is not correct, so
> please fix it.  Thanks!
> 

Anyone?  We're going public with this in 36 hours, so I'd love for it to
actually be correct.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] Risk Estimation WAS: Planner hints in Postgresql

Atri Sharma  writes:
> One of the factors that leads to bad estimates is that the histogram of the
> values of a column maintained by the planner gets old by time and the data
> in the column changes. So, the histogram is no longer a quite accurate view
> of the data and it leads to bad selectivity.

TBH, this is so far down the list of problems that it'll be a long time
before we need to worry about it.  It's certainly not the number one
priority for any project to model risk in the planner.

The thing that I think is probably the number one problem is estimates
that depend on an assumption of uniform distribution of sought-after rows
among those encountered by a scan.  This is usually where bad plans for
LIMIT queries are coming from.  We could certainly add some sort of fudge
factor to those costs, but I'd like to have a more-or-less principled
framework for doing so.

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] Risk Estimation WAS: Planner hints in Postgresql

2014-03-18 Thread Atri Sharma

On Tue, Mar 18, 2014 at 11:43 PM, Josh Berkus  wrote:

>
> > On Mon, Mar 17, 2014 at 8:47 PM, Tom Lane  wrote:
> >> Yeah.  I would like to see the planner's cost estimates extended to
> >> include some sort of uncertainty estimate, whereupon risk-averse people
> >> could ask it to prefer low-uncertainty plans over high-uncertainty ones
> >> (the plans we typically choose for ORDER BY ... LIMIT queries being
> great
> >> examples of the latter).  But it's a long way from wishing that to
> making
> >> it so.  Right now it's not even clear (to me anyway) how we'd measure or
> >> model such uncertainty.
>
>

I have been thinking of some ways to have a risk estimate of each
selectivity that our planner gives. I think a way to do it is as follows:

One of the factors that leads to bad estimates is that the histogram of the
values of a column maintained by the planner gets old by time and the data
in the column changes. So, the histogram is no longer a quite accurate view
of the data and it leads to bad selectivity.

One thing we can try to do is to add a factor of error that we feel the
selectivity given can have. This allows us to factor in the probability
that the data changed and the estimate of the difference of the current
histogram and the histogram of the actual data currently present in the
column in the table.

We can use Central Limit Theorem (
http://en.wikipedia.org/wiki/Central_limit_theorem). Essentially, what the
theorem says is that given a distribution that has finite variance and
finite mean, we can take random independent samples from the data and
calculate the standard deviation and the mean of the sample. If we have
large enough number of samples and if we plot the mean and SD, they would
follow a normal distribution.

What is interesting is that this can allow us to predict the SD of a given
dataset from the curve and the SD should be directly proportional to the
deviation it has from the given planner histogram.

I am no mathematician hence its hard for me to explain. I think this link
[1] will be more helpful.

So, we can have a probability value for the random variable and that shall
model the confidence we have in our estimate.

I may be wrong in some parts but I hope I have been able to convey the
general idea.

If this idea develops, I shall be happy to work on this but my hands are
full in ROLLUPS right now, so for my part it shall take time. I just want
to float the idea and get a general feel about the idea right now.

Please let me know your comments and feedback on this.

Regards,

Atri

[1]: http://www.theriac.org/DeskReference/viewDocument.php?id=177
-- 
Regards,

Atri
*l'apprenant*

Re: [HACKERS] Portability issues in shm_mq

and...@anarazel.de (Andres Freund) writes:
> On 2014-03-18 13:31:47 -0400, Robert Haas wrote:
>> Well, I definitely forgot that.  I'll count myself lucky if that's the
>> only problem.

> One minor thing missing, patch attached.

setup_dynamic_shared_memory needed some more hacking too.  Committed.

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_archivecleanup bug

2014-03-18 Thread Bruce Momjian

On Tue, Mar 18, 2014 at 06:11:30PM +, Simon Riggs wrote:
> On 18 March 2014 18:01, Bruce Momjian  wrote:
> > On Tue, Mar 18, 2014 at 04:17:53PM +, Simon Riggs wrote:
> >> > While I am not a fan of backpatching, the fact we are ignoring errors in
> >> > some critical cases seems the non-cosmetic parts should be backpatched.
> >>
> >> pg_resetxlog was not an offender here; its coding was sound.
> >>
> >> We shouldn't be discussing backpatching a patch that contains changes
> >> to coding style.
> >
> > I was going to remove the coding style changes to pg_resetxlog from the
> > backpatched portion.
> 
> Why make style changes at all? A patch with no style changes would
> mean backpatch and HEAD versions would be the same.

The old style had errno set in two places in the loop, while the new
style has it set in only one place.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] Risk Estimation WAS: Planner hints in Postgresql

> On Mon, Mar 17, 2014 at 8:47 PM, Tom Lane  wrote:
>> Yeah.  I would like to see the planner's cost estimates extended to
>> include some sort of uncertainty estimate, whereupon risk-averse people
>> could ask it to prefer low-uncertainty plans over high-uncertainty ones
>> (the plans we typically choose for ORDER BY ... LIMIT queries being great
>> examples of the latter).  But it's a long way from wishing that to making
>> it so.  Right now it's not even clear (to me anyway) how we'd measure or
>> model such uncertainty.

This is not a model, but here's some starting thoughts:

A "high risk" plan has two components:

a) our statistical data is out-of-date or inadequate

b) the potential execution time if our estimates of selectivity are
wrong is high

c) the cost ratio of certain operations is wrong.

Factor (a) can be modeled two ways:

1. If last_analyze is a long time ago, we have increased the risk.
   (Ideally, we'd have some idea of the change rate on the table vs.
the last analyze time; right now we don't have those stats)

2. Certain patterns, such as multi-column selectivity and GIN/GiST
selectivity are known to have poor estimates, and be higher risk.
Certainly selectivity functions which have been programmed with a flat
coefficient (like default 0.05 selectivity for gist_ops) could also
return a risk factor which is fairly high.

Factor (b) can be modeled simply by estimating the cost of a plan where
all row estimates are changed by 10X, or even better by a calculation on
the risk factor calculated in (a).  This would then give us the "failure
cost" of the bad plan.  Note that we need to estimate in both
directions, both for higher estimates and lower ones; "abort early"
plans fail because the rows returned are lower than expected, for example.

(b) estimation would be expensive if we did every combination of the
entire plan with wrong estimates, so I'm wondering if it would be
adequate to just estimate the node selectivity being off on a per-node
basis.

(c) we can't realistically estimate for at all (i.e. if we knew the cost
factor was wrong, we'd fix it) so I suggest ignoring it for risk estimation.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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_archivecleanup bug

On 18 March 2014 18:01, Bruce Momjian  wrote:
> On Tue, Mar 18, 2014 at 04:17:53PM +, Simon Riggs wrote:
>> > While I am not a fan of backpatching, the fact we are ignoring errors in
>> > some critical cases seems the non-cosmetic parts should be backpatched.
>>
>> pg_resetxlog was not an offender here; its coding was sound.
>>
>> We shouldn't be discussing backpatching a patch that contains changes
>> to coding style.
>
> I was going to remove the coding style changes to pg_resetxlog from the
> backpatched portion.

Why make style changes at all? A patch with no style changes would
mean backpatch and HEAD versions would be the same.

>> ISTM we should change the code with missing checks to adopt the coding
>> style of pg_resetxlog, not the other way around.
>>
>> I assume you or Kevin have this in hand and you don't want me to apply
>> the patch? (Since it was originally my bug)
>
> I know the email subject says pg_archivecleanup but the problem is all
> over our code.

Yes, understood.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_archivecleanup bug

2014-03-18 Thread Bruce Momjian

On Tue, Mar 18, 2014 at 04:17:53PM +, Simon Riggs wrote:
> > While I am not a fan of backpatching, the fact we are ignoring errors in
> > some critical cases seems the non-cosmetic parts should be backpatched.
> 
> pg_resetxlog was not an offender here; its coding was sound.
> 
> We shouldn't be discussing backpatching a patch that contains changes
> to coding style.

I was going to remove the coding style changes to pg_resetxlog from the
backpatched portion.

> ISTM we should change the code with missing checks to adopt the coding
> style of pg_resetxlog, not the other way around.
> 
> I assume you or Kevin have this in hand and you don't want me to apply
> the patch? (Since it was originally my bug)

I know the email subject says pg_archivecleanup but the problem is all
over our code.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
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] Portability issues in shm_mq

On 2014-03-18 13:31:47 -0400, Robert Haas wrote:
> Well, I definitely forgot that.  I'll count myself lucky if that's the
> only problem.

One minor thing missing, patch attached.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/contrib/test_shm_mq/test.c b/contrib/test_shm_mq/test.c
index dba5e69..5ff1e9a 100644
--- a/contrib/test_shm_mq/test.c
+++ b/contrib/test_shm_mq/test.c
@@ -254,12 +254,12 @@ verify_message(Size origlen, char *origdata, Size newlen, char *newdata)
 	if (origlen != newlen)
 		ereport(ERROR,
 (errmsg("message corrupted"),
- errdetail("The original message was " UINT64_FORMAT " bytes but the final message is " UINT64_FORMAT " bytes.",
+ errdetail("The original message was %zu bytes but the final message is %zu bytes.",
 	 origlen, newlen)));
 
 	for (i = 0; i < origlen; ++i)
 		if (origdata[i] != newdata[i])
 			ereport(ERROR,
 	(errmsg("message corrupted"),
-	 errdetail("The new and original messages differ at byte " UINT64_FORMAT " of " UINT64_FORMAT ".", i, origlen)));
+	 errdetail("The new and original messages differ at byte %zu of %zu.", i, origlen)));
 }

-- 
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] Portability issues in shm_mq

On Tue, Mar 18, 2014 at 1:16 PM, Tom Lane  wrote:
> I wrote:
>> Early returns not good:
>
> Also, these compiler messages are probably relevant:
>
> ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
> -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g 
> -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2  
> -I/usr/include/et  -c -o test.o test.c
> test.c: In function 'test_shm_mq':
> test.c:89:3: warning: passing argument 2 of 'shm_mq_receive' from 
> incompatible pointer type [enabled by default]
> In file included from test_shm_mq.h:18:0,
>  from test.c:19:
> ../../src/include/storage/shm_mq.h:61:22: note: expected 'Size *' but 
> argument is of type 'uint64 *'
> test.c: In function 'test_shm_mq_pipelined':
> test.c:198:4: warning: passing argument 2 of 'shm_mq_receive' from 
> incompatible pointer type [enabled by default]
> In file included from test_shm_mq.h:18:0,
>  from test.c:19:
> ../../src/include/storage/shm_mq.h:61:22: note: expected 'Size *' but 
> argument is of type 'uint64 *'
> ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
> -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g 
> -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2  
> -I/usr/include/et  -c -o setup.o setup.c
> ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
> -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g 
> -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2  
> -I/usr/include/et  -c -o worker.o worker.c
> worker.c: In function 'copy_messages':
> worker.c:193:3: warning: passing argument 2 of 'shm_mq_receive' from 
> incompatible pointer type [enabled by default]
> In file included from worker.c:25:0:
> ../../src/include/storage/shm_mq.h:61:22: note: expected 'Size *' but 
> argument is of type 'uint64 *'
>
> I'm thinking maybe you just forgot to update the contrib module.

Well, I definitely forgot that.  I'll count myself lucky if that's the
only problem.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Portability issues in shm_mq

I wrote:
> Early returns not good:

Also, these compiler messages are probably relevant:

ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g 
-fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2  
-I/usr/include/et  -c -o test.o test.c
test.c: In function 'test_shm_mq':
test.c:89:3: warning: passing argument 2 of 'shm_mq_receive' from incompatible 
pointer type [enabled by default]
In file included from test_shm_mq.h:18:0,
 from test.c:19:
../../src/include/storage/shm_mq.h:61:22: note: expected 'Size *' but argument 
is of type 'uint64 *'
test.c: In function 'test_shm_mq_pipelined':
test.c:198:4: warning: passing argument 2 of 'shm_mq_receive' from incompatible 
pointer type [enabled by default]
In file included from test_shm_mq.h:18:0,
 from test.c:19:
../../src/include/storage/shm_mq.h:61:22: note: expected 'Size *' but argument 
is of type 'uint64 *'
ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g 
-fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2  
-I/usr/include/et  -c -o setup.o setup.c
ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g 
-fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2  
-I/usr/include/et  -c -o worker.o worker.c
worker.c: In function 'copy_messages':
worker.c:193:3: warning: passing argument 2 of 'shm_mq_receive' from 
incompatible pointer type [enabled by default]
In file included from worker.c:25:0:
../../src/include/storage/shm_mq.h:61:22: note: expected 'Size *' but argument 
is of type 'uint64 *'

I'm thinking maybe you just forgot to update the contrib module.

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] GSoC proposal. Index-only scans for GIST

Alexander,

Can you comment on the below proposal?  I'd like your opinion on how
difficult it will be.

On 03/18/2014 06:12 AM, Anastasia Lubennikova wrote:
> Hello!
> Here is the text of my proposal which I've applied to GSoC.
> (and link
> http://www.google-melange.com/gsoc/proposal/public/google/gsoc2014/lubennikovaav/5629499534213120)
> Any suggestions and comments are welcome.
> 
> *Project name*
> 
> Support for index-only scans for GIST index
> 
> 
> 
> *Brief review*
> 
> Currently GiST index don't have index-only scan functionality. Index-only
> scans are a major performance feature added to Postgres 9.2. They allow
> certain types of queries to be satisfied just by retrieving data from
> indexes, and not from tables. This feature for B-trees (implemented since
> PostgreSQL-9.2) allows doing certain types of queries significantly faster.
> This is achieved by reduction in the amount of I/O necessary to satisfy
> queries. I think it will be useful to implement this feature for user
> defined data types that use GiST index.
> 
> 
> 
> *Benefits to the PostgreSQL Community*
> 
> Faster GiST index search would be actual for many PostgreSQL applications
> (for example some GIS systems).
> 
> 
>  *Quantifiable results*
> 
> Acceleration of GiST index search.
> 
> 
> *Project details*
> 
> 1. The GiST is a balanced tree structure like a B-tree, containing  pointer> pairs. GiST key is a member of a user-defined class, and
> represents some property that is true of all data items reachable from the
> pointer associated with the key. The GiST provides a possibility to create
> custom data types with indexed access methods and extensible set of queries.
> 
> There are seven methods that an index operator class for GiST must provide,
> and an eighth that is optional.
> 
> -consistent
> 
> -union
> 
> -compress
> 
> -decompress
> 
> -penalty
> 
> -picksplit
> 
> -equal
> 
> -distance (optional)
> 
> I'm going to create new optional method fetch() in addition to existing.
> Thus user can create a method of retrieving data from the index without
> loss. It will be used when performing search queries to speed data
> retrieving.
> 
> 
> 
> 2. gistget algorithm (several parts omitted):
> 
> Check the key
> gistindex_keytest() - does this index tuple satisfy the scan key(s)?
> 
> Scan all items on the GiST index page and insert them into the queue (or
> directly to output areas)
> 
> plain scan
> 
> Heap tuple TIDs are returned into so->pageData[]
> 
> ordered scan
> 
> Heap tuple TIDs are pushed into individual search queue items
> 
> If the fetch() is specified by the developer, then using it, algorithm can
> retrieve the data directly to output areas at this stage, without reference
> to the heap.
> 
> 
> 3. Create function gistcanreturn to check whether fetch() is specified by
> user.
> 
> Amcanreturn - Function to check whether index supports index-only scans, or
> zero if none
> 
> There is the question of support index-only scans for multicolumn indexes.
> Probably it would require extend the interface to add separate columns
> checking.
> 
> To solve this question I need the community's help.
> 
> 
> 4. Add details for index only scans into gistcostestimate function
> 
> 
> 
>  *Links*
> 
> 1) Hellerstein J. M., Naughton J. F., Pfeffer A. Generalized search
> trees for database systems. - September, 1995.
> 
> 2) http://www.sai.msu.su/~megera/postgres/gist/
> 
> 3) PostgreSQL 9.3.3 Documentation: chapters 11. Indexes, 55. GiST
> Indexes.
> 


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] json/jsonb/hstore operator precedence

Fwiw I'm finding myself repeatedly caught up by the operator
precedence rules when experimenting with jsonb:

stark=***# select  segment->'id' as id from flight_segments where
segment->>'marketing_airline_code' <>
segment->>'operating_airline_code' ;
ERROR:  42883: operator does not exist: text <> jsonb
LINE 2: ...segments where segment->>'marketing_airline_code' <> segment...
 ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LOCATION:  op_error, parse_oper.c:722
Time: 0.407 ms
stark=***# select  segment->'id' as id from flight_segments where
(segment->>'marketing_airline_code') <>
(segment->>'operating_airline_code') ;
 id
-
 "45866185"
 "95575359"


I don't think this is related to the jsonb patch -- json and hstore
have the same behaviour so jsonb is obviously going to follow suit.
The only option right now would be to use a higher precedence operator
like % or ^ for all of these data types which I'm not for. I suspect
it's a pipe dream to think we might be able to override the '.' and
changing the precedence of -> and ->> would be fraught...

I think the best we can do is to highlight it in the docs.

Incidentally it's a good thing there wasn't an implicit cast
text->jsonb. In this case it would have resulted in just a confusing
error of jsonb->>boolean not existing.


-- 
greg


-- 
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] Planner hints in Postgresql

2014-03-18 Thread Claudio Freire

On Mon, Mar 17, 2014 at 8:47 PM, Tom Lane  wrote:

> Claudio Freire  writes:
> > On Mon, Mar 17, 2014 at 7:01 PM, Jim Nasby  wrote:
> >> Even better would be if the planner could estimate how bad a plan will
> >> become if we made assumptions that turn out to be wrong.
>
> > That's precisely what risk estimation was about.
>
> Yeah.  I would like to see the planner's cost estimates extended to
> include some sort of uncertainty estimate, whereupon risk-averse people
> could ask it to prefer low-uncertainty plans over high-uncertainty ones
> (the plans we typically choose for ORDER BY ... LIMIT queries being great
> examples of the latter).  But it's a long way from wishing that to making
> it so.  Right now it's not even clear (to me anyway) how we'd measure or
> model such uncertainty.
>

Well, currently, selectivity estimates based on MCV should be pretty
low-uncertainty, whereas certainty of other estimates could be modeled as a
random variable if ANALYZE gathered a few statistical moments (for
variables that are prone to that kind of statistical analysis).

That alone could improve things considerably, and statistical info could be
propagated along expressions to make it possible to model uncertainty in
complex expressions as well.

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

Alvaro Herrera  writes:
> Please see my reply to Robert.  My proposal (in form of a patch) is
>   while operating on tuple (0,2) in table "foo": updating tuple
> Would this work for you?

It's pretty lousy from a readability standpoint, even in English;
I shudder to think what it might come out as after translation.

I think the enum idea you floated is probably worth doing.  It's
certainly no more complex than passing a string, no?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

Tom Lane escribió:
> Robert Haas  writes:
> > Well, if we're back to one version of the message, and I'm glad we
> > are, can we go back to saying:
> 
> > CONTEXT:  while updating tuple (0,2) in relation "public"."foo" of
> > database "postgres"
> 
> If I end up being the one who commits this, it's going to say
> 
> while updating tuple (0,2) in table "foo"
> 
> Not more, and not less.

Please see my reply to Robert.  My proposal (in form of a patch) is
  while operating on tuple (0,2) in table "foo": updating tuple
Would this work for you?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

Robert Haas  writes:
> Well, if we're back to one version of the message, and I'm glad we
> are, can we go back to saying:

> CONTEXT:  while updating tuple (0,2) in relation "public"."foo" of
> database "postgres"

If I end up being the one who commits this, it's going to say

while updating tuple (0,2) in table "foo"

Not more, and not less.  It is not project style to include schema names
(much less database names) in error messages where they're not central to
the meaning.

One reason why not is that schema and database names are generally not
available without an extra lookup step, which you don't really want to do
in an error-reporting code path.  Every extra action you take increases
the risk of a cascading failure, so that the user will get something
unhelpful like "out of memory" rather than the oh-so-extra-helpful message
you wanted to print.  The added utility of the extra information, for most
cases, is insufficient to justify that risk.

Even without that argument, it's still not project style; why should this
message be randomly different from many hundreds of others?  If you want
to start a push to include schema names anywhere a table name is given,
that should be debated separately and then done in a reasonably uniform
fashion.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

Robert Haas escribió:
> On Tue, Mar 18, 2014 at 12:00 AM, Amit Kapila  wrote:
> >> Therefore I think the only case worth considering here is when
> >> database/relation/TID are all defined.  The other cases where there is
> >> partial information are dead code; and the case where there is nothing
> >> defined (such as the one in SnapBuildFindSnapshot) is already handled by
> >> simply not setting up a context at all.
> >
> > Right. So I think we should just keep one version of message.
> 
> Well, if we're back to one version of the message, and I'm glad we
> are, can we go back to saying:
> 
> CONTEXT:  while updating tuple (0,2) in relation "public"."foo" of
> database "postgres"

That isn't easy.  The callers would need to pass the whole message in
order for it to be translatable; and generating a format string in one
module and having the arguments be stapled on top in a separate module
doesn't seem a very good idea to me.  Currently we have this:

/* wait for regular transaction to end */
XactLockTableWait(xwait, relation, &tp.t_data->t_ctid,
/* translator: string is XactLockTableWait operation 
name */
  "deleting tuple");

And if we go with what you propose, we would have this:

/* wait for regular transaction to end */
XactLockTableWait(xwait, relation, &tp.t_data->t_ctid,
"while updating tuple (%u,%u) in relation \"%s\"")

which doesn't seem an improvement to me.

Now another idea would be to pass a code or something; so callers would
do
XactLockTableWait(xwait, relation, TID, XLTW_OPER_UPDATE);

and the callback would select the appropriate message.  Not really
excited about that, though.

In the current version of the patch, attached, I've reduced the callback
to this:

if (ItemPointerIsValid(info->ctid) && RelationIsValid(info->rel))
{
errcontext("while operating on tuple (%u,%u) in relation 
\"%s\": %s",
   ItemPointerGetBlockNumber(info->ctid),
   ItemPointerGetOffsetNumber(info->ctid),
   RelationGetRelationName(info->rel),
   _(info->opr_name));
}

Note that:
1. the database name is gone, as discussed
2. the schema name is gone too, because it requires a syscache lookup

Now we can go back to having a schema name, but I think we would have to
add an IsTransactionState() check first or something like that.  Or save
the schema name when setting up the callback, but this doesn't sound so
good (particularly considering that lock waits might end without
actually waiting at all, so this'd be wasted effort.)


If you have a better idea, I'm all ears.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/src/backend/access/heap/heapam.c
--- b/src/backend/access/heap/heapam.c
***
*** 105,115  static void GetMultiXactIdHintBits(MultiXactId multi, uint16 *new_infomask,
  	   uint16 *new_infomask2);
  static TransactionId MultiXactIdGetUpdateXid(TransactionId xmax,
  		uint16 t_infomask);
! static void MultiXactIdWait(MultiXactId multi, MultiXactStatus status,
! int *remaining, uint16 infomask);
! static bool ConditionalMultiXactIdWait(MultiXactId multi,
! 		   MultiXactStatus status, int *remaining,
! 		   uint16 infomask);
  static XLogRecPtr log_heap_new_cid(Relation relation, HeapTuple tup);
  static HeapTuple ExtractReplicaIdentity(Relation rel, HeapTuple tup, bool key_modified,
  		bool *copy);
--- 105,116 
  	   uint16 *new_infomask2);
  static TransactionId MultiXactIdGetUpdateXid(TransactionId xmax,
  		uint16 t_infomask);
! static void MultiXactIdWait(MultiXactId multi, MultiXactStatus status, uint16 infomask,
! Relation rel, ItemPointer ctid, const char *opr,
! int *remaining);
! static bool ConditionalMultiXactIdWait(MultiXactId multi, MultiXactStatus status,
! 		   uint16 infomask, Relation rel, ItemPointer ctid,
! 		   const char *opr, int *remaining);
  static XLogRecPtr log_heap_new_cid(Relation relation, HeapTuple tup);
  static HeapTuple ExtractReplicaIdentity(Relation rel, HeapTuple tup, bool key_modified,
  		bool *copy);
***
*** 2714,2721  l1:
  		if (infomask & HEAP_XMAX_IS_MULTI)
  		{
  			/* wait for multixact */
! 			MultiXactIdWait((MultiXactId) xwait, MultiXactStatusUpdate,
! 			NULL, infomask);
  			LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
  
  			/*
--- 2715,2724 
  		if (infomask & HEAP_XMAX_IS_MULTI)
  		{
  			/* wait for multixact */
! 			MultiXactIdWait((MultiXactId) xwait, MultiXactStatusUpdate, infomask,
! 			/* translator: string is XactLockTableWait operation name */
! 			relation, &tp.t_data->t_ctid, "deleti

Re: [HACKERS] Portability issues in shm_mq

I wrote:
> Robert Haas  writes:
>> First, can you retest this with the latest code?

> Yeah, on it now.

Early returns not good:

*** 
/Users/buildfarm/bf-data/HEAD/pgsql.93630/contrib/test_shm_mq/expected/test_shm_mq.out
  Tue Mar 18 12:00:18 2014
--- 
/Users/buildfarm/bf-data/HEAD/pgsql.93630/contrib/test_shm_mq/results/test_shm_mq.out
   Tue Mar 18 12:17:04 2014
***
*** 5,18 
  -- internal sanity tests fail.
  --
  SELECT test_shm_mq(32768, (select string_agg(chr(32+(random()*96)::int), '') 
from generate_series(1,400)), 1, 1);
!  test_shm_mq 
! -
!  
! (1 row)
! 
  SELECT test_shm_mq_pipelined(16384, (select 
string_agg(chr(32+(random()*96)::int), '') from generate_series(1,27)), 
200, 3);
!  test_shm_mq_pipelined 
! ---
!  
! (1 row)
! 
--- 5,12 
  -- internal sanity tests fail.
  --
  SELECT test_shm_mq(32768, (select string_agg(chr(32+(random()*96)::int), '') 
from generate_series(1,400)), 1, 1);
! ERROR:  message corrupted
! DETAIL:  The original message was 400 bytes but the final message is 
7492059346764176 bytes.
  SELECT test_shm_mq_pipelined(16384, (select 
string_agg(chr(32+(random()*96)::int), '') from generate_series(1,27)), 
200, 3);
! ERROR:  message corrupted
! DETAIL:  The original message was 27 bytes but the final message is 
7492059347033776 bytes.


This is C locale on a 32-bit machine, so you'll likely be seeing the same
complaint in already-online buildfarm members.

Note that size_t is definitely not int64 on this machine, so it looks to
me like your int64-ectomy was incomplete.  Those message lengths should
be impossible no matter what on this hardware.

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] Minimum supported version of Python?

2014-03-18 Thread David Johnston

Peter Eisentraut-2 wrote
> On 3/18/14, 11:22 AM, Andrew Dunstan wrote:
>> Actually, if you run a buildfarm animal you have considerable control
>> over what it tests.
> 
> I appreciate that.  My problem here isn't time or ideas or coding, but
> lack of hardware resources.  If I had hardware, I could set up tests for
> every build dependency under the sun.

I don't imagine there is enough churn in this area that having a constantly
testing buildfarm animal is a strong need; but a wiki page dedicated to
"Python Support in PostgreSQL" where we can publicly and officially release
testing results and commentary would be an improvement.

As it sounds like the only caveat to supporting 2.3 is that we don't
technically (or do we - is Decimal mandatory?) support an un-modified core
installation but require that at one add-on module be installed.  Assuming
that clears up all the errors Tom is seeing then saying that plpythonu works
with a slightly modified 2.3 on all current releases of PostgreSQL isn't a
stretch nor does it commit us to fixing bugs in the unlikely event that any
are discovered in two extremely stable environments.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Minimum-supported-version-of-Python-tp5796175p5796615.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_archivecleanup bug

On 13 March 2014 05:48, Bruce Momjian  wrote:
> On Mon, Dec  9, 2013 at 11:27:28AM -0500, Robert Haas wrote:
>> On Thu, Dec 5, 2013 at 6:15 PM, Tom Lane  wrote:
>> > But the other usages seem to be in assorted utilities, which
>> > will need to do it right for themselves.  initdb.c's walkdir() seems to
>> > have it right and might be a reasonable model to follow.  Or maybe we
>> > should invent a frontend-friendly version of ReadDir() rather than
>> > duplicating all the error checking code in ten-and-counting places?
>>
>> If there's enough uniformity in all of those places to make that
>> feasible, it certainly seems wise to do it that way.  I don't know if
>> that's the case, though - e.g. maybe some callers want to exit and
>> others do not.  pg_resetxlog wants to exit; pg_archivecleanup and
>> pg_standby most likely want to print an error and carry on.
>
> I have developed the attached patch which fixes all cases where
> readdir() wasn't checking for errno, and cleaned up the syntax in other
> cases to be consistent.
>
> While I am not a fan of backpatching, the fact we are ignoring errors in
> some critical cases seems the non-cosmetic parts should be backpatched.

pg_resetxlog was not an offender here; its coding was sound.

We shouldn't be discussing backpatching a patch that contains changes
to coding style.

ISTM we should change the code with missing checks to adopt the coding
style of pg_resetxlog, not the other way around.

I assume you or Kevin have this in hand and you don't want me to apply
the patch? (Since it was originally my bug)

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

On Tue, Mar 18, 2014 at 12:00 AM, Amit Kapila  wrote:
>> Therefore I think the only case worth considering here is when
>> database/relation/TID are all defined.  The other cases where there is
>> partial information are dead code; and the case where there is nothing
>> defined (such as the one in SnapBuildFindSnapshot) is already handled by
>> simply not setting up a context at all.
>
> Right. So I think we should just keep one version of message.

Well, if we're back to one version of the message, and I'm glad we
are, can we go back to saying:

CONTEXT:  while updating tuple (0,2) in relation "public"."foo" of
database "postgres"

Instead of:

CONTEXT:  while operating on tuple (0,2) in relation "public"."foo" of
database "postgres": updating tuple

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Portability issues in shm_mq

Robert Haas  writes:
> First, can you retest this with the latest code?

Yeah, on it now.

> If we want to inject some randomness into the test, which parameters
> do we want to randomize and over what ranges?

I think the message length is the only particularly interesting
parameter.  It'd be nice if the length varied *within* a test, but
that would take rather considerable restructuring, so maybe it's
not worth the trouble.

> Also, if a buildfarm
> critter falls over, how will we know what value triggered the failure?

Maybe we won't, but I think knowing that it does fail on platform X is
likely to be enough to find the problem.

>  It's tempting to instead add one or more tests that we specifically
> choose to have values we think are likely to exercise
> platform-specific differences or otherwise find bugs - e.g. just add a
> second test where the queue size and message length are both odd.

Meh.  I think you're putting a bit too much faith in your ability to
predict the locus of bugs that you think aren't there.

> maybe at a test where the queue is smaller than the message size, so
> that every message wraps (multiple times?).

Does the code support messages larger than the queue size?  If so, yes,
that case clearly oughta be tested.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

On Tue, Mar 18, 2014 at 11:59 AM, Greg Stark  wrote:
> On Tue, Mar 18, 2014 at 3:42 AM, Amit Kapila  wrote:
>> The message for exclusive lock on tuple print the database information.
>
> It is true that it is possible to have a deadlock or lock chains that
> involves locks on other databases.
> In this example the table "test" is not in the database that just
> logged the deadlock.
>
> STATEMENT:  create role test;
> ERROR:  deadlock detected
> DETAIL:  Process 8968 waits for ShareLock on transaction 1067; blocked
> by process 8973.
> Process 8973 waits for ShareLock on transaction 1064; blocked
> by process 8971.
> Process 8971 waits for ShareLock on transaction 1062; blocked
> by process 8968.
> Process 8968: create role test;
> Process 8973: insert into test values (2);
> Process 8971: create role test2;

This is a good point, but I think it's acceptable to leave out the
database name as Tom proposes.  The context message applies to what
the current backend was doing when the message got printed, and that's
always relative to the current database.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Portability issues in shm_mq

All right.

On Fri, Mar 14, 2014 at 4:43 PM, Tom Lane  wrote:
> Whilst setting up a buildfarm member on an old, now-spare Mac, I was
> somewhat astonished to discover that contrib/test_shm_mq crashes thus:
> TRAP: FailedAssertion("!(rb >= sizeof(uint64))", File: "shm_mq.c", Line: 429)
> but only in UTF8 locales, not in C locale.  You'd have bet your last
> dollar that that code was locale-independent, right?

First, can you retest this with the latest code?

> Recommendations:
>
> 1. Reduce the random() multiplier from 96 to 95.  In multibyte encodings
> other than UTF8, chr() would flat out reject values of 128, so this test
> case is unportable.
>
> 2. Why in the world is the test case testing exactly one message length
> that happens to be a multiple of 8?  Put some randomness into that,
> instead.
>
> 3. Either you need to work a bit harder at forcing alignment, or you need
> to fix shm_mq_receive to cope with split message length words.
>
> 4. The header comment for shm_mq_receive_bytes may once have described its
> API accurately, but that appears to have been a long long time ago in a
> galaxy far far away.  Please fix.

On these recommendations, I believe that #3 and #4 are now dealt with.
 That leaves #1 and #2.  #1 is of course easy, but I think we should
do them both together.

If we want to inject some randomness into the test, which parameters
do we want to randomize and over what ranges?  Also, if a buildfarm
critter falls over, how will we know what value triggered the failure?
 It's tempting to instead add one or more tests that we specifically
choose to have values we think are likely to exercise
platform-specific differences or otherwise find bugs - e.g. just add a
second test where the queue size and message length are both odd.  And
maybe at a test where the queue is smaller than the message size, so
that every message wraps (multiple times?).

Thoughts?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Minimum supported version of Python?

2014-03-18 Thread Peter Eisentraut

On 3/18/14, 11:22 AM, Andrew Dunstan wrote:
> Actually, if you run a buildfarm animal you have considerable control
> over what it tests.

I appreciate that.  My problem here isn't time or ideas or coding, but
lack of hardware resources.  If I had hardware, I could set up tests for
every build dependency under the sun.

-- 
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_archivecleanup bug

On 18 March 2014 15:50, Robert Haas  wrote:
> On Tue, Mar 18, 2014 at 11:36 AM, Simon Riggs  wrote:
>> Given the above, this means we've run for about 7 years without a
>> reported issue on this. If we are going to "make this better" by
>> actually having it throw errors in places that didn't throw errors
>> before, are we sure that is going to make people happier? The archive
>> cleanup isn't exactly critical in most cases, so dynamic errors don't
>> matter much.
>
> We report errors returned by system calls in many other places.  I
> can't see why this place should be any different.

Sure. Just wanted to make sure it's a conscious, explicit choice to do so.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Minimum supported version of Python?

2014-03-18 Thread Peter Eisentraut

On 3/17/14, 10:47 PM, Joshua D. Drake wrote:
> We shouldn't be supporting anything the community doesn't support.
> Python 2.3 is dead. We shouldn't actively support it nor suggest that we
> could or should via the docs.

The information that is available to me about this issue is lacking
details, but as far as I can tell, we are partially talking about
backbranches, which were released at a time when Python 2.3 and 2.4 were
not unreasonable targets.  We're not going to desupport build
dependencies in the middle of a stable release branch unless we have a
solid reason.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Patch: show relation and tuple infos of a lock to acquire

On Tue, Mar 18, 2014 at 3:42 AM, Amit Kapila  wrote:
> The message for exclusive lock on tuple print the database information.

It is true that it is possible to have a deadlock or lock chains that
involves locks on other databases.
In this example the table "test" is not in the database that just
logged the deadlock.

STATEMENT:  create role test;
ERROR:  deadlock detected
DETAIL:  Process 8968 waits for ShareLock on transaction 1067; blocked
by process 8973.
Process 8973 waits for ShareLock on transaction 1064; blocked
by process 8971.
Process 8971 waits for ShareLock on transaction 1062; blocked
by process 8968.
Process 8968: create role test;
Process 8973: insert into test values (2);
Process 8971: create role test2;


-- 
greg


-- 
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_archivecleanup bug

On Tue, Mar 18, 2014 at 11:36 AM, Simon Riggs  wrote:
> Given the above, this means we've run for about 7 years without a
> reported issue on this. If we are going to "make this better" by
> actually having it throw errors in places that didn't throw errors
> before, are we sure that is going to make people happier? The archive
> cleanup isn't exactly critical in most cases, so dynamic errors don't
> matter much.

We report errors returned by system calls in many other places.  I
can't see why this place should be any different.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Minimum supported version of Python?

2014-03-18 Thread Peter Eisentraut

On 3/17/14, 10:55 PM, Tom Lane wrote:
> It doesn't pass the regression tests.  Do you need more of a bug report
> than that?

It does pass the tests for me and others.  If you are seeing something
different, then we need to see some details, like what platform, what
version, what Python version, how installed, what PostgreSQL version,
how installed, actual diffs, etc.

-- 
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_archivecleanup bug

On 18 March 2014 14:15, Alvaro Herrera  wrote:
> Bruce Momjian escribió:
>> On Tue, Mar 18, 2014 at 10:03:46AM -0400, Robert Haas wrote:
>> > On Tue, Mar 18, 2014 at 9:56 AM, Tom Lane  wrote:
>> > > Bruce Momjian  writes:
>> > >> Very good point.  I have modified the patch to add this block in all
>> > >> cases where it was missing.  I started to wonder about the comment and
>> > >> if the Mingw fix was released.  Based on some research, I see this as
>> > >> fixed in mingw-runtime-3.2, released 2003-10-10.  That's pretty old.
>> > >
>> > > Yeah.  I would vote for removing that code in all branches.  There is no
>> > > reason to suppose somebody is going to install 8.4.22 on a machine that
>> > > they haven't updated mingw on since 2003.  Or, if you prefer, just remove
>> > > it in HEAD --- but going around and *adding* more copies seems like
>> > > make-work.  The fact that we've not heard complaints about the omissions
>> > > is good evidence that nobody's using the buggy mingw versions anymore.
>> >
>> > I don't think it is.  Right now we're not checking errno *at all* in a
>> > bunch of these places, so we're sure not going to get complaints about
>> > doing it incorrectly in those places.  Or do I need more caffeine?
>>
>> You are correct.  This code is seriously broken and I am susprised we
>> have not gotten more complaints.  Good thing readdir/closedir rarely
>> fail.

> back-patching

Some commentary on this...

Obviously, all errors are mine.

If pg_archivecleanup is a problem, then so is pg_standby a problem.

Given the above, this means we've run for about 7 years without a
reported issue on this. If we are going to "make this better" by
actually having it throw errors in places that didn't throw errors
before, are we sure that is going to make people happier? The archive
cleanup isn't exactly critical in most cases, so dynamic errors don't
matter much.

Also, the programs were originally written to work as standalone
program as well as an archive_cleanup_command. So we can't use
PostgreSQL infrastructure (can we?). That aspect is needed to allow
testing the program before it goes live.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Portability issues in shm_mq

On Tue, Mar 18, 2014 at 10:44 AM, Tom Lane  wrote:
>> The thing I kind of like about this approach is that it makes the code
>> fully independent of the relationship between MAXIMUM_ALIGNOF and
>> sizeof(Size).
>
> Yeah.  If it's not costing us much to support both cases, let's do so.

OK, committed as posted.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Minimum supported version of Python?

2014-03-18 Thread Andrew Dunstan



On 03/17/2014 10:31 PM, Peter Eisentraut wrote:

On Sun, 2014-03-16 at 22:34 -0400, Tom Lane wrote:

As for 2.4 vs 2.5, I don't have a lot of faith that we're really
supporting anything that's not represented in the buildfarm...

There are many other features that the build farm doesn't test and that
I don't have a lot of faith in, but I'm not proposing to remove those.
I don't control what the build farm tests, I only control my own work.






Actually, if you run a buildfarm animal you have considerable control 
over what it tests.


It's a very loosely coupled distributed system.

The whole basis on which it was constructed was that people who were 
interested in certain combinations of hardware and infrastructure would 
create animals to test those combinations.


If that's too much trouble for people, I have often offered to set up an 
animal for people if they give me ssh access. If that's too much trouble 
for people I tend to conclude that they aren't actually all that interested.



cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GSoC proposal. Index-only scans for GIST

Robert Haas  writes:
> Tom Lane previously proposed extending SP-GiST to support index-only
> scans.  You might find that discussing worth reading, or perhaps
> consider it as an alternative if GiST doesn't work out:
> http://www.postgresql.org/message-id/10839.1323885...@sss.pgh.pa.us

That wasn't just a proposal, see commits
3695a555136a6d179cac8ae48d5f90171d5b30e9 and
92203624934095163f8b57b5b3d7bbd2645da2c8.  But yeah, that might be a
useful reference for what is likely to be involved with making GIST
do it.

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] GSoC proposal. Index-only scans for GIST

On Tue, Mar 18, 2014 at 9:12 AM, Anastasia Lubennikova
 wrote:
> Support for index-only scans for GIST index

This is a cool idea, if it can be made to work.

> If the fetch() is specified by the developer, then using it, algorithm can
> retrieve the data directly to output areas at this stage, without reference
> to the heap.

This seems to be the crux of your proposal, but it seems vague: what
exactly do you mean by "retrieve the data directly to output areas"?
What data are you going to retrieve and where are you going to put it?

Another question to consider is: which operator classes do you
anticipate that this will work for and which ones do you anticipate
that it will not work for?  Any operator class that lossifies that
input data before storing it in the index is presumably doomed, but
which ones do that, and which do not?

Tom Lane previously proposed extending SP-GiST to support index-only
scans.  You might find that discussing worth reading, or perhaps
consider it as an alternative if GiST doesn't work out:

http://www.postgresql.org/message-id/10839.1323885...@sss.pgh.pa.us

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] Portability issues in shm_mq