date:20140915


On 09/14/2014 08:21 PM, cowwoc wrote:

Hi Adrian,

Replies below.

On 14/09/2014 8:34 PM, Adrian Klaver wrote:

On 09/14/2014 02:01 PM, cowwoc wrote:

See http://dba.stackexchange.com/q/76494/4719 for a related discussion.

So from the above link and the discussion here so far I gather you want:

1) A CREATE TEMPORARY SCHEMA that behaves like CREATE TEMPORARY TABLE,
where it lasts only for a session and masks any existing schema and
contained objects with the same name for the duration.


Yes. Tests should not be able to see each other's tables, data (and
ideally not even each other's schema).


2) You want to run this on a test database as so you can have 'masked'
tests over the permanent schema. Presumably because the tests can be
destructive and you do not want change the underlying structure and/or
data in your test database.


Not exactly. Each test is responsible for populating its own schema
(creating tables, inserting data). The main purpose of using temporary
schemas is to ensure that each test runs in isolation so that data from
other tests cannot influence the outcome of the test. This ensures test
execution/results are 100% reproducible.


So the tests may or may not have anything to do with the existing test 
database?





3) This needs to happen on the order of 4-10 times a second and it
needs to be tied to a session, as multiple transactions need to be run
in each test.

As to whether this is the appropriate list, I would say yes at least
for sounding out the idea. The list to address for dealing with the
folks that actually would implement the idea I would suggest --hackers.

In the meantime, the only way I can see doing this is creating and
dropping databases. Not sure that would meet your 4-10 times a second
requirement though.


One of the requirements is that if someone kills the process running the
unit tests, it can't leave behind any dangling schemas. I expect all
test data to get dropped automatically when the connection is closed
unexpectedly, so DROP DATABASE won't do.


I would think a DROP DATABASE IF EXISTS, CREATE DATABASE at the 
beginning of the test would handle that.




Gili





--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Feature request: temporary schemas


On 15/09/2014 9:39 AM, Adrian Klaver wrote:


Not exactly. Each test is responsible for populating its own schema
(creating tables, inserting data). The main purpose of using temporary
schemas is to ensure that each test runs in isolation so that data from
other tests cannot influence the outcome of the test. This ensures test
execution/results are 100% reproducible.


So the tests may or may not have anything to do with the existing test 
database?


Hi Adrian,

I don't understand what you mean by the existing test database. In my 
mind, tests have nothing in common with each other. They are meant to 
execute in complete isolation of each other.


It sounds to me like you thought I create a test database once 
(containing the tables, functions, triggers used by tests) and then 
running tests against that one at a time. In actuality, each test is 
expected to create its own tables, functions, triggers and execute 
concurrently and in complete isolation with other tests.




One of the requirements is that if someone kills the process running the
unit tests, it can't leave behind any dangling schemas. I expect all
test data to get dropped automatically when the connection is closed
unexpectedly, so DROP DATABASE won't do.


I would think a DROP DATABASE IF EXISTS, CREATE DATABASE at the 
beginning of the test would handle that.


This would only clean up the next time tests are run. I'm looking for a 
cleanup at the end of the tests, not the beginning.
As well, the fact that I have concurrent test execution means that I 
don't know how many databases/schemas there are to drop. I guess I could 
scan the database metadata for all test-related schemas but clearly this 
isn't as clean/fun as having temporary schemas in the first place.


And lastly, remember that we want these tests to run as fast as 
possible. TEMPORARY/UNLOGGED tables are ideal from that point of view 
but I can't specify TEMPORARY/UNLOGGED because the unit tests and 
production code must share the same SQL script.


Gili


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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 Thread Merlin Moncure

On Mon, Sep 15, 2014 at 6:03 AM, Chris Travers chris.trav...@gmail.com wrote:


 On Sun, Sep 14, 2014 at 10:22 PM, cowwoc cow...@bbs.darktech.org wrote:

 Hi,

 Out of curiosity, why is Postgresql's Java support so poor? I am
 specifically looking for the ability to write triggers in Java.


 Because it hasn't been a priority of contributors.  This is how
 non-single-vendor open source projects work: people decide what is important
 to them and do the work required.  If something gets neglected then I guess
 it wasn't really important.


 I took a look at the PL/Java project and it looked both incomplete and
 dead,
 yet other languages like Javascript are taking off. I would have expected
 to
 see very strong support for Java because it's the most frequently used
 language on the server-side.

 I think Javascript is taking off because JSON is taking off.  The problem
 with Java is that while there are plenty of use cases for it in the db, it
 isn't something which there are *common* use cases for.

JSON is part of it but there's more.  javascript is a language
designed for embedding and so makes very little assumptions about the
host environment.  It doesn't use threads which is particularly nice
and that (along with the highly asynchronous nature of the language)
has influenced developer culture and library design.   It doesn't
expose things which are dubious to do in a stored procedure and the pl
wrapper can expose such things in a proper API or not at all.   Java
OTOH  exposes (being a systems language) anything and everything.  The
architecture of having the GC embedded inside a postgres process is
awkward to say the least.  Don't get me wrong: pl/java works
beautifully if you know what you're doing but you have to approach
things as a minimalist from the java point of view if you want things
to work well and minimalism is not, uh, endemic to the java culture.

