Re: Reference-Partitioned Tables
On 2020-Nov-06, Сергей _ wrote: > https://docs.oracle.com/database/121/VLDBG/GUID-00923EB3-05F6-41F7-8437-E42FC9BD9571.htm#VLDBG1093 > > There is a widespread model when data related to properties of a superclass > is stored in a base table, and data related to subclasses in a detail table. > Or > when the main entity has volatile additional parameters. A very simplified > example in the attached image. Sounds easier to achieve by using a JSONB column that holds all the contract details. > The most successful choice of partitioning table *Contracts* is sectioning > by list with key column *ContractTypeID*. It would be nice if the table > *ContractDetails* is partitioned automatically like the parent table > *Contracts*. Then we don't have to add a crutch column *ContractTypeID* to > the table* Contract**Details* and partition manually in sync with > *Contracts*. This field also consumes disk space, since the table of details > is usually large and there can be more than one partitioning key. Hmm, so you want to partition a table based on values appearing in another table. I wouldn't hold my breath waiting for this.
Re: pgagent
I don't think that most of them are related to pgagent, since they are coming anyway (I suppose it's about pgadmin's Dashboard). There is just one that I think it's about "Run now" : ... EET postgres postgres [2104]LOG: statement: UPDATE pgagent.pga_job SET jobnextrun=now()::timestamptz WHERE jobid=1::integer but that's just about it ! Is there any way I can test that pgagent it's able to start a job ? Regards, Gabi On Fri, Nov 6, 2020 at 6:41 PM Adrian Klaver wrote: > On 11/6/20 8:12 AM, Gabi Draghici wrote: > > > > It't not a daemon yet (I started manually) but yes, it's running : > > > > postgres@dbdocs:~> ps aux | grep postgresql > > postgres 2093 0.0 0.3 8720088 218280 ? Ss 17:54 0:00 > > /usr/lib/postgresql12/bin/postgres -D /opt/postgresql/database > > postgres 2315 0.0 0.0 64664 5708 pts/2S17:57 0:00 > > /usr/bin/postgresql12-pgagent hostaddr=10.1.0.4 dbname=postgres > > user=pgagent -s /opt/postgresql/pglog/pg_agent.log > > postgres 2326 0.0 0.0 8696 820 pts/2S+ 17:57 0:00 grep > > --color=auto postgresql > > > > > I've switched log_statement to 'all' and restarted the DB. All I see > > it's a bunch of statements like these : > > > > 2020-11-06 18:07:03.869 EET postgres pgagent [2316]LOG: statement: > > SELECT J.jobid FROM pgagent.pga_job J WHERE jobenabledAND > > jobagentid IS NULLAND jobnextrun <= now()AND (jobhostagent = '' > > OR jobhostagent = 'dbdocs-prd') ORDER BY jobnextrun > > 2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG: statement: > > /*pga4dash*/ > > The above is from when you click 'Run now'? > > > > > Regards, > > Gabi > > > > > > > > > > > > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: pgagent
My bad ! I didn't read the documentation too well ... and completed the 'Host agent' with the IP instead of the hostname ! The job it's running now. Thank you for help ! Regards, Gabriel On Sat, Nov 7, 2020 at 1:29 PM Gabi Draghici wrote: > > I don't think that most of them are related to pgagent, since they are > coming anyway (I suppose it's about pgadmin's Dashboard). > There is just one that I think it's about "Run now" : > > ... EET postgres postgres [2104]LOG: statement: UPDATE > pgagent.pga_job SET jobnextrun=now()::timestamptz WHERE jobid=1::integer > > but that's just about it ! > Is there any way I can test that pgagent it's able to start a job ? > > Regards, > Gabi > > > > > > > On Fri, Nov 6, 2020 at 6:41 PM Adrian Klaver > wrote: > >> On 11/6/20 8:12 AM, Gabi Draghici wrote: >> > >> > It't not a daemon yet (I started manually) but yes, it's running : >> > >> > postgres@dbdocs:~> ps aux | grep postgresql >> > postgres 2093 0.0 0.3 8720088 218280 ? Ss 17:54 0:00 >> > /usr/lib/postgresql12/bin/postgres -D /opt/postgresql/database >> > postgres 2315 0.0 0.0 64664 5708 pts/2S17:57 0:00 >> > /usr/bin/postgresql12-pgagent hostaddr=10.1.0.4 dbname=postgres >> > user=pgagent -s /opt/postgresql/pglog/pg_agent.log >> > postgres 2326 0.0 0.0 8696 820 pts/2S+ 17:57 0:00 grep >> > --color=auto postgresql >> >> > >> > I've switched log_statement to 'all' and restarted the DB. All I see >> > it's a bunch of statements like these : >> > >> > 2020-11-06 18:07:03.869 EET postgres pgagent [2316]LOG: statement: >> > SELECT J.jobid FROM pgagent.pga_job J WHERE jobenabledAND >> > jobagentid IS NULLAND jobnextrun <= now()AND (jobhostagent = '' >> > OR jobhostagent = 'dbdocs-prd') ORDER BY jobnextrun >> > 2020-11-06 18:07:04.466 EET postgres postgres [2104]LOG: statement: >> > /*pga4dash*/ >> >> The above is from when you click 'Run now'? >> >> > >> > Regards, >> > Gabi >> > >> > >> > >> > >> > >> > >> > >> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >
pg_bulkload sequential
Hello, I have a process using pg_bulkload and sometimes i have duplicated keys in my csv file that pg_bulkload uses. My question is: pg_bulkload insert it in sequential order ? Example, if i have the following csv file: key_1;0.00 key_1;100.00 And use the ON_DUPLICATE_KEEP = NEW in .ctl file, it's guaranteed that the value 0.00 will be overwritten with 100.00? Or pg_bulkload can't guarantee this order? Thanks! Leandro Guimarães
Re: pg_bulkload sequential
On 11/7/20 9:28 AM, Leandro Guimarães wrote: Hello, I have a process using pg_bulkload and sometimes i have duplicated keys in my csv file that pg_bulkload uses. My question is: pg_bulkload insert it in sequential order ? Example, if i have the following csv file: key_1;0.00 key_1;100.00 And use the ON_DUPLICATE_KEEP = NEW in .ctl file, it's guaranteed that the value 0.00 will be overwritten with 100.00? Or pg_bulkload can't guarantee this order? Assuming they are in that order in the file and you are using DIRECT mode I would say that would be the case. In PARALLEL mode, who knows? In any case I would be dubious of any process that overwrites and depends strictly on ordering to do the right thing. You are putting a lot of confidence in the data in the CSV file being correctly ordered. Thanks! Leandro Guimarães -- Adrian Klaver adrian.kla...@aklaver.com
Not able to set pgaudit.log with pgaudit 1.3.2 in PostgreSQL 11.9
Hello, I had installed PostgreSQL 11.9 and pgaudit 1.3.2. updated shared_preload_libraries, restarted PostgreSQL, created an extension for pgaudit and set parameters for pgaudit in postgresql.conf. But surprisingly pgaudit.log is not getting picked up and neither able to set it manually. postgres>pwd /usr/pgsql-11/lib postgres>ls -ltr *pgaudit* -rwxr-xr-x. 1 root root 33088 Oct 6 10:48 pgaudit.so postgres> postgres>pwd /usr/pgsql-11/share/extension postgres>ls -ltr *pgaudit* -rw-r--r--. 1 root root 145 Oct 6 10:48 pgaudit.control -rw-r--r--. 1 root root 615 Oct 6 10:48 pgaudit--1.3.2.sql -rw-r--r--. 1 root root 175 Oct 6 10:48 pgaudit--1.3--1.3.1.sql -rw-r--r--. 1 root root 177 Oct 6 10:48 pgaudit--1.3.1--1.3.2.sql postgres> postgres=# create extension pgaudit; CREATE EXTENSION postgres=# select * from pg_Extension; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -+--+--+++---+-- plpgsql | 10 | 11 | f | 1.0| | pgaudit | 10 | 2200 | t | 1.3.2 | | (2 rows) postgres=# select name, setting, source from pg_settings where name like '%audit%'; name| setting | source +-+ pgaudit.log| none| default pgaudit.log_catalog| on | configuration file pgaudit.log_client | on | configuration file pgaudit.log_level | log | default pgaudit.log_parameter | on | configuration file pgaudit.log_relation | off | default pgaudit.log_statement_once | off | default pgaudit.role | | default (8 rows) But I have below parameters in postgresql.conf.. not sure why its not picking up pgaudit.log value. log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_line_prefix = '%m %u %d [%p]: ' shared_preload_libraries = 'pgaudit' # (change requires restart) pgaudit.log = 'write, role, ddl, misc_set' pgaudit.log_catalog = on pgaudit.log_client = on pgaudit.log_parameter = on Thanks, Dhiraam.
Re: Not able to set pgaudit.log with pgaudit 1.3.2 in PostgreSQL 11.9
On 11/7/20 10:42 AM, Dhinakaran R wrote: Hello, I had installed PostgreSQL 11.9 and pgaudit 1.3.2. updated shared_preload_libraries, restarted PostgreSQL, created an extension for pgaudit and set parameters for pgaudit in postgresql.conf. But surprisingly pgaudit.log is not getting picked up and neither able to set it manually. postgres>pwd /usr/pgsql-11/lib postgres>ls -ltr *pgaudit* -rwxr-xr-x. 1 root root 33088 Oct 6 10:48 pgaudit.so postgres> postgres>pwd /usr/pgsql-11/share/extension postgres>ls -ltr *pgaudit* -rw-r--r--. 1 root root 145 Oct 6 10:48 pgaudit.control -rw-r--r--. 1 root root 615 Oct 6 10:48 pgaudit--1.3.2.sql -rw-r--r--. 1 root root 175 Oct 6 10:48 pgaudit--1.3--1.3.1.sql -rw-r--r--. 1 root root 177 Oct 6 10:48 pgaudit--1.3.1--1.3.2.sql postgres> postgres=# create extension pgaudit; CREATE EXTENSION postgres=# select * from pg_Extension; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -+--+--+++---+-- plpgsql | 10 | 11 | f | 1.0 | | pgaudit | 10 | 2200 | t | 1.3.2 | | (2 rows) postgres=# select name, setting, source from pg_settings where name like '%audit%'; name | setting | source +-+ pgaudit.log | none | default pgaudit.log_catalog | on | configuration file pgaudit.log_client | on | configuration file pgaudit.log_level | log | default pgaudit.log_parameter | on | configuration file pgaudit.log_relation | off | default pgaudit.log_statement_once | off | default pgaudit.role | | default (8 rows) But I have below parameters in postgresql.conf.. not sure why its not picking up pgaudit.log value. Have you looked at the Postgres log to see if there are relevant error messages? log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_line_prefix = '%m %u %d [%p]: ' shared_preload_libraries = 'pgaudit' # (change requires restart) pgaudit.log = 'write, role, ddl, misc_set' pgaudit.log_catalog = on pgaudit.log_client = on pgaudit.log_parameter = on Thanks, Dhiraam. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Not able to set pgaudit.log with pgaudit 1.3.2 in PostgreSQL 11.9
Hi Adrian There were no error messages from startup, even with debug level 1 I got only below. 2020-11-07 18:57:07.335 UTC [2290044]: LOG: database system was shut down at 2020-11-07 18:57:03 UTC 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: checkpoint record is at 0/1A3FE00 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: redo record is at 0/1A3FE00; shutdown true 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: next transaction ID: 0:616; next OID: 24676 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: next MultiXactId: 1; next MultiXactOffset: 0 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: oldest unfrozen transaction ID: 561, in database 1 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: oldest MultiXactId: 1, in database 1 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: commit timestamp Xid oldest/newest: 0/0 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: transaction ID wrap limit is 2147484208, limited by database with OID 1 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 1 2020-11-07 18:57:07.335 UTC [2290044]: DEBUG: starting up replication slots 2020-11-07 18:57:07.336 UTC [2290044]: DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 1 2020-11-07 18:57:07.336 UTC [2290044]: DEBUG: MultiXact member stop limit is now 4294914944 based on MultiXact 1 2020-11-07 18:57:07.340 UTC [2290042]: DEBUG: starting background worker process "logical replication launcher" 2020-11-07 18:57:07.340 UTC [2290042]: LOG: database system is ready to accept connections 2020-11-07 18:57:07.342 UTC [2290048]: DEBUG: autovacuum launcher started 2020-11-07 18:57:07.343 UTC [2290050]: DEBUG: logical replication launcher started On Sun, 8 Nov 2020 at 00:25, Adrian Klaver wrote: > On 11/7/20 10:42 AM, Dhinakaran R wrote: > > Hello, > > > > I had installed PostgreSQL 11.9 and pgaudit 1.3.2. updated > > shared_preload_libraries, restarted PostgreSQL, created an extension for > > pgaudit and set parameters for pgaudit in postgresql.conf. But > > surprisingly pgaudit.log is not getting picked up and neither able to > > set it manually. > > > > > > postgres>pwd > > /usr/pgsql-11/lib > > postgres>ls -ltr *pgaudit* > > -rwxr-xr-x. 1 root root 33088 Oct 6 10:48 pgaudit.so > > postgres> > > > > postgres>pwd > > /usr/pgsql-11/share/extension > > postgres>ls -ltr *pgaudit* > > -rw-r--r--. 1 root root 145 Oct 6 10:48 pgaudit.control > > -rw-r--r--. 1 root root 615 Oct 6 10:48 pgaudit--1.3.2.sql > > -rw-r--r--. 1 root root 175 Oct 6 10:48 pgaudit--1.3--1.3.1.sql > > -rw-r--r--. 1 root root 177 Oct 6 10:48 pgaudit--1.3.1--1.3.2.sql > > postgres> > > > > postgres=# create extension pgaudit; > > CREATE EXTENSION > > postgres=# select * from pg_Extension; > > extname | extowner | extnamespace | extrelocatable | extversion | > > extconfig | extcondition > > > -+--+--+++---+-- > > plpgsql | 10 | 11 | f | 1.0| > >| > > pgaudit | 10 | 2200 | t | 1.3.2 | > >| > > (2 rows) > > > > > > > > postgres=# select name, setting, source from pg_settings where name like > > '%audit%'; > > name| setting | source > > +-+ > > pgaudit.log| none| default > > pgaudit.log_catalog| on | configuration file > > pgaudit.log_client | on | configuration file > > pgaudit.log_level | log | default > > pgaudit.log_parameter | on | configuration file > > pgaudit.log_relation | off | default > > pgaudit.log_statement_once | off | default > > pgaudit.role | | default > > (8 rows) > > > > > > > > > > But I have below parameters in postgresql.conf.. not sure why its not > > picking up pgaudit.log value. > > Have you looked at the Postgres log to see if there are relevant error > messages? > > > > > log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' > > log_line_prefix = '%m %u %d [%p]: ' > > shared_preload_libraries = 'pgaudit' # (change requires restart) > > pgaudit.log = 'write, role, ddl, misc_set' > > pgaudit.log_catalog = on > > pgaudit.log_client = on > > pgaudit.log_parameter = on > > > > Thanks, > > Dhiraam. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
RE: Not able to set pgaudit.log with pgaudit 1.3.2 in PostgreSQL 11.9
Dhinakaran R wrote on 2020/11/07 02:03:22 PM: > > I had installed PostgreSQL 11.9 and pgaudit 1.3.2. updated > > shared_preload_libraries, restarted PostgreSQL, created an extension for > > pgaudit and set parameters for pgaudit in postgresql.conf. But > > surprisingly pgaudit.log is not getting picked up and neither able to > > set it manually. > > pgaudit.log = 'write, role, ddl, misc_set' misc_set was added in pgAudit 1.4, if you remove that it will likely work. pgAudit should probably log when it has an invalid parameter. Brad.
Foreign Data Wrapper Handler
Can anyone recommend a good online resource for learning how to set up a foreign data wrapper using a custom fdw name? It seems the trick is to use a handler to make it work but so far the search results have been elusive for creating a fdw with a successful outcome. I'm using 'PostgreSQL 11.8 on amd64-portbld-freebsd12.1, compiled by FreeBSD clang version 8.0.1 (tags/RELEASE_801/final 366581) (based on LLVM 8.0.1), 64-bit' Thanks for your help! Sue -- Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hu...@brookhurstdata.com Mobile: 314-486-3261
Re: Foreign Data Wrapper Handler
On 11/7/20 12:44 PM, Susan Hurst wrote: Can anyone recommend a good online resource for learning how to set up a foreign data wrapper using a custom fdw name? It seems the trick is to use a handler to make it work but so far the search results have been elusive for creating a fdw with a successful outcome. Have you gone through this?: https://www.postgresql.org/docs/12/fdwhandler.html Also can we get a definition of 'custom fdw name'? I'm using 'PostgreSQL 11.8 on amd64-portbld-freebsd12.1, compiled by FreeBSD clang version 8.0.1 (tags/RELEASE_801/final 366581) (based on LLVM 8.0.1), 64-bit' Thanks for your help! Sue -- Adrian Klaver adrian.kla...@aklaver.com
Building for 64-bit platform
Hi, I build libpq with the standard configure/dmake. Now I realize I need t build it for a 64-bit platform. My questions are: Is it enough to ust do [code] cd libpq CFLAGS="-m64" LDFLAGS="-m64" ./configure dmake [/code] or I have to do: [code] dmake clean [/code] ? 2. Is my configure line above correct? Or PostgreSQL configure contains special flags for 64 bit builds already? Thank you.
Re: Building for 64-bit platform
Igor Korot writes: > I build libpq with the standard configure/dmake. > Now I realize I need t build it for a 64-bit platform. > My questions are: > Is it enough to ust do Do "make distclean" at the top level, then re-configure with the new options and re-make. Maybe you can get away with a partial rebuild, but there is no way that it's worth your time to experiment. On any machine built in the last two decades, you could have already finished a full rebuild in the time it took me to type this. On the other hand, if you do a partial rebuild and it turns out to be broken, you could waste many hours figuring that out. regards, tom lane
