Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

[Luca Ferrari]

On Wed, Aug 7, 2019 at 11:36 PM Benedict Holland
 wrote:
>
> Also, I have never seen a rollback at the start of a proc. A pure 
> hypothetical is that it is doing nothing or definitely not what you think it 
> is.

That's my point, thanks.

Luca

lead() with arrays - strange behaviour

[Thomas Kellerer]

Consider the following dummy table (this is a simplified example from a bigger 
query):

create table sample_data (id int, id_list int[]);
insert into sample_data (id, id_list)
values 
   (1, array[1,2,3]),
   (2, array[2,3,4]),
   (3, array[4,5,6]);

The following statement tries to find the overlapping values in id_list between 
the current row and the next row:

select id, 
   id_list,
   lead(id_list) over (order by id) as next_list,
   array(select unnest(id_list) intersect select unnest(lead(id_list) 
over (order by id))) as common_ids
from sample_data;   

The above returns: 

id | id_list | next_list | common_ids
---+-+---+---
 1 | {1,2,3} | {2,3,4}   | {}
 2 | {2,3,4} | {4,5,6}   | {}
 3 | {4,5,6} |   | {}

The empty array for "common_ids" is obviously incorrect. 

However, when the evaluation of the "next_list" is put into a derived table, 
then this works as expected:

select id, id_list, next_list, 
   array(select unnest(id_list) intersect select unnest(next_list)) as 
common_ids
from (
  select id, 
 id_list,
 lead(id_list) over (order by id) as next_list
  from sample_data
) t

returns:

id | id_list | next_list | common_ids
---+-+---+---
 1 | {1,2,3} | {2,3,4}   | {2,3} 
 2 | {2,3,4} | {4,5,6}   | {4}   
 3 | {4,5,6} |   | {}

This is with Postgres 11.4

Is this a bug or simply not supported?

It does work correctly with intarray's "intersect" operator:

select id, 
   id_list,
   id_list & lead(id_list) over (order by id) as next_list
from sample_data;   


However, the actual data uses a bigint, so intarray isn't an option.

Thomas



  

Re: Recomended front ends?

[Tim Clarke]

All excellent solutions, may I add Lucee to the list. We call it "the
best web development system no-one knows about".


Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420

On 07/08/2019 21:38, Rich Shepard wrote:
> On Wed, 7 Aug 2019, Igor Korot wrote:
>
>> On top of what already been said - make sure that the product you are
>> about to start working on will have its requirements clear and concise.
>
> This is a critical process that needs to be developed in depth. One
> criterion that will guide your choice of UI is whether the database
> will be
> accessed only on the LAN or also remotely. For the former, consider using
> Python3 + psycopg + SQLAlchemy. For the latter, consider a web-based
> application using Django.
>
> HTH,
>
> Rich
>
>


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Having the issue in while selecting the data and feltering in order by.

[nikhil raj]

Hi ALL,

I have recently migrated to windows postgres server to linux postgres
server.

while i run this query in windows PG server
SELECT *
FROM (VALUES ('a'), ('___b1'), ('_a1'),('a2'),('a3'),('a5'), ('a2')) t
(val) order by val

I am getting the out put in this sort order
[image: 10.250.200.184 - Remote Desktop Connection 2019-08-08 14.46.46.png]

Same when i run this in linux machine i am getting this out in  different
sort order on the same query.
[image: 10.250.200.184 - Remote Desktop Connection 2019-08-08 14.42.27.png]

Re: Having the issue in while selecting the data and feltering in order by.

[Luca Ferrari]

On Thu, Aug 8, 2019 at 11:20 AM nikhil raj  wrote:
> Same when i run this in linux machine i am getting this out in  different 
> sort order on the same query.

A collation problem?
What does this query do?
SELECT *
FROM (VALUES ('a'), ('___b1'), ('_a1'),('a2'),('a3'),('a5'), ('a2')) t
(val) order by val COLLATE "C";

Re: Having the issue in while selecting the data and feltering in order by.

[Francisco Olarte]

Nikhil ( or is it Raj? ):

On Thu, Aug 8, 2019 at 11:47 AM nikhil raj  wrote:
> Here are the detailes.
> Windows :
Zapped image showing windows locale "English-united-states.1252"
> linux :
Zapped image showing Linux locale "En-us.utf-8"
> both are in same version 11.3
> How can i get the sorting order by like windows how to change the lc_collate  
> Please can you help me over here  OR else is there an other way we get the 
> sort order like window through code

1st, it is a locale mismatch problem. I do not own windows, so I can
not test it, but IIRC win1252 is a latin-1 variant. I do not know how
their locales sort, but it seems they are doing something like the
C-utf-8 locale.

You could try to recreate the database using the same locale as in
windows. If you are able to do this ( dump, recreate, restore) it may
be the easier way. I do not know if there are windows-like locales for
linux, of how does that locale sort ( I mean, how does it process
things like á or ö or ñ, your example show only the most basic stuff
). You could probably use C locale, in ascii, win1252 or utf-8
depending on your data content to get good results ( locale comes from
the OS, but pg has the charsets ). With the restricted data in your
examples, just switching to C locale will suffice, but I do not know
if you have some more complex examples / sort orders ( someone
knowledgeable in windows may be able to help. It seems like windows
locales are not as sophisticated as Linux one, and IIRC this was true
20 years ago when I used them ).

Other option is to modify either your columns, to include a collate
option in the relevant ones, or the order-by clauses in the relevant
queries, which support the collate clause to. You may want to read
https://www.postgresql.org/docs/11/charset.html and investigate a bit
with some test dbs.