There's more: plv8 relies in BSD licensed c++ runtime from google.
It's pretty self contained and friendly.  Java is provided by Oracle
('nuff said) with a complicated license that I don't understand TBH.
This doesn't necessarily argue against 'core support' but it's
something to think about.

So I'd argue plv8 has a much better case of being put 'in core', but
I'd argue that neither of them should be.  Why? The core team is
already supporting enough code and it seems better to make the
extension framework more robust so that users have a easier route to
pulling in 3rd party libraries than they currently do.

merlin


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

Re: [GENERAL] Feature request: temporary schemas

2014-09-15 Thread Rob Sargent

Interesting enough concept. Please don't forget to test against a realistic 
data set as well. It does seem to me that the devs can easily make, fill, clean 
up their own db. And a central builder (eg Jenkins?) can do the same with, 
importantly using ALL tests. 
Then again using real data. 


 On Sep 15, 2014, at 8:08 AM, cowwoc cow...@bbs.darktech.org wrote:
 
 On 15/09/2014 9:39 AM, Adrian Klaver wrote:
 
 Not exactly. Each test is responsible for populating its own schema
 (creating tables, inserting data). The main purpose of using temporary
 schemas is to ensure that each test runs in isolation so that data from
 other tests cannot influence the outcome of the test. This ensures test
 execution/results are 100% reproducible.
 
 So the tests may or may not have anything to do with the existing test 
 database?
 
 Hi Adrian,
 
 I don't understand what you mean by the existing test database. In my mind, 
 tests have nothing in common with each other. They are meant to execute in 
 complete isolation of each other.
 
 It sounds to me like you thought I create a test database once (containing 
 the tables, functions, triggers used by tests) and then running tests against 
 that one at a time. In actuality, each test is expected to create its own 
 tables, functions, triggers and execute concurrently and in complete 
 isolation with other tests.
 
 
 One of the requirements is that if someone kills the process running the
 unit tests, it can't leave behind any dangling schemas. I expect all
 test data to get dropped automatically when the connection is closed
 unexpectedly, so DROP DATABASE won't do.
 
 I would think a DROP DATABASE IF EXISTS, CREATE DATABASE at the beginning of 
 the test would handle that.
 
 This would only clean up the next time tests are run. I'm looking for a 
 cleanup at the end of the tests, not the beginning.
 As well, the fact that I have concurrent test execution means that I don't 
 know how many databases/schemas there are to drop. I guess I could scan the 
 database metadata for all test-related schemas but clearly this isn't as 
 clean/fun as having temporary schemas in the first place.
 
 And lastly, remember that we want these tests to run as fast as possible. 
 TEMPORARY/UNLOGGED tables are ideal from that point of view but I can't 
 specify TEMPORARY/UNLOGGED because the unit tests and production code must 
 share the same SQL script.
 
 Gili
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general


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

Re: [GENERAL] Feature request: temporary schemas


On 09/15/2014 07:08 AM, cowwoc wrote:

On 15/09/2014 9:39 AM, Adrian Klaver wrote:


Not exactly. Each test is responsible for populating its own schema
(creating tables, inserting data). The main purpose of using temporary
schemas is to ensure that each test runs in isolation so that data from
other tests cannot influence the outcome of the test. This ensures test
execution/results are 100% reproducible.


So the tests may or may not have anything to do with the existing test
database?


Hi Adrian,

I don't understand what you mean by the existing test database. In my
mind, tests have nothing in common with each other. They are meant to
execute in complete isolation of each other.


From your second post:

 1. I'm already planning to run unit tests against a separate (but
identical) database than production, so there's no danger of wiping
out the production database.
 2. I need to create a new temporary schema per test, and run 4-10 of
tests per second. I'm guessing this wouldn't perform well using
pg_ctl.


I took that to mean you want to create the temporary schemas over an 
existing test database.




It sounds to me like you thought I create a test database once
(containing the tables, functions, triggers used by tests) and then
running tests against that one at a time. In actuality, each test is
expected to create its own tables, functions, triggers and execute
concurrently and in complete isolation with other tests.



One of the requirements is that if someone kills the process running the
unit tests, it can't leave behind any dangling schemas. I expect all
test data to get dropped automatically when the connection is closed
unexpectedly, so DROP DATABASE won't do.


I would think a DROP DATABASE IF EXISTS, CREATE DATABASE at the
beginning of the test would handle that.


This would only clean up the next time tests are run. I'm looking for a
cleanup at the end of the tests, not the beginning.
As well, the fact that I have concurrent test execution means that I
don't know how many databases/schemas there are to drop. I guess I could
scan the database metadata for all test-related schemas but clearly this
isn't as clean/fun as having temporary schemas in the first place.


I will admit to not being a testing expert, but from what I have done, 
I know test suites have setup and teardown sections.


Would this not work?

My previous suggestion was a fall through for the case you mentioned 
where a process is terminated outside the test.




And lastly, remember that we want these tests to run as fast as
possible. TEMPORARY/UNLOGGED tables are ideal from that point of view
but I can't specify TEMPORARY/UNLOGGED because the unit tests and
production code must share the same SQL script.


