Convert a row to a nested JSON document containing all relations in PostgreSQL

[Ali Alizadeh]

Hello.


In PostgreSQL 10.10, I have created a trigger function that converts the `NEW` 
row to a JSON object using `to_jsonb(NEW)`. But now I need to include the 
records on the other side of the foreign keys in `NEW` record in the JSON 
object in a nested fashion.


What is the best and most generic way to accomplish this without prior 
knowledge about the schema of the `NEW` record? I need to keep this trigger 
function as generic as possible, because I plan to use it on all tables. One 
level of depth in following foreign keys is currently enough for me.


Thank you.

Re: Environment Variable for --data-checksum during initdb

[Ray Cote]

On Thu, Sep 5, 2019 at 3:25 PM Ray Cote 
wrote:
>
> I'm trying to remember what environment variable can be set
--data-checksum before running postgresql-setup initdb. I know I've seen it
but it escapes my recall at the moment.
>
> The PostgreSQL documentation has a nice page of environment variables for
when PostgreSQL is running, but I'm not finding the variable to set during
the initdb phase.
>
> Any help to jog my memory greatly appreciated.
> --Ray


Found my answer:
There are three variables you can set, one for init, one for upgrading, and
one for debug messaging.
These can be set when calling postgresql-nn-setup.
No documentation regarding these in the PostgreSQL documentation.
I'm assuming this feature is part of the RHEL/CentOS deploy configuration.

Environment:
  PGSETUP_INITDB_OPTIONS Options carried by this variable are passed to
 subsequent call of `initdb` binary (see man
 initdb(1)).  This variable is used also during
 'upgrade' mode because the new cluster is
actually
 re-initialized from the old one.
  PGSETUP_PGUPGRADE_OPTIONS  Options in this variable are passed next to the
 subsequent call of `pg_upgrade`.  For more info
 about possible options please look at man
 pg_upgrade(1).
  PGSETUP_DEBUG  Set to '1' if you want to see debugging output.

kind of a bag of attributes in a DB . . .

[Albretch Mueller]

Say, you get lots of data and their corresponding metadata, which in
some cases may be undefined or undeclared (left as an empty string).
Think of youtube json files or the result of the "file" command.

I need to be able to "instantly" search that metadata and I think DBs
are best for such jobs and get some metrics out of it.

I know this is not exactly a kosher way to deal with data which can't
be represented in a nice tabular form, but I don't find the idea that
half way off either.

What is the pattern, anti-pattern or whatever relating to such design?

Do you know of such implementations with such data?

lbrtchx

Re: Environment Variable for --data-checksum during initdb

[Adrian Klaver]

On 9/7/19 5:32 AM, Ray Cote wrote:
On Thu, Sep 5, 2019 at 3:25 PM Ray Cote 
> wrote:

 >
 > I'm trying to remember what environment variable can be set 
--data-checksum before running postgresql-setup initdb. I know I've seen 
it but it escapes my recall at the moment.

 >
 > The PostgreSQL documentation has a nice page of environment variables 
for when PostgreSQL is running, but I'm not finding the variable to set 
during the initdb phase.

 >
 > Any help to jog my memory greatly appreciated.
 > --Ray


Found my answer:
There are three variables you can set, one for init, one for upgrading, 
and one for debug messaging.

These can be set when calling postgresql-nn-setup.
No documentation regarding these in the PostgreSQL documentation.
I'm assuming this feature is part of the RHEL/CentOS deploy configuration.


I believe so as there is no mention of the below in the Postgres source.



Environment:
   PGSETUP_INITDB_OPTIONS     Options carried by this variable are passed to
                              subsequent call of `initdb` binary (see man
                              initdb(1)).  This variable is used also during
                              'upgrade' mode because the new cluster is 
actually

                              re-initialized from the old one.
   PGSETUP_PGUPGRADE_OPTIONS  Options in this variable are passed next 
to the
                              subsequent call of `pg_upgrade`.  For more 
info

                              about possible options please look at man
                              pg_upgrade(1).
   PGSETUP_DEBUG              Set to '1' if you want to see debugging 
output.



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

Re: kind of a bag of attributes in a DB . . .

[Adrian Klaver]

On 9/7/19 5:45 AM, Albretch Mueller wrote:

Say, you get lots of data and their corresponding metadata, which in
some cases may be undefined or undeclared (left as an empty string).
Think of youtube json files or the result of the "file" command.

I need to be able to "instantly" search that metadata and I think DBs
are best for such jobs and get some metrics out of it.


Is the metadata uniform or are you dealing with a variety of different data?




I know this is not exactly a kosher way to deal with data which can't
be represented in a nice tabular form, but I don't find the idea that
half way off either.

What is the pattern, anti-pattern or whatever relating to such design?

Do you know of such implementations with such data?

lbrtchx






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

Re: Convert a row to a nested JSON document containing all relations in PostgreSQL

[Adrian Klaver]

On 9/6/19 9:35 PM, Ali Alizadeh wrote:

Hello.


In PostgreSQL 10.10, I have created a trigger function that converts the 
`|NEW|` row to a JSON object using |`to_jsonb(NEW)|`. But now I need to 
include the records on the other side of the foreign keys in |`NEW|` 
record in the JSON object in a nested fashion.


Questions:

1) What side are you talking about, the parent of the record or the 
children?


2) What procedural language are you using?




What is the best and most generic way to accomplish this without prior 
knowledge about the schema of the `|NEW`| record? I need to keep this 
trigger function as generic as possible, because I plan to use it on all 
tables. One level of depth in following foreign keys is currently enough 
for me.



Thank you.




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

How to access Postgres .pgpass file from php?

[Howard Wells]

I have my php files in the web root, and the Postgres 10 logon credentials are 
in the php file.  I want to put them outside the web root for security, because 
a malicious robot could easily read the credentials.

After research, I found the .pgpass file.  That looks like the ideal solution, 
but after even more research, I haven't found how to use that file from a php 
logon script.

Here is the section from my php script:

$dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s', [
'host' => '000.00.00.00',
'port' => '5432',
'dbname' => '[dbname]',
'user' => '[username]',
'password' => '[password]',
]);

Currently I store the real dbname, user and password in the php.  My questions 
are:

1. How can I access it from the .pgpass file instead?

2. Where is .phpass loccated in Apache2 Ubuntu 18.04?

Thanks for any help with this.

Howard

Re: How to access Postgres .pgpass file from php?

[Adrian Klaver]

On 9/7/19 3:17 PM, Howard Wells wrote:
I have my php files in the web root, and the Postgres 10 logon 
credentials are in the php file.  I want to put them outside the web 
root for security, because a malicious robot could easily read the 
credentials.


After research, I found the .pgpass file.  That looks like the ideal 
solution, but after even more research, I haven't found how to use that 
file from a php logon script.


Here is the section from my php script:

$dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s', [
     'host' => '000.00.00.00',
     'port' => '5432',
     'dbname' => '[dbname]',
     'user' => '[username]',
     'password' => '[password]',
]);

Currently I store the real dbname, user and password in the php.  My 
questions are:


1. How can I access it from the .pgpass file instead?


I think what you are looking for is the connection service file:

https://www.postgresql.org/docs/11/libpq-pgservice.html




2. Where is .phpass loccated in Apache2 Ubuntu 18.04?


Information on where .pgpass can be:

https://www.postgresql.org/docs/11/libpq-pgpass.html



Thanks for any help with this.

Howard






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