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

[Luca Ferrari]

On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn  wrote:
> 1. my call p2() starts a txn.

In my opinion this is the point: a procedure must be "owner" of the
transaction to issue transaction control statements. You can watch
different behaviors placing here and there txid_current() before and
within p2 and re-run with autocommit off an on.
On autocmmmit = on the call opens a transaction and the procedure is
the only thing within the transaction, so it has full control. With
autocommit off you gain an implicit begin and the procedure is
possibly not the only thing you have within the transaction.
You can test it with:

testdb=# begin;
BEGIN
testdb=# call p2();
INFO:  TXID 134994
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function p2() line 9 at ROLLBACK
testdb=# rollback;
ROLLBACK


Having said that, I believe there is no reason ever to begin a
procedure with a rollback.

Here's a small example that performs a few commits and rollback


Luca

P.S:
I don't believe that asking on a public mailing list for a company to
answer is the right thing to do, you can always ask themselves on your
own.

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

[Kyotaro Horiguchi]

Hello, Bryn.

At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn  wrote in 

> Here’s how I’ve tried to describe what I see for p2() with AUTOCOMMIT ON for 
> myself:
> 
> 1. my call p2() starts a txn.
> 
> 2. However, during the execution of the proc, the usual autocommit behavior 
> is programmatically turned off by explicit PostgreSQL code.
> 
> 3. Other explicit PostgreSQL code makes “start transaction” inside a proc 
> simply cause a runtime error under all circumstances. However, txns can be 
> ended by “commit” or “rollback”. And new ones can be started—but only 
> implicitly by executing a SQL statement that, as a top level SQL, would start 
> a txn.

In-procedure transaction control premises that no transaction is
active before calling the procedure.

https://www.postgresql.org/docs/11/sql-call.html

> If CALL is executed in a transaction block, then the called
> procedure cannot execute transaction control
> statements. Transaction control statements are only allowed if
> CALL is executed in its own transaction.

With AUTOCOMMIT=off, implicit BEGIN is invoked just before
CALLing p2() if no transaction is active. Thus p2() is always
called having a transaction active, which inhibits in-procedure
transaction control.

I'm not sure why you want to turn AUTOCOMNIT off, but even with
AUTOCOMMIT on, explict BEGIN prevents per-command COMMIT as you
perhaps know.

https://www.postgresql.org/docs/11/app-psql.html
> When on (the default), each SQL command is automatically
> committed upon successful completion. To postpone commit in
> this mode, you must enter a BEGIN or START TRANSACTION SQL
> command.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Guidance needed on an alternative take on common prefix SQL

[Laura Smith]

On Wednesday, August 7, 2019 2:01 AM, Andy Colson  wrote:

> On 8/6/19 6:25 PM, Laura Smith wrote:
>
> > Hi,
> > I've seen various Postgres examples here and elsewhere that deal with the 
> > old common-prefix problem (i.e. "given 1234 show me the longest match").
> > I'm in need of a bit of guidance on how best to implement an alternative 
> > take. Frankly I don't quite know where to start but I'm guessing it will 
> > probably involve CTEs, which is an area I'm very weak on.
> > So, without further ado, here's the scenario:
> > Given an SQL filtering query output that includes the following column:
> > 87973891
> > 87973970
> > 87973971
> > 87973972
> > 87973973
> > 87973975
> > 87973976
> > 87973977
> > 87973978
> > 87973979
> > 8797400
> > The final output should be further filtered down to:
> > 87973891
> > 8797397
> > 8797400
> > i.e. if $last_digit is present 0–9 inclusive, recursively filter until the 
> > remaining string is all the same (i.e. in this case, when $last_digit[0-9] 
> > is removed, 8797397 is the same).
> > So, coming back to the example above:
> > 8797397[0-9] is present
> > so the "nearest common" I would be looking for is 8797397 because once 
> > [0-9] is removed, the 7 is the same on the preceeding digit.
> > The other two rows ( 87973891 and 8797400) are left untouched because 
> > $last_digit is not present in [0-9].
> > Hope this question makes sense !
> > Laura
>
> Hows this?
>
> select distinct
> case cc
> when 1 then num
> else left(num,-1)
> end
> from (
> select
> num,
> (select count(*) as cc from numbers n2 where left(n2.num, -1) = 
> left(numbers.num, -1))
> from numbers
> ) as tmpx ;
>
> -Andy



Hi Andy,

That looks supremely clever !

I have just done a quick test and looks like it works as intended. Will do some 
more thorough testing with a larger dataset in due course.

