UPDATE an updatable view

On 08/27/2015 09:07 AM, David Nelson wrote:

Good morning all,

I am creating an updatable view on a set of tables, and just ran into
unexpected (or more likely misunderstood) behavior with the UPDATE
statement. If an attribute is not explicitly listed in the UPDATE statement,
the NEW value appears to be populated with the OLD value. Unless I'm missing
something, this means there is no way to check to see if the UPDATE
statement
includes references to attributes unless it changes them.

I doubt that statement is is very clear, so let me clarify using Example
39-3 from the 9.1 manual:

\pset expanded on
Expanded display is on.

SELECT VERSION();
-[ RECORD 1
]-----------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit

DROP TRIGGER emp_stamp ON emp;
ERROR: relation "emp" does not exist
DROP FUNCTION emp_stamp();
ERROR: function emp_stamp() does not exist
DROP TABLE emp;
ERROR: table "emp" does not exist

CREATE TABLE emp
(
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE TABLE

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE FUNCTION

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
CREATE TRIGGER

INSERT INTO emp
(
empname,
salary,
last_date,
last_user
)
VALUES
(
'John Doe',
45000,
'2015-08-27 09:50:21-05',
'no_such_person'
);
INSERT 0 1

SELECT *
FROM emp;
-[ RECORD 1 ]------------------------
empname | John Doe
salary | 45000
last_date | 2015-08-27 10:15:57.64472
last_user | postgres

UPDATE emp
SET last_user = 'someone_else';
UPDATE 1

SELECT *
FROM emp;
-[ RECORD 1 ]-------------------------
empname | John Doe
salary | 45000
last_date | 2015-08-27 10:16:40.101402
last_user | postgres

So in the UPDATE statement, I only provided a value for last_user. But the
first test of the trigger function tests for a NULL value of
NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception to
be thrown. Am I just misunderstanding how things work? Is there any way to
test to see if the UPDATE statement contained a reference to empname? If the
answer is no, I can certainly work with that, but before I go on I wanted
to make sure I wasn't missing anything.

An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is
deleted and the NEW one inserted with the OLD values unless they where
explicitly changed. So in your test NEW.empname is still 'John Doe' and
therefore NOT NULL. That test would only work if someone explicitly set
empname = NULL in the update. If you want to check whether the value has
not been changed then:

IF NEW.empname = OLD.empname THEN

Thanks!

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

So in the UPDATE statement, I only provided a value for last_user. But

the

first test of the trigger function tests for a NULL value of
NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception to
be thrown. Am I just misunderstanding how things work? Is there any way

to

test to see if the UPDATE statement contained a reference to empname? If

the

answer is no, I can certainly work with that, but before I go on I wanted
to make sure I wasn't missing anything.

An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is

deleted and the NEW one inserted with the OLD values unless they where
explicitly changed. So

Shoot, I went totally brain-dead on that one. I forgot that I'm actually
doing
a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
called MVCC. Thanks for setting me straight!

in your test NEW.empname is still 'John Doe' and therefore NOT NULL.

That test would only work if someone explicitly set empname = NULL in the
update. If you want to check whether the value has not been changed then:

IF NEW.empname = OLD.empname THEN

That's exactly the solution I hit on. Back to work, and thanks again.

David Nelson <dnelson77808@gmail.com> writes:

So in the UPDATE statement, I only provided a value for last_user. But the
first test of the trigger function tests for a NULL value of
NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception to
be thrown. Am I just misunderstanding how things work? Is there any way to
test to see if the UPDATE statement contained a reference to empname? If the
answer is no, I can certainly work with that, but before I go on I wanted
to make sure I wasn't missing anything.

An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted and the NEW one inserted with the OLD values unless they where explicitly changed. So

Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
called MVCC. Thanks for setting me straight!

Huh?

I think any DB platform regardless of how it does MVCC is going to leave
existing fields as-is in an update if same fields aren't specified.

This has nothing specifically to do with Postgres, MVCC, updatable views
etc. IMO.

in your test  NEW.empname is still 'John Doe' and therefore NOT NULL. That test would only work if someone explicitly set empname = NULL in the update. If you want to

check whether the value has not been changed then:

IF NEW.empname = OLD.empname THEN

That's exactly the solution I hit on. Back to work, and thanks again.

Thanks!

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello.

On 27.8.2015 18:35, David Nelson wrote:

So in the UPDATE statement, I only provided a value for last_user. But the
first test of the trigger function tests for a NULL value of
NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception to
be thrown. Am I just misunderstanding how things work? Is there any way to
test to see if the UPDATE statement contained a reference to empname? If the
answer is no, I can certainly work with that, but before I go on I wanted
to make sure I wasn't missing anything.

An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted

and the NEW one inserted with the OLD values unless they where explicitly
changed. So

Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
called MVCC. Thanks for setting me straight!

in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That

test would only work if someone explicitly set empname = NULL in the update. If
you want to check whether the value has not been changed then:

IF NEW.empname = OLD.empname THEN

That's exactly the solution I hit on. Back to work, and thanks again.

Just for the sake of completeness...

If the value (empname in the above example) can be NULL, the compare does not
work, because

SELECT NULL = NULL

returns NULL which is treated as FALSE.

But I am sure you know this :-)

HTH,

Ladislav Lenart

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 27.8.2015 18:35, David Nelson wrote:

