date:20130725

Pavan Deolasee pavan.deola...@gmail.com writes:
 Ok. I will write up something and submit a patch. Constraints probably also
 suffer from the same issue. Whats surprising is we don't mandate that the
 functions used in CHECK constraint are immutable (like we do for indexes).
 What that means is, even if a row was satisfying a constraint while
 insertion, it may not once its there. Is that intentional ?

Well, it's probably somewhat historical, but I doubt we'd want to
tighten it up now.  Here's an example of a sensible CHECK that's
only stable:

create ... last_update timestamptz check (last_update = now()) ...

More generally, I think the argument was that the behavior of a
non-immutable CHECK would at least be easy to understand, assuming you
know that the check will only be applied at row insertion or update.
Non-immutable indexes could misbehave in much less obvious ways, for
instance causing the results of a query to differ depending on whether
the planner chose to use that index.

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

[HACKERS] Extensions makefiles - coverage

2013-07-25 Thread Ronan Dunklau

Hello.

I was having trouble figuring how to use the coverage targets when
using an extension.
I am using approximatively the layout that was proposed here:
http://www.postgresql.org/message-id/51bb1b6e.2070...@dunslane.net
It looks like everything is hard-coded to take the source and the
gcda, gcno files in the base directory, but these files lay in a src
directory with the proposed layout.

It may be better to base the .gcda file discovery on the OBJS
variables when using PGXS.

Please find attached a small patch that implements this. There is
probably a better way than the redundant rm $(gcda_files) / rm *.gcda
to cleanup the generated files.

With the attached patch, the following targets seem to have the same
behaviour as on the current HEAD, both on the whole tree and on
individual contrib modules:

- coverage-html
- clean
- coverage-clean
- clean-coverage

I noticed that make clean leaves gcda and gcov files on the current
HEAD, and this is no different with the given patch.

I also tested it against several pgxn extensions, and it seems to work fine.


coverage_pgxs.patch
Description: Binary data

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

Re: [HACKERS] Extensions makefiles - coverage

2013-07-25 Thread Ronan Dunklau

Please ignore this comment:

 I noticed that make clean leaves gcda and gcov files on the current
 HEAD, and this is no different with the given 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] pg_upgrade -j broken on Windows

2013-07-25 Thread Bruce Momjian

Everyone should be aware that the 9.3 pg_upgrade -j/--jobs option on
Windows is currently broken, and hopefully will be fixed by the next
beta.

Someone at PGDay UK told me they were getting pg_upgrade -j crashes on
Windows.  Andrew Dunstan was able to reproduce the crash, and that has
been fixed, but there is still a race condition that I am trying to
diagnose.

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

  + It's impossible for everything to be true. +


-- 
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] [GENERAL] currval and DISCARD ALL

2013-07-25 Thread suresh.balasubra

Disclaimer: I am no hacker, just a PostGreSQL user, trying to provide a user
scenario where DISCARD SEQUENCES functionality is required.

We have designed a developed a small Application Development platform for
which the backend is PostGreSQL.

There is a DBLayer which is responsible in generating SQL statements for all
the INSERT, UPDATE, DELETE operations. Data can be pushed into multiple
tables using this layer. What we provide to this layer is just a DataSet
(.NET). DataTables in the DataSet will be named after their respective
tables. We also use DataColumn extended properties to push in additional
logic. All these happen with in a transaction and also in one shot, just one
hit to the DB by appending SQL statements in proper order.

There is an interesting feature that we have built into this DBlayer which
is auto linking. All tables in our system will have a serial field 'id'.
Suppose there is a master table (let us call it 'voucher') and a detail
table ('voucher_lines'), and we are using the layer to push one record to
the master table and 50 records to the detail table. 'voucher_lines' table
will have a integer column 'voucher_id'. '*_id' fields are automatically
populated with 'currval('*_id_seq'). All this works like a charm.

Now, imagine we want to push data into another table (say 'invoice') which
also has a field 'voucher_id'. This is a different activity not connected
with the above mentioned transaction. In this scenario this field will get
updated as currval('voucher_id_seq') returns a value. But we do not want
that to be updated. What we want is to resolve '*_id' fields into values
only within a transaction. After the transaction we want to get away with
the session. If we could have cleared the session someway (DISCARD ALL does
it, but not the currval sequence info) after the first transaction it would
have worked for us.

Hope I am clear enough.

Thanks for PostGres, it is a great DB, love working with it.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-GENERAL-currval-and-DISCARD-ALL-tp5752340p5765110.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] dynamic background workers, round two

On Wed, Jul 24, 2013 at 5:34 PM, Andres Freund and...@2ndquadrant.com wrote:
 This seems like a sensible idea to me. But, in the context of dynamic
 query, don't we also need the reverse infrastructure of notifying a
 bgworker that the client, that requested it to be started, has died?
 Ending up with a dozen bgworkers running because the normal backend
 FATALed doesn't seem nice.

