Rules versus triggers
Could someone give me a brief description of the intended functionally, and how the 2 features work of rules, versus triggers? It appears to me that they are simply 2 different ways to set up triggers, but I am certain that is just because of my lack of knowledge. Thank you. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Rules versus triggers
On 07/03/2020 11:56, stan wrote: Could someone give me a brief description of the intended functionally, and how the 2 features work of rules, versus triggers? It appears to me that they are simply 2 different ways to set up triggers, but I am certain that is just because of my lack of knowledge. The pages https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_rules links to will give you some idea of the differences. Cheers, Steve
Re: Rules versus triggers
Hi Stan Rules actual are able to rewrite the SQL query sent to postgresql. Most everyone suggestion is avoid rules. Triggers are just like every other databases Triggers firing off code for Insert/Update/Delete/Truncate event https://www.postgresql.org/docs/current/sql-createtrigger.html On Sat, Mar 7, 2020 at 6:56 AM stan wrote: > Could someone give me a brief description of the intended functionally, and > how the 2 features work of rules, versus triggers? It appears to me that > they are simply 2 different ways to set up triggers, but I am certain that > is just because of my lack of knowledge. > > Thank you. > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin > > >
Re: geting results of query in plperl
On 3/6/20 6:57 AM, stan wrote:
I have looked at:
https://www.postgresql.org/docs/8.4/plperl-database.html
I am also comfortable querying data from tables in perl. But I do not
quite see how to get the results of a query in plperl. Here is what I
tried, and it is not working:
my $rv2 = spi_exec_query('SELECT current_user');
my $user = $rv2->{rows}[1]->{my_column};
I have used this query in SQL functions, so I know it works. I also ran it
in plsql.
What do I have wrong here?
It starts at zero:
my $user = $rv2->{rows}[0]->{my_column};
Here is some live code:
my ($q, $i, $row, %map);
$q = spi_exec_query('select lower(username) as username, id from
employee');
foreach $i (0.. $q->{processed} - 1)
{
$row = $q->{rows}[$i];
$map{ $row->{username} } = $row->{id};
}
$q = undef;
RAISE ERROR
I used raise(ERROR) in some functions, and it seems to work as expected, in that it aborts the statement/function and displays the message. Went to look at the documentation for this as part of my cleanup to find out this is undocumented. Should I go back and change all these calls to RAISE(EXCEPTION)? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Rules versus triggers
On Sat, Mar 07, 2020 at 09:47:39AM -0500, Justin wrote: > Hi Stan > > Rules actual are able to rewrite the SQL query sent to postgresql. Most > everyone suggestion is avoid rules. > > Triggers are just like every other databases Triggers firing off code for > Insert/Update/Delete/Truncate event > https://www.postgresql.org/docs/current/sql-createtrigger.html > So, the RULE can actually rewrite the query, instead of replacing it, which is what I m doing in the function, correct? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Rules versus triggers
Yes a rule can rewrite query or replace the query Read through Depesz post about rules and the weird side affects that can occurr https://www.depesz.com/2010/06/15/to-rule-or-not-to-rule-that-is-the-question/ On Sat, Mar 7, 2020 at 9:57 AM stan wrote: > On Sat, Mar 07, 2020 at 09:47:39AM -0500, Justin wrote: > > Hi Stan > > > > Rules actual are able to rewrite the SQL query sent to postgresql. Most > > everyone suggestion is avoid rules. > > > > Triggers are just like every other databases Triggers firing off code for > > Insert/Update/Delete/Truncate event > > https://www.postgresql.org/docs/current/sql-createtrigger.html > > > > So, the RULE can actually rewrite the query, instead of replacing it, > which is what I m doing in the function, correct? > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin >
duplicate key value violates unique constraint
Hi all, how to fix a problem, suppose there is a table with id and username if I set the id to bigint so the limit is 9223372036854775807 if I insert for example 3 rows idusername ---- 1 abc 2 def 3 ghi if I delete all rows and insert one another it is like idusername ---- 4 jkl So it doesn't start again from non-available id 1, so what is needed to do to make the new inserts go into non-available id numbers?
Re: duplicate key value violates unique constraint
On 3/7/20 11:29 AM, Ashkar Dev wrote: Hi all, how to fix a problem, suppose there is a table with id and username if I set the id to bigint so the limit is 9223372036854775807 if I insert for example 3 rows id username -- -- 1 abc 2 def 3 ghi if I delete all rows and insert one another it is like id username -- -- 4 jkl So I am assuming id is of type bigserial or something that has a sequence behind it? So it doesn't start again from non-available id 1, so what is needed to do to make the new inserts go into non-available id numbers? If you are sequences then they do not go backwards: https://www.postgresql.org/docs/12/sql-createsequence.html "Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently." If you want that to happen you will have to roll your own implementation. -- Adrian Klaver adrian.kla...@aklaver.com
Re: duplicate key value violates unique constraint
Asking the same question on multiple lists is strongly frowned upon. On 3/7/20 1:29 PM, Ashkar Dev wrote: Hi all, how to fix a problem, suppose there is a table with id and username if I set the id to bigint so the limit is 9223372036854775807 if I insert for example 3 rows id username -- -- 1 abc 2 def 3 ghi if I delete all rows and insert one another it is like id username -- -- 4 jkl So it doesn't start again from non-available id 1, so what is needed to do to make the new inserts go into non-available id numbers? -- Angular momentum makes the world go 'round.
Re: Restrict user to create only one db with a specific name
Thanks David and Paul. Because each user wants the ability to drop and recreate their own databases, I want to impose a restriction on the database names. I’ll just implement what Paul has suggested. Thanks. Tiff On Sat, Mar 7, 2020 at 2:35 AM Paul Förster wrote: > Hi Tiff, > > from what you say, it sounds that each user should have his or her own > database. > > Considering the other answers here already pointing out the difficulties, > why don't you just create a database for each user with the same name as > the username and grant him or her access to it. > > So, basically like this: > > postgres=# create role "userA" login; > CREATE ROLE > postgres=# create database "userA" owner "userA"; > CREATE DATABASE > postgres=# create role "userB" login; > CREATE ROLE > postgres=# create database "userB" owner "userB"; > CREATE DATABASE > > When, say, "userB" goes away, his or her data will go the way all things > do: > > drop database "userB"; > drop role "userB"; > > Or did I misunderstand you? > > Cheers, > Paul > > > > On 07. Mar, 2020, at 00:28, Tiffany Thang > wrote: > > > > Hi, > > Is there a way in PostgreSQL 12 to restrict user to creating a database > with a specific database name? > > > > For example, userA can only create a database with a name called mydb. > Any other names would generate an error. > > > > If that is not possible, will it be possible then to limit userA to > creating only one database? Granting the createdb privilege would allow the > user to create any number of databases which I want to avoid. > > > > Thanks. > > > > Tiff > >