I would test something like creating a database with C locale, charset
win1252, this may be a good starting point. But test some more queries
in windows, i.e., things like this:

$ echo -e 'a\nA\nb\nB1' | LC_ALL=en_US.UTF-8 sort
a
A
b
B1
$ echo -e 'a\nA\nb\nB1' | LC_ALL=C.UTF-8 sort
A
B1
a
b

Because it may matter for you, and I doubt the interactions can be
properly tested without a proper dataset AND a windos pg server.

Francisco Olarte.

Re: Having the issue in while selecting the data and feltering in order by.

[Francisco Olarte]

Luca:

On Thu, Aug 8, 2019 at 12:33 PM Luca Ferrari  wrote:
> On Thu, Aug 8, 2019 at 11:20 AM nikhil raj  wrote:
> > Same when i run this in linux machine i am getting this out in  different 
> > sort order on the same query.
> A collation problem?
> What does this query do?
> SELECT *
> FROM (VALUES ('a'), ('___b1'), ('_a1'),('a2'),('a3'),('a5'), ('a2')) t
> (val) order by val COLLATE "C";

It will probacly do the expected thing for him, but I think another
problem may be what does this query:

SELECT * FROM (VALUES
('0'),('1'),('a'),('A'),('á'),('à'),('b'),('B'),('ñ'),('Ñ')) order by
1;

Does on the Windows server, what he is trying to replicate. Finding a
locale which does not ignore _ as the windows one does is easy, but
replicating all the doodahs may be difficult without knowing how the
windows locale sorts all the chars ( and I'm not getting at 1a vs a1,
or _ vs a vs A).

I suspect C locale may be ok though.

Francisco Olarte.

Re: lead() with arrays - strange behaviour

[David Rowley]

On Thu, 8 Aug 2019 at 21:06, Thomas Kellerer  wrote:
> The following statement tries to find the overlapping values in id_list 
> between the current row and the next row:
>
> select id,
>id_list,
>lead(id_list) over (order by id) as next_list,
>array(select unnest(id_list) intersect select unnest(lead(id_list) 
> over (order by id))) as common_ids
> from sample_data;
>
> The above returns:
>
> id | id_list | next_list | common_ids
> ---+-+---+---
>  1 | {1,2,3} | {2,3,4}   | {}
>  2 | {2,3,4} | {4,5,6}   | {}
>  3 | {4,5,6} |   | {}
>
> The empty array for "common_ids" is obviously incorrect.

I think you're confused with what the SELECT with the empty FROM
clause does here.  In your subquery "id_list" is just a parameter from
the outer query. LEAD(id_list) OVER (ORDER BY id) is never going to
return anything since those are both just effectively scalar values,
to which there is no "next" value.

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

Re: lead() with arrays - strange behaviour

[Thomas Kellerer]

David Rowley schrieb am 08.08.2019 um 13:03:
>> The following statement tries to find the overlapping values in id_list 
>> between the current row and the next row:
>>
>> select id,
>>id_list,
>>lead(id_list) over (order by id) as next_list,
>>array(select unnest(id_list) intersect select 
>> unnest(lead(id_list) over (order by id))) as common_ids
>> from sample_data;
>>
>> The above returns:
>>
>> id | id_list | next_list | common_ids
>> ---+-+---+---
>>  1 | {1,2,3} | {2,3,4}   | {}
>>  2 | {2,3,4} | {4,5,6}   | {}
>>  3 | {4,5,6} |   | {}
>>
>> The empty array for "common_ids" is obviously incorrect.
> 
> I think you're confused with what the SELECT with the empty FROM
> clause does here.  In your subquery "id_list" is just a parameter from
> the outer query. LEAD(id_list) OVER (ORDER BY id) is never going to
> return anything since those are both just effectively scalar values,
> to which there is no "next" value.

id_list is a column in the table and as you can see in the output 
lead(id_list) most definitely returns the array from the next row. 

and "select unnest(some_array)" works just fine as you can see 
when "next_list" is taken from the derived table. 

Thomas

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

[Daniel Verite]

Bryn Llewellyn wrote:

> B.t.w., I’m guessing that the “begin” SQL command that you see in the log
> that I mentioned is actually issued by (some) clients—at least psql and
> Python-on-psycopg2—as an explicit call from the client. In other words, it
> isn’t the server that generates this. Does anyone know for sure how this
> works?

Well, that's the point of Autocommit, and moreover it does nothing
else. Maybe you're still confused about this.

* Autocommit off = the client automatically adds a "BEGIN" when
it thinks a transaction must be started on behalf of the user.

* Autocommit on = the client does nothing.

The fact that "off" implies doing something and "on" implies not
interfering is counterintuitive, but that's how it is.  Autocommit is
for compatibility with other databases. If it was only for Postgres, I
guesss either it wouldn't exist in the first place or it should be
called "AutoStartTransactionBlock" or something like that, because
that's just what it really does.

Anyway, the server only know whether a BEGIN has been issued.
It never knows or cares whether it has been added implicitly or explicitly,
which is why it can be quite confusing to reason about server-side
differences in terms of Autocommit, as you do in some of your previous
messages.

