Re: What is wrong with my pgadmin?
David G. Johnston wrote: > On Monday, March 5, 2018, Łukasz Jarych wrote: > > Anyone pleasE? > > Don't know but you might get better results by emailing the support list for > the pgAdmin application. This list is for general questions about the server. I agree about asking the specialized list, but -general is certainly not only for the server. Yours, Laurenz Albe
Re: dirty_ratio & dirty_background_ratio settings with huge memory
pinker wrote: > I've got cutomer with really huge RAM, now it's: > total used free sharedbuffers cached > Mem: 31021113052596 49515 2088019922961185 > -/+ buffers/cache: 904183011693 > Swap: 8191 1 8190 > > (free -m) > > and before it was twice more (6TB). > > and trying to figure out how to set dirty_ratio & dirty_background_ratio > parameters. Even for normal sized server those default sizes are to high, > but now would be ridiculously huge, respectively 1,24TB and 300GB. > I'm thinking about 1 percent for dirty_ratio and not using > dirty_background_ratio because it's not possible to set it below 1% but to > set dirty_background_bytes instead to about 1-2GB. > Maybe someone has got other experience with RAM of this size and those > settings? Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes and not use the *_ratio settings. 2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: dirty_ratio & dirty_background_ratio settings with huge memory
Tomas Vondra wrote: > > > > 2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine. > > > > It should be the other way around: dirty_background_bytes < dirty_bytes Of course, thanks for the correction. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: dirty_ratio & dirty_background_ratio settings with huge memory
pinker wrote: > Laurenz Albe wrote > > Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes > > and not use the *_ratio settings. > > > > 2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds > > fine. To repeat (for the archive): it should be the other way round. > Thank you Laurenz. I was always wondering why not to set bigger window > between those 2. Because setting dirty_background_bytes seems quite natural > for me, i.e. start to write asynchronously faster, but why to provoke stalls > faster? is it not better to stretch the window much wider, like 1GB for > dirty_background_bytes and for instance 20 GB dirty_bytes? it's the Approach > 3: Both Ways from > https://lonesysadmin.net/2013/12/22/better-linux-disk-caching-performance-vm-dirty_ratio/ I'd say that setting vm.dirty_background_bytes low and vm.dirty_bytes very high is not a good idea, at least for databases: - If flushing data to disk in the background can keep up with the write activity, you're fine. But then you'd also be fine with a lower setting for vm.dirty_bytes, right? - If flushing to disk cannot keep up with the write activity, you'll get an I/O spike when everything is flushed to disk at checkpoint time and the database may become unresponsive for a while. The idea behind a low vm.dirty_bytes setting is to avoid one big I/O spike and have several smaller ones instead. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Help troubleshooting SubtransControlLock problems
Scott Frazer wrote: > Hi, we have a Postgres 9.6 setup using replication that has recently started > seeing a lot of processes stuck in > "SubtransControlLock" as a wait_event on the read-replicas. Like this, only > usually about 300-800 of them: > > > 179706 | LWLockNamed | SubtransControlLock > 186602 | LWLockNamed | SubtransControlLock > 186606 | LWLockNamed | SubtransControlLock > 180947 | LWLockNamed | SubtransControlLock > 186621 | LWLockNamed | SubtransControlLock > > The server then begins to crawl, with some queries just never finishing until > I finally shut the server down. > > Searching for that particular combo of wait_event_type and wait_event only > seems to turn up the page > about statistics collection, but no helpful information on troubleshooting > this lock. > > Restarting the replica server clears the locks and allows us to start working > again, but it's happened > twice now in 12 hours and I'm worried it will happen again. > > Does anyone have any advice on where to start looking? I don't think there is any connection to statistics collection. This lock is used when subtransactions (SAVEPOINTs in SQL or EXCEPTION blocks in PL/pgSQL) are created, read or removed. This sounds like a PostgreSQL bug. What is the exact PostgreSQL version you are running? It would be cool if you could get a stack trace from the backend that is holding the lock. Yours, Laurenz Albe
Re: Help troubleshooting SubtransControlLock problems
Scott Frazer wrote: > > On Wed, Mar 7, 2018 at 9:52 AM, Laurenz Albe wrote: > > Scott Frazer wrote: > > > Hi, we have a Postgres 9.6 setup using replication that has recently > > > started seeing a lot of processes stuck in > > > "SubtransControlLock" as a wait_event on the read-replicas. Like this, > > > only usually about 300-800 of them: > > > > > > > > > 179706 | LWLockNamed | SubtransControlLock > > > 186602 | LWLockNamed | SubtransControlLock > > > 186606 | LWLockNamed | SubtransControlLock > > > 180947 | LWLockNamed | SubtransControlLock > > > 186621 | LWLockNamed | SubtransControlLock > > > > > > The server then begins to crawl, with some queries just never finishing > > > until I finally shut the server down. > > > > > > Searching for that particular combo of wait_event_type and wait_event > > > only seems to turn up the page > > > about statistics collection, but no helpful information on > > > troubleshooting this lock. > > > > > > Restarting the replica server clears the locks and allows us to start > > > working again, but it's happened > > > twice now in 12 hours and I'm worried it will happen again. > > > > > > Does anyone have any advice on where to start looking? > > > > I don't think there is any connection to statistics collection. > > > > This lock is used when subtransactions (SAVEPOINTs in SQL or EXCEPTION > > blocks > > in PL/pgSQL) are created, read or removed. > > > > This sounds like a PostgreSQL bug. > > > > What is the exact PostgreSQL version you are running? > > > > It would be cool if you could get a stack trace from the backend that is > > holding the lock. > > Server version is 9.6.5 > > Is there a decent guide to getting a stack trace on Centos7 when using the > official Postgres repo? > trying to follow the Fedora guide put the debug info for 9.2.23 on the box > instead of the 9.6.5 version. You'll have to install debuginfo from the same source where you got PostgreSQL. It has to be for the same database version. You should upgrade to 9.6.8 and see if the problem persists. There is a guid how to get a stack trace at https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Point-in-time recovery after failover
Dylan Luong wrote: > We are on Postgres 9.6 and we have primary/standby wal replication setup for > HA. > > I am trying to perform a point-in-time recovery after a failover has occurred. > > I extracted the base backups (tar files) to the data directory and extracted > the all the archive backups (P1 and S1) to the archive directory. > Followed the steps from > Created recovery.conf with the following, but the recovery only recovers, to > the point before the failover and comes online. > > Example of my recovery.conf : > restore_command = 'cp /pg_backup/backup/archive/%f %p' > recovery_target_time = '2018-03-13 13:54:00' > recovery_target_inclusive = 'false' > recovery_target_timeline = '6' (I also tried revovery_target_timeline = > ‘lastest) > > Is it possible to recovery to PITR after a failover using base backup from > before failover and both archive backups from P1 and S1? > > > Here is the log output : > > 018-03-13 20:46:53 ACDT [154912]: [1-1] db=,user= app=,host= LOG: database > system was interrupted; last known up at 2018-03-1 > 3 01:31:25 ACDT > 2018-03-13 20:46:53 ACDT [154912]: [2-1] db=,user= app=,host= LOG: restored > log file "0006.history" from archive > cp: cannot stat ‘/pg_backup/backup/archive/0007.history’: No such file or > directory > 2018-03-13 20:46:53 ACDT [154912]: [3-1] db=,user= app=,host= LOG: starting > point-in-time recovery to 2018-03-13 13:54:00+10:3 > 0 > 2018-03-13 20:46:53 ACDT [154912]: [4-1] db=,user= app=,host= LOG: restored > log file "0006.history" from archive > cp: cannot stat ‘/pg_backup/backup/archive/000601110087’: No such > file or directory > 2018-03-13 20:46:53 ACDT [154912]: [5-1] db=,user= app=,host= LOG: restored > log file "000501110087" from archive > cp: cannot stat ‘/pg_backup/backup/archive/0005.history’: No such file or > directory > 2018-03-13 20:46:53 ACDT [154912]: [6-1] db=,user= app=,host= LOG: redo > starts at 111/8728 > 2018-03-13 20:46:53 ACDT [154912]: [7-1] db=,user= app=,host= LOG: > consistent recovery state reached at 111/870B4418 > 2018-03-13 20:46:53 ACDT [154908]: [3-1] db=,user= app=,host= LOG: database > system is ready to accept read only connections > cp: cannot stat ‘/pg_backup/backup/archive/000601110088’: No such > file or directory > 2018-03-13 20:46:53 ACDT [154912]: [8-1] db=,user= app=,host= LOG: restored > log file "000501110088" from archive > cp: cannot stat ‘/pg_backup/backup/archive/000601110089’: No such > file or directory > 2018-03-13 20:46:53 ACDT [154912]: [9-1] db=,user= app=,host= LOG: restored > log file "000501110089" from archive > cp: cannot stat ‘/pg_backup/backup/archive/00060111008A’: No such > file or directory > 2018-03-13 20:46:53 ACDT [154912]: [10-1] db=,user= app=,host= LOG: restored > log file "00050111008A" from archive [...] > cp: cannot stat ‘/pg_backup/backup/archive/0006011100AD’: No such > file or directory > 2018-03-13 20:46:57 ACDT [154912]: [45-1] db=,user= app=,host= LOG: restored > log file "0005011100AD" from archive > cp: cannot stat ‘/pg_backup/backup/archive/0006011100AE’: No such > file or directory > cp: cannot stat ‘/pg_backup/backup/archive/0005011100AE’: No such > file or directory > 2018-03-13 20:46:57 ACDT [154912]: [46-1] db=,user= app=,host= LOG: redo > done at 111/ADFFE160 > 2018-03-13 20:46:57 ACDT [154912]: [47-1] db=,user= app=,host= LOG: last > completed transaction was at log time 2018-03-13 13:4 > 0:02.36347+10:30 > > > Here is what is in the archive directory after extracting from the archive > backups and after database comes online: > [...] > -rw---. 1 postgres postgres 16777216 Mar 13 13:40 0005011100AA > -rw---. 1 postgres postgres 16777216 Mar 13 13:40 0005011100AB > -rw---. 1 postgres postgres 16777216 Mar 13 13:40 0005011100AC > -rw---. 1 postgres postgres 16777216 Mar 13 13:40 0005011100AD > -rw---. 1 postgres postgres 16777216 Mar 13 13:48 > 0005011100B1.partial > -rw---. 1 postgres postgres 16777216 Mar 13 13:49 0006011100B1 > -rw---. 1 postgres postgres 16777216 Mar 13 14:04 0006011100B2 > -rw---. 1 postgres postgres 221 Mar 13 13:48 0006.history > -rw---. 1 postgres postgres 16777216 Mar 13 20:48 0007011100AD > -rw---. 1 postgres postgres 271 Mar 13 20:46 0007.history You are missing 0005011100AE or 0006011100AE. One of these files should be there. I suspect that there is a problem with your WAL archiving in connection with failover. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: You might be able to move the set-returning function into a LATERAL FROM item.
Alexander Farber wrote: > for a word game in PostgreSQL 10.3 I have a table with jsonb column "tiles". > > The column holds either a JSON array of objects (word tiles played) or a > string (of swapped letters). > > I am trying to fetch a history/protocol of a game with: > > > CREATE OR REPLACE FUNCTION words_get_moves( > [...] AS > $func$ > [...] > CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN > JSONB_ARRAY_ELEMENTS(tiles) ELSE NULL END AS x > [...] > $func$ LANGUAGE sql; > > However calling this stored function gives the error: > > ERROR: 0A000: set-returning functions are not allowed in CASE > LINE 18: ... CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRA... > ^ > HINT: You might be able to move the set-returning function into a LATERAL > FROM item. > > I have read that PostgreSQL 10 handles SRF more strictly, but what does it > want me to do here, to add 1 more table to the LEFT JOIN? The problem is that "jsonb_array_elements" returns several rows, which does not make sense in this context. Which of the rows do you want? If you know that it will always return at most one row, you could use: ... THEN (SELECT jae FROM jsonb_array_elements(tiles) jae LIMIT 1) Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Prepared statements
Rakesh Kumar wrote: > Only in Oracle 12c there are > options to let the planner change existing plan by peeking into the parameter > values (supplied in ? of prepare) and checking it against the distribution. You mean Oracle 11g. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Issue with PostgreSQL replication and PITR
Amee Sankhesara - Quipment India wrote: > We have setup replication and PITR on this PostgreSQL server and wal files > for replication and PITR we are storing on the another server which is > shared with primary and secondary server. > > For doing some OS migration activity for the PostgreSQL database servers > We created two new replication server and promoted to new master server for > the same > > During that activity one .partial file is created on master server and after > that wal files are not copied in shared folder from which secondary server > take it and restore it. All Wal files after .partial file are stored in > pg_xlog > folder of master data so it is increasing size of data directory of master > server. > > Wal files are not copying in shared location of master and secondary server > but > still replication is running and on secondary server data is up to date. > > In logs getting only below warning : > Warning : The failed archive command was: > copy "D:\PostgreSQL\9.6\data\pg_xlog\00010A8800F8.partial" > "\\10.0.0.35\Archive_Replication\00010A8800F8.partial" > | copy "D:\PostgreSQL\9.6\data\pg_xlog\00010A8800F8.partial" > "\\10.0.0.35\Archive_PITR\00010A8800F8.partial" > > Can’t figure out that in shared folder wal files are not copied but still > secondary server is running in sync with master server. > > What could be reason behind this? If archiving is not working and the standby still can replicat, I would assume that streaming replication is configured. Is "primary_conninfo" defined in "recovery.conf"? Are there WAL sender and WAL receiver processes? > And how to remove this .partial file from pg_xlog safely and how to start > copying wal files from pg_xlog of master server to shared location of master > and secondary server ? You should try to figure out why your "archive_command" fails; perhaps try it by hand. Once you have got that fixed, archiving will continue normally. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Connection hangs on new created schema
Juan Manuel Cuello wrote: > I have a postgresql database with around 4000 schemas. Each schema has around > the same 65 tables. There are 8 processes connected to the database (it is a > web app). > Each process has only one direct connection to the database (no connection > pool). > > Everything works ok until a new schema (with all its tables, indices, etc) > is added to the database. When this happens, the next query made to the > database > takes too long, sometimes more than a minute o two. Also I see a high DB CPU > usage > during that lapse. Then everything continues working ok as usual and CPU > drops to normal levels. > > I'm mostly sure this has to do with the amount of schemas, maybe related to > relcache, > but not sure why creating new schemas causes all this and where to look to > solve this problem. If you know the slow query EXPLAIN (ANALYZE, BUFFERS) it and see where the time is spent. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: dblink: could not send query: another command is already in progress
Thiemo Kellner wrote: > I try to use dblink to create a asynchronous logging facility. I have > the following code > > [...] > perform dblink_send_query( > V_DBLINK_CONNECTION_NAME, > V_QUERY > ); > raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); > raise notice 'Last error: %', > dblink_error_message(V_DBLINK_CONNECTION_NAME); > raise notice 'Cancel query: %', > dblink_cancel_query(V_DBLINK_CONNECTION_NAME); > -- ??? commit needed? > raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); > while dblink_is_busy(V_DBLINK_CONNECTION_NAME) != 0 loop > perform pg_sleep_for(V_WAIT_FOR); > raise notice 'Waited for commit for % seconds', V_WAIT_FOR; > raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); > end loop; > perform dblink_send_query( > V_DBLINK_CONNECTION_NAME, > 'commit' > ); > > I get the following output. > psql:testing/test.pg_sql:41: NOTICE: Connection busy: 1 > > psql:testing/test.pg_sql:41: NOTICE: Last error: OK > > psql:testing/test.pg_sql:41: NOTICE: Cancel query: OK > > psql:testing/test.pg_sql:41: NOTICE: Connection busy: 0 > > psql:testing/test.pg_sql:41: NOTICE: could not send query: another > command is already in progress > Has anyone an idea? The cause of the error message is clear; as the documentation says: dblink_cancel_query attempts to cancel any query that is in progress on the named connection. Note that this is not certain to succeed (since, for example, the remote query might already have finished). A cancel request simply improves the odds that the query will fail soon. You must still complete the normal query protocol, for example by calling dblink_get_result. Not sure if that answers all your questions. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Asynchronous Trigger?
Cory Tucker wrote: > Is it possible to have the execution of a trigger (or any function) not block > the > completion of the statement they are associated with? Constraint triggers can be deferred to the end of the transaction, but I am certain that is not what you are looking for. > A pattern I had hoped to implement was to do a quick update of rows that > signaled > they needed attention, and then an async per-row trigger would come and do the > maintenance (in this case, make an expensive materialized view). > > Any suggestions welcome. This seems like a really bad idea - even if it happens "in the background", would you want an expensive materialized view to be updated whenever a row is modified? If you modify three rows, that would happen three times in a row! One solution would be to set a flag somewhere that marks the materialized view dirty, and a regular cron-job that checks the flag and rebuilds the view. With the asynchronous solution you had in mind, the materialized view would not be up to date anyway. Other than that, if your materialized view definition is simple enough, you might be able to use a regular table and a trigger that updates the table to reflect the modifications caused by the insert, update or delete. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: dblink: could not send query: another command is already in progress
Thiemo Kellner wrote: > The cause of the error message is clear; as the documentation says: > > >the query will fail soon. You must still complete the normal query > > protocol, > >for example by calling dblink_get_result. > > Ah, maybe this is the underlying problem. If dblink requires that > results get retrieved by dblink_get_result before the dblink is actually > ready to receive another query, it would explain the error I get. > However, I feel than the result of dblink_is_busy is faulty, > counter-intuitive or just useless in that context. Or I just > misinterpreted documentation: "checks if connection is busy with an > async query" > > My understand there is that the actual query is still being processed, > the gathering of the results. I did not count the keeping of the result > as part of the query. That is a misunderstanding. If the connection is "busy", that means that the asynchronous query is still busy receiving a result from the server. Once it is no longer busy, a result has arrived and is ready to be consumed. You have to consume the result before you can send the next query. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: dblink: could not send query: another command is already in progress
Thiemo Kellner wrote:
> On 03/30/18 11:14, Laurenz Albe wrote:
> > You have to consume the result before you can send the next query.
>
> I changed implementation but still get the same error but now different
> context. I tried to retrieve the result but I failed
>
> I committed the last code to its project repository at SourceForge
> https://sourceforge.net/p/pglogger/code/HEAD/tree/
>
> and put the code to pastebin.com
> Test calls: https://pastebin.com/xfUp9NAL
> function WRITE_MESSAGE: https://pastebin.com/LDjE0Czx
> function WRITE_MESSAGE_TO_TABLE: https://pastebin.com/vkBkehZF
>
> [...]
> psql:testing/test.pg_sql:42: ERROR: function
> WRITE_MESSAGE_TO_TABLE(i_function => text, i_message => text, i_level =>
> text, i_present_user => name, i_session_user => name,
> i_transaction_timestamp => timestamp with time zone, i_transaction_id =>
> bigint, i_server_pid => bigint, i_remote_address => inet, i_remote_port
> => integer) does not exist
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> CONTEXT: Error occurred on dblink connection named
> "PGLOGGER_CONNECTION": could not execute query.
> SQL statement "SELECT * from
> dblink_get_result(V_DBLINK_CONNECTION_NAME) as t(ID bigint)"
> PL/pgSQL function write_message(text,text) line 126 at PERFORM
> psql:testing/test.pg_sql:42: STATEMENT: select WRITE_MESSAGE('Test
> WRITE_MESSAGE 2nd call', 'NOTICE');
I couldn't dig through all your code, but two things seemed suspicious:
> if (
> V_DBLINK_CONNECTION_NAMES is null
> or V_DBLINK_CONNECTION_NAME != any(V_DBLINK_CONNECTION_NAMES)
I don't think you want "!=ANY" there. Shouldn't that be "<>ALL" ?
"<>ANY" will *always* be true if there are at least two different entries.
The other thing is that you seem to call "dblink_get_result" on any existing
connection before use. But you can only call the function if there is a
result outstanding.
One of your error messages above seems to suggest that this is your problem.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: dblink: could not send query: another command is already in progress
Thiemo Kellner wrote: > > The other thing is that you seem to call "dblink_get_result" on any existing > > connection before use. But you can only call the function if there is a > > result outstanding. > > I call dblink_get_result only if I do not open a dblink connection, i. > e. only on second and following function calls. I put more notice output > into the code showing that dblink_send_query has been called once before > the first call of dblink_get_result. I changed my query to reflect > return bigint value of the called function write_message_to_table. Error > persists. That's not going to work. You can only call dblink_get_result if there is an outstanding result. You cannot call it on an idle connection, that will cause an error. You should write your code so that whenever dblink_send_query has been called, you also call dblink_get_result, regardless if the query has been cancelled or not, so that you always leave the connection in the "ready for query" state. Then you don't have to clean up. Of course you could also ignore the error you get on dblink_get_result, but that is ugly. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_basebackup or dump for starting replication process
PegoraroF10 wrote: > For replication purposes only, there are any difference between pg_basebackup > or dump to copy data from Master to Slave ? > On Docs is written that pg_basebackup can be used both for point-in-time > recovery and as the starting point for a log shipping or streaming > replication standby servers. > > We are using just Dump, there are any caveat of using it for logical > replication ? For *logical* replication you can start with a "pg_dump --schema-only", but for *physical* replication only a pg_basebackup will do. Make sure that he tables are empty before you start logical replication. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: dealing with lock
On Fri, 2018-04-06 at 16:58 +0200, Thomas Poty wrote: > Here is a bit of context : we are migrating from MySQL to PostgreSQL and we > have about 1000 tables. > Some tables are quite small but some others are very large. The service > provided to our clients > relies on a high avaiability with a minimum down time due to any legal > deadlines. > > So, lets imagine : > in Transaction 1 : I am querying Table A (select) > in Transaction 2 : I am trying to alter Table A ( due to our product > evolution) > in Transaction 3 : I am want to query Table1 (select) > > in MySQL : Transaction 1 retrieve data in Table A. > Transaction 2 : is trying to alter Table A but it is blocked by Transaction 1 > Transaction 3 : Transaction 1 retrieves data in Table A ( Retreiving data is > possible until Transaction 2 commit) > > In PostgreSQL, it is a bit different : Transaction 1 retrieve data in Table A. > Transaction 2 : is trying to alter Table A but it is blocked by Transaction 1 > Transaction 3 : Transaction 3 cannot retrieve data because Transaction 2 did > not terminate its transaction. > > So, with MySQL, the application is able to keep working with the table until > the alter table completed. > > With PostgreSQL, the application will probably be blocked (until having the > lock on this table). > If I understand, if the alter table takes a long time (several hours) to > execute, clients will be blocked during several hours. > > How do you deal with this problem? Maybe I missed something ? The solution is to avoid ALTER TABLE statements that have to rewrite the table outside of maintenance windows. If your transactions are short, as they should be, it should not be a big deal to add or drop a column, for example. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Suggest the best values for the postgres configuration parameters
Raghavendra Rao J S V wrote: > We are using postgres 9.2 version on Centos operating system. That's no good, 9.2 is out of support. Use v10 or at least 9.6. > Total ram available is 80GB . At present we don't have any connection pool > mechanisiam. Max number of connections are allowed is 1000. That's not good either. Use a connection pool. > Could you please suggest the best values for the below configuration > parameters? > > shared_buffers 4 or 8 GB > effective_cache_size 80 GB minus what the OS and other applications need. > work_mem Depends on your queries. With max_connections absurdly high as 1000, no more than 50MB. > maintenance_work_mem 1GB > checkpoint_segments Depends on your workload. > wal_keep_segments 0 (unless you need replication and have nothing better) > checkpoint_completion_target 0.8 > Max_prepared_transactions =0 0 (unless you need distributed transactions) Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Planning to change autovacuum_vacuum_scale_factor value to zero. Please suggest me if any negative impact.
Raghavendra Rao J S V wrote: > We are using postgres 9.2 version on Centos operating system. We have > around 1300+ tables. > We have following auto vacuum settings are enables. Still few of the tables > which are always busy are not vacuumed. Due to that tables are bloating and > observed few areas has performance degradation. > > autovacuum_max_workers = 6 > autovacuum_naptime = 15s > autovacuum_vacuum_threshold = 25 > autovacuum_analyze_threshold = 10 > autovacuum_vacuum_scale_factor = 0.1 > autovacuum_analyze_scale_factor = 0.05 > autovacuum_vacuum_cost_delay = 10ms > autovacuum_vacuum_cost_limit = 1000 > > To avoid the above problem, I am planning to make ' > autovacuum_vacuum_scale_factor' value to zero and > autovacuum_vacuum_threshold value to 150. Please suggest me does it have any > negative impact. That's an excellent way to keep your database from functioning well. Rather, raise autovacuum_vacuum_cost_limit, or, more aggressively, set autovacuum_vacuum_cost_delay to 0. It is better to change the settings on individual busy tables than changing them globally. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Planning to change autovacuum_vacuum_scale_factor value to zero. Please suggest me if any negative impact.
Raghavendra Rao J S V wrote: > Are you suggesting me to keep "autovacuum_vacuum_cost_limit" to zero or > " autovacuum_vacuum_scale_factor" to zero or both? Please clarify me. autovacuum_vacuum_cost_limit is effectively 100 by default. You could raise it to 2000 or more. If that does not take care of your problems, set autovacuum_vacuum_cost_delay to 0. That will make autovacuum even more aggressive. Do *not* set autovacuum_vacuum_scale_factor to 0. That will just cause autovacuum to run all the time without getting anywhere. Sorry for trying to use irony in e-mail; I am aware I shouldn't do that. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: To prefer sorts or filters in postgres, that is the question....
Bob Jones wrote: > I've been playing around with hierarchical queries a bit and one thing > I wanted to do is build a query that gives the ultimate parent for a > given child. > > The two queries below seem to be a good a shortlist as any. > > I'm no query-plan guru, but these seem to be largely identical aside > from one uses "filter IS NULL" and the other uses "top-N heapsort". > > Would there be a reason to prefer one over the other (or perhaps > there's an altogether more efficient way of doing this query ?!?). > My gut-instinct says the sort version ? The ultimate criterion which one is better is the execution time, but you probably need more data to tell with certainty. At a short glance, I'd say that they are pretty much the same. The filter and the top-1-sort will both require a single scan through the result set and one operation per row found. And the recursive queries are pretty similar, right? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: To prefer sorts or filters in postgres, that is the question....
Bob Jones wrote: > My current thinking is that the filter is a bit like an "fgrep" and > the sort actually requires memory allocation and some "real work", and > thus I've settled on the filter for now pending experiments with a > larger quantity of data. That's fine. A top-1-sort is less work than you maybe think: You go through all items and find the biggest one. So there is one comparison operator per row - very similar to what happens when "grepping" for NULL values. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets
Thiemo Kellner, NHC Barhufpflege wrote: > > What do you have your log levels set to? > > Thanks for pointing this out. I put client level to debug1. So, I am > just lucky not to have got flooded with Messages? Sort of. This is a normal operation and should not worry you. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Using the public schema
Charlin Barak wrote: > We will be developing three new applications in PostgreSQL, each having its > own > database instance running on different hosts. We will only have one schema per > Postgres instance. The data is read-write only by one application/schema in > the > DB instance and the data is published to other applications via API calls. > > In such a standalone database configuration, are there any security > implications > or any downsides to creating the application in the public schema? No, that's fine. Just REVOKE CREATE ON SCHEMA public FROM PUBLIC. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Parameter passing in trigger function write in C
a wrote: > I was request to write a trigger that perform the following function: > > 1, The trigger will be set to exam all statement that will result change of > datas > > 2, The trigger will exam three information other than the data updated or > inserted: >user name, password and reasons of change > > 3, It will record down, in some format, the time and content of the change. > > Therefore, I would need some way of passing parameters into the trigger > function. > Can anyone give me some instructions on how to do it or, if it is possible?? The only information you can pass to a trigger function are the old and new values of the table row being changed and the constants you can supply to the trigger function during CREATE TRIGGER. Everything else will have to be drawn from the current state of the database. One thing you can do is to define your own GUC (configuration parameter) and set that before you run the data modifying SQL statement. Then you can read that information in the trigger. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: what's the error meaning? on mast/slavery replication .
林晓东 wrote: > > hi,all >I got this when setup two replication server: > > 致命错误: 在主用服务器和备用服务器之间，数据库系统标识符是不一样的。 > 详细信息: 主用服务器的标识符是6551342231479624101，备用服务器的标识符是6551367700460643417。 > ---fatal erro: in main and backup server ,the database 's system id is not > same... > what's mean of this ,how to deal with ? You need to start the standby with a physical backup of the database, e.g. using pg_basebackup. You cannot restore a database dump taken with pg_dump to start streaming replication, because that is a logical backup (there is no guarantee that the same data are in the same block of the same file). Yours, Laurenz Albe -- +43-670-6056265 Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: https://www.cybertec-postgresql.com
Re: Is it possible to get username information while writing trigger?
a wrote: > What I want is to add a log entry at the last column of each row, which will > record the > history update, insert automatically when relative statement is processed. > > I have read the documentation on triggers, which helps a lot. However, I may > have few > more extra requirement to complete my wishes: > > 1, I would like to get the username of who executed the statement; > > 2, I would like to get the column name that is being updated; > > If it is possible and how should I do it?? You could use the "current_user" function to get the current user. Mind, however, that updates caused by a cascading update from a foreign key constraint will be executed as the owner of the table, so it would be better to use "session_user" to avoid surprises. You cannot get the column name, because PostgreSQL updates a whole row, not an individual column. The best you can do is to check which column values are different in OLD and NEW. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Is it possible to get username information while writingtrigger?
a wrote: > Please allow me to ask few more questions: > > 1, Since I'm writing a C trigger function, is there any method for me to get > some of the >basic information like the follow: > > (1) Total number of rows; > (2) Rows' names; > (3) Value of OLD and NEW; > > 2, Is there any possibility of passing the SQL statement it self into the > trigger? > > 3, Is it possible for me to exam before statement trigger so that I would be > able to loop it >once and copying the update information to the rest of rows. I don't know if there is a reliable way to get the SQL statement from a C trigger. For the other things, perhaps a statement level trigger with transition relations can help. You can then access the transition relations from your C code with the tg_oldtable and tg_newtable tuplestores. (https://www.postgresql.org/docs/current/static/trigger-interface.html) Yours, Laurenz Albe -- +43-670-6056265 Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: https://www.cybertec-postgresql.com
Re: duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index" while performing full vacuum on the database
Raghavendra Rao J S V wrote: > While performing vacuum full, I have received the below highlighted error. > Please guide me how to resolve this issue. > > > /opt/postgres/9.2/bin/psql -p 5433 --username=cmuser cpcm -c "VACUUM FULL > ANALYZE;" > > > ERROR: duplicate key value violates unique constraint > "pg_statistic_relid_att_inh_index" > DETAIL: Key (starelid, staattnum, stainherit)=(18915, 6, f) already exists. > > ' pg_statistic ' is a meta data table. Is it ok if I remove one duplicated > record from ' pg_statistic' table?. That is data corruption. Do you have any idea how you got there? Any crashes, any unsafe settings? Unreliable hardware? Fortunately it is only the pg_statistic table. You can stop the server, start it with pg_ctl start -o -O Then connect as superuser and run TRUNCATE pg_statistic; ANALYZE; That should take care of the problem. It would be a good idea to pg_dumpall the cluster, remove it, recreate it and load the dump so that you are sure to have no data corruption. Yours, Laurenz Albe
Re: Best conception of a table
hmidi slim wrote: > To clarify the purpose of the table 'product_price_period': If I have a > product > and I choose period like [2018-05-01, 2018-05-31] and in the days_checked = > [0,2,3]. > The values of days_checked are the indexes of the week's day. > In this case 0 => sunday, 2 => tuesday, 3 => wednesday. > So the product is not product for every sunday and tuesday and wednesday in > the given period. > The problem with this design is when I make a select to fetch all the product > available for a given period, I have to generate all the dates of a given > period > and then eliminate the dates corresponding to days_checked and after that > return the products. > > E.g: > If I want to fetch all the products in a period of [2018-05-01, 2018-05-08] > > And considering that I have a list of products : > 1) product_id_1 [2018-04-01, 2018-05-05] [0,2] > 2) product_id_2 [2018-05-01, 2018-05-01] [2] > 3) product_id_3 [2018-04-01, 2018-05-17] [] > > The result wil be product_id_3. This should be possible without using a temporary table. First filter out the products whose daterange does not contain your interval, then remove all products where generate_series(DATE '2018-05-01', DATE '2018-05-08') contains one of the forbidden week days. Maybe you should choose a simpler data model, like storing all allowed days for a product in an array (you can use a GIN index to speed up the <@ operator). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: How can I retrieve attribute category with attribute Oid in C function?
a wrote: > Hi I am writing something that would need to identify the attribute category > inside a C function, > now I may need a way of fast access it, but the information is not stored in > FormData_pg_attribute structure. Are you talking about pg_type.typcategory? That is accessible from FormData_pg_type. You'd have to look up atttypid in the TYPEOID cache. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: How can I retrieve attribute category with attribute Oid in Cfunction?
a wrote: > could you provide some more detail information?? > > What function should I use to get FormData_pg_type structure?? > > BTW, I searched another function called "lookup_type_cache", which will > return a pointer of structure of "TypeCacheEntry". > > The structure contains "TypeCacheEntry.typtype" that may also help to identify the type. > > But I'm concerning about possible memory leak and allocation stuff. Is it > safe to just declare a pointer and call this function?? In "logicalrep_write_typ" in src/backend/replication/logical/proto.c you can find an example how to get the pg_type values from the OID. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: sql function with empty row
Philipp Kraus wrote: > I have defined a SQL function > > CREATE OR REPLACE FUNCTION substancetrivialname(text) > RETURNS substance > LANGUAGE 'sql' > COST 100 > VOLATILE > AS $BODY$ > select s.* from substancetrivialname n > join substance s on s.id = n.idsubstance > where lower(btrim(n.name)) = lower(btrim($1)); > $BODY$; > > substance and substancetrivialname have got a 1-to-N relationship (for each > substance can exist multiple trivial names). > If I call the function with a non-existing trivial name it returns a single > row with all fields are set to NULL. > > If I run the join query directly it returns an empty record set on a > non-existing trivial name. > I expected equal behavior on my function, so my question is, how can I fix > this? The difference is that the function has to return exactly one value, while the query it at liberty to return 0, 1 or more rows. Since there is no result, it returns a NULL value. What you are seeing is a valid composite NULL value: SELECT ROW(NULL, NULL) IS NULL; ?column? -- t (1 row) It looks weird, but the SQL standard wants it that way. NULLs and composite types is a topic that can really twist your brain. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Login with LDAP authentication takes 5 seconds
Andreas Schmid wrote: > I configured my PostgreSQL 10 DB on Debian 9.2 with LDAP authentication > (simple bind mode). > While this basically works, it has the strange effect that the first login > with psql > takes around 5 seconds. When I reconnect within 60 seconds, the login > completes immediately. > > The LDAP server is behind a firewall. So for a test, in pg_hba.conf I put the > LDAP servers > IP address instead of its DNS name (for parameter ldapserver). Like that, all > logins > complete immediately. But in general I prefer specifying the DNS name rather > than the IP. > > When I checked on the DB machine with the following commands > host my.ldap.server.org > dig my.ldap.server.org > both always returned the host name and IP address of the LDAP server > immediately. > > Does anyone of you have an explanation for this, or a hint, where I could do > some further > investigation? I would run a network trace with timestamps to see where the time is spent. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Insert UUID GEN 4 Value
tango ward wrote: > I would like to know how to properly use uuid_generate_v4 when inserting data > into PostgreSQL table. > > When I run the command select * from pg_available_extensions; > I can see this uuid-ossp | 1.0 | | > generate universally unique identifiers (UUIDs) > > I am trying to find a basic and easy to understand documentation of it on how > to use > when inserting data because I have a column that is a uuid type. Are you looking for this? ALTER TABLE tab ALTER col SET DEFAULT uuid_generate_v1(); Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Insert UUID GEN 4 Value
tango ward wrote: > I found this: > > CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; > SELECT uuid_generate_v4(); > > My problem is I have a table like this: > CREATE TABLE enrollmentinfo ( > id integer NOT NULL, > created timestamp with time zone NOT NULL, > modified timestamp with time zone NOT NULL, > secure_id uuid NOT NULL, > relationship character varying(50) NOT NULL, > tuition_bill character varying(255) NOT NULL > ); > > Now I need to insert data into this table, I just don't know if I can use > something like this > "INSERT INTO enrollmentinfo (current_timestamp, current_timestamp, > uuid_generate_v4(), '', ''); > I haven't tried this but also not sure if I can call the function inside > INSERT. Why didn't you try it? I see no problem with that. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Query hitting empty tables taking 48 minutes
Robert Creager wrote: > I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK > 1.8.0_131, > jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior. > A query is executing against a couple of tables that have 1 and 0 records in > them. > ds3.job_entry has 0 rows, ds3.blob has 1 row. > If I execute the query manually via command line, it executes fine. There > are no other active queries, no locks. > The system is idle, in our Dev Test group, so I can leave it this way for a > bit of time. > The general software setup is in production and I’ve seen nothing like this > before. > Even a system with 300M ds3.blob entries executes this query fine. > > Jun 7 17:24:21 blackpearl postgres[10670]: [7737-1] > db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG: > duration: 2903612.206 ms execute fetch from S_2037436/C_2037437: SELECT * > FROM ds3.blob WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = > ds3.blob.id AND (job_id = $1)) > > tapesystem=# explain analyze SELECT * FROM ds3.blob WHERE EXISTS (SELECT * > FROM ds3.job_entry WHERE blob_id = ds3.blob.id AND (job_id = > 'b51357cd-e07a-4c87-a50b-999c347a5c71')); > QUERY PLAN > --- > > Nested Loop (cost=0.84..6.89 rows=1 width=77) (actual time=0.044..0.044 > rows=0 loops=1) >-> Index Scan using job_entry_job_id_idx on job_entry (cost=0.42..2.44 > rows=1 width=16) (actual time=0.042..0.042 rows=0 loops=1) > Index Cond: (job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71'::uuid) >-> Index Scan using blob_pkey on blob (cost=0.42..4.44 rows=1 width=77) > (never executed) > Index Cond: (id = job_entry.blob_id) > > Planning time: 0.388 ms > Execution time: 0.118 ms > > > > > pid client_port runtime query_start datname state query usename > 10670 11211 0 years 0 mons 0 days 0 hours 43 mins 28.852273 secs > 2018-06-07 17:24:22.026384 tapesystem active SELECT * FROM > ds3.blob WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = > ds3.blob.id AND (job_id = $1)) Administrator > > > From the system with 300M ds3.blob entries, which works fine (along with > every other system in house): > > QUERY PLAN > Nested Loop (cost=0.57..738052.90 rows=164379 width=75) (actual > time=1.001..1947.029 rows=5 loops=1) > -> Seq Scan on job_entry (cost=0.00..10039.50 rows=164379 width=16) > (actual time=0.871..56.442 rows=5 loops=1) > Filter: (job_id = 'ef27d2fa-2727-424e-8f44-da9e33a5ca05'::uuid) > Rows Removed by Filter: 60001 > -> Index Scan using blob_pkey on blob (cost=0.57..4.42 rows=1 width=75) > (actual time=0.037..0.037 rows=1 loops=5) > Index Cond: (id = job_entry.blob_id) > Planning time: 6.330 ms > Execution time: 1951.564 ms The JDBC query is using a cursor since you are using setFetchSize(1). I can see two reasons why the plan might be different: 1. It is a prepared statement that has been executed often enough for the generic plan to kick in, and the generic plan is bad. 2. PostgreSQL chooses a fast startup plan because a cursor is used, and that plan performs much worse. To see if 1. causes the problem, you could set m_statement.setPrepareThreshold(0); and see if that makes a difference. To test if 2. has an influence, try setting cursor_tuple_fraction to 1.0 and see if it makes a difference. You can capture the execution plan of the bad statement by using auto_explain, that would certainly shed more light on the problem. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Bad performance with cascaded deletes
Haug Bürger wrote: > Delete on zpg_data.session (cost=190.51..4491.20 rows=500 width=46) > ... > Planning time: 0.222 ms > Trigger RI_ConstraintTrigger_a_16481 for constraint > availability_cache_ac_session_id_fkey: time=350.116 calls=500 > > For me this reads like the delete takes 0.2ms and the cascaded delete > takes 350ms. Could you share the complete plan? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Logging
Andrew Bartley wrote:
> Can someone please tell me how to log the values being inserted in this
> example..
>
> 2018-06-12 22:31:49 UTC-5b2049d4.434ac-STATEMENT: insert into
> "api_consumers" ("consumer_id", "create_datetime") values ($1, $2).
>
> I have tried many different logging options and combinations.
That should automatically be logged as a DETAIL message.
log_min_duration_statement = 0
log_min_error_statement = log or better
log_min_messages = log or better
That should do the trick.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: How can I retrieve double or int data type for libpq
a wrote: > I wanna use C library to connect pgsql server and I got 2 questions: This is all well documented in https://www.postgresql.org/docs/current/static/libpq.html > 1, If I can fetch a row's data instead of using PQgetvalue? PGgetvalue *is* the way to fetch the row's data. There is no call to fetch *all* columns at once, but that is not necessary. > 2, PQgetvalue will return all data as text, I would have to convert them into > the > relative data type, is there a way of getting data by its original format? You will get the data in binary form if you specify a resultFormat of 1 with PQexecParams. Note, however, that they will be in the native binary format of the *server*. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Trying to understand odd trigger behavior
Bruno Wolff III wrote:
> I think I know what is happening, but I wanted to see if my understanding
> is correct.
>
> I have a perl after insert trigger for a table with a non-null column element
> and I am getting an occasional error when the trigger executes for
> printing a null value which is $TD->{new}{element}. However, I do the
> insert with an on conflict that converts it into an update. And I have
> a before update trigger that blocks changing the record, but returns null
> if the old and new records are the same.
>
> My theory is that the insert trigger is firing even though the action
> got changed to an update and that because the update trigger cancelled
> the update, there is no new record. So I should just test for $TD->{new}
> being doing before doing stuff.
>
> Does that sound correct?
Absolutely, but it should be easy to run a few tests with only a single row
insert that confirms your theory.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: About "Cost-based Vacuum Delay"
Ilyeop Yi wrote: > I have some questions about "cost-based vacuum delay". > > Q1. How can I know/check if the autovacuum is actually paused periodically > according to autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay? > > I cannot find such an information from log files. These pauses are so short and so frequent that it wouldn't make sense to log them. You could attach "strace" to an autovacuum worker and see it pause from the system calls it performs. > Q2. Is there any way to manually pause a running vacuum process? > > If so, is there also any way to manually resume the paused vacuum process? These pauses are so short (20 ms by default) that you won't catch them. I suspect that you have some problem that makes you ask these questions. What is it? Or are you just curious? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Query hitting empty tables taking 48 minutes
Robert Creager wrote:
> A different query started showing up as the problem, the auto_explain with
> analyze shows an oddity,
> the total query duration is 11k seconds, while the query itself is 3 seconds.
> I captured a ZFS
> snapshot as soon as the problem was noticed.
>
>db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG:
> duration: 10856644.336 ms plan:
>
> Query Text: DELETE FROM ds3.blob WHERE EXISTS (SELECT * FROM
> ds3.s3_object WHERE id = ds3.blob.object_id AND (bucket_id = $1))
>
> Delete on blob (cost=1308.79..1312.82 rows=1 width=12) (actual
> time=3465.919..3465.919 rows=0 loops=1)
>
> [...]
>
> Doing a ZFS rollback and executing the query shows what is happening,
> although not to the extent above.
> If I read this correctly, it’s the constraint checks that are causing the
> query to take so long.
> I don’t see any server configuration that might allow those checks to be
> parallelized. Is that possible?
>
> tapesystem=# PREPARE foo(uuid) AS DELETE FROM ds3.blob WHERE EXISTS (SELECT *
> FROM ds3.s3_object WHERE id = ds3.blob.object_id AND (bucket_id = $1));
> EXPLAIN ANALYZE EXECUTE foo('bc6e6b10-80ad-4329-9fb9-1a66d8c1505e');
> PREPARE
Are we missing an "EXECUTE foo('bc6e6b10-80ad-4329-9fb9-1a66d8c1505e')" here?
>QUERY PLAN
>
>
>
>
> Delete on blob (cost=9555.07..21134.01 rows=23 width=12) (actual
> time=1516.140..1516.140 rows=0 loops=1)
> [...]
> Trigger for constraint blob_tape_blob_id_fkey: time=5389.627 calls=30
> Trigger for constraint multi_part_upload_placeholder_blob_id_fkey:
> time=4566.305 calls=30
> Trigger for constraint multi_part_upload_part_blob_id_fkey: time=3597.896
> calls=30
> Trigger for constraint blob_pool_blob_id_fkey: time=4631.851 calls=30
> Trigger for constraint blob_target_blob_id_fkey: time=4688.731 calls=30
> Trigger for constraint suspect_blob_tape_blob_id_fkey: time=4473.787
> calls=30
> Trigger for constraint suspect_blob_pool_blob_id_fkey: time=4475.286
> calls=30
> Trigger for constraint suspect_blob_target_blob_id_fkey: time=4353.237
> calls=30
> Trigger for constraint blob_s3_target_blob_id_fkey: time=4451.687
> calls=30
> Trigger for constraint blob_azure_target_blob_id_fkey: time=4448.613
> calls=30
> Trigger for constraint suspect_blob_azure_target_blob_id_fkey: time=4353.467
> calls=30
> Execution time: 85175.581 ms
>
> I executed a vacuum analyze, then a vacuum full analyze, neither changed the
> timing.
> Other than removing constraints, is there any way to address this?
I cannot explain the discrepancy between the runtimes of 85 seconds versus
10857 seconds.
But other than that, it sure looks like the foreign keys are missing an index on
the source columns, leading to a sequential scan for each deletion and table.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: About "Cost-based Vacuum Delay"
Ilyeop Yi wrote: > Currently, I am working with a workload that is mostly insert and update, and > its performance suffers from autovacuum. > > I've adjusted parameters such as vacuum_cost_delay and vacuum_cost_limit, but > they have no significant effect. > > So, I would like to find a way to pause a running vacuum during bursty > insert/update period and resume the vacuum after that period. > > Is there such a way? Please keep the list copied. You can do ALTER TABLE mytab SET (autovacuum_enabled = off); Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Analyze plan of foreign data wrapper
Mathieu PUJOL wrote: > I'am writing a foreign data wrapper. To avoid returning data for a column > that is not used, I parse 'targetlist' and 'qual' of the Plan. > I'am able to find Var nodes but I can't figure out how i could now if this > node is related to a column my foreign table. For a Var v, v->varattno contains the attribute number of the column. That is the same as the attnum column in pg_attribute. If v->varattno == 0, it is a whole-row reference, like in SELECT mytab FROM mytab; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Analyze plan of foreign data wrapper
Mathieu PUJOL wrote: > I understand that I should also use varno to check which table is referenced > by varattno. In case of Join, aggregation, etc. Sometimes I get a number or > INNER_VAR or OUTER_VAR. > I am lost on how i could resolve this. > I understand that OUTER_VAR/INNER_VAR are related to joins sub plans. Is > outer related to left plan and inner to right plan ? In this case varattno is > index of target list of subplan ? > When varno is an index how to retrieve table info ? I have no deep understanding of these things. Maybe the following comment from include/nodes/primnodes.h can help: /* * Var - expression node representing a variable (ie, a table column) * * Note: during parsing/planning, varnoold/varoattno are always just copies * of varno/varattno. At the tail end of planning, Var nodes appearing in * upper-level plan nodes are reassigned to point to the outputs of their * subplans; for example, in a join node varno becomes INNER_VAR or OUTER_VAR * and varattno becomes the index of the proper element of that subplan's * target list. Similarly, INDEX_VAR is used to identify Vars that reference * an index column rather than a heap column. (In ForeignScan and CustomScan * plan nodes, INDEX_VAR is abused to signify references to columns of a * custom scan tuple type.) In all these cases, varnoold/varoattno hold the * original values. The code doesn't really need varnoold/varoattno, but they * are very useful for debugging and interpreting completed plans, so we keep * them around. */ #defineINNER_VAR65000 /* reference to inner subplan */ #defineOUTER_VAR65001 /* reference to outer subplan */ #defineINDEX_VAR65002 /* reference to index column */ #define IS_SPECIAL_VARNO(varno) ((varno) >= INNER_VAR) Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: functions with side effect
Torsten Förtsch wrote:
> assuming
>
> SELECT nextval('s'), currval('s');
>
> or
>
> SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
>
> is there any guarantee that the 2 output values are the same?
You can use at EXPLAIN (VERBOSE) output to see if it will work
in this special case, but I wouldn't depend on it in general.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: sql questions
haman...@t-online.de wrote:
> a) I am running some select query
> select ... order by
> Now, I would like to preserver the ordering through further processing by
> adding a sequence number
> Of course I can do:
> create temp sequence mseq;
> select xx.*, nextval('mseq') as ord from (select ... order by ) xx;
> drop sequence mseq;
> Is there a simpler way (avoiding the create/drop parts)
A window function would be the best thing:
SELECT ...,
row_number() OVER (ORDER BY ...)
FROM ...
> b) can a sql function return the count of affected rows of some query?
> create function merge_names(int, int) returns void as
> $_$
> update namelinks set nid = $2 where nid = $1;
> -- want the affected rows of the above query
> delete from names where nid = $1
> -- return result here
> $_$
> language sql;
You cannot do it in an SQL function.
In PL/pgSQL you can use
GET DIAGNOSTICS avariable = ROW_COUNT;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: Restore from dumps
Nicola Contu wrote:
> we recently moved from postgres 9.6.6 to 10.4
>
> We perform a pg_dump in production to restore daily in a preprod env.
> This process used to work perfectly, but now we have a tiny problem.
>
> We first restore data, we perform a vacuum and then we restore matviews.
> Restoring matviews now we have :
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 23090; 0 1912379424
> MATERIALIZED VIEW DATA matview_vrs_request_sla postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: relation
> "all_days" does not exist
> LINE 3: from all_days
> ^
> QUERY:
> select count(*)::numeric
> from all_days
> where (("date" between $2::date and $1::date) or ("date" between $1::date and
> $2::date))
> and dow not in (0,6)
>
> CONTEXT: SQL function "bdays" during inlining
> Command was: REFRESH MATERIALIZED VIEW public.matview_vrs_request_sla;
>
> The relation is there, in fact if I go there when I get in to the office, the
> same command works.
>
> I'm not sure why it does not work here, this seems really strange to me.
I suspect that it has to do with the recent security fixes around the "public"
schema.
Try to ALTER the materialized view so that it refers to "public.all_days"
rather than "all_days".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: Immutable function WAY slower than Stable function?
Ken Tanzer wrote: > On Mon, Aug 6, 2018 at 4:11 PM Tom Lane wrote: > > Ken Tanzer writes: > > > Hi. I was recently troubleshooting a function, and realized it had > > > incorrectly been declared as Immutable, when it should have been declared > > > Stable. When I changed it to Stable, the query I was running ran > > > dramatically faster. Digging into this a little more, this is what I > > > found: > > spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_imm(client_id) > FROM tbl_residence_own; > QUERY PLAN > > --- > Seq Scan on public.tbl_residence_own (cost=0.00..1990.02 rows=6977 width=8) > (actual time=3.771..22665.604 rows=6983 loops=1) >Output: client_id, si_imm(client_id) >Buffers: shared hit=199814 > Planning time: 0.156 ms > Execution time: 22677.333 ms > (5 rows) > > spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_stable(client_id) > FROM tbl_residence_own; > QUERY PLAN > > -- > Seq Scan on public.tbl_residence_own (cost=0.00..3734.27 rows=6977 width=8) > (actual time=3.100..1302.888 rows=6983 loops=1) >Output: client_id, staff_inspector_stable(client_id, target_date()) >Buffers: shared hit=60174 > Planning time: 0.354 ms > Execution time: 1315.746 ms > (5 rows) That seems to say that the _stable function is running much faster. Buth functions don't get inlined. I'd dig into the functions and find out how long the queries in them take. auto_explain is a good helper for that. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Immutable function WAY slower than Stable function?
Ken Tanzer wrote: > > Buth functions don't get inlined. > > OK, I clearly don't understand the output lines. What does it mean then that > the stable > output line shows the underlying (two-argument) function, while the immutable > one shows > the convenience function? > > Output: client_id, si_imm(client_id) > Output: client_id, staff_inspector_stable(client_id, target_date()) I was wrong, obviously "si_imm" gets inlined and replaced with the other function call in the STABLE case. > > I'd dig into the functions and find out how long the queries in > > them take. auto_explain is a good helper for that. > > I'm definitely not understanding why or how auto-explain would help here. > (Also, don't > overlook the fact that both si_stable and si_imm have the exact same > definition > (except for stable v. immutable), and are calling the same function > (staff_inspector_stable)). > > Let me know if that is not helpful. Or if you need something from > auto-explain, > please help me with some more specifics. Thanks! If you set shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = 0 auto_explain.log_analyze = on auto_explain.log_buffers = on auto_explain.log_verbose = on auto_explain.log_nested_statements = on and run both the slow and the fast query, the log will contain the execution plans and time for all SQL statements that were called from the functions. Then you can identify in which nested query the time is spent, which should give us more material to determine the cause. The most likely explanation for the difference is that the same query is running with different execution plans in both cases. Do you notice a difference if you start a new database session and run the queries several times? Is there a difference in execution time from the sixth execution on? If yes, generic plans may be part of the problem. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: How to get connection details from psql -> \e
hubert depesz lubaczewski wrote: > I'm trying to work on some extension to vim when invoked as \e from > psql. > > To make it fully work, I need to know connection details that psql was > using while it invoked \e. > > Is it possible to do in any way, or if not, any chance it could be added > to wishlist for next versions of Pg? Do you mean something like \set? test=> \set DBNAME = 'test' ENCODING = 'UTF8' HOST = '/var/run/postgresql' PORT = '5432' SERVER_VERSION_NAME = '10.5' SERVER_VERSION_NUM = '15' VERSION_NAME = '10.5' VERSION_NUM = '15' ... Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: 'Identifier' columns
David Favro wrote: > A couple of questions about auto-assigned identifier columns, > forgive my ignorance, I'm used to other methods to create IDs... > > 1. If creating a new application [i.e. no "legacy" reasons to do > anything] using PostgreSQL 10, when creating an "auto-assigned > integer ID" column, what are the advantages/disadvantages of using > the 'SERIAL' datatype [or equivalent explicitly created SEQUENCE w/ > nextval() used as default for column] versus the SQL-standard > 'integer GENERATED AS IDENTITY'? All other things being equal, it > would seem a no-brainer to follow the standard. Absolutely. Use GENERATED ALWAYS AS IDENTITY. > 2. When using the SQL-standard 'integer GENERATED AS IDENTITY' > column, after inserting a column, what is the recommended method to > find the ID of the just-inserted row? Is there no SQL-standard way? > The docs seem to imply (without explicitly stating) that a SEQUENCE > is used behind the scenes hence 'currval()' could be used, but I > didn't see in the docs any mention of what the underlying sequence's > name is, or how to specify a name. Perhaps 'lastval()' would work, > but not in all cases and in any event it has a sloppy feel to me. The best way is to use the (non-standard) RETURNING clause: INSERT ... RETURNING id; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Odd Row Estimates in Query Plan (rows=75)
Don Seiler wrote:
> We have a report query that has gone from maybe a few seconds to run to a few
> minutes to run since mid-July.
> Looking at the output of EXPLAIN ANALYZE, the row count estimates are way
> off, even though this table was
> just analyzed a day or so ago. What's more bizarre to me is that the row
> count esimate is *always* 75 for
> every node of the plan, where the actual rows is in the hundreds or
> thousands. This table is one of the
> busiest tables in our production database (many inserts and updates). It is
> autovacuumed and autoanalyzed
> a few times per week, although I'm looking to change it to a nightly manual
> schedule to avoid daytime autovacuums.
>
> Hash Join (cost=1869142.34..1869146.15 rows=75 width=88) (actual
> time=179877.869..179878.011 rows=759 loops=1)
>Hash Cond: (stores.pkey = lt.store_pkey)
>Buffers: shared hit=1654593 read=331897 dirtied=249
>-> Seq Scan on stores (cost=0.00..2.77 rows=77 width=22) (actual
> time=0.007..0.023 rows=78 loops=1)
> Buffers: shared hit=2
>-> Hash (cost=1869141.40..1869141.40 rows=75 width=50) (actual
> time=179877.847..179877.847 rows=759 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 73kB
> Buffers: shared hit=1654591 read=331897 dirtied=249
> -> Subquery Scan on lt (cost=1869138.59..1869141.40 rows=75
> width=50) (actual time=179875.976..179877.697 rows=759 loops=1)
>Buffers: shared hit=1654591 read=331897 dirtied=249
>-> GroupAggregate (cost=1869138.59..1869140.65 rows=75
> width=50) (actual time=179875.976..179877.606 rows=759 loops=1)
> Group Key: lts.store_pkey, lts.owner,
> (date_trunc('minute'::text, lts.date_gifted))
> Filter: (count(*) IS NOT NULL)
> Buffers: shared hit=1654591 read=331897 dirtied=249
> -> Sort (cost=1869138.59..1869138.78 rows=75 width=42)
> (actual time=179875.961..179876.470 rows=6731 loops=1)
>Sort Key: lts.store_pkey, lts.entry_source_owner,
> (date_trunc('minute'::text, lts.date_gifted))
>Sort Method: quicksort Memory: 757kB
>Buffers: shared hit=1654591 read=331897 dirtied=249
>-> Index Scan using gifts_date_added on gifts lts
> (cost=0.56..1869136.25 rows=75 width=42) (actual time=190.657..179870.165
> rows=6731 loops=1)
> Index Cond: ((date_added > '2018-07-14
> 11:13:05'::timestamp without time zone) AND (date_added < '2018-08-13
> 14:14:21'::timestamp without time zone))
> Filter: ((date_gifted >= '2018-08-13
> 11:13:05'::timestamp without time zone) AND (date_gifted < '2018-08-13
> 14:14:21'::timestamp without time zone))
> Rows Removed by Filter: 938197
> Buffers: shared hit=1654591 read=331897
> dirtied=249
> Planning time: 0.426 ms
> Execution time: 179893.894 ms
>
> I don't have a version of this query from prior to this summer, but getting
> explain plan for older data from
> older sandboxes show a similar plan.
>
> Sidenote: I am suggesting that an index be added on the date_gifted field as
> that is far more selective and avoids
> throwing rows away. However I'm very interested in why every node dealing
> with the gifts table thinks rows=75
> when the actual is much, much higher. And 75 seems like too round of a number
> to be random?
Yes, I would say that adding an index on "date_gifted" would help. You may end
up with two bitmap index scans that get combined.
Make sure "work_mem" is big enough to avoid lossy bitmaps (indicated in the
plan).
About the misestimate:
You could try running ANALYZE with an increased "default_statistics_target" and
see
if that changes the estimate.
If yes, then maybe you should increase statistics for that table or (seing that
you are
querying current values) you should collect statistics more often.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: including header files in a C extension
TalGloz wrote: > If I have an external library that I install using make install into the > /usr/local/include/libraryname path by default and I want to include it in a > C extension, the Makefile for the .so file includes the headers in > /usr/local/include/libraryname path. > > Can I use #include when writing C/CPP code for my > PostgreSQL C extension or do I have to install the external library in a > different path than /usr/local/include/libraryname. I think that the > external library headers are not getting included. > > I do manage to build the .so file without error. If you can build the extension, what is your problem? Usually you use the LDFLAGS to tell "make" where to find your headers. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Linker errors while creating a PostgreSQL C extension function.
TalGloz wrote: > Do you or anyone know why is it trying to link with -L/usr/lib64 path and > not -L/usr/local/lib as provided? > > After recompiling the libseal.a with the -fPIC flag and copying it manually > from /usr/local/lib/ to /usr/lib64/ I get those errors: > > g++ -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o > -L/usr/pgsql-10/lib -L/usr/lib64 -Wl,--as-needed > -Wl,-rpath,'/usr/pgsql-10/lib',--enable-new-dtags -L/usr/local/lib -lseal > -pthread > seal_diff_cpp.o: In function `seal_diff_cpp': > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:106: > undefined reference to `pg_detoast_datum_packed' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:107: > undefined reference to `pg_detoast_datum_packed' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:108: > undefined reference to `pg_detoast_datum_packed' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:112: > undefined reference to `text_to_cstring' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:113: > undefined reference to `text_to_cstring' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:114: > undefined reference to `text_to_cstring' > /etc/opt/pgsql_c_functions/CPP/seal_extension/seal_diff_cpp.cpp:130: > undefined reference to `cstring_to_text_with_len' > > Did I miss something in one of my files? I think the --no-undefined is wrong. Any reference to PostgreSQL functions is undefined at build time and gets resolved when the shared library is loaded into PostgreSQL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Hungarian collation in English Windows server
Durumdara wrote: > CREATE DATABASE yyy > WITH > OWNER = xxx > ENCODING = 'UTF8' > LC_COLLATE = 'Hungarian_Hungary.1250' < > LC_CTYPE = 'Hungarian_Hungary.1250' < > > But we got error that collation doesn't exists in English Windows server. You have to install the collation on the Windows maching, not in PostgreSQL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Command line session and running queries
imp3 wrote: > I've run an update query inside command line tool (psql) then I've exit the > tool (closing the window). > > When I re-logged in, I've noticed in pg_stat_activity that my query was still > running. > I used the command pg_cancel_backend with the right PID, but it returned "f" > few seconds later. > I re-checked pg_stat_activity and I was not able to see my query; there was > only an autovacuum running on the table impacted by my query. > Since my update query usually takes about one hour, I was wandering what > happened. > > Is postgres regularly checking command line session? If session has expired, > would postgres stop the query and rollback it? > > I'm using both client and server v10.1. The query probably endet by itself between the point in time when you saw it in pg_stat_activity and when you tried to cancel it. The PostgreSQL backend will only notice that the client has died when it is done processing the query and tries to send an answer to the client. If that is a regular problem for you, you could set "statement_timeout" to put an upper limit on "runaway" queries. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Is my text_pattern_ops index working for a LIKE prefix search with column reference?
Seamus Abshere wrote: > I created an index with text_pattern_ops because I want fast prefix search > [1] [2]. > > ...but I don't think it's working when I use a column reference || '%'... An index can only be used for expressions like and you have a column refrence on the right side. Perhaps you can rephrase your query in such a fashion. Yours, Laurenz Albe
Re: Why my query not doing index only scan
Arup Rakshit wrote: > I am learning at this point how index works in DBMS. So I am looking for a > very broad explanation to clear my basics. > > I have an index defined "inspector_tool_idx4_1" UNIQUE, btree (company_id, > item_code, deleted_at). > Now I am using the *company_id* column in the where clause, and the selecting > just the *item_code* field for all matching rows. > I expected here the planner will do a index only scans. But it is doing > bitmap index scan. Any idea what it is not doing what I expected it to do. > > > inspection_development=# explain analyze select item_code from > inspector_tool_components where company_id = > '7881ff2e-0557-4734-9da8-2d33072ff1ef'; > QUERY PLAN > - > Bitmap Heap Scan on inspector_tool_components (cost=45.92..1360.48 > rows=1226 width=8) (actual time=0.382..1.202 rows=1232 loops=1) >Recheck Cond: (company_id = '7881ff2e-0557-4734-9da8-2d33072ff1ef'::uuid) >Heap Blocks: exact=81 >-> Bitmap Index Scan on inspector_tool_idx4_1 (cost=0.00..45.61 > rows=1226 width=0) (actual time=0.347..0.347 rows=1232 loops=1) > Index Cond: (company_id = > '7881ff2e-0557-4734-9da8-2d33072ff1ef'::uuid) > Planning time: 0.207 ms > Execution time: 1.358 ms > (7 rows) Try to "VACUUM (ANALYZE) inspector_tool_components", that will set the visibility mape and get the statistics right, maybe than you get an index only scan. How many rows does the table contain? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Advice on machine specs for growth
Rory Campbell-Lange wrote: > We are looking to upgrade our current database server infrastructure so > that it is suitable for the next 3 years or so. > > We envisage needing about 800GB of primary database storage in the next > three years, with 1000 databases in the cluster. 1000 is a lot, but should still be ok. > We are imagining either splitting the cluster into two and (to have four > main servers) or increasing the disk capacity and RAM in each server. > The second seems preferable from a day-to-day management basis, but it > wouldn't be too difficult to deploy our software upgrades across two > machines rather than one. If you can scale horizontally by splitting the load across several independent database servers, then do so by all means. This may be more administration work initially, but scaling will come easy when you need it. It is much more difficult to scale a single monolithic database server. > Resources on the main machines seem to be perfectly adequate at present > but it is difficult to know at what stage queries might start spilling > to disk. We presently occasionally hit 45% CPU utilisation, load average > peaking at 4.0 and we occasionally go into swap in a minor way (although > we can't determine the reason for going into swap). There is close to no > iowait in normal operation. Disable memory overcommit and set swappiness to 0 on database servers. > It also seems a bit incongruous writing about physical machines these > days, but I can't find pricing on a UK data protection compatible cloud > provider that beats physical price amortised over three years (including > rack costs). The ability to more easily "make" machines to help with > upgrades is attractive, though. I think physical machines are cool. The resulting system becomes simpler with fewer dependencies, and it is much easier to debug performance problems. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Advice on machine specs for growth
Steven Winfield wrote: > Just to be clear, are you suggesting (on linux) setting overcommit_memory = 2 > but leaving overcommit_ratio alone, which defaults to 50%? > I’ve tried setting them to 2 and 0% respectively in the past and it didn’t > end well… Yes, vm.overcommit_memory = 2 As for vm.overcommit ratio, set it to 100 * (RAM - swap) / RAM That is because Linux will commit memory up to swap + overcommit_ratio * RAM / 100, and you don't want to commit more than the available RAM. > Also I’ve read, and now use, swappiness = 1 which is supposed to disable > swapping > entirely except when not swapping would cause OOM. > Any thoughts on that? My thought is vm.swappiness = 0 If you don't overcommit memory, you should never have to swap. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Mysteriously varying index scan costs
Last week I encountered the following at a customer site on PostgreSQL 9.6,
and I cannot explain it.
The first run gave me this:
Index Scan using device_outbound_messages_status on device_outbound_messages
(cost=0.43..20.46 rows=97 width=128) (actual time=34.021..35.545 rows=133
loops=1)
Index Cond: ((status)::text = ANY
('{WAITING_FOR_TX,WAITING_FOR_IMMEDIATE_TX}'::text[]))
Buffers: shared hit=74917 dirtied=707
Subsequent runs of the same query gave me:
Index Scan using device_outbound_messages_status on device_outbound_messages
(cost=0.43..20.46 rows=97 width=128) (actual time=2.129..3.907 rows=133 loops=1)
Index Cond: ((status)::text = ANY
('{WAITING_FOR_TX,WAITING_FOR_IMMEDIATE_TX}'::text[]))
Buffers: shared hit=1185
There were no concurrent changes to the data (test environment).
This was part of a bigger statement.
I understand that some pages can be dirtied because table fetches that
cause hint bits to be set.
But how can it be that the first run has to touch 74917 blocks,
while whe second run only needs to touch 1185?
Thanks for any ideas!
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: Mysteriously varying index scan costs
Pavan Deolasee wrote: > On Mon, Sep 24, 2018 at 2:20 PM, Laurenz Albe > wrote: > > > > But how can it be that the first run has to touch 74917 blocks, > > while whe second run only needs to touch 1185? > > > > The first index scan may have killed lots of index tuples. So the first index scan visited lots of table tuples, found them dead, and set the LP_DEAD flag on the corresponding index items so that subsequent index scans could ignore them, right? Thanks, that must be the correct explanation. It correlates nicely with the dirtied pages, too. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Help to understand Actual Rows vs Plan Rows from the query planner output
Arup Rakshit wrote: > Thanks for the link. I read the documentation you linked, and part of it > I understood and rest went above my head. Probably I need to read it multiple > times to understand what is going on. I am learning how indexing works in > DBMS. > Mostly I understood Btree so far. I am an application developer. > Being an application developer I think I need to know which column should be > indexed and what kind of index to apply in which case. That is true; a truth that many developers unfortunately ignore. > Most of the time, when > I see slow query, I saw people ask to run the explain to see the plan. > And explain statement shows lot of data about the query. So my questions is > that: > Which part I should read from the plan output to figure out reason of slowness > or what need to be used to improve it. What are basic things I should know > about it. > I think, I don’t need to internal math for this, am I right? The query in > this post is not a slow query, it is something I ran to see how index merge > happens. > I am asking generally. Can you give me some directions on this, so that I can > build up some bases on this subject. This is a broad topic, and I can only give you some hints. In order to understand EXPLAIN output and to improve your query, you need to know some of how the database is implemented. You have to understand index scans, index only scans and bitmap index scans. You have to understand nested loop, hash and merge joins. You have to understand table statistics, dead tuples and table bloat. The first things to look for in EXPLAIN (ANALYZE, BUFFERS) output is in which nodes the time is spent, and where the estimated number of rows diverges significantly from the actual number of rows. The former are the spots where there is room for improvement, and the latter is often the root cause of a bad plan choice. Also, watch out for the nodes that touch a lot of blocks. They can cause intermittent slow-down if the blocks are not in cache. > Also what are the best resources to learn GIST, GIN indexes — something which > teaches it from the ground up? The documentation, and for what goes beyond that, the source. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Out of Memory
greigwise wrote: > All right.. one more thing here. Any suggestions for how to set overcommit > on a postgres db server with 16 GB of RAM and no swap? I think I want > vm.overcommit_memory = 2, as I understand that prevents the OOM killer from > zapping me. Is 100% the right way to go for overcommit_ratio? Is there a > drawback to this? vm.overcommit_memory = 2 vm_overcommit_ratio = 100 Linux commits (swap * overcommit_ratio * RAM / 100), so without any swap the correct value would be 100. I don't know how safe it is to got entirely without swap. I would add a little bit just to be sure. Yours, Laurenz Albe
Re: [GENERAL] Postgre compatible version with RHEL 7.5
Deepti Sharma S wrote: > Can you please confirm if PostgreSQL version 9.4.x is compatible with RHEL7.5? Yes; there are even PGDG binaries on https://www.postgresql.org/download/linux/redhat/: yum install https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-7-x86_64/pgdg-redhat94-9.4-3.noarch.rpm yum install postgresql94-server Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Out of Memory
Christoph Moench-Tegeder wrote: > ## Laurenz Albe (laurenz.a...@cybertec.at): > > > vm.overcommit_memory = 2 > > vm_overcommit_ratio = 100 > > > > Linux commits (swap * overcommit_ratio * RAM / 100), > > ^ > That should be a "+". Yes; shame on me for careless typing, and thank you for the correction. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Out of Memory
Rob Sargent wrote: > > Christoph Moench-Tegeder wrote: > > > ## Laurenz Albe (laurenz.a...@cybertec.at): > > > > > > > vm.overcommit_memory = 2 > > > > vm_overcommit_ratio = 100 > > > > > > > > Linux commits (swap * overcommit_ratio * RAM / 100), > > > > > > ^ > > > That should be a "+". > > > > Yes; shame on me for careless typing, and thank you for the > > correction. > > Are there any parentheses needed in that formula? No. It is swap space plus a certain percentage of RAM. I don't know how the Linux developers came up with that weird formula. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Replication Issues
bhargav kamineni wrote: > Hi Team, > > I have configured replication using slot ,But it failed by throwing the > ERROR pg_basebackup: could not get transaction log end position from server: > ERROR: requested WAL segment 00012C9D0085 has already been > removed , > which is unexpected because i have created the slot on master first > and then issued the base backup command from slave's end > the command is > usr/lib/postgresql/9.5/bin/pg_basebackup -U user --max-rate='150 M' > --progress --verbose --write-recovery-conf --status-interval='10 s' -D > data_dir -h host_ip -p 5433 > These are the settings on my master > archive_mode=on > archive_command='/bin/true' > wal_keep_segments=512 > max_wal_senders=4 > Series of steps i have followed : > 1) Enabled password less authentication between master and slave > 2)created slot on master (assuming it will store wal's regardless of other > settings) > 3)started basebackup from slave's end > 4)Issued checkpoint at master's end > > Here my concern is , slave should recover WAL from replication slot but why i > got the above ERROR , Why slot removed the requested wal file , Could you > please > let me know the reason why it happened or did i miss something ? I guess your base backup took long enough for the required WAL segments to be removed by the time it was done. To prevent that, create a replication slot *before* you perform pg_basebackup and use the options "-S -X stream" of pg_basebackup. You then use the same slot in "recovery.conf". That way you cannot lose any WAL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: how to identify the timeline of specified recovery_target_time when do multiple PITR
magodo wrote: > I read the document about recovery configuration, it says: [...] > Therefore, suppose I am going to recover to a point of some child > timeline, to identify the point, I have to specify either > recovery_target_name or recovery_target_time, and also specify the > recovery_target_timeline. > > It is more like a tuple like (recovery_target_time, > recovery_target_timeline), that specify a real point among all history > branches. Am I understand this correctly? I think you understood the concept well enough. > If yes, what I want to ask is that, though the timeline is increasing > between different recovery, but each timestamp corresponds to a > timeline ID, one by one. So if I get a recovery_target_time, why should > I still specify the recovery_target_timeline? > > Suppose following illustration: > > A B > BASE-+-+--o1 (recover to A) 1 > | | C > +.|...+---o2 (regret, recover to B) 2 >| | >+...|..--o3 (regret again, recover to C) 3 >| >+ 4 Consider this ^ point in time. | Suppose you specify this point in time as recovery_target_time. Then it is not clear which of the timelines you want to follow. The point of time exists in timeline 1, 2 and 3. In other words, should recovery use the WAL from 00010ABC0012, 00020ABC0012 or 00030ABC0012? By default, recovery will stay on the timeline where it started. If you want to go to timeline 2 or 3, you have to specify recovery_target_timeline. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_sleep() inside plpgsql block - pro & cons
pinker wrote: > Francisco Olarte wrote > > I do some similar things, but I sleep outside of the > > database, is there a reason this can not be done? > > > > Francisco Olarte. > > Yes, I do try to convince them to do it outside the db, that's the reason > I'm looking for some support here :) I'm not sure those 2 reasons are enough > to convince them, wanted to be prepared... The main reason why longer sleeping in the database is harmful is that it causes long database transactions. Remember that a function always runs inside a database transaction. Long transactions have two very bad consequences: - They hold locks for a long time, blocking concurrent transactions and increasing the likelihood of deadlocks (the word "deadlock" often works wonders when convincing people). - They keep autovacuum from freeing dead tuples, which can lead to bloated tables and indexes if you have many concurrent data modifications. This wastes storage space and slows down sequential scans. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: We are facing "PANIC: could not read from control file:Success error while starting the database.
Raghavendra Rao J S V wrote: > archive_mode is turned on unfortunately in my Postgres 9.2 database. > > Due to that disk space is full 100%. We have removed few old xlog files. Now > space is available.But still we are facing below problem when we try to start > the database. > > PANIC: could not read from control file:Success > > Please help me to resolve the above error. Time to restore from backup, and then upgrade to a more recent PostgreSQL version. Yours, Laurenz Albe
Re: how to identify the timeline of specified recovery_target_time when do multiple PITR
magodo wrote: > > By default, recovery will stay on the timeline where it started. > > If you want to go to timeline 2 or 3, you have to specify > > recovery_target_timeline. > > > For me, the specified point corresponds to timeline 1, because at that > time, the timeline is 1 indeed (when there is no timeline 2 branched > out yet). > > So in other word, my expectation is like I want to get the first > timeline which covers the specified time. > > As you mentioned, there is no active timeline concept in postgres, then > what is the best practice to meet my expectation? Do I have to somehow > record the timestamp when archived wal's timeline has been changed, > then compare the specified recovery target time with those records to > find the first timeline which covers that time? As I wrote, that is the default behavior; PostgreSQL will stay on the timeline that was active when recovery was started. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: COPY threads
Rob Sargent wrote: > Can anyone here tell me whether or not the CopyManager facility in JDBC > via org.postgresql:postgresql:42.1.4 is internally multithreaded? > Running on CentOS 7 (all participants), java8, postgres 10.5 It isn't, and there would not be much reason for it to be, as COPY in PostgreSQL cannot be parallelized. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: how to identify the timeline of specified recovery_target_time when do multiple PITR
magodo wrote: > OK... Just take another example: > > A B > BASE-+-+--o1 (recover to A) 1 > | | C > +.|...+---o2 (regret, recover to B) 2 >| | >+...|..--o3 (regret again, recover to C) 3 >| >+ 4 > > > Suppose I'm at "o3" and want to recover to point "C". Because I want to > recover to the first timeline which covers this time point, it means I > wish to recover to timeline 2. Ah, I finally understand your question. You assume tht timeline 1 and 2 have ended (that's how you drew it), and that consequently timeline 3 is the "earliest existing" timeline, so why doesn't PostgreSQL choose that one automatically? Even though you drew it that way, timeline 1 and 2 have not ended, in a way. There may be more on them. How should PostgreSQL know what is the last WAL entry on a certain timeline? For that, it would have to restore and examine *all* WAL segments on that timeline until that fails. But that is unreasonable because of the potential amount of time and work involved. Rather, PostgreSQL has to decide at the point where timeline 2 branches off which one it should follow. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Please let me know which configuration setting we need to modify to speedup the pg_dump backup.
Please bottom-post, otherwise the thread becomes hard to read. On Thu, 2018-10-11 at 08:44 +0530, Raghavendra Rao J S V wrote: > We are using postgresql 9.2. It doesn't contain the option. > > Please guide me any other approaches to improve the performance of pg_dump. > > On Thu 11 Oct, 2018, 8:05 AM Pavan Teja, wrote: > > You can use -j jobs option to speed up the process. > > > > Hope it works. > > > > On Thu, Oct 11, 2018, 8:02 AM Raghavendra Rao J S V > > wrote: > > > pg_dump is taking more time. Please let me know which configuration > > > setting we > > > need to modify to speedup the pg_dump backup.We are using 9.2 version on > > > Centos Box. There you have your first configuration change to speed up pg_dump: upgrade from 9.2 to a release that is still supported. And please don't tell me that you cannot / may not. If your data are important for you, and you really want that speed-up, you can. Anyway, to make pg_dump faster with the -j option, you need to use the "directory format" (-F d). Other options to speed up pg_dump are limited: you can get faster storage so that sequential scans are faster or more memory so that more of the data resides in the filesystem cache. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: how to identify the timeline of specified recovery_target_time when do multiple PITR
magodo wrote: > On Thu, 2018-10-11 at 06:35 +0200, Laurenz Albe wrote: > > magodo wrote: > > > OK... Just take another example: > > > > > > A B > > > BASE-+-+--o1 (recover to > > > A) 1 > > > | | C > > > +.|...+---o2 (regret, recover to > > > B) 2 > > >| | > > >+...|..--o3 (regret again, recover to > > > C) 3 > > >| > > >+-- > > > -- 4 > > > > > > > > > Suppose I'm at "o3" and want to recover to point "C". Because I want to > > > recover to the first timeline which covers this time point, it means I > > > wish to recover to timeline 2. > > > > Ah, I finally understand your question. > > > > You assume tht timeline 1 and 2 have ended (that's how you drew it), > > and that consequently timeline 3 is the "earliest existing" timeline, > > so why doesn't PostgreSQL choose that one automatically? > > > > Even though you drew it that way, timeline 1 and 2 have not ended, in > > a way. There may be more on them. How should PostgreSQL know what is > > the last WAL entry on a certain timeline? For that, it would have to > > restore and examine *all* WAL segments on that timeline until that fails. > > > > But that is unreasonable because of the potential amount of time > > and work involved. Rather, PostgreSQL has to decide at the point where > > timeline 2 branches off which one it should follow. > > Thank God I finnally conveied my meaning :) > > For one cluster, if , for example, it recovered to "A" at "o1", then > the switched WAL(in case of stop-then-recover) or .partial > corresponding WAL(in case of promote) is the last WAL of the timeline1, > and it makes no sense to consider timeline1 will continue grow after > "o1", because it has ended. > > You meantioned: "There may be more on them.". > > Would you please give me an example? I must have missed something > out... The timeline is determined before any WAL segments are restored, because the timeline history (as persisted in the *.history files) determines which WAL segments will be restored. You seem to assume that the point A at which recovery ended is the end of the WAL stream of timeline 1, but you cannot know that: - it could have been a point-in-time-recovery - it could be that during the first recovery attempt some WAL archives were missing, which caused a premature end of recovery, but they are there at the second attempt. Indeed, your sentence > if , for example, it recovered to "A" at "o1", then > the switched WAL(in case of stop-then-recover) or .partial > corresponding WAL(in case of promote) is the last WAL of the timeline1 seems to contradict your drawing, which has B after A on timeline 1. Example: Assume that timeline 1 reaches to 000100150030. We recover to point A, which is in the middle of 000100150020, and there branch to timeline 2. After some time, we decide to recover again, starting from a checkpoint in 000100150010. We want to recover to 2018-10-11 12:00:00. How can you know how many WAL segments there are on timeline 1, and if there is one that extends past 2018-10-11 12:00:00 or not? The only way would be to restore and read them all *before* you even decide which timeline you want to choose for recovery. That would be forbiddingly expensive, wouldn't it? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: how to identify the timeline of specified recovery_target_time when do multiple PITR
magodo wrote: > > How can you know how many WAL segments there are on timeline 1, and if > > there is one that extends past 2018-10-11 12:00:00 or not? > > This is the exact problem I want to figure out. My approach is as you said, > I will parse each archived WAL segment via `pg_xlogdump -r Transaction`, > and try to find the first least earliest WAL against the specified time. > This is a linear search, which has complexity of O(n). That means that the time spent grows linearly, but it is still a lot of time if there are a lot of WAL archives. > So if you want to recover to that point of time, how do you choose the > timeline? PostgreSQL doesn't provide auch a functionality, and I can think of three reasons: 1) the high cost as mentioned above 2) during archive recovery, it does not happen very often that you have to choose between timelines at all. It is more typical for streaming replication. 3) nobody saw a use case for such a functionality If you really need this functionality, you'll have to write it yourself. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments = 128" postgresql.conf file.
Raghavendra Rao J S V wrote: > Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments = > 128" > postgresql.conf file. If yes,what is the imapct? Yes. - You will have fewer checkpoints requested by data modification activity. - Crash recovery might take longer. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Regarding varchar max length in postgres
Durgamahesh Manne wrote: > was there any specific reason that you have given max length for varchar is > limited to 10485760 value? > > why you have not given max length for varchar is unlimited like text datatype > ? > > character varying(n), varchar(n)variable-length with limit > character(n), char(n)fixed-length, blank padded > textvariable unlimited length The data type "text" has the same size limit of 1GB. "character varying" (without type modifier) and "text" are pretty much identical. Since data of these types are loaded into memory when you read them from or write them to the database, you usually start having problems long before you reach that limit. If you want to store huge text files, either store them outside the database or use Large Objects, which can be read and written in chunks. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Problem creating a database
Joshua White wrote: > I'm hoping someone can point me in the right direction. I've got a PostgreSQL > 10 server > instance on CentOS 6, which I set up and manage. I have full admin rights on > this machine, > so I can access logs, etc. > > Recently I attempted to create a new database in this cluster. The command > succeeds, > but when I try to connect to the new database, I get a "could not open file" > error: > > psql: FATAL: could not open file "base/618720/2610": No such file or > directory > > It has been some time since I set up the database, so I don't know how long > ago this > became an issue. I can't seem to find any other instances of this problem > online either. > The logs are not helpful - even on the highest debug setting, I only see the > "connection authorized" then the fatal "could not open file" error. > > The data directory is on a separate disk array to the OS. Recently checked it > and > there are no disk errors. > > Any thoughts or ideas would be much appreciated. Looks like the file backing the "pg_index" table is gone. Can you check if the file exists in the data directory or not? It's hard to determine what happened, but something has been eating your data. As it is, your best option would be to drop the database and recreate it from a backup. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Problem creating a database
Please don't top post. Joshua White wrote: > On Mon, 15 Oct 2018 at 21:13, Laurenz Albe wrote: > > Joshua White wrote: > > > I'm hoping someone can point me in the right direction. I've got a > > > PostgreSQL 10 server > > > instance on CentOS 6, which I set up and manage. I have full admin rights > > > on this machine, > > > so I can access logs, etc. > > > > > > Recently I attempted to create a new database in this cluster. The > > > command succeeds, > > > but when I try to connect to the new database, I get a "could not open > > > file" error: > > > > > > psql: FATAL: could not open file "base/618720/2610": No such file or > > > directory > > > > > > It has been some time since I set up the database, so I don't know how > > > long ago this > > > became an issue. I can't seem to find any other instances of this problem > > > online either. > > > The logs are not helpful - even on the highest debug setting, I only see > > > the > > > "connection authorized" then the fatal "could not open file" error. > > > > > > The data directory is on a separate disk array to the OS. Recently > > > checked it and > > > there are no disk errors. > > > > > > Any thoughts or ideas would be much appreciated. > > > > Looks like the file backing the "pg_index" table is gone. > > > > Can you check if the file exists in the data directory or not? > > Thanks for the tip. I've checked and the on-disk file behind "pg_index" still > exists. > > I have existing databases in this cluster that I'd prefer not to drop and > recreate if possible. > > I've tried dropping and recreating the new database I want to use, but each > time get the same type of error. "pg_index" initially uses file 2610. That may of yourse change if you rewrite the table. Try the following as OS user "postgres": oid2name -d -f 2610 Then you can see which table is associated to that file. Anyway, your database seems to be quite wrecked, and you'd probably need an expert to save what can be saved. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: New tablespace: just an advice
Moreno Andreo wrote: > Now, 2 questions. > 1. Is it all or do I need to adjust something else about permissions, > indexes, vacuuming, etc...? > ALTERing the database namespace means copying its physical files to new > directory, but is it applied to all objects (indexes, triggers, etc)? All objects that are located in the default tablespace of the database will be moved. > 2. What will happen to who tries to access the database while it's being > moved from one tablespace to another? They will be "hanging" with a lock until the transaction is done. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Swap on postgres master server
Nicola Contu wrote: > we are running Postgres 10.5 with master slave replication. > > This server is on Centos 7 and the strange thing is that we see a lot of swap > usage : > > [root@usnyh-cmd1 ~]# free -m > totalusedfree shared buff/cache > available > Mem: 25765275555559 12804 244536 > 236036 > Swap: 1638373269057 > > 7GB used. > > But can't see it from any of the commands like top etc. > I am sure it is postgres because it is the only service running on that > machine. > > Is there anything we can do? > On the sync slave, the usage is just 400MB. I recommend that you set "vm.swappiness = 0" using "sysctl" to discourage the operating system from swapping as much as possible. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Enabling autovacuum per table
Rijo Roy wrote: > Is there a possibility to disable auto analyze if we set > autovacuum_analyze_scale_factor > to 100% and autovacuum_analyze_threshold = 0? Why would you want that? You are trying very hard to hurt yourself by disabling autovacuum; maybe there is a better way to solve your problem. Can you tell us more about the actual problem? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Problem creating a database
Joshua White wrote: > > > > > > psql: FATAL: could not open file "base/618720/2610": No such file > > > > > > or directory > > > > > > "pg_index" initially uses file 2610. > > > > > > Anyway, your database seems to be quite wrecked, and you'd probably need > > > an expert to save what can be saved. > > > So you're right about it being seriously corrupted somehow. All my custom > databases seem to work fine. > I have data checksums and amcheck enabled and haven't found any issues using > the query below. > > SELECT bt_index_check(index => c.oid), c.relname, c.relpages > FROM pg_index i > JOIN pg_opclass op ON i.indclass[0] = op.oid > JOIN pg_am am ON op.opcmethod = am.oid > JOIN pg_class c ON i.indexrelid = c.oid > JOIN pg_namespace n ON c.relnamespace = n.oid > WHERE am.amname = 'btree' > -- Don't check temp tables, which may be from another session: > AND c.relpersistence != 't' > AND i.indisready AND i.indisvalid > ORDER BY c.relpages DESC; > > However, when I went to connect to template1, it failed: > > # \connect template1 > FATAL: could not open file "base/1/2610": No such file or directory > Previous connection kept > > If template1 is corrupt, that would explain why I am unable to create new > databases. Right. I think that dumping what you can and importing it in a new cluster is the way to go. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: postgres server process crashes when using odbc_fdw
Ravi Krishna wrote: > On a related note is fdw for Oracle and SQLServer supported by the community ? The PostgreSQL Global Development Group, specifically these mailing lists, will provide support for "core PostgreSQL", that is the source code you can download from www.postgresql.org and binaries built from that source. The support here does not provide any guarantees, but it also has no well-defined limits. So if you ask well, people will also try to help you with versions that are "out of support" or third-party products like the foreign data wrappers you mention above. Likewise, "the community" has no well-defined limits: in a way, people who provide third-party open source extensions also are a part of the community, or at least the communities overlap. That said, it is a good idea to contact the maintainers of those third-party extensions first: usually, they know more about their product than the general public, and it also keeps mails off the main list that are of little interest to most people. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_dump and search_path
On Mon, 2019-07-08 at 23:54 -0700, Igal @ Lucee.org wrote:
> > I have a custom search_path:
> >
> > # show search_path;
> >search_path
> > --
> > "staging, transient, pg_catalog"
> > (1 row)
> >
> > I ran `pg_dump --schema-only` and the only reference in the output to
> > search_path is:
> >
> > SELECT pg_catalog.set_config('search_path', '', false);
> >
> > Then one of my functions which does not reference the full name of a
> > table with its schema fails with "relation [rel-name] does not exist".
> >
> > Is that a bug? I have seen some old posts about this issue but am not
> > sure if there is a ticket or why it still is an issue.
> >
> Looks like this might be by design. I will follow the links at
> https://www.postgresql.org/message-id/flat/MWHPR14MB160079A6D9DC64A2F60E9004C0D00%40MWHPR14MB1600.namprd14.prod.outlook.com
>
> and ask more questions if I have them.
>
> I might need to add the schema name to the table in my function.
Right.
Allowing object lookup along the search_path during pg_restore opens
doors to abuse, because it can make a superuser inadvertedly execute
code crafted by an attacker.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Re: Issue: Creating Symlink for data directory of postgresql in CentOS7
On Fri, 2019-07-12 at 10:11 +, Chatterjee, Shibayan wrote: > I’m trying to create a symlink for ‘data’ directory present in > ‘/var/lib/pgsql/’, pointing to different location. > After providing exact access permissions and ownership to postgres, I’m still > unable to restart postgres after modifications. > > I’ve also modified ‘/var/lib/pgsql/postgresql.conf’ and > ‘/usr/lib/systemd/system/postgresql.service’ > to point the $PGDATA location to the actual destination as respectively: > > data_directory = '/data/postgresql/data' > Environment=PGDATA=/data/postgresql/data [...] > >>> /var/log/messages > > Jul 12 10:00:51 systemd: Starting PostgreSQL database server... > Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing > or empty. > Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to > initialize the database cluster. > Jul 12 10:00:51 postgresql-check-db-dir: See > /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information. > Jul 12 10:00:51 systemd: postgresql.service: control process exited, > code=exited status=1 > Jul 12 10:00:51 systemd: Failed to start PostgreSQL database server. > Jul 12 10:00:51 systemd: Unit postgresql.service entered failed state. > Jul 12 10:00:51 systemd: postgresql.service failed. So there is some "postgresql-check-db-dir" (not part of PostgreSQL) that complains that there is nothing in /data/postgresql/data. Is that accurate? It looks like you succeeded in getting the startup process to look for the PostgreSQL data directory in the new location, but - alas - there is no data directory there yet. You could follow the instructions and run "initdb" to create the data directory. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Issue: Creating Symlink for data directory of postgresql in CentOS7
On Fri, 2019-07-12 at 18:08 +, Chatterjee, Shibayan wrote: > > Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is missing > > or empty. > > Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" to > > initialize the database cluster. > > Jul 12 10:00:51 postgresql-check-db-dir: See > > /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information. > > For sure there's all the necessary files in '/data/postgresql/data'. > The startup process cannot read it, because of sym link. Well, where is the source for this fabled "postgresql-check-db-dir"? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Issue: Creating Symlink for data directory of postgresql in CentOS7
Adrian Klaver wrote: > On 7/13/19 4:30 AM, Laurenz Albe wrote: > > On Fri, 2019-07-12 at 18:08 +, Chatterjee, Shibayan wrote: > > > > Jul 12 10:00:51 postgresql-check-db-dir: "/data/postgresql/data" is > > > > missing or empty. > > > > Jul 12 10:00:51 postgresql-check-db-dir: Use "postgresql-setup initdb" > > > > to initialize the database cluster. > > > > Jul 12 10:00:51 postgresql-check-db-dir: See > > > > /usr/share/doc/postgresql-9.2.24/README.rpm-dist for more information. > > > > > > For sure there's all the necessary files in '/data/postgresql/data'. > > > The startup process cannot read it, because of sym link. > > > > Well, where is the source for this fabled "postgresql-check-db-dir"? > > https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/9.2/postgresql/EL-7/postgresql92-check-db-dir;h=550b31770cabacf32cbb1b8f272e8ce305fc9908;hb=HEAD Thanks. I read this: 30 # Check for the PGDATA structure 31 if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ] 32 then [...] 49 else 50 # No existing PGDATA! Warn the user to initdb it. 51 echo $"\"$PGDATA\" is missing or empty." 52 echo $"Use \"/usr/pgsql-9.2/bin/postgresql92-setup initdb\" to initialize the database cluster." 53 echo $"See $PGDOCDIR/README.rpm-dist for more information." 54 exit 1 55 fi That means that either there was no regular file /data/postgresql/data/PG_VERSION or no directory /data/postgresql/data/base, or that the user running the script lacked the permissions to access them. Since you say that there was a regular data directory there, that would point to permission problems. Witn that information, it should be simple to debug the problem. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)?
On Fri, 2019-07-19 at 21:15 +0300, Alexandru Lazarev wrote: > I receive locking failure on pg_advisory_lock, I do deadlock condition and > receive following: > - - - > ERROR: deadlock detected > SQL state: 40P01 > Detail: Process 240828 waits for ExclusiveLock on advisory lock > [1167570,16820923,3422556162,1]; blocked by process 243637. > Process 243637 waits for ExclusiveLock on advisory lock > [1167570,16820923,3422556161,1]; blocked by process 240828. > - - - > I do from Tx1: > select pg_advisory_lock(72245317596090369); > select pg_advisory_lock(72245317596090370); > and from Tx2: > select pg_advisory_lock(72245317596090370); > select pg_advisory_lock(72245317596090369); > > where long key is following: 72245317596090369-> HEX 0x0100AABBCC001001 > where 1st byte (highest significance "0x01") is namespace masked with MAC > Address " AABBCC001001", but in error i see 4 numbers - what is their meaning? > I deducted that 2nd ( 16820923 .) HEX 0x100AABB, 1st half of long key) and > 3rd is ( 3422556161 -> HEX 0xCC001001, 2nd half of long key) > but what are 1st ( 1167570 ) and 4th (1) numbers? See this code in src/backend/utils/adt/lockfuncs.c: /* * Functions for manipulating advisory locks * * We make use of the locktag fields as follows: * * field1: MyDatabaseId ... ensures locks are local to each database * field2: first of 2 int4 keys, or high-order half of an int8 key * field3: second of 2 int4 keys, or low-order half of an int8 key * field4: 1 if using an int8 key, 2 if using 2 int4 keys */ #define SET_LOCKTAG_INT64(tag, key64) \ SET_LOCKTAG_ADVISORY(tag, \ MyDatabaseId, \ (uint32) ((key64) >> 32), \ (uint32) (key64), \ 1) #define SET_LOCKTAG_INT32(tag, key1, key2) \ SET_LOCKTAG_ADVISORY(tag, MyDatabaseId, key1, key2, 2) Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Sorting composite types
Miles Elam wrote: > Is there any way to define a natural sorting order for composite types? For > example, let's say you have a type like: > > CREATE TYPE contrived AS ( > i1 integer, > i2 integer > ); > > The semantics of this contrived type are that the natural order is ascending > NULLS first for i1 and descending NULLS last for i2. > I know I can do an ORDER BY per query and specify each part separately, but > if my contrived example has a perceived > natural sort order, is there any way to set that in a global way so that I > can simply and universally ORDER BY my_contrived_column? You'd have to define a default b-tree operator class for the type with operators that implement your desired sort order. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Postgres Database Backup Size
Shiwangini Shishulkar wrote: > Any way to reduce backup size of postgres backups or it's default behavior of > postgres to take backups which are double in size? The smallest backup would be a compressed custom format "pg_dump": pg_dump -F c -Z 9 ... I is very unusual for a custom format dump to be bigger than the original database. How did you measure the database size? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: How to gracefully keep my specific index in memory ?
James(王旭) wrote: > As the title,How to keep a specific index in memory gracefully? > > After some statistical query, I can determine that not all indexes can be fit > into memory, > but one of the most frequently used indexes(say idx_xyz) can be definitely > fit into > memory(specifically ,[the size of idx_xyz]=20% x [memory size]). > > I know there's pgprewarm, but I feel with pgprewarm I can't keep things under > control, e.g. no realtime monitor, being squeezed out of memory ,.etc. > > Is it possible that I can simply do something like "select idx_xyz into xxx" > and keep the specific index in memory forever? If the indexes are frequently used, they should remain cached anyway. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Changing work_mem
rihad wrote: > > Sorry, I just decreased work_mem back to 256MB, reloaded, and > > instantly started seeing 82mb temp file creation, not 165mb as was > > usual with work_mem=512MB. > > > > So it indeed was applied immediately. > > Really weird figures ) > > Increased work_mem to 768MB and start seeing temp file creation log > entries 331MB in size. > > Bizzare ) It looks like the bigger it gets, the bigger temp files > are > created. > > Why not decrease it to 64mb then... Temporary files are created whenever the data is estimated to not fit into "work_mem". So it is unsurprising that you see bigger temporary files being created if you increase "work_mem". Big temporary files will also be created when "work_mem" is small, but maybe they got lost in the noise of the smaller files. You should have noticed that fewer files are created when you increase "work_mem". Another thing to notice is that the temporary files use another, more compact format than the data in memory, so you need to increase "work_mem" to more than X if you want to avoid temporary files of size X. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Collumn level permissions ?
stan wrote: > I do know a way to solve this. I could create a view "B" that is a > view of > tab;e "A" without column "C" on the select, and give a user > permissions on > B, but not on A, or at least I think that would work. > > What I specifically need to do is "hide" one column of a table from a > set > of users. > > Any better way to do this? You can use column permissions: REVOKE SELECT ON mytable FROM auser; GRANT SELECT (col1, col2) ON mytable TO auser; This will cause any query that tries to read any other column to fail with an error. If you don't like that, your technique is good, but don't forget to add "WITH (security_barrier = true)" so that nobody can subvert security. Yours, Laurenz Albe -- +43-670-6056265 Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: https://www.cybertec-postgresql.com
Re: Two Postgres master process are showing - one is on and off
chiru r wrote: > I have observed one of our PostgreSQL DB instance showing two postgres > process on Linux server as highlighted. The second postgres process is on and > off. > We did not find any references in logs. > > Please provide your advice and help on this issue. > > DB version : PostgreSQL 9.5.10 > Server version : RHEL 7.6 > > [postgres@PGSERVER data]$ ps -ef|grep postgres > postgres 33438 1 0 12:41 ?00:00:03 > /u01/postgres/9.5/bin/postgres -D /u02/pgdata01/9.5/data > [...] > postgres 110181 33438 0 15:30 ?00:00:00 > /u01/postgres/9.5/bin/postgres -D /u02/pgdata01/9.5/data Perhaps it is a new client connection, and the process title just hasn't been changed (yet). What is your setting for "update_process_title"? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