I think that will be desirable for many, although not all, uses of
background workers.  For example, you might want to be able to do
something like SELECT pg_background('CREATE INDEX CONCURRENTLY ...')
and then exit your session and have the background worker continue to
run.  Or maybe somebody writes a trigger that starts a background
worker (if there's not one already working) and queues up work for the
trigger, and the background worker continues processing the queue
until it is empty, and then exits.

But for parallel query specifically, yeah, we'll probably want the
untimely demise of either the original backend or any of its workers
to result in the death of all the workers and an abort of the parent's
transaction.  However, there's no need for the postmaster to be
involved in any of that.  For example, once the worker process is up
and running and the parent has its PID, it can use an on_shmem_exit
hook or a PG_ENSURE_ERROR_CLEANUP() block to send SIGTERM to any
still-living workers.  In the other direction, the parent needs to
know not only whether or not the child has died after startup, but
most likely also needs to receive errors, notices, tuples, or other
data from the child.  If the parent receives an ERROR or FATAL
indication from a parallel-worker child, it should most likely kill
all other workers and then abort the transaction.  But in neither of
those cases does the postmaster need to be involved.

The reason why this particular mechanism is needed is because the
worker isn't able to report its own failure to start.  Only the
postmaster can do that.  Once both processes are up and running, it's
up to them to handle their own IPC needs.

 It really should be pid_t even though we're not consistently doing that
 in the code.

Hmm, I thought I saw we weren't using that type.  But I guess we are,
so I can go make this consistent.

 +#define InvalidPid   (-1)
 +

 That value strikes me as a rather bad idea. As a pid that represents
 init's processgroup. I.e. kill(InvalidPid) will try to kill init... I'd
 rather not spread that outside async.c.

Well, if we ever try to kill(InvalidPid), that's a PostgreSQL bug.
And if the postgres process has permission to send signals to init,
that's an OS bug (or a PostgreSQL bug, since we refuse to run as
root).  So I'm not very worried about it.  I've got two functions that
need distinguishable return values for the not-yet-started case and
the already-dead case, neither of which can be real PIDs.  If we don't
use 0 and -1, the alternative is presumably to complicate the calling
signature with another out parameter.  That does not seem like a net
improvement.

 +/*
 + * Report the PID of a newly-launched background worker in shared memory.
 + *
 + * This function should only be called from the postmaster.
 + */
 +void
 +ReportBackgroundWorkerPID(RegisteredBgWorker *rw)
 +{
 + BackgroundWorkerSlot *slot;
 +
 + Assert(rw-rw_shmem_slot  max_worker_processes);
 + slot = BackgroundWorkerData-slot[rw-rw_shmem_slot];
 + slot-pid = rw-rw_pid;
 +
 + if (rw-rw_worker.bgw_notify_pid != 0)
 + kill(rw-rw_worker.bgw_notify_pid, SIGUSR1);
 +}

 Any reason not to use SendProcSignal() properly here? Just that you
 don't know the BackendId? I seems unclean to reuse SIGUSR1 without using
 the infrastructure built for that.

We're in the postmaster here.  The postmaster currently uses kill
directly in all cases, rather than SendProcSignal(), and I'd rather
not change that.  Any code that the postmaster calls has got to be
safe against arbitrary shared memory corruption, and I'd rather keep
that footprint as small as possible.  For example, if we added
bgw_backend_id, the postmaster would have to bounds check it to make
sure it didn't seg fault.  The more of that kind of stuff we add, the
more chances there are to destabilize the postmaster.  I'd like to
keep it as minimal as possible.

 + * If handle != NULL, we'll set *handle to a pointer that can subsequently
 + * be used as an argument to GetBackgroundWorkerPid().  The caller can
 + * free this pointer using pfree(), if desired.
   */
  bool
 -RegisterDynamicBackgroundWorker(BackgroundWorker *worker)
 +RegisterDynamicBackgroundWorker(BackgroundWorker *worker,
 +
 BackgroundWorkerHandle **handle)

 I'd just let the caller provide the memory, but whatever ;)

It could, but then the structure couldn't be opaque.  That wouldn't be
a disaster but this provides better protection against future API
violations.

 None of the containing code should be allowed to raise signals
 afaics. Was there

Re: [HACKERS] Review: UNNEST (and other functions) WITH ORDINALITY

On Wed, Jul 24, 2013 at 1:50 PM, Greg Stark st...@mit.edu wrote:
 On Wed, Jul 24, 2013 at 6:39 PM, Robert Haas robertmh...@gmail.com wrote:
 This patch will introduce, without documentation, a fifth class of
 keyword.  ORDINALITY will need to be quoted when, and only when, it
 immediately follows WITH.  Without some change to our deparsing code,
 this is a dump/restore hazard; and with some such change it's still
 probably not a good idea.

 Strictly speaking this patc doesn't introduce this fifth class of
 keyword. We already had TIME in that category (and also FIRST and LAST
 in a similar category following NULLS). If we have a solution for WITH
 keyword then presumably we would implement it for WITH TIME and WITH
 ORDINALITY at the same time.

 In the interim I suppose we could teach pg_dump to quote any keyword
 that follows WITH or NULLS pretty easily. Or just quote those four
 words unconditionally.

