Re: array_agg to array

[Pavel Stehule]

Hi

2018-05-16 8:14 GMT+02:00 Philipp Kraus :

> Hello,
>
> I have got a function with a reg expr to split chemical formulas e.g. H2O
> -> H2 O.
>
> CREATE OR REPLACE FUNCTION daimon.text2sumformula(text) RETURNS text[] AS
> $$
> select array_agg(i::text) as e from ( select unnest( regexp_matches(
> $1, '[0-9]*[A-Z][a-z]?\d*|\((?:[^()]*(?:\(.*\))?[^()]*)+\)\d+', 'g') ) )
> i;
> $$ LANGUAGE SQL IMMUTABLE;
>
> For H2O I get an array with {(H2),(O)}
> How I can return the inner elements as text, I would like to get {H2,O}
> without round brackets?
>
> Thanks
>

maybe you want array_to_string function

postgres=# select array['a','b'];
┌───┐
│ array │
╞═══╡
│ {a,b} │
└───┘
(1 row)

postgres=# select array_to_string(array['a','b'],'');
┌─┐
│ array_to_string │
╞═╡
│ ab  │
└─┘
(1 row)

Regards

Pavel

>
> Phil
>
>
>
>

Re: Function to set up variable inside it

[Pavel Stehule]

Hi

2018-05-16 6:52 GMT+02:00 Łukasz Jarych :

> Hi Guys,
>
> thank you for your help !
>
> Hmm yes something like this.
>
> I was wondering if is possible to pass variable into function:
>
> CREATE FUNCTION change_trigger() RETURNS trigger AS $$
>
> BEGIN
>
> IF  TG_OP = 'INSERT'
>
> THEN
>
> INSERT INTO logging.t_history (tabname, schemaname, 
> operation, new_val)
>
> VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, 
> row_to_json(NEW));
>
> RETURN NEW;
>
> ELSIF   TG_OP = 'UPDATE'
>
> THEN
>
> INSERT INTO logging.t_history (tabname, schemaname, 
> operation, new_val, old_val)
>
> VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
>
> row_to_json(NEW), row_to_json(OLD));
>
> RETURN NEW;
>
> ELSIF   TG_OP = 'DELETE'
>
> THEN
>
> INSERT INTO logging.t_history (tabname, schemaname, 
> operation, old_val)
>
> VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, 
> row_to_json(OLD));
>
> RETURN OLD;
>
> END IF;
>
> END;
>
> $$ LANGUAGE 'plpgsql' SECURITY DEFINER;
>
> Best,
> Jacek
>

It is possible to pass values into functions -

postgres=# select fx(10);
NOTICE:  >>>10<<<
┌┐
│ fx │
╞╡
││
└┘
(1 row)

postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx(a integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
  raise notice '>>>%<<<', a;
end;
$function$

but it is not possible to pass parameters to trigger functions. You can
define trigger parameters when you define trigger, but these values must be
constant.

a) the request of trigger parametrization is usually signal of bad using of
triggers - probably you should to use a function, not trigger

b) there is workaround - but you should not to use it if it is not really
necessary

There are few implementations of session variables in postgres - you can
find via google. Then you can set session variable before SQL command, and
you can read this session variable inside trigger function.

Regards

Pavel


>
> 2018-05-15 14:58 GMT+02:00 Adrian Klaver :
>
>> On 05/15/2018 05:28 AM, Łukasz Jarych wrote:
>>
>>> Hi Guys,
>>>
>>> I am using postgres 10.3 (or 4?).
>>> IT is possible to set up variable inside function?
>>>
>>
>> Like this?:
>>
>> https://www.postgresql.org/docs/10/static/plpgsql-declarations.html
>>
>>
>>> Best,
>>> Jacek
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: array_agg to array

[Torsten Förtsch]

On Wed, May 16, 2018 at 8:14 AM, Philipp Kraus <
philipp.kr...@tu-clausthal.de> wrote:

> Hello,
>
> I have got a function with a reg expr to split chemical formulas e.g. H2O
> -> H2 O.
>
> CREATE OR REPLACE FUNCTION daimon.text2sumformula(text) RETURNS text[] AS
> $$
> select array_agg(i::text) as e from ( select unnest( regexp_matches(
> $1, '[0-9]*[A-Z][a-z]?\d*|\((?:[^()]*(?:\(.*\))?[^()]*)+\)\d+', 'g') ) )
> i;
> $$ LANGUAGE SQL IMMUTABLE;
>
> For H2O I get an array with {(H2),(O)}
> How I can return the inner elements as text, I would like to get {H2,O}
> without round brackets?
>

