How to tell which event was fired in Trigger function

[Igal @ Lucee.org]

Hi,

I am writing a trigger function that is used after DELETE, INSERT, and 
UPDATE, like so:


    CREATE TRIGGER tr_name AFTER DELETE OR INSERT OR UPDATE ...

How can I tell inside the trigger function if the event was DELETE or 
INSERT/UPDATE?


The table has a `NOT NULL id` column, so I am thinking that maybe a 
DELETE will have a NULL value in NEW.id?  Will that work?  Is there a 
better way?


Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: How to tell which event was fired in Trigger function

[Igal @ Lucee.org]

On 7/11/2018 10:38 AM, Adrian Klaver wrote:

On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote:
How can I tell inside the trigger function if the event was DELETE or 
INSERT/UPDATE?


https://www.postgresql.org/docs/10/static/plpgsql-trigger.html


This looks like it have all of the information that I need.  For some 
reason search engines bring up many other pages but not that one.


Thanks Adrian!

Igal

Re: How to tell which event was fired in Trigger function

[Igal @ Lucee.org]

On 7/11/2018 11:02 AM, David G. Johnston wrote:
On Wed, Jul 11, 2018 at 10:54 AM, Igal @ Lucee.org <mailto:i...@lucee.org>>wrote:


On 7/11/2018 10:38 AM, Adrian Klaver wrote:

On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote:

How can I tell inside the trigger function if the event
was DELETE or INSERT/UPDATE?


https://www.postgresql.org/docs/10/static/plpgsql-trigger.html
<https://www.postgresql.org/docs/10/static/plpgsql-trigger.html>


This looks like it have all of the information that I need.  For
some reason search engines bring up many other pages but not that one.


​For these situations I recommend bookmarking and navigating to the 
Table of Contents [1] for the documentation and looking for relevant 
chapter titles - in this case the "V. 38 - Triggers" one is right 
there on the main page.


https://www.postgresql.org/docs/10/static/index.html

On a related note, maybe the pl/pgsql link from the Triggers chapter 
should link directly to the Triggers section under pl/pgsql instead of 
the top of the chapter...


+1

Should link to ¶42.9 - 
https://www.postgresql.org/docs/10/static/plpgsql-trigger.html

pg_dump and search_path

[Igal @ Lucee.org]

I have a custom search_path:

# show search_path;
   search_path
--
 "staging, transient, pg_catalog"
(1 row)

I ran `pg_dump --schema-only` and the only reference in the output to 
search_path is:


  SELECT pg_catalog.set_config('search_path', '', false);

Then one of my functions which does not reference the full name of a 
table with its schema fails with "relation [rel-name] does not exist".


Is that a bug?  I have seen some old posts about this issue but am not 
sure if there is a ticket or why it still is an issue.


Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: pg_dump and search_path

[Igal @ Lucee.org]

On 7/8/2019 11:48 PM, Igal @ Lucee.org wrote:


I have a custom search_path:

# show search_path;
   search_path
--
 "staging, transient, pg_catalog"
(1 row)

I ran `pg_dump --schema-only` and the only reference in the output to 
search_path is:


  SELECT pg_catalog.set_config('search_path', '', false);

Then one of my functions which does not reference the full name of a 
table with its schema fails with "relation [rel-name] does not exist".


Is that a bug?  I have seen some old posts about this issue but am not 
sure if there is a ticket or why it still is an issue.


Looks like this might be by design.  I will follow the links at 
https://www.postgresql.org/message-id/flat/MWHPR14MB160079A6D9DC64A2F60E9004C0D00%40MWHPR14MB1600.namprd14.prod.outlook.com 
and ask more questions if I have them.


I might need to add the schema name to the table in my function.

Igal

Re: pg_dump and search_path

[Igal @ Lucee.org]

On 7/9/2019 7:02 AM, Adrian Klaver wrote:

On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:

I have a custom search_path:

# show search_path;
    search_path
--
  "staging, transient, pg_catalog"
(1 row)

I ran `pg_dump --schema-only` and the only reference in the output to 
search_path is:


   SELECT pg_catalog.set_config('search_path', '', false);

Then one of my functions which does not reference the full name of a 
table with its schema fails with "relation [rel-name] does not exist".


Where is this failing?

Do you have the search_path set in the config for the server you are 
dumping to?


It is failing during the Restore operation.  I can provide more 
information if I'll understand what you mean exactly by "Where".