Thank you very much indeed

Laura

pg_wal fills up on big update query

[Daniel Fink (PDF)]

Hi all,



I have a migration where I

· Add a new nullable column to a table

· update almost every row in this big table (8 million rows) from
another table where I set this new column



I have also a replication setup running.

The database has a size of around 20GB.

While the migration is running, it more than doubles is size and fills up
all space.

Then the migration fails and is rolled back.



What is the best way of keeping this from happening?

My current idea is to lock both tables completely from access (the queried
and the updated one) so that postgresql does not have to ensure isolation
for concurrent queries by keeping a copy of each row.

Is my thinking here correct?



Thanks in advance and Best Regards,

Daniel

-- 
This message may contain confidential and privileged information. If it has 
been sent to you in error, please reply to advise the sender of the error 
and then immediately permanently delete it and all attachments to it from 
your systems. If you are not the intended recipient, do not read, copy, 
disclose or otherwise use this message or any attachments to it. The sender 
disclaims any liability for such unauthorized use.  PLEASE NOTE that all 
incoming e-mails sent to PDF e-mail accounts will be archived and may be 
scanned by us and/or by external service providers to detect and prevent 
threats to our systems, investigate illegal or inappropriate behavior, 
and/or eliminate unsolicited promotional e-mails (“spam”).  If you have any 
concerns about this process, please contact us at legal.departm...@pdf.com 
.

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

[Adrian Klaver]

On 8/7/19 12:15 AM, Luca Ferrari wrote:

On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn  wrote:

1. my call p2() starts a txn.



Luca

P.S:
I don't believe that asking on a public mailing list for a company to
answer is the right thing to do, you can always ask themselves on your
own.


Sort of moot as PROCEDURE is a Postgres feature not a company feature.


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

Re: pg_wal fills up on big update query

[Rob Sargent]

> On Aug 7, 2019, at 7:34 AM, Daniel Fink (PDF)  wrote:
> 
> Hi all,
>  
> I have a migration where I
> · Add a new nullable column to a table
> · update almost every row in this big table (8 million rows) from 
> another table where I set this new column
>  
> I have also a replication setup running.
> The database has a size of around 20GB.
> While the migration is running, it more than doubles is size and fills up all 
> space.
> Then the migration fails and is rolled back.
>  
> What is the best way of keeping this from happening?
> My current idea is to lock both tables completely from access (the queried 
> and the updated one) so that postgresql does not have to ensure isolation for 
> concurrent queries by keeping a copy of each row.
> Is my thinking here correct?
>  
> Thanks in advance and Best Regards,
> 
Do the update in small chunks

Re: pg_wal fills up on big update query

[Luca Ferrari]

On Wed, Aug 7, 2019 at 3:34 PM Daniel Fink (PDF)  wrote:
> My current idea is to lock both tables completely from access (the queried 
> and the updated one) so that postgresql does not have to ensure isolation for 
> concurrent queries by keeping a copy of each row.

I'm not sure that locking will prevent the snapshotting and the WAL
machinery, but someone more expert on the are could clarify this.
Since the column is nullable, I would apply it outside of the
transaction, and then do the update. If that still fails, I would try
to split the update on small chunks (after all, it's an update, so it
is smething you can line up data).

Luca

Input validation

[stan]

 Have table that contains employee keys, paired up with work type keys
 (both foreign keys) and a 3rd column that you enter a billing rate in.
 Then I have a table where employees enter their work. I need to validate
 that the employee, work type pair exists, before allowing the new record
 to be inserted.

 Any thoughts as to good way to do this?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Input validation

[Adrian Klaver]

On 8/7/19 11:07 AM, stan wrote:

  Have table that contains employee keys, paired up with work type keys
  (both foreign keys) and a 3rd column that you enter a billing rate in.
  Then I have a table where employees enter their work. I need to validate
  that the employee, work type pair exists, before allowing the new record
  to be inserted.

  Any thoughts as to good way to do this?


INSERT UPDATE trigger:

https://www.postgresql.org/docs/11/sql-createtrigger.html

https://www.postgresql.org/docs/11/plpgsql-trigger.html






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

Re: Input validation

[Rob Sargent]

On 8/7/19 12:07 PM, stan wrote:

  Have table that contains employee keys, paired up with work type keys
  (both foreign keys) and a 3rd column that you enter a billing rate in.
  Then I have a table where employees enter their work. I need to validate
  that the employee, work type pair exists, before allowing the new record
  to be inserted.

  Any thoughts as to good way to do this?


Does the employee interactively specify the "work type" then some 
time-spent value?


