Tag Archives: PostgreSQL

Error: current transaction is aborted, commands ignored until end of transaction blockp

In normal database programming, we encountered errors from time to time: Current Transaction is aborted, commands ignored until the end of transaction BlockP. How to solve it?We usually look it up on the Internet. If you’re a little bit better at English you can tell from the error message that there’s probably something wrong there. But if the experience is not very rich, or the Internet to find more convenient. The most basic error message can see from the official documentation (http://www.postgresql.org/docs/8.4/interactive/errcodes-appendix.html).
The error above is the cause of improper transaction control, which can be written as follows.
Improper writing 1:
Connection OPEN.
Try {
Try {
Database operation A.
} Catch (Exception e) {
The log. The error (” do something “);
}
 
Database operation B. // The above error may occur during this data operation.
The transaction commits commit.
} Catch (Exception ex) {
Transaction rollback rollback.
} the finally {
Close the connection.
}
 
Improper writing method 2:
Connection OPEN.
Try {
Database operation A.
Database operation B.
The transaction commits commit.
} Catch (Exception ex) {
Database operation C. // The above error may occur during this data operation.
The transaction commits commit.
} the finally {
Close the connection.
}
 
In a Postgres database, if there is an error in a database operation in the same transaction, all subsequent databases in that transaction will fail.
If there is an error in database operation A, the same database operation B will report an error when it is executed. If there is an error in database operation A or database operation B, the same database operation C will report an error.
To avoid errors, someone is using the re-open method, which in effect throws away all previous operations (not in the case of auto-commit) and adds overhead by re-opening, which is not recommended.
Depending on the cause of the error, we can treat the processing before the possible error as a transaction. The processing after the error is then treated as a transaction, depending on the specific logic. That way you can avoid making mistakes.
In this way, we can change the improper 1 to
Try {
Database operation A.
} Catch (Exception e) {
Transaction rollback or commit;
The log. The error (” do something “);
}
Database operation B.  
 
In this way we can change the improper notation 2 to
} Catch (Exception ex) {
Transaction rollback or commit;
Database operation C.   
The transaction commits commit.
} the finally {
Close the connection.
}
 
 
 

PHP connection PostgreSQL error call to undefined function PG_ connect()

Use PHP to connect PostgreSQL, Call to undefined function pg_connect();
‘PDOException’ with message ‘could not find driver’ with message ‘could not find driver’
Testing environment:
First, the extension=php_pdo_pgsql.dll and extension=php_pgsql.dll extensions are on, but not working.
Then, add \PostgreSQL\bin to the environment variable, which still doesn’t work.
Then, putting \PostgreSQL\bin\libpq.dll into System32 has no effect.
Finally, change libpq.dll from \ PHP \php5.*\ into \apache*\bin to solve the problem.

【error】postgresql relation does not exist

There have been a lot of problems with PostgresQL lately.
postgresql relation does not exist
When querling AAA tables using PostgresQL, postgresQL relation does not exist, but

SELECT   tablename   FROM   pg_tables;

AAA tables exist. It’s weird.
The search turned out to be a problem with quotes. PostgreSQL itself is not case sensitive, so if you want to create a new table with uppercase letters, you must use quotation marks, and if you want to query it, you must use quotation marks.

select * from "AAA";

The following from: http://blog.csdn.net/dream20nn/article/details/51790106
A recently developed ETL tool for the WEB requires different data sources. The first time POSTGRESQL has found a problem with double quotes:
standard SQL is case-insensitive. But PostgreSQL allows case-sensitive definition and reference methods for the names of objects in the database. This is done by enclosing the name of the object you want to support size in double quotes in the DDL. For example, you want to create a table called AAA. If you use CREATE TABLE AAA (…) ; So the table that you create is actually aaa.
CREATE TABLE “AAA” (…) if you want to CREATE an upper case AAA TABLE. ; This is the double quote way of defining the object name. The disadvantage of writing
is that the query must also refer to the object name in double quotes. SELECT * FROM “AAA”; PostgreSQL doesn’t need to go to the AAA object and return an error that aaa doesn’t exist. It is important to note that not only tables can be defined and referenced in this way, but any object (column name, index name, etc.) in PostgreSQL is valid.
In fact, traditional SQL is case-insensitive, so there is no problem as long as DDL and DLLS manipulate database objects in the traditional (without double quotes) way. The problem is that if tables are created through PostgreSQL’s pgAdmin III tool, objects are created with double quotes by default, so the DLL must also be used with double quotes. However, this is not standard and is not supported if the database is accessed through some common library function.
Recommendation:
1. The tool pgAdmin III is not recommended for creating database objects. You should still write DDL statements by hand. 2. Double quotation marks in DDL are not recommended for creating case-sensitive objects. PostgreSQL recommends using uppercase for SQL key word and lowercase for all other names.

