Re: When use triggers?

[Tim Cross]

hmidi slim  writes:

> 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 think triggers are generally best avoided. They do have a purpose, but
like regular expressions and Lisp style macros, they are abused more
often than used appropriately. When used correctly, they can help to
ensure your code is robust, clear and easy to maintain. 

The big issue with triggers is that they are really a side effect of
some other action. As such, they are obscure, easily missed, difficult
to debug and often frustrating to maintain.

In nearly 30 years of working with different databases, I've rarely
found triggers necessary. As mentioned by others in this thread, they
can be useful when you need low level auditing and like all guidelines,
there are always exceptions, but in general, they should usually be the
last choice, not the first.

Database functions on the other hand are extremely useful and probably
something more developers should take advantage of. There are far too
many applications out there which are doing things within external
application code which could be handled far more efficiently and
consistently as a database function. The challenge is in getting the
right balance.

My rule of thumb is to develop under the assumption that someone else
will have this dumped on them to maintain. I want the code to be as easy
to understand and follow as possible and I want to make it as easy to
make changes and test those changes as possible. Therefore I prefer my
code to consist of simple units of functionality which can be tested in
isolation and have a purpose which can be understood without requiring a
knowledge of hidden actions or unexpected side effects. If a function
cannot be viewed in a single screen, it is probably too big and trying
to do too many different things which should be broken up into smaller
functions. 

regards,

Tim


-- 
Tim Cross

Re: Problem compiling PostgreSQL.

[Paul Linehan]

Hi and thanks for replying,

> I tend to install ICUs versions into their own directories rather
> than /usr/local, and use Debian rather than Fedora, but you
> might try PKG_CONFIG_PATH=/usr/local/lib/pkgconfig


I  have figured it out. It all boils down to
actually taking the time and effort to RTFM...


In the readme.html - there is the following line:

Run the runConfigureICU script for your platform. (See configuration
note below).

So, I ran ./runConfigureICU Linux --prefix=/usr, and **then** ran
make, sudo make install and it's compiled.

Sorry for wasting everybody's time - it's just that, nowadays, I have
a Pavlovian response if I see an executable configure in the source
directory!