Can the work-type be chosen from a drop-down generated by

    select work_type from table where employee = 

Otherwise you'll need a trigger on the insert into "enter their work" 
table.  Sad thing here is the user has likely left the scene.

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

[Bryn Llewellyn]

I have a version of the code that I attached with my first email in this thread 
where I’ve added “raise notice” invocations to show the txid as my p1() and 
p2() execute. Everything that I see is consistent with what I wrote separately 
in reply to adrian.kla...@aklaver.com :

<<
1. my call p2() starts a txn.

2. However, during the execution of the proc, the usual autocommit behavior is 
programmatically turned off by explicit PostgreSQL code.

3. Other explicit PostgreSQL code makes “start transaction” inside a proc 
simply cause a runtime error under all circumstances. However, txns can be 
ended by “commit” or “rollback”. And new ones can be started—but only 
implicitly by executing a SQL statement that, as a top level SQL, would start a 
txn.

4. This is why “set transaction isolation level repeatable read” in my p2() is 
legal immediately after “rollback”—and produces the semantics I’d expect. At 
top level, and with autocommit turned on, it implicitly starts a txn—and you 
see the “begin” in the log file.

5. When the proc call ends, normal AUTOCOMMIT mode is turned on again, and a 
“commit” is issued automatically. This may, or may not, have something to do—as 
you can see by running p3() with AUTOCOMMIT ON.
>>

I’m interested only in the case that my proc does txn control—i.e. my p2()—and 
so I’m not interested in how my p1() behaves.

About your “I believe there is no reason ever to begin a procedure with a 
rollback”, I already explained why I did that. My higher goal is to take 
advantage of the “serializable” isolation level to safely enforce a multi-row 
data rule. And I want my pspgsql proc—following the time-honored philosophy for 
stored procs—to own the complete implementation. I discovered that doing 
“rollback” as the first executable statement in my proc allowed me to do “set 
transaction isolation level serializable”. And I’ve found no other way to do 
this. As I mentioned, the “real” version of my proc, written this way does pass 
my functionality tests.

B.t.w., I noticed that “set transaction isolation level serializable” must be 
the very first statement after “rollback” (or “commit”). Even an invocation of 
txid_current() after the rollback and before the ““set transaction” causes this 
runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before any 
query”.

About your PS, I’m new to this list—so forgive me if I didn’t follow proper 
etiquette. But as adrian.kla...@aklaver.com  
pointed out, the functionality under discussion here is part of the core 
PostgreSQL implementation.

On 07-Aug-2019, at 00:15, Luca Ferrari  wrote:

On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn  wrote:
> 1. my call p2() starts a txn.

In my opinion this is the point: a procedure must be "owner" of the
transaction to issue transaction control statements. You can watch
different behaviors placing here and there txid_current() before and
within p2 and re-run with autocommit off an on.
On autocmmmit = on the call opens a transaction and the procedure is
the only thing within the transaction, so it has full control. With
autocommit off you gain an implicit begin and the procedure is
possibly not the only thing you have within the transaction.
You can test it with:

testdb=# begin;
BEGIN
testdb=# call p2();
INFO:  TXID 134994
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function p2() line 9 at ROLLBACK
testdb=# rollback;
ROLLBACK


Having said that, I believe there is no reason ever to begin a
procedure with a rollback.

Here's a small example that performs a few commits and rollback


Luca

P.S:
I don't believe that asking on a public mailing list for a company to
answer is the right thing to do, you can always ask themselves on your
own.

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

[Bryn Llewellyn]

Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that 
“call proc_that_does_txn_control()” is legal only when AUTOCOMMIT is ON. Esp. 
when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this rule is 
“an implementation restriction, for the most part.” See HERE 
.

About your “In-procedure transaction control premises that no transaction is 
active before calling the procedure”… yes. Nevertheless, as the code that Umair 
Sahid showed us in the blog post that I referenced in my email that started 
this thread, you can indeed start end end transactions from an executing proc 
(as long as the session’s AUTOCOMMIT mode s ON).

So, logic tells me that once a txn is ended by issuing “commit” or “rollback”, 
you should be allowed to start the next one explicitly with “start 
transaction”. However, as mentioned, this causes a runtime error. I’ve decided 
simply not to care because I’ve discovered how to write my proc so that it 
passes the functionality tests that it ought to. I have to rely on the fact the 
the statements I’m interested in doing (including setting the isolation level) 
all implicitly start a txn and so “start transaction” isn’t needed!

Thanks to all who responded. The synthesis of what you all wrote helped me 
enormously. Case closed.