like this?

postgres=# select array_agg(i[1]) as e from regexp_matches( 'H2O',
'[0-9]*[A-Z][a-z]?\d*|\((?:[^()]*(?:\(.*\))?[^()]*)+\)\d+', 'g') t(i);
  e

{H2,O}
(1 row)

Re: array_agg to array

[Philipp Kraus]

> Am 16.05.2018 um 09:10 schrieb Torsten Förtsch :
> 
> select array_agg(i[1]) as e from regexp_matches( 'H2O', 
> '[0-9]*[A-Z][a-z]?\d*|\((?:[^()]*(?:\(.*\))?[^()]*)+\)\d+', 'g') t(i);

perfect, this helps with the t(i) call

Phil


signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: Out of memory error with PG10.3, 10.4 but not 9.3.19

[Michael Paquier]

On Tue, May 15, 2018 at 03:02:48PM +, ChatPristi wrote:
> I got the error with PG 10.3 and 10.4 on CentOS 7 up-to-date.The
> command works with a smaller size database. 
> The command works with the same database with PG 9.3.19 on RHEL 6.9
> up-to-date.
> 
> I attach the EXPLAIN SELECT command.

Well, instead of an explain output which takes 2.4MB compressed and
9.6MB uncompressed (take it as unreadable), could you produce a
self-contained test case with a glimpse of the schema you are using?
Where does the OOM happen, and how did you change your partitioned table
schema?  Are you using the native partitioning instead?
--
Michael


signature.asc
Description: PGP signature

sql function with empty row

[Philipp Kraus]

Hello,

I have defined a SQL function 

CREATE OR REPLACE FUNCTION substancetrivialname(text)
RETURNS substance
LANGUAGE 'sql'
COST 100
VOLATILE 
AS $BODY$
select s.* from substancetrivialname n 
join substance s on s.id = n.idsubstance
where lower(btrim(n.name)) = lower(btrim($1));
$BODY$;

substance and substancetrivialname have got a 1-to-N relationship (for each 
substance can exist multiple trivial names).
If I call the function with a non-existing trivial name it returns a single row 
with all fields are set to NULL.

If I run the join query directly it returns an empty record set on a 
non-existing trivial name.
I expected equal behavior on my function, so my question is, how can I fix this?

Thanks

Phil

Re: sql function with empty row

[Adrian Klaver]

On 05/16/2018 11:07 AM, Philipp Kraus wrote:

Hello,

I have defined a SQL function

CREATE OR REPLACE FUNCTION substancetrivialname(text)
 RETURNS substance
 LANGUAGE 'sql'
 COST 100
 VOLATILE
AS $BODY$
select s.* from substancetrivialname n
 join substance s on s.id = n.idsubstance
where lower(btrim(n.name)) = lower(btrim($1));
$BODY$;

substance and substancetrivialname have got a 1-to-N relationship (for each 
substance can exist multiple trivial names).
If I call the function with a non-existing trivial name it returns a single row 
with all fields are set to NULL.


Since there can be many trivial names per substance shouldn't you be 
using SETOF?:


https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET



If I run the join query directly it returns an empty record set on a 
non-existing trivial name.
I expected equal behavior on my function, so my question is, how can I fix this?

Thanks

Phil






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: sql function with empty row

[Adrian Klaver]

On 05/16/2018 11:07 AM, Philipp Kraus wrote:

Hello,

I have defined a SQL function

CREATE OR REPLACE FUNCTION substancetrivialname(text)
 RETURNS substance
 LANGUAGE 'sql'
 COST 100
 VOLATILE
AS $BODY$
select s.* from substancetrivialname n
 join substance s on s.id = n.idsubstance
where lower(btrim(n.name)) = lower(btrim($1));
$BODY$;

substance and substancetrivialname have got a 1-to-N relationship (for each 
substance can exist multiple trivial names).
If I call the function with a non-existing trivial name it returns a single row 
with all fields are set to NULL.


Ignore my previous post I got turned around on what was being returned.



If I run the join query directly it returns an empty record set on a 
non-existing trivial name.
I expected equal behavior on my function, so my question is, how can I fix this?

Thanks