It should be stressed that Autocommit is not a parameter of the
session between Postgres and the SQL client, but rather it's a
parameter of the session between the user and their SQL client.
So when you're hypothesizing that a plpgsql block in a procedure
would look at this parameter or change it temporarily (your
points #2 and #5 in your analysis of p2's execution), you should
see that it's impossible, because on the server-side, this parameter
just does not exist.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: lead() with arrays - strange behaviour

[Tom Lane]

Thomas Kellerer  writes:
> David Rowley schrieb am 08.08.2019 um 13:03:
>> I think you're confused with what the SELECT with the empty FROM
>> clause does here.  In your subquery "id_list" is just a parameter from
>> the outer query. LEAD(id_list) OVER (ORDER BY id) is never going to
>> return anything since those are both just effectively scalar values,
>> to which there is no "next" value.

> id_list is a column in the table and as you can see in the output 
> lead(id_list) most definitely returns the array from the next row. 
> and "select unnest(some_array)" works just fine as you can see 
> when "next_list" is taken from the derived table. 

David's point is that the two occurrences of lead() don't mean the
same thing.  A window function is directly tied to the SELECT that
it is in the select-list of, and its notion of next and previous
rows is concerned with the set of rows that that SELECT's FROM-clause
generates.  In this example, the inner SELECT has an empty FROM that
returns one row, so the lead() in that SELECT doesn't do anything
useful.

You could probably get where you want to go with something along
the lines of

select id, 
   id_list,
   next_list,
   array(select unnest(id_list) intersect select unnest(next_list)) as 
common_ids
from (
select id, 
   id_list,
   lead(id_list) over (order by id) as next_list
from sample_data
) ss; 

regards, tom lane

Re: lead() with arrays - strange behaviour

[Thomas Kellerer]

Tom Lane schrieb am 08.08.2019 um 16:10:
> David's point is that the two occurrences of lead() don't mean the
> same thing.  A window function is directly tied to the SELECT that
> it is in the select-list of, and its notion of next and previous
> rows is concerned with the set of rows that that SELECT's FROM-clause
> generates.  In this example, the inner SELECT has an empty FROM that
> returns one row, so the lead() in that SELECT doesn't do anything
> useful.

Ah! Now I get it ;) 

Thanks for clearing that up.

> You could probably get where you want to go with something along
> the lines of

Yes, that's what I did in the end (see my initial post)

Re: Recomended front ends?

[Tony Shelver]

On Wed, 7 Aug 2019 at 20:57, stan  wrote:

> I am in the process of defining an application for a very small company
> that uses Postgresql for the backend DB. This DB will eventually run on a
> hosted machine. As you imagine all of the employees have Windows machines
> for their normal work asks. Frankly I am not very strong on Windows. so I
> am wondering what the consensus is for creating forms and reports?
>
> My first though is Libre Office as that is cross platform, and i can test
> on my development Linux machine. However, i am getting a bit of push-back
> from the user as he is having issues with installing Libre Office on his
> computer. he says it does not play well with MS Office. Also we seem to be
> having some bugs with Libre Office Base in early development.
>
>
>
I went through this some months ago, and put out a similar query to this
list.
It very much depends what type of app / data you are trying to address.
If you are considering something like Base:  what your is users' level of
expertise, and your appetite for distributing / maintaining / installing
the app and it's infrastructure.