configure: error: readline library not found solution

Configure: error: readline Library not found.
Solution: Yum Install Readline-devel
The detailed process is as follows:

[root@April postgresql-9.2.24]# yum install readline-devel
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: mirrors.163.com
 * extras: mirrors.163.com
 * updates: mirrors.163.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package readline-devel.i686 0:6.0-4.el6 will be installed
--> Processing Dependency: ncurses-devel for package: readline-devel-6.0-4.el6.i686
--> Running transaction check
---> Package ncurses-devel.i686 0:5.7-4.20090207.el6 will be installed
--> Processing Dependency: ncurses-libs = 5.7-4.20090207.el6 for package: ncurses-devel-5.7-4.20090207.el6.i686
--> Running transaction check
---> Package ncurses-libs.i686 0:5.7-3.20090208.el6 will be updated
---> Package ncurses-libs.i686 0:5.7-4.20090207.el6 will be an update
--> Processing Dependency: ncurses-base = 5.7-4.20090207.el6 for package: ncurses-libs-5.7-4.20090207.el6.i686
--> Running transaction check
---> Package ncurses-base.i686 0:5.7-3.20090208.el6 will be updated
---> Package ncurses-base.i686 0:5.7-4.20090207.el6 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================
 Package                           Arch                    Version                               Repository               Size
===============================================================================================================================
Installing:
 readline-devel                    i686                    6.0-4.el6                             base                    134 k
Installing for dependencies:
 ncurses-devel                     i686                    5.7-4.20090207.el6                    base                    641 k
Updating for dependencies:
 ncurses-base                      i686                    5.7-4.20090207.el6                    base                     61 k
 ncurses-libs                      i686                    5.7-4.20090207.el6                    base                    249 k

Transaction Summary
===============================================================================================================================
Install       2 Package(s)
Upgrade       2 Package(s)

Total download size: 1.1 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): ncurses-base-5.7-4.20090207.el6.i686.rpm                                                         |  61 kB     00:00     
(2/4): ncurses-devel-5.7-4.20090207.el6.i686.rpm                                                        | 641 kB     00:00     
(3/4): ncurses-libs-5.7-4.20090207.el6.i686.rpm                                                         | 249 kB     00:00     
(4/4): readline-devel-6.0-4.el6.i686.rpm                                                                | 134 kB     00:00     
-------------------------------------------------------------------------------------------------------------------------------
Total                                                                                          311 kB/s | 1.1 MB     00:03     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Updating   : ncurses-base-5.7-4.20090207.el6.i686                                                                        1/6 
  Updating   : ncurses-libs-5.7-4.20090207.el6.i686                                                                        2/6 
  Installing : ncurses-devel-5.7-4.20090207.el6.i686                                                                       3/6 
  Installing : readline-devel-6.0-4.el6.i686                                                                               4/6 
  Cleanup    : ncurses-libs-5.7-3.20090208.el6.i686                                                                        5/6 
  Cleanup    : ncurses-base-5.7-3.20090208.el6.i686                                                                        6/6 
  Verifying  : ncurses-base-5.7-4.20090207.el6.i686                                                                        1/6 
  Verifying  : ncurses-libs-5.7-4.20090207.el6.i686                                                                        2/6 
  Verifying  : readline-devel-6.0-4.el6.i686                                                                               3/6 
  Verifying  : ncurses-devel-5.7-4.20090207.el6.i686                                                                       4/6 
  Verifying  : ncurses-libs-5.7-3.20090208.el6.i686                                                                        5/6 
  Verifying  : ncurses-base-5.7-3.20090208.el6.i686                                                                        6/6 

Installed:
  readline-devel.i686 0:6.0-4.el6                                                                                              

Dependency Installed:
  ncurses-devel.i686 0:5.7-4.20090207.el6                                                                                      

Dependency Updated:
  ncurses-base.i686 0:5.7-4.20090207.el6                         ncurses-libs.i686 0:5.7-4.20090207.el6                        

Complete!

View installation results:

[root@April postgresql-9.2.24]# ls -l /usr/include/ | grep readline
drwxr-xr-x.  2 root root   4096 Apr  2 14:50 readline

 

Log search of SQL execution on distributed green plus database master

enter/gpmaster// pg_log gpseg – 1, you can see the listed log list: using the command for a keyword in the log file: 5409626 is the SESSION ID
more gpdb-2020-09-26_000000. CSV |grep 5409526

finally renders the SQL execution LOG containing the session id. The easiest way to find the LOG above is to find the LOG. If there is a better way to find the LOG later, then update it.