Phil






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: sql function with empty row

[Melvin Davidson]

On Wed, May 16, 2018 at 2:25 PM, Adrian Klaver 
wrote:

> On 05/16/2018 11:07 AM, Philipp Kraus wrote:
>
>> Hello,
>>
>> I have defined a SQL function
>>
>> CREATE OR REPLACE FUNCTION substancetrivialname(text)
>>  RETURNS substance
>>  LANGUAGE 'sql'
>>  COST 100
>>  VOLATILE
>> AS $BODY$
>> select s.* from substancetrivialname n
>>  join substance s on s.id = n.idsubstance
>> where lower(btrim(n.name)) = lower(btrim($1));
>> $BODY$;
>>
>> substance and substancetrivialname have got a 1-to-N relationship (for
>> each substance can exist multiple trivial names).
>> If I call the function with a non-existing trivial name it returns a
>> single row with all fields are set to NULL.
>>
>
> Ignore my previous post I got turned around on what was being returned.
>
>
>
>> If I run the join query directly it returns an empty record set on a
>> non-existing trivial name.
>> I expected equal behavior on my function, so my question is, how can I
>> fix this?
>>
>> Thanks
>>
>> Phil
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
*I would start by changing*
*  RETURNS substance*
*to*
*RETURNS RECORD*

*Note: you might also conside using RETURNS TABLE(...)*

*https://www.postgresql.org/docs/10/static/sql-createfunction.html
*


*You might also want to consider adding LIMIT 1 to the end of the query.*

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: sql function with empty row

[Adrian Klaver]

On 05/16/2018 11:07 AM, Philipp Kraus wrote:

Hello,

I have defined a SQL function

CREATE OR REPLACE FUNCTION substancetrivialname(text)
 RETURNS substance
 LANGUAGE 'sql'
 COST 100
 VOLATILE
AS $BODY$
select s.* from substancetrivialname n
 join substance s on s.id = n.idsubstance
where lower(btrim(n.name)) = lower(btrim($1));
$BODY$;

substance and substancetrivialname have got a 1-to-N relationship (for each 
substance can exist multiple trivial names).
If I call the function with a non-existing trivial name it returns a single row 
with all fields are set to NULL.


Well I was on the right track for the wrong reason. At any rate SETOF works:

select * from cell_per where cell_per = 100;
 line_id | category | cell_per | ts_insert | ts_update | user_insert | 
user_update | plant_type | season | short_category

-+--+--+---+---+-+-+++
(0 rows)

CREATE OR REPLACE FUNCTION cp(integer)
RETURNS cell_per
LANGUAGE 'sql'
AS $BODY$
select cell_per.* from cell_per where cell_per = $1;
$BODY$;


select * from cp(100);
 line_id | category | cell_per | ts_insert | ts_update | user_insert | 
user_update | plant_type | season | short_category

-+--+--+---+---+-+-+++
NULL | NULL | NULL | NULL  | NULL  | NULL| 
NULL| NULL   | NULL   | NULL

(1 row)


CREATE OR REPLACE FUNCTION cp(integer)
RETURNS SETOF cell_per
LANGUAGE 'sql'
AS $BODY$
select cell_per.* from cell_per where cell_per = $1;
$BODY$;


select * from cp(100);
line_id | category | cell_per | ts_insert | ts_update | user_insert | 
user_update | plant_type | season | short_category

-+--+--+---+---+-+-+++
(0 rows)



If I run the join query directly it returns an empty record set on a 
non-existing trivial name.
I expected equal behavior on my function, so my question is, how can I fix this?

Thanks

Phil






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: sql function with empty row

[Philipp Kraus]

