Re: New website

[Enrico Pirozzi]

Hi Adrian,
I had the same problem too, but only on Chrome

Regards,

Enrico


Il 19/04/2018 00:03, Adrian Klaver ha scritto:
I would contact the Webmaster but Contact goes to a big image of an 
elephant head. That is also where Downloads, Support and Donate lands. 
Might have been a good idea to roll out a demo site for testing first. 
Will reserve judgment on the site design until it is functioning.




--
Enrico Pirozzi
NBS Group S.r.l.
via Val Tiberina 23/A
63074, San Benedetto del Tronto (AP)
Tel. 0735-7626201

Re: pg_upgrade help

[Akshay Ballarpure]

Hi Fabio,
Yes i ran initdb on new database and able to start as below.

[root@ms-esmon root]# su - postgres -c "/usr/bin/postgres -p 50432 -D 
/var/ericsson/esm-data/postgresql-data/ 2>&1 &"
[root@ms-esmon root]# su - postgres -c 
"/opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
/var/ericsson/esm-data/postgresql-data-9.4/ 2>&1 &"
[root@ms-esmon root]# 2018-04-19 08:17:53.553 IST  LOG:  redirecting log 
output to logging collector process
2018-04-19 08:17:53.553 IST  HINT:  Future log output will appear in 
directory "pg_log".

[root@ms-esmon root]#
[root@ms-esmon root]# ps -eaf | grep postgre
sroot  8646  9365  0 Apr18 pts/100:00:00 su - postgres
postgres  8647  8646  0 Apr18 pts/100:00:00 -bash
postgres 28009 1  2 08:17 ?00:00:00 /usr/bin/postgres -p 50432 
-D /var/ericsson/esm-data/postgresql-data/  --8.4
postgres 28010 28009  0 08:17 ?00:00:00 postgres: logger process
postgres 28012 28009  0 08:17 ?00:00:00 postgres: writer process
postgres 28013 28009  0 08:17 ?00:00:00 postgres: wal writer 
process
postgres 28014 28009  0 08:17 ?00:00:00 postgres: autovacuum 
launcher process
postgres 28015 28009  0 08:17 ?00:00:00 postgres: stats collector 
process
postgres 28048 1  0 08:17 ?00:00:00 
/opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
/var/ericsson/esm-data/postgresql-data-9.4/
postgres 28049 28048  0 08:17 ?00:00:00 postgres: logger process
postgres 28051 28048  0 08:17 ?00:00:00 postgres: checkpointer 
process
postgres 28052 28048  0 08:17 ?00:00:00 postgres: writer process
postgres 28053 28048  0 08:17 ?00:00:00 postgres: wal writer 
process
postgres 28054 28048  0 08:17 ?00:00:00 postgres: autovacuum 
launcher process
postgres 28055 28048  0 08:17 ?00:00:00 postgres: stats collector 
process
root 28057  2884  0 08:17 pts/000:00:00 grep --color=auto postgre


Also i am able to start db with the command provided by you and run psql.

/opt/rh/rh-postgresql94/root/usr/bin/pg_ctl  start -o "-p 50432 -c 
listen_addresses='' -c unix_socket_permissions=0700"  -D 
/var/ericsson/esm-data/postgresql-data-9.4/
pg_ctl: another server might be running; trying to start server anyway
server starting
-bash-4.2$ 2018-04-19 08:22:46.527 IST  LOG:  redirecting log output to 
logging collector process
2018-04-19 08:22:46.527 IST  HINT:  Future log output will appear in 
directory "pg_log".

-bash-4.2$ ps -eaf | grep postg
root  8646  9365  0 Apr18 pts/100:00:00 su - postgres
postgres  8647  8646  0 Apr18 pts/100:00:00 -bash
postgres 28174 1  0 08:22 pts/100:00:00 
/opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
/var/ericsson/esm-data/postgresql-data-9.4 -p 50432 -c listen_addresses= 
-c unix_socket_permissions=0700
postgres 28175 28174  0 08:22 ?00:00:00 postgres: logger process
postgres 28177 28174  0 08:22 ?00:00:00 postgres: checkpointer 
process
postgres 28178 28174  0 08:22 ?00:00:00 postgres: writer process
postgres 28179 28174  0 08:22 ?00:00:00 postgres: wal writer 
process
postgres 28180 28174  0 08:22 ?00:00:00 postgres: autovacuum 
launcher process
postgres 28181 28174  0 08:22 ?00:00:00 postgres: stats collector 
process
postgres 28182  8647  0 08:22 pts/100:00:00 ps -eaf
postgres 28183  8647  0 08:22 pts/100:00:00 grep --color=auto postg

-bash-4.2$ psql -p 50432 -h /var/run/postgresql -U rhqadmin -d rhq
psql (8.4.20, server 9.4.9)
WARNING: psql version 8.4, server version 9.4.
 Some psql features might not work.
Type "help" for help.

rhq=>


Still its failing...

-bash-4.2$ ps -efa | grep postgre
root  8646  9365  0 Apr18 pts/100:00:00 su - postgres
postgres  8647  8646  0 Apr18 pts/100:00:00 -bash
postgres 28349  8647  0 08:34 pts/100:00:00 ps -efa
postgres 28350  8647  0 08:34 pts/100:00:00 grep --color=auto postgre

-bash-4.2$ echo $OLDCLUSTER
/usr/bin/postgres
-bash-4.2$ echo $NEWCLUSTER
/opt/rh/rh-postgresql94/

[root@ms-esmon rh-postgresql94]# 
/opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade --old-bindir=/usr/bin 
--new-bindir=/opt/rh/rh-postgresql94/root/usr/bin 
--old-datadir=/var/ericsson/esm-data/postgresql-data 
--new-datadir=/var/ericsson/esm-data/postgresql-data-9.4

Performing Consistency Checks
-
Checking cluster versions   ok

connection to database failed: could not connect to server: No such file 
or directory
Is the server running locally and accepting
connections on Unix domain socket 
"/var/run/postgresql/.s.PGSQL.50432"?


