Creating Delete Triggers in PostgreSQL

One of the common data integrity issues that can happen in a database is the unintended deletion of a row. Here is how to create a DELETE trigger in PostgreSQL.

The example code below assumes you have a “customer_cus” table and a “customer_archive_cua” table with at least two fields. It is a good idea to add a timestamp field to the archive table with DEFAULT now(), so that the date of deletion is captured.

CREATE FUNCTION archive_customer() RETURNS TRIGGER AS '
BEGIN
INSERT INTO customer_archive_cua (name_cua, address_cua)
VALUES (OLD.name_cus,OLD.address_cus);
RETURN NULL;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER customer_archive_on_delete AFTER DELETE ON customer_cus
FOR EACH ROW EXECUTE PROCEDURE archive_customer();

References


Connection pooling with mod_perl

I found this info in the PostgreSQL archives. Here are  2 methods:

Best method from Dan Lyke: Apache::DBI will pool across Perl programs, and you don’t have to change anything in your scripts.

Next best method from Gilles DAROLD: in your perl script use the following code

use vars qw($dbh);

$dbh ||= DBI::connect($datasrc, $dbuser, $dbpwd);

These can be use to create a database connection class in Perl that can handle transparently the connection / reconnection and disconnection for your code. The end use code doesn’t have to be OO, thus the connection object can be reused in any legacy code.


How to change ownership of a PostgreSQL database

To change the owner of a database we have found this solution:

UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename='new_owner') WHERE datname='db_name';

http://archives.postgresql.org/pgsql-admin/2003-07/msg00301.php

    * From: Tom Lane
    * To: Devrim GUNDUZ
    * Subject: Re: changing ownership of db
    * Date: Tue, 29 Jul 2003 17:41:08 -0400

Devrim GUNDUZ  writes:
> On Tue, 29 Jul 2003, Benjamin Thelen (CCGIS) wrote:
>> I would like to change the ownership of a database.

> UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE
> usename='new_owner') WHERE datname='db_name';

That is all you need to do --- it's the only place CREATE DATABASE
records the owner's identity.

> If you also want to change the owner of the tables, update pg_class:

> UPDATE pg_class SET relowner=(SELECT usesysid FROM pg_shadow WHERE
> usename='new_owner')  WHERE relname IN (SELECT relname from
> pg_class WHERE relname NOT LIKE 'pg_%');

This is likely to be a very bad idea, especially if you give ownership
of the system tables to a non-superuser.  Ownership of those tables
stays with the postgres user during a CREATE DATABASE.

			regards, tom lane

PostreSQL Upgrade Process

  • First you need to change permissions on the localhost to trust:
    # cd /var/lib/pgsql/data
    
    # vi pg_hba.conf

    Change the autorization/authentication for the line that starts with “local” to trust:

    local        all                                           trust
  • And prevent clients to access the database by changing in postgresql.conf:
    tcpip_socket = true

    to

    tcpip_socket = false

    and

    ssl = true

    to

    ssl = false
  • restart postgres:
    # service postgresql restart

    If you are having trouble restarting postgres, try to force the restart:

    # su - postgres
    
    $ pg_ctl -m immediate -D /var/lib/pgsql/data restart
  • Create a backup of the database:
    # sudo -u postgres pg_dumpall > ~postgres/backups/full-[version]-[date].out
  • Shut down PostgreSQL
    # service postgresql stop
  • Upgrade PostgreSQL
    # rpm -Uvh /usr/src/redhat/RPMS/i686/postgresql-*rpm
  • Move the old db structure out of the way:
    # mv /var/lib/pgsql/data /var/lib/pgsql/backups/data-[oldversion]
  • Start postgres back up:
    # service postgresql start
  • Modify the pg_hba.conf file back to what we need it to be for the migration (see previously)
  • Restore the database:
    sudo -u postgres psql -e template1 -f ~postgres/backups/full-[version]-[date].out > create.log 2> error.log
  • Restore the /etc/init.d/postgresql file with the logging:
    su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -l /var/log/pgsql -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start  > /dev/null 2>&1" < /dev/null

    Make sure to check the error.log, and if you encounter “permission denied” problems, make sure the the user (at top of the backup file) has the correct permissions set.

  • Restore pg_hba.conf and the postgresql.conf files to the original values
  • Restore the server certs
    cp -a /var/lib/pgsql/backups/data-[version]/server.* /var/lib/pgsql/data
    
    chown postgres.root /var/lib/pgsql/data/server.*
    
    chmod 600 /var/lib/pgsql/data/server.*
  • Restore the postgres password to the correct pass:
    ALTER USER "postgres" WITH PASSWORD 'secret-password-here' CREATEDB CREATEUSER
  • Restart PostgreSQL

Rebuild Postgres for specific platform

# rpm -i postgres-[version number].src.rpm

# rpmbuild -bb --target i686 /usr/src/redhat/SPECS/postgresql.spec

If you get a problem with unpackaged files, use the following trick:

vi /usr/lib/rpm/macros

An modify some of the lines as follows:

#

# Script gets packaged file list on input and buildroot as first parameter.

# Returns list of unpackaged files, i.e. files in $RPM_BUILD_ROOT not packaged.

#

# Note: Disable (by commenting out) for legacy compatibility.

#%__check_files         /usr/lib/rpm/check-files %{buildroot}

#

# Should unpackaged files in a build root terminate a build?

#

# Note: The default value should be 0 for legacy compatibility.

%_unpackaged_files_terminate_build      0

#

# Should missing %doc files in the build directory terminate a build?

#

# Note: The default value should be 0 for legacy compatibility.

%_missing_doc_files_terminate_build     0


Creating a PostgreSQL datasource with ColdFusion MX

To set up a PostgreSQL datasource for ColdFusion (CFMX), follow these steps:

  • Open CFMX administrator
  • Click on “Data Sources”
  • In the “add new datasource” write in a name and select “other” before clicking “add”
  • Fill our the fields as follows:
    CF Data Source Name: JDBC
    URL: jdbc:postgresql://DB_server_fqdn:5432/ (as per Pedro's comment)
    Driver Class: org.postgresql.Driver
    Driver Name: PostgreSQL
    Username: db_user_name
    Password: ******
    Description:
    
  • Click submit
  • Test connection (Status shoudl say “OK”)

How to delete records with duplicate fields, such as dupplicate emails?

To delete records where only a field is duplicated, we can use a similar technique to the general duplicate removal technique:

CREATE TABLE temp AS SELECT DISTINCT ON (email) * FROM table_to_deduplicate;
DROP TABLE table_to_deduplicate;
ALTER TABLE temp RENAME TO table_to_deduplicate;

Be mindful of the fact that this process will not preserve the constraints on the original table. So if you have indexes, NOT NULL attributes, primary keys, foreign keys, you’ll have to recreate them.


How to delete dupplicate records in DB2, Oracle, MySQL, and PostgreSQL

To delete dupplicate records in SQL, the following sequence of commands will do the trick:

CREATE TABLE temp AS SELECT DISTINCT * FROM table_to_deduplicate;
DROP TABLE table_to_deduplicate;
ALTER TABLE temp RENAME TO table_to_deduplicate;

Be mindful of the fact that this process will not preserve the constraints on the original table. So if you have indexes, NOT NULL attributes, primary keys, foreign keys, you’ll have to recreate them.


PostgreSQL to DB2 export conversion

PostgreSQL and DB2 use different styles of conventions to represent NULL. In PostgreSQL, the convention is to have “N” in the data to be loaded with the COPY command. In DB2, the LOAD command can be set to consider and empty field as NULL. Thus, to convert from DB2 to POstgreSQL style data dump, assuming TAB as the delimiter, we would use:

sed -e 's/tt/t\Nt/g' -e 's/t$/t\N/g' -e 's/tt/t\Nt/g' db2_file > pg_file

Conversely, to convert from PostgreSQL to DB2 style, with TAB delimited data:

sed -e 's/t\Nt/tt/g' -e 's/t\N$/t/g' -e 's/t\Nt/tt/g' db2_file > pg_file

The reason we need to go with 3 steps is the following:

  • step 1: convert TAB NULL TAB
  • step2: convert TAB NULL at the end of line
  • step3: convert TAB NULL TAB from sequences where originally there was TAB NULL TAB NULL

All this has been tested on AIX and Linux.


Can’t edit PostgreSQL datasource in CFMX

The original URL where we found this solution is here: PostgreSQL DSN Config Bug

Workaround for PostgreSQL DSN Config Bug

How to fix a bug for PostgreSQL Data Source configuration in the ColdFusion MX Administrator.

The problem is that when configuring a PostgreSQL DSN according to the instructions in this TechNote, if you choose a Driver Name field of PostgreSQL, then that will trigger ColdFusion to invoke a handler template of postgresql.cfm. This handler template is defined in the configuration file neo-query.xml in ColdFusion MX’s lib directory. At one time the ColdFusion MX Administrator was going to provide a PostgreSQL JDBC driver out of the box, and that was partially implemented. However, the postgresql.cfm handler file was never implemented or even created. The result is that after first creating the data source when you return to edit it again ColdFusion will recognize the Driver Name of PostgreSQL and attempt to trigger the postgresql.cfmhandler. This causes the error:

"File not found: /CFIDE/administrator/datasources/postgresql.cfm 
The specific sequence of files included or processed is: 
C:\CFusionMX\wwwroot\CFIDE\administrator\datasources\postgresql.cfm "

The author discovered the solution on a CFTALK thread. The solution in the CFTALK thread shows how to change the handler reference from postgresql.cfm to default.cfm. Then restart ColdFusion MX. You will then be able to edit the data source correctly. Click Here for the interactive RoboHelp (Demo).
Pete Freitag pointed out in a comment that a better solution would be to find the default.cfm then make a copy named postgresql.cfm.