Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 2:15, Christophe Pettus :

>
> > On Jul 15, 2018, at 16:06, Dmitry Igrishin  wrote:
> >
> > The cross-platform GUI toolkit will be the challenge.
> > This is why I've consider GUI for the Windows only. And if I'll not find
> an adequate GUI
> > toolkit (at reasonable price and/or license), there is an option to make
> the GUI available
> > on Windows only and provide the Linux version without a GUI (at least at
> the first time).
>
> I'm not sure I quite understand an PostgreSQL IDE without a GUI.  Isn't
> that psql, to a first approximation?
>
In general, with psql the one can execute arbitrary SQL query either
interactively or by calling
it from another program. But there is no advanced refactoring features out
of the box. For
example, suppose, the one have a file with the following DDL commands:

  create table foo(id integer, data text, extra_data text);
  create view v1 as select * from foo;

To make this DDL file usabe with psql(1) it must be reentrant - that is
the one need to provide the DROP commands in reverse order:

  drop view v1;
  drop table foo;

  create table foo(id integer, data text, extra_data text);
  create view v1 as select * from foo;

With a large code base it's can be tedious. It's possible to automate such
a tasks and don't
worry about the reentrance at all.


> I'm also curious how you see this IDE comparing to, say, pgAdmin4.
> There's no reason we can't have multiple IDEs, of course, but when I think
> of an "integrated development environment," I think of something (along the
> lines of the JetBrains family) that handles the full stack, including
> debugging.

It is possible to create the full fledged IDE with a convenient debugging
features. But to create
such an instrument, we need financial support.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Albrecht Dreß]

Am 16.07.18 00:14 schrieb(en) Tim Cross:

Thank you for the point. I'm the C++ programmer and I'm author of the C++ 
client library for PostgreSQL - Pgfe and I'm going to use it in this project. 
But I'm not sure about the cross-platform GUI toolkit.


The cross-platform GUI toolkit will be the challenge.


Try Qt .  It uses c++, comes with a 
dual license (LGPL/commercial) and supports all relevant platforms:
- Linux: will work ootb for all distos I know, without the need to ship it with 
libraries
- macOS: includes support to create the usual bundles which go into the 
Applications folder
- Winbloze: works fine there, too, if you insist on a broken os ;-)

I worked on a Qt-based oss project in the past, and it is actually trivial to 
create binaries for all aforementioned platforms from the same sources.

Hth,
Albrecht.

pgpMDUeNshprc.pgp
Description: PGP signature

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 12:41, Albrecht Dreß :

> Am 16.07.18 00:14 schrieb(en) Tim Cross:
> >> Thank you for the point. I'm the C++ programmer and I'm author of the
> C++ client library for PostgreSQL - Pgfe and I'm going to use it in this
> project. But I'm not sure about the cross-platform GUI toolkit.
> >
> > The cross-platform GUI toolkit will be the challenge.
>
> Try Qt .  It uses c++, comes
> with a dual license (LGPL/commercial) and supports all relevant platforms:
> - Linux: will work ootb for all distos I know, without the need to ship it
> with libraries
> - macOS: includes support to create the usual bundles which go into the
> Applications folder
> - Winbloze: works fine there, too, if you insist on a broken os ;-)
>
Qt looks the best and I would like to go with it. But althought it may be a
good candidate for an
open source project, but may be too expensive for developing proprietary
software. (Recenlty,
it has become even more expensive.)

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Vincenzo Campanella]

Il 16.07.2018 11:41, Albrecht Dreß ha scritto:

Am 16.07.18 00:14 schrieb(en) Tim Cross:
Thank you for the point. I'm the C++ programmer and I'm author of the 
C++ client library for PostgreSQL - Pgfe and I'm going to use it in 
this project. But I'm not sure about the cross-platform GUI toolkit.


The cross-platform GUI toolkit will be the challenge.


Try Qt .  It uses c++, comes 
with a dual license (LGPL/commercial) and supports all relevant platforms:
- Linux: will work ootb for all distos I know, without the need to ship 
it with libraries
- macOS: includes support to create the usual bundles which go into the 
Applications folder

- Winbloze: works fine there, too, if you insist on a broken os ;-)

I worked on a Qt-based oss project in the past, and it is actually 
trivial to create binaries for all aforementioned platforms from the 
same sources.


That's a very good solution, IMHO.

Otherwise, WxWidgets (https://www.wxwidgets.org/) could also be a good 
solution...

Re: Building a notification system.

[Dave Cramer]

On 15 July 2018 at 23:25, Anto Aravinth  wrote:

>
>
> On Mon, Jul 16, 2018 at 8:02 AM, Christopher Browne 
> wrote:
>
>> On Sun, Jul 15, 2018, 5:30 AM Anto Aravinth, 
>> wrote:
>>
>>> Hello Everyone,
>>>
>>>
>>> I'm playing around with postgresql with SO datasets. In the process, I
>>> have dumped 60M questions data onto the postgresql. I'm trying to build a
>>> notification system on top of this, so that, when a user edits a question,
>>> I need to show a notification to the user when he/she logs in next time. So
>>> literally, trying to create the clone of SO (with very minimal feature)
>>>
>>> I'm not sure, how to get started with this. Read about NOTIFY:
>>> https://www.postgresql.org/docs/current/static/sql-notify.html
>>>
>>>
>>> Not sure that fits my use case, thanks for your help in this.
>>>
>>
>>
>> I do not think that the NOTIFY command implemented in postgreSQL is
>> terribly likely to be  useful for your application.
>>
>> That command is useful for distribution of notifications to applications
>> that are continuously connected to the database, which is not likely true
>> for web app connections, particularly in view of your comment about
>> notifying users "when they log in next time."
>>
>> Instead, you need a table that captures a log of undelivered
>> notifications of changes to questions.  It should capture useful attributes
>> such as..
>> - Who made the change
>> - Who is to be notified
>> - The time of the change
>> - Perhaps the nature of the change, which could be pretty open ended
>> - A reference to the question, e.g. its ID
>> - Some lifecycle attribute such as "viewed-on" or "acknowledged-on"
>>
>> When a user logs in, it should be easy to query that table, providing the
>> list of unexamined updates.
>>
>
Also look at Logical Decoding for implementing Change Data Capture

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Tim Clarke]

+1 for not re-inventing the wheel - building on Netbeans or the Eclipse
project would save you heaps of time and effort and provide
cross-platform out of the box. I use Eclipse all the time.

Tim Clarke


On 15/07/18 20:41, Chuck Davis wrote:
> If you decide to proceed on this project there's no need to reinvent the 
> wheel.
>
> I use Netbeans for my development.  it has quite a good facility for
> working with databases and I use it regularly with Postgres.  Since
> Netbeans is now licensed under Apache 2 you might find useful code
> there.  Be assured it uses JDBC for access but JDBC is universally
> available and the folks at Postgresql have done quite a nice job with
> JDBC drivers.  Of course, this already works on all platforms.  The
> implementation is basic but very useful:  i.e. a good starting point.
>
> On Sun, Jul 15, 2018 at 12:08 PM, Dmitry Igrishin  wrote:
>> Thank you all for your responses!
>>
>> Okay, if I decide to start this project, the Linux platform will be
>> supported.
>>
>> As I see it, pgspa (PostgreSQL Server Programming Assistant) should have
>> both the command line interface,
>> and the GUI for visualizing some aspects of the work. The tool should be
>> friendly for your favorite editors (Emacs,
>> VSCode etc) for easy integration to the daily workflow. (There is no plans
>> to write feature rich text editor inside
>> the IDE. It's more rational to provide the integration with the existing
>> text editors for professional programmers.)
>> What do you think?

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 13:41, Tim Clarke :

> +1 for not re-inventing the wheel - building on Netbeans or the Eclipse
> project would save you heaps of time and effort and provide
> cross-platform out of the box. I use Eclipse all the time.
>
I agree and don't want to waste my time for reinventing the wheel. And I'm
also
considering Visual Studio Code as the base.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Thomas Kellerer]

Tim Clarke schrieb am 16.07.2018 um 11:52:
> +1 for not re-inventing the wheel - building on Netbeans or the Eclipse
> project would save you heaps of time and effort and provide
> cross-platform out of the box. I use Eclipse all the time.