*Options*
I looked at several options, and ended up using Base as a quick and dirty
front end to build a content management system for an eStore.
I looked at a few other options, including Kexi.  Most I ruled out as they
didn't seem to have active support communities.
One interesting looking one I played with a little was Kexi,but you need to
build your database through Kexi (
https://kde.org/applications/office/org.kde.kexi).
One of my constraining factors was that I didn't want Kexi to build the
database for me, as I have a DB schema graphical design and generation tool
I prefer to use (pgmodeler).

*Bugs / features / learning curve*
I didn't find any bugs in Base that caused me problems, more like missing
features, or features that don't work the way I'd expected.
With a good Youtube series for a guide, I got a reasonable application up
and running in a weekend. I do have a basic development background dating
back to tools such as Oracle Forms, Mantis, and various other products of
that ilk. Also have a little experience with MS Access, and Cobol and Java
app generation.

*End-user 'friendliness'*
I would be a little wary of using Base for robust end-user data
interaction, unless the users are fairly savvy on how to work with
something like Base or Access applications.  Things like deleting,
inserting, and updating data on the forms are not all that intuitive at
first, especially if you have multi-table forms (I have 4 tables embedded
on my product form).

*Some limitations*

   - You will need to be a bit aware of Base macro programming.  Base does
   not have anything like VBA to work on.
   - You will be limited on the type of application you can deliver.  Basic
   data editing, probably suitable.  Something more complex like selectable
   calendars, WYSIWYG text editors and so on, not so much. For example, I
   would love to include a markdown text editor for product descriptions
   (which Jekyll or Python can convert to HTML).  Trivial in Vue.js or Quasar,
   not so much in Base, where I have to cut and paste the text to and from an
   editor.
   - Using Base to search / navigate through large datasets is not very
   easy, and you need to think very carefully how you will design around this.


I am using my application quite regularly to maintain the data, but intend
to replace it with a javascript (Vue and / or Quasar) front end at some
stage.
One comment on the recommendations to create a web application, if you do
not have current skills in Python or javascript and probably stuff like
CSS  / HTML the learning curve can be very time consuming.

*Security*:
Another issue is security.  If the database is accessed within your
corporate firewall, then it's not too bad.  If you need to access it from
across the internet using standard postgres drivers, then you may want to
have a careful think as to how you can lock down access to the database. I
allow postgres to communicate only to specific IP address ranges, and I
also have the firewall set up to restrict access to port 5432 to a few
specific IP ranges.

When it comes to pulling the product data out of the system, that's only
through a GraphQL interface to a GraphQL server.  That was pretty easy to
generate.  I pull the data out via a Python program, which in combination
with Jekyll static site generator generates our 3 websites, The python
program creates XML product file feeds to Jekyll, and in other cases
generates the basic HTML product page for Jekyll to complete site
generation.

*Most can ignore from here*
For a weekend's work and a day or two of later enhancements, the Base app
works pretty well.
To give you an idea of the complexity it consists of:

   1. Site: The domain name, the site base directory, discount and tax
   percentages
  - Block to add / delete / display product categories applicable to
  the site
  2. Category: 

Re: Recomended front ends?

[Stuart McGraw]

On 8/7/19 2:38 PM, Rich Shepard wrote:

On Wed, 7 Aug 2019, Igor Korot wrote:


On top of what already been said - make sure that the product you are
about to start working on will have its requirements clear and concise.


This is a critical process that needs to be developed in depth. One
criterion that will guide your choice of UI is whether the database will be
accessed only on the LAN or also remotely. For the former, consider using
Python3 + psycopg + SQLAlchemy. For the latter, consider a web-based
application using Django.

HTH,

Rich


I would be a little cautious about Django.  Last time I checked,
like many other web frameworks, it treats the database as just another
component, one that provides data persistence for it, and consequently
imposes its own constraints the schemas it will work with.  Specifically
IIRC it insists that tables have a single-column primary keys.  If the
client's existing database is already designed this way then that may
not be a problem but if it has composite PKs then another option may
be better.

Flask is another relatively easy to use framework, can be used with or
without Sqlalchemy but doesn't have the wealth of addons available with
Django and being simpler requires more work to build the end application.
There are of course many other framework options (Bottle, Web2Py, etc)

Although it's been a decade plus since I worked with Microsoft products
I had fairly good luck back then using Microsoft Access / VBA connected
to a Postgresql backend via ODBC.  Even back then MS's frontend development
tools were way more advanced and easy to use than anything available for
free in the Linux world.  The downside was having to program in VBA but
things may be much better these days with .NET et.al.

Re: Recomended front ends?

[Adrian Klaver]

On 8/8/19 9:55 AM, Stuart McGraw wrote:

On 8/7/19 2:38 PM, Rich Shepard wrote:

On Wed, 7 Aug 2019, Igor Korot wrote:


On top of what already been said - make sure that the product you are
about to start working on will have its requirements clear and concise.


This is a critical process that needs to be developed in depth. One
criterion that will guide your choice of UI is whether the database 
will be

accessed only on the LAN or also remotely. For the former, consider using
Python3 + psycopg + SQLAlchemy. For the latter, consider a web-based
application using Django.

HTH,

Rich


I would be a little cautious about Django.  Last time I checked,
like many other web frameworks, it treats the database as just another
component, one that provides data persistence for it, and consequently
imposes its own constraints the schemas it will work with.  Specifically
IIRC it insists that tables have a single-column primary keys.  If the
client's existing database is already designed this way then that may
not be a problem but if it has composite PKs then another option may
be better.


Agreed the single-column PK is an annoyance, though it can be mitigated 
with unique_together.


The real annoyance is:
https://docs.djangoproject.com/en/1.11/ref/models/fields/#primary-key
"The primary key field is read-only. If you change the value of the 
primary key on an existing object and then save it, a new object will be 
created alongside the old one."


That being said I use Django with managed set to False on models and 
Sqitch doing the schema changes with no problems. Also Postgres is the 
reference database for Django and has a contrib module with Postgres 
specific features:

https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/




Flask is another relatively easy to use framework, can be used with or
without Sqlalchemy but doesn't have the wealth of addons available with
Django and being simpler requires more work to build the end application.
There are of course many other framework options (Bottle, Web2Py, etc)

Although it's been a decade plus since I worked with Microsoft products
I had fairly good luck back then using Microsoft Access / VBA connected
to a Postgresql backend via ODBC.  Even back then MS's frontend development
tools were way more advanced and easy to use than anything available for
free in the Linux world.  The downside was having to program in VBA but
things may be much better these days with .NET et.al.





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

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Stuart McGraw wrote:


I would be a little cautious about Django.



Specifically IIRC it insists that tables have a single-column primary
keys.


Stuart,

I looked seriously at Django and did not encounter that limitation. However,
I did learn that I'm not a web application developer nor do I want to be.
The applications I develop, primarily for my own business needs. use
SQLAlchemy and that allows multi-column primary keys. That's a necessity for
many-to-many tables (or SA classes).

I suspect that Django also allows multi-column primary keys but the syntax
might not be obvious.

Regards,

Rich

Re: Recomended front ends?

[Adrian Klaver]

On 8/8/19 10:34 AM, Rich Shepard wrote:

On Thu, 8 Aug 2019, Stuart McGraw wrote:


I would be a little cautious about Django.



Specifically IIRC it insists that tables have a single-column primary
keys.


Stuart,

I looked seriously at Django and did not encounter that limitation. 
However,

I did learn that I'm not a web application developer nor do I want to be.
The applications I develop, primarily for my own business needs. use
SQLAlchemy and that allows multi-column primary keys. That's a necessity 
for

many-to-many tables (or SA classes).

I suspect that Django also allows multi-column primary keys but the syntax
might not be obvious.


Unfortunately it does not:
https://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys

Given that the issue:

https://code.djangoproject.com/ticket/373

is 14 years old does not inspire confidence that it will change anytime 
soon.




Regards,

Rich





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

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Adrian Klaver wrote:


Unfortunately it does not:
https://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys

Given that the issue:
https://code.djangoproject.com/ticket/373
is 14 years old does not inspire confidence that it will change anytime soon.


Adrian,

That's really interesting. I don't see how a framework cannot implement
multi-column PKs.

Many databases I have include tables for samples (geochemical, biological,
physical) where the PK for each row is location, date, parameter. Good thing
I don't like browser user interfaces, eh? :-)

Thanks for the information,

Rich

Re: Recomended front ends?

[Tim Clarke]

On 08/08/2019 17:55, Stuart McGraw wrote:
>
> I would be a little cautious about Django.  Last time I checked,
> like many other web frameworks, it treats the database as just another
> component, one that provides data persistence for it, and consequently
> imposes its own constraints the schemas it will work with. Specifically
> IIRC it insists that tables have a single-column primary keys.  If the
> client's existing database is already designed this way then that may
> not be a problem but if it has composite PKs then another option may
> be better.
>
> Flask is another relatively easy to use framework, can be used with or
> without Sqlalchemy but doesn't have the wealth of addons available with
> Django and being simpler requires more work to build the end application.
> There are of course many other framework options (Bottle, Web2Py, etc)
>
> Although it's been a decade plus since I worked with Microsoft products
> I had fairly good luck back then using Microsoft Access / VBA connected
> to a Postgresql backend via ODBC.  Even back then MS's frontend
> development
> tools were way more advanced and easy to use than anything available for
> free in the Linux world.  The downside was having to program in VBA but
> things may be much better these days with .NET et.al.


We tried Django without any pleasant results.

I'd also caution using MS Access, we're desperate to get away from it.
Sharing code has challenges and it is horribly aggressive with caching
unless you use un-bound forms and write all the CRUD interface code
yourself.

Tim Clarke



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

[Bryn Llewellyn]

Please try the following.Stop PostgreSQL with this:pg_ctl -D /usr/local/var/postgres stopEdit the file "/usr/local/var/postgres/postgresql.conf” and add these lines at the end:log_statement = 'all'log_directory = 'log'logging_collector = onThen start  PostgreSQL with this:pg_ctl -D /usr/local/var/postgres startThen, in one terminal window, find the latest log file with this:ls -l /usr/local/var/postgres/log/postgresql-*.logThen, in a second terminal window, start psql and do these tests. By all means, add your own.-- Test 1.\set AUTOCOMMIT ONinsert into t(n) values(42);I see _only_ the “insert” in the log—in other words, no “commit”. (And no “BEGIN” for that matter.) If it were the case the psql sends the commit after every statement when AUTOCOMMIT is ON, then I’d see this in the log. Yet the effect of the “insert” has indeed been committed. You can see this by issuing “rollback” (you get the "there is no transaction in progress” warning). And a subsequent “select” confirms that the “insert” was committed.My interpretation is that AUTOCOMMIT is a server-side phenomenon (but, as we shall see, the client does know that this mode has been set).-- Test 2.\set AUTOCOMMIT OFFinsert into t(n) values(42);rollback;I see this in the log:…LOG:  statement: BEGIN…LOG:  statement: insert into t(n) values(42);…LOG:  statement: rollback;(I also see this when I do the test using Python on psycopg2 after doing “my_session.set_session(autocommit=False)”. The “rollback” doesn’t draw a warning. And, indeed, the effect of my “insert” has been undone, as has been shown by a subsequent “select”.)-- Test 3.\set AUTOCOMMIT OFFstart transaction;insert into t(n) values(42);rollback;I see this in the log:…LOG:  statement: start transaction;…LOG:  statement: insert into t(n) values(42);…LOG:  statement: rollback;Same outcome as with Test 2. Test 2 and Test 3 are consistent with the hypothesis that it’s the client that issues the “BEGIN” before your first  SQL command after setting AUTOCOMMIT to OFF. But it doesn’t _prove_ the hypothesis. However, experts on this list have said that this is how it works. And this is consistent with the fact that, when AUTOCOMMIT is ON, I don’t see this in the log:BEGIN;insert into t(n) values(42);COMMIT;If AUTOCOMMIT were entirely a client-side phenomenon, then you _would_ have to see this to get the defined semantics.In summary, my hypothesis is that AUTOCOMMIT is a server side phenomenon. But, because to set it with a call from the client, the client does know what mode you’re in and adds its own BEGIN before the first SQL command that follows setting AUTOCOMMIT to ON.Test 4 is more elaborate. Please run the attached setup_for_test_4.sql, and then do this by hand. But do read the definition of "p2()"  carefully first. Then do this:-- Test 4.\set AUTOCOMMIT ONdo $$ begin raise notice 'txid at top level before "p2()" . %', txid_current(); end $$;call p2();do $$ begin raise notice 'txid at top level after "p1()" .. %', txid_current(); end $$;select n from t order by n;This is the output:NOTICE:  txid at top level before "p2()" . 9478NOTICE:  txid in "p2()" at start . 9479NOTICE:  txid in "p2()" after "set txn"... 9480, level = repeatable readNOTICE:  txid in "p2()" after "set txn"... 9481, level = serializableNOTICE:  txid at top level after "p1()" .. 9482 n   42And this is what the log shows:…LOG:  statement: do $$ begin raise notice 'txid at top level before "p2()" . %', txid_current(); end $$;…LOG:  statement: call p2();…LOG:  statement: do $$ begin raise notice 'txid at top level after "p1()" .. %', txid_current(); end $$;…LOG:  statement: select n from t order by n;Notice that there’s no visible “BEGIN” between my first “do" block and the “call”. And (again) no visible “COMMIT” after the “call" finishes. But the txid values show that “call” did indeed start a new txt, the “rollback” in the proc ended it so that "set transaction isolation level” in the proc can (implicitly) start a new txn. And so on. All this is consistent with the hypothesis that there is no such thing as a “nested transaction”. Rather, there’s just a series of non-overlapping ordinary txns. The BLOG POST that I quoted with my first post on this topic explains that this is intended.This is consistent with the hypothesis that AUTOCOMMIT is indeed a server-side phenomenon and that, during the execution of a procedure, its usual effect is suspended—and is resumed when the procedure execution ends.I’ve said several times that my goal is to set the isolation level from inside a proc so that the proc can own the entire logic for concurrent txns that might violate a multi-row data-rule.And I’ve said several times that I discovered that starting my proc with “rollback” allows this—and that I've