Making these keywords reserved-enough they get quoted would indeed fix
the problem.  It may not be desirable for other reasons, but the fact
that we have existing cases where pg_dump DTWT doesn't seem like a
good reason to add more of them.

-- 
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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])

On Thu, Jul 18, 2013 at 6:03 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
 Josh Berkus escribió:

 We are missing one feature, which is the ability to relocate the
 postgresql.auto.conf file if relocating it is desireable according to
 some sysadmin spec.  This kind of ties into another patch which was
 discussed on this list -- the ability to relocate the recovery.conf
 file.

 Well, postgresql.conf is already relocatable.  Is there any advantage in
 being able to move postgresql.auto.conf to a different location than
 postgresql.conf?  I don't see any.  I didn't follow the recovery.conf
 discussion, but I imagine that the reason for wanting to be able to
 relocate it is related to standby vs. master distinction.  This doesn't
 apply to postgresql.auto.conf, I think, does it?

 If people want to drill real down, they can always have a
 postgresql.auto.conf that's a symlink. (In this light, we would ship an
 empty postgresql.auto.conf in a freshly initdb'd system.  IIRC the
 current patch already does that.)

My thought is that people might put postgresql.conf in a directory
that only contains configuration files and isn't writeable by the
postgres user.  So I would expect to find postgresql.auto.conf in the
data directory always.

But in general +1 for your proposal.

-- 
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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])

On Mon, Jul 22, 2013 at 7:56 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Josh Berkus j...@agliodbs.com writes:
 Christophe just discovered something with include files which is going
 to cause issues with ALTER SYSTEM SET.

 So, take as a hypothetical that you use the default postgresql.conf
 file, which sets shared_buffers = 32MB.

 Instead of editing this file, you do ALTER SYSTEM SET shared_buffers =
 '1GB', which updates config/postgresql.auto.conf.

 Then you restart PostgreSQL.

 Everything is hunky-dory, until a later occasion where you *reload*
 postgresql.

 Everything was already *not* hunky-dory as soon as you did that, since
 a SIGHUP would have had the same problem.

 I'd be inclined to think that ALTER SYSTEM SET should not be allowed to
 modify any PGC_POSTMASTER parameters.

That significantly decreases the usefulness of ALTER SYSTEM without
actually preventing the underlying problem.  If having multiple
conflicting values for parameters in the config files doesn't work
cleanly, then we should fix that, not cripple this feature.

-- 
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] Preventing tuple-table leakage in plpgsql

I wrote:
 Another point worth making is that this version of the patch deletes the
 tuple tables during AtEOSubXact_SPI(), earlier in cleanup than would
 happen with the prior version.  That increases the risk that external
 code might try to delete an already-deleted tuple table, if it tries
 to call SPI_freetuptable() during subxact cleanup.  The new code won't
 crash, although come to think of it it will probably throw an error
 because you're not connected anymore.  (Maybe this is a reason to not
 insist on being connected, but just silently search whatever the top
 stack context is?)

After further reflection I think that's the prudent way to do it, so
I've adjusted SPI_freetuptable to not insist on being connected.
Pushed with that change:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3d13623d75d3206c8f009353415043a191ebab39

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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])

Robert Haas escribió:
 On Mon, Jul 22, 2013 at 7:56 PM, Tom Lane t...@sss.pgh.pa.us wrote:

  I'd be inclined to think that ALTER SYSTEM SET should not be allowed to
  modify any PGC_POSTMASTER parameters.
 
 That significantly decreases the usefulness of ALTER SYSTEM without
 actually preventing the underlying problem.  If having multiple
 conflicting values for parameters in the config files doesn't work
 cleanly, then we should fix that, not cripple this feature.

I think the only solution that makes sense here is to comment out the
entry in postgresql.conf.  We weren't supposed to modify that file, but
in light of this problem I'm starting to be of the opinion that we ought
to allow this implementation do this one change.  I admit this is
somewhat problematic: consider that somebody having postgresql.conf open
in an editor while somebody else runs ALTER SYSTEM would see the file
changing underneath.  But at least in Vim, what happens is that the
editor warns you that the file has changed underneath, and asks you
whether you want to load the new version.

Now, this is not likely to be an easy thing to implement ...

Another option would be to separate config loading in two stages; first
read all the files, and only check and throw errors and warnings later,
when we know the definite place that's going to be the authoritative
definition of each variable.

I'm not comfortable with the idea that PGC_POSTMASTER variables should
be treated differently from others.

-- 
Á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

[HACKERS] install libpq.dll in bin directory on Windows / Cygwin

2013-07-25 Thread Andrew Dunstan