The NetBeans platform (and Eclipse as well) is based on Java however. 

But Dmitry stated that he is using C++ so that won't really help.

AW: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[kpi6288]

We – and the majority of our customers - are mainly focused on Windows. We use 
pgadmin iii and our own assistants. pgadmin iv ist still too slow on Windows 
compared to pgadmin iii. That is one reason why we still use PostgreSQL 9.6.  

 

That said, one requirement on a commercial tool for us would be royalty free 
distribution to our customers. It should however provide the functions of 
pgadmin iii. 

 

Regards Klaus

 

 

Von: Dmitry Igrishin  
Gesendet: Sonntag, 15. Juli 2018 18:59
An: pgsql-gene...@postgresql.org
Betreff: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

 

Hello all,

 

Colleagues. There is an idea to develop a commercial IDE for PostgreSQL under 
Windows.

At the initial stage, not so much an IDE, as an assistant for the server side 
development.

What features would you like to see in such an instrument? Thanks.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 14:26, :

> We – and the majority of our customers - are mainly focused on Windows. We
> use pgadmin iii and our own assistants. pgadmin iv ist still too slow on
> Windows compared to pgadmin iii. That is one reason why we still use
> PostgreSQL 9.6.
>
For performance reasons I mostly use the C++ language. Thus, I think the
performance
should not be a problem here.