I can see that being possible. There will always be some difference 
though as unit tests are not the same as rolling out production scripts. 
The test code will need to include the actual test, unless I am missing 
something obvious. Entirely possible:)




Gili





--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Feature request: temporary schemas


On 15/09/2014 10:37 AM, Adrian Klaver wrote:

From your second post:

 1. I'm already planning to run unit tests against a separate (but
identical) database than production, so there's no danger of wiping
out the production database.
 2. I need to create a new temporary schema per test, and run 4-10 of
tests per second. I'm guessing this wouldn't perform well using
pg_ctl.


I took that to mean you want to create the temporary schemas over an 
existing test database.


Sorry, I am having problems with the Postgresql terminology. H2 
database = Postgresql schema.


To answer your original question: ideally I would want each test to run 
in its own database but in practice I don't think you can create/drop a 
database through JDBC, can you? So instead, I'm thinking about manually 
creating a test database and have unit tests create/drop schemas inside 
of that. The test database wouldn't contain anything. It would just 
isolate unit tests from the production database to make sure they don't 
clobber each other.


If I could get each unit test to run inside its own database, that would 
be even better.

I would think a DROP DATABASE IF EXISTS, CREATE DATABASE at the
beginning of the test would handle that.


This would only clean up the next time tests are run. I'm looking for a
cleanup at the end of the tests, not the beginning.
As well, the fact that I have concurrent test execution means that I
don't know how many databases/schemas there are to drop. I guess I could
scan the database metadata for all test-related schemas but clearly this
isn't as clean/fun as having temporary schemas in the first place.


I will admit to not being a testing expert, but from what I have done, 
I know test suites have setup and teardown sections.


Would this not work?

My previous suggestion was a fall through for the case you mentioned 
where a process is terminated outside the test.


Right, dropping databases in the teardown section is certainly doable. 
It's just that the teardown section never runs if the unit tests are 
terminated.





And lastly, remember that we want these tests to run as fast as
possible. TEMPORARY/UNLOGGED tables are ideal from that point of view
but I can't specify TEMPORARY/UNLOGGED because the unit tests and
production code must share the same SQL script.


I can see that being possible. There will always be some difference 
though as unit tests are not the same as rolling out production 
scripts. The test code will need to include the actual test, unless I 
am missing something obvious. Entirely possible:)


Unit tests proceed as follows:

1. Run SQL script to populate the test schema (identical for all tests)
2. Run test code (different for each test)

I want unit tests and production to share #1 but have different #2. 
Currently I am forced to produce nearly identical #1 for production and 
unit tests. This feature request would enable me to have identical #1 :)


Gili


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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 Thread Michael Paquier

On Mon, Sep 15, 2014 at 11:23 PM, Merlin Moncure mmonc...@gmail.com wrote:
 So I'd argue plv8 has a much better case of being put 'in core', but
 I'd argue that neither of them should be.  Why? The core team is
 already supporting enough code and it seems better to make the
 extension framework more robust so that users have a easier route to
 pulling in 3rd party libraries than they currently do.
It is worth noting that v8 newer than versions 3.14 (if I recall
correctly) has introduced many API breakages making plv8 incompatible
in those cases with newer versions, making it rather harder to
integrate into core with a long-term vision:
https://apps.fedoraproject.org/packages/v8
Regards,
-- 
Michael


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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 Thread Michael Paquier

On Tue, Sep 16, 2014 at 1:43 AM, Michael Paquier
michael.paqu...@gmail.com wrote:
 https://apps.fedoraproject.org/packages/v8
Forgot to add that this is probably one of the reasons why Fedora
sticks to this version.
-- 
Michael


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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

Hi guys,

Any chance you guys could help cleaning up the build/deploy process?

This is a pretty big hurdle to overcome for new users.

Gili

On 15/09/2014 8:55 AM, Tim Clarke [via PostgreSQL] wrote:
On 15/09/14 13:30, Achilleas Mantzios wrote:

This is far from dead. I works perfectly with java 1.7 and postgresql
9.3 ,
but you need maybe a little bit more extra homework + some skills with
maven.
If i managed to build this on a FreeBSD machine, in linux it should a
piece of cake.
The docs suck, granted, but the community is very much alive and
helpful.
We use it for production environment. We had some really complex Java
code, that
we were unwilling to port to pl/pgsql, therefore we gave pl/java a try.
It was worth it. + it has proven to be really stable. No JVM crashes
after 2 years in production.

+1 for that; we are too.

--
Tim Clarke

--
Sent via pgsql-general mailing list ([hidden email]
/user/SendEmail.jtp?type=nodenode=5819054i=0)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

If you reply to this email, your message will be added to the
discussion below:
http://postgresql.1045698.n5.nabble.com/Why-isn-t-Java-support-part-of-Postgresql-core-tp5819025p5819054.html

To unsubscribe from Why isn't Java support part of Postgresql core?,
click here
http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5819025code=Y293d29jQGJicy5kYXJrdGVjaC5vcmd8NTgxOTAyNXwxNTc0MzIxMjQ3.
NAML
http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Why-isn-t-Java-support-part-of-Postgresql-core-tp5819025p5819084.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Why isn't Java support part of Postgresql core?


On 15/09/2014 7:03 AM, Chris Travers wrote:
I have a few questions on this, the answers of which may help answer 
your question:


1.  How well does having a server-side JVM work, resource-wise, when 
you have a forked process model like PostgreSQL?  Does having the 
additional JVM's pose performance and competition for resources that 
lighter languages would not?


I don't think this is really a concern when connection pooling is used 
(otherwise you end up creating a new JVM per connection which is indeed 
problematic).



2.  What is your specific use case for a trigger in Java?


The main drivers are:

1. Not having to learn yet another language. I find the expressiveness
   and readability of the other scripting languages very clunky
   compared to Java.
2. Ease of porting triggers across databases. The only thing that
   really changes across databases is how triggers interact with
   input/output parameters. The main body remains the same (thanks to
   JDBC). This is quasi portability in the sense that the underlying
   SQL is itself quasi portable, but I find it a much more compelling
   approach than having to rewrite the triggers for each database type.

Gili

Re: [GENERAL] Why isn't Java support part of Postgresql core?


On 15/09/2014 7:58 AM, Bill Moran wrote:

On Sun, 14 Sep 2014 22:22:21 -0700 (PDT)
cowwoc cow...@bbs.darktech.org wrote:

Out of curiosity, why is Postgresql's Java support so poor?

To trampoline off what others have said: it gets implemented and maintained if
people want/need it.

But I feel like I have a little more insight into _why_ people aren't taking
the effort, based on experience at my last job.

We were interested in both pl/Java and pl/PHP.  In theory, both of those would
allow us to leverage both existing codebases and existing developer skills.  We
were looking at taking an active role in maintainership of these two languages
to facilitate our use.

In practice, the amount of code in existing code bases that would be reused for
stored procedures turned out to be very low.  Additionally, the number of
developers who had difficulty adapting to plPGSQL programming was 0.  As a
result, we found that, in practice, the existing pl/SQL and plPGSQL were
_good_enough_ and there was so little benefit from using other languages that
we couldn't justify the effort of ensuring they worked consistently.

 From a meta standpoint, it feels like pl/Java and others are really neat ideas
that simply aren't _necessary_ (although they're nice to have).  When it comes
down to work done for employer, it was just less effort to succeed by going the
route of using the existing plSQL/plPGSQL, and employers are all about less
money spent to accomplish the goal.

Other people may have other opinions or stories or whatever.  That's mine.


I'm very glad you posted this because I was thinking the same but needed 
someone to reinforce my views. pl/pgsql is beginning to look like the 
lesser evil to getting pl/java to work. Sad but true.


I strongly believe that pl/java would catapult the expressiveness of 
triggers to a new level, but getting this off the ground will require 
the concerted effort of 2-3 dedicated developers.


Gili


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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 19:34 GMT+02:00 cowwoc cow...@bbs.darktech.org:

  On 15/09/2014 7:03 AM, Chris Travers wrote:

 I have a few questions on this, the answers of which may help answer your
 question:

  1.  How well does having a server-side JVM work, resource-wise, when you
 have a forked process model like PostgreSQL?  Does having the additional
 JVM's pose performance and competition for resources that lighter languages
 would not?


 I don't think this is really a concern when connection pooling is used
 (otherwise you end up creating a new JVM per connection which is indeed
 problematic).

   2.  What is your specific use case for a trigger in Java?


 The main drivers are:

1. Not having to learn yet another language. I find the expressiveness
and readability of the other scripting languages very clunky compared to
Java.


PLpgSQL is different, it is based on Ada language



1. Ease of porting triggers across databases. The only thing that
really changes across databases is how triggers interact with input/output
parameters. The main body remains the same (thanks to JDBC). This is quasi
portability in the sense that the underlying SQL is itself quasi portable,
but I find it a much more compelling approach than having to rewrite the
triggers for each database type.

 any time plpgsql will be faster then Java probably due a type
compatibility with Postgres and execution as inprocess

There is a few task, that can be done in database, that will be faster in
PL/Java than PL/pgSQL

Regards

Pavel




 Gili

Re: [GENERAL] Pgpool starting problem

2014-09-15 Thread Jay at Verizon

Problem has been solved utilizing tcpdump which revealed that the standbys were 
not trying to contact the primary server, although they each were contacting 
the other standby. From 
this, we determined that a minor typographical error was present in the 
pgpool.conf file. The setting under the watchdog section for other_hostname0 
was correct, and other required 
settings for that entry were also correct, but had somehow been commented out. 
The log file was not very revealing, and finding one badly inserted # in a 
647 line file was 
problematic to say the least.