On 07-Aug-2019, at 00:26, Kyotaro Horiguchi  wrote:

Hello, Bryn.

At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn  wrote in 

> Here’s how I’ve tried to describe what I see for p2() with AUTOCOMMIT ON for 
> myself:
> 
> 1. my call p2() starts a txn.
> 
> 2. However, during the execution of the proc, the usual autocommit behavior 
> is programmatically turned off by explicit PostgreSQL code.
> 
> 3. Other explicit PostgreSQL code makes “start transaction” inside a proc 
> simply cause a runtime error under all circumstances. However, txns can be 
> ended by “commit” or “rollback”. And new ones can be started—but only 
> implicitly by executing a SQL statement that, as a top level SQL, would start 
> a txn.

In-procedure transaction control premises that no transaction is
active before calling the procedure.

https://www.postgresql.org/docs/11/sql-call.html

> If CALL is executed in a transaction block, then the called
> procedure cannot execute transaction control
> statements. Transaction control statements are only allowed if
> CALL is executed in its own transaction.

With AUTOCOMMIT=off, implicit BEGIN is invoked just before
CALLing p2() if no transaction is active. Thus p2() is always
called having a transaction active, which inhibits in-procedure
transaction control.

I'm not sure why you want to turn AUTOCOMNIT off, but even with
AUTOCOMMIT on, explict BEGIN prevents per-command COMMIT as you
perhaps know.

https://www.postgresql.org/docs/11/app-psql.html
> When on (the default), each SQL command is automatically
> committed upon successful completion. To postpone commit in
> this mode, you must enter a BEGIN or START TRANSACTION SQL
> command.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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

[Adrian Klaver]

On 8/7/19 11:46 AM, Bryn Llewellyn wrote:
Thanks for your response, Kyotaro. I’m happy, now, to accept the rule 
that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT 
is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter 
Eisentraut, that this rule is “an implementation restriction, for the 
most part.” See HERE 
.


About your “In-procedure transaction control premises that no 
transaction is active before calling the procedure”… yes. Nevertheless, 
as the code that Umair Sahid showed us in the blog post that I 
referenced in my email that started this thread, you can indeed start 
end end transactions from an executing proc (as long as the session’s 
AUTOCOMMIT mode s ON).




The key is that the AUTOCOMMIT status is just a specific case of the 
general rule. The general rule being that a PROCEDURE cannot do 
transaction ending commands when it it called within an outer 
transaction. You can run into the same issue in other situations e.g. 
ORM's that start a transaction behind the scenes. In other words this is 
not psql specific.  As long as you understand the general rule then 
things become clearer.



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

Recomended front ends?

[stan]

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.

What is the community wisdom here?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Recomended front ends?

[Igor Korot]

Hi,

On Wed, Aug 7, 2019 at 1:57 PM 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.
>
> What is the community wisdom here?

What language/tools you are most comfortable with?

Thank you.

>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>

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

[Luca Ferrari]

On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn  wrote:
> About your “I believe there is no reason ever to begin a procedure with a 
> rollback”, I already explained why I did that. My higher goal is to take 
> advantage of the “serializable” isolation level to safely enforce a multi-row 
> data rule. And I want my pspgsql proc—following the time-honored philosophy 
> for stored procs—to own the complete implementation. I discovered that doing 
> “rollback” as the first executable statement in my proc allowed me to do “set 
> transaction isolation level serializable”. And I’ve found no other way to do 
> this. As I mentioned, the “real” version of my proc, written this way does 
> pass my functionality tests.

I'm sorry, I still don't get the point in issuing a rollback as first
instruction because it restricts, at least in my opinion, the use case
of your procedure, that in turns restrict the mean of a procedure
(reusability). However, since you are dwealing with it, I'm fine.

>
> B.t.w., I noticed that “set transaction isolation level serializable” must be 
> the very first statement after “rollback” (or “commit”). Even an invocation 
> of txid_current() after the rollback and before the ““set transaction” causes 
> this runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before 
> any query”.
>

Well, SET TRANSACTION ISOLATION must be the very first instruction of
every transaction, not only within the case you describe.

> About your PS, I’m new to this list—so forgive me if I didn’t follow proper 
> etiquette. But as adrian.kla...@aklaver.com pointed out, the functionality 
> under discussion here is part of the core PostgreSQL implementation.