Jeff Janes asked me about this, and Bruce just tripped up on it. Usually 
on Windows it's necessary to have libpq.dll/cygpq.dll either in the PATH 
or in the same directory as client .exe files. The buildfarm client has 
for many years simply copied this dll from the installation lib to the 
installation bin directory after running make install. But I can't 
really see why we don't do that as part of make install anyway. I 
haven't tested but I think something like this patch would achieve this 
goal - it would fix something that's tripped a lot of people up over the 
years.


Comments? If we do this, should it be backported?

cheers

andrew


diff --git a/src/interfaces/libpq/Makefile b/src/interfaces/libpq/Makefile
index f116000..bece893 100644
--- a/src/interfaces/libpq/Makefile
+++ b/src/interfaces/libpq/Makefile
@@ -115,18 +115,21 @@ fe-misc.o: fe-misc.c $(top_builddir)/src/port/pg_config_paths.h
 $(top_builddir)/src/port/pg_config_paths.h:
 	$(MAKE) -C $(top_builddir)/src/port pg_config_paths.h
 
-install: all installdirs install-lib
+install: all installdirs install-lib install-dll-bin
 	$(INSTALL_DATA) $(srcdir)/libpq-fe.h '$(DESTDIR)$(includedir)'
 	$(INSTALL_DATA) $(srcdir)/libpq-events.h '$(DESTDIR)$(includedir)'
 	$(INSTALL_DATA) $(srcdir)/libpq-int.h '$(DESTDIR)$(includedir_internal)'
 	$(INSTALL_DATA) $(srcdir)/pqexpbuffer.h '$(DESTDIR)$(includedir_internal)'
 	$(INSTALL_DATA) $(srcdir)/pg_service.conf.sample '$(DESTDIR)$(datadir)/pg_service.conf.sample'
+ifneq (,$findstring($(PORTNAME), win32 cygwin))
+	$(INSTALL_DATA) $(shlib) '$(DESTDIR)$(bindir)/$(shlib)'
+endif
 
 installcheck:
 	$(MAKE) -C test $@
 
 installdirs: installdirs-lib
-	$(MKDIR_P) '$(DESTDIR)$(includedir)' '$(DESTDIR)$(includedir_internal)' '$(DESTDIR)$(datadir)'
+	$(MKDIR_P) '$(DESTDIR)$(includedir)' '$(DESTDIR)$(includedir_internal)' '$(DESTDIR)$(datadir)' '$(DESTDIR)$(bindir)'
 
 uninstall: uninstall-lib
 	rm -f '$(DESTDIR)$(includedir)/libpq-fe.h'
@@ -134,6 +137,9 @@ uninstall: uninstall-lib
 	rm -f '$(DESTDIR)$(includedir_internal)/libpq-int.h'
 	rm -f '$(DESTDIR)$(includedir_internal)/pqexpbuffer.h'
 	rm -f '$(DESTDIR)$(datadir)/pg_service.conf.sample'
+ifneq (,$findstring($(PORTNAME), win32 cygwin))
+	rm -f '$(DESTDIR)$(bindir)/$(shlib)'
+endif
 
 clean distclean: clean-lib
 	$(MAKE) -C test $@

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

Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])

Alvaro Herrera alvhe...@2ndquadrant.com writes:
 Robert Haas escribió:
 On Mon, Jul 22, 2013 at 7:56 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I'd be inclined to think that ALTER SYSTEM SET should not be allowed to
 modify any PGC_POSTMASTER parameters.

 That significantly decreases the usefulness of ALTER SYSTEM without
 actually preventing the underlying problem.  If having multiple
 conflicting values for parameters in the config files doesn't work
 cleanly, then we should fix that, not cripple this feature.

 I think the only solution that makes sense here is to comment out the
 entry in postgresql.conf.

Actually, this is much ado about nothing.  If you actually put two
conflicting values for shared_buffers into postgresql.conf, you'll
find out that:

1. on initial start, the server just silently adopts the later one.

2. if you SIGHUP, you get:

LOG:  autovacuum launcher started
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter shared_buffers cannot be changed without restarting the server
LOG:  configuration file /home/postgres/version93/data/postgresql.conf 
contains errors; unaffected changes were applied

We could possibly improve the code to not produce that log bleat, but
it's hardly a show-stopper as is.

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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])

Robert Haas robertmh...@gmail.com writes:
 On Thu, Jul 18, 2013 at 6:03 PM, Alvaro Herrera
 alvhe...@2ndquadrant.com wrote:
 Josh Berkus escribió:
 We are missing one feature, which is the ability to relocate the
 postgresql.auto.conf file if relocating it is desireable according to
 some sysadmin spec.

 My thought is that people might put postgresql.conf in a directory
 that only contains configuration files and isn't writeable by the
 postgres user.  So I would expect to find postgresql.auto.conf in the
 data directory always.

Yeah, exactly.  I think putting it anywhere but $PGDATA is a bad idea,
and a sysadmin who thinks he knows better probably doesn't.

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] install libpq.dll in bin directory on Windows / Cygwin