setup_for_test_4.sql
Description: Binary data
 found no other way to meet my goal. I don’t understand, therefore, why some people (but not you, Daniel!) who’ve responded to my questions say that star

Re: Recomended front ends?

[Andrew Kerber]

On Thu, Aug 8, 2019 at 1:30 PM Tim Clarke  wrote:

> On 08/08/2019 17:55, Stuart McGraw wrote:
> >
> > I would be a little cautious about Django.  Last time I checked,
> > like many other web frameworks, it treats the database as just another
> > component, one that provides data persistence for it, and consequently
> > imposes its own constraints the schemas it will work with. Specifically
> > IIRC it insists that tables have a single-column primary keys.  If the
> > client's existing database is already designed this way then that may
> > not be a problem but if it has composite PKs then another option may
> > be better.
> >
> > Flask is another relatively easy to use framework, can be used with or
> > without Sqlalchemy but doesn't have the wealth of addons available with
> > Django and being simpler requires more work to build the end application.
> > There are of course many other framework options (Bottle, Web2Py, etc)
> >
> > Although it's been a decade plus since I worked with Microsoft products
> > I had fairly good luck back then using Microsoft Access / VBA connected
> > to a Postgresql backend via ODBC.  Even back then MS's frontend
> > development
> > tools were way more advanced and easy to use than anything available for
> > free in the Linux world.  The downside was having to program in VBA but
> > things may be much better these days with .NET et.al.
>
>
> We tried Django without any pleasant results.
>
> I'd also caution using MS Access, we're desperate to get away from it.
> Sharing code has challenges and it is horribly aggressive with caching
> unless you use un-bound forms and write all the CRUD interface code
> yourself.
>
> Tim Clarke
>
>
>
> Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 |
> Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1
> 647 503 2848
> Web: https://www.manifest.co.uk/
>
>
>
> Minerva Analytics Ltd - A Solactive Company
> 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United
> Kingdom
>
> 
>
> Copyright: This e-mail may contain confidential or legally privileged
> information. If you are not the named addressee you must not use or
> disclose such information, instead please report it to ad...@minerva.info
> 
> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd:
> Registered in England Number 11260966 & The Manifest Voting Agency Ltd:
> Registered in England Number 2920820 Registered Office at above address.
> Please Click Here https://www.manifest.co.uk/legal/ for further
> information.
>

