Converting sql anywhere to postgres
Hi there
I am trying to convert a SQL Anywhere database to postgres. Within SQL anywhere a field can have a default value of 'last user'. This means that when you perform an update on a table, if the field is not explicitly set then the current user is used. So for instance if I have a field called mod_user in a table, but when I do an update on the table and do not set mod_user then SQL Anywhere sets the field to current_uer. I have tried to replicate this using a postgres trigger in the before update. However, if I do not set the value then it automatically picks up the value that was already in the field. Is there a way to tell the difference between me setting the value to the same as the previous value and postgres automatically picking it up.
If the field myfield contains the word 'me'. Can I tell the difference between:
Update table1 set field1='something',myfield='me'
And
Update table1 set field1='something'
On 8/15/23 08:04, Russell Rose | Passfield Data Systems wrote:
Hi there
I am trying to convert a SQL Anywhere database to postgres. Within SQL
anywhere a field can have a default value of ‘last user’. This means
that when you perform an update on a table, if the field is not
explicitly set then the current user is used. So for instance if I have
a field called mod_user in a table, but when I do an update on the table
and do not set mod_user then SQL Anywhere sets the field to current_uer.
I have tried to replicate this using a postgres trigger in the before
update. However, if I do not set the value then it automatically picks
up the value that was already in the field. Is there a way to tell the
difference between me setting the value to the same as the previous
value and postgres automatically picking it up.
Not that I know of. In Postgres an UPDATE is essentially a DELETE of the
OLD tuple and an INSERT of a new tuple. You cannot determine whether the
same value in a given field for the OLD/NEW records was explicitly set
the same or just carried over.
If the field myfield contains the word ‘me’. Can I tell the difference
between:Update table1 set field1=’something’,myfield=’me’
And
Update table1 set field1=’something’
--
Adrian Klaver
adrian.klaver@aklaver.com
On 15/08/2023 17:04 CEST Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk> wrote:
I am trying to convert a SQL Anywhere database to postgres. Within SQL
anywhere a field can have a default value of ‘last user’. This means that
when you perform an update on a table, if the field is not explicitly set
then the current user is used. So for instance if I have a field called
mod_user in a table, but when I do an update on the table and do not set
mod_user then SQL Anywhere sets the field to current_uer. I have tried to
replicate this using a postgres trigger in the before update. However, if
I do not set the value then it automatically picks up the value that was
already in the field. Is there a way to tell the difference between me
setting the value to the same as the previous value and postgres automatically
picking it up.If the field myfield contains the word ‘me’. Can I tell the difference
between:
Update table1 set field1=’something’,myfield=’me’
And
Update table1 set field1=’something’
Do you also have a timestamp column (let's say mod_time) that goes along with
mod_user (both updated together)?
In that case you can compare OLD.mod_time and NEW.mod_time in the BEFORE
trigger. Only if the timestamp does not change should the trigger then assign
NEW.mod_user := current_user and NEW.mod_time := now().
Or use clock_timestamp() instead of now() if you need to handle multiple updates
of the same row in one transaction. But this is only relevant when changing the
current user with SET ROLE during the transaction.
--
Erik
Hi,
Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:
Hi there
I am trying to convert a SQL Anywhere database to postgres. Within SQL
anywhere a field can have a default value of ‘last user’. This means
that when you perform an update on a table, if the field is not
explicitly set then the current user is used. So for instance if I
have a field called mod_user in a table, but when I do an update on
the table and do not set mod_user then SQL Anywhere sets the field to
current_uer. I have tried to replicate this using a postgres trigger
in the before update. However, if I do not set the value then it
automatically picks up the value that was already in the field. Is
there a way to tell the difference between me setting the value to the
same as the previous value and postgres automatically picking it up.If the field myfield contains the word ‘me’. Can I tell the difference
between:Update table1 set field1=’something’,myfield=’me’
And
Update table1 set field1=’something’
maybe this is what you're looking for (without a trigger)
CREATE TABLE mytest.autovalues
(
key serial NOT NULL,
product text,
updated_by text DEFAULT current_user,
updated_at timestamp without time zone DEFAULT current_timestamp,
PRIMARY KEY (key)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS mytest.autovalues
OWNER to postgres;
-- instead of current_user you may also use |session_user see
https://www.postgresql.org/docs/current/functions-info.html|
|
|
|then try:
|
|insert into mytest.autovalues (product) values ('apple') ;
insert into mytest.autovalues (product,updated_by) values
('apple','justanotheruser') ;
insert into mytest.autovalues (product,updated_by) values
('peach','justanotheruser') ;
select * from mytest.autovalues;
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
select * from mytest.autovalues;|
|
|
|In case you want to "automate" the update command (not setting
|||updated_by to DEFAULT manually/programmatically)| you may use an on
update trigger that compares current_user/session_user with
old.|updated_by and if they are different you could set new.updated_by
to DEFAULT (or whatever logic fits your needs)||
kind regards
Georg
On 8/15/23 09:43, Georg H. wrote:
Hi,
Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:
Hi there
I am trying to convert a SQL Anywhere database to postgres. Within SQL
anywhere a field can have a default value of ‘last user’. This means
that when you perform an update on a table, if the field is not
explicitly set then the current user is used. So for instance if I
have a field called mod_user in a table, but when I do an update on
the table and do not set mod_user then SQL Anywhere sets the field to
current_uer. I have tried to replicate this using a postgres trigger
in the before update. However, if I do not set the value then it
automatically picks up the value that was already in the field. Is
there a way to tell the difference between me setting the value to the
same as the previous value and postgres automatically picking it up.If the field myfield contains the word ‘me’. Can I tell the difference
between:Update table1 set field1=’something’,myfield=’me’
And
Update table1 set field1=’something’
maybe this is what you're looking for (without a trigger)
CREATE TABLE mytest.autovalues
(
key serial NOT NULL,
product text,
updated_by text DEFAULT current_user,
updated_at timestamp without time zone DEFAULT current_timestamp,
PRIMARY KEY (key)
)TABLESPACE pg_default;
ALTER TABLE IF EXISTS mytest.autovalues
OWNER to postgres;-- instead of current_user you may also use |session_user see
https://www.postgresql.org/docs/current/functions-info.html||
||then try:
||insert into mytest.autovalues (product) values ('apple') ;
insert into mytest.autovalues (product,updated_by) values
('apple','justanotheruser') ;
insert into mytest.autovalues (product,updated_by) values
('peach','justanotheruser') ;
select * from mytest.autovalues;
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
That is not going to catch the case the OP was interested in:
Current row:
updated_by='me'
update mytest.autovalues set product='pear', updated_by='me' where key=2
vs
update mytest.autovalues set product='pear' where key=2
In other distinguishing whether 'me' is explicitly set in the UPDATE or
whether it carried over from the OLD tuple.
select * from mytest.autovalues;|
|
||In case you want to "automate" the update command (not setting
|||updated_by to DEFAULT manually/programmatically)| you may use an on
update trigger that compares current_user/session_user with
old.|updated_by and if they are different you could set new.updated_by
to DEFAULT (or whatever logic fits your needs)||
Same problem as above, distinguishing between an explicitly set
update_by value and one carried over.
kind regards
Georg
--
Adrian Klaver
adrian.klaver@aklaver.com
The timestamp default in Sql Anywhere is quite easy to convert with a trigger as I can check the old and new values and if they are the same then update. It is very unlikely that the application will manually set a timestamp that is identical to the value stored. This is not the case with a text field. I think I am going to have to look through the whole application to check how things are set and change the code as required.
Russell Rose
Developer
<http://www.passfield.co.uk/>
01404 514400
Passfield Data Systems Ltd VAT Registration No: 673 8387 86 Company Registration No: 3130617 Registered address: The Globe, 165 High Street, Honiton, EX14 1LQ, United Kingdom
This email is sent in confidence for the addressee(s) only. If you receive this communication in error, please notify us immediately and delete any copies. Passfield Data Systems Ltd cannot accept responsibility for any loss or damage arising from any use of this e-mail or attachments. Any views expressed may not necessarily
<https://aka.ms/AAb9ysg>
________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, August 15, 2023 7:19:56 PM
To: Georg H. <georg-h@silentrunner.de>; Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Converting sql anywhere to postgres
On 8/15/23 09:43, Georg H. wrote:
Hi,
Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:
Hi there
I am trying to convert a SQL Anywhere database to postgres. Within SQL
anywhere a field can have a default value of ‘last user’. This means
that when you perform an update on a table, if the field is not
explicitly set then the current user is used. So for instance if I
have a field called mod_user in a table, but when I do an update on
the table and do not set mod_user then SQL Anywhere sets the field to
current_uer. I have tried to replicate this using a postgres trigger
in the before update. However, if I do not set the value then it
automatically picks up the value that was already in the field. Is
there a way to tell the difference between me setting the value to the
same as the previous value and postgres automatically picking it up.If the field myfield contains the word ‘me’. Can I tell the difference
between:Update table1 set field1=’something’,myfield=’me’
And
Update table1 set field1=’something’
maybe this is what you're looking for (without a trigger)
CREATE TABLE mytest.autovalues
(
key serial NOT NULL,
product text,
updated_by text DEFAULT current_user,
updated_at timestamp without time zone DEFAULT current_timestamp,
PRIMARY KEY (key)
)TABLESPACE pg_default;
ALTER TABLE IF EXISTS mytest.autovalues
OWNER to postgres;-- instead of current_user you may also use |session_user see
https://www.postgresql.org/docs/current/functions-info.html||
||then try:
||insert into mytest.autovalues (product) values ('apple') ;
insert into mytest.autovalues (product,updated_by) values
('apple','justanotheruser') ;
insert into mytest.autovalues (product,updated_by) values
('peach','justanotheruser') ;
select * from mytest.autovalues;
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
That is not going to catch the case the OP was interested in:
Current row:
updated_by='me'
update mytest.autovalues set product='pear', updated_by='me' where key=2
vs
update mytest.autovalues set product='pear' where key=2
In other distinguishing whether 'me' is explicitly set in the UPDATE or
whether it carried over from the OLD tuple.
select * from mytest.autovalues;|
|
||In case you want to "automate" the update command (not setting
|||updated_by to DEFAULT manually/programmatically)| you may use an on
update trigger that compares current_user/session_user with
old.|updated_by and if they are different you could set new.updated_by
to DEFAULT (or whatever logic fits your needs)||
Same problem as above, distinguishing between an explicitly set
update_by value and one carried over.
kind regards
Georg
--
Adrian Klaver
adrian.klaver@aklaver.com
I’m fairly confident you can do this using a Rule. It would even be fairly simple.
Be careful, though: Rules are Postgres’ biggest potential foot gun.
Guyren G Howe
Show quoted text
On Aug 15, 2023 at 08:05 -0700, Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk>, wrote:
Hi there
I am trying to convert a SQL Anywhere database to postgres. Within SQL anywhere a field can have a default value of ‘last user’. This means that when you perform an update on a table, if the field is not explicitly set then the current user is used. So for instance if I have a field called mod_user in a table, but when I do an update on the table and do not set mod_user then SQL Anywhere sets the field to current_uer. I have tried to replicate this using a postgres trigger in the before update. However, if I do not set the value then it automatically picks up the value that was already in the field. Is there a way to tell the difference between me setting the value to the same as the previous value and postgres automatically picking it up.
If the field myfield contains the word ‘me’. Can I tell the difference between:
Update table1 set field1=’something’,myfield=’me’
And
Update table1 set field1=’something’
On 2023-08-15 18:43:11 +0200, Georg H. wrote:
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
^^^^^^^^
Oh, I didn't know that worked. Obvious in hindsight, of course (I've
been using DEFAULT in VALUES(...) for ages) but I never thought of it.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
I have just had a quick look at rules and I am not sure how it can be done. Rules still use the concept of NEW and OLD. If my original row has 'myfield' set to 'me' then I don't think I can tell the difference between:
Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'
Within the rule I think NEW.myfield will be set to 'me' in both cases. Please can you explain how I can tell the difference between the two update statements
Import Notes
Resolved by subject fallback
For some reason, I was thinking the rule could see just the fields from the command, but you’re right; a rule won’t work. Sorry.
Guyren G Howe
Show quoted text
On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk>, wrote:
I have just had a quick look at rules and I am not sure how it can be done. Rules still use the concept of NEW and OLD. If my original row has 'myfield' set to 'me' then I don't think I can tell the difference between:
Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'Within the rule I think NEW.myfield will be set to 'me' in both cases. Please can you explain how I can tell the difference between the two update statements
On 8/16/23 12:30, Guyren Howe wrote:
For some reason, I was thinking the rule could see just the fields
from the command, but you’re right; a rule won’t work. Sorry.Guyren G Howe
On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems
<russellrose@passfield.co.uk>, wrote:I have just had a quick look at rules and I am not sure how it can be
done. Rules still use the concept of NEW and OLD. If my original row
has 'myfield' set to 'me' then I don't think I can tell the
difference between:Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'Within the rule I think NEW.myfield will be set to 'me' in both
cases. Please can you explain how I can tell the difference between
the two update statements
If the original value in the user column is "me", what is the difference
between "set other_column = some_value, user = 'me'" and "set
other_column = some_value" at the business level?
On 8/16/23 12:01, Rob Sargent wrote:
On 8/16/23 12:30, Guyren Howe wrote:
For some reason, I was thinking the rule could see just the fields
from the command, but you’re right; a rule won’t work. Sorry.Guyren G Howe
On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems
<russellrose@passfield.co.uk>, wrote:I have just had a quick look at rules and I am not sure how it can be
done. Rules still use the concept of NEW and OLD. If my original row
has 'myfield' set to 'me' then I don't think I can tell the
difference between:Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'Within the rule I think NEW.myfield will be set to 'me' in both
cases. Please can you explain how I can tell the difference between
the two update statementsIf the original value in the user column is "me", what is the difference
between "set other_column = some_value, user = 'me'" and "set
other_column = some_value" at the business level?
Affirmation that the user updating the record explicitly set the user value.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Aug 16, 2023, at 1:35 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 8/16/23 12:01, Rob Sargent wrote:
On 8/16/23 12:30, Guyren Howe wrote:
For some reason, I was thinking the rule could see just the fields from the command, but you’re right; a rule won’t work. Sorry.
Guyren G Howe
On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk>, wrote:I have just had a quick look at rules and I am not sure how it can be done. Rules still use the concept of NEW and OLD. If my original row has 'myfield' set to 'me' then I don't think I can tell the difference between:
Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'Within the rule I think NEW.myfield will be set to 'me' in both cases. Please can you explain how I can tell the difference between the two update statements
If the original value in the user column is "me", what is the difference between "set other_column = some_value, user = 'me'" and "set other_column = some_value" at the business level?
Affirmation that the user updating the record explicitly set the user value.
--
Adrian Klaver
adrian.klaver@aklaver.com
Agreed. But at the end of the day, the difference is what exactly? Wouldn't auditing (short of sql logging) say “no change” with respect to “me” column?
This then is a client issue, no? There has to be two paths in the client code, one which generates an update without “me” and one which includes “me" and the second path does not take in to account current value. If it’s worth the effort the latter code path needs to be cognizant of the current state of the record (“me” column).
On 8/16/23 14:16, Rob Sargent wrote:
I have just had a quick look at rules and I am not sure how it can be done. Rules still use the concept of NEW and OLD. If my original row has 'myfield' set to 'me' then I don't think I can tell the difference between:
Update mytable set afield='something'
and
Update mytable set afield='something',myfield='me'Within the rule I think NEW.myfield will be set to 'me' in both cases. Please can you explain how I can tell the difference between the two update statements
If the original value in the user column is "me", what is the difference between "set other_column = some_value, user = 'me'" and "set other_column = some_value" at the business level?
Affirmation that the user updating the record explicitly set the user value.
--
Adrian Klaver
adrian.klaver@aklaver.comAgreed. But at the end of the day, the difference is what exactly? Wouldn't auditing (short of sql logging) say “no change” with respect to “me” column?
That is above my pay grade, that is for the OP to elaborate on. I was
just saying that the Postgres server could not make that distinction
with the information provided.
--
Adrian Klaver
adrian.klaver@aklaver.com