Andrew Dunstan wrote:
 Jeff Janes asked me about this, and Bruce just tripped up on it.
 Usually on Windows it's necessary to have libpq.dll/cygpq.dll either
 in the PATH or in the same directory as client .exe files. The
 buildfarm client has for many years simply copied this dll from the
 installation lib to the installation bin directory after running
 make install. But I can't really see why we don't do that as part
 of make install anyway. I haven't tested but I think something
 like this patch would achieve this goal - it would fix something
 that's tripped a lot of people up over the years.

Seems a reasonable workaround for a silly platform bug.  Do you need to
patch the MSVC stuff as well?

 Comments? If we do this, should it be backported?

To 9.3, sure, but not further back, as there's probably little point.

 +ifneq (,$findstring($(PORTNAME), win32 cygwin))
 + $(INSTALL_DATA) $(shlib) '$(DESTDIR)$(bindir)/$(shlib)'
 +endif

I wonder if someday we will create a win64 $(PORTNAME) value, or
something like that, and then we'll have to update the port list on
which these hacks are applied all over the place.  Not complaining
about this patch in particular, just an idle thought.


-- 
Á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] install libpq.dll in bin directory on Windows / Cygwin

2013-07-25 Thread Andrew Dunstan



On 07/25/2013 05:02 PM, Alvaro Herrera wrote:

Andrew Dunstan wrote:

Jeff Janes asked me about this, and Bruce just tripped up on it.
Usually on Windows it's necessary to have libpq.dll/cygpq.dll either
in the PATH or in the same directory as client .exe files. The
buildfarm client has for many years simply copied this dll from the
installation lib to the installation bin directory after running
make install. But I can't really see why we don't do that as part
of make install anyway. I haven't tested but I think something
like this patch would achieve this goal - it would fix something
that's tripped a lot of people up over the years.

Seems a reasonable workaround for a silly platform bug.  Do you need to
patch the MSVC stuff as well?



MSVC already does it - see src/tools/msvc/Install.pm:


lcopy($target . '/lib/libpq.dll', $target . '/bin/libpq.dll');


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] install libpq.dll in bin directory on Windows / Cygwin

Andrew Dunstan wrote:
 
 On 07/25/2013 05:02 PM, Alvaro Herrera wrote:
 Andrew Dunstan wrote:

 Usually on Windows it's necessary to have libpq.dll/cygpq.dll either
 in the PATH or in the same directory as client .exe files.
 
 Seems a reasonable workaround for a silly platform bug.  Do you need to
 patch the MSVC stuff as well?
 
 MSVC already does it - see src/tools/msvc/Install.pm:
 
 lcopy($target . '/lib/libpq.dll', $target . '/bin/libpq.dll');

Oh, so your patch is just a bug fix and we should backpatch it all the
way, no?

-- 
Á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] Adding Zigzag Merge Join to Index Nested Loops Join

2013-07-25 Thread Jeff Janes

On Tue, Jul 23, 2013 at 12:40 PM, tubadzin tubad...@o2.pl wrote:
 Hi.
 I want add Zigzag Merge join to Index Nested Loops Join alghoritm.
 http://www.cs.berkeley.edu/~fox/summaries/database/query_eval_5-8.html
 Which files are responsible for Index nested loops join ? (not simple nested
 loops join which has double foreach in nodeNestloop.c) nodeIndexscan.c?
 nodeIndexonlyscan.c?
 Best Regards
 Tom

As far as I can tell, a zigzag merge is nothing more than a nested
loop which uses an index scan on both the inner and outer.  It seems a
bit strange to me to draw a box around that particular combination and
give it a specific name.

If that is what it really is, then there is nothing to implement.
PostgreSQL already does that when it thinks it is faster than the
alternatives.

pgbench -i -s10

set enable_seqscan TO off;
set enable_mergejoin TO off;
set enable_hashjoin TO off;
explain select * from pgbench_accounts join pgbench_branches using
(bid) where aid between 1 and 5;

  QUERY PLAN
--
 Nested Loop  (cost=0.58..25.21 rows=4 width=457)
   -  Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.43..8.51 rows=4 width=97)
 Index Cond: ((aid = 1) AND (aid = 5))
   -  Index Scan using pgbench_branches_pkey on pgbench_branches
(cost=0.14..4.16 rows=1 width=364)
 Index Cond: (bid = pgbench_accounts.bid)

Cheers,

Jeff


-- 
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] Design proposal: fsync absorb linear slider

2013-07-25 Thread didier

Hi


On Tue, Jul 23, 2013 at 5:48 AM, Greg Smith g...@2ndquadrant.com wrote:

 Recently I've been dismissing a lot of suggested changes to checkpoint
 fsync timing without suggesting an alternative.  I have a simple one in
 mind that captures the biggest problem I see:  that the number of backend
 and checkpoint writes to a file are not connected at all.

 We know that a 1GB relation segment can take a really long time to write
 out.  That could include up to 128 changed 8K pages, and we allow all of
 them to get dirty before any are forced to disk with fsync.

 It was surely already discussed but why isn't postresql  writing