> Am 16.05.2018 um 20:40 schrieb Adrian Klaver :
> 
> On 05/16/2018 11:07 AM, Philipp Kraus wrote:
>> Hello,
>> I have defined a SQL function
>> CREATE OR REPLACE FUNCTION substancetrivialname(text)
>> RETURNS substance
>> LANGUAGE 'sql'
>> COST 100
>> VOLATILE
>> AS $BODY$
>> select s.* from substancetrivialname n
>> join substance s on s.id = n.idsubstance
>>  where lower(btrim(n.name)) = lower(btrim($1));
>> $BODY$;
>> substance and substancetrivialname have got a 1-to-N relationship (for each 
>> substance can exist multiple trivial names).
>> If I call the function with a non-existing trivial name it returns a single 
>> row with all fields are set to NULL.
> 
> Well I was on the right track for the wrong reason. At any rate SETOF works:
> 
> select * from cell_per where cell_per = 100;
> line_id | category | cell_per | ts_insert | ts_update | user_insert | 
> user_update | plant_type | season | short_category
> -+--+--+---+---+-+-+++
> (0 rows)
> 
> CREATE OR REPLACE FUNCTION cp(integer)
>RETURNS cell_per
>LANGUAGE 'sql'
> AS $BODY$
> select cell_per.* from cell_per where cell_per = $1;
> $BODY$;
> 
> 
> select * from cp(100);
> line_id | category | cell_per | ts_insert | ts_update | user_insert | 
> user_update | plant_type | season | short_category
> -+--+--+---+---+-+-+++
>NULL | NULL | NULL | NULL  | NULL  | NULL| NULL
> | NULL   | NULL   | NULL
> (1 row)
> 
> 
> CREATE OR REPLACE FUNCTION cp(integer)
>RETURNS SETOF cell_per
>LANGUAGE 'sql'
> AS $BODY$
> select cell_per.* from cell_per where cell_per = $1;
> $BODY$;
> 
> 
> select * from cp(100);
> line_id | category | cell_per | ts_insert | ts_update | user_insert | 
> user_update | plant_type | season | short_category
> -+--+--+---+---+-+-+++
> (0 rows)

I have tested it on my data and it works also, but that is a little bit 
confusing, because imho setof is >= 0 rows and
without setof it is [0,1]. On this I know there exist only one or no record, so 
I choose the solution without setof

Thanks for help

Phil 

Re: sql function with empty row

[Adrian Klaver]

On 05/16/2018 11:49 AM, Philipp Kraus wrote:



Am 16.05.2018 um 20:40 schrieb Adrian Klaver :





I have tested it on my data and it works also, but that is a little bit confusing, 
because imho setof is >= 0 rows and
without setof it is [0,1]. On this I know there exist only one or no record, so 
I choose the solution without setof


I gotta believe the difference is:

RETURNS substance
https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS
"
If the function is defined to return a composite type, the table 
function produces a column for each attribute of the composite type."


RETURNS SETOF substance

https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

"When an SQL function is declared as returning SETOF sometype, the 
function's final query is executed to completion, and each row it 
outputs is returned as an element of the result set."





Thanks for help

Phil




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Rapid disk usage spikes when updating large tables with GIN indexes

[Jonathan Marks]

Hi Tom —

We turned on log_temp_files and since the last stats reset (about a week ago) 
we’re seeing 0 temp files altogether (grabbing that info from pg_stat_database).

So, as far as we know:

1) It’s not WAL
2) It’s not tempfiles
3) It’s not the size of the error logs
4) It’s not the size of the actual rows in the database or the indexes

Another thread we found suggested pg_subtrans — this seems less likely because 
we’ve been able to replicate this across many different types of connections 
etc. but thought it might be a potential source.

Any other system-monitoring queries that we can run that might further 
illuminate the issue?

Thank you!