You might look at Benthic SQLall.
-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: Recomended front ends?

[Rich Shepard]

On Thu, 8 Aug 2019, Tim Clarke wrote:


We tried Django without any pleasant results.


Tim,

That's unexpected and too bad.


I'd also caution using MS Access, we're desperate to get away from it.
Sharing code has challenges and it is horribly aggressive with caching
unless you use un-bound forms and write all the CRUD interface code
yourself.


Slightly off-topic, but I've not seen anything good about Access. My
understanding is it's a flat-file database intended as a user front end to
Microsoft's relational database product. My experiences with those who use
it have been painful.

Just yesterday I downloaded a very large database of fisheries data from a
federal agency and have started translating it to postgres using the
mdbtools. There's no schema provided, only 32 pages of table columns and
types without descriptions of the column names. No primary keys, no foreign
keys, and only 66 tables were found in the .mdb file while all table names
starting with s through z were not available. There are also many tables
that hold redundant data which should not exist as the contents are easily
generated by SQL queries. It will take me a while to make it a working
relational database.

Rich

Re: Recomended front ends?

[Basques, Bob (CI-StPaul)]

All,

No Web driven, but . . . . we’ve had some success with using LibreOffice(calc) 
as a frontend.  Fairly easy to build forms, etc.  Only limited experience so 
far, but was able to build domain lists from SQL calls, for form pulldown 
lists, etc. 

bobb


> On Aug 8, 2019, at 2:10 PM, Rich Shepard  wrote:
> 
> Think Before You Click: This email originated outside our organization.
> 
> 
> On Thu, 8 Aug 2019, Tim Clarke wrote:
> 
>> We tried Django without any pleasant results.
> 
> Tim,
> 
> That's unexpected and too bad.
> 
>> I'd also caution using MS Access, we're desperate to get away from it.
>> Sharing code has challenges and it is horribly aggressive with caching
>> unless you use un-bound forms and write all the CRUD interface code
>> yourself.
> 
> Slightly off-topic, but I've not seen anything good about Access. My
> understanding is it's a flat-file database intended as a user front end to
> Microsoft's relational database product. My experiences with those who use
> it have been painful.
> 
> Just yesterday I downloaded a very large database of fisheries data from a
> federal agency and have started translating it to postgres using the
> mdbtools. There's no schema provided, only 32 pages of table columns and
> types without descriptions of the column names. No primary keys, no foreign
> keys, and only 66 tables were found in the .mdb file while all table names
> starting with s through z were not available. There are also many tables
> that hold redundant data which should not exist as the contents are easily
> generated by SQL queries. It will take me a while to make it a working
> relational database.
> 
> Rich
> 
> 
> 

Re: Recomended front ends?

[DiasCosta]

Hi,
After many years of using Oracle Forms and Oracle Reports with Oracle 
DB, I have been using Lazarus and "Code Typhon"