sequentially its cache in a temporary file? With storage random speed at
least five to ten time slower it could help a lot.
Thanks

Didier

Re: [HACKERS] install libpq.dll in bin directory on Windows / Cygwin

2013-07-25 Thread MauMau


From: Andrew Dunstan andrew.duns...@pgexperts.com

on Windows it's necessary to have libpq.dll/cygpq.dll either in the PATH
or in the same directory as client .exe files. The buildfarm client has
for many years simply copied this dll from the installation lib to the
installation bin directory after running make install. But I can't
really see why we don't do that as part of make install anyway. I
haven't tested but I think something like this patch would achieve this
goal - it would fix something that's tripped a lot of people up over the
years.

Comments? If we do this, should it be backported?


I was just about to propose something related to this.

On native Windows (not on Cygwin or MinGW), DLLs are in general placed in 
(1) system directory (e.g. c:\windows\system32), (2) somewhere in PATH, or 
(3) the same directory as EXEs which automatically load the DLL at 
invocation. It's no problem that most DLLs in PostgreSQL's lib directory, 
because they are loaded at runtime by postgres.exe by calling LoadLibrary() 
specifying an absolute or a relative path.  However, the below files are 
misplaced:


libecpg.dll
libecpg_compat.dll
libpgtypes.dll

These should be placed in bin directory.  If done so, when running SQL 
embedded C applications, users can just add bin in PATH, which is usually 
done already.  Otherwise, users have to add both bin and lib in PATH. 
Usually, lib is not added in PATH in many software.


Could you please place the above files in bin and remove them from lib?

BTW, why is libpq.dll in lib necessary?  For the above files?  If so, we can 
remove libpq.dll from lib.  Or, libpqwalreceiver.dll needs it?


Regards
MauMau




--
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] getting rid of SnapshotNow

On Tue, Jul 23, 2013 at 2:53 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Works for me.

OK.  I've taken care of all remaining uses of SnapshotNow in the code
base.  I think we can go ahead and remove it, now.  Patch attached.

(And there was, hopefully, much rejoicing.)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


remove-snapshotnow-v1.patch
Description: Binary data

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

Re: [HACKERS] Design proposal: fsync absorb linear slider

On Thu, Jul 25, 2013 at 6:02 PM, didier did...@gmail.com wrote:
 It was surely already discussed but why isn't postresql  writing
 sequentially its cache in a temporary file? With storage random speed at
 least five to ten time slower it could help a lot.
 Thanks

Sure, that's what the WAL does.  But you still have to checkpoint eventually.

-- 
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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])

2013-07-25 Thread Josh Berkus

On 07/25/2013 02:02 PM, Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Thu, Jul 18, 2013 at 6:03 PM, Alvaro Herrera
 alvhe...@2ndquadrant.com wrote:
 Josh Berkus escribi�:
 We are missing one feature, which is the ability to relocate the
 postgresql.auto.conf file if relocating it is desireable according to
 some sysadmin spec.
 
 My thought is that people might put postgresql.conf in a directory
 that only contains configuration files and isn't writeable by the
 postgres user.  So I would expect to find postgresql.auto.conf in the
 data directory always.
 
 Yeah, exactly.  I think putting it anywhere but $PGDATA is a bad idea,
 and a sysadmin who thinks he knows better probably doesn't.

Please see Greg Smith's fairly strong arguments for putting it in the
config/ directory.


-- 
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] comment for fast promote

2013-07-25 Thread Tomonari Katsumata


Hi Fujii-san,

Thank you for response.

(2013/07/25 21:15), Fujii Masao wrote:
 On Thu, Jul 25, 2013 at 5:33 PM, Tomonari Katsumata
 katsumata.tomon...@po.ntts.co.jp wrote:
 Hi,

 Now I'm seeing xlog.c in 93_stable for studying fast promote,
 and I have a question.

 I think it has an extra unlink command for promote file.
 (on 9937 line)
 ---
 9934 if (stat(FAST_PROMOTE_SIGNAL_FILE, stat_buf) == 0)
 9935 {
 9936 unlink(FAST_PROMOTE_SIGNAL_FILE);
 9937 unlink(PROMOTE_SIGNAL_FILE);
 9938 fast_promote = true;
 9939 }
 ---

 Is this command necesary ?

 Yes, it prevents PROMOTE_SIGNAL_FILE from remaining even if
 both promote files exist.

The command(unlink(PROMOTE_SIGNAL_FILE)) here is for
unusualy case.
Because the case is when done both procedures below.
 - user create promote file on PGDATA
 - user issue pg_ctl promote

I understand the reason.
But I think it's better to unlink(PROMOTE_SIGNAL_FILE) before
unlink(FAST_PROMOTE_SIGNAL_FILE).
Because FAST_PROMOTE_SIGNAL_FILE is definetly there but
PROMOTE_SIGNAL_FILE is sometimes there or not there.