could not connect to old postmaster started with the command:
"/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D 
"/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off -c 
autovacuum_freeze_max_age=20  -c listen_addresses='' -c 
unix_socket_permissions=0700" start
Failure, exiting

With Best Regards
Akshay
Ericsson OSS MON
Tata

obsoleting plpython2u and defaulting plpythonu to plpython3u

[Pavel Raiskup]

Per current plpython docs:

The language named plpythonu implements PL/Python based on the default
Python language variant, which is currently Python 2. (This default is
independent of what any local Python installations might consider to
be their “default”, for example, what /usr/bin/python might be.) The
default will probably be changed to Python 3 in a distant future
release of PostgreSQL, depending on the progress of the migration to
Python 3 in the Python community.

.. the status quo seems to be bit optimistic with the "distant future",
and we should start thinking about dropping plpython2 support, same as
upstream (a bit optimistically too, IMO) does [1].

The docs don't suggest the explicit use of plpython2, but still the docs
are not discouraging from it -- so it is likely some clusters run against
that.

What's the expected future migration path from plpython2 to plpython3 in
such cases?  I'm thinking about rewrite of the docs and creating some
scripting which could simplify the migration steps.  Would such patches be
welcome at this point?

[1] https://pythonclock.org/

Pavel

Re: pg_upgrade help

[Fabio Pardi]

Hi,

while trying to reproduce your problem, i noticed that on my Centos 6 
installations Postgres 8.4 and Postgres 9.6 (I do not have 9.4 readily 
available) store the socket in different places:

Postgres 9.6.6 uses /var/run/postgresql/

Postgres 8.4 uses /tmp/

therefore using default settings, i can connect to 9.6 but not 8.4 without 
specifying where the socket is

Connect to 9.6

12:01 postgres@machine:~# psql
psql (8.4.20, server 9.6.6)
WARNING: psql version 8.4, server version 9.6.
 Some psql features might not work.
Type "help" for help.

-

Connect to 8.4

12:01 postgres@machine:~# psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

12:04 postgres@machine:~# psql -h /tmp
psql (8.4.20)
Type "help" for help.




I think you might be incurring in the same problem.

Can you confirm it?


regards,

fabio pardi 





On 04/19/2018 09:37 AM, Akshay Ballarpure wrote:
> Hi Fabio,
> Yes i ran initdb on new database and able to start as below.
> 
> [root@ms-esmon root]# su - postgres -c "/usr/bin/postgres -p 50432 -D 
> /var/ericsson/esm-data/postgresql-data/ 2>&1 &"
> [root@ms-esmon root]# su - postgres -c 
> "/opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
> /var/ericsson/esm-data/postgresql-data-9.4/ 2>&1 &"
> [root@ms-esmon root]# 2018-04-19 08:17:53.553 IST  LOG:  redirecting log 
> output to logging collector process
> 2018-04-19 08:17:53.553 IST  HINT:  Future log output will appear in 
> directory "pg_log".
> 
> [root@ms-esmon root]#
> [root@ms-esmon root]# ps -eaf | grep postgre
> sroot      8646  9365  0 Apr18 pts/1    00:00:00 su - postgres
> postgres  8647  8646  0 Apr18 pts/1    00:00:00 -bash
> postgres 28009     1  2 08:17 ?        00:00:00 /usr/bin/postgres -p 50432 -D 
> /var/ericsson/esm-data/postgresql-data/  *--8.4*
> postgres 28010 28009  0 08:17 ?        00:00:00 postgres: logger process
> postgres 28012 28009  0 08:17 ?        00:00:00 postgres: writer process
> postgres 28013 28009  0 08:17 ?        00:00:00 postgres: wal writer process
> postgres 28014 28009  0 08:17 ?        00:00:00 postgres: autovacuum launcher 
> process
> postgres 28015 28009  0 08:17 ?        00:00:00 postgres: stats collector 
> process
> postgres 28048     1  0 08:17 ?        00:00:00 
> /opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
> /var/ericsson/esm-data/postgresql-data-9.4/
> postgres 28049 28048  0 08:17 ?        00:00:00 postgres: logger process
> postgres 28051 28048  0 08:17 ?        00:00:00 postgres: checkpointer process
> postgres 28052 28048  0 08:17 ?        00:00:00 postgres: writer process
> postgres 28053 28048  0 08:17 ?        00:00:00 postgres: wal writer process
> postgres 28054 28048  0 08:17 ?        00:00:00 postgres: autovacuum launcher 
> process
> postgres 28055 28048  0 08:17 ?        00:00:00 postgres: stats collector 
> process
> root     28057  2884  0 08:17 pts/0    00:00:00 grep --color=auto postgre
> 
> 
> Also i am able to start db with the command provided by you and run psql.
> 
> /opt/rh/rh-postgresql94/root/usr/bin/pg_ctl  start -o "-p 50432 -c 
> listen_addresses='' -c unix_socket_permissions=0700"  -D 
> /var/ericsson/esm-data/postgresql-data-9.4/
> pg_ctl: another server might be running; trying to start server anyway
> server starting
> -bash-4.2$ 2018-04-19 08:22:46.527 IST  LOG:  redirecting log output to 
> logging collector process
> 2018-04-19 08:22:46.527 IST  HINT:  Future log output will appear in 
> directory "pg_log".
> 
> -bash-4.2$ ps -eaf | grep postg
> root      8646  9365  0 Apr18 pts/1    00:00:00 su - postgres
> postgres  8647  8646  0 Apr18 pts/1    00:00:00 -bash
> postgres 28174     1  0 08:22 pts/1    00:00:00 
> /opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
> /var/ericsson/esm-data/postgresql-data-9.4 -p 50432 -c listen_addresses= -c 
> unix_socket_permissions=0700
> postgres 28175 28174  0 08:22 ?        00:00:00 postgres: logger process
> postgres 28177 28174  0 08:22 ?        00:00:00 postgres: checkpointer process
> postgres 28178 28174  0 08:22 ?        00:00:00 postgres: writer process
> postgres 28179 28174  0 08:22 ?        00:00:00 postgres: wal writer process
> postgres 28180 28174  0 08:22 ?        00:00:00 postgres: autovacuum launcher 
> process
> postgres 28181 28174  0 08:22 ?        00:00:00 postgres: stats collector 
> process
> postgres 28182  8647  0 08:22 pts/1    00:00:00 ps -eaf
> postgres 28183  8647  0 08:22 pts/1    00:00:00 grep --color=auto postg
> 
> -bash-4.2$ psql -p 50432 -h /var/run/postgresql -U rhqadmin -d rhq
> psql (8.4.20, server 9.4.9)
> WARNING: psql version 8.4, server version 9.4.
>          Some psql features might not work.
> Type "help" for help.
> 
> rhq=>
> 
> 
> Still its failing...
> 
> -bash-4.2$ ps -efa | grep postgre
> root      8646  9365  0 Apr18 pts/1    00:00:00 su - postgres
> postgres  8647  8646  0 Apr18 pts/1    00:00:

