[GENERAL] Aborted pg_dump run empties existing archive file
I just discovered that a run of pg_dump that gets aborted empties any pre-existing backup file of the same name. It happens whether the run was deliberately canceled by the user or just failed because of a bad password (as in the example below). ~/dba$ pg_dump --host=localhost -U ken --format=c --file=mess_development.bak --no-owner --no-privileges mess_development Password: ~/dba$ ls -l mess_development.bak -rw-rw-r-- 1 ken ken *87070* May 26 14:20 mess_development.bak ~/dba$ pg_dump --host=localhost -U ken --format=c --file=mess_development.bak --no-owner --no-privileges mess_development Password: pg_dump: [archiver (db)] connection to database "mess_development" failed: FATAL: password authentication failed for user "ken" FATAL: password authentication failed for user "ken" ~/dba$ ls -l mess_development.bak -rw-rw-r-- 1 ken ken *0* May 26 14:21 mess_development.bak ~/dba$ This is troubling because I can imagine a disaster scenario where one loses one's previous backup and, perhaps because of a forgotten password, can't generate a new one. (This scenario hasn't happened to *me* yet, but seems like it could.) In general, a failed run of a piece of software should leave everything unchanged. So maybe this is a bug that needs to be fixed? ~ Ken
Re: [GENERAL] Generate PG schemas from the Oracle Data Modeler tool?
I have established a connection between the Oracle tool and my PG databases by installing the PG JDBC driver from https://jdbc.postgresql.org/download.html and following instructions at http://stackoverflow.com/questions/7592519/oracle-sql-developer-and-postgresql/22299552 and https://www.youtube.com/watch?v=UGG_N9Mlgdw=youtu.be . While the connection thus established seems solid, it delivers access to only part of the PG database objects. Most weirdly, the public schema is invisible in the Oracle tool browser, though it displays other schemas I create. For full details of this weirdness, see http://stackoverflow.com/questions/35809963/cant-see-postgresql-public-schema-in-oracle-sql-developer. Any advice from the PG world would be very welcome. ~ Thanks, Ken On Wed, Feb 24, 2016 at 10:52 PM, Ken Winter <k...@sunward.org> wrote: > The best affordable (in this case, free) data modeling tool that I have > found is the "Oracle SQL Developer Data Modeler" ( > http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html). > > > The best DBMS (free or otherwise) that I have found is PostgreSQL. > > So of course it would be great to be able to connect the Oracle tool to a > PG database, so one could forward- and reverse-engineer between the two. > At present, apparently the Oracle tool only natively connects with Oracle, > DB2, and SQL Server. > > So I'm wondering if anybody knows of a utility or an Oracle Data Modeler > add-on that will take some dialect of SQL DDL that that tool generates and > turn it into PG-readable SQL. > > I get it from the list at > https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL > that there are tools that do something more ambitious: read schemas from > actual Oracle databases and implement them as schemas in actual PG > databases. What I need is more modest than that: a tool that inputs a file > of DDL from the Oracle Data Modeler tool and outputs that DDL in PG syntax. > > ~ Thanks for any leads you can provide > ~ Ken > > >
[GENERAL] Generate PG schemas from the Oracle Data Modeler tool?
The best affordable (in this case, free) data modeling tool that I have found is the "Oracle SQL Developer Data Modeler" ( http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html). The best DBMS (free or otherwise) that I have found is PostgreSQL. So of course it would be great to be able to connect the Oracle tool to a PG database, so one could forward- and reverse-engineer between the two. At present, apparently the Oracle tool only natively connects with Oracle, DB2, and SQL Server. So I'm wondering if anybody knows of a utility or an Oracle Data Modeler add-on that will take some dialect of SQL DDL that that tool generates and turn it into PG-readable SQL. I get it from the list at https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL that there are tools that do something more ambitious: read schemas from actual Oracle databases and implement them as schemas in actual PG databases. What I need is more modest than that: a tool that inputs a file of DDL from the Oracle Data Modeler tool and outputs that DDL in PG syntax. ~ Thanks for any leads you can provide ~ Ken
[GENERAL] Is IF EXISTS legit in ALTER TABLE ... RENAME?
According to the PG 9.1 doc ( http://www.postgresql.org/docs/current/static/sql-altertable.html), this is a valid flavor of ALTER TABLE: ALTER TABLE [ IF EXISTS ] name RENAME TO new_name But when I try to execute such a command, I get: ERROR: syntax error at or near EXISTS LINE 1: ALTER TABLE IF EXISTS event ^ QUERY: ALTER TABLE IF EXISTS event RENAME TO event_s; CONTEXT: PL/pgSQL function convert_table_to_history line 60 at EXECUTE statement As you'll see, in this case the command was issued from a function. But issuing it directly evokes the same error. ?!? ~ Thanks in advance ~ Ken
[GENERAL] EXECUTE command-string INTO target USING expression isn't working
I swear this used to work, but in PostgreSQL 9.1 it doesn't work any more... CASE 1: If I write it like this: FOR func IN ( SELECT * FROM information_schema.routines WHERE routine_schema = 'tests' ) LOOP q := 'SELECT tests.' || func.routine_name || '()'; EXECUTE q INTO r; ... END LOOP; on the first time through the loop I get this error: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function cre_supers_for_organization_i line 12 at SQL statement SQL statement INSERT INTO organization (name, status) VALUES (str, 'Closed Ongoing Group') PL/pgSQL function event line 32 at SQL statement SQL statement SELECT tests.event() PL/pgSQL function run_all_tests line 16 at EXECUTE statement SQL state: 42601 CASE 2: If I write it like this: FOR func IN ( SELECT * FROM information_schema.routines WHERE routine_schema = 'tests' ) LOOP q := 'SELECT tests.$1()'; EXECUTE q INTO r USING func.routine_name; ... END LOOP; on the first time through the loop I get this error: ERROR: syntax error at or near $1 LINE 1: SELECT tests.$1() ^ QUERY: SELECT tests.$1() CONTEXT: PL/pgSQL function run_all_tests line 17 at EXECUTE statement SQL state: 42601 In both cases, each of the functions to be called returns a string, and r is a VARCHAR variable. What's wrong with this picture? ~ Thanks in advance for your help ~ Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null comparisons and the transform_null_equals run-time parameter
I get it. Thanks, Tom. In case anybody else needs this functionality, let me offer a family of functions that do comparisons that treat NULL as a real value (rather than as unknown). For example: CREATE OR REPLACE FUNCTION eqnull(varchar, varchar) RETURNS boolean AS $BODY$ /* Return True if both args have the same non-NULL values or both args are NULL; otherwise False. */ DECLARE v1 ALIAS FOR $1; v2 ALIAS FOR $2; BEGIN -- NULL = NULL IF v1 IS NULL AND v2 IS NULL THEN RETURN True; -- NULL != Any non-NULL value ELSIF v1 IS NULL AND v2 IS NOT NULL THEN RETURN False; -- Any non-NULL value != NULL ELSIF v1 IS NOT NULL AND v2 IS NULL THEN RETURN False; -- Non-NULL value = non-NULL value ELSIF v1 = v2 THEN RETURN True; -- Non-NULL value != non-NULL value ELSE RETURN False; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE ; You need a separate function for each data type you want to compare; the only difference among these functions is their argument types. I've implemented variants for VARCHAR, NUMERIC, TIMESTAMP, and BOOLEAN. The reason I need this is that I'm writing functions to test my database programming (triggers, rules, etc), and these tests sometimes need to treat Null as just another value in doing test comparisons. ~ Ken -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Saturday, June 05, 2010 9:41 AM To: Ken Winter Cc: 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Null comparisons and the transform_null_equals run- time parameter Ken Winter k...@sunward.org writes: When the run-time parameter transform_null_equals is on, shouldn't two variables with NULL values evaluate as equal? No. That setting does not change the runtime behavior of comparison. The only thing it does is change the literal syntax something = NULL to something IS NULL. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null comparisons and the transform_null_equals run-time parameter
Steve ~ Thanks for the great tips. See comments below. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Steve Atkins Sent: Saturday, June 05, 2010 1:04 PM To: PostgreSQL pg-general List Subject: Re: [GENERAL] Null comparisons and the transform_null_equals run- time parameter On Jun 5, 2010, at 9:46 AM, Ken Winter wrote: In case anybody else needs this functionality, let me offer a family of functions that do comparisons that treat NULL as a real value (rather than as unknown). For example: You should take a look at is not distinct from. Wow, that's the same wheel I just reinvented! Works fine in my test functions. Guess I can scrap my home-brewed functions that do the same thing. The reason I need this is that I'm writing functions to test my database programming (triggers, rules, etc), and these tests sometimes need to treat Null as just another value in doing test comparisons. You also might find http://pgtap.org/ useful. Yes indeed, looks very promising. I see there are also test frameworks available at http://en.dklab.ru/lib/dklab_pgunit/ and http://www.epictest.org/ . This will take me a while to digest, but hopefully I'll be able to scrap, or greatly simplify, my elaborate homemade test functions too. It's always a joy to be able to replace my own code with somebody else's. ~ Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Null comparisons and the transform_null_equals run-time parameter
When the run-time parameter transform_null_equals is on, shouldn't two variables with NULL values evaluate as equal? They don't seem to. At the bottom of this message is a little test function. It tries all comparisons of NULL-valued variables and NULL constants, both before and after turning transform_null_equals on. Here's what it returns: transform_null_equals OFF: NULL = NULL - Unknown transform_null_equals OFF: v1 = NULL - Unknown transform_null_equals OFF: NULL = v2 - Unknown transform_null_equals OFF: v1 = v2 - Unknown transform_null_equals ON: NULL = NULL - True transform_null_equals ON: v1 = NULL - True transform_null_equals ON: NULL = v2 - True transform_null_equals ON: v1 = v2 - Unknown My problem is in the last line: Comparing two NULL variables produces an unknown result. I need it to evaluate as True, like the preceding three comparisons. Any suggestions? ~ TIA ~ Ken CREATE OR REPLACE FUNCTION test() RETURNS varchar AS $BODY$ DECLARE v1 VARCHAR; v2 VARCHAR; s VARCHAR := ''; BEGIN v1 := Null; v2 := Null; IF NULL = NULL THEN s := s || 'transform_null_equals OFF: NULL = NULL - True '; ELSIF NOT (NULL = NULL) THEN s := s || 'transform_null_equals OFF: NULL = NULL - False '; ELSE s := s || 'transform_null_equals OFF: NULL = NULL - Unknown '; END IF; s := s || chr(10); IF v1 = NULL THEN s := s || 'transform_null_equals OFF: v1 = NULL - True '; ELSIF NOT (v1 = NULL) THEN s := s || 'transform_null_equals OFF: v1 = NULL - False '; ELSE s := s || 'transform_null_equals OFF: v1 = NULL - Unknown '; END IF; s := s || chr(10); IF NULL = v2 THEN s := s || 'transform_null_equals OFF: NULL = v2 - True '; ELSIF NOT (NULL = v2) THEN s := s || 'transform_null_equals OFF: NULL = v2 - False '; ELSE s := s || 'transform_null_equals OFF: NULL = v2 - Unknown '; END IF; s := s || chr(10); IF v1 = v2 THEN s := s || 'transform_null_equals OFF: v1 = v2 - True '; ELSIF NOT v1 = v2 THEN s := s || 'transform_null_equals OFF: v1 = v2 - False '; ELSE s := s || 'transform_null_equals OFF: v1 = v2 - Unknown '; END IF; s := s || chr(10); SET LOCAL transform_null_equals TO ON; IF NULL = NULL THEN s := s || 'transform_null_equals ON: NULL = NULL - True '; ELSIF NOT (NULL = NULL) THEN s := s || 'transform_null_equals ON: NULL = NULL - False '; ELSE s := s || 'transform_null_equals ON: NULL = NULL - Unknown '; END IF; s := s || chr(10); IF v1 = NULL THEN s := s || 'transform_null_equals ON: v1 = NULL - True '; ELSIF NOT (v1 = NULL) THEN s := s || 'transform_null_equals ON: v1 = NULL - False '; ELSE s := s || 'transform_null_equals ON: v1 = NULL - Unknown '; END IF; s := s || chr(10); IF NULL = v2 THEN s := s || 'transform_null_equals ON: NULL = v2 - True '; ELSIF NOT (NULL = v2) THEN s := s || 'transform_null_equals ON: NULL = v2 - False '; ELSE s := s || 'transform_null_equals ON: NULL = v2 - Unknown '; END IF; s := s || chr(10); IF v1 = v2 THEN s := s || 'transform_null_equals ON: v1 = v2 - True '; ELSIF NOT v1 = v2 THEN s := s || 'transform_null_equals ON: v1 = v2 - False '; ELSE s := s || 'transform_null_equals ON: v1 = v2 - Unknown '; END IF; RETURN s; END; $BODY$ LANGUAGE plpgsql VOLATILE; SELECT test();
Re: [GENERAL] ROLLBACK in a function
Craig ~
Your rollback solution works! For anyone who wants to use Craig's solution,
the new version of my example (with the test code omitted to clarify the
structural pattern) is:
CREATE OR REPLACE FUNCTION tests() RETURNS varchar AS
$BODY$
DECLARE
errs VARCHAR = '';
-- Test declarations: omitted
BEGIN
-- Test code: omitted
RAISE EXCEPTION '';
EXCEPTION
WHEN raise_exception THEN
RETURN errs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
By contrast, the old version (with the test code omitted) was:
CREATE OR REPLACE FUNCTION tests() RETURNS varchar AS
$BODY$
DECLARE
errs VARCHAR = '';
-- Test declarations: omitted
BEGIN
RAISE EXCEPTION '';
EXCEPTION
WHEN raise_exception THEN
-- Test code: omitted
RETURN errs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
To clarify the purpose of this structure, for anyone who may want to do the
same thing:
This is a framework for running test code whose job is to run one or more
tests against the database. Every test that fails is supposed to simply add
a diagnostic message to the errs string, which is then returned. Every
test that is passed is to do nothing. All tests need to be designed so that
they do not cause an exception that causes the function to abort. And then,
at the end, the framework of the function returns errs and rolls back all
data changes it has made.
~ Thanks!
~ Ken
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Craig Ringer
Sent: Sunday, May 23, 2010 8:48 PM
To: Ken Winter
Cc: PostgreSQL pg-general List
Subject: Re: [GENERAL] ROLLBACK in a function
On 24/05/10 02:51, Ken Winter wrote:
How can I write a PL/PgSQL function that rolls back every database
change it has done?
Have it raise an exception, causing the surrounding transaction to
terminate with an error.
Another function calling yours can still catch the exception and handle
it, so it's possible for your function's changes to be applied, but only
if someone explicitly and intentionally catches and ignores the error.
BEGIN
RAISE EXCEPTION '';
EXCEPTION
WHEN raise_exception THEN
SET search_path TO public, tests;
-- Test code:
SELECT count(*) INTO old_count FROM person_i;
INSERT INTO person (last_name) VALUES ('_test');
SELECT count(*) INTO new_count FROM person_i;
IF NOT new_count = old_count + 1 THEN
errs := errs || ': Failed to insert into ''person''';
END IF;
The function runs OK, but it does not roll back the actions it did (in
this case, the INSERT).
You're on the right track, but you've got the code that changes the
database in the exception handler _after_ the exception is thrown.
You want it in the BEGIN block _before_ the exception is thrown.
However, you're also doing this quite an expensive way.
An INSERT/UPDATE/DELETE can only fail to have any effect without
throwing an exception if if has a WHERE clause, is intercepted by a
BEFORE trigger that returns NULL, or is rewritten by a rule. If none of
those are possible, your INSERT/UPDATE/DELETE is guaranteed to work or
throw an exception.
eg (untested code, please verify before relying on it):
BEGIN
SELECT count(*) INTO old_count FROM person_i;
INSERT INTO person (last_name) VALUES ('_test');
SELECT count(*) INTO new_count FROM person_i;
IF NOT old_count + 1 = new_count THEN
RAISE EXCEPTION 'Update failed';
END IF;
EXCEPTION WHEN raise_exception THEN
errs := errs || ': Failed to insert into ''person''';
END;
But ... please consider whether you really need this check with two
expensive count(*) queries. Unless you're using BEFORE triggers that
return NULL or rewrite rules, you can trust that an INSERT without a
WHERE clause will always succeed or throw an exception.
If you *are* using triggers or rules, you should be able to use GET
DIAGNOSTICS to see the affected row count, saving yourself those
count(*) queries. See:
http://www.postgresql.org/docs/current/static/plpgsql-
statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
eg (untested code, should give you the idea):
DECLARE
rowcount INTEGER;
BEGIN
-- function body here
BEGIN
INSERT INTO person (last_name) VALUES ('_test');
GET DIAGNOSTICS rowcount = ROW_COUNT;
IF NOT rowcount = 1 THEN
RAISE EXCEPTION 'Update failed';
END IF;
EXCEPTION WHEN raise_exception THEN
errs := errs || ': Failed to insert into ''person''';
END;
END;
You *can* trick Pg into not seeing that an update has happened even when
it really has. For example, a BEFORE trigger can do its own INSERT then
return NULL to tell Pg to ignore the INSERT that invoked the trigger.
Stored functions that do INSERTs don't set the rowcount either. If
you're not doing anything like that, though
[GENERAL] ROLLBACK in a function
How can I write a PL/PgSQL function that rolls back every database change it
has done?
I'm about to write a set of database test functions. Each function needs to
do some INSERT, UPDATE, or DELETE actions, test whether they had their
intended effect, and then roll back the test changes to restore the database
to its prior state. Here's a simple example:
CREATE OR REPLACE FUNCTION tests.person() RETURNS varchar AS
$BODY$
DECLARE
errs VARCHAR = '';
-- Test declarations:
old_count BIGINT;
new_count BIGINT;
BEGIN
-- RAISE EXCEPTION '';
-- EXCEPTION
-- WHEN raise_exception THEN
SAVEPOINT s;
SET search_path TO public, tests;
-- Test code:
SELECT count(*) INTO old_count FROM person_i;
INSERT INTO person (last_name) VALUES ('_test');
SELECT count(*) INTO new_count FROM person_i;
IF NOT new_count = old_count + 1 THEN
errs := errs || ': Failed to insert into
''person'';
END IF;
ROLLBACK TO SAVEPOINT s;
RETURN errs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
When I run this function, I get this error:
ERROR: SPI_execute_plan failed executing query SAVEPOINT s:
SPI_ERROR_TRANSACTION
SQL state: XX000
Context: PL/pgSQL function person line 11 at SQL statement
I tried another way to make the rollback happen: The last paragraph of
http://www.postgresql.org/docs/8.3/static/plpgsql-structure.html said a
block containing an EXCEPTION clause effectively forms a subtransaction that
can be rolled back without affecting the outer transaction. So I rewrote
the test function like this:
CREATE OR REPLACE FUNCTION tests.person() RETURNS varchar AS
$BODY$
DECLARE
errs VARCHAR = '';
-- Test declarations:
old_count BIGINT;
new_count BIGINT;
BEGIN
RAISE EXCEPTION '';
EXCEPTION
WHEN raise_exception THEN
SET search_path TO public, tests;
-- Test code:
SELECT count(*) INTO old_count FROM person_i;
INSERT INTO person (last_name) VALUES
('_test');
SELECT count(*) INTO new_count FROM person_i;
IF NOT new_count = old_count + 1 THEN
errs := errs || ': Failed to insert
into ''person''';
END IF;
RETURN errs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
The function runs OK, but it does not roll back the actions it did (in this
case, the INSERT).
~ TIA
~ Ken
Re: [GENERAL] 'Infinity' in date columns?
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Tom Lane ... Ken Winter k...@sunward.org writes: The documentation at http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to say that the special value 'infinity' (later than all other time stamps) should work for an date-time column, and the type date is listed as among the date-time data types. But I can't get 'infinity' to work for columns of type date. You seem to be carefully reading around the middle column in Table 8-13, which specifically shows that infinity doesn't work for type date. Oh, duh, right you are... Now, if you were to update to Postgres 8.4, it *would* work. I'll see if I can get my host to do that. ~ Thanks ~ Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 'Infinity' in date columns?
The documentation at http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to say that the special value 'infinity' (later than all other time stamps) should work for an date-time column, and the type date is listed as among the date-time data types. But I can't get 'infinity' to work for columns of type date. Here's a test case: CREATE TABLE _test ( timestampx timestamp without time zone DEFAULT 'infinity'::timestamp without time zone, datex date DEFAULT 'infinity'::timestamp without time zone ) ); INSERT INTO _test DEFAULT VALUES; SELECT * FROM _test; This returns (as viewed through pgAdmin III): timestampx datex --- - infinity When I tried to declared the datex column like this: datex date DEFAULT 'infinity'::date I got this: ERROR: invalid input syntax for type date: infinity Is there any way to get 'infinity' to work for a date column? Or any other way to get the equivalent functionality in a date column? ~ TIA ~ Ken
Re: [GENERAL] Problem with execution of an update rule
Mark this one solved. I finally stumbled across an old, forgotten e-mail thread from 2006 where Tom Lane solved exactly this problem. See http://archives.postgresql.org/pgsql-general/2006-02/msg01039.php. ~ Thanks again, Tom! ~ Ken -Original Message- From: Ken Winter [mailto:k...@sunward.org] Sent: Friday, January 22, 2010 7:00 PM To: 'PostgreSQL pg-general List' Subject: Problem with execution of an update rule Im trying to implement a history-keeping scheme using PostgreSQL views and update rules. My problem is that one of the commands in one of my crucial update rules apparently never executes. Briefly, the history-keeping scheme involves: * Two tables: an h table that contains the columns for which we want to preserve a full history of all updates, and an i table that contains columns whose history we don't want to preserve. * A view of the two tables, showing all the columns of the h and I tables. * A set of rules that makes the view behave like a fully updatable table, while invisibly preserving a copy of the record as it existed prior to each update. The problem rule (see example in the PS below) is the one that fires when the user issues a SQL UPDATE against the view. This rule fires if the UPDATE has changed any column value. It is supposed to execute three commands: 1. Insert a new record into the _h table, containing the old values of the record being updated. This is the record that preserves the prior state of the record. 2. Update the existing h table record with the new values. 3. Update the existing i table record with the new values. The problem is that command 1 apparently never executes. That is, in response to an UPDATE against the view, a new h table record is NOT inserted - even though data changes in both the h and the i table are successfully recorded, and no error messages occur. I have tried changing the order of the 3 commands in the rule - no effect. Can you tell me what's wrong with this picture? ~ TIA ~ Ken PS: This example involves a view named people, an h table named people_h (including columns first_name and last_name), an i table named people_i (including column birth_date), a sequence-assigned identifier people_id in both tables, some effective and expiration timestamps in people_h, and some rules including this troublesome one: CREATE OR REPLACE RULE on_update_2_preserve AS ON UPDATE TO people WHERE ( (OLD.people_id NEW.people_id OR (OLD.people_id IS NULL AND NEW.people_id IS NOT NULL) OR (OLD.people_id IS NOT NULL AND NEW.people_id IS NULL )) OR (OLD.effective_date_and_time NEW.effective_date_and_time OR (OLD.effective_date_and_time IS NULL AND NEW.effective_date_and_time IS NOT NULL) OR (OLD.effective_date_and_time IS NOT NULL AND NEW.effective_date_and_time IS NULL )) OR (OLD.first_name NEW.first_name OR (OLD.first_name IS NULL AND NEW.first_name IS NOT NULL) OR (OLD.first_name IS NOT NULL AND NEW.first_name IS NULL )) OR (OLD.last_name NEW.last_name OR (OLD.last_name IS NULL AND NEW.last_name IS NOT NULL) OR (OLD.last_name IS NOT NULL AND NEW.last_name IS NULL )) OR (OLD._action NEW._action OR (OLD._action IS NULL AND NEW._action IS NOT NULL) OR (OLD._action IS NOT NULL AND NEW._action IS NULL )) OR (OLD.birth_date NEW.birth_date OR (OLD.birth_date IS NULL AND NEW.birth_date IS NOT NULL) OR (OLD.birth_date IS NOT NULL AND NEW.birth_date IS NULL ))) ) DO ( /* Copy the old values to a new record. Expire it either now (if no effective date was provided) or whenever the update query specifies.*/ INSERT INTO people_h ( people_id, first_name, last_name, effective_date_and_time, expiration_date_and_time) VALUES ( OLD.people_id, OLD.first_name, OLD.last_name, OLD.effective_date_and_time, NEW.effective_date_and_time) ; /* Update the current H record and make it effective as of either now (if no effective date was provided) or whenever the update query specifies.*/ UPDATE people_h SET people_id = NEW.people_id, first_name = NEW.first_name, last_name = NEW.last_name, _action = 'preserved', effective_date_and_time = CASE WHEN NEW.effective_date_and_time = OLD.effective_date_and_time THEN CURRENT_TIMESTAMP ELSE NEW.effective_date_and_time END WHERE people_id = OLD.people_id AND effective_date_and_time = OLD.effective_date_and_time ; /* Update I table. */ UPDATE people_i SET people_id = NEW.people_id, birth_date = NEW.birth_date, WHERE people_id = OLD.people_id; SELECT public.debug
[GENERAL] Problem with execution of an update rule
I'm trying to implement a history-keeping scheme using PostgreSQL views and update rules. My problem is that one of the commands in one of my crucial update rules apparently never executes. Briefly, the history-keeping scheme involves: * Two tables: an h table that contains the columns for which we want to preserve a full history of all updates, and an i table that contains columns whose history we don't want to preserve. * A view of the two tables, showing all the columns of the h and I tables. * A set of rules that makes the view behave like a fully updatable table, while invisibly preserving a copy of the record as it existed prior to each update. The problem rule (see example in the PS below) is the one that fires when the user issues a SQL UPDATE against the view. This rule fires if the UPDATE has changed any column value. It is supposed to execute three commands: 1. Insert a new record into the _h table, containing the old values of the record being updated. This is the record that preserves the prior state of the record. 2. Update the existing h table record with the new values. 3. Update the existing i table record with the new values. The problem is that command 1 apparently never executes. That is, in response to an UPDATE against the view, a new h table record is NOT inserted - even though data changes in both the h and the i table are successfully recorded, and no error messages occur. I have tried changing the order of the 3 commands in the rule - no effect. Can you tell me what's wrong with this picture? ~ TIA ~ Ken PS: This example involves a view named people, an h table named people_h (including columns first_name and last_name), an i table named people_i (including column birth_date), a sequence-assigned identifier people_id in both tables, some effective and expiration timestamps in people_h, and some rules including this troublesome one: CREATE OR REPLACE RULE on_update_2_preserve AS ON UPDATE TO people WHERE ( (OLD.people_id NEW.people_id OR (OLD.people_id IS NULL AND NEW.people_id IS NOT NULL) OR (OLD.people_id IS NOT NULL AND NEW.people_id IS NULL )) OR (OLD.effective_date_and_time NEW.effective_date_and_time OR (OLD.effective_date_and_time IS NULL AND NEW.effective_date_and_time IS NOT NULL) OR (OLD.effective_date_and_time IS NOT NULL AND NEW.effective_date_and_time IS NULL )) OR (OLD.first_name NEW.first_name OR (OLD.first_name IS NULL AND NEW.first_name IS NOT NULL) OR (OLD.first_name IS NOT NULL AND NEW.first_name IS NULL )) OR (OLD.last_name NEW.last_name OR (OLD.last_name IS NULL AND NEW.last_name IS NOT NULL) OR (OLD.last_name IS NOT NULL AND NEW.last_name IS NULL )) OR (OLD._action NEW._action OR (OLD._action IS NULL AND NEW._action IS NOT NULL) OR (OLD._action IS NOT NULL AND NEW._action IS NULL )) OR (OLD.birth_date NEW.birth_date OR (OLD.birth_date IS NULL AND NEW.birth_date IS NOT NULL) OR (OLD.birth_date IS NOT NULL AND NEW.birth_date IS NULL ))) ) DO ( /* Copy the old values to a new record. Expire it either now (if no effective date was provided) or whenever the update query specifies.*/ INSERT INTO people_h ( people_id, first_name, last_name, effective_date_and_time, expiration_date_and_time) VALUES ( OLD.people_id, OLD.first_name, OLD.last_name, OLD.effective_date_and_time, NEW.effective_date_and_time) ; /* Update the current H record and make it effective as of either now (if no effective date was provided) or whenever the update query specifies.*/ UPDATE people_h SET people_id = NEW.people_id, first_name = NEW.first_name, last_name = NEW.last_name, _action = 'preserved', effective_date_and_time = CASE WHEN NEW.effective_date_and_time = OLD.effective_date_and_time THEN CURRENT_TIMESTAMP
Re: [GENERAL] Array in nested query
Thanks, Osvaldo and Fernando - your solution works! -Original Message- From: Osvaldo Kussama [mailto:osvaldo.kuss...@gmail.com] Sent: Saturday, February 14, 2009 8:24 PM To: Ken Winter Subject: Re: [GENERAL] Array in nested query 2009/2/14 Ken Winter k...@sunward.org: I'm trying to look up the columns in a constraint in pg_catalog (of PostgreSQL 8.0.x). I can't figure out how to join the elements of the array that lists the 'attnum's of the columns in the table to the 'conkey' array in the constraint definition (see http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html and http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html ). The problem is in the last line of this query: SELECT a.attname AS name FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a WHERE t.oid = 3626912 AND a.attrelid = t.oid AND a.attnum = any (select conkey FROM pg_catalog.pg_constraint WHERE oid = 3708025) I have tried all the variations on this syntax that I can think of, after plowing through all the documentation of arrays I can find in http://www.postgresql.org/docs/8.0/static/index.html, and none of them work. I've a similar problem. Try explicit cast and an extra parenthesis: SELECT a.attname AS name FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a WHERE t.oid = 3626912 AND a.attrelid = t.oid AND a.attnum = any ((select conkey FROM pg_catalog.pg_constraint WHERE oid = 3708025)::smallint[]); Osvaldo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with non-unique constraint names
The following query against my PostgreSQL 8.0.x 'public' schema: SELECT fkc.table_name as fk_table_name, fkc.constraint_name AS fk_constraint_name FROM information_schema.table_constraints fkc WHERE fkc.constraint_schema = 'public' AND fkc.constraint_type = 'FOREIGN KEY' AND ( SELECT count(*) FROM information_schema.table_constraints dup WHERE dup.constraint_schema = 'public' AND dup.constraint_name = fkc.constraint_name ) 1 Returns this: fk_table_name fk_constraint_name - -- site_role___site_permission fk_site_rol_site_role_site_rol site_role_criterion fk_site_rol_site_role_site_rol teachingfk_teaching_teaching__teaching teaching_package_distribution fk_teaching_teaching__teaching Since constraint name uniqueness is a SQL standard, I was surprised that PostgreSQL doesn't enforce it. I found one thread (from 2002) in the archive that discusses this, but the thread ended inconclusively. And I just discovered a warning at http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html that these names aren't necessarily unique. This is more than just a quibble about standards. When there are duplicate constraint names, I'm having trouble writing queries against the information_schema that accurately return the FK columns of all the FKs that reference a given table. For example, this query gives some details of the two constraints named 'fk_teaching_teaching__teaching': SELECT fkc.table_name as fk_table_name, fkc.constraint_name AS fk_constraint_name FROM information_schema.table_constraints fkc WHERE fkc.table_schema = 'public' AND fkc.constraint_name = 'fk_teaching_teaching__teaching' It returns: fk_table_name fk_constraint_name - -- teachingfk_teaching_teaching__teaching teaching_package_distribution fk_teaching_teaching__teaching So far so good. But what table is referenced by the FK in the 'teaching' table? One way to bridge across is via the info schema view 'referential_constraints': SELECT fkc.table_name as fk_table_name, fkc.constraint_name AS fk_constraint_name, pkc.constraint_name AS pk_constraint_name, pkc.table_name as pk_table_name FROM information_schema.table_constraints fkc, information_schema.referential_constraints r, information_schema.table_constraints pkc WHERE fkc.table_schema = 'public' AND fkc.constraint_name = 'fk_teaching_teaching__teaching' AND fkc.table_name = 'teaching' AND r.constraint_schema = fkc.constraint_schema AND r.constraint_name = fkc.constraint_name AND pkc.constraint_schema = r.unique_constraint_schema AND pkc.constraint_name = r.unique_constraint_name This returns: fk_table_name fk_constraint_name pk_constraint_name pk_table_name - - teaching fk_teaching_teaching pk_teaching_type_lov teaching_type_lov __teaching teaching fk_teaching_teaching pk_teaching_package teaching_package __teaching The second row returned is bogus: there is no FK from 'teaching' to 'teaching package'. The problem is that information_schema.referential_constraints does not contain a table_name column identifying the table that contains the FK, so there is no way to specify which of the redundantly named constraints you want. The other way to find the referenced table of a FK constraint is via information_schema.constraint_column_usage, but it has the same problem: the FK constraint you want to follow can be identified in constraint_column_usage only by its schema and its name, which ain't enough when constraints are redundantly named. I shudder at the horrors that might ensue if PK constraints also had duplicate names! I can do what I need with queries on pk_catalog, but I would rather do it through information_schema because it's based on a broader standard. Is there some way that I have missed to do it that way? ~ TIA ~ Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Array in nested query
I'm trying to look up the columns in a constraint in pg_catalog (of PostgreSQL 8.0.x). I can't figure out how to join the elements of the array that lists the 'attnum's of the columns in the table to the 'conkey' array in the constraint definition (see http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html and http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html ). The problem is in the last line of this query: SELECT a.attname AS name FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a WHERE t.oid = 3626912 AND a.attrelid = t.oid AND a.attnum = any (select conkey FROM pg_catalog.pg_constraint WHERE oid = 3708025) I have tried all the variations on this syntax that I can think of, after plowing through all the documentation of arrays I can find in http://www.postgresql.org/docs/8.0/static/index.html, and none of them work. Any ideas? ~ TIA ~ Ken
Re: [GENERAL] Extracting data from deprecated MONEY fields
Right you are, Tom! In case anyone else is facing the same migration, pasted in below is a pl/pgsql function that does the conversion. ~ Thanks to all ~ Ken -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Saturday, June 07, 2008 11:25 AM Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields ... If you do it within plpgsql it should work. Just assign the money value to a text variable (or vice versa). regards, tom lane CREATE OR REPLACE FUNCTION public.convert_money_column(varchar, varchar, varchar) RETURNS varchar AS $BODY$ /* Converts the column given by arg 3 (in the table given by arg 2 in the schema given by arg 1) from a money type to a numeric(10,2) type, and repopulates the new column with the values from the old. Before doing that, it makes a backup of the original table, which should be deleted manually after verifying the results. */ DECLARE this_schema ALIAS FOR $1; this_table ALIAS FOR $2; this_column ALIAS FOR $3; q varchar := ''; q2 varchar := ''; rec record; this_oid oid; this_varchar varchar := ''; this_numeric numeric(10,2); n integer := 0; BEGIN q := 'CREATE TABLE ' || this_schema || '.' || this_table || '_bak' || ' AS SELECT * FROM ' || this_schema || '.' || this_table; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' ADD COLUMN ' || this_column || '_ money'; EXECUTE q; q := 'UPDATE ' || this_schema || '.' || this_table || ' SET ' || this_column || '_ = ' || this_column; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' DROP COLUMN ' || this_column || ' CASCADE'; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' ADD COLUMN ' || this_column || ' numeric(10,2)'; EXECUTE q; q := 'SELECT oid, ' || this_column || '_ AS money_column FROM ' || this_schema || '.' || this_table ; FOR rec IN EXECUTE q LOOP this_oid := rec.oid; this_varchar := rec.money_column; this_varchar := replace(this_varchar, '$', ''); this_varchar := replace(this_varchar, ',', ''); this_numeric := this_varchar; q2 := 'UPDATE ' || this_schema || '.' || this_table || ' SET ' || this_column || ' = ' || this_numeric || ' WHERE oid = ' || this_oid; EXECUTE q2; n := n + 1; END LOOP; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' DROP COLUMN ' || this_column || '_ CASCADE'; EXECUTE q; RETURN 'Did ' || n || ' records'; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extracting data from deprecated MONEY fields
Thanks, Joshua ~ What you suggest is basically what I'm trying to do. Where I'm stuck is in finding a construct (a CAST or whatever) to turn the existing money column data (directly or indirectly) into numeric. I've tried to convert a column named amount in the following ways, with the following results: CAST(amount AS numeric) - cannot cast type money to numeric CAST(amount AS numeric(10,2)) - cannot cast type money to numeric CAST(amount AS decimal) - cannot cast type money to numeric CAST(amount AS text) - cannot cast type money to text CAST(amount AS varchar) - cannot cast type money to character varying to_char(money) - function to_char(money) does not exist ~ Ken -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2008 11:22 PM To: Ken Winter Cc: PostgreSQL pg-general List Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields Ken Winter wrote: I understand from http://www.postgresql.org/docs/8.0/static/datatype-money.html that the money data type is deprecated. Money is no longer deprecated in newer releases (specifically 8.3), although I do think it would be wise to push it to numeric. I think the way to do it would be to backup the table and edit the table definition from the file. Make the money a numeric. Then reload the table from the backup. Sincerely, Joshua D. Drake -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extracting data from deprecated MONEY fields
Thanks Adrian ~ See comments at end.
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Adrian Klaver
Sent: Friday, June 06, 2008 11:49 AM
To: pgsql-general@postgresql.org
Cc: Ken Winter
Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields
On Friday 06 June 2008 8:25 am, Ken Winter wrote:
Thanks, Joshua ~
What you suggest is basically what I'm trying to do. Where I'm stuck is
in
finding a construct (a CAST or whatever) to turn the existing money
column data (directly or indirectly) into numeric. I've tried to
convert a
column named amount in the following ways, with the following results:
CAST(amount AS numeric) - cannot cast type money to numeric
CAST(amount AS numeric(10,2)) - cannot cast type money to numeric
CAST(amount AS decimal) - cannot cast type money to numeric
CAST(amount AS text) - cannot cast type money to text
CAST(amount AS varchar) - cannot cast type money to character varying
to_char(money) - function to_char(money) does not exist
I don't know if this helps.
...
**There is no simple way of doing the reverse in a locale-independent
manner,
namely casting a money value to a numeric type. If you know the currency
symbol and thousands separator you can use regexp_replace():
SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
I'm indeed trying to get rid of US$ notation.
Alas, here's what I get from trying the regexp pattern on my amount column
(type=money):
regexp_replace(amount::money::text, '[$,]', '', 'g')::numeric - cannot
cast type money to text
regexp_replace(amount::text, '[$,]', '', 'g')::numeric - cannot cast type
money to text
And if remove the cast to text, I get:
regexp_replace(amount::money, '[$,]', '', 'g')::numeric - function
regexp_replace(money, unknown, unknown, unknown) does not exist
~ Ken
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Extracting data from deprecated MONEY fields
I understand from http://www.postgresql.org/docs/8.0/static/datatype-money.html that the money data type is deprecated. So I want to convert the data from my existing money columns into new un-deprecated columns, e.g. with type decimal(10,2). But every SQL command I try tells me I can't cast or convert money data into any other type I have tried, including decimal, numeric, varchar, and text. Is there any way to do this? ~ TIA ~ Ken
[GENERAL] Converting empty input strings to Nulls
Applications accessing my PostgreSQL 8.0 database like to submit no-value date column values as empty strings rather than as Nulls. This, of course, causes this PG error: SQL State: 22007 ERROR: invalid input syntax for type date: I'm looking for a way to trap this bad input at the database level, quietly convert the input empty strings to Null, and store the Null in the date column. I tried a BEFORE INSERT OR UPDATE trigger evoking this function ... CREATE OR REPLACE FUNCTION public.empty_string_to_null() RETURNS trigger AS $BODY$ BEGIN IF CAST(NEW.birth_date AS text) = '' THEN NEW.birth_date = Null; END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ... but an empty string still evokes the error even before this function is triggered. Is there a way to convert empty strings to Nulls before the error is evoked? ~ TIA ~ Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What pg_restore does to a non-empty target database
Based on Tom Lane's response, here is version 2 of my attempt to document what pg_restore does to a target database that already contains objects. Version 2 has been limited to the case where pg_dump was run with the --column-inserts option and pg_restore is run with the --clean option. Also, when there is a possible difference between what pg_restore tries to do (i.e. the SQL actions it invokes) and what actually happens, Version 2 indicates the latter on a line labeled Exception:. Version 2 depends on the answer to the following question: Is it true that the sequence of SQL commands that is extracted into a file by: pg_restore --file=foo.sql [other arguments] foo.bak is exactly the sequence of SQL commands that will be executed in database foo by the command: pg_restore --dbname=foo [other arguments] foo.bak (assuming the same [other arguments] in both cases)? If so, having looked at the extracted SQL from several examples, I'm thinking it's a safe bet that no pg_restore DROP, DELETE, or CREATE action will ever fail (except when a DROP's target object does not exist), because pg_restore sequences the cleaning activities so they get rid of dependent objects first. Right? If so, that would seem to guarantee that every action that pg_restore tries to do in the narrative below is guaranteed to succeed, except INSERTs, which can fail only as described below. If not, I have another version of the story below that is a lot more qualified and complicated. As before, I solicit your confirmations, corrections, and additions of this document, hoping to get it to the point where my project team (and anyone else who wants it) can use it with confidence. ~ Thanks ~ Ken --- WHAT PG_RESTORE DOES Version 2 Given a pg_restore command, where: * A is the source archive file (as filtered and reordered by a ToC file, if any) produced by pg_dump with --format=t and --column-inserts options. * T is the target database. * O is a database object (table, function, etc) that exists in A and/or in C. * The pg_restore command has the --clean option, and it does not have the --table= or --trigger= or --exit-on-error or --disable-triggers options. The following are the changes that the pg_restore command will produce in database T. EFFECTS ON SCHEMA OBJECTS If object O exists in both A and T, pg_restore: * Drops T's version of O. * Creates A's version of O. If object O exists in T but not in A, pg_restore: * Leaves T's version of O unchanged. If object O exists in A but not in T, pg_restore: * Creates A's version of O. EFFECTS ON TABLES AND THEIR DATA Suppose in addition that: * Database object O is a base table. * O contains data in both archive A and target database T. If the command says --data-only, pg_restore: * Leaves T's schema definition of O unchanged. * Tries to delete all of T's O data. If this causes FK violations, the result depends row-by-row on the ON DELETE action of the FK constraint. * Tries to insert all of A's O data. The INSERT of any row that causes constraint violations or other fatal errors (see below) fails. If the command says --schema-only, pg_restore: * Drops T's version of O, which deletes T's O data as a side-effect. * Create A's version of O. * Does not try to insert any of A's O data, so O ends up empty. If the command says --data-only and --schema-only, pg_restore: * Leaves O and its data unchanged. If the command says neither --data-only nor --schema-only, pg_restore: * Drops T's version of O. * Assumes that T's O data were deleted (as a side-product of the DROP) * Creates A's version of O. * Inserts all of A's O data. EFFECTS OF DIFFERENCES BETWEEN A AND T TABLE SCHEMAS Suppose in addition that: * The pg_restore command says --data-only. * T's schema definition of table O is different from A's. If column O.C exists in T's schema but not in A's: * O.C is set to Null in all rows that pg_restore inserts. If column O.C exists in A's schema but not in T's: * The O.C values are lost from all rows that pg_restore inserts. If column O.C exists in both schemas with incompatible types: * All inserts of rows from A fail. If constraint K exists in T's schema but not in A's: * Inserts of rows from A that would violate K fail. If constraint K exists in A's schema but not in T's: * K has no effect on the insertion of rows from A. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] What pg_restore does to a non-empty target database
I need to understand, in as much detail as possible, the results that will occur when pg_restore restores from an archive file into a target database that already contains some database objects. I can't find any reference that spells this out. (The PG manual isn't specific enough.) Instead of just asking questions, I decided to make my best guess about the answers (below), and ask you to confirm, refute, and correct my guesses until this becomes a complete and accurate statement. If I have left out any conditions that would affect the results, please add them into the account. A definitive version of this story might be worth posting in some more permanent and visible place than an e-mail thread. In case it matters, I'm currently working with PostgreSQL 8.0. I don't know if the truth I'm seeking here is version-dependent. Also, I'm assuming the archive is in compressed format. I don't know how different the story would be if the archive were in a different format. ~ TIA ~ Ken Given a pg_restore command (possibly filtered and reordered by a ToC file), where: * A is the source archive file (as filtered and reordered by the ToC file, if any) * T is the target database * O is a database object (table, function, etc) that exists in A and/or in C The following are the changes that the pg_restore will produce in T. If object O exists in both A and T: If the command says --clean: T's version of O is dropped A's version of O is created Else: T's version of O is left unchanged If object O exists in T but not in A: T's version of O is left unchanged If object O exists in A but not in T: A's version is created Suppose in addition that O is a data object (a table or sequence) that is defined by the database schema and contains data in both A and T. If the command says --data-only: T's schema definition of O is left unchanged T's O data are deleted A's O data are inserted If the command says --schema-only: T's schema definition of O is dropped T's O data are deleted (as a side-product of the drop) A's schema definition of O is created No O data are inserted If the command says --data-only and --schema-only: T's schema definition of O is left unchanged T's O data are left unchanged In other words, nothing changes If the command says neither --data-only nor --schema-only: T's schema definition of O is dropped T's O data are deleted (as a side-product of the drop) A's schema definition of O is created A's O data are inserted In other words, A's version of O entirely replaces T's version Suppose in addition that the command says --data-only, it doesn't say --exit-on-error, and T's schema definition of O is different from A's. If T's schema includes a column O.C that does not exist in A's schema: A's O data are inserted, and O.C is Null in all rows If A's schema includes a column O.C that does not exist in T's schema: A's O data are inserted, but A's values of O.C are lost If T's schema includes a constraint K that does not exist in A's schema: A's O data are inserted, except for those that violate K If A's schema includes a constraint K that does not exist in T's schema: A's O data are all inserted ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Problem with pg_dump?
When I do a pg_dump from an 8.1 database (with options schema-only, no-owner, and plain format), the dump file includes the following: -- -- TOC entry 1623 (class 1259 OID 17618) -- Dependencies: 5 1624 -- Name: transaction_transaction_id_seq; Type: SEQUENCE; Schema: public; -- Owner: - -- CREATE SEQUENCE transaction_transaction_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 3403 (class 0 OID 0) -- Dependencies: 1623 -- Name: transaction_transaction_id_seq; Type: SEQUENCE OWNED BY; Schema: -- public; Owner: - -- ALTER SEQUENCE transaction_transaction_id_seq OWNED BY transaction.transaction_id; When I try to execute the dump script, the second command produces this error: ERROR: syntax error at or near OWNED as well it should, since the OWNED clause is not part of the ALTER SEQUENCE syntax (see http://www.postgresql.org/docs/8.1/static/sql-altersequence.html), which is: ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] ALTER SEQUENCE name SET SCHEMA new_schema (Full disclosure: I am actually getting this error when working through the tool PG Lightning Assistant (see http://www.amsoftwaredesign.com/lightning_admin.php). I assume that PGLA uses pg_dump to execute its backup database command. However, if no one else in PostgreSQL-land has encountered this problem, presumably it is a bug in PGLA, not in pg_dump, and I will take my problem there.) If it is a pg_dump bug, are there plans to fix it? And, meanwhile, any workarounds other than manually editing the pg_dump DDL file? ~ TIA ~ Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with pg_dump?
Everything I'm doing involves only 8.1. I don't have any 8.2 instances at all... Oops, just I just got a message from my tool's developer that the tool uses the 8.2 pg_dump no matter what actual PostgreSQL version it's working on. Sigh. ~ Thanks ~ Ken -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Friday, January 04, 2008 3:35 PM To: Ken Winter Cc: PostgreSQL pg-general List Subject: Re: [GENERAL] Problem with pg_dump? Ken Winter [EMAIL PROTECTED] writes: When I do a pg_dump from an 8.1 database (with options schema-only, no-owner, and plain format), the dump file includes the following: ALTER SEQUENCE transaction_transaction_id_seq OWNED BY transaction.transaction_id; Don't use 8.2 pg_dump if you are intending to reload the output into 8.1. In general, pg_dump's output is not promised to be backward compatible with older server versions. (Full disclosure: I am actually getting this error when working through the tool PG Lightning Assistant (see http://www.amsoftwaredesign.com/lightning_admin.php). There are some helpful tools that seem not to be aware that pg_dump and server versions are closely linked. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] How to uninstall the geometry package?
While installing PostgreSQL (8.1) recently, I was offered the option to install the geometry functions. Why not? I said to myself, and to the installer I said yes. Well, the answer to Why not? is that I have a couple hundred functions that I'll probably never use junking up my installation. The install was from the Win installer package postgresql-8.1.msi. My question is: Is there any setup routine or something that I can run to get rid of all the geometry objects, or is the only way to do this to completely uninstall and reinstall PostgreSQL? ~ TIA ~ Ken
[GENERAL] How to convert money columns to numeric?
I want to convert a column named amount, currently of type money, to type numeric(10,2). When I try to do this using: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2); I get: PostgreSQL Error Code: (1) ERROR: column amount cannot be cast to type pg_catalog.numeric So then I figure I need to do it with SQL of the form: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING expression; But I can't find a conversion function or operator that will accept a money column as input. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING to_number(amount, '.99'); Evokes this error message: PostgreSQL Error Code: (1) ERROR: function to_number(money, unknown) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. And I can't seem to cast a money column into anything else. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING cast(amount as numeric); Evokes: PostgreSQL Error Code: (1) ERROR: column amount cannot be cast to type pg_catalog.numeric So I'm fresh out of ideas - other than dropping and recreating the column, which would lose a lot of data. ~ TIA ~ Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Disabling and enabling constraints and triggers to make pg_restore work
Tom et al ~ I understand that this is all a risky business. So maybe someone can tell me a safer way to accomplish what I was trying to accomplish with pg_dump and data-only pg_restore. It's a basic bit of database administration work. I'm trying to establish two (or if necessary more) instances of the same database - a production instance and a development instance - and a change management process for coordinating them. As you can probably guess: 1. The production instance is the one the users are actually using. Its data are The Truth. 2. The development instance is where design changes (to tables, procedures, and all other database objects) are developed, tested, and readied to go into production. Its data are of no value except for testing purposes. 3. The crucial purpose of the change management process is to put into production each new release of the database design. The required outcome is that the new design (from the development instance) be up and running in the production instance, managing the production data (from the production instance), which have been preserved without loss. (Of course, certain changes in the new design - dropping a table, for example - will cause certain data to be lost. That's not the problem I'm wrestling with here.) So, the process I have in mind goes like this: 1. At the start of a release cycle, drop everything from the development instance, and copy the schema (with the production data if you want) from the production instance into the development instance. 2. During the release cycle, the users use the production instance (including modifying the data in it), and the developers do their work (which is modifying the design) in the development instance. The developers can do whatever they want to the data in the development instance. 3. At the end of the release cycle, empty all the data from the development instance, shut down the production instance (or at least write-lock up its data), and copy the production data (data only) into the development instance. Then shut down the production instance to users, drop everything in the production instance, copy everything (data + schema) from development into production, and reopen it to users. Note that step 3 also accomplishes step 1 of the next release cycle, so as soon as step 3 is done, the users can go on using the data and the developers can begin developing the next release. It seemed to me that the PostgreSQL utilities for accomplishing this process would be pg_dump and pg_restore. And I've been successful using them - except for that point in step 3 where I need to do a data-only restore from production into development. Then I run into the FK violations (when a FK table gets restored before its PK table) that led me to start this discussion thread. So, to say it again: What I *have* to accomplish is to separate my production environment from my development environment, and periodically to move the new version of the design from development into production. I've spelled out my whole approach from top to bottom in hopes that someone can suggest something at some level (different process? different utilities? different something else?) that will make this possible with PostgreSQL. There must be thousands of DBAs out there who have solved this problem, and I hope one of you can tell me how to join your happy ranks. ~ TIA ~ Ken -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Sunday, August 06, 2006 11:35 AM To: [EMAIL PROTECTED] Cc: PostgreSQL pg-general List Subject: Re: [GENERAL] Disabling and enabling constraints and triggers to make pg_restore work Ken Winter [EMAIL PROTECTED] writes: I'm trying to do a data-only pg_restore. I'm running into a roadblock whenever the restore tries to populate a table with a foreign key before it has populated the primary key table that it refers to: This violates the FK constraint, which aborts the restore. The simplest answer is don't do that. A full restore (schema+data) will work correctly. A data-only restore cannot hope to guarantee referential integrity, other than by dropping and later recreating all the FK constraints, and there's a fatal flaw in that plan: what if it doesn't know about all the FK constraints that are in the database it's trying to load into? There could be permissions problems too, if you're trying to do it as non-superuser. Someone on that forum suggested update pg_catalog.pg_class set relchecks=0 where relname ='mytab' to disable and update pg_catalog.pg_class set relchecks=1 where relname ='mytab' to re-enable. But to write to pg_catalog you apparently need to be a superuser, which alas I'm not. You should certainly not do anything as risky as messing with relchecks by hand --- there was a case just a couple weeks ago where someone destroyed
[GENERAL] Disabling and enabling constraints and triggers to make pg_restore work
Im trying to do a data-only pg_restore. Im running into a roadblock whenever the restore tries to populate a table with a foreign key before it has populated the primary key table that it refers to: This violates the FK constraint, which aborts the restore. Given the complexity of the database Im trying to restore, the prospect of having to manual order the restore process so that all PK tables are always populated before all FK tables that reference them is daunting. Even if I did that, I dont think that would handle recursive relationships, where a FK refers to its own tables PK. What I need is a way to disable FKs, so I can then restore all the data, and then re-enable the FKs. I first looked for something like ALTER TABLE mytab DISABLE CONSTRAINT mycon and ALTER TABLE mytab ENABLE CONSTRAINT mycon à la Oracle. I finally found a French PostgreSQL forum (http://www.postgresqlfr.org/?q=node/156#comment) that says theres no such thing in PostgreSQL. Someone on that forum suggested update pg_catalog.pg_class set relchecks=0 where relname =mytab to disable and update pg_catalog.pg_class set relchecks=1 where relname =mytab to re-enable. But to write to pg_catalog you apparently need to be a superuser, which alas I'm not. I also have some triggers that I think I may need to be able to disable. pg_restore does have an option to do that, but according to http://www.postgresql.org/docs/8.1/static/app-pgrestore.html this option is available to superusers only. (Perhaps I could write a program that drops all my FKs and triggers, and a second program that recreates them after the data restore is complete. But that seems a rather brutal and scary way to patch up a gap in the PostgreSQL utilities.) Any suggestions? ~ TIA ~ Ken Winter ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Confused about a function returning SETOF
Well, duh! Thank you. I could have stared at it for hours without seeing
it...
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 21, 2006 11:12 PM
To: [EMAIL PROTECTED]
Cc: PostgreSQL pg-general List
Subject: Re: [GENERAL] Confused about a function returning SETOF
Ken Winter [EMAIL PROTECTED] writes:
select lov_personinorganization_role_status('Student','Applicant');
ERROR: set-valued function called in context that cannot accept a set
You need to write
select * from
lov_personinorganization_role_status('Student','Applicant');
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Confused about a function returning SETOF
I have the following function:
CREATE or REPLACE FUNCTION public.lov_personinorganization_role_status(
varchar,
varchar)
RETURNS SETOF person_status_in_organization AS
$BODY$
DECLARE
rec person_status_in_organization%ROWTYPE;
role ALIAS FOR $1;
from_status ALIAS FOR $2;
BEGIN
FOR rec IN
SELECT * FROM person_status_in_organization
lov,
status_in_organization_change_lov res
WHERE res.role
= role
AND res.from_status = from_status
AND lov.role = res.role
AND
lov.status = res.to_status
ORDER BY lov.display_order
LOOP
RETURN NEXT rec;
END LOOP; -- FOR rec IN person_status_in_organization
RETURN;
END; -- of lov_personinorganization_role_status
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Where person_status_in_organization is a table.
When I try to invoke it with:
select lov_personinorganization_role_status('Student','Applicant');
I get the error message:
ERROR: set-valued function called in context that
cannot accept a set
CONTEXT: PL/pgSQL function lov_personinorganization_role_status
line 15 at return next
Where have I gone astray?
~ TIA
~ Ken
Re: [GENERAL] Another perplexity with PG rules
Tom ~ Thanks ever so much for - again - helping me get unstuck. See comments and results inserted below. ~ Ken -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Sunday, February 26, 2006 1:47 PM To: [EMAIL PROTECTED] Cc: PostgreSQL pg-general List Subject: Re: [GENERAL] Another perplexity with PG rules Ken Winter [EMAIL PROTECTED] writes: After trying about a million things, I'm wondering about the meaning of OLD. as the actions in a rule are successively executed. What I have done assumes that: ... (b) The OLD. values that appear in the second (INSERT) action in the rule are not changed by the execution of the first (UPDATE) rule. I believe this is mistaken. OLD is effectively a macro for the existing row(s) satisfying the rule's WHERE clause. You've got two problems here --- one is that the UPDATE may have changed the data in those rows, and the other is that the UPDATE may cause them to not satisfy the WHERE clause anymore. I was afraid of this. Your conclusions do seem to fit my results. (c) Whatever the truth of the above assumptions, the second (INSERT) action in the 'on_update_2_preserve_h' rule should insert SOMEthing. See above. If no rows remain satisfying WHERE, nothing will happen. Yep, that's what was happening. How to make this whole thing do what is required? I'd suggest seeing if you can't do the INSERT first then the UPDATE. This may require rethinking which of the two resulting rows is the historical one and which the updated one, but it could probably be made to work. Yes, I had already had it working with such a scheme. It expired the existing record, and then inserted a new record with the updated values. However this scheme seemed to be causing troubles with other triggers on the base tables. That's why I was trying to recast it into a scheme that updated the existing record and then inserted a new record containing the old data. Also, you might think about keeping the historical info in a separate table (possibly it could be an inheritance child of the master table). This would make it easier to distinguish the historical and current info when you need to. I've been striving mightily to avoid taking this path, because it threatens to hopelessly complicate my foreign keys. Lastly, I'd advise using triggers not rules wherever you possibly can. In particular, generation of the historical-log records would be far more reliable if implemented as an AFTER UPDATE trigger on the base table. This appears to be the WINNER! I eliminated the INSERT action from my UPDATE rule: CREATE OR REPLACE RULE on_update_2_preserve_h AS ON UPDATE TO person ... DO ( /* Update the current H record and make it effective as of either now (if no effective date was provided) or whenever the update query specifies.*/ UPDATE person_h SET person_id = NEW.person_id, first_name = NEW.first_name, middle_names = NEW.middle_names, last_name_prefix = NEW.last_name_prefix, last_name = NEW.last_name, name_suffix = NEW.name_suffix, preferred_full_name = NEW.preferred_full_name, preferred_business_name = NEW.preferred_business_name, user_name = NEW.user_name, _action = NEW._action, effective_date_and_time = CASE WHEN NEW.effective_date_and_time = OLD.effective_date_and_time THEN CURRENT_TIMESTAMP -- Query assigned no value ELSE NEW.effective_date_and_time -- Query assigned value END WHERE person_id = OLD.person_id AND effective_date_and_time = OLD.effective_date_and_time ; /* Copy the old values to a new record. Expire it either now (if no effective date was provided) or whenever the update query specifies.*/ INSERT INTO person_h ( person_id, first_name, middle_names, last_name_prefix, last_name, name_suffix, preferred_full_name, preferred_business_name, user_name, _action, effective_date_and_time, expiration_date_and_time) VALUES ( OLD.person_id, OLD.first_name, OLD.middle_names, OLD.last_name_prefix, OLD.last_name, OLD.name_suffix, OLD.preferred_full_name, OLD.preferred_business_name, OLD.user_name, OLD._action, OLD.effective_date_and_time, CASE WHEN NEW.effective_date_and_time = OLD.effective_date_and_time THEN CURRENT_TIMESTAMP-- Query assigned no value ELSE NEW.effective_date_and_time-- Query assigned a value END
[GENERAL] Another perplexity with PG rules
I'm stumped on the following problem.
Everything between the --- rows should be executable.
Please advise.
~ TIA
~ Ken
---
-- Here's a table:
CREATE TABLE public.person_h
(
person_id bigint DEFAULT nextval('pop_seq'::text),
effective_date_and_time timestamptz DEFAULT ('now'::text)::timestamp(6) with
time zone,
expiration_date_and_time timestamptz DEFAULT 'infinity'::timestamp with time
zone,
first_name varchar(255),
middle_names varchar(255),
last_name_prefix varchar(255),
last_name varchar(255),
name_suffix varchar(255),
preferred_full_name varchar(255),
preferred_business_name varchar(255),
user_name varchar(255),
_action varchar(32) DEFAULT 'preserve'::character varying,
CONSTRAINT pk_person_h_identifier_2 PRIMARY KEY (person_id,
effective_date_and_time)
);
-- Indexes
CREATE UNIQUE INDEX personal_data_px ON person_h USING btree (person_id,
effective_date_and_time);
-- Here's a view of that table plus a few ALTERs on the view:
CREATE OR REPLACE VIEW person AS
SELECT h.person_id AS person_id,
h.effective_date_and_time AS effective_date_and_time,
h.expiration_date_and_time AS expiration_date_and_time,
h.first_name AS first_name,
h.middle_names AS middle_names,
h.last_name_prefix AS last_name_prefix,
h.last_name AS last_name,
h.name_suffix AS name_suffix,
h.preferred_full_name AS preferred_full_name,
h.preferred_business_name AS preferred_business_name,
h.user_name AS user_name,
h._action AS _action
FROM person_h AS h
WHERE h.effective_date_and_time = CURRENT_TIMESTAMP
AND h.expiration_date_and_time = CURRENT_TIMESTAMP
ALTER TABLE person
ALTER COLUMN person_id
SET DEFAULT nextval('pop_seq'::text)
;
ALTER TABLE person
ALTER COLUMN effective_date_and_time
SET DEFAULT ('now'::text)::timestamp(6) with time zone
;
ALTER TABLE person
ALTER COLUMN expiration_date_and_time
SET DEFAULT 'infinity'::timestamp with time zone
;
ALTER TABLE person
ALTER COLUMN _action
SET DEFAULT 'preserve'::character varying
-- Here are a couple of rules on that view:
/*** Rule on_insert inserts the object's first history record into person_h.
***/
CREATE OR REPLACE RULE on_insert AS
ON INSERT TO person
DO INSTEAD (
/* Insert the row into the H table.
Effective and expiration dates take the defaults,
unless query overrides them. */
INSERT INTO person_h
( person_id,
effective_date_and_time,
first_name,
middle_names,
last_name_prefix,
last_name,
name_suffix,
preferred_full_name,
preferred_business_name,
user_name,
_action )
VALUES ( nextval('pop_seq'::text),
NEW.effective_date_and_time,
NEW.first_name,
NEW.middle_names,
NEW.last_name_prefix,
NEW.last_name,
NEW.name_suffix,
NEW.preferred_full_name,
NEW.preferred_business_name,
NEW.user_name,
NEW._action )
)
;
/*** Rule on_update_1_nothing meets the PostgreSQL requirement for one
unconditional UPDATE rule. ***/
CREATE OR REPLACE RULE on_update_1_nothing AS
ON UPDATE TO person
DO INSTEAD NOTHING
;
/*** Rule on_update_2_preserve_h inserts a new record with the old data into
history table person_h,
expires this record effective either now or at the effective time given in
the query,
and updates the current record as of the same time. ***/
CREATE OR REPLACE RULE on_update_2_preserve_h AS
ON UPDATE TO person
WHERE (
(OLD.person_id NEW.person_id
OR (OLD.person_id IS NULL AND NEW.person_id IS NOT NULL)
OR (OLD.person_id IS NOT NULL AND NEW.person_id IS NULL ))
OR (OLD.effective_date_and_time NEW.effective_date_and_time
OR (OLD.effective_date_and_time IS NULL AND
NEW.effective_date_and_time IS NOT NULL)
OR (OLD.effective_date_and_time IS NOT NULL AND
NEW.effective_date_and_time IS NULL ))
OR (OLD.first_name NEW.first_name
OR (OLD.first_name IS NULL AND NEW.first_name IS NOT NULL)
OR (OLD.first_name IS NOT NULL AND NEW.first_name IS NULL ))
OR (OLD.middle_names NEW.middle_names
OR (OLD.middle_names IS NULL AND NEW.middle_names IS NOT NULL)
OR (OLD.middle_names IS NOT NULL AND NEW.middle_names IS NULL ))
OR (OLD.last_name_prefix NEW.last_name_prefix
OR (OLD.last_name_prefix IS NULL AND NEW.last_name_prefix IS NOT
NULL)
OR (OLD.last_name_prefix IS NOT NULL AND NEW.last_name_prefix IS
NULL ))
OR (OLD.last_name NEW.last_name
OR (OLD.last_name IS NULL AND NEW.last_name IS NOT NULL)
OR (OLD.last_name IS NOT NULL AND NEW.last_name IS NULL ))
OR (OLD.name_suffix NEW.name_suffix
OR (OLD.name_suffix IS
Re: [GENERAL] Does PG really lack a time zone for India?
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 15, 2006 5:39 PM
To: [EMAIL PROTECTED]
Cc: 'PostgreSQL pg-general List'
Subject: Re: [GENERAL] Does PG really lack a time zone for India?
Ken Winter [EMAIL PROTECTED] writes:
Yes, that's what I'm trying to do. My problem has been: how to enter
the
equivalent of '2006-02-15 10:22:46-05' when the time I want to enter and
convert to EST is a variable value or now().
This still shows a problem in your grasp of what's going on. now() is
absolute and should never need to be converted ...
regards, tom lane
Tom ~
OK, let me show a little example, in hopes that you can show where I'm
making things needlessly complicated:
/* The table payment records when particular payments were received and
when they were due. The requirement is that the due dates are in the local
time of the office receiving the payment, and late fees must be calculated
based on that time. (In the real world, this very denormalized table would
actually be the result of a query joining many tables, but I'm trying to
distill the essence here.) */
CREATE TABLE payment (
office_location character varying NOT NULL,
-- City of the office to which the payment is due
office_time_zone character varying(10),
-- PostgreSQL time zone code of the office where the payment is due.
-- I had to lie about Bangalore, because PostgreSQL has no code
-- for Indian Time (GMT+5:30) and the payment_calcs view evokes
-- an error if not given a valid tz code.
office_gmt_offset interval,
-- Offset of office time zone from GMT
due_date_in_office_time_zone timestamp with time zone,
-- Timestamp for when the pmt is due.
-- IMPORTANT: This is interpreted as a time in the office's time
zone.
-- Note that they due dates all the same clock-time, namely 5 pm.
paid_date_in_server_time_zone timestamp with time zone
-- The time when the payment was received. As these payments were
-- all entered with no tz modifier, the time entered was interpreted
-- as the server's time zone, which happens to be CST (GMT-06).
-- IMPORTANT: The payment date was entered as a literal, not as
-- now(), but that makes no difference; once the timestamp is
-- stored it is impossible to tell which way it was entered.
);
/* This view makes several calculations from the payment table that may help
understand what is going on. The most important is
paid_date_in_office_time, because that is the one that I believe has to be
used in the real system, either when the payment time is entered or when
the late calcs are being made, in order to get the right results in terms
of assessing late fees. */
CREATE VIEW payment_calcs AS
SELECT
payment.office_location,
payment.office_time_zone,
payment.office_gmt_offset,
payment.due_date_in_office_time_zone,
payment.paid_date_in_server_time_zone,
(payment.due_date_in_office_time_zone - payment.office_gmt_offset)
AS due_date_in_gmt,
-- The GMT when the payment is due.
timezone('GMT'::text, payment.paid_date_in_server_time_zone)
AS paid_date_in_gmt,
-- The GMT when the payment was received.
(timezone('GMT'::text,
payment.paid_date_in_server_time_zone) +
payment.office_gmt_offset) AS paid_date_in_office_time,
-- The time when the payment was received, converted into
-- the time zone of the receiving office.
((timezone((payment.office_time_zone)::text,
payment.paid_date_in_server_time_zone))::timestamp
with time zone - payment.due_date_in_office_time_zone)
AS late_by_time_zone_code,
-- The time interval between the due date and the payment,
-- calculated using the PostgreSQL time zone code.
-- Note that the Bangalore result is wrong by 1/2 hour
-- because the nearest PG code is wrong by 1/2 hour.
(((timezone('GMT'::text, payment.paid_date_in_server_time_zone) +
payment.office_gmt_offset))::timestamp with time zone
- payment.due_date_in_office_time_zone) AS late_by_offset
-- The time interval between the due date and the payment,
-- calculated using the office_gmt_offset.
-- Note that the Bangalore result is right
-- because the offset is right.
FROM payment
ORDER BY (payment.office_gmt_offset)::time without time zone;
INSERT INTO payment VALUES ('Paris', 'CET', '01:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT INTO payment VALUES ('Baghdad', 'BT', '03:00:00', '2006-02-15
17:00:00-06', '2006-02-15 08:00:00-06');
INSERT
Re: [GENERAL] Does PG really lack a time zone for India?
Tom ~ Thanks for yet another prompt and helpful response. May I submit a follow-up question? Briefly, what I'm trying to do is build a (web-front-ended) system that translates a now() entry into a timestamptz column into now at a specified time zone (which is looked up from the database). Why? Say there's a payment deadline recorded in a database column, and that deadline is supposed to be as of the local time of the office that accepted the order. The database has tables and columns that record the time zone of each office and associate each order with the office that accepted it. Say there's an actual payment column that records the actual time when a payment is received, and that column is compared to the deadline column to determine whether to charge a late fee. Say I place an order with the London office (GMT), but I am in California (GMT+8) (and the PostgreSQL server is there, too). If I pay one hour before the deadline, CA time, I should be charged a late fee, because the deadline actually occurred 7 hours ago in London. To have that come out right, the system needs to translate now() on input to now() AT TIME ZONE 'GMT', so that the timestamp that gets stored in the payment column is 7 hours after the deadline. As far as I can tell, that works fine - that is, I submit such a query to PostgreSQL and it returns the correct time in that zone. The problem comes when PostgreSQL has no time zone code that gives me the GMT offset that I need - most painfully, that is the case with India (GMT+5:30). So, I'm wondering if the AT TIME ZONE construct can accept the offset in any syntax that isn't dependent on the time zone code. I tried entries such as now() AT TIME ZONE 'GMT+5:30' and now() AT TIME ZONE '+5:30', but they didn't work. Any suggestions? ~ Thanks again ~ Ken PS: If it would be of any help, I would be happy to share the country uses timezone table that I cobbled together yesterday, mapping the existing PG 7.4 time zone codes (sometimes renamed) to a country list derived from lists of countries that have postal codes and/or international dialing prefixes. It's yet another hack, but hey it's free for the asking. (Given the lack of a worldwide standard for time zone names and abbreviations, any such effort is going to be something of a hack; I think the best we can hope for is a list of names and codes that most people can recognize, but a fully correct list of the GMT offsets.) PPS: I'm glad that PG 8.x has discovered India. Alas, my web host informs me that I'm stuck with 7.4 until a production version of the psycopg2 connector comes out. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 15, 2006 12:34 AM To: [EMAIL PROTECTED] Cc: 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Does PG really lack a time zone for India? Ken Winter [EMAIL PROTECTED] writes: The documentation (http://www.postgresql.org/docs/7.4/static/datetime-keywords.html) = doesn't have an entry for Indian Standard Time, nor for any other time zone with = a GMT+5:30 offset. I don't see any such entry in datetktbl in datetime.c, either. You could enter it explicitly as +5:30, though, and (as of PG 8.0) there are settings in the main timezone database for India. (In PG 7.4 it'd depend on what zone names your operating system knows.) regression=# set timezone = 'GMT'; SET regression=# select now(); now --- 2006-02-15 05:20:33.317049+00 (1 row) regression=# select '2006-02-15 05:20:33.317049+05:30'::timestamptz; timestamptz --- 2006-02-14 23:50:33.317049+00 (1 row) regression=# set timezone = 'Asia/Calcutta'; SET regression=# select now(); now -- 2006-02-15 10:51:19.241808+05:30 (1 row) The existence of duplicate timezone abbreviations is certainly a pain :-(. The solution I would like to see is to factor all the hardwired timezone abbreviations in datetktbl out into a configuration file that could be adjusted for local conditions. However, it's not entirely clear how to deal with words that could be either a zone name or some other date keyword, for instance SAT is not just a day of the week but a known zone name in Australia. Plan B would be to extend the existing australian_timezones hack with some other specialized options, but I think that way madness lies ... Anyway, what this area needs is for somebody to get annoyed enough to design and then code a generally acceptable solution. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does PG really lack a time zone for India?
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 15, 2006 10:25 AM To: [EMAIL PROTECTED] Cc: 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Does PG really lack a time zone for India? Ken Winter [EMAIL PROTECTED] writes: Briefly, what I'm trying to do is build a (web-front-ended) system that translates a now() entry into a timestamptz column into now at a specified time zone (which is looked up from the database). Why? Say there's a payment deadline recorded in a database column, and that deadline is supposed to be as of the local time of the office that accepted the order. The database has tables and columns that record the time zone of each office and associate each order with the office that accepted it. Say there's an actual payment column that records the actual time when a payment is received, and that column is compared to the deadline column to determine whether to charge a late fee. Say I place an order with the London office (GMT), but I am in California (GMT+8) (and the PostgreSQL server is there, too). If I pay one hour before the deadline, CA time, I should be charged a late fee, because the deadline actually occurred 7 hours ago in London. To have that come out right, the system needs to translate now() on input to now() AT TIME ZONE 'GMT', so that the timestamp that gets stored in the payment column is 7 hours after the deadline. I think you're thinking about this in entirely the wrong fashion. What you are really saying is that you want to deal with absolute time: the payment deadline is a fixed time instant and you don't want the observer's timezone to affect the decision about whether the deadline has passed or not. The way to do that in Postgres is to store all timestamps as TIMESTAMP WITH TIME ZONE and not do any explicit timezone translations. When you enter a timestamp value, either write the correct GMT offset in it, eg '2006-02-15 10:22:46-05', or leave it out and the database will assume that it's expressed in the current TimeZone zone. Either way, it'll get converted to UTC internally and all subsequent comparisons are absolute. regards, tom lane Yes, that's what I'm trying to do. My problem has been: how to enter the equivalent of '2006-02-15 10:22:46-05' when the time I want to enter and convert to EST is a variable value or now(). I've finally figured out the answer - do data entry through: timestamptz variable value AT TIME ZONE 'GMT' + '-5:00' and now() AT TIME ZONE 'GMT' + '-5:00' You can substitute a variable of type interval for the '-5:00' constant. These could of course also be accomplished by: timestamptz variable value AT TIME ZONE 'EST' and now() AT TIME ZONE 'EST' But the first solution bypasses PostgreSQL's incomplete list of time zone codes. So it can be used to handle the missing time codes for (for example) India (GMT+5:30) and Nepal (GMT+5:45), which is what I need. ~ Thanks to all for the help! ~ Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Does PG really lack a time zone for India?
The documentation (http://www.postgresql.org/docs/7.4/static/datetime-keywords.html) doesnt have an entry for Indian Standard Time, nor for any other time zone with a GMT+5:30 offset. Is this just an omission from the documentation? If so, what are the name and codes of the GMT+5:30 time zone? Or does PostgreSQL really have no time zone that it can read for this rather important place? If so, can this problem be fixed? (I believe the standard code for Indian Standard Time is IST, which alas conflicts with PostgreSQLs code for Israel Standard Time, so I guess some other code needs to be used for India.) The documentation and/or PostgreSQL itself are also missing some other GMT offsets: GMT+5:45 (Nepal) GMT+11 (various mid-Pacific islands) There actually is one zone with this offset, but it is an Australian daylight saving time zone (AESST), so using it for standard time is a bit untidy. GMT+11:30 (Norfolk Island) GMT+13 and GMT+14 (Kribali, Tonga) Yes indeed, these Pacific islands are actually more than 12 hours ahead of GMT! These are less important than India because (except for Nepal) they only apply to a few Pacific islands, but if someone were to go in there and fix the India problem, it might be a good time to tidy these up as well. ~ TIA ~ Ken
Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN?
Stephan ~ You're right: This thing I call a view-table would behave *exactly* like a view that has insert, update, and delete rules. The *only* difference I'm trying to achieve is to get it stored in pg_catalog.pg_class with relkind = 'r' (ordinary table) rather than 'v' (view). The *only* reason I'm trying to disguise a view as a table is to trick my client tools into letting me use their handy pre-made forms and grids to read and write to these structures. The reason I'm trying to activate these forms and grids is to enable my testing users to start entering and viewing test data immediately - without their having to learn and write SQL, and without my having to build data entry and review forms for them. I thought, all things considered, my little trick - admittedly a workaround - would be the easiest way to achieve what I need without requiring anything of either the PostgreSQL architects or the tool builders. So it is frustrating to be defeated by this one PostgreSQL constraint (which isn't even published in the documentation, as far as I can see). I just had another workaround idea - declare the view-table as an ordinary table and put triggers on it that implement the functionality of the rules - but before resorting to that I thought I'd try my simpler trick once more. So let me ask again: Is there any way to disable this constraint that forces the SELECT rule to be named _RETURN? Or is there any other way to accomplish what I'm trying to do? ~ Thanks again ~ Ken -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Sunday, February 12, 2006 11:39 PM To: Ken Winter Cc: 'Tom Lane'; 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN? On Sun, 12 Feb 2006, Ken Winter wrote: -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Sunday, February 12, 2006 8:47 PM To: Ken Winter Cc: 'Tom Lane'; 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN? On Sun, 12 Feb 2006, Ken Winter wrote: Hi Tom ~ You're right: I appealed to the PostgreSQL folks rather than the client tool builders. I did so because my guess is that the latter have a harder row to hoe: They have to figure out whether a view really IS updatable - most presumably aren't, so if they provide forms that offer to update views, most of the time these forms are going to crash. It seems harder for the client tool builders to figure out the updatability question than for PostgreSQL to let people (like me) do the real table with ON SELECT trick and take responsibility for making it work. I don't see why that is inherently broken. What does a real table with ON SELECT mean? It means a table that, due to the rules on it, works exactly like a view (from the client's perspective). (Here, let me call it a view-table.) No row ever gets inserted into the view-table. The rules deflect inserts into one or more base tables. Updates and deletes, though from the client's view they modify or remove rows in the view-table, actually update and delete in the underlying base tables. How is this different from a view with on insert, on update and on delete rules right now? For example, if a row is inserted that doesn't come into the on select output, was a row inserted? In what I'm doing, that would not happen. But there might be a case where someone would want a design where rows inserted through the view-table, though they do get inserted into the underlying base tables, would not be visible through SELECT actions on the view-table. I can't imagine offhand why anyone would want to do this, but I don't see why PostgreSQL should stop them. (...Actually, on second thought, I have thought of doing a trick like this myself, to get around the PostgreSQL constraint I'm complaining about: Define a view-table with all of the update rules on it, so no rows ever get inserted into it but my client tools can do updates against it; then define a second, read-only, view for SELECTs to reveal the data entered through the first view. Right; I would rather not stoop to this.) Can it cause unique key violations, can it satisfy a foreign key constraint? PK, UK, FK, and check constraints would all be defined on the base tables, not on the view-table. So actions on the view-table would satisfy or violate these constraints, like any other actions redirected through PostgreSQL update rules. But then this view-table isn't really a real table. If it's not a real table, it pretty much defeats the original stated argument of having real tables with on select rules. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate
[GENERAL] Why does an ON SELECT rule have to be named _RETURN?
I'm trying to build something that behaves like an updatable view but that PostgreSQL (version 7.4) regards and presents to the world as a table. The reason I want to do this odd thing is that my front-end tools (phpPgAdmin and PostgreSQL Lightning Admin) have handy pre-made data entry and viewing forms, but they only work against tables (not against views). The PostgreSQL documentation (http://www.postgresql.org/docs/7.4/static/rules-views.html) says that: There is essentially no difference between CREATE VIEW myview AS SELECT * FROM mytab; compared against the two commands CREATE TABLE myview (same column list as mytab); CREATE RULE _RETURN AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; because this is exactly what the CREATE VIEW command does internally. OK, I figured, so if I turn my existing view (made updatable by suitable ON INSERT, ON UPDATE, and ON DELETE rules), which works fine, into a table with an ON SELECT rule on the above pattern, that ought to work. But I decided to name my ON SELECT rule something other than _RETURN, so PostgreSQL wouldn't suspect what I was up to. Alas, PostgreSQL responded with an error message saying that a view rule...must be named _RETURN'. When I renamed it thus, PostgreSQL accepted the whole thing - but ended up classifying the resulting structure as a view, which defeated my purpose of making it accessible through my front-end tools. So I'm wondering: * Why this constraint? * Would anything break if I were allowed to get away with my little trick? * Is there any way to get around the constraint? ~ TIA ~ Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN?
Hi Tom ~ You're right: I appealed to the PostgreSQL folks rather than the client tool builders. I did so because my guess is that the latter have a harder row to hoe: They have to figure out whether a view really IS updatable - most presumably aren't, so if they provide forms that offer to update views, most of the time these forms are going to crash. It seems harder for the client tool builders to figure out the updatability question than for PostgreSQL to let people (like me) do the real table with ON SELECT trick and take responsibility for making it work. I don't see why that is inherently broken. Everybody from E.F. Codd onward has struggled with the view updatability problem; it seems like PostgreSQL is one unnecessary constraint away from letting users find a pragmatic solution (a.k.a. workaround) for it. ~ Ken -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Sunday, February 12, 2006 6:43 PM To: [EMAIL PROTECTED] Cc: 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN? Ken Winter [EMAIL PROTECTED] writes: * Why this constraint? * Would anything break if I were allowed to get away with my little trick? * Is there any way to get around the constraint? The reason why the table is converted to a view is that ancient pg_dump dumps used to create views in exactly that way (make a table and then add an ON SELECT rule) and so when we started making a hard distinction between tables and views, we needed to force the conversion to occur. The notion of a real table that has an ON SELECT rule seems fairly broken to me in any case. I think you should be complaining to the authors of your client-side tools that they won't do what you want. It would probably be quite a trivial change to get them to support data entry forms against views, but changing the backend on this point won't be an easy sell. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN?
-Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Sunday, February 12, 2006 8:47 PM To: Ken Winter Cc: 'Tom Lane'; 'PostgreSQL pg-general List' Subject: Re: [GENERAL] Why does an ON SELECT rule have to be named _RETURN? On Sun, 12 Feb 2006, Ken Winter wrote: Hi Tom ~ You're right: I appealed to the PostgreSQL folks rather than the client tool builders. I did so because my guess is that the latter have a harder row to hoe: They have to figure out whether a view really IS updatable - most presumably aren't, so if they provide forms that offer to update views, most of the time these forms are going to crash. It seems harder for the client tool builders to figure out the updatability question than for PostgreSQL to let people (like me) do the real table with ON SELECT trick and take responsibility for making it work. I don't see why that is inherently broken. What does a real table with ON SELECT mean? It means a table that, due to the rules on it, works exactly like a view (from the client's perspective). (Here, let me call it a view-table.) No row ever gets inserted into the view-table. The rules deflect inserts into one or more base tables. Updates and deletes, though from the client's view they modify or remove rows in the view-table, actually update and delete in the underlying base tables. For example, if a row is inserted that doesn't come into the on select output, was a row inserted? In what I'm doing, that would not happen. But there might be a case where someone would want a design where rows inserted through the view-table, though they do get inserted into the underlying base tables, would not be visible through SELECT actions on the view-table. I can't imagine offhand why anyone would want to do this, but I don't see why PostgreSQL should stop them. (...Actually, on second thought, I have thought of doing a trick like this myself, to get around the PostgreSQL constraint I'm complaining about: Define a view-table with all of the update rules on it, so no rows ever get inserted into it but my client tools can do updates against it; then define a second, read-only, view for SELECTs to reveal the data entered through the first view. Right; I would rather not stoop to this.) Can it cause unique key violations, can it satisfy a foreign key constraint? PK, UK, FK, and check constraints would all be defined on the base tables, not on the view-table. So actions on the view-table would satisfy or violate these constraints, like any other actions redirected through PostgreSQL update rules. ~ Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] NEW variable values in actions in rules
Tom ~
Your answers (below) to my questions about the values of NEW columns in
trigger functions was very clarifying for me (as well as being correct!).
Now I'm having similar problems with NEW values in actions that are embedded
in rules, and the answers don't seem to be the same.
I have a table person_h with a not-null column effective_date_and_time
that defaults to CURRENT_TIMESTAMP.
I have a view person with the following rule defined on it:
CREATE RULE on_insert AS
ON INSERT TO person
DO INSTEAD (
INSERT INTO person_i (person_id, birth_date)
VALUES (nextval('pop_seq'::text), new.birth_date);
INSERT INTO person_h (person_id, ...
effective_date_and_time,...)
VALUES (currval('pop_seq'::text), last_name, ...
new.effective_date_and_time, new.last_name, ...);
where the ...s are some other columns not of interest here.
So now when I do this query:
insert into public.person (last_name) values ('Jones');
I get this error:
PostgreSQL Error Code: (1)
ERROR: null value in column effective_date_and_time violates not-null
constraint--0 Rows Affected
So the query processor seems not to be behaving as you described in
answering my question 2 below: it is not taking the
NEW.effective_date_and_time value to be whatever the default is for the
column.
If I submit the same INSERT INTO person_h... query directly, rather than
as part of the rule, it works fine.
Help?
~ TIA
~ Ken
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 05, 2006 7:49 PM
To: Ken Winter
Cc: PostgreSQL pg-general List
Subject: Re: [GENERAL] NEW variable values in trigger functions
Ken Winter [EMAIL PROTECTED] writes:
1. What is the value of the NEW variable for a column that is not
mentioned
in an UPDATE statement? Is it NULL? If not NULL, what?
No, it's whatever value is going to be assigned to the new row (which in
this particular case would be the same as the OLD value).
2. Same questions re the value of a NEW variable that is not assigned a
value in an INSERT statement.
Same answer: whatever value is due to go into the row (in this case,
whatever the default is for the column).
3. If an UPDATE query set a column to DEFAULT, what value does a trigger
function see for the column's NEW variable?
Same answer.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Seeking a better PL/pgSQL editor-debugger
Is a better PL/pgSQL editor / debugger than pgAdmin III or phpPgAdmin available anywhere? I ask because I was stuck for two days on the following error message: ERROR: syntax error at or near LOOP CONTEXT: compile of PL/pgSQL function gen_history near line 126 and neither of those tools offered any more help than that. It turned out the error was a missing ; way back in line 53, and it took two days of trial and error and staring at the code to find it. (On the positive side, pgAdmin IIIs use of different colors to distinguish different program elements [variables, keywords, string constants, comments] made the staring part easier to do.) Ive learned that pgAdmin syntax error can mean anything from a missing ; to a faulty block structure to an undeclared variable to I dont know what else, and as witness the example that error may be nowhere near the line that is flagged. So Im looking for a PL/pgSQL tool that would at least provide more diagnostic error messages. Preferably, it would also offer some sort of breakpoint function to let the developer see the values of variables at specified points in the code. I looked on the pgAdmin web site. The only place a PL/pgSQL debugger was mentioned was on the to do page, under major projects (http://www.pgadmin.org/development/todo.php), which I guess means dont hold your breath. As editors, the two pgAdmin tools apparently dont offer elementary functions such as find and replace, which means I have to slurp my code out into a text editor when I really need these things. So I guess my questions are: Is it the case that the pgAdmin tools actually do offer these features, but I just havent found them yet? If so, can you show me where they are? Are these functions available through add-ons to either pgAdmin tool? If so, where can I get these add-ons? Are there other PL/pgSQL editors that provide these functions? If so, what? Obviously, Id prefer a free one, but would pay for one if necessary. ~ TIA ~ Ken
Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger
Tom ~ Thanks for the news. I'm on PostgreSQL 7.4.7, alas, and there's nothing I can do about it because it resides on a host that I don't control. I suppose if I don't find an alternative, I could move my development work to a local installation of PostgreSQL 8.1. ~ Ken -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Saturday, January 28, 2006 1:26 PM To: Ken Winter Cc: PostgreSQL pg-general List Subject: Re: [GENERAL] Seeking a better PL/pgSQL editor-debugger Ken Winter [EMAIL PROTECTED] writes: I ask because I was stuck for two days on the following error message: ERROR: syntax error at or near LOOP CONTEXT: compile of PL/pgSQL function gen_history near line 126 and neither of those tools offered any more help than that. It turned out the error was a missing ; way back in line 53, and it took two days of trial and error and staring at the code to find it. What Postgres version are you using? I would blame the backend more than the client tools for the failure to localize this syntax error. We've made significant progress in 8.0 and again in 8.1 on improving plpgsql's error messages --- if you are not on 8.1 the first thing to try is a backend upgrade. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] NEW variable values in trigger functions
1. What is the value of the NEW variable for a column that is not mentioned in an UPDATE statement? Is it NULL? If not NULL, what? For example, given this table: my_tbl (id integer, att1 varchar, att2 varchar) and a row-wise ON UPDATE OR INSERT trigger function containing this conditional: IF NEW.att2 IS NULL THEN do stuff END IF; and this UPDATE query: UPDATE my_tbl SET att1 = 'foo' where id = 1; will that conditional be satisfied? 2. Same questions re the value of a NEW variable that is not assigned a value in an INSERT statement. For example, how would the previous conditional behave in response to: INSERT INTO my_tbl (id) VALUES (1); ? 3. If an UPDATE query set a column to DEFAULT, what value does a trigger function see for the column's NEW variable? Is it the string 'DEFAULT', a reserved word DEFAULT, an empty string, or what? For example, what would you put in place of ?? in this UPDATE trigger function: IF NEW.att2 ?? THEN do stuff END IF; to get it to do stuff in response to this UPDATE query: UPDATE my_tbl SET att2 = DEFAULT where id = 1; ? ~ TIA ~ Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] In processing DDL, when does pg_catalog get updated?
Tom ~
Right you are! I added some code to trim off the schema name, and it works
fine.
You also alerted me to the NOTICE facility. (I'm new to PostgreSQL, and
have been learning it in wade right in mode rather than properly studying
the whole environment.) I have a PostgreSQL for Dummies question about
RAISE NOTICE: Where do I find its output? According to the documentation
(http://www.postgresql.org/docs/7.4/static/plpgsql-errors-and-messages.htmlh
ttp://www.postgresql.org/docs/7.4/static/plpgsql-errors-and-messages.html),
the messages are either reported to the client, written to the server log,
or both. My database is on a web host (zettai.net), and I'm working on it
via phpPgAdmin. I don't know where to find messages reported to the
client, and I don't know how to access the system log. Can anyone help?
~ Thanks!
~ Ken
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Friday, December 30, 2005 12:10 PM
To: Ken Winter
Cc: 'PostgreSQL pg-general List'
Subject: Re: [GENERAL] In processing DDL, when does pg_catalog get
updated?
Ken Winter [EMAIL PROTECTED] writes:
My question is: Why didn't the chunk of gen_sequences code that
consults
pg_catalog find a record of e_mail_address_invisible_id_seq, and
thereby
refrain from trying to create it again?
I added a few raise notice commands to your function, and got this:
NOTICE: sub_idcol = invisible_id
NOTICE: default_exp =
nextval('public.e_mail_address_invisible_id_seq'::text)
NOTICE: sequence_name = public.e_mail_address_invisible_id_seq
NOTICE: not found
NOTICE: sub_idcol = pop_id
NOTICE: default_exp = nextval('pop_seq'::text)
NOTICE: sequence_name = pop_seq
NOTICE: found
ERROR: relation e_mail_address_invisible_id_seq already exists
CONTEXT: SQL statement CREATE SEQUENCE
public.e_mail_address_invisible_id_seq;
PL/pgSQL function gen_sequences line 51 at execute statement
The problem seems to be that you're not accounting for a schema name
possibly appearing in nextval's argument.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] In processing DDL, when does pg_catalog get updated?
Tom ~
Good idea. The grisly details are as follows.
Here is the DDL script (generated from PowerDesigner 10.1.0.1134):
/*==*/
/* DBMS name: PostgreSQL 7.3 */
/* Created on: 12/30/2005 11:08:02 AM */
/*==*/
SET search_path TO public;
/*==*/
/* Table: e_mail_address*/
/*==*/
create table e_mail_address (
pop_id INT8 not null default
nextval('pop_seq'),
effective_date_and_time TIMESTAMP WITH TIME ZONE not null default
CURRENT_TIMESTAMP,
invisible_id BIGSERIALnot null,
e_mail_type VARCHAR(255) null,
expiration_date_and_time TIMESTAMP WITH TIME ZONE null default 'infinity',
user_nameVARCHAR(255) not null,
domain_name VARCHAR(255) not null,
use_this_e_mail_for_administrative_matters BOOL null,
use_this_e_mail_for_teaching_and_practice BOOL null,
use_this_e_mail_for_personal_messages BOOL null,
omit_this_e_mail_from_school_directory BOOL null,
comments VARCHAR(4000)null,
constraint PK_e_mail_address_priority_pk primary key (pop_id,
effective_date_and_time, invisible_id),
constraint fk_contact___e_mail_address foreign key (pop_id)
references pop (pop_id)
on delete cascade on update cascade,
constraint fk_e_mail_type___e_mail_address foreign key (e_mail_type)
references e_mail_type (e_mail_type)
on delete cascade on update cascade
)
INHERITS (when_and_who)
WITH OIDS;
SELECT gen_sequences('e_mail_address', 'public');
Here is the function gen_sequences that is evoking the error:
CREATE OR REPLACE FUNCTION gen_sequences ( VARCHAR, VARCHAR ) RETURNS
VARCHAR AS
'
DECLARE
table_name ALIAS FOR $1;
schema_name ALIAS FOR $2;
this_table RECORD;
dummy RECORD;
sequence_name VARCHAR;
cre_seq_arr VARCHAR [] := ''{}'';
cre_seq_code VARCHAR := ;
BEGIN
EXECUTE ''SET search_path TO '' || schema_name;
/* Create a CREATE SEQUENCE statement for the sequence
of each sequence-assigned column,
if the sequence doesnt exist already. */
FOR this_table IN
SELECT c.column_name AS sub_idcol,
c.column_default AS default_expr
FROM information_schema.columns c
WHERE c.table_name = table_name
AND c.table_schema = schema_name
AND c.column_default LIKE ''nextval%''
LOOP
sequence_name := split_part(this_table.default_expr, ,
2);
IF NOT EXISTS (SELECT 1
FROM pg_catalog.pg_class AS t,
pg_catalog.pg_namespace AS s
WHERE t.relname = sequence_name
AND s.nspname = schema_name
AND t.relnamespace = s.oid
AND t.relkind = ''S'')
THEN
IF array_upper(cre_seq_arr, 1) IS NULL THEN
cre_seq_arr[1] := ''CREATE SEQUENCE '' || sequence_name
|| '';'' ;
ELSE
cre_seq_arr[array_upper(cre_seq_arr, 1) + 1] := ''CREATE
SEQUENCE '' || sequence_name || '';'' ;
END IF;
cre_seq_code := cre_seq_code ||
cre_seq_arr[array_upper(cre_seq_arr, 1)] || ''
'';
END IF;
END LOOP;
/* Execute the CREATE SEQUENCE statements, if any. */
IF array_upper(cre_seq_arr, 1) IS NOT NULL THEN
FOR n IN 1..array_upper(cre_seq_arr, 1) LOOP
EXECUTE cre_seq_arr[n];
END LOOP;
END IF;
RETURN cre_seq_code;
END;
'
LANGUAGE plpgsql
;
And here is the error message from phpPgAdmin:
SQL error:
ERROR: relation e_mail_address_invisible_id_seq already exists
CONTEXT: PL/pgSQL function gen_sequences line 45 at execute statement
Line 45 is the line that contains the execute statement.
Those are the raw facts.
My question is: Why didn't the chunk of gen_sequences code that consults
pg_catalog find a record of e_mail_address_invisible_id_seq, and thereby
refrain from trying to create it again?
~ Thanks again
~ Ken
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] In processing DDL, when does pg_catalog get updated?
I'm running a DDL script that does the following (in this order): 1. Creates a table containing a BIGSERIAL primary key column declaration, which apparently automatically creates a sequence to populate this column. 2. Runs a gen_sequences function that I wrote, which executes CREATE SEQUENCE statements for all columns in the table that have defaults like 'nextval%' but that don't already have sequences. The part of the function that checks that the sequence doesn't already exist consults the pg_catalog, as follows: IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_class AS t, pg_catalog.pg_namespace AS s WHERE t.relname = sequence_name AND s.nspname = schema_name AND t.relnamespace = s.oid AND t.relkind = 'S') THEN execute the CREATE SEQUENCE statement END IF; This script aborts with a message like this: ERROR: relation my_table_id_seq already exists ...which implies that the code above is not finding my_table_id_seq in the catalog. I know that the code works OK in detecting sequences that pre-existed the execution of this script. So the only explanation that I can come up with is that, at step 2, the pg_catalog has not yet been updated to reflect the results of step 1 - namely, that the new sequence has been created. Is it possible that the pg_catalog is not updated with the results of a DDL script until the whole script has executed? If this is so, is there any way to force the pg_catalog to be updated along the way? ~ TIA ~ Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Error in 7.4.9 Documentation
FWIW: At http://www.postgresql.org/docs/7.4/static/infoschema-triggers.html, it says there is a column in the information_schema triggers table named event_object_name. The name actually is event_object_table. ~ Ken
Re: [GENERAL] How to run a stored PL/pgSQL function?
That works. Thanks! (Thanks also to Joshua Drake, who contributed the same answer.) ~ Ken -Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 30, 2005 11:19 PM To: Ken Winter Cc: PostgreSQL pg-general List Subject: Re: How to run a stored PL/pgSQL function? On Dec 1, 2005, at 12:38 , Ken Winter wrote: How can I cause a PL/pgSQL function to be executed from a SQL script? snip / I have a PL/pgSQL foo (varchar). select foo(bar); -- where bar is of type varchar Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