I'm going to send them an email (annoyed from Dublin) to say that they
could put an INSTALL file with this in it - although it is really my
fault! :-(



Go raibh maith agat/Merci encore,


Pól...


> Daniel Vérité

Re: Problem compiling PostgreSQL.

[Daniel Verite]

Paul Linehan wrote:

> Now, I've installed the icu libraries using the classic ./configure, make
> and sudo make install.

So it got installed under /usr/local

> collationcmds.c:(.text+0xe36): undefined reference to `uloc_getAvailable_61'
> collationcmds.c:(.text+0xe5b): undefined reference to
> `uloc_toLanguageTag_61'

61 is ICU's major version number.
You have two ICU versions installed, one from Fedora, with
its icu*.pc config files located in usr/lib64/pkgconfig, the other
(presumably v61, as it's the latest) in /usr/local/lib/pkgconfig/
Without specifying which one should be used, it looks like
Postgres get them mixed between compilation and link.

To use self-compiled ICU, I've been having success
configuring postgres with:

PKG_CONFIG_PATH=/path/to/icu/lib/pkgconfig \
./configure --with-icu [other flags]

I tend to install ICUs versions into their own directories rather
than /usr/local, and use Debian rather than Fedora, but you
might try PKG_CONFIG_PATH=/usr/local/lib/pkgconfig


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: Problem compiling PostgreSQL.

[Paul Linehan]

Hi again,


As a followup - I ran this command  (do I need to set LD_LIBRARY_PATH?)

[pol@polhost /]$ sudo find . -name "icu*" | grep -v Downloads |  more


And got:


./usr/local/sbin/icupkg
./usr/local/bin/icuinfo
./usr/local/bin/icu-config
./usr/local/lib/icu
./usr/local/lib/pkgconfig/icu-io.pc
./usr/local/lib/pkgconfig/icu-uc.pc
./usr/local/lib/pkgconfig/icu-i18n.pc
./usr/local/share/icu
./usr/local/share/man/man8/icupkg.8
./usr/local/share/man/man1/icu-config.1
./usr/local/include/unicode/icuplug.h
./usr/local/include/unicode/icudataver.h
./usr/bin/icuinfo
./usr/bin/icu-config-64
./usr/bin/icu-config
./usr/lib64/icu
./usr/lib64/pkgconfig/icu-io.pc
./usr/lib64/pkgconfig/icu-uc.pc
./usr/lib64/pkgconfig/icu-i18n.pc
./usr/lib/jvm/java-10-openjdk-10.0.1.10-3.fc28.x86_64/legal/java.base/icu.md
./usr/share/maven-metadata/icu4j.xml
./usr/share/icu
./usr/share/licenses/icu4j
./usr/share/jython/javalib/icu4j_icu4j.jar
./usr/share/maven-poms/icu4j
./usr/share/maven-poms/icu4j/icu4j.pom
./usr/share/java/icu4j
./usr/share/java/icu4j/icu4j.jar
./usr/share/doc/icu4j
./usr/share/man/man1/icu-config.1.gz
./usr/share/man/man1/icuinfo.1.gz
./usr/include/boost/regex/icu.hpp
./usr/include/unicode/icuplug.h
./usr/include/unicode/icudataver.h
[pol@polhost /]$

It seems that the library was installed, but the PostgreSQL make
command can't "see" the libs?

Re: Problem compiling PostgreSQL.

[Paul Linehan]

>> On which platform and/or distribution are you trying the code
>> compilation?

> sorry about that - it's Linux Fedora 28, 64 bit Intel X86_64 - Samsung
> laptop, 8GB RAM, quad-core.

PostgreSQL 10.4 also.


Pól...

Re: Problem compiling PostgreSQL.

[Paul Linehan]

Hi, and thanks for responding,


>> I'm having problems compiling PostgreSQL.

> On which platform and/or distribution are you trying the code
> compilation?


Ooops!

sorry about that - it's Linux Fedora 28, 64 bit Intel X86_64 - Samsung
laptop, 8GB RAM, quad-core.


Please don't hesitate to ask should you require further information!

Rgs,


Pól...

Re: Problem compiling PostgreSQL.

[Michael Paquier]

On Thu, May 17, 2018 at 08:31:48AM +0100, Paul Linehan wrote:
> I'm having problems compiling PostgreSQL.

On which platform and/or distribution are you trying the code
compilation?
--
Michael


signature.asc
Description: PGP signature

Problem compiling PostgreSQL.

[Paul Linehan]

Hi all,

I'm having problems compiling PostgreSQL.

I can run "make" no problem with this command

./configure --prefix=/home/pol/Downloads/sware/db/pg/inst
--mandir=/home/pol/Downloads/sware/db/pg/inst/man
--docdir=/home/pol/Downloads/sware/db/pg/inst/doc --enable-nls
--with-python --with-openssl --with-systemd --with-uuid=e2fs --with-libxml
--with-libxslt

but not this one

./configure --prefix=/home/pol/Downloads/sware/db/pg/inst
--mandir=/home/pol/Downloads/sware/db/pg/inst/man
--docdir=/home/pol/Downloads/sware/db/pg/inst/doc --enable-nls
--with-python --with-icu --with-openssl --with-systemd --with-uuid=e2fs
--with-libxml --with-libxslt

(for those of you with bad eyesight, the difference is in the extra
"--with-icu" in the second failing command! :-) )


Now, I've installed the icu libraries using the classic ./configure, make
and sudo make install.

The error I'm getting is

make[4]: Leaving directory '/home/pol/Downloads/sware/db/pg/src/src/port'
make -C ../../src/common all
make[4]: Entering directory '/home/pol/Downloads/sware/db/pg/src/src/common'
make -C ../backend submake-errcodes
make[5]: Entering directory
'/home/pol/Downloads/sware/db/pg/src/src/backend'
make[5]: Nothing to be done for 'submake-errcodes'.
make[5]: Leaving directory '/home/pol/Downloads/sware/db/pg/src/src/backend'
make[4]: Leaving directory '/home/pol/Downloads/sware/db/pg/src/src/common'
make[3]: Leaving directory
'/home/pol/Downloads/sware/db/pg/src/src/timezone'
/usr/bin/gcc -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-O2 -L../../src/port -L../../src/common -Wl,--as-needed
-Wl,-rpath,'/home/pol/Downloads/sware/db/pg/inst/lib',--enable-new-dtags
-Wl,-E access/brin/brin.o access/brin/brin_pageops.o

... a shedload more .o files snipped from error and then at the end:

../../src/timezone/pgtz.o ../../src/port/libpgport_srv.a
../../src/common/libpgcommon_srv.a -lpthread -lxslt -lxml2 -lssl -lcrypto
-lrt -lcrypt -ldl -lm -licui18n -licuuc -licudata -lsystemd -o postgres
commands/collationcmds.o: In function `pg_import_system_collations':
collationcmds.c:(.text+0xe16): undefined reference to
`uloc_countAvailable_61'
collationcmds.c:(.text+0xe36): undefined reference to `uloc_getAvailable_61'
collationcmds.c:(.text+0xe5b): undefined reference to
`uloc_toLanguageTag_61'
collationcmds.c:(.text+0xf0c): undefined reference to
`uloc_countAvailable_61'
collationcmds.c:(.text+0xf85): undefined reference to
`uloc_getDisplayName_61'
collationcmds.c:(.text+0x112e): undefined reference to `u_errorName_61'
regex/regcomp.o: In function `pg_wc_isalpha':
regcomp.c:(.text+0x9a1): undefined reference to `u_isalpha_61'  <--- seems
to be an icu issue here?
regex/regcomp.o: In function `pg_wc_isspace':
regcomp.c:(.text+0xa81): undefined reference to `u_isspace_61'


... snip a lot more undefined reference errors with u__61 in them

and then at the end

varlena.c:(.text+0x2245): undefined reference to `ucol_strcoll_61'
varlena.c:(.text+0x2292): undefined reference to `ucol_strcollUTF8_61'
varlena.c:(.text+0x22c3): undefined reference to `u_errorName_61'
collect2: error: ld returned 1 exit status
make[2]: *** [Makefile:61: postgres] Error 1
make[2]: Leaving directory '/home/pol/Downloads/sware/db/pg/src/src/backend'
make[1]: *** [Makefile:38: all-backend-recurse] Error 2
make[1]: Leaving directory '/home/pol/Downloads/sware/db/pg/src/src'
make: *** [GNUmakefile:11: all-src-recurse] Error 2
[pol@polhost src]$


I've compiled PostgreSQL many times and I've always been impressed with the
way it "just works"!

I would be grateful if anybody could show me how I could resolve my issue.

TIA, Pól...

PostgreSQL : encryption with pgcrypto

[ROS Didier]

Hi
   Regarding the encryption of data by pgcrypto, I would like to 
know the recommendations for the management of the key.
   Is it possible to store it off the PostgreSQL server?
   Is there the equivalent of Oracle "wallet" ?

   Thanks in advance

Best Regards
[cid:image002.png@01D14E0E.8515EB90]


Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD
Nanterre Picasso - E2 565D (aile nord-est)
32 Avenue Pablo Picasso
92000 Nanterre
didier@edf.fr







Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à 
l'intention exclusive des destinataires et les informations qui y figurent sont 
strictement confidentielles. Toute utilisation de ce Message non conforme à sa 
destination, toute diffusion ou toute publication totale ou partielle, est 
interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le 
copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si 
vous avez reçu ce Message par erreur, merci de le supprimer de votre système, 
ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support 
que ce soit. Nous vous remercions également d'en avertir immédiatement 
l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie 
électronique arrivent en temps utile, sont sécurisées ou dénuées de toute 
erreur ou virus.


This message and any attachments (the 'Message') are intended solely for the 
addressees. The information contained in this Message is confidential. Any use 
of information contained in this Message not in accord with its purpose, any 
dissemination or disclosure, either whole or partial, is prohibited except 
formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any 
part of it. If you have received this message in error, please delete it and 
all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or 
virus-free.