Re: Inconsistent compilation error

[David G. Johnston]

On Wednesday, April 18, 2018, Adrian Klaver 
wrote:
>
> Hmm, wonder if there is an oops in the below:
>
> http://www.pygresql.org/contents/changelog.html
>
> Version 5.0 (2016-03-20)
> Changes in the DB-API 2 module (pgdb):
> "SQL commands are always handled as if they include parameters, i.e.
> literal percent signs must always be doubled. This consistent behavior is
> necessary for using pgdb with wrappers like SQLAlchemy."


I'd hope not, as far as the driver is concerned the percent signs are text
content.  It's plpgsql that is interpreting them directly in the server.

David J.

Re: pg_upgrade help

[Akshay Ballarpure]

Hi Fabio,
I think you have found the problem. Please find o/p below.


-bash-4.2$ ps -aef | grep postgres
postgres   478 1  0 13:40 ?00:00:00 /usr/bin/postgres -p 50432 
-D /var/ericsson/esm-data/postgresql-data/
postgres   490   478  0 13:40 ?00:00:00 postgres: logger process
postgres   492   478  0 13:40 ?00:00:00 postgres: writer process
postgres   493   478  0 13:40 ?00:00:00 postgres: wal writer 
process
postgres   494   478  0 13:40 ?00:00:00 postgres: autovacuum 
launcher process
postgres   495   478  0 13:40 ?00:00:00 postgres: stats collector 
process
postgres   528 1  0 13:40 ?00:00:00 
/opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
/var/ericsson/esm-data/postgresql-data-9.4/
postgres   529   528  0 13:40 ?00:00:00 postgres: logger process
postgres   531   528  0 13:40 ?00:00:00 postgres: checkpointer 
process
postgres   532   528  0 13:40 ?00:00:00 postgres: writer process
postgres   533   528  0 13:40 ?00:00:00 postgres: wal writer 
process
postgres   534   528  0 13:40 ?00:00:00 postgres: autovacuum 
launcher process
postgres   535   528  0 13:40 ?00:00:00 postgres: stats collector 
process
postgres   734  8647  0 13:50 pts/100:00:00 ps -aef
postgres   735  8647  0 13:50 pts/100:00:00 grep --color=auto postgres
root  8646  9365  0 Apr18 pts/100:00:00 su - postgres
postgres  8647  8646  0 Apr18 pts/100:00:00 -bash

9.4
===

-bash-4.2$ psql
psql (8.4.20, server 9.4.9)
WARNING: psql version 8.4, server version 9.4.
 Some psql features might not work.
Type "help" for help.

postgres=#

-bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/psql
psql (9.4.9)
Type "help" for help.

postgres=#

8.4


-bash-4.2$  psql -p 50432
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.50432"?



==

After setting PGHOST, i can connect to PSQL
 
-bash-4.2$ echo $PGHOST
/var/run/postgresql
-bash-4.2$ psql -p 50432
psql (8.4.20)
Type "help" for help.

postgres=#

 
 


 




With Best Regards
Akshay
Ericsson OSS MON
Tata Consultancy Services
Mailto: akshay.ballarp...@tcs.com
Website: http://www.tcs.com

Experience certainty.   IT Services
Business Solutions
Consulting





From:   Fabio Pardi 
To: Akshay Ballarpure , 
pgsql-general@lists.postgresql.org
Date:   04/19/2018 03:45 PM
Subject:Re: pg_upgrade help



Hi,

while trying to reproduce your problem, i noticed that on my Centos 6 
installations Postgres 8.4 and Postgres 9.6 (I do not have 9.4 readily 
available) store the socket in different places:

Postgres 9.6.6 uses /var/run/postgresql/

Postgres 8.4 uses /tmp/

therefore using default settings, i can connect to 9.6 but not 8.4 without 
specifying where the socket is

Connect to 9.6

12:01 postgres@machine:~# psql
psql (8.4.20, server 9.6.6)
WARNING: psql version 8.4, server version 9.6.
 Some psql features might not work.
Type "help" for help.

-

Connect to 8.4

12:01 postgres@machine:~# psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket 
"/var/run/postgresql/.s.PGSQL.5432"?

12:04 postgres@machine:~# psql -h /tmp
psql (8.4.20)
Type "help" for help.




I think you might be incurring in the same problem.

Can you confirm it?


regards,

fabio pardi 





On 04/19/2018 09:37 AM, Akshay Ballarpure wrote:
> Hi Fabio,
> Yes i ran initdb on new database and able to start as below.
> 
> [root@ms-esmon root]# su - postgres -c "/usr/bin/postgres -p 50432 -D 
/var/ericsson/esm-data/postgresql-data/ 2>&1 &"
> [root@ms-esmon root]# su - postgres -c 
"/opt/rh/rh-postgresql94/root/usr/bin/postgres -D 
/var/ericsson/esm-data/postgresql-data-9.4/ 2>&1 &"
> [root@ms-esmon root]# 2018-04-19 08:17:53.553 IST  LOG:  redirecting log 
output to logging collector process
> 2018-04-19 08:17:53.553 IST  HINT:  Future log output will appear in 
directory "pg_log".
> 
> [root@ms-esmon root]#
> [root@ms-esmon root]# ps -eaf | grep postgre
> sroot  8646  9365  0 Apr18 pts/100:00:00 su - postgres
> postgres  8647  8646  0 Apr18 pts/100:00:00 -bash
> postgres 28009 1  2 08:17 ?00:00:00 /usr/bin/postgres -p 
50432 -D /var/ericsson/esm-data/postgresql-data/  *--8.4*
> postgres 28010 28009  0 08:17 ?00:00:00 postgres: logger process
> postgres 28012 28009  0 08:17 ?00:00:00 postgres: writer process
> postgres 28013 28009  0 08:17 ?00:00:00 postgres: wal writer 
process
> postgres 28014 28009  0 08:17 ?00:00:00 postgres: autovacuum 
launcher process
> postg