> On May 14, 2018, at 3:31 PM, Jonathan Marks  
> wrote:
> 
> We’ll turn on log_temp_files and get back to you to see if that’s the cause. 
> Re: the exact queries — these are just normal INSERTs and UPDATEs. This 
> occurs as part of normal database operations — i.e., we are processing 10% of 
> a table and marking changes to a particular row, or happen to be inserting 
> 5-10% of the table volume with new rows. Whenever we bulk load we have to 
> drop the indexes because the disk space loss just isn’t tenable.
> 
> Re: extra disk space consumption not within PG — the AWS folks can’t tell me 
> what the problem is because it’s all internal to the PG part of the instance 
> they can’t access. Doesn’t mean your last suggestion can’t be the case but 
> makes it slightly less likely.
> 
> Any chance that GIN indexes are double-logging? I.e. with fastupdate off they 
> are still trying to keep track of the changes in the pending list or 
> something?
> 
> Our thought has been temp files for a while, but we’re not sure what we 
> should do if that turns out to be the case.
> 
>> On May 14, 2018, at 3:08 PM, Tom Lane  wrote:
>> 
>> [ please keep the list cc'd ]
>> 
>> Jonathan Marks  writes:
>>> Thanks for your quick reply. Here’s a bit more information:
>>> 1) to measure the “size of the database” we run something like `select 
>>> datname, pg_size_pretty(pg_database_size(datname)) from pg_database;` I’m 
>>> not sure if this includes WAL size.
>>> 2) I’ve tried measuring WAL size with `select sum(size) from 
>>> pg_ls_waldir();` — this also doesn’t budge.
>>> 3) Our current checkpoint_timeout is 600s with a 
>>> checkpoint_completion_target of 0.9 — what does that suggest?
>> 
>> Hmph.  Your WAL-size query seems on point, and that pretty much destroys
>> my idea about a WAL emission spike.
>> 
>> pg_database_size() should include all regular and temporary tables/indexes
>> in the named DB.  It doesn't include WAL (but we've eliminated that), nor
>> cluster-wide tables such as pg_database (but those seem pretty unlikely
>> to be at issue), nor non-relation temporary files such as sort/hash temp
>> space.  At this point I think we have to focus our attention on what might
>> be creating large temp files.  I do not see anything in the GIN index code
>> that could do that, especially not if you have fastupdate off.  I wonder
>> whether there is something about the particular bulk-insertion queries
>> you're using that could result in large temp files --- which'd make the
>> apparent correlation with GIN index use a mirage, but we're running out
>> of other ideas.  You could try enabling log_temp_files to see if there's
>> anything to that.
>> 
>> In the grasping-at-straws department: are you quite sure that the extra
>> disk space consumption is PG's to begin with, rather than something
>> outside the database entirely?
>> 
>>  regards, tom lane
> 

Re: Rapid disk usage spikes when updating large tables with GIN indexes

[Tom Lane]

Jonathan Marks  writes:
> We turned on log_temp_files and since the last stats reset (about a week ago) 
> we’re seeing 0 temp files altogether (grabbing that info from 
> pg_stat_database).

Hm.

> Another thread we found suggested pg_subtrans — this seems less likely 
> because we’ve been able to replicate this across many different types of 
> connections etc. but thought it might be a potential source.

We're running out of other ideas, so maybe, but it's kind of hard to
credit large numbers of gigabytes going into pg_subtrans et al.
Still, you could easily adapt your WAL-size query to track the sizes
of other DB subdirectories and see if anything springs out.  Since
I'm really feeling a bit baffled at this point, I'd suggest watching
all of them:

pg_commit_ts/
pg_dynshmem/
pg_logical/mappings/
pg_logical/snapshots/
pg_multixact/members/
pg_multixact/offsets/
pg_logical/
pg_multixact/
pg_notify/
pg_replslot/
pg_serial/
pg_snapshots/
pg_stat/
pg_stat_tmp/
pg_subtrans/
pg_tblspc/
pg_twophase/
pg_wal/
pg_xact/

regards, tom lane

Re: Rapid disk usage spikes when updating large tables with GIN indexes

[Nikolay Samokhvalov]

Why not set up a spot EC2 instance with Postgres 10.1, load database from a
dump (yes you’ll need to create one from RDS because they don’t provide
direct access to dumps/backups; probably you need to get only specific
tables) and repeat your actions, closely looking at filesystem.

ср, 16 мая 2018 г. в 13:10, Jonathan Marks :