And I have another question linking this behavior.
I think TriggerFile should be removed too.
This is corner-case but it will happen.
How do you think of it ?

 One question is that: we really still need to support normal promote?
 pg_ctl promote provides only way to do fast promotion. If we want to
 do normal promotion, we need to create PROMOTE_SIGNAL_FILE
 and send the SIGUSR1 signal to postmaster by hand. This seems messy.

 I think that we should remove normal promotion at all, or change
 pg_ctl promote so that provides also the way to do normal promotion.

I think he merit of fast promote is
 - allowing quick connection by skipping checkpoint
and its demerit is
 - taking little bit longer when crash-recovery

If it is seldom to happen its crash soon after promoting
and fast promte never breaks consistency of database cluster,
I think we don't need normal promotion.
(of course we need to put detail about promotion on document.)

regards,

NTT Software Corporation
Tomonari Katsumata





--
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] Expression indexes and dependecies

2013-07-25 Thread Pavan Deolasee

On Thu, Jul 25, 2013 at 6:43 PM, Tom Lane t...@sss.pgh.pa.us wrote:



 Well, it's probably somewhat historical, but I doubt we'd want to
 tighten it up now.  Here's an example of a sensible CHECK that's
 only stable:

 create ... last_update timestamptz check (last_update = now()) ...


Agree. That looks like a very sensible use case and something not possible
without support for mutable functions.



 More generally, I think the argument was that the behavior of a
 non-immutable CHECK would at least be easy to understand, assuming you
 know that the check will only be applied at row insertion or update.


But they are also prone to unexpected behaviour, no ? For example, a slight
variation of the above example is:

create ... last_update timestamptz check (last_update = now() and
last_update = now() - '1 week'::interval) ...

This constraint would most likely fail if someone was to restore the table
from a dump.

Given that we haven't seen any complaints may mean I am imagining a problem
that does not exist in practice, though I thought the example looks quite
sensible too.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: [HACKERS] Expression indexes and dependecies

Pavan Deolasee pavan.deola...@gmail.com writes:
 On Thu, Jul 25, 2013 at 6:43 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 More generally, I think the argument was that the behavior of a
 non-immutable CHECK would at least be easy to understand, assuming you
 know that the check will only be applied at row insertion or update.

 But they are also prone to unexpected behaviour, no ? For example, a slight
 variation of the above example is:
 create ... last_update timestamptz check (last_update = now() and
 last_update = now() - '1 week'::interval) ...
 This constraint would most likely fail if someone was to restore the table
 from a dump.

Sure, but the reason for the failure would be entirely obvious.  It
might be annoying, but it'd still be obvious --- and not too hard to
fix, either.  The prohibition on mutable index functions is because you
might waste a great deal of time on diagnosing the reason for a problem.

Now, I grant that that argument could also be used to justify trying
harder than we do now to detect not-really-immutable index functions,
or for trying harder than we do now to prevent you from changing an
index function's behavior.  I'm not opposed in principle to tightening
those checks more; I'm just doubtful that we can easily make things
better 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

[HACKERS] Condition to become the standby mode.

2013-07-25 Thread Sawada Masahiko

Hi All,

When the slave server starts, the slave server perform the following
steps in StartupXLOG():
1. Read latest CheckPoint record LSN from pg_control file.
2. Try to fetch CheckPoint record from pg_xlog directory at first.
  ( The server try to read up to prior CheckPoint record from pg_contrl file)
3. If there is not in pg_xlog, the slave server requests CheckPoint
record to the primary server.

in #3, it works only when StandbyMode is true.　For StandbyMode is to
true, database cluster state should be DB_SHUTDOWNED (it is one of
the conditions).
that is, slave server can try to fetch checkpoint record from the
master server after slave server was successful completion.

But there is the case that slave server can catch up the primary
server fetching WAL record from the primary server even if slave
server was not successful completion.

For example, when the 2 servers are connected over a relatively slower link.
even if there is possible of clustering replication without taking
full backup of the primary server, we ignore the possible. I think
that is a waste.

so I think it is better that slave server try to request WAL record to
the primary server even if database cluster state of pg_control is
DB_IN_PRODUCTION ( is not enough?)

If this problem is solved, there is possible of that we can failback
by removing the all WAL record which is in pg_xlog before server
starts as the slave server.
( And we also use synchronous_transfer which I'm proposing, I think
we can fail-back without taking full backup surely)

Am I missing something? Please give me feedback.

diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index 96aceb9..c3ccd0e 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -6213,7 +6213,8 @@ StartupXLOG(void)
(ControlFile-minRecoveryPoint != InvalidXLogRecPtr ||
 ControlFile-backupEndRequired ||
 ControlFile-backupEndPoint != InvalidXLogRecPtr ||
-ControlFile-state == DB_SHUTDOWNED))
+ControlFile-state == DB_SHUTDOWNED ||
+ControlFile-state == DB_IN_PRODUCTION))
{
InArchiveRecovery = true;
if (StandbyModeRequested)

synchronous transfer discussion :
http://www.postgresql.org/message-id/CAF8Q-Gy7xa60HwXc0MKajjkWFEbFDWTG=ggyu1kmt+s2xcq...@mail.gmail.com

-- 
Regards,

---
Sawada Masahiko


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

[HACKERS] inconsistent state after crash recovery

2013-07-25 Thread Satoshi Nagayasu

Hi,

I received a question about inconsistent state after crash recovery.

When a table file is broken (or just lost), PostgreSQL can not recover
a whole table, and does not show any notice while recoverying.
I think it means inconsistent state.

(1) create a table, and fill records.
(2) process a checkpoint.
(3) fill more records.
(4) force a crash, and delete the table file.
(5) run recovery on restarting.
(6) only records after the checkpoint can be recoverd.

For example, the attached log shows that PostgreSQL can recover
only 1058 records in the table which contains 2000 records
before the crash, and does not tell anything in the server log.

--
insert into t1 values (trim(to_char(generate_series(1,1000),
'')) );
INSERT 0 1000
select count(*) from t1;
 count
---
  1000
(1 row)

checkpoint;
CHECKPOINT
insert into t1 values (trim(to_char(generate_series(1001,2000),
'')) );
INSERT 0 1000
select count(*) from t1;
 count
---
  2000
(1 row)

(delete the table file)
(kill postgres)
(restart postgres with recovery)

select count(*) from t1;
 count
---
  1058
(1 row)
--

Is this expected or acceptable?

I think, at least, PostgreSQL should say something about this
situation in the server log, because DBA can not recognize
this situation if no server log exists.

To reproduce it, please check the attached test script.

Any comments?

Regards,
-- 
Satoshi Nagayasu sn...@uptime.jp
Uptime Technologies, LLC. http://www.uptime.jp
[snaga@devsv03 postgresql.git]$ sh test_recovery.sh
createdb: database creation failed: ERROR:  database testdb already exists
drop table if exists t1;
DROP TABLE
create table t1 ( c1 varchar(20) );
CREATE TABLE
checkpoint;
CHECKPOINT
select count(*) from t1;
 count
---
 0
(1 row)

select relname,relfilenode from pg_class where relname='t1';
 relname | relfilenode
-+-
 t1  |  147456
(1 row)

select oid,* from pg_database where datname=current_database();
  oid  | datname | datdba | encoding | datcollate | datctype | datistemplate | 
datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | 
dattablespace | datacl
---+-++--++--+---+--+--+---+--++---+
 16384 | testdb  | 10 |0 | C  | C| f | 
t|   -1 | 12895 | 1799 |  1 |   
   1663 |
(1 row)

insert into t1 values (trim(to_char(generate_series(1,1000), 
'')) );
INSERT 0 1000
select count(*) from t1;
 count
---
  1000
(1 row)

select pg_relation_size('t1');
 pg_relation_size
--
57344
(1 row)

checkpoint;
CHECKPOINT
insert into t1 values (trim(to_char(generate_series(1001,2000), 
'')) );
INSERT 0 1000
select count(*) from t1;
 count
---
  2000
(1 row)

select pg_relation_size('t1');
 pg_relation_size
--
   106496
(1 row)

/tmp/pgsql/data/base/16384/147456
waiting for server to start2013-07-26 13:33:03 JST [@ 14651] LOG:  loaded 
library pg_stat_statements
 done
server started
2013-07-26 13:33:03 JST [@ 14653] LOG:  database system was interrupted; last 
known up at 2013-07-26 13:33:03 JST
2013-07-26 13:33:03 JST [@ 14653] LOG:  database system was not properly shut 
down; automatic recovery in progress
2013-07-26 13:33:03 JST [@ 14653] LOG:  redo starts at 0/1D493A0
2013-07-26 13:33:03 JST [@ 14653] LOG:  record with zero length at 0/1D5D958
2013-07-26 13:33:03 JST [@ 14653] LOG:  redo done at 0/1D5D928
2013-07-26 13:33:03 JST [@ 14653] LOG:  last completed transaction was at log 
time 2013-07-26 13:33:03.158161+09
2013-07-26 13:33:03 JST [@ 14651] LOG:  database system is ready to accept 
connections
2013-07-26 13:33:03 JST [@ 14657] LOG:  autovacuum launcher started
select count(*) from t1;
 count
---
  1058
(1 row)

select pg_relation_size('t1');
 pg_relation_size
--
   106496
(1 row)

createdb: database creation failed: ERROR:  database testdb already exists
drop table if exists t1;
DROP TABLE
create table t1 ( c1 varchar(20) );
CREATE TABLE
checkpoint;
CHECKPOINT
select count(*) from t1;
 count
---
 0
(1 row)

select relname,relfilenode from pg_class where relname='t1';
 relname | relfilenode
-+-
 t1  |  155648
(1 row)

select oid,* from pg_database where datname=current_database();
  oid  | datname | datdba | encoding | datcollate | datctype | datistemplate | 
datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | 
dattablespace | datacl
---+-++--++--+---+--+--+---+--++---+
 16384 |

Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])