search_path is not set int he config, but rather with ALTER DATABASE SET 
search_path TO ... but I have executed that prior to the RESTORE on the 
target database.  Would it make a difference if I set it in the config?


Thanks,

Igal

Re: pg_dump and search_path

[Igal @ Lucee.org]

David,

On 7/9/2019 7:49 AM, David G. Johnston wrote:

On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org <mailto:i...@lucee.org>> wrote:


search_path is not set int he config, but rather with ALTER
DATABASE SET
search_path TO ... but I have executed that prior to the RESTORE
on the
target database.  Would it make a difference if I set it in the
config?


What is your restore command then?  Because if you are dropping and 
recreating the same named database the ALTER DATABASE SET command is 
going to be lost with the drop since it is associated to an OID and 
not just the name.  By placing the search_path into postgres.conf you 
avoid that issue altogether.


The restore command is:

pg_restore.exe --verbose --single-transaction -h  -p  -d 
 -U postgres 


But how will I avoid the issue if the command  `SELECT 
pg_catalog.set_config('search_path', '', false);` is part of the pgdump 
file?  Wouldn't that override the config file setting during the restore 
process?


But, yes, objects saved to the database should usually have schema 
qualifications (which gets a bit messy with custom operators).  
search_path reliance should probably be reserved to interactive use or 
at worse client supplied queries.


In my case I use a separate Postgres cluster for each database and the 
roles, absent of any successful hacking, are all limited to trusted 
users, so the risk mentioned in the CVE is non-existent and it would be 
great if there was an option to turn off that "feature".


Thanks,

Igal

Re: pg_dump and search_path

[Igal @ Lucee.org]

On 7/9/2019 10:45 AM, Adrian Klaver wrote:

On 7/9/19 7:41 AM, Igal @ Lucee.org wrote:

On 7/9/2019 7:02 AM, Adrian Klaver wrote:

On 7/8/19 11:48 PM, Igal @ Lucee.org wrote:

I have a custom search_path:

# show search_path;
    search_path
--
  "staging, transient, pg_catalog"
(1 row)

I ran `pg_dump --schema-only` and the only reference in the output 
to search_path is:


   SELECT pg_catalog.set_config('search_path', '', false);

Then one of my functions which does not reference the full name of 
a table with its schema fails with "relation [rel-name] does not 
exist".


Where is this failing?

Do you have the search_path set in the config for the server you are 
dumping to?


It is failing during the Restore operation.  I can provide more 
information if I'll understand what you mean exactly by "Where".


Yes, because I cannot replicate with just a function:

CREATE OR REPLACE FUNCTION public.search_path_test(integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
    perform * from test_tbl;
    RETURN 1;
END;
$function$

test_(postgres)# \d test_tbl
   Table "test_schema.test_tbl"
 Column |  Type   | Collation | Nullable | Default
+-+---+--+-
 id | integer |

pg_dump -d test -U postgres -x -p 5412 -Fc -f dump_search_path.out
pg_restore --single-transaction -d test -c -U postgres -p 5412 
dump_search_path.out


SELECT pg_catalog.set_config('search_path', '', false);

postgres-2019-07-09 10:37:32.488 PDT-604LOG:  statement: CREATE 
FUNCTION public.search_path_test(integer) RETURNS integer

    LANGUAGE plpgsql
    AS $$
    BEGIN
    perform * from test_tbl;
    RETURN 1;
    END;
    $$;



postgres-2019-07-09 10:37:32.489 PDT-604LOG:  statement: ALTER 
FUNCTION public.search_path_test(integer) OWNER TO aklaver;



My guess is the function is being used somewhere.


I see.  Yes, the function is used by an INDEX.  So somewhere down the 
line in the pgdump file I have:


  CREATE INDEX ix_items_tags ON staging.items USING gin 
(staging.some_func_returning_array(col1));


Igal

DDL and DML in a transaction

[Igal @ Lucee.org]

I am trying to change a text column into a numeric one in a large table.

My idea was to add a new column, update it, drop the old column, and 
rename the new one to the old name.  I am hoping that that would make it 
faster and minimize locking time though I'm not sure that it would.


I am therefore trying to execute the following but I'm getting an error 
that the new column does not exist:


begin;
    alter table some_table
        add column if not exists amount_num numeric(30,12);

    update some_table
        set amount_num = amount_text::numeric(30,12);

    alter table some_table
        drop column amount_text;

    alter table some_table
        rename column amount_num to amount_text;

    alter table some_table
        drop column amount_num;

    commit;
end;

Am I missing something?  Is this supposed to work?

Would it have less locking than simply altering the column?

Thanks,

Igal

Re: DDL and DML in a transaction

[Igal @ Lucee.org]

Thank you, David.

I should get more sleep...

Igal

On 7/31/2019 11:52 AM, David G. Johnston wrote:

On Wed, Jul 31, 2019 at 11:38 AM Igal @ Lucee.org <mailto:i...@lucee.org>> wrote:


     alter table some_table
     rename column amount_num to amount_text;

 alter table some_table
     drop column amount_num;


You just renamed amount_num to amount_text so I'm not sure why you 
expect the drop to succeed.


Would it have less locking than simply altering the column?


I doubt anything will improve upon simply altering the column.  You 
have to perform a full table rewrite in either case which is going to 
be the main resource consumer.


David J.

How to check if a field exists in NEW in trigger

[Igal @ Lucee.org]

I have the following statement in a trigger:

    new.email = lower(new.email);

When I try to update a record without setting the email column however, 
I get an error:


SQL Error [42703]: ERROR: record "new" has no field "email"
  Where: SQL statement "SELECT lower(new.email)"
PL/pgSQL function on_record_modified() line 26 at assignment

I have seen some hacks suggesting TRY/CATCH or converting to a JSON and 
checking if the field exists, but I would think that there's a better 
way to check if the field is in the NEW record, no?


Any ideas?  Thanks!

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: How to check if a field exists in NEW in trigger

[Igal @ Lucee.org]

On 8/5/2019 11:34 AM, Michael Lewis wrote:

As a note to the original poster, you might want to check out-

https://www.postgresql.org/docs/current/citext.html


Thanks, Michael.  I'm familiar with the citext module.

There is no reason, however, for an email address to be not-lower-cased, 
so while in some cases (no pun intended) it makes sense to keep the 
original CaSe while performing a case insensitive comparison, when it 
comes to email addresses I rather collapse the value upon insertion/update.


Since that table has many more reads than writes, I am pretty sure that 
it's more performant too.


Best,

Igal

How to install Postgres 12 beta with YUM

[Igal @ Lucee.org]

I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
...

But when I try to find the version to install I do not see it, e.g. `yum 
list postgresql* | grep 12` doesn't show anything with version 12.


Any ideas?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

How to install Postgres 12 beta with YUM

[Igal @ Lucee.org]

I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
...

But when I try to find the version to install I do not see it, e.g. `yum 
list postgresql* | grep 12` doesn't show anything with version 12.


Any ideas?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: How to install Postgres 12 beta with YUM

[Igal @ Lucee.org]

Adrian,

On 8/22/2019 12:22 PM, Adrian Klaver wrote:


On 8/22/19 11:29 AM, Igal @ Lucee.org wrote:
I have installed the CentOS 7 PGDG file from 
https://yum.postgresql.org/repopackages.php


If I `cat /etc/yum.repos.d/pgdg-redhat-all.repo` I see:

# PGDG Red Hat Enterprise Linux / CentOS / Scientific Linux stable 
repositories:


[pgdg12]
name=PostgreSQL 12 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch 


enabled=0


Not a YUM user, but what if you change enabled to 1?


Thanks, that was indeed the issue (Joshua Drake pointed it to me offline 
earlier as it seemed that my emails are not posted to the list), but 
upon changing enabled to 1 and then running `yum update`, there is a new 
error:


failure: repodata/repomd.xml from pgdg12: [Errno 256] No more mirrors to 
try.
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/repodata/repomd.xml: 
[Errno 14] HTTPS Error 404 - Not Found


The XML data is missing.  If I change the URL to 11 then I get the XML 
file for Postgres 11.


Where should I report this issue?

Thank you,

Igal

Re: How to install Postgres 12 beta with YUM

[Igal @ Lucee.org]

Adrian,

On 8/22/2019 12:48 PM, Adrian Klaver wrote:


In addition to my previous post maybe change:

baseurl=https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch 



to:

baseurl=https://download.postgresql.org/pub/repos/testing/yum/12/redhat/rhel-$releasever-$basearch 



Almost!  "/testing" comes after "/yum" rather than before it, so it's

baseurl=https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-$releasever-$basearch

But before I figured that out I used the other link you posted and 
installed the 3 packages separately like so:


# yum install 
https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-7.6-x86_64/postgresql12-libs-12beta3-1PGDG.rhel7.x86_64.rpm


# yum install 
https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-7.6-x86_64/postgresql12-12beta3-1PGDG.rhel7.x86_64.rpm


# yum install 
https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-7.6-x86_64/postgresql12-server-12beta3-1PGDG.rhel7.x86_64.rpm


Thanks for your help,

Igal

database files are incompatible with server

[Igal @ Lucee.org]

I have a data directory that was created by Postgres 12 (I thought beta 
3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to set 
the cluster to the same PGDATA.  I have set the owner of the directory 
to postgres:postrgres, and the permissions to 0700, but I'm getting the 
following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


How can I start up the Cluster?

Is version 201906161 beta 2?

Would running PG12 beta 2 work?

Would running initdb destroy the data?

Thank you,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: database files are incompatible with server

[Igal @ Lucee.org]

Adrian,

On 8/22/2019 5:08 PM, Adrian Klaver wrote:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought 
beta 3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to 
set the cluster to the same PGDATA.  I have set the owner of the 
directory to postgres:postrgres, and the permissions to 0700, but I'm 
getting the following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


So the cluster is from an older version then the server.


How can I start up the Cluster?

Is version 201906161 beta 2?


Yes:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139 



Thanks for the link.  It's good to know where these things are stored.

Thank you,

Igal

Re: database files are incompatible with server

[Igal @ Lucee.org]

On 8/22/2019 5:08 PM, Adrian Klaver wrote:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:
I have a data directory that was created by Postgres 12 (I thought 
beta 3 but now am not sure anymore) running in Docker.


I have installed Postgres 12b3 as a Systemd service and am trying to 
set the cluster to the same PGDATA.  I have set the owner of the 
directory to postgres:postrgres, and the permissions to 0700, but I'm 
getting the following error:


2019-08-22 23:40:48.759 UTC [23044] FATAL:  database files are 
incompatible with server
2019-08-22 23:40:48.759 UTC [23044] DETAIL:  The database cluster was 
initialized with CATALOG_VERSION_NO  201906161, but the server was 
compiled with CATALOG_VERSION_NO 201907221.


So the cluster is from an older version then the server.



How can I start up the Cluster?

Is version 201906161 beta 2?


Yes:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139 



Would running PG12 beta 2 work?


Worth a try.


That worked.  I used the Docker image postgres:12-beta2 to bring up the 
server.


One issue that I kept having was error messages like chown: changing 
ownership of '...': Permission denied.  That turned out to be an SELinux 
issue on the CentOS machine.  I have set SELinux to permissive mode 
temporarily to bring the server up.  Sharing it here for the next guy 
(most likely my future self).


Thanks again,

Igal

Re: database files are incompatible with server

[Igal @ Lucee.org]

On 8/22/2019 9:15 PM, Tom Lane wrote:

Adrian Klaver  writes:

On 8/22/19 4:52 PM, Igal @ Lucee.org wrote:

How can I start up the Cluster?
Is version 201906161 beta 2?



Yes:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/catversion.h;h=fe44cae3ff453ccc02e6c56548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139

FWIW, if you have both beta2 and beta3 executables at hand, it should
work to do a pg_upgrade to convert the cluster to beta3.


Good to know!

Thank you,

Igal

Re: Case Insensitive Comparison with Postgres 12

[Igal @ Lucee.org]

On 10/9/2019 12:34 AM, Laurenz Albe wrote:

Igal Sapir wrote:

I am trying to test a simple case insensitive comparison.  Most likely the
collation that I chose is wrong, but I'm not sure how to choose the correct
one (for English/US?).  Here is my snippet:

create collation case_insensitive(
 provider=icu, locale='en-US-x-icu', deterministic=false
);
select 'Abc' = 'abc' collate case_insensitive;

I expected true but am getting false.

Any thoughts?

Yes, the LOCALE is wrong. Use

create collation case_insensitive (
provider=icu, locale='en-US-u-ks-level2', deterministic=false
);

The name of the locale defines it.

My blog post can give a simple introduction:
https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/


Thank you all for replying.  I tried to use the locale suggested by both 
Laurenz and Pavel, en-US-u-ks-level2, but I'm still getting false for a 
simple comparison of 'Abc' = 'abc'.  I tried the locale both as a 
'string' and as an "identifier":


> select version();

version |
---|
PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-39), 64-bit|


> drop collation if exists case_insensitive;

> create collation case_insensitive (
   provider=icu, locale="en-US-u-ks-level2", deterministic=false
);

> select 'Abc' = 'abc' collate case_insensitive as is_equal;

is_equal|
|
false   |

What am I doing wrong here?

Thanks,

Igal

Re: Case Insensitive Comparison with Postgres 12

[Igal @ Lucee.org]

Thomas,

On 10/10/2019 6:22 AM, Thomas Kellerer wrote:


Igal @ Lucee.org schrieb am 10.10.2019 um 14:41:

Thank you all for replying.  I tried to use the locale suggested by
both Laurenz and Pavel, en-US-u-ks-level2, but I'm still getting
false for a simple comparison of 'Abc' = 'abc'.  I tried the locale
both as a 'string' and as an "identifier":


drop collation if exists case_insensitive;
create collation case_insensitive (

    provider=icu, locale="en-US-u-ks-level2", deterministic=false
);


select 'Abc' = 'abc' collate case_insensitive as is_equal;

is_equal|
|
false   |

What am I doing wrong here?

Check the version of libicu that your Linux is using.
That locale format requires version 54 or later.
(My up-to-date CentOS 7.7 for example is still using version 50 and the EDB 
Windows binaries include version 53).

In another thread about ICU problems, Daniel Verite explained that in more 
detail:


With ICU 53 or older, instead of the locale above, we must use the old-style 
syntax:

  locale = 'de-DE@colStrength=secondary'

In your case I guess, it should be

locale = 'en-US@colStrength=secondary'


That works, thank you!

I also have CentOS installed on that machine: CentOS Linux release 
7.7.1908 (Core), showing libicu Version 50.2 via `yum info libicu`.


Best,

Igal

Function Volatility Stable vs Immutable

[Igal @ Lucee.org]

If a function select data from a table, and the rows in the table may 
change, would that function qualify for Immutable or does it have to be 
Stable?  I'm asking because according to the docs [1]: "An IMMUTABLE 
function cannot modify the database and is guaranteed to return the same 
results given the same arguments forever".


So for the same arguments, the result would change only if the data in 
the table changes.  Does that mean that it violates the "forever" clause 
and therefore can be only marked as Stable and not Immutable?


Thanks,

Igal

Re: Function Volatility Stable vs Immutable

[Igal @ Lucee.org]

On 6/24/2019 7:38 PM, David G. Johnston wrote:
On Mon, Jun 24, 2019 at 7:31 PM Igal @ Lucee.org <mailto:i...@lucee.org>> wrote:


If a function select data from a table, and the rows in the table may
change, would that function qualify for Immutable or does it have
to be
Stable?  I'm asking because according to the docs [1]: "An IMMUTABLE
function cannot modify the database and is guaranteed to return
the same
results given the same arguments forever".

So for the same arguments, the result would change only if the
data in
the table changes.  Does that mean that it violates the "forever"
clause
and therefore can be only marked as Stable and not Immutable?


Yes

Forever means beyond the lifetime of a single transaction and thus it 
is possible for the changing of the table contents to impact the 
return value of the function.


Thanks for clarifying, David.

Igal

Trigger function does not modify the NEW value

[Igal @ Lucee.org]

I have a trigger that is created like so:

    create trigger tr_on_table_modified after insert or delete or update
        on some_table for each row execute procedure on_table_modified();

    CREATE OR REPLACE FUNCTION on_table_modified() RETURNS trigger 
LANGUAGE plpgsql $$
        /* some code that does not modify any values but calls PERFORM 
on another function */


        raise notice 'lowercasing %', new.email;
        new.email = lower(new.email);
        raise notice '    to %', new.email;

        return new;
    $$

I can see in the output the notices with the expected values, but the 
value in the updated record is not lower-cased.


update some_table
set    email = 'i...@lucee.org'
where  id = 1;

> 0: lowercasing i...@lucee.org
> 0:     to i...@lucee.org

select email
from   some_table
where  id = 1;

> email |
> --|
> i...@lucee.org|

Any ideas?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: Trigger function does not modify the NEW value

[Igal @ Lucee.org]

On 6/28/2019 8:07 PM, David Rowley wrote:

On Sat, 29 Jun 2019 at 14:54, Igal @ Lucee.org  wrote:

 create trigger tr_on_table_modified after insert or delete or update
 on some_table for each row execute procedure on_table_modified();
I can see in the output the notices with the expected values, but the value in 
the updated record is not lower-cased.

You'll need a BEFORE trigger, not an AFTER trigger.
https://www.postgresql.org/docs/current/sql-createtrigger.html


*facepalm*

I was trying to "save" on creating a separate trigger so I added that to 
the bottom of an existing one and missed that.


Thanks David!

Igal

Group Roles with Inheritance

[Igal @ Lucee.org]

Hello,

I want to create three (group) roles.  The first one will be read-only, 
the second will add INSERT, and the third will add UPDATE and DELETE.


Does the below look OK for this purpose or did I forget something?


/** role_r is read-only with SELECT and EXECUTE */
CREATE ROLE role_r;

GRANT USAGE ON SCHEMA  TO role_r;

GRANT SELECT ON ALL TABLES IN SCHEMA  TO role_r;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA  TO role_r;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA  TO role_r;

ALTER DEFAULT PRIVILEGES IN SCHEMA 
    GRANT SELECT ON TABLES TO role_r;

ALTER DEFAULT PRIVILEGES IN SCHEMA 
    GRANT SELECT ON SEQUENCES TO role_r;


/** role_ra adds INSERT */
CREATE ROLE role_ra;
GRANT role_r TO role_ra;

GRANT INSERT ON ALL TABLES IN SCHEMA  TO role_ra;

ALTER DEFAULT PRIVILEGES IN SCHEMA 
    GRANT INSERT ON TABLES TO role_ra;


/** role_rawd adds UPDATE, DELETE */
CREATE ROLE role_rawd;
GRANT role_ra TO role_rawd;

GRANT INSERT ON ALL TABLES IN SCHEMA  TO role_rawd;

ALTER DEFAULT PRIVILEGES IN SCHEMA 
    GRANT UPDATE, DELETE ON TABLES TO role_rawd;


Thank you,


Igal Sapir
Lucee Core Developer
Lucee.org

Re: Scheme conversion MySQL to PGSQL

[Igal @ Lucee.org]

On 12/24/2017 10:18 AM, Andreas Kretschmer wrote:

On 24 December 2017 18:52:39 CET, Michelle Konzack 
 wrote:

I try to convert a Database scheme from mySQL to pgSQL and have
problems  with the line:

  KEY post_date (post_date)

and later probably with the lines

  UNIQUE KEY user   (stat_login)

That's just indexe. Create them later with create index ...


I agree with Andreas.  First create the tables, then create the 
indexes.  If you can copy the data before creating the indexes then you 
will probably save some time on all of the INSERTs.


I am doing a similar thing migrating from SQL Server, and I am 
considering to publish an open source Migrator.


Can you tell me about some of the data types that you had to change 
moving from MySQL to Postgres?


Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: Scheme conversion MySQL to PGSQL

[Igal @ Lucee.org]

Michelle,

On 12/24/2017 1:20 PM, Michelle Konzack wrote:

Hello Igal

Am 2017-12-24 hackte Igal @ Lucee.org in die Tasten:

I am doing a similar thing migrating from SQL Server, and I am
considering to publish an open source Migrator.

Can you tell me about some of the data types that you had to change
moving from MySQL to Postgres?

I convert only those four CREATE TABLE from mySQL to pgSQL which
is the forum pluging for squirrelmail.

auto_increment  ->   serial
int(NN) ->   int
datetime->   timestamptz  (attention:  it is NOT timestamp)
longtext->   text


Thank you.  My migration tool (written in Java) already supports 
migrating a SQL Server database to Postgres with most data types.


Perhaps I will improve it further when I have some more time to support 
MySQL source databases as well, in which case I will use the information 
that you sent as a starting point.


Best,

Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/>

pg_trgm Extension Installed but Operators and Functions do not exist

[Igal @ Lucee.org]

I am trying to run a query which was working a few weeks ago, and it 
utilizes the <<-> operator but I am getting an error: operator does not 
exist text <<-> text.


If I try instead the function word_similarity() I get a similar (no pun 
intended) error: function word_similarity(text, text) does not exist.


If I try to install the pg_trgm extension with `CREATE EXTENSION 
pg_trgm;` I get an error: extension "pg_trgm" already exists.


Running PostgreSQL 10.1, compiled by Visual C++ build 1800, 64-bit

Any ideas?  Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: pg_trgm Extension Installed but Operators and Functions do not exist

[Igal @ Lucee.org]

On 1/28/2018 12:35 PM, Tom Lane wrote:

"Igal @ Lucee.org"  writes:

I am trying to run a query which was working a few weeks ago, and it
utilizes the <<-> operator but I am getting an error: operator does not
exist text <<-> text.

If I try to install the pg_trgm extension with `CREATE EXTENSION
pg_trgm;` I get an error: extension "pg_trgm" already exists.

Seems like a likely bet is that the extension is installed in a schema
that isn't in your search_path.


Thank you, Tom.  I was unaware of the fact that extensions are installed 
to specific schemas.


For future users who might face this issue, I fixed it by re-creating 
the extension in the pg_catalog schema, which is always in the 
search_path [1]:


  DROP EXTENSION pg_trgm;

  CREATE EXTENSION pg_trgm SCHEMA pg_catalog;

Thanks again,

Igal

[1] 
https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-SEARCH-PATH

How to Optimize pg_trgm Performance

[Igal @ Lucee.org]

I want to use pg_trgm for auto-suggest functionality.  I created a 
Materialized View with the information that I need, with the relevant 
columns being (keywords text, rank int).  keywords is the column from 
which I build the tri-grams, and rank is some popularity factor so that 
popular results will show up higher than less popular results given the 
same tri-gram distance.


I want to return results in the order of [distance], [distance_word], 
[rank].  The input comes from the user and is not known in advance.  My 
query is as follows:


    SELECT title
    ,id
    ,(input <-> keywords) AS distance
        ,(input <<-> keywords) AS distance_word
    ,rank
    FROM  (VALUES (cast('red pill' AS text))) consts(input)
    ,mv_autosuggest
    ORDER BY 3, 4, 5
    LIMIT 20;

This gives me pretty good results, but it takes too long and is not 
likely to scale well.


I have created two indexes but neither seem to be used:

CREATE INDEX mv_autosuggest_keywords_tgrm_gist ON staging.mv_autosuggest 
USING gist (keywords gist_trgm_ops);


CREATE INDEX mv_autosuggest_keywords_tgrm_gin ON staging.mv_autosuggest 
USING gin (keywords gin_trgm_ops);


This is the result of explain analyze:

QUERY PLAN |
---|
Limit  (cost=356.41..356.46 rows=20 width=51) (actual 
time=163.132..163.135 rows=20 
loops=1)   |
  ->  Sort  (cost=356.41..372.96 rows=6619 width=51) (actual 
time=163.130..163.131 rows=20 
loops=1)    |
    Sort Key: (('red pill'::text <-> mv_autosuggest.keywords)), 
(('red pill'::text <<-> mv_autosuggest.keywords)), mv_autosuggest.rank |

    Sort Method: top-N heapsort  Memory: 28kB |
    ->  Seq Scan on mv_autosuggest  (cost=0.00..180.29 rows=6619 
width=51) (actual time=0.263..161.289 rows=6619 loops=1)  |

Planning time: 0.139 ms |
Execution time: 163.174 ms |

How can I improve the performance here?

Thank you,

Igal Sapir
Lucee Core Developer
Lucee.org 

Consider Spaces in pg_trgm for Better Similarity

[Igal @ Lucee.org]

Is there a way to consider white space in tri-grams?  That would allow 
for better matches of phrases.


For example, currently "one two three" and "three two one" would 
generate the same tri-grams ({  o,  t, on, th, tw,ee ,hre,ne 
,one,ree,thr,two,wo }), and the distance of "one two four" will be the 
same for both of them.  The query:


SELECT   phrase
        ,input
        ,similarity(t1.phrase, t2.input)
        ,word_similarity(t1.phrase, t2.input)
FROM      (values('one two three'),('three two one')) t1(phrase)
        ,(values('one two four')) t2(input);

Returns:

phrase    |input    |similarity  |word_similarity |
--|-|||
one two three |one two four |0.8 |0.615384638 |
three two one |one two four |0.8 |0.615384638 |

But surely "one two four" is more similar to "one two three" than to 
"three two one".


Any thoughts?

Igal Sapir
Lucee Core Developer
Lucee.org 

DOW is 0-based?

[Igal @ Lucee.org]

Is there a rational reason why Day of the Week is 0-based, i.e. Sunday 
(0) to Saturday (6) instead of the more intuitive Sunday (1) to Saturday 
(7)?


    SELECT date_part('dow', current_date);

https://www.postgresql.org/docs/current/static/functions-datetime.html

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org