> Hi Tom —
>
> We turned on log_temp_files and since the last stats reset (about a week
> ago) we’re seeing 0 temp files altogether (grabbing that info from
> pg_stat_database).
>
> So, as far as we know:
>
> 1) It’s not WAL
> 2) It’s not tempfiles
> 3) It’s not the size of the error logs
> 4) It’s not the size of the actual rows in the database or the indexes
>
> Another thread we found suggested pg_subtrans — this seems less likely
> because we’ve been able to replicate this across many different types of
> connections etc. but thought it might be a potential source.
>
> Any other system-monitoring queries that we can run that might further
> illuminate the issue?
>
> Thank you!
>
> > On May 14, 2018, at 3:31 PM, Jonathan Marks <
> jonathanaveryma...@gmail.com> wrote:
> >
> > We’ll turn on log_temp_files and get back to you to see if that’s the
> cause. Re: the exact queries — these are just normal INSERTs and UPDATEs.
> This occurs as part of normal database operations — i.e., we are processing
> 10% of a table and marking changes to a particular row, or happen to be
> inserting 5-10% of the table volume with new rows. Whenever we bulk load we
> have to drop the indexes because the disk space loss just isn’t tenable.
> >
> > Re: extra disk space consumption not within PG — the AWS folks can’t
> tell me what the problem is because it’s all internal to the PG part of the
> instance they can’t access. Doesn’t mean your last suggestion can’t be the
> case but makes it slightly less likely.
> >
> > Any chance that GIN indexes are double-logging? I.e. with fastupdate off
> they are still trying to keep track of the changes in the pending list or
> something?
> >
> > Our thought has been temp files for a while, but we’re not sure what we
> should do if that turns out to be the case.
> >
> >> On May 14, 2018, at 3:08 PM, Tom Lane  wrote:
> >>
> >> [ please keep the list cc'd ]
> >>
> >> Jonathan Marks  writes:
> >>> Thanks for your quick reply. Here’s a bit more information:
> >>> 1) to measure the “size of the database” we run something like `select
> datname, pg_size_pretty(pg_database_size(datname)) from pg_database;` I’m
> not sure if this includes WAL size.
> >>> 2) I’ve tried measuring WAL size with `select sum(size) from
> pg_ls_waldir();` — this also doesn’t budge.
> >>> 3) Our current checkpoint_timeout is 600s with a
> checkpoint_completion_target of 0.9 — what does that suggest?
> >>
> >> Hmph.  Your WAL-size query seems on point, and that pretty much destroys
> >> my idea about a WAL emission spike.
> >>
> >> pg_database_size() should include all regular and temporary
> tables/indexes
> >> in the named DB.  It doesn't include WAL (but we've eliminated that),
> nor
> >> cluster-wide tables such as pg_database (but those seem pretty unlikely
> >> to be at issue), nor non-relation temporary files such as sort/hash temp
> >> space.  At this point I think we have to focus our attention on what
> might
> >> be creating large temp files.  I do not see anything in the GIN index
> code
> >> that could do that, especially not if you have fastupdate off.  I wonder
> >> whether there is something about the particular bulk-insertion queries
> >> you're using that could result in large temp files --- which'd make the
> >> apparent correlation with GIN index use a mirage, but we're running out
> >> of other ideas.  You could try enabling log_temp_files to see if there's
> >> anything to that.
> >>
> >> In the grasping-at-straws department: are you quite sure that the extra
> >> disk space consumption is PG's to begin with, rather than something
> >> outside the database entirely?
> >>
> >>  regards, tom lane
> >
>
>
>

Re: sql function with empty row

[David G. Johnston]

On Wed, May 16, 2018 at 11:49 AM, Philipp Kraus <
philipp.kr...@tu-clausthal.de> wrote:

> I have tested it on my data and it works also, but that is a little bit
> confusing, because imho setof is >= 0 rows and
> without setof it is [0,1].


​Without setof it will always return exactly 1 row, never 0.  This is a
feature as returning zero rows is problematic in many ways; a 0-row result
in a select-list will cause that row to be removed from the result when is
most cases people would rather be told whatever they were trying to compute
for the row simply didn't compute - via a NULL.

David J.

When use triggers?

[hmidi slim]

HI,

I'm working on a microservice application and I avoid using triggers
because they will not be easy to maintain and need an experimented person
in database administration to manage them. So I prefer to manage the work
in the application using ORM and javascript.
However I want to get some opinions and advices about using triggers: when
should I use them? How to manage them when there are some problems?

Re: When use triggers?

[Adrian Klaver]

On 05/16/2018 03:19 PM, hmidi slim wrote:

HI,

I'm working on a microservice application and I avoid using triggers 
because they will not be easy to maintain and need an experimented 
person in database administration to manage them. So I prefer to manage 
the work in the application using ORM and javascript.
However I want to get some opinions and advices about using triggers: 
when should I use them? How to manage them when there are some problems?


Two benefits triggers and their associated functions offer, with the 
understanding these are general statements:


1) Are in the database so tasks that always need happen do not need to 
be replicated across the various interfaces that touch the database.


2) Also since they run on the server the data does not necessarily cross 
a network, so less network bandwidth and better performance.


An example of a use case is table auditing. If you need to track what is 
being INSERTed/UPDATEd/DELETEd in a table stick an audit 
trigger/function on the table and push the information to an audit table.


