Re: When use triggers?

[Berend Tober]

Michael Stephenson wrote:

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. ...

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.


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



A great use case for triggers and stored functions is data validation or cleaning. Sure, this could 
be done in the end-user application ... or, that is, in *all* end-user applications if you don't 
mind duplicating algorithmic implementations and you trust the applications to do it properly and 
the same way every time and non-maliciously.


Triggering a before-insert function allows for a single implementation to be used consistently for 
all inserts.


Also, while appropriately limiting access privileges for end-users, triggers can invoke functions 
that do stuff at a higher privilege level safely, such as, for example, creating data base roles for 
new users.


Here's a complicated but cool example:

First, there is a publicly-exposed but non-data-leaking view that allows unprivileged users to 
initiate inserts for account creation (and by unprivileged I mean really unprivileged, that is, end 
users that don't even have an associated database login role when they do the insert):



CREATE OR REPLACE VIEW public.fairian AS
 SELECT NULL::name AS fairian_name,
NULL::name AS passwd,
NULL::name AS email_address;

GRANT USAGE ON SCHEMA public TO public;
GRANT SELECT, INSERT ON TABLE public.fairian TO public;


A rule redirects inserts on the public dummy view to a protected intermediate view (i.e., a view 
contained in non-publicly-visible schema) on which new and unprivileged users do not have read, 
write, or update privileges, but since relations that are used due to rules get checked against the 
privileges of the rule owner, not the user invoking the rule, this actually works:


REVOKE ALL ON schema protected  FROM public;

CREATE OR REPLACE RULE fairian_iir AS
ON INSERT TO public.fairian DO INSTEAD
INSERT INTO protected.fairian (fairian_name, passwd, email_address)
VALUES (new.fairian_name, new.passwd, new.email_address);


The redirected insert triggers a function that drills the insert down one level further to the 
actual table that is visible only to the data base owner:



CREATE TRIGGER fairian_iit
  INSTEAD OF INSERT
  ON protected.fairian
  FOR EACH ROW
  EXECUTE PROCEDURE protected.fairian_iit();

CREATE OR REPLACE FUNCTION protected.fairian_iit()
  RETURNS trigger AS
$BODY$
BEGIN
-- Note, the password is not actually stored but there is some
-- validation ... look at the private trigger function

INSERT INTO private.fairian (fairian_name, passwd, email_address)
VALUES (new.fairian_name, new.passwd, new.email_address);

  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER;


When the insert is finally pushed all the way down to the actual table in the (non-publicly visible) 
private schema, there's a trigger function which prevents account name collisions by serializing 
inserts, that ensures the credentials meet certain requirements such as containing no white space 
and starting with a character, and (although omitted below) sanitizes against SQL injection:



REVOKE ALL ON schema private  FROM public;

CREATE TRIGGER fairian_bit
  BEFORE INSERT
  ON private.fairian
  FOR EACH ROW
  EXECUTE PROCEDURE private.fairian_bit();


CREATE OR REPLACE FUNCTION private.fairian_bit()
  RETURNS trigger AS
$BODY$
DECLARE
  _fairwinds private.fairwinds;
BEGIN
  -- Serializes account creation
  select * into _fairwinds from private.fairwinds for update;

-- Deny white space characters

if position(' ' in new.fairian_name) > 0 then
raise exception 'Fairian name may not contain spaces.';
end if;
if position(' ' in new.passwd) > 0 then
raise exception 'Password may not contain spaces.';
end if;

Re: When use triggers?

[Ken Tanzer]

On Fri, May 18, 2018 at 12:45 PM, Melvin Davidson 
wrote:

>
>
> > I have used triggers to keep audit-logs of changes to certain columns in
> a table
> Another good use for triggers is to maintain customer balance..EG: An
> INSERT, UPDATE or DELETE involving a customer payment
> (or in the case of banks (deposit or withdrawals) would automatically
> maintain the balance in the customer master record.
>
> Another scenario is if you want to put a (check) constraint on your table
that refers to other rows or tables.  You can't actually do that, and need
to use a trigger instead.

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: When use triggers?

[Melvin Davidson]

On Fri, May 18, 2018 at 9:35 AM, Vick Khera  wrote:

> On Wed, May 16, 2018 at 6: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?
>>
>
> I have used triggers to keep audit-logs of changes to certain columns in a
> table. For example, I want to know when a customer went "overdue" and then
> back to "active". The best place to create that log is in the database
> itself, since that also captures any manually updated rows (ie, those
> actions not initiated by the application code itself).
>
> I have also used triggers to ensure data consistency and enforce state
> diagram transition rules for status columns in a table. These help capture
> logic errors in application code. For example, if your state diagram allows
> A -> B <-> C, then the trigger would disallow a transition from B or C  to
> A, disallow A -> C, but allow C -> B and B -> C and A -> B.
>
> To manage them, we treat them like all DDL changes: everything is done via
> SQL script, and those are tracked using our version control software, go
> through developer testing then staging testing, then finally production.
>

> I have used triggers to keep audit-logs of changes to certain columns in
a table
Another good use for triggers is to maintain customer balance..EG: An
INSERT, UPDATE or DELETE involving a customer payment
(or in the case of banks (deposit or withdrawals) would automatically
maintain the balance in the customer master record.

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

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

[Peter J. Holzer]

On 2018-05-15 15:02:48 +, ChatPristi wrote:
> I have a SELECT command (in partitionned tables) that failed with:
> psql:/tmp/query.txt:1: ERROR:  out of memory
> DETAIL:  Cannot enlarge string buffer containing 1073741818 bytes by 32 more
> bytes.

1073741818 is a bit less than 1GB and 1073741818+32 is a bit more. So
you are obviously hitting a 1GB limit here.

Given that 1GB is the maximum length of a character type value in
PostgreSQL and the error message mentions a "string buffer", I suspect
that your query tries to construct a very long string. Try to rewrite
the query so that it creates several shorter strings instead.

hp




-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Importing data from CSV into a table with array and composite types

[a]

Hi:


I would like to import data from a csv table. But the table that is going to be 
inserted is constructed with arrays and composite types, also with array of 
composite.


I have tried many ways of inserting but fail. Can anyone help? Thank you so 
much.