Re: pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

[Jerry Sievers]

Adrian Klaver  writes:

> On 04/18/2018 07:22 AM, Tom Lane wrote:
>
>> Pavel Raiskup  writes:
>>> . and it seems like the hstore.so was somewhat intimately integrated into
>>> OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
>>> --format=custom' called through 'pg_upgrade' failed with:
>>>pg_dump: [archiver (db)] query failed: ERROR:  could not access file
>>>"$libdir/hstore": No such file or directory
>>> Which means that the dump from old datadir, with old server (without
>>> hstore.so packaged) failed.  But playing with hstore.so a bit, the upgrade
>>> always worked smoothly for me even without the "old" hstore.so
>>
>> Hi Pavel,
>>
>> There are certainly plenty of reasons why extension .so's might be needed
>> during pg_dump, even in a binary-upgrade situation.  The first example
>> that comes to mind is that an hstore-type constant appearing in a view
>> definition would require hstore_out() to be invoked while dumping the view
>> definition.
>
> I am obviously missing something. If the old server was using hstore
> in a database how could hstore.so could be accessible to it but not
> pg_dump?

I presume because something stole the depended upon libs but went
unnoticed due to the referring objs being generally unused.

Along comes pg_upgrade and the requisite dump... BOOM!



>
>>
>> I don't remember anymore whether I'd set up the postgresql-update package
>> to include the contrib modules for the old server version.  If I didn't,
>> it was an oversight :-(.
>>
>>  regards, tom lane
>>
>>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800

postgres with graph model

[Philipp Kraus]

Hello,

I’m using in a project Postgresql and PostGIS for a geospatial data model, but 
now I need also a graph in this structure, so my question is, is there any 
existing
extension for Postgres to build a graph. I found ltree but this is for tree 
structures only, not for graphs. In general I have different undirected 
weighted graphs. So
I need some routing algorithms based on the graph weights, distance calculation 
between nodes. My first idea was to use a graph database e.g neo4j, but I have
got a limitation, that the whole system should be designed in Postgres.
Did you can give some ideas to build a graph within Postgres?

Thanks

Phil

Re: postgres with graph model

[Fabrízio de Royes Mello]

Em qui, 19 de abr de 2018 às 11:54, Philipp Kraus <
philipp.kr...@tu-clausthal.de> escreveu:

> Hello,
>
> I’m using in a project Postgresql and PostGIS for a geospatial data model,
> but now I need also a graph in this structure, so my question is, is there
> any existing
> extension for Postgres to build a graph. I found ltree but this is for
> tree structures only, not for graphs. In general I have different
> undirected weighted graphs. So
> I need some routing algorithms based on the graph weights, distance
> calculation between nodes. My first idea was to use a graph database e.g
> neo4j, but I have
> got a limitation, that the whole system should be designed in Postgres.
> Did you can give some ideas to build a graph within Postgres


Do you already check the pgrouting [1] project?

Regards,

[1] http://pgrouting.org/

> --
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: postgres with graph model

[Philipp Kraus]

Thanks,

seems to be a good idea, but I need some routing on geo position data, but also 
some graph algorithm depends on machine learning structures,
if I can use the pgRouting project to deal with a „feature graph“ and 
geo-partial data this will be great, I will test it

Thanks for this hint

Am 19.04.2018 um 18:42 schrieb Fabrízio de Royes Mello 
mailto:fabri...@timbira.com.br>>:


Em qui, 19 de abr de 2018 às 11:54, Philipp Kraus 
mailto:philipp.kr...@tu-clausthal.de>> escreveu:
Hello,

I’m using in a project Postgresql and PostGIS for a geospatial data model, but 
now I need also a graph in this structure, so my question is, is there any 
existing
extension for Postgres to build a graph. I found ltree but this is for tree 
structures only, not for graphs. In general I have different undirected 
weighted graphs. So
I need some routing algorithms based on the graph weights, distance calculation 
between nodes. My first idea was to use a graph database e.g neo4j, but I have
got a limitation, that the whole system should be designed in Postgres.
Did you can give some ideas to build a graph within Postgres

Do you already check the pgrouting [1] project?

Regards,

[1] http://pgrouting.org/
--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: postgres with graph model

[Thomas Kellerer]

Philipp Kraus schrieb am 19.04.2018 um 16:53:

I’m using in a project Postgresql and PostGIS for a geospatial data
model, but now I need also a graph in this structure, so my question
is, is there any existing extension for Postgres to build a graph. I
found ltree but this is for tree structures only, not for graphs. In
general I have different undirected weighted graphs. So I need some
routing algorithms based on the graph weights, distance calculation
between nodes. My first idea was to use a graph database e.g neo4j,
but I have got a limitation, that the whole system should be designed
in Postgres. 
Did you can give some ideas to build a graph within Postgres?


There is a Postgres fork that claims to combine the graph and relational world

http://bitnine.net/agensgraph

Problem with trigger makes Detail record be invalid

[PegoraroF10]

I´m using Postgres 10 on ubuntu.

suppose a simple Master/Detail structure like this:

create table Master(ID integer primary key, name text);
create table Detail(ID integer primary key, Master_ID Integer, OtherInfo
text);
alter table Detail add constraint FKMasterDetail foreign key (Master_ID)
references Master(ID) on update cascade on delete cascade;

Then insert some records on it:
insert into Master(ID, Name) values(1,'First'), values(2,'Second');
insert into Detail(ID, Master_ID, OtherInfo) values(1,1,'Detail
Information'), (2,2,'Detail Information2');

Then, if I delete on Master will delete on detail too. Fine.
delete from Master where ID=1;

But now, suppose I have a complex trigger before update or delete that runs
on Detail table.
create function DoAComplexJobOnDetail() returns trigger as $$
begin
  -- Do lots of things then
  Return new; --This is the error, because I´m returning new even for
delete;
end;$$ language plpgsql;
create trigger DetailDoAComplexJob before update or delete on Detail for
each row execute procedure DoAComplexJobOnDetail();

Then try to delete the other Master record. It will be deleted on Master but
Detail record doesn´t and will obviously become invalid because the foreign
key.
delete from Master where ID=2;
select * from Master; --will show no records.
select * from Detail; --will show one record pointing to Master_ID=2, that
doesn´t exist anymore.

Is this a bug or it´s mine responsability to check that trigger result ?
If that trigger responds incorrectly I think that no information could be
executed.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Doubts about replication..

[Edmundo Robles]

I have several versions of postgres 9.4.5, 9.4.4, 9.4.15 (3), 9.5.3
in different versions of Debian 7.6, 7.8, 7.11, 8.5 and 8.6.

I need to replicate the databases and I have clear that I must update all
to one version.
My main question is, Do you  recommended me update to 9.6 or better update
to 10?.

Actually, is not the goal have high availability . I will use replication
as simple backup.
For reasons of $$$ I can only have 1 server in which I will replicate the 6
databases.

Do you recommend using a postgres service for the 6 databases?, or better,
I think,   I must run  a postgres service in different ports, for each
database?.

thanks in advance.
regards!
--

Re: Problem with trigger makes Detail record be invalid

[Adrian Klaver]

On 04/19/2018 10:55 AM, PegoraroF10 wrote:

I´m using Postgres 10 on ubuntu.

suppose a simple Master/Detail structure like this:

create table Master(ID integer primary key, name text);
create table Detail(ID integer primary key, Master_ID Integer, OtherInfo
text);
alter table Detail add constraint FKMasterDetail foreign key (Master_ID)
references Master(ID) on update cascade on delete cascade;

Then insert some records on it:
insert into Master(ID, Name) values(1,'First'), values(2,'Second');
insert into Detail(ID, Master_ID, OtherInfo) values(1,1,'Detail
Information'), (2,2,'Detail Information2');

Then, if I delete on Master will delete on detail too. Fine.
delete from Master where ID=1;

But now, suppose I have a complex trigger before update or delete that runs
on Detail table.
create function DoAComplexJobOnDetail() returns trigger as $$
begin
   -- Do lots of things then
   Return new; --This is the error, because I´m returning new even for
delete;


That can be dealt with using TG_OP value to conditionally change what is 
RETURNed:


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

"TG_OP

Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE 
telling for which operation the trigger was fired.

"

See Example 42.4. A PL/pgSQL Trigger Procedure For Auditing at bottom of 
page.



end;$$ language plpgsql;
create trigger DetailDoAComplexJob before update or delete on Detail for
each row execute procedure DoAComplexJobOnDetail();

Then try to delete the other Master record. It will be deleted on Master but
Detail record doesn´t and will obviously become invalid because the foreign
key.
delete from Master where ID=2;
select * from Master; --will show no records.
select * from Detail; --will show one record pointing to Master_ID=2, that
doesn´t exist anymore.

Is this a bug or it´s mine responsability to check that trigger result ?


Without seeing exactly what the trigger function on Detail is doing that 
is not answerable.



If that trigger responds incorrectly I think that no information could be
executed.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html




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

Re: Doubts about replication..

[Vick Khera]

You want the replication for backup purposes. What is the use case for your
backups: Is it disaster recovery? Is it archiving historical data? Is it
failover? Is it off-site backup?

If you outline your needs then the proper solution can be offered. There
are other methods than just using the built-in binary file replication.

Based on your use of the words "as simple backup" it seems to me you would
be better off just using pg_dump periodically to copy the database to the
backup system. I would use the same version of pg_dump as the database for
maximal compatibility on restoring to that version.

As for your versions, all of the 9.4.x should be upgraded to the latest
9.4.x release there is. This is a simple upgrade and restart, and very safe
to do. You will get many fixed bugs, some of which could cause data loss.
Similarly for the 9.5.x release.

On Thu, Apr 19, 2018 at 1:57 PM, Edmundo Robles 
wrote:

>
>
> I have several versions of postgres 9.4.5, 9.4.4, 9.4.15 (3), 9.5.3
> in different versions of Debian 7.6, 7.8, 7.11, 8.5 and 8.6.
>
> I need to replicate the databases and I have clear that I must update all
> to one version.
> My main question is, Do you  recommended me update to 9.6 or better update
> to 10?.
>
> Actually, is not the goal have high availability . I will use replication
> as simple backup.
> For reasons of $$$ I can only have 1 server in which I will replicate the
> 6 databases.
>
> Do you recommend using a postgres service for the 6 databases?, or better,
> I think,   I must run  a postgres service in different ports, for each
> database?.
>
> thanks in advance.
> regards!
> --
>
>

Re: Problem with trigger makes Detail record be invalid

[PegoraroF10]

I know my trigger is incorrect. I know that I can use TG_OP to know what
operation is being done. 
My question is ...
> Is this a bug or it´s mine responsability to check that trigger result ?

I think it´s a bug because if something got wrong on detail deletion and it
was rolled back, how could be a parent record be deleted ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Doubts about replication..

[Adrian Klaver]

On 04/19/2018 10:57 AM, Edmundo Robles wrote:



I have several versions of postgres 9.4.5, 9.4.4, 9.4.15 (3), 9.5.3
in different versions of Debian 7.6, 7.8, 7.11, 8.5 and 8.6.

I need to replicate the databases and I have clear that I must update 
all to one version.
My main question is, Do you  recommended me update to 9.6 or better 
update to 10?.


If you have the choice go with 10 as you get an additional year of 
community support.




Actually, is not the goal have high availability . I will use 
replication as simple backup.
For reasons of $$$ I can only have 1 server in which I will replicate 
the 6 databases.


Do you recommend using a postgres service for the 6 databases?, or 
better, I think,   I must run  a postgres service in different ports, 
for each  database?.


I am assuming you are going to be using some form of logical replication 
as binary replication will not work between Postgres major versions.


One cluster would be simpler to manage. The down side is that there are 
cluster(global) data e.g. roles that would be shared by all the 
databases. There is also the fact that everything is in one cluster and 
should it fail all six databases will be down. Running as separate 
clusters would give you some redundancy.


thanks in advance.
regards!
--




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

Re: Doubts about replication..

[Andreas Kretschmer]

Am 19.04.2018 um 19:57 schrieb Edmundo Robles:

I will use replication as simple backup.


please keep in mind, replication is not a backup. All logical errors on 
the master (delete from table and forgot the where-condition) will 
replicated to the standby.



Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Problem with trigger makes Detail record be invalid

[Adrian Klaver]

On 04/19/2018 11:30 AM, PegoraroF10 wrote:

I know my trigger is incorrect. I know that I can use TG_OP to know what
operation is being done.
My question is ...

Is this a bug or it´s mine responsability to check that trigger result ?


I think it´s a bug because if something got wrong on detail deletion and it
was rolled back, how could be a parent record be deleted ?


In your example I saw no rollback or error message:

"delete from Master where ID=2;
select * from Master; --will show no records.
select * from Detail; --will show one record pointing to Master_ID=2, that
doesn´t exist anymore."

Was there an error message?

Then there is the fact that your trigger is doing something to the row 
BEFORE the delete or update and presumably modifying it. Without knowing 
what the function is doing or what it is actually returning then we are 
in full on guessing mode.






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html




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

Re: Problem with trigger makes Detail record be invalid

[Adrian Klaver]

On 04/19/2018 11:30 AM, PegoraroF10 wrote:

I know my trigger is incorrect. I know that I can use TG_OP to know what
operation is being done.
My question is ...

Is this a bug or it´s mine responsability to check that trigger result ?


I think it´s a bug because if something got wrong on detail deletion and it
was rolled back, how could be a parent record be deleted ?


Another thought, are there are any other triggers on the Master and/or 
Detail tables?






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html




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

Re: Doubts about replication..

[Edmundo Robles]

Yes, you are right, the replication is not  a backup ;)  , actually   i
backup  database  daily at  3:00 am but if database crash,  the amount  of
data is  big!  that is the reason i want to  replicate to reduce  the data
loss. By the way  a few days ago a job partner did a delete with no where.

On Thu, Apr 19, 2018 at 1:33 PM, Andreas Kretschmer  wrote:

>
>
> Am 19.04.2018 um 19:57 schrieb Edmundo Robles:
>
>> I will use replication as simple backup.
>>
>
> please keep in mind, replication is not a backup. All logical errors on
> the master (delete from table and forgot the where-condition) will
> replicated to the standby.
>
>
> Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>


--

Re: Problem with trigger makes Detail record be invalid

[PegoraroF10]

My point of view that there was a partial rollback, just on detail table. If
I´ve done a delete from Master and I have a foreign key to it with cascade
option, or all records should be deleted or no one should, this is my point.

Did you see that Master table has no records and Detail table has one record
?
I think you agree with me that we have a a detail record with no master, so
it´s unusable, right ?

I´m not able to do a backup/restore of this database because that record
doesn´t match. 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Problem with trigger makes Detail record be invalid

[Tom Lane]

Adrian Klaver  writes:
> On 04/19/2018 10:55 AM, PegoraroF10 wrote:
>> Is this a bug or it´s mine responsability to check that trigger result ?

> Without seeing exactly what the trigger function on Detail is doing that 
> is not answerable.

I think the OP is complaining because his misimplemented trigger can break
the consistency of the foreign key constraint.  That is not a bug, it's
an intentional design decision: triggers are lower-level than foreign key
enforcement queries, and fire during such queries.  It's easy to construct
examples where people would be very unhappy if this were not so, because
then FK-driven updates would not be seen by the table's triggers.  It does
mean that you have to be careful when writing a trigger.

(I'm not sure that this issue is adequately documented, though.
I'd have expected to find something about it in triggers.sgml and/or
create_trigger.sgml, but in a quick look neither of them mentions foreign
keys.)

regards, tom lane

Re: Problem with trigger makes Detail record be invalid

[Adrian Klaver]

On 04/19/2018 11:52 AM, PegoraroF10 wrote:

My point of view that there was a partial rollback, just on detail table. If
I´ve done a delete from Master and I have a foreign key to it with cascade
option, or all records should be deleted or no one should, this is my point.


Except you now have a trigger or possibly triggers that are altering the 
delete process and possibly counteracting the system trigger that is a 
FK. There have been enough instances of this show up on this list for me 
to know this is a distinct possibility.




Did you see that Master table has no records and Detail table has one record
?
I think you agree with me that we have a a detail record with no master, so
it´s unusable, right ?


We have not seen the actual records, so I cannot say or agree/disagree.



I´m not able to do a backup/restore of this database because that record
doesn´t match.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html




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

Re: Problem with trigger makes Detail record be invalid

[Fabrízio de Royes Mello]

2018-04-19 15:57 GMT-03:00 Tom Lane :
>
> Adrian Klaver  writes:
> > On 04/19/2018 10:55 AM, PegoraroF10 wrote:
> >> Is this a bug or it´s mine responsability to check that trigger result
?
>
> > Without seeing exactly what the trigger function on Detail is doing that
> > is not answerable.
>
> I think the OP is complaining because his misimplemented trigger can break
> the consistency of the foreign key constraint.  That is not a bug, it's
> an intentional design decision: triggers are lower-level than foreign key
> enforcement queries, and fire during such queries.  It's easy to construct
> examples where people would be very unhappy if this were not so, because
> then FK-driven updates would not be seen by the table's triggers.  It does
> mean that you have to be careful when writing a trigger.
>

Yeap... it's already mentioned in stackoverflow in ptbr sometime ago [1]
with a reproducible test case.

> (I'm not sure that this issue is adequately documented, though.
> I'd have expected to find something about it in triggers.sgml and/or
> create_trigger.sgml, but in a quick look neither of them mentions foreign
> keys.)
>

We don't have it properly documented... at the time I answered this
question on pt-br stackoverflow I noticed the lack o documentation and
unfortunately I completely forgot to propose a small patch for it.

Regards,


[1]
https://pt.stackoverflow.com/questions/256115/postgresql-foreign-keys-falhando/256398#256398

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Problem with trigger makes Detail record be invalid

[David G. Johnston]

On Thu, Apr 19, 2018 at 11:57 AM, Tom Lane  wrote:

> (I'm not sure that this issue is adequately documented, though.
> I'd have expected to find something about it in triggers.sgml and/or
> create_trigger.sgml, but in a quick look neither of them mentions foreign
> keys.)
>

​I'm leading toward inadequate myself...though create_trigger.sgml does
have:

"""
​SQL specifies that BEFORE DELETE triggers on cascaded deletes fire after
the cascaded DELETE completes. The PostgreSQL behavior is for BEFORE DELETE
to always fire before the delete action, even a cascading one. This is
considered more consistent. There is also nonstandard behavior if BEFORE
triggers modify rows or prevent updates during an update that is caused by
a referential action. This can lead to constraint violations or stored data
that does not honor the referential constraint.
"""

And triggers.sgml has:

"""
A row-level BEFORE trigger that does not intend to cause [a delete] must be
careful to return as its result the same row that was passed in (that is,
the NEW row for INSERT and UPDATE triggers, the OLD row for DELETE
triggers).
"""

There is a lot of surrounding text to sift through though - and the former
is a "compatibility" comment.  Warning blurb after the triggers.sgml quoted
section about preventing the action from occurring potentially breaking FK
constraints would be a reasonable response to this report.

I'd rather have a developer spend time coding up having an FK constraint
define an AFTER STATEMENT trigger using a transition table and ensure that
all FK constraints remain enforced for all changed records.  Correctly or
incorrectly written triggers do not have any liberty to violate FK
constraints and the fact that they can is reasonably considered by the user
base to be a bug.

David J.

Re: Problem with trigger makes Detail record be invalid

[Tom Lane]

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?=  writes:
> 2018-04-19 15:57 GMT-03:00 Tom Lane :
>> (I'm not sure that this issue is adequately documented, though.
>> I'd have expected to find something about it in triggers.sgml and/or
>> create_trigger.sgml, but in a quick look neither of them mentions foreign
>> keys.)

> We don't have it properly documented... at the time I answered this
> question on pt-br stackoverflow I noticed the lack o documentation and
> unfortunately I completely forgot to propose a small patch for it.

It strikes me that there are basically two things a trigger could do to
break FK consistency:

1. Turn an FK-commanded update into a no-op by returning NULL.

2. Change the content of the FK-related columns during an FK-commanded
update.

Both of these apply only to BEFORE ROW triggers, of course.

It might not be unreasonable or unduly expensive to throw an error for
case #1.  I don't think I want to get into the expense of checking
for case #2, but covering case #1 would be enough to catch all of the
reports of this type of problem that I can remember.

IIRC, you can also break FK consistency with poorly-thought-out rules,
but given that rules are close-to-deprecated, I'm not very concerned
about sanding down rough edges in that case.

(But if you feel like writing a documentation patch, please do, because
we'd not be likely to back-patch a behavioral change like this even
if we get around to making it.)

regards, tom lane

Re: Inconsistent compilation error

[Melvin Davidson]

On Thu, Apr 19, 2018 at 8:13 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wednesday, April 18, 2018, Adrian Klaver 
> wrote:
>>
>> Hmm, wonder if there is an oops in the below:
>>
>> http://www.pygresql.org/contents/changelog.html
>>
>> Version 5.0 (2016-03-20)
>> Changes in the DB-API 2 module (pgdb):
>> "SQL commands are always handled as if they include parameters, i.e.
>> literal percent signs must always be doubled. This consistent behavior is
>> necessary for using pgdb with wrappers like SQLAlchemy."
>
>
> I'd hope not, as far as the driver is concerned the percent signs are text
> content.  It's plpgsql that is interpreting them directly in the server.
>
> David J.
>


*Hmmm, looking at the code you provided us, it is obviously not what is
actually in production. So if you copy the actual function from the system
that works *


*and use that to frop and rebuild the function on the system that fails,
does that resolve the problem? I suspect there is an error in the failing
system that you have overlooked.*

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

A couple of pg_dump questions

[Ron]

$ pg_dump --host=farawaysrvr -Fc $REMOTEDB > /local/disk/backups/$REMOTEDB.dump

Is the data compressed on the remote server (thus minimizing traffic on the 
wire), or locally?  (I'd test this myself, but the company has really strict 
firewall rules in place.)


$ pg_dump --host=farawaysrvr -Fc -j4 $REMOTEDB > 
/local/disk/backups/$REMOTEDB.dump


Will parallel backups work if pg_dump is v9.6 and the remote system is v8.4?

Thanks

--
Angular momentum makes the world go 'round.

Re: A couple of pg_dump questions

[David G. Johnston]

On Thu, Apr 19, 2018 at 3:39 PM, Ron  wrote:

>
> $ pg_dump --host=farawaysrvr -Fc $REMOTEDB > /local/disk/backups/$REMOTEDB.
> dump
>
> Is the data compressed on the remote server (thus minimizing traffic on
> the wire), or locally?  (I'd test this myself, but the company has really
> strict firewall rules in place.)
>

The ​pg_dump client doesn't get any special treatment in terms of the
amount of data being sent over the wire from the server.  It issues "COPY
TO STDOUT" commands on the server and captures the results and, while
packaging them up, performs any requested compression.​  To what extent the
server compresses all its sent content I am unsure though it does some.
​

>
> $ pg_dump --host=farawaysrvr -Fc -j4 $REMOTEDB >
> /local/disk/backups/$REMOTEDB.dump
>
> Will parallel backups work if pg_dump is v9.6 and the remote system is
> v8.4?
> ​
>

​The last paragraph of the 9.6 pg_dump --jobs documentation covers pre-9.2
server dumps.

David J.
​

Re: Problem with trigger makes Detail record be invalid

[Ken Tanzer]

On Thu, Apr 19, 2018 at 12:21 PM, Tom Lane  wrote:

> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?=  writes:
> > 2018-04-19 15:57 GMT-03:00 Tom Lane :
> >> (I'm not sure that this issue is adequately documented, though.
> >> I'd have expected to find something about it in triggers.sgml and/or
> >> create_trigger.sgml, but in a quick look neither of them mentions
> foreign
> >> keys.)
>
> > We don't have it properly documented... at the time I answered this
> > question on pt-br stackoverflow I noticed the lack o documentation and
> > unfortunately I completely forgot to propose a small patch for it.
>
> It strikes me that there are basically two things a trigger could do to
> break FK consistency:
>
> 1. Turn an FK-commanded update into a no-op by returning NULL.
>
> 2. Change the content of the FK-related columns during an FK-commanded
> update.
>
> Both of these apply only to BEFORE ROW triggers, of course.
>
> It might not be unreasonable or unduly expensive to throw an error for
> case #1.  I don't think I want to get into the expense of checking
> for case #2, but covering case #1 would be enough to catch all of the
> reports of this type of problem that I can remember.
>
> IIRC, you can also break FK consistency with poorly-thought-out rules,
> but given that rules are close-to-deprecated, I'm not very concerned
> about sanding down rough edges in that case.
>
> (But if you feel like writing a documentation patch, please do, because
> we'd not be likely to back-patch a behavioral change like this even
> if we get around to making it.)
>
> regards, tom lane
>
>
I'm gonna chime in here from a simple user perspective.  I'm kinda shocked
reading this thread that any of this is possible.  I had always understood
and relied on foreign keys being a _guarantee_ of referential integrity.
I'd personally be in favor of at least an option to disallow this, even
with a performance cost.  Maybe you could even call it "Strict Mode." ;)

But regardless, I think some better documentation is in order, and not just
in the triggers section.  I'd suggest this be prominently mentioned as a
big asterisk in any places that talk about constratints.  This page seems
like an obvious candidate:
https://www.postgresql.org/docs/9.5/static/ddl-constraints.html), as it has
nothing qualifying lots of statements such as "If a user attempts to store
data in a column that would violate a constraint, an error is raised."

I do understand none of this happens unless you break it yourself, but it
might change both how I write and test triggers, and how I might look at
using other people's triggers or materials.  Knowing my referential
integrity can't be broken is a nice guard rail to have, but if you can't
necessarily count on it, some prominent signs saying "warning, no guard
rail ahead" seem like a good idea.

Thanks for listening!

Ken




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

Dynamically filtering a CTE?

[W. Trevor King]

I have a slow ‘WITH RECURSIVE’ CTE like:

  CREATE VIEW ancestors AS
  WITH RECURSIVE _ancestors(descendant, ancestors) AS (
  SELECT
item.id AS id,
ARRAY[item.ancestor_id] AS ancestors
  FROM items AS item
UNION ALL
  SELECT
child.id AS id
child.ancestors || ancestor.ancestor_id AS ancestors
  FROM _ancestors AS child
  JOIN items as ancestor
ON child.ancestors[array_length(child.ancestors, 1)] = ancestor.id
  )
  SELECT *
  FROM _ancestors
  WHERE child.ancestors[array_length(child.ancestors, 1)] IS NULL;

I'll usually only need a few rows, so I'm being bitten by PostgreSQL's
CTE optimization fence [1].  I'm looking to optimize it by limiting
the dynamically limiting the number of rows in the initial query, as
if it had been:

  WITH RECURSIVE _ancestors(id, ancestors) AS (
  SELECT
item.id AS id,
ARRAY[item.ancestor_id] AS ancestors
  FROM items AS item
  WHERE {your condition here}
UNION ALL
  …
  )

My initial thought was to create a function which accepted a WITH
clause as an argument.  Something like:

  CREATE OR REPLACE FUNCTION ancestors(condition)
RETURNS TABLE(id integer, ancestors integer[]) AS
  $$
WITH RECURSIVE _ancestors(id, ancestors) AS (
SELECT
  item.id AS id,
  ARRAY[item.ancestor_id] AS ancestors
FROM items AS item
WHERE condition
  UNION ALL
…
)
…
  $$ LANGUAGE SQL;

or with ‘WHERE condition(item)’.  But I couldn't find a way to define
an argument that was a where condition [2] or a record→boolean
function [3].  I could probably use PREPARE/EXECUTE [4] to dynamically
construct the WHERE statement, but that looks like it may have its own
optimization issues and there's no way to stash it for use in
subsequent sessions.  Perhaps a function to run the PREPARE?  Is there
an idiomatic way to approach this problem?

Thanks,
Trevor

[1]: https://www.postgresql.org/message-id/201209191305.44674...@kavod.com
[2]: https://www.postgresql.org/docs/10/static/sql-select.html#SQL-WHERE
[3]: https://www.postgresql.org/docs/10/static/sql-createfunction.html
[4]: https://www.postgresql.org/docs/10/static/sql-prepare.html

-- 
This email may be signed or encrypted with GnuPG (http://www.gnupg.org).
For more information, see http://en.wikipedia.org/wiki/Pretty_Good_Privacy


signature.asc
Description: OpenPGP digital signature

Re: Dynamically filtering a CTE?

[David G. Johnston]

On Thursday, April 19, 2018, W. Trevor King  wrote:

>   Is there
> an idiomatic way to approach this problem?
>
>
I would use pl/pgsql as the language and build a query using a combination
of text literals and the format() function - invoking via pl/pgsql's
EXECUTE command.

David J.

Re: Problem with trigger makes Detail record be invalid

[PegoraroF10]

Correct, that delete done a partial commit. And this is absolutely
unacceptable.
I know I did that trigger incorrectly but referential integrity is
obligatory.
Imagine if I have a database crash and need to restore as soon as possible.
How much time I´ll spend removing those records from a backup to get entire
database restored properly. 

Well, I´ll review all my triggers. And I have hundreds of them.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html