As to managing, they are like any other code. I keep my schema code in 
scripts under version control and deploy them from there. I use 
Sqitch(http://sqitch.org/) which allows me to deploy and revert changes. 
I use a dev database to test and troubleshoot triggers and functions.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: When use triggers?

[Benjamin Scherrey]

I've always found it most useful to consider the difference between "what
the system is" vs. "what the system does". The core data entities and their
stable relationships comprise most of what the system is. These are the
things that should be enforced at the lowest level possible (in a db
schema) and can be supported by other tools preferably as close to the
database as possible - such as triggers and stored procedures. The purpose
of these items should be strictly regulated to keeping the integrity of
these entities intact. They should not presume a particular usage model nor
should they implement application-specific business logic (which is
something that changes more frequently).

The various applications and businesses logic comprise "what the system
does". Those entities now play various (often dynamic) roles in the context
of your use cases. This is what should be implemented in your various
programming languages and should be independent of any specifics of your
database (or any particular UI as well if possible). This makes testing the
application much easier and your application is also easier to write if it
can assume that integrity constraints are already enforced at a lower level
by the DB itself. This business functionality generally should not be
implemented within the DB as it tightly couples the DB and the app which
gets really painful as the app evolves.

Hope that general set of policies helps guide your thinking and makes your
efforts more effective.

  - - Ben Scherrey

On Thu, May 17, 2018, 5:20 AM hmidi slim  wrote:

> HI,
>
> I'm working on a microservice application and I avoid using triggers
> because they will not be easy to maintain and need an experimented person
> in database administration to manage them. So I prefer to manage the work
> in the application using ORM and javascript.
> However I want to get some opinions and advices about using triggers: when
> should I use them? How to manage them when there are some problems?
>

What is the C function to modify attribute

[a]

Hi:


I am writing a trigger that will record all adjustment of the row into the last 
column of the row. I have done a iterator to produce text record and the last 
step would be add the text record into the last column (which designed as an 
array of user defined type, with the output function display the abstraction of 
update and a user defined function is used to retrieve detail update info). 


Now I would like to find a way that will allow me to update the last column in 
C efficiently.


Can anyone help? Thank you so much!!


Shore

Re: What is the C function to modify attribute

[David Rowley]

On 17 May 2018 at 15:36, a <372660...@qq.com> wrote:
> I am writing a trigger that will record all adjustment of the row into the
> last column of the row. I have done a iterator to produce text record and
> the last step would be add the text record into the last column (which
> designed as an array of user defined type, with the output function display
> the abstraction of update and a user defined function is used to retrieve
> detail update info).
>
> Now I would like to find a way that will allow me to update the last column
> in C efficiently.
>
> Can anyone help? Thank you so much!!

You might find https://www.postgresql.org/docs/10/static/triggers.html useful.

There's a complete example of a trigger function written in C there.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: sql function with empty row

[Laurenz Albe]

Philipp Kraus wrote:
> I have defined a SQL function 
> 
> CREATE OR REPLACE FUNCTION substancetrivialname(text)
> RETURNS substance
> LANGUAGE 'sql'
> COST 100
> VOLATILE 
> AS $BODY$
> select s.* from substancetrivialname n 
> join substance s on s.id = n.idsubstance
>   where lower(btrim(n.name)) = lower(btrim($1));
> $BODY$;
> 
> substance and substancetrivialname have got a 1-to-N relationship (for each 
> substance can exist multiple trivial names).
> If I call the function with a non-existing trivial name it returns a single 
> row with all fields are set to NULL.
> 
> If I run the join query directly it returns an empty record set on a 
> non-existing trivial name.
> I expected equal behavior on my function, so my question is, how can I fix 
> this?

The difference is that the function has to return exactly one value,
while the query it at liberty to return 0, 1 or more rows.

Since there is no result, it returns a NULL value.
What you are seeing is a valid composite NULL value:

SELECT ROW(NULL, NULL) IS NULL;

 ?column? 
--
 t
(1 row)

It looks weird, but the SQL standard wants it that way.
NULLs and composite types is a topic that can really twist your brain.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Function to set up variable inside it

[Łukasz Jarych]

Thank you Pavel!  I wilk set Up variable before  function and use it Inside 
function , best jacek

Pobierz aplikację Outlook dla systemu iOS

From: Pavel Stehule 
Sent: Wednesday, May 16, 2018 9:07:15 AM
To: Łukasz Jarych
Cc: Adrian Klaver; pgsql-gene...@postgresql.org >> PG-General Mailing List
Subject: Re: Function to set up variable inside it

Hi

2018-05-16 6:52 GMT+02:00 Łukasz Jarych 
mailto:jarys...@gmail.com>>:
Hi Guys,

thank you for your help !

Hmm yes something like this.

I was wondering if is possible to pass variable into function:


CREATE FUNCTION change_trigger() RETURNS trigger AS $$

BEGIN

IF  TG_OP = 'INSERT'

THEN

INSERT INTO logging.t_history (tabname, schemaname, 
operation, new_val)

VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, 
row_to_json(NEW));