for many years now.
Both work with Free Pascal Compiler and both are open source and free.
Both have a very good IDE, the code produced is Pascal (very easily 
readable), and they connect directly to many DBMS including PostgreSQL, 
Oracle,  MSSQL, SQLITE, etc..


You can find information here: https://en.wikipedia.org/wiki/Lazarus_(IDE)
and here:  https://www.pilotlogic.com/sitejoom/

Also here:   https://en.wikipedia.org/wiki/Lazarus_(IDE)
you can find some interesting information.

Also here:   https://www.getlazarus.org/learn/tutorials/intro/
"If you are haven't used Lazarus  recently 
then this tutorial is for you. In it we give users a broad overview of 
Lazarus
and some of its key features. We look at the type of applications you 
can create with Lazarus, and show you the core

concepts to desktop application development it makes so very easy.
Highlights include the two way design process, events handlers, testing 
and debugging, and deployment.
A brief gallery of applications 
 I've personally created 
with Lazarus is included at the end, and I honestly believe it's
the best tool in the world for developing platform agnostic desktop 
applications. Like the video says, give Lazarus a try 
."



Dias Costa









On 08-08-2019 20:26, Basques, Bob (CI-StPaul) wrote:

All,

No Web driven, but . . . . we’ve had some success with using LibreOffice(calc) 
as a frontend.  Fairly easy to build forms, etc.  Only limited experience so 
far, but was able to build domain lists from SQL calls, for form pulldown 
lists, etc.

bobb



On Aug 8, 2019, at 2:10 PM, Rich Shepard  wrote:

Think Before You Click: This email originated outside our organization.


On Thu, 8 Aug 2019, Tim Clarke wrote:


We tried Django without any pleasant results.

Tim,

That's unexpected and too bad.


I'd also caution using MS Access, we're desperate to get away from it.
Sharing code has challenges and it is horribly aggressive with caching
unless you use un-bound forms and write all the CRUD interface code
yourself.

Slightly off-topic, but I've not seen anything good about Access. My
understanding is it's a flat-file database intended as a user front end to
Microsoft's relational database product. My experiences with those who use
it have been painful.

Just yesterday I downloaded a very large database of fisheries data from a
federal agency and have started translating it to postgres using the
mdbtools. There's no schema provided, only 32 pages of table columns and
types without descriptions of the column names. No primary keys, no foreign
keys, and only 66 tables were found in the .mdb file while all table names
starting with s through z were not available. There are also many tables
that hold redundant data which should not exist as the contents are easily
generated by SQL queries. It will take me a while to make it a working
relational database.

Rich






--
J. M. Dias Costa
Telef. 214026948 Telem. 939307421

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não 
respeitem o
malfadado acordo ortográfico.

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

[Adrian Klaver]

On 8/8/19 11:35 AM, Bryn Llewellyn wrote:

Please try the following.

Stop PostgreSQL with this:

pg_ctl -D /usr/local/var/postgres stop

Edit the file "/usr/local/var/postgres/postgresql.conf” and add these 
lines at the end:


log_statement = 'all'
log_directory = 'log'
logging_collector = on

Then start  PostgreSQL with this:

pg_ctl -D /usr/local/var/postgres start

Then, in one terminal window, find the latest log file with this:

ls -l /usr/local/var/postgres/log/postgresql-*.log

Then, in a second terminal window, start psql and do these tests. By all 
means, add your own.


-- Test 1.
\set AUTOCOMMIT ON
insert into t(n) values(42);

I see _only_ the “insert” in the log—in other words, no “commit”. (And 
no “BEGIN” for that matter.) If it were the case the psql sends the 
commit after every statement when AUTOCOMMIT is ON, then I’d see this in 
the log. Yet the effect of the “insert” has indeed been committed. You 
can see this by issuing “rollback” (you get the "there is no transaction 
in progress” warning). And a subsequent “select” confirms that the 
“insert” was committed.


Yeah if you do:
log_min_messages = debug5
you see it:

postgres-2019-08-08 13:54:26.842 PDT-0DEBUG:  StartTransaction(1) name: 
unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0


postgres-2019-08-08 13:54:26.842 PDT-0LOG:  statement: insert into t 
values (1);


postgres-2019-08-08 13:54:26.843 PDT-38496DEBUG:  CommitTransaction(1) 
name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 
38496/1/0 (used)




My interpretation is that AUTOCOMMIT is a server-side phenomenon (but, 
as we shall see, the client does know that this mode has been set).


Yes it is:
https://www.postgresql.org/docs/11/sql-begin.html:
"BEGIN initiates a transaction block, that is, all statements after a 
BEGIN command will be executed in a single transaction until an explicit 
COMMIT or ROLLBACK is given. By default (without BEGIN), PostgreSQL 
executes transactions in “autocommit” mode, that is, each statement is 
executed in its own transaction and a commit is implicitly performed at 
the end of the statement (if execution was successful, otherwise a 
rollback is done)."



Seems the 7.2 --> 7.3 --> 7.4 server setting for 
autocommit(change/change back) planted the idea in my head that it was not:


https://www.postgresql.org/docs/7.4/release-7-4.html
"The server-side autocommit setting was removed and reimplemented in 
client applications and languages. Server-side autocommit was causing 
too many problems with languages and applications that wanted to control 
their own autocommit behavior, so autocommit was removed from the server 
and added to individual client APIs as appropriate."






-- Test 2.
\set AUTOCOMMIT OFF
insert into t(n) values(42);
rollback;

I see this in the log:

…LOG:  statement: BEGIN
…LOG:  statement: insert into t(n) values(42);
…LOG:  statement: rollback;