On 9/9/2014 2:42 PM, Ellen [via PostgreSQL] wrote:
 Hi Jay,
 Can you pls tell me how you resolved this issue.
 We are running pgpool-II version 3.3.3
 Thanks.
 Ellen

 
 If you reply to this email, your message will be added to the discussion 
 below:
 http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5818354.html
 To unsubscribe from Pgpool starting problem, click here 
 http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5803276code=amF5a25vd3N1bml4QGdtYWlsLmNvbXw1ODAzMjc2fDM1NDE5ODU4OQ==.
 NAML 
 http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
  






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5819090.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 19:37 GMT+02:00 cowwoc cow...@bbs.darktech.org:

 On 15/09/2014 7:58 AM, Bill Moran wrote:

 On Sun, 14 Sep 2014 22:22:21 -0700 (PDT)
 cowwoc cow...@bbs.darktech.org wrote:

 Out of curiosity, why is Postgresql's Java support so poor?

 To trampoline off what others have said: it gets implemented and
 maintained if
 people want/need it.

 But I feel like I have a little more insight into _why_ people aren't
 taking
 the effort, based on experience at my last job.

 We were interested in both pl/Java and pl/PHP.  In theory, both of those
 would
 allow us to leverage both existing codebases and existing developer
 skills.  We
 were looking at taking an active role in maintainership of these two
 languages
 to facilitate our use.

 In practice, the amount of code in existing code bases that would be
 reused for
 stored procedures turned out to be very low.  Additionally, the number of
 developers who had difficulty adapting to plPGSQL programming was 0.  As a
 result, we found that, in practice, the existing pl/SQL and plPGSQL were
 _good_enough_ and there was so little benefit from using other languages
 that
 we couldn't justify the effort of ensuring they worked consistently.

  From a meta standpoint, it feels like pl/Java and others are really neat
 ideas
 that simply aren't _necessary_ (although they're nice to have).  When it
 comes
 down to work done for employer, it was just less effort to succeed by
 going the
 route of using the existing plSQL/plPGSQL, and employers are all about
 less
 money spent to accomplish the goal.

 Other people may have other opinions or stories or whatever.  That's mine.


 I'm very glad you posted this because I was thinking the same but needed
 someone to reinforce my views. pl/pgsql is beginning to look like the
 lesser evil to getting pl/java to work. Sad but true.

 I strongly believe that pl/java would catapult the expressiveness of
 triggers to a new level, but getting this off the ground will require the
 concerted effort of 2-3 dedicated developers.


I am strong sceptic. There is relative slow progress in JDBC driver, that
is 100x more important project than PL/Java - so It is hard to believe, so
there can be 3 developers, who start work on PL/Java.

Regards

Pavel



 Gili


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why isn't Java support part of Postgresql core?


Hi Pavel,

On 15/09/2014 1:40 PM, Pavel Stehule wrote:

The main drivers are:

 1. Not having to learn yet another language. I find the
expressiveness and readability of the other scripting
languages very clunky compared to Java.


PLpgSQL is different, it is based on Ada language


I'm sure it's a very lovely language, but it is yet another language 
most people are not familiar with and will have to learn.



 1. Ease of porting triggers across databases. The only thing that
really changes across databases is how triggers interact with
input/output parameters. The main body remains the same
(thanks to JDBC). This is quasi portability in the sense that
the underlying SQL is itself quasi portable, but I find it a
much more compelling approach than having to rewrite the
triggers for each database type.

any time plpgsql will be faster then Java probably due a type 
compatibility with Postgres and execution as inprocess


There is a few task, that can be done in database, that will be faster 
in PL/Java than PL/pgSQL


I think developers choosing this route (myself included) are willing to 
pay the price in exchange for improved readability/maintainability (the 
assumption being that the resulting performance will be good enough). 
There seem to be plenty of people heading in this direction otherwise 
other languages (like pl/v8) wouldn't enjoy the popularity they do.


Gili

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 19:46 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com:



 2014-09-15 19:37 GMT+02:00 cowwoc cow...@bbs.darktech.org:

 On 15/09/2014 7:58 AM, Bill Moran wrote:

 On Sun, 14 Sep 2014 22:22:21 -0700 (PDT)
 cowwoc cow...@bbs.darktech.org wrote:

 Out of curiosity, why is Postgresql's Java support so poor?

 To trampoline off what others have said: it gets implemented and
 maintained if
 people want/need it.

 But I feel like I have a little more insight into _why_ people aren't
 taking
 the effort, based on experience at my last job.

 We were interested in both pl/Java and pl/PHP.  In theory, both of those
 would
 allow us to leverage both existing codebases and existing developer
 skills.  We
 were looking at taking an active role in maintainership of these two
 languages
 to facilitate our use.

 In practice, the amount of code in existing code bases that would be
 reused for
 stored procedures turned out to be very low.  Additionally, the number of
 developers who had difficulty adapting to plPGSQL programming was 0.  As
 a
 result, we found that, in practice, the existing pl/SQL and plPGSQL were
 _good_enough_ and there was so little benefit from using other languages
 that
 we couldn't justify the effort of ensuring they worked consistently.

  From a meta standpoint, it feels like pl/Java and others are really
 neat ideas
 that simply aren't _necessary_ (although they're nice to have).  When it
 comes
 down to work done for employer, it was just less effort to succeed by
 going the
 route of using the existing plSQL/plPGSQL, and employers are all about
 less
 money spent to accomplish the goal.

 Other people may have other opinions or stories or whatever.  That's
 mine.


 I'm very glad you posted this because I was thinking the same but needed
 someone to reinforce my views. pl/pgsql is beginning to look like the
 lesser evil to getting pl/java to work. Sad but true.

 I strongly believe that pl/java would catapult the expressiveness of
 triggers to a new level, but getting this off the ground will require the
 concerted effort of 2-3 dedicated developers.


 I am strong sceptic. There is relative slow progress in JDBC driver, that
 is 100x more important project than PL/Java - so It is hard to believe, so
 there can be 3 developers, who start work on PL/Java.


