Waiting for PostgreSQL 13 – Add %x to default PROMPT1 and PROMPT2 in psql

On 12nd of February 2020, Michael Paquier committed patch:

Add %x to default PROMPT1 and PROMPT2 in psql
 
%d can be used to track if the current connection is in a transaction
block or not, and adding it by default to the prompt has the advantage
to not need a modification of .psqlrc, something not possible depending
on the environment.
 
This discussion has happened across various sources, and there was a
strong consensus in favor of this change.
 
Author: Vik Fearing
Reviewed-by: Fabien Coelho
Discussion: https://postgr.es/m/09502c40-cfe1-bb29-10f9-4b3fa7b2bbb2@2ndquadrant.com

This is very simple, but cool addition.

Previously PROMPT1 and PROMPT2 were, by default, “%/%R%# “. That is, they contained:

  1. %/ – name of current database
  2. %R – additional marker showing at what stage of parsing are we:
    • = – new command
    • – continuation of multiline query
    • * – inside of /* */ comment block
    • ! – if disconnected from database
    • – inside of ‘….' string
    • $ – inside of $$…$$ string
    • – inside of “quoted" identifier
  3. %# – # or > character depending on whether user is superuser or not

Now, with this patch, between %R and %# we also have %x which can mean:

  • * – we're inside transaction
  • ! – inside transaction that failed, but hasn't been rolled back yet
  • ? – transaction state is unknown (no connection)
  • empty string – not in transaction

This will immediately show if the transaction failed.

For example, in previous Pg:

depesz=# BEGIN;
BEGIN
 
depesz=# SELECT 1/0;
ERROR:  division BY zero
 
depesz=# SELECT 1;
ERROR:  CURRENT TRANSACTION IS aborted, commands ignored until END OF TRANSACTION block

and in new one:

depesz=# BEGIN;
BEGIN
 
depesz=*# SELECT 1/0;
ERROR:  division BY zero
 
depesz=!# SELECT 1;
ERROR:  CURRENT TRANSACTION IS aborted, commands ignored until END OF TRANSACTION block

Pretty cool.

Thanks to all involved 🙂

One thought on “Waiting for PostgreSQL 13 – Add %x to default PROMPT1 and PROMPT2 in psql”

  1. PostgreSql 13 can include multiple table queries with stored procedures, such as Sqlserver,MySql can query the stored procedures and return multiple results directly

    CREATE PROCEDURE sp_SelectTables

    AS
    BEGIN
    select * from table01;
    select * from table02;
    select * from table03;
    END
    GO

    PostgreSql 13 中能否加入 存储过程 的多表查询, 如Sqlserver,MySql 均可在存储过程中查询并直接返回多个结果

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.