Sorry, but in your original post you placed the sentence: "I’m hoping
that someone from 2ndQuadrant can answer my questions", that's why I
pointed out thrat, as people at 2ndquadrant have already told you,
this has nothing to do with 2ndquadrant specifically. And that's why I
replied that "hoping" for an answer is not as good as asking directly
to them.
And please stop quote posting and jumping to different part of the
message, because it makes reading it very hard.

Luca

Re: Recomended front ends?

[Adrian Klaver]

On 8/7/19 11:57 AM, 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.


Yeah, I gave up on Base awhile back due to its flaky performance.



What is the community wisdom here?



What I have done is gone the Web route. In my case using Django as the 
framework/backend and the users browsers as the clients. That greatly 
simplifies keeping up with changes on the client end. There is still a 
need to deal with cross browser issues, but that is less of a chore. As 
Igor's post said it comes down to what you are comfortable with.




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

Re: Recomended front ends?

[Rob Sargent]

On 8/7/19 1:38 PM, Adrian Klaver wrote:

On 8/7/19 11:57 AM, 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.


Yeah, I gave up on Base awhile back due to its flaky performance.



What is the community wisdom here?



What I have done is gone the Web route. In my case using Django as the 
framework/backend and the users browsers as the clients. That greatly 
simplifies keeping up with changes on the client end. There is still a 
need to deal with cross browser issues, but that is less of a chore. 
As Igor's post said it comes down to what you are comfortable with.




And if you choose to go down the web route, and you're not intimately 
familiar with at least one of the strands, get help immediately. There 
is much, much magic involved.

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

[Bryn Llewellyn]

Yes, I do believe that I understand this. But there’s no getting away from the 
fact that the AUTOCOMMIT mode, and what this implies, is a server-side 
phenomenon—at least as several PostgreSQL experts have assured me. For example, 
when you use client-side Python with the psycopg2 driver, then once you’ve done 
“my_session = psycopg2.connect(connect_str)”, you can then do 
“my_session.set_session(autocommit=False)”. And then everything we’ve been 
saying in the psql context now applies in that context—yes?

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?

On 07-Aug-2019, at 11:56, Adrian Klaver  wrote:

On 8/7/19 11:46 AM, Bryn Llewellyn wrote:
> Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that 
> “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT is ON. Esp. 
> when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this rule 
> is “an implementation restriction, for the most part.” See HERE 
> .
> About your “In-procedure transaction control premises that no transaction is 
> active before calling the procedure”… yes. Nevertheless, as the code that 
> Umair Sahid showed us in the blog post that I referenced in my email that 
> started this thread, you can indeed start end end transactions from an 
> executing proc (as long as the session’s AUTOCOMMIT mode s ON).

The key is that the AUTOCOMMIT status is just a specific case of the general 
rule. The general rule being that a PROCEDURE cannot do transaction ending 
commands when it it called within an outer transaction. You can run into the 
same issue in other situations e.g. ORM's that start a transaction behind the 
scenes. In other words this is not psql specific.  As long as you understand 
the general rule then things become clearer.


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

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

[Adrian Klaver]

On 8/7/19 12:54 PM, Bryn Llewellyn wrote:
Yes, I do believe that I understand this. But there’s no getting away 
from the fact that the AUTOCOMMIT mode, and what this implies, is a 
server-side phenomenon—at least as several PostgreSQL experts have 
assured me. For example, when you use client-side Python with 
the psycopg2 driver, then once you’ve done “my_session = 
psycopg2.connect(connect_str)”, you can then 
do “my_session.set_session(autocommit=False)”. And then everything we’ve 
been saying in the psql context now applies in that context—yes?


The server responds to instructions from the client.

General rule:
https://www.postgresql.org/docs/11/sql-call.html

"If CALL is executed in a transaction block, then the called procedure 
cannot execute transaction control statements. Transaction control 
statements are only allowed if CALL is executed in its own transaction.

"



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?


psql:
https://doxygen.postgresql.org/bin_2psql_2common_8c.html
Starting ~ line 1355