and I am not sure if Java as stored procedures is living technology, It was
designed as esperanto, but it is supported only by Oracle after 14 years.

Pavel



 Regards

 Pavel



 Gili


 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Feature request: temporary schemas


On 09/15/2014 08:05 AM, cowwoc wrote:

On 15/09/2014 10:37 AM, Adrian Klaver wrote:

From your second post:

 1. I'm already planning to run unit tests against a separate (but
identical) database than production, so there's no danger of wiping
out the production database.
 2. I need to create a new temporary schema per test, and run 4-10 of
tests per second. I'm guessing this wouldn't perform well using
pg_ctl.


I took that to mean you want to create the temporary schemas over an
existing test database.


Sorry, I am having problems with the Postgresql terminology. H2
database = Postgresql schema.

To answer your original question: ideally I would want each test to run
in its own database but in practice I don't think you can create/drop a
database through JDBC, can you? So instead, I'm thinking about manually
creating a test database and have unit tests create/drop schemas inside
of that. The test database wouldn't contain anything. It would just
isolate unit tests from the production database to make sure they don't
clobber each other.


Well DROP DATABASE is a SQL command so it could be used in a query:

http://www.postgresql.org/docs/9.3/interactive/sql-dropdatabase.html

The caveat is you cannot DROP the database you are connected to. You 
would need to connect one of the system databases, postgres for 
instance, and then run the DROP DATABASE command there.




If I could get each unit test to run inside its own database, that would
be even better.


That would be possible, though some thought would need to be given per 
your desire to run concurrent tests. Would need to think carefully about 
naming issues.




I would think a DROP DATABASE IF EXISTS, CREATE DATABASE at the
beginning of the test would handle that.


This would only clean up the next time tests are run. I'm looking for a
cleanup at the end of the tests, not the beginning.
As well, the fact that I have concurrent test execution means that I
don't know how many databases/schemas there are to drop. I guess I could
scan the database metadata for all test-related schemas but clearly this
isn't as clean/fun as having temporary schemas in the first place.


I will admit to not being a testing expert, but from what I have done,
I know test suites have setup and teardown sections.

Would this not work?

My previous suggestion was a fall through for the case you mentioned
where a process is terminated outside the test.


Right, dropping databases in the teardown section is certainly doable.
It's just that the teardown section never runs if the unit tests are
terminated.


Just a food for thought item. This is something I think you would have 
to deal with any case. I think it is plausible that even in the 
TEMPORARY SCHEMA case a unit test could terminate and leave a 
connection(session) open and therefore the schema still active. 
Subsequent runs would create new temp schemas, but you could have old 
carcasses lingering. My guess is that might end up impacting your test 
results.







And lastly, remember that we want these tests to run as fast as
possible. TEMPORARY/UNLOGGED tables are ideal from that point of view
but I can't specify TEMPORARY/UNLOGGED because the unit tests and
production code must share the same SQL script.


I can see that being possible. There will always be some difference
though as unit tests are not the same as rolling out production
scripts. The test code will need to include the actual test, unless I
am missing something obvious. Entirely possible:)


Unit tests proceed as follows:

1. Run SQL script to populate the test schema (identical for all tests)
2. Run test code (different for each test)

I want unit tests and production to share #1 but have different #2.
Currently I am forced to produce nearly identical #1 for production and
unit tests. This feature request would enable me to have identical #1 :)


I see the logic. The thing is this is a time issue. What you want would 
be considered a feature change. Feature changes only happen in new major 
versions. Currently work has started on the next major version, 9.5. So 
you would need to convince developers to make the change in the near 
future in order to get it out with 9.5. Even then you are probably 
looking at around a year before it hits production. In the meantime you 
will need a Plan B. Doing the DROP/CREATE DATABASE adds just a little 
difference to the script in #1. After that everything else would be the 
same.




Gili



--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] BDR Error recovery

2014-09-15 Thread p...@mailme.dk

Ubuntu 14.04 with compiled BDR 0.7.1

This is a very interesting project for a lot of potential applications.
However as in any project there will be a few initial issues.

My question is how do I recover from DDL errors ?

For example: given a setup of 2 BDR PostgreSQL hosts and on one of them
execute the following SQL:

create table test_table(id int);
create or replace view test_view as select * from test_table;
create or replace view test_view as select * from test_table;

This will result in an endless replication error. As far as I understand
because it is replicated as:

create table test_table(id int);
create view test_view as select * from test_table;
create view test_view as select * from test_table;

But rather than having you fix the problem I would be much more
interesting in learning how to recover from such as scenario.

Thanks

Poul




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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 Thread Rob Sargent


On 09/15/2014 11:49 AM, cowwoc wrote:

Hi Pavel,

On 15/09/2014 1:40 PM, Pavel Stehule wrote:

The main drivers are:

 1. Not having to learn yet another language. I find the
expressiveness and readability of the other scripting
languages very clunky compared to Java.


PLpgSQL is different, it is based on Ada language


I'm sure it's a very lovely language, but it is yet another language 
most people are not familiar with and will have to learn.



 1. Ease of porting triggers across databases. The only thing