(I also see this when I do the test using Python on psycopg2 after doing 
“my_session.set_session(autocommit=False)”. The “rollback” doesn’t draw 
a warning. And, indeed, the effect of my “insert” has been undone, as 
has been shown by a subsequent “select”.)


-- Test 3.
\set AUTOCOMMIT OFF
start transaction;
insert into t(n) values(42);
rollback;

I see this in the log:

…LOG:  statement: start transaction;
…LOG:  statement: insert into t(n) values(42);
…LOG:  statement: rollback;

Same outcome as with Test 2. Test 2 and Test 3 are consistent with the 
hypothesis that it’s the client that issues the “BEGIN” before your 
first  SQL command after setting AUTOCOMMIT to OFF. But it doesn’t 
_prove_ the hypothesis. However, experts on this list have said that 
this is how it works. And this is consistent with the fact that, when 
AUTOCOMMIT is ON, I don’t see this in the log:


As shown before that is done in /src/bin/psql/common.c:
if (transaction_status == PQTRANS_IDLE &&
!pset.autocommit &&
!command_no_begin(query))
{
results = PQexec(pset.db, "BEGIN");
Same thing in psycopg2 code, see my link in previous post.



BEGIN;
insert into t(n) values(42);
COMMIT;

If AUTOCOMMIT were entirely a client-side phenomenon, then you _would_ 
have to see this to get the defined semantics.


In summary, my hypothesis is that AUTOCOMMIT is a server side 
phenomenon. But, because to set it with a call from the client, the 
client does know what mode you’re in and adds its own BEGIN before the 
first SQL command that follows setting AUTOCOMMIT to ON.


You mean OFF. As you have shown autocommit is the default and you either 
turn it off by AUTOCOMMIT OFF in psql or equivalent in other clients or 
by starting an explicit transaction with BEGIN;




Test 4 is more elaborate. Please run the attached setup_for_test_4.sql, 
and then do this by hand. But do read the definition of "p2()" 
  carefully first. Then do this:


-- Test 4.
\set AUTOCOMMIT ON
do $$ begin raise notice 'txid at top leve

PostgreSQL 8.4 Tablespace Inconsistency

[Harold Falkmeyer]

Hello,

We operate a PostgreSQL 8.4.22 database on RHEL 6.10.  Over the years,
we've attempted to intelligently distribute tables and indexes across
multiple tablespaces.  After introducing a new tablespace, we attempted to
inventory which tables and indexes were on which tablespaces only to find
that pg_class indicated something inconsistent with our change records, as
well as with what we found on our filesystems.

As an example of the seeming inconsistency, pg_class indicated that certain
tables and indexes were on reltablespace 0, which, as we understood, refers
to pg_default, which, coincidentally is listed with pg_tablespace with oid
1663.  That same pg_class object is clearly present on the filesystem for a
non-default tablespace.

As another example, pg_class lists no tables or indexes with one of our
non-default tablespace; though, that filesystem has a tablespace-like path
with many open files (lsof) listed whenever the database is running.

Our tablespaces were setup with CREATE TABLESPACE, ALTER TABLE ... SET
TABLESPACE, and ALTER INDEX ... SET TABLESPACE.

Thank you in advance for any insights and/or assistance you can provide,

Harold Falkmeyer

Understanding PostgreSQL installer debug log

[Ramesh Maddi]

Hi Team,



Can you please help me to understand the PostgreSQL installer’s debug log.
I am installing PostgreSQL in CentOS 7.6 system with PostgreSQL binary (
postgresql-9.6.6-1-linux-x64.run). I am using the below command to install
it in unattended mode.



./postgresql-9.6.6-1-linux-x64.run --installer-language en --serviceaccount
postgres --servicename postgresqld  --datadir "/home/postgres/" --prefix
"/home/postgres" --superpassword 1234 --serverport 5432 --debuglevel 4
--debugtrace ./postgresql-debug.log --mode unattended





A debug log file postgresql-debug.log is created in the same directory. But
it is in some encoded format which I am not able to decode. Here is the
first few lines of the debug log.





MwSUdmn65MlwA81MDBVmg34ZjlXDReCYnIxjAwgZ15jHp8UXS0OQ2L/a8iph

kR7moeATHKE7UkwbqeM1bluAAET0rr2AXwDdQdowNVgI5BYvwz7YBMUd5nsn

AgldXNczMw/dSiFsj334+Bb/iOhXuaQo/S0FzyzqFPEqaBHVjPrJv70vdhTD

dmHn7vKY/Zo2xZ/eyouLxobkFYdRw2zqX+HAkRpygUNPqHzvy0AJg6Kln8uv

GhwphVedsu1buJc7gb2T+1HWqsCXB8jm9LI7GmDvA62sKmgmjxRDMl+UI6UE

rn7gbhrc59oI4Wjem2aJK8ufTHuYM+xfXNFc5yY9CtoI4Wjem2aJzo+oIONC

gYoZFhKeT1iysdoI4Wjem2aJ8B3Ju++KR9us+PNdpxdCk9oI4Wjem2aJL+bx

eZCCp83ng4pRhi+GoQyXnF++cnIKHlx0bVlSX7X7AV8O24BoFwyXnF++cnIK

rJUoxqtsZe6URWF1lQj7xgyXnF++cnIKkX2dNd0GKfLyeRPuEuaM1N6/6xVl





We are trying to debug an issue with installation at client’s location and
before that we are trying to run debug mode as trial. But looks like this
cannot be understood. Before going back to client, we would like to
understand how to decode the debug log so that we could trace out the
issues at client side.



Thanks in advance and appreciate your response. Please consider this as a
high priority for us.



Regards,

Ramesh Maddi – DevOPs Lead.



Mobigesture