if (transaction_status == PQTRANS_IDLE &&
 !pset.autocommit &&
 !command_no_begin(query))
 {
 results = PQexec(pset.db, "BEGIN");
  if (PQresultStatus(results) != PGRES_COMMAND_OK)

...

psycopg2:

https://github.com/psycopg/psycopg2/blob/master/psycopg/connection_int.c
~line 1294



On 07-Aug-2019, at 11:56, Adrian Klaver > wrote:


On 8/7/19 11:46 AM, Bryn Llewellyn wrote:
Thanks for your response, Kyotaro. I’m happy, now, to accept the rule 
that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT 
is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter 
Eisentraut, that this rule is “an implementation restriction, for the 
most part.” See HERE 
.
About your “In-procedure transaction control premises that no 
transaction is active before calling the procedure”… yes. 
Nevertheless, as the code that Umair Sahid showed us in the blog post 
that I referenced in my email that started this thread, you can indeed 
start end end transactions from an executing proc (as long as the 
session’s AUTOCOMMIT mode s ON).


The key is that the AUTOCOMMIT status is just a specific case of the 
general rule. The general rule being that a PROCEDURE cannot do 
transaction ending commands when it it called within an outer 
transaction. You can run into the same issue in other situations e.g. 
ORM's that start a transaction behind the scenes. In other words this is 
not psql specific.  As long as you understand the general rule then 
things become clearer.



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




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

Re: Recomended front ends?

[Igor Korot]

Hi,

On Wed, Aug 7, 2019 at 1:57 PM 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.
>
> What is the community wisdom here?

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.

What is expected from the software?
Does it needs to go out and access the web?
Is the company split between different areas of the country/state?
Does it needs to support Windows only?
Will there be a need to a handheld device or bar code scanner?
Will printing be involved?

List is preliminary and can go on and on. Its just first that comes to mind.

Get the requirements from the company management, make sure you understand them
check you knowledge of different tools available and their support of
the feature requested
and start working.

Good luck!!

Thank you.

>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>

Re: Recomended front ends?

[Rich Shepard]

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

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

[David G. Johnston]

On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari  wrote:

> On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn  wrote:
>
> > B.t.w., I noticed that “set transaction isolation level serializable”
> must be the very first statement after “rollback” (or “commit”). Even an
> invocation of txid_current() after the rollback and before the ““set
> transaction” causes this runtime error: “SET TRANSACTION ISOLATION LEVEL
> must be called before any query”.
> >
>
> Well, SET TRANSACTION ISOLATION must be the very first instruction of
> every transaction, not only within the case you describe.
>

IMHO, The documentation, probably in chapter 13, could use some exposition
on this topic.

What is being described here is basically:

[implicit] BEGIN (using default transaction options)
CALL
BEGIN (with inherited default transactions options)
ROLLBACK (to get rid of the nested transaction setup by the call with
the inherited default options)
START WITH OPTIONS
COMMIT;
<<<

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

[Benedict Holland]

All stored procedures run in their own transaction. My guess is that when
you turn off autocommit, you are not committing something after your
transaction ends. 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.

BTW, this is why you cant close a transaction within a stored procedure. It
doesnt make sense.

Thanks,
~Ben

On Wed, Aug 7, 2019, 4:41 PM David G. Johnston 
wrote:

> On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari  wrote:
>
>> On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn  wrote:
>>
>> > B.t.w., I noticed that “set transaction isolation level serializable”
>> must be the very first statement after “rollback” (or “commit”). Even an
>> invocation of txid_current() after the rollback and before the ““set
>> transaction” causes this runtime error: “SET TRANSACTION ISOLATION LEVEL
>> must be called before any query”.
>> >
>>
>> Well, SET TRANSACTION ISOLATION must be the very first instruction of
>> every transaction, not only within the case you describe.
>>
>
> IMHO, The documentation, probably in chapter 13, could use some exposition
> on this topic.
>
> What is being described here is basically:
>
> [implicit] BEGIN (using default transaction options)
> CALL
> BEGIN (with inherited default transactions options)
> ROLLBACK (to get rid of the nested transaction setup by the call with
> the inherited default options)
> START WITH OPTIONS
> COMMIT;
> <<< [implicit] COMMIT
>
> As far as psql is concerned there is only one statement and once its
> executed psql issues the implicit commit to match the implicit begin is
> sent previously.
>
> It should be better documented which combinations of outer and inner
> transaction commands are considered valid and which are not.  WIth
> examples.  The current scattering of words leaves the user to perform
> trial-and-error, just as the OP has, to determine what is allowed.
>
> The nested transaction seems like it has to be correct since otherwise the
> rollback as a first statement would attempt to rollback the transaction the
> call itself is executing within...
>
> Note I only have access to v10 at the moment so I haven't tried my own
> experiments.  To my main point I shouldn't have to - the expected behavior
> should be something I could directly interpret from the documentation and
> in my admitted brief attempt I could not do so.
>
> David J.
>
>
>

Re: Input validation

[Benedict Holland]

I think a check will also work but I second triggers.

Thanks,
~Ben

On Wed, Aug 7, 2019, 2:21 PM Rob Sargent  wrote:

>
> On 8/7/19 12:07 PM, stan wrote:
> >   Have table that contains employee keys, paired up with work type keys
> >   (both foreign keys) and a 3rd column that you enter a billing rate in.
> >   Then I have a table where employees enter their work. I need to
> validate
> >   that the employee, work type pair exists, before allowing the new
> record
> >   to be inserted.
> >
> >   Any thoughts as to good way to do this?
> >
> >
> Does the employee interactively specify the "work type" then some
> time-spent value?
>
> Can the work-type be chosen from a drop-down generated by
>
>  select work_type from table where employee = 
>
> Otherwise you'll need a trigger on the insert into "enter their work"
> table.  Sad thing here is the user has likely left the scene.
>
>
>
>

Re: How to check if a field exists in NEW in trigger

[Igal @ Lucee.org]

On 8/5/2019 11:34 AM, Michael Lewis wrote:

As a note to the original poster, you might want to check out-

https://www.postgresql.org/docs/current/citext.html


Thanks, Michael.  I'm familiar with the citext module.

There is no reason, however, for an email address to be not-lower-cased, 
so while in some cases (no pun intended) it makes sense to keep the 
original CaSe while performing a case insensitive comparison, when it 
comes to email addresses I rather collapse the value upon insertion/update.


Since that table has many more reads than writes, I am pretty sure that 
it's more performant too.


Best,

Igal

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

[Bryn Llewellyn]

Thanks, Adrian.

On 07-Aug-2019, at 13:19, Adrian Klaver  wrote:

On 8/7/19 12:54 PM, Bryn Llewellyn wrote:
> Yes, I do believe that I understand this. But there’s no getting away from 
> the fact that the AUTOCOMMIT mode, and what this implies, is a server-side 
> phenomenon—at least as several PostgreSQL experts have assured me. For 
> example, when you use client-side Python with the psycopg2 driver, then once 
> you’ve done “my_session = psycopg2.connect(connect_str)”, you can then do 
> “my_session.set_session(autocommit=False)”. And then everything we’ve been 
> saying in the psql context now applies in that context—yes?

The server responds to instructions from the client.

General rule:
https://www.postgresql.org/docs/11/sql-call.html

"If CALL is executed in a transaction block, then the called procedure cannot 
execute transaction control statements. Transaction control statements are only 
allowed if CALL is executed in its own transaction.
"

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

psql:
https://doxygen.postgresql.org/bin_2psql_2common_8c.html
Starting ~ line 1355

if (transaction_status == PQTRANS_IDLE &&
!pset.autocommit &&
!command_no_begin(query))
{
results = PQexec(pset.db, "BEGIN");
 if (PQresultStatus(results) != PGRES_COMMAND_OK)

...

psycopg2:

https://github.com/psycopg/psycopg2/blob/master/psycopg/connection_int.c
~line 1294

> On 07-Aug-2019, at 11:56, Adrian Klaver  > wrote:
> On 8/7/19 11:46 AM, Bryn Llewellyn wrote:
>> Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that 
>> “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT is ON. 
>> Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this 
>> rule is “an implementation restriction, for the most part.” See HERE 
>> .
>> About your “In-procedure transaction control premises that no transaction is 
>> active before calling the procedure”… yes. Nevertheless, as the code that 
>> Umair Sahid showed us in the blog post that I referenced in my email that 
>> started this thread, you can indeed start end end transactions from an 
>> executing proc (as long as the session’s AUTOCOMMIT mode s ON).
> The key is that the AUTOCOMMIT status is just a specific case of the general 
> rule. The general rule being that a PROCEDURE cannot do transaction ending 
> commands when it it called within an outer transaction. You can run into the 
> same issue in other situations e.g. ORM's that start a transaction behind the 
> scenes. In other words this is not psql specific.  As long as you understand 
> the general rule then things become clearer.
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 


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

Re: How to check if a field exists in NEW in trigger

[David G. Johnston]

On Sun, Aug 4, 2019 at 3:52 PM Igal @ Lucee.org  wrote:

> I have the following statement in a trigger:
>
> new.email = lower(new.email);
>
> When I try to update a record without setting the email column however, I
> get an error:
>
> SQL Error [42703]: ERROR: record "new" has no field "email"
>   Where: SQL statement "SELECT lower(new.email)"
> PL/pgSQL function on_record_modified() line 26 at assignment
>
> I have seen some hacks suggesting TRY/CATCH or converting to a JSON and
> checking if the field exists, but I would think that there's a better way
> to check if the field is in the NEW record, no?
>
> Any ideas?
>
As already suggested you can spend not inconsiderable (you should measure
it yourself) runtime time figuring out the schema of the table the trigger
is attached to every single time it is invoked (even though the schema
likely changes highly infrequently) or you can figure out a "compile time"
way to program the schema structure into the individual function you are
attaching to the trigger.

A hybrid approach would be to write the trigger function with an input
argument (has_email_field boolean) and when attaching the function to the
trigger attach it with either true/false depending on whether the target
table has an email field.

David J.

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

[Bryn Llewellyn]

Thanks David. I’m relieved to hear that I’m not the only one who finds it hard 
to predict the behavior of some things in PostgreSQL just from reading the 
docs. Hypothesising and experimenting seem to be needed for the trickier cases.

You use the phrase "nested transaction”. This Google search gets no hits:

  "nested transaction" site:www.postgresql.org/docs/11/ 


And as I’ve come to understand the phrase in the wider world, "nested 
transaction” is synonymous with “autonomous transaction”. PostgreSQL 11.2 
doesn’t support these. But I’ve heard that some future release might. So, as I 
see it, a single session can do only one transaction after another with no 
overlapping. My experiments using txid_current()—mentioned elsewhere in the 
offshoots from my original post—are consistent with this hypothesis.

On 07-Aug-2019, at 13:40, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote:

On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari mailto:fluca1...@gmail.com>> wrote:
On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn mailto:b...@yugabyte.com>> wrote:

> B.t.w., I noticed that “set transaction isolation level serializable” must be 
> the very first statement after “rollback” (or “commit”). Even an invocation 
> of txid_current() after the rollback and before the ““set transaction” causes 
> this runtime error: “SET TRANSACTION ISOLATION LEVEL must be called before 
> any query”.
>

Well, SET TRANSACTION ISOLATION must be the very first instruction of
every transaction, not only within the case you describe.

IMHO, The documentation, probably in chapter 13, could use some exposition on 
this topic.

What is being described here is basically:

[implicit] BEGIN (using default transaction options)
CALL
BEGIN (with inherited default transactions options)
ROLLBACK (to get rid of the nested transaction setup by the call with the 
inherited default options)
START WITH OPTIONS
COMMIT;
<<<

Re: Guidance needed on an alternative take on common prefix SQL

[Rob Sargent]

On 8/7/19 3:36 AM, Laura Smith wrote:

On Wednesday, August 7, 2019 2:01 AM, Andy Colson  wrote:


On 8/6/19 6:25 PM, Laura Smith wrote:


Hi,
I've seen various Postgres examples here and elsewhere that deal with the old 
common-prefix problem (i.e. "given 1234 show me the longest match").
I'm in need of a bit of guidance on how best to implement an alternative take. 
Frankly I don't quite know where to start but I'm guessing it will probably 
involve CTEs, which is an area I'm very weak on.
So, without further ado, here's the scenario:
Given an SQL filtering query output that includes the following column:
87973891
87973970
87973971
87973972
87973973
87973975
87973976
87973977
87973978
87973979
8797400
The final output should be further filtered down to:
87973891
8797397
8797400
i.e. if $last_digit is present 0–9 inclusive, recursively filter until the 
remaining string is all the same (i.e. in this case, when $last_digit[0-9] is 
removed, 8797397 is the same).
So, coming back to the example above:
8797397[0-9] is present
so the "nearest common" I would be looking for is 8797397 because once [0-9] is 
removed, the 7 is the same on the preceeding digit.
The other two rows ( 87973891 and 8797400) are left untouched because 
$last_digit is not present in [0-9].
Hope this question makes sense !
Laura

Hows this?

select distinct
case cc
when 1 then num
else left(num,-1)
end
from (
select
num,
(select count(*) as cc from numbers n2 where left(n2.num, -1) = 
left(numbers.num, -1))
from numbers
) as tmpx ;

-Andy



Hi Andy,

That looks supremely clever !

I have just done a quick test and looks like it works as intended. Will do some 
more thorough testing with a larger dataset in due course.

Thank you very much indeed

Laura




If the target field is really an integer type and you have lots of rows 
you might be better off with arithmetic functions.



create table short as select id/10 as base, array_agg(mod(id,10)) as 
odds from head group by base;


select * from short;
  base   |    odds
-+-
  879740 | {0}
 8797389 | {1}
 8797397 | {0,1,2,3,5,6,7,8,9}
(3 rows)

 select case when array_length(odds,1) = 1 then 10*base + odds[1] else 
base end from short;

   base
--
  8797400
 87973891
  8797397
(3 rows)