that really changes across databases is how triggers interact
with input/output parameters. The main body remains the same
(thanks to JDBC). This is quasi portability in the sense that
the underlying SQL is itself quasi portable, but I find it a
much more compelling approach than having to rewrite the
triggers for each database type.

any time plpgsql will be faster then Java probably due a type 
compatibility with Postgres and execution as inprocess


There is a few task, that can be done in database, that will be 
faster in PL/Java than PL/pgSQL


I think developers choosing this route (myself included) are willing 
to pay the price in exchange for improved readability/maintainability 
(the assumption being that the resulting performance will be good 
enough). There seem to be plenty of people heading in this direction 
otherwise other languages (like pl/v8) wouldn't enjoy the popularity 
they do.


Gili
I've seen too many good java developers write too much terrible 
database-oriented code.  If they are good with db and sql, plpgsql will 
not be a problem to learn.

[GENERAL] BDR DML Only

2014-09-15 Thread p...@mailme.dk

Is it already possible or would you consider a configuration option that
would only replicate DML but not DDL ?

This should of course be combined with a predictable way of manually
handling DDL errors. Like simply manually adding any missing DDL on the
slave.

Thanks

Poul



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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 15/09/2014 1:51 PM, Pavel Stehule [via PostgreSQL] wrote:
 and I am not sure if Java as stored procedures is living technology, 
 It was designed as esperanto, but it is supported only by Oracle 
 after 14 years.

 Pavel

H2, HSQLDB, Derby all support Java triggers. Granted, we are not talking 
about MySQL or Oracle here, but these did not add Java triggers as an 
afterthought.

Gili




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Why-isn-t-Java-support-part-of-Postgresql-core-tp5819025p5819098.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 19:49 GMT+02:00 cowwoc cow...@bbs.darktech.org:

  Hi Pavel,

 On 15/09/2014 1:40 PM, Pavel Stehule wrote:

 The main drivers are:


1. Not having to learn yet another language. I find the
expressiveness and readability of the other scripting languages very 
 clunky
compared to Java.


  PLpgSQL is different, it is based on Ada language


 I'm sure it's a very lovely language, but it is yet another language most
 people are not familiar with and will have to learn.


you need one day learning only .. it contains only necessary functionality
for stored procedures, nothing more.

It is like Java in 90 years - just simple






1. Ease of porting triggers across databases. The only thing that
really changes across databases is how triggers interact with input/output
parameters. The main body remains the same (thanks to JDBC). This is quasi
portability in the sense that the underlying SQL is itself quasi portable,
but I find it a much more compelling approach than having to rewrite the
triggers for each database type.

  any time plpgsql will be faster then Java probably due a type
 compatibility with Postgres and execution as inprocess

  There is a few task, that can be done in database, that will be faster
 in PL/Java than PL/pgSQL


 I think developers choosing this route (myself included) are willing to
 pay the price in exchange for improved readability/maintainability (the
 assumption being that the resulting performance will be good enough).
 There seem to be plenty of people heading in this direction otherwise other
 languages (like pl/v8) wouldn't enjoy the popularity they do.


I know a situation in Czech Republic well and in Europe little bit

My estimation is about PostgreSQL applications

95% applications is wrote without stored procedures
4% applications is wrote with PL/pgSQL
.9% is combination PL/pgSQL with PL/Perl or PL/Python (Perl is older with
CPAN, Python is popular in GIS community)
0.01% has all other .. pl/v8 was used mainly for JSON manipulation, because
this possibility was not in PG, PL/R, PL/Lua, PL/PHP, PL/v8 has very small
user community

For typical Java or Javascript users the stored procedures are devil still.






 Gili

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 15/09/2014 2:02 PM, lup [via PostgreSQL] wrote:
 On 09/15/2014 11:49 AM, cowwoc wrote:
 I think developers choosing this route (myself included) are willing 
 to pay the price in exchange for improved readability/maintainability 
 (the assumption being that the resulting performance will be good 
 enough). There seem to be plenty of people heading in this direction 
 otherwise other languages (like pl/v8) wouldn't enjoy the popularity 
 they do.

 Gili
 I've seen too many good java developers write too much terrible 
 database-oriented code.  If they are good with db and sql, plpgsql 
 will not be a problem to learn.

lup,

Then does Postgresql support languages other than pl/pgsql? I'm not 
trying to tear down pl/pgsql, simply pointing out that there is strong 
demand for other languages as well.

And to answer Pavel's earlier point: the lack of developers getting 
behind pl/java and JDBC driver does not equate the lack of demand for 
those tools. For every one person contributing patches, there are 1000s 
who do not but are more than happy to use the finished work.

Gili




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Why-isn-t-Java-support-part-of-Postgresql-core-tp5819025p5819104.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Why isn't Java support part of Postgresql core?