RETURN NEW;

ELSIF   TG_OP = 'UPDATE'

THEN

INSERT INTO logging.t_history (tabname, schemaname, 
operation, new_val, old_val)

VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,

row_to_json(NEW), row_to_json(OLD));

RETURN NEW;

ELSIF   TG_OP = 'DELETE'

THEN

INSERT INTO logging.t_history (tabname, schemaname, 
operation, old_val)

VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, 
row_to_json(OLD));

RETURN OLD;

END IF;

END;

$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

Best,
Jacek

It is possible to pass values into functions -

postgres=# select fx(10);
NOTICE:  >>>10<<<
┌┐
│ fx │
╞╡
││
└┘
(1 row)

postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx(a integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
  raise notice '>>>%<<<', a;
end;
$function$

but it is not possible to pass parameters to trigger functions. You can define 
trigger parameters when you define trigger, but these values must be constant.

a) the request of trigger parametrization is usually signal of bad using of 
triggers - probably you should to use a function, not trigger

b) there is workaround - but you should not to use it if it is not really 
necessary

There are few implementations of session variables in postgres - you can find 
via google. Then you can set session variable before SQL command, and you can 
read this session variable inside trigger function.

Regards

Pavel


2018-05-15 14:58 GMT+02:00 Adrian Klaver 
mailto:adrian.kla...@aklaver.com>>:
On 05/15/2018 05:28 AM, Łukasz Jarych wrote:
Hi Guys,

I am using postgres 10.3 (or 4?).
IT is possible to set up variable inside function?

Like this?:

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


Best,
Jacek


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: When use triggers?

[Michael Stephenson]

On Wed, May 16, 2018 at 6:36 PM, Adrian Klaver
 wrote:
> On 05/16/2018 03:19 PM, hmidi slim wrote:
>>
>> HI,
>>
>> I'm working on a microservice application and I avoid using triggers
>> because they will not be easy to maintain and need an experimented person in
>> database administration to manage them. So I prefer to manage the work in
>> the application using ORM and javascript.
>> However I want to get some opinions and advices about using triggers: when
>> should I use them? How to manage them when there are some problems?
>
>
> Two benefits triggers and their associated functions offer, with the
> understanding these are general statements:
>
> 1) Are in the database so tasks that always need happen do not need to be
> replicated across the various interfaces that touch the database.
>
> 2) Also since they run on the server the data does not necessarily cross a
> network, so less network bandwidth and better performance.
>
> An example of a use case is table auditing. If you need to track what is
> being INSERTed/UPDATEd/DELETEd in a table stick an audit trigger/function on
> the table and push the information to an audit table.
>
> As to managing, they are like any other code. I keep my schema code in
> scripts under version control and deploy them from there. I use
> Sqitch(http://sqitch.org/) which allows me to deploy and revert changes. I
> use a dev database to test and troubleshoot triggers and functions.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

The only appropriate use for triggers that I've ever found was for
auditing changes to tables.

It can be quite trivial for simple cases: just use an update trigger
and write all of the "deleted" (e.g., before the update)
values/columns that you want to track to your audit table.  Each row
in the audit represents the previous state before changes were made.
It's so trivial that you could write a sql script to generate a
simplest-case audit table and audit trigger for any table where you
don't need any custom behavior in the audit logic

As for management, you could also have a build or deployment task that
updates audit tables and the triggers as columns are added (or perhaps
removed) from their related tables (e.g., semi-automatic management).

To keep them lightweight, my typical audit table is insert-only (other
grants are removed) with a primary key, no foreign keys, and a single
nonunique index on the main table's primary key if the audits related
to a main-table row need to be viewable by an end user or something
similar.

It's kind of interesting that you speak of microservices and triggers.
If you go "all in" with microservices, the typical approach is to use
event sourcing and CQRS and then all of your writes __are__ your
audit.

~Mike Stephenson