So in the UPDATE statement, I only provided a value for last_user.
But the first test of the trigger function tests for a NULL value of
NEW.empname. Since I did not provide one, I was expecting it to be
NULL and an exception to be thrown. Am I just misunderstanding how
things work? Is there any way to test to see if the UPDATE statement
contained a reference to empname? If the answer is no, I can
certainly work with that, but before I go on I wanted to make sure I
wasn't missing anything.

An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is
deleted

and the NEW one inserted with the OLD values unless they where
explicitly changed. So

Shoot, I went totally brain-dead on that one. I forgot that I'm
actually doing a DELETE/INSERT, and the behaviour makes perfect sense
in that light. It's called MVCC. Thanks for setting me straight!

in your test NEW.empname is still 'John Doe' and therefore NOT NULL.
That

test would only work if someone explicitly set empname = NULL in the
update. If you want to check whether the value has not been changed then:

IF NEW.empname = OLD.empname THEN

That's exactly the solution I hit on. Back to work, and thanks again.

Just for the sake of completeness...

If the value (empname in the above example) can be NULL, the compare does not work, because

SELECT NULL = NULL

returns NULL which is treated as FALSE.

But I am sure you know this :-)

HTH,

Ladislav Lenart

___________________________

Right. And that's why you use very nice option provided by PG:

IF NEW.empname IS DISTINCT FROM OLD.empname THEN

which again you probably know :)

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Aug 27, 2015 at 1:21 PM, Jerry Sievers <gsievers19@comcast.net>
wrote:

David Nelson <dnelson77808@gmail.com> writes:

So in the UPDATE statement, I only provided a value for last_user.

But the

first test of the trigger function tests for a NULL value of
NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception

to

be thrown. Am I just misunderstanding how things work? Is there any

way to

test to see if the UPDATE statement contained a reference to empname?

If the

answer is no, I can certainly work with that, but before I go on I

wanted

to make sure I wasn't missing anything.

An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is

deleted and the NEW one inserted with the OLD values unless they where
explicitly changed. So

Shoot, I went totally brain-dead on that one. I forgot that I'm

actually doing

a DELETE/INSERT, and the behaviour makes perfect sense in that light.

It's

called MVCC. Thanks for setting me straight!

Huh?

I think any DB platform regardless of how it does MVCC is going to leave
existing fields as-is in an update if same fields aren't specified.

This has nothing specifically to do with Postgres, MVCC, updatable views
etc. IMO.

You are of course correct, but I could swear I've done an updatable view oin
another system in the past and could count on having the values for column
names that were not called in the UPDATE statement on the view as being
either
NULL or maybe undefined values. But I haven't done an updable view in
anything
other than PostgreSQL in so long that I most likely dreamed that up
(because,
as you seem to be saying, that wouldn't make much sense). So I humbly
retract
that part of my reply. Regardless, the DELETE/INSERT explanation makes it
all clear, and I've gotten the trigger function seemingly working the way
I need for it to. At least it's passed all 30 something tests I've thought
of to throw at it. Still testing though. And I'm sure the one test I fail to
think of will be the first thing the front-end developers throw at it. It
always works that way...

Regards

in your test NEW.empname is still 'John Doe' and therefore NOT NULL.

That test would only work if someone explicitly set empname = NULL in the
update. If you want to

On Fri, Aug 28, 2015 at 6:39 AM, Ladislav Lenart <lenartlad@volny.cz> wrote:

Hello.

On 27.8.2015 18:35, David Nelson wrote:

So in the UPDATE statement, I only provided a value for last_user.

But the

first test of the trigger function tests for a NULL value of
NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception

to

be thrown. Am I just misunderstanding how things work? Is there any

way to

test to see if the UPDATE statement contained a reference to empname?

If the

answer is no, I can certainly work with that, but before I go on I

wanted

to make sure I wasn't missing anything.

An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is

deleted

and the NEW one inserted with the OLD values unless they where

explicitly

changed. So

Shoot, I went totally brain-dead on that one. I forgot that I'm

actually doing

a DELETE/INSERT, and the behaviour makes perfect sense in that light.

It's

called MVCC. Thanks for setting me straight!

in your test NEW.empname is still 'John Doe' and therefore NOT NULL.

That

test would only work if someone explicitly set empname = NULL in the

update. If

you want to check whether the value has not been changed then:

IF NEW.empname = OLD.empname THEN

That's exactly the solution I hit on. Back to work, and thanks again.

Just for the sake of completeness...

If the value (empname in the above example) can be NULL, the compare does

not

work, because

SELECT NULL = NULL

returns NULL which is treated as FALSE.

But I am sure you know this :-)

HTH,

Ladislav Lenart

Thanks Ladislav. To be able to capture updates to NULL, I ended up going
with

IF NEW.empname IS DISTINCT FROM OLD.empname THEN ...

Just for the sake of completeness...

If the value (empname in the above example) can be NULL, the compare does

not work, because

SELECT NULL = NULL

returns NULL which is treated as FALSE.

But I am sure you know this :-)

HTH,

Ladislav Lenart

___________________________

Right. And that's why you use very nice option provided by PG:

IF NEW.empname IS DISTINCT FROM OLD.empname THEN

which again you probably know :)

Regards,
Igor Neyman

Yep, I got there pretty quickiy. IS [NOT] DISTINCT FROM is pretty cool
stuff!