>
>
> That said, one requirement on a commercial tool for us would be royalty
> free distribution to our customers. It should however provide the functions
> of pgadmin iii.
>
Do you need an administration tool or an assistant for database
development? I conceived Pgspa as a
development tool, which works with source files organized in the usual way.
For example, the sources
of the schema "foo" could be organized as:
  foo/functions/*.sql
   /views/*.sql
   /triggers/*.sql
   ...
The developer works with files rather than objects retrieved from the
database and loaded
into the tree view of the GUI (like in pgAdmin and most of other similar
tools). Though, the
database browser GUI is a useful feature of course, and should be
implemented.


>
> Regards Klaus
>
>
>
>
>
> *Von:* Dmitry Igrishin 
> *Gesendet:* Sonntag, 15. Juli 2018 18:59
> *An:* pgsql-gene...@postgresql.org
> *Betreff:* Do we need yet another IDE (SQL development assistant) for
> PostgreSQL?
>
>
>
> Hello all,
>
>
>
> Colleagues. There is an idea to develop a commercial IDE for PostgreSQL
> under Windows.
>
> At the initial stage, not so much an IDE, as an assistant for the server
> side development.
>
> What features would you like to see in such an instrument? Thanks.
>

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Pavel Stehule]

2018-07-16 13:52 GMT+02:00 Dmitry Igrishin :

>
>
> пн, 16 июл. 2018 г. в 14:26, :
>
>> We – and the majority of our customers - are mainly focused on Windows.
>> We use pgadmin iii and our own assistants. pgadmin iv ist still too slow on
>> Windows compared to pgadmin iii. That is one reason why we still use
>> PostgreSQL 9.6.
>>
> For performance reasons I mostly use the C++ language. Thus, I think the
> performance
> should not be a problem here.
>
>
>>
>>
>> That said, one requirement on a commercial tool for us would be royalty
>> free distribution to our customers. It should however provide the functions
>> of pgadmin iii.
>>
> Do you need an administration tool or an assistant for database
> development? I conceived Pgspa as a
> development tool, which works with source files organized in the usual
> way. For example, the sources
> of the schema "foo" could be organized as:
>   foo/functions/*.sql
>/views/*.sql
>/triggers/*.sql
>...
> The developer works with files rather than objects retrieved from the
> database and loaded
> into the tree view of the GUI (like in pgAdmin and most of other similar
> tools). Though, the
> database browser GUI is a useful feature of course, and should be
> implemented.
>

Few years I am thinking about new IDE for stored procedures. Probably It
should not be written from scratch, but It should to be multiplatform.

what can be nice

1. source should be in files with GIT support
2. integration with developer databese + well autocomplete support
3. formatting - SQL, PL, ..
4. online code validation
5. The should not be strong relation between files and schemas. Now is not
too hard to have information what content is in some file. There can be
physical organization (by files), and logical (by schemas, functions,
views, ...)
6. good performance is important - but Java is good enough today - DBeaver
is has good speed

Regards

Good luck - can be pretty hard to write it.

p.s. IDE for developers is some different than admin tool for
administrators. Should be decided what is target.

Pavel



>
>>
>> Regards Klaus
>>
>>
>>
>>
>>
>> *Von:* Dmitry Igrishin 
>> *Gesendet:* Sonntag, 15. Juli 2018 18:59
>> *An:* pgsql-gene...@postgresql.org
>> *Betreff:* Do we need yet another IDE (SQL development assistant) for
>> PostgreSQL?
>>
>>
>>
>> Hello all,
>>
>>
>>
>> Colleagues. There is an idea to develop a commercial IDE for PostgreSQL
>> under Windows.
>>
>> At the initial stage, not so much an IDE, as an assistant for the server
>> side development.
>>
>> What features would you like to see in such an instrument? Thanks.
>>
>

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 15:01, Pavel Stehule :

>
>
> 2018-07-16 13:52 GMT+02:00 Dmitry Igrishin :
>
>>
>>
>> пн, 16 июл. 2018 г. в 14:26, :
>>
>>> We – and the majority of our customers - are mainly focused on Windows.
>>> We use pgadmin iii and our own assistants. pgadmin iv ist still too slow on
>>> Windows compared to pgadmin iii. That is one reason why we still use
>>> PostgreSQL 9.6.
>>>
>> For performance reasons I mostly use the C++ language. Thus, I think the
>> performance
>> should not be a problem here.
>>
>>
>>>
>>>
>>> That said, one requirement on a commercial tool for us would be royalty
>>> free distribution to our customers. It should however provide the functions
>>> of pgadmin iii.
>>>
>> Do you need an administration tool or an assistant for database
>> development? I conceived Pgspa as a
>> development tool, which works with source files organized in the usual
>> way. For example, the sources
>> of the schema "foo" could be organized as:
>>   foo/functions/*.sql
>>/views/*.sql
>>/triggers/*.sql
>>...
>> The developer works with files rather than objects retrieved from the
>> database and loaded
>> into the tree view of the GUI (like in pgAdmin and most of other similar
>> tools). Though, the
>> database browser GUI is a useful feature of course, and should be
>> implemented.
>>
>
> Few years I am thinking about new IDE for stored procedures. Probably It
> should not be written from scratch, but It should to be multiplatform.
>
Me too :-) I have a command line prototype of the tool with the basic
functional. It's written
in C++ by using the Pgfe client library and in PL/pgSQL as the PostgreSQL
extension.


> what can be nice
>
> 1. source should be in files with GIT support
>
+1. It's the main feature. Already done.

> 2. integration with developer databese + well autocomplete support
>
It's the most hard part and could be implemented later.

> 3. formatting - SQL, PL, ..
>
 Good feature for future releases.

> 4. online code validation
>
Not sure I understand. Can you please elaborate what do you mean?

> 5. The should not be strong relation between files and schemas. Now is not
> too hard to have information what content is in some file. There can be
> physical organization (by files), and logical (by schemas, functions,
> views, ...)
>
I agree and there is no problems with it. But logical organization would be
a bit simpler
to implement, and would be suitable for the most users. Also it can be even
helpful when someone
working with foreign project since the database objects are arranged in
shelves.

> 6. good performance is important - but Java is good enough today - DBeaver
> is has good speed
>
My primary (and favorite) language still C++ :-)

>
> Regards
>
> Good luck - can be pretty hard to write it.
>
Thank you, Pavel! But I haven't decided about starting this project, since
I'm not sure about
the interest from the community.

>
> p.s. IDE for developers is some different than admin tool for
> administrators. Should be decided what is target.
>
Yeah, I'm talking about the tool for developers here.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Pavel Stehule]

2018-07-16 14:28 GMT+02:00 Dmitry Igrishin :

>
>
> пн, 16 июл. 2018 г. в 15:01, Pavel Stehule :
>
>>
>>
>> 2018-07-16 13:52 GMT+02:00 Dmitry Igrishin :
>>
>>>
>>>
>>> пн, 16 июл. 2018 г. в 14:26, :
>>>
 We – and the majority of our customers - are mainly focused on Windows.
 We use pgadmin iii and our own assistants. pgadmin iv ist still too slow on
 Windows compared to pgadmin iii. That is one reason why we still use
 PostgreSQL 9.6.

>>> For performance reasons I mostly use the C++ language. Thus, I think the
>>> performance
>>> should not be a problem here.
>>>
>>>


 That said, one requirement on a commercial tool for us would be royalty
 free distribution to our customers. It should however provide the functions
 of pgadmin iii.

>>> Do you need an administration tool or an assistant for database
>>> development? I conceived Pgspa as a
>>> development tool, which works with source files organized in the usual
>>> way. For example, the sources
>>> of the schema "foo" could be organized as:
>>>   foo/functions/*.sql
>>>/views/*.sql
>>>/triggers/*.sql
>>>...
>>> The developer works with files rather than objects retrieved from the
>>> database and loaded
>>> into the tree view of the GUI (like in pgAdmin and most of other similar
>>> tools). Though, the
>>> database browser GUI is a useful feature of course, and should be
>>> implemented.
>>>
>>
>> Few years I am thinking about new IDE for stored procedures. Probably It
>> should not be written from scratch, but It should to be multiplatform.
>>
> Me too :-) I have a command line prototype of the tool with the basic
> functional. It's written
> in C++ by using the Pgfe client library and in PL/pgSQL as the PostgreSQL
> extension.
>
>
>> what can be nice
>>
>> 1. source should be in files with GIT support
>>
> +1. It's the main feature. Already done.
>
>> 2. integration with developer databese + well autocomplete support
>>
> It's the most hard part and could be implemented later.
>

The basic autocomplete is necessary - table names, column names, .. It
should not be too intelligent - but this is main benefit again generic
already available IDE.


> 3. formatting - SQL, PL, ..
>>
>  Good feature for future releases.
>
4. online code validation
>>
> Not sure I understand. Can you please elaborate what do you mean?
>

For PLpgSQL simple (press one key) send source code to server and highlight
errors (it can be integrated with plpgsql_check). For SQL using not
existing identifier, ..



> 5. The should not be strong relation between files and schemas. Now is not
>> too hard to have information what content is in some file. There can be
>> physical organization (by files), and logical (by schemas, functions,
>> views, ...)
>>
> I agree and there is no problems with it. But logical organization would
> be a bit simpler
> to implement, and would be suitable for the most users. Also it can be
> even helpful when someone
> working with foreign project since the database objects are arranged in
> shelves.
>

I cannot to estimate the cost of these variants - I use mapping - one
schema - one or more files, but the objects to files are divided by
dependency - some objects can be simply updated, other not.

Very specific kind of DB objects are views. The IDE can helps with changes
of views. It is pretty hard now due dependency.


> 6. good performance is important - but Java is good enough today - DBeaver
>> is has good speed
>>
> My primary (and favorite) language still C++ :-)
>

I have no problem with it. But C++ is harder for junior developers and
multiplatform Qt can be expensive for commercial product. But I understand
personal preferences (I don't like Java too). On second hand - the
performance argument is not valid against Java.


>> Regards
>>
>> Good luck - can be pretty hard to write it.
>>
> Thank you, Pavel! But I haven't decided about starting this project, since
> I'm not sure about
> the interest from the community.
>

Understand. Developer is alone every time. But lot of work is done. If I
started similar project (but I have not this plan), then I don't try to
write own IDE, but I'll use some existing and I'll write plugin for
eclipse, or some else.


Although the work is maybe harder, you can get more quickly more wide
community.

Regards

Pavel



>> p.s. IDE for developers is some different than admin tool for
>> administrators. Should be decided what is target.
>>
> Yeah, I'm talking about the tool for developers here.
>

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 16:00, Pavel Stehule :

>
>
> 2018-07-16 14:28 GMT+02:00 Dmitry Igrishin :
>
>>
>>
>> пн, 16 июл. 2018 г. в 15:01, Pavel Stehule :
>>
>>>
>>>
>>> 2018-07-16 13:52 GMT+02:00 Dmitry Igrishin :
>>>


 пн, 16 июл. 2018 г. в 14:26, :

> We – and the majority of our customers - are mainly focused on
> Windows. We use pgadmin iii and our own assistants. pgadmin iv ist still
> too slow on Windows compared to pgadmin iii. That is one reason why we
> still use PostgreSQL 9.6.
>
 For performance reasons I mostly use the C++ language. Thus, I think
 the performance
 should not be a problem here.


>
>
> That said, one requirement on a commercial tool for us would be
> royalty free distribution to our customers. It should however provide the
> functions of pgadmin iii.
>
 Do you need an administration tool or an assistant for database
 development? I conceived Pgspa as a
 development tool, which works with source files organized in the usual
 way. For example, the sources
 of the schema "foo" could be organized as:
   foo/functions/*.sql
/views/*.sql
/triggers/*.sql
...
 The developer works with files rather than objects retrieved from the
 database and loaded
 into the tree view of the GUI (like in pgAdmin and most of other
 similar tools). Though, the
 database browser GUI is a useful feature of course, and should be
 implemented.

>>>
>>> Few years I am thinking about new IDE for stored procedures. Probably It
>>> should not be written from scratch, but It should to be multiplatform.
>>>
>> Me too :-) I have a command line prototype of the tool with the basic
>> functional. It's written
>> in C++ by using the Pgfe client library and in PL/pgSQL as the PostgreSQL
>> extension.
>>
>>
>>> what can be nice
>>>
>>> 1. source should be in files with GIT support
>>>
>> +1. It's the main feature. Already done.
>>
>>> 2. integration with developer databese + well autocomplete support
>>>
>> It's the most hard part and could be implemented later.
>>
>
> The basic autocomplete is necessary - table names, column names, .. It
> should not be too intelligent - but this is main benefit again generic
> already available IDE.
>
Suppose the one write
  create table foo (id integer default n
and the autocomplete shows all it knows that starts with "n". Would you be
satisfied with such an autocomplete? :-)
Me - not. (Although it is relatively easy to implement.)

>
>
>> 3. formatting - SQL, PL, ..
>>>
>>  Good feature for future releases.
>>
> 4. online code validation
>>>
>> Not sure I understand. Can you please elaborate what do you mean?
>>
>
> For PLpgSQL simple (press one key) send source code to server and
> highlight errors (it can be integrated with plpgsql_check). For SQL using
> not existing identifier, ..
>
Wow, cool! With plpgsql_check it's possible to achieve the user experience
similar to the SLIME - the IDE for Common Lisp.

>
>
>
>> 5. The should not be strong relation between files and schemas. Now is
>>> not too hard to have information what content is in some file. There can be
>>> physical organization (by files), and logical (by schemas, functions,
>>> views, ...)
>>>
>> I agree and there is no problems with it. But logical organization would
>> be a bit simpler
>> to implement, and would be suitable for the most users. Also it can be
>> even helpful when someone
>> working with foreign project since the database objects are arranged in
>> shelves.
>>
>
> I cannot to estimate the cost of these variants - I use mapping - one
> schema - one or more files, but the objects to files are divided by
> dependency - some objects can be simply updated, other not.
>
The prototype I already have can deal with DDL commands organized as the
user wish. No
need to create the objects in the order of dependency. This is a very
convenient.

>
> Very specific kind of DB objects are views. The IDE can helps with changes
> of views. It is pretty hard now due dependency.
>
Yes! My tool can safely drop the dependend objects (with no cascading) and
recreate all of them from files.

>
>
>> 6. good performance is important - but Java is good enough today -
>>> DBeaver is has good speed
>>>
>> My primary (and favorite) language still C++ :-)
>>
>
> I have no problem with it. But C++ is harder for junior developers and
> multiplatform Qt can be expensive for commercial product. But I understand
> personal preferences (I don't like Java too). On second hand - the
> performance argument is not valid against Java.
>

>
>>> Regards
>>>
>>> Good luck - can be pretty hard to write it.
>>>
>> Thank you, Pavel! But I haven't decided about starting this project,
>> since I'm not sure about
>> the interest from the community.
>>
>
> Understand. Developer is alone every time. But lot of work is done. If I
> started similar project (but I have not this plan), then I do

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Pavel Stehule]

2018-07-16 15:22 GMT+02:00 Dmitry Igrishin :

>
>
> пн, 16 июл. 2018 г. в 16:00, Pavel Stehule :
>
>>
>>
>> 2018-07-16 14:28 GMT+02:00 Dmitry Igrishin :
>>
>>>
>>>
>>> пн, 16 июл. 2018 г. в 15:01, Pavel Stehule :
>>>


 2018-07-16 13:52 GMT+02:00 Dmitry Igrishin :

>
>
> пн, 16 июл. 2018 г. в 14:26, :
>
>> We – and the majority of our customers - are mainly focused on
>> Windows. We use pgadmin iii and our own assistants. pgadmin iv ist still
>> too slow on Windows compared to pgadmin iii. That is one reason why we
>> still use PostgreSQL 9.6.
>>
> For performance reasons I mostly use the C++ language. Thus, I think
> the performance
> should not be a problem here.
>
>
>>
>>
>> That said, one requirement on a commercial tool for us would be
>> royalty free distribution to our customers. It should however provide the
>> functions of pgadmin iii.
>>
> Do you need an administration tool or an assistant for database
> development? I conceived Pgspa as a
> development tool, which works with source files organized in the usual
> way. For example, the sources
> of the schema "foo" could be organized as:
>   foo/functions/*.sql
>/views/*.sql
>/triggers/*.sql
>...
> The developer works with files rather than objects retrieved from the
> database and loaded
> into the tree view of the GUI (like in pgAdmin and most of other
> similar tools). Though, the
> database browser GUI is a useful feature of course, and should be
> implemented.
>

 Few years I am thinking about new IDE for stored procedures. Probably
 It should not be written from scratch, but It should to be multiplatform.

>>> Me too :-) I have a command line prototype of the tool with the basic
>>> functional. It's written
>>> in C++ by using the Pgfe client library and in PL/pgSQL as the
>>> PostgreSQL extension.
>>>
>>>
 what can be nice

 1. source should be in files with GIT support

>>> +1. It's the main feature. Already done.
>>>
 2. integration with developer databese + well autocomplete support

>>> It's the most hard part and could be implemented later.
>>>
>>
>> The basic autocomplete is necessary - table names, column names, .. It
>> should not be too intelligent - but this is main benefit again generic
>> already available IDE.
>>
> Suppose the one write
>   create table foo (id integer default n
> and the autocomplete shows all it knows that starts with "n". Would you be
> satisfied with such an autocomplete? :-)
> Me - not. (Although it is relatively easy to implement.)
>
>>
>>
>>> 3. formatting - SQL, PL, ..

>>>  Good feature for future releases.
>>>
>> 4. online code validation

>>> Not sure I understand. Can you please elaborate what do you mean?
>>>
>>
>> For PLpgSQL simple (press one key) send source code to server and
>> highlight errors (it can be integrated with plpgsql_check). For SQL using
>> not existing identifier, ..
>>
> Wow, cool! With plpgsql_check it's possible to achieve the user experience
> similar to the SLIME - the IDE for Common Lisp.
>
>>
>>
>>
>>> 5. The should not be strong relation between files and schemas. Now is
 not too hard to have information what content is in some file. There can be
 physical organization (by files), and logical (by schemas, functions,
 views, ...)

>>> I agree and there is no problems with it. But logical organization would
>>> be a bit simpler
>>> to implement, and would be suitable for the most users. Also it can be
>>> even helpful when someone
>>> working with foreign project since the database objects are arranged in
>>> shelves.
>>>
>>
>> I cannot to estimate the cost of these variants - I use mapping - one
>> schema - one or more files, but the objects to files are divided by
>> dependency - some objects can be simply updated, other not.
>>
> The prototype I already have can deal with DDL commands organized as the
> user wish. No
> need to create the objects in the order of dependency. This is a very
> convenient.
>
>>
>> Very specific kind of DB objects are views. The IDE can helps with
>> changes of views. It is pretty hard now due dependency.
>>
> Yes! My tool can safely drop the dependend objects (with no cascading) and
> recreate all of them from files.
>
>>
>>
>>> 6. good performance is important - but Java is good enough today -
 DBeaver is has good speed

>>> My primary (and favorite) language still C++ :-)
>>>
>>
>> I have no problem with it. But C++ is harder for junior developers and
>> multiplatform Qt can be expensive for commercial product. But I understand
>> personal preferences (I don't like Java too). On second hand - the
>> performance argument is not valid against Java.
>>
>
>>
 Regards

 Good luck - can be pretty hard to write it.

>>> Thank you, Pavel! But I haven't decided about starting this p

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Tim Clarke]

-1 for VSC not being open source

Tim Clarke


On 16/07/18 11:47, Dmitry Igrishin wrote:
>
>
> пн, 16 июл. 2018 г. в 13:41, Tim Clarke
>  >:
>
> +1 for not re-inventing the wheel - building on Netbeans or the
> Eclipse
> project would save you heaps of time and effort and provide
> cross-platform out of the box. I use Eclipse all the time.
>
> I agree and don't want to waste my time for reinventing the wheel. And
> I'm also
> considering Visual Studio Code as the base.

RE: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Chris Coutinho]

VS Code is open source (MIT License). You may be thinking of Visual Studo, the 
closed source IDE - the two are not the same

Met vriendlijke groet,
REDstack BV

Chris Coutinho
Onderzoeker/Data analist

-Original Message-
From: Tim Clarke [mailto:tim.cla...@minerva-analytics.info] 
Sent: maandag 16 juli 2018 13:09
To: pgsql-general@lists.postgresql.org
Cc: Dmitry Igrishin 
Subject: Re: Do we need yet another IDE (SQL development assistant) for 
PostgreSQL?

-1 for VSC not being open source

Tim Clarke


On 16/07/18 11:47, Dmitry Igrishin wrote:
>
>
> пн, 16 июл. 2018 г. в 13:41, Tim Clarke 
>  >:
>
> +1 for not re-inventing the wheel - building on Netbeans or the
> Eclipse
> project would save you heaps of time and effort and provide
> cross-platform out of the box. I use Eclipse all the time.
>
> I agree and don't want to waste my time for reinventing the wheel. And 
> I'm also considering Visual Studio Code as the base.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Josef Šimánek]

It is licensed under MIT - https://github.com/Microsoft/vscode.

2018-07-16 13:09 GMT+02:00 Tim Clarke :
> -1 for VSC not being open source
>
> Tim Clarke
>
>
> On 16/07/18 11:47, Dmitry Igrishin wrote:
>>
>>
>> пн, 16 июл. 2018 г. в 13:41, Tim Clarke
>> > >:
>>
>> +1 for not re-inventing the wheel - building on Netbeans or the
>> Eclipse
>> project would save you heaps of time and effort and provide
>> cross-platform out of the box. I use Eclipse all the time.
>>
>> I agree and don't want to waste my time for reinventing the wheel. And
>> I'm also
>> considering Visual Studio Code as the base.
>
>

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Tim Clarke]

Ah thanks, I stand corected! :)

Tim Clarke


On 16/07/18 15:04, Josef Šimánek wrote:
> It is licensed under MIT - https://github.com/Microsoft/vscode.
>
> 2018-07-16 13:09 GMT+02:00 Tim Clarke :
>> -1 for VSC not being open source
>>
>> Tim Clarke
>>
>>

Query to monitor index bloat

[Alessandro Aste]

Hello,  I am trying to put togheter a query to monitor the index bloat for
a database I maintain.
Is there a "SQL" way to obtain  bloated index ? I googled around but I
found nothing working.

I'm currently running 9.6 but I'm looking for something compatible with
version 10 too.

Thank you very much in advance,


Alessandro.

Re: Query to monitor index bloat

[Adrien NAYRAT]

On 07/16/2018 05:16 PM, Alessandro Aste wrote:
Hello,  I am trying to put togheter a query to monitor the index bloat 
for a database I maintain.
Is there a "SQL" way to obtain  bloated index ? I googled around but I 
found nothing working.


I'm currently running 9.6 but I'm looking for something compatible with 
version 10 too.


Thank you very much in advance,


Alessandro.


Hello,

You should look at : https://github.com/ioguix/pgsql-bloat-estimation

Regards,

User documentation vs Official Docs

[Joshua D. Drake]

-general.

Over the last year as I have visited many meetups and interacted with 
people at conferences etc... There are three prevailing issues that 
continue to come up in contributing to the community. This email is 
about one of them. Where is the "user" documentation? The official 
documentation is awesome, if you know what you are doing. It is not 
particularly useful for HOWTO style docs. There is some user 
documentation in the wiki but let's be honest, writing a 
blog/article/howto in a wiki is a pain in the butt.


What does the community think about a community run, community 
organized, sub project for USER documentation? This type of 
documentation would be things like, "10 steps to configure replication", 
"Dumb simple Postgres backups",  "5 things to NEVER do with Postgres". I 
imagine we would sort it by version (9.6/10.0 etc...) as well as break 
it down via type (Administration, Tuning, Gotchas) etc...


What do we think?

Thanks!

JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: User documentation vs Official Docs

[Adrian Klaver]

On 07/16/2018 01:32 PM, Joshua D. Drake wrote:

-general.

Over the last year as I have visited many meetups and interacted with 
people at conferences etc... There are three prevailing issues that 
continue to come up in contributing to the community. This email is 
about one of them. Where is the "user" documentation? The official 
documentation is awesome, if you know what you are doing. It is not 
particularly useful for HOWTO style docs. There is some user 
documentation in the wiki but let's be honest, writing a 
blog/article/howto in a wiki is a pain in the butt.


What does the community think about a community run, community 
organized, sub project for USER documentation? This type of 
documentation would be things like, "10 steps to configure replication", 
"Dumb simple Postgres backups",  "5 things to NEVER do with Postgres". I 
imagine we would sort it by version (9.6/10.0 etc...) as well as break 
it down via type (Administration, Tuning, Gotchas) etc...


What do we think?


Not sure this is much different from the Wiki unless:

Who is going to?:

1) Run/maintain it.

2) Get people to contribute.

3) Edit new content, clean up old content





Thanks!

JD





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

Re: User documentation vs Official Docs

[Thomas Kellerer]

Joshua D. Drake schrieb am 16.07.2018 um 22:32:

-general.

Over the last year as I have visited many meetups and interacted with
people at conferences etc... There are three prevailing issues that
continue to come up in contributing to the community. This email is
about one of them. Where is the "user" documentation? The official
documentation is awesome, if you know what you are doing. It is not
particularly useful for HOWTO style docs. There is some user
documentation in the wiki but let's be honest, writing a
blog/article/howto in a wiki is a pain in the butt.

What does the community think about a community run, community
organized, sub project for USER documentation? This type of
documentation would be things like, "10 steps to configure
replication", "Dumb simple Postgres backups",  "5 things to NEVER do
with Postgres". I imagine we would sort it by version (9.6/10.0
etc...) as well as break it down via type (Administration, Tuning,
Gotchas) etc...



What about: https://en.wikibooks.org/wiki/PostgreSQL

Re: User documentation vs Official Docs

[Benjamin Scherrey]

On Tue, Jul 17, 2018, 3:33 AM Joshua D. Drake  wrote:

> -general.
>
> Over the last year as I have visited many meetups and interacted with
> people at conferences etc... There are three prevailing issues that
> continue to come up in contributing to the community. This email is
> about one of them. Where is the "user" documentation? The official
> documentation is awesome, if you know what you are doing. It is not
> particularly useful for HOWTO style docs. There is some user
> documentation in the wiki but let's be honest, writing a
> blog/article/howto in a wiki is a pain in the butt.
>
> What does the community think about a community run, community
> organized, sub project for USER documentation? This type of
> documentation would be things like, "10 steps to configure replication",
> "Dumb simple Postgres backups",  "5 things to NEVER do with Postgres". I
> imagine we would sort it by version (9.6/10.0 etc...) as well as break
> it down via type (Administration, Tuning, Gotchas) etc...
>
> What do we think?
>
> Thanks!
>
> JD
>
>
> --
> Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
> ***  A fault and talent of mine is to tell it exactly how it is.  ***
> PostgreSQL centered full stack support, consulting and development.
> Advocate: @amplifypostgres || Learn: https://postgresconf.org
> * Unless otherwise stated, opinions are my own.   *
>
>
One thing I recall very fondly about the early days of the Lamp stack was
that the official documentation of PHP and MySQL was augmented with user
created practical examples. It was still reference documentation organized
by command or function, but in a comment-like section underneath the formal
docs were user provided short practical examples of how the command would
be used in real situations. One was able to teach themselves how to build a
dynamic website front ending a database just by exploring the core docs and
reading the examples.

-- Ben Scherrey

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 01:39 PM, Adrian Klaver wrote:


Not sure this is much different from the Wiki unless:


The wiki is certainly "a place" that can be used for this but the wiki 
takes a lot of effort to find things on it, manage it etc...



Who is going to?:

1) Run/maintain it.



Well this is a community. The hope would be that the community would 
step up. As a proposer (and writer) I am certainly willing to participate.




2) Get people to contribute.


I don't think this is nearly as difficult except if we create artificial 
barriers to contribution (writing in the wiki is a highly subpar 
experience).




3) Edit new content, clean up old content



So some of this could be original content, some of it could just be 
links to various blogs/articles that already exist and some could be 
maintenance. However, I see maintenance as a secondary issue because we 
would publish based on version. If someone wrote an article for 9.6 it 
may or may not apply for 11 but that doesn't matter. People are always 
generating new content.


JD









Thanks!

JD







--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: User documentation vs Official Docs

[Tom Lane]

"Joshua D. Drake"  writes:
> On 07/16/2018 01:39 PM, Adrian Klaver wrote:
>> Not sure this is much different from the Wiki unless:

> The wiki is certainly "a place" that can be used for this but the wiki 
> takes a lot of effort to find things on it, manage it etc...

You haven't exactly explained how that wouldn't be equally true of
this hypothetical new thing.

regards, tom lane

Re: User documentation vs Official Docs

[Stephen Frost]

Greetings,

* Benjamin Scherrey (scher...@proteus-tech.com) wrote:
> One thing I recall very fondly about the early days of the Lamp stack was
> that the official documentation of PHP and MySQL was augmented with user
> created practical examples. It was still reference documentation organized
> by command or function, but in a comment-like section underneath the formal
> docs were user provided short practical examples of how the command would
> be used in real situations. One was able to teach themselves how to build a
> dynamic website front ending a database just by exploring the core docs and
> reading the examples.

We have a place for this to go, in the official docs, already split out
by version, and it starts here:

https://www.postgresql.org/docs/10/static/tutorial-start.html

Adding more to that certainly sounds good to me.

So, for my 2c, at least, "patches welcome."

Drive-by comments saying that we need a place for this, when we already
have one, and saying that the community should develop it, while not
acknowledging or contributing to what we already have, does not strike
me as particularly useful.

We tried having a comment area on the docs and those ultimately ended up
being... less than ideal.  I'm not anxious to repeat that experiment.
I'm glad it worked for other communities, but it didn't work for us and
we have a good bit of history to show that.

The best way to improve that section of the docs is to write up good
user example-based documentation and submit it as patches.  I'd
certainly be happy to see that and to try and help by moving such
patches forward to commit.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: User documentation vs Official Docs

[Adrian Klaver]

On 07/16/2018 01:48 PM, Joshua D. Drake wrote:

On 07/16/2018 01:39 PM, Adrian Klaver wrote:


Not sure this is much different from the Wiki unless:


The wiki is certainly "a place" that can be used for this but the wiki 
takes a lot of effort to find things on it, manage it etc...



Who is going to?:

1) Run/maintain it.



Well this is a community. The hope would be that the community would 
step up. As a proposer (and writer) I am certainly willing to participate.


In theory a nice idea, but community encompasses everybody and when it 
comes to organization everybody = nobody. Without some sort of editorial 
board running this then we are back to the Wiki.






2) Get people to contribute.


I don't think this is nearly as difficult except if we create artificial 
barriers to contribution (writing in the wiki is a highly subpar 
experience).


Not difficult, but folks will write about what they are interested in 
which is not necessarily what users are looking for.






3) Edit new content, clean up old content



So some of this could be original content, some of it could just be 
links to various blogs/articles that already exist and some could be 
maintenance. However, I see maintenance as a secondary issue because we 
would publish based on version. If someone wrote an article for 9.6 it 
may or may not apply for 11 but that doesn't matter. People are always 
generating new content.


Actually I see maintenance as a big if not the primary issue. The only 
thing worse then no docs are docs that are not vetted/maintained. The 
list is full of posts from folks that got information from dubious 
sources and did things in error. If this project is to be useful then 
the quality of the information should match that of the official 
documentation and that is a high standard.




JD









Thanks!

JD










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

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 01:59 PM, Stephen Frost wrote:


We have a place for this to go, in the official docs, already split out
by version, and it starts here:

https://www.postgresql.org/docs/10/static/tutorial-start.html

Adding more to that certainly sounds good to me.


I didn't know that existed. I will take a look.

JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 02:22 PM, Joshua D. Drake wrote:

On 07/16/2018 01:59 PM, Stephen Frost wrote:


We have a place for this to go, in the official docs, already split out
by version, and it starts here:

https://www.postgresql.org/docs/10/static/tutorial-start.html

Adding more to that certainly sounds good to me.


I didn't know that existed. I will take a look.


Well now that I see it is just the "tutorial" in the official docs, I 
disagree that is the correct place to start. At least not if it is going 
to ship with the 1000+ pages of documentation we already have. What I am 
envisioning is something with a strong SEO that gives pointed and direct 
information about solving a specific problem. A tutorial book could 
certainly do that as could (what I am really talking about) is Postgres 
recipes or something like that.


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Query to monitor index bloat

[Alessandro Aste]

Thanks much, I'll check that out.  I see the queries are 3 years old so I'm
wondering if they still work for 9.6.x or 10

Il lun 16 lug 2018, 17:44 Adrien NAYRAT  ha
scritto:

> On 07/16/2018 05:16 PM, Alessandro Aste wrote:
> > Hello,  I am trying to put togheter a query to monitor the index bloat
> > for a database I maintain.
> > Is there a "SQL" way to obtain  bloated index ? I googled around but I
> > found nothing working.
> >
> > I'm currently running 9.6 but I'm looking for something compatible with
> > version 10 too.
> >
> > Thank you very much in advance,
> >
> >
> > Alessandro.
>
> Hello,
>
> You should look at : https://github.com/ioguix/pgsql-bloat-estimation
>
> Regards,
>

Re: User documentation vs Official Docs

[Vick Khera]

On Mon, Jul 16, 2018 at 5:44 PM, Joshua D. Drake 
wrote:

> On 07/16/2018 02:22 PM, Joshua D. Drake wrote:
>
>> On 07/16/2018 01:59 PM, Stephen Frost wrote:
>>
>>>
>>> We have a place for this to go, in the official docs, already split out
>>> by version, and it starts here:
>>>
>>> https://www.postgresql.org/docs/10/static/tutorial-start.html
>>>
>>> Adding more to that certainly sounds good to me.
>>>
>>
>> I didn't know that existed. I will take a look.
>
>
> Well now that I see it is just the "tutorial" in the official docs, I
> disagree that is the correct place to start. At least not if it is going to
> ship with the 1000+ pages of documentation we already have. What I am
> envisioning is something with a strong SEO that gives pointed and direct
> information about solving a specific problem. A tutorial book could
> certainly do that as could (what I am really talking about) is Postgres
> recipes or something like that.
>
>

I didn't know it existed either, mostly because I know how to ask google to
do things, and the things I need to know are not covered here (yet). This
does seem to me to be the ideal place to add more how to documentation to
augment all the reference docs we have.

As for some "strong SEO" I think already the top hit for almost everything
I seek postgres related is the official manual, so it seems to have good
SEO. The only big improvement would be somehow to tell google to only show
me the newest version of the manual, not all of the older ones too, for the
same page.

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 02:54 PM, Vick Khera wrote:


On Mon, Jul 16, 2018 at 5:44 PM, Joshua D. Drake > wrote:


On 07/16/2018 02:22 PM, Joshua D. Drake wrote:

On 07/16/2018 01:59 PM, Stephen Frost wrote:


We have a place for this to go, in the official docs,
already split out
by version, and it starts here:

https://www.postgresql.org/docs/10/static/tutorial-start.html



As for some "strong SEO" I think already the top hit for almost 
everything I seek postgres related is the official manual, so it seems 
to have good SEO. The only big improvement would be somehow to tell 
google to only show me the newest version of the manual, not all of 
the older ones too, for the same page.




You aren't wrong on how its ranked but here is an example of what I am 
talking about with SEO:


Search:
postgresql backups

For me, the first three are the docs which are not very useful if you 
just want backups that just work (unless you are someone like you and I 
who are using the docs for reference not howto). The fourth link is this 
one:


https://www.compose.com/articles/postgresql-backups-and-everything-you-need-to-know/

Which is a great article but also isn't what I am thinking about. What I 
am thinking about is articles like this:


https://www.commandprompt.com/blog/a_better_backup_with_postgresql_using_pg_dump/

Which in this case is clearly out of date but provides context of what I 
am trying to achieve.


JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: User documentation vs Official Docs

[Tim Cross]

Joshua D. Drake  writes:

> -general.
>
> Over the last year as I have visited many meetups and interacted with 
> people at conferences etc... There are three prevailing issues that 
> continue to come up in contributing to the community. This email is 
> about one of them. Where is the "user" documentation? The official 
> documentation is awesome, if you know what you are doing. It is not 
> particularly useful for HOWTO style docs. There is some user 
> documentation in the wiki but let's be honest, writing a 
> blog/article/howto in a wiki is a pain in the butt.
>
> What does the community think about a community run, community 
> organized, sub project for USER documentation? This type of 
> documentation would be things like, "10 steps to configure replication", 
> "Dumb simple Postgres backups", "5 things to NEVER do with Postgres". I 
> imagine we would sort it by version (9.6/10.0 etc...) as well as break 
> it down via type (Administration, Tuning, Gotchas) etc...
>
> What do we think?
>

I think encouraging user developed docs is a great idea.

However, I'm not sure how your proposal really addresses the issue. How
would your proposal deal with the "but let's be honest, writing a 
blog/article/howto in a wiki is a pain in the butt" issue? Writing
decent documentation or clear examples is hard and the only thing worse
than no documentation is misleading or confusing documentation. 

My only real concern would be to further fracture the PG user base. If
there are barriers preventing users from adding documentation to the
existing documents or wiki, perhaps it would be better to try and
address those first?

Tim

-- 
Tim Cross

Re: User documentation vs Official Docs

[David G. Johnston]

On Mon, Jul 16, 2018 at 1:32 PM, Joshua D. Drake 
wrote:

> -general.
>
> Over the last year as I have visited many meetups and interacted with
> people at conferences etc... There are three prevailing issues that
> continue to come up in contributing to the community. This email is about
> one of them. Where is the "user" documentation? The official documentation
> is awesome, if you know what you are doing. It is not particularly useful
> for HOWTO style docs. There is some user documentation in the wiki but
> let's be honest, writing a blog/article/howto in a wiki is a pain in the
> butt.
>
> What does the community think about a community run, community organized,
> sub project for USER documentation? This type of documentation would be
> things like, "10 steps to configure replication", "Dumb simple Postgres
> backups",  "5 things to NEVER do with Postgres". I imagine we would sort it
> by version (9.6/10.0 etc...) as well as break it down via type
> (Administration, Tuning, Gotchas) etc...
>
> What do we think?
>

​Politely tell them to buy some of the many well written books that are
available on these very topics...

David J.

Re: Query to monitor index bloat

[Adrien Nayrat]

On 07/16/2018 11:50 PM, Alessandro Aste wrote:
> Thanks much, I'll check that out.  I see the queries are 3 years old so I'm
> wondering if they still work for 9.6.x or 10

AFAIK they work. You can try yourself by comparing bloated table/index size
before and after vacuum full or reindex ;)




signature.asc
Description: OpenPGP digital signature

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 03:07 PM, Tim Cross wrote:


I think encouraging user developed docs is a great idea.

However, I'm not sure how your proposal really addresses the issue. How
would your proposal deal with the "but let's be honest, writing a
blog/article/howto in a wiki is a pain in the butt" issue? Writing
decent documentation or clear examples is hard and the only thing worse
than no documentation is misleading or confusing documentation.


Well I threw all this out there to start a discussion on how best this 
could be done. What *I* would do is either create a series of templates 
to be followed that we would push to HTML and PDF. That could be done 
with a form and TinyMCE or we could use LibreOffice/Office templates. 
However, I don't know that the community would buy into the office 
template idea (thus seeking feedback).

My only real concern would be to further fracture the PG user base. If
there are barriers preventing users from adding documentation to the
existing documents or wiki, perhaps it would be better to try and
address those first?


First, my assumption is that this project would be done within the .Org 
infrastructure and if the community thinks that we should just use 
DocBook that is certainly an option (although adhering to something like 
Docbook Simple may be best).


Thanks,

JD



Tim



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 03:14 PM, David G. Johnston wrote:


What does the community think about a community run, community
organized, sub project for USER documentation? This type of
documentation would be things like, "10 steps to configure
replication", "Dumb simple Postgres backups",  "5 things to NEVER
do with Postgres". I imagine we would sort it by version (9.6/10.0
etc...) as well as break it down via type (Administration, Tuning,
Gotchas) etc...

What do we think?


​Politely tell them to buy some of the many well written books that 
are available on these very topics...


Politely tell them to buy a license to MSSQl...

Kind of misses the whole point doesn't it?

JD




David J.



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Tim Cross]

Dmitry Igrishin  writes:

> пн, 16 июл. 2018 г. в 1:14, Tim Cross :
>
>>
>> Your idea to make it integrate with user's preferred editor is a good
>> idea as editors are like opinions and certain anatomical parts -
>> everyone has one! Finding an appropriate API to do this will be a
>> challenge.
>>
> I see two options here: the core of the tool acts as a long-lived server or
> as a short-lived
> console application which communicates with the editor's plugin via
> stdin/stdout.
> Btw, what the text editor do you prefer? :-)
>

Most of the time, I use Emacs on either Linux or macOS. With the support
it has for running a psql process, it works pretty well for most
things. There are pretty reasonable packages for writing SQL and
'static' completion. Getting things setup can take a bit of effort, but
once it is working, it tends to work pretty well.

The two areas where it lacks are dynamic completion i.e. completing on
objects the user has created such as table names and column
names/function names etc. and decent result formatting. 

>>
>> I seem to remember reading somewhere that Oracle was going to remove
>> swing from the core java library. I've always been a little disappointed
>> with Java UIs and found they don't give the cross-platform support that
>> Java originally promised, plus OSX/macOS has not made Java as welcome as
>> it use to be. If you do choose Java, it will need to work under openJDK
>> as this is what most Linux users will have installed.
>>
> For now, the possible options for the GUI part are Qt, wxWidgets or FLTK,
> or even Electron.

I would look at either Qt or even Electron (I believe visual code is
written using Electron, which is the other editor I use from time to
time).

There was an Emacs project called Eclaim (I think) which interfaced with
Eclipse services in order to provide dynamic completion when doing
Java. That could be worth checking out for ideas to borrow.

Tim

-- 
Tim Cross

Re: User documentation vs Official Docs

[David G. Johnston]

On Mon, Jul 16, 2018 at 3:19 PM, Joshua D. Drake 
wrote:

> On 07/16/2018 03:14 PM, David G. Johnston wrote:
>
>
> What does the community think about a community run, community organized,
>> sub project for USER documentation? This type of documentation would be
>> things like, "10 steps to configure replication", "Dumb simple Postgres
>> backups",  "5 things to NEVER do with Postgres". I imagine we would sort it
>> by version (9.6/10.0 etc...) as well as break it down via type
>> (Administration, Tuning, Gotchas) etc...
>>
>> What do we think?
>>
>
> ​Politely tell them to buy some of the many well written books that are
> available on these very topics...
>
>
> Politely tell them to buy a license to MSSQl...
>
> Kind of misses the whole point doesn't it?
>

​I'm going for practicality over idealism here.  That some of the best
written material for learning how to be an application developer or DBA is
presently really only available in the forms of books is a fact of our
existence.  I frankly don't have a problem that there isn't a "free beer"
resource available to complete with it.


I'm all for continual improvement but color me doubtful that there is
enough desire and discipline here to invent and then maintain a
high-maintenance system.  So, yes, I am intentionally trying to avoid the
trap that is problem that you want to solve by suggesting forgetting the
revolution and instead coming at the problem from an entirely different
angle and working to evolve the equilibrium that presently exists.

David J.

Re: User documentation vs Official Docs

[Adrian Klaver]

On 07/16/2018 03:18 PM, Joshua D. Drake wrote:

On 07/16/2018 03:07 PM, Tim Cross wrote:


I think encouraging user developed docs is a great idea.

However, I'm not sure how your proposal really addresses the issue. How
would your proposal deal with the "but let's be honest, writing a
blog/article/howto in a wiki is a pain in the butt" issue? Writing
decent documentation or clear examples is hard and the only thing worse
than no documentation is misleading or confusing documentation.


Well I threw all this out there to start a discussion on how best this 
could be done. What *I* would do is either create a series of templates 
to be followed that we would push to HTML and PDF. That could be done 
with a form and TinyMCE or we could use LibreOffice/Office templates. 
However, I don't know that the community would buy into the office 
template idea (thus seeking feedback).

My only real concern would be to further fracture the PG user base. If
there are barriers preventing users from adding documentation to the
existing documents or wiki, perhaps it would be better to try and
address those first?


First, my assumption is that this project would be done within the .Org 
infrastructure and if the community thinks that we should just use 
DocBook that is certainly an option (although adhering to something like 
Docbook Simple may be best).


All the above is cool and everything, but is putting the cart before the 
horse. To me to make this work the following needs to happen:


1) Create an editorial board. This group of people would determine the 
answers to the questions above. They would also develop the framework 
for what needs covered. This would be based on what users want to see. 
Then a call for contributors could be made.


2) The other thing the editorial board would do is create list of 
qualified peer reviewers. These folks would then review the 
contributions and give feedback. On successfully passing a review a 
contribution would go into the documentation.


3) Some combination of the editorial board/peer reviewers/other 
volunteers would periodically go over existing documentation to 
remove/update stale content.





Thanks,

JD



Tim






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

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 03:37 PM, David G. Johnston wrote:
On Mon, Jul 16, 2018 at 3:19 PM, Joshua D. Drake >wrote:


On 07/16/2018 03:14 PM, David G. Johnston wrote:


What does the community think about a community run,
community organized, sub project for USER documentation? This
type of documentation would be things like, "10 steps to
configure replication", "Dumb simple Postgres backups",  "5
things to NEVER do with Postgres". I imagine we would sort it
by version (9.6/10.0 etc...) as well as break it down via
type (Administration, Tuning, Gotchas) etc...

What do we think?


​Politely tell them to buy some of the many well written books
that are available on these very topics...


Politely tell them to buy a license to MSSQl...

Kind of misses the whole point doesn't it?


​I'm going for practicality over idealism here.  That some of the best 
written material for learning how to be an application developer or 
DBA is presently really only available in the forms of books is a fact 
of our existence.  I frankly don't have a problem that there isn't a 
"free beer" resource available to complete with it.


Fair enough but what about those that cant afford it? I think us in the 
Western World tend to forget that by far the majority of users cant 
afford a latte from Starbucks let alone a 60.00 USD dead tree.




I'm all for continual improvement but color me doubtful that there is 
enough desire and discipline here to invent and then maintain a 
high-maintenance system.  So, yes, I am intentionally trying to avoid 
the trap that is problem that you want to solve by suggesting 
forgetting the revolution and instead coming at the problem from an 
entirely different angle and working to evolve the equilibrium that 
presently exists.


Hey, don't get me wrong. I get your point but I also know what I hear 
and I hear from *lots* of users because of PostgresConf and all the 
meetups. I am just trying to resolve a problem that exists for that 
community. Think of this (if we can figure out how to pull this off): 
User on StackOverflow says, "How do I do X", someone answers with a 
direct link to a recipe on PostgreSQL.Org that tells them exactly how to 
do X (with caveats of course). There isn't much more user friendly than 
that.


I am also not suggesting this wouldn't be work but it is also work a lot 
more people can do than people that can submit a patch to -hackers 
(exponentially so).


IMO,

JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 04:33 PM, Adrian Klaver wrote:


First, my assumption is that this project would be done within the 
.Org infrastructure and if the community thinks that we should just 
use DocBook that is certainly an option (although adhering to 
something like Docbook Simple may be best).


All the above is cool and everything, but is putting the cart before 
the horse. To me to make this work the following needs to happen:


1) Create an editorial board. This group of people would determine the 
answers to the questions above. They would also develop the framework 
for what needs covered. This would be based on what users want to see. 
Then a call for contributors could be made.


2) The other thing the editorial board would do is create list of 
qualified peer reviewers. These folks would then review the 
contributions and give feedback. On successfully passing a review a 
contribution would go into the documentation.


3) Some combination of the editorial board/peer reviewers/other 
volunteers would periodically go over existing documentation to 
remove/update stale content.


I did it! Want to help? I think if we got together 5-7 people and came 
up with a proposal we could submit to -www/-core and get some buy in.


JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 04:56 PM, Joshua D. Drake wrote:


3) Some combination of the editorial board/peer reviewers/other 
volunteers would periodically go over existing documentation to 
remove/update stale content.


I did it! 


s/did/dig


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: User documentation vs Official Docs

[Alvaro Herrera]

On 2018-Jul-16, Joshua D. Drake wrote:

> Think of this (if we can figure out how to pull this off): User on
> StackOverflow says, "How do I do X", someone answers with a direct
> link to a recipe on PostgreSQL.Org that tells them exactly how to do X
> (with caveats of course).  There isn't much more user friendly than
> that.

Sounds like wiki pages could solve need this pretty conveniently.  If
and when the content is mature enough and migrates to the tutorial main
documentation pages, the wiki pages can be replaced with redirects to
those.

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

Re: User documentation vs Official Docs

[Joshua D. Drake]

On 07/16/2018 05:08 PM, Alvaro Herrera wrote:


Sounds like wiki pages could solve need this pretty conveniently.  If
and when the content is mature enough and migrates to the tutorial main
documentation pages, the wiki pages can be replaced with redirects to
those.


Anyone who writes a lot is going to rebel against using a wiki. They are 
one of the worst to write in from a productivity perspective. I would 
rather write in Docbook, at least then I can template everything and we 
could have a standard xsl sheet etc...


JD





--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

PG backup check

[Ravi Krishna]

Not sure I am following this.  Did Google release this because PG backups
are not 100% reliable or the data corruption can occur due to hardware
failure.

http://www.eweek.com/cloud/google-releases-open-source-tool-that-checks-postgres-backup-integrity?utm_medium=email&utm_campaign=EWK_NL_EP_20180713_STR5L2&dni=450493554&rni=24844166

Re: PG backup check

[Vick Khera]

On Mon, Jul 16, 2018 at 8:35 PM, Ravi Krishna 
wrote:

> Not sure I am following this.  Did Google release this because PG backups
> are not 100% reliable or the data corruption can occur due to hardware
> failure.
>
> http://www.eweek.com/cloud/google-releases-open-source-
> tool-that-checks-postgres-backup-integrity?utm_medium=
> email&utm_campaign=EWK_NL_EP_20180713_STR5L2&dni=450493554&rni=24844166
>

The rule of thumb is you should verify that your backups can be restored
before you consider them reliable. You don't want to end up in a disaster
recovery situation and find that your backups have been corrupted in any
way shape or form. Many things can break your backups, especially if they
are automated and have many steps involved.

One way to do this is to actually restore the data on a different system
and compare. This tool appears to do the comparison for you directly to the
dump, increasing confidence that it is reliable.

Re: User documentation vs Official Docs

[Christopher Browne]

On Mon, 16 Jul 2018 at 20:14, Joshua D. Drake  wrote:
>
> On 07/16/2018 05:08 PM, Alvaro Herrera wrote:
> >
> > Sounds like wiki pages could solve need this pretty conveniently.  If
> > and when the content is mature enough and migrates to the tutorial main
> > documentation pages, the wiki pages can be replaced with redirects to
> > those.
>
> Anyone who writes a lot is going to rebel against using a wiki. They are
> one of the worst to write in from a productivity perspective. I would
> rather write in Docbook, at least then I can template everything and we
> could have a standard xsl sheet etc...

Indeed.

I think it would be a fine idea to have some proposals for improved examples
for the tutorial pages.  By putting them there, it becomes easy to reference
material either in reference sections or in the manual pages on SQL commands.
(As "for instances."  It would also be nice to have examples that make reference
to executable programs, whether pg_dump, pg_ctl, or ...)

I'd be willing to help write something of the sort; if I'm to shoot my mouth
off on what we ought to do, best to volunteer to actually make some of it
happen.

Having some small sample applications that do interesting things with
different Postgres facilities seems like a neat approach.

It would be interesting to have an example that makes decent use of
LISTEN/NOTIFY; people keep asking how to have PostgreSQL send
email, and writing a small example that handles it via queueing
requests into a table, where a daemon stops in to send queued
email once in a while.

Another idea would be an app that captures messages into tables
and enables full text search would be nice.

Doing some somewhat simplistic partitioning of a perhaps large
(but simplistic, so it fits into docs) data set would help motivate
use of partitioning facilities.

Applications need to be kept fairly tiny so that they represent
good examples without being of dominant size.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc

[Igor Polishchuk]

Hello Everybody.
We are trying to use logical decoding for detecting database changes.
However, when we create a replication slot, the data processing pauses if
there are still transactions running from before the slot creation. If I
understand correctly, the slot is waiting for creating a consistent
snapshot and is blocked by the long transactions.
In our application, we don't need it, as we only want to see if some tables
were modified. Is it possible to create a  logical replication slot with
NOEXPORT_SNAPSHOT option using jdbc?
This is a feature of  feature of the Streaming Replication Protocol
described in
https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html#id-1.8.14.8.5

It is just not clear how to pass this option. We are using Postgres 10.4,
jbc
changelog for 42.2.2


This is a groovy method for creating the replication slot:

@CompileStatic(TypeCheckingMode.SKIP)
private void createReplicationSlot( PGConnection replConnection) {

def slotName = getSlotName()
Sql sql = new Sql(dataSource)

def exists = sql.firstRow("select * from pg_replication_slots where
slot_name = '${slotName}'".toString())

if (exists)
log.info "replication slot ${slotName} detected and
${exists?.active ? 'active' : 'not active'}"
else
log.info "replication slot ${slotName} not detected"

if (!exists) {
replConnection.getReplicationAPI()
.createReplicationSlot()
.logical()
.withSlotName(slotName)
.withOutputPlugin("wal2json")
.make();

log.info "created replication slot ${slotName}"
}



-- 
Thanks
Igor Polishchuk

Re: User documentation vs Official Docs

[Jonathan S. Katz]

> On Jul 16, 2018, at 8:08 PM, Alvaro Herrera  wrote:
> 
> On 2018-Jul-16, Joshua D. Drake wrote:
> 
>> Think of this (if we can figure out how to pull this off): User on
>> StackOverflow says, "How do I do X", someone answers with a direct
>> link to a recipe on PostgreSQL.Org that tells them exactly how to do X
>> (with caveats of course).  There isn't much more user friendly than
>> that.
> 
> Sounds like wiki pages could solve need this pretty conveniently.  If
> and when the content is mature enough and migrates to the tutorial main
> documentation pages, the wiki pages can be replaced with redirects to
> those.

We’ve also tried to use the website to point to some already existing
resources to learn PostgreSQL:

https://www.postgresql.org/docs/online-resources/ 


Some of these includes tutorials that people have put together. If other
resources exist, I’m sure the -www team would be happy to review and
add them. We could also consider renaming the page to make it more
clear that it links to tutorials and the like.

That said, I’m sure contributions to improving the tutorial in the docs
would be well received. I figure it would just take a bit of work from people
who want to add to it. I see it being no different than getting a large patch
in, just some collaborative efforts from people who want to make it better
and some community back-and-forth.

Jonathan