On 15/09/2014 2:12 PM, cowwoc wrote:
 On 15/09/2014 2:02 PM, lup [via PostgreSQL] wrote:
 On 09/15/2014 11:49 AM, cowwoc wrote:
 I think developers choosing this route (myself included) are willing 
 to pay the price in exchange for improved 
 readability/maintainability (the assumption being that the resulting 
 performance will be good enough). There seem to be plenty of 
 people heading in this direction otherwise other languages (like 
 pl/v8) wouldn't enjoy the popularity they do.

 Gili
 I've seen too many good java developers write too much terrible 
 database-oriented code.  If they are good with db and sql, plpgsql 
 will not be a problem to learn.

 lup,

 Then does Postgresql support languages other than pl/pgsql? I'm not 
 trying to tear down pl/pgsql, simply pointing out that there is strong 
 demand for other languages as well.

 And to answer Pavel's earlier point: the lack of developers getting 
 behind pl/java and JDBC driver does not equate the lack of demand for 
 those tools. For every one person contributing patches, there are 
 1000s who do not but are more than happy to use the finished work.

 Gili

Then does was meant to read Then *why* does :)

Gili




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Why-isn-t-Java-support-part-of-Postgresql-core-tp5819025p5819105.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] Why isn't Java support part of Postgresql core?


On 09/15/2014 11:00 AM, Rob Sargent wrote:

On 09/15/2014 11:49 AM, cowwoc wrote:

Hi Pavel,

On 15/09/2014 1:40 PM, Pavel Stehule wrote:

The main drivers are:

 1. Not having to learn yet another language. I find the
expressiveness and readability of the other scripting
languages very clunky compared to Java.





Gili

I've seen too many good java developers write too much terrible
database-oriented code.  If they are good with db and sql, plpgsql will
not be a problem to learn.



Agreed, plpgsql is SQL with Postgres extensions. I went down this road 
with plpythonu. I am comfortable in Python, so I thought it would be 
easier to write functions using plpythonu. I actually did this 
backwards. I had created a fairly complex function in plpgsql and I 
figured I could simplify by using plpythonu. I was well into it when I 
had realized I written more code in plpythonu than plpgsql and still was 
not close to completing it. I still use plpythonu but only when I need 
the dynamic nature of Python or really need to use some Python module.


--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 Thread Szymon Guz

On 15 September 2014 20:13, cowwoc cow...@bbs.darktech.org wrote:

On 15/09/2014 2:12 PM, cowwoc wrote:

 On 15/09/2014 2:02 PM, lup [via PostgreSQL] wrote:

 On 09/15/2014 11:49 AM, cowwoc wrote:

 I think developers choosing this route (myself included) are willing to
 pay the price in exchange for improved readability/maintainability (the
 assumption being that the resulting performance will be good enough).
 There seem to be plenty of people heading in this direction otherwise other
 languages (like pl/v8) wouldn't enjoy the popularity they do.

 Gili

 I've seen too many good java developers write too much terrible
 database-oriented code.  If they are good with db and sql, plpgsql will not
 be a problem to learn.


 lup,

 Then does Postgresql support languages other than pl/pgsql? I'm not trying
 to tear down pl/pgsql, simply pointing out that there is strong demand for
 other languages as well.

 And to answer Pavel's earlier point: the lack of developers getting behind
 pl/java and JDBC driver does not equate the lack of demand for those tools.
 For every one person contributing patches, there are 1000s who do not but
 are more than happy to use the finished work.

 Gili


 Then does was meant to read Then *why* does :)

 Gili


Hi,
you're right. But there is no other way to have this done than have someone
done it. If there are no people interested in doing that it will not be
done. Unfortunately.

- Szymon

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 Thread Alberto Cabello Sánchez

On Mon, 15 Sep 2014 13:34:53 -0400
cowwoc cow...@bbs.darktech.org wrote:

  2.  What is your specific use case for a trigger in Java?
 
 The main drivers are:
 
  1. Not having to learn yet another language.

Bear in mind that DB programmers often know SQL. To me, and apparently to them,
PL/pgsql seems closer to the already-known SQL than PL/JAVA.

Besides that, PL/JAVA -which I honestly think is a great concept in itself-
doesn't free you from learning yet another language if you are working with
PHP, Python, Node.js, etc... In these cases, PL/pgsql is a safer bet.

-- 
Alberto Cabello Sánchez
albe...@unex.es


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

Re: [GENERAL] Why isn't Java support part of Postgresql core?

2014-09-15 Thread Thomas Kellerer


cowwoc wrote on 15.09.2014 19:55:

H2, HSQLDB, Derby all support Java triggers.


But only because they already live/run inside a JVM, so it's the natural 
choice of language.

And H2 and Derby *only* support Java stored procedures.

The main disadvantage I see with that is, that you can't just write a 
procedure (or trigger) with a simple (procedural) SQL Statement. Yyou need to compile it, 
package into a jar file and the add the jar file(s) to the classpath of the application 
(or the server process).

This essentially means you need to restart your application or the server when 
you deploy a trigger.
Hardly anyhting that you want to do in a production environment.


Granted, we are not talking about MySQL or Oracle here,
but these did not add Java triggers as an afterthought.


MySQL does not have Java triggers (or procedures).








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

Re: [GENERAL] BDR DML Only

2014-09-15 Thread Christoph Moench-Tegeder

## p...@mailme.dk (p...@mailme.dk):

 Is it already possible or would you consider a configuration option that
 would only replicate DML but not DDL ?

bdr.skip_ddl_replication = true
can even be set at transaction level

Regards,
Christoph

-- 
Spare Space


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

Re: [GENERAL] Why isn